1# 2004 November 10 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#************************************************************************* 11# This file implements regression tests for SQLite library. The 12# focus of this script is testing the ALTER TABLE statement. 13# 14# $Id: alter.test,v 1.11 2005/03/29 03:11:00 danielk1977 Exp $ 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 21ifcapable !altertable { 22 finish_test 23 return 24} 25 26#---------------------------------------------------------------------- 27# Test organization: 28# 29# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables 30# with implicit and explicit indices. These tests came from an earlier 31# fork of SQLite that also supported ALTER TABLE. 32# alter-1.8.*: Tests for ALTER TABLE when the table resides in an 33# attached database. 34# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the 35# table name and left parenthesis token. i.e: 36# "CREATE TABLE abc (a, b, c);" 37# alter-2.*: Test error conditions and messages. 38# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them. 39# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields. 40# 41 42# Create some tables to rename. Be sure to include some TEMP tables 43# and some tables with odd names. 44# 45do_test alter-1.1 { 46 ifcapable tempdb { 47 set ::temp TEMP 48 } else { 49 set ::temp {} 50 } 51 execsql [subst -nocommands { 52 CREATE TABLE t1(a,b); 53 INSERT INTO t1 VALUES(1,2); 54 CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY); 55 INSERT INTO [t1'x1] VALUES(3,4); 56 CREATE INDEX t1i1 ON T1(B); 57 CREATE INDEX t1i2 ON t1(a,b); 58 CREATE INDEX i3 ON [t1'x1](b,c); 59 CREATE $::temp TABLE "temp table"(e,f,g UNIQUE); 60 CREATE INDEX i2 ON [temp table](f); 61 INSERT INTO [temp table] VALUES(5,6,7); 62 }] 63 execsql { 64 SELECT 't1', * FROM t1; 65 SELECT 't1''x1', * FROM "t1'x1"; 66 SELECT * FROM [temp table]; 67 } 68} {t1 1 2 t1'x1 3 4 5 6 7} 69do_test alter-1.2 { 70 execsql [subst { 71 CREATE $::temp TABLE objlist(type, name, tbl_name); 72 INSERT INTO objlist SELECT type, name, tbl_name 73 FROM sqlite_master WHERE NAME!='objlist'; 74 }] 75 ifcapable tempdb { 76 execsql { 77 INSERT INTO objlist SELECT type, name, tbl_name 78 FROM sqlite_temp_master WHERE NAME!='objlist'; 79 } 80 } 81 82 execsql { 83 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 84 } 85} [list \ 86 table t1 t1 \ 87 index t1i1 t1 \ 88 index t1i2 t1 \ 89 table t1'x1 t1'x1 \ 90 index i3 t1'x1 \ 91 index {sqlite_autoindex_t1'x1_1} t1'x1 \ 92 index {sqlite_autoindex_t1'x1_2} t1'x1 \ 93 table {temp table} {temp table} \ 94 index i2 {temp table} \ 95 index {sqlite_autoindex_temp table_1} {temp table} \ 96 ] 97 98# Make some changes 99# 100do_test alter-1.3 { 101 execsql { 102 ALTER TABLE [T1] RENAME to [-t1-]; 103 ALTER TABLE "t1'x1" RENAME TO T2; 104 ALTER TABLE [temp table] RENAME to TempTab; 105 } 106} {} 107integrity_check alter-1.3.1 108do_test alter-1.4 { 109 execsql { 110 SELECT 't1', * FROM [-t1-]; 111 SELECT 't2', * FROM t2; 112 SELECT * FROM temptab; 113 } 114} {t1 1 2 t2 3 4 5 6 7} 115do_test alter-1.5 { 116 execsql { 117 DELETE FROM objlist; 118 INSERT INTO objlist SELECT type, name, tbl_name 119 FROM sqlite_master WHERE NAME!='objlist'; 120 } 121 catchsql { 122 INSERT INTO objlist SELECT type, name, tbl_name 123 FROM sqlite_temp_master WHERE NAME!='objlist'; 124 } 125 execsql { 126 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 127 } 128} [list \ 129 table -t1- -t1- \ 130 index t1i1 -t1- \ 131 index t1i2 -t1- \ 132 table T2 T2 \ 133 index i3 T2 \ 134 index {sqlite_autoindex_T2_1} T2 \ 135 index {sqlite_autoindex_T2_2} T2 \ 136 table {TempTab} {TempTab} \ 137 index i2 {TempTab} \ 138 index {sqlite_autoindex_TempTab_1} {TempTab} \ 139 ] 140 141# Make sure the changes persist after restarting the database. 142# (The TEMP table will not persist, of course.) 143# 144ifcapable tempdb { 145 do_test alter-1.6 { 146 db close 147 set DB [sqlite3 db test.db] 148 execsql { 149 CREATE TEMP TABLE objlist(type, name, tbl_name); 150 INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master; 151 INSERT INTO objlist 152 SELECT type, name, tbl_name FROM sqlite_temp_master 153 WHERE NAME!='objlist'; 154 SELECT type, name, tbl_name FROM objlist 155 ORDER BY tbl_name, type desc, name; 156 } 157 } [list \ 158 table -t1- -t1- \ 159 index t1i1 -t1- \ 160 index t1i2 -t1- \ 161 table T2 T2 \ 162 index i3 T2 \ 163 index {sqlite_autoindex_T2_1} T2 \ 164 index {sqlite_autoindex_T2_2} T2 \ 165 ] 166} else { 167 execsql { 168 DROP TABLE TempTab; 169 } 170} 171 172# Make sure the ALTER TABLE statements work with the 173# non-callback API 174# 175do_test alter-1.7 { 176 stepsql $DB { 177 ALTER TABLE [-t1-] RENAME to [*t1*]; 178 ALTER TABLE T2 RENAME TO [<t2>]; 179 } 180 execsql { 181 DELETE FROM objlist; 182 INSERT INTO objlist SELECT type, name, tbl_name 183 FROM sqlite_master WHERE NAME!='objlist'; 184 } 185 catchsql { 186 INSERT INTO objlist SELECT type, name, tbl_name 187 FROM sqlite_temp_master WHERE NAME!='objlist'; 188 } 189 execsql { 190 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 191 } 192} [list \ 193 table *t1* *t1* \ 194 index t1i1 *t1* \ 195 index t1i2 *t1* \ 196 table <t2> <t2> \ 197 index i3 <t2> \ 198 index {sqlite_autoindex_<t2>_1} <t2> \ 199 index {sqlite_autoindex_<t2>_2} <t2> \ 200 ] 201 202# Check that ALTER TABLE works on attached databases. 203# 204do_test alter-1.8.1 { 205 file delete -force test2.db 206 file delete -force test2.db-journal 207 execsql { 208 ATTACH 'test2.db' AS aux; 209 } 210} {} 211do_test alter-1.8.2 { 212 execsql { 213 CREATE TABLE t4(a PRIMARY KEY, b, c); 214 CREATE TABLE aux.t4(a PRIMARY KEY, b, c); 215 CREATE INDEX i4 ON t4(b); 216 CREATE INDEX aux.i4 ON t4(b); 217 } 218} {} 219do_test alter-1.8.3 { 220 execsql { 221 INSERT INTO t4 VALUES('main', 'main', 'main'); 222 INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux'); 223 SELECT * FROM t4 WHERE a = 'main'; 224 } 225} {main main main} 226do_test alter-1.8.4 { 227 execsql { 228 ALTER TABLE t4 RENAME TO t5; 229 SELECT * FROM t4 WHERE a = 'aux'; 230 } 231} {aux aux aux} 232do_test alter-1.8.5 { 233 execsql { 234 SELECT * FROM t5; 235 } 236} {main main main} 237do_test alter-1.8.6 { 238 execsql { 239 SELECT * FROM t5 WHERE b = 'main'; 240 } 241} {main main main} 242do_test alter-1.8.7 { 243 execsql { 244 ALTER TABLE aux.t4 RENAME TO t5; 245 SELECT * FROM aux.t5 WHERE b = 'aux'; 246 } 247} {aux aux aux} 248 249do_test alter-1.9.1 { 250 execsql { 251 CREATE TABLE tbl1 (a, b, c); 252 INSERT INTO tbl1 VALUES(1, 2, 3); 253 } 254} {} 255do_test alter-1.9.2 { 256 execsql { 257 SELECT * FROM tbl1; 258 } 259} {1 2 3} 260do_test alter-1.9.3 { 261 execsql { 262 ALTER TABLE tbl1 RENAME TO tbl2; 263 SELECT * FROM tbl2; 264 } 265} {1 2 3} 266do_test alter-1.9.4 { 267 execsql { 268 DROP TABLE tbl2; 269 } 270} {} 271 272# Test error messages 273# 274do_test alter-2.1 { 275 catchsql { 276 ALTER TABLE none RENAME TO hi; 277 } 278} {1 {no such table: none}} 279do_test alter-2.2 { 280 execsql { 281 CREATE TABLE t3(p,q,r); 282 } 283 catchsql { 284 ALTER TABLE [<t2>] RENAME TO t3; 285 } 286} {1 {there is already another table or index with this name: t3}} 287do_test alter-2.3 { 288 catchsql { 289 ALTER TABLE [<t2>] RENAME TO i3; 290 } 291} {1 {there is already another table or index with this name: i3}} 292do_test alter-2.4 { 293 catchsql { 294 ALTER TABLE SqLiTe_master RENAME TO master; 295 } 296} {1 {table sqlite_master may not be altered}} 297do_test alter-2.5 { 298 catchsql { 299 ALTER TABLE t3 RENAME TO sqlite_t3; 300 } 301} {1 {object name reserved for internal use: sqlite_t3}} 302 303# If this compilation does not include triggers, omit the alter-3.* tests. 304ifcapable trigger { 305 306#----------------------------------------------------------------------- 307# Tests alter-3.* test ALTER TABLE on tables that have triggers. 308# 309# alter-3.1.*: ALTER TABLE with triggers. 310# alter-3.2.*: Test that the ON keyword cannot be used as a database, 311# table or column name unquoted. This is done because part of the 312# ALTER TABLE code (specifically the implementation of SQL function 313# "sqlite_alter_trigger") will break in this case. 314# alter-3.3.*: ALTER TABLE with TEMP triggers (todo). 315# 316 317# An SQL user-function for triggers to fire, so that we know they 318# are working. 319proc trigfunc {args} { 320 set ::TRIGGER $args 321} 322db func trigfunc trigfunc 323 324do_test alter-3.1.0 { 325 execsql { 326 CREATE TABLE t6(a, b, c); 327 CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN 328 SELECT trigfunc('trig1', new.a, new.b, new.c); 329 END; 330 } 331} {} 332do_test alter-3.1.1 { 333 execsql { 334 INSERT INTO t6 VALUES(1, 2, 3); 335 } 336 set ::TRIGGER 337} {trig1 1 2 3} 338do_test alter-3.1.2 { 339 execsql { 340 ALTER TABLE t6 RENAME TO t7; 341 INSERT INTO t7 VALUES(4, 5, 6); 342 } 343 set ::TRIGGER 344} {trig1 4 5 6} 345do_test alter-3.1.3 { 346 execsql { 347 DROP TRIGGER trig1; 348 } 349} {} 350do_test alter-3.1.4 { 351 execsql { 352 CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN 353 SELECT trigfunc('trig2', new.a, new.b, new.c); 354 END; 355 INSERT INTO t7 VALUES(1, 2, 3); 356 } 357 set ::TRIGGER 358} {trig2 1 2 3} 359do_test alter-3.1.5 { 360 execsql { 361 ALTER TABLE t7 RENAME TO t8; 362 INSERT INTO t8 VALUES(4, 5, 6); 363 } 364 set ::TRIGGER 365} {trig2 4 5 6} 366do_test alter-3.1.6 { 367 execsql { 368 DROP TRIGGER trig2; 369 } 370} {} 371do_test alter-3.1.7 { 372 execsql { 373 CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN 374 SELECT trigfunc('trig3', new.a, new.b, new.c); 375 END; 376 INSERT INTO t8 VALUES(1, 2, 3); 377 } 378 set ::TRIGGER 379} {trig3 1 2 3} 380do_test alter-3.1.8 { 381 execsql { 382 ALTER TABLE t8 RENAME TO t9; 383 INSERT INTO t9 VALUES(4, 5, 6); 384 } 385 set ::TRIGGER 386} {trig3 4 5 6} 387 388# Make sure "ON" cannot be used as a database, table or column name without 389# quoting. Otherwise the sqlite_alter_trigger() function might not work. 390file delete -force test3.db 391file delete -force test3.db-journal 392do_test alter-3.2.1 { 393 catchsql { 394 ATTACH 'test3.db' AS ON; 395 } 396} {1 {near "ON": syntax error}} 397do_test alter-3.2.2 { 398 catchsql { 399 ATTACH 'test3.db' AS 'ON'; 400 } 401} {0 {}} 402do_test alter-3.2.3 { 403 catchsql { 404 CREATE TABLE ON.t1(a, b, c); 405 } 406} {1 {near "ON": syntax error}} 407do_test alter-3.2.4 { 408 catchsql { 409 CREATE TABLE 'ON'.t1(a, b, c); 410 } 411} {0 {}} 412do_test alter-3.2.4 { 413 catchsql { 414 CREATE TABLE 'ON'.ON(a, b, c); 415 } 416} {1 {near "ON": syntax error}} 417do_test alter-3.2.5 { 418 catchsql { 419 CREATE TABLE 'ON'.'ON'(a, b, c); 420 } 421} {0 {}} 422do_test alter-3.2.6 { 423 catchsql { 424 CREATE TABLE t10(a, ON, c); 425 } 426} {1 {near "ON": syntax error}} 427do_test alter-3.2.7 { 428 catchsql { 429 CREATE TABLE t10(a, 'ON', c); 430 } 431} {0 {}} 432do_test alter-3.2.8 { 433 catchsql { 434 CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END; 435 } 436} {1 {near "ON": syntax error}} 437do_test alter-3.2.9 { 438 catchsql { 439 CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END; 440 } 441} {0 {}} 442do_test alter-3.2.10 { 443 execsql { 444 DROP TABLE t10; 445 } 446} {} 447 448do_test alter-3.3.1 { 449 execsql [subst { 450 CREATE TABLE tbl1(a, b, c); 451 CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN 452 SELECT trigfunc('trig1', new.a, new.b, new.c); 453 END; 454 }] 455} {} 456do_test alter-3.3.2 { 457 execsql { 458 INSERT INTO tbl1 VALUES('a', 'b', 'c'); 459 } 460 set ::TRIGGER 461} {trig1 a b c} 462do_test alter-3.3.3 { 463 execsql { 464 ALTER TABLE tbl1 RENAME TO tbl2; 465 INSERT INTO tbl2 VALUES('d', 'e', 'f'); 466 } 467 set ::TRIGGER 468} {trig1 d e f} 469do_test alter-3.3.4 { 470 execsql [subst { 471 CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN 472 SELECT trigfunc('trig2', new.a, new.b, new.c); 473 END; 474 }] 475} {} 476do_test alter-3.3.5 { 477 execsql { 478 ALTER TABLE tbl2 RENAME TO tbl3; 479 INSERT INTO tbl3 VALUES('g', 'h', 'i'); 480 } 481 set ::TRIGGER 482} {trig1 g h i} 483do_test alter-3.3.6 { 484 execsql { 485 UPDATE tbl3 SET a = 'G' where a = 'g'; 486 } 487 set ::TRIGGER 488} {trig2 G h i} 489do_test alter-3.3.7 { 490 execsql { 491 DROP TABLE tbl3; 492 } 493} {} 494ifcapable tempdb { 495 do_test alter-3.3.8 { 496 execsql { 497 SELECT * FROM sqlite_temp_master WHERE type = 'trigger'; 498 } 499 } {} 500} 501 502} ;# ifcapable trigger 503 504# If the build does not include AUTOINCREMENT fields, omit alter-4.*. 505ifcapable autoinc { 506 507do_test alter-4.1 { 508 execsql { 509 CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT); 510 INSERT INTO tbl1 VALUES(10); 511 } 512} {} 513do_test alter-4.2 { 514 execsql { 515 INSERT INTO tbl1 VALUES(NULL); 516 SELECT a FROM tbl1; 517 } 518} {10 11} 519do_test alter-4.3 { 520 execsql { 521 ALTER TABLE tbl1 RENAME TO tbl2; 522 DELETE FROM tbl2; 523 INSERT INTO tbl2 VALUES(NULL); 524 SELECT a FROM tbl2; 525 } 526} {12} 527do_test alter-4.4 { 528 execsql { 529 DROP TABLE tbl2; 530 } 531} {} 532 533} ;# ifcapable autoinc 534 535# Test that it is Ok to execute an ALTER TABLE immediately after 536# opening a database. 537do_test alter-5.1 { 538 execsql { 539 CREATE TABLE tbl1(a, b, c); 540 INSERT INTO tbl1 VALUES('x', 'y', 'z'); 541 } 542} {} 543do_test alter-5.2 { 544 sqlite3 db2 test.db 545 execsql { 546 ALTER TABLE tbl1 RENAME TO tbl2; 547 SELECT * FROM tbl2; 548 } db2 549} {x y z} 550do_test alter-5.3 { 551 db2 close 552} {} 553 554finish_test 555