gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/altertab2.test (about) 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 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix altertab2 16 17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18 ifcapable !altertable { 19 finish_test 20 return 21 } 22 23 ifcapable 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 # 51 do_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 58 do_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 67 do_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 77 do_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 # 91 foreach {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 #------------------------------------------------------------------------- 146 reset_db 147 do_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 154 do_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 163 do_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 172 do_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 #------------------------------------------------------------------------- 182 ifcapable windowfunc { 183 do_execsql_test 5.0 { 184 CREATE TABLE t2(a); 185 CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN 186 SELECT a, sum(a) OVER w1 FROM t2 187 WINDOW w1 AS ( 188 PARTITION BY a ORDER BY a 189 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING 190 ), 191 w2 AS ( 192 PARTITION BY a 193 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 194 ); 195 END; 196 } {} 197 198 do_execsql_test 5.0.1 { 199 INSERT INTO t2 VALUES(1); 200 } {} 201 202 do_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 BEGIN 207 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 FOLLOWING 211 ), 212 w2 AS ( 213 PARTITION BY a 214 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 215 ); 216 END} 217 } 218 219 do_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 BEGIN 224 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 FOLLOWING 228 ), 229 w2 AS ( 230 PARTITION BY aaaa 231 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 232 ); 233 END} 234 } 235 236 do_execsql_test 5.3 { 237 INSERT INTO t2x VALUES(1); 238 } {} 239 } ;# windowfunc 240 241 #------------------------------------------------------------------------- 242 243 do_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 BEGIN 246 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 251 do_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 BEGIN 256 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 261 do_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 BEGIN 266 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 #------------------------------------------------------------------------- 272 reset_db 273 274 do_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 280 do_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 284 do_execsql_test 7.2 { 285 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 286 INSERT INTO t2 287 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 293 do_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" BEGIN 298 INSERT INTO t2 299 SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c; 300 END} 301 } 302 303 do_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" BEGIN 308 INSERT INTO t2 309 SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc; 310 END} 311 } 312 313 #------------------------------------------------------------------------- 314 reset_db 315 do_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 BEGIN 321 UPDATE t2 SET a = new.d; 322 SELECT a, b, c FROM v1; 323 END; 324 } 325 326 do_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 be 332 # resolved. But at one point there was a bug allowing the ALTER TABLE 333 # succeed. Which meant the subsequent INSERT statement would fail. 334 do_catchsql_test 8.2 { 335 ALTER TABLE t1 RENAME a TO aaa; 336 } {1 {error in trigger tr after rename: no such column: a}} 337 do_execsql_test 8.3 { 338 INSERT INTO t3 VALUES(4, 5, 6); 339 } 340 341 do_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 # 349 do_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://www.sqlite.org/src/info/533010b8cacebe82 355 reset_db 356 do_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 {error in index i0: second argument to likelihood() must be a constant between 0.0 and 1.0}} 362 363 finish_test