wasCSharpSQLite – Blame information for rev
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 September 25 |
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 | # |
||
12 | # This file implements tests to verify that the "testable statements" in |
||
13 | # the lang_createtable.html document are correct. |
||
14 | # |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | set ::testprefix e_createtable |
||
20 | |||
21 | # Test organization: |
||
22 | # |
||
23 | # e_createtable-0.*: Test that the syntax diagrams are correct. |
||
24 | # |
||
25 | # e_createtable-1.*: Test statements related to table and database names, |
||
26 | # the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause. |
||
27 | # |
||
28 | # e_createtable-2.*: Test "CREATE TABLE AS" statements. |
||
29 | # |
||
30 | |||
31 | proc do_createtable_tests {nm args} { |
||
32 | uplevel do_select_tests [list e_createtable-$nm] $args |
||
33 | } |
||
34 | |||
35 | |||
36 | #------------------------------------------------------------------------- |
||
37 | # This command returns a serialized tcl array mapping from the name of |
||
38 | # each attached database to a list of tables in that database. For example, |
||
39 | # if the database schema is created with: |
||
40 | # |
||
41 | # CREATE TABLE t1(x); |
||
42 | # CREATE TEMP TABLE t2(x); |
||
43 | # CREATE TEMP TABLE t3(x); |
||
44 | # |
||
45 | # Then this command returns "main t1 temp {t2 t3}". |
||
46 | # |
||
47 | proc table_list {} { |
||
48 | set res [list] |
||
49 | db eval { pragma database_list } a { |
||
50 | set dbname $a(name) |
||
51 | set master $a(name).sqlite_master |
||
52 | if {$dbname == "temp"} { set master sqlite_temp_master } |
||
53 | lappend res $dbname [ |
||
54 | db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name" |
||
55 | ] |
||
56 | } |
||
57 | set res |
||
58 | } |
||
59 | |||
60 | |||
61 | # EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name |
||
62 | # |
||
63 | do_createtable_tests 0.1.1 -repair { |
||
64 | drop_all_tables |
||
65 | } { |
||
66 | 1 "CREATE TABLE t1(c1 one)" {} |
||
67 | 2 "CREATE TABLE t1(c1 one two)" {} |
||
68 | 3 "CREATE TABLE t1(c1 one two three)" {} |
||
69 | 4 "CREATE TABLE t1(c1 one two three four)" {} |
||
70 | 5 "CREATE TABLE t1(c1 one two three four(14))" {} |
||
71 | 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {} |
||
72 | 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {} |
||
73 | 8 "CREATE TABLE t1(c1 var(1.0e10))" {} |
||
74 | } |
||
75 | do_createtable_tests 0.1.2 -error { |
||
76 | near "%s": syntax error |
||
77 | } { |
||
78 | 1 "CREATE TABLE t1(c1 one(number))" {number} |
||
79 | } |
||
80 | |||
81 | |||
82 | # EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint |
||
83 | # |
||
84 | # Note: Not shown in the syntax diagram is the "NULL" constraint. This |
||
85 | # is the opposite of "NOT NULL" - it implies that the column may |
||
86 | # take a NULL value. This is the default anyway, so this type of |
||
87 | # constraint is rarely used. |
||
88 | # |
||
89 | do_createtable_tests 0.2.1 -repair { |
||
90 | drop_all_tables |
||
91 | execsql { CREATE TABLE t2(x PRIMARY KEY) } |
||
92 | } { |
||
93 | 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {} |
||
94 | 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {} |
||
95 | 1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {} |
||
96 | 1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {} |
||
97 | |||
98 | 2.1 "CREATE TABLE t1(c1 text NOT NULL)" {} |
||
99 | 2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {} |
||
100 | 2.3 "CREATE TABLE t1(c1 text NULL)" {} |
||
101 | 2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {} |
||
102 | |||
103 | 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {} |
||
104 | 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {} |
||
105 | |||
106 | 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {} |
||
107 | 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {} |
||
108 | |||
109 | 5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {} |
||
110 | 5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {} |
||
111 | 5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {} |
||
112 | 5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {} |
||
113 | 5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {} |
||
114 | 5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {} |
||
115 | |||
116 | 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {} |
||
117 | 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {} |
||
118 | |||
119 | 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {} |
||
120 | 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {} |
||
121 | |||
122 | 8.1 { |
||
123 | CREATE TABLE t1(c1 |
||
124 | PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1 |
||
125 | ); |
||
126 | } {} |
||
127 | 8.2 { |
||
128 | CREATE TABLE t1(c1 |
||
129 | REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY |
||
130 | ); |
||
131 | } {} |
||
132 | } |
||
133 | |||
134 | # EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint |
||
135 | # |
||
136 | do_createtable_tests 0.3.1 -repair { |
||
137 | drop_all_tables |
||
138 | execsql { CREATE TABLE t2(x PRIMARY KEY) } |
||
139 | } { |
||
140 | 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {} |
||
141 | 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {} |
||
142 | 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {} |
||
143 | |||
144 | 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {} |
||
145 | 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {} |
||
146 | 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {} |
||
147 | |||
148 | 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {} |
||
149 | |||
150 | 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {} |
||
151 | } |
||
152 | |||
153 | # EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def |
||
154 | # |
||
155 | do_createtable_tests 0.4.1 -repair { |
||
156 | drop_all_tables |
||
157 | } { |
||
158 | 1 {CREATE TABLE t1( |
||
159 | col1, |
||
160 | col2 TEXT, |
||
161 | col3 INTEGER UNIQUE, |
||
162 | col4 VARCHAR(10, 10) PRIMARY KEY, |
||
163 | "name with spaces" REFERENCES t1 |
||
164 | ); |
||
165 | } {} |
||
166 | } |
||
167 | |||
168 | # EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt |
||
169 | # |
||
170 | do_createtable_tests 0.5.1 -repair { |
||
171 | drop_all_tables |
||
172 | execsql { CREATE TABLE t2(a, b, c) } |
||
173 | } { |
||
174 | 1 "CREATE TABLE t1(a, b, c)" {} |
||
175 | 2 "CREATE TEMP TABLE t1(a, b, c)" {} |
||
176 | 3 "CREATE TEMPORARY TABLE t1(a, b, c)" {} |
||
177 | 4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {} |
||
178 | 5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {} |
||
179 | 6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {} |
||
180 | |||
181 | 7 "CREATE TABLE main.t1(a, b, c)" {} |
||
182 | 8 "CREATE TEMP TABLE temp.t1(a, b, c)" {} |
||
183 | 9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {} |
||
184 | 10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {} |
||
185 | 11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {} |
||
186 | 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {} |
||
187 | |||
188 | 13 "CREATE TABLE t1 AS SELECT * FROM t2" {} |
||
189 | 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {} |
||
190 | 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {} |
||
191 | } |
||
192 | |||
193 | # EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause |
||
194 | # |
||
195 | # 1: Explicit parent-key columns. |
||
196 | # 2: Implicit child-key columns. |
||
197 | # |
||
198 | # 1: MATCH FULL |
||
199 | # 2: MATCH PARTIAL |
||
200 | # 3: MATCH SIMPLE |
||
201 | # 4: MATCH STICK |
||
202 | # 5: |
||
203 | # |
||
204 | # 1: ON DELETE SET NULL |
||
205 | # 2: ON DELETE SET DEFAULT |
||
206 | # 3: ON DELETE CASCADE |
||
207 | # 4: ON DELETE RESTRICT |
||
208 | # 5: ON DELETE NO ACTION |
||
209 | # 6: |
||
210 | # |
||
211 | # 1: ON UPDATE SET NULL |
||
212 | # 2: ON UPDATE SET DEFAULT |
||
213 | # 3: ON UPDATE CASCADE |
||
214 | # 4: ON UPDATE RESTRICT |
||
215 | # 5: ON UPDATE NO ACTION |
||
216 | # 6: |
||
217 | # |
||
218 | # 1: NOT DEFERRABLE INITIALLY DEFERRED |
||
219 | # 2: NOT DEFERRABLE INITIALLY IMMEDIATE |
||
220 | # 3: NOT DEFERRABLE |
||
221 | # 4: DEFERRABLE INITIALLY DEFERRED |
||
222 | # 5: DEFERRABLE INITIALLY IMMEDIATE |
||
223 | # 6: DEFERRABLE |
||
224 | # 7: |
||
225 | # |
||
226 | do_createtable_tests 0.6.1 -repair { |
||
227 | drop_all_tables |
||
228 | execsql { CREATE TABLE t2(x PRIMARY KEY, y) } |
||
229 | execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) } |
||
230 | } { |
||
231 | 11146 { CREATE TABLE t1(a |
||
232 | REFERENCES t2(x) MATCH FULL |
||
233 | ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE |
||
234 | )} {} |
||
235 | 11412 { CREATE TABLE t1(a |
||
236 | REFERENCES t2(x) |
||
237 | ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL |
||
238 | NOT DEFERRABLE INITIALLY IMMEDIATE |
||
239 | )} {} |
||
240 | 12135 { CREATE TABLE t1(a |
||
241 | REFERENCES t2(x) MATCH PARTIAL |
||
242 | ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE |
||
243 | )} {} |
||
244 | 12427 { CREATE TABLE t1(a |
||
245 | REFERENCES t2(x) MATCH PARTIAL |
||
246 | ON DELETE RESTRICT ON UPDATE SET DEFAULT |
||
247 | )} {} |
||
248 | 12446 { CREATE TABLE t1(a |
||
249 | REFERENCES t2(x) MATCH PARTIAL |
||
250 | ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE |
||
251 | )} {} |
||
252 | 12522 { CREATE TABLE t1(a |
||
253 | REFERENCES t2(x) MATCH PARTIAL |
||
254 | ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE |
||
255 | )} {} |
||
256 | 13133 { CREATE TABLE t1(a |
||
257 | REFERENCES t2(x) MATCH SIMPLE |
||
258 | ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE |
||
259 | )} {} |
||
260 | 13216 { CREATE TABLE t1(a |
||
261 | REFERENCES t2(x) MATCH SIMPLE |
||
262 | ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE |
||
263 | )} {} |
||
264 | 13263 { CREATE TABLE t1(a |
||
265 | REFERENCES t2(x) MATCH SIMPLE |
||
266 | ON DELETE SET DEFAULT NOT DEFERRABLE |
||
267 | )} {} |
||
268 | 13421 { CREATE TABLE t1(a |
||
269 | REFERENCES t2(x) MATCH SIMPLE |
||
270 | ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED |
||
271 | )} {} |
||
272 | 13432 { CREATE TABLE t1(a |
||
273 | REFERENCES t2(x) MATCH SIMPLE |
||
274 | ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE |
||
275 | )} {} |
||
276 | 13523 { CREATE TABLE t1(a |
||
277 | REFERENCES t2(x) MATCH SIMPLE |
||
278 | ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE |
||
279 | )} {} |
||
280 | 14336 { CREATE TABLE t1(a |
||
281 | REFERENCES t2(x) MATCH STICK |
||
282 | ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE |
||
283 | )} {} |
||
284 | 14611 { CREATE TABLE t1(a |
||
285 | REFERENCES t2(x) MATCH STICK |
||
286 | ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED |
||
287 | )} {} |
||
288 | 15155 { CREATE TABLE t1(a |
||
289 | REFERENCES t2(x) |
||
290 | ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE |
||
291 | )} {} |
||
292 | 15453 { CREATE TABLE t1(a |
||
293 | REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE |
||
294 | )} {} |
||
295 | 15661 { CREATE TABLE t1(a |
||
296 | REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED |
||
297 | )} {} |
||
298 | 21115 { CREATE TABLE t1(a |
||
299 | REFERENCES t2 MATCH FULL |
||
300 | ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE |
||
301 | )} {} |
||
302 | 21123 { CREATE TABLE t1(a |
||
303 | REFERENCES t2 MATCH FULL |
||
304 | ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE |
||
305 | )} {} |
||
306 | 21217 { CREATE TABLE t1(a |
||
307 | REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL |
||
308 | )} {} |
||
309 | 21362 { CREATE TABLE t1(a |
||
310 | REFERENCES t2 MATCH FULL |
||
311 | ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE |
||
312 | )} {} |
||
313 | 22143 { CREATE TABLE t1(a |
||
314 | REFERENCES t2 MATCH PARTIAL |
||
315 | ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE |
||
316 | )} {} |
||
317 | 22156 { CREATE TABLE t1(a |
||
318 | REFERENCES t2 MATCH PARTIAL |
||
319 | ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE |
||
320 | )} {} |
||
321 | 22327 { CREATE TABLE t1(a |
||
322 | REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT |
||
323 | )} {} |
||
324 | 22663 { CREATE TABLE t1(a |
||
325 | REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE |
||
326 | )} {} |
||
327 | 23236 { CREATE TABLE t1(a |
||
328 | REFERENCES t2 MATCH SIMPLE |
||
329 | ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE |
||
330 | )} {} |
||
331 | 24155 { CREATE TABLE t1(a |
||
332 | REFERENCES t2 MATCH STICK |
||
333 | ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE |
||
334 | )} {} |
||
335 | 24522 { CREATE TABLE t1(a |
||
336 | REFERENCES t2 MATCH STICK |
||
337 | ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE |
||
338 | )} {} |
||
339 | 24625 { CREATE TABLE t1(a |
||
340 | REFERENCES t2 MATCH STICK |
||
341 | ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE |
||
342 | )} {} |
||
343 | 25454 { CREATE TABLE t1(a |
||
344 | REFERENCES t2 |
||
345 | ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED |
||
346 | )} {} |
||
347 | } |
||
348 | |||
349 | #------------------------------------------------------------------------- |
||
350 | # Test cases e_createtable-1.* - test statements related to table and |
||
351 | # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS |
||
352 | # clause. |
||
353 | # |
||
354 | drop_all_tables |
||
355 | forcedelete test.db2 test.db3 |
||
356 | |||
357 | do_execsql_test e_createtable-1.0 { |
||
358 | ATTACH 'test.db2' AS auxa; |
||
359 | ATTACH 'test.db3' AS auxb; |
||
360 | } {} |
||
361 | |||
362 | # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are |
||
363 | # reserved for internal use. It is an error to attempt to create a table |
||
364 | # with a name that starts with "sqlite_". |
||
365 | # |
||
366 | do_createtable_tests 1.1.1 -error { |
||
367 | object name reserved for internal use: %s |
||
368 | } { |
||
369 | 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc |
||
370 | 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld |
||
371 | 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__ |
||
372 | 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_ |
||
373 | 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL |
||
374 | } |
||
375 | do_createtable_tests 1.1.2 { |
||
376 | 1 "CREATE TABLE sqlit_abc(a, b, c)" {} |
||
377 | 2 "CREATE TABLE temp.sqlitehelloworld(x)" {} |
||
378 | 3 {CREATE TABLE auxa."sqlite"(x, y)} {} |
||
379 | 4 {CREATE TABLE auxb."sqlite-"(z)} {} |
||
380 | 5 {CREATE TABLE "SQLITE-TBL"(z)} {} |
||
381 | } |
||
382 | |||
383 | |||
384 | # EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it |
||
385 | # must be either "main", "temp", or the name of an attached database. |
||
386 | # |
||
387 | # EVIDENCE-OF: R-39822-07822 In this case the new table is created in |
||
388 | # the named database. |
||
389 | # |
||
390 | # Test cases 1.2.* test the first of the two requirements above. The |
||
391 | # second is verified by cases 1.3.*. |
||
392 | # |
||
393 | do_createtable_tests 1.2.1 -error { |
||
394 | unknown database %s |
||
395 | } { |
||
396 | 1 "CREATE TABLE george.t1(a, b)" george |
||
397 | 2 "CREATE TABLE _.t1(a, b)" _ |
||
398 | } |
||
399 | do_createtable_tests 1.2.2 { |
||
400 | 1 "CREATE TABLE main.abc(a, b, c)" {} |
||
401 | 2 "CREATE TABLE temp.helloworld(x)" {} |
||
402 | 3 {CREATE TABLE auxa."t 1"(x, y)} {} |
||
403 | 4 {CREATE TABLE auxb.xyz(z)} {} |
||
404 | } |
||
405 | drop_all_tables |
||
406 | do_createtable_tests 1.3 -tclquery { |
||
407 | unset -nocomplain X |
||
408 | array set X [table_list] |
||
409 | list $X(main) $X(temp) $X(auxa) $X(auxb) |
||
410 | } { |
||
411 | 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}} |
||
412 | 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}} |
||
413 | 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}} |
||
414 | 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl} |
||
415 | 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}} |
||
416 | 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}} |
||
417 | } |
||
418 | |||
419 | # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs |
||
420 | # between the "CREATE" and "TABLE" then the new table is created in the |
||
421 | # temp database. |
||
422 | # |
||
423 | drop_all_tables |
||
424 | do_createtable_tests 1.4 -tclquery { |
||
425 | unset -nocomplain X |
||
426 | array set X [table_list] |
||
427 | list $X(main) $X(temp) $X(auxa) $X(auxb) |
||
428 | } { |
||
429 | 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}} |
||
430 | 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}} |
||
431 | } |
||
432 | |||
433 | # EVIDENCE-OF: R-49439-47561 It is an error to specify both a |
||
434 | # <database-name> and the TEMP or TEMPORARY keyword, unless the |
||
435 | # <database-name> is "temp". |
||
436 | # |
||
437 | drop_all_tables |
||
438 | do_createtable_tests 1.5.1 -error { |
||
439 | temporary table name must be unqualified |
||
440 | } { |
||
441 | 1 "CREATE TEMP TABLE main.t1(a, b)" {} |
||
442 | 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {} |
||
443 | 3 "CREATE TEMP TABLE auxb.t3(a, b)" {} |
||
444 | 4 "CREATE TEMPORARY TABLE main.xxx(x)" {} |
||
445 | } |
||
446 | drop_all_tables |
||
447 | do_createtable_tests 1.5.2 -tclquery { |
||
448 | unset -nocomplain X |
||
449 | array set X [table_list] |
||
450 | list $X(main) $X(temp) $X(auxa) $X(auxb) |
||
451 | } { |
||
452 | 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}} |
||
453 | 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}} |
||
454 | 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}} |
||
455 | 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}} |
||
456 | } |
||
457 | |||
458 | # EVIDENCE-OF: R-00917-09393 If no database name is specified and the |
||
459 | # TEMP keyword is not present then the table is created in the main |
||
460 | # database. |
||
461 | # |
||
462 | drop_all_tables |
||
463 | do_createtable_tests 1.6 -tclquery { |
||
464 | unset -nocomplain X |
||
465 | array set X [table_list] |
||
466 | list $X(main) $X(temp) $X(auxa) $X(auxb) |
||
467 | } { |
||
468 | 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}} |
||
469 | 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}} |
||
470 | 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}} |
||
471 | 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}} |
||
472 | } |
||
473 | |||
474 | drop_all_tables |
||
475 | do_execsql_test e_createtable-1.7.0 { |
||
476 | CREATE TABLE t1(x, y); |
||
477 | CREATE INDEX i1 ON t1(x); |
||
478 | CREATE VIEW v1 AS SELECT * FROM t1; |
||
479 | |||
480 | CREATE TABLE auxa.tbl1(x, y); |
||
481 | CREATE INDEX auxa.idx1 ON tbl1(x); |
||
482 | CREATE VIEW auxa.view1 AS SELECT * FROM tbl1; |
||
483 | } {} |
||
484 | |||
485 | # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create |
||
486 | # a new table in a database that already contains a table, index or view |
||
487 | # of the same name. |
||
488 | # |
||
489 | # Test cases 1.7.1.* verify that creating a table in a database with a |
||
490 | # table/index/view of the same name does fail. 1.7.2.* tests that creating |
||
491 | # a table with the same name as a table/index/view in a different database |
||
492 | # is Ok. |
||
493 | # |
||
494 | do_createtable_tests 1.7.1 -error { %s } { |
||
495 | 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}} |
||
496 | 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}} |
||
497 | 3 "CREATE TABLE v1(a, b)" {{table v1 already exists}} |
||
498 | 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}} |
||
499 | 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}} |
||
500 | 6 "CREATE TABLE auxa.view1(a, b)" {{table view1 already exists}} |
||
501 | } |
||
502 | do_createtable_tests 1.7.2 { |
||
503 | 1 "CREATE TABLE auxa.t1(a, b)" {} |
||
504 | 2 "CREATE TABLE auxa.i1(a, b)" {} |
||
505 | 3 "CREATE TABLE auxa.v1(a, b)" {} |
||
506 | 4 "CREATE TABLE tbl1(a, b)" {} |
||
507 | 5 "CREATE TABLE idx1(a, b)" {} |
||
508 | 6 "CREATE TABLE view1(a, b)" {} |
||
509 | } |
||
510 | |||
511 | # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is |
||
512 | # specified as part of the CREATE TABLE statement and a table or view of |
||
513 | # the same name already exists, the CREATE TABLE command simply has no |
||
514 | # effect (and no error message is returned). |
||
515 | # |
||
516 | drop_all_tables |
||
517 | do_execsql_test e_createtable-1.8.0 { |
||
518 | CREATE TABLE t1(x, y); |
||
519 | CREATE INDEX i1 ON t1(x); |
||
520 | CREATE VIEW v1 AS SELECT * FROM t1; |
||
521 | CREATE TABLE auxa.tbl1(x, y); |
||
522 | CREATE INDEX auxa.idx1 ON tbl1(x); |
||
523 | CREATE VIEW auxa.view1 AS SELECT * FROM tbl1; |
||
524 | } {} |
||
525 | do_createtable_tests 1.8 { |
||
526 | 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {} |
||
527 | 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {} |
||
528 | 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {} |
||
529 | 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {} |
||
530 | } |
||
531 | |||
532 | # EVIDENCE-OF: R-16465-40078 An error is still returned if the table |
||
533 | # cannot be created because of an existing index, even if the "IF NOT |
||
534 | # EXISTS" clause is specified. |
||
535 | # |
||
536 | do_createtable_tests 1.9 -error { %s } { |
||
537 | 1 "CREATE TABLE IF NOT EXISTS i1(a, b)" |
||
538 | {{there is already an index named i1}} |
||
539 | 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)" |
||
540 | {{there is already an index named idx1}} |
||
541 | } |
||
542 | |||
543 | # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that |
||
544 | # has the same name as an existing trigger. |
||
545 | # |
||
546 | drop_all_tables |
||
547 | do_execsql_test e_createtable-1.10.0 { |
||
548 | CREATE TABLE t1(x, y); |
||
549 | CREATE TABLE auxb.t2(x, y); |
||
550 | |||
551 | CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
||
552 | SELECT 1; |
||
553 | END; |
||
554 | CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN |
||
555 | SELECT 1; |
||
556 | END; |
||
557 | } {} |
||
558 | do_createtable_tests 1.10 { |
||
559 | 1 "CREATE TABLE tr1(a, b)" {} |
||
560 | 2 "CREATE TABLE tr2(a, b)" {} |
||
561 | 3 "CREATE TABLE auxb.tr1(a, b)" {} |
||
562 | 4 "CREATE TABLE auxb.tr2(a, b)" {} |
||
563 | } |
||
564 | |||
565 | # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE |
||
566 | # statement. |
||
567 | # |
||
568 | drop_all_tables |
||
569 | do_execsql_test e_createtable-1.11.0 { |
||
570 | CREATE TABLE t1(a, b); |
||
571 | CREATE TABLE t2(a, b); |
||
572 | CREATE TABLE auxa.t3(a, b); |
||
573 | CREATE TABLE auxa.t4(a, b); |
||
574 | } {} |
||
575 | |||
576 | do_execsql_test e_createtable-1.11.1.1 { |
||
577 | SELECT * FROM t1; |
||
578 | SELECT * FROM t2; |
||
579 | SELECT * FROM t3; |
||
580 | SELECT * FROM t4; |
||
581 | } {} |
||
582 | do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {} |
||
583 | do_catchsql_test e_createtable-1.11.1.3 { |
||
584 | SELECT * FROM t1 |
||
585 | } {1 {no such table: t1}} |
||
586 | do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {} |
||
587 | do_catchsql_test e_createtable-1.11.1.5 { |
||
588 | SELECT * FROM t3 |
||
589 | } {1 {no such table: t3}} |
||
590 | |||
591 | do_execsql_test e_createtable-1.11.2.1 { |
||
592 | SELECT name FROM sqlite_master; |
||
593 | SELECT name FROM auxa.sqlite_master; |
||
594 | } {t2 t4} |
||
595 | do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {} |
||
596 | do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {} |
||
597 | do_execsql_test e_createtable-1.11.2.4 { |
||
598 | SELECT name FROM sqlite_master; |
||
599 | SELECT name FROM auxa.sqlite_master; |
||
600 | } {} |
||
601 | |||
602 | #------------------------------------------------------------------------- |
||
603 | # Test cases e_createtable-2.* - test statements related to the CREATE |
||
604 | # TABLE AS ... SELECT statement. |
||
605 | # |
||
606 | |||
607 | # Three Tcl commands: |
||
608 | # |
||
609 | # select_column_names SQL |
||
610 | # The argument must be a SELECT statement. Return a list of the names |
||
611 | # of the columns of the result-set that would be returned by executing |
||
612 | # the SELECT. |
||
613 | # |
||
614 | # table_column_names TBL |
||
615 | # The argument must be a table name. Return a list of column names, from |
||
616 | # left to right, for the table. |
||
617 | # |
||
618 | # table_column_decltypes TBL |
||
619 | # The argument must be a table name. Return a list of column declared |
||
620 | # types, from left to right, for the table. |
||
621 | # |
||
622 | proc sci {select cmd} { |
||
623 | set res [list] |
||
624 | set STMT [sqlite3_prepare_v2 db $select -1 dummy] |
||
625 | for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { |
||
626 | lappend res [$cmd $STMT $i] |
||
627 | } |
||
628 | sqlite3_finalize $STMT |
||
629 | set res |
||
630 | } |
||
631 | proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd } |
||
632 | proc select_column_names {sql} { sci $sql sqlite3_column_name } |
||
633 | proc table_column_names {tbl} { tci $tbl sqlite3_column_name } |
||
634 | proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype } |
||
635 | |||
636 | # Create a database schema. This schema is used by tests 2.1.* through 2.3.*. |
||
637 | # |
||
638 | drop_all_tables |
||
639 | do_execsql_test e_createtable-2.0 { |
||
640 | CREATE TABLE t1(a, b, c); |
||
641 | CREATE TABLE t2(d, e, f); |
||
642 | CREATE TABLE t3(g BIGINT, h VARCHAR(10)); |
||
643 | CREATE TABLE t4(i BLOB, j ANYOLDATA); |
||
644 | CREATE TABLE t5(k FLOAT, l INTEGER); |
||
645 | CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n)); |
||
646 | CREATE TABLE t7(x INTEGER PRIMARY KEY); |
||
647 | CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc'); |
||
648 | CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE); |
||
649 | } {} |
||
650 | |||
651 | # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as |
||
652 | # the rows returned by the SELECT statement. The name of each column is |
||
653 | # the same as the name of the corresponding column in the result set of |
||
654 | # the SELECT statement. |
||
655 | # |
||
656 | do_createtable_tests 2.1 -tclquery { |
||
657 | table_column_names x1 |
||
658 | } -repair { |
||
659 | catchsql { DROP TABLE x1 } |
||
660 | } { |
||
661 | 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c} |
||
662 | 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a} |
||
663 | 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f} |
||
664 | 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)} |
||
665 | 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)} |
||
666 | } |
||
667 | |||
668 | # EVIDENCE-OF: R-37111-22855 The declared type of each column is |
||
669 | # determined by the expression affinity of the corresponding expression |
||
670 | # in the result set of the SELECT statement, as follows: Expression |
||
671 | # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT" |
||
672 | # REAL "REAL" NONE "" (empty string) |
||
673 | # |
||
674 | do_createtable_tests 2.2 -tclquery { |
||
675 | table_column_decltypes x1 |
||
676 | } -repair { |
||
677 | catchsql { DROP TABLE x1 } |
||
678 | } { |
||
679 | 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""} |
||
680 | 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT} |
||
681 | 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM} |
||
682 | 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT} |
||
683 | } |
||
684 | |||
685 | # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has |
||
686 | # no PRIMARY KEY and no constraints of any kind. The default value of |
||
687 | # each column is NULL. The default collation sequence for each column of |
||
688 | # the new table is BINARY. |
||
689 | # |
||
690 | # The following tests create tables based on SELECT statements that read |
||
691 | # from tables that have primary keys, constraints and explicit default |
||
692 | # collation sequences. None of this is transfered to the definition of |
||
693 | # the new table as stored in the sqlite_master table. |
||
694 | # |
||
695 | # Tests 2.3.2.* show that the default value of each column is NULL. |
||
696 | # |
||
697 | do_createtable_tests 2.3.1 -query { |
||
698 | SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1 |
||
699 | } { |
||
700 | 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}} |
||
701 | 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}} |
||
702 | 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}} |
||
703 | 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}} |
||
704 | } |
||
705 | do_execsql_test e_createtable-2.3.2.1 { |
||
706 | INSERT INTO x1 DEFAULT VALUES; |
||
707 | INSERT INTO x2 DEFAULT VALUES; |
||
708 | INSERT INTO x3 DEFAULT VALUES; |
||
709 | INSERT INTO x4 DEFAULT VALUES; |
||
710 | } {} |
||
711 | db nullvalue null |
||
712 | do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null} |
||
713 | do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null} |
||
714 | do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null} |
||
715 | do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null} |
||
716 | db nullvalue {} |
||
717 | |||
718 | drop_all_tables |
||
719 | do_execsql_test e_createtable-2.4.0 { |
||
720 | CREATE TABLE t1(x, y); |
||
721 | INSERT INTO t1 VALUES('i', 'one'); |
||
722 | INSERT INTO t1 VALUES('ii', 'two'); |
||
723 | INSERT INTO t1 VALUES('iii', 'three'); |
||
724 | } {} |
||
725 | |||
726 | # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are |
||
727 | # initially populated with the rows of data returned by the SELECT |
||
728 | # statement. |
||
729 | # |
||
730 | # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending |
||
731 | # rowid values, starting with 1, in the order that they are returned by |
||
732 | # the SELECT statement. |
||
733 | # |
||
734 | # Each test case below is specified as the name of a table to create |
||
735 | # using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in |
||
736 | # creating it. The table is created. |
||
737 | # |
||
738 | # Test cases 2.4.*.1 check that after it has been created, the data in the |
||
739 | # table is the same as the data returned by the SELECT statement executed as |
||
740 | # a standalone command, verifying the first testable statement above. |
||
741 | # |
||
742 | # Test cases 2.4.*.2 check that the rowids were allocated contiguously |
||
743 | # as required by the second testable statement above. That the rowids |
||
744 | # from the contiguous block were allocated to rows in the order rows are |
||
745 | # returned by the SELECT statement is verified by 2.4.*.1. |
||
746 | # |
||
747 | # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement |
||
748 | # creates and populates a database table based on the results of a |
||
749 | # SELECT statement. |
||
750 | # |
||
751 | # The above is also considered to be tested by the following. It is |
||
752 | # clear that tables are being created and populated by the command in |
||
753 | # question. |
||
754 | # |
||
755 | foreach {tn tbl select} { |
||
756 | 1 x1 "SELECT * FROM t1" |
||
757 | 2 x2 "SELECT * FROM t1 ORDER BY x DESC" |
||
758 | 3 x3 "SELECT * FROM t1 ORDER BY x ASC" |
||
759 | } { |
||
760 | # Create the table using a "CREATE TABLE ... AS SELECT ..." command. |
||
761 | execsql [subst {CREATE TABLE $tbl AS $select}] |
||
762 | |||
763 | # Check that the rows inserted into the table, sorted in ascending rowid |
||
764 | # order, match those returned by executing the SELECT statement as a |
||
765 | # standalone command. |
||
766 | do_execsql_test e_createtable-2.4.$tn.1 [subst { |
||
767 | SELECT * FROM $tbl ORDER BY rowid; |
||
768 | }] [execsql $select] |
||
769 | |||
770 | # Check that the rowids in the new table are a contiguous block starting |
||
771 | # with rowid 1. Note that this will fail if SELECT statement $select |
||
772 | # returns 0 rows (as max(rowid) will be NULL). |
||
773 | do_execsql_test e_createtable-2.4.$tn.2 [subst { |
||
774 | SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl |
||
775 | }] {1 1} |
||
776 | } |
||
777 | |||
778 | #-------------------------------------------------------------------------- |
||
779 | # Test cases for column defintions in CREATE TABLE statements that do not |
||
780 | # use a SELECT statement. Not including data constraints. In other words, |
||
781 | # tests for the specification of: |
||
782 | # |
||
783 | # * declared types, |
||
784 | # * default values, and |
||
785 | # * default collation sequences. |
||
786 | # |
||
787 | |||
788 | # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not |
||
789 | # restrict the type of data that may be inserted into a column based on |
||
790 | # the columns declared type. |
||
791 | # |
||
792 | # Test this by creating a few tables with varied declared types, then |
||
793 | # inserting various different types of values into them. |
||
794 | # |
||
795 | drop_all_tables |
||
796 | do_execsql_test e_createtable-3.1.0 { |
||
797 | CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE); |
||
798 | CREATE TABLE t2(a DATETIME, b STRING, c REAL); |
||
799 | CREATE TABLE t3(o, t); |
||
800 | } {} |
||
801 | |||
802 | # value type -> declared column type |
||
803 | # ---------------------------------- |
||
804 | # integer -> VARCHAR(10) |
||
805 | # string -> INTEGER |
||
806 | # blob -> DOUBLE |
||
807 | # |
||
808 | do_execsql_test e_createtable-3.1.1 { |
||
809 | INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655'); |
||
810 | SELECT * FROM t1; |
||
811 | } {14 {quite a lengthy string} UVU} |
||
812 | |||
813 | # string -> DATETIME |
||
814 | # integer -> STRING |
||
815 | # time -> REAL |
||
816 | # |
||
817 | do_execsql_test e_createtable-3.1.2 { |
||
818 | INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59'); |
||
819 | SELECT * FROM t2; |
||
820 | } {{not a datetime} 13 12:41:59} |
||
821 | |||
822 | # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to |
||
823 | # determine the affinity of the column only. |
||
824 | # |
||
825 | # Affinities are tested in more detail elsewhere (see document |
||
826 | # datatype3.html). Here, just test that affinity transformations |
||
827 | # consistent with the expected affinity of each column (based on |
||
828 | # the declared type) appear to take place. |
||
829 | # |
||
830 | # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL |
||
831 | # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL |
||
832 | # Affinities of t3 (test cases 3.2.3.*): NONE, NONE |
||
833 | # |
||
834 | do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {} |
||
835 | |||
836 | do_createtable_tests 3.2.1 -query { |
||
837 | SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1; |
||
838 | } { |
||
839 | 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0} |
||
840 | 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0} |
||
841 | } |
||
842 | do_createtable_tests 3.2.2 -query { |
||
843 | SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1; |
||
844 | } { |
||
845 | 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0} |
||
846 | 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0} |
||
847 | } |
||
848 | do_createtable_tests 3.2.3 -query { |
||
849 | SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1; |
||
850 | } { |
||
851 | 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'} |
||
852 | 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0} |
||
853 | } |
||
854 | |||
855 | # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause |
||
856 | # attached to a column definition, then the default value of the column |
||
857 | # is NULL. |
||
858 | # |
||
859 | # None of the columns in table t1 have an explicit DEFAULT clause. |
||
860 | # So testing that the default value of all columns in table t1 is |
||
861 | # NULL serves to verify the above. |
||
862 | # |
||
863 | do_createtable_tests 3.2.3 -query { |
||
864 | SELECT quote(x), quote(y), quote(z) FROM t1 |
||
865 | } -repair { |
||
866 | execsql { DELETE FROM t1 } |
||
867 | } { |
||
868 | 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL} |
||
869 | 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'} |
||
870 | 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL} |
||
871 | } |
||
872 | |||
873 | # EVIDENCE-OF: R-62940-43005 An explicit DEFAULT clause may specify that |
||
874 | # the default value is NULL, a string constant, a blob constant, a |
||
875 | # signed-number, or any constant expression enclosed in parentheses. An |
||
876 | # explicit default value may also be one of the special case-independent |
||
877 | # keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. |
||
878 | # |
||
879 | do_execsql_test e_createtable-3.3.1 { |
||
880 | CREATE TABLE t4( |
||
881 | a DEFAULT NULL, |
||
882 | b DEFAULT 'string constant', |
||
883 | c DEFAULT X'424C4F42', |
||
884 | d DEFAULT 1, |
||
885 | e DEFAULT -1, |
||
886 | f DEFAULT 3.14, |
||
887 | g DEFAULT -3.14, |
||
888 | h DEFAULT ( substr('abcd', 0, 2) || 'cd' ), |
||
889 | i DEFAULT CURRENT_TIME, |
||
890 | j DEFAULT CURRENT_DATE, |
||
891 | k DEFAULT CURRENT_TIMESTAMP |
||
892 | ); |
||
893 | } {} |
||
894 | |||
895 | # EVIDENCE-OF: R-10288-43169 For the purposes of the DEFAULT clause, an |
||
896 | # expression is considered constant provided that it does not contain |
||
897 | # any sub-queries or string constants enclosed in double quotes. |
||
898 | # |
||
899 | do_createtable_tests 3.4.1 -error { |
||
900 | default value of column [x] is not constant |
||
901 | } { |
||
902 | 1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {} |
||
903 | 2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {} |
||
904 | 3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {} |
||
905 | 4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {} |
||
906 | } |
||
907 | do_createtable_tests 3.4.2 -repair { |
||
908 | catchsql { DROP TABLE t5 } |
||
909 | } { |
||
910 | 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {} |
||
911 | 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {} |
||
912 | } |
||
913 | |||
914 | # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table |
||
915 | # by an INSERT statement that does not provide explicit values for all |
||
916 | # table columns the values stored in the new row are determined by their |
||
917 | # default values |
||
918 | # |
||
919 | # Verify this with some assert statements for which all, some and no |
||
920 | # columns lack explicit values. |
||
921 | # |
||
922 | set sqlite_current_time 1000000000 |
||
923 | do_createtable_tests 3.5 -query { |
||
924 | SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), |
||
925 | quote(g), quote(h), quote(i), quote(j), quote(k) |
||
926 | FROM t4 ORDER BY rowid DESC LIMIT 1; |
||
927 | } { |
||
928 | 1 "INSERT INTO t4 DEFAULT VALUES" { |
||
929 | NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 |
||
930 | 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} |
||
931 | } |
||
932 | |||
933 | 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" { |
||
934 | 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} |
||
935 | } |
||
936 | |||
937 | 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" { |
||
938 | NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1 |
||
939 | } |
||
940 | |||
941 | 4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" { |
||
942 | 1 2 3 4 5 6 7 8 9 10 11 |
||
943 | } |
||
944 | } |
||
945 | |||
946 | # EVIDENCE-OF: R-12572-62501 If the default value of the column is a |
||
947 | # constant NULL, text, blob or signed-number value, then that value is |
||
948 | # used directly in the new row. |
||
949 | # |
||
950 | do_execsql_test e_createtable-3.6.1 { |
||
951 | CREATE TABLE t5( |
||
952 | a DEFAULT NULL, |
||
953 | b DEFAULT 'text value', |
||
954 | c DEFAULT X'424C4F42', |
||
955 | d DEFAULT -45678.6, |
||
956 | e DEFAULT 394507 |
||
957 | ); |
||
958 | } {} |
||
959 | do_execsql_test e_createtable-3.6.2 { |
||
960 | INSERT INTO t5 DEFAULT VALUES; |
||
961 | SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5; |
||
962 | } {NULL {'text value'} X'424C4F42' -45678.6 394507} |
||
963 | |||
964 | # EVIDENCE-OF: R-60616-50251 If the default value of a column is an |
||
965 | # expression in parentheses, then the expression is evaluated once for |
||
966 | # each row inserted and the results used in the new row. |
||
967 | # |
||
968 | # Test case 3.6.4 demonstrates that the expression is evaluated |
||
969 | # separately for each row if the INSERT is an "INSERT INTO ... SELECT ..." |
||
970 | # command. |
||
971 | # |
||
972 | set ::nextint 0 |
||
973 | proc nextint {} { incr ::nextint } |
||
974 | db func nextint nextint |
||
975 | |||
976 | do_execsql_test e_createtable-3.7.1 { |
||
977 | CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() )); |
||
978 | } {} |
||
979 | do_execsql_test e_createtable-3.7.2 { |
||
980 | INSERT INTO t6 DEFAULT VALUES; |
||
981 | SELECT quote(a), quote(b) FROM t6; |
||
982 | } {1 2} |
||
983 | do_execsql_test e_createtable-3.7.3 { |
||
984 | INSERT INTO t6(a) VALUES('X'); |
||
985 | SELECT quote(a), quote(b) FROM t6; |
||
986 | } {1 2 'X' 3} |
||
987 | do_execsql_test e_createtable-3.7.4 { |
||
988 | INSERT INTO t6(a) SELECT a FROM t6; |
||
989 | SELECT quote(a), quote(b) FROM t6; |
||
990 | } {1 2 'X' 3 1 4 'X' 5} |
||
991 | |||
992 | # EVIDENCE-OF: R-15363-55230 If the default value of a column is |
||
993 | # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used |
||
994 | # in the new row is a text representation of the current UTC date and/or |
||
995 | # time. |
||
996 | # |
||
997 | # This is difficult to test literally without knowing what time the |
||
998 | # user will run the tests. Instead, we test that the three cases |
||
999 | # above set the value to the current date and/or time according to |
||
1000 | # the xCurrentTime() method of the VFS. Which is usually the same |
||
1001 | # as UTC. In this case, however, we instrument it to always return |
||
1002 | # a time equivalent to "2001-09-09 01:46:40 UTC". |
||
1003 | # |
||
1004 | set sqlite_current_time 1000000000 |
||
1005 | do_execsql_test e_createtable-3.8.1 { |
||
1006 | CREATE TABLE t7( |
||
1007 | a DEFAULT CURRENT_TIME, |
||
1008 | b DEFAULT CURRENT_DATE, |
||
1009 | c DEFAULT CURRENT_TIMESTAMP |
||
1010 | ); |
||
1011 | } {} |
||
1012 | do_execsql_test e_createtable-3.8.2 { |
||
1013 | INSERT INTO t7 DEFAULT VALUES; |
||
1014 | SELECT quote(a), quote(b), quote(c) FROM t7; |
||
1015 | } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}} |
||
1016 | |||
1017 | |||
1018 | # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value |
||
1019 | # is "HH:MM:SS". |
||
1020 | # |
||
1021 | # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD". |
||
1022 | # |
||
1023 | # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is |
||
1024 | # "YYYY-MM-DD HH:MM:SS". |
||
1025 | # |
||
1026 | # The three above are demonstrated by tests 1, 2 and 3 below. |
||
1027 | # Respectively. |
||
1028 | # |
||
1029 | do_createtable_tests 3.8.3 -query { |
||
1030 | SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1; |
||
1031 | } { |
||
1032 | 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y} |
||
1033 | 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x} |
||
1034 | 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}} |
||
1035 | } |
||
1036 | |||
1037 | # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a |
||
1038 | # collating sequence to use as the default collation sequence for the |
||
1039 | # column. |
||
1040 | # |
||
1041 | # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the |
||
1042 | # default collation sequence is BINARY. |
||
1043 | # |
||
1044 | do_execsql_test e_createtable-3-9.1 { |
||
1045 | CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d); |
||
1046 | INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc'); |
||
1047 | INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc '); |
||
1048 | INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC '); |
||
1049 | INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC'); |
||
1050 | } {} |
||
1051 | do_createtable_tests 3.9 { |
||
1052 | 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }} |
||
1053 | 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }} |
||
1054 | 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }} |
||
1055 | 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }} |
||
1056 | } |
||
1057 | |||
1058 | # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited |
||
1059 | # by the SQLITE_MAX_COLUMN compile-time parameter. |
||
1060 | # |
||
1061 | proc columns {n} { |
||
1062 | set res [list] |
||
1063 | for {set i 0} {$i < $n} {incr i} { lappend res "c$i" } |
||
1064 | join $res ", " |
||
1065 | } |
||
1066 | do_execsql_test e_createtable-3.10.1 [subst { |
||
1067 | CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]); |
||
1068 | }] {} |
||
1069 | do_catchsql_test e_createtable-3.10.2 [subst { |
||
1070 | CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]); |
||
1071 | }] {1 {too many columns on t10}} |
||
1072 | |||
1073 | # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at |
||
1074 | # runtime using the sqlite3_limit() C/C++ interface. |
||
1075 | # |
||
1076 | # A 30,000 byte blob consumes 30,003 bytes of record space. A record |
||
1077 | # that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests |
||
1078 | # 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered |
||
1079 | # at runtime, are based on this calculation. |
||
1080 | # |
||
1081 | sqlite3_limit db SQLITE_LIMIT_COLUMN 500 |
||
1082 | do_execsql_test e_createtable-3.11.1 [subst { |
||
1083 | CREATE TABLE t10([columns 500]); |
||
1084 | }] {} |
||
1085 | do_catchsql_test e_createtable-3.11.2 [subst { |
||
1086 | CREATE TABLE t11([columns 501]); |
||
1087 | }] {1 {too many columns on t11}} |
||
1088 | |||
1089 | # Check that it is not possible to raise the column limit above its |
||
1090 | # default compile time value. |
||
1091 | # |
||
1092 | sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2] |
||
1093 | do_catchsql_test e_createtable-3.11.3 [subst { |
||
1094 | CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]); |
||
1095 | }] {1 {too many columns on t11}} |
||
1096 | |||
1097 | sqlite3_limit db SQLITE_LIMIT_LENGTH 90010 |
||
1098 | do_execsql_test e_createtable-3.11.4 { |
||
1099 | CREATE TABLE t12(a, b, c); |
||
1100 | INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000)); |
||
1101 | } {} |
||
1102 | do_catchsql_test e_createtable-3.11.5 { |
||
1103 | INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000)); |
||
1104 | } {1 {string or blob too big}} |
||
1105 | |||
1106 | #------------------------------------------------------------------------- |
||
1107 | # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT |
||
1108 | # NULL and CHECK constraints). |
||
1109 | # |
||
1110 | |||
1111 | # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one |
||
1112 | # PRIMARY KEY. |
||
1113 | # |
||
1114 | # EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY |
||
1115 | # clause in a single CREATE TABLE statement, it is an error. |
||
1116 | # |
||
1117 | # To test the two above, show that zero primary keys is Ok, one primary |
||
1118 | # key is Ok, and two or more primary keys is an error. |
||
1119 | # |
||
1120 | drop_all_tables |
||
1121 | do_createtable_tests 4.1.1 { |
||
1122 | 1 "CREATE TABLE t1(a, b, c)" {} |
||
1123 | 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {} |
||
1124 | 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {} |
||
1125 | 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {} |
||
1126 | } |
||
1127 | do_createtable_tests 4.1.2 -error { |
||
1128 | table "t5" has more than one primary key |
||
1129 | } { |
||
1130 | 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {} |
||
1131 | 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {} |
||
1132 | 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {} |
||
1133 | 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {} |
||
1134 | 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {} |
||
1135 | 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {} |
||
1136 | } |
||
1137 | |||
1138 | proc table_pk {tbl} { |
||
1139 | set pk [list] |
||
1140 | db eval "pragma table_info($tbl)" a { |
||
1141 | if {$a(pk)} { lappend pk $a(name) } |
||
1142 | } |
||
1143 | set pk |
||
1144 | } |
||
1145 | |||
1146 | # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a |
||
1147 | # column definition, then the primary key for the table consists of that |
||
1148 | # single column. |
||
1149 | # |
||
1150 | # The above is tested by 4.2.1.* |
||
1151 | # |
||
1152 | # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as |
||
1153 | # a table-constraint, then the primary key of the table consists of the |
||
1154 | # list of columns specified as part of the PRIMARY KEY clause. |
||
1155 | # |
||
1156 | # The above is tested by 4.2.2.* |
||
1157 | # |
||
1158 | do_createtable_tests 4.2 -repair { |
||
1159 | catchsql { DROP TABLE t5 } |
||
1160 | } -tclquery { |
||
1161 | table_pk t5 |
||
1162 | } { |
||
1163 | 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} |
||
1164 | 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a} |
||
1165 | |||
1166 | 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a} |
||
1167 | 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c} |
||
1168 | 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} |
||
1169 | } |
||
1170 | |||
1171 | # EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must |
||
1172 | # feature a unique combination of values in its primary key columns. |
||
1173 | # |
||
1174 | # EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts |
||
1175 | # to modify the table content so that two or more rows feature identical |
||
1176 | # primary key values, it is a constraint violation. |
||
1177 | # |
||
1178 | drop_all_tables |
||
1179 | do_execsql_test 4.3.0 { |
||
1180 | CREATE TABLE t1(x PRIMARY KEY, y); |
||
1181 | INSERT INTO t1 VALUES(0, 'zero'); |
||
1182 | INSERT INTO t1 VALUES(45.5, 'one'); |
||
1183 | INSERT INTO t1 VALUES('brambles', 'two'); |
||
1184 | INSERT INTO t1 VALUES(X'ABCDEF', 'three'); |
||
1185 | |||
1186 | CREATE TABLE t2(x, y, PRIMARY KEY(x, y)); |
||
1187 | INSERT INTO t2 VALUES(0, 'zero'); |
||
1188 | INSERT INTO t2 VALUES(45.5, 'one'); |
||
1189 | INSERT INTO t2 VALUES('brambles', 'two'); |
||
1190 | INSERT INTO t2 VALUES(X'ABCDEF', 'three'); |
||
1191 | } {} |
||
1192 | |||
1193 | do_createtable_tests 4.3.1 -error { %s not unique } { |
||
1194 | 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"} |
||
1195 | 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"} |
||
1196 | 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"} |
||
1197 | 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"} |
||
1198 | 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"} |
||
1199 | |||
1200 | 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"} |
||
1201 | 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"} |
||
1202 | 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"} |
||
1203 | 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"} |
||
1204 | 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"} |
||
1205 | } |
||
1206 | do_createtable_tests 4.3.2 { |
||
1207 | 1 "INSERT INTO t1 VALUES(-1, 0)" {} |
||
1208 | 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {} |
||
1209 | 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {} |
||
1210 | 4 "INSERT INTO t1 VALUES('bramble', 'abc')" {} |
||
1211 | 5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {} |
||
1212 | |||
1213 | 6 "INSERT INTO t2 VALUES(0, 0)" {} |
||
1214 | 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {} |
||
1215 | 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {} |
||
1216 | 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {} |
||
1217 | 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {} |
||
1218 | } |
||
1219 | do_createtable_tests 4.3.3 -error { %s not unique } { |
||
1220 | 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"} |
||
1221 | 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"} |
||
1222 | 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"} |
||
1223 | 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"} |
||
1224 | 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"} |
||
1225 | |||
1226 | 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"} |
||
1227 | 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'" |
||
1228 | {"columns x, y are"} |
||
1229 | 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"} |
||
1230 | 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" |
||
1231 | {"columns x, y are"} |
||
1232 | 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'" |
||
1233 | {"columns x, y are"} |
||
1234 | } |
||
1235 | |||
1236 | |||
1237 | # EVIDENCE-OF: R-52572-02078 For the purposes of determining the |
||
1238 | # uniqueness of primary key values, NULL values are considered distinct |
||
1239 | # from all other values, including other NULLs. |
||
1240 | # |
||
1241 | do_createtable_tests 4.4 { |
||
1242 | 1 "INSERT INTO t1 VALUES(NULL, 0)" {} |
||
1243 | 2 "INSERT INTO t1 VALUES(NULL, 0)" {} |
||
1244 | 3 "INSERT INTO t1 VALUES(NULL, 0)" {} |
||
1245 | |||
1246 | 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {} |
||
1247 | 5 "INSERT INTO t2 VALUES(NULL, 'one')" {} |
||
1248 | 6 "INSERT INTO t2 VALUES(NULL, 'two')" {} |
||
1249 | 7 "INSERT INTO t2 VALUES(NULL, 'three')" {} |
||
1250 | |||
1251 | 8 "INSERT INTO t2 VALUES(0, NULL)" {} |
||
1252 | 9 "INSERT INTO t2 VALUES(45.5, NULL)" {} |
||
1253 | 10 "INSERT INTO t2 VALUES(0.0, NULL)" {} |
||
1254 | 11 "INSERT INTO t2 VALUES('brambles', NULL)" {} |
||
1255 | 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {} |
||
1256 | |||
1257 | 13 "INSERT INTO t2 VALUES(NULL, NULL)" {} |
||
1258 | 14 "INSERT INTO t2 VALUES(NULL, NULL)" {} |
||
1259 | } |
||
1260 | |||
1261 | # EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY |
||
1262 | # SQLite allows NULL values in a PRIMARY KEY column. |
||
1263 | # |
||
1264 | # If the column is an integer primary key, attempting to insert a NULL |
||
1265 | # into the column triggers the auto-increment behaviour. Attempting |
||
1266 | # to use UPDATE to set an ipk column to a NULL value is an error. |
||
1267 | # |
||
1268 | do_createtable_tests 4.5.1 { |
||
1269 | 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3 |
||
1270 | 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6 |
||
1271 | 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7 |
||
1272 | 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2 |
||
1273 | } |
||
1274 | do_execsql_test 4.5.2 { |
||
1275 | CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v); |
||
1276 | INSERT INTO t3 VALUES(1, NULL, 2); |
||
1277 | INSERT INTO t3 VALUES('x', NULL, 'y'); |
||
1278 | SELECT u FROM t3; |
||
1279 | } {1 2} |
||
1280 | do_catchsql_test 4.5.3 { |
||
1281 | INSERT INTO t3 VALUES(2, 5, 3); |
||
1282 | UPDATE t3 SET u = NULL WHERE s = 2; |
||
1283 | } {1 {datatype mismatch}} |
||
1284 | |||
1285 | # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY |
||
1286 | # KEY constraint, except that a single table may have any number of |
||
1287 | # UNIQUE constraints. |
||
1288 | # |
||
1289 | drop_all_tables |
||
1290 | do_createtable_tests 4.6 { |
||
1291 | 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {} |
||
1292 | 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {} |
||
1293 | 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {} |
||
1294 | 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {} |
||
1295 | } |
||
1296 | |||
1297 | # EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table, |
||
1298 | # each row must feature a unique combination of values in the columns |
||
1299 | # identified by the UNIQUE constraint. |
||
1300 | # |
||
1301 | # EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts |
||
1302 | # to modify the table content so that two or more rows feature identical |
||
1303 | # values in a set of columns that are subject to a UNIQUE constraint, it |
||
1304 | # is a constraint violation. |
||
1305 | # |
||
1306 | do_execsql_test 4.7.0 { |
||
1307 | INSERT INTO t1 VALUES(1, 2); |
||
1308 | INSERT INTO t1 VALUES(4.3, 5.5); |
||
1309 | INSERT INTO t1 VALUES('reveal', 'variableness'); |
||
1310 | INSERT INTO t1 VALUES(X'123456', X'654321'); |
||
1311 | |||
1312 | INSERT INTO t4 VALUES('xyx', 1, 1); |
||
1313 | INSERT INTO t4 VALUES('xyx', 2, 1); |
||
1314 | INSERT INTO t4 VALUES('uvw', 1, 1); |
||
1315 | } |
||
1316 | do_createtable_tests 4.7.1 -error { %s not unique } { |
||
1317 | 1 "INSERT INTO t1 VALUES(1, 'one')" {{column a is}} |
||
1318 | 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{column a is}} |
||
1319 | 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{column a is}} |
||
1320 | 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{column a is}} |
||
1321 | |||
1322 | 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{column a is}} |
||
1323 | 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{column a is}} |
||
1324 | 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{column a is}} |
||
1325 | 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{column a is}} |
||
1326 | |||
1327 | 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{columns a, b, c are}} |
||
1328 | 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{columns a, b, c are}} |
||
1329 | 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{columns a, b, c are}} |
||
1330 | |||
1331 | 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{columns a, b, c are}} |
||
1332 | 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{columns a, b, c are}} |
||
1333 | 14 "UPDATE t4 SET a=0, b=0, c=0" {{columns a, b, c are}} |
||
1334 | } |
||
1335 | |||
1336 | # EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the |
||
1337 | # purposes of UNIQUE constraints NULL values are considered distinct |
||
1338 | # from all other values (including other NULLs). |
||
1339 | # |
||
1340 | do_createtable_tests 4.8 { |
||
1341 | 1 "INSERT INTO t1 VALUES(NULL, NULL)" {} |
||
1342 | 2 "INSERT INTO t1 VALUES(NULL, NULL)" {} |
||
1343 | 3 "UPDATE t1 SET a = NULL" {} |
||
1344 | 4 "UPDATE t1 SET b = NULL" {} |
||
1345 | |||
1346 | 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} |
||
1347 | 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} |
||
1348 | 7 "UPDATE t4 SET a = NULL" {} |
||
1349 | 8 "UPDATE t4 SET b = NULL" {} |
||
1350 | 9 "UPDATE t4 SET c = NULL" {} |
||
1351 | } |
||
1352 | |||
1353 | # EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both |
||
1354 | # UNIQUE and PRIMARY KEY constraints are implemented by creating an |
||
1355 | # index in the database (in the same way as a "CREATE UNIQUE INDEX" |
||
1356 | # statement would). |
||
1357 | do_createtable_tests 4.9 -repair drop_all_tables -query { |
||
1358 | SELECT count(*) FROM sqlite_master WHERE type='index' |
||
1359 | } { |
||
1360 | 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1 |
||
1361 | 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0 |
||
1362 | 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1 |
||
1363 | 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2 |
||
1364 | 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2 |
||
1365 | } |
||
1366 | |||
1367 | # EVIDENCE-OF: R-02252-33116 Such an index is used like any other index |
||
1368 | # in the database to optimize queries. |
||
1369 | # |
||
1370 | do_execsql_test 4.10.0 { |
||
1371 | CREATE TABLE t1(a, b PRIMARY KEY); |
||
1372 | CREATE TABLE t2(a, b, c, UNIQUE(b, c)); |
||
1373 | } |
||
1374 | do_createtable_tests 4.10 { |
||
1375 | 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" |
||
1376 | {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} |
||
1377 | |||
1378 | 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" |
||
1379 | {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} |
||
1380 | |||
1381 | 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" |
||
1382 | {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}} |
||
1383 | } |
||
1384 | |||
1385 | # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a |
||
1386 | # column definition or specified as a table constraint. In practice it |
||
1387 | # makes no difference. |
||
1388 | # |
||
1389 | # All the tests that deal with CHECK constraints below (4.11.* and |
||
1390 | # 4.12.*) are run once for a table with the check constraint attached |
||
1391 | # to a column definition, and once with a table where the check |
||
1392 | # condition is specified as a table constraint. |
||
1393 | # |
||
1394 | # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the |
||
1395 | # table or an existing row is updated, the expression associated with |
||
1396 | # each CHECK constraint is evaluated and cast to a NUMERIC value in the |
||
1397 | # same way as a CAST expression. If the result is zero (integer value 0 |
||
1398 | # or real value 0.0), then a constraint violation has occurred. |
||
1399 | # |
||
1400 | drop_all_tables |
||
1401 | do_execsql_test 4.11 { |
||
1402 | CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 )); |
||
1403 | CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 )); |
||
1404 | INSERT INTO x1 VALUES('x', 'xx'); |
||
1405 | INSERT INTO x1 VALUES('y', 'yy'); |
||
1406 | INSERT INTO t1 SELECT * FROM x1; |
||
1407 | |||
1408 | CREATE TABLE x2(a CHECK( a||b ), b); |
||
1409 | CREATE TABLE t2(a, b, CHECK( a||b )); |
||
1410 | INSERT INTO x2 VALUES(1, 'xx'); |
||
1411 | INSERT INTO x2 VALUES(1, 'yy'); |
||
1412 | INSERT INTO t2 SELECT * FROM x2; |
||
1413 | } |
||
1414 | |||
1415 | do_createtable_tests 4.11 -error {constraint failed} { |
||
1416 | 1a "INSERT INTO x1 VALUES('one', 0)" {} |
||
1417 | 1b "INSERT INTO t1 VALUES('one', -4.0)" {} |
||
1418 | |||
1419 | 2a "INSERT INTO x2 VALUES('abc', 1)" {} |
||
1420 | 2b "INSERT INTO t2 VALUES('abc', 1)" {} |
||
1421 | |||
1422 | 3a "INSERT INTO x2 VALUES(0, 'abc')" {} |
||
1423 | 3b "INSERT INTO t2 VALUES(0, 'abc')" {} |
||
1424 | |||
1425 | 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {} |
||
1426 | 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {} |
||
1427 | |||
1428 | 4a "UPDATE x2 SET a='' WHERE rowid=1" {} |
||
1429 | 4b "UPDATE t2 SET a='' WHERE rowid=1" {} |
||
1430 | } |
||
1431 | |||
1432 | # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL, |
||
1433 | # or any other non-zero value, it is not a constraint violation. |
||
1434 | # |
||
1435 | do_createtable_tests 4.12 { |
||
1436 | 1a "INSERT INTO x1 VALUES('one', NULL)" {} |
||
1437 | 1b "INSERT INTO t1 VALUES('one', NULL)" {} |
||
1438 | |||
1439 | 2a "INSERT INTO x1 VALUES('one', 2)" {} |
||
1440 | 2b "INSERT INTO t1 VALUES('one', 2)" {} |
||
1441 | |||
1442 | 3a "INSERT INTO x2 VALUES(1, 'abc')" {} |
||
1443 | 3b "INSERT INTO t2 VALUES(1, 'abc')" {} |
||
1444 | } |
||
1445 | |||
1446 | # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached |
||
1447 | # to a column definition, not specified as a table constraint. |
||
1448 | # |
||
1449 | drop_all_tables |
||
1450 | do_createtable_tests 4.13.1 { |
||
1451 | 1 "CREATE TABLE t1(a NOT NULL, b)" {} |
||
1452 | 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {} |
||
1453 | 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {} |
||
1454 | } |
||
1455 | do_createtable_tests 4.13.2 -error { |
||
1456 | near "NOT": syntax error |
||
1457 | } { |
||
1458 | 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {} |
||
1459 | 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {} |
||
1460 | 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {} |
||
1461 | } |
||
1462 | |||
1463 | # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the |
||
1464 | # associated column may not contain a NULL value. Attempting to set the |
||
1465 | # column value to NULL when inserting a new row or updating an existing |
||
1466 | # one causes a constraint violation. |
||
1467 | # |
||
1468 | # These tests use the tables created by 4.13. |
||
1469 | # |
||
1470 | do_execsql_test 4.14.0 { |
||
1471 | INSERT INTO t1 VALUES('x', 'y'); |
||
1472 | INSERT INTO t1 VALUES('z', NULL); |
||
1473 | |||
1474 | INSERT INTO t2 VALUES('x', 'y'); |
||
1475 | INSERT INTO t2 VALUES('z', NULL); |
||
1476 | |||
1477 | INSERT INTO t3 VALUES('x', 'y', 'z'); |
||
1478 | INSERT INTO t3 VALUES(1, 2, 3); |
||
1479 | } |
||
1480 | do_createtable_tests 4.14 -error { |
||
1481 | %s may not be NULL |
||
1482 | } { |
||
1483 | 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a} |
||
1484 | 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a} |
||
1485 | 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c} |
||
1486 | 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b} |
||
1487 | 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a} |
||
1488 | } |
||
1489 | |||
1490 | # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL |
||
1491 | # constraints may be explicitly assigned a default conflict resolution |
||
1492 | # algorithm by including a conflict-clause in their definitions. |
||
1493 | # |
||
1494 | # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE |
||
1495 | # |
||
1496 | # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL |
||
1497 | # and UNIQUE constraints, respectively. |
||
1498 | # |
||
1499 | drop_all_tables |
||
1500 | do_execsql_test 4.15.0 { |
||
1501 | CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b); |
||
1502 | CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b); |
||
1503 | CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b); |
||
1504 | CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b); |
||
1505 | CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b); |
||
1506 | CREATE TABLE t1_xx(a PRIMARY KEY, b); |
||
1507 | |||
1508 | INSERT INTO t1_ab VALUES(1, 'one'); |
||
1509 | INSERT INTO t1_ab VALUES(2, 'two'); |
||
1510 | INSERT INTO t1_ro SELECT * FROM t1_ab; |
||
1511 | INSERT INTO t1_ig SELECT * FROM t1_ab; |
||
1512 | INSERT INTO t1_fa SELECT * FROM t1_ab; |
||
1513 | INSERT INTO t1_re SELECT * FROM t1_ab; |
||
1514 | INSERT INTO t1_xx SELECT * FROM t1_ab; |
||
1515 | |||
1516 | CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT); |
||
1517 | CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK); |
||
1518 | CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE); |
||
1519 | CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL); |
||
1520 | CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE); |
||
1521 | CREATE TABLE t2_xx(a, b NOT NULL); |
||
1522 | |||
1523 | INSERT INTO t2_ab VALUES(1, 'one'); |
||
1524 | INSERT INTO t2_ab VALUES(2, 'two'); |
||
1525 | INSERT INTO t2_ro SELECT * FROM t2_ab; |
||
1526 | INSERT INTO t2_ig SELECT * FROM t2_ab; |
||
1527 | INSERT INTO t2_fa SELECT * FROM t2_ab; |
||
1528 | INSERT INTO t2_re SELECT * FROM t2_ab; |
||
1529 | INSERT INTO t2_xx SELECT * FROM t2_ab; |
||
1530 | |||
1531 | CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT); |
||
1532 | CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK); |
||
1533 | CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE); |
||
1534 | CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL); |
||
1535 | CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE); |
||
1536 | CREATE TABLE t3_xx(a, b, UNIQUE(a, b)); |
||
1537 | |||
1538 | INSERT INTO t3_ab VALUES(1, 'one'); |
||
1539 | INSERT INTO t3_ab VALUES(2, 'two'); |
||
1540 | INSERT INTO t3_ro SELECT * FROM t3_ab; |
||
1541 | INSERT INTO t3_ig SELECT * FROM t3_ab; |
||
1542 | INSERT INTO t3_fa SELECT * FROM t3_ab; |
||
1543 | INSERT INTO t3_re SELECT * FROM t3_ab; |
||
1544 | INSERT INTO t3_xx SELECT * FROM t3_ab; |
||
1545 | } |
||
1546 | |||
1547 | foreach {tn tbl res ac data} { |
||
1548 | 1 t1_ab {1 {column a is not unique}} 0 {1 one 2 two 3 three} |
||
1549 | 2 t1_ro {1 {column a is not unique}} 1 {1 one 2 two} |
||
1550 | 3 t1_fa {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string} |
||
1551 | 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string} |
||
1552 | 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string} |
||
1553 | 6 t1_xx {1 {column a is not unique}} 0 {1 one 2 two 3 three} |
||
1554 | } { |
||
1555 | catchsql COMMIT |
||
1556 | do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" |
||
1557 | |||
1558 | do_catchsql_test 4.15.$tn.2 " |
||
1559 | INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl; |
||
1560 | " $res |
||
1561 | |||
1562 | do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac |
||
1563 | do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data |
||
1564 | } |
||
1565 | foreach {tn tbl res ac data} { |
||
1566 | 1 t2_ab {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three} |
||
1567 | 2 t2_ro {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two} |
||
1568 | 3 t2_fa {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx} |
||
1569 | 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx} |
||
1570 | 5 t2_re {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three} |
||
1571 | 6 t2_xx {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three} |
||
1572 | } { |
||
1573 | catchsql COMMIT |
||
1574 | do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" |
||
1575 | |||
1576 | do_catchsql_test 4.16.$tn.2 " |
||
1577 | INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl |
||
1578 | " $res |
||
1579 | |||
1580 | do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac |
||
1581 | do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data |
||
1582 | } |
||
1583 | foreach {tn tbl res ac data} { |
||
1584 | 1 t3_ab {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three} |
||
1585 | 2 t3_ro {1 {columns a, b are not unique}} 1 {1 one 2 two} |
||
1586 | 3 t3_fa {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three} |
||
1587 | 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three} |
||
1588 | 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three} |
||
1589 | 6 t3_xx {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three} |
||
1590 | } { |
||
1591 | catchsql COMMIT |
||
1592 | do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" |
||
1593 | |||
1594 | do_catchsql_test 4.17.$tn.2 " |
||
1595 | INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl |
||
1596 | " $res |
||
1597 | |||
1598 | do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac |
||
1599 | do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data |
||
1600 | } |
||
1601 | catchsql COMMIT |
||
1602 | |||
1603 | # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not |
||
1604 | # include a conflict-clause or it is a CHECK constraint, the default |
||
1605 | # conflict resolution algorithm is ABORT. |
||
1606 | # |
||
1607 | # The first half of the above is tested along with explicit ON |
||
1608 | # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx |
||
1609 | # and t3_xx). The following just tests that the default conflict |
||
1610 | # handling for CHECK constraints is ABORT. |
||
1611 | # |
||
1612 | do_execsql_test 4.18.1 { |
||
1613 | CREATE TABLE t4(a, b CHECK (b!=10)); |
||
1614 | INSERT INTO t4 VALUES(1, 2); |
||
1615 | INSERT INTO t4 VALUES(3, 4); |
||
1616 | } |
||
1617 | do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) } |
||
1618 | do_catchsql_test 4.18.3 { |
||
1619 | INSERT INTO t4 SELECT a+4, b+4 FROM t4 |
||
1620 | } {1 {constraint failed}} |
||
1621 | do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0 |
||
1622 | do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6} |
||
1623 | |||
1624 | # EVIDENCE-OF: R-19114-56113 Different constraints within the same table |
||
1625 | # may have different default conflict resolution algorithms. |
||
1626 | # |
||
1627 | do_execsql_test 4.19.0 { |
||
1628 | CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT); |
||
1629 | } |
||
1630 | do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}} |
||
1631 | do_execsql_test 4.19.2 { SELECT * FROM t5 } {} |
||
1632 | do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \ |
||
1633 | {1 {t5.b may not be NULL}} |
||
1634 | do_execsql_test 4.19.4 { SELECT * FROM t5 } {} |
||
1635 | |||
1636 | #------------------------------------------------------------------------ |
||
1637 | # Tests for INTEGER PRIMARY KEY and rowid related statements. |
||
1638 | # |
||
1639 | |||
1640 | # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one |
||
1641 | # of the special case-independent names "rowid", "oid", or "_rowid_" in |
||
1642 | # place of a column name. |
||
1643 | # |
||
1644 | drop_all_tables |
||
1645 | do_execsql_test 5.1.0 { |
||
1646 | CREATE TABLE t1(x, y); |
||
1647 | INSERT INTO t1 VALUES('one', 'first'); |
||
1648 | INSERT INTO t1 VALUES('two', 'second'); |
||
1649 | INSERT INTO t1 VALUES('three', 'third'); |
||
1650 | } |
||
1651 | do_createtable_tests 5.1 { |
||
1652 | 1 "SELECT rowid FROM t1" {1 2 3} |
||
1653 | 2 "SELECT oid FROM t1" {1 2 3} |
||
1654 | 3 "SELECT _rowid_ FROM t1" {1 2 3} |
||
1655 | 4 "SELECT ROWID FROM t1" {1 2 3} |
||
1656 | 5 "SELECT OID FROM t1" {1 2 3} |
||
1657 | 6 "SELECT _ROWID_ FROM t1" {1 2 3} |
||
1658 | 7 "SELECT RoWiD FROM t1" {1 2 3} |
||
1659 | 8 "SELECT OiD FROM t1" {1 2 3} |
||
1660 | 9 "SELECT _RoWiD_ FROM t1" {1 2 3} |
||
1661 | } |
||
1662 | |||
1663 | # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column |
||
1664 | # named "rowid", "oid" or "_rowid_", then that name always refers the |
||
1665 | # explicitly declared column and cannot be used to retrieve the integer |
||
1666 | # rowid value. |
||
1667 | # |
||
1668 | do_execsql_test 5.2.0 { |
||
1669 | CREATE TABLE t2(oid, b); |
||
1670 | CREATE TABLE t3(a, _rowid_); |
||
1671 | CREATE TABLE t4(a, b, rowid); |
||
1672 | |||
1673 | INSERT INTO t2 VALUES('one', 'two'); |
||
1674 | INSERT INTO t2 VALUES('three', 'four'); |
||
1675 | |||
1676 | INSERT INTO t3 VALUES('five', 'six'); |
||
1677 | INSERT INTO t3 VALUES('seven', 'eight'); |
||
1678 | |||
1679 | INSERT INTO t4 VALUES('nine', 'ten', 'eleven'); |
||
1680 | INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen'); |
||
1681 | } |
||
1682 | do_createtable_tests 5.2 { |
||
1683 | 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2} |
||
1684 | 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight} |
||
1685 | 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2} |
||
1686 | } |
||
1687 | |||
1688 | |||
1689 | # Argument $tbl is the name of a table in the database. Argument $col is |
||
1690 | # the name of one of the tables columns. Return 1 if $col is an alias for |
||
1691 | # the rowid, or 0 otherwise. |
||
1692 | # |
||
1693 | proc is_integer_primary_key {tbl col} { |
||
1694 | lindex [db eval [subst { |
||
1695 | DELETE FROM $tbl; |
||
1696 | INSERT INTO $tbl ($col) VALUES(0); |
||
1697 | SELECT (rowid==$col) FROM $tbl; |
||
1698 | DELETE FROM $tbl; |
||
1699 | }]] 0 |
||
1700 | } |
||
1701 | |||
1702 | # EVIDENCE-OF: R-53738-31673 With one exception, if a table has a |
||
1703 | # primary key that consists of a single column, and the declared type of |
||
1704 | # that column is "INTEGER" in any mixture of upper and lower case, then |
||
1705 | # the column becomes an alias for the rowid. |
||
1706 | # |
||
1707 | # EVIDENCE-OF: R-45951-08347 if the declaration of a column with |
||
1708 | # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does |
||
1709 | # not become an alias for the rowid and is not classified as an integer |
||
1710 | # primary key. |
||
1711 | # |
||
1712 | do_createtable_tests 5.3 -tclquery { |
||
1713 | is_integer_primary_key t5 pk |
||
1714 | } -repair { |
||
1715 | catchsql { DROP TABLE t5 } |
||
1716 | } { |
||
1717 | 1 "CREATE TABLE t5(pk integer primary key)" 1 |
||
1718 | 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1 |
||
1719 | 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1 |
||
1720 | 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0 |
||
1721 | 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0 |
||
1722 | 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0 |
||
1723 | 7 "CREATE TABLE t5(pk int primary key, v integer)" 0 |
||
1724 | 8 "CREATE TABLE t5(pk inTEger primary key)" 1 |
||
1725 | 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1 |
||
1726 | 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1 |
||
1727 | } |
||
1728 | |||
1729 | # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or |
||
1730 | # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary |
||
1731 | # key column to behave as an ordinary table column with integer affinity |
||
1732 | # and a unique index, not as an alias for the rowid. |
||
1733 | # |
||
1734 | do_execsql_test 5.4.1 { |
||
1735 | CREATE TABLE t6(pk INT primary key); |
||
1736 | CREATE TABLE t7(pk BIGINT primary key); |
||
1737 | CREATE TABLE t8(pk SHORT INTEGER primary key); |
||
1738 | CREATE TABLE t9(pk UNSIGNED INTEGER primary key); |
||
1739 | } |
||
1740 | do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0 |
||
1741 | do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0 |
||
1742 | do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0 |
||
1743 | do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0 |
||
1744 | |||
1745 | do_execsql_test 5.4.3 { |
||
1746 | INSERT INTO t6 VALUES('2.0'); |
||
1747 | INSERT INTO t7 VALUES('2.0'); |
||
1748 | INSERT INTO t8 VALUES('2.0'); |
||
1749 | INSERT INTO t9 VALUES('2.0'); |
||
1750 | SELECT typeof(pk), pk FROM t6; |
||
1751 | SELECT typeof(pk), pk FROM t7; |
||
1752 | SELECT typeof(pk), pk FROM t8; |
||
1753 | SELECT typeof(pk), pk FROM t9; |
||
1754 | } {integer 2 integer 2 integer 2 integer 2} |
||
1755 | |||
1756 | do_catchsql_test 5.4.4.1 { |
||
1757 | INSERT INTO t6 VALUES(2) |
||
1758 | } {1 {column pk is not unique}} |
||
1759 | do_catchsql_test 5.4.4.2 { |
||
1760 | INSERT INTO t7 VALUES(2) |
||
1761 | } {1 {column pk is not unique}} |
||
1762 | do_catchsql_test 5.4.4.3 { |
||
1763 | INSERT INTO t8 VALUES(2) |
||
1764 | } {1 {column pk is not unique}} |
||
1765 | do_catchsql_test 5.4.4.4 { |
||
1766 | INSERT INTO t9 VALUES(2) |
||
1767 | } {1 {column pk is not unique}} |
||
1768 | |||
1769 | # EVIDENCE-OF: R-56094-57830 the following three table declarations all |
||
1770 | # cause the column "x" to be an alias for the rowid (an integer primary |
||
1771 | # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE |
||
1772 | # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y, |
||
1773 | # z, PRIMARY KEY(x DESC)); |
||
1774 | # |
||
1775 | # EVIDENCE-OF: R-20149-25884 the following declaration does not result |
||
1776 | # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY |
||
1777 | # KEY DESC, y, z); |
||
1778 | # |
||
1779 | do_createtable_tests 5 -tclquery { |
||
1780 | is_integer_primary_key t x |
||
1781 | } -repair { |
||
1782 | catchsql { DROP TABLE t } |
||
1783 | } { |
||
1784 | 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1 |
||
1785 | 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1 |
||
1786 | 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1 |
||
1787 | 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0 |
||
1788 | } |
||
1789 | |||
1790 | # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an |
||
1791 | # UPDATE statement in the same way as any other column value can, either |
||
1792 | # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by |
||
1793 | # using an alias created by an integer primary key. |
||
1794 | # |
||
1795 | do_execsql_test 5.7.0 { |
||
1796 | CREATE TABLE t10(a, b); |
||
1797 | INSERT INTO t10 VALUES('ten', 10); |
||
1798 | |||
1799 | CREATE TABLE t11(a, b INTEGER PRIMARY KEY); |
||
1800 | INSERT INTO t11 VALUES('ten', 10); |
||
1801 | } |
||
1802 | do_createtable_tests 5.7.1 -query { |
||
1803 | SELECT rowid, _rowid_, oid FROM t10; |
||
1804 | } { |
||
1805 | 1 "UPDATE t10 SET rowid = 5" {5 5 5} |
||
1806 | 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6} |
||
1807 | 3 "UPDATE t10 SET oid = 7" {7 7 7} |
||
1808 | } |
||
1809 | do_createtable_tests 5.7.2 -query { |
||
1810 | SELECT rowid, _rowid_, oid, b FROM t11; |
||
1811 | } { |
||
1812 | 1 "UPDATE t11 SET rowid = 5" {5 5 5 5} |
||
1813 | 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6} |
||
1814 | 3 "UPDATE t11 SET oid = 7" {7 7 7 7} |
||
1815 | 4 "UPDATE t11 SET b = 8" {8 8 8 8} |
||
1816 | } |
||
1817 | |||
1818 | # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide |
||
1819 | # a value to use as the rowid for each row inserted. |
||
1820 | # |
||
1821 | do_createtable_tests 5.8.1 -query { |
||
1822 | SELECT rowid, _rowid_, oid FROM t10; |
||
1823 | } -repair { |
||
1824 | execsql { DELETE FROM t10 } |
||
1825 | } { |
||
1826 | 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15} |
||
1827 | 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16} |
||
1828 | 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17} |
||
1829 | 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3} |
||
1830 | } |
||
1831 | do_createtable_tests 5.8.2 -query { |
||
1832 | SELECT rowid, _rowid_, oid, b FROM t11; |
||
1833 | } -repair { |
||
1834 | execsql { DELETE FROM t11 } |
||
1835 | } { |
||
1836 | 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15} |
||
1837 | 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16} |
||
1838 | 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17} |
||
1839 | 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2} |
||
1840 | } |
||
1841 | |||
1842 | # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer |
||
1843 | # primary key or rowid column must contain integer values. Integer |
||
1844 | # primary key or rowid columns are not able to hold floating point |
||
1845 | # values, strings, BLOBs, or NULLs. |
||
1846 | # |
||
1847 | # This is considered by the tests for the following 3 statements, |
||
1848 | # which show that: |
||
1849 | # |
||
1850 | # 1. Attempts to UPDATE a rowid column to a non-integer value fail, |
||
1851 | # 2. Attempts to INSERT a real, string or blob value into a rowid |
||
1852 | # column fail, and |
||
1853 | # 3. Attempting to INSERT a NULL value into a rowid column causes the |
||
1854 | # system to automatically select an integer value to use. |
||
1855 | # |
||
1856 | |||
1857 | |||
1858 | # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an |
||
1859 | # integer primary key or rowid column to a NULL or blob value, or to a |
||
1860 | # string or real value that cannot be losslessly converted to an |
||
1861 | # integer, a "datatype mismatch" error occurs and the statement is |
||
1862 | # aborted. |
||
1863 | # |
||
1864 | drop_all_tables |
||
1865 | do_execsql_test 5.9.0 { |
||
1866 | CREATE TABLE t12(x INTEGER PRIMARY KEY, y); |
||
1867 | INSERT INTO t12 VALUES(5, 'five'); |
||
1868 | } |
||
1869 | do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } { |
||
1870 | 1 "UPDATE t12 SET x = 4" {integer 4} |
||
1871 | 2 "UPDATE t12 SET x = 10.0" {integer 10} |
||
1872 | 3 "UPDATE t12 SET x = '12.0'" {integer 12} |
||
1873 | 4 "UPDATE t12 SET x = '-15.0'" {integer -15} |
||
1874 | } |
||
1875 | do_createtable_tests 5.9.2 -error { |
||
1876 | datatype mismatch |
||
1877 | } { |
||
1878 | 1 "UPDATE t12 SET x = 4.1" {} |
||
1879 | 2 "UPDATE t12 SET x = 'hello'" {} |
||
1880 | 3 "UPDATE t12 SET x = NULL" {} |
||
1881 | 4 "UPDATE t12 SET x = X'ABCD'" {} |
||
1882 | 5 "UPDATE t12 SET x = X'3900'" {} |
||
1883 | 6 "UPDATE t12 SET x = X'39'" {} |
||
1884 | } |
||
1885 | |||
1886 | # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a |
||
1887 | # blob value, or a string or real value that cannot be losslessly |
||
1888 | # converted to an integer into an integer primary key or rowid column, a |
||
1889 | # "datatype mismatch" error occurs and the statement is aborted. |
||
1890 | # |
||
1891 | do_execsql_test 5.10.0 { DELETE FROM t12 } |
||
1892 | do_createtable_tests 5.10.1 -error { |
||
1893 | datatype mismatch |
||
1894 | } { |
||
1895 | 1 "INSERT INTO t12(x) VALUES(4.1)" {} |
||
1896 | 2 "INSERT INTO t12(x) VALUES('hello')" {} |
||
1897 | 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {} |
||
1898 | 4 "INSERT INTO t12(x) VALUES(X'3900')" {} |
||
1899 | 5 "INSERT INTO t12(x) VALUES(X'39')" {} |
||
1900 | } |
||
1901 | do_createtable_tests 5.10.2 -query { |
||
1902 | SELECT typeof(x), x FROM t12 |
||
1903 | } -repair { |
||
1904 | execsql { DELETE FROM t12 } |
||
1905 | } { |
||
1906 | 1 "INSERT INTO t12(x) VALUES(4)" {integer 4} |
||
1907 | 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10} |
||
1908 | 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12} |
||
1909 | 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000} |
||
1910 | 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14} |
||
1911 | } |
||
1912 | |||
1913 | # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a |
||
1914 | # NULL value into a rowid or integer primary key column, the system |
||
1915 | # chooses an integer value to use as the rowid automatically. |
||
1916 | # |
||
1917 | do_execsql_test 5.11.0 { DELETE FROM t12 } |
||
1918 | do_createtable_tests 5.11 -query { |
||
1919 | SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12) |
||
1920 | } { |
||
1921 | 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1} |
||
1922 | 2 "INSERT INTO t12(y) VALUES(5)" {integer 2} |
||
1923 | 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3} |
||
1924 | 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" |
||
1925 | {integer 4 integer 5 integer 6} |
||
1926 | 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3" |
||
1927 | {integer 7 integer 8 integer 9} |
||
1928 | } |
||
1929 | |||
1930 | finish_test |