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: |
20e16fef55c355a1d7e97d0c390769b9 |
| 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
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 } {