1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/table.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,676 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the CREATE TABLE statement.
1.16 +#
1.17 +# $Id: table.test,v 1.48 2007/10/09 08:29:33 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Create a basic table and verify it is added to sqlite_master
1.23 +#
1.24 +do_test table-1.1 {
1.25 + execsql {
1.26 + CREATE TABLE test1 (
1.27 + one varchar(10),
1.28 + two text
1.29 + )
1.30 + }
1.31 + execsql {
1.32 + SELECT sql FROM sqlite_master WHERE type!='meta'
1.33 + }
1.34 +} {{CREATE TABLE test1 (
1.35 + one varchar(10),
1.36 + two text
1.37 + )}}
1.38 +
1.39 +
1.40 +# Verify the other fields of the sqlite_master file.
1.41 +#
1.42 +do_test table-1.3 {
1.43 + execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
1.44 +} {test1 test1 table}
1.45 +
1.46 +# Close and reopen the database. Verify that everything is
1.47 +# still the same.
1.48 +#
1.49 +do_test table-1.4 {
1.50 + db close
1.51 + sqlite3 db test.db
1.52 + execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
1.53 +} {test1 test1 table}
1.54 +
1.55 +# Drop the database and make sure it disappears.
1.56 +#
1.57 +do_test table-1.5 {
1.58 + execsql {DROP TABLE test1}
1.59 + execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
1.60 +} {}
1.61 +
1.62 +# Close and reopen the database. Verify that the table is
1.63 +# still gone.
1.64 +#
1.65 +do_test table-1.6 {
1.66 + db close
1.67 + sqlite3 db test.db
1.68 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.69 +} {}
1.70 +
1.71 +# Repeat the above steps, but this time quote the table name.
1.72 +#
1.73 +do_test table-1.10 {
1.74 + execsql {CREATE TABLE "create" (f1 int)}
1.75 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.76 +} {create}
1.77 +do_test table-1.11 {
1.78 + execsql {DROP TABLE "create"}
1.79 + execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
1.80 +} {}
1.81 +do_test table-1.12 {
1.82 + execsql {CREATE TABLE test1("f1 ho" int)}
1.83 + execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
1.84 +} {test1}
1.85 +do_test table-1.13 {
1.86 + execsql {DROP TABLE "TEST1"}
1.87 + execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
1.88 +} {}
1.89 +
1.90 +
1.91 +
1.92 +# Verify that we cannot make two tables with the same name
1.93 +#
1.94 +do_test table-2.1 {
1.95 + execsql {CREATE TABLE TEST2(one text)}
1.96 + catchsql {CREATE TABLE test2(two text default 'hi')}
1.97 +} {1 {table test2 already exists}}
1.98 +do_test table-2.1.1 {
1.99 + catchsql {CREATE TABLE "test2" (two)}
1.100 +} {1 {table "test2" already exists}}
1.101 +do_test table-2.1b {
1.102 + set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
1.103 + lappend v $msg
1.104 +} {1 {object name reserved for internal use: sqlite_master}}
1.105 +do_test table-2.1c {
1.106 + db close
1.107 + sqlite3 db test.db
1.108 + set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
1.109 + lappend v $msg
1.110 +} {1 {object name reserved for internal use: sqlite_master}}
1.111 +do_test table-2.1d {
1.112 + catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
1.113 +} {0 {}}
1.114 +do_test table-2.1e {
1.115 + catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
1.116 +} {0 {}}
1.117 +do_test table-2.1f {
1.118 + execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
1.119 +} {}
1.120 +
1.121 +# Verify that we cannot make a table with the same name as an index
1.122 +#
1.123 +do_test table-2.2a {
1.124 + execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
1.125 + set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
1.126 + lappend v $msg
1.127 +} {1 {there is already an index named test3}}
1.128 +do_test table-2.2b {
1.129 + db close
1.130 + sqlite3 db test.db
1.131 + set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
1.132 + lappend v $msg
1.133 +} {1 {there is already an index named test3}}
1.134 +do_test table-2.2c {
1.135 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.136 +} {test2 test3}
1.137 +do_test table-2.2d {
1.138 + execsql {DROP INDEX test3}
1.139 + set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
1.140 + lappend v $msg
1.141 +} {0 {}}
1.142 +do_test table-2.2e {
1.143 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.144 +} {test2 test3}
1.145 +do_test table-2.2f {
1.146 + execsql {DROP TABLE test2; DROP TABLE test3}
1.147 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.148 +} {}
1.149 +
1.150 +# Create a table with many field names
1.151 +#
1.152 +set big_table \
1.153 +{CREATE TABLE big(
1.154 + f1 varchar(20),
1.155 + f2 char(10),
1.156 + f3 varchar(30) primary key,
1.157 + f4 text,
1.158 + f5 text,
1.159 + f6 text,
1.160 + f7 text,
1.161 + f8 text,
1.162 + f9 text,
1.163 + f10 text,
1.164 + f11 text,
1.165 + f12 text,
1.166 + f13 text,
1.167 + f14 text,
1.168 + f15 text,
1.169 + f16 text,
1.170 + f17 text,
1.171 + f18 text,
1.172 + f19 text,
1.173 + f20 text
1.174 +)}
1.175 +do_test table-3.1 {
1.176 + execsql $big_table
1.177 + execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
1.178 +} \{$big_table\}
1.179 +do_test table-3.2 {
1.180 + set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
1.181 + lappend v $msg
1.182 +} {1 {table BIG already exists}}
1.183 +do_test table-3.3 {
1.184 + set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
1.185 + lappend v $msg
1.186 +} {1 {table biG already exists}}
1.187 +do_test table-3.4 {
1.188 + set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
1.189 + lappend v $msg
1.190 +} {1 {table bIg already exists}}
1.191 +do_test table-3.5 {
1.192 + db close
1.193 + sqlite3 db test.db
1.194 + set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
1.195 + lappend v $msg
1.196 +} {1 {table Big already exists}}
1.197 +do_test table-3.6 {
1.198 + execsql {DROP TABLE big}
1.199 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.200 +} {}
1.201 +
1.202 +# Try creating large numbers of tables
1.203 +#
1.204 +set r {}
1.205 +for {set i 1} {$i<=100} {incr i} {
1.206 + lappend r [format test%03d $i]
1.207 +}
1.208 +do_test table-4.1 {
1.209 + for {set i 1} {$i<=100} {incr i} {
1.210 + set sql "CREATE TABLE [format test%03d $i] ("
1.211 + for {set k 1} {$k<$i} {incr k} {
1.212 + append sql "field$k text,"
1.213 + }
1.214 + append sql "last_field text)"
1.215 + execsql $sql
1.216 + }
1.217 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.218 +} $r
1.219 +do_test table-4.1b {
1.220 + db close
1.221 + sqlite3 db test.db
1.222 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.223 +} $r
1.224 +
1.225 +# Drop the even numbered tables
1.226 +#
1.227 +set r {}
1.228 +for {set i 1} {$i<=100} {incr i 2} {
1.229 + lappend r [format test%03d $i]
1.230 +}
1.231 +do_test table-4.2 {
1.232 + for {set i 2} {$i<=100} {incr i 2} {
1.233 + # if {$i==38} {execsql {pragma vdbe_trace=on}}
1.234 + set sql "DROP TABLE [format TEST%03d $i]"
1.235 + execsql $sql
1.236 + }
1.237 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.238 +} $r
1.239 +#exit
1.240 +
1.241 +# Drop the odd number tables
1.242 +#
1.243 +do_test table-4.3 {
1.244 + for {set i 1} {$i<=100} {incr i 2} {
1.245 + set sql "DROP TABLE [format test%03d $i]"
1.246 + execsql $sql
1.247 + }
1.248 + execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1.249 +} {}
1.250 +
1.251 +# Try to drop a table that does not exist
1.252 +#
1.253 +do_test table-5.1.1 {
1.254 + catchsql {DROP TABLE test009}
1.255 +} {1 {no such table: test009}}
1.256 +do_test table-5.1.2 {
1.257 + catchsql {DROP TABLE IF EXISTS test009}
1.258 +} {0 {}}
1.259 +
1.260 +# Try to drop sqlite_master
1.261 +#
1.262 +do_test table-5.2 {
1.263 + catchsql {DROP TABLE IF EXISTS sqlite_master}
1.264 +} {1 {table sqlite_master may not be dropped}}
1.265 +
1.266 +# Make sure an EXPLAIN does not really create a new table
1.267 +#
1.268 +do_test table-5.3 {
1.269 + ifcapable {explain} {
1.270 + execsql {EXPLAIN CREATE TABLE test1(f1 int)}
1.271 + }
1.272 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.273 +} {}
1.274 +
1.275 +# Make sure an EXPLAIN does not really drop an existing table
1.276 +#
1.277 +do_test table-5.4 {
1.278 + execsql {CREATE TABLE test1(f1 int)}
1.279 + ifcapable {explain} {
1.280 + execsql {EXPLAIN DROP TABLE test1}
1.281 + }
1.282 + execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1.283 +} {test1}
1.284 +
1.285 +# Create a table with a goofy name
1.286 +#
1.287 +#do_test table-6.1 {
1.288 +# execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
1.289 +# execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
1.290 +# set list [glob -nocomplain testdb/spaces*.tbl]
1.291 +#} {testdb/spaces+in+this+name+.tbl}
1.292 +
1.293 +# Try using keywords as table names or column names.
1.294 +#
1.295 +do_test table-7.1 {
1.296 + set v [catch {execsql {
1.297 + CREATE TABLE weird(
1.298 + desc text,
1.299 + asc text,
1.300 + key int,
1.301 + [14_vac] boolean,
1.302 + fuzzy_dog_12 varchar(10),
1.303 + begin blob,
1.304 + end clob
1.305 + )
1.306 + }} msg]
1.307 + lappend v $msg
1.308 +} {0 {}}
1.309 +do_test table-7.2 {
1.310 + execsql {
1.311 + INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
1.312 + SELECT * FROM weird;
1.313 + }
1.314 +} {a b 9 0 xyz hi y'all}
1.315 +do_test table-7.3 {
1.316 + execsql2 {
1.317 + SELECT * FROM weird;
1.318 + }
1.319 +} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
1.320 +
1.321 +# Try out the CREATE TABLE AS syntax
1.322 +#
1.323 +do_test table-8.1 {
1.324 + execsql2 {
1.325 + CREATE TABLE t2 AS SELECT * FROM weird;
1.326 + SELECT * FROM t2;
1.327 + }
1.328 +} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
1.329 +do_test table-8.1.1 {
1.330 + execsql {
1.331 + SELECT sql FROM sqlite_master WHERE name='t2';
1.332 + }
1.333 +} {{CREATE TABLE t2(
1.334 + "desc" text,
1.335 + "asc" text,
1.336 + "key" int,
1.337 + "14_vac" boolean,
1.338 + fuzzy_dog_12 varchar(10),
1.339 + "begin" blob,
1.340 + "end" clob
1.341 +)}}
1.342 +do_test table-8.2 {
1.343 + execsql {
1.344 + CREATE TABLE "t3""xyz"(a,b,c);
1.345 + INSERT INTO [t3"xyz] VALUES(1,2,3);
1.346 + SELECT * FROM [t3"xyz];
1.347 + }
1.348 +} {1 2 3}
1.349 +do_test table-8.3 {
1.350 + execsql2 {
1.351 + CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
1.352 + SELECT * FROM [t4"abc];
1.353 + }
1.354 +} {cnt 1 max(b+c) 5}
1.355 +
1.356 +# Update for v3: The declaration type of anything except a column is now a
1.357 +# NULL pointer, so the created table has no column types. (Changed result
1.358 +# from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
1.359 +do_test table-8.3.1 {
1.360 + execsql {
1.361 + SELECT sql FROM sqlite_master WHERE name='t4"abc'
1.362 + }
1.363 +} {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
1.364 +
1.365 +ifcapable tempdb {
1.366 + do_test table-8.4 {
1.367 + execsql2 {
1.368 + CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
1.369 + SELECT * FROM t5;
1.370 + }
1.371 + } {y'all 1}
1.372 +}
1.373 +
1.374 +do_test table-8.5 {
1.375 + db close
1.376 + sqlite3 db test.db
1.377 + execsql2 {
1.378 + SELECT * FROM [t4"abc];
1.379 + }
1.380 +} {cnt 1 max(b+c) 5}
1.381 +do_test table-8.6 {
1.382 + execsql2 {
1.383 + SELECT * FROM t2;
1.384 + }
1.385 +} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
1.386 +do_test table-8.7 {
1.387 + catchsql {
1.388 + SELECT * FROM t5;
1.389 + }
1.390 +} {1 {no such table: t5}}
1.391 +do_test table-8.8 {
1.392 + catchsql {
1.393 + CREATE TABLE t5 AS SELECT * FROM no_such_table;
1.394 + }
1.395 +} {1 {no such table: no_such_table}}
1.396 +
1.397 +# Make sure we cannot have duplicate column names within a table.
1.398 +#
1.399 +do_test table-9.1 {
1.400 + catchsql {
1.401 + CREATE TABLE t6(a,b,a);
1.402 + }
1.403 +} {1 {duplicate column name: a}}
1.404 +do_test table-9.2 {
1.405 + catchsql {
1.406 + CREATE TABLE t6(a varchar(100), b blob, a integer);
1.407 + }
1.408 +} {1 {duplicate column name: a}}
1.409 +
1.410 +# Check the foreign key syntax.
1.411 +#
1.412 +ifcapable {foreignkey} {
1.413 +do_test table-10.1 {
1.414 + catchsql {
1.415 + CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
1.416 + INSERT INTO t6 VALUES(NULL);
1.417 + }
1.418 +} {1 {t6.a may not be NULL}}
1.419 +do_test table-10.2 {
1.420 + catchsql {
1.421 + DROP TABLE t6;
1.422 + CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
1.423 + }
1.424 +} {0 {}}
1.425 +do_test table-10.3 {
1.426 + catchsql {
1.427 + DROP TABLE t6;
1.428 + CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
1.429 + }
1.430 +} {0 {}}
1.431 +do_test table-10.4 {
1.432 + catchsql {
1.433 + DROP TABLE t6;
1.434 + CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
1.435 + }
1.436 +} {0 {}}
1.437 +do_test table-10.5 {
1.438 + catchsql {
1.439 + DROP TABLE t6;
1.440 + CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
1.441 + }
1.442 +} {0 {}}
1.443 +do_test table-10.6 {
1.444 + catchsql {
1.445 + DROP TABLE t6;
1.446 + CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
1.447 + }
1.448 +} {0 {}}
1.449 +do_test table-10.7 {
1.450 + catchsql {
1.451 + DROP TABLE t6;
1.452 + CREATE TABLE t6(a,
1.453 + FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
1.454 + );
1.455 + }
1.456 +} {0 {}}
1.457 +do_test table-10.8 {
1.458 + catchsql {
1.459 + DROP TABLE t6;
1.460 + CREATE TABLE t6(a,b,c,
1.461 + FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
1.462 + ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1.463 + );
1.464 + }
1.465 +} {0 {}}
1.466 +do_test table-10.9 {
1.467 + catchsql {
1.468 + DROP TABLE t6;
1.469 + CREATE TABLE t6(a,b,c,
1.470 + FOREIGN KEY (b,c) REFERENCES t4(x)
1.471 + );
1.472 + }
1.473 +} {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
1.474 +do_test table-10.10 {
1.475 + catchsql {DROP TABLE t6}
1.476 + catchsql {
1.477 + CREATE TABLE t6(a,b,c,
1.478 + FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
1.479 + );
1.480 + }
1.481 +} {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
1.482 +do_test table-10.11 {
1.483 + catchsql {DROP TABLE t6}
1.484 + catchsql {
1.485 + CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
1.486 + }
1.487 +} {1 {foreign key on c should reference only one column of table t4}}
1.488 +do_test table-10.12 {
1.489 + catchsql {DROP TABLE t6}
1.490 + catchsql {
1.491 + CREATE TABLE t6(a,b,c,
1.492 + FOREIGN KEY (b,x) REFERENCES t4(x,y)
1.493 + );
1.494 + }
1.495 +} {1 {unknown column "x" in foreign key definition}}
1.496 +do_test table-10.13 {
1.497 + catchsql {DROP TABLE t6}
1.498 + catchsql {
1.499 + CREATE TABLE t6(a,b,c,
1.500 + FOREIGN KEY (x,b) REFERENCES t4(x,y)
1.501 + );
1.502 + }
1.503 +} {1 {unknown column "x" in foreign key definition}}
1.504 +} ;# endif foreignkey
1.505 +
1.506 +# Test for the "typeof" function. More tests for the
1.507 +# typeof() function are found in bind.test and types.test.
1.508 +#
1.509 +do_test table-11.1 {
1.510 + execsql {
1.511 + CREATE TABLE t7(
1.512 + a integer primary key,
1.513 + b number(5,10),
1.514 + c character varying (8),
1.515 + d VARCHAR(9),
1.516 + e clob,
1.517 + f BLOB,
1.518 + g Text,
1.519 + h
1.520 + );
1.521 + INSERT INTO t7(a) VALUES(1);
1.522 + SELECT typeof(a), typeof(b), typeof(c), typeof(d),
1.523 + typeof(e), typeof(f), typeof(g), typeof(h)
1.524 + FROM t7 LIMIT 1;
1.525 + }
1.526 +} {integer null null null null null null null}
1.527 +do_test table-11.2 {
1.528 + execsql {
1.529 + SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
1.530 + FROM t7 LIMIT 1;
1.531 + }
1.532 +} {null null null null}
1.533 +
1.534 +# Test that when creating a table using CREATE TABLE AS, column types are
1.535 +# assigned correctly for (SELECT ...) and 'x AS y' expressions.
1.536 +do_test table-12.1 {
1.537 + ifcapable subquery {
1.538 + execsql {
1.539 + CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
1.540 + }
1.541 + } else {
1.542 + execsql {
1.543 + CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
1.544 + }
1.545 + }
1.546 +} {}
1.547 +do_test table-12.2 {
1.548 + execsql {
1.549 + SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
1.550 + }
1.551 +} {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}
1.552 +
1.553 +#--------------------------------------------------------------------
1.554 +# Test cases table-13.*
1.555 +#
1.556 +# Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
1.557 +# and CURRENT_TIMESTAMP.
1.558 +#
1.559 +do_test table-13.1 {
1.560 + execsql {
1.561 + CREATE TABLE tablet8(
1.562 + a integer primary key,
1.563 + tm text DEFAULT CURRENT_TIME,
1.564 + dt text DEFAULT CURRENT_DATE,
1.565 + dttm text DEFAULT CURRENT_TIMESTAMP
1.566 + );
1.567 + SELECT * FROM tablet8;
1.568 + }
1.569 +} {}
1.570 +set i 0
1.571 +foreach {date time seconds} {
1.572 + 1976-07-04 12:00:00 205329600
1.573 + 1994-04-16 14:00:00 766504800
1.574 + 2000-01-01 00:00:00 946684800
1.575 + 2003-12-31 12:34:56 1072874096
1.576 +} {
1.577 + incr i
1.578 + set sqlite_current_time $seconds
1.579 + do_test table-13.2.$i {
1.580 + execsql "
1.581 + INSERT INTO tablet8(a) VALUES($i);
1.582 + SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
1.583 + "
1.584 + } [list $time $date [list $date $time]]
1.585 +}
1.586 +set sqlite_current_time 0
1.587 +
1.588 +#--------------------------------------------------------------------
1.589 +# Test cases table-14.*
1.590 +#
1.591 +# Test that a table cannot be created or dropped while other virtual
1.592 +# machines are active. This is required because otherwise when in
1.593 +# auto-vacuum mode the btree-layer may need to move the root-pages of
1.594 +# a table for which there is an open cursor.
1.595 +#
1.596 +# 2007-05-02: A open btree cursor no longer blocks CREATE TABLE.
1.597 +# But DROP TABLE is still prohibited because we do not want to
1.598 +# delete a table out from under a running query.
1.599 +#
1.600 +
1.601 +# db eval {
1.602 +# pragma vdbe_trace = 0;
1.603 +# }
1.604 +# Try to create a table from within a callback:
1.605 +unset -nocomplain result
1.606 +do_test table-14.1 {
1.607 + set rc [
1.608 + catch {
1.609 + db eval {SELECT * FROM tablet8 LIMIT 1} {} {
1.610 + db eval {CREATE TABLE t9(a, b, c)}
1.611 + }
1.612 + } msg
1.613 + ]
1.614 + set result [list $rc $msg]
1.615 +} {0 {}}
1.616 +
1.617 +# Try to drop a table from within a callback:
1.618 +do_test table-14.2 {
1.619 + set rc [
1.620 + catch {
1.621 + db eval {SELECT * FROM tablet8 LIMIT 1} {} {
1.622 + db eval {DROP TABLE t9;}
1.623 + }
1.624 + } msg
1.625 + ]
1.626 + set result [list $rc $msg]
1.627 +} {1 {database table is locked}}
1.628 +
1.629 +ifcapable attach {
1.630 + # Now attach a database and ensure that a table can be created in the
1.631 + # attached database whilst in a callback from a query on the main database.
1.632 + do_test table-14.3 {
1.633 + file delete -force test2.db
1.634 + file delete -force test2.db-journal
1.635 + execsql {
1.636 + ATTACH 'test2.db' as aux;
1.637 + }
1.638 + db eval {SELECT * FROM tablet8 LIMIT 1} {} {
1.639 + db eval {CREATE TABLE aux.t1(a, b, c)}
1.640 + }
1.641 + } {}
1.642 +
1.643 + # On the other hand, it should be impossible to drop a table when any VMs
1.644 + # are active. This is because VerifyCookie instructions may have already
1.645 + # been executed, and btree root-pages may not move after this (which a
1.646 + # delete table might do).
1.647 + do_test table-14.4 {
1.648 + set rc [
1.649 + catch {
1.650 + db eval {SELECT * FROM tablet8 LIMIT 1} {} {
1.651 + db eval {DROP TABLE aux.t1;}
1.652 + }
1.653 + } msg
1.654 + ]
1.655 + set result [list $rc $msg]
1.656 + } {1 {database table is locked}}
1.657 +}
1.658 +
1.659 +# Create and drop 2000 tables. This is to check that the balance_shallow()
1.660 +# routine works correctly on the sqlite_master table. At one point it
1.661 +# contained a bug that would prevent the right-child pointer of the
1.662 +# child page from being copied to the root page.
1.663 +#
1.664 +do_test table-15.1 {
1.665 + execsql {BEGIN}
1.666 + for {set i 0} {$i<2000} {incr i} {
1.667 + execsql "CREATE TABLE tbl$i (a, b, c)"
1.668 + }
1.669 + execsql {COMMIT}
1.670 +} {}
1.671 +do_test table-15.2 {
1.672 + execsql {BEGIN}
1.673 + for {set i 0} {$i<2000} {incr i} {
1.674 + execsql "DROP TABLE tbl$i"
1.675 + }
1.676 + execsql {COMMIT}
1.677 +} {}
1.678 +
1.679 +finish_test