Debian Codegraph @

test/alterlegacy.test

raw
1 # 2018 September 20
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 alterlegacy
16
17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18 ifcapable !altertable {
19 finish_test
20 return
21 }
22
23 do_execsql_test 1.0 {
24 PRAGMA legacy_alter_table = 1;
25 CREATE TABLE t1(a, b, CHECK(t1.a != t1.b));
26 CREATE TABLE t2(a, b);
27 CREATE INDEX t2expr ON t2(a) WHERE t2.b>0;
28 }
29
30 do_execsql_test 1.1 {
31 SELECT sql FROM sqlite_master
32 } {
33 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
34 {CREATE TABLE t2(a, b)}
35 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
36 }
37
38 # Legacy behavior is to corrupt the schema in this case, as the table name in
39 # the CHECK constraint is incorrect after "t1" is renamed. This version is
40 # slightly different - it rejects the change and rolls back the transaction.
41 do_catchsql_test 1.2 {
42 ALTER TABLE t1 RENAME TO t1new;
43 } {1 {error in table t1new after rename: no such column: t1.a}}
44
45 do_execsql_test 1.3 {
46 CREATE TABLE t3(c, d);
47 ALTER TABLE t3 RENAME TO t3new;
48 DROP TABLE t3new;
49 }
50
51 do_execsql_test 1.4 {
52 SELECT sql FROM sqlite_master
53 } {
54 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
55 {CREATE TABLE t2(a, b)}
56 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
57 }
58
59
60 do_catchsql_test 1.3 {
61 ALTER TABLE t2 RENAME TO t2new;
62 } {1 {error in index t2expr after rename: no such column: t2.b}}
63 do_execsql_test 1.4 {
64 SELECT sql FROM sqlite_master
65 } {
66 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
67 {CREATE TABLE t2(a, b)}
68 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
69 }
70
71
72 #-------------------------------------------------------------------------
73 reset_db
74 ifcapable vtab {
75 register_echo_module db
76
77 do_execsql_test 2.0 {
78 PRAGMA legacy_alter_table = 1;
79 CREATE TABLE abc(a, b, c);
80 INSERT INTO abc VALUES(1, 2, 3);
81 CREATE VIRTUAL TABLE eee USING echo('abc');
82 SELECT * FROM eee;
83 } {1 2 3}
84
85 do_execsql_test 2.1 {
86 ALTER TABLE eee RENAME TO fff;
87 SELECT * FROM fff;
88 } {1 2 3}
89
90 db close
91 sqlite3 db test.db
92
93 do_catchsql_test 2.2 {
94 ALTER TABLE fff RENAME TO ggg;
95 } {1 {no such module: echo}}
96 }
97
98 #-------------------------------------------------------------------------
99 reset_db
100
101 do_execsql_test 3.0 {
102 PRAGMA legacy_alter_table = 1;
103 CREATE TABLE txx(a, b, c);
104 INSERT INTO txx VALUES(1, 2, 3);
105 CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx;
106 CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one;
107 CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx;
108 }
109
110 do_execsql_test 3.1.1 {
111 SELECT * FROM vvv;
112 } {1 2 3}
113 do_execsql_test 3.1.2a {
114 ALTER TABLE txx RENAME TO "t xx";
115 }
116 do_catchsql_test 3.1.2b {
117 SELECT * FROM vvv;
118 } {1 {no such table: main.txx}}
119 do_execsql_test 3.1.3 {
120 SELECT sql FROM sqlite_master WHERE name='vvv';
121 } {{CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx}}
122
123
124 do_catchsql_test 3.2.1 {
125 SELECT * FROM uuu;
126 } {1 {no such table: main.txx}}
127 do_execsql_test 3.2.2 {
128 SELECT sql FROM sqlite_master WHERE name='uuu';;
129 } {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one}}
130
131 do_catchsql_test 3.3.1 {
132 SELECT * FROM ttt;
133 } {1 {no such table: txx}}
134 do_execsql_test 3.3.2 {
135 SELECT sql FROM sqlite_temp_master WHERE name='ttt';
136 } {{CREATE VIEW ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx}}
137
138 #-------------------------------------------------------------------------
139 reset_db
140 do_execsql_test 4.0 {
141 PRAGMA legacy_alter_table = 1;
142 CREATE table t1(x, y);
143 CREATE table t2(a, b);
144
145 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
146 SELECT t1.x, * FROM t1, t2;
147 INSERT INTO t2 VALUES(new.x, new.y);
148 END;
149 }
150
151 do_execsql_test 4.1 {
152 INSERT INTO t1 VALUES(1, 1);
153 ALTER TABLE t1 RENAME TO t11;
154 }
155 do_catchsql_test 4.1a {
156 INSERT INTO t11 VALUES(2, 2);
157 } {1 {no such table: main.t1}}
158 do_execsql_test 4.1b {
159 ALTER TABLE t11 RENAME TO t1;
160 ALTER TABLE t2 RENAME TO t22;
161 }
162 do_catchsql_test 4.1c {
163 INSERT INTO t1 VALUES(3, 3);
164 } {1 {no such table: main.t2}}
165
166 proc squish {a} {
167 string trim [regsub -all {[[:space:]][[:space:]]*} $a { }]
168 }
169 db func squish squish
170 do_test 4.2 {
171 execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' }
172 } [list [squish {
173 CREATE TRIGGER tr1 AFTER INSERT ON "t1" BEGIN
174 SELECT t1.x, * FROM t1, t2;
175 INSERT INTO t2 VALUES(new.x, new.y);
176 END
177 }]]
178
179 #-------------------------------------------------------------------------
180 reset_db
181 do_execsql_test 5.0 {
182 PRAGMA legacy_alter_table = 1;
183 CREATE TABLE t9(a, b, c);
184 CREATE TABLE t10(a, b, c);
185 CREATE TEMP TABLE t9(a, b, c);
186
187 CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN
188 INSERT INTO t10 VALUES(new.a, new.b, new.c);
189 END;
190
191 INSERT INTO temp.t9 VALUES(1, 2, 3);
192 SELECT * FROM t10;
193 } {1 2 3}
194
195 do_execsql_test 5.1 {
196 ALTER TABLE temp.t9 RENAME TO 't1234567890'
197 }
198
199 do_execsql_test 5.2 {
200 CREATE TABLE t1(a, b);
201 CREATE TABLE t2(a, b);
202 INSERT INTO t1 VALUES(1, 2);
203 INSERT INTO t2 VALUES(3, 4);
204 CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;
205 SELECT * FROM v;
206 } {1 2 3 4}
207
208 do_execsql_test 5.3 {
209 ALTER TABLE t2 RENAME TO one;
210 } {}
211
212 do_catchsql_test 5.4 {
213 SELECT * FROM v
214 } {1 {no such table: main.t2}}
215
216 do_execsql_test 5.5 {
217 ALTER TABLE one RENAME TO t2;
218 DROP VIEW v;
219 CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;
220 SELECT * FROM vv;
221 } {1 2 3 4}
222
223 do_execsql_test 5.6 {
224 ALTER TABLE t2 RENAME TO one;
225 } {}
226 do_catchsql_test 5.7 {
227 SELECT * FROM vv
228 } {1 {no such table: t2}}
229
230 #-------------------------------------------------------------------------
231
232 ifcapable vtab {
233 register_tcl_module db
234 proc tcl_command {method args} {
235 switch -- $method {
236 xConnect {
237 return "CREATE TABLE t1(a, b, c)"
238 }
239 }
240 return {}
241 }
242
243 do_execsql_test 6.0 {
244 CREATE VIRTUAL TABLE x1 USING tcl(tcl_command);
245 }
246
247 do_execsql_test 6.1 {
248 ALTER TABLE x1 RENAME TO x2;
249 SELECT sql FROM sqlite_master WHERE name = 'x2'
250 } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}}
251
252 do_execsql_test 7.1 {
253 CREATE TABLE ddd(db, sql, zOld, zNew, bTemp);
254 INSERT INTO ddd VALUES(
255 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0
256 ), (
257 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0
258 ), (
259 'main', NULL, 'ddd', 'eee', 0
260 );
261 } {}
262 }
263
264 #-------------------------------------------------------------------------
265 #
266 reset_db
267 forcedelete test.db2
268 do_execsql_test 8.1 {
269 PRAGMA legacy_alter_table = 1;
270 ATTACH 'test.db2' AS aux;
271 PRAGMA foreign_keys = on;
272 CREATE TABLE aux.p1(a INTEGER PRIMARY KEY, b);
273 CREATE TABLE aux.c1(x INTEGER PRIMARY KEY, y REFERENCES p1(a));
274 INSERT INTO aux.p1 VALUES(1, 1);
275 INSERT INTO aux.p1 VALUES(2, 2);
276 INSERT INTO aux.c1 VALUES(NULL, 2);
277 CREATE TABLE aux.c2(x INTEGER PRIMARY KEY, y REFERENCES c1(a));
278 }
279
280 do_execsql_test 8.2 {
281 ALTER TABLE aux.p1 RENAME TO ppp;
282 }
283
284 do_execsql_test 8.2 {
285 INSERT INTO aux.c1 VALUES(NULL, 1);
286 SELECT sql FROM aux.sqlite_master WHERE name = 'c1';
287 } {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}}
288
289 reset_db
290 do_execsql_test 9.0 {
291 PRAGMA legacy_alter_table = 1;
292 CREATE TABLE t1(a, b, c);
293 CREATE VIEW v1 AS SELECT * FROM t2;
294 }
295 do_execsql_test 9.1 {
296 ALTER TABLE t1 RENAME TO t3;
297 } {}
298 do_execsql_test 9.1b {
299 ALTER TABLE t3 RENAME TO t1;
300 } {}
301 do_execsql_test 9.2 {
302 DROP VIEW v1;
303 CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN
304 INSERT INTO t2 VALUES(new.a);
305 END;
306 }
307 do_execsql_test 9.3 {
308 ALTER TABLE t1 RENAME TO t3;
309 } {}
310
311 forcedelete test.db2
312 do_execsql_test 9.4 {
313 ALTER TABLE t3 RENAME TO t1;
314 DROP TRIGGER tr;
315
316 ATTACH 'test.db2' AS aux;
317 CREATE TRIGGER tr AFTER INSERT ON t1 WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END;
318
319 CREATE TABLE aux.t1(x);
320 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END;
321 }
322 do_execsql_test 9.5 {
323 ALTER TABLE main.t1 RENAME TO t3;
324 }
325 do_execsql_test 9.6 {
326 SELECT sql FROM sqlite_temp_master;
327 SELECT sql FROM sqlite_master WHERE type='trigger';
328 } {
329 {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END}
330 {CREATE TRIGGER tr AFTER INSERT ON "t3" WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END}
331 }
332
333 #-------------------------------------------------------------------------
334 reset_db
335 ifcapable fts5 {
336 do_execsql_test 10.0 {
337 PRAGMA legacy_alter_table = 1;
338 CREATE VIRTUAL TABLE fff USING fts5(x, y, z);
339 }
340
341 do_execsql_test 10.1 {
342 BEGIN;
343 INSERT INTO fff VALUES('a', 'b', 'c');
344 ALTER TABLE fff RENAME TO ggg;
345 COMMIT;
346 }
347
348 do_execsql_test 10.2 {
349 SELECT * FROM ggg;
350 } {a b c}
351 }
352
353 #-------------------------------------------------------------------------
354 reset_db
355 forcedelete test.db2
356 db func trigger trigger
357 set ::trigger [list]
358 proc trigger {args} {
359 lappend ::trigger $args
360 }
361 do_execsql_test 11.0 {
362 PRAGMA legacy_alter_table = 1;
363 ATTACH 'test.db2' AS aux;
364 CREATE TABLE aux.t1(a, b, c);
365 CREATE TABLE main.t1(a, b, c);
366 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN
367 SELECT trigger(new.a, new.b, new.c);
368 END;
369 }
370
371 do_execsql_test 11.1 {
372 INSERT INTO main.t1 VALUES(1, 2, 3);
373 INSERT INTO aux.t1 VALUES(4, 5, 6);
374 }
375 do_test 11.2 { set ::trigger } {{4 5 6}}
376
377 do_execsql_test 11.3 {
378 SELECT name, tbl_name FROM sqlite_temp_master;
379 } {tr t1}
380
381 do_execsql_test 11.4 {
382 ALTER TABLE main.t1 RENAME TO t2;
383 SELECT name, tbl_name FROM sqlite_temp_master;
384 } {tr t1}
385
386 do_execsql_test 11.5 {
387 ALTER TABLE aux.t1 RENAME TO t2;
388 SELECT name, tbl_name FROM sqlite_temp_master;
389 } {tr t2}
390
391 do_execsql_test 11.6 {
392 INSERT INTO aux.t2 VALUES(7, 8, 9);
393 }
394 do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}}
395
396 #-------------------------------------------------------------------------
397 reset_db
398 do_execsql_test 12.0 {
399 PRAGMA legacy_alter_table = 1;
400 CREATE TABLE t1(a);
401 CREATE TABLE t2(w);
402 CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN
403 INSERT INTO t1(a) VALUES(new.w);
404 END;
405 CREATE TEMP TABLE t2(x);
406 }
407
408 do_execsql_test 12.1 {
409 ALTER TABLE main.t2 RENAME TO t3;
410 }
411
412 do_execsql_test 12.2 {
413 INSERT INTO t3 VALUES('WWW');
414 SELECT * FROM t1;
415 } {WWW}
416
417
418 #-------------------------------------------------------------------------
419 reset_db
420
421 ifcapable rtree {
422 do_execsql_test 14.0 {
423 PRAGMA legacy_alter_table = 1;
424 CREATE VIRTUAL TABLE rt USING rtree(id, minx, maxx, miny, maxy);
425
426 CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB);
427
428 CREATE TRIGGER tr1 AFTER UPDATE OF "geom" ON "mytable"
429 WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN
430 DELETE FROM rt WHERE id = OLD."fid";
431 END;
432
433 INSERT INTO mytable VALUES(1, X'abcd');
434 }
435
436 do_execsql_test 14.1 {
437 UPDATE mytable SET geom = X'1234'
438 }
439
440 do_execsql_test 14.2 {
441 ALTER TABLE mytable RENAME TO mytable_renamed;
442 }
443
444 do_execsql_test 14.3 {
445 CREATE TRIGGER tr2 AFTER INSERT ON mytable_renamed BEGIN
446 DELETE FROM rt WHERE id=(SELECT min(id) FROM rt);
447 END;
448 }
449
450 do_execsql_test 14.4 {
451 ALTER TABLE mytable_renamed RENAME TO mytable2;
452 }
453 }
454
455 reset_db
456 do_execsql_test 14.5 {
457 PRAGMA legacy_alter_table = 1;
458 CREATE TABLE t1(a, b, c);
459 CREATE VIEW v1 AS SELECT * FROM t1;
460 CREATE TRIGGER xyz AFTER INSERT ON t1 BEGIN
461 SELECT a, b FROM v1;
462 END;
463 }
464 do_execsql_test 14.6 {
465 ALTER TABLE t1 RENAME TO tt1;
466 }
467
468
469 finish_test