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