# 2018 September 20 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix alterlegacy # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. ifcapable !altertable { finish_test return } do_execsql_test 1.0 { PRAGMA legacy_alter_table = 1; CREATE TABLE t1(a, b, CHECK(t1.a != t1.b)); CREATE TABLE t2(a, b); CREATE INDEX t2expr ON t2(a) WHERE t2.b>0; } do_execsql_test 1.1 { SELECT sql FROM sqlite_master } { {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))} {CREATE TABLE t2(a, b)} {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0} } # Legacy behavior is to corrupt the schema in this case, as the table name in # the CHECK constraint is incorrect after "t1" is renamed. This version is # slightly different - it rejects the change and rolls back the transaction. do_catchsql_test 1.2 { ALTER TABLE t1 RENAME TO t1new; } {1 {no such column: t1.a}} do_execsql_test 1.3 { CREATE TABLE t3(c, d); ALTER TABLE t3 RENAME TO t3new; DROP TABLE t3new; } do_execsql_test 1.4 { SELECT sql FROM sqlite_master } { {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))} {CREATE TABLE t2(a, b)} {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0} } do_catchsql_test 1.3 { ALTER TABLE t2 RENAME TO t2new; } {1 {no such column: t2.b}} do_execsql_test 1.4 { SELECT sql FROM sqlite_master } { {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))} {CREATE TABLE t2(a, b)} {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0} } #------------------------------------------------------------------------- reset_db ifcapable vtab { register_echo_module db do_execsql_test 2.0 { PRAGMA legacy_alter_table = 1; CREATE TABLE abc(a, b, c); INSERT INTO abc VALUES(1, 2, 3); CREATE VIRTUAL TABLE eee USING echo('abc'); SELECT * FROM eee; } {1 2 3} do_execsql_test 2.1 { ALTER TABLE eee RENAME TO fff; SELECT * FROM fff; } {1 2 3} db close sqlite3 db test.db do_catchsql_test 2.2 { ALTER TABLE fff RENAME TO ggg; } {1 {no such module: echo}} } #------------------------------------------------------------------------- reset_db do_execsql_test 3.0 { PRAGMA legacy_alter_table = 1; CREATE TABLE txx(a, b, c); INSERT INTO txx VALUES(1, 2, 3); CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx; CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one; CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx; } do_execsql_test 3.1.1 { SELECT * FROM vvv; } {1 2 3} do_execsql_test 3.1.2a { ALTER TABLE txx RENAME TO "t xx"; } do_catchsql_test 3.1.2b { SELECT * FROM vvv; } {1 {no such table: main.txx}} do_execsql_test 3.1.3 { SELECT sql FROM sqlite_master WHERE name='vvv'; } {{CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx}} do_catchsql_test 3.2.1 { SELECT * FROM uuu; } {1 {no such table: main.txx}} do_execsql_test 3.2.2 { SELECT sql FROM sqlite_master WHERE name='uuu';; } {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one}} do_catchsql_test 3.3.1 { SELECT * FROM ttt; } {1 {no such table: txx}} do_execsql_test 3.3.2 { SELECT sql FROM sqlite_temp_master WHERE name='ttt'; } {{CREATE VIEW ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx}} #------------------------------------------------------------------------- reset_db do_execsql_test 4.0 { PRAGMA legacy_alter_table = 1; CREATE table t1(x, y); CREATE table t2(a, b); CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT t1.x, * FROM t1, t2; INSERT INTO t2 VALUES(new.x, new.y); END; } do_execsql_test 4.1 { INSERT INTO t1 VALUES(1, 1); ALTER TABLE t1 RENAME TO t11; } do_catchsql_test 4.1a { INSERT INTO t11 VALUES(2, 2); } {1 {no such table: main.t1}} do_execsql_test 4.1b { ALTER TABLE t11 RENAME TO t1; ALTER TABLE t2 RENAME TO t22; } do_catchsql_test 4.1c { INSERT INTO t1 VALUES(3, 3); } {1 {no such table: main.t2}} proc squish {a} { string trim [regsub -all {[[:space:]][[:space:]]*} $a { }] } db func squish squish do_test 4.2 { execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' } } [list [squish { CREATE TRIGGER tr1 AFTER INSERT ON "t1" BEGIN SELECT t1.x, * FROM t1, t2; INSERT INTO t2 VALUES(new.x, new.y); END }]] #------------------------------------------------------------------------- reset_db do_execsql_test 5.0 { PRAGMA legacy_alter_table = 1; CREATE TABLE t9(a, b, c); CREATE TABLE t10(a, b, c); CREATE TEMP TABLE t9(a, b, c); CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN INSERT INTO t10 VALUES(new.a, new.b, new.c); END; INSERT INTO temp.t9 VALUES(1, 2, 3); SELECT * FROM t10; } {1 2 3} do_execsql_test 5.1 { ALTER TABLE temp.t9 RENAME TO 't1234567890' } do_execsql_test 5.2 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b); INSERT INTO t1 VALUES(1, 2); INSERT INTO t2 VALUES(3, 4); CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; SELECT * FROM v; } {1 2 3 4} do_execsql_test 5.3 { ALTER TABLE t2 RENAME TO one; } {} do_catchsql_test 5.4 { SELECT * FROM v } {1 {no such table: main.t2}} do_execsql_test 5.5 { ALTER TABLE one RENAME TO t2; DROP VIEW v; CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; SELECT * FROM vv; } {1 2 3 4} do_execsql_test 5.6 { ALTER TABLE t2 RENAME TO one; } {} do_catchsql_test 5.7 { SELECT * FROM vv } {1 {no such table: t2}} #------------------------------------------------------------------------- ifcapable vtab { register_tcl_module db proc tcl_command {method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b, c)" } } return {} } do_execsql_test 6.0 { CREATE VIRTUAL TABLE x1 USING tcl(tcl_command); } do_execsql_test 6.1 { ALTER TABLE x1 RENAME TO x2; SELECT sql FROM sqlite_master WHERE name = 'x2' } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}} do_execsql_test 7.1 { CREATE TABLE ddd(db, sql, zOld, zNew, bTemp); INSERT INTO ddd VALUES( 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0 ), ( 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0 ), ( 'main', NULL, 'ddd', 'eee', 0 ); } {} } #------------------------------------------------------------------------- # reset_db forcedelete test.db2 do_execsql_test 8.1 { PRAGMA legacy_alter_table = 1; ATTACH 'test.db2' AS aux; PRAGMA foreign_keys = on; CREATE TABLE aux.p1(a INTEGER PRIMARY KEY, b); CREATE TABLE aux.c1(x INTEGER PRIMARY KEY, y REFERENCES p1(a)); INSERT INTO aux.p1 VALUES(1, 1); INSERT INTO aux.p1 VALUES(2, 2); INSERT INTO aux.c1 VALUES(NULL, 2); CREATE TABLE aux.c2(x INTEGER PRIMARY KEY, y REFERENCES c1(a)); } do_execsql_test 8.2 { ALTER TABLE aux.p1 RENAME TO ppp; } do_execsql_test 8.2 { INSERT INTO aux.c1 VALUES(NULL, 1); SELECT sql FROM aux.sqlite_master WHERE name = 'c1'; } {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}} reset_db do_execsql_test 9.0 { PRAGMA legacy_alter_table = 1; CREATE TABLE t1(a, b, c); CREATE VIEW v1 AS SELECT * FROM t2; } do_execsql_test 9.1 { ALTER TABLE t1 RENAME TO t3; } {} do_execsql_test 9.1b { ALTER TABLE t3 RENAME TO t1; } {} do_execsql_test 9.2 { DROP VIEW v1; CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.a); END; } do_execsql_test 9.3 { ALTER TABLE t1 RENAME TO t3; } {} forcedelete test.db2 do_execsql_test 9.4 { ALTER TABLE t3 RENAME TO t1; DROP TRIGGER tr; ATTACH 'test.db2' AS aux; CREATE TRIGGER tr AFTER INSERT ON t1 WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END; CREATE TABLE aux.t1(x); CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END; } do_execsql_test 9.5 { ALTER TABLE main.t1 RENAME TO t3; } do_execsql_test 9.6 { SELECT sql FROM sqlite_temp_master; SELECT sql FROM sqlite_master WHERE type='trigger'; } { {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END} {CREATE TRIGGER tr AFTER INSERT ON "t3" WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END} } #------------------------------------------------------------------------- reset_db ifcapable fts5 { do_execsql_test 10.0 { PRAGMA legacy_alter_table = 1; CREATE VIRTUAL TABLE fff USING fts5(x, y, z); } do_execsql_test 10.1 { BEGIN; INSERT INTO fff VALUES('a', 'b', 'c'); ALTER TABLE fff RENAME TO ggg; COMMIT; } do_execsql_test 10.2 { SELECT * FROM ggg; } {a b c} } #------------------------------------------------------------------------- reset_db forcedelete test.db2 db func trigger trigger set ::trigger [list] proc trigger {args} { lappend ::trigger $args } do_execsql_test 11.0 { PRAGMA legacy_alter_table = 1; ATTACH 'test.db2' AS aux; CREATE TABLE aux.t1(a, b, c); CREATE TABLE main.t1(a, b, c); CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT trigger(new.a, new.b, new.c); END; } do_execsql_test 11.1 { INSERT INTO main.t1 VALUES(1, 2, 3); INSERT INTO aux.t1 VALUES(4, 5, 6); } do_test 11.2 { set ::trigger } {{4 5 6}} do_execsql_test 11.3 { SELECT name, tbl_name FROM sqlite_temp_master; } {tr t1} do_execsql_test 11.4 { ALTER TABLE main.t1 RENAME TO t2; SELECT name, tbl_name FROM sqlite_temp_master; } {tr t1} do_execsql_test 11.5 { ALTER TABLE aux.t1 RENAME TO t2; SELECT name, tbl_name FROM sqlite_temp_master; } {tr t2} do_execsql_test 11.6 { INSERT INTO aux.t2 VALUES(7, 8, 9); } do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}} #------------------------------------------------------------------------- reset_db do_execsql_test 12.0 { PRAGMA legacy_alter_table = 1; CREATE TABLE t1(a); CREATE TABLE t2(w); CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN INSERT INTO t1(a) VALUES(new.w); END; CREATE TEMP TABLE t2(x); } do_execsql_test 12.1 { ALTER TABLE main.t2 RENAME TO t3; } do_execsql_test 12.2 { INSERT INTO t3 VALUES('WWW'); SELECT * FROM t1; } {WWW} #------------------------------------------------------------------------- reset_db ifcapable rtree { do_execsql_test 14.0 { PRAGMA legacy_alter_table = 1; CREATE VIRTUAL TABLE rt USING rtree(id, minx, maxx, miny, maxy); CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB); CREATE TRIGGER tr1 AFTER UPDATE OF "geom" ON "mytable" WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN DELETE FROM rt WHERE id = OLD."fid"; END; INSERT INTO mytable VALUES(1, X'abcd'); } do_execsql_test 14.1 { UPDATE mytable SET geom = X'1234' } do_execsql_test 14.2 { ALTER TABLE mytable RENAME TO mytable_renamed; } do_execsql_test 14.3 { CREATE TRIGGER tr2 AFTER INSERT ON mytable_renamed BEGIN DELETE FROM rt WHERE id=(SELECT min(id) FROM rt); END; } do_execsql_test 14.4 { ALTER TABLE mytable_renamed RENAME TO mytable2; } } reset_db do_execsql_test 14.5 { PRAGMA legacy_alter_table = 1; CREATE TABLE t1(a, b, c); CREATE VIEW v1 AS SELECT * FROM t1; CREATE TRIGGER xyz AFTER INSERT ON t1 BEGIN SELECT a, b FROM v1; END; } do_execsql_test 14.6 { ALTER TABLE t1 RENAME TO tt1; } finish_test