1# 2# 2001 September 15 3# 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# This file implements regression tests for SQLite library. The 13# focus of this file is testing the CREATE TABLE statement. 14# 15# $Id: table.test,v 1.22 2003/01/29 18:46:54 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Create a basic table and verify it is added to sqlite_master 21# 22do_test table-1.1 { 23 execsql { 24 CREATE TABLE test1 ( 25 one varchar(10), 26 two text 27 ) 28 } 29 execsql { 30 SELECT sql FROM sqlite_master WHERE type!='meta' 31 } 32} {{CREATE TABLE test1 ( 33 one varchar(10), 34 two text 35 )}} 36 37 38# Verify the other fields of the sqlite_master file. 39# 40do_test table-1.3 { 41 execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'} 42} {test1 test1 table} 43 44# Close and reopen the database. Verify that everything is 45# still the same. 46# 47do_test table-1.4 { 48 db close 49 sqlite db test.db 50 execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'} 51} {test1 test1 table} 52 53# Drop the database and make sure it disappears. 54# 55do_test table-1.5 { 56 execsql {DROP TABLE test1} 57 execsql {SELECT * FROM sqlite_master WHERE type!='meta'} 58} {} 59 60# Close and reopen the database. Verify that the table is 61# still gone. 62# 63do_test table-1.6 { 64 db close 65 sqlite db test.db 66 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 67} {} 68 69# Repeat the above steps, but this time quote the table name. 70# 71do_test table-1.10 { 72 execsql {CREATE TABLE "create" (f1 int)} 73 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 74} {create} 75do_test table-1.11 { 76 execsql {DROP TABLE "create"} 77 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} 78} {} 79do_test table-1.12 { 80 execsql {CREATE TABLE test1("f1 ho" int)} 81 execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'} 82} {test1} 83do_test table-1.13 { 84 execsql {DROP TABLE "TEST1"} 85 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} 86} {} 87 88 89 90# Verify that we cannot make two tables with the same name 91# 92do_test table-2.1 { 93 execsql {CREATE TABLE TEST2(one text)} 94 set v [catch {execsql {CREATE TABLE test2(two text)}} msg] 95 lappend v $msg 96} {1 {table test2 already exists}} 97do_test table-2.1b { 98 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] 99 lappend v $msg 100} {1 {table sqlite_master already exists}} 101do_test table-2.1c { 102 db close 103 sqlite db test.db 104 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] 105 lappend v $msg 106} {1 {table sqlite_master already exists}} 107do_test table-2.1d { 108 execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'} 109} {} 110 111# Verify that we cannot make a table with the same name as an index 112# 113do_test table-2.2a { 114 execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)} 115 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 116 lappend v $msg 117} {1 {there is already an index named test3}} 118do_test table-2.2b { 119 db close 120 sqlite db test.db 121 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 122 lappend v $msg 123} {1 {there is already an index named test3}} 124do_test table-2.2c { 125 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 126} {test2 test3} 127do_test table-2.2d { 128 execsql {DROP INDEX test3} 129 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 130 lappend v $msg 131} {0 {}} 132do_test table-2.2e { 133 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 134} {test2 test3} 135do_test table-2.2f { 136 execsql {DROP TABLE test2; DROP TABLE test3} 137 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 138} {} 139 140# Create a table with many field names 141# 142set big_table \ 143{CREATE TABLE big( 144 f1 varchar(20), 145 f2 char(10), 146 f3 varchar(30) primary key, 147 f4 text, 148 f5 text, 149 f6 text, 150 f7 text, 151 f8 text, 152 f9 text, 153 f10 text, 154 f11 text, 155 f12 text, 156 f13 text, 157 f14 text, 158 f15 text, 159 f16 text, 160 f17 text, 161 f18 text, 162 f19 text, 163 f20 text 164)} 165do_test table-3.1 { 166 execsql $big_table 167 execsql {SELECT sql FROM sqlite_master WHERE type=='table'} 168} \{$big_table\} 169do_test table-3.2 { 170 set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg] 171 lappend v $msg 172} {1 {table BIG already exists}} 173do_test table-3.3 { 174 set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg] 175 lappend v $msg 176} {1 {table biG already exists}} 177do_test table-3.4 { 178 set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg] 179 lappend v $msg 180} {1 {table bIg already exists}} 181do_test table-3.5 { 182 db close 183 sqlite db test.db 184 set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg] 185 lappend v $msg 186} {1 {table Big already exists}} 187do_test table-3.6 { 188 execsql {DROP TABLE big} 189 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 190} {} 191 192# Try creating large numbers of tables 193# 194set r {} 195for {set i 1} {$i<=100} {incr i} { 196 lappend r [format test%03d $i] 197} 198do_test table-4.1 { 199 for {set i 1} {$i<=100} {incr i} { 200 set sql "CREATE TABLE [format test%03d $i] (" 201 for {set k 1} {$k<$i} {incr k} { 202 append sql "field$k text," 203 } 204 append sql "last_field text)" 205 execsql $sql 206 } 207 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 208} $r 209do_test table-4.1b { 210 db close 211 sqlite db test.db 212 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 213} $r 214 215# Drop the even numbered tables 216# 217set r {} 218for {set i 1} {$i<=100} {incr i 2} { 219 lappend r [format test%03d $i] 220} 221do_test table-4.2 { 222 for {set i 2} {$i<=100} {incr i 2} { 223 # if {$i==38} {execsql {pragma vdbe_trace=on}} 224 set sql "DROP TABLE [format TEST%03d $i]" 225 execsql $sql 226 } 227 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 228} $r 229#exit 230 231# Drop the odd number tables 232# 233do_test table-4.3 { 234 for {set i 1} {$i<=100} {incr i 2} { 235 set sql "DROP TABLE [format test%03d $i]" 236 execsql $sql 237 } 238 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 239} {} 240 241# Try to drop a table that does not exist 242# 243do_test table-5.1 { 244 set v [catch {execsql {DROP TABLE test009}} msg] 245 lappend v $msg 246} {1 {no such table: test009}} 247 248# Try to drop sqlite_master 249# 250do_test table-5.2 { 251 set v [catch {execsql {DROP TABLE sqlite_master}} msg] 252 lappend v $msg 253} {1 {table sqlite_master may not be dropped}} 254 255# Make sure an EXPLAIN does not really create a new table 256# 257do_test table-5.3 { 258 execsql {EXPLAIN CREATE TABLE test1(f1 int)} 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 execsql {EXPLAIN DROP TABLE test1} 267 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 268} {test1} 269 270# Create a table with a goofy name 271# 272#do_test table-6.1 { 273# execsql {CREATE TABLE 'Spaces In This Name!'(x int)} 274# execsql {INSERT INTO 'spaces in this name!' VALUES(1)} 275# set list [glob -nocomplain testdb/spaces*.tbl] 276#} {testdb/spaces+in+this+name+.tbl} 277 278# Try using keywords as table names or column names. 279# 280do_test table-7.1 { 281 set v [catch {execsql { 282 CREATE TABLE weird( 283 desc text, 284 asc text, 285 explain int, 286 [14_vac] boolean, 287 fuzzy_dog_12 varchar(10), 288 begin blob, 289 end clob 290 ) 291 }} msg] 292 lappend v $msg 293} {0 {}} 294do_test table-7.2 { 295 execsql { 296 INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all'); 297 SELECT * FROM weird; 298 } 299} {a b 9 0 xyz hi y'all} 300do_test table-7.3 { 301 execsql2 { 302 SELECT * FROM weird; 303 } 304} {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} 305 306# Try out the CREATE TABLE AS syntax 307# 308do_test table-8.1 { 309 execsql2 { 310 CREATE TABLE t2 AS SELECT * FROM weird; 311 SELECT * FROM t2; 312 } 313} {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} 314do_test table-8.1.1 { 315 execsql { 316 SELECT sql FROM sqlite_master WHERE name='t2'; 317 } 318} {{CREATE TABLE t2( 319 'desc', 320 'asc', 321 'explain', 322 '14_vac', 323 fuzzy_dog_12, 324 'begin', 325 'end' 326)}} 327do_test table-8.2 { 328 execsql { 329 CREATE TABLE 't3''xyz'(a,b,c); 330 INSERT INTO [t3'xyz] VALUES(1,2,3); 331 SELECT * FROM [t3'xyz]; 332 } 333} {1 2 3} 334do_test table-8.3 { 335 execsql2 { 336 CREATE TABLE [t4'abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3'xyz]; 337 SELECT * FROM [t4'abc]; 338 } 339} {cnt 1 max(b+c) 5} 340do_test table-8.3.1 { 341 execsql { 342 SELECT sql FROM sqlite_master WHERE name='t4''abc' 343 } 344} {{CREATE TABLE 't4''abc'(cnt,'max(b+c)')}} 345do_test table-8.4 { 346 execsql2 { 347 CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz]; 348 SELECT * FROM t5; 349 } 350} {y'all 1} 351do_test table-8.5 { 352 db close 353 sqlite db test.db 354 execsql2 { 355 SELECT * FROM [t4'abc]; 356 } 357} {cnt 1 max(b+c) 5} 358do_test table-8.6 { 359 execsql2 { 360 SELECT * FROM t2; 361 } 362} {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} 363do_test table-8.7 { 364 catchsql { 365 SELECT * FROM t5; 366 } 367} {1 {no such table: t5}} 368do_test table-8.8 { 369 catchsql { 370 CREATE TABLE t5 AS SELECT * FROM no_such_table; 371 } 372} {1 {no such table: no_such_table}} 373 374# Make sure we cannot have duplicate column names within a table. 375# 376do_test table-9.1 { 377 catchsql { 378 CREATE TABLE t6(a,b,a); 379 } 380} {1 {duplicate column name: a}} 381 382# Check the foreign key syntax. 383# 384do_test table-10.1 { 385 catchsql { 386 CREATE TABLE t6(a REFERENCES t4(a) NOT NULL); 387 INSERT INTO t6 VALUES(NULL); 388 } 389} {1 {t6.a may not be NULL}} 390do_test table-10.2 { 391 catchsql { 392 DROP TABLE t6; 393 CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL); 394 } 395} {0 {}} 396do_test table-10.3 { 397 catchsql { 398 DROP TABLE t6; 399 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL); 400 } 401} {0 {}} 402do_test table-10.4 { 403 catchsql { 404 DROP TABLE t6; 405 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1); 406 } 407} {0 {}} 408do_test table-10.5 { 409 catchsql { 410 DROP TABLE t6; 411 CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE); 412 } 413} {0 {}} 414do_test table-10.6 { 415 catchsql { 416 DROP TABLE t6; 417 CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED); 418 } 419} {0 {}} 420do_test table-10.7 { 421 catchsql { 422 DROP TABLE t6; 423 CREATE TABLE t6(a, 424 FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED 425 ); 426 } 427} {0 {}} 428do_test table-10.8 { 429 catchsql { 430 DROP TABLE t6; 431 CREATE TABLE t6(a,b,c, 432 FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL 433 ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED 434 ); 435 } 436} {0 {}} 437do_test table-10.9 { 438 catchsql { 439 DROP TABLE t6; 440 CREATE TABLE t6(a,b,c, 441 FOREIGN KEY (b,c) REFERENCES t4(x) 442 ); 443 } 444} {1 {number of columns in foreign key does not match the number of columns in the referenced table}} 445do_test table-10.10 { 446 catchsql {DROP TABLE t6} 447 catchsql { 448 CREATE TABLE t6(a,b,c, 449 FOREIGN KEY (b,c) REFERENCES t4(x,y,z) 450 ); 451 } 452} {1 {number of columns in foreign key does not match the number of columns in the referenced table}} 453do_test table-10.11 { 454 catchsql {DROP TABLE t6} 455 catchsql { 456 CREATE TABLE t6(a,b, c REFERENCES t4(x,y)); 457 } 458} {1 {foreign key on c should reference only one column of table t4}} 459do_test table-10.12 { 460 catchsql {DROP TABLE t6} 461 catchsql { 462 CREATE TABLE t6(a,b,c, 463 FOREIGN KEY (b,x) REFERENCES t4(x,y) 464 ); 465 } 466} {1 {unknown column "x" in foreign key definition}} 467do_test table-10.13 { 468 catchsql {DROP TABLE t6} 469 catchsql { 470 CREATE TABLE t6(a,b,c, 471 FOREIGN KEY (x,b) REFERENCES t4(x,y) 472 ); 473 } 474} {1 {unknown column "x" in foreign key definition}} 475 476 477# Test for the "typeof" function. 478# 479do_test table-11.1 { 480 execsql { 481 CREATE TABLE t7( 482 a integer primary key, 483 b number(5,10), 484 c character varying (8), 485 d VARCHAR(9), 486 e clob, 487 f BLOB, 488 g Text, 489 h 490 ); 491 INSERT INTO t7(a) VALUES(1); 492 SELECT typeof(a), typeof(b), typeof(c), typeof(d), 493 typeof(e), typeof(f), typeof(g), typeof(h) 494 FROM t7 LIMIT 1; 495 } 496} {numeric numeric text text text text text numeric} 497do_test table-11.2 { 498 execsql { 499 SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d) 500 FROM t7 LIMIT 1; 501 } 502} {numeric text numeric text} 503 504finish_test 505