/ Check-in [0a4528d6]
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 further tests to e_createtable.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0a4528d629018eae0b0f3e173ebda666c2e2d502
User & Date: dan 2010-09-30 18:43:14
Context
2010-09-30
20:33
Merge experimental into trunk: Refactor the text-to-numeric conversion routines to work without zero-terminators and in UTF16 as well as UTF8. Avoid invalidating strings with doing affinity conversions. check-in: 07ee080e user: drh tags: trunk
18:43
Add further tests to e_createtable.test. check-in: 0a4528d6 user: dan tags: trunk
2010-09-29
18:26
Add test cases to e_createtable.test. check-in: f34dc54d user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_createtable.test.

    12     12   # This file implements tests to verify that the "testable statements" in 
    13     13   # the lang_createtable.html document are correct.
    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
           19  +set ::testprefix e_createtable
           20  +
    19     21   # Test organization:
    20     22   #
    21     23   #   e_createtable-0.*: Test that the syntax diagrams are correct.
    22     24   #
    23     25   #   e_createtable-1.*: Test statements related to table and database names, 
    24     26   #       the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
    25     27   #
................................................................................
  1049   1051   do_createtable_tests 3.9 {
  1050   1052     2    "SELECT a FROM t8 ORDER BY a, rowid"    {abc ABC {abc  } {ABC  }}
  1051   1053     3    "SELECT b FROM t8 ORDER BY b, rowid"    {{ABC  } ABC abc {abc  }}
  1052   1054     4    "SELECT c FROM t8 ORDER BY c, rowid"    {ABC {ABC  } abc {abc  }}
  1053   1055     5    "SELECT d FROM t8 ORDER BY d, rowid"    {ABC {ABC  } abc {abc  }}
  1054   1056   }
  1055   1057   
         1058  +# EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
         1059  +# by the SQLITE_MAX_COLUMN compile-time parameter.
         1060  +#
         1061  +proc columns {n} {
         1062  +  set res [list]
         1063  +  for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
         1064  +  join $res ", "
         1065  +}
         1066  +do_execsql_test e_createtable-3.10.1 [subst {
         1067  +  CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
         1068  +}] {}
         1069  +do_catchsql_test e_createtable-3.10.2 [subst {
         1070  +  CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
         1071  +}] {1 {too many columns on t10}}
         1072  +
         1073  +# EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
         1074  +# runtime using the sqlite3_limit() C/C++ interface.
         1075  +#
         1076  +#   A 30,000 byte blob consumes 30,003 bytes of record space. A record 
         1077  +#   that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
         1078  +#   3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
         1079  +#   at runtime, are based on this calculation.
         1080  +#
         1081  +sqlite3_limit db SQLITE_LIMIT_COLUMN 500
         1082  +do_execsql_test e_createtable-3.11.1 [subst {
         1083  +  CREATE TABLE t10([columns 500]);
         1084  +}] {}
         1085  +do_catchsql_test e_createtable-3.11.2 [subst {
         1086  +  CREATE TABLE t11([columns 501]);
         1087  +}] {1 {too many columns on t11}}
         1088  +
         1089  +# Check that it is not possible to raise the column limit above its 
         1090  +# default compile time value.
         1091  +#
         1092  +sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
         1093  +do_catchsql_test e_createtable-3.11.3 [subst {
         1094  +  CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
         1095  +}] {1 {too many columns on t11}}
         1096  +
         1097  +sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
         1098  +do_execsql_test e_createtable-3.11.4 {
         1099  +  CREATE TABLE t12(a, b, c);
         1100  +  INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
         1101  +} {}
         1102  +do_catchsql_test e_createtable-3.11.5 {
         1103  +  INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
         1104  +} {1 {string or blob too big}}
         1105  +
         1106  +#-------------------------------------------------------------------------
         1107  +# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT 
         1108  +# NULL and CHECK constraints).
         1109  +#
         1110  +
         1111  +# EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
         1112  +# PRIMARY KEY.
         1113  +# 
         1114  +# EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY
         1115  +# clause in a single CREATE TABLE statement, it is an error.
         1116  +#
         1117  +#     To test the two above, show that zero primary keys is Ok, one primary
         1118  +#     key is Ok, and two or more primary keys is an error.
         1119  +#
         1120  +drop_all_tables
         1121  +do_createtable_tests 4.1.1 {
         1122  +  1    "CREATE TABLE t1(a, b, c)"                                        {}
         1123  +  2    "CREATE TABLE t2(a PRIMARY KEY, b, c)"                            {}
         1124  +  3    "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))"                        {}
         1125  +  4    "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))"                    {}
         1126  +}
         1127  +do_createtable_tests 4.1.2 -error {
         1128  +  table "t5" has more than one primary key
         1129  +} {
         1130  +  1    "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)"                {}
         1131  +  2    "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))"            {}
         1132  +  3    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)"        {}
         1133  +  4    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
         1134  +  5    "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))"            {}
         1135  +  6    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))"    {}
         1136  +}
         1137  +
         1138  +proc table_pk {tbl} { 
         1139  +  set pk [list]
         1140  +  db eval "pragma table_info($tbl)" a {
         1141  +    if {$a(pk)} { lappend pk $a(name) }
         1142  +  }
         1143  +  set pk
         1144  +}
         1145  +
         1146  +# EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
         1147  +# column definition, then the primary key for the table consists of that
         1148  +# single column.
         1149  +#
         1150  +#     The above is tested by 4.2.1.*
         1151  +#
         1152  +# EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
         1153  +# a table-constraint, then the primary key of the table consists of the
         1154  +# list of columns specified as part of the PRIMARY KEY clause.
         1155  +#
         1156  +#     The above is tested by 4.2.2.*
         1157  +#
         1158  +do_createtable_tests 4.2 -repair {
         1159  +  catchsql { DROP TABLE t5 }
         1160  +} -tclquery {
         1161  +  table_pk t5
         1162  +} {
         1163  +  1.1    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
         1164  +  1.2    "CREATE TABLE t5(a PRIMARY KEY, b, c)"               {a}
         1165  +
         1166  +  2.1    "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))"           {a}
         1167  +  2.2    "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))"       {a b c}
         1168  +  2.3    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
         1169  +}
         1170  +
         1171  +# EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must
         1172  +# feature a unique combination of values in its primary key columns.
         1173  +#
         1174  +# EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts
         1175  +# to modify the table content so that two or more rows feature identical
         1176  +# primary key values, it is a constraint violation.
         1177  +#
         1178  +drop_all_tables
         1179  +do_execsql_test 4.3.0 {
         1180  +  CREATE TABLE t1(x PRIMARY KEY, y);
         1181  +  INSERT INTO t1 VALUES(0,          'zero');
         1182  +  INSERT INTO t1 VALUES(45.5,       'one');
         1183  +  INSERT INTO t1 VALUES('brambles', 'two');
         1184  +  INSERT INTO t1 VALUES(X'ABCDEF',  'three');
         1185  +
         1186  +  CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
         1187  +  INSERT INTO t2 VALUES(0,          'zero');
         1188  +  INSERT INTO t2 VALUES(45.5,       'one');
         1189  +  INSERT INTO t2 VALUES('brambles', 'two');
         1190  +  INSERT INTO t2 VALUES(X'ABCDEF',  'three');
         1191  +} {}
         1192  +
         1193  +do_createtable_tests 4.3.1 -error { %s not unique } {
         1194  +  1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
         1195  +  2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
         1196  +  3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
         1197  +  4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
         1198  +  5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
         1199  +
         1200  +  6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
         1201  +  7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
         1202  +  8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
         1203  +  9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
         1204  +  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
         1205  +}
         1206  +do_createtable_tests 4.3.2 {
         1207  +  1    "INSERT INTO t1 VALUES(-1, 0)"                {}
         1208  +  2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
         1209  +  3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
         1210  +  4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
         1211  +  5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
         1212  +
         1213  +  6    "INSERT INTO t2 VALUES(0, 0)"                 {}
         1214  +  7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
         1215  +  8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
         1216  +  9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
         1217  +  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
         1218  +}
         1219  +do_createtable_tests 4.3.3 -error { %s not unique } {
         1220  +  1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
         1221  +  2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
         1222  +  3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
         1223  +  4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
         1224  +  5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
         1225  +
         1226  +  6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
         1227  +  7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  
         1228  +       {"columns x, y are"}
         1229  +  8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
         1230  +  9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 
         1231  +       {"columns x, y are"}
         1232  +  10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  
         1233  +       {"columns x, y are"}
         1234  +}
         1235  +
         1236  +
         1237  +# EVIDENCE-OF: R-52572-02078 For the purposes of determining the
         1238  +# uniqueness of primary key values, NULL values are considered distinct
         1239  +# from all other values, including other NULLs.
         1240  +#
         1241  +do_createtable_tests 4.4 {
         1242  +  1    "INSERT INTO t1 VALUES(NULL, 0)"              {}
         1243  +  2    "INSERT INTO t1 VALUES(NULL, 0)"              {}
         1244  +  3    "INSERT INTO t1 VALUES(NULL, 0)"              {}
         1245  +
         1246  +  4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {}
         1247  +  5    "INSERT INTO t2 VALUES(NULL, 'one')"          {}
         1248  +  6    "INSERT INTO t2 VALUES(NULL, 'two')"          {}
         1249  +  7    "INSERT INTO t2 VALUES(NULL, 'three')"        {}
         1250  +
         1251  +  8    "INSERT INTO t2 VALUES(0, NULL)"              {}
         1252  +  9    "INSERT INTO t2 VALUES(45.5, NULL)"           {}
         1253  +  10   "INSERT INTO t2 VALUES(0.0, NULL)"            {}
         1254  +  11   "INSERT INTO t2 VALUES('brambles', NULL)"     {}
         1255  +  12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {}
         1256  +
         1257  +  13   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
         1258  +  14   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
         1259  +}
         1260  +
         1261  +# EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY
         1262  +# SQLite allows NULL values in a PRIMARY KEY column.
         1263  +#
         1264  +#     If the column is an integer primary key, attempting to insert a NULL
         1265  +#     into the column triggers the auto-increment behaviour. Attempting
         1266  +#     to use UPDATE to set an ipk column to a NULL value is an error.
         1267  +#
         1268  +do_createtable_tests 4.5.1 {
         1269  +  1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   3
         1270  +  2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   6
         1271  +  3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   7
         1272  +  4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     2
         1273  +}
         1274  +do_execsql_test 4.5.2 {
         1275  +  CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
         1276  +  INSERT INTO t3 VALUES(1, NULL, 2);
         1277  +  INSERT INTO t3 VALUES('x', NULL, 'y');
         1278  +  SELECT u FROM t3;
         1279  +} {1 2}
         1280  +do_catchsql_test 4.5.3 {
         1281  +  INSERT INTO t3 VALUES(2, 5, 3);
         1282  +  UPDATE t3 SET u = NULL WHERE s = 2;
         1283  +} {1 {datatype mismatch}}
         1284  +
  1056   1285   finish_test
         1286  +

Changes to test/tester.tcl.

   331    331       puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
   332    332       fail_test $name
   333    333     } else {
   334    334       puts " Ok"
   335    335     }
   336    336     flush stdout
   337    337   }
          338  +
          339  +proc fix_testname {varname} {
          340  +  upvar $varname testname
          341  +  if {[info exists ::testprefix] 
          342  +   && [string is digit [string range $testname 0 0]]
          343  +  } {
          344  +    set testname "${::testprefix}-$testname"
          345  +  }
          346  +}
   338    347       
   339    348   proc do_execsql_test {testname sql result} {
          349  +  fix_testname testname
   340    350     uplevel do_test $testname [list "execsql {$sql}"] [list $result]
   341    351   }
   342    352   proc do_catchsql_test {testname sql result} {
          353  +  fix_testname testname
   343    354     uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
   344    355   }
   345    356   
   346    357   #-------------------------------------------------------------------------
   347    358   #   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
   348    359   #
   349    360   # Where switches are: