/ Check-in [20e16fef]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add new file e_createtable.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 20e16fef55c355a1d7e97d0c390769b941e83fdb
User & Date: dan 2010-09-25 17:29:58
Context
2010-09-25
22:32
Enhance the ANALYZE command so that it gathers statistics in the sqlite_stat1 table even for tables that are empty or have no indices. check-in: a7645d29 user: drh tags: trunk
17:29
Add new file e_createtable.test. check-in: 20e16fef user: dan tags: trunk
14:13
Do not call gethostuuid() on MacOS 10.4 and earlier, since it is not supported there. check-in: 44deaaef user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/e_createtable.test.

            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  +proc do_createtable_tests {nm args} {
           20  +  uplevel do_select_tests [list e_createtable-$nm] $args
           21  +}
           22  +
           23  +# EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
           24  +#
           25  +do_createtable_tests 0.1.1 -repair {
           26  +  drop_all_tables
           27  +} {
           28  +  1   "CREATE TABLE t1(c1 one)"                        {}
           29  +  2   "CREATE TABLE t1(c1 one two)"                    {}
           30  +  3   "CREATE TABLE t1(c1 one two three)"              {}
           31  +  4   "CREATE TABLE t1(c1 one two three four)"         {}
           32  +  5   "CREATE TABLE t1(c1 one two three four(14))"     {}
           33  +  6   "CREATE TABLE t1(c1 one two three four(14, 22))" {}
           34  +  7   "CREATE TABLE t1(c1 var(+14, -22.3))"            {}
           35  +  8   "CREATE TABLE t1(c1 var(1.0e10))"                {}
           36  +}
           37  +do_createtable_tests 0.1.2 -error {
           38  +  near "%s": syntax error
           39  +} {
           40  +  1   "CREATE TABLE t1(c1 one(number))"                {number}
           41  +}
           42  +
           43  +
           44  +# EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint
           45  +#
           46  +#   Note: Not shown in the syntax diagram is the "NULL" constraint. This
           47  +#         is the opposite of "NOT NULL" - it implies that the column may
           48  +#         take a NULL value. This is the default anyway, so this type of
           49  +#         constraint is rarely used.
           50  +#
           51  +do_createtable_tests 0.2.1 -repair {
           52  +  drop_all_tables 
           53  +  execsql { CREATE TABLE t2(x PRIMARY KEY) }
           54  +} {
           55  +  1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
           56  +  1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}
           57  +  1.3   "CREATE TABLE t1(c1 text PRIMARY KEY DESC)"                    {}
           58  +  1.4   "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)"    {}
           59  +
           60  +  2.1   "CREATE TABLE t1(c1 text NOT NULL)"                            {}
           61  +  2.2   "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)"              {}
           62  +  2.3   "CREATE TABLE t1(c1 text NULL)"                                {}
           63  +  2.4   "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)"                  {}
           64  +
           65  +  3.1   "CREATE TABLE t1(c1 text UNIQUE)"                              {}
           66  +  3.2   "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)"                {}
           67  +
           68  +  4.1   "CREATE TABLE t1(c1 text CHECK(c1!=0))"                        {}
           69  +  4.2   "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))"         {}
           70  +
           71  +  5.1   "CREATE TABLE t1(c1 text DEFAULT 1)"                           {}
           72  +  5.2   "CREATE TABLE t1(c1 text DEFAULT -1)"                          {}
           73  +  5.3   "CREATE TABLE t1(c1 text DEFAULT +1)"                          {}
           74  +  5.4   "CREATE TABLE t1(c1 text DEFAULT -45.8e22)"                    {}
           75  +  5.5   "CREATE TABLE t1(c1 text DEFAULT (1+1))"                       {}
           76  +  5.6   "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))"    {}
           77  +
           78  +  6.1   "CREATE TABLE t1(c1 text COLLATE nocase)"        {}
           79  +  6.2   "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)"     {}
           80  +
           81  +  7.1   "CREATE TABLE t1(c1 REFERENCES t2)"                            {}
           82  +  7.2   "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)"             {}
           83  +
           84  +  8.1   {
           85  +    CREATE TABLE t1(c1 
           86  +      PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
           87  +    );
           88  +  } {}
           89  +  8.2   {
           90  +    CREATE TABLE t1(c1 
           91  +      REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 
           92  +    );
           93  +  } {}
           94  +}
           95  +
           96  +# EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint
           97  +#
           98  +do_createtable_tests 0.3.1 -repair {
           99  +  drop_all_tables 
          100  +  execsql { CREATE TABLE t2(x PRIMARY KEY) }
          101  +} {
          102  +  1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
          103  +  1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
          104  +  1.3   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)"  {}
          105  +
          106  +  2.1   "CREATE TABLE t1(c1, c2, UNIQUE(c1))"                              {}
          107  +  2.2   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))"                          {}
          108  +  2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}
          109  +
          110  +  3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}
          111  +
          112  +  4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
          113  +}
          114  +
          115  +# EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def
          116  +#
          117  +do_createtable_tests 0.4.1 -repair {
          118  +  drop_all_tables 
          119  +} {
          120  +  1     {CREATE TABLE t1(
          121  +           col1,
          122  +           col2 TEXT,
          123  +           col3 INTEGER UNIQUE,
          124  +           col4 VARCHAR(10, 10) PRIMARY KEY,
          125  +           "name with spaces" REFERENCES t1
          126  +         );
          127  +        } {}
          128  +}
          129  +
          130  +# EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt
          131  +#
          132  +do_createtable_tests 0.5.1 -repair {
          133  +  drop_all_tables 
          134  +  execsql { CREATE TABLE t2(a, b, c) }
          135  +} {
          136  +  1     "CREATE TABLE t1(a, b, c)"                                    {}
          137  +  2     "CREATE TEMP TABLE t1(a, b, c)"                               {}
          138  +  3     "CREATE TEMPORARY TABLE t1(a, b, c)"                          {}
          139  +  4     "CREATE TABLE IF NOT EXISTS t1(a, b, c)"                      {}
          140  +  5     "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)"                 {}
          141  +  6     "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)"            {}
          142  +
          143  +  7     "CREATE TABLE main.t1(a, b, c)"                               {}
          144  +  8     "CREATE TEMP TABLE temp.t1(a, b, c)"                          {}
          145  +  9     "CREATE TEMPORARY TABLE temp.t1(a, b, c)"                     {}
          146  +  10    "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)"                 {}
          147  +  11    "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)"            {}
          148  +  12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}
          149  +
          150  +  13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
          151  +  14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
          152  +  15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
          153  +}
          154  +
          155  +# EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause
          156  +#
          157  +#   1:         Explicit parent-key columns.
          158  +#   2:         Implicit child-key columns.
          159  +#
          160  +#   1:         MATCH FULL
          161  +#   2:         MATCH PARTIAL
          162  +#   3:         MATCH SIMPLE
          163  +#   4:         MATCH STICK
          164  +#   5:         
          165  +#
          166  +#   1:         ON DELETE SET NULL
          167  +#   2:         ON DELETE SET DEFAULT
          168  +#   3:         ON DELETE CASCADE
          169  +#   4:         ON DELETE RESTRICT
          170  +#   5:         ON DELETE NO ACTION
          171  +#   6:
          172  +#
          173  +#   1:         ON UPDATE SET NULL
          174  +#   2:         ON UPDATE SET DEFAULT
          175  +#   3:         ON UPDATE CASCADE
          176  +#   4:         ON UPDATE RESTRICT
          177  +#   5:         ON UPDATE NO ACTION
          178  +#   6:
          179  +#
          180  +#   1:         NOT DEFERRABLE INITIALLY DEFERRED
          181  +#   2:         NOT DEFERRABLE INITIALLY IMMEDIATE
          182  +#   3:         NOT DEFERRABLE
          183  +#   4:         DEFERRABLE INITIALLY DEFERRED
          184  +#   5:         DEFERRABLE INITIALLY IMMEDIATE
          185  +#   6:         DEFERRABLE
          186  +#   7:         
          187  +#
          188  +do_createtable_tests 0.6.1 -repair {
          189  +  drop_all_tables 
          190  +  execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
          191  +  execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
          192  +} {
          193  +  11146 { CREATE TABLE t1(a 
          194  +    REFERENCES t2(x) MATCH FULL 
          195  +    ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
          196  +  )} {}
          197  +  11412 { CREATE TABLE t1(a 
          198  +    REFERENCES t2(x) 
          199  +    ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL 
          200  +    NOT DEFERRABLE INITIALLY IMMEDIATE
          201  +  )} {}
          202  +  12135 { CREATE TABLE t1(a 
          203  +    REFERENCES t2(x) MATCH PARTIAL 
          204  +    ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
          205  +  )} {}
          206  +  12427 { CREATE TABLE t1(a 
          207  +    REFERENCES t2(x) MATCH PARTIAL 
          208  +    ON DELETE RESTRICT ON UPDATE SET DEFAULT 
          209  +  )} {}
          210  +  12446 { CREATE TABLE t1(a 
          211  +    REFERENCES t2(x) MATCH PARTIAL 
          212  +    ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
          213  +  )} {}
          214  +  12522 { CREATE TABLE t1(a 
          215  +    REFERENCES t2(x) MATCH PARTIAL 
          216  +    ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
          217  +  )} {}
          218  +  13133 { CREATE TABLE t1(a 
          219  +    REFERENCES t2(x) MATCH SIMPLE 
          220  +    ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
          221  +  )} {}
          222  +  13216 { CREATE TABLE t1(a 
          223  +    REFERENCES t2(x) MATCH SIMPLE 
          224  +    ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
          225  +  )} {}
          226  +  13263 { CREATE TABLE t1(a 
          227  +    REFERENCES t2(x) MATCH SIMPLE 
          228  +    ON DELETE SET DEFAULT  NOT DEFERRABLE
          229  +  )} {}
          230  +  13421 { CREATE TABLE t1(a 
          231  +    REFERENCES t2(x) MATCH SIMPLE 
          232  +    ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
          233  +  )} {}
          234  +  13432 { CREATE TABLE t1(a 
          235  +    REFERENCES t2(x) MATCH SIMPLE 
          236  +    ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
          237  +  )} {}
          238  +  13523 { CREATE TABLE t1(a 
          239  +    REFERENCES t2(x) MATCH SIMPLE 
          240  +    ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
          241  +  )} {}
          242  +  14336 { CREATE TABLE t1(a 
          243  +    REFERENCES t2(x) MATCH STICK 
          244  +    ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
          245  +  )} {}
          246  +  14611 { CREATE TABLE t1(a 
          247  +    REFERENCES t2(x) MATCH STICK 
          248  +    ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
          249  +  )} {}
          250  +  15155 { CREATE TABLE t1(a 
          251  +    REFERENCES t2(x)
          252  +    ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
          253  +  )} {}
          254  +  15453 { CREATE TABLE t1(a 
          255  +    REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
          256  +  )} {}
          257  +  15661 { CREATE TABLE t1(a 
          258  +    REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
          259  +  )} {}
          260  +  21115 { CREATE TABLE t1(a 
          261  +    REFERENCES t2 MATCH FULL 
          262  +    ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
          263  +  )} {}
          264  +  21123 { CREATE TABLE t1(a 
          265  +    REFERENCES t2 MATCH FULL 
          266  +    ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
          267  +  )} {}
          268  +  21217 { CREATE TABLE t1(a 
          269  +    REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL 
          270  +  )} {}
          271  +  21362 { CREATE TABLE t1(a 
          272  +    REFERENCES t2 MATCH FULL 
          273  +    ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
          274  +  )} {}
          275  +  22143 { CREATE TABLE t1(a 
          276  +    REFERENCES t2 MATCH PARTIAL 
          277  +    ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
          278  +  )} {}
          279  +  22156 { CREATE TABLE t1(a 
          280  +    REFERENCES t2 MATCH PARTIAL 
          281  +    ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
          282  +  )} {}
          283  +  22327 { CREATE TABLE t1(a 
          284  +    REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT 
          285  +  )} {}
          286  +  22663 { CREATE TABLE t1(a 
          287  +    REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
          288  +  )} {}
          289  +  23236 { CREATE TABLE t1(a 
          290  +    REFERENCES t2 MATCH SIMPLE 
          291  +    ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
          292  +  )} {}
          293  +  24155 { CREATE TABLE t1(a 
          294  +    REFERENCES t2 MATCH STICK 
          295  +    ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
          296  +  )} {}
          297  +  24522 { CREATE TABLE t1(a 
          298  +    REFERENCES t2 MATCH STICK 
          299  +    ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
          300  +  )} {}
          301  +  24625 { CREATE TABLE t1(a 
          302  +    REFERENCES t2 MATCH STICK 
          303  +    ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
          304  +  )} {}
          305  +  25454 { CREATE TABLE t1(a 
          306  +    REFERENCES t2 
          307  +    ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
          308  +  )} {}
          309  +}
          310  +
          311  +finish_test

Changes to test/e_insert.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12         -# This file implements tests to verify that the "testable statements" in 
    13         -# the lang_insert.html document are correct.
           12  +# The majority of this file implements tests to verify that the "testable
           13  +# statements" in the lang_insert.html document are correct.
           14  +#
           15  +# Also, it contains tests to verify the statements in (the very short)
           16  +# lang_replace.html.
    14     17   #
    15     18   set testdir [file dirname $argv0]
    16     19   source $testdir/tester.tcl
    17     20   
    18     21   # Organization of tests:
    19     22   #
    20     23   #   e_insert-0.*: Test the syntax diagram.
................................................................................
   326    329   # algorithm to use during this one INSERT command.
   327    330   #
   328    331   # EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
   329    332   # keyword REPLACE as an alias for "INSERT OR REPLACE".
   330    333   #
   331    334   #    The two requirements above are tested by e_select-4.1.* and
   332    335   #    e_select-4.2.*, respectively.
          336  +#
          337  +# EVIDENCE-OF: R-03421-22330 The REPLACE command is an alias for the
          338  +# "INSERT OR REPLACE" variant of the INSERT command.
          339  +#
          340  +#    This is a dup of R-23110-47146. Therefore it is also verified 
          341  +#    by e_select-4.2.*. This requirement is the only one from
          342  +#    lang_replace.html.
   333    343   #
   334    344   do_execsql_test e_insert-4.1.0 {
   335    345     INSERT INTO a4 VALUES(1, 'a');
   336    346     INSERT INTO a4 VALUES(2, 'a');
   337    347     INSERT INTO a4 VALUES(3, 'a');
   338    348   } {}
   339    349   foreach {tn sql error ac data } {