diff options
| author | bernard <[email protected]> | 2017-11-02 13:50:42 +0800 |
|---|---|---|
| committer | bernard <[email protected]> | 2017-11-02 13:50:42 +0800 |
| commit | b2057f506a09e2adef81fc77efd697d16ae14bec (patch) | |
| tree | 3affefabfe376a0ec1b3ac4a5eef957c1dd58dc8 /components/external/SQLite-3.8.1/test/table.test | |
| parent | 23582c8129228b4961ad35319cfd2d270039345f (diff) | |
[External] Remove external from RT-Thread
Diffstat (limited to 'components/external/SQLite-3.8.1/test/table.test')
| -rw-r--r-- | components/external/SQLite-3.8.1/test/table.test | 729 |
1 files changed, 0 insertions, 729 deletions
diff --git a/components/external/SQLite-3.8.1/test/table.test b/components/external/SQLite-3.8.1/test/table.test deleted file mode 100644 index e186450a2f..0000000000 --- a/components/external/SQLite-3.8.1/test/table.test +++ /dev/null @@ -1,729 +0,0 @@ -# 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}} - -# 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; - DROP TABLE IF EXISTS sqlite_stat3; - DROP TABLE IF EXISTS sqlite_stat4; - SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*'; - } -} {} - -# 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 -unset -nocomplain date time seconds -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 { - forcedelete test2.db - forcedelete 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 |
