blob: 934a636669d05a6380d91be1c6a6031f963d6cc2 [file] [log] [blame]
danb8bbe3e2022-05-26 19:10:11 +00001# 2022 May 27
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 altertrig
16
17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18ifcapable !altertable {
19 finish_test
20 return
21}
22
23proc collapse_whitespace {in} {
24 regsub -all {[ \t\n]+} [string trim $in] { }
25}
26
27proc do_whitespace_sql_test {tn sql res} {
28 set got [execsql $sql]
29 set wgot [list]
30 set wres [list]
31 foreach g $got { lappend wgot [collapse_whitespace $g] }
32 foreach r $res { lappend wres [collapse_whitespace $r] }
33
34 uplevel [list do_test $tn [list set {} $wgot] $wres]
35}
36
37do_execsql_test 1.0 {
38 CREATE TABLE t1(x);
39 CREATE TABLE t2(y);
40 CREATE TABLE t3(z);
41 CREATE TABLE t4(a);
42
43 CREATE TRIGGER r1 INSERT ON t1 BEGIN
44 UPDATE t1 SET d='xyz' FROM t2, t3;
45 END;
46}
47
48do_whitespace_sql_test 1.1 {
49 ALTER TABLE t3 RENAME TO t5;
50 SELECT sql FROM sqlite_schema WHERE type='trigger';
51} {{
52 CREATE TRIGGER r1 INSERT ON t1 BEGIN
53 UPDATE t1 SET d='xyz' FROM t2, "t5";
54 END
55}}
56
57do_execsql_test 1.2 {
58 DROP TRIGGER r1;
59 CREATE TRIGGER r1 INSERT ON t1 BEGIN
60 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t5);
61 END;
62}
63
64do_whitespace_sql_test 1.3 {
65 ALTER TABLE t5 RENAME TO t3;
66 SELECT sql FROM sqlite_schema WHERE type='trigger';
67} {{
68 CREATE TRIGGER r1 INSERT ON t1 BEGIN
69 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t3");
70 END
71}}
72
73foreach {tn alter update final} {
74 1 {
75 ALTER TABLE t3 RENAME TO t10
76 } {
77 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t3)
78 } {
79 UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t10")
80 }
81
82 2 {
83 ALTER TABLE t3 RENAME TO t10
84 } {
dan4209d552022-05-27 15:04:43 +000085 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
danb8bbe3e2022-05-26 19:10:11 +000086 } {
dan4209d552022-05-27 15:04:43 +000087 UPDATE t1 SET a='xyz' FROM "t10", (SELECT * FROM (SELECT e FROM "t10"))
danb8bbe3e2022-05-26 19:10:11 +000088 }
89
90 3 {
91 ALTER TABLE t3 RENAME e TO abc
92 } {
dan4209d552022-05-27 15:04:43 +000093 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
danb8bbe3e2022-05-26 19:10:11 +000094 } {
dan4209d552022-05-27 15:04:43 +000095 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT abc FROM t3))
danb8bbe3e2022-05-26 19:10:11 +000096 }
97
98 4 {
99 ALTER TABLE t2 RENAME c TO abc
100 } {
dan4209d552022-05-27 15:04:43 +0000101 UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE c)
danb8bbe3e2022-05-26 19:10:11 +0000102 } {
dan4209d552022-05-27 15:04:43 +0000103 UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE abc)
104 }
105
106 5 {
107 ALTER TABLE t2 RENAME c TO abc
108 } {
109 UPDATE t1 SET a=t2.c FROM t2
110 } {
111 UPDATE t1 SET a=t2.abc FROM t2
112 }
113
114 6 {
115 ALTER TABLE t2 RENAME c TO abc
116 } {
117 UPDATE t1 SET a=t2.c FROM t2, t3
118 } {
119 UPDATE t1 SET a=t2.abc FROM t2, t3
danb8bbe3e2022-05-26 19:10:11 +0000120 }
danca29bbc2022-05-27 15:33:51 +0000121
122 7 {
123 ALTER TABLE t4 RENAME e TO abc
124 } {
125 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a
126 } {
127 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.abc=a
128 }
129
130 8 {
131 ALTER TABLE t4 RENAME TO abc
132 } {
133 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a
134 } {
135 UPDATE t1 SET a=1 FROM t3 NATURAL JOIN "abc" WHERE "abc".e=a
136 }
danb8bbe3e2022-05-26 19:10:11 +0000137
138} {
139 reset_db
140 do_execsql_test 2.$tn.1 {
141 CREATE TABLE t1(a,b);
142 CREATE TABLE t2(c,d);
143 CREATE TABLE t3(e,f);
danca29bbc2022-05-27 15:33:51 +0000144 CREATE TABLE t4(e,f);
danb8bbe3e2022-05-26 19:10:11 +0000145 }
146 do_execsql_test 2.$tn.2 "
147 CREATE TRIGGER r1 INSERT ON t1 BEGIN
148 $update;
149 END
150 "
151 do_execsql_test 2.$tn.3 $alter
152
153 do_whitespace_sql_test 2.$tn.4 {
154 SELECT sqL FROM sqlite_schema WHERE type='trigger'
155 } "{
156 CREATE TRIGGER r1 INSERT ON t1 BEGIN
157 $final;
158 END
159 }"
160}
161
162finish_test
163