Move bundled copy of sqlite one level deeper to better separate it...

third_party/sqlite/test/table.test

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