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