Annotation of embedaddon/sqlite3/test/e_createtable.test, revision 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