| OLD | NEW |
|---|
| (Empty) |
| 1 # 2001 September 15 | |
| 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 # This file implements regression tests for SQLite library. The | |
| 12 # focus of this file is testing the CREATE TABLE statement. | |
| 13 # | |
| 14 | |
| 15 set testdir [file dirname $argv0] | |
| 16 source $testdir/tester.tcl | |
| 17 | |
| 18 # Create a basic table and verify it is added to sqlite_master | |
| 19 # | |
| 20 do_test table-1.1 { | |
| 21 execsql { | |
| 22 CREATE TABLE test1 ( | |
| 23 one varchar(10), | |
| 24 two text | |
| 25 ) | |
| 26 } | |
| 27 execsql { | |
| 28 SELECT sql FROM sqlite_master WHERE type!='meta' | |
| 29 } | |
| 30 } {{CREATE TABLE test1 ( | |
| 31 one varchar(10), | |
| 32 two text | |
| 33 )}} | |
| 34 | |
| 35 | |
| 36 # Verify the other fields of the sqlite_master file. | |
| 37 # | |
| 38 do_test table-1.3 { | |
| 39 execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'} | |
| 40 } {test1 test1 table} | |
| 41 | |
| 42 # Close and reopen the database. Verify that everything is | |
| 43 # still the same. | |
| 44 # | |
| 45 do_test table-1.4 { | |
| 46 db close | |
| 47 sqlite3 db test.db | |
| 48 execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'} | |
| 49 } {test1 test1 table} | |
| 50 | |
| 51 # Drop the database and make sure it disappears. | |
| 52 # | |
| 53 do_test table-1.5 { | |
| 54 execsql {DROP TABLE test1} | |
| 55 execsql {SELECT * FROM sqlite_master WHERE type!='meta'} | |
| 56 } {} | |
| 57 | |
| 58 # Close and reopen the database. Verify that the table is | |
| 59 # still gone. | |
| 60 # | |
| 61 do_test table-1.6 { | |
| 62 db close | |
| 63 sqlite3 db test.db | |
| 64 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 65 } {} | |
| 66 | |
| 67 # Repeat the above steps, but this time quote the table name. | |
| 68 # | |
| 69 do_test table-1.10 { | |
| 70 execsql {CREATE TABLE "create" (f1 int)} | |
| 71 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 72 } {create} | |
| 73 do_test table-1.11 { | |
| 74 execsql {DROP TABLE "create"} | |
| 75 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} | |
| 76 } {} | |
| 77 do_test table-1.12 { | |
| 78 execsql {CREATE TABLE test1("f1 ho" int)} | |
| 79 execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'} | |
| 80 } {test1} | |
| 81 do_test table-1.13 { | |
| 82 execsql {DROP TABLE "TEST1"} | |
| 83 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} | |
| 84 } {} | |
| 85 | |
| 86 | |
| 87 | |
| 88 # Verify that we cannot make two tables with the same name | |
| 89 # | |
| 90 do_test table-2.1 { | |
| 91 execsql {CREATE TABLE TEST2(one text)} | |
| 92 catchsql {CREATE TABLE test2(two text default 'hi')} | |
| 93 } {1 {table test2 already exists}} | |
| 94 do_test table-2.1.1 { | |
| 95 catchsql {CREATE TABLE "test2" (two)} | |
| 96 } {1 {table "test2" already exists}} | |
| 97 do_test table-2.1b { | |
| 98 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] | |
| 99 lappend v $msg | |
| 100 } {1 {object name reserved for internal use: sqlite_master}} | |
| 101 do_test table-2.1c { | |
| 102 db close | |
| 103 sqlite3 db test.db | |
| 104 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] | |
| 105 lappend v $msg | |
| 106 } {1 {object name reserved for internal use: sqlite_master}} | |
| 107 do_test table-2.1d { | |
| 108 catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)} | |
| 109 } {0 {}} | |
| 110 do_test table-2.1e { | |
| 111 catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)} | |
| 112 } {0 {}} | |
| 113 do_test table-2.1f { | |
| 114 execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 115 } {} | |
| 116 | |
| 117 # Verify that we cannot make a table with the same name as an index | |
| 118 # | |
| 119 do_test table-2.2a { | |
| 120 execsql {CREATE TABLE test2(one text)} | |
| 121 execsql {CREATE INDEX test3 ON test2(one)} | |
| 122 catchsql {CREATE TABLE test3(two text)} | |
| 123 } {1 {there is already an index named test3}} | |
| 124 do_test table-2.2b { | |
| 125 db close | |
| 126 sqlite3 db test.db | |
| 127 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] | |
| 128 lappend v $msg | |
| 129 } {1 {there is already an index named test3}} | |
| 130 do_test table-2.2c { | |
| 131 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 132 } {test2 test3} | |
| 133 do_test table-2.2d { | |
| 134 execsql {DROP INDEX test3} | |
| 135 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] | |
| 136 lappend v $msg | |
| 137 } {0 {}} | |
| 138 do_test table-2.2e { | |
| 139 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 140 } {test2 test3} | |
| 141 do_test table-2.2f { | |
| 142 execsql {DROP TABLE test2; DROP TABLE test3} | |
| 143 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 144 } {} | |
| 145 | |
| 146 # Create a table with many field names | |
| 147 # | |
| 148 set big_table \ | |
| 149 {CREATE TABLE big( | |
| 150 f1 varchar(20), | |
| 151 f2 char(10), | |
| 152 f3 varchar(30) primary key, | |
| 153 f4 text, | |
| 154 f5 text, | |
| 155 f6 text, | |
| 156 f7 text, | |
| 157 f8 text, | |
| 158 f9 text, | |
| 159 f10 text, | |
| 160 f11 text, | |
| 161 f12 text, | |
| 162 f13 text, | |
| 163 f14 text, | |
| 164 f15 text, | |
| 165 f16 text, | |
| 166 f17 text, | |
| 167 f18 text, | |
| 168 f19 text, | |
| 169 f20 text | |
| 170 )} | |
| 171 do_test table-3.1 { | |
| 172 execsql $big_table | |
| 173 execsql {SELECT sql FROM sqlite_master WHERE type=='table'} | |
| 174 } \{$big_table\} | |
| 175 do_test table-3.2 { | |
| 176 set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg] | |
| 177 lappend v $msg | |
| 178 } {1 {table BIG already exists}} | |
| 179 do_test table-3.3 { | |
| 180 set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg] | |
| 181 lappend v $msg | |
| 182 } {1 {table biG already exists}} | |
| 183 do_test table-3.4 { | |
| 184 set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg] | |
| 185 lappend v $msg | |
| 186 } {1 {table bIg already exists}} | |
| 187 do_test table-3.5 { | |
| 188 db close | |
| 189 sqlite3 db test.db | |
| 190 set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg] | |
| 191 lappend v $msg | |
| 192 } {1 {table Big already exists}} | |
| 193 do_test table-3.6 { | |
| 194 execsql {DROP TABLE big} | |
| 195 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 196 } {} | |
| 197 | |
| 198 # Try creating large numbers of tables | |
| 199 # | |
| 200 set r {} | |
| 201 for {set i 1} {$i<=100} {incr i} { | |
| 202 lappend r [format test%03d $i] | |
| 203 } | |
| 204 do_test table-4.1 { | |
| 205 for {set i 1} {$i<=100} {incr i} { | |
| 206 set sql "CREATE TABLE [format test%03d $i] (" | |
| 207 for {set k 1} {$k<$i} {incr k} { | |
| 208 append sql "field$k text," | |
| 209 } | |
| 210 append sql "last_field text)" | |
| 211 execsql $sql | |
| 212 } | |
| 213 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 214 } $r | |
| 215 do_test table-4.1b { | |
| 216 db close | |
| 217 sqlite3 db test.db | |
| 218 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 219 } $r | |
| 220 | |
| 221 # Drop the even numbered tables | |
| 222 # | |
| 223 set r {} | |
| 224 for {set i 1} {$i<=100} {incr i 2} { | |
| 225 lappend r [format test%03d $i] | |
| 226 } | |
| 227 do_test table-4.2 { | |
| 228 for {set i 2} {$i<=100} {incr i 2} { | |
| 229 # if {$i==38} {execsql {pragma vdbe_trace=on}} | |
| 230 set sql "DROP TABLE [format TEST%03d $i]" | |
| 231 execsql $sql | |
| 232 } | |
| 233 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 234 } $r | |
| 235 #exit | |
| 236 | |
| 237 # Drop the odd number tables | |
| 238 # | |
| 239 do_test table-4.3 { | |
| 240 for {set i 1} {$i<=100} {incr i 2} { | |
| 241 set sql "DROP TABLE [format test%03d $i]" | |
| 242 execsql $sql | |
| 243 } | |
| 244 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} | |
| 245 } {} | |
| 246 | |
| 247 # Try to drop a table that does not exist | |
| 248 # | |
| 249 do_test table-5.1.1 { | |
| 250 catchsql {DROP TABLE test009} | |
| 251 } {1 {no such table: test009}} | |
| 252 do_test table-5.1.2 { | |
| 253 catchsql {DROP TABLE IF EXISTS test009} | |
| 254 } {0 {}} | |
| 255 | |
| 256 # Try to drop sqlite_master | |
| 257 # | |
| 258 do_test table-5.2 { | |
| 259 catchsql {DROP TABLE IF EXISTS sqlite_master} | |
| 260 } {1 {table sqlite_master may not be dropped}} | |
| 261 | |
| 262 # Dropping sqlite_statN tables is OK. | |
| 263 # | |
| 264 do_test table-5.2.1 { | |
| 265 db eval { | |
| 266 ANALYZE; | |
| 267 DROP TABLE IF EXISTS sqlite_stat1; | |
| 268 DROP TABLE IF EXISTS sqlite_stat2; | |
| 269 DROP TABLE IF EXISTS sqlite_stat3; | |
| 270 DROP TABLE IF EXISTS sqlite_stat4; | |
| 271 SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*'; | |
| 272 } | |
| 273 } {} | |
| 274 | |
| 275 # Make sure an EXPLAIN does not really create a new table | |
| 276 # | |
| 277 do_test table-5.3 { | |
| 278 ifcapable {explain} { | |
| 279 execsql {EXPLAIN CREATE TABLE test1(f1 int)} | |
| 280 } | |
| 281 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 282 } {} | |
| 283 | |
| 284 # Make sure an EXPLAIN does not really drop an existing table | |
| 285 # | |
| 286 do_test table-5.4 { | |
| 287 execsql {CREATE TABLE test1(f1 int)} | |
| 288 ifcapable {explain} { | |
| 289 execsql {EXPLAIN DROP TABLE test1} | |
| 290 } | |
| 291 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 292 } {test1} | |
| 293 | |
| 294 # Create a table with a goofy name | |
| 295 # | |
| 296 #do_test table-6.1 { | |
| 297 # execsql {CREATE TABLE 'Spaces In This Name!'(x int)} | |
| 298 # execsql {INSERT INTO 'spaces in this name!' VALUES(1)} | |
| 299 # set list [glob -nocomplain testdb/spaces*.tbl] | |
| 300 #} {testdb/spaces+in+this+name+.tbl} | |
| 301 | |
| 302 # Try using keywords as table names or column names. | |
| 303 # | |
| 304 do_test table-7.1 { | |
| 305 set v [catch {execsql { | |
| 306 CREATE TABLE weird( | |
| 307 desc text, | |
| 308 asc text, | |
| 309 key int, | |
| 310 [14_vac] boolean, | |
| 311 fuzzy_dog_12 varchar(10), | |
| 312 begin blob, | |
| 313 end clob | |
| 314 ) | |
| 315 }} msg] | |
| 316 lappend v $msg | |
| 317 } {0 {}} | |
| 318 do_test table-7.2 { | |
| 319 execsql { | |
| 320 INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all'); | |
| 321 SELECT * FROM weird; | |
| 322 } | |
| 323 } {a b 9 0 xyz hi y'all} | |
| 324 do_test table-7.3 { | |
| 325 execsql2 { | |
| 326 SELECT * FROM weird; | |
| 327 } | |
| 328 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} | |
| 329 do_test table-7.3 { | |
| 330 execsql { | |
| 331 CREATE TABLE savepoint(release); | |
| 332 INSERT INTO savepoint(release) VALUES(10); | |
| 333 UPDATE savepoint SET release = 5; | |
| 334 SELECT release FROM savepoint; | |
| 335 } | |
| 336 } {5} | |
| 337 | |
| 338 # Try out the CREATE TABLE AS syntax | |
| 339 # | |
| 340 do_test table-8.1 { | |
| 341 execsql2 { | |
| 342 CREATE TABLE t2 AS SELECT * FROM weird; | |
| 343 SELECT * FROM t2; | |
| 344 } | |
| 345 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} | |
| 346 do_test table-8.1.1 { | |
| 347 execsql { | |
| 348 SELECT sql FROM sqlite_master WHERE name='t2'; | |
| 349 } | |
| 350 } {{CREATE TABLE t2( | |
| 351 "desc" TEXT, | |
| 352 "asc" TEXT, | |
| 353 "key" INT, | |
| 354 "14_vac" NUM, | |
| 355 fuzzy_dog_12 TEXT, | |
| 356 "begin", | |
| 357 "end" TEXT | |
| 358 )}} | |
| 359 do_test table-8.2 { | |
| 360 execsql { | |
| 361 CREATE TABLE "t3""xyz"(a,b,c); | |
| 362 INSERT INTO [t3"xyz] VALUES(1,2,3); | |
| 363 SELECT * FROM [t3"xyz]; | |
| 364 } | |
| 365 } {1 2 3} | |
| 366 do_test table-8.3 { | |
| 367 execsql2 { | |
| 368 CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz]; | |
| 369 SELECT * FROM [t4"abc]; | |
| 370 } | |
| 371 } {cnt 1 max(b+c) 5} | |
| 372 | |
| 373 # Update for v3: The declaration type of anything except a column is now a | |
| 374 # NULL pointer, so the created table has no column types. (Changed result | |
| 375 # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}). | |
| 376 do_test table-8.3.1 { | |
| 377 execsql { | |
| 378 SELECT sql FROM sqlite_master WHERE name='t4"abc' | |
| 379 } | |
| 380 } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}} | |
| 381 | |
| 382 ifcapable tempdb { | |
| 383 do_test table-8.4 { | |
| 384 execsql2 { | |
| 385 CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz]; | |
| 386 SELECT * FROM t5; | |
| 387 } | |
| 388 } {y'all 1} | |
| 389 } | |
| 390 | |
| 391 do_test table-8.5 { | |
| 392 db close | |
| 393 sqlite3 db test.db | |
| 394 execsql2 { | |
| 395 SELECT * FROM [t4"abc]; | |
| 396 } | |
| 397 } {cnt 1 max(b+c) 5} | |
| 398 do_test table-8.6 { | |
| 399 execsql2 { | |
| 400 SELECT * FROM t2; | |
| 401 } | |
| 402 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} | |
| 403 do_test table-8.7 { | |
| 404 catchsql { | |
| 405 SELECT * FROM t5; | |
| 406 } | |
| 407 } {1 {no such table: t5}} | |
| 408 do_test table-8.8 { | |
| 409 catchsql { | |
| 410 CREATE TABLE t5 AS SELECT * FROM no_such_table; | |
| 411 } | |
| 412 } {1 {no such table: no_such_table}} | |
| 413 | |
| 414 do_test table-8.9 { | |
| 415 execsql { | |
| 416 CREATE TABLE t10("col.1" [char.3]); | |
| 417 CREATE TABLE t11 AS SELECT * FROM t10; | |
| 418 SELECT sql FROM sqlite_master WHERE name = 't11'; | |
| 419 } | |
| 420 } {{CREATE TABLE t11("col.1" TEXT)}} | |
| 421 do_test table-8.10 { | |
| 422 execsql { | |
| 423 CREATE TABLE t12( | |
| 424 a INTEGER, | |
| 425 b VARCHAR(10), | |
| 426 c VARCHAR(1,10), | |
| 427 d VARCHAR(+1,-10), | |
| 428 e VARCHAR (+1,-10), | |
| 429 f "VARCHAR (+1,-10, 5)", | |
| 430 g BIG INTEGER | |
| 431 ); | |
| 432 CREATE TABLE t13 AS SELECT * FROM t12; | |
| 433 SELECT sql FROM sqlite_master WHERE name = 't13'; | |
| 434 } | |
| 435 } {{CREATE TABLE t13( | |
| 436 a INT, | |
| 437 b TEXT, | |
| 438 c TEXT, | |
| 439 d TEXT, | |
| 440 e TEXT, | |
| 441 f TEXT, | |
| 442 g INT | |
| 443 )}} | |
| 444 | |
| 445 # Make sure we cannot have duplicate column names within a table. | |
| 446 # | |
| 447 do_test table-9.1 { | |
| 448 catchsql { | |
| 449 CREATE TABLE t6(a,b,a); | |
| 450 } | |
| 451 } {1 {duplicate column name: a}} | |
| 452 do_test table-9.2 { | |
| 453 catchsql { | |
| 454 CREATE TABLE t6(a varchar(100), b blob, a integer); | |
| 455 } | |
| 456 } {1 {duplicate column name: a}} | |
| 457 | |
| 458 # Check the foreign key syntax. | |
| 459 # | |
| 460 ifcapable {foreignkey} { | |
| 461 do_test table-10.1 { | |
| 462 catchsql { | |
| 463 CREATE TABLE t6(a REFERENCES t4(a) NOT NULL); | |
| 464 INSERT INTO t6 VALUES(NULL); | |
| 465 } | |
| 466 } {1 {NOT NULL constraint failed: t6.a}} | |
| 467 do_test table-10.2 { | |
| 468 catchsql { | |
| 469 DROP TABLE t6; | |
| 470 CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL); | |
| 471 } | |
| 472 } {0 {}} | |
| 473 do_test table-10.3 { | |
| 474 catchsql { | |
| 475 DROP TABLE t6; | |
| 476 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL); | |
| 477 } | |
| 478 } {0 {}} | |
| 479 do_test table-10.4 { | |
| 480 catchsql { | |
| 481 DROP TABLE t6; | |
| 482 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1); | |
| 483 } | |
| 484 } {0 {}} | |
| 485 do_test table-10.5 { | |
| 486 catchsql { | |
| 487 DROP TABLE t6; | |
| 488 CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE); | |
| 489 } | |
| 490 } {0 {}} | |
| 491 do_test table-10.6 { | |
| 492 catchsql { | |
| 493 DROP TABLE t6; | |
| 494 CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED); | |
| 495 } | |
| 496 } {0 {}} | |
| 497 do_test table-10.7 { | |
| 498 catchsql { | |
| 499 DROP TABLE t6; | |
| 500 CREATE TABLE t6(a, | |
| 501 FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED | |
| 502 ); | |
| 503 } | |
| 504 } {0 {}} | |
| 505 do_test table-10.8 { | |
| 506 catchsql { | |
| 507 DROP TABLE t6; | |
| 508 CREATE TABLE t6(a,b,c, | |
| 509 FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL | |
| 510 ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED | |
| 511 ); | |
| 512 } | |
| 513 } {0 {}} | |
| 514 do_test table-10.9 { | |
| 515 catchsql { | |
| 516 DROP TABLE t6; | |
| 517 CREATE TABLE t6(a,b,c, | |
| 518 FOREIGN KEY (b,c) REFERENCES t4(x) | |
| 519 ); | |
| 520 } | |
| 521 } {1 {number of columns in foreign key does not match the number of columns in t
he referenced table}} | |
| 522 do_test table-10.10 { | |
| 523 catchsql {DROP TABLE t6} | |
| 524 catchsql { | |
| 525 CREATE TABLE t6(a,b,c, | |
| 526 FOREIGN KEY (b,c) REFERENCES t4(x,y,z) | |
| 527 ); | |
| 528 } | |
| 529 } {1 {number of columns in foreign key does not match the number of columns in t
he referenced table}} | |
| 530 do_test table-10.11 { | |
| 531 catchsql {DROP TABLE t6} | |
| 532 catchsql { | |
| 533 CREATE TABLE t6(a,b, c REFERENCES t4(x,y)); | |
| 534 } | |
| 535 } {1 {foreign key on c should reference only one column of table t4}} | |
| 536 do_test table-10.12 { | |
| 537 catchsql {DROP TABLE t6} | |
| 538 catchsql { | |
| 539 CREATE TABLE t6(a,b,c, | |
| 540 FOREIGN KEY (b,x) REFERENCES t4(x,y) | |
| 541 ); | |
| 542 } | |
| 543 } {1 {unknown column "x" in foreign key definition}} | |
| 544 do_test table-10.13 { | |
| 545 catchsql {DROP TABLE t6} | |
| 546 catchsql { | |
| 547 CREATE TABLE t6(a,b,c, | |
| 548 FOREIGN KEY (x,b) REFERENCES t4(x,y) | |
| 549 ); | |
| 550 } | |
| 551 } {1 {unknown column "x" in foreign key definition}} | |
| 552 } ;# endif foreignkey | |
| 553 | |
| 554 # Test for the "typeof" function. More tests for the | |
| 555 # typeof() function are found in bind.test and types.test. | |
| 556 # | |
| 557 do_test table-11.1 { | |
| 558 execsql { | |
| 559 CREATE TABLE t7( | |
| 560 a integer primary key, | |
| 561 b number(5,10), | |
| 562 c character varying (8), | |
| 563 d VARCHAR(9), | |
| 564 e clob, | |
| 565 f BLOB, | |
| 566 g Text, | |
| 567 h | |
| 568 ); | |
| 569 INSERT INTO t7(a) VALUES(1); | |
| 570 SELECT typeof(a), typeof(b), typeof(c), typeof(d), | |
| 571 typeof(e), typeof(f), typeof(g), typeof(h) | |
| 572 FROM t7 LIMIT 1; | |
| 573 } | |
| 574 } {integer null null null null null null null} | |
| 575 do_test table-11.2 { | |
| 576 execsql { | |
| 577 SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d) | |
| 578 FROM t7 LIMIT 1; | |
| 579 } | |
| 580 } {null null null null} | |
| 581 | |
| 582 # Test that when creating a table using CREATE TABLE AS, column types are | |
| 583 # assigned correctly for (SELECT ...) and 'x AS y' expressions. | |
| 584 do_test table-12.1 { | |
| 585 ifcapable subquery { | |
| 586 execsql { | |
| 587 CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7; | |
| 588 } | |
| 589 } else { | |
| 590 execsql { | |
| 591 CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7; | |
| 592 } | |
| 593 } | |
| 594 } {} | |
| 595 do_test table-12.2 { | |
| 596 execsql { | |
| 597 SELECT sql FROM sqlite_master WHERE tbl_name = 't8' | |
| 598 } | |
| 599 } {{CREATE TABLE t8(b NUM,h,i INT,j)}} | |
| 600 | |
| 601 #-------------------------------------------------------------------- | |
| 602 # Test cases table-13.* | |
| 603 # | |
| 604 # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE | |
| 605 # and CURRENT_TIMESTAMP. | |
| 606 # | |
| 607 do_test table-13.1 { | |
| 608 execsql { | |
| 609 CREATE TABLE tablet8( | |
| 610 a integer primary key, | |
| 611 tm text DEFAULT CURRENT_TIME, | |
| 612 dt text DEFAULT CURRENT_DATE, | |
| 613 dttm text DEFAULT CURRENT_TIMESTAMP | |
| 614 ); | |
| 615 SELECT * FROM tablet8; | |
| 616 } | |
| 617 } {} | |
| 618 set i 0 | |
| 619 unset -nocomplain date time seconds | |
| 620 foreach {date time seconds} { | |
| 621 1976-07-04 12:00:00 205329600 | |
| 622 1994-04-16 14:00:00 766504800 | |
| 623 2000-01-01 00:00:00 946684800 | |
| 624 2003-12-31 12:34:56 1072874096 | |
| 625 } { | |
| 626 incr i | |
| 627 set sqlite_current_time $seconds | |
| 628 do_test table-13.2.$i { | |
| 629 execsql " | |
| 630 INSERT INTO tablet8(a) VALUES($i); | |
| 631 SELECT tm, dt, dttm FROM tablet8 WHERE a=$i; | |
| 632 " | |
| 633 } [list $time $date [list $date $time]] | |
| 634 } | |
| 635 set sqlite_current_time 0 | |
| 636 | |
| 637 #-------------------------------------------------------------------- | |
| 638 # Test cases table-14.* | |
| 639 # | |
| 640 # Test that a table cannot be created or dropped while other virtual | |
| 641 # machines are active. This is required because otherwise when in | |
| 642 # auto-vacuum mode the btree-layer may need to move the root-pages of | |
| 643 # a table for which there is an open cursor. | |
| 644 # | |
| 645 # 2007-05-02: A open btree cursor no longer blocks CREATE TABLE. | |
| 646 # But DROP TABLE is still prohibited because we do not want to | |
| 647 # delete a table out from under a running query. | |
| 648 # | |
| 649 | |
| 650 # db eval { | |
| 651 # pragma vdbe_trace = 0; | |
| 652 # } | |
| 653 # Try to create a table from within a callback: | |
| 654 unset -nocomplain result | |
| 655 do_test table-14.1 { | |
| 656 set rc [ | |
| 657 catch { | |
| 658 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 659 db eval {CREATE TABLE t9(a, b, c)} | |
| 660 } | |
| 661 } msg | |
| 662 ] | |
| 663 set result [list $rc $msg] | |
| 664 } {0 {}} | |
| 665 | |
| 666 # Try to drop a table from within a callback: | |
| 667 do_test table-14.2 { | |
| 668 set rc [ | |
| 669 catch { | |
| 670 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 671 db eval {DROP TABLE t9;} | |
| 672 } | |
| 673 } msg | |
| 674 ] | |
| 675 set result [list $rc $msg] | |
| 676 } {1 {database table is locked}} | |
| 677 | |
| 678 ifcapable attach { | |
| 679 # Now attach a database and ensure that a table can be created in the | |
| 680 # attached database whilst in a callback from a query on the main database. | |
| 681 do_test table-14.3 { | |
| 682 forcedelete test2.db | |
| 683 forcedelete test2.db-journal | |
| 684 execsql { | |
| 685 ATTACH 'test2.db' as aux; | |
| 686 } | |
| 687 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 688 db eval {CREATE TABLE aux.t1(a, b, c)} | |
| 689 } | |
| 690 } {} | |
| 691 | |
| 692 # On the other hand, it should be impossible to drop a table when any VMs | |
| 693 # are active. This is because VerifyCookie instructions may have already | |
| 694 # been executed, and btree root-pages may not move after this (which a | |
| 695 # delete table might do). | |
| 696 do_test table-14.4 { | |
| 697 set rc [ | |
| 698 catch { | |
| 699 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 700 db eval {DROP TABLE aux.t1;} | |
| 701 } | |
| 702 } msg | |
| 703 ] | |
| 704 set result [list $rc $msg] | |
| 705 } {1 {database table is locked}} | |
| 706 } | |
| 707 | |
| 708 # Create and drop 2000 tables. This is to check that the balance_shallow() | |
| 709 # routine works correctly on the sqlite_master table. At one point it | |
| 710 # contained a bug that would prevent the right-child pointer of the | |
| 711 # child page from being copied to the root page. | |
| 712 # | |
| 713 do_test table-15.1 { | |
| 714 execsql {BEGIN} | |
| 715 for {set i 0} {$i<2000} {incr i} { | |
| 716 execsql "CREATE TABLE tbl$i (a, b, c)" | |
| 717 } | |
| 718 execsql {COMMIT} | |
| 719 } {} | |
| 720 do_test table-15.2 { | |
| 721 execsql {BEGIN} | |
| 722 for {set i 0} {$i<2000} {incr i} { | |
| 723 execsql "DROP TABLE tbl$i" | |
| 724 } | |
| 725 execsql {COMMIT} | |
| 726 } {} | |
| 727 | |
| 728 # Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05) | |
| 729 # The following SQL script segfaults while running the INSERT statement: | |
| 730 # | |
| 731 # CREATE TABLE t1(x DEFAULT(max(1))); | |
| 732 # INSERT INTO t1(rowid) VALUES(1); | |
| 733 # | |
| 734 # The problem appears to be the use of an aggregate function as part of | |
| 735 # the default value for a column. This problem has been in the code since | |
| 736 # at least 2006-01-01 and probably before that. This problem was detected | |
| 737 # and reported on the sqlite-users@sqlite.org mailing list by Zsbán Ambrus. | |
| 738 # | |
| 739 do_execsql_test table-16.1 { | |
| 740 CREATE TABLE t16(x DEFAULT(max(1))); | |
| 741 INSERT INTO t16(x) VALUES(123); | |
| 742 SELECT rowid, x FROM t16; | |
| 743 } {1 123} | |
| 744 do_catchsql_test table-16.2 { | |
| 745 INSERT INTO t16(rowid) VALUES(4); | |
| 746 } {1 {unknown function: max()}} | |
| 747 do_execsql_test table-16.3 { | |
| 748 DROP TABLE t16; | |
| 749 CREATE TABLE t16(x DEFAULT(abs(1))); | |
| 750 INSERT INTO t16(rowid) VALUES(4); | |
| 751 SELECT rowid, x FROM t16; | |
| 752 } {4 1} | |
| 753 do_catchsql_test table-16.4 { | |
| 754 DROP TABLE t16; | |
| 755 CREATE TABLE t16(x DEFAULT(avg(1))); | |
| 756 INSERT INTO t16(rowid) VALUES(123); | |
| 757 SELECT rowid, x FROM t16; | |
| 758 } {1 {unknown function: avg()}} | |
| 759 do_catchsql_test table-16.5 { | |
| 760 DROP TABLE t16; | |
| 761 CREATE TABLE t16(x DEFAULT(count())); | |
| 762 INSERT INTO t16(rowid) VALUES(123); | |
| 763 SELECT rowid, x FROM t16; | |
| 764 } {1 {unknown function: count()}} | |
| 765 do_catchsql_test table-16.6 { | |
| 766 DROP TABLE t16; | |
| 767 CREATE TABLE t16(x DEFAULT(group_concat('x',','))); | |
| 768 INSERT INTO t16(rowid) VALUES(123); | |
| 769 SELECT rowid, x FROM t16; | |
| 770 } {1 {unknown function: group_concat()}} | |
| 771 do_catchsql_test table-16.7 { | |
| 772 INSERT INTO t16 DEFAULT VALUES; | |
| 773 } {1 {unknown function: group_concat()}} | |
| 774 | |
| 775 # Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c
63] | |
| 776 # describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT stateme
nt. | |
| 777 # the following test verifies that the problem has been fixed. | |
| 778 # | |
| 779 do_execsql_test table-17.1 { | |
| 780 DROP TABLE IF EXISTS t1; | |
| 781 CREATE TABLE t1(a TEXT); | |
| 782 INSERT INTO t1(a) VALUES(1),(2); | |
| 783 DROP TABLE IF EXISTS t2; | |
| 784 CREATE TABLE t2(x TEXT, y TEXT); | |
| 785 INSERT INTO t2(x,y) VALUES(3,4); | |
| 786 DROP TABLE IF EXISTS t3; | |
| 787 CREATE TABLE t3 AS | |
| 788 SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x; | |
| 789 SELECT p, q, '|' FROM t3 ORDER BY p; | |
| 790 } {1 1 | 2 2 |} | |
| 791 | |
| 792 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
