wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15 |
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 file is testing the CREATE TABLE statement. |
||
13 | # |
||
14 | # $Id: table.test,v 1.53 2009/06/05 17:09:12 drh Exp $ |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | # Create a basic table and verify it is added to sqlite_master |
||
20 | # |
||
21 | do_test table-1.1 { |
||
22 | execsql { |
||
23 | CREATE TABLE test1 ( |
||
24 | one varchar(10), |
||
25 | two text |
||
26 | ) |
||
27 | } |
||
28 | execsql { |
||
29 | SELECT sql FROM sqlite_master WHERE type!='meta' |
||
30 | } |
||
31 | } {{CREATE TABLE test1 ( |
||
32 | one varchar(10), |
||
33 | two text |
||
34 | )}} |
||
35 | |||
36 | |||
37 | # Verify the other fields of the sqlite_master file. |
||
38 | # |
||
39 | do_test table-1.3 { |
||
40 | execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'} |
||
41 | } {test1 test1 table} |
||
42 | |||
43 | # Close and reopen the database. Verify that everything is |
||
44 | # still the same. |
||
45 | # |
||
46 | do_test table-1.4 { |
||
47 | db close |
||
48 | sqlite3 db test.db |
||
49 | execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'} |
||
50 | } {test1 test1 table} |
||
51 | |||
52 | # Drop the database and make sure it disappears. |
||
53 | # |
||
54 | do_test table-1.5 { |
||
55 | execsql {DROP TABLE test1} |
||
56 | execsql {SELECT * FROM sqlite_master WHERE type!='meta'} |
||
57 | } {} |
||
58 | |||
59 | # Close and reopen the database. Verify that the table is |
||
60 | # still gone. |
||
61 | # |
||
62 | do_test table-1.6 { |
||
63 | db close |
||
64 | sqlite3 db test.db |
||
65 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
||
66 | } {} |
||
67 | |||
68 | # Repeat the above steps, but this time quote the table name. |
||
69 | # |
||
70 | do_test table-1.10 { |
||
71 | execsql {CREATE TABLE "create" (f1 int)} |
||
72 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
||
73 | } {create} |
||
74 | do_test table-1.11 { |
||
75 | execsql {DROP TABLE "create"} |
||
76 | execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} |
||
77 | } {} |
||
78 | do_test table-1.12 { |
||
79 | execsql {CREATE TABLE test1("f1 ho" int)} |
||
80 | execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'} |
||
81 | } {test1} |
||
82 | do_test table-1.13 { |
||
83 | execsql {DROP TABLE "TEST1"} |
||
84 | execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} |
||
85 | } {} |
||
86 | |||
87 | |||
88 | |||
89 | # Verify that we cannot make two tables with the same name |
||
90 | # |
||
91 | do_test table-2.1 { |
||
92 | execsql {CREATE TABLE TEST2(one text)} |
||
93 | catchsql {CREATE TABLE test2(two text default 'hi')} |
||
94 | } {1 {table test2 already exists}} |
||
95 | do_test table-2.1.1 { |
||
96 | catchsql {CREATE TABLE "test2" (two)} |
||
97 | } {1 {table "test2" already exists}} |
||
98 | do_test table-2.1b { |
||
99 | set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] |
||
100 | lappend v $msg |
||
101 | } {1 {object name reserved for internal use: sqlite_master}} |
||
102 | do_test table-2.1c { |
||
103 | db close |
||
104 | sqlite3 db test.db |
||
105 | set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] |
||
106 | lappend v $msg |
||
107 | } {1 {object name reserved for internal use: sqlite_master}} |
||
108 | do_test table-2.1d { |
||
109 | catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)} |
||
110 | } {0 {}} |
||
111 | do_test table-2.1e { |
||
112 | catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)} |
||
113 | } {0 {}} |
||
114 | do_test table-2.1f { |
||
115 | execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'} |
||
116 | } {} |
||
117 | |||
118 | # Verify that we cannot make a table with the same name as an index |
||
119 | # |
||
120 | do_test table-2.2a { |
||
121 | execsql {CREATE TABLE test2(one text)} |
||
122 | execsql {CREATE INDEX test3 ON test2(one)} |
||
123 | catchsql {CREATE TABLE test3(two text)} |
||
124 | } {1 {there is already an index named test3}} |
||
125 | do_test table-2.2b { |
||
126 | db close |
||
127 | sqlite3 db test.db |
||
128 | set v [catch {execsql {CREATE TABLE test3(two text)}} msg] |
||
129 | lappend v $msg |
||
130 | } {1 {there is already an index named test3}} |
||
131 | do_test table-2.2c { |
||
132 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
133 | } {test2 test3} |
||
134 | do_test table-2.2d { |
||
135 | execsql {DROP INDEX test3} |
||
136 | set v [catch {execsql {CREATE TABLE test3(two text)}} msg] |
||
137 | lappend v $msg |
||
138 | } {0 {}} |
||
139 | do_test table-2.2e { |
||
140 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
141 | } {test2 test3} |
||
142 | do_test table-2.2f { |
||
143 | execsql {DROP TABLE test2; DROP TABLE test3} |
||
144 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
145 | } {} |
||
146 | |||
147 | # Create a table with many field names |
||
148 | # |
||
149 | set big_table \ |
||
150 | {CREATE TABLE big( |
||
151 | f1 varchar(20), |
||
152 | f2 char(10), |
||
153 | f3 varchar(30) primary key, |
||
154 | f4 text, |
||
155 | f5 text, |
||
156 | f6 text, |
||
157 | f7 text, |
||
158 | f8 text, |
||
159 | f9 text, |
||
160 | f10 text, |
||
161 | f11 text, |
||
162 | f12 text, |
||
163 | f13 text, |
||
164 | f14 text, |
||
165 | f15 text, |
||
166 | f16 text, |
||
167 | f17 text, |
||
168 | f18 text, |
||
169 | f19 text, |
||
170 | f20 text |
||
171 | )} |
||
172 | do_test table-3.1 { |
||
173 | execsql $big_table |
||
174 | execsql {SELECT sql FROM sqlite_master WHERE type=='table'} |
||
175 | } \{$big_table\} |
||
176 | do_test table-3.2 { |
||
177 | set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg] |
||
178 | lappend v $msg |
||
179 | } {1 {table BIG already exists}} |
||
180 | do_test table-3.3 { |
||
181 | set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg] |
||
182 | lappend v $msg |
||
183 | } {1 {table biG already exists}} |
||
184 | do_test table-3.4 { |
||
185 | set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg] |
||
186 | lappend v $msg |
||
187 | } {1 {table bIg already exists}} |
||
188 | do_test table-3.5 { |
||
189 | db close |
||
190 | sqlite3 db test.db |
||
191 | set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg] |
||
192 | lappend v $msg |
||
193 | } {1 {table Big already exists}} |
||
194 | do_test table-3.6 { |
||
195 | execsql {DROP TABLE big} |
||
196 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
||
197 | } {} |
||
198 | |||
199 | # Try creating large numbers of tables |
||
200 | # |
||
201 | set r {} |
||
202 | for {set i 1} {$i<=100} {incr i} { |
||
203 | lappend r [format test%03d $i] |
||
204 | } |
||
205 | do_test table-4.1 { |
||
206 | for {set i 1} {$i<=100} {incr i} { |
||
207 | set sql "CREATE TABLE [format test%03d $i] (" |
||
208 | for {set k 1} {$k<$i} {incr k} { |
||
209 | append sql "field$k text," |
||
210 | } |
||
211 | append sql "last_field text)" |
||
212 | execsql $sql |
||
213 | } |
||
214 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
215 | } $r |
||
216 | do_test table-4.1b { |
||
217 | db close |
||
218 | sqlite3 db test.db |
||
219 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
220 | } $r |
||
221 | |||
222 | # Drop the even numbered tables |
||
223 | # |
||
224 | set r {} |
||
225 | for {set i 1} {$i<=100} {incr i 2} { |
||
226 | lappend r [format test%03d $i] |
||
227 | } |
||
228 | do_test table-4.2 { |
||
229 | for {set i 2} {$i<=100} {incr i 2} { |
||
230 | # if {$i==38} {execsql {pragma vdbe_trace=on}} |
||
231 | set sql "DROP TABLE [format TEST%03d $i]" |
||
232 | execsql $sql |
||
233 | } |
||
234 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
235 | } $r |
||
236 | #exit |
||
237 | |||
238 | # Drop the odd number tables |
||
239 | # |
||
240 | do_test table-4.3 { |
||
241 | for {set i 1} {$i<=100} {incr i 2} { |
||
242 | set sql "DROP TABLE [format test%03d $i]" |
||
243 | execsql $sql |
||
244 | } |
||
245 | execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} |
||
246 | } {} |
||
247 | |||
248 | # Try to drop a table that does not exist |
||
249 | # |
||
250 | do_test table-5.1.1 { |
||
251 | catchsql {DROP TABLE test009} |
||
252 | } {1 {no such table: test009}} |
||
253 | do_test table-5.1.2 { |
||
254 | catchsql {DROP TABLE IF EXISTS test009} |
||
255 | } {0 {}} |
||
256 | |||
257 | # Try to drop sqlite_master |
||
258 | # |
||
259 | do_test table-5.2 { |
||
260 | catchsql {DROP TABLE IF EXISTS sqlite_master} |
||
261 | } {1 {table sqlite_master may not be dropped}} |
||
262 | |||
263 | # Make sure an EXPLAIN does not really create a new table |
||
264 | # |
||
265 | do_test table-5.3 { |
||
266 | ifcapable {explain} { |
||
267 | execsql {EXPLAIN CREATE TABLE test1(f1 int)} |
||
268 | } |
||
269 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
||
270 | } {} |
||
271 | |||
272 | # Make sure an EXPLAIN does not really drop an existing table |
||
273 | # |
||
274 | do_test table-5.4 { |
||
275 | execsql {CREATE TABLE test1(f1 int)} |
||
276 | ifcapable {explain} { |
||
277 | execsql {EXPLAIN DROP TABLE test1} |
||
278 | } |
||
279 | execsql {SELECT name FROM sqlite_master WHERE type!='meta'} |
||
280 | } {test1} |
||
281 | |||
282 | # Create a table with a goofy name |
||
283 | # |
||
284 | #do_test table-6.1 { |
||
285 | # execsql {CREATE TABLE 'Spaces In This Name!'(x int)} |
||
286 | # execsql {INSERT INTO 'spaces in this name!' VALUES(1)} |
||
287 | # set list [glob -nocomplain testdb/spaces*.tbl] |
||
288 | #} {testdb/spaces+in+this+name+.tbl} |
||
289 | |||
290 | # Try using keywords as table names or column names. |
||
291 | # |
||
292 | do_test table-7.1 { |
||
293 | set v [catch {execsql { |
||
294 | CREATE TABLE weird( |
||
295 | desc text, |
||
296 | asc text, |
||
297 | key int, |
||
298 | [14_vac] boolean, |
||
299 | fuzzy_dog_12 varchar(10), |
||
300 | begin blob, |
||
301 | end clob |
||
302 | ) |
||
303 | }} msg] |
||
304 | lappend v $msg |
||
305 | } {0 {}} |
||
306 | do_test table-7.2 { |
||
307 | execsql { |
||
308 | INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all'); |
||
309 | SELECT * FROM weird; |
||
310 | } |
||
311 | } {a b 9 0 xyz hi y'all} |
||
312 | do_test table-7.3 { |
||
313 | execsql2 { |
||
314 | SELECT * FROM weird; |
||
315 | } |
||
316 | } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} |
||
317 | do_test table-7.3 { |
||
318 | execsql { |
||
319 | CREATE TABLE savepoint(release); |
||
320 | INSERT INTO savepoint(release) VALUES(10); |
||
321 | UPDATE savepoint SET release = 5; |
||
322 | SELECT release FROM savepoint; |
||
323 | } |
||
324 | } {5} |
||
325 | |||
326 | # Try out the CREATE TABLE AS syntax |
||
327 | # |
||
328 | do_test table-8.1 { |
||
329 | execsql2 { |
||
330 | CREATE TABLE t2 AS SELECT * FROM weird; |
||
331 | SELECT * FROM t2; |
||
332 | } |
||
333 | } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} |
||
334 | do_test table-8.1.1 { |
||
335 | execsql { |
||
336 | SELECT sql FROM sqlite_master WHERE name='t2'; |
||
337 | } |
||
338 | } {{CREATE TABLE t2( |
||
339 | "desc" TEXT, |
||
340 | "asc" TEXT, |
||
341 | "key" INT, |
||
342 | "14_vac" NUM, |
||
343 | fuzzy_dog_12 TEXT, |
||
344 | "begin", |
||
345 | "end" TEXT |
||
346 | )}} |
||
347 | do_test table-8.2 { |
||
348 | execsql { |
||
349 | CREATE TABLE "t3""xyz"(a,b,c); |
||
350 | INSERT INTO [t3"xyz] VALUES(1,2,3); |
||
351 | SELECT * FROM [t3"xyz]; |
||
352 | } |
||
353 | } {1 2 3} |
||
354 | do_test table-8.3 { |
||
355 | execsql2 { |
||
356 | CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz]; |
||
357 | SELECT * FROM [t4"abc]; |
||
358 | } |
||
359 | } {cnt 1 max(b+c) 5} |
||
360 | |||
361 | # Update for v3: The declaration type of anything except a column is now a |
||
362 | # NULL pointer, so the created table has no column types. (Changed result |
||
363 | # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}). |
||
364 | do_test table-8.3.1 { |
||
365 | execsql { |
||
366 | SELECT sql FROM sqlite_master WHERE name='t4"abc' |
||
367 | } |
||
368 | } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}} |
||
369 | |||
370 | ifcapable tempdb { |
||
371 | do_test table-8.4 { |
||
372 | execsql2 { |
||
373 | CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz]; |
||
374 | SELECT * FROM t5; |
||
375 | } |
||
376 | } {y'all 1} |
||
377 | } |
||
378 | |||
379 | do_test table-8.5 { |
||
380 | db close |
||
381 | sqlite3 db test.db |
||
382 | execsql2 { |
||
383 | SELECT * FROM [t4"abc]; |
||
384 | } |
||
385 | } {cnt 1 max(b+c) 5} |
||
386 | do_test table-8.6 { |
||
387 | execsql2 { |
||
388 | SELECT * FROM t2; |
||
389 | } |
||
390 | } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} |
||
391 | do_test table-8.7 { |
||
392 | catchsql { |
||
393 | SELECT * FROM t5; |
||
394 | } |
||
395 | } {1 {no such table: t5}} |
||
396 | do_test table-8.8 { |
||
397 | catchsql { |
||
398 | CREATE TABLE t5 AS SELECT * FROM no_such_table; |
||
399 | } |
||
400 | } {1 {no such table: no_such_table}} |
||
401 | |||
402 | do_test table-8.9 { |
||
403 | execsql { |
||
404 | CREATE TABLE t10("col.1" [char.3]); |
||
405 | CREATE TABLE t11 AS SELECT * FROM t10; |
||
406 | SELECT sql FROM sqlite_master WHERE name = 't11'; |
||
407 | } |
||
408 | } {{CREATE TABLE t11("col.1" TEXT)}} |
||
409 | do_test table-8.10 { |
||
410 | execsql { |
||
411 | CREATE TABLE t12( |
||
412 | a INTEGER, |
||
413 | b VARCHAR(10), |
||
414 | c VARCHAR(1,10), |
||
415 | d VARCHAR(+1,-10), |
||
416 | e VARCHAR (+1,-10), |
||
417 | f "VARCHAR (+1,-10, 5)", |
||
418 | g BIG INTEGER |
||
419 | ); |
||
420 | CREATE TABLE t13 AS SELECT * FROM t12; |
||
421 | SELECT sql FROM sqlite_master WHERE name = 't13'; |
||
422 | } |
||
423 | } {{CREATE TABLE t13( |
||
424 | a INT, |
||
425 | b TEXT, |
||
426 | c TEXT, |
||
427 | d TEXT, |
||
428 | e TEXT, |
||
429 | f TEXT, |
||
430 | g INT |
||
431 | )}} |
||
432 | |||
433 | # Make sure we cannot have duplicate column names within a table. |
||
434 | # |
||
435 | do_test table-9.1 { |
||
436 | catchsql { |
||
437 | CREATE TABLE t6(a,b,a); |
||
438 | } |
||
439 | } {1 {duplicate column name: a}} |
||
440 | do_test table-9.2 { |
||
441 | catchsql { |
||
442 | CREATE TABLE t6(a varchar(100), b blob, a integer); |
||
443 | } |
||
444 | } {1 {duplicate column name: a}} |
||
445 | |||
446 | # Check the foreign key syntax. |
||
447 | # |
||
448 | ifcapable {foreignkey} { |
||
449 | do_test table-10.1 { |
||
450 | catchsql { |
||
451 | CREATE TABLE t6(a REFERENCES t4(a) NOT NULL); |
||
452 | INSERT INTO t6 VALUES(NULL); |
||
453 | } |
||
454 | } {1 {t6.a may not be NULL}} |
||
455 | do_test table-10.2 { |
||
456 | catchsql { |
||
457 | DROP TABLE t6; |
||
458 | CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL); |
||
459 | } |
||
460 | } {0 {}} |
||
461 | do_test table-10.3 { |
||
462 | catchsql { |
||
463 | DROP TABLE t6; |
||
464 | CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL); |
||
465 | } |
||
466 | } {0 {}} |
||
467 | do_test table-10.4 { |
||
468 | catchsql { |
||
469 | DROP TABLE t6; |
||
470 | CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1); |
||
471 | } |
||
472 | } {0 {}} |
||
473 | do_test table-10.5 { |
||
474 | catchsql { |
||
475 | DROP TABLE t6; |
||
476 | CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE); |
||
477 | } |
||
478 | } {0 {}} |
||
479 | do_test table-10.6 { |
||
480 | catchsql { |
||
481 | DROP TABLE t6; |
||
482 | CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED); |
||
483 | } |
||
484 | } {0 {}} |
||
485 | do_test table-10.7 { |
||
486 | catchsql { |
||
487 | DROP TABLE t6; |
||
488 | CREATE TABLE t6(a, |
||
489 | FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED |
||
490 | ); |
||
491 | } |
||
492 | } {0 {}} |
||
493 | do_test table-10.8 { |
||
494 | catchsql { |
||
495 | DROP TABLE t6; |
||
496 | CREATE TABLE t6(a,b,c, |
||
497 | FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL |
||
498 | ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED |
||
499 | ); |
||
500 | } |
||
501 | } {0 {}} |
||
502 | do_test table-10.9 { |
||
503 | catchsql { |
||
504 | DROP TABLE t6; |
||
505 | CREATE TABLE t6(a,b,c, |
||
506 | FOREIGN KEY (b,c) REFERENCES t4(x) |
||
507 | ); |
||
508 | } |
||
509 | } {1 {number of columns in foreign key does not match the number of columns in the referenced table}} |
||
510 | do_test table-10.10 { |
||
511 | catchsql {DROP TABLE t6} |
||
512 | catchsql { |
||
513 | CREATE TABLE t6(a,b,c, |
||
514 | FOREIGN KEY (b,c) REFERENCES t4(x,y,z) |
||
515 | ); |
||
516 | } |
||
517 | } {1 {number of columns in foreign key does not match the number of columns in the referenced table}} |
||
518 | do_test table-10.11 { |
||
519 | catchsql {DROP TABLE t6} |
||
520 | catchsql { |
||
521 | CREATE TABLE t6(a,b, c REFERENCES t4(x,y)); |
||
522 | } |
||
523 | } {1 {foreign key on c should reference only one column of table t4}} |
||
524 | do_test table-10.12 { |
||
525 | catchsql {DROP TABLE t6} |
||
526 | catchsql { |
||
527 | CREATE TABLE t6(a,b,c, |
||
528 | FOREIGN KEY (b,x) REFERENCES t4(x,y) |
||
529 | ); |
||
530 | } |
||
531 | } {1 {unknown column "x" in foreign key definition}} |
||
532 | do_test table-10.13 { |
||
533 | catchsql {DROP TABLE t6} |
||
534 | catchsql { |
||
535 | CREATE TABLE t6(a,b,c, |
||
536 | FOREIGN KEY (x,b) REFERENCES t4(x,y) |
||
537 | ); |
||
538 | } |
||
539 | } {1 {unknown column "x" in foreign key definition}} |
||
540 | } ;# endif foreignkey |
||
541 | |||
542 | # Test for the "typeof" function. More tests for the |
||
543 | # typeof() function are found in bind.test and types.test. |
||
544 | # |
||
545 | do_test table-11.1 { |
||
546 | execsql { |
||
547 | CREATE TABLE t7( |
||
548 | a integer primary key, |
||
549 | b number(5,10), |
||
550 | c character varying (8), |
||
551 | d VARCHAR(9), |
||
552 | e clob, |
||
553 | f BLOB, |
||
554 | g Text, |
||
555 | h |
||
556 | ); |
||
557 | INSERT INTO t7(a) VALUES(1); |
||
558 | SELECT typeof(a), typeof(b), typeof(c), typeof(d), |
||
559 | typeof(e), typeof(f), typeof(g), typeof(h) |
||
560 | FROM t7 LIMIT 1; |
||
561 | } |
||
562 | } {integer null null null null null null null} |
||
563 | do_test table-11.2 { |
||
564 | execsql { |
||
565 | SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d) |
||
566 | FROM t7 LIMIT 1; |
||
567 | } |
||
568 | } {null null null null} |
||
569 | |||
570 | # Test that when creating a table using CREATE TABLE AS, column types are |
||
571 | # assigned correctly for (SELECT ...) and 'x AS y' expressions. |
||
572 | do_test table-12.1 { |
||
573 | ifcapable subquery { |
||
574 | execsql { |
||
575 | CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7; |
||
576 | } |
||
577 | } else { |
||
578 | execsql { |
||
579 | CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7; |
||
580 | } |
||
581 | } |
||
582 | } {} |
||
583 | do_test table-12.2 { |
||
584 | execsql { |
||
585 | SELECT sql FROM sqlite_master WHERE tbl_name = 't8' |
||
586 | } |
||
587 | } {{CREATE TABLE t8(b NUM,h,i INT,j)}} |
||
588 | |||
589 | #-------------------------------------------------------------------- |
||
590 | # Test cases table-13.* |
||
591 | # |
||
592 | # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE |
||
593 | # and CURRENT_TIMESTAMP. |
||
594 | # |
||
595 | do_test table-13.1 { |
||
596 | execsql { |
||
597 | CREATE TABLE tablet8( |
||
598 | a integer primary key, |
||
599 | tm text DEFAULT CURRENT_TIME, |
||
600 | dt text DEFAULT CURRENT_DATE, |
||
601 | dttm text DEFAULT CURRENT_TIMESTAMP |
||
602 | ); |
||
603 | SELECT * FROM tablet8; |
||
604 | } |
||
605 | } {} |
||
606 | set i 0 |
||
607 | unset -nocomplain date time seconds |
||
608 | foreach {date time seconds} { |
||
609 | 1976-07-04 12:00:00 205329600 |
||
610 | 1994-04-16 14:00:00 766504800 |
||
611 | 2000-01-01 00:00:00 946684800 |
||
612 | 2003-12-31 12:34:56 1072874096 |
||
613 | } { |
||
614 | incr i |
||
615 | set sqlite_current_time $seconds |
||
616 | do_test table-13.2.$i { |
||
617 | execsql " |
||
618 | INSERT INTO tablet8(a) VALUES($i); |
||
619 | SELECT tm, dt, dttm FROM tablet8 WHERE a=$i; |
||
620 | " |
||
621 | } [list $time $date [list $date $time]] |
||
622 | } |
||
623 | set sqlite_current_time 0 |
||
624 | |||
625 | #-------------------------------------------------------------------- |
||
626 | # Test cases table-14.* |
||
627 | # |
||
628 | # Test that a table cannot be created or dropped while other virtual |
||
629 | # machines are active. This is required because otherwise when in |
||
630 | # auto-vacuum mode the btree-layer may need to move the root-pages of |
||
631 | # a table for which there is an open cursor. |
||
632 | # |
||
633 | # 2007-05-02: A open btree cursor no longer blocks CREATE TABLE. |
||
634 | # But DROP TABLE is still prohibited because we do not want to |
||
635 | # delete a table out from under a running query. |
||
636 | # |
||
637 | |||
638 | # db eval { |
||
639 | # pragma vdbe_trace = 0; |
||
640 | # } |
||
641 | # Try to create a table from within a callback: |
||
642 | unset -nocomplain result |
||
643 | do_test table-14.1 { |
||
644 | set rc [ |
||
645 | catch { |
||
646 | db eval {SELECT * FROM tablet8 LIMIT 1} {} { |
||
647 | db eval {CREATE TABLE t9(a, b, c)} |
||
648 | } |
||
649 | } msg |
||
650 | ] |
||
651 | set result [list $rc $msg] |
||
652 | } {0 {}} |
||
653 | |||
654 | # Try to drop a table from within a callback: |
||
655 | do_test table-14.2 { |
||
656 | set rc [ |
||
657 | catch { |
||
658 | db eval {SELECT * FROM tablet8 LIMIT 1} {} { |
||
659 | db eval {DROP TABLE t9;} |
||
660 | } |
||
661 | } msg |
||
662 | ] |
||
663 | set result [list $rc $msg] |
||
664 | } {1 {database table is locked}} |
||
665 | |||
666 | ifcapable attach { |
||
667 | # Now attach a database and ensure that a table can be created in the |
||
668 | # attached database whilst in a callback from a query on the main database. |
||
669 | do_test table-14.3 { |
||
670 | file delete -force test2.db |
||
671 | file delete -force test2.db-journal |
||
672 | execsql { |
||
673 | ATTACH 'test2.db' as aux; |
||
674 | } |
||
675 | db eval {SELECT * FROM tablet8 LIMIT 1} {} { |
||
676 | db eval {CREATE TABLE aux.t1(a, b, c)} |
||
677 | } |
||
678 | } {} |
||
679 | |||
680 | # On the other hand, it should be impossible to drop a table when any VMs |
||
681 | # are active. This is because VerifyCookie instructions may have already |
||
682 | # been executed, and btree root-pages may not move after this (which a |
||
683 | # delete table might do). |
||
684 | do_test table-14.4 { |
||
685 | set rc [ |
||
686 | catch { |
||
687 | db eval {SELECT * FROM tablet8 LIMIT 1} {} { |
||
688 | db eval {DROP TABLE aux.t1;} |
||
689 | } |
||
690 | } msg |
||
691 | ] |
||
692 | set result [list $rc $msg] |
||
693 | } {1 {database table is locked}} |
||
694 | } |
||
695 | |||
696 | # Create and drop 2000 tables. This is to check that the balance_shallow() |
||
697 | # routine works correctly on the sqlite_master table. At one point it |
||
698 | # contained a bug that would prevent the right-child pointer of the |
||
699 | # child page from being copied to the root page. |
||
700 | # |
||
701 | do_test table-15.1 { |
||
702 | execsql {BEGIN} |
||
703 | for {set i 0} {$i<2000} {incr i} { |
||
704 | execsql "CREATE TABLE tbl$i (a, b, c)" |
||
705 | } |
||
706 | execsql {COMMIT} |
||
707 | } {} |
||
708 | do_test table-15.2 { |
||
709 | execsql {BEGIN} |
||
710 | for {set i 0} {$i<2000} {incr i} { |
||
711 | execsql "DROP TABLE tbl$i" |
||
712 | } |
||
713 | execsql {COMMIT} |
||
714 | } {} |
||
715 | |||
716 | finish_test |