wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |