wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 #
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
21 ifcapable !altertable {
22 finish_test
23 return
24 }
25  
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 #
43  
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 }
83  
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 ]
99  
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 ]
143  
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 }
175  
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}}
189  
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 ]
219  
220 # Check that ALTER TABLE works on attached databases.
221 #
222 ifcapable attach {
223 do_test alter-1.8.1 {
224 file delete -force test2.db
225 file delete -force 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 }
268  
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 } {}
291  
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}}
327  
328 # If this compilation does not include triggers, omit the alter-3.* tests.
329 ifcapable trigger {
330  
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 #
341  
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
348  
349 do_test alter-3.1.0 {
350 execsql {
351 CREATE TABLE t6(a, b, c);
352 CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN
353 SELECT trigfunc('trig1', new.a, new.b, new.c);
354 END;
355 }
356 } {}
357 do_test alter-3.1.1 {
358 execsql {
359 INSERT INTO t6 VALUES(1, 2, 3);
360 }
361 set ::TRIGGER
362 } {trig1 1 2 3}
363 do_test alter-3.1.2 {
364 execsql {
365 ALTER TABLE t6 RENAME TO t7;
366 INSERT INTO t7 VALUES(4, 5, 6);
367 }
368 set ::TRIGGER
369 } {trig1 4 5 6}
370 do_test alter-3.1.3 {
371 execsql {
372 DROP TRIGGER trig1;
373 }
374 } {}
375 do_test alter-3.1.4 {
376 execsql {
377 CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN
378 SELECT trigfunc('trig2', new.a, new.b, new.c);
379 END;
380 INSERT INTO t7 VALUES(1, 2, 3);
381 }
382 set ::TRIGGER
383 } {trig2 1 2 3}
384 do_test alter-3.1.5 {
385 execsql {
386 ALTER TABLE t7 RENAME TO t8;
387 INSERT INTO t8 VALUES(4, 5, 6);
388 }
389 set ::TRIGGER
390 } {trig2 4 5 6}
391 do_test alter-3.1.6 {
392 execsql {
393 DROP TRIGGER trig2;
394 }
395 } {}
396 do_test alter-3.1.7 {
397 execsql {
398 CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN
399 SELECT trigfunc('trig3', new.a, new.b, new.c);
400 END;
401 INSERT INTO t8 VALUES(1, 2, 3);
402 }
403 set ::TRIGGER
404 } {trig3 1 2 3}
405 do_test alter-3.1.8 {
406 execsql {
407 ALTER TABLE t8 RENAME TO t9;
408 INSERT INTO t9 VALUES(4, 5, 6);
409 }
410 set ::TRIGGER
411 } {trig3 4 5 6}
412  
413 # Make sure "ON" cannot be used as a database, table or column name without
414 # quoting. Otherwise the sqlite_alter_trigger() function might not work.
415 file delete -force test3.db
416 file delete -force test3.db-journal
417 ifcapable attach {
418 do_test alter-3.2.1 {
419 catchsql {
420 ATTACH 'test3.db' AS ON;
421 }
422 } {1 {near "ON": syntax error}}
423 do_test alter-3.2.2 {
424 catchsql {
425 ATTACH 'test3.db' AS 'ON';
426 }
427 } {0 {}}
428 do_test alter-3.2.3 {
429 catchsql {
430 CREATE TABLE ON.t1(a, b, c);
431 }
432 } {1 {near "ON": syntax error}}
433 do_test alter-3.2.4 {
434 catchsql {
435 CREATE TABLE 'ON'.t1(a, b, c);
436 }
437 } {0 {}}
438 do_test alter-3.2.4 {
439 catchsql {
440 CREATE TABLE 'ON'.ON(a, b, c);
441 }
442 } {1 {near "ON": syntax error}}
443 do_test alter-3.2.5 {
444 catchsql {
445 CREATE TABLE 'ON'.'ON'(a, b, c);
446 }
447 } {0 {}}
448 }
449 do_test alter-3.2.6 {
450 catchsql {
451 CREATE TABLE t10(a, ON, c);
452 }
453 } {1 {near "ON": syntax error}}
454 do_test alter-3.2.7 {
455 catchsql {
456 CREATE TABLE t10(a, 'ON', c);
457 }
458 } {0 {}}
459 do_test alter-3.2.8 {
460 catchsql {
461 CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;
462 }
463 } {1 {near "ON": syntax error}}
464 ifcapable attach {
465 do_test alter-3.2.9 {
466 catchsql {
467 CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
468 }
469 } {0 {}}
470 }
471 do_test alter-3.2.10 {
472 execsql {
473 DROP TABLE t10;
474 }
475 } {}
476  
477 do_test alter-3.3.1 {
478 execsql [subst {
479 CREATE TABLE tbl1(a, b, c);
480 CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
481 SELECT trigfunc('trig1', new.a, new.b, new.c);
482 END;
483 }]
484 } {}
485 do_test alter-3.3.2 {
486 execsql {
487 INSERT INTO tbl1 VALUES('a', 'b', 'c');
488 }
489 set ::TRIGGER
490 } {trig1 a b c}
491 do_test alter-3.3.3 {
492 execsql {
493 ALTER TABLE tbl1 RENAME TO tbl2;
494 INSERT INTO tbl2 VALUES('d', 'e', 'f');
495 }
496 set ::TRIGGER
497 } {trig1 d e f}
498 do_test alter-3.3.4 {
499 execsql [subst {
500 CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
501 SELECT trigfunc('trig2', new.a, new.b, new.c);
502 END;
503 }]
504 } {}
505 do_test alter-3.3.5 {
506 execsql {
507 ALTER TABLE tbl2 RENAME TO tbl3;
508 INSERT INTO tbl3 VALUES('g', 'h', 'i');
509 }
510 set ::TRIGGER
511 } {trig1 g h i}
512 do_test alter-3.3.6 {
513 execsql {
514 UPDATE tbl3 SET a = 'G' where a = 'g';
515 }
516 set ::TRIGGER
517 } {trig2 G h i}
518 do_test alter-3.3.7 {
519 execsql {
520 DROP TABLE tbl3;
521 }
522 } {}
523 ifcapable tempdb {
524 do_test alter-3.3.8 {
525 execsql {
526 SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
527 }
528 } {}
529 }
530  
531 } ;# ifcapable trigger
532  
533 # If the build does not include AUTOINCREMENT fields, omit alter-4.*.
534 ifcapable autoinc {
535  
536 do_test alter-4.1 {
537 execsql {
538 CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT);
539 INSERT INTO tbl1 VALUES(10);
540 }
541 } {}
542 do_test alter-4.2 {
543 execsql {
544 INSERT INTO tbl1 VALUES(NULL);
545 SELECT a FROM tbl1;
546 }
547 } {10 11}
548 do_test alter-4.3 {
549 execsql {
550 ALTER TABLE tbl1 RENAME TO tbl2;
551 DELETE FROM tbl2;
552 INSERT INTO tbl2 VALUES(NULL);
553 SELECT a FROM tbl2;
554 }
555 } {12}
556 do_test alter-4.4 {
557 execsql {
558 DROP TABLE tbl2;
559 }
560 } {}
561  
562 } ;# ifcapable autoinc
563  
564 # Test that it is Ok to execute an ALTER TABLE immediately after
565 # opening a database.
566 do_test alter-5.1 {
567 execsql {
568 CREATE TABLE tbl1(a, b, c);
569 INSERT INTO tbl1 VALUES('x', 'y', 'z');
570 }
571 } {}
572 do_test alter-5.2 {
573 sqlite3 db2 test.db
574 execsql {
575 ALTER TABLE tbl1 RENAME TO tbl2;
576 SELECT * FROM tbl2;
577 } db2
578 } {x y z}
579 do_test alter-5.3 {
580 db2 close
581 } {}
582  
583 foreach tblname [execsql {
584 SELECT name FROM sqlite_master
585 WHERE type='table' AND name NOT GLOB 'sqlite*'
586 }] {
587 execsql "DROP TABLE \"$tblname\""
588 }
589  
590 set ::tbl_name "abc\uABCDdef"
591 do_test alter-6.1 {
592 string length $::tbl_name
593 } {7}
594 do_test alter-6.2 {
595 execsql "
596 CREATE TABLE ${tbl_name}(a, b, c);
597 "
598 set ::oid [execsql {SELECT max(oid) FROM sqlite_master}]
599 execsql "
600 SELECT sql FROM sqlite_master WHERE oid = $::oid;
601 "
602 } "{CREATE TABLE ${::tbl_name}(a, b, c)}"
603 execsql "
604 SELECT * FROM ${::tbl_name}
605 "
606 set ::tbl_name2 "abcXdef"
607 do_test alter-6.3 {
608 execsql "
609 ALTER TABLE $::tbl_name RENAME TO $::tbl_name2
610 "
611 execsql "
612 SELECT sql FROM sqlite_master WHERE oid = $::oid
613 "
614 } "{CREATE TABLE \"${::tbl_name2}\"(a, b, c)}"
615 do_test alter-6.4 {
616 execsql "
617 ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name
618 "
619 execsql "
620 SELECT sql FROM sqlite_master WHERE oid = $::oid
621 "
622 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c)}"
623 set ::col_name ghi\1234\jkl
624 do_test alter-6.5 {
625 execsql "
626 ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR
627 "
628 execsql "
629 SELECT sql FROM sqlite_master WHERE oid = $::oid
630 "
631 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR)}"
632 set ::col_name2 B\3421\A
633 do_test alter-6.6 {
634 db close
635 sqlite3 db test.db
636 execsql "
637 ALTER TABLE $::tbl_name ADD COLUMN $::col_name2
638 "
639 execsql "
640 SELECT sql FROM sqlite_master WHERE oid = $::oid
641 "
642 } "{CREATE TABLE \"${::tbl_name}\"(a, b, c, $::col_name VARCHAR, $::col_name2)}"
643 do_test alter-6.7 {
644 execsql "
645 INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5);
646 SELECT $::col_name, $::col_name2 FROM $::tbl_name;
647 "
648 } {4 5}
649  
650 # Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table
651 # that includes a COLLATE clause.
652 #
653 do_realnum_test alter-7.1 {
654 execsql {
655 CREATE TABLE t1(a TEXT COLLATE BINARY);
656 ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
657 INSERT INTO t1 VALUES(1,'-2');
658 INSERT INTO t1 VALUES(5.4e-08,'5.4e-08');
659 SELECT typeof(a), a, typeof(b), b FROM t1;
660 }
661 } {text 1 integer -2 text 5.4e-08 real 5.4e-08}
662  
663 # Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
664 # a default value that the default value is used by aggregate functions.
665 #
666 do_test alter-8.1 {
667 execsql {
668 CREATE TABLE t2(a INTEGER);
669 INSERT INTO t2 VALUES(1);
670 INSERT INTO t2 VALUES(1);
671 INSERT INTO t2 VALUES(2);
672 ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
673 SELECT sum(b) FROM t2;
674 }
675 } {27}
676 do_test alter-8.2 {
677 execsql {
678 SELECT a, sum(b) FROM t2 GROUP BY a;
679 }
680 } {1 18 2 9}
681  
682 #--------------------------------------------------------------------------
683 # alter-9.X - Special test: Make sure the sqlite_rename_trigger() and
684 # rename_table() functions do not crash when handed bad input.
685 #
686 ifcapable trigger {
687 do_test alter-9.1 {
688 execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)}
689 } {{}}
690 }
691 do_test alter-9.2 {
692 execsql {
693 SELECT SQLITE_RENAME_TABLE(0,0);
694 SELECT SQLITE_RENAME_TABLE(10,20);
695 SELECT SQLITE_RENAME_TABLE('foo', 'foo');
696 }
697 } {{} {} {}}
698  
699 #------------------------------------------------------------------------
700 # alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters
701 # in the names.
702 #
703 do_test alter-10.1 {
704 execsql "CREATE TABLE xyz(x UNIQUE)"
705 execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc"
706 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}
707 } [list xyz\u1234abc]
708 do_test alter-10.2 {
709 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}
710 } [list sqlite_autoindex_xyz\u1234abc_1]
711 do_test alter-10.3 {
712 execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc"
713 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'xyz*'}
714 } [list xyzabc]
715 do_test alter-10.4 {
716 execsql {SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_autoindex*'}
717 } [list sqlite_autoindex_xyzabc_1]
718  
719 do_test alter-11.1 {
720 sqlite3_exec db {CREATE TABLE t11(%c6%c6)}
721 execsql {
722 ALTER TABLE t11 ADD COLUMN abc;
723 }
724 catchsql {
725 ALTER TABLE t11 ADD COLUMN abc;
726 }
727 } {1 {duplicate column name: abc}}
728 set isutf16 [regexp 16 [db one {PRAGMA encoding}]]
729 if {!$isutf16} {
730 do_test alter-11.2 {
731 execsql {INSERT INTO t11 VALUES(1,2)}
732 sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11}
733 } {0 {xyz abc 1 2}}
734 }
735 do_test alter-11.3 {
736 sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)}
737 execsql {
738 ALTER TABLE t11b ADD COLUMN abc;
739 }
740 catchsql {
741 ALTER TABLE t11b ADD COLUMN abc;
742 }
743 } {1 {duplicate column name: abc}}
744 if {!$isutf16} {
745 do_test alter-11.4 {
746 execsql {INSERT INTO t11b VALUES(3,4)}
747 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b}
748 } {0 {xyz abc 3 4}}
749 do_test alter-11.5 {
750 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b}
751 } {0 {xyz abc 3 4}}
752 do_test alter-11.6 {
753 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b}
754 } {0 {xyz abc 3 4}}
755 }
756 do_test alter-11.7 {
757 sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)}
758 execsql {
759 ALTER TABLE t11c ADD COLUMN abc;
760 }
761 catchsql {
762 ALTER TABLE t11c ADD COLUMN abc;
763 }
764 } {1 {duplicate column name: abc}}
765 if {!$isutf16} {
766 do_test alter-11.8 {
767 execsql {INSERT INTO t11c VALUES(5,6)}
768 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c}
769 } {0 {xyz abc 5 6}}
770 do_test alter-11.9 {
771 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c}
772 } {0 {xyz abc 5 6}}
773 do_test alter-11.10 {
774 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c}
775 } {0 {xyz abc 5 6}}
776 }
777  
778 do_test alter-12.1 {
779 execsql {
780 CREATE TABLE t12(a, b, c);
781 CREATE VIEW v1 AS SELECT * FROM t12;
782 }
783 } {}
784 do_test alter-12.2 {
785 catchsql {
786 ALTER TABLE v1 RENAME TO v2;
787 }
788 } {1 {view v1 may not be altered}}
789 do_test alter-12.3 {
790 execsql { SELECT * FROM v1; }
791 } {}
792 do_test alter-12.4 {
793 db close
794 sqlite3 db test.db
795 execsql { SELECT * FROM v1; }
796 } {}
797 do_test alter-12.5 {
798 catchsql {
799 ALTER TABLE v1 ADD COLUMN new_column;
800 }
801 } {1 {Cannot add a column to a view}}
802  
803 # Ticket #3102:
804 # Verify that comments do not interfere with the table rename
805 # algorithm.
806 #
807 do_test alter-13.1 {
808 execsql {
809 CREATE TABLE /* hi */ t3102a(x);
810 CREATE TABLE t3102b -- comment
811 (y);
812 CREATE INDEX t3102c ON t3102a(x);
813 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
814 }
815 } {t3102a t3102b t3102c}
816 do_test alter-13.2 {
817 execsql {
818 ALTER TABLE t3102a RENAME TO t3102a_rename;
819 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
820 }
821 } {t3102a_rename t3102b t3102c}
822 do_test alter-13.3 {
823 execsql {
824 ALTER TABLE t3102b RENAME TO t3102b_rename;
825 SELECT name FROM sqlite_master WHERE name GLOB 't3102*' ORDER BY 1;
826 }
827 } {t3102a_rename t3102b_rename t3102c}
828  
829 # Ticket #3651
830 do_test alter-14.1 {
831 catchsql {
832 CREATE TABLE t3651(a UNIQUE);
833 ALTER TABLE t3651 ADD COLUMN b UNIQUE;
834 }
835 } {1 {Cannot add a UNIQUE column}}
836 do_test alter-14.2 {
837 catchsql {
838 ALTER TABLE t3651 ADD COLUMN b PRIMARY KEY;
839 }
840 } {1 {Cannot add a PRIMARY KEY column}}
841  
842  
843 #-------------------------------------------------------------------------
844 # Test that it is not possible to use ALTER TABLE on any system table.
845 #
846 set system_table_list {1 sqlite_master}
847 catchsql ANALYZE
848 ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
849 ifcapable stat2 { lappend system_table_list 3 sqlite_stat2 }
850  
851 foreach {tn tbl} $system_table_list {
852 do_test alter-15.$tn.1 {
853 catchsql "ALTER TABLE $tbl RENAME TO xyz"
854 } [list 1 "table $tbl may not be altered"]
855  
856 do_test alter-15.$tn.2 {
857 catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
858 } [list 1 "table $tbl may not be altered"]
859 }
860  
861  
862 finish_test