Debian Codegraph @

test/altertab2.test

raw
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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix altertab2
16
17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18 ifcapable !altertable {
19 finish_test
20 return
21 }
22
23 ifcapable 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 #
51 do_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
58 do_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
67 do_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
77 do_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 #
91 foreach {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 #-------------------------------------------------------------------------
146 reset_db
147 do_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
154 do_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
163 do_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
172 do_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
181 #-------------------------------------------------------------------------
182 ifcapable windowfunc {
183 do_execsql_test 5.0 {
184 CREATE TABLE t2(a);
185 CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN
186 SELECT a, sum(a) OVER w1 FROM t2
187 WINDOW w1 AS (
188 PARTITION BY a ORDER BY a
189 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
190 ),
191 w2 AS (
192 PARTITION BY a
193 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
194 );
195 END;
196 } {}
197
198 do_execsql_test 5.0.1 {
199 INSERT INTO t2 VALUES(1);
200 } {}
201
202 do_execsql_test 5.1 {
203 ALTER TABLE t2 RENAME TO t2x;
204 SELECT sql FROM sqlite_master WHERE name = 'r2';
205 } {
206 {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN
207 SELECT a, sum(a) OVER w1 FROM "t2x"
208 WINDOW w1 AS (
209 PARTITION BY a ORDER BY a
210 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
211 ),
212 w2 AS (
213 PARTITION BY a
214 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
215 );
216 END}
217 }
218
219 do_execsql_test 5.2 {
220 ALTER TABLE t2x RENAME a TO aaaa;
221 SELECT sql FROM sqlite_master WHERE name = 'r2';
222 } {
223 {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN
224 SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x"
225 WINDOW w1 AS (
226 PARTITION BY aaaa ORDER BY aaaa
227 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
228 ),
229 w2 AS (
230 PARTITION BY aaaa
231 ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
232 );
233 END}
234 }
235
236 do_execsql_test 5.3 {
237 INSERT INTO t2x VALUES(1);
238 } {}
239 } ;# windowfunc
240
241 #-------------------------------------------------------------------------
242
243 do_execsql_test 6.0 {
244 CREATE TABLE t3(a,b,c,d);
245 CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN
246 SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;
247 SELECT rowid, * FROM t3;
248 END;
249 } {}
250
251 do_execsql_test 6.1 {
252 ALTER TABLE t3 RENAME TO t3x;
253 SELECT sql FROM sqlite_master WHERE name = 'r3';
254 } {
255 {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN
256 SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a;
257 SELECT rowid, * FROM "t3x";
258 END}
259 }
260
261 do_execsql_test 6.2 {
262 ALTER TABLE t3x RENAME a TO abcd;
263 SELECT sql FROM sqlite_master WHERE name = 'r3';
264 } {
265 {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN
266 SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd;
267 SELECT rowid, * FROM "t3x";
268 END}
269 }
270
271 #-------------------------------------------------------------------------
272 reset_db
273
274 do_execsql_test 7.0 {
275 CREATE TABLE t1(a,b,c,d,e,f);
276 INSERT INTO t1 VALUES(1,2,3,4,5,6);
277 CREATE TABLE t2(x,y,z);
278 }
279
280 do_execsql_test 7.1 {
281 SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
282 } {1 2 3 4 5 6}
283
284 do_execsql_test 7.2 {
285 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
286 INSERT INTO t2
287 SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
288 END;
289 INSERT INTO t1 VALUES(2,3,4,5,6,7);
290 SELECT * FROM t2;
291 } {1 2 3 2 3 4 4 5 6 5 6 7}
292
293 do_execsql_test 7.3 {
294 ALTER TABLE t1 RENAME TO xyzzy;
295 SELECT sql FROM sqlite_master WHERE name='r1'
296 } {
297 {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
298 INSERT INTO t2
299 SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c;
300 END}
301 }
302
303 do_execsql_test 7.3 {
304 ALTER TABLE xyzzy RENAME c TO ccc;
305 SELECT sql FROM sqlite_master WHERE name='r1'
306 } {
307 {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
308 INSERT INTO t2
309 SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;
310 END}
311 }
312
313 #-------------------------------------------------------------------------
314 reset_db
315 do_execsql_test 8.0 {
316 CREATE TABLE t1(a, b, c);
317 CREATE TABLE t2(a, b, c);
318 CREATE TABLE t3(d, e, f);
319 CREATE VIEW v1 AS SELECT * FROM t1;
320 CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
321 UPDATE t2 SET a = new.d;
322 SELECT a, b, c FROM v1;
323 END;
324 }
325
326 do_execsql_test 8.1 {
327 INSERT INTO t3 VALUES(1, 2, 3);
328 }
329
330 # The following ALTER TABLE fails as if column "t1.a" is renamed the "a"
331 # in the "SELECT a, b, c FROM v1" within the trigger can no longer be
332 # resolved. But at one point there was a bug allowing the ALTER TABLE
333 # succeed. Which meant the subsequent INSERT statement would fail.
334 do_catchsql_test 8.2 {
335 ALTER TABLE t1 RENAME a TO aaa;
336 } {1 {error in trigger tr after rename: no such column: a}}
337 do_execsql_test 8.3 {
338 INSERT INTO t3 VALUES(4, 5, 6);
339 }
340
341 do_execsql_test 8.4 {
342 CREATE TABLE t4(a, b);
343 CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2;
344 }
345
346 # Branches of an expression tree that are optimized out by the AND
347 # optimization are renamed.
348 #
349 do_execsql_test 8.5 {
350 ALTER TABLE t4 RENAME a TO c;
351 SELECT sql FROM sqlite_master WHERE name = 'v4'
352 } {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}}
353
354 # 2019-06-10 https://sqlite.org/src/info/533010b8cacebe82
355 reset_db
356 do_catchsql_test 8.6 {
357 CREATE TABLE t0(c0);
358 CREATE INDEX i0 ON t0(likelihood(1,2) AND 0);
359 ALTER TABLE t0 RENAME TO t1;
360 SELECT sql FROM sqlite_master WHERE name='i0';
361 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
362
363
364 reset_db
365
366 do_execsql_test 9.0 {
367 CREATE TABLE t1(a,b,c,d);
368 CREATE TABLE t2(a,b,c,d,x);
369
370 CREATE TRIGGER AFTER INSERT ON t2 BEGIN
371
372 SELECT group_conct(
373 123 ORDER BY (
374 SELECT 1 FROM ( VALUES(a, 'b'), ('c') )
375 ))
376 FROM t1;
377
378 END;
379 }
380
381 do_catchsql_test 9.1 {
382 ALTER TABLE t2 RENAME TO newname;
383 } {1 {error in trigger AFTER: all VALUES must have the same number of terms}}
384
385 finish_test