1# 2018 September 30 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# 12 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix altertab2 16 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18ifcapable !altertable { 19 finish_test 20 return 21} 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 BEGIN 28 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 updated 46# when a table is renamed unless: 47# 48# a) "PRAGMA legacy_alter_table" is true, and 49# 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 BEGIN 102 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 BEGIN 111 INSERT INTO t2 112 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 AS 120 WITH xyz(x) AS (SELECT col1 FROM log_entry) 121 SELECT x FROM xyz; 122 } 123} { 124 reset_db 125 do_execsql_test 3.$tn.1 $schema 126 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 } $expect 133 134 reset_db 135 do_execsql_test 3.$tn.3 $schema 136 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 } $expect 143} 144 145#------------------------------------------------------------------------- 146reset_db 147do_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 BEGIN 150 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 BEGIN 159 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 BEGIN 168 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 BEGIN 177 UPDATE "t1x" SET (c,ddd)=(aaa,b); 178 END} 179} 180 181#------------------------------------------------------------------------- 182do_execsql_test 5.0 { 183 CREATE TABLE t2(a); 184 CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN 185 SELECT a, rank() OVER w1 FROM t2 186 WINDOW w1 AS ( 187 PARTITION BY b ORDER BY d ROWS BETWEEN 2 PRECEDING AND a FOLLOWING 188 ), 189 w2 AS ( 190 PARTITION BY b 191 ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 192 ); 193 END; 194} {} 195 196do_catchsql_test 5.0.1 { 197 INSERT INTO t2 VALUES(1); 198} {1 {no such column: b}} 199 200do_execsql_test 5.1 { 201 ALTER TABLE t2 RENAME TO t2x; 202 SELECT sql FROM sqlite_master WHERE name = 'r2'; 203} { 204 {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN 205 SELECT a, rank() OVER w1 FROM "t2x" 206 WINDOW w1 AS ( 207 PARTITION BY b ORDER BY d ROWS BETWEEN 2 PRECEDING AND a FOLLOWING 208 ), 209 w2 AS ( 210 PARTITION BY b 211 ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 212 ); 213 END} 214} 215 216do_execsql_test 5.2 { 217 ALTER TABLE t2x RENAME a TO aaaa; 218 SELECT sql FROM sqlite_master WHERE name = 'r2'; 219} { 220 {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN 221 SELECT aaaa, rank() OVER w1 FROM "t2x" 222 WINDOW w1 AS ( 223 PARTITION BY b ORDER BY d ROWS BETWEEN 2 PRECEDING AND a FOLLOWING 224 ), 225 w2 AS ( 226 PARTITION BY b 227 ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 228 ); 229 END} 230} 231 232do_catchsql_test 5.3 { 233 INSERT INTO t2x VALUES(1); 234} {1 {no such column: b}} 235 236#------------------------------------------------------------------------- 237 238do_execsql_test 6.0 { 239 CREATE TABLE t3(a,b,c,d); 240 CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN 241 SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a; 242 SELECT rowid, * FROM t3; 243 END; 244} {} 245 246do_execsql_test 6.1 { 247 ALTER TABLE t3 RENAME TO t3x; 248 SELECT sql FROM sqlite_master WHERE name = 'r3'; 249} { 250 {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN 251 SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a; 252 SELECT rowid, * FROM "t3x"; 253 END} 254} 255 256do_execsql_test 6.2 { 257 ALTER TABLE t3x RENAME a TO abcd; 258 SELECT sql FROM sqlite_master WHERE name = 'r3'; 259} { 260 {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN 261 SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd; 262 SELECT rowid, * FROM "t3x"; 263 END} 264} 265 266finish_test 267 268 269