diff options
Diffstat (limited to 'test/alterlegacy.test')
| -rw-r--r-- | test/alterlegacy.test | 469 |
1 files changed, 469 insertions, 0 deletions
diff --git a/test/alterlegacy.test b/test/alterlegacy.test new file mode 100644 index 0000000..dfa1716 --- /dev/null +++ b/test/alterlegacy.test @@ -0,0 +1,469 @@ +# 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 {error in table t1new after rename: 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 {error in index t2expr after rename: 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 |