Debian Codegraph @

test/altercol.test

raw
1 # 2009 February 2
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 # This file implements regression tests for SQLite library. The
12 # focus of this script is testing that SQLite can handle a subtle
13 # file format change that may be used in the future to implement
14 # "ALTER TABLE ... RENAME COLUMN ... TO".
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 set testprefix altercol
20
21 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
22 ifcapable !altertable {
23 finish_test
24 return
25 }
26
27 # Drop all the tables and views in the 'main' database of database connect
28 # [db]. Sort the objects by name before dropping them.
29 #
30 proc drop_all_tables_and_views {db} {
31 set SQL {
32 SELECT name, type FROM sqlite_master
33 WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
34 ORDER BY 1
35 }
36 foreach {z t} [db eval $SQL] {
37 db eval "DROP $t $z"
38 }
39 }
40
41 foreach {tn before after} {
42 1 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB)}
43 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB)}
44
45 2 {CREATE TABLE t1(a INTEGER, x TEXT, "b" BLOB)}
46 {CREATE TABLE t1(a INTEGER, x TEXT, "d" BLOB)}
47
48 3 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK(b!=''))}
49 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK(d!=''))}
50
51 4 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK(t1.b!=''))}
52 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK(t1.d!=''))}
53
54 5 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, CHECK( coalesce(b,c) ))}
55 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, CHECK( coalesce(d,c) ))}
56
57 6 {CREATE TABLE t1(a INTEGER, "b"TEXT, c BLOB, CHECK( coalesce(b,c) ))}
58 {CREATE TABLE t1(a INTEGER, "d"TEXT, c BLOB, CHECK( coalesce(d,c) ))}
59
60 7 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b, c))}
61 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d, c))}
62
63 8 {CREATE TABLE t1(a INTEGER, b TEXT PRIMARY KEY, c BLOB)}
64 {CREATE TABLE t1(a INTEGER, d TEXT PRIMARY KEY, c BLOB)}
65
66 9 {CREATE TABLE t1(a, b TEXT, c, PRIMARY KEY(a, b), UNIQUE("B"))}
67 {CREATE TABLE t1(a, d TEXT, c, PRIMARY KEY(a, d), UNIQUE("d"))}
68
69 10 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(a, c)}
70 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(a, c)}}
71
72 11 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(b, c)}
73 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d, c)}}
74
75 12 {CREATE TABLE t1(a, b, c); CREATE INDEX t1i ON t1(b+b+b+b, c) WHERE b>0}
76 {{CREATE TABLE t1(a, d, c)} {CREATE INDEX t1i ON t1(d+d+d+d, c) WHERE d>0}}
77
78 13 {CREATE TABLE t1(a, b, c, FOREIGN KEY (b) REFERENCES t2)}
79 {CREATE TABLE t1(a, d, c, FOREIGN KEY (d) REFERENCES t2)}
80
81 14 {CREATE TABLE t1(a INTEGER, b TEXT, c BLOB, PRIMARY KEY(b))}
82 {CREATE TABLE t1(a INTEGER, d TEXT, c BLOB, PRIMARY KEY(d))}
83
84 15 {CREATE TABLE t1(a INTEGER, b INTEGER, c BLOB, PRIMARY KEY(b))}
85 {CREATE TABLE t1(a INTEGER, d INTEGER, c BLOB, PRIMARY KEY(d))}
86
87 16 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB)}
88 {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB)}
89
90 17 {CREATE TABLE t1(a INTEGER, b INTEGER PRIMARY KEY, c BLOB, FOREIGN KEY (b) REFERENCES t2)}
91 {CREATE TABLE t1(a INTEGER, d INTEGER PRIMARY KEY, c BLOB, FOREIGN KEY (d) REFERENCES t2)}
92
93 } {
94 reset_db
95 do_execsql_test 1.$tn.0 $before
96
97 do_execsql_test 1.$tn.1 {
98 INSERT INTO t1 VALUES(1, 2, 3);
99 }
100
101 do_execsql_test 1.$tn.2 {
102 ALTER TABLE t1 RENAME COLUMN b TO d;
103 }
104
105 do_execsql_test 1.$tn.3 {
106 SELECT * FROM t1;
107 } {1 2 3}
108
109 if {[string first INDEX $before]>0} {
110 set res $after
111 } else {
112 set res [list $after]
113 }
114 do_execsql_test 1.$tn.4 {
115 SELECT sql FROM sqlite_master WHERE tbl_name='t1' AND sql!=''
116 } $res
117 }
118
119 #-------------------------------------------------------------------------
120 #
121 do_execsql_test 2.0 {
122 CREATE TABLE t3(a, b, c, d, e, f, g, h, i, j, k, l, m, FOREIGN KEY (b, c, d, e, f, g, h, i, j, k, l, m) REFERENCES t4);
123 }
124
125 sqlite3 db2 test.db
126 do_execsql_test -db db2 2.1 { SELECT b FROM t3 }
127
128 do_execsql_test 2.2 {
129 ALTER TABLE t3 RENAME b TO biglongname;
130 SELECT sql FROM sqlite_master WHERE name='t3';
131 } {{CREATE TABLE t3(a, biglongname, c, d, e, f, g, h, i, j, k, l, m, FOREIGN KEY (biglongname, c, d, e, f, g, h, i, j, k, l, m) REFERENCES t4)}}
132
133 do_execsql_test -db db2 2.3 { SELECT biglongname FROM t3 }
134
135 #-------------------------------------------------------------------------
136 #
137 do_execsql_test 3.0 {
138 CREATE TABLE t4(x, y, z);
139 CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN
140 SELECT x, y, z FROM t4;
141 DELETE FROM t4 WHERE y=32;
142 UPDATE t4 SET x=y+1, y=0 WHERE y=32;
143 INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0;
144 END;
145 INSERT INTO t4 VALUES(3, 2, 1);
146 }
147
148 do_execsql_test 3.1 {
149 ALTER TABLE t4 RENAME y TO abc;
150 SELECT sql FROM sqlite_master WHERE name='t4';
151 } {{CREATE TABLE t4(x, abc, z)}}
152
153 do_execsql_test 3.2 {
154 SELECT * FROM t4;
155 } {3 2 1}
156
157 do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {}
158
159 do_execsql_test 3.4 { SELECT sql FROM sqlite_master WHERE type='trigger' } {
160 {CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.abc<0 BEGIN
161 SELECT x, abc, z FROM t4;
162 DELETE FROM t4 WHERE abc=32;
163 UPDATE t4 SET x=abc+1, abc=0 WHERE abc=32;
164 INSERT INTO t4(x, abc, z) SELECT 4, 5, 6 WHERE 0;
165 END}
166 }
167
168 #-------------------------------------------------------------------------
169 #
170 do_execsql_test 4.0 {
171 CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, d));
172 CREATE TABLE p1(c, d, PRIMARY KEY(c, d));
173 PRAGMA foreign_keys = 1;
174 INSERT INTO p1 VALUES(1, 2);
175 INSERT INTO p1 VALUES(3, 4);
176 }
177
178 do_execsql_test 4.1 {
179 ALTER TABLE p1 RENAME d TO "silly name";
180 SELECT sql FROM sqlite_master WHERE name IN ('c1', 'p1');
181 } {
182 {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "silly name"))}
183 {CREATE TABLE p1(c, "silly name", PRIMARY KEY(c, "silly name"))}
184 }
185
186 do_execsql_test 4.2 { INSERT INTO c1 VALUES(1, 2); }
187
188 do_execsql_test 4.3 {
189 CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1);
190 }
191
192 do_execsql_test 4.4 {
193 ALTER TABLE p1 RENAME "silly name" TO reasonable;
194 SELECT sql FROM sqlite_master WHERE name IN ('c1', 'c2', 'p1');
195 } {
196 {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "reasonable"))}
197 {CREATE TABLE p1(c, "reasonable", PRIMARY KEY(c, "reasonable"))}
198 {CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1)}
199 }
200
201 #-------------------------------------------------------------------------
202
203 do_execsql_test 5.0 {
204 CREATE TABLE t5(a, b, c);
205 CREATE INDEX t5a ON t5(a);
206 INSERT INTO t5 VALUES(1, 2, 3), (4, 5, 6);
207 ANALYZE;
208 }
209
210 do_execsql_test 5.1 {
211 ALTER TABLE t5 RENAME b TO big;
212 SELECT big FROM t5;
213 } {2 5}
214
215 do_catchsql_test 6.1 {
216 ALTER TABLE sqlite_stat1 RENAME tbl TO thetable;
217 } {1 {table sqlite_stat1 may not be altered}}
218
219 #-------------------------------------------------------------------------
220 #
221 do_execsql_test 6.0 {
222 CREATE TABLE blob(
223 rid INTEGER PRIMARY KEY,
224 rcvid INTEGER,
225 size INTEGER,
226 uuid TEXT UNIQUE NOT NULL,
227 content BLOB,
228 CHECK( length(uuid)>=40 AND rid>0 )
229 );
230 }
231
232 do_execsql_test 6.1 {
233 ALTER TABLE "blob" RENAME COLUMN "rid" TO "a1";
234 }
235
236 do_catchsql_test 6.2 {
237 ALTER TABLE "blob" RENAME COLUMN "a1" TO [where];
238 } {0 {}}
239
240 do_execsql_test 6.3 {
241 SELECT "where" FROM blob;
242 } {}
243
244 #-------------------------------------------------------------------------
245 # Triggers.
246 #
247 db close
248 db2 close
249 reset_db
250 do_execsql_test 7.0 {
251 CREATE TABLE c(x);
252 INSERT INTO c VALUES(0);
253 CREATE TABLE t6("col a", "col b", "col c");
254 CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN
255 UPDATE c SET x=x+1;
256 END;
257 }
258
259 do_execsql_test 7.1.1 {
260 INSERT INTO t6 VALUES(0, 0, 0);
261 UPDATE t6 SET "col c" = 1;
262 SELECT * FROM c;
263 } {1}
264
265 do_execsql_test 7.1.2 {
266 ALTER TABLE t6 RENAME "col c" TO "col 3";
267 }
268
269 do_execsql_test 7.1.3 {
270 UPDATE t6 SET "col 3" = 0;
271 SELECT * FROM c;
272 } {2}
273
274 #-------------------------------------------------------------------------
275 # Views.
276 #
277 reset_db
278 do_execsql_test 8.0 {
279 CREATE TABLE a1(x INTEGER, y TEXT, z BLOB, PRIMARY KEY(x));
280 CREATE TABLE a2(a, b, c);
281 CREATE VIEW v1 AS SELECT x, y, z FROM a1;
282 }
283
284 do_execsql_test 8.1 {
285 ALTER TABLE a1 RENAME y TO yyy;
286 SELECT sql FROM sqlite_master WHERE type='view';
287 } {{CREATE VIEW v1 AS SELECT x, yyy, z FROM a1}}
288
289 do_execsql_test 8.2.1 {
290 DROP VIEW v1;
291 CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2;
292 } {}
293 do_execsql_test 8.2.2 {
294 ALTER TABLE a1 RENAME x TO xxx;
295 }
296 do_execsql_test 8.2.3 {
297 SELECT sql FROM sqlite_master WHERE type='view';
298 } {{CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2}}
299
300 do_execsql_test 8.3.1 {
301 DROP TABLE a2;
302 DROP VIEW v2;
303 CREATE TABLE a2(a INTEGER PRIMARY KEY, b, c);
304 CREATE VIEW v2 AS SELECT xxx, xxx+xxx, a, a+a FROM a1, a2;
305 } {}
306 do_execsql_test 8.3.2 {
307 ALTER TABLE a1 RENAME xxx TO x;
308 }
309 do_execsql_test 8.3.3 {
310 SELECT sql FROM sqlite_master WHERE type='view';
311 } {{CREATE VIEW v2 AS SELECT x, x+x, a, a+a FROM a1, a2}}
312
313 do_execsql_test 8.4.0 {
314 CREATE TABLE b1(a, b, c);
315 CREATE TABLE b2(x, y, z);
316 }
317
318 do_execsql_test 8.4.1 {
319 CREATE VIEW vvv AS SELECT c+c || coalesce(c, c) FROM b1, b2 WHERE x=c GROUP BY c HAVING c>0;
320 ALTER TABLE b1 RENAME c TO "a;b";
321 SELECT sql FROM sqlite_master WHERE name='vvv';
322 } {{CREATE VIEW vvv AS SELECT "a;b"+"a;b" || coalesce("a;b", "a;b") FROM b1, b2 WHERE x="a;b" GROUP BY "a;b" HAVING "a;b">0}}
323
324 do_execsql_test 8.4.2 {
325 CREATE VIEW www AS SELECT b FROM b1 UNION ALL SELECT y FROM b2;
326 ALTER TABLE b1 RENAME b TO bbb;
327 SELECT sql FROM sqlite_master WHERE name='www';
328 } {{CREATE VIEW www AS SELECT bbb FROM b1 UNION ALL SELECT y FROM b2}}
329
330 db collate nocase {string compare}
331
332 do_execsql_test 8.4.3 {
333 CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT x FROM b2 ORDER BY 1 COLLATE nocase;
334 }
335
336 do_execsql_test 8.4.4 {
337 ALTER TABLE b2 RENAME x TO hello;
338 SELECT sql FROM sqlite_master WHERE name='xxx';
339 } {{CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT hello FROM b2 ORDER BY 1 COLLATE nocase}}
340
341 do_catchsql_test 8.4.5 {
342 CREATE VIEW zzz AS SELECT george, ringo FROM b1;
343 ALTER TABLE b1 RENAME a TO aaa;
344 } {1 {error in view zzz: no such column: george}}
345
346 do_execsql_test 8.5 {
347 DROP VIEW zzz;
348 CREATE TABLE t5(a TEXT, b INT);
349 INSERT INTO t5(a,b) VALUES('aaa',7),('bbb',3),('ccc',4);
350 CREATE VIEW vt5(x) AS SELECT group_concat(a ORDER BY b) FROM t5;
351 SELECT x FROM vt5;
352 } {bbb,ccc,aaa}
353 do_execsql_test 8.5.1 {
354 ALTER TABLE t5 RENAME COLUMN b TO bbb;
355 SELECT sql FROM sqlite_schema WHERE name='vt5';
356 } {{CREATE VIEW vt5(x) AS SELECT group_concat(a ORDER BY bbb) FROM t5}}
357 do_execsql_test 8.5.2 {
358 SELECT x FROM vt5;
359 } {bbb,ccc,aaa}
360
361 #-------------------------------------------------------------------------
362 # More triggers.
363 #
364 proc do_rename_column_test {tn old new lSchema} {
365 for {set i 0} {$i < 2} {incr i} {
366 drop_all_tables_and_views db
367
368 set lSorted [list]
369 foreach sql $lSchema {
370 execsql $sql
371 lappend lSorted [string trim $sql]
372 }
373 set lSorted [lsort $lSorted]
374
375 do_execsql_test $tn.$i.1 {
376 SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
377 } $lSorted
378
379 if {$i==1} {
380 db close
381 sqlite3 db test.db
382 }
383
384 do_execsql_test $tn.$i.2 "ALTER TABLE t1 RENAME $old TO $new"
385
386 do_execsql_test $tn.$i.3 {
387 SELECT sql FROM sqlite_master ORDER BY 1
388 } [string map [list $old $new] $lSorted]
389 }
390 }
391
392 foreach {tn old new lSchema} {
393 1 _x_ _xxx_ {
394 { CREATE TABLE t1(a, b, _x_) }
395 { CREATE TRIGGER AFTER INSERT ON t1 BEGIN
396 SELECT _x_ FROM t1;
397 END }
398 }
399
400 2 _x_ _xxx_ {
401 { CREATE TABLE t1(a, b, _x_) }
402 { CREATE TABLE t2(c, d, e) }
403 { CREATE TRIGGER ttt AFTER INSERT ON t2 BEGIN
404 SELECT _x_ FROM t1;
405 END }
406 }
407
408 3 _x_ _xxx_ {
409 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
410 { CREATE TABLE t2(c, d, e) }
411 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN
412 INSERT INTO t2 VALUES(new.a, new.b, new._x_);
413 END }
414 }
415
416 4 _x_ _xxx_ {
417 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
418 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN
419 INSERT INTO t1 VALUES(new.a, new.b, new._x_)
420 ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
421 END }
422 }
423
424 4 _x_ _xxx_ {
425 { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
426 { CREATE TRIGGER ttt AFTER UPDATE ON t1 BEGIN
427 INSERT INTO t1 VALUES(new.a, new.b, new._x_)
428 ON CONFLICT (_x_) WHERE _x_>10 DO NOTHING;
429 END }
430 }
431 } {
432 do_rename_column_test 9.$tn $old $new $lSchema
433 }
434
435 #-------------------------------------------------------------------------
436 # Test that views can be edited even if there are missing collation
437 # sequences or user defined functions.
438 #
439 reset_db
440
441 ifcapable vtab {
442 foreach {tn old new lSchema} {
443 1 _x_ _xxx_ {
444 { CREATE TABLE t1(a, b, _x_) }
445 { CREATE VIEW s1 AS SELECT a, b, _x_ FROM t1 WHERE _x_='abc' COLLATE xyz }
446 }
447
448 2 _x_ _xxx_ {
449 { CREATE TABLE t1(a, b, _x_) }
450 { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE scalar(_x_) }
451 }
452
453 3 _x_ _xxx_ {
454 { CREATE TABLE t1(a, b, _x_) }
455 { CREATE VIEW v1 AS SELECT a, b, _x_ FROM t1 WHERE _x_ = unicode(1, 2, 3) }
456 }
457
458 4 _x_ _xxx_ {
459 { CREATE TABLE t1(a, b, _x_) }
460 { CREATE VIRTUAL TABLE e1 USING echo(t1) }
461 }
462 } {
463 register_echo_module db
464 do_rename_column_test 10.$tn $old $new $lSchema
465 }
466
467 #--------------------------------------------------------------------------
468 # Test that if a view or trigger refers to a virtual table for which the
469 # module is not available, RENAME COLUMN cannot proceed.
470 #
471 reset_db
472 register_echo_module db
473 do_execsql_test 11.0 {
474 CREATE TABLE x1(a, b, c);
475 CREATE VIRTUAL TABLE e1 USING echo(x1);
476 }
477 db close
478 sqlite3 db test.db
479
480 do_execsql_test 11.1 {
481 ALTER TABLE x1 RENAME b TO bbb;
482 SELECT sql FROM sqlite_master;
483 } { {CREATE TABLE x1(a, bbb, c)} {CREATE VIRTUAL TABLE e1 USING echo(x1)} }
484
485 do_execsql_test 11.2 {
486 CREATE VIEW v1 AS SELECT e1.*, x1.c FROM e1, x1;
487 }
488
489 do_catchsql_test 11.3 {
490 ALTER TABLE x1 RENAME c TO ccc;
491 } {1 {error in view v1: no such module: echo}}
492 }
493
494 #-------------------------------------------------------------------------
495 # Test some error conditions:
496 #
497 # 1. Renaming a column of a system table,
498 # 2. Renaming a column of a VIEW,
499 # 3. Renaming a column of a virtual table.
500 # 4. Renaming a column that does not exist.
501 # 5. Renaming a column of a table that does not exist.
502 #
503 reset_db
504 do_execsql_test 12.1.1 {
505 CREATE TABLE t1(a, b);
506 CREATE INDEX t1a ON t1(a);
507 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 4);
508 ANALYZE;
509 }
510 do_catchsql_test 12.1.2 {
511 ALTER TABLE sqlite_stat1 RENAME idx TO theindex;
512 } {1 {table sqlite_stat1 may not be altered}}
513 do_execsql_test 12.1.3 {
514 SELECT sql FROM sqlite_master WHERE tbl_name = 'sqlite_stat1'
515 } {{CREATE TABLE sqlite_stat1(tbl,idx,stat)}}
516
517 do_execsql_test 12.2.1 {
518 CREATE VIEW v1 AS SELECT * FROM t1;
519 CREATE VIEW v2(c, d) AS SELECT * FROM t1;
520 }
521 do_catchsql_test 12.2.2 {
522 ALTER TABLE v1 RENAME a TO z;
523 } {1 {cannot rename columns of view "v1"}}
524 do_catchsql_test 12.2.3 {
525 ALTER TABLE v2 RENAME c TO y;
526 } {1 {cannot rename columns of view "v2"}}
527
528 ifcapable fts5 {
529 do_execsql_test 12.3.1 {
530 CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
531 }
532 do_catchsql_test 12.3.2 {
533 ALTER TABLE ft RENAME a TO z;
534 } {1 {cannot rename columns of virtual table "ft"}}
535 }
536
537 do_execsql_test 12.4.1 {
538 CREATE TABLE t2(x, y, z);
539 }
540 do_catchsql_test 12.4.2 {
541 ALTER TABLE t2 RENAME COLUMN a TO b;
542 } {1 {no such column: "a"}}
543
544 do_catchsql_test 12.5.1 {
545 ALTER TABLE t3 RENAME COLUMN a TO b;
546 } {1 {no such table: t3}}
547
548 #-------------------------------------------------------------------------
549 # Test the effect of some parse/resolve errors.
550 #
551 reset_db
552 do_execsql_test 13.1.1 {
553 CREATE TABLE x1(i INTEGER, t TEXT UNIQUE);
554 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
555 SELECT * FROM nosuchtable;
556 END;
557 }
558
559 do_catchsql_test 13.1.2 {
560 ALTER TABLE x1 RENAME COLUMN t TO ttt;
561 } {1 {error in trigger tr1: no such table: main.nosuchtable}}
562
563 do_execsql_test 13.1.3 {
564 DROP TRIGGER tr1;
565 CREATE INDEX x1i ON x1(i);
566 SELECT sql FROM sqlite_master WHERE name='x1i';
567 } {{CREATE INDEX x1i ON x1(i)}}
568
569 sqlite3_db_config db DEFENSIVE 0
570 do_execsql_test 13.1.4 {
571 PRAGMA writable_schema = ON;
572 UPDATE sqlite_master SET sql = 'CREATE INDEX x1i ON x1(j)' WHERE name='x1i';
573 PRAGMA writable_schema = OFF;
574 } {}
575
576 do_catchsql_test 13.1.5 {
577 ALTER TABLE x1 RENAME COLUMN t TO ttt;
578 } {1 {error in index x1i: no such column: j}}
579
580 do_execsql_test 13.1.6 {
581 PRAGMA writable_schema = ON;
582 UPDATE sqlite_master SET sql = '' WHERE name='x1i';
583 PRAGMA writable_schema = OFF;
584 } {}
585
586 do_catchsql_test 13.1.7 {
587 ALTER TABLE x1 RENAME COLUMN t TO ttt;
588 } {1 {error in index x1i: }}
589
590 do_execsql_test 13.1.8 {
591 PRAGMA writable_schema = ON;
592 DELETE FROM sqlite_master WHERE name = 'x1i';
593 PRAGMA writable_schema = OFF;
594 }
595
596 do_execsql_test 13.2.0 {
597 CREATE TABLE data(x UNIQUE, y, z);
598 }
599 foreach {tn trigger error} {
600 1 {
601 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
602 UPDATE data SET x=x+1 WHERE zzz=new.i;
603 END;
604 } {no such column: zzz}
605
606 2 {
607 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
608 INSERT INTO data(x, y) VALUES(new.i, new.t, 1)
609 ON CONFLICT (x) DO UPDATE SET z=zz+1;
610 END;
611 } {no such column: zz}
612
613 3 {
614 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
615 INSERT INTO x1(i, t) VALUES(new.i+1, new.t||'1')
616 ON CONFLICT (tttttt) DO UPDATE SET t=i+1;
617 END;
618 } {no such column: tttttt}
619
620 4 {
621 CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
622 INSERT INTO nosuchtable VALUES(new.i, new.t);
623 END;
624 } {no such table: main.nosuchtable}
625 } {
626 do_execsql_test 13.2.$tn.1 "
627 DROP TRIGGER IF EXISTS tr1;
628 $trigger
629 "
630
631 do_catchsql_test 13.2.$tn.2 {
632 ALTER TABLE x1 RENAME COLUMN t TO ttt;
633 } "1 {error in trigger tr1: $error}"
634 }
635
636 #-------------------------------------------------------------------------
637 # Passing invalid parameters directly to sqlite_rename_column().
638 #
639 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
640 do_execsql_test 14.1 {
641 CREATE TABLE ddd(sql, type, object, db, tbl, icol, znew, bquote);
642 INSERT INTO ddd VALUES(
643 'CREATE TABLE x1(i INTEGER, t TEXT)',
644 'table', 'x1', 'main', 'x1', -1, 'zzz', 0
645 ), (
646 'CREATE TABLE x1(i INTEGER, t TEXT)',
647 'table', 'x1', 'main', 'x1', 2, 'zzz', 0
648 ), (
649 'CREATE TABLE x1(i INTEGER, t TEXT)',
650 'table', 'x1', 'main', 'notable', 0, 'zzz', 0
651 ), (
652 'CREATE TABLE x1(i INTEGER, t TEXT)',
653 'table', 'x1', 'main', 'ddd', -1, 'zzz', 0
654 );
655 } {}
656
657 do_execsql_test 14.2 {
658 SELECT
659 sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote, 0)
660 FROM ddd;
661 } {{} {} {} {}}
662 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
663
664 # If the INTERNAL_FUNCTIONS test-control is disabled (which is the default)
665 # then the sqlite_rename_table() SQL function is not accessible to
666 # ordinary SQL.
667 #
668 do_catchsql_test 14.3 {
669 SELECT sqlite_rename_column(0,0,0,0,0,0,0,0,0);
670 } {1 {no such function: sqlite_rename_column}}
671
672 #-------------------------------------------------------------------------
673 #
674 reset_db
675 do_execsql_test 15.0 {
676 CREATE TABLE xxx(a, b, c);
677 SELECT a AS d FROM xxx WHERE d=0;
678 }
679
680 do_execsql_test 15.1 {
681 CREATE VIEW vvv AS SELECT a AS d FROM xxx WHERE d=0;
682 ALTER TABLE xxx RENAME a TO xyz;
683 }
684
685 do_execsql_test 15.2 {
686 SELECT sql FROM sqlite_master WHERE type='view';
687 } {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}}
688
689 #-------------------------------------------------------------------------
690 #
691 do_execsql_test 16.1.0 {
692 CREATE TABLE t1(a,b,c);
693 CREATE TABLE t2(d,e,f);
694 INSERT INTO t1 VALUES(1,2,3);
695 INSERT INTO t2 VALUES(4,5,6);
696 CREATE VIEW v4 AS SELECT a, d FROM t1, t2;
697 SELECT * FROM v4;
698 } {1 4}
699
700 do_catchsql_test 16.1.1 {
701 ALTER TABLE t2 RENAME d TO a;
702 } {1 {error in view v4 after rename: ambiguous column name: a}}
703
704 do_execsql_test 16.1.2 {
705 SELECT * FROM v4;
706 } {1 4}
707
708 do_execsql_test 16.1.3 {
709 CREATE UNIQUE INDEX t2d ON t2(d);
710 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
711 INSERT INTO t2 VALUES(new.a, new.b, new.c)
712 ON CONFLICT(d) DO UPDATE SET f = excluded.f;
713 END;
714 }
715
716 do_execsql_test 16.1.4 {
717 INSERT INTO t1 VALUES(4, 8, 456);
718 SELECT * FROM t2;
719 } {4 5 456}
720
721 do_execsql_test 16.1.5 {
722 ALTER TABLE t2 RENAME COLUMN f TO "big f";
723 INSERT INTO t1 VALUES(4, 0, 20456);
724 SELECT * FROM t2;
725 } {4 5 20456}
726
727 do_execsql_test 16.1.6 {
728 ALTER TABLE t1 RENAME COLUMN c TO "big c";
729 INSERT INTO t1 VALUES(4, 0, 0);
730 SELECT * FROM t2;
731 } {4 5 0}
732
733 do_execsql_test 16.2.1 {
734 CREATE VIEW temp.v5 AS SELECT "big c" FROM t1;
735 SELECT * FROM v5;
736 } {3 456 20456 0}
737
738 do_execsql_test 16.2.2 {
739 ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc;
740 } {}
741
742 do_execsql_test 16.2.3 {
743 SELECT * FROM v5;
744 } {3 456 20456 0}
745
746 #-------------------------------------------------------------------------
747 #
748 do_execsql_test 17.0 {
749 CREATE TABLE u7(x, y, z);
750 CREATE TRIGGER u7t AFTER INSERT ON u7 BEGIN
751 INSERT INTO u8 VALUES(new.x, new.y, new.z);
752 END;
753 } {}
754 do_catchsql_test 17.1 {
755 ALTER TABLE u7 RENAME x TO xxx;
756 } {1 {error in trigger u7t: no such table: main.u8}}
757
758 do_execsql_test 17.2 {
759 CREATE TEMP TABLE uu7(x, y, z);
760 CREATE TRIGGER uu7t AFTER INSERT ON uu7 BEGIN
761 INSERT INTO u8 VALUES(new.x, new.y, new.z);
762 END;
763 } {}
764 do_catchsql_test 17.3 {
765 ALTER TABLE uu7 RENAME x TO xxx;
766 } {1 {error in trigger uu7t: no such table: u8}}
767
768 reset_db
769 forcedelete test.db2
770 do_execsql_test 18.0 {
771 ATTACH 'test.db2' AS aux;
772 CREATE TABLE t1(a);
773 CREATE TABLE aux.log(v);
774 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN
775 INSERT INTO log VALUES(new.a);
776 END;
777 INSERT INTO t1 VALUES(111);
778 SELECT v FROM log;
779 } {111}
780
781 do_execsql_test 18.1 {
782 ALTER TABLE t1 RENAME a TO b;
783 }
784
785 reset_db
786 do_execsql_test 19.0 {
787 CREATE TABLE t1(a, b);
788 CREATE TABLE t2(c, d);
789 CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.a) FROM t1, t2 WHERE t1.b=t2.d;
790 }
791
792 do_execsql_test 19.1 {
793 ALTER TABLE t1 RENAME a TO f;
794 SELECT sql FROM sqlite_master WHERE name = 'v2';
795 } {
796 {CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.f) FROM t1, t2 WHERE t1.b=t2.d}
797 }
798
799 # 2019-01-08: https://sqlite.org/src/tktview/bc8d94f0fbd633fd9a051e3
800 #
801 # ALTER TABLE RENAME COLUMN does not work for tables that have redundant
802 # UNIQUE constraints.
803 #
804 sqlite3 db :memory:
805 do_execsql_test 20.100 {
806 CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA));
807 ALTER TABLE t1 RENAME aaa TO bbb;
808 SELECT sql FROM sqlite_master WHERE name='t1';
809 } {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))}}
810 do_execsql_test 20.105 {
811 DROP TABLE t1;
812 CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA))WITHOUT ROWID;
813 ALTER TABLE t1 RENAME aaa TO bbb;
814 SELECT sql FROM sqlite_master WHERE name='t1';
815 } {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))WITHOUT ROWID}}
816 do_execsql_test 20.110 {
817 DROP TABLE t1;
818 CREATE TABLE t1(aa UNIQUE,bb UNIQUE,cc UNIQUE,UNIQUE(aA),PRIMARY KEY(bB),UNIQUE(cC));
819 ALTER TABLE t1 RENAME aa TO xx;
820 ALTER TABLE t1 RENAME bb TO yy;
821 ALTER TABLE t1 RENAME cc TO zz;
822 SELECT sql FROM sqlite_master WHERE name='t1';
823 } {{CREATE TABLE t1(xx UNIQUE,yy UNIQUE,zz UNIQUE,UNIQUE(xx),PRIMARY KEY(yy),UNIQUE(zz))}}
824
825 #-------------------------------------------------------------------------
826 reset_db
827 do_execsql_test 21.0 {
828 CREATE TABLE t1(a, b, c NOT NULL);
829 CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN new.c IS NOT NULL BEGIN
830 SELECT c NOT NULL FROM t1;
831 END;
832 }
833
834 do_execsql_test 21.1 {
835 ALTER TABLE t1 RENAME c TO d;
836 }
837
838 do_execsql_test 21.2 {
839 SELECT sql FROM sqlite_schema WHERE name IS 'tr1'
840 } {{CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN new.d IS NOT NULL BEGIN
841 SELECT d NOT NULL FROM t1;
842 END}
843 }
844
845 #-------------------------------------------------------------------------
846 #
847 reset_db
848 do_execsql_test 22.0 {
849 CREATE TABLE t1(a, b);
850 CREATE TABLE t2(c, othername, extra AS (c + 1));
851 ALTER TABLE t1 RENAME a to othername;
852 SELECT sql FROM sqlite_schema;
853 } {
854 {CREATE TABLE t1(othername, b)}
855 {CREATE TABLE t2(c, othername, extra AS (c + 1))}
856 }
857
858 #-------------------------------------------------------------------------
859 #
860 reset_db
861 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1
862 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
863 do_execsql_test 22.0 {
864 CREATE TABLE t1(a, b);
865 CREATE INDEX x1 on t1("c"=b);
866 INSERT INTO t1 VALUES('a', 'a');
867 INSERT INTO t1 VALUES('b', 'b');
868 INSERT INTO t1 VALUES('c', 'c');
869 ALTER TABLE t1 RENAME COLUMN a TO "c";
870 PRAGMA integrity_check;
871 } {ok}
872
873 reset_db
874 do_execsql_test 23.0 {
875 CREATE TABLE t1('a'"b",c);
876 CREATE INDEX i1 ON t1('a');
877 INSERT INTO t1 VALUES(1,2), (3,4);
878 ALTER TABLE t1 RENAME COLUMN a TO x;
879 PRAGMA integrity_check;
880 SELECT sql FROM sqlite_schema WHERE name='t1';
881
882 } {ok {CREATE TABLE t1("x" "b",c)}}
883
884 # 2022-02-04
885 # Do not complain about syntax errors in the schema if
886 # in PRAGMA writable_schema=ON mode.
887 #
888 reset_db
889 do_execsql_test 23.0 {
890 CREATE TABLE t1(a INT, b REAL, c TEXT, d BLOB, e ANY);
891 CREATE INDEX t1abx ON t1(a, b, a+b) WHERE c IS NOT NULL;
892 CREATE VIEW t2 AS SELECT a+10, b*5.0, xyz FROM t1; -- unknown column "xyz"
893 CREATE TABLE schema_copy(name TEXT, sql TEXT);
894 INSERT INTO schema_copy(name,sql) SELECT name, sql FROM sqlite_schema WHERE sql IS NOT NULL;
895 } {}
896 do_catchsql_test 23.1 {
897 ALTER TABLE t1 RENAME COLUMN e TO eeee;
898 } {1 {error in view t2: no such column: xyz}}
899 do_execsql_test 23.2 {
900 SELECT name, sql FROM sqlite_master
901 EXCEPT SELECT name, sql FROM schema_copy;
902 } {}
903 do_execsql_test 23.3 {
904 BEGIN;
905 PRAGMA writable_schema=ON;
906 ALTER TABLE t1 RENAME COLUMN e TO eeee;
907 PRAGMA writable_schema=OFF;
908 SELECT name FROM sqlite_master
909 WHERE (name, sql) NOT IN (SELECT name, sql FROM schema_copy);
910 ROLLBACK;
911 } {t1}
912 do_execsql_test 23.10 {
913 DROP VIEW t2;
914 CREATE TRIGGER r3 AFTER INSERT ON t1 BEGIN
915 INSERT INTO t3(x,y) VALUES(new.a, new.b);
916 INSERT INTO t4(p) VALUES(new.c); -- no such table "t4"
917 END;
918 DELETE FROM schema_copy;
919 INSERT INTO schema_copy(name,sql) SELECT name, sql FROM sqlite_schema WHERE sql IS NOT NULL;
920 } {}
921 do_catchsql_test 23.11 {
922 ALTER TABLE t1 RENAME COLUMN e TO eeee;
923 } {1 {error in trigger r3: no such table: main.t3}}
924 do_execsql_test 23.12 {
925 SELECT name, sql FROM sqlite_master
926 EXCEPT SELECT name, sql FROM schema_copy;
927 } {}
928 do_execsql_test 23.13 {
929 BEGIN;
930 PRAGMA writable_schema=ON;
931 ALTER TABLE t1 RENAME COLUMN e TO eeee;
932 PRAGMA writable_schema=OFF;
933 SELECT name FROM sqlite_master
934 WHERE (name, sql) NOT IN (SELECT name, sql FROM schema_copy);
935 ROLLBACK;
936 } {t1}
937 do_execsql_test 23.20 {
938 CREATE TABLE t4(id INTEGER PRIMARY KEY, c1 INT, c2 INT);
939 CREATE VIEW t4v1 AS SELECT id, c1, c99 FROM t4;
940 DELETE FROM schema_copy;
941 INSERT INTO schema_copy SELECT name, sql FROM sqlite_schema;
942 BEGIN;
943 PRAGMA writable_schema=ON;
944 ALTER TABLE t4 RENAME to t4new;
945 SELECT name FROM sqlite_schema WHERE (name,sql) NOT IN (SELECT * FROM schema_copy);
946 ROLLBACK;
947 } {t4new}
948
949 #-------------------------------------------------------------------------
950 reset_db
951 do_execsql_test 24.0 {
952 CREATE TABLE t1(a PRIMARY KEY, b);
953 CREATE TABLE t2(a, b, c);
954 INSERT INTO t2 VALUES(1, 1, 1);
955 }
956
957 do_catchsql_test 24.1 {
958 PRAGMA foreign_keys = 1;
959 ALTER TABLE t2 ADD COLUMN d REFERENCES t1 DEFAULT 123;
960 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
961
962 finish_test