xref: /sqlite-3.40.0/test/altertab2.test (revision dfb5c963) 
1# 2018 September 30
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#*************************************************************************
11#
12
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix altertab2
16
17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18ifcapable !altertable {
19  finish_test
20  return
21}
22
23ifcapable fts5 {
24  do_execsql_test 1.0 {
25    CREATE TABLE rr(a, b);
26    CREATE VIRTUAL TABLE ff USING fts5(a, b);
27    CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
28      INSERT INTO ff VALUES(new.a, new.b);
29    END;
30    INSERT INTO rr VALUES('hello', 'world');
31    SELECT * FROM ff;
32  } {hello world}
33
34  do_execsql_test 1.1 {
35    ALTER TABLE ff RENAME TO ffff;
36  }
37
38  do_execsql_test 1.2 {
39    INSERT INTO rr VALUES('in', 'tcl');
40    SELECT * FROM ffff;
41  } {hello world in tcl}
42}
43
44#-------------------------------------------------------------------------
45# Check that table names that appear in REFERENCES clauses are updated
46# when a table is renamed unless:
47#
48#   a) "PRAGMA legacy_alter_table" is true, and
49#   b) "PRAGMA foreign_keys" is false.
50#
51do_execsql_test 2.0 {
52  CREATE TABLE p1(a PRIMARY KEY, b);
53  CREATE TABLE c1(x REFERENCES p1);
54  CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1);
55  CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a));
56}
57
58do_execsql_test 2.1 {
59  ALTER TABLE p1 RENAME TO p2;
60  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
61} {
62  {CREATE TABLE c1(x REFERENCES "p2")}
63  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
64  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
65}
66
67do_execsql_test 2.2 {
68  PRAGMA legacy_alter_table = 1;
69  ALTER TABLE p2 RENAME TO p3;
70  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
71} {
72  {CREATE TABLE c1(x REFERENCES "p2")}
73  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
74  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
75}
76
77do_execsql_test 2.3 {
78  ALTER TABLE p3 RENAME TO p2;
79  PRAGMA foreign_keys = 1;
80  ALTER TABLE p2 RENAME TO p3;
81  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
82} {
83  {CREATE TABLE c1(x REFERENCES "p3")}
84  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")}
85  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))}
86}
87
88#-------------------------------------------------------------------------
89# Table name in WITH clauses that are part of views or triggers.
90#
91foreach {tn schema} {
92  1 {
93    CREATE TABLE log_entry(col1, y);
94    CREATE INDEX i1 ON log_entry(col1);
95  }
96
97  2 {
98    CREATE TABLE t1(a, b, c);
99    CREATE TABLE t2(x);
100    CREATE TABLE log_entry(col1);
101    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
102      INSERT INTO t2 SELECT col1 FROM log_entry;
103    END;
104  }
105
106  3 {
107    CREATE TABLE t1(a, b, c);
108    CREATE TABLE t2(x);
109    CREATE TABLE log_entry(col1);
110    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
111      INSERT INTO t2
112        WITH xyz(x) AS (SELECT col1 FROM log_entry)
113        SELECT x FROM xyz;
114    END;
115  }
116
117  4 {
118    CREATE TABLE log_entry(col1);
119    CREATE VIEW ttt AS
120        WITH xyz(x) AS (SELECT col1 FROM log_entry)
121        SELECT x FROM xyz;
122  }
123} {
124  reset_db
125  do_execsql_test 3.$tn.1 $schema
126  set expect [db eval "SELECT sql FROM sqlite_master"]
127  set expect [string map {log_entry {"newname"}} $expect]
128
129  do_execsql_test 3.$tn.2 {
130    ALTER TABLE log_entry RENAME TO newname;
131    SELECT sql FROM sqlite_master;
132  } $expect
133
134  reset_db
135  do_execsql_test 3.$tn.3 $schema
136  set expect [db eval "SELECT sql FROM sqlite_master"]
137  set expect [string map {col1 newname} $expect]
138
139  do_execsql_test 3.$tn.4 {
140    ALTER TABLE log_entry RENAME col1 TO newname;
141    SELECT sql FROM sqlite_master;
142  } $expect
143}
144
145#-------------------------------------------------------------------------
146reset_db
147do_execsql_test 4.0 {
148  CREATE TABLE t1(a,b,c,d,e,f);
149  CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
150    UPDATE t1 SET (c,d)=(a,b);
151  END;
152}
153
154do_execsql_test 4.1 {
155  ALTER TABLE t1 RENAME TO t1x;
156  SELECT sql FROM sqlite_master WHERE type = 'trigger';
157} {
158{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN
159    UPDATE "t1x" SET (c,d)=(a,b);
160  END}
161}
162
163do_execsql_test 4.2 {
164  ALTER TABLE t1x RENAME a TO aaa;
165  SELECT sql FROM sqlite_master WHERE type = 'trigger';
166} {
167{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
168    UPDATE "t1x" SET (c,d)=(aaa,b);
169  END}
170}
171
172do_execsql_test 4.3 {
173  ALTER TABLE t1x RENAME d TO ddd;
174  SELECT sql FROM sqlite_master WHERE type = 'trigger';
175} {
176{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
177    UPDATE "t1x" SET (c,ddd)=(aaa,b);
178  END}
179}
180
181finish_test
182