Annotation of embedaddon/sqlite3/test/table.test, revision 1.1
1.1 ! misho 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:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # Create a basic table and verify it is added to sqlite_master
! 20: #
! 21: do_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: #
! 39: do_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: #
! 46: do_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: #
! 54: do_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: #
! 62: do_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: #
! 70: do_test table-1.10 {
! 71: execsql {CREATE TABLE "create" (f1 int)}
! 72: execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
! 73: } {create}
! 74: do_test table-1.11 {
! 75: execsql {DROP TABLE "create"}
! 76: execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
! 77: } {}
! 78: do_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}
! 82: do_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: #
! 91: do_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}}
! 95: do_test table-2.1.1 {
! 96: catchsql {CREATE TABLE "test2" (two)}
! 97: } {1 {table "test2" already exists}}
! 98: do_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}}
! 102: do_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}}
! 108: do_test table-2.1d {
! 109: catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
! 110: } {0 {}}
! 111: do_test table-2.1e {
! 112: catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
! 113: } {0 {}}
! 114: do_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: #
! 120: do_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}}
! 125: do_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}}
! 131: do_test table-2.2c {
! 132: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 133: } {test2 test3}
! 134: do_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 {}}
! 139: do_test table-2.2e {
! 140: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 141: } {test2 test3}
! 142: do_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: #
! 149: set 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: )}
! 172: do_test table-3.1 {
! 173: execsql $big_table
! 174: execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
! 175: } \{$big_table\}
! 176: do_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}}
! 180: do_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}}
! 184: do_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}}
! 188: do_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}}
! 194: do_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: #
! 201: set r {}
! 202: for {set i 1} {$i<=100} {incr i} {
! 203: lappend r [format test%03d $i]
! 204: }
! 205: do_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
! 216: do_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: #
! 224: set r {}
! 225: for {set i 1} {$i<=100} {incr i 2} {
! 226: lappend r [format test%03d $i]
! 227: }
! 228: do_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: #
! 240: do_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: #
! 250: do_test table-5.1.1 {
! 251: catchsql {DROP TABLE test009}
! 252: } {1 {no such table: test009}}
! 253: do_test table-5.1.2 {
! 254: catchsql {DROP TABLE IF EXISTS test009}
! 255: } {0 {}}
! 256:
! 257: # Try to drop sqlite_master
! 258: #
! 259: do_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: #
! 265: do_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: SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*';
! 272: }
! 273: } {}
! 274:
! 275: # Make sure an EXPLAIN does not really create a new table
! 276: #
! 277: do_test table-5.3 {
! 278: ifcapable {explain} {
! 279: execsql {EXPLAIN CREATE TABLE test1(f1 int)}
! 280: }
! 281: execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
! 282: } {}
! 283:
! 284: # Make sure an EXPLAIN does not really drop an existing table
! 285: #
! 286: do_test table-5.4 {
! 287: execsql {CREATE TABLE test1(f1 int)}
! 288: ifcapable {explain} {
! 289: execsql {EXPLAIN DROP TABLE test1}
! 290: }
! 291: execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
! 292: } {test1}
! 293:
! 294: # Create a table with a goofy name
! 295: #
! 296: #do_test table-6.1 {
! 297: # execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
! 298: # execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
! 299: # set list [glob -nocomplain testdb/spaces*.tbl]
! 300: #} {testdb/spaces+in+this+name+.tbl}
! 301:
! 302: # Try using keywords as table names or column names.
! 303: #
! 304: do_test table-7.1 {
! 305: set v [catch {execsql {
! 306: CREATE TABLE weird(
! 307: desc text,
! 308: asc text,
! 309: key int,
! 310: [14_vac] boolean,
! 311: fuzzy_dog_12 varchar(10),
! 312: begin blob,
! 313: end clob
! 314: )
! 315: }} msg]
! 316: lappend v $msg
! 317: } {0 {}}
! 318: do_test table-7.2 {
! 319: execsql {
! 320: INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
! 321: SELECT * FROM weird;
! 322: }
! 323: } {a b 9 0 xyz hi y'all}
! 324: do_test table-7.3 {
! 325: execsql2 {
! 326: SELECT * FROM weird;
! 327: }
! 328: } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
! 329: do_test table-7.3 {
! 330: execsql {
! 331: CREATE TABLE savepoint(release);
! 332: INSERT INTO savepoint(release) VALUES(10);
! 333: UPDATE savepoint SET release = 5;
! 334: SELECT release FROM savepoint;
! 335: }
! 336: } {5}
! 337:
! 338: # Try out the CREATE TABLE AS syntax
! 339: #
! 340: do_test table-8.1 {
! 341: execsql2 {
! 342: CREATE TABLE t2 AS SELECT * FROM weird;
! 343: SELECT * FROM t2;
! 344: }
! 345: } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
! 346: do_test table-8.1.1 {
! 347: execsql {
! 348: SELECT sql FROM sqlite_master WHERE name='t2';
! 349: }
! 350: } {{CREATE TABLE t2(
! 351: "desc" TEXT,
! 352: "asc" TEXT,
! 353: "key" INT,
! 354: "14_vac" NUM,
! 355: fuzzy_dog_12 TEXT,
! 356: "begin",
! 357: "end" TEXT
! 358: )}}
! 359: do_test table-8.2 {
! 360: execsql {
! 361: CREATE TABLE "t3""xyz"(a,b,c);
! 362: INSERT INTO [t3"xyz] VALUES(1,2,3);
! 363: SELECT * FROM [t3"xyz];
! 364: }
! 365: } {1 2 3}
! 366: do_test table-8.3 {
! 367: execsql2 {
! 368: CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
! 369: SELECT * FROM [t4"abc];
! 370: }
! 371: } {cnt 1 max(b+c) 5}
! 372:
! 373: # Update for v3: The declaration type of anything except a column is now a
! 374: # NULL pointer, so the created table has no column types. (Changed result
! 375: # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
! 376: do_test table-8.3.1 {
! 377: execsql {
! 378: SELECT sql FROM sqlite_master WHERE name='t4"abc'
! 379: }
! 380: } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
! 381:
! 382: ifcapable tempdb {
! 383: do_test table-8.4 {
! 384: execsql2 {
! 385: CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
! 386: SELECT * FROM t5;
! 387: }
! 388: } {y'all 1}
! 389: }
! 390:
! 391: do_test table-8.5 {
! 392: db close
! 393: sqlite3 db test.db
! 394: execsql2 {
! 395: SELECT * FROM [t4"abc];
! 396: }
! 397: } {cnt 1 max(b+c) 5}
! 398: do_test table-8.6 {
! 399: execsql2 {
! 400: SELECT * FROM t2;
! 401: }
! 402: } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
! 403: do_test table-8.7 {
! 404: catchsql {
! 405: SELECT * FROM t5;
! 406: }
! 407: } {1 {no such table: t5}}
! 408: do_test table-8.8 {
! 409: catchsql {
! 410: CREATE TABLE t5 AS SELECT * FROM no_such_table;
! 411: }
! 412: } {1 {no such table: no_such_table}}
! 413:
! 414: do_test table-8.9 {
! 415: execsql {
! 416: CREATE TABLE t10("col.1" [char.3]);
! 417: CREATE TABLE t11 AS SELECT * FROM t10;
! 418: SELECT sql FROM sqlite_master WHERE name = 't11';
! 419: }
! 420: } {{CREATE TABLE t11("col.1" TEXT)}}
! 421: do_test table-8.10 {
! 422: execsql {
! 423: CREATE TABLE t12(
! 424: a INTEGER,
! 425: b VARCHAR(10),
! 426: c VARCHAR(1,10),
! 427: d VARCHAR(+1,-10),
! 428: e VARCHAR (+1,-10),
! 429: f "VARCHAR (+1,-10, 5)",
! 430: g BIG INTEGER
! 431: );
! 432: CREATE TABLE t13 AS SELECT * FROM t12;
! 433: SELECT sql FROM sqlite_master WHERE name = 't13';
! 434: }
! 435: } {{CREATE TABLE t13(
! 436: a INT,
! 437: b TEXT,
! 438: c TEXT,
! 439: d TEXT,
! 440: e TEXT,
! 441: f TEXT,
! 442: g INT
! 443: )}}
! 444:
! 445: # Make sure we cannot have duplicate column names within a table.
! 446: #
! 447: do_test table-9.1 {
! 448: catchsql {
! 449: CREATE TABLE t6(a,b,a);
! 450: }
! 451: } {1 {duplicate column name: a}}
! 452: do_test table-9.2 {
! 453: catchsql {
! 454: CREATE TABLE t6(a varchar(100), b blob, a integer);
! 455: }
! 456: } {1 {duplicate column name: a}}
! 457:
! 458: # Check the foreign key syntax.
! 459: #
! 460: ifcapable {foreignkey} {
! 461: do_test table-10.1 {
! 462: catchsql {
! 463: CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
! 464: INSERT INTO t6 VALUES(NULL);
! 465: }
! 466: } {1 {t6.a may not be NULL}}
! 467: do_test table-10.2 {
! 468: catchsql {
! 469: DROP TABLE t6;
! 470: CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
! 471: }
! 472: } {0 {}}
! 473: do_test table-10.3 {
! 474: catchsql {
! 475: DROP TABLE t6;
! 476: CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
! 477: }
! 478: } {0 {}}
! 479: do_test table-10.4 {
! 480: catchsql {
! 481: DROP TABLE t6;
! 482: CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
! 483: }
! 484: } {0 {}}
! 485: do_test table-10.5 {
! 486: catchsql {
! 487: DROP TABLE t6;
! 488: CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
! 489: }
! 490: } {0 {}}
! 491: do_test table-10.6 {
! 492: catchsql {
! 493: DROP TABLE t6;
! 494: CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
! 495: }
! 496: } {0 {}}
! 497: do_test table-10.7 {
! 498: catchsql {
! 499: DROP TABLE t6;
! 500: CREATE TABLE t6(a,
! 501: FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
! 502: );
! 503: }
! 504: } {0 {}}
! 505: do_test table-10.8 {
! 506: catchsql {
! 507: DROP TABLE t6;
! 508: CREATE TABLE t6(a,b,c,
! 509: FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
! 510: ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
! 511: );
! 512: }
! 513: } {0 {}}
! 514: do_test table-10.9 {
! 515: catchsql {
! 516: DROP TABLE t6;
! 517: CREATE TABLE t6(a,b,c,
! 518: FOREIGN KEY (b,c) REFERENCES t4(x)
! 519: );
! 520: }
! 521: } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
! 522: do_test table-10.10 {
! 523: catchsql {DROP TABLE t6}
! 524: catchsql {
! 525: CREATE TABLE t6(a,b,c,
! 526: FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
! 527: );
! 528: }
! 529: } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
! 530: do_test table-10.11 {
! 531: catchsql {DROP TABLE t6}
! 532: catchsql {
! 533: CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
! 534: }
! 535: } {1 {foreign key on c should reference only one column of table t4}}
! 536: do_test table-10.12 {
! 537: catchsql {DROP TABLE t6}
! 538: catchsql {
! 539: CREATE TABLE t6(a,b,c,
! 540: FOREIGN KEY (b,x) REFERENCES t4(x,y)
! 541: );
! 542: }
! 543: } {1 {unknown column "x" in foreign key definition}}
! 544: do_test table-10.13 {
! 545: catchsql {DROP TABLE t6}
! 546: catchsql {
! 547: CREATE TABLE t6(a,b,c,
! 548: FOREIGN KEY (x,b) REFERENCES t4(x,y)
! 549: );
! 550: }
! 551: } {1 {unknown column "x" in foreign key definition}}
! 552: } ;# endif foreignkey
! 553:
! 554: # Test for the "typeof" function. More tests for the
! 555: # typeof() function are found in bind.test and types.test.
! 556: #
! 557: do_test table-11.1 {
! 558: execsql {
! 559: CREATE TABLE t7(
! 560: a integer primary key,
! 561: b number(5,10),
! 562: c character varying (8),
! 563: d VARCHAR(9),
! 564: e clob,
! 565: f BLOB,
! 566: g Text,
! 567: h
! 568: );
! 569: INSERT INTO t7(a) VALUES(1);
! 570: SELECT typeof(a), typeof(b), typeof(c), typeof(d),
! 571: typeof(e), typeof(f), typeof(g), typeof(h)
! 572: FROM t7 LIMIT 1;
! 573: }
! 574: } {integer null null null null null null null}
! 575: do_test table-11.2 {
! 576: execsql {
! 577: SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
! 578: FROM t7 LIMIT 1;
! 579: }
! 580: } {null null null null}
! 581:
! 582: # Test that when creating a table using CREATE TABLE AS, column types are
! 583: # assigned correctly for (SELECT ...) and 'x AS y' expressions.
! 584: do_test table-12.1 {
! 585: ifcapable subquery {
! 586: execsql {
! 587: CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
! 588: }
! 589: } else {
! 590: execsql {
! 591: CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
! 592: }
! 593: }
! 594: } {}
! 595: do_test table-12.2 {
! 596: execsql {
! 597: SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
! 598: }
! 599: } {{CREATE TABLE t8(b NUM,h,i INT,j)}}
! 600:
! 601: #--------------------------------------------------------------------
! 602: # Test cases table-13.*
! 603: #
! 604: # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
! 605: # and CURRENT_TIMESTAMP.
! 606: #
! 607: do_test table-13.1 {
! 608: execsql {
! 609: CREATE TABLE tablet8(
! 610: a integer primary key,
! 611: tm text DEFAULT CURRENT_TIME,
! 612: dt text DEFAULT CURRENT_DATE,
! 613: dttm text DEFAULT CURRENT_TIMESTAMP
! 614: );
! 615: SELECT * FROM tablet8;
! 616: }
! 617: } {}
! 618: set i 0
! 619: unset -nocomplain date time seconds
! 620: foreach {date time seconds} {
! 621: 1976-07-04 12:00:00 205329600
! 622: 1994-04-16 14:00:00 766504800
! 623: 2000-01-01 00:00:00 946684800
! 624: 2003-12-31 12:34:56 1072874096
! 625: } {
! 626: incr i
! 627: set sqlite_current_time $seconds
! 628: do_test table-13.2.$i {
! 629: execsql "
! 630: INSERT INTO tablet8(a) VALUES($i);
! 631: SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
! 632: "
! 633: } [list $time $date [list $date $time]]
! 634: }
! 635: set sqlite_current_time 0
! 636:
! 637: #--------------------------------------------------------------------
! 638: # Test cases table-14.*
! 639: #
! 640: # Test that a table cannot be created or dropped while other virtual
! 641: # machines are active. This is required because otherwise when in
! 642: # auto-vacuum mode the btree-layer may need to move the root-pages of
! 643: # a table for which there is an open cursor.
! 644: #
! 645: # 2007-05-02: A open btree cursor no longer blocks CREATE TABLE.
! 646: # But DROP TABLE is still prohibited because we do not want to
! 647: # delete a table out from under a running query.
! 648: #
! 649:
! 650: # db eval {
! 651: # pragma vdbe_trace = 0;
! 652: # }
! 653: # Try to create a table from within a callback:
! 654: unset -nocomplain result
! 655: do_test table-14.1 {
! 656: set rc [
! 657: catch {
! 658: db eval {SELECT * FROM tablet8 LIMIT 1} {} {
! 659: db eval {CREATE TABLE t9(a, b, c)}
! 660: }
! 661: } msg
! 662: ]
! 663: set result [list $rc $msg]
! 664: } {0 {}}
! 665:
! 666: # Try to drop a table from within a callback:
! 667: do_test table-14.2 {
! 668: set rc [
! 669: catch {
! 670: db eval {SELECT * FROM tablet8 LIMIT 1} {} {
! 671: db eval {DROP TABLE t9;}
! 672: }
! 673: } msg
! 674: ]
! 675: set result [list $rc $msg]
! 676: } {1 {database table is locked}}
! 677:
! 678: ifcapable attach {
! 679: # Now attach a database and ensure that a table can be created in the
! 680: # attached database whilst in a callback from a query on the main database.
! 681: do_test table-14.3 {
! 682: forcedelete test2.db
! 683: forcedelete test2.db-journal
! 684: execsql {
! 685: ATTACH 'test2.db' as aux;
! 686: }
! 687: db eval {SELECT * FROM tablet8 LIMIT 1} {} {
! 688: db eval {CREATE TABLE aux.t1(a, b, c)}
! 689: }
! 690: } {}
! 691:
! 692: # On the other hand, it should be impossible to drop a table when any VMs
! 693: # are active. This is because VerifyCookie instructions may have already
! 694: # been executed, and btree root-pages may not move after this (which a
! 695: # delete table might do).
! 696: do_test table-14.4 {
! 697: set rc [
! 698: catch {
! 699: db eval {SELECT * FROM tablet8 LIMIT 1} {} {
! 700: db eval {DROP TABLE aux.t1;}
! 701: }
! 702: } msg
! 703: ]
! 704: set result [list $rc $msg]
! 705: } {1 {database table is locked}}
! 706: }
! 707:
! 708: # Create and drop 2000 tables. This is to check that the balance_shallow()
! 709: # routine works correctly on the sqlite_master table. At one point it
! 710: # contained a bug that would prevent the right-child pointer of the
! 711: # child page from being copied to the root page.
! 712: #
! 713: do_test table-15.1 {
! 714: execsql {BEGIN}
! 715: for {set i 0} {$i<2000} {incr i} {
! 716: execsql "CREATE TABLE tbl$i (a, b, c)"
! 717: }
! 718: execsql {COMMIT}
! 719: } {}
! 720: do_test table-15.2 {
! 721: execsql {BEGIN}
! 722: for {set i 0} {$i<2000} {incr i} {
! 723: execsql "DROP TABLE tbl$i"
! 724: }
! 725: execsql {COMMIT}
! 726: } {}
! 727:
! 728: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to table.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test