sl@0: # 2001 September 15
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is testing the CREATE TABLE statement.
sl@0: #
sl@0: # $Id: table.test,v 1.48 2007/10/09 08:29:33 danielk1977 Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # Create a basic table and verify it is added to sqlite_master
sl@0: #
sl@0: do_test table-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE test1 (
sl@0:       one varchar(10),
sl@0:       two text
sl@0:     )
sl@0:   }
sl@0:   execsql {
sl@0:     SELECT sql FROM sqlite_master WHERE type!='meta'
sl@0:   }
sl@0: } {{CREATE TABLE test1 (
sl@0:       one varchar(10),
sl@0:       two text
sl@0:     )}}
sl@0: 
sl@0: 
sl@0: # Verify the other fields of the sqlite_master file.
sl@0: #
sl@0: do_test table-1.3 {
sl@0:   execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
sl@0: } {test1 test1 table}
sl@0: 
sl@0: # Close and reopen the database.  Verify that everything is
sl@0: # still the same.
sl@0: #
sl@0: do_test table-1.4 {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
sl@0: } {test1 test1 table}
sl@0: 
sl@0: # Drop the database and make sure it disappears.
sl@0: #
sl@0: do_test table-1.5 {
sl@0:   execsql {DROP TABLE test1}
sl@0:   execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
sl@0: } {}
sl@0: 
sl@0: # Close and reopen the database.  Verify that the table is
sl@0: # still gone.
sl@0: #
sl@0: do_test table-1.6 {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
sl@0: } {}
sl@0: 
sl@0: # Repeat the above steps, but this time quote the table name.
sl@0: #
sl@0: do_test table-1.10 {
sl@0:   execsql {CREATE TABLE "create" (f1 int)}
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
sl@0: } {create}
sl@0: do_test table-1.11 {
sl@0:   execsql {DROP TABLE "create"}
sl@0:   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
sl@0: } {}
sl@0: do_test table-1.12 {
sl@0:   execsql {CREATE TABLE test1("f1 ho" int)}
sl@0:   execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
sl@0: } {test1}
sl@0: do_test table-1.13 {
sl@0:   execsql {DROP TABLE "TEST1"}
sl@0:   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
sl@0: } {}
sl@0: 
sl@0: 
sl@0: 
sl@0: # Verify that we cannot make two tables with the same name
sl@0: #
sl@0: do_test table-2.1 {
sl@0:   execsql {CREATE TABLE TEST2(one text)}
sl@0:   catchsql {CREATE TABLE test2(two text default 'hi')}
sl@0: } {1 {table test2 already exists}}
sl@0: do_test table-2.1.1 {
sl@0:   catchsql {CREATE TABLE "test2" (two)}
sl@0: } {1 {table "test2" already exists}}
sl@0: do_test table-2.1b {
sl@0:   set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {object name reserved for internal use: sqlite_master}}
sl@0: do_test table-2.1c {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {object name reserved for internal use: sqlite_master}}
sl@0: do_test table-2.1d {
sl@0:   catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
sl@0: } {0 {}}
sl@0: do_test table-2.1e {
sl@0:   catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
sl@0: } {0 {}}
sl@0: do_test table-2.1f {
sl@0:   execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
sl@0: } {}
sl@0: 
sl@0: # Verify that we cannot make a table with the same name as an index
sl@0: #
sl@0: do_test table-2.2a {
sl@0:   execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
sl@0:   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {there is already an index named test3}}
sl@0: do_test table-2.2b {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {there is already an index named test3}}
sl@0: do_test table-2.2c {
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } {test2 test3}
sl@0: do_test table-2.2d {
sl@0:   execsql {DROP INDEX test3}
sl@0:   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
sl@0:   lappend v $msg
sl@0: } {0 {}}
sl@0: do_test table-2.2e {
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } {test2 test3}
sl@0: do_test table-2.2f {
sl@0:   execsql {DROP TABLE test2; DROP TABLE test3}
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } {}
sl@0: 
sl@0: # Create a table with many field names
sl@0: #
sl@0: set big_table \
sl@0: {CREATE TABLE big(
sl@0:   f1 varchar(20),
sl@0:   f2 char(10),
sl@0:   f3 varchar(30) primary key,
sl@0:   f4 text,
sl@0:   f5 text,
sl@0:   f6 text,
sl@0:   f7 text,
sl@0:   f8 text,
sl@0:   f9 text,
sl@0:   f10 text,
sl@0:   f11 text,
sl@0:   f12 text,
sl@0:   f13 text,
sl@0:   f14 text,
sl@0:   f15 text,
sl@0:   f16 text,
sl@0:   f17 text,
sl@0:   f18 text,
sl@0:   f19 text,
sl@0:   f20 text
sl@0: )}
sl@0: do_test table-3.1 {
sl@0:   execsql $big_table
sl@0:   execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
sl@0: } \{$big_table\}
sl@0: do_test table-3.2 {
sl@0:   set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {table BIG already exists}}
sl@0: do_test table-3.3 {
sl@0:   set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {table biG already exists}}
sl@0: do_test table-3.4 {
sl@0:   set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {table bIg already exists}}
sl@0: do_test table-3.5 {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {table Big already exists}}
sl@0: do_test table-3.6 {
sl@0:   execsql {DROP TABLE big}
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
sl@0: } {}
sl@0: 
sl@0: # Try creating large numbers of tables
sl@0: #
sl@0: set r {}
sl@0: for {set i 1} {$i<=100} {incr i} {
sl@0:   lappend r [format test%03d $i]
sl@0: }
sl@0: do_test table-4.1 {
sl@0:   for {set i 1} {$i<=100} {incr i} {
sl@0:     set sql "CREATE TABLE [format test%03d $i] ("
sl@0:     for {set k 1} {$k<$i} {incr k} {
sl@0:       append sql "field$k text,"
sl@0:     }
sl@0:     append sql "last_field text)"
sl@0:     execsql $sql
sl@0:   }
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } $r
sl@0: do_test table-4.1b {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } $r
sl@0: 
sl@0: # Drop the even numbered tables
sl@0: #
sl@0: set r {}
sl@0: for {set i 1} {$i<=100} {incr i 2} {
sl@0:   lappend r [format test%03d $i]
sl@0: }
sl@0: do_test table-4.2 {
sl@0:   for {set i 2} {$i<=100} {incr i 2} {
sl@0:     # if {$i==38} {execsql {pragma vdbe_trace=on}}
sl@0:     set sql "DROP TABLE [format TEST%03d $i]"
sl@0:     execsql $sql
sl@0:   }
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } $r
sl@0: #exit
sl@0: 
sl@0: # Drop the odd number tables
sl@0: #
sl@0: do_test table-4.3 {
sl@0:   for {set i 1} {$i<=100} {incr i 2} {
sl@0:     set sql "DROP TABLE [format test%03d $i]"
sl@0:     execsql $sql
sl@0:   }
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
sl@0: } {}
sl@0: 
sl@0: # Try to drop a table that does not exist
sl@0: #
sl@0: do_test table-5.1.1 {
sl@0:   catchsql {DROP TABLE test009}
sl@0: } {1 {no such table: test009}}
sl@0: do_test table-5.1.2 {
sl@0:   catchsql {DROP TABLE IF EXISTS test009}
sl@0: } {0 {}}
sl@0: 
sl@0: # Try to drop sqlite_master
sl@0: #
sl@0: do_test table-5.2 {
sl@0:   catchsql {DROP TABLE IF EXISTS sqlite_master}
sl@0: } {1 {table sqlite_master may not be dropped}}
sl@0: 
sl@0: # Make sure an EXPLAIN does not really create a new table
sl@0: #
sl@0: do_test table-5.3 {
sl@0:   ifcapable {explain} {
sl@0:     execsql {EXPLAIN CREATE TABLE test1(f1 int)}
sl@0:   }
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
sl@0: } {}
sl@0: 
sl@0: # Make sure an EXPLAIN does not really drop an existing table
sl@0: #
sl@0: do_test table-5.4 {
sl@0:   execsql {CREATE TABLE test1(f1 int)}
sl@0:   ifcapable {explain} {
sl@0:     execsql {EXPLAIN DROP TABLE test1}
sl@0:   }
sl@0:   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
sl@0: } {test1}
sl@0: 
sl@0: # Create a table with a goofy name
sl@0: #
sl@0: #do_test table-6.1 {
sl@0: #  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
sl@0: #  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
sl@0: #  set list [glob -nocomplain testdb/spaces*.tbl]
sl@0: #} {testdb/spaces+in+this+name+.tbl}
sl@0: 
sl@0: # Try using keywords as table names or column names.
sl@0: # 
sl@0: do_test table-7.1 {
sl@0:   set v [catch {execsql {
sl@0:     CREATE TABLE weird(
sl@0:       desc text,
sl@0:       asc text,
sl@0:       key int,
sl@0:       [14_vac] boolean,
sl@0:       fuzzy_dog_12 varchar(10),
sl@0:       begin blob,
sl@0:       end clob
sl@0:     )
sl@0:   }} msg]
sl@0:   lappend v $msg
sl@0: } {0 {}}
sl@0: do_test table-7.2 {
sl@0:   execsql {
sl@0:     INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
sl@0:     SELECT * FROM weird;
sl@0:   }
sl@0: } {a b 9 0 xyz hi y'all}
sl@0: do_test table-7.3 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM weird;
sl@0:   }
sl@0: } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
sl@0: 
sl@0: # Try out the CREATE TABLE AS syntax
sl@0: #
sl@0: do_test table-8.1 {
sl@0:   execsql2 {
sl@0:     CREATE TABLE t2 AS SELECT * FROM weird;
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
sl@0: do_test table-8.1.1 {
sl@0:   execsql {
sl@0:     SELECT sql FROM sqlite_master WHERE name='t2';
sl@0:   }
sl@0: } {{CREATE TABLE t2(
sl@0:   "desc" text,
sl@0:   "asc" text,
sl@0:   "key" int,
sl@0:   "14_vac" boolean,
sl@0:   fuzzy_dog_12 varchar(10),
sl@0:   "begin" blob,
sl@0:   "end" clob
sl@0: )}}
sl@0: do_test table-8.2 {
sl@0:   execsql {
sl@0:     CREATE TABLE "t3""xyz"(a,b,c);
sl@0:     INSERT INTO [t3"xyz] VALUES(1,2,3);
sl@0:     SELECT * FROM [t3"xyz];
sl@0:   }
sl@0: } {1 2 3}
sl@0: do_test table-8.3 {
sl@0:   execsql2 {
sl@0:     CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
sl@0:     SELECT * FROM [t4"abc];
sl@0:   }
sl@0: } {cnt 1 max(b+c) 5}
sl@0: 
sl@0: # Update for v3: The declaration type of anything except a column is now a
sl@0: # NULL pointer, so the created table has no column types. (Changed result
sl@0: # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
sl@0: do_test table-8.3.1 {
sl@0:   execsql {
sl@0:     SELECT sql FROM sqlite_master WHERE name='t4"abc'
sl@0:   }
sl@0: } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
sl@0: 
sl@0: ifcapable tempdb {
sl@0:   do_test table-8.4 {
sl@0:     execsql2 {
sl@0:       CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
sl@0:       SELECT * FROM t5;
sl@0:     }
sl@0:   } {y'all 1}
sl@0: }
sl@0: 
sl@0: do_test table-8.5 {
sl@0:   db close
sl@0:   sqlite3 db test.db
sl@0:   execsql2 {
sl@0:     SELECT * FROM [t4"abc];
sl@0:   }
sl@0: } {cnt 1 max(b+c) 5}
sl@0: do_test table-8.6 {
sl@0:   execsql2 {
sl@0:     SELECT * FROM t2;
sl@0:   }
sl@0: } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
sl@0: do_test table-8.7 {
sl@0:   catchsql {
sl@0:     SELECT * FROM t5;
sl@0:   }
sl@0: } {1 {no such table: t5}}
sl@0: do_test table-8.8 {
sl@0:   catchsql {
sl@0:     CREATE TABLE t5 AS SELECT * FROM no_such_table;
sl@0:   }
sl@0: } {1 {no such table: no_such_table}}
sl@0: 
sl@0: # Make sure we cannot have duplicate column names within a table.
sl@0: #
sl@0: do_test table-9.1 {
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a,b,a);
sl@0:   }
sl@0: } {1 {duplicate column name: a}}
sl@0: do_test table-9.2 {
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a varchar(100), b blob, a integer);
sl@0:   }
sl@0: } {1 {duplicate column name: a}}
sl@0: 
sl@0: # Check the foreign key syntax.
sl@0: #
sl@0: ifcapable {foreignkey} {
sl@0: do_test table-10.1 {
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
sl@0:     INSERT INTO t6 VALUES(NULL);
sl@0:   }
sl@0: } {1 {t6.a may not be NULL}}
sl@0: do_test table-10.2 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.3 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.4 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.5 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.6 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.7 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a,
sl@0:       FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
sl@0:     );
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.8 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a,b,c,
sl@0:       FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
sl@0:         ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
sl@0:     );
sl@0:   }
sl@0: } {0 {}}
sl@0: do_test table-10.9 {
sl@0:   catchsql {
sl@0:     DROP TABLE t6;
sl@0:     CREATE TABLE t6(a,b,c,
sl@0:       FOREIGN KEY (b,c) REFERENCES t4(x)
sl@0:     );
sl@0:   }
sl@0: } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
sl@0: do_test table-10.10 {
sl@0:   catchsql {DROP TABLE t6}
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a,b,c,
sl@0:       FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
sl@0:     );
sl@0:   }
sl@0: } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
sl@0: do_test table-10.11 {
sl@0:   catchsql {DROP TABLE t6}
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
sl@0:   }
sl@0: } {1 {foreign key on c should reference only one column of table t4}}
sl@0: do_test table-10.12 {
sl@0:   catchsql {DROP TABLE t6}
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a,b,c,
sl@0:       FOREIGN KEY (b,x) REFERENCES t4(x,y)
sl@0:     );
sl@0:   }
sl@0: } {1 {unknown column "x" in foreign key definition}}
sl@0: do_test table-10.13 {
sl@0:   catchsql {DROP TABLE t6}
sl@0:   catchsql {
sl@0:     CREATE TABLE t6(a,b,c,
sl@0:       FOREIGN KEY (x,b) REFERENCES t4(x,y)
sl@0:     );
sl@0:   }
sl@0: } {1 {unknown column "x" in foreign key definition}}
sl@0: } ;# endif foreignkey
sl@0: 
sl@0: # Test for the "typeof" function. More tests for the
sl@0: # typeof() function are found in bind.test and types.test.
sl@0: #
sl@0: do_test table-11.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t7(
sl@0:        a integer primary key,
sl@0:        b number(5,10),
sl@0:        c character varying (8),
sl@0:        d VARCHAR(9),
sl@0:        e clob,
sl@0:        f BLOB,
sl@0:        g Text,
sl@0:        h
sl@0:     );
sl@0:     INSERT INTO t7(a) VALUES(1);
sl@0:     SELECT typeof(a), typeof(b), typeof(c), typeof(d),
sl@0:            typeof(e), typeof(f), typeof(g), typeof(h)
sl@0:     FROM t7 LIMIT 1;
sl@0:   }
sl@0: } {integer null null null null null null null} 
sl@0: do_test table-11.2 {
sl@0:   execsql {
sl@0:     SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
sl@0:     FROM t7 LIMIT 1;
sl@0:   }
sl@0: } {null null null null}
sl@0: 
sl@0: # Test that when creating a table using CREATE TABLE AS, column types are
sl@0: # assigned correctly for (SELECT ...) and 'x AS y' expressions.
sl@0: do_test table-12.1 {
sl@0:   ifcapable subquery {
sl@0:     execsql {
sl@0:       CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
sl@0:     }
sl@0:   } else {
sl@0:     execsql {
sl@0:       CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
sl@0:     }
sl@0:   }
sl@0: } {}
sl@0: do_test table-12.2 {
sl@0:   execsql {
sl@0:     SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
sl@0:   }
sl@0: } {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # Test cases table-13.*
sl@0: #
sl@0: # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
sl@0: # and CURRENT_TIMESTAMP.
sl@0: #
sl@0: do_test table-13.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE tablet8(
sl@0:        a integer primary key,
sl@0:        tm text DEFAULT CURRENT_TIME,
sl@0:        dt text DEFAULT CURRENT_DATE,
sl@0:        dttm text DEFAULT CURRENT_TIMESTAMP
sl@0:     );
sl@0:     SELECT * FROM tablet8;
sl@0:   }
sl@0: } {}
sl@0: set i 0
sl@0: foreach {date time seconds} {
sl@0:   1976-07-04 12:00:00 205329600
sl@0:   1994-04-16 14:00:00 766504800
sl@0:   2000-01-01 00:00:00 946684800
sl@0:   2003-12-31 12:34:56 1072874096
sl@0: } {
sl@0:   incr i
sl@0:   set sqlite_current_time $seconds
sl@0:   do_test table-13.2.$i {
sl@0:     execsql "
sl@0:       INSERT INTO tablet8(a) VALUES($i);
sl@0:       SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
sl@0:     "
sl@0:   } [list $time $date [list $date $time]]
sl@0: }
sl@0: set sqlite_current_time 0
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # Test cases table-14.*
sl@0: #
sl@0: # Test that a table cannot be created or dropped while other virtual
sl@0: # machines are active. This is required because otherwise when in 
sl@0: # auto-vacuum mode the btree-layer may need to move the root-pages of 
sl@0: # a table for which there is an open cursor.
sl@0: #
sl@0: # 2007-05-02:  A open btree cursor no longer blocks CREATE TABLE.
sl@0: # But DROP TABLE is still prohibited because we do not want to
sl@0: # delete a table out from under a running query.
sl@0: #
sl@0: 
sl@0: # db eval {
sl@0: #   pragma vdbe_trace = 0;
sl@0: # }
sl@0: # Try to create a table from within a callback:
sl@0: unset -nocomplain result
sl@0: do_test table-14.1 {
sl@0:   set rc [
sl@0:     catch {
sl@0:       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
sl@0:         db eval {CREATE TABLE t9(a, b, c)}
sl@0:       }
sl@0:     } msg
sl@0:   ]
sl@0:   set result [list $rc $msg]
sl@0: } {0 {}}
sl@0: 
sl@0: # Try to drop a table from within a callback:
sl@0: do_test table-14.2 {
sl@0:   set rc [
sl@0:     catch {
sl@0:       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
sl@0:         db eval {DROP TABLE t9;}
sl@0:       }
sl@0:     } msg
sl@0:   ] 
sl@0:   set result [list $rc $msg]
sl@0: } {1 {database table is locked}}
sl@0: 
sl@0: ifcapable attach {
sl@0:   # Now attach a database and ensure that a table can be created in the 
sl@0:   # attached database whilst in a callback from a query on the main database.
sl@0:   do_test table-14.3 {
sl@0:     file delete -force test2.db
sl@0:     file delete -force test2.db-journal
sl@0:     execsql {
sl@0:       ATTACH 'test2.db' as aux;
sl@0:     }
sl@0:     db eval {SELECT * FROM tablet8 LIMIT 1} {} {
sl@0:       db eval {CREATE TABLE aux.t1(a, b, c)}
sl@0:     }
sl@0:   } {}
sl@0:   
sl@0:   # On the other hand, it should be impossible to drop a table when any VMs 
sl@0:   # are active. This is because VerifyCookie instructions may have already
sl@0:   # been executed, and btree root-pages may not move after this (which a
sl@0:   # delete table might do).
sl@0:   do_test table-14.4 {
sl@0:     set rc [
sl@0:       catch {
sl@0:         db eval {SELECT * FROM tablet8 LIMIT 1} {} {
sl@0:           db eval {DROP TABLE aux.t1;}
sl@0:         }
sl@0:       } msg
sl@0:     ] 
sl@0:     set result [list $rc $msg]
sl@0:   } {1 {database table is locked}}
sl@0: }
sl@0: 
sl@0: # Create and drop 2000 tables. This is to check that the balance_shallow()
sl@0: # routine works correctly on the sqlite_master table. At one point it
sl@0: # contained a bug that would prevent the right-child pointer of the
sl@0: # child page from being copied to the root page.
sl@0: #
sl@0: do_test table-15.1 {
sl@0:   execsql {BEGIN}
sl@0:   for {set i 0} {$i<2000} {incr i} {
sl@0:     execsql "CREATE TABLE tbl$i (a, b, c)"
sl@0:   }
sl@0:   execsql {COMMIT}
sl@0: } {}
sl@0: do_test table-15.2 {
sl@0:   execsql {BEGIN}
sl@0:   for {set i 0} {$i<2000} {incr i} {
sl@0:     execsql "DROP TABLE tbl$i"
sl@0:   }
sl@0:   execsql {COMMIT}
sl@0: } {}
sl@0: 
sl@0: finish_test