gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/alterlegacy.test (about) 1 # 2018 September 20 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 alterlegacy 16 17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18 ifcapable !altertable { 19 finish_test 20 return 21 } 22 23 do_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 30 do_execsql_test 1.1 { 31 SELECT sql FROM sqlite_master 32 } { 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 in 39 # the CHECK constraint is incorrect after "t1" is renamed. This version is 40 # slightly different - it rejects the change and rolls back the transaction. 41 do_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 45 do_execsql_test 1.3 { 46 CREATE TABLE t3(c, d); 47 ALTER TABLE t3 RENAME TO t3new; 48 DROP TABLE t3new; 49 } 50 51 do_execsql_test 1.4 { 52 SELECT sql FROM sqlite_master 53 } { 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 60 do_catchsql_test 1.3 { 61 ALTER TABLE t2 RENAME TO t2new; 62 } {1 {error in index t2expr after rename: no such column: t2.b}} 63 do_execsql_test 1.4 { 64 SELECT sql FROM sqlite_master 65 } { 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 #------------------------------------------------------------------------- 73 reset_db 74 ifcapable vtab { 75 register_echo_module db 76 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 close 91 sqlite3 db test.db 92 93 do_catchsql_test 2.2 { 94 ALTER TABLE fff RENAME TO ggg; 95 } {1 {no such module: echo}} 96 } 97 98 #------------------------------------------------------------------------- 99 reset_db 100 101 do_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 110 do_execsql_test 3.1.1 { 111 SELECT * FROM vvv; 112 } {1 2 3} 113 do_execsql_test 3.1.2a { 114 ALTER TABLE txx RENAME TO "t xx"; 115 } 116 do_catchsql_test 3.1.2b { 117 SELECT * FROM vvv; 118 } {1 {no such table: main.txx}} 119 do_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 124 do_catchsql_test 3.2.1 { 125 SELECT * FROM uuu; 126 } {1 {no such table: main.txx}} 127 do_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 131 do_catchsql_test 3.3.1 { 132 SELECT * FROM ttt; 133 } {1 {no such table: txx}} 134 do_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 #------------------------------------------------------------------------- 139 reset_db 140 do_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 BEGIN 146 SELECT t1.x, * FROM t1, t2; 147 INSERT INTO t2 VALUES(new.x, new.y); 148 END; 149 } 150 151 do_execsql_test 4.1 { 152 INSERT INTO t1 VALUES(1, 1); 153 ALTER TABLE t1 RENAME TO t11; 154 } 155 do_catchsql_test 4.1a { 156 INSERT INTO t11 VALUES(2, 2); 157 } {1 {no such table: main.t1}} 158 do_execsql_test 4.1b { 159 ALTER TABLE t11 RENAME TO t1; 160 ALTER TABLE t2 RENAME TO t22; 161 } 162 do_catchsql_test 4.1c { 163 INSERT INTO t1 VALUES(3, 3); 164 } {1 {no such table: main.t2}} 165 166 proc squish {a} { 167 string trim [regsub -all {[[:space:]][[:space:]]*} $a { }] 168 } 169 db func squish squish 170 do_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" BEGIN 174 SELECT t1.x, * FROM t1, t2; 175 INSERT INTO t2 VALUES(new.x, new.y); 176 END 177 }]] 178 179 #------------------------------------------------------------------------- 180 reset_db 181 do_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 BEGIN 188 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 195 do_execsql_test 5.1 { 196 ALTER TABLE temp.t9 RENAME TO 't1234567890' 197 } 198 199 do_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 208 do_execsql_test 5.3 { 209 ALTER TABLE t2 RENAME TO one; 210 } {} 211 212 do_catchsql_test 5.4 { 213 SELECT * FROM v 214 } {1 {no such table: main.t2}} 215 216 do_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 223 do_execsql_test 5.6 { 224 ALTER TABLE t2 RENAME TO one; 225 } {} 226 do_catchsql_test 5.7 { 227 SELECT * FROM vv 228 } {1 {no such table: t2}} 229 230 #------------------------------------------------------------------------- 231 232 ifcapable vtab { 233 register_tcl_module db 234 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, 0 256 ), ( 257 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0 258 ), ( 259 'main', NULL, 'ddd', 'eee', 0 260 ); 261 } {} 262 } 263 264 #------------------------------------------------------------------------- 265 # 266 reset_db 267 forcedelete test.db2 268 do_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 280 do_execsql_test 8.2 { 281 ALTER TABLE aux.p1 RENAME TO ppp; 282 } 283 284 do_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 289 reset_db 290 do_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 } 295 do_execsql_test 9.1 { 296 ALTER TABLE t1 RENAME TO t3; 297 } {} 298 do_execsql_test 9.1b { 299 ALTER TABLE t3 RENAME TO t1; 300 } {} 301 do_execsql_test 9.2 { 302 DROP VIEW v1; 303 CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN 304 INSERT INTO t2 VALUES(new.a); 305 END; 306 } 307 do_execsql_test 9.3 { 308 ALTER TABLE t1 RENAME TO t3; 309 } {} 310 311 forcedelete test.db2 312 do_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 } 322 do_execsql_test 9.5 { 323 ALTER TABLE main.t1 RENAME TO t3; 324 } 325 do_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 #------------------------------------------------------------------------- 334 reset_db 335 ifcapable 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 #------------------------------------------------------------------------- 354 reset_db 355 forcedelete test.db2 356 db func trigger trigger 357 set ::trigger [list] 358 proc trigger {args} { 359 lappend ::trigger $args 360 } 361 do_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 BEGIN 367 SELECT trigger(new.a, new.b, new.c); 368 END; 369 } 370 371 do_execsql_test 11.1 { 372 INSERT INTO main.t1 VALUES(1, 2, 3); 373 INSERT INTO aux.t1 VALUES(4, 5, 6); 374 } 375 do_test 11.2 { set ::trigger } {{4 5 6}} 376 377 do_execsql_test 11.3 { 378 SELECT name, tbl_name FROM sqlite_temp_master; 379 } {tr t1} 380 381 do_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 386 do_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 391 do_execsql_test 11.6 { 392 INSERT INTO aux.t2 VALUES(7, 8, 9); 393 } 394 do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}} 395 396 #------------------------------------------------------------------------- 397 reset_db 398 do_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 BEGIN 403 INSERT INTO t1(a) VALUES(new.w); 404 END; 405 CREATE TEMP TABLE t2(x); 406 } 407 408 do_execsql_test 12.1 { 409 ALTER TABLE main.t2 RENAME TO t3; 410 } 411 412 do_execsql_test 12.2 { 413 INSERT INTO t3 VALUES('WWW'); 414 SELECT * FROM t1; 415 } {WWW} 416 417 418 #------------------------------------------------------------------------- 419 reset_db 420 421 ifcapable 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 BEGIN 446 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 455 reset_db 456 do_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 BEGIN 461 SELECT a, b FROM v1; 462 END; 463 } 464 do_execsql_test 14.6 { 465 ALTER TABLE t1 RENAME TO tt1; 466 } 467 468 469 finish_test