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