0
0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-16 13:48:52 +00:00
libsql/libsql-sqlite3/test/alterlegacy.test
2023-10-16 13:58:16 +02:00

470 lines
11 KiB
Plaintext

# 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