1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/temptable.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,422 @@
1.4 +# 2001 October 7
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.
1.15 +#
1.16 +# This file implements tests for temporary tables and indices.
1.17 +#
1.18 +# $Id: temptable.test,v 1.19 2007/10/09 08:29:33 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +ifcapable !tempdb {
1.24 + finish_test
1.25 + return
1.26 +}
1.27 +
1.28 +# Create an alternative connection to the database
1.29 +#
1.30 +do_test temptable-1.0 {
1.31 + sqlite3 db2 ./test.db
1.32 + set dummy {}
1.33 +} {}
1.34 +
1.35 +# Create a permanent table.
1.36 +#
1.37 +do_test temptable-1.1 {
1.38 + execsql {CREATE TABLE t1(a,b,c);}
1.39 + execsql {INSERT INTO t1 VALUES(1,2,3);}
1.40 + execsql {SELECT * FROM t1}
1.41 +} {1 2 3}
1.42 +do_test temptable-1.2 {
1.43 + catch {db2 eval {SELECT * FROM sqlite_master}}
1.44 + db2 eval {SELECT * FROM t1}
1.45 +} {1 2 3}
1.46 +do_test temptable-1.3 {
1.47 + execsql {SELECT name FROM sqlite_master}
1.48 +} {t1}
1.49 +do_test temptable-1.4 {
1.50 + db2 eval {SELECT name FROM sqlite_master}
1.51 +} {t1}
1.52 +
1.53 +# Create a temporary table. Verify that only one of the two
1.54 +# processes can see it.
1.55 +#
1.56 +do_test temptable-1.5 {
1.57 + db2 eval {
1.58 + CREATE TEMP TABLE t2(x,y,z);
1.59 + INSERT INTO t2 VALUES(4,5,6);
1.60 + }
1.61 + db2 eval {SELECT * FROM t2}
1.62 +} {4 5 6}
1.63 +do_test temptable-1.6 {
1.64 + catch {execsql {SELECT * FROM sqlite_master}}
1.65 + catchsql {SELECT * FROM t2}
1.66 +} {1 {no such table: t2}}
1.67 +do_test temptable-1.7 {
1.68 + catchsql {INSERT INTO t2 VALUES(8,9,0);}
1.69 +} {1 {no such table: t2}}
1.70 +do_test temptable-1.8 {
1.71 + db2 eval {INSERT INTO t2 VALUES(8,9,0);}
1.72 + db2 eval {SELECT * FROM t2 ORDER BY x}
1.73 +} {4 5 6 8 9 0}
1.74 +do_test temptable-1.9 {
1.75 + db2 eval {DELETE FROM t2 WHERE x==8}
1.76 + db2 eval {SELECT * FROM t2 ORDER BY x}
1.77 +} {4 5 6}
1.78 +do_test temptable-1.10 {
1.79 + db2 eval {DELETE FROM t2}
1.80 + db2 eval {SELECT * FROM t2}
1.81 +} {}
1.82 +do_test temptable-1.11 {
1.83 + db2 eval {
1.84 + INSERT INTO t2 VALUES(7,6,5);
1.85 + INSERT INTO t2 VALUES(4,3,2);
1.86 + SELECT * FROM t2 ORDER BY x;
1.87 + }
1.88 +} {4 3 2 7 6 5}
1.89 +do_test temptable-1.12 {
1.90 + db2 eval {DROP TABLE t2;}
1.91 + set r [catch {db2 eval {SELECT * FROM t2}} msg]
1.92 + lappend r $msg
1.93 +} {1 {no such table: t2}}
1.94 +
1.95 +# Make sure temporary tables work with transactions
1.96 +#
1.97 +do_test temptable-2.1 {
1.98 + execsql {
1.99 + BEGIN TRANSACTION;
1.100 + CREATE TEMPORARY TABLE t2(x,y);
1.101 + INSERT INTO t2 VALUES(1,2);
1.102 + SELECT * FROM t2;
1.103 + }
1.104 +} {1 2}
1.105 +do_test temptable-2.2 {
1.106 + execsql {ROLLBACK}
1.107 + catchsql {SELECT * FROM t2}
1.108 +} {1 {no such table: t2}}
1.109 +do_test temptable-2.3 {
1.110 + execsql {
1.111 + BEGIN TRANSACTION;
1.112 + CREATE TEMPORARY TABLE t2(x,y);
1.113 + INSERT INTO t2 VALUES(1,2);
1.114 + SELECT * FROM t2;
1.115 + }
1.116 +} {1 2}
1.117 +do_test temptable-2.4 {
1.118 + execsql {COMMIT}
1.119 + catchsql {SELECT * FROM t2}
1.120 +} {0 {1 2}}
1.121 +do_test temptable-2.5 {
1.122 + set r [catch {db2 eval {SELECT * FROM t2}} msg]
1.123 + lappend r $msg
1.124 +} {1 {no such table: t2}}
1.125 +
1.126 +# Make sure indices on temporary tables are also temporary.
1.127 +#
1.128 +do_test temptable-3.1 {
1.129 + execsql {
1.130 + CREATE INDEX i2 ON t2(x);
1.131 + SELECT name FROM sqlite_master WHERE type='index';
1.132 + }
1.133 +} {}
1.134 +do_test temptable-3.2 {
1.135 + execsql {
1.136 + SELECT y FROM t2 WHERE x=1;
1.137 + }
1.138 +} {2}
1.139 +do_test temptable-3.3 {
1.140 + execsql {
1.141 + DROP INDEX i2;
1.142 + SELECT y FROM t2 WHERE x=1;
1.143 + }
1.144 +} {2}
1.145 +do_test temptable-3.4 {
1.146 + execsql {
1.147 + CREATE INDEX i2 ON t2(x);
1.148 + DROP TABLE t2;
1.149 + }
1.150 + catchsql {DROP INDEX i2}
1.151 +} {1 {no such index: i2}}
1.152 +
1.153 +# Check for correct name collision processing. A name collision can
1.154 +# occur when process A creates a temporary table T then process B
1.155 +# creates a permanent table also named T. The temp table in process A
1.156 +# hides the existance of the permanent table.
1.157 +#
1.158 +do_test temptable-4.1 {
1.159 + execsql {
1.160 + CREATE TEMP TABLE t2(x,y);
1.161 + INSERT INTO t2 VALUES(10,20);
1.162 + SELECT * FROM t2;
1.163 + } db2
1.164 +} {10 20}
1.165 +do_test temptable-4.2 {
1.166 + execsql {
1.167 + CREATE TABLE t2(x,y,z);
1.168 + INSERT INTO t2 VALUES(9,8,7);
1.169 + SELECT * FROM t2;
1.170 + }
1.171 +} {9 8 7}
1.172 +do_test temptable-4.3 {
1.173 + catchsql {
1.174 + SELECT * FROM t2;
1.175 + } db2
1.176 +} {0 {10 20}}
1.177 +do_test temptable-4.4.1 {
1.178 + catchsql {
1.179 + SELECT * FROM temp.t2;
1.180 + } db2
1.181 +} {0 {10 20}}
1.182 +do_test temptable-4.4.2 {
1.183 + catchsql {
1.184 + SELECT * FROM main.t2;
1.185 + } db2
1.186 +} {1 {no such table: main.t2}}
1.187 +#do_test temptable-4.4.3 {
1.188 +# catchsql {
1.189 +# SELECT name FROM main.sqlite_master WHERE type='table';
1.190 +# } db2
1.191 +#} {1 {database schema has changed}}
1.192 +do_test temptable-4.4.4 {
1.193 + catchsql {
1.194 + SELECT name FROM main.sqlite_master WHERE type='table';
1.195 + } db2
1.196 +} {0 {t1 t2}}
1.197 +do_test temptable-4.4.5 {
1.198 + catchsql {
1.199 + SELECT * FROM main.t2;
1.200 + } db2
1.201 +} {0 {9 8 7}}
1.202 +do_test temptable-4.4.6 {
1.203 + # TEMP takes precedence over MAIN
1.204 + catchsql {
1.205 + SELECT * FROM t2;
1.206 + } db2
1.207 +} {0 {10 20}}
1.208 +do_test temptable-4.5 {
1.209 + catchsql {
1.210 + DROP TABLE t2; -- should drop TEMP
1.211 + SELECT * FROM t2; -- data should be from MAIN
1.212 + } db2
1.213 +} {0 {9 8 7}}
1.214 +do_test temptable-4.6 {
1.215 + db2 close
1.216 + sqlite3 db2 ./test.db
1.217 + catchsql {
1.218 + SELECT * FROM t2;
1.219 + } db2
1.220 +} {0 {9 8 7}}
1.221 +do_test temptable-4.7 {
1.222 + catchsql {
1.223 + DROP TABLE t2;
1.224 + SELECT * FROM t2;
1.225 + }
1.226 +} {1 {no such table: t2}}
1.227 +do_test temptable-4.8 {
1.228 + db2 close
1.229 + sqlite3 db2 ./test.db
1.230 + execsql {
1.231 + CREATE TEMP TABLE t2(x unique,y);
1.232 + INSERT INTO t2 VALUES(1,2);
1.233 + SELECT * FROM t2;
1.234 + } db2
1.235 +} {1 2}
1.236 +do_test temptable-4.9 {
1.237 + execsql {
1.238 + CREATE TABLE t2(x unique, y);
1.239 + INSERT INTO t2 VALUES(3,4);
1.240 + SELECT * FROM t2;
1.241 + }
1.242 +} {3 4}
1.243 +do_test temptable-4.10.1 {
1.244 + catchsql {
1.245 + SELECT * FROM t2;
1.246 + } db2
1.247 +} {0 {1 2}}
1.248 +# Update: The schema is reloaded in test temptable-4.10.1. And tclsqlite.c
1.249 +# handles it and retries the query anyway.
1.250 +# do_test temptable-4.10.2 {
1.251 +# catchsql {
1.252 +# SELECT name FROM sqlite_master WHERE type='table'
1.253 +# } db2
1.254 +# } {1 {database schema has changed}}
1.255 +do_test temptable-4.10.3 {
1.256 + catchsql {
1.257 + SELECT name FROM sqlite_master WHERE type='table'
1.258 + } db2
1.259 +} {0 {t1 t2}}
1.260 +do_test temptable-4.11 {
1.261 + execsql {
1.262 + SELECT * FROM t2;
1.263 + } db2
1.264 +} {1 2}
1.265 +do_test temptable-4.12 {
1.266 + execsql {
1.267 + SELECT * FROM t2;
1.268 + }
1.269 +} {3 4}
1.270 +do_test temptable-4.13 {
1.271 + catchsql {
1.272 + DROP TABLE t2; -- drops TEMP.T2
1.273 + SELECT * FROM t2; -- uses MAIN.T2
1.274 + } db2
1.275 +} {0 {3 4}}
1.276 +do_test temptable-4.14 {
1.277 + execsql {
1.278 + SELECT * FROM t2;
1.279 + }
1.280 +} {3 4}
1.281 +do_test temptable-4.15 {
1.282 + db2 close
1.283 + sqlite3 db2 ./test.db
1.284 + execsql {
1.285 + SELECT * FROM t2;
1.286 + } db2
1.287 +} {3 4}
1.288 +
1.289 +# Now create a temporary table in db2 and a permanent index in db. The
1.290 +# temporary table in db2 should mask the name of the permanent index,
1.291 +# but the permanent index should still be accessible and should still
1.292 +# be updated when its corresponding table changes.
1.293 +#
1.294 +do_test temptable-5.1 {
1.295 + execsql {
1.296 + CREATE TEMP TABLE mask(a,b,c)
1.297 + } db2
1.298 + execsql {
1.299 + CREATE INDEX mask ON t2(x);
1.300 + SELECT * FROM t2;
1.301 + }
1.302 +} {3 4}
1.303 +#do_test temptable-5.2 {
1.304 +# catchsql {
1.305 +# SELECT * FROM t2;
1.306 +# } db2
1.307 +#} {1 {database schema has changed}}
1.308 +do_test temptable-5.3 {
1.309 + catchsql {
1.310 + SELECT * FROM t2;
1.311 + } db2
1.312 +} {0 {3 4}}
1.313 +do_test temptable-5.4 {
1.314 + execsql {
1.315 + SELECT y FROM t2 WHERE x=3
1.316 + }
1.317 +} {4}
1.318 +do_test temptable-5.5 {
1.319 + execsql {
1.320 + SELECT y FROM t2 WHERE x=3
1.321 + } db2
1.322 +} {4}
1.323 +do_test temptable-5.6 {
1.324 + execsql {
1.325 + INSERT INTO t2 VALUES(1,2);
1.326 + SELECT y FROM t2 WHERE x=1;
1.327 + } db2
1.328 +} {2}
1.329 +do_test temptable-5.7 {
1.330 + execsql {
1.331 + SELECT y FROM t2 WHERE x=3
1.332 + } db2
1.333 +} {4}
1.334 +do_test temptable-5.8 {
1.335 + execsql {
1.336 + SELECT y FROM t2 WHERE x=1;
1.337 + }
1.338 +} {2}
1.339 +do_test temptable-5.9 {
1.340 + execsql {
1.341 + SELECT y FROM t2 WHERE x=3
1.342 + }
1.343 +} {4}
1.344 +
1.345 +db2 close
1.346 +
1.347 +# Test for correct operation of read-only databases
1.348 +#
1.349 +do_test temptable-6.1 {
1.350 + execsql {
1.351 + CREATE TABLE t8(x);
1.352 + INSERT INTO t8 VALUES('xyzzy');
1.353 + SELECT * FROM t8;
1.354 + }
1.355 +} {xyzzy}
1.356 +do_test temptable-6.2 {
1.357 + db close
1.358 + catch {file attributes test.db -permissions 0444}
1.359 + catch {file attributes test.db -readonly 1}
1.360 + sqlite3 db test.db
1.361 + if {[file writable test.db]} {
1.362 + error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
1.363 + }
1.364 + execsql {
1.365 + SELECT * FROM t8;
1.366 + }
1.367 +} {xyzzy}
1.368 +do_test temptable-6.3 {
1.369 + if {[file writable test.db]} {
1.370 + error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
1.371 + }
1.372 + catchsql {
1.373 + CREATE TABLE t9(x,y);
1.374 + }
1.375 +} {1 {attempt to write a readonly database}}
1.376 +do_test temptable-6.4 {
1.377 + catchsql {
1.378 + CREATE TEMP TABLE t9(x,y);
1.379 + }
1.380 +} {0 {}}
1.381 +do_test temptable-6.5 {
1.382 + catchsql {
1.383 + INSERT INTO t9 VALUES(1,2);
1.384 + SELECT * FROM t9;
1.385 + }
1.386 +} {0 {1 2}}
1.387 +do_test temptable-6.6 {
1.388 + if {[file writable test.db]} {
1.389 + error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
1.390 + }
1.391 + catchsql {
1.392 + INSERT INTO t8 VALUES('hello');
1.393 + SELECT * FROM t8;
1.394 + }
1.395 +} {1 {attempt to write a readonly database}}
1.396 +do_test temptable-6.7 {
1.397 + catchsql {
1.398 + SELECT * FROM t8,t9;
1.399 + }
1.400 +} {0 {xyzzy 1 2}}
1.401 +do_test temptable-6.8 {
1.402 + db close
1.403 + sqlite3 db test.db
1.404 + catchsql {
1.405 + SELECT * FROM t8,t9;
1.406 + }
1.407 +} {1 {no such table: t9}}
1.408 +
1.409 +file delete -force test2.db test2.db-journal
1.410 +ifcapable attach {
1.411 + do_test temptable-7.1 {
1.412 + catchsql {
1.413 + ATTACH 'test2.db' AS two;
1.414 + CREATE TEMP TABLE two.abc(x,y);
1.415 + }
1.416 + } {1 {temporary table name must be unqualified}}
1.417 +}
1.418 +
1.419 +# Need to do the following for tcl 8.5 on mac. On that configuration, the
1.420 +# -readonly flag is taken so seriously that a subsequent [file delete -force]
1.421 +# (required before the next test file can be executed) will fail.
1.422 +#
1.423 +catch {file attributes test.db -readonly 0}
1.424 +
1.425 +finish_test