AryaWu/sqlite
0
1# 2018 September 202#3# The author disclaims copyright to this source code. In place of4# 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#12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix alterlegacy16 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19 finish_test20 return21}22 23do_execsql_test 1.0 {24 PRAGMA legacy_alter_table = 1;25 CREATE TABLE t1(a, b, CHECK(t1.a != t1.b));26 CREATE TABLE t2(a, b);27 CREATE INDEX t2expr ON t2(a) WHERE t2.b>0;28}29 30do_execsql_test 1.1 {31 SELECT sql FROM sqlite_master32} {33 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}34 {CREATE TABLE t2(a, b)}35 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}36}37 38# Legacy behavior is to corrupt the schema in this case, as the table name in39# the CHECK constraint is incorrect after "t1" is renamed. This version is40# slightly different - it rejects the change and rolls back the transaction.41do_catchsql_test 1.2 {42 ALTER TABLE t1 RENAME TO t1new;43} {1 {error in table t1new after rename: no such column: t1.a}}44 45do_execsql_test 1.3 {46 CREATE TABLE t3(c, d);47 ALTER TABLE t3 RENAME TO t3new;48 DROP TABLE t3new;49}50 51do_execsql_test 1.4 {52 SELECT sql FROM sqlite_master53} {54 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}55 {CREATE TABLE t2(a, b)}56 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}57}58 59 60do_catchsql_test 1.3 {61 ALTER TABLE t2 RENAME TO t2new;62} {1 {error in index t2expr after rename: no such column: t2.b}}63do_execsql_test 1.4 {64 SELECT sql FROM sqlite_master65} {66 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}67 {CREATE TABLE t2(a, b)}68 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}69}70 71 72#-------------------------------------------------------------------------73reset_db74ifcapable vtab {75 register_echo_module db76 77 do_execsql_test 2.0 {78 PRAGMA legacy_alter_table = 1;79 CREATE TABLE abc(a, b, c);80 INSERT INTO abc VALUES(1, 2, 3);81 CREATE VIRTUAL TABLE eee USING echo('abc');82 SELECT * FROM eee;83 } {1 2 3}84 85 do_execsql_test 2.1 {86 ALTER TABLE eee RENAME TO fff;87 SELECT * FROM fff;88 } {1 2 3}89 90 db close91 sqlite3 db test.db92 93 do_catchsql_test 2.2 {94 ALTER TABLE fff RENAME TO ggg;95 } {1 {no such module: echo}}96}97 98#-------------------------------------------------------------------------99reset_db100 101do_execsql_test 3.0 {102 PRAGMA legacy_alter_table = 1;103 CREATE TABLE txx(a, b, c);104 INSERT INTO txx VALUES(1, 2, 3);105 CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx;106 CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one;107 CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx;108}109 110do_execsql_test 3.1.1 {111 SELECT * FROM vvv;112} {1 2 3}113do_execsql_test 3.1.2a {114 ALTER TABLE txx RENAME TO "t xx";115}116do_catchsql_test 3.1.2b {117 SELECT * FROM vvv;118} {1 {no such table: main.txx}}119do_execsql_test 3.1.3 {120 SELECT sql FROM sqlite_master WHERE name='vvv';121} {{CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx}}122 123 124do_catchsql_test 3.2.1 {125 SELECT * FROM uuu;126} {1 {no such table: main.txx}}127do_execsql_test 3.2.2 {128 SELECT sql FROM sqlite_master WHERE name='uuu';;129} {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one}}130 131do_catchsql_test 3.3.1 {132 SELECT * FROM ttt;133} {1 {no such table: txx}}134do_execsql_test 3.3.2 {135 SELECT sql FROM sqlite_temp_master WHERE name='ttt';136} {{CREATE VIEW ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx}}137 138#-------------------------------------------------------------------------139reset_db140do_execsql_test 4.0 {141 PRAGMA legacy_alter_table = 1;142 CREATE table t1(x, y);143 CREATE table t2(a, b);144 145 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN146 SELECT t1.x, * FROM t1, t2;147 INSERT INTO t2 VALUES(new.x, new.y);148 END;149}150 151do_execsql_test 4.1 {152 INSERT INTO t1 VALUES(1, 1);153 ALTER TABLE t1 RENAME TO t11;154} 155do_catchsql_test 4.1a {156 INSERT INTO t11 VALUES(2, 2);157} {1 {no such table: main.t1}}158do_execsql_test 4.1b {159 ALTER TABLE t11 RENAME TO t1;160 ALTER TABLE t2 RENAME TO t22;161}162do_catchsql_test 4.1c {163 INSERT INTO t1 VALUES(3, 3);164} {1 {no such table: main.t2}}165 166proc squish {a} {167 string trim [regsub -all {[[:space:]][[:space:]]*} $a { }]168}169db func squish squish170do_test 4.2 {171 execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' }172} [list [squish {173 CREATE TRIGGER tr1 AFTER INSERT ON "t1" BEGIN174 SELECT t1.x, * FROM t1, t2;175 INSERT INTO t2 VALUES(new.x, new.y);176 END177}]]178 179#-------------------------------------------------------------------------180reset_db181do_execsql_test 5.0 {182 PRAGMA legacy_alter_table = 1;183 CREATE TABLE t9(a, b, c);184 CREATE TABLE t10(a, b, c);185 CREATE TEMP TABLE t9(a, b, c);186 187 CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN188 INSERT INTO t10 VALUES(new.a, new.b, new.c);189 END;190 191 INSERT INTO temp.t9 VALUES(1, 2, 3);192 SELECT * FROM t10;193} {1 2 3}194 195do_execsql_test 5.1 {196 ALTER TABLE temp.t9 RENAME TO 't1234567890'197}198 199do_execsql_test 5.2 {200 CREATE TABLE t1(a, b);201 CREATE TABLE t2(a, b);202 INSERT INTO t1 VALUES(1, 2);203 INSERT INTO t2 VALUES(3, 4);204 CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;205 SELECT * FROM v;206} {1 2 3 4}207 208do_execsql_test 5.3 {209 ALTER TABLE t2 RENAME TO one;210} {}211 212do_catchsql_test 5.4 {213 SELECT * FROM v214} {1 {no such table: main.t2}}215 216do_execsql_test 5.5 {217 ALTER TABLE one RENAME TO t2;218 DROP VIEW v;219 CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;220 SELECT * FROM vv;221} {1 2 3 4}222 223do_execsql_test 5.6 {224 ALTER TABLE t2 RENAME TO one;225} {}226do_catchsql_test 5.7 {227 SELECT * FROM vv228} {1 {no such table: t2}}229 230#-------------------------------------------------------------------------231 232ifcapable vtab {233 register_tcl_module db234 proc tcl_command {method args} {235 switch -- $method {236 xConnect {237 return "CREATE TABLE t1(a, b, c)"238 }239 }240 return {}241 }242 243 do_execsql_test 6.0 {244 CREATE VIRTUAL TABLE x1 USING tcl(tcl_command);245 }246 247 do_execsql_test 6.1 {248 ALTER TABLE x1 RENAME TO x2;249 SELECT sql FROM sqlite_master WHERE name = 'x2'250 } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}}251 252 do_execsql_test 7.1 {253 CREATE TABLE ddd(db, sql, zOld, zNew, bTemp);254 INSERT INTO ddd VALUES(255 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0256 ), (257 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0258 ), (259 'main', NULL, 'ddd', 'eee', 0260 );261 } {}262}263 264#-------------------------------------------------------------------------265#266reset_db267forcedelete test.db2268do_execsql_test 8.1 {269 PRAGMA legacy_alter_table = 1;270 ATTACH 'test.db2' AS aux;271 PRAGMA foreign_keys = on;272 CREATE TABLE aux.p1(a INTEGER PRIMARY KEY, b);273 CREATE TABLE aux.c1(x INTEGER PRIMARY KEY, y REFERENCES p1(a));274 INSERT INTO aux.p1 VALUES(1, 1);275 INSERT INTO aux.p1 VALUES(2, 2);276 INSERT INTO aux.c1 VALUES(NULL, 2);277 CREATE TABLE aux.c2(x INTEGER PRIMARY KEY, y REFERENCES c1(a));278}279 280do_execsql_test 8.2 {281 ALTER TABLE aux.p1 RENAME TO ppp;282}283 284do_execsql_test 8.2 {285 INSERT INTO aux.c1 VALUES(NULL, 1);286 SELECT sql FROM aux.sqlite_master WHERE name = 'c1';287} {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}}288 289reset_db290do_execsql_test 9.0 {291 PRAGMA legacy_alter_table = 1;292 CREATE TABLE t1(a, b, c);293 CREATE VIEW v1 AS SELECT * FROM t2;294}295do_execsql_test 9.1 {296 ALTER TABLE t1 RENAME TO t3;297} {}298do_execsql_test 9.1b {299 ALTER TABLE t3 RENAME TO t1;300} {}301do_execsql_test 9.2 {302 DROP VIEW v1;303 CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN304 INSERT INTO t2 VALUES(new.a);305 END;306}307do_execsql_test 9.3 {308 ALTER TABLE t1 RENAME TO t3;309} {}310 311forcedelete test.db2312do_execsql_test 9.4 {313 ALTER TABLE t3 RENAME TO t1;314 DROP TRIGGER tr;315 316 ATTACH 'test.db2' AS aux;317 CREATE TRIGGER tr AFTER INSERT ON t1 WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END;318 319 CREATE TABLE aux.t1(x);320 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END;321}322do_execsql_test 9.5 {323 ALTER TABLE main.t1 RENAME TO t3;324}325do_execsql_test 9.6 {326 SELECT sql FROM sqlite_temp_master;327 SELECT sql FROM sqlite_master WHERE type='trigger';328} {329 {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END}330 {CREATE TRIGGER tr AFTER INSERT ON "t3" WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END}331}332 333#-------------------------------------------------------------------------334reset_db335ifcapable fts5 {336 do_execsql_test 10.0 {337 PRAGMA legacy_alter_table = 1;338 CREATE VIRTUAL TABLE fff USING fts5(x, y, z);339 }340 341 do_execsql_test 10.1 {342 BEGIN;343 INSERT INTO fff VALUES('a', 'b', 'c');344 ALTER TABLE fff RENAME TO ggg;345 COMMIT;346 }347 348 do_execsql_test 10.2 {349 SELECT * FROM ggg;350 } {a b c}351}352 353#-------------------------------------------------------------------------354reset_db355forcedelete test.db2356db func trigger trigger357set ::trigger [list]358proc trigger {args} {359 lappend ::trigger $args360}361do_execsql_test 11.0 {362 PRAGMA legacy_alter_table = 1;363 ATTACH 'test.db2' AS aux;364 CREATE TABLE aux.t1(a, b, c);365 CREATE TABLE main.t1(a, b, c);366 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN367 SELECT trigger(new.a, new.b, new.c);368 END;369}370 371do_execsql_test 11.1 {372 INSERT INTO main.t1 VALUES(1, 2, 3);373 INSERT INTO aux.t1 VALUES(4, 5, 6);374}375do_test 11.2 { set ::trigger } {{4 5 6}}376 377do_execsql_test 11.3 {378 SELECT name, tbl_name FROM sqlite_temp_master;379} {tr t1}380 381do_execsql_test 11.4 {382 ALTER TABLE main.t1 RENAME TO t2;383 SELECT name, tbl_name FROM sqlite_temp_master;384} {tr t1}385 386do_execsql_test 11.5 {387 ALTER TABLE aux.t1 RENAME TO t2;388 SELECT name, tbl_name FROM sqlite_temp_master;389} {tr t2}390 391do_execsql_test 11.6 {392 INSERT INTO aux.t2 VALUES(7, 8, 9);393}394do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}}395 396#-------------------------------------------------------------------------397reset_db398do_execsql_test 12.0 {399 PRAGMA legacy_alter_table = 1;400 CREATE TABLE t1(a);401 CREATE TABLE t2(w);402 CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN403 INSERT INTO t1(a) VALUES(new.w);404 END;405 CREATE TEMP TABLE t2(x);406}407 408do_execsql_test 12.1 {409 ALTER TABLE main.t2 RENAME TO t3;410}411 412do_execsql_test 12.2 {413 INSERT INTO t3 VALUES('WWW');414 SELECT * FROM t1;415} {WWW}416 417 418#-------------------------------------------------------------------------419reset_db420 421ifcapable rtree {422 do_execsql_test 14.0 {423 PRAGMA legacy_alter_table = 1;424 CREATE VIRTUAL TABLE rt USING rtree(id, minx, maxx, miny, maxy);425 426 CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB);427 428 CREATE TRIGGER tr1 AFTER UPDATE OF "geom" ON "mytable" 429 WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN 430 DELETE FROM rt WHERE id = OLD."fid"; 431 END;432 433 INSERT INTO mytable VALUES(1, X'abcd');434 }435 436 do_execsql_test 14.1 {437 UPDATE mytable SET geom = X'1234'438 }439 440 do_execsql_test 14.2 {441 ALTER TABLE mytable RENAME TO mytable_renamed;442 }443 444 do_execsql_test 14.3 {445 CREATE TRIGGER tr2 AFTER INSERT ON mytable_renamed BEGIN446 DELETE FROM rt WHERE id=(SELECT min(id) FROM rt);447 END;448 }449 450 do_execsql_test 14.4 {451 ALTER TABLE mytable_renamed RENAME TO mytable2;452 }453}454 455reset_db456do_execsql_test 14.5 {457 PRAGMA legacy_alter_table = 1;458 CREATE TABLE t1(a, b, c);459 CREATE VIEW v1 AS SELECT * FROM t1;460 CREATE TRIGGER xyz AFTER INSERT ON t1 BEGIN461 SELECT a, b FROM v1;462 END;463}464do_execsql_test 14.6 {465 ALTER TABLE t1 RENAME TO tt1;466}467 468 469finish_test470