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