# 2018 September 30
#
# 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 altertab2
# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
finish_test
return
}
ifcapable fts5 {
do_execsql_test 1.0 {
CREATE TABLE rr(a, b);
CREATE VIRTUAL TABLE ff USING fts5(a, b);
CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
INSERT INTO ff VALUES(new.a, new.b);
END;
INSERT INTO rr VALUES('hello', 'world');
SELECT * FROM ff;
} {hello world}
do_execsql_test 1.1 {
ALTER TABLE ff RENAME TO ffff;
}
do_execsql_test 1.2 {
INSERT INTO rr VALUES('in', 'tcl');
SELECT * FROM ffff;
} {hello world in tcl}
}
#-------------------------------------------------------------------------
# Check that table names that appear in REFERENCES clauses are updated
# when a table is renamed unless:
#
# a) "PRAGMA legacy_alter_table" is true, and
# b) "PRAGMA foreign_keys" is false.
#
do_execsql_test 2.0 {
CREATE TABLE p1(a PRIMARY KEY, b);
CREATE TABLE c1(x REFERENCES p1);
CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1);
CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a));
}
do_execsql_test 2.1 {
ALTER TABLE p1 RENAME TO p2;
SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
} {
{CREATE TABLE c1(x REFERENCES "p2")}
{CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
{CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
}
do_execsql_test 2.2 {
PRAGMA legacy_alter_table = 1;
ALTER TABLE p2 RENAME TO p3;
SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
} {
{CREATE TABLE c1(x REFERENCES "p2")}
{CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
{CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
}
do_execsql_test 2.3 {
ALTER TABLE p3 RENAME TO p2;
PRAGMA foreign_keys = 1;
ALTER TABLE p2 RENAME TO p3;
SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
} {
{CREATE TABLE c1(x REFERENCES "p3")}
{CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")}
{CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))}
}
#-------------------------------------------------------------------------
# Table name in WITH clauses that are part of views or triggers.
#
foreach {tn schema} {
1 {
CREATE TABLE log_entry(col1, y);
CREATE INDEX i1 ON log_entry(col1);
}
2 {
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(x);
CREATE TABLE log_entry(col1);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
INSERT INTO t2 SELECT col1 FROM log_entry;
END;
}
3 {
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(x);
CREATE TABLE log_entry(col1);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
INSERT INTO t2
WITH xyz(x) AS (SELECT col1 FROM log_entry)
SELECT x FROM xyz;
END;
}
4 {
CREATE TABLE log_entry(col1);
CREATE VIEW ttt AS
WITH xyz(x) AS (SELECT col1 FROM log_entry)
SELECT x FROM xyz;
}
} {
reset_db
do_execsql_test 3.$tn.1 $schema
set expect [db eval "SELECT sql FROM sqlite_master"]
set expect [string map {log_entry {"newname"}} $expect]
do_execsql_test 3.$tn.2 {
ALTER TABLE log_entry RENAME TO newname;
SELECT sql FROM sqlite_master;
} $expect
reset_db
do_execsql_test 3.$tn.3 $schema
set expect [db eval "SELECT sql FROM sqlite_master"]
set expect [string map {col1 newname} $expect]
do_execsql_test 3.$tn.4 {
ALTER TABLE log_entry RENAME col1 TO newname;
SELECT sql FROM sqlite_master;
} $expect
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
CREATE TABLE t1(a,b,c,d,e,f);
CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
UPDATE t1 SET (c,d)=(a,b);
END;
}
do_execsql_test 4.1 {
ALTER TABLE t1 RENAME TO t1x;
SELECT sql FROM sqlite_master WHERE type = 'trigger';
} {
{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN
UPDATE "t1x" SET (c,d)=(a,b);
END}
}
do_execsql_test 4.2 {
ALTER TABLE t1x RENAME a TO aaa;
SELECT sql FROM sqlite_master WHERE type = 'trigger';
} {
{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
UPDATE "t1x" SET (c,d)=(aaa,b);
END}
}
do_execsql_test 4.3 {
ALTER TABLE t1x RENAME d TO ddd;
SELECT sql FROM sqlite_master WHERE type = 'trigger';
} {
{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
UPDATE "t1x" SET (c,ddd)=(aaa,b);
END}
}
#-------------------------------------------------------------------------
do_execsql_test 5.0 {
CREATE TABLE t2(a);
CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN
SELECT a, sum(a) OVER w1 FROM t2
WINDOW w1 AS (
PARTITION BY a ORDER BY a
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
),
w2 AS (
PARTITION BY a
ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
END;
} {}
do_execsql_test 5.0.1 {
INSERT INTO t2 VALUES(1);
} {}
do_execsql_test 5.1 {
ALTER TABLE t2 RENAME TO t2x;
SELECT sql FROM sqlite_master WHERE name = 'r2';
} {
{CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN
SELECT a, sum(a) OVER w1 FROM "t2x"
WINDOW w1 AS (
PARTITION BY a ORDER BY a
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
),
w2 AS (
PARTITION BY a
ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
END}
}
do_execsql_test 5.2 {
ALTER TABLE t2x RENAME a TO aaaa;
SELECT sql FROM sqlite_master WHERE name = 'r2';
} {
{CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN
SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x"
WINDOW w1 AS (
PARTITION BY aaaa ORDER BY aaaa
ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
),
w2 AS (
PARTITION BY aaaa
ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
END}
}
do_execsql_test 5.3 {
INSERT INTO t2x VALUES(1);
} {}
#-------------------------------------------------------------------------
do_execsql_test 6.0 {
CREATE TABLE t3(a,b,c,d);
CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN
SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;
SELECT rowid, * FROM t3;
END;
} {}
do_execsql_test 6.1 {
ALTER TABLE t3 RENAME TO t3x;
SELECT sql FROM sqlite_master WHERE name = 'r3';
} {
{CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN
SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a;
SELECT rowid, * FROM "t3x";
END}
}
do_execsql_test 6.2 {
ALTER TABLE t3x RENAME a TO abcd;
SELECT sql FROM sqlite_master WHERE name = 'r3';
} {
{CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN
SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd;
SELECT rowid, * FROM "t3x";
END}
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.0 {
CREATE TABLE t1(a,b,c,d,e,f);
INSERT INTO t1 VALUES(1,2,3,4,5,6);
CREATE TABLE t2(x,y,z);
}
do_execsql_test 7.1 {
SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
} {1 2 3 4 5 6}
do_execsql_test 7.2 {
CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
INSERT INTO t2
SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
END;
INSERT INTO t1 VALUES(2,3,4,5,6,7);
SELECT * FROM t2;
} {1 2 3 2 3 4 4 5 6 5 6 7}
do_execsql_test 7.3 {
ALTER TABLE t1 RENAME TO xyzzy;
SELECT sql FROM sqlite_master WHERE name='r1'
} {
{CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
INSERT INTO t2
SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c;
END}
}
do_execsql_test 7.3 {
ALTER TABLE xyzzy RENAME c TO ccc;
SELECT sql FROM sqlite_master WHERE name='r1'
} {
{CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
INSERT INTO t2
SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;
END}
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 8.0 {
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(a, b, c);
CREATE TABLE t3(d, e, f);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
UPDATE t2 SET a = new.d;
SELECT a, b, c FROM v1;
END;
}
do_execsql_test 8.1 {
INSERT INTO t3 VALUES(1, 2, 3);
}
# The following ALTER TABLE fails as if column "t1.a" is renamed the "a"
# in the "SELECT a, b, c FROM v1" within the trigger can no longer be
# resolved. But at one point there was a bug allowing the ALTER TABLE
# succeed. Which meant the subsequent INSERT statement would fail.
do_catchsql_test 8.2 {
ALTER TABLE t1 RENAME a TO aaa;
} {1 {error in trigger tr after rename: no such column: a}}
do_execsql_test 8.3 {
INSERT INTO t3 VALUES(4, 5, 6);
}
do_execsql_test 8.1 {
CREATE TABLE t4(a, b);
CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2;
}
do_execsql_test 8.2 {
ALTER TABLE t4 RENAME a TO c;
SELECT sql FROM sqlite_master WHERE name = 'v4'
} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}}
finish_test