File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / e_createtable.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    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>