1# 2018 August 24 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 altertab 16 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18ifcapable !altertable { 19 finish_test 20 return 21} 22 23do_execsql_test 1.0 { 24 CREATE TABLE t1(a, b, CHECK(t1.a != t1.b)); 25 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_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 38do_execsql_test 1.2 { 39 ALTER TABLE t1 RENAME TO t1new; 40} 41 42do_execsql_test 1.3 { 43 CREATE TABLE t3(c, d); 44 ALTER TABLE t3 RENAME TO t3new; 45 DROP TABLE t3new; 46} 47 48do_execsql_test 1.4 { 49 SELECT sql FROM sqlite_master 50} { 51 {CREATE TABLE "t1new"(a, b, CHECK("t1new".a != "t1new".b))} 52 {CREATE TABLE t2(a, b)} 53 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0} 54} 55 56 57do_execsql_test 1.3 { 58 ALTER TABLE t2 RENAME TO t2new; 59} 60do_execsql_test 1.4 { 61 SELECT sql FROM sqlite_master 62} { 63 {CREATE TABLE "t1new"(a, b, CHECK("t1new".a != "t1new".b))} 64 {CREATE TABLE "t2new"(a, b)} 65 {CREATE INDEX t2expr ON "t2new"(a) WHERE "t2new".b>0} 66} 67 68 69#------------------------------------------------------------------------- 70reset_db 71ifcapable vtab { 72 register_echo_module db 73 74 do_execsql_test 2.0 { 75 CREATE TABLE abc(a, b, c); 76 INSERT INTO abc VALUES(1, 2, 3); 77 CREATE VIRTUAL TABLE eee USING echo('abc'); 78 SELECT * FROM eee; 79 } {1 2 3} 80 81 do_execsql_test 2.1 { 82 ALTER TABLE eee RENAME TO fff; 83 SELECT * FROM fff; 84 } {1 2 3} 85 86 db close 87 sqlite3 db test.db 88 89 do_catchsql_test 2.2 { 90 ALTER TABLE fff RENAME TO ggg; 91 } {1 {no such module: echo}} 92} 93 94#------------------------------------------------------------------------- 95reset_db 96 97do_execsql_test 3.0 { 98 CREATE TABLE txx(a, b, c); 99 INSERT INTO txx VALUES(1, 2, 3); 100 CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx; 101 CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one; 102 CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx; 103} 104 105do_execsql_test 3.1.1 { 106 SELECT * FROM vvv; 107} {1 2 3} 108do_execsql_test 3.1.2 { 109 ALTER TABLE txx RENAME TO "t xx"; 110 SELECT * FROM vvv; 111} {1 2 3} 112do_execsql_test 3.1.3 { 113 SELECT sql FROM sqlite_master WHERE name='vvv'; 114} {{CREATE VIEW vvv AS SELECT main."t xx".a, "t xx".b, c FROM "t xx"}} 115 116 117do_execsql_test 3.2.1 { 118 SELECT * FROM uuu; 119} {1 2 3} 120do_execsql_test 3.2.2 { 121 SELECT sql FROM sqlite_master WHERE name='uuu';; 122} {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM "t xx" AS one}} 123 124do_execsql_test 3.3.1 { 125 SELECT * FROM ttt; 126} {1 2 2 1} 127do_execsql_test 3.3.2 { 128 SELECT sql FROM sqlite_temp_master WHERE name='ttt'; 129} {{CREATE VIEW ttt AS SELECT main."t xx".a, "t xx".b, one.b, main.one.a FROM "t xx" AS one, "t xx"}} 130 131#------------------------------------------------------------------------- 132reset_db 133do_execsql_test 4.0 { 134 CREATE table t1(x, y); 135 CREATE table t2(a, b); 136 137 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 138 SELECT t1.x, * FROM t1, t2; 139 INSERT INTO t2 VALUES(new.x, new.y); 140 END; 141} 142 143do_execsql_test 4.1 { 144 INSERT INTO t1 VALUES(1, 1); 145 ALTER TABLE t1 RENAME TO t11; 146 INSERT INTO t11 VALUES(2, 2); 147 ALTER TABLE t2 RENAME TO t22; 148 INSERT INTO t11 VALUES(3, 3); 149} 150 151proc squish {a} { 152 string trim [regsub -all {[[:space:]][[:space:]]*} $a { }] 153} 154db func squish squish 155do_test 4.2 { 156 execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' } 157} [list [squish { 158 CREATE TRIGGER tr1 AFTER INSERT ON "t11" BEGIN 159 SELECT "t11".x, * FROM "t11", "t22"; 160 INSERT INTO "t22" VALUES(new.x, new.y); 161 END 162}]] 163 164#------------------------------------------------------------------------- 165reset_db 166do_execsql_test 5.0 { 167 CREATE TABLE t9(a, b, c); 168 CREATE TABLE t10(a, b, c); 169 CREATE TEMP TABLE t9(a, b, c); 170 171 CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN 172 INSERT INTO t10 VALUES(new.a, new.b, new.c); 173 END; 174 175 INSERT INTO temp.t9 VALUES(1, 2, 3); 176 SELECT * FROM t10; 177} {1 2 3} 178 179do_execsql_test 5.1 { 180 ALTER TABLE temp.t9 RENAME TO 't1234567890' 181} 182 183do_execsql_test 5.2 { 184 CREATE TABLE t1(a, b); 185 CREATE TABLE t2(a, b); 186 INSERT INTO t1 VALUES(1, 2); 187 INSERT INTO t2 VALUES(3, 4); 188 CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; 189 SELECT * FROM v; 190} {1 2 3 4} 191 192do_catchsql_test 5.3 { 193 ALTER TABLE t2 RENAME TO one; 194} {1 {error in view v after rename: ambiguous column name: one.a}} 195 196do_execsql_test 5.4 { 197 SELECT * FROM v 198} {1 2 3 4} 199 200do_execsql_test 5.5 { 201 DROP VIEW v; 202 CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; 203 SELECT * FROM vv; 204} {1 2 3 4} 205 206do_catchsql_test 5.6 { 207 ALTER TABLE t2 RENAME TO one; 208} {1 {error in view vv after rename: ambiguous column name: one.a}} 209 210#------------------------------------------------------------------------- 211 212ifcapable vtab { 213 register_tcl_module db 214 proc tcl_command {method args} { 215 switch -- $method { 216 xConnect { 217 return "CREATE TABLE t1(a, b, c)" 218 } 219 } 220 return {} 221 } 222 223 do_execsql_test 6.0 { 224 CREATE VIRTUAL TABLE x1 USING tcl(tcl_command); 225 } 226 227 do_execsql_test 6.1 { 228 ALTER TABLE x1 RENAME TO x2; 229 SELECT sql FROM sqlite_master WHERE name = 'x2' 230 } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}} 231 232 do_execsql_test 7.1 { 233 CREATE TABLE ddd(db, sql, zOld, zNew, bTemp); 234 INSERT INTO ddd VALUES( 235 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0 236 ), ( 237 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0 238 ), ( 239 'main', NULL, 'ddd', 'eee', 0 240 ); 241 } {} 242 243 do_execsql_test 7.2 { 244 SELECT 245 sqlite_rename_table(db, 0, 0, sql, zOld, zNew, bTemp) 246 FROM ddd; 247 } {{} {} {}} 248} 249 250#------------------------------------------------------------------------- 251# 252reset_db 253forcedelete test.db2 254do_execsql_test 8.1 { 255 ATTACH 'test.db2' AS aux; 256 PRAGMA foreign_keys = on; 257 CREATE TABLE aux.p1(a INTEGER PRIMARY KEY, b); 258 CREATE TABLE aux.c1(x INTEGER PRIMARY KEY, y REFERENCES p1(a)); 259 INSERT INTO aux.p1 VALUES(1, 1); 260 INSERT INTO aux.p1 VALUES(2, 2); 261 INSERT INTO aux.c1 VALUES(NULL, 2); 262 CREATE TABLE aux.c2(x INTEGER PRIMARY KEY, y REFERENCES c1(a)); 263} 264 265do_execsql_test 8.2 { 266 ALTER TABLE aux.p1 RENAME TO ppp; 267} 268 269do_execsql_test 8.2 { 270 INSERT INTO aux.c1 VALUES(NULL, 1); 271 SELECT sql FROM aux.sqlite_master WHERE name = 'c1'; 272} {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}} 273 274reset_db 275do_execsql_test 9.0 { 276 CREATE TABLE t1(a, b, c); 277 CREATE VIEW v1 AS SELECT * FROM t2; 278} 279do_catchsql_test 9.1 { 280 ALTER TABLE t1 RENAME TO t3; 281} {1 {error in view v1: no such table: main.t2}} 282do_execsql_test 9.2 { 283 DROP VIEW v1; 284 CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN 285 INSERT INTO t2 VALUES(new.a); 286 END; 287} 288do_catchsql_test 9.3 { 289 ALTER TABLE t1 RENAME TO t3; 290} {1 {error in trigger tr: no such table: main.t2}} 291 292forcedelete test.db2 293do_execsql_test 9.4 { 294 DROP TRIGGER tr; 295 296 ATTACH 'test.db2' AS aux; 297 CREATE TRIGGER tr AFTER INSERT ON t1 WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END; 298 299 CREATE TABLE aux.t1(x); 300 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END; 301} 302do_execsql_test 9.5 { 303 ALTER TABLE main.t1 RENAME TO t3; 304} 305do_execsql_test 9.6 { 306 SELECT sql FROM sqlite_temp_master; 307 SELECT sql FROM sqlite_master WHERE type='trigger'; 308} { 309 {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END} 310 {CREATE TRIGGER tr AFTER INSERT ON "t3" WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END} 311} 312 313#------------------------------------------------------------------------- 314reset_db 315ifcapable fts5 { 316 do_execsql_test 10.0 { 317 CREATE VIRTUAL TABLE fff USING fts5(x, y, z); 318 } 319 320 do_execsql_test 10.1 { 321 BEGIN; 322 INSERT INTO fff VALUES('a', 'b', 'c'); 323 ALTER TABLE fff RENAME TO ggg; 324 COMMIT; 325 } 326 327 do_execsql_test 10.2 { 328 SELECT * FROM ggg; 329 } {a b c} 330} 331 332#------------------------------------------------------------------------- 333reset_db 334forcedelete test.db2 335db func trigger trigger 336set ::trigger [list] 337proc trigger {args} { 338 lappend ::trigger $args 339} 340do_execsql_test 11.0 { 341 ATTACH 'test.db2' AS aux; 342 CREATE TABLE aux.t1(a, b, c); 343 CREATE TABLE main.t1(a, b, c); 344 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN 345 SELECT trigger(new.a, new.b, new.c); 346 END; 347} 348 349do_execsql_test 11.1 { 350 INSERT INTO main.t1 VALUES(1, 2, 3); 351 INSERT INTO aux.t1 VALUES(4, 5, 6); 352} 353do_test 11.2 { set ::trigger } {{4 5 6}} 354 355do_execsql_test 11.3 { 356 SELECT name, tbl_name FROM sqlite_temp_master; 357} {tr t1} 358 359do_execsql_test 11.4 { 360 ALTER TABLE main.t1 RENAME TO t2; 361 SELECT name, tbl_name FROM sqlite_temp_master; 362} {tr t1} 363 364do_execsql_test 11.5 { 365 ALTER TABLE aux.t1 RENAME TO t2; 366 SELECT name, tbl_name FROM sqlite_temp_master; 367} {tr t2} 368 369do_execsql_test 11.6 { 370 INSERT INTO aux.t2 VALUES(7, 8, 9); 371} 372do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}} 373 374#------------------------------------------------------------------------- 375reset_db 376do_execsql_test 12.0 { 377 CREATE TABLE t1(a); 378 CREATE TABLE t2(w); 379 CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN 380 INSERT INTO t1(a) VALUES(new.w); 381 END; 382 CREATE TEMP TABLE t2(x); 383} 384 385do_execsql_test 12.1 { 386 ALTER TABLE main.t2 RENAME TO t3; 387} 388 389do_execsql_test 12.2 { 390 INSERT INTO t3 VALUES('WWW'); 391 SELECT * FROM t1; 392} {WWW} 393 394 395#------------------------------------------------------------------------- 396reset_db 397do_execsql_test 13.0 { 398 CREATE TABLE t1(x, y); 399 CREATE TABLE t2(a, b); 400 CREATE TABLE log(c); 401 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 402 INSERT INTO log SELECT y FROM t1, t2; 403 END; 404} 405 406do_execsql_test 13.1 { 407 INSERT INTO t1 VALUES(1, 2); 408} 409 410do_catchsql_test 13.2 { 411 ALTER TABLE t2 RENAME b TO y; 412} {1 {error in trigger tr1 after rename: ambiguous column name: y}} 413 414#------------------------------------------------------------------------- 415reset_db 416 417ifcapable rtree { 418 do_execsql_test 14.0 { 419 CREATE VIRTUAL TABLE rt USING rtree(id, minx, maxx, miny, maxy); 420 421 CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB); 422 423 CREATE TRIGGER tr1 AFTER UPDATE OF "geom" ON "mytable" 424 WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN 425 DELETE FROM rt WHERE id = OLD."fid"; 426 END; 427 428 INSERT INTO mytable VALUES(1, X'abcd'); 429 } 430 431 do_execsql_test 14.1 { 432 UPDATE mytable SET geom = X'1234' 433 } 434 435 do_execsql_test 14.2 { 436 ALTER TABLE mytable RENAME TO mytable_renamed; 437 } 438 439 do_execsql_test 14.3 { 440 CREATE TRIGGER tr2 AFTER INSERT ON mytable_renamed BEGIN 441 DELETE FROM rt WHERE id=(SELECT min(id) FROM rt); 442 END; 443 } 444 445 do_execsql_test 14.4 { 446 ALTER TABLE mytable_renamed RENAME TO mytable2; 447 } 448} 449 450reset_db 451do_execsql_test 14.5 { 452 CREATE TABLE t1(a, b, c); 453 CREATE VIEW v1 AS SELECT * FROM t1; 454 CREATE TRIGGER xyz AFTER INSERT ON t1 BEGIN 455 SELECT a, b FROM v1; 456 END; 457} 458do_execsql_test 14.6 { 459 ALTER TABLE t1 RENAME TO tt1; 460} 461 462#------------------------------------------------------------------------- 463reset_db 464do_execsql_test 15.0 { 465 CREATE TABLE t1(a integer NOT NULL PRIMARY KEY); 466 CREATE VIEW v1 AS SELECT a FROM t1; 467 CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN 468 UPDATE t1 SET a = NEW.a; 469 END; 470 CREATE TRIGGER tr2 INSTEAD OF INSERT ON v1 BEGIN 471 SELECT new.a; 472 END; 473 CREATE TABLE t2 (b); 474} 475 476do_execsql_test 15.1 { 477 INSERT INTO v1 VALUES(1); 478 ALTER TABLE t2 RENAME TO t3; 479} 480 481do_execsql_test 15.2 { 482 CREATE TABLE x(f1 integer NOT NULL); 483 CREATE VIEW y AS SELECT f1 AS f1 FROM x; 484 CREATE TRIGGER t INSTEAD OF UPDATE OF f1 ON y BEGIN 485 UPDATE x SET f1 = NEW.f1; 486 END; 487 CREATE TABLE z (f1 integer NOT NULL PRIMARY KEY); 488 ALTER TABLE z RENAME TO z2; 489} 490 491do_execsql_test 15.3 { 492 INSERT INTO x VALUES(1), (2), (3); 493 ALTER TABLE x RENAME f1 TO f2; 494 SELECT * FROM x; 495} {1 2 3} 496 497do_execsql_test 15.4 { 498 UPDATE y SET f1 = 'x' WHERE f1 = 1; 499 SELECT * FROM x; 500} {x x x} 501 502do_execsql_test 15.5 { 503 SELECT sql FROM sqlite_master WHERE name = 'y'; 504} {{CREATE VIEW y AS SELECT f2 AS f1 FROM x}} 505 506 507finish_test 508 509