| OLD | NEW |
|---|
| (Empty) |
| 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 do_createtable_tests 0.1.1 -repair { | |
| 62 drop_all_tables | |
| 63 } { | |
| 64 1 "CREATE TABLE t1(c1 one)" {} | |
| 65 2 "CREATE TABLE t1(c1 one two)" {} | |
| 66 3 "CREATE TABLE t1(c1 one two three)" {} | |
| 67 4 "CREATE TABLE t1(c1 one two three four)" {} | |
| 68 5 "CREATE TABLE t1(c1 one two three four(14))" {} | |
| 69 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {} | |
| 70 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {} | |
| 71 8 "CREATE TABLE t1(c1 var(1.0e10))" {} | |
| 72 } | |
| 73 do_createtable_tests 0.1.2 -error { | |
| 74 near "%s": syntax error | |
| 75 } { | |
| 76 1 "CREATE TABLE t1(c1 one(number))" {number} | |
| 77 } | |
| 78 | |
| 79 | |
| 80 # syntax diagram column-constraint | |
| 81 # | |
| 82 do_createtable_tests 0.2.1 -repair { | |
| 83 drop_all_tables | |
| 84 execsql { CREATE TABLE t2(x PRIMARY KEY) } | |
| 85 } { | |
| 86 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {} | |
| 87 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {} | |
| 88 1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {} | |
| 89 1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {} | |
| 90 | |
| 91 2.1 "CREATE TABLE t1(c1 text NOT NULL)" {} | |
| 92 2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {} | |
| 93 2.3 "CREATE TABLE t1(c1 text NULL)" {} | |
| 94 2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {} | |
| 95 | |
| 96 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {} | |
| 97 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {} | |
| 98 | |
| 99 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {} | |
| 100 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {} | |
| 101 | |
| 102 5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {} | |
| 103 5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {} | |
| 104 5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {} | |
| 105 5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {} | |
| 106 5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {} | |
| 107 5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {} | |
| 108 | |
| 109 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {} | |
| 110 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {} | |
| 111 | |
| 112 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {} | |
| 113 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {} | |
| 114 | |
| 115 8.1 { | |
| 116 CREATE TABLE t1(c1 | |
| 117 PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1 | |
| 118 ); | |
| 119 } {} | |
| 120 8.2 { | |
| 121 CREATE TABLE t1(c1 | |
| 122 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY | |
| 123 ); | |
| 124 } {} | |
| 125 } | |
| 126 | |
| 127 # -- syntax diagram table-constraint | |
| 128 # | |
| 129 do_createtable_tests 0.3.1 -repair { | |
| 130 drop_all_tables | |
| 131 execsql { CREATE TABLE t2(x PRIMARY KEY) } | |
| 132 } { | |
| 133 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {} | |
| 134 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {} | |
| 135 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {} | |
| 136 | |
| 137 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {} | |
| 138 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {} | |
| 139 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {} | |
| 140 | |
| 141 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {} | |
| 142 | |
| 143 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {} | |
| 144 } | |
| 145 | |
| 146 # -- syntax diagram column-def | |
| 147 # | |
| 148 do_createtable_tests 0.4.1 -repair { | |
| 149 drop_all_tables | |
| 150 } { | |
| 151 1 {CREATE TABLE t1( | |
| 152 col1, | |
| 153 col2 TEXT, | |
| 154 col3 INTEGER UNIQUE, | |
| 155 col4 VARCHAR(10, 10) PRIMARY KEY, | |
| 156 "name with spaces" REFERENCES t1 | |
| 157 ); | |
| 158 } {} | |
| 159 } | |
| 160 | |
| 161 # -- syntax diagram create-table-stmt | |
| 162 # | |
| 163 do_createtable_tests 0.5.1 -repair { | |
| 164 drop_all_tables | |
| 165 execsql { CREATE TABLE t2(a, b, c) } | |
| 166 } { | |
| 167 1 "CREATE TABLE t1(a, b, c)" {} | |
| 168 2 "CREATE TEMP TABLE t1(a, b, c)" {} | |
| 169 3 "CREATE TEMPORARY TABLE t1(a, b, c)" {} | |
| 170 4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {} | |
| 171 5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {} | |
| 172 6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {} | |
| 173 | |
| 174 7 "CREATE TABLE main.t1(a, b, c)" {} | |
| 175 8 "CREATE TEMP TABLE temp.t1(a, b, c)" {} | |
| 176 9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {} | |
| 177 10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {} | |
| 178 11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {} | |
| 179 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {} | |
| 180 | |
| 181 13 "CREATE TABLE t1 AS SELECT * FROM t2" {} | |
| 182 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {} | |
| 183 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {} | |
| 184 } | |
| 185 | |
| 186 # | |
| 187 # 1: Explicit parent-key columns. | |
| 188 # 2: Implicit child-key columns. | |
| 189 # | |
| 190 # 1: MATCH FULL | |
| 191 # 2: MATCH PARTIAL | |
| 192 # 3: MATCH SIMPLE | |
| 193 # 4: MATCH STICK | |
| 194 # 5: | |
| 195 # | |
| 196 # 1: ON DELETE SET NULL | |
| 197 # 2: ON DELETE SET DEFAULT | |
| 198 # 3: ON DELETE CASCADE | |
| 199 # 4: ON DELETE RESTRICT | |
| 200 # 5: ON DELETE NO ACTION | |
| 201 # 6: | |
| 202 # | |
| 203 # 1: ON UPDATE SET NULL | |
| 204 # 2: ON UPDATE SET DEFAULT | |
| 205 # 3: ON UPDATE CASCADE | |
| 206 # 4: ON UPDATE RESTRICT | |
| 207 # 5: ON UPDATE NO ACTION | |
| 208 # 6: | |
| 209 # | |
| 210 # 1: NOT DEFERRABLE INITIALLY DEFERRED | |
| 211 # 2: NOT DEFERRABLE INITIALLY IMMEDIATE | |
| 212 # 3: NOT DEFERRABLE | |
| 213 # 4: DEFERRABLE INITIALLY DEFERRED | |
| 214 # 5: DEFERRABLE INITIALLY IMMEDIATE | |
| 215 # 6: DEFERRABLE | |
| 216 # 7: | |
| 217 # | |
| 218 do_createtable_tests 0.6.1 -repair { | |
| 219 drop_all_tables | |
| 220 execsql { CREATE TABLE t2(x PRIMARY KEY, y) } | |
| 221 execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) } | |
| 222 } { | |
| 223 11146 { CREATE TABLE t1(a | |
| 224 REFERENCES t2(x) MATCH FULL | |
| 225 ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE | |
| 226 )} {} | |
| 227 11412 { CREATE TABLE t1(a | |
| 228 REFERENCES t2(x) | |
| 229 ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL | |
| 230 NOT DEFERRABLE INITIALLY IMMEDIATE | |
| 231 )} {} | |
| 232 12135 { CREATE TABLE t1(a | |
| 233 REFERENCES t2(x) MATCH PARTIAL | |
| 234 ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE | |
| 235 )} {} | |
| 236 12427 { CREATE TABLE t1(a | |
| 237 REFERENCES t2(x) MATCH PARTIAL | |
| 238 ON DELETE RESTRICT ON UPDATE SET DEFAULT | |
| 239 )} {} | |
| 240 12446 { CREATE TABLE t1(a | |
| 241 REFERENCES t2(x) MATCH PARTIAL | |
| 242 ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE | |
| 243 )} {} | |
| 244 12522 { CREATE TABLE t1(a | |
| 245 REFERENCES t2(x) MATCH PARTIAL | |
| 246 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE | |
| 247 )} {} | |
| 248 13133 { CREATE TABLE t1(a | |
| 249 REFERENCES t2(x) MATCH SIMPLE | |
| 250 ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE | |
| 251 )} {} | |
| 252 13216 { CREATE TABLE t1(a | |
| 253 REFERENCES t2(x) MATCH SIMPLE | |
| 254 ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE | |
| 255 )} {} | |
| 256 13263 { CREATE TABLE t1(a | |
| 257 REFERENCES t2(x) MATCH SIMPLE | |
| 258 ON DELETE SET DEFAULT NOT DEFERRABLE | |
| 259 )} {} | |
| 260 13421 { CREATE TABLE t1(a | |
| 261 REFERENCES t2(x) MATCH SIMPLE | |
| 262 ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED | |
| 263 )} {} | |
| 264 13432 { CREATE TABLE t1(a | |
| 265 REFERENCES t2(x) MATCH SIMPLE | |
| 266 ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE | |
| 267 )} {} | |
| 268 13523 { CREATE TABLE t1(a | |
| 269 REFERENCES t2(x) MATCH SIMPLE | |
| 270 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE | |
| 271 )} {} | |
| 272 14336 { CREATE TABLE t1(a | |
| 273 REFERENCES t2(x) MATCH STICK | |
| 274 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE | |
| 275 )} {} | |
| 276 14611 { CREATE TABLE t1(a | |
| 277 REFERENCES t2(x) MATCH STICK | |
| 278 ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED | |
| 279 )} {} | |
| 280 15155 { CREATE TABLE t1(a | |
| 281 REFERENCES t2(x) | |
| 282 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE | |
| 283 )} {} | |
| 284 15453 { CREATE TABLE t1(a | |
| 285 REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE | |
| 286 )} {} | |
| 287 15661 { CREATE TABLE t1(a | |
| 288 REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED | |
| 289 )} {} | |
| 290 21115 { CREATE TABLE t1(a | |
| 291 REFERENCES t2 MATCH FULL | |
| 292 ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE | |
| 293 )} {} | |
| 294 21123 { CREATE TABLE t1(a | |
| 295 REFERENCES t2 MATCH FULL | |
| 296 ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE | |
| 297 )} {} | |
| 298 21217 { CREATE TABLE t1(a | |
| 299 REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL | |
| 300 )} {} | |
| 301 21362 { CREATE TABLE t1(a | |
| 302 REFERENCES t2 MATCH FULL | |
| 303 ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE | |
| 304 )} {} | |
| 305 22143 { CREATE TABLE t1(a | |
| 306 REFERENCES t2 MATCH PARTIAL | |
| 307 ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE | |
| 308 )} {} | |
| 309 22156 { CREATE TABLE t1(a | |
| 310 REFERENCES t2 MATCH PARTIAL | |
| 311 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE | |
| 312 )} {} | |
| 313 22327 { CREATE TABLE t1(a | |
| 314 REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT | |
| 315 )} {} | |
| 316 22663 { CREATE TABLE t1(a | |
| 317 REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE | |
| 318 )} {} | |
| 319 23236 { CREATE TABLE t1(a | |
| 320 REFERENCES t2 MATCH SIMPLE | |
| 321 ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE | |
| 322 )} {} | |
| 323 24155 { CREATE TABLE t1(a | |
| 324 REFERENCES t2 MATCH STICK | |
| 325 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE | |
| 326 )} {} | |
| 327 24522 { CREATE TABLE t1(a | |
| 328 REFERENCES t2 MATCH STICK | |
| 329 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE | |
| 330 )} {} | |
| 331 24625 { CREATE TABLE t1(a | |
| 332 REFERENCES t2 MATCH STICK | |
| 333 ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE | |
| 334 )} {} | |
| 335 25454 { CREATE TABLE t1(a | |
| 336 REFERENCES t2 | |
| 337 ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED | |
| 338 )} {} | |
| 339 } | |
| 340 | |
| 341 #------------------------------------------------------------------------- | |
| 342 # Test cases e_createtable-1.* - test statements related to table and | |
| 343 # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS | |
| 344 # clause. | |
| 345 # | |
| 346 drop_all_tables | |
| 347 forcedelete test.db2 test.db3 | |
| 348 | |
| 349 do_execsql_test e_createtable-1.0 { | |
| 350 ATTACH 'test.db2' AS auxa; | |
| 351 ATTACH 'test.db3' AS auxb; | |
| 352 } {} | |
| 353 | |
| 354 # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are | |
| 355 # reserved for internal use. It is an error to attempt to create a table | |
| 356 # with a name that starts with "sqlite_". | |
| 357 # | |
| 358 do_createtable_tests 1.1.1 -error { | |
| 359 object name reserved for internal use: %s | |
| 360 } { | |
| 361 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc | |
| 362 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld | |
| 363 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__ | |
| 364 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_ | |
| 365 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL | |
| 366 } | |
| 367 do_createtable_tests 1.1.2 { | |
| 368 1 "CREATE TABLE sqlit_abc(a, b, c)" {} | |
| 369 2 "CREATE TABLE temp.sqlitehelloworld(x)" {} | |
| 370 3 {CREATE TABLE auxa."sqlite"(x, y)} {} | |
| 371 4 {CREATE TABLE auxb."sqlite-"(z)} {} | |
| 372 5 {CREATE TABLE "SQLITE-TBL"(z)} {} | |
| 373 } | |
| 374 | |
| 375 | |
| 376 # EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it | |
| 377 # must be either "main", "temp", or the name of an attached database. | |
| 378 # | |
| 379 # EVIDENCE-OF: R-39822-07822 In this case the new table is created in | |
| 380 # the named database. | |
| 381 # | |
| 382 # Test cases 1.2.* test the first of the two requirements above. The | |
| 383 # second is verified by cases 1.3.*. | |
| 384 # | |
| 385 do_createtable_tests 1.2.1 -error { | |
| 386 unknown database %s | |
| 387 } { | |
| 388 1 "CREATE TABLE george.t1(a, b)" george | |
| 389 2 "CREATE TABLE _.t1(a, b)" _ | |
| 390 } | |
| 391 do_createtable_tests 1.2.2 { | |
| 392 1 "CREATE TABLE main.abc(a, b, c)" {} | |
| 393 2 "CREATE TABLE temp.helloworld(x)" {} | |
| 394 3 {CREATE TABLE auxa."t 1"(x, y)} {} | |
| 395 4 {CREATE TABLE auxb.xyz(z)} {} | |
| 396 } | |
| 397 drop_all_tables | |
| 398 do_createtable_tests 1.3 -tclquery { | |
| 399 unset -nocomplain X | |
| 400 array set X [table_list] | |
| 401 list $X(main) $X(temp) $X(auxa) $X(auxb) | |
| 402 } { | |
| 403 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}} | |
| 404 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}} | |
| 405 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}} | |
| 406 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl} | |
| 407 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}} | |
| 408 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}} | |
| 409 } | |
| 410 | |
| 411 # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs | |
| 412 # between the "CREATE" and "TABLE" then the new table is created in the | |
| 413 # temp database. | |
| 414 # | |
| 415 drop_all_tables | |
| 416 do_createtable_tests 1.4 -tclquery { | |
| 417 unset -nocomplain X | |
| 418 array set X [table_list] | |
| 419 list $X(main) $X(temp) $X(auxa) $X(auxb) | |
| 420 } { | |
| 421 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}} | |
| 422 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}} | |
| 423 } | |
| 424 | |
| 425 # EVIDENCE-OF: R-49439-47561 It is an error to specify both a | |
| 426 # <database-name> and the TEMP or TEMPORARY keyword, unless the | |
| 427 # <database-name> is "temp". | |
| 428 # | |
| 429 drop_all_tables | |
| 430 do_createtable_tests 1.5.1 -error { | |
| 431 temporary table name must be unqualified | |
| 432 } { | |
| 433 1 "CREATE TEMP TABLE main.t1(a, b)" {} | |
| 434 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {} | |
| 435 3 "CREATE TEMP TABLE auxb.t3(a, b)" {} | |
| 436 4 "CREATE TEMPORARY TABLE main.xxx(x)" {} | |
| 437 } | |
| 438 drop_all_tables | |
| 439 do_createtable_tests 1.5.2 -tclquery { | |
| 440 unset -nocomplain X | |
| 441 array set X [table_list] | |
| 442 list $X(main) $X(temp) $X(auxa) $X(auxb) | |
| 443 } { | |
| 444 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}} | |
| 445 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}} | |
| 446 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}} | |
| 447 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}} | |
| 448 } | |
| 449 | |
| 450 # EVIDENCE-OF: R-00917-09393 If no database name is specified and the | |
| 451 # TEMP keyword is not present then the table is created in the main | |
| 452 # database. | |
| 453 # | |
| 454 drop_all_tables | |
| 455 do_createtable_tests 1.6 -tclquery { | |
| 456 unset -nocomplain X | |
| 457 array set X [table_list] | |
| 458 list $X(main) $X(temp) $X(auxa) $X(auxb) | |
| 459 } { | |
| 460 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}} | |
| 461 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}} | |
| 462 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}} | |
| 463 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}} | |
| 464 } | |
| 465 | |
| 466 drop_all_tables | |
| 467 do_execsql_test e_createtable-1.7.0 { | |
| 468 CREATE TABLE t1(x, y); | |
| 469 CREATE INDEX i1 ON t1(x); | |
| 470 CREATE VIEW v1 AS SELECT * FROM t1; | |
| 471 | |
| 472 CREATE TABLE auxa.tbl1(x, y); | |
| 473 CREATE INDEX auxa.idx1 ON tbl1(x); | |
| 474 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1; | |
| 475 } {} | |
| 476 | |
| 477 # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create | |
| 478 # a new table in a database that already contains a table, index or view | |
| 479 # of the same name. | |
| 480 # | |
| 481 # Test cases 1.7.1.* verify that creating a table in a database with a | |
| 482 # table/index/view of the same name does fail. 1.7.2.* tests that creating | |
| 483 # a table with the same name as a table/index/view in a different database | |
| 484 # is Ok. | |
| 485 # | |
| 486 do_createtable_tests 1.7.1 -error { %s } { | |
| 487 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}} | |
| 488 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}} | |
| 489 3 "CREATE TABLE v1(a, b)" {{table v1 already exists}} | |
| 490 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}} | |
| 491 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}} | |
| 492 6 "CREATE TABLE auxa.view1(a, b)" {{table view1 already exists}} | |
| 493 } | |
| 494 do_createtable_tests 1.7.2 { | |
| 495 1 "CREATE TABLE auxa.t1(a, b)" {} | |
| 496 2 "CREATE TABLE auxa.i1(a, b)" {} | |
| 497 3 "CREATE TABLE auxa.v1(a, b)" {} | |
| 498 4 "CREATE TABLE tbl1(a, b)" {} | |
| 499 5 "CREATE TABLE idx1(a, b)" {} | |
| 500 6 "CREATE TABLE view1(a, b)" {} | |
| 501 } | |
| 502 | |
| 503 # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is | |
| 504 # specified as part of the CREATE TABLE statement and a table or view of | |
| 505 # the same name already exists, the CREATE TABLE command simply has no | |
| 506 # effect (and no error message is returned). | |
| 507 # | |
| 508 drop_all_tables | |
| 509 do_execsql_test e_createtable-1.8.0 { | |
| 510 CREATE TABLE t1(x, y); | |
| 511 CREATE INDEX i1 ON t1(x); | |
| 512 CREATE VIEW v1 AS SELECT * FROM t1; | |
| 513 CREATE TABLE auxa.tbl1(x, y); | |
| 514 CREATE INDEX auxa.idx1 ON tbl1(x); | |
| 515 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1; | |
| 516 } {} | |
| 517 do_createtable_tests 1.8 { | |
| 518 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {} | |
| 519 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {} | |
| 520 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {} | |
| 521 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {} | |
| 522 } | |
| 523 | |
| 524 # EVIDENCE-OF: R-16465-40078 An error is still returned if the table | |
| 525 # cannot be created because of an existing index, even if the "IF NOT | |
| 526 # EXISTS" clause is specified. | |
| 527 # | |
| 528 do_createtable_tests 1.9 -error { %s } { | |
| 529 1 "CREATE TABLE IF NOT EXISTS i1(a, b)" | |
| 530 {{there is already an index named i1}} | |
| 531 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)" | |
| 532 {{there is already an index named idx1}} | |
| 533 } | |
| 534 | |
| 535 # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that | |
| 536 # has the same name as an existing trigger. | |
| 537 # | |
| 538 drop_all_tables | |
| 539 do_execsql_test e_createtable-1.10.0 { | |
| 540 CREATE TABLE t1(x, y); | |
| 541 CREATE TABLE auxb.t2(x, y); | |
| 542 | |
| 543 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN | |
| 544 SELECT 1; | |
| 545 END; | |
| 546 CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN | |
| 547 SELECT 1; | |
| 548 END; | |
| 549 } {} | |
| 550 do_createtable_tests 1.10 { | |
| 551 1 "CREATE TABLE tr1(a, b)" {} | |
| 552 2 "CREATE TABLE tr2(a, b)" {} | |
| 553 3 "CREATE TABLE auxb.tr1(a, b)" {} | |
| 554 4 "CREATE TABLE auxb.tr2(a, b)" {} | |
| 555 } | |
| 556 | |
| 557 # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE | |
| 558 # statement. | |
| 559 # | |
| 560 drop_all_tables | |
| 561 do_execsql_test e_createtable-1.11.0 { | |
| 562 CREATE TABLE t1(a, b); | |
| 563 CREATE TABLE t2(a, b); | |
| 564 CREATE TABLE auxa.t3(a, b); | |
| 565 CREATE TABLE auxa.t4(a, b); | |
| 566 } {} | |
| 567 | |
| 568 do_execsql_test e_createtable-1.11.1.1 { | |
| 569 SELECT * FROM t1; | |
| 570 SELECT * FROM t2; | |
| 571 SELECT * FROM t3; | |
| 572 SELECT * FROM t4; | |
| 573 } {} | |
| 574 do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {} | |
| 575 do_catchsql_test e_createtable-1.11.1.3 { | |
| 576 SELECT * FROM t1 | |
| 577 } {1 {no such table: t1}} | |
| 578 do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {} | |
| 579 do_catchsql_test e_createtable-1.11.1.5 { | |
| 580 SELECT * FROM t3 | |
| 581 } {1 {no such table: t3}} | |
| 582 | |
| 583 do_execsql_test e_createtable-1.11.2.1 { | |
| 584 SELECT name FROM sqlite_master; | |
| 585 SELECT name FROM auxa.sqlite_master; | |
| 586 } {t2 t4} | |
| 587 do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {} | |
| 588 do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {} | |
| 589 do_execsql_test e_createtable-1.11.2.4 { | |
| 590 SELECT name FROM sqlite_master; | |
| 591 SELECT name FROM auxa.sqlite_master; | |
| 592 } {} | |
| 593 | |
| 594 #------------------------------------------------------------------------- | |
| 595 # Test cases e_createtable-2.* - test statements related to the CREATE | |
| 596 # TABLE AS ... SELECT statement. | |
| 597 # | |
| 598 | |
| 599 # Three Tcl commands: | |
| 600 # | |
| 601 # select_column_names SQL | |
| 602 # The argument must be a SELECT statement. Return a list of the names | |
| 603 # of the columns of the result-set that would be returned by executing | |
| 604 # the SELECT. | |
| 605 # | |
| 606 # table_column_names TBL | |
| 607 # The argument must be a table name. Return a list of column names, from | |
| 608 # left to right, for the table. | |
| 609 # | |
| 610 # table_column_decltypes TBL | |
| 611 # The argument must be a table name. Return a list of column declared | |
| 612 # types, from left to right, for the table. | |
| 613 # | |
| 614 proc sci {select cmd} { | |
| 615 set res [list] | |
| 616 set STMT [sqlite3_prepare_v2 db $select -1 dummy] | |
| 617 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} { | |
| 618 lappend res [$cmd $STMT $i] | |
| 619 } | |
| 620 sqlite3_finalize $STMT | |
| 621 set res | |
| 622 } | |
| 623 proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd } | |
| 624 proc select_column_names {sql} { sci $sql sqlite3_column_name } | |
| 625 proc table_column_names {tbl} { tci $tbl sqlite3_column_name } | |
| 626 proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype } | |
| 627 | |
| 628 # Create a database schema. This schema is used by tests 2.1.* through 2.3.*. | |
| 629 # | |
| 630 drop_all_tables | |
| 631 do_execsql_test e_createtable-2.0 { | |
| 632 CREATE TABLE t1(a, b, c); | |
| 633 CREATE TABLE t2(d, e, f); | |
| 634 CREATE TABLE t3(g BIGINT, h VARCHAR(10)); | |
| 635 CREATE TABLE t4(i BLOB, j ANYOLDATA); | |
| 636 CREATE TABLE t5(k FLOAT, l INTEGER); | |
| 637 CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n)); | |
| 638 CREATE TABLE t7(x INTEGER PRIMARY KEY); | |
| 639 CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc'); | |
| 640 CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE); | |
| 641 } {} | |
| 642 | |
| 643 # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as | |
| 644 # the rows returned by the SELECT statement. The name of each column is | |
| 645 # the same as the name of the corresponding column in the result set of | |
| 646 # the SELECT statement. | |
| 647 # | |
| 648 do_createtable_tests 2.1 -tclquery { | |
| 649 table_column_names x1 | |
| 650 } -repair { | |
| 651 catchsql { DROP TABLE x1 } | |
| 652 } { | |
| 653 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c} | |
| 654 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a} | |
| 655 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f} | |
| 656 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)} | |
| 657 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)} | |
| 658 } | |
| 659 | |
| 660 # EVIDENCE-OF: R-37111-22855 The declared type of each column is | |
| 661 # determined by the expression affinity of the corresponding expression | |
| 662 # in the result set of the SELECT statement, as follows: Expression | |
| 663 # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT" | |
| 664 # REAL "REAL" NONE "" (empty string) | |
| 665 # | |
| 666 do_createtable_tests 2.2 -tclquery { | |
| 667 table_column_decltypes x1 | |
| 668 } -repair { | |
| 669 catchsql { DROP TABLE x1 } | |
| 670 } { | |
| 671 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""} | |
| 672 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT} | |
| 673 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM} | |
| 674 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT} | |
| 675 } | |
| 676 | |
| 677 # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has | |
| 678 # no PRIMARY KEY and no constraints of any kind. The default value of | |
| 679 # each column is NULL. The default collation sequence for each column of | |
| 680 # the new table is BINARY. | |
| 681 # | |
| 682 # The following tests create tables based on SELECT statements that read | |
| 683 # from tables that have primary keys, constraints and explicit default | |
| 684 # collation sequences. None of this is transfered to the definition of | |
| 685 # the new table as stored in the sqlite_master table. | |
| 686 # | |
| 687 # Tests 2.3.2.* show that the default value of each column is NULL. | |
| 688 # | |
| 689 do_createtable_tests 2.3.1 -query { | |
| 690 SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1 | |
| 691 } { | |
| 692 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}} | |
| 693 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}} | |
| 694 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}} | |
| 695 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}} | |
| 696 } | |
| 697 do_execsql_test e_createtable-2.3.2.1 { | |
| 698 INSERT INTO x1 DEFAULT VALUES; | |
| 699 INSERT INTO x2 DEFAULT VALUES; | |
| 700 INSERT INTO x3 DEFAULT VALUES; | |
| 701 INSERT INTO x4 DEFAULT VALUES; | |
| 702 } {} | |
| 703 db nullvalue null | |
| 704 do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null} | |
| 705 do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null} | |
| 706 do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null} | |
| 707 do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null} | |
| 708 db nullvalue {} | |
| 709 | |
| 710 drop_all_tables | |
| 711 do_execsql_test e_createtable-2.4.0 { | |
| 712 CREATE TABLE t1(x, y); | |
| 713 INSERT INTO t1 VALUES('i', 'one'); | |
| 714 INSERT INTO t1 VALUES('ii', 'two'); | |
| 715 INSERT INTO t1 VALUES('iii', 'three'); | |
| 716 } {} | |
| 717 | |
| 718 # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are | |
| 719 # initially populated with the rows of data returned by the SELECT | |
| 720 # statement. | |
| 721 # | |
| 722 # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending | |
| 723 # rowid values, starting with 1, in the order that they are returned by | |
| 724 # the SELECT statement. | |
| 725 # | |
| 726 # Each test case below is specified as the name of a table to create | |
| 727 # using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in | |
| 728 # creating it. The table is created. | |
| 729 # | |
| 730 # Test cases 2.4.*.1 check that after it has been created, the data in the | |
| 731 # table is the same as the data returned by the SELECT statement executed as | |
| 732 # a standalone command, verifying the first testable statement above. | |
| 733 # | |
| 734 # Test cases 2.4.*.2 check that the rowids were allocated contiguously | |
| 735 # as required by the second testable statement above. That the rowids | |
| 736 # from the contiguous block were allocated to rows in the order rows are | |
| 737 # returned by the SELECT statement is verified by 2.4.*.1. | |
| 738 # | |
| 739 # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement | |
| 740 # creates and populates a database table based on the results of a | |
| 741 # SELECT statement. | |
| 742 # | |
| 743 # The above is also considered to be tested by the following. It is | |
| 744 # clear that tables are being created and populated by the command in | |
| 745 # question. | |
| 746 # | |
| 747 foreach {tn tbl select} { | |
| 748 1 x1 "SELECT * FROM t1" | |
| 749 2 x2 "SELECT * FROM t1 ORDER BY x DESC" | |
| 750 3 x3 "SELECT * FROM t1 ORDER BY x ASC" | |
| 751 } { | |
| 752 # Create the table using a "CREATE TABLE ... AS SELECT ..." command. | |
| 753 execsql [subst {CREATE TABLE $tbl AS $select}] | |
| 754 | |
| 755 # Check that the rows inserted into the table, sorted in ascending rowid | |
| 756 # order, match those returned by executing the SELECT statement as a | |
| 757 # standalone command. | |
| 758 do_execsql_test e_createtable-2.4.$tn.1 [subst { | |
| 759 SELECT * FROM $tbl ORDER BY rowid; | |
| 760 }] [execsql $select] | |
| 761 | |
| 762 # Check that the rowids in the new table are a contiguous block starting | |
| 763 # with rowid 1. Note that this will fail if SELECT statement $select | |
| 764 # returns 0 rows (as max(rowid) will be NULL). | |
| 765 do_execsql_test e_createtable-2.4.$tn.2 [subst { | |
| 766 SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl | |
| 767 }] {1 1} | |
| 768 } | |
| 769 | |
| 770 #-------------------------------------------------------------------------- | |
| 771 # Test cases for column defintions in CREATE TABLE statements that do not | |
| 772 # use a SELECT statement. Not including data constraints. In other words, | |
| 773 # tests for the specification of: | |
| 774 # | |
| 775 # * declared types, | |
| 776 # * default values, and | |
| 777 # * default collation sequences. | |
| 778 # | |
| 779 | |
| 780 # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not | |
| 781 # restrict the type of data that may be inserted into a column based on | |
| 782 # the columns declared type. | |
| 783 # | |
| 784 # Test this by creating a few tables with varied declared types, then | |
| 785 # inserting various different types of values into them. | |
| 786 # | |
| 787 drop_all_tables | |
| 788 do_execsql_test e_createtable-3.1.0 { | |
| 789 CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE); | |
| 790 CREATE TABLE t2(a DATETIME, b STRING, c REAL); | |
| 791 CREATE TABLE t3(o, t); | |
| 792 } {} | |
| 793 | |
| 794 # value type -> declared column type | |
| 795 # ---------------------------------- | |
| 796 # integer -> VARCHAR(10) | |
| 797 # string -> INTEGER | |
| 798 # blob -> DOUBLE | |
| 799 # | |
| 800 do_execsql_test e_createtable-3.1.1 { | |
| 801 INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655'); | |
| 802 SELECT * FROM t1; | |
| 803 } {14 {quite a lengthy string} UVU} | |
| 804 | |
| 805 # string -> DATETIME | |
| 806 # integer -> STRING | |
| 807 # time -> REAL | |
| 808 # | |
| 809 do_execsql_test e_createtable-3.1.2 { | |
| 810 INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59'); | |
| 811 SELECT * FROM t2; | |
| 812 } {{not a datetime} 13 12:41:59} | |
| 813 | |
| 814 # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to | |
| 815 # determine the affinity of the column only. | |
| 816 # | |
| 817 # Affinities are tested in more detail elsewhere (see document | |
| 818 # datatype3.html). Here, just test that affinity transformations | |
| 819 # consistent with the expected affinity of each column (based on | |
| 820 # the declared type) appear to take place. | |
| 821 # | |
| 822 # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL | |
| 823 # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL | |
| 824 # Affinities of t3 (test cases 3.2.3.*): NONE, NONE | |
| 825 # | |
| 826 do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {} | |
| 827 | |
| 828 do_createtable_tests 3.2.1 -query { | |
| 829 SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1; | |
| 830 } { | |
| 831 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0} | |
| 832 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0} | |
| 833 } | |
| 834 do_createtable_tests 3.2.2 -query { | |
| 835 SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1; | |
| 836 } { | |
| 837 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0} | |
| 838 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0} | |
| 839 } | |
| 840 do_createtable_tests 3.2.3 -query { | |
| 841 SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1; | |
| 842 } { | |
| 843 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'} | |
| 844 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0} | |
| 845 } | |
| 846 | |
| 847 # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause | |
| 848 # attached to a column definition, then the default value of the column | |
| 849 # is NULL. | |
| 850 # | |
| 851 # None of the columns in table t1 have an explicit DEFAULT clause. | |
| 852 # So testing that the default value of all columns in table t1 is | |
| 853 # NULL serves to verify the above. | |
| 854 # | |
| 855 do_createtable_tests 3.2.3 -query { | |
| 856 SELECT quote(x), quote(y), quote(z) FROM t1 | |
| 857 } -repair { | |
| 858 execsql { DELETE FROM t1 } | |
| 859 } { | |
| 860 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL} | |
| 861 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'} | |
| 862 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL} | |
| 863 } | |
| 864 | |
| 865 # EVIDENCE-OF: R-07343-35026 An explicit DEFAULT clause may specify that | |
| 866 # the default value is NULL, a string constant, a blob constant, a | |
| 867 # signed-number, or any constant expression enclosed in parentheses. A | |
| 868 # default value may also be one of the special case-independent keywords | |
| 869 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. | |
| 870 # | |
| 871 do_execsql_test e_createtable-3.3.1 { | |
| 872 CREATE TABLE t4( | |
| 873 a DEFAULT NULL, | |
| 874 b DEFAULT 'string constant', | |
| 875 c DEFAULT X'424C4F42', | |
| 876 d DEFAULT 1, | |
| 877 e DEFAULT -1, | |
| 878 f DEFAULT 3.14, | |
| 879 g DEFAULT -3.14, | |
| 880 h DEFAULT ( substr('abcd', 0, 2) || 'cd' ), | |
| 881 i DEFAULT CURRENT_TIME, | |
| 882 j DEFAULT CURRENT_DATE, | |
| 883 k DEFAULT CURRENT_TIMESTAMP | |
| 884 ); | |
| 885 } {} | |
| 886 | |
| 887 # EVIDENCE-OF: R-18415-27776 For the purposes of the DEFAULT clause, an | |
| 888 # expression is considered constant if it does contains no sub-queries, | |
| 889 # column or table references, bound parameters, or string literals | |
| 890 # enclosed in double-quotes instead of single-quotes. | |
| 891 # | |
| 892 do_createtable_tests 3.4.1 -error { | |
| 893 default value of column [x] is not constant | |
| 894 } { | |
| 895 1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {} | |
| 896 2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {} | |
| 897 3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {} | |
| 898 4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {} | |
| 899 5 {CREATE TABLE t5(x DEFAULT ( x!=?1 ))} {} | |
| 900 } | |
| 901 do_createtable_tests 3.4.2 -repair { | |
| 902 catchsql { DROP TABLE t5 } | |
| 903 } { | |
| 904 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {} | |
| 905 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {} | |
| 906 } | |
| 907 | |
| 908 # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table | |
| 909 # by an INSERT statement that does not provide explicit values for all | |
| 910 # table columns the values stored in the new row are determined by their | |
| 911 # default values | |
| 912 # | |
| 913 # Verify this with some assert statements for which all, some and no | |
| 914 # columns lack explicit values. | |
| 915 # | |
| 916 set sqlite_current_time 1000000000 | |
| 917 do_createtable_tests 3.5 -query { | |
| 918 SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), | |
| 919 quote(g), quote(h), quote(i), quote(j), quote(k) | |
| 920 FROM t4 ORDER BY rowid DESC LIMIT 1; | |
| 921 } { | |
| 922 1 "INSERT INTO t4 DEFAULT VALUES" { | |
| 923 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 | |
| 924 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} | |
| 925 } | |
| 926 | |
| 927 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" { | |
| 928 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} | |
| 929 } | |
| 930 | |
| 931 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" { | |
| 932 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1 | |
| 933 } | |
| 934 | |
| 935 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)" { | |
| 936 1 2 3 4 5 6 7 8 9 10 11 | |
| 937 } | |
| 938 } | |
| 939 | |
| 940 # EVIDENCE-OF: R-12572-62501 If the default value of the column is a | |
| 941 # constant NULL, text, blob or signed-number value, then that value is | |
| 942 # used directly in the new row. | |
| 943 # | |
| 944 do_execsql_test e_createtable-3.6.1 { | |
| 945 CREATE TABLE t5( | |
| 946 a DEFAULT NULL, | |
| 947 b DEFAULT 'text value', | |
| 948 c DEFAULT X'424C4F42', | |
| 949 d DEFAULT -45678.6, | |
| 950 e DEFAULT 394507 | |
| 951 ); | |
| 952 } {} | |
| 953 do_execsql_test e_createtable-3.6.2 { | |
| 954 INSERT INTO t5 DEFAULT VALUES; | |
| 955 SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5; | |
| 956 } {NULL {'text value'} X'424C4F42' -45678.6 394507} | |
| 957 | |
| 958 # EVIDENCE-OF: R-60616-50251 If the default value of a column is an | |
| 959 # expression in parentheses, then the expression is evaluated once for | |
| 960 # each row inserted and the results used in the new row. | |
| 961 # | |
| 962 # Test case 3.6.4 demonstrates that the expression is evaluated | |
| 963 # separately for each row if the INSERT is an "INSERT INTO ... SELECT ..." | |
| 964 # command. | |
| 965 # | |
| 966 set ::nextint 0 | |
| 967 proc nextint {} { incr ::nextint } | |
| 968 db func nextint nextint | |
| 969 | |
| 970 do_execsql_test e_createtable-3.7.1 { | |
| 971 CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() )); | |
| 972 } {} | |
| 973 do_execsql_test e_createtable-3.7.2 { | |
| 974 INSERT INTO t6 DEFAULT VALUES; | |
| 975 SELECT quote(a), quote(b) FROM t6; | |
| 976 } {1 2} | |
| 977 do_execsql_test e_createtable-3.7.3 { | |
| 978 INSERT INTO t6(a) VALUES('X'); | |
| 979 SELECT quote(a), quote(b) FROM t6; | |
| 980 } {1 2 'X' 3} | |
| 981 do_execsql_test e_createtable-3.7.4 { | |
| 982 INSERT INTO t6(a) SELECT a FROM t6; | |
| 983 SELECT quote(a), quote(b) FROM t6; | |
| 984 } {1 2 'X' 3 1 4 'X' 5} | |
| 985 | |
| 986 # EVIDENCE-OF: R-15363-55230 If the default value of a column is | |
| 987 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used | |
| 988 # in the new row is a text representation of the current UTC date and/or | |
| 989 # time. | |
| 990 # | |
| 991 # This is difficult to test literally without knowing what time the | |
| 992 # user will run the tests. Instead, we test that the three cases | |
| 993 # above set the value to the current date and/or time according to | |
| 994 # the xCurrentTime() method of the VFS. Which is usually the same | |
| 995 # as UTC. In this case, however, we instrument it to always return | |
| 996 # a time equivalent to "2001-09-09 01:46:40 UTC". | |
| 997 # | |
| 998 set sqlite_current_time 1000000000 | |
| 999 do_execsql_test e_createtable-3.8.1 { | |
| 1000 CREATE TABLE t7( | |
| 1001 a DEFAULT CURRENT_TIME, | |
| 1002 b DEFAULT CURRENT_DATE, | |
| 1003 c DEFAULT CURRENT_TIMESTAMP | |
| 1004 ); | |
| 1005 } {} | |
| 1006 do_execsql_test e_createtable-3.8.2 { | |
| 1007 INSERT INTO t7 DEFAULT VALUES; | |
| 1008 SELECT quote(a), quote(b), quote(c) FROM t7; | |
| 1009 } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}} | |
| 1010 | |
| 1011 | |
| 1012 # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value | |
| 1013 # is "HH:MM:SS". | |
| 1014 # | |
| 1015 # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD". | |
| 1016 # | |
| 1017 # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is | |
| 1018 # "YYYY-MM-DD HH:MM:SS". | |
| 1019 # | |
| 1020 # The three above are demonstrated by tests 1, 2 and 3 below. | |
| 1021 # Respectively. | |
| 1022 # | |
| 1023 do_createtable_tests 3.8.3 -query { | |
| 1024 SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1; | |
| 1025 } { | |
| 1026 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y} | |
| 1027 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x} | |
| 1028 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}} | |
| 1029 } | |
| 1030 | |
| 1031 # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a | |
| 1032 # collating sequence to use as the default collation sequence for the | |
| 1033 # column. | |
| 1034 # | |
| 1035 # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the | |
| 1036 # default collation sequence is BINARY. | |
| 1037 # | |
| 1038 do_execsql_test e_createtable-3-9.1 { | |
| 1039 CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d); | |
| 1040 INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc'); | |
| 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 } {} | |
| 1045 do_createtable_tests 3.9 { | |
| 1046 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }} | |
| 1047 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }} | |
| 1048 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }} | |
| 1049 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }} | |
| 1050 } | |
| 1051 | |
| 1052 # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited | |
| 1053 # by the SQLITE_MAX_COLUMN compile-time parameter. | |
| 1054 # | |
| 1055 proc columns {n} { | |
| 1056 set res [list] | |
| 1057 for {set i 0} {$i < $n} {incr i} { lappend res "c$i" } | |
| 1058 join $res ", " | |
| 1059 } | |
| 1060 do_execsql_test e_createtable-3.10.1 [subst { | |
| 1061 CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]); | |
| 1062 }] {} | |
| 1063 do_catchsql_test e_createtable-3.10.2 [subst { | |
| 1064 CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]); | |
| 1065 }] {1 {too many columns on t10}} | |
| 1066 | |
| 1067 # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at | |
| 1068 # runtime using the sqlite3_limit() C/C++ interface. | |
| 1069 # | |
| 1070 # A 30,000 byte blob consumes 30,003 bytes of record space. A record | |
| 1071 # that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests | |
| 1072 # 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered | |
| 1073 # at runtime, are based on this calculation. | |
| 1074 # | |
| 1075 sqlite3_limit db SQLITE_LIMIT_COLUMN 500 | |
| 1076 do_execsql_test e_createtable-3.11.1 [subst { | |
| 1077 CREATE TABLE t10([columns 500]); | |
| 1078 }] {} | |
| 1079 do_catchsql_test e_createtable-3.11.2 [subst { | |
| 1080 CREATE TABLE t11([columns 501]); | |
| 1081 }] {1 {too many columns on t11}} | |
| 1082 | |
| 1083 # Check that it is not possible to raise the column limit above its | |
| 1084 # default compile time value. | |
| 1085 # | |
| 1086 sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2] | |
| 1087 do_catchsql_test e_createtable-3.11.3 [subst { | |
| 1088 CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]); | |
| 1089 }] {1 {too many columns on t11}} | |
| 1090 | |
| 1091 sqlite3_limit db SQLITE_LIMIT_LENGTH 90010 | |
| 1092 do_execsql_test e_createtable-3.11.4 { | |
| 1093 CREATE TABLE t12(a, b, c); | |
| 1094 INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000)); | |
| 1095 } {} | |
| 1096 do_catchsql_test e_createtable-3.11.5 { | |
| 1097 INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000)); | |
| 1098 } {1 {string or blob too big}} | |
| 1099 | |
| 1100 #------------------------------------------------------------------------- | |
| 1101 # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT | |
| 1102 # NULL and CHECK constraints). | |
| 1103 # | |
| 1104 | |
| 1105 # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one | |
| 1106 # PRIMARY KEY. | |
| 1107 # | |
| 1108 # EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY | |
| 1109 # KEY clause appears in a CREATE TABLE statement. | |
| 1110 # | |
| 1111 # To test the two above, show that zero primary keys is Ok, one primary | |
| 1112 # key is Ok, and two or more primary keys is an error. | |
| 1113 # | |
| 1114 drop_all_tables | |
| 1115 do_createtable_tests 4.1.1 { | |
| 1116 1 "CREATE TABLE t1(a, b, c)" {} | |
| 1117 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {} | |
| 1118 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {} | |
| 1119 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {} | |
| 1120 } | |
| 1121 do_createtable_tests 4.1.2 -error { | |
| 1122 table "t5" has more than one primary key | |
| 1123 } { | |
| 1124 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {} | |
| 1125 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {} | |
| 1126 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {} | |
| 1127 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {} | |
| 1128 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {} | |
| 1129 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {} | |
| 1130 } | |
| 1131 | |
| 1132 # EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary | |
| 1133 # tables but is required for WITHOUT ROWID tables. | |
| 1134 # | |
| 1135 do_catchsql_test 4.1.3 { | |
| 1136 CREATE TABLE t6(a, b); --ok | |
| 1137 } {0 {}} | |
| 1138 do_catchsql_test 4.1.4 { | |
| 1139 CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY | |
| 1140 } {1 {PRIMARY KEY missing on table t7}} | |
| 1141 | |
| 1142 | |
| 1143 proc table_pk {tbl} { | |
| 1144 set pk [list] | |
| 1145 db eval "pragma table_info($tbl)" a { | |
| 1146 if {$a(pk)} { lappend pk $a(name) } | |
| 1147 } | |
| 1148 set pk | |
| 1149 } | |
| 1150 | |
| 1151 # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a | |
| 1152 # column definition, then the primary key for the table consists of that | |
| 1153 # single column. | |
| 1154 # | |
| 1155 # The above is tested by 4.2.1.* | |
| 1156 # | |
| 1157 # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as | |
| 1158 # a table-constraint, then the primary key of the table consists of the | |
| 1159 # list of columns specified as part of the PRIMARY KEY clause. | |
| 1160 # | |
| 1161 # The above is tested by 4.2.2.* | |
| 1162 # | |
| 1163 do_createtable_tests 4.2 -repair { | |
| 1164 catchsql { DROP TABLE t5 } | |
| 1165 } -tclquery { | |
| 1166 table_pk t5 | |
| 1167 } { | |
| 1168 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} | |
| 1169 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a} | |
| 1170 | |
| 1171 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a} | |
| 1172 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c} | |
| 1173 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} | |
| 1174 } | |
| 1175 | |
| 1176 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must | |
| 1177 # have a unique combination of values in its primary key columns. | |
| 1178 # | |
| 1179 # EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts | |
| 1180 # to modify the table content so that two or more rows have identical | |
| 1181 # primary key values, that is a constraint violation. | |
| 1182 # | |
| 1183 drop_all_tables | |
| 1184 do_execsql_test 4.3.0 { | |
| 1185 CREATE TABLE t1(x PRIMARY KEY, y); | |
| 1186 INSERT INTO t1 VALUES(0, 'zero'); | |
| 1187 INSERT INTO t1 VALUES(45.5, 'one'); | |
| 1188 INSERT INTO t1 VALUES('brambles', 'two'); | |
| 1189 INSERT INTO t1 VALUES(X'ABCDEF', 'three'); | |
| 1190 | |
| 1191 CREATE TABLE t2(x, y, PRIMARY KEY(x, y)); | |
| 1192 INSERT INTO t2 VALUES(0, 'zero'); | |
| 1193 INSERT INTO t2 VALUES(45.5, 'one'); | |
| 1194 INSERT INTO t2 VALUES('brambles', 'two'); | |
| 1195 INSERT INTO t2 VALUES(X'ABCDEF', 'three'); | |
| 1196 } {} | |
| 1197 | |
| 1198 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} { | |
| 1199 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"} | |
| 1200 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"} | |
| 1201 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"} | |
| 1202 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"} | |
| 1203 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"} | |
| 1204 } | |
| 1205 do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} { | |
| 1206 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"} | |
| 1207 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"} | |
| 1208 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"} | |
| 1209 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"} | |
| 1210 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"} | |
| 1211 } | |
| 1212 do_createtable_tests 4.3.2 { | |
| 1213 1 "INSERT INTO t1 VALUES(-1, 0)" {} | |
| 1214 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {} | |
| 1215 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {} | |
| 1216 4 "INSERT INTO t1 VALUES('bramble', 'abc')" {} | |
| 1217 5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {} | |
| 1218 | |
| 1219 6 "INSERT INTO t2 VALUES(0, 0)" {} | |
| 1220 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {} | |
| 1221 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {} | |
| 1222 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {} | |
| 1223 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {} | |
| 1224 } | |
| 1225 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} { | |
| 1226 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"} | |
| 1227 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"} | |
| 1228 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"} | |
| 1229 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"} | |
| 1230 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"} | |
| 1231 } | |
| 1232 do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} { | |
| 1233 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"} | |
| 1234 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'" | |
| 1235 {"columns x, y are"} | |
| 1236 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"} | |
| 1237 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" | |
| 1238 {"columns x, y are"} | |
| 1239 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'" | |
| 1240 {"columns x, y are"} | |
| 1241 } | |
| 1242 | |
| 1243 | |
| 1244 # EVIDENCE-OF: R-52572-02078 For the purposes of determining the | |
| 1245 # uniqueness of primary key values, NULL values are considered distinct | |
| 1246 # from all other values, including other NULLs. | |
| 1247 # | |
| 1248 do_createtable_tests 4.4 { | |
| 1249 1 "INSERT INTO t1 VALUES(NULL, 0)" {} | |
| 1250 2 "INSERT INTO t1 VALUES(NULL, 0)" {} | |
| 1251 3 "INSERT INTO t1 VALUES(NULL, 0)" {} | |
| 1252 | |
| 1253 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {} | |
| 1254 5 "INSERT INTO t2 VALUES(NULL, 'one')" {} | |
| 1255 6 "INSERT INTO t2 VALUES(NULL, 'two')" {} | |
| 1256 7 "INSERT INTO t2 VALUES(NULL, 'three')" {} | |
| 1257 | |
| 1258 8 "INSERT INTO t2 VALUES(0, NULL)" {} | |
| 1259 9 "INSERT INTO t2 VALUES(45.5, NULL)" {} | |
| 1260 10 "INSERT INTO t2 VALUES(0.0, NULL)" {} | |
| 1261 11 "INSERT INTO t2 VALUES('brambles', NULL)" {} | |
| 1262 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {} | |
| 1263 | |
| 1264 13 "INSERT INTO t2 VALUES(NULL, NULL)" {} | |
| 1265 14 "INSERT INTO t2 VALUES(NULL, NULL)" {} | |
| 1266 } | |
| 1267 | |
| 1268 # EVIDENCE-OF: R-35113-43214 Unless the column is an INTEGER PRIMARY KEY | |
| 1269 # or the table is a WITHOUT ROWID table or the column is declared NOT | |
| 1270 # NULL, SQLite allows NULL values in a PRIMARY KEY column. | |
| 1271 # | |
| 1272 # If the column is an integer primary key, attempting to insert a NULL | |
| 1273 # into the column triggers the auto-increment behavior. Attempting | |
| 1274 # to use UPDATE to set an ipk column to a NULL value is an error. | |
| 1275 # | |
| 1276 do_createtable_tests 4.5.1 { | |
| 1277 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3 | |
| 1278 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6 | |
| 1279 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7 | |
| 1280 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2 | |
| 1281 } | |
| 1282 do_execsql_test 4.5.2 { | |
| 1283 CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v); | |
| 1284 INSERT INTO t3 VALUES(1, NULL, 2); | |
| 1285 INSERT INTO t3 VALUES('x', NULL, 'y'); | |
| 1286 SELECT u FROM t3; | |
| 1287 } {1 2} | |
| 1288 do_catchsql_test 4.5.3 { | |
| 1289 INSERT INTO t3 VALUES(2, 5, 3); | |
| 1290 UPDATE t3 SET u = NULL WHERE s = 2; | |
| 1291 } {1 {datatype mismatch}} | |
| 1292 do_catchsql_test 4.5.4 { | |
| 1293 CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID; | |
| 1294 INSERT INTO t4 VALUES(1, NULL, 2); | |
| 1295 } {1 {NOT NULL constraint failed: t4.u}} | |
| 1296 do_catchsql_test 4.5.5 { | |
| 1297 CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v); | |
| 1298 INSERT INTO t5 VALUES(1, NULL, 2); | |
| 1299 } {1 {NOT NULL constraint failed: t5.u}} | |
| 1300 | |
| 1301 # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY | |
| 1302 # KEY constraint, except that a single table may have any number of | |
| 1303 # UNIQUE constraints. | |
| 1304 # | |
| 1305 drop_all_tables | |
| 1306 do_createtable_tests 4.6 { | |
| 1307 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {} | |
| 1308 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {} | |
| 1309 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {} | |
| 1310 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {} | |
| 1311 } | |
| 1312 | |
| 1313 # EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table, | |
| 1314 # each row must contain a unique combination of values in the columns | |
| 1315 # identified by the UNIQUE constraint. | |
| 1316 # | |
| 1317 # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must | |
| 1318 # have a unique combination of values in its primary key columns. | |
| 1319 # | |
| 1320 do_execsql_test 4.7.0 { | |
| 1321 INSERT INTO t1 VALUES(1, 2); | |
| 1322 INSERT INTO t1 VALUES(4.3, 5.5); | |
| 1323 INSERT INTO t1 VALUES('reveal', 'variableness'); | |
| 1324 INSERT INTO t1 VALUES(X'123456', X'654321'); | |
| 1325 | |
| 1326 INSERT INTO t4 VALUES('xyx', 1, 1); | |
| 1327 INSERT INTO t4 VALUES('xyx', 2, 1); | |
| 1328 INSERT INTO t4 VALUES('uvw', 1, 1); | |
| 1329 } | |
| 1330 do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} { | |
| 1331 1 "INSERT INTO t1 VALUES(1, 'one')" {{t1.a}} | |
| 1332 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{t1.a}} | |
| 1333 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{t1.a}} | |
| 1334 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{t1.a}} | |
| 1335 | |
| 1336 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{t1.a}} | |
| 1337 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{t1.a}} | |
| 1338 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{t1.a}} | |
| 1339 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{t1.a}} | |
| 1340 | |
| 1341 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{t4.a, t4.b, t4.c}} | |
| 1342 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{t4.a, t4.b, t4.c}} | |
| 1343 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{t4.a, t4.b, t4.c}} | |
| 1344 | |
| 1345 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{t4.a, t4.b, t4.c}} | |
| 1346 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{t4.a, t4.b, t4.c}} | |
| 1347 14 "UPDATE t4 SET a=0, b=0, c=0" {{t4.a, t4.b, t4.c}} | |
| 1348 } | |
| 1349 | |
| 1350 # EVIDENCE-OF: R-00404-17670 For the purposes of UNIQUE constraints, | |
| 1351 # NULL values are considered distinct from all other values, including | |
| 1352 # other NULLs. | |
| 1353 # | |
| 1354 do_createtable_tests 4.8 { | |
| 1355 1 "INSERT INTO t1 VALUES(NULL, NULL)" {} | |
| 1356 2 "INSERT INTO t1 VALUES(NULL, NULL)" {} | |
| 1357 3 "UPDATE t1 SET a = NULL" {} | |
| 1358 4 "UPDATE t1 SET b = NULL" {} | |
| 1359 | |
| 1360 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} | |
| 1361 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} | |
| 1362 7 "UPDATE t4 SET a = NULL" {} | |
| 1363 8 "UPDATE t4 SET b = NULL" {} | |
| 1364 9 "UPDATE t4 SET c = NULL" {} | |
| 1365 } | |
| 1366 | |
| 1367 # EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY | |
| 1368 # constraints are implemented by creating a unique index in the | |
| 1369 # database. | |
| 1370 do_createtable_tests 4.9 -repair drop_all_tables -query { | |
| 1371 SELECT count(*) FROM sqlite_master WHERE type='index' | |
| 1372 } { | |
| 1373 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1 | |
| 1374 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0 | |
| 1375 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1 | |
| 1376 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2 | |
| 1377 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2 | |
| 1378 } | |
| 1379 | |
| 1380 # Obsolete: R-02252-33116 Such an index is used like any other index | |
| 1381 # in the database to optimize queries. | |
| 1382 # | |
| 1383 do_execsql_test 4.10.0 { | |
| 1384 CREATE TABLE t1(a, b PRIMARY KEY); | |
| 1385 CREATE TABLE t2(a, b, c, UNIQUE(b, c)); | |
| 1386 } | |
| 1387 do_createtable_tests 4.10 { | |
| 1388 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" | |
| 1389 {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)}} | |
| 1390 | |
| 1391 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" | |
| 1392 {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1}} | |
| 1393 | |
| 1394 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" | |
| 1395 {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}} | |
| 1396 } | |
| 1397 | |
| 1398 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a | |
| 1399 # column definition or specified as a table constraint. In practice it | |
| 1400 # makes no difference. | |
| 1401 # | |
| 1402 # All the tests that deal with CHECK constraints below (4.11.* and | |
| 1403 # 4.12.*) are run once for a table with the check constraint attached | |
| 1404 # to a column definition, and once with a table where the check | |
| 1405 # condition is specified as a table constraint. | |
| 1406 # | |
| 1407 # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the | |
| 1408 # table or an existing row is updated, the expression associated with | |
| 1409 # each CHECK constraint is evaluated and cast to a NUMERIC value in the | |
| 1410 # same way as a CAST expression. If the result is zero (integer value 0 | |
| 1411 # or real value 0.0), then a constraint violation has occurred. | |
| 1412 # | |
| 1413 drop_all_tables | |
| 1414 do_execsql_test 4.11 { | |
| 1415 CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 )); | |
| 1416 CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 )); | |
| 1417 INSERT INTO x1 VALUES('x', 'xx'); | |
| 1418 INSERT INTO x1 VALUES('y', 'yy'); | |
| 1419 INSERT INTO t1 SELECT * FROM x1; | |
| 1420 | |
| 1421 CREATE TABLE x2(a CHECK( a||b ), b); | |
| 1422 CREATE TABLE t2(a, b, CHECK( a||b )); | |
| 1423 INSERT INTO x2 VALUES(1, 'xx'); | |
| 1424 INSERT INTO x2 VALUES(1, 'yy'); | |
| 1425 INSERT INTO t2 SELECT * FROM x2; | |
| 1426 } | |
| 1427 | |
| 1428 do_createtable_tests 4.11 -error {CHECK constraint failed: %s} { | |
| 1429 1a "INSERT INTO x1 VALUES('one', 0)" {x1} | |
| 1430 1b "INSERT INTO t1 VALUES('one', -4.0)" {t1} | |
| 1431 | |
| 1432 2a "INSERT INTO x2 VALUES('abc', 1)" {x2} | |
| 1433 2b "INSERT INTO t2 VALUES('abc', 1)" {t2} | |
| 1434 | |
| 1435 3a "INSERT INTO x2 VALUES(0, 'abc')" {x2} | |
| 1436 3b "INSERT INTO t2 VALUES(0, 'abc')" {t2} | |
| 1437 | |
| 1438 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {t1} | |
| 1439 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {x1} | |
| 1440 | |
| 1441 4a "UPDATE x2 SET a='' WHERE rowid=1" {x2} | |
| 1442 4b "UPDATE t2 SET a='' WHERE rowid=1" {t2} | |
| 1443 } | |
| 1444 | |
| 1445 # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL, | |
| 1446 # or any other non-zero value, it is not a constraint violation. | |
| 1447 # | |
| 1448 do_createtable_tests 4.12 { | |
| 1449 1a "INSERT INTO x1 VALUES('one', NULL)" {} | |
| 1450 1b "INSERT INTO t1 VALUES('one', NULL)" {} | |
| 1451 | |
| 1452 2a "INSERT INTO x1 VALUES('one', 2)" {} | |
| 1453 2b "INSERT INTO t1 VALUES('one', 2)" {} | |
| 1454 | |
| 1455 3a "INSERT INTO x2 VALUES(1, 'abc')" {} | |
| 1456 3b "INSERT INTO t2 VALUES(1, 'abc')" {} | |
| 1457 } | |
| 1458 | |
| 1459 # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached | |
| 1460 # to a column definition, not specified as a table constraint. | |
| 1461 # | |
| 1462 drop_all_tables | |
| 1463 do_createtable_tests 4.13.1 { | |
| 1464 1 "CREATE TABLE t1(a NOT NULL, b)" {} | |
| 1465 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {} | |
| 1466 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {} | |
| 1467 } | |
| 1468 do_createtable_tests 4.13.2 -error { | |
| 1469 near "NOT": syntax error | |
| 1470 } { | |
| 1471 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {} | |
| 1472 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {} | |
| 1473 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {} | |
| 1474 } | |
| 1475 | |
| 1476 # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the | |
| 1477 # associated column may not contain a NULL value. Attempting to set the | |
| 1478 # column value to NULL when inserting a new row or updating an existing | |
| 1479 # one causes a constraint violation. | |
| 1480 # | |
| 1481 # These tests use the tables created by 4.13. | |
| 1482 # | |
| 1483 do_execsql_test 4.14.0 { | |
| 1484 INSERT INTO t1 VALUES('x', 'y'); | |
| 1485 INSERT INTO t1 VALUES('z', NULL); | |
| 1486 | |
| 1487 INSERT INTO t2 VALUES('x', 'y'); | |
| 1488 INSERT INTO t2 VALUES('z', NULL); | |
| 1489 | |
| 1490 INSERT INTO t3 VALUES('x', 'y', 'z'); | |
| 1491 INSERT INTO t3 VALUES(1, 2, 3); | |
| 1492 } | |
| 1493 do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} { | |
| 1494 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a} | |
| 1495 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a} | |
| 1496 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c} | |
| 1497 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b} | |
| 1498 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a} | |
| 1499 } | |
| 1500 | |
| 1501 # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL | |
| 1502 # constraints may be explicitly assigned a default conflict resolution | |
| 1503 # algorithm by including a conflict-clause in their definitions. | |
| 1504 # | |
| 1505 # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE | |
| 1506 # | |
| 1507 # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL | |
| 1508 # and UNIQUE constraints, respectively. | |
| 1509 # | |
| 1510 drop_all_tables | |
| 1511 do_execsql_test 4.15.0 { | |
| 1512 CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b); | |
| 1513 CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b); | |
| 1514 CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b); | |
| 1515 CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b); | |
| 1516 CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b); | |
| 1517 CREATE TABLE t1_xx(a PRIMARY KEY, b); | |
| 1518 | |
| 1519 INSERT INTO t1_ab VALUES(1, 'one'); | |
| 1520 INSERT INTO t1_ab VALUES(2, 'two'); | |
| 1521 INSERT INTO t1_ro SELECT * FROM t1_ab; | |
| 1522 INSERT INTO t1_ig SELECT * FROM t1_ab; | |
| 1523 INSERT INTO t1_fa SELECT * FROM t1_ab; | |
| 1524 INSERT INTO t1_re SELECT * FROM t1_ab; | |
| 1525 INSERT INTO t1_xx SELECT * FROM t1_ab; | |
| 1526 | |
| 1527 CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT); | |
| 1528 CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK); | |
| 1529 CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE); | |
| 1530 CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL); | |
| 1531 CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE); | |
| 1532 CREATE TABLE t2_xx(a, b NOT NULL); | |
| 1533 | |
| 1534 INSERT INTO t2_ab VALUES(1, 'one'); | |
| 1535 INSERT INTO t2_ab VALUES(2, 'two'); | |
| 1536 INSERT INTO t2_ro SELECT * FROM t2_ab; | |
| 1537 INSERT INTO t2_ig SELECT * FROM t2_ab; | |
| 1538 INSERT INTO t2_fa SELECT * FROM t2_ab; | |
| 1539 INSERT INTO t2_re SELECT * FROM t2_ab; | |
| 1540 INSERT INTO t2_xx SELECT * FROM t2_ab; | |
| 1541 | |
| 1542 CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT); | |
| 1543 CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK); | |
| 1544 CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE); | |
| 1545 CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL); | |
| 1546 CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE); | |
| 1547 CREATE TABLE t3_xx(a, b, UNIQUE(a, b)); | |
| 1548 | |
| 1549 INSERT INTO t3_ab VALUES(1, 'one'); | |
| 1550 INSERT INTO t3_ab VALUES(2, 'two'); | |
| 1551 INSERT INTO t3_ro SELECT * FROM t3_ab; | |
| 1552 INSERT INTO t3_ig SELECT * FROM t3_ab; | |
| 1553 INSERT INTO t3_fa SELECT * FROM t3_ab; | |
| 1554 INSERT INTO t3_re SELECT * FROM t3_ab; | |
| 1555 INSERT INTO t3_xx SELECT * FROM t3_ab; | |
| 1556 } | |
| 1557 | |
| 1558 foreach {tn tbl res ac data} { | |
| 1559 1 t1_ab {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three} | |
| 1560 2 t1_ro {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two} | |
| 1561 3 t1_fa {1 {UNIQUE constraint failed: t1_fa.a}} 0 {1 one 2 two 3 three 4
string} | |
| 1562 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string} | |
| 1563 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string} | |
| 1564 6 t1_xx {1 {UNIQUE constraint failed: t1_xx.a}} 0 {1 one 2 two 3 three} | |
| 1565 } { | |
| 1566 catchsql COMMIT | |
| 1567 do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" | |
| 1568 | |
| 1569 do_catchsql_test 4.15.$tn.2 " | |
| 1570 INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl; | |
| 1571 " $res | |
| 1572 | |
| 1573 do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac | |
| 1574 do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data | |
| 1575 } | |
| 1576 foreach {tn tbl res ac data} { | |
| 1577 1 t2_ab {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three} | |
| 1578 2 t2_ro {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two} | |
| 1579 3 t2_fa {1 {NOT NULL constraint failed: t2_fa.b}} 0 {1 one 2 two 3 three
4 xx} | |
| 1580 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx} | |
| 1581 5 t2_re {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three} | |
| 1582 6 t2_xx {1 {NOT NULL constraint failed: t2_xx.b}} 0 {1 one 2 two 3 three} | |
| 1583 } { | |
| 1584 catchsql COMMIT | |
| 1585 do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" | |
| 1586 | |
| 1587 do_catchsql_test 4.16.$tn.2 " | |
| 1588 INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl | |
| 1589 " $res | |
| 1590 | |
| 1591 do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac | |
| 1592 do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data | |
| 1593 } | |
| 1594 foreach {tn tbl res ac data} { | |
| 1595 1 t3_ab {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}} | |
| 1596 0 {1 one 2 two 3 three} | |
| 1597 2 t3_ro {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}} | |
| 1598 1 {1 one 2 two} | |
| 1599 3 t3_fa {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}} | |
| 1600 0 {1 one 2 two 3 three 4 three} | |
| 1601 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three} | |
| 1602 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three} | |
| 1603 6 t3_xx {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}} | |
| 1604 0 {1 one 2 two 3 three} | |
| 1605 } { | |
| 1606 catchsql COMMIT | |
| 1607 do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" | |
| 1608 | |
| 1609 do_catchsql_test 4.17.$tn.2 " | |
| 1610 INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl | |
| 1611 " $res | |
| 1612 | |
| 1613 do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac | |
| 1614 do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data | |
| 1615 } | |
| 1616 catchsql COMMIT | |
| 1617 | |
| 1618 # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not | |
| 1619 # include a conflict-clause or it is a CHECK constraint, the default | |
| 1620 # conflict resolution algorithm is ABORT. | |
| 1621 # | |
| 1622 # The first half of the above is tested along with explicit ON | |
| 1623 # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx | |
| 1624 # and t3_xx). The following just tests that the default conflict | |
| 1625 # handling for CHECK constraints is ABORT. | |
| 1626 # | |
| 1627 do_execsql_test 4.18.1 { | |
| 1628 CREATE TABLE t4(a, b CHECK (b!=10)); | |
| 1629 INSERT INTO t4 VALUES(1, 2); | |
| 1630 INSERT INTO t4 VALUES(3, 4); | |
| 1631 } | |
| 1632 do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) } | |
| 1633 do_catchsql_test 4.18.3 { | |
| 1634 INSERT INTO t4 SELECT a+4, b+4 FROM t4 | |
| 1635 } {1 {CHECK constraint failed: t4}} | |
| 1636 do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0 | |
| 1637 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6} | |
| 1638 | |
| 1639 # EVIDENCE-OF: R-19114-56113 Different constraints within the same table | |
| 1640 # may have different default conflict resolution algorithms. | |
| 1641 # | |
| 1642 do_execsql_test 4.19.0 { | |
| 1643 CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT); | |
| 1644 } | |
| 1645 do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}} | |
| 1646 do_execsql_test 4.19.2 { SELECT * FROM t5 } {} | |
| 1647 do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \ | |
| 1648 {1 {NOT NULL constraint failed: t5.b}} | |
| 1649 do_execsql_test 4.19.4 { SELECT * FROM t5 } {} | |
| 1650 | |
| 1651 #------------------------------------------------------------------------ | |
| 1652 # Tests for INTEGER PRIMARY KEY and rowid related statements. | |
| 1653 # | |
| 1654 | |
| 1655 # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one | |
| 1656 # of the special case-independent names "rowid", "oid", or "_rowid_" in | |
| 1657 # place of a column name. | |
| 1658 # | |
| 1659 # EVIDENCE-OF: R-06726-07466 A column name can be any of the names | |
| 1660 # defined in the CREATE TABLE statement or one of the following special | |
| 1661 # identifiers: "ROWID", "OID", or "_ROWID_". | |
| 1662 # | |
| 1663 drop_all_tables | |
| 1664 do_execsql_test 5.1.0 { | |
| 1665 CREATE TABLE t1(x, y); | |
| 1666 INSERT INTO t1 VALUES('one', 'first'); | |
| 1667 INSERT INTO t1 VALUES('two', 'second'); | |
| 1668 INSERT INTO t1 VALUES('three', 'third'); | |
| 1669 } | |
| 1670 do_createtable_tests 5.1 { | |
| 1671 1 "SELECT rowid FROM t1" {1 2 3} | |
| 1672 2 "SELECT oid FROM t1" {1 2 3} | |
| 1673 3 "SELECT _rowid_ FROM t1" {1 2 3} | |
| 1674 4 "SELECT ROWID FROM t1" {1 2 3} | |
| 1675 5 "SELECT OID FROM t1" {1 2 3} | |
| 1676 6 "SELECT _ROWID_ FROM t1" {1 2 3} | |
| 1677 7 "SELECT RoWiD FROM t1" {1 2 3} | |
| 1678 8 "SELECT OiD FROM t1" {1 2 3} | |
| 1679 9 "SELECT _RoWiD_ FROM t1" {1 2 3} | |
| 1680 } | |
| 1681 | |
| 1682 # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column | |
| 1683 # named "rowid", "oid" or "_rowid_", then that name always refers the | |
| 1684 # explicitly declared column and cannot be used to retrieve the integer | |
| 1685 # rowid value. | |
| 1686 # | |
| 1687 # EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the | |
| 1688 # row key if the CREATE TABLE statement does not define a real column | |
| 1689 # with the same name. | |
| 1690 # | |
| 1691 do_execsql_test 5.2.0 { | |
| 1692 CREATE TABLE t2(oid, b); | |
| 1693 CREATE TABLE t3(a, _rowid_); | |
| 1694 CREATE TABLE t4(a, b, rowid); | |
| 1695 | |
| 1696 INSERT INTO t2 VALUES('one', 'two'); | |
| 1697 INSERT INTO t2 VALUES('three', 'four'); | |
| 1698 | |
| 1699 INSERT INTO t3 VALUES('five', 'six'); | |
| 1700 INSERT INTO t3 VALUES('seven', 'eight'); | |
| 1701 | |
| 1702 INSERT INTO t4 VALUES('nine', 'ten', 'eleven'); | |
| 1703 INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen'); | |
| 1704 } | |
| 1705 do_createtable_tests 5.2 { | |
| 1706 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2} | |
| 1707 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight} | |
| 1708 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2} | |
| 1709 } | |
| 1710 | |
| 1711 | |
| 1712 # Argument $tbl is the name of a table in the database. Argument $col is | |
| 1713 # the name of one of the tables columns. Return 1 if $col is an alias for | |
| 1714 # the rowid, or 0 otherwise. | |
| 1715 # | |
| 1716 proc is_integer_primary_key {tbl col} { | |
| 1717 lindex [db eval [subst { | |
| 1718 DELETE FROM $tbl; | |
| 1719 INSERT INTO $tbl ($col) VALUES(0); | |
| 1720 SELECT (rowid==$col) FROM $tbl; | |
| 1721 DELETE FROM $tbl; | |
| 1722 }]] 0 | |
| 1723 } | |
| 1724 | |
| 1725 # EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid | |
| 1726 # table has a primary key that consists of a single column and the | |
| 1727 # declared type of that column is "INTEGER" in any mixture of upper and | |
| 1728 # lower case, then the column becomes an alias for the rowid. | |
| 1729 # | |
| 1730 # EVIDENCE-OF: R-45951-08347 if the declaration of a column with | |
| 1731 # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does | |
| 1732 # not become an alias for the rowid and is not classified as an integer | |
| 1733 # primary key. | |
| 1734 # | |
| 1735 do_createtable_tests 5.3 -tclquery { | |
| 1736 is_integer_primary_key t5 pk | |
| 1737 } -repair { | |
| 1738 catchsql { DROP TABLE t5 } | |
| 1739 } { | |
| 1740 1 "CREATE TABLE t5(pk integer primary key)" 1 | |
| 1741 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1 | |
| 1742 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1 | |
| 1743 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0 | |
| 1744 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0 | |
| 1745 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0 | |
| 1746 7 "CREATE TABLE t5(pk int primary key, v integer)" 0 | |
| 1747 8 "CREATE TABLE t5(pk inTEger primary key)" 1 | |
| 1748 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1 | |
| 1749 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1 | |
| 1750 } | |
| 1751 | |
| 1752 # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or | |
| 1753 # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary | |
| 1754 # key column to behave as an ordinary table column with integer affinity | |
| 1755 # and a unique index, not as an alias for the rowid. | |
| 1756 # | |
| 1757 do_execsql_test 5.4.1 { | |
| 1758 CREATE TABLE t6(pk INT primary key); | |
| 1759 CREATE TABLE t7(pk BIGINT primary key); | |
| 1760 CREATE TABLE t8(pk SHORT INTEGER primary key); | |
| 1761 CREATE TABLE t9(pk UNSIGNED INTEGER primary key); | |
| 1762 } | |
| 1763 do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0 | |
| 1764 do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0 | |
| 1765 do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0 | |
| 1766 do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0 | |
| 1767 | |
| 1768 do_execsql_test 5.4.3 { | |
| 1769 INSERT INTO t6 VALUES('2.0'); | |
| 1770 INSERT INTO t7 VALUES('2.0'); | |
| 1771 INSERT INTO t8 VALUES('2.0'); | |
| 1772 INSERT INTO t9 VALUES('2.0'); | |
| 1773 SELECT typeof(pk), pk FROM t6; | |
| 1774 SELECT typeof(pk), pk FROM t7; | |
| 1775 SELECT typeof(pk), pk FROM t8; | |
| 1776 SELECT typeof(pk), pk FROM t9; | |
| 1777 } {integer 2 integer 2 integer 2 integer 2} | |
| 1778 | |
| 1779 do_catchsql_test 5.4.4.1 { | |
| 1780 INSERT INTO t6 VALUES(2) | |
| 1781 } {1 {UNIQUE constraint failed: t6.pk}} | |
| 1782 do_catchsql_test 5.4.4.2 { | |
| 1783 INSERT INTO t7 VALUES(2) | |
| 1784 } {1 {UNIQUE constraint failed: t7.pk}} | |
| 1785 do_catchsql_test 5.4.4.3 { | |
| 1786 INSERT INTO t8 VALUES(2) | |
| 1787 } {1 {UNIQUE constraint failed: t8.pk}} | |
| 1788 do_catchsql_test 5.4.4.4 { | |
| 1789 INSERT INTO t9 VALUES(2) | |
| 1790 } {1 {UNIQUE constraint failed: t9.pk}} | |
| 1791 | |
| 1792 # EVIDENCE-OF: R-56094-57830 the following three table declarations all | |
| 1793 # cause the column "x" to be an alias for the rowid (an integer primary | |
| 1794 # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE | |
| 1795 # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y, | |
| 1796 # z, PRIMARY KEY(x DESC)); | |
| 1797 # | |
| 1798 # EVIDENCE-OF: R-20149-25884 the following declaration does not result | |
| 1799 # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY | |
| 1800 # KEY DESC, y, z); | |
| 1801 # | |
| 1802 do_createtable_tests 5 -tclquery { | |
| 1803 is_integer_primary_key t x | |
| 1804 } -repair { | |
| 1805 catchsql { DROP TABLE t } | |
| 1806 } { | |
| 1807 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1 | |
| 1808 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1 | |
| 1809 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1 | |
| 1810 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0 | |
| 1811 } | |
| 1812 | |
| 1813 # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an | |
| 1814 # UPDATE statement in the same way as any other column value can, either | |
| 1815 # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by | |
| 1816 # using an alias created by an integer primary key. | |
| 1817 # | |
| 1818 do_execsql_test 5.7.0 { | |
| 1819 CREATE TABLE t10(a, b); | |
| 1820 INSERT INTO t10 VALUES('ten', 10); | |
| 1821 | |
| 1822 CREATE TABLE t11(a, b INTEGER PRIMARY KEY); | |
| 1823 INSERT INTO t11 VALUES('ten', 10); | |
| 1824 } | |
| 1825 do_createtable_tests 5.7.1 -query { | |
| 1826 SELECT rowid, _rowid_, oid FROM t10; | |
| 1827 } { | |
| 1828 1 "UPDATE t10 SET rowid = 5" {5 5 5} | |
| 1829 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6} | |
| 1830 3 "UPDATE t10 SET oid = 7" {7 7 7} | |
| 1831 } | |
| 1832 do_createtable_tests 5.7.2 -query { | |
| 1833 SELECT rowid, _rowid_, oid, b FROM t11; | |
| 1834 } { | |
| 1835 1 "UPDATE t11 SET rowid = 5" {5 5 5 5} | |
| 1836 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6} | |
| 1837 3 "UPDATE t11 SET oid = 7" {7 7 7 7} | |
| 1838 4 "UPDATE t11 SET b = 8" {8 8 8 8} | |
| 1839 } | |
| 1840 | |
| 1841 # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide | |
| 1842 # a value to use as the rowid for each row inserted. | |
| 1843 # | |
| 1844 do_createtable_tests 5.8.1 -query { | |
| 1845 SELECT rowid, _rowid_, oid FROM t10; | |
| 1846 } -repair { | |
| 1847 execsql { DELETE FROM t10 } | |
| 1848 } { | |
| 1849 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15} | |
| 1850 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16} | |
| 1851 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17} | |
| 1852 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3} | |
| 1853 } | |
| 1854 do_createtable_tests 5.8.2 -query { | |
| 1855 SELECT rowid, _rowid_, oid, b FROM t11; | |
| 1856 } -repair { | |
| 1857 execsql { DELETE FROM t11 } | |
| 1858 } { | |
| 1859 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15} | |
| 1860 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16} | |
| 1861 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17} | |
| 1862 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2} | |
| 1863 } | |
| 1864 | |
| 1865 # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer | |
| 1866 # primary key or rowid column must contain integer values. Integer | |
| 1867 # primary key or rowid columns are not able to hold floating point | |
| 1868 # values, strings, BLOBs, or NULLs. | |
| 1869 # | |
| 1870 # This is considered by the tests for the following 3 statements, | |
| 1871 # which show that: | |
| 1872 # | |
| 1873 # 1. Attempts to UPDATE a rowid column to a non-integer value fail, | |
| 1874 # 2. Attempts to INSERT a real, string or blob value into a rowid | |
| 1875 # column fail, and | |
| 1876 # 3. Attempting to INSERT a NULL value into a rowid column causes the | |
| 1877 # system to automatically select an integer value to use. | |
| 1878 # | |
| 1879 | |
| 1880 | |
| 1881 # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an | |
| 1882 # integer primary key or rowid column to a NULL or blob value, or to a | |
| 1883 # string or real value that cannot be losslessly converted to an | |
| 1884 # integer, a "datatype mismatch" error occurs and the statement is | |
| 1885 # aborted. | |
| 1886 # | |
| 1887 drop_all_tables | |
| 1888 do_execsql_test 5.9.0 { | |
| 1889 CREATE TABLE t12(x INTEGER PRIMARY KEY, y); | |
| 1890 INSERT INTO t12 VALUES(5, 'five'); | |
| 1891 } | |
| 1892 do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } { | |
| 1893 1 "UPDATE t12 SET x = 4" {integer 4} | |
| 1894 2 "UPDATE t12 SET x = 10.0" {integer 10} | |
| 1895 3 "UPDATE t12 SET x = '12.0'" {integer 12} | |
| 1896 4 "UPDATE t12 SET x = '-15.0'" {integer -15} | |
| 1897 } | |
| 1898 do_createtable_tests 5.9.2 -error { | |
| 1899 datatype mismatch | |
| 1900 } { | |
| 1901 1 "UPDATE t12 SET x = 4.1" {} | |
| 1902 2 "UPDATE t12 SET x = 'hello'" {} | |
| 1903 3 "UPDATE t12 SET x = NULL" {} | |
| 1904 4 "UPDATE t12 SET x = X'ABCD'" {} | |
| 1905 5 "UPDATE t12 SET x = X'3900'" {} | |
| 1906 6 "UPDATE t12 SET x = X'39'" {} | |
| 1907 } | |
| 1908 | |
| 1909 # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a | |
| 1910 # blob value, or a string or real value that cannot be losslessly | |
| 1911 # converted to an integer into an integer primary key or rowid column, a | |
| 1912 # "datatype mismatch" error occurs and the statement is aborted. | |
| 1913 # | |
| 1914 do_execsql_test 5.10.0 { DELETE FROM t12 } | |
| 1915 do_createtable_tests 5.10.1 -error { | |
| 1916 datatype mismatch | |
| 1917 } { | |
| 1918 1 "INSERT INTO t12(x) VALUES(4.1)" {} | |
| 1919 2 "INSERT INTO t12(x) VALUES('hello')" {} | |
| 1920 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {} | |
| 1921 4 "INSERT INTO t12(x) VALUES(X'3900')" {} | |
| 1922 5 "INSERT INTO t12(x) VALUES(X'39')" {} | |
| 1923 } | |
| 1924 do_createtable_tests 5.10.2 -query { | |
| 1925 SELECT typeof(x), x FROM t12 | |
| 1926 } -repair { | |
| 1927 execsql { DELETE FROM t12 } | |
| 1928 } { | |
| 1929 1 "INSERT INTO t12(x) VALUES(4)" {integer 4} | |
| 1930 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10} | |
| 1931 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12} | |
| 1932 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000} | |
| 1933 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14} | |
| 1934 } | |
| 1935 | |
| 1936 # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a | |
| 1937 # NULL value into a rowid or integer primary key column, the system | |
| 1938 # chooses an integer value to use as the rowid automatically. | |
| 1939 # | |
| 1940 do_execsql_test 5.11.0 { DELETE FROM t12 } | |
| 1941 do_createtable_tests 5.11 -query { | |
| 1942 SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12) | |
| 1943 } { | |
| 1944 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1} | |
| 1945 2 "INSERT INTO t12(y) VALUES(5)" {integer 2} | |
| 1946 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3} | |
| 1947 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" | |
| 1948 {integer 4 integer 5 integer 6} | |
| 1949 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3" | |
| 1950 {integer 7 integer 8 integer 9} | |
| 1951 } | |
| 1952 | |
| 1953 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
