| blob | ddf16988246dd5a242ffbf31df742889a0a52930 |
1 # 2004 November 10
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 the ALTER TABLE statement.
13 #
14 # $Id: alter.test,v 1.32 2009/03/24 15:08:10 drh Exp $
15 #
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
21 ifcapable !altertable {
22 finish_test
23 return
24 }
26 #----------------------------------------------------------------------
27 # Test organization:
28 #
29 # alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables
30 # with implicit and explicit indices. These tests came from an earlier
31 # fork of SQLite that also supported ALTER TABLE.
32 # alter-1.8.*: Tests for ALTER TABLE when the table resides in an
33 # attached database.
34 # alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the
35 # table name and left parenthesis token. i.e:
36 # "CREATE TABLE abc (a, b, c);"
37 # alter-2.*: Test error conditions and messages.
38 # alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.
39 # alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.
40 # ...
41 # alter-12.*: Test ALTER TABLE on views.
42 #
44 # Create some tables to rename. Be sure to include some TEMP tables
45 # and some tables with odd names.
46 #
47 do_test alter-1.1 {
48 ifcapable tempdb {
49 set ::temp TEMP
50 } else {
51 set ::temp {}
52 }
53 execsql [subst -nocommands {
54 CREATE TABLE t1(a,b);
55 INSERT INTO t1 VALUES(1,2);
56 CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);
57 INSERT INTO [t1'x1] VALUES(3,4);
58 CREATE INDEX t1i1 ON T1(B);
59 CREATE INDEX t1i2 ON t1(a,b);
60 CREATE INDEX i3 ON [t1'x1](b,c);
61 CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);
62 CREATE INDEX i2 ON [temp table](f);
63 INSERT INTO [temp table] VALUES(5,6,7);
64 }]
65 execsql {
66 SELECT 't1', * FROM t1;
67 SELECT 't1''x1', * FROM "t1'x1";
68 SELECT * FROM [temp table];
69 }
70 } {t1 1 2 t1'x1 3 4 5 6 7}
71 do_test alter-1.2 {
72 execsql [subst {
73 CREATE $::temp TABLE objlist(type, name, tbl_name);
74 INSERT INTO objlist SELECT type, name, tbl_name
75 FROM sqlite_master WHERE NAME!='objlist';
76 }]
77 ifcapable tempdb {
78 execsql {
79 INSERT INTO objlist SELECT type, name, tbl_name
80 FROM sqlite_temp_master WHERE NAME!='objlist';
81 }
82 }
84 execsql {
85 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
86 }
87 } [list \
88 table t1 t1 \
89 index t1i1 t1 \
90 index t1i2 t1 \
91 table t1'x1 t1'x1 \
92 index i3 t1'x1 \
93 index {sqlite_autoindex_t1'x1_1} t1'x1 \
94 index {sqlite_autoindex_t1'x1_2} t1'x1 \
95 table {temp table} {temp table} \
96 index i2 {temp table} \
97 index {sqlite_autoindex_temp table_1} {temp table} \
98 ]
100 # Make some changes
101 #
102 integrity_check alter-1.3.0
103 do_test alter-1.3 {
104 execsql {
105 ALTER TABLE [T1] RENAME to [-t1-];
106 ALTER TABLE "t1'x1" RENAME TO T2;
107 ALTER TABLE [temp table] RENAME to TempTab;
108 }
109 } {}
110 integrity_check alter-1.3.1
111 do_test alter-1.4 {
112 execsql {
113 SELECT 't1', * FROM [-t1-];
114 SELECT 't2', * FROM t2;
115 SELECT * FROM temptab;
116 }
117 } {t1 1 2 t2 3 4 5 6 7}
118 do_test alter-1.5 {
119 execsql {
120 DELETE FROM objlist;
121 INSERT INTO objlist SELECT type, name, tbl_name
122 FROM sqlite_master WHERE NAME!='objlist';
123 }
124 catchsql {
125 INSERT INTO objlist SELECT type, name, tbl_name
126 FROM sqlite_temp_master WHERE NAME!='objlist';
127 }
128 execsql {
129 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
130 }
131 } [list \
132 table -t1- -t1- \
133 index t1i1 -t1- \
134 index t1i2 -t1- \
135 table T2 T2 \
136 index i3 T2 \
137 index {sqlite_autoindex_T2_1} T2 \
138 index {sqlite_autoindex_T2_2} T2 \
139 table {TempTab} {TempTab} \
140 index i2 {TempTab} \
141 index {sqlite_autoindex_TempTab_1} {TempTab} \
142 ]
144 # Make sure the changes persist after restarting the database.
145 # (The TEMP table will not persist, of course.)
146 #
147 ifcapable tempdb {
148 do_test alter-1.6 {
149 db close
150 sqlite3 db test.db
151 set DB [sqlite3_connection_pointer db]
152 execsql {
153 CREATE TEMP TABLE objlist(type, name, tbl_name);
154 INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;
155 INSERT INTO objlist
156 SELECT type, name, tbl_name FROM sqlite_temp_master
157 WHERE NAME!='objlist';
158 SELECT type, name, tbl_name FROM objlist
159 ORDER BY tbl_name, type desc, name;
160 }
161 } [list \
162 table -t1- -t1- \
163 index t1i1 -t1- \
164 index t1i2 -t1- \
165 table T2 T2 \
166 index i3 T2 \
167 index {sqlite_autoindex_T2_1} T2 \
168 index {sqlite_autoindex_T2_2} T2 \
169 ]
170 } else {
171 execsql {
172 DROP TABLE TempTab;
173 }
174 }
176 # Create bogus application-defined functions for functions used
177 # internally by ALTER TABLE, to ensure that ALTER TABLE falls back
178 # to the built-in functions.
179 #
180 proc failing_app_func {args} {error "bad function"}
181 do_test alter-1.7-prep {
182 db func substr failing_app_func
183 db func like failing_app_func
184 db func sqlite_rename_table failing_app_func
185 db func sqlite_rename_trigger failing_app_func
186 db func sqlite_rename_parent failing_app_func
187 catchsql {SELECT substr(name,1,3) FROM sqlite_master}
188 } {1 {bad function}}
190 # Make sure the ALTER TABLE statements work with the
191 # non-callback API
192 #
193 do_test alter-1.7 {
194 stepsql $DB {
195 ALTER TABLE [-t1-] RENAME to [*t1*];
196 ALTER TABLE T2 RENAME TO [<t2>];
197 }
198 execsql {
199 DELETE FROM objlist;
200 INSERT INTO objlist SELECT type, name, tbl_name
201 FROM sqlite_master WHERE NAME!='objlist';
202 }
203 catchsql {
204 INSERT INTO objlist SELECT type, name, tbl_name
205 FROM sqlite_temp_master WHERE NAME!='objlist';
206 }
207 execsql {
208 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
209 }
210 } [list \
211 table *t1* *t1* \
212 index t1i1 *t1* \
213 index t1i2 *t1* \
214 table <t2> <t2> \
215 index i3 <t2> \
216 index {sqlite_autoindex_<t2>_1} <t2> \
217 index {sqlite_autoindex_<t2>_2} <t2> \
218 ]
220 # Check that ALTER TABLE works on attached databases.
221 #
222 ifcapable attach {
223 do_test alter-1.8.1 {
224 forcedelete test2.db
225 forcedelete test2.db-journal
226 execsql {
227 ATTACH 'test2.db' AS aux;
228 }
229 } {}
230 do_test alter-1.8.2 {
231 execsql {
232 CREATE TABLE t4(a PRIMARY KEY, b, c);
233 CREATE TABLE aux.t4(a PRIMARY KEY, b, c);
234 CREATE INDEX i4 ON t4(b);
235 CREATE INDEX aux.i4 ON t4(b);
236 }
237 } {}
238 do_test alter-1.8.3 {
239 execsql {
240 INSERT INTO t4 VALUES('main', 'main', 'main');
241 INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');
242 SELECT * FROM t4 WHERE a = 'main';
243 }
244 } {main main main}
245 do_test alter-1.8.4 {
246 execsql {
247 ALTER TABLE t4 RENAME TO t5;
248 SELECT * FROM t4 WHERE a = 'aux';
249 }
250 } {aux aux aux}
251 do_test alter-1.8.5 {
252 execsql {
253 SELECT * FROM t5;
254 }
255 } {main main main}
256 do_test alter-1.8.6 {
257 execsql {
258 SELECT * FROM t5 WHERE b = 'main';
259 }
260 } {main main main}
261 do_test alter-1.8.7 {
262 execsql {
263 ALTER TABLE aux.t4 RENAME TO t5;
264 SELECT * FROM aux.t5 WHERE b = 'aux';
265 }
266 } {aux aux aux}
267 }
269 do_test alter-1.9.1 {
270 execsql {
271 CREATE TABLE tbl1 (a, b, c);
272 INSERT INTO tbl1 VALUES(1, 2, 3);
273 }
274 } {}
275 do_test alter-1.9.2 {
276 execsql {
277 SELECT * FROM tbl1;
278 }
279 } {1 2 3}
280 do_test alter-1.9.3 {
281 execsql {
282 ALTER TABLE tbl1 RENAME TO tbl2;
283 SELECT * FROM tbl2;
284 }
285 } {1 2 3}
286 do_test alter-1.9.4 {
287 execsql {
288 DROP TABLE tbl2;
289 }
290 } {}
292 # Test error messages
293 #
294 do_test alter-2.1 {
295 catchsql {
296 ALTER TABLE none RENAME TO hi;
297 }
298 } {1 {no such table: none}}
299 do_test alter-2.2 {
300 execsql {
301 CREATE TABLE t3(p,q,r);
302 }
303 catchsql {
304 ALTER TABLE [<t2>] RENAME TO t3;
305 }
306 } {1 {there is already another table or index with this name: t3}}
307 do_test alter-2.3 {
308 catchsql {
309 ALTER TABLE [<t2>] RENAME TO i3;
310 }
311 } {1 {there is already another table or index with this name: i3}}
312 do_test alter-2.4 {
313 catchsql {
314 ALTER TABLE SqLiTe_master RENAME TO master;
315 }
316 } {1 {table sqlite_master may not be altered}}
317 do_test alter-2.5 {
318 catchsql {
319 ALTER TABLE t3 RENAME TO sqlite_t3;
320 }
321 } {1 {object name reserved for internal use: sqlite_t3}}
322 do_test alter-2.6 {
323 catchsql {
324 ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN);
325 }
326 } {1 {near "(": syntax error}}
328 # If this compilation does not include triggers, omit the alter-3.* tests.
329 ifcapable trigger {
331 #-----------------------------------------------------------------------
332 # Tests alter-3.* test ALTER TABLE on tables that have triggers.
333 #
334 # alter-3.1.*: ALTER TABLE with triggers.
335 # alter-3.2.*: Test that the ON keyword cannot be used as a database,
336 # table or column name unquoted. This is done because part of the
337 # ALTER TABLE code (specifically the implementation of SQL function
338 # "sqlite_alter_trigger") will break in this case.
339 # alter-3.3.*: ALTER TABLE with TEMP triggers (todo).
340 #
342 # An SQL user-function for triggers to fire, so that we know they
343 # are working.
344 proc trigfunc {args} {
345 set ::TRIGGER $args
346 }
347 db func trigfunc trigfunc
349 do_test alter-3.1.0 {
350 execsql {
351 CREATE TABLE t6(a, b, c);
352 -- Different case for the table name in the trigger.
353 CREATE TRIGGER trig1 AFTER INSERT ON T6 BEGIN
354 SELECT trigfunc('trig1', new.a, new.b, new.c);
355 END;
356 }
357 } {}
358 do_test alter-3.1.1 {
359 execsql {
360 INSERT INTO t6 VALUES(1, 2, 3);
361 }
362 set ::TRIGGER
363 } {trig1 1 2 3}
364 do_test alter-3.1.2 {
365 execsql {
366 ALTER TABLE t6 RENAME TO t7;
367 INSERT INTO t7 VALUES(4, 5, 6);
368 }
369 set ::TRIGGER
370 } {trig1 4 5 6}
371 do_test alter-3.1.3 {
372 execsql {
373 DROP TRIGGER trig1;
374 }
375 } {}
376 do_test alter-3.1.4 {
377 execsql {
378 CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN
379 SELECT trigfunc('trig2', new.a, new.b, new.c);
380 END;
381 INSERT INTO t7 VALUES(1, 2, 3);
382 }
383 set ::TRIGGER
384 } {trig2 1 2 3}
385 do_test alter-3.1.5 {
386 execsql {
387 ALTER TABLE t7 RENAME TO t8;
388 INSERT INTO t8 VALUES(4, 5, 6);
389 }
390 set ::TRIGGER
391 } {trig2 4 5 6}
392 do_test alter-3.1.6 {
393 execsql {
394 DROP TRIGGER trig2;
395 }
396 } {}
397 do_test alter-3.1.7 {
398 execsql {
399 CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN
400 SELECT trigfunc('trig3', new.a, new.b, new.c);
401 END;
402 INSERT INTO t8 VALUES(1, 2, 3);
403 }
404 set ::TRIGGER
405 } {trig3 1 2 3}
406 do_test alter-3.1.8 {
407 execsql {
408 ALTER TABLE t8 RENAME TO t9;
409 INSERT INTO t9 VALUES(4, 5, 6);
410 }
411 set ::TRIGGER
412 } {trig3 4 5 6}
414 # Make sure "ON" cannot be used as a database, table or column name without
415 # quoting. Otherwise the sqlite_alter_trigger() function might not work.
416 forcedelete test3.db
417 forcedelete test3.db-journal
418 ifcapable attach {
419 do_test alter-3.2.1 {
420 catchsql {
421 ATTACH 'test3.db' AS ON;
422 }
423 } {1 {near "ON": syntax error}}
424 do_test alter-3.2.2 {
425 catchsql {
426 ATTACH 'test3.db' AS 'ON';
427 }
428 } {0 {}}
429 do_test alter-3.2.3 {
430 catchsql {
431 CREATE TABLE ON.t1(a, b, c);
432 }
433 } {1 {near "ON": syntax error}}
434 do_test alter-3.2.4 {
435 catchsql {
436 CREATE TABLE 'ON'.t1(a, b, c);
437 }
438 } {0 {}}
439 do_test alter-3.2.4 {
440 catchsql {
441 CREATE TABLE 'ON'.ON(a, b, c);
442 }
443 } {1 {near "ON": syntax error}}
444 do_test alter-3.2.5 {
445 catchsql {
446 CREATE TABLE 'ON'.'ON'(a, b, c);
447 }
448 } {0 {}}
449 }
450 do_test alter-3.2.6 {
451 catchsql {
452 CREATE TABLE t10(a, ON, c);
453 }
454 } {1 {near "ON": syntax error}}
455 do_test alter-3.2.7 {
456 catchsql {
457 CREATE TABLE t10(a, 'ON', c);
458 }
459 } {0 {}}
460 do_test alter-3.2.8 {
461 catchsql {
462 CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;
463 }
464 } {1 {near "ON": syntax error}}
465 ifcapable attach {
466 do_test alter-3.2.9 {
467 catchsql {
468 CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
469 }
470 } {0 {}}
471 }
472 do_test alter-3.2.10 {
473 execsql {
474 DROP TABLE t10;
475 }
476 } {}
478 do_test alter-3.3.1 {
479 execsql [subst {
480 CREATE TABLE tbl1(a, b, c);
481 CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
482 SELECT trigfunc('trig1', new.a, new.b, new.c);
483 END;
484 }]
485 } {}
486 do_test alter-3.3.2 {
487 execsql {
488 INSERT INTO tbl1 VALUES('a', 'b', 'c');
489 }
490 set ::TRIGGER
491 } {trig1 a b c}
492 do_test alter-3.3.3 {
493 execsql {
494 ALTER TABLE tbl1 RENAME TO tbl2;
495 INSERT INTO tbl2 VALUES('d', 'e', 'f');
496 }
497 set ::TRIGGER
498 } {trig1 d e f}
499 do_test alter-3.3.4 {
500 execsql [subst {
501 CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
502 SELECT trigfunc('trig2', new.a, new.b, new.c);
503 END;
504 }]
505 } {}
506 do_test alter-3.3.5 {
507 execsql {
508 ALTER TABLE tbl2 RENAME TO tbl3;
509 INSERT INTO tbl3 VALUES('g', 'h', 'i');
510 }
511 set ::TRIGGER
512 } {trig1 g h i}
513 do_test alter-3.3.6 {
514 execsql {
515 UPDATE tbl3 SET a = 'G' where a = 'g';
516 }
517 set ::TRIGGER
518 } {trig2 G h i}
519 do_test alter-3.3.7 {
520 execsql {
521 DROP TABLE tbl3;
522 }
523 } {}
524 ifcapable tempdb {
525 do_test alter-3.3.8 {
526 execsql {
527 SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
528 }
529 } {}
530 }
532 } ;# ifcapable trigger
534 # If the build does not include AUTOINCREMENT fields, omit alter-4.*.
535 ifcapable autoinc {
537 do_test alter-4.1 {
538 execsql {
539 CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);
540 INSERT INTO tbl1 VALUES(10);
541 }
542 } {}
543 do_test alter-4.2 {
544 execsql {
545 INSERT INTO tbl1 VALUES(NULL);
546 SELECT a FROM tbl1;
547 }
548 } {10 11}
549 do_test alter-4.3 {
550 execsql {
551 ALTER TABLE tbl1 RENAME TO tbl2;
552 DELETE FROM tbl2;
553 INSERT INTO tbl2 VALUES(NULL);
554 SELECT a FROM tbl2;
555 }
556 } {12}
557 do_test alter-4.4 {
558 execsql {
559 DROP TABLE tbl2;
560 }
561 } {}
563 } ;# ifcapable autoinc
565 # Test that it is Ok to execute an ALTER TABLE immediately after
566 # opening a database.
567 do_test alter-5.1 {
568 execsql {
569 CREATE TABLE tbl1(a, b, c);
570 INSERT INTO tbl1 VALUES('x', 'y', 'z');
571 }
572 } {}
573 do_test alter-5.2 {
574 sqlite3 db2 test.db
575 execsql {
576 ALTER TABLE tbl1 RENAME TO tbl2;
577 SELECT * FROM tbl2;
578 } db2
579 } {x y z}
580 do_test alter-5.3 {
581 db2 close
582 } {}
584 foreach tblname [execsql {
585 SELECT name FROM sqlite_master
586 WHERE type='table' AND name NOT GLOB 'sqlite*'
587 }] {
588 execsql "DROP TABLE \"$tblname\""
589 }
591 set ::tbl_name "abc\uABCDdef"
592 do_test alter-6.1 {
593 string length $::tbl_name
594 } {7}
595 do_test alter-6.2 {
596 execsql "
597 CREATE TABLE ${tbl_name}(a, b, c);
598 "
599 set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]
600 execsql "
601 SELECT sql FROM sqlite_master WHERE oid = $::oid;
602 "
603 } "{CREATE TABLE ${::tbl_name}(a, b, c)}"
604 execsql "
605 SELECT * FROM ${::tbl_name}
606 "
607 set ::tbl_name2 "abcXdef"
608 do_test alter-6.3 {
609 execsql "
610 ALTER TABLE $::tbl_name RENAME TO $::tbl_name2
611 "
612 execsql "
613 SELECT sql FROM sqlite_master WHERE oid = $::oid
614 "
615 } "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"
616 do_test alter-6.4 {
617 execsql "
618 ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name
619 "
620 execsql "
621 SELECT sql FROM sqlite_master WHERE oid = $::oid
622 "
623 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"
624 set ::col_name ghi\1234\jkl
625 do_test alter-6.5 {
626 execsql "
627 ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR
628 "
629 execsql "
630 SELECT sql FROM sqlite_master WHERE oid = $::oid
631 "
632 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"
633 set ::col_name2 B\3421\A
634 do_test alter-6.6 {
635 db close
636 sqlite3 db test.db
637 execsql "
638 ALTER TABLE $::tbl_name ADD COLUMN $::col_name2
639 "
640 execsql "
641 SELECT sql FROM sqlite_master WHERE oid = $::oid
642 "
643 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"
644 do_test alter-6.7 {
645 execsql "
646 INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);
647 SELECT $::col_name, $::col_name2 FROM $::tbl_name;
648 "
649 } {4 5}
651 # Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table
652 # that includes a COLLATE clause.
653 #
654 do_realnum_test alter-7.1 {
655 execsql {
656 CREATE TABLE t1(a TEXT COLLATE BINARY);
657 ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
658 INSERT INTO t1 VALUES(1,'-2');
659 INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');
660 SELECT typeof(a), a, typeof(b), b FROM t1;
661 }
662 } {text 1 integer -2 text 5.4e-08 real 5.4e-08}
664 # Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
665 # a default value that the default value is used by aggregate functions.
666 #
667 do_test alter-8.1 {
668 execsql {
669 CREATE TABLE t2(a INTEGER);
670 INSERT INTO t2 VALUES(1);
671 INSERT INTO t2 VALUES(1);
672 INSERT INTO t2 VALUES(2);
673 ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
674 SELECT sum(b) FROM t2;
675 }
676 } {27}
677 do_test alter-8.2 {
678 execsql {
679 SELECT a, sum(b) FROM t2 GROUP BY a;
680 }
681 } {1 18 2 9}
683 #--------------------------------------------------------------------------
684 # alter-9.X - Special test: Make sure the sqlite_rename_trigger() and
685 # rename_table() functions do not crash when handed bad input.
686 #
687 ifcapable trigger {
688 do_test alter-9.1 {
689 execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}
690 } {{}}
691 }
692 do_test alter-9.2 {
693 execsql {
694 SELECT SQLITE_RENAME_TABLE(0,0);
695 SELECT SQLITE_RENAME_TABLE(10,20);
696 SELECT SQLITE_RENAME_TABLE('foo', 'foo');
697 }
698 } {{} {} {}}
700 #------------------------------------------------------------------------
701 # alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters
702 # in the names.
703 #
704 do_test alter-10.1 {
705 execsql "CREATE TABLE xyz(x UNIQUE)"
706 execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"
707 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}
708 } [list xyz\u1234abc]
709 do_test alter-10.2 {
710 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}
711 } [list sqlite_autoindex_xyz\u1234abc_1]
712 do_test alter-10.3 {
713 execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"
714 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}
715 } [list xyzabc]
716 do_test alter-10.4 {
717 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}
718 } [list sqlite_autoindex_xyzabc_1]
720 do_test alter-11.1 {
721 sqlite3_exec db {CREATE TABLE t11(%c6%c6)}
722 execsql {
723 ALTER TABLE t11 ADD COLUMN abc;
724 }
725 catchsql {
726 ALTER TABLE t11 ADD COLUMN abc;
727 }
728 } {1 {duplicate column name: abc}}
729 set isutf16 [regexp 16 [db one {PRAGMA encoding}]]
730 if {!$isutf16} {
731 do_test alter-11.2 {
732 execsql {INSERT INTO t11 VALUES(1,2)}
733 sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}
734 } {0 {xyz abc 1 2}}
735 }
736 do_test alter-11.3 {
737 sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}
738 execsql {
739 ALTER TABLE t11b ADD COLUMN abc;
740 }
741 catchsql {
742 ALTER TABLE t11b ADD COLUMN abc;
743 }
744 } {1 {duplicate column name: abc}}
745 if {!$isutf16} {
746 do_test alter-11.4 {
747 execsql {INSERT INTO t11b VALUES(3,4)}
748 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}
749 } {0 {xyz abc 3 4}}
750 do_test alter-11.5 {
751 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}
752 } {0 {xyz abc 3 4}}
753 do_test alter-11.6 {
754 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}
755 } {0 {xyz abc 3 4}}
756 }
757 do_test alter-11.7 {
758 sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}
759 execsql {
760 ALTER TABLE t11c ADD COLUMN abc;
761 }
762 catchsql {
763 ALTER TABLE t11c ADD COLUMN abc;
764 }
765 } {1 {duplicate column name: abc}}
766 if {!$isutf16} {
767 do_test alter-11.8 {
768 execsql {INSERT INTO t11c VALUES(5,6)}
769 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}
770 } {0 {xyz abc 5 6}}
771 do_test alter-11.9 {
772 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}
773 } {0 {xyz abc 5 6}}
774 do_test alter-11.10 {
775 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}
776 } {0 {xyz abc 5 6}}
777 }
779 do_test alter-12.1 {
780 execsql {
781 CREATE TABLE t12(a, b, c);
782 CREATE VIEW v1 AS SELECT * FROM t12;
783 }
784 } {}
785 do_test alter-12.2 {
786 catchsql {
787 ALTER TABLE v1 RENAME TO v2;
788 }
789 } {1 {view v1 may not be altered}}
790 do_test alter-12.3 {
791 execsql { SELECT * FROM v1; }
792 } {}
793 do_test alter-12.4 {
794 db close
795 sqlite3 db test.db
796 execsql { SELECT * FROM v1; }
797 } {}
798 do_test alter-12.5 {
799 catchsql {
800 ALTER TABLE v1 ADD COLUMN new_column;
801 }
802 } {1 {Cannot add a column to a view}}
804 # Ticket #3102:
805 # Verify that comments do not interfere with the table rename
806 # algorithm.
807 #
808 do_test alter-13.1 {
809 execsql {
810 CREATE TABLE /* hi */ t3102a(x);
811 CREATE TABLE t3102b -- comment
812 (y);
813 CREATE INDEX t3102c ON t3102a(x);
814 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
815 }
816 } {t3102a t3102b t3102c}
817 do_test alter-13.2 {
818 execsql {
819 ALTER TABLE t3102a RENAME TO t3102a_rename;
820 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
821 }
822 } {t3102a_rename t3102b t3102c}
823 do_test alter-13.3 {
824 execsql {
825 ALTER TABLE t3102b RENAME TO t3102b_rename;
826 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
827 }
828 } {t3102a_rename t3102b_rename t3102c}
830 # Ticket #3651
831 do_test alter-14.1 {
832 catchsql {
833 CREATE TABLE t3651(a UNIQUE);
834 ALTER TABLE t3651 ADD COLUMN b UNIQUE;
835 }
836 } {1 {Cannot add a UNIQUE column}}
837 do_test alter-14.2 {
838 catchsql {
839 ALTER TABLE t3651 ADD COLUMN b PRIMARY KEY;
840 }
841 } {1 {Cannot add a PRIMARY KEY column}}
844 #-------------------------------------------------------------------------
845 # Test that it is not possible to use ALTER TABLE on any system table.
846 #
847 set system_table_list {1 sqlite_master}
848 catchsql ANALYZE
849 ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
850 ifcapable stat3 { lappend system_table_list 3 sqlite_stat3 }
851 ifcapable stat4 { lappend system_table_list 4 sqlite_stat4 }
853 foreach {tn tbl} $system_table_list {
854 do_test alter-15.$tn.1 {
855 catchsql "ALTER TABLE $tbl RENAME TO xyz"
856 } [list 1 "table $tbl may not be altered"]
858 do_test alter-15.$tn.2 {
859 catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
860 } [list 1 "table $tbl may not be altered"]
861 }
863 #------------------------------------------------------------------------
864 # Verify that ALTER TABLE works on tables with the WITHOUT rowid option.
865 #
866 do_execsql_test alter-16.1 {
867 CREATE TABLE t16a(a TEXT, b REAL, c INT, PRIMARY KEY(a,b)) WITHOUT rowid;
868 INSERT INTO t16a VALUES('abc',1.25,99);
869 ALTER TABLE t16a ADD COLUMN d TEXT DEFAULT 'xyzzy';
870 INSERT INTO t16a VALUES('cba',5.5,98,'fizzle');
871 SELECT * FROM t16a ORDER BY a;
872 } {abc 1.25 99 xyzzy cba 5.5 98 fizzle}
873 do_execsql_test alter-16.2 {
874 ALTER TABLE t16a RENAME TO t16a_rn;
875 SELECT * FROM t16a_rn ORDER BY a;
876 } {abc 1.25 99 xyzzy cba 5.5 98 fizzle}
878 #-------------------------------------------------------------------------
879 # Verify that NULL values into the internal-use-only sqlite_rename_*()
880 # functions do not cause problems.
881 #
882 do_execsql_test alter-17.1 {
883 SELECT sqlite_rename_table('CREATE TABLE xyz(a,b,c)','abc');
884 } {{CREATE TABLE "abc"(a,b,c)}}
885 do_execsql_test alter-17.2 {
886 SELECT sqlite_rename_table('CREATE TABLE xyz(a,b,c)',NULL);
887 } {{CREATE TABLE "(NULL)"(a,b,c)}}
888 do_execsql_test alter-17.3 {
889 SELECT sqlite_rename_table(NULL,'abc');
890 } {{}}
891 do_execsql_test alter-17.4 {
892 SELECT sqlite_rename_trigger('CREATE TRIGGER r1 ON xyz WHEN','abc');
893 } {{CREATE TRIGGER r1 ON "abc" WHEN}}
894 do_execsql_test alter-17.5 {
895 SELECT sqlite_rename_trigger('CREATE TRIGGER r1 ON xyz WHEN',NULL);
896 } {{CREATE TRIGGER r1 ON "(NULL)" WHEN}}
897 do_execsql_test alter-17.6 {
898 SELECT sqlite_rename_trigger(NULL,'abc');
899 } {{}}
900 do_execsql_test alter-17.7 {
901 SELECT sqlite_rename_parent('CREATE TABLE t1(a REFERENCES "xyzzy")',
902 'xyzzy','lmnop');
903 } {{CREATE TABLE t1(a REFERENCES "lmnop")}}
904 do_execsql_test alter-17.8 {
905 SELECT sqlite_rename_parent('CREATE TABLE t1(a REFERENCES "xyzzy")',
906 'xyzzy',NULL);
907 } {{CREATE TABLE t1(a REFERENCES "(NULL)")}}
908 do_execsql_test alter-17.9 {
909 SELECT sqlite_rename_parent('CREATE TABLE t1(a REFERENCES "xyzzy")',
910 NULL, 'lmnop');
911 } {{}}
912 do_execsql_test alter-17.10 {
913 SELECT sqlite_rename_parent(NULL,'abc','xyz');
914 } {{}}
916 finish_test