Debian Codegraph @

test/altertrig.test

raw
1 # 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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix altertrig
16
17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18 ifcapable !altertable {
19 finish_test
20 return
21 }
22
23 proc collapse_whitespace {in} {
24 regsub -all {[ \t\n]+} [string trim $in] { }
25 }
26
27 proc 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
37 do_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
48 do_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
57 do_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
64 do_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
73 foreach {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 } {
85 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
86 } {
87 UPDATE t1 SET a='xyz' FROM "t10", (SELECT * FROM (SELECT e FROM "t10"))
88 }
89
90 3 {
91 ALTER TABLE t3 RENAME e TO abc
92 } {
93 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
94 } {
95 UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT abc FROM t3))
96 }
97
98 4 {
99 ALTER TABLE t2 RENAME c TO abc
100 } {
101 UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE c)
102 } {
103 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
120 }
121
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 }
137
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);
144 CREATE TABLE t4(e,f);
145 }
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
162 finish_test