2001-09-16 00:13:26 +00:00
# 2001 September 15
2000-05-29 20:41:49 +00:00
#
2001-09-16 00:13:26 +00:00
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
2000-05-29 20:41:49 +00:00
#
2001-09-16 00:13:26 +00:00
# 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.
2000-05-29 20:41:49 +00:00
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the CREATE TABLE statement.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Create a basic table and verify it is added to sqlite_master
#
2000-05-29 23:58:11 +00:00
do_test table-1.1 {
2000-05-29 20:41:49 +00:00
execsql {
CREATE TABLE test1 (
one varchar(10),
two text
)
}
execsql {
2000-08-02 13:47:41 +00:00
SELECT sql FROM sqlite_master WHERE type!='meta'
2000-05-29 20:41:49 +00:00
}
} {{CREATE TABLE test1 (
one varchar(10),
two text
)}}
# Verify the other fields of the sqlite_master file.
#
2000-05-29 23:58:11 +00:00
do_test table-1.3 {
2000-08-02 13:47:41 +00:00
execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
2000-05-29 20:41:49 +00:00
} {test1 test1 table}
# Close and reopen the database. Verify that everything is
# still the same.
#
2000-05-29 23:58:11 +00:00
do_test table-1.4 {
2000-05-29 20:41:49 +00:00
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2000-08-02 13:47:41 +00:00
execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
2000-05-29 20:41:49 +00:00
} {test1 test1 table}
# Drop the database and make sure it disappears.
#
2000-05-29 23:58:11 +00:00
do_test table-1.5 {
2000-05-29 20:41:49 +00:00
execsql {DROP TABLE test1}
2000-08-02 13:47:41 +00:00
execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
2000-05-29 20:41:49 +00:00
} {}
# Close and reopen the database. Verify that the table is
# still gone.
#
2000-05-29 23:58:11 +00:00
do_test table-1.6 {
2000-05-29 20:41:49 +00:00
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2000-05-29 20:41:49 +00:00
} {}
2000-05-30 16:27:03 +00:00
# Repeat the above steps, but this time quote the table name.
#
do_test table-1.10 {
execsql {CREATE TABLE "create" (f1 int)}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2000-05-30 16:27:03 +00:00
} {create}
do_test table-1.11 {
execsql {DROP TABLE "create"}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
2000-05-30 16:27:03 +00:00
} {}
do_test table-1.12 {
execsql {CREATE TABLE test1("f1 ho" int)}
2000-08-02 13:47:41 +00:00
execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
2000-05-30 16:27:03 +00:00
} {test1}
do_test table-1.13 {
execsql {DROP TABLE "TEST1"}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
2000-05-30 16:27:03 +00:00
} {}
2000-05-29 23:30:50 +00:00
# Verify that we cannot make two tables with the same name
#
2000-05-29 23:58:11 +00:00
do_test table-2.1 {
2000-06-08 15:10:46 +00:00
execsql {CREATE TABLE TEST2(one text)}
2005-08-13 00:56:27 +00:00
catchsql {CREATE TABLE test2(two text default 'hi')}
2000-05-30 03:12:21 +00:00
} {1 {table test2 already exists}}
2006-03-29 00:24:06 +00:00
do_test table-2.1.1 {
catchsql {CREATE TABLE "test2" (two)}
} {1 {table "test2" already exists}}
2000-05-29 23:58:11 +00:00
do_test table-2.1b {
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
lappend v $msg
2004-06-12 09:25:12 +00:00
} {1 {object name reserved for internal use: sqlite_master}}
2000-05-29 23:58:11 +00:00
do_test table-2.1c {
2000-05-29 23:30:50 +00:00
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
lappend v $msg
2004-06-12 09:25:12 +00:00
} {1 {object name reserved for internal use: sqlite_master}}
2000-05-29 23:58:11 +00:00
do_test table-2.1d {
2005-12-29 23:33:54 +00:00
catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
} {0 {}}
do_test table-2.1e {
2006-01-04 21:40:06 +00:00
catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
} {0 {}}
do_test table-2.1f {
2000-08-02 13:47:41 +00:00
execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
2000-05-29 23:30:50 +00:00
} {}
# Verify that we cannot make a table with the same name as an index
#
2000-05-30 13:44:19 +00:00
do_test table-2.2a {
2009-04-28 15:43:45 +00:00
execsql {CREATE TABLE test2(one text)}
execsql {CREATE INDEX test3 ON test2(one)}
catchsql {CREATE TABLE test3(two text)}
2000-05-30 03:12:21 +00:00
} {1 {there is already an index named test3}}
2000-05-29 23:58:11 +00:00
do_test table-2.2b {
2000-05-29 23:30:50 +00:00
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
lappend v $msg
2000-05-30 03:12:21 +00:00
} {1 {there is already an index named test3}}
2000-05-29 23:58:11 +00:00
do_test table-2.2c {
2001-09-14 03:24:23 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} {test2 test3}
do_test table-2.2d {
2000-05-29 23:30:50 +00:00
execsql {DROP INDEX test3}
set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
lappend v $msg
} {0 {}}
2001-09-14 03:24:23 +00:00
do_test table-2.2e {
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2000-05-29 23:30:50 +00:00
} {test2 test3}
2001-09-14 03:24:23 +00:00
do_test table-2.2f {
2000-05-29 23:30:50 +00:00
execsql {DROP TABLE test2; DROP TABLE test3}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2000-05-29 23:30:50 +00:00
} {}
# Create a table with many field names
#
set big_table \
{CREATE TABLE big(
f1 varchar(20),
f2 char(10),
2000-05-30 13:44:19 +00:00
f3 varchar(30) primary key,
2000-05-29 23:30:50 +00:00
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
)}
2000-05-29 23:58:11 +00:00
do_test table-3.1 {
2000-05-29 23:30:50 +00:00
execsql $big_table
2001-09-27 15:11:53 +00:00
execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
2000-05-29 23:30:50 +00:00
} \{$big_table\}
2000-05-29 23:58:11 +00:00
do_test table-3.2 {
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
lappend v $msg
2000-05-30 03:12:21 +00:00
} {1 {table BIG already exists}}
2000-05-29 23:58:11 +00:00
do_test table-3.3 {
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
lappend v $msg
2000-05-30 03:12:21 +00:00
} {1 {table biG already exists}}
2000-05-29 23:58:11 +00:00
do_test table-3.4 {
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
lappend v $msg
2000-05-30 03:12:21 +00:00
} {1 {table bIg already exists}}
2000-05-29 23:58:11 +00:00
do_test table-3.5 {
2000-05-29 23:30:50 +00:00
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2000-05-29 23:30:50 +00:00
set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
lappend v $msg
2000-05-30 03:12:21 +00:00
} {1 {table Big already exists}}
2000-05-29 23:58:11 +00:00
do_test table-3.6 {
2000-05-29 23:30:50 +00:00
execsql {DROP TABLE big}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2000-05-29 23:30:50 +00:00
} {}
# Try creating large numbers of tables
#
set r {}
for {set i 1} {$i<=100} {incr i} {
2002-08-13 23:02:57 +00:00
lappend r [format test%03d $i]
2000-05-29 23:30:50 +00:00
}
2000-05-29 23:58:11 +00:00
do_test table-4.1 {
2000-05-29 23:30:50 +00:00
for {set i 1} {$i<=100} {incr i} {
2002-08-13 23:02:57 +00:00
set sql "CREATE TABLE [format test%03d $i] ("
2000-05-29 23:30:50 +00:00
for {set k 1} {$k<$i} {incr k} {
append sql "field$k text,"
}
append sql "last_field text)"
execsql $sql
}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2000-05-29 23:30:50 +00:00
} $r
2000-05-29 23:58:11 +00:00
do_test table-4.1b {
2000-05-29 23:30:50 +00:00
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2000-05-29 23:30:50 +00:00
} $r
2001-04-04 11:48:57 +00:00
# Drop the even numbered tables
2000-05-29 23:30:50 +00:00
#
set r {}
for {set i 1} {$i<=100} {incr i 2} {
2002-08-13 23:02:57 +00:00
lappend r [format test%03d $i]
2000-05-29 23:30:50 +00:00
}
2000-05-29 23:58:11 +00:00
do_test table-4.2 {
2000-05-29 23:30:50 +00:00
for {set i 2} {$i<=100} {incr i 2} {
2003-01-04 16:48:09 +00:00
# if {$i==38} {execsql {pragma vdbe_trace=on}}
2002-08-13 23:02:57 +00:00
set sql "DROP TABLE [format TEST%03d $i]"
2000-05-29 23:30:50 +00:00
execsql $sql
}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2000-05-29 23:30:50 +00:00
} $r
2001-09-14 03:24:23 +00:00
#exit
2000-05-29 23:30:50 +00:00
# Drop the odd number tables
#
2000-05-29 23:58:11 +00:00
do_test table-4.3 {
2000-05-29 23:30:50 +00:00
for {set i 1} {$i<=100} {incr i 2} {
2002-08-13 23:02:57 +00:00
set sql "DROP TABLE [format test%03d $i]"
2000-05-29 23:30:50 +00:00
execsql $sql
}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2000-05-29 23:30:50 +00:00
} {}
2000-05-29 23:58:11 +00:00
# Try to drop a table that does not exist
#
2005-12-29 01:11:36 +00:00
do_test table-5.1.1 {
catchsql {DROP TABLE test009}
2002-08-13 23:02:57 +00:00
} {1 {no such table: test009}}
2005-12-29 01:11:36 +00:00
do_test table-5.1.2 {
catchsql {DROP TABLE IF EXISTS test009}
} {0 {}}
2000-05-29 23:58:11 +00:00
# Try to drop sqlite_master
#
do_test table-5.2 {
2005-12-29 01:11:36 +00:00
catchsql {DROP TABLE IF EXISTS sqlite_master}
2000-05-30 03:12:21 +00:00
} {1 {table sqlite_master may not be dropped}}
2000-05-29 23:58:11 +00:00
2011-10-07 23:52:25 +00:00
# Dropping sqlite_statN tables is OK.
#
do_test table-5.2.1 {
db eval {
ANALYZE;
DROP TABLE IF EXISTS sqlite_stat1;
DROP TABLE IF EXISTS sqlite_stat2;
2013-08-12 20:14:04 +00:00
DROP TABLE IF EXISTS sqlite_stat3;
2013-08-05 05:34:30 +00:00
DROP TABLE IF EXISTS sqlite_stat4;
2011-10-07 23:52:25 +00:00
SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*';
}
} {}
2015-04-17 18:22:53 +00:00
do_test table-5.2.2 {
db close
forcedelete test.db
sqlite3 db test.db
2018-11-07 02:17:01 +00:00
sqlite3_db_config db DEFENSIVE 0
2015-04-17 18:22:53 +00:00
db eval {
CREATE TABLE t0(a,b);
CREATE INDEX t ON t0(a);
PRAGMA writable_schema=ON;
UPDATE sqlite_master SET sql='CREATE TABLE a.b(a UNIQUE';
BEGIN;
CREATE TABLE t1(x);
ROLLBACK;
DROP TABLE IF EXISTS t99;
}
} {}
db close
forcedelete test.db
sqlite3 db test.db
2000-05-29 23:58:11 +00:00
# Make sure an EXPLAIN does not really create a new table
#
do_test table-5.3 {
2004-11-03 16:27:01 +00:00
ifcapable {explain} {
execsql {EXPLAIN CREATE TABLE test1(f1 int)}
}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2000-05-29 23:58:11 +00:00
} {}
# Make sure an EXPLAIN does not really drop an existing table
#
do_test table-5.4 {
execsql {CREATE TABLE test1(f1 int)}
2004-11-03 16:27:01 +00:00
ifcapable {explain} {
execsql {EXPLAIN DROP TABLE test1}
}
2000-08-02 13:47:41 +00:00
execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2000-05-29 23:58:11 +00:00
} {test1}
2000-06-08 15:10:46 +00:00
# Create a table with a goofy name
#
2001-09-14 03:24:23 +00:00
#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}
2000-06-08 15:10:46 +00:00
2001-04-04 11:48:57 +00:00
# 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,
2004-11-03 16:27:01 +00:00
key int,
2002-02-21 12:01:27 +00:00
[14_vac] boolean,
fuzzy_dog_12 varchar(10),
2001-04-12 23:21:58 +00:00
begin blob,
end clob
2001-04-04 11:48:57 +00:00
)
}} msg]
lappend v $msg
} {0 {}}
do_test table-7.2 {
execsql {
2001-04-12 23:21:58 +00:00
INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
2001-04-04 11:48:57 +00:00
SELECT * FROM weird;
}
2001-04-12 23:21:58 +00:00
} {a b 9 0 xyz hi y'all}
2001-04-04 11:48:57 +00:00
do_test table-7.3 {
execsql2 {
SELECT * FROM weird;
}
2004-11-03 16:27:01 +00:00
} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
2009-01-16 11:04:58 +00:00
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}
2001-04-04 11:48:57 +00:00
2002-02-18 18:30:32 +00:00
# Try out the CREATE TABLE AS syntax
#
do_test table-8.1 {
execsql2 {
CREATE TABLE t2 AS SELECT * FROM weird;
SELECT * FROM t2;
}
2004-11-03 16:27:01 +00:00
} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
2002-02-21 12:01:27 +00:00
do_test table-8.1.1 {
execsql {
SELECT sql FROM sqlite_master WHERE name='t2';
}
} {{CREATE TABLE t2(
2009-05-11 20:53:28 +00:00
"desc" TEXT,
"asc" TEXT,
"key" INT,
"14_vac" NUM,
fuzzy_dog_12 TEXT,
"begin",
"end" TEXT
2002-02-21 12:01:27 +00:00
)}}
2002-02-18 18:30:32 +00:00
do_test table-8.2 {
execsql {
2004-07-24 03:30:47 +00:00
CREATE TABLE "t3""xyz"(a,b,c);
INSERT INTO [t3"xyz] VALUES(1,2,3);
SELECT * FROM [t3"xyz];
2002-02-18 18:30:32 +00:00
}
} {1 2 3}
do_test table-8.3 {
execsql2 {
2004-07-24 03:30:47 +00:00
CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
SELECT * FROM [t4"abc];
2002-02-18 18:30:32 +00:00
}
2002-02-21 12:01:27 +00:00
} {cnt 1 max(b+c) 5}
2004-06-21 07:36:32 +00:00
# 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
2004-07-24 03:30:47 +00:00
# from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
2002-02-21 12:01:27 +00:00
do_test table-8.3.1 {
execsql {
2004-07-24 03:30:47 +00:00
SELECT sql FROM sqlite_master WHERE name='t4"abc'
2002-02-21 12:01:27 +00:00
}
2004-07-24 03:30:47 +00:00
} {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
2005-03-29 03:10:59 +00:00
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}
}
2002-02-18 18:30:32 +00:00
do_test table-8.5 {
db close
2004-06-19 00:16:31 +00:00
sqlite3 db test.db
2002-02-18 18:30:32 +00:00
execsql2 {
2004-07-24 03:30:47 +00:00
SELECT * FROM [t4"abc];
2002-02-18 18:30:32 +00:00
}
2002-02-21 12:01:27 +00:00
} {cnt 1 max(b+c) 5}
2002-02-18 18:30:32 +00:00
do_test table-8.6 {
execsql2 {
SELECT * FROM t2;
}
2004-11-03 16:27:01 +00:00
} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
2002-02-18 18:30:32 +00:00
do_test table-8.7 {
catchsql {
SELECT * FROM t5;
}
} {1 {no such table: t5}}
2002-02-21 12:01:27 +00:00
do_test table-8.8 {
catchsql {
CREATE TABLE t5 AS SELECT * FROM no_such_table;
}
} {1 {no such table: no_such_table}}
2002-02-18 18:30:32 +00:00
2009-03-14 08:37:23 +00:00
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';
}
2009-05-11 20:53:28 +00:00
} {{CREATE TABLE t11("col.1" TEXT)}}
2009-03-14 08:37:23 +00:00
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(
2009-05-11 20:53:28 +00:00
a INT,
b TEXT,
c TEXT,
d TEXT,
e TEXT,
f TEXT,
g INT
2009-03-14 08:37:23 +00:00
)}}
2002-05-22 21:27:03 +00:00
# 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}}
2005-09-10 15:35:06 +00:00
do_test table-9.2 {
catchsql {
CREATE TABLE t6(a varchar(100), b blob, a integer);
}
} {1 {duplicate column name: a}}
2002-05-22 21:27:03 +00:00
2002-06-02 18:19:00 +00:00
# Check the foreign key syntax.
#
2004-11-03 16:27:01 +00:00
ifcapable {foreignkey} {
2002-06-02 18:19:00 +00:00
do_test table-10.1 {
catchsql {
CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
INSERT INTO t6 VALUES(NULL);
}
2013-11-05 13:33:55 +00:00
} {1 {NOT NULL constraint failed: t6.a}}
2002-06-02 18:19:00 +00:00
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 {}}
2002-08-31 18:53:06 +00:00
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}}
2004-11-03 16:27:01 +00:00
} ;# endif foreignkey
2002-06-02 18:19:00 +00:00
2004-05-24 12:55:54 +00:00
# Test for the "typeof" function. More tests for the
# typeof() function are found in bind.test and types.test.
2002-07-05 21:42:36 +00:00
#
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;
}
2004-05-24 12:55:54 +00:00
} {integer null null null null null null null}
2002-07-05 21:42:36 +00:00
do_test table-11.2 {
execsql {
SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
FROM t7 LIMIT 1;
}
2004-05-24 12:55:54 +00:00
} {null null null null}
2002-05-22 21:27:03 +00:00
2004-06-21 07:36:32 +00:00
# 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 {
2005-01-21 03:12:14 +00:00
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;
}
2004-06-21 07:36:32 +00:00
}
} {}
do_test table-12.2 {
execsql {
SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
}
2009-05-11 20:53:28 +00:00
} {{CREATE TABLE t8(b NUM,h,i INT,j)}}
2004-06-21 07:36:32 +00:00
2004-11-10 11:55:10 +00:00
#--------------------------------------------------------------------
# Test cases table-13.*
#
2004-11-09 12:44:37 +00:00
# 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
2010-05-03 19:20:46 +00:00
unset -nocomplain date time seconds
2006-09-01 15:49:05 +00:00
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
2004-11-09 12:44:37 +00:00
} {
incr i
2006-09-01 15:49:05 +00:00
set sqlite_current_time $seconds
2004-11-09 12:44:37 +00:00
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
2004-11-10 11:55:10 +00:00
#--------------------------------------------------------------------
# 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 17:54:55 +00:00
# 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.
#
2004-11-10 11:55:10 +00:00
2005-01-24 10:25:59 +00:00
# db eval {
# pragma vdbe_trace = 0;
# }
2004-11-10 11:55:10 +00:00
# Try to create a table from within a callback:
2004-11-23 22:16:39 +00:00
unset -nocomplain result
2004-11-10 11:55:10 +00:00
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]
2007-05-02 17:54:55 +00:00
} {0 {}}
2004-11-10 11:55:10 +00:00
# Try to drop a table from within a callback:
2007-10-09 08:29:32 +00:00
do_test table-14.2 {
2004-11-10 11:55:10 +00:00
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}}
2007-10-09 08:29:32 +00:00
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 {
2011-08-02 00:57:34 +00:00
forcedelete test2.db
forcedelete test2.db-journal
2007-10-09 08:29:32 +00:00
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}}
}
2004-11-10 11:55:10 +00:00
2004-11-23 09:06:55 +00:00
# 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}
} {}
2014-08-05 21:31:08 +00:00
# Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05)
# The following SQL script segfaults while running the INSERT statement:
#
# CREATE TABLE t1(x DEFAULT(max(1)));
# INSERT INTO t1(rowid) VALUES(1);
#
# The problem appears to be the use of an aggregate function as part of
# the default value for a column. This problem has been in the code since
# at least 2006-01-01 and probably before that. This problem was detected
# and reported on the sqlite-users@sqlite.org mailing list by Zsbán Ambrus.
#
do_execsql_test table-16.1 {
CREATE TABLE t16(x DEFAULT(max(1)));
INSERT INTO t16(x) VALUES(123);
SELECT rowid, x FROM t16;
} {1 123}
do_catchsql_test table-16.2 {
INSERT INTO t16(rowid) VALUES(4);
2014-08-06 00:29:06 +00:00
} {1 {unknown function: max()}}
2014-08-05 21:31:08 +00:00
do_execsql_test table-16.3 {
DROP TABLE t16;
CREATE TABLE t16(x DEFAULT(abs(1)));
INSERT INTO t16(rowid) VALUES(4);
SELECT rowid, x FROM t16;
} {4 1}
do_catchsql_test table-16.4 {
DROP TABLE t16;
CREATE TABLE t16(x DEFAULT(avg(1)));
INSERT INTO t16(rowid) VALUES(123);
SELECT rowid, x FROM t16;
2014-08-06 00:29:06 +00:00
} {1 {unknown function: avg()}}
2014-08-05 21:31:08 +00:00
do_catchsql_test table-16.5 {
DROP TABLE t16;
CREATE TABLE t16(x DEFAULT(count()));
INSERT INTO t16(rowid) VALUES(123);
SELECT rowid, x FROM t16;
2014-08-06 00:29:06 +00:00
} {1 {unknown function: count()}}
2014-08-05 21:31:08 +00:00
do_catchsql_test table-16.6 {
DROP TABLE t16;
2023-10-20 20:19:30 +00:00
CREATE TABLE t16(x DEFAULT(string_agg('x',',')));
2014-08-05 21:31:08 +00:00
INSERT INTO t16(rowid) VALUES(123);
SELECT rowid, x FROM t16;
2023-10-20 20:19:30 +00:00
} {1 {unknown function: string_agg()}}
2014-08-06 00:29:06 +00:00
do_catchsql_test table-16.7 {
INSERT INTO t16 DEFAULT VALUES;
2023-10-20 20:19:30 +00:00
} {1 {unknown function: string_agg()}}
2014-08-05 21:31:08 +00:00
2014-11-05 15:57:39 +00:00
# Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c63]
# describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT statement.
# the following test verifies that the problem has been fixed.
#
do_execsql_test table-17.1 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a TEXT);
INSERT INTO t1(a) VALUES(1),(2);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(x TEXT, y TEXT);
INSERT INTO t2(x,y) VALUES(3,4);
DROP TABLE IF EXISTS t3;
CREATE TABLE t3 AS
SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x;
SELECT p, q, '|' FROM t3 ORDER BY p;
} {1 1 | 2 2 |}
2015-06-16 16:39:01 +00:00
# 2015-06-16
# Ticket [https://www.sqlite.org/src/tktview/873cae2b6e25b1991ce5e9b782f9cd0409b96063]
# Make sure a CREATE TABLE AS statement correctly rolls back partial changes to the
# sqlite_master table when the SELECT on the right-hand side aborts.
#
do_catchsql_test table-18.1 {
DROP TABLE IF EXISTS t1;
BEGIN;
CREATE TABLE t1 AS SELECT zeroblob(2e20);
} {1 {string or blob too big}}
do_execsql_test table-18.2 {
COMMIT;
PRAGMA integrity_check;
} {ok}
2015-09-09 13:28:06 +00:00
# 2015-09-09
# Ticket [https://www.sqlite.org/src/info/acd12990885d9276]
# "CREATE TABLE ... AS SELECT ... FROM sqlite_master" fails because the row
# in the sqlite_master table for the next table is initially populated
# with a NULL instead of a record created by OP_Record.
#
do_execsql_test table-19.1 {
CREATE TABLE t19 AS SELECT * FROM sqlite_master;
SELECT name FROM t19 ORDER BY name;
} {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird}
2015-06-16 16:39:01 +00:00
2000-05-29 20:41:49 +00:00
finish_test