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