AryaWu/sqlite
0
1# 2018 September 302#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 altertab216 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19 finish_test20 return21}22 23ifcapable fts5 {24 do_execsql_test 1.0 {25 CREATE TABLE rr(a, b);26 CREATE VIRTUAL TABLE ff USING fts5(a, b);27 CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN28 INSERT INTO ff VALUES(new.a, new.b);29 END;30 INSERT INTO rr VALUES('hello', 'world');31 SELECT * FROM ff;32 } {hello world}33 34 do_execsql_test 1.1 {35 ALTER TABLE ff RENAME TO ffff;36 }37 38 do_execsql_test 1.2 {39 INSERT INTO rr VALUES('in', 'tcl');40 SELECT * FROM ffff;41 } {hello world in tcl}42}43 44#-------------------------------------------------------------------------45# Check that table names that appear in REFERENCES clauses are updated46# when a table is renamed unless:47#48# a) "PRAGMA legacy_alter_table" is true, and49# b) "PRAGMA foreign_keys" is false.50#51do_execsql_test 2.0 {52 CREATE TABLE p1(a PRIMARY KEY, b);53 CREATE TABLE c1(x REFERENCES p1);54 CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1);55 CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a));56}57 58do_execsql_test 2.1 {59 ALTER TABLE p1 RENAME TO p2;60 SELECT sql FROM sqlite_master WHERE name LIKE 'c%';61} {62 {CREATE TABLE c1(x REFERENCES "p2")}63 {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}64 {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}65}66 67do_execsql_test 2.2 {68 PRAGMA legacy_alter_table = 1;69 ALTER TABLE p2 RENAME TO p3;70 SELECT sql FROM sqlite_master WHERE name LIKE 'c%';71} {72 {CREATE TABLE c1(x REFERENCES "p2")}73 {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}74 {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}75}76 77do_execsql_test 2.3 {78 ALTER TABLE p3 RENAME TO p2;79 PRAGMA foreign_keys = 1;80 ALTER TABLE p2 RENAME TO p3;81 SELECT sql FROM sqlite_master WHERE name LIKE 'c%';82} {83 {CREATE TABLE c1(x REFERENCES "p3")}84 {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")}85 {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))}86}87 88#-------------------------------------------------------------------------89# Table name in WITH clauses that are part of views or triggers.90#91foreach {tn schema} {92 1 {93 CREATE TABLE log_entry(col1, y);94 CREATE INDEX i1 ON log_entry(col1);95 }96 97 2 {98 CREATE TABLE t1(a, b, c);99 CREATE TABLE t2(x);100 CREATE TABLE log_entry(col1);101 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN102 INSERT INTO t2 SELECT col1 FROM log_entry;103 END;104 }105 106 3 {107 CREATE TABLE t1(a, b, c);108 CREATE TABLE t2(x);109 CREATE TABLE log_entry(col1);110 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN111 INSERT INTO t2112 WITH xyz(x) AS (SELECT col1 FROM log_entry)113 SELECT x FROM xyz;114 END;115 }116 117 4 {118 CREATE TABLE log_entry(col1);119 CREATE VIEW ttt AS120 WITH xyz(x) AS (SELECT col1 FROM log_entry)121 SELECT x FROM xyz;122 }123} {124 reset_db125 do_execsql_test 3.$tn.1 $schema126 set expect [db eval "SELECT sql FROM sqlite_master"]127 set expect [string map {log_entry {"newname"}} $expect]128 129 do_execsql_test 3.$tn.2 {130 ALTER TABLE log_entry RENAME TO newname;131 SELECT sql FROM sqlite_master;132 } $expect133 134 reset_db135 do_execsql_test 3.$tn.3 $schema136 set expect [db eval "SELECT sql FROM sqlite_master"]137 set expect [string map {col1 newname} $expect]138 139 do_execsql_test 3.$tn.4 {140 ALTER TABLE log_entry RENAME col1 TO newname;141 SELECT sql FROM sqlite_master;142 } $expect143}144 145#-------------------------------------------------------------------------146reset_db147do_execsql_test 4.0 {148 CREATE TABLE t1(a,b,c,d,e,f);149 CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN150 UPDATE t1 SET (c,d)=(a,b);151 END;152}153 154do_execsql_test 4.1 {155 ALTER TABLE t1 RENAME TO t1x;156 SELECT sql FROM sqlite_master WHERE type = 'trigger';157} {158{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN159 UPDATE "t1x" SET (c,d)=(a,b);160 END}161}162 163do_execsql_test 4.2 {164 ALTER TABLE t1x RENAME a TO aaa;165 SELECT sql FROM sqlite_master WHERE type = 'trigger';166} {167{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN168 UPDATE "t1x" SET (c,d)=(aaa,b);169 END}170}171 172do_execsql_test 4.3 {173 ALTER TABLE t1x RENAME d TO ddd;174 SELECT sql FROM sqlite_master WHERE type = 'trigger';175} {176{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN177 UPDATE "t1x" SET (c,ddd)=(aaa,b);178 END}179}180 181#-------------------------------------------------------------------------182ifcapable windowfunc {183do_execsql_test 5.0 {184 CREATE TABLE t2(a);185 CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN186 SELECT a, sum(a) OVER w1 FROM t2187 WINDOW w1 AS (188 PARTITION BY a ORDER BY a 189 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING190 ),191 w2 AS (192 PARTITION BY a193 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING194 );195 END;196} {}197 198do_execsql_test 5.0.1 {199 INSERT INTO t2 VALUES(1);200} {}201 202do_execsql_test 5.1 {203 ALTER TABLE t2 RENAME TO t2x;204 SELECT sql FROM sqlite_master WHERE name = 'r2';205} {206 {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN207 SELECT a, sum(a) OVER w1 FROM "t2x"208 WINDOW w1 AS (209 PARTITION BY a ORDER BY a 210 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING211 ),212 w2 AS (213 PARTITION BY a214 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING215 );216 END}217}218 219do_execsql_test 5.2 {220 ALTER TABLE t2x RENAME a TO aaaa;221 SELECT sql FROM sqlite_master WHERE name = 'r2';222} {223 {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN224 SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x"225 WINDOW w1 AS (226 PARTITION BY aaaa ORDER BY aaaa 227 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING228 ),229 w2 AS (230 PARTITION BY aaaa231 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING232 );233 END}234}235 236do_execsql_test 5.3 {237 INSERT INTO t2x VALUES(1);238} {}239} ;# windowfunc240 241#-------------------------------------------------------------------------242 243do_execsql_test 6.0 {244 CREATE TABLE t3(a,b,c,d);245 CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN246 SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;247 SELECT rowid, * FROM t3;248 END;249} {}250 251do_execsql_test 6.1 {252 ALTER TABLE t3 RENAME TO t3x;253 SELECT sql FROM sqlite_master WHERE name = 'r3';254} {255 {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN256 SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a;257 SELECT rowid, * FROM "t3x";258 END}259}260 261do_execsql_test 6.2 {262 ALTER TABLE t3x RENAME a TO abcd;263 SELECT sql FROM sqlite_master WHERE name = 'r3';264} {265 {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN266 SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd;267 SELECT rowid, * FROM "t3x";268 END}269}270 271#-------------------------------------------------------------------------272reset_db273 274do_execsql_test 7.0 {275 CREATE TABLE t1(a,b,c,d,e,f);276 INSERT INTO t1 VALUES(1,2,3,4,5,6);277 CREATE TABLE t2(x,y,z);278}279 280do_execsql_test 7.1 {281 SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;282} {1 2 3 4 5 6}283 284do_execsql_test 7.2 {285 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN286 INSERT INTO t2287 SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;288 END;289 INSERT INTO t1 VALUES(2,3,4,5,6,7);290 SELECT * FROM t2;291} {1 2 3 2 3 4 4 5 6 5 6 7}292 293do_execsql_test 7.3 {294 ALTER TABLE t1 RENAME TO xyzzy;295 SELECT sql FROM sqlite_master WHERE name='r1'296} {297 {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN298 INSERT INTO t2299 SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c;300 END}301}302 303do_execsql_test 7.3 {304 ALTER TABLE xyzzy RENAME c TO ccc;305 SELECT sql FROM sqlite_master WHERE name='r1'306} {307 {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN308 INSERT INTO t2309 SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;310 END}311}312 313#-------------------------------------------------------------------------314reset_db315do_execsql_test 8.0 {316 CREATE TABLE t1(a, b, c); 317 CREATE TABLE t2(a, b, c); 318 CREATE TABLE t3(d, e, f);319 CREATE VIEW v1 AS SELECT * FROM t1;320 CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN321 UPDATE t2 SET a = new.d;322 SELECT a, b, c FROM v1;323 END;324}325 326do_execsql_test 8.1 {327 INSERT INTO t3 VALUES(1, 2, 3);328}329 330# The following ALTER TABLE fails as if column "t1.a" is renamed the "a"331# in the "SELECT a, b, c FROM v1" within the trigger can no longer be332# resolved. But at one point there was a bug allowing the ALTER TABLE333# succeed. Which meant the subsequent INSERT statement would fail.334do_catchsql_test 8.2 {335 ALTER TABLE t1 RENAME a TO aaa;336} {1 {error in trigger tr after rename: no such column: a}}337do_execsql_test 8.3 {338 INSERT INTO t3 VALUES(4, 5, 6);339}340 341do_execsql_test 8.4 {342 CREATE TABLE t4(a, b);343 CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2;344}345 346# Branches of an expression tree that are optimized out by the AND 347# optimization are renamed.348#349do_execsql_test 8.5 {350 ALTER TABLE t4 RENAME a TO c;351 SELECT sql FROM sqlite_master WHERE name = 'v4'352} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}}353 354# 2019-06-10 https://sqlite.org/src/info/533010b8cacebe82355reset_db356do_catchsql_test 8.6 {357 CREATE TABLE t0(c0);358 CREATE INDEX i0 ON t0(likelihood(1,2) AND 0);359 ALTER TABLE t0 RENAME TO t1;360 SELECT sql FROM sqlite_master WHERE name='i0';361} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}362 363 364reset_db365 366do_execsql_test 9.0 {367 CREATE TABLE t1(a,b,c,d);368 CREATE TABLE t2(a,b,c,d,x);369 370 CREATE TRIGGER AFTER INSERT ON t2 BEGIN371 372 SELECT group_conct(373 123 ORDER BY (374 SELECT 1 FROM ( VALUES(a, 'b'), ('c') )375 )) 376 FROM t1;377 378 END;379}380 381do_catchsql_test 9.1 {382 ALTER TABLE t2 RENAME TO newname;383} {1 {error in trigger AFTER: all VALUES must have the same number of terms}}384 385finish_test386