# 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
}
finish_test