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