| 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 do_test table-5.2.2 { | |
| 276 db close | |
| 277 forcedelete test.db | |
| 278 sqlite3 db test.db | |
| 279 db eval { | |
| 280 CREATE TABLE t0(a,b); | |
| 281 CREATE INDEX t ON t0(a); | |
| 282 PRAGMA writable_schema=ON; | |
| 283 UPDATE sqlite_master SET sql='CREATE TABLE a.b(a UNIQUE'; | |
| 284 BEGIN; | |
| 285 CREATE TABLE t1(x); | |
| 286 ROLLBACK; | |
| 287 DROP TABLE IF EXISTS t99; | |
| 288 } | |
| 289 } {} | |
| 290 db close | |
| 291 forcedelete test.db | |
| 292 sqlite3 db test.db | |
| 293 | |
| 294 # Make sure an EXPLAIN does not really create a new table | |
| 295 # | |
| 296 do_test table-5.3 { | |
| 297 ifcapable {explain} { | |
| 298 execsql {EXPLAIN CREATE TABLE test1(f1 int)} | |
| 299 } | |
| 300 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 301 } {} | |
| 302 | |
| 303 # Make sure an EXPLAIN does not really drop an existing table | |
| 304 # | |
| 305 do_test table-5.4 { | |
| 306 execsql {CREATE TABLE test1(f1 int)} | |
| 307 ifcapable {explain} { | |
| 308 execsql {EXPLAIN DROP TABLE test1} | |
| 309 } | |
| 310 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} | |
| 311 } {test1} | |
| 312 | |
| 313 # Create a table with a goofy name | |
| 314 # | |
| 315 #do_test table-6.1 { | |
| 316 # execsql {CREATE TABLE 'Spaces In This Name!'(x int)} | |
| 317 # execsql {INSERT INTO 'spaces in this name!' VALUES(1)} | |
| 318 # set list [glob -nocomplain testdb/spaces*.tbl] | |
| 319 #} {testdb/spaces+in+this+name+.tbl} | |
| 320 | |
| 321 # Try using keywords as table names or column names. | |
| 322 # | |
| 323 do_test table-7.1 { | |
| 324 set v [catch {execsql { | |
| 325 CREATE TABLE weird( | |
| 326 desc text, | |
| 327 asc text, | |
| 328 key int, | |
| 329 [14_vac] boolean, | |
| 330 fuzzy_dog_12 varchar(10), | |
| 331 begin blob, | |
| 332 end clob | |
| 333 ) | |
| 334 }} msg] | |
| 335 lappend v $msg | |
| 336 } {0 {}} | |
| 337 do_test table-7.2 { | |
| 338 execsql { | |
| 339 INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all'); | |
| 340 SELECT * FROM weird; | |
| 341 } | |
| 342 } {a b 9 0 xyz hi y'all} | |
| 343 do_test table-7.3 { | |
| 344 execsql2 { | |
| 345 SELECT * FROM weird; | |
| 346 } | |
| 347 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} | |
| 348 do_test table-7.3 { | |
| 349 execsql { | |
| 350 CREATE TABLE savepoint(release); | |
| 351 INSERT INTO savepoint(release) VALUES(10); | |
| 352 UPDATE savepoint SET release = 5; | |
| 353 SELECT release FROM savepoint; | |
| 354 } | |
| 355 } {5} | |
| 356 | |
| 357 # Try out the CREATE TABLE AS syntax | |
| 358 # | |
| 359 do_test table-8.1 { | |
| 360 execsql2 { | |
| 361 CREATE TABLE t2 AS SELECT * FROM weird; | |
| 362 SELECT * FROM t2; | |
| 363 } | |
| 364 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} | |
| 365 do_test table-8.1.1 { | |
| 366 execsql { | |
| 367 SELECT sql FROM sqlite_master WHERE name='t2'; | |
| 368 } | |
| 369 } {{CREATE TABLE t2( | |
| 370 "desc" TEXT, | |
| 371 "asc" TEXT, | |
| 372 "key" INT, | |
| 373 "14_vac" NUM, | |
| 374 fuzzy_dog_12 TEXT, | |
| 375 "begin", | |
| 376 "end" TEXT | |
| 377 )}} | |
| 378 do_test table-8.2 { | |
| 379 execsql { | |
| 380 CREATE TABLE "t3""xyz"(a,b,c); | |
| 381 INSERT INTO [t3"xyz] VALUES(1,2,3); | |
| 382 SELECT * FROM [t3"xyz]; | |
| 383 } | |
| 384 } {1 2 3} | |
| 385 do_test table-8.3 { | |
| 386 execsql2 { | |
| 387 CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz]; | |
| 388 SELECT * FROM [t4"abc]; | |
| 389 } | |
| 390 } {cnt 1 max(b+c) 5} | |
| 391 | |
| 392 # Update for v3: The declaration type of anything except a column is now a | |
| 393 # NULL pointer, so the created table has no column types. (Changed result | |
| 394 # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}). | |
| 395 do_test table-8.3.1 { | |
| 396 execsql { | |
| 397 SELECT sql FROM sqlite_master WHERE name='t4"abc' | |
| 398 } | |
| 399 } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}} | |
| 400 | |
| 401 ifcapable tempdb { | |
| 402 do_test table-8.4 { | |
| 403 execsql2 { | |
| 404 CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz]; | |
| 405 SELECT * FROM t5; | |
| 406 } | |
| 407 } {y'all 1} | |
| 408 } | |
| 409 | |
| 410 do_test table-8.5 { | |
| 411 db close | |
| 412 sqlite3 db test.db | |
| 413 execsql2 { | |
| 414 SELECT * FROM [t4"abc]; | |
| 415 } | |
| 416 } {cnt 1 max(b+c) 5} | |
| 417 do_test table-8.6 { | |
| 418 execsql2 { | |
| 419 SELECT * FROM t2; | |
| 420 } | |
| 421 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} | |
| 422 do_test table-8.7 { | |
| 423 catchsql { | |
| 424 SELECT * FROM t5; | |
| 425 } | |
| 426 } {1 {no such table: t5}} | |
| 427 do_test table-8.8 { | |
| 428 catchsql { | |
| 429 CREATE TABLE t5 AS SELECT * FROM no_such_table; | |
| 430 } | |
| 431 } {1 {no such table: no_such_table}} | |
| 432 | |
| 433 do_test table-8.9 { | |
| 434 execsql { | |
| 435 CREATE TABLE t10("col.1" [char.3]); | |
| 436 CREATE TABLE t11 AS SELECT * FROM t10; | |
| 437 SELECT sql FROM sqlite_master WHERE name = 't11'; | |
| 438 } | |
| 439 } {{CREATE TABLE t11("col.1" TEXT)}} | |
| 440 do_test table-8.10 { | |
| 441 execsql { | |
| 442 CREATE TABLE t12( | |
| 443 a INTEGER, | |
| 444 b VARCHAR(10), | |
| 445 c VARCHAR(1,10), | |
| 446 d VARCHAR(+1,-10), | |
| 447 e VARCHAR (+1,-10), | |
| 448 f "VARCHAR (+1,-10, 5)", | |
| 449 g BIG INTEGER | |
| 450 ); | |
| 451 CREATE TABLE t13 AS SELECT * FROM t12; | |
| 452 SELECT sql FROM sqlite_master WHERE name = 't13'; | |
| 453 } | |
| 454 } {{CREATE TABLE t13( | |
| 455 a INT, | |
| 456 b TEXT, | |
| 457 c TEXT, | |
| 458 d TEXT, | |
| 459 e TEXT, | |
| 460 f TEXT, | |
| 461 g INT | |
| 462 )}} | |
| 463 | |
| 464 # Make sure we cannot have duplicate column names within a table. | |
| 465 # | |
| 466 do_test table-9.1 { | |
| 467 catchsql { | |
| 468 CREATE TABLE t6(a,b,a); | |
| 469 } | |
| 470 } {1 {duplicate column name: a}} | |
| 471 do_test table-9.2 { | |
| 472 catchsql { | |
| 473 CREATE TABLE t6(a varchar(100), b blob, a integer); | |
| 474 } | |
| 475 } {1 {duplicate column name: a}} | |
| 476 | |
| 477 # Check the foreign key syntax. | |
| 478 # | |
| 479 ifcapable {foreignkey} { | |
| 480 do_test table-10.1 { | |
| 481 catchsql { | |
| 482 CREATE TABLE t6(a REFERENCES t4(a) NOT NULL); | |
| 483 INSERT INTO t6 VALUES(NULL); | |
| 484 } | |
| 485 } {1 {NOT NULL constraint failed: t6.a}} | |
| 486 do_test table-10.2 { | |
| 487 catchsql { | |
| 488 DROP TABLE t6; | |
| 489 CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL); | |
| 490 } | |
| 491 } {0 {}} | |
| 492 do_test table-10.3 { | |
| 493 catchsql { | |
| 494 DROP TABLE t6; | |
| 495 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL); | |
| 496 } | |
| 497 } {0 {}} | |
| 498 do_test table-10.4 { | |
| 499 catchsql { | |
| 500 DROP TABLE t6; | |
| 501 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1); | |
| 502 } | |
| 503 } {0 {}} | |
| 504 do_test table-10.5 { | |
| 505 catchsql { | |
| 506 DROP TABLE t6; | |
| 507 CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE); | |
| 508 } | |
| 509 } {0 {}} | |
| 510 do_test table-10.6 { | |
| 511 catchsql { | |
| 512 DROP TABLE t6; | |
| 513 CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED); | |
| 514 } | |
| 515 } {0 {}} | |
| 516 do_test table-10.7 { | |
| 517 catchsql { | |
| 518 DROP TABLE t6; | |
| 519 CREATE TABLE t6(a, | |
| 520 FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED | |
| 521 ); | |
| 522 } | |
| 523 } {0 {}} | |
| 524 do_test table-10.8 { | |
| 525 catchsql { | |
| 526 DROP TABLE t6; | |
| 527 CREATE TABLE t6(a,b,c, | |
| 528 FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL | |
| 529 ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED | |
| 530 ); | |
| 531 } | |
| 532 } {0 {}} | |
| 533 do_test table-10.9 { | |
| 534 catchsql { | |
| 535 DROP TABLE t6; | |
| 536 CREATE TABLE t6(a,b,c, | |
| 537 FOREIGN KEY (b,c) REFERENCES t4(x) | |
| 538 ); | |
| 539 } | |
| 540 } {1 {number of columns in foreign key does not match the number of columns in t
he referenced table}} | |
| 541 do_test table-10.10 { | |
| 542 catchsql {DROP TABLE t6} | |
| 543 catchsql { | |
| 544 CREATE TABLE t6(a,b,c, | |
| 545 FOREIGN KEY (b,c) REFERENCES t4(x,y,z) | |
| 546 ); | |
| 547 } | |
| 548 } {1 {number of columns in foreign key does not match the number of columns in t
he referenced table}} | |
| 549 do_test table-10.11 { | |
| 550 catchsql {DROP TABLE t6} | |
| 551 catchsql { | |
| 552 CREATE TABLE t6(a,b, c REFERENCES t4(x,y)); | |
| 553 } | |
| 554 } {1 {foreign key on c should reference only one column of table t4}} | |
| 555 do_test table-10.12 { | |
| 556 catchsql {DROP TABLE t6} | |
| 557 catchsql { | |
| 558 CREATE TABLE t6(a,b,c, | |
| 559 FOREIGN KEY (b,x) REFERENCES t4(x,y) | |
| 560 ); | |
| 561 } | |
| 562 } {1 {unknown column "x" in foreign key definition}} | |
| 563 do_test table-10.13 { | |
| 564 catchsql {DROP TABLE t6} | |
| 565 catchsql { | |
| 566 CREATE TABLE t6(a,b,c, | |
| 567 FOREIGN KEY (x,b) REFERENCES t4(x,y) | |
| 568 ); | |
| 569 } | |
| 570 } {1 {unknown column "x" in foreign key definition}} | |
| 571 } ;# endif foreignkey | |
| 572 | |
| 573 # Test for the "typeof" function. More tests for the | |
| 574 # typeof() function are found in bind.test and types.test. | |
| 575 # | |
| 576 do_test table-11.1 { | |
| 577 execsql { | |
| 578 CREATE TABLE t7( | |
| 579 a integer primary key, | |
| 580 b number(5,10), | |
| 581 c character varying (8), | |
| 582 d VARCHAR(9), | |
| 583 e clob, | |
| 584 f BLOB, | |
| 585 g Text, | |
| 586 h | |
| 587 ); | |
| 588 INSERT INTO t7(a) VALUES(1); | |
| 589 SELECT typeof(a), typeof(b), typeof(c), typeof(d), | |
| 590 typeof(e), typeof(f), typeof(g), typeof(h) | |
| 591 FROM t7 LIMIT 1; | |
| 592 } | |
| 593 } {integer null null null null null null null} | |
| 594 do_test table-11.2 { | |
| 595 execsql { | |
| 596 SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d) | |
| 597 FROM t7 LIMIT 1; | |
| 598 } | |
| 599 } {null null null null} | |
| 600 | |
| 601 # Test that when creating a table using CREATE TABLE AS, column types are | |
| 602 # assigned correctly for (SELECT ...) and 'x AS y' expressions. | |
| 603 do_test table-12.1 { | |
| 604 ifcapable subquery { | |
| 605 execsql { | |
| 606 CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7; | |
| 607 } | |
| 608 } else { | |
| 609 execsql { | |
| 610 CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7; | |
| 611 } | |
| 612 } | |
| 613 } {} | |
| 614 do_test table-12.2 { | |
| 615 execsql { | |
| 616 SELECT sql FROM sqlite_master WHERE tbl_name = 't8' | |
| 617 } | |
| 618 } {{CREATE TABLE t8(b NUM,h,i INT,j)}} | |
| 619 | |
| 620 #-------------------------------------------------------------------- | |
| 621 # Test cases table-13.* | |
| 622 # | |
| 623 # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE | |
| 624 # and CURRENT_TIMESTAMP. | |
| 625 # | |
| 626 do_test table-13.1 { | |
| 627 execsql { | |
| 628 CREATE TABLE tablet8( | |
| 629 a integer primary key, | |
| 630 tm text DEFAULT CURRENT_TIME, | |
| 631 dt text DEFAULT CURRENT_DATE, | |
| 632 dttm text DEFAULT CURRENT_TIMESTAMP | |
| 633 ); | |
| 634 SELECT * FROM tablet8; | |
| 635 } | |
| 636 } {} | |
| 637 set i 0 | |
| 638 unset -nocomplain date time seconds | |
| 639 foreach {date time seconds} { | |
| 640 1976-07-04 12:00:00 205329600 | |
| 641 1994-04-16 14:00:00 766504800 | |
| 642 2000-01-01 00:00:00 946684800 | |
| 643 2003-12-31 12:34:56 1072874096 | |
| 644 } { | |
| 645 incr i | |
| 646 set sqlite_current_time $seconds | |
| 647 do_test table-13.2.$i { | |
| 648 execsql " | |
| 649 INSERT INTO tablet8(a) VALUES($i); | |
| 650 SELECT tm, dt, dttm FROM tablet8 WHERE a=$i; | |
| 651 " | |
| 652 } [list $time $date [list $date $time]] | |
| 653 } | |
| 654 set sqlite_current_time 0 | |
| 655 | |
| 656 #-------------------------------------------------------------------- | |
| 657 # Test cases table-14.* | |
| 658 # | |
| 659 # Test that a table cannot be created or dropped while other virtual | |
| 660 # machines are active. This is required because otherwise when in | |
| 661 # auto-vacuum mode the btree-layer may need to move the root-pages of | |
| 662 # a table for which there is an open cursor. | |
| 663 # | |
| 664 # 2007-05-02: A open btree cursor no longer blocks CREATE TABLE. | |
| 665 # But DROP TABLE is still prohibited because we do not want to | |
| 666 # delete a table out from under a running query. | |
| 667 # | |
| 668 | |
| 669 # db eval { | |
| 670 # pragma vdbe_trace = 0; | |
| 671 # } | |
| 672 # Try to create a table from within a callback: | |
| 673 unset -nocomplain result | |
| 674 do_test table-14.1 { | |
| 675 set rc [ | |
| 676 catch { | |
| 677 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 678 db eval {CREATE TABLE t9(a, b, c)} | |
| 679 } | |
| 680 } msg | |
| 681 ] | |
| 682 set result [list $rc $msg] | |
| 683 } {0 {}} | |
| 684 | |
| 685 # Try to drop a table from within a callback: | |
| 686 do_test table-14.2 { | |
| 687 set rc [ | |
| 688 catch { | |
| 689 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 690 db eval {DROP TABLE t9;} | |
| 691 } | |
| 692 } msg | |
| 693 ] | |
| 694 set result [list $rc $msg] | |
| 695 } {1 {database table is locked}} | |
| 696 | |
| 697 ifcapable attach { | |
| 698 # Now attach a database and ensure that a table can be created in the | |
| 699 # attached database whilst in a callback from a query on the main database. | |
| 700 do_test table-14.3 { | |
| 701 forcedelete test2.db | |
| 702 forcedelete test2.db-journal | |
| 703 execsql { | |
| 704 ATTACH 'test2.db' as aux; | |
| 705 } | |
| 706 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 707 db eval {CREATE TABLE aux.t1(a, b, c)} | |
| 708 } | |
| 709 } {} | |
| 710 | |
| 711 # On the other hand, it should be impossible to drop a table when any VMs | |
| 712 # are active. This is because VerifyCookie instructions may have already | |
| 713 # been executed, and btree root-pages may not move after this (which a | |
| 714 # delete table might do). | |
| 715 do_test table-14.4 { | |
| 716 set rc [ | |
| 717 catch { | |
| 718 db eval {SELECT * FROM tablet8 LIMIT 1} {} { | |
| 719 db eval {DROP TABLE aux.t1;} | |
| 720 } | |
| 721 } msg | |
| 722 ] | |
| 723 set result [list $rc $msg] | |
| 724 } {1 {database table is locked}} | |
| 725 } | |
| 726 | |
| 727 # Create and drop 2000 tables. This is to check that the balance_shallow() | |
| 728 # routine works correctly on the sqlite_master table. At one point it | |
| 729 # contained a bug that would prevent the right-child pointer of the | |
| 730 # child page from being copied to the root page. | |
| 731 # | |
| 732 do_test table-15.1 { | |
| 733 execsql {BEGIN} | |
| 734 for {set i 0} {$i<2000} {incr i} { | |
| 735 execsql "CREATE TABLE tbl$i (a, b, c)" | |
| 736 } | |
| 737 execsql {COMMIT} | |
| 738 } {} | |
| 739 do_test table-15.2 { | |
| 740 execsql {BEGIN} | |
| 741 for {set i 0} {$i<2000} {incr i} { | |
| 742 execsql "DROP TABLE tbl$i" | |
| 743 } | |
| 744 execsql {COMMIT} | |
| 745 } {} | |
| 746 | |
| 747 # Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05) | |
| 748 # The following SQL script segfaults while running the INSERT statement: | |
| 749 # | |
| 750 # CREATE TABLE t1(x DEFAULT(max(1))); | |
| 751 # INSERT INTO t1(rowid) VALUES(1); | |
| 752 # | |
| 753 # The problem appears to be the use of an aggregate function as part of | |
| 754 # the default value for a column. This problem has been in the code since | |
| 755 # at least 2006-01-01 and probably before that. This problem was detected | |
| 756 # and reported on the sqlite-users@sqlite.org mailing list by Zsbán Ambrus. | |
| 757 # | |
| 758 do_execsql_test table-16.1 { | |
| 759 CREATE TABLE t16(x DEFAULT(max(1))); | |
| 760 INSERT INTO t16(x) VALUES(123); | |
| 761 SELECT rowid, x FROM t16; | |
| 762 } {1 123} | |
| 763 do_catchsql_test table-16.2 { | |
| 764 INSERT INTO t16(rowid) VALUES(4); | |
| 765 } {1 {unknown function: max()}} | |
| 766 do_execsql_test table-16.3 { | |
| 767 DROP TABLE t16; | |
| 768 CREATE TABLE t16(x DEFAULT(abs(1))); | |
| 769 INSERT INTO t16(rowid) VALUES(4); | |
| 770 SELECT rowid, x FROM t16; | |
| 771 } {4 1} | |
| 772 do_catchsql_test table-16.4 { | |
| 773 DROP TABLE t16; | |
| 774 CREATE TABLE t16(x DEFAULT(avg(1))); | |
| 775 INSERT INTO t16(rowid) VALUES(123); | |
| 776 SELECT rowid, x FROM t16; | |
| 777 } {1 {unknown function: avg()}} | |
| 778 do_catchsql_test table-16.5 { | |
| 779 DROP TABLE t16; | |
| 780 CREATE TABLE t16(x DEFAULT(count())); | |
| 781 INSERT INTO t16(rowid) VALUES(123); | |
| 782 SELECT rowid, x FROM t16; | |
| 783 } {1 {unknown function: count()}} | |
| 784 do_catchsql_test table-16.6 { | |
| 785 DROP TABLE t16; | |
| 786 CREATE TABLE t16(x DEFAULT(group_concat('x',','))); | |
| 787 INSERT INTO t16(rowid) VALUES(123); | |
| 788 SELECT rowid, x FROM t16; | |
| 789 } {1 {unknown function: group_concat()}} | |
| 790 do_catchsql_test table-16.7 { | |
| 791 INSERT INTO t16 DEFAULT VALUES; | |
| 792 } {1 {unknown function: group_concat()}} | |
| 793 | |
| 794 # Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c
63] | |
| 795 # describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT stateme
nt. | |
| 796 # the following test verifies that the problem has been fixed. | |
| 797 # | |
| 798 do_execsql_test table-17.1 { | |
| 799 DROP TABLE IF EXISTS t1; | |
| 800 CREATE TABLE t1(a TEXT); | |
| 801 INSERT INTO t1(a) VALUES(1),(2); | |
| 802 DROP TABLE IF EXISTS t2; | |
| 803 CREATE TABLE t2(x TEXT, y TEXT); | |
| 804 INSERT INTO t2(x,y) VALUES(3,4); | |
| 805 DROP TABLE IF EXISTS t3; | |
| 806 CREATE TABLE t3 AS | |
| 807 SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x; | |
| 808 SELECT p, q, '|' FROM t3 ORDER BY p; | |
| 809 } {1 1 | 2 2 |} | |
| 810 | |
| 811 # 2015-06-16 | |
| 812 # Ticket [https://www.sqlite.org/src/tktview/873cae2b6e25b1991ce5e9b782f9cd0409b
96063] | |
| 813 # Make sure a CREATE TABLE AS statement correctly rolls back partial changes to
the | |
| 814 # sqlite_master table when the SELECT on the right-hand side aborts. | |
| 815 # | |
| 816 do_catchsql_test table-18.1 { | |
| 817 DROP TABLE IF EXISTS t1; | |
| 818 BEGIN; | |
| 819 CREATE TABLE t1 AS SELECT zeroblob(2e20); | |
| 820 } {1 {string or blob too big}} | |
| 821 do_execsql_test table-18.2 { | |
| 822 COMMIT; | |
| 823 PRAGMA integrity_check; | |
| 824 } {ok} | |
| 825 | |
| 826 # 2015-09-09 | |
| 827 # Ticket [https://www.sqlite.org/src/info/acd12990885d9276] | |
| 828 # "CREATE TABLE ... AS SELECT ... FROM sqlite_master" fails because the row | |
| 829 # in the sqlite_master table for the next table is initially populated | |
| 830 # with a NULL instead of a record created by OP_Record. | |
| 831 # | |
| 832 do_execsql_test table-19.1 { | |
| 833 CREATE TABLE t19 AS SELECT * FROM sqlite_master; | |
| 834 SELECT name FROM t19 ORDER BY name; | |
| 835 } {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1
weird} | |
| 836 | |
| 837 | |
| 838 | |
| 839 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
