AryaWu/sqlite
0
1# 2001 October 72#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13# This file implements tests for temporary tables and indices.14#15# $Id: temptable.test,v 1.21 2009/06/16 17:49:36 drh Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !tempdb {21 finish_test22 return23}24 25# Create an alternative connection to the database26#27do_test temptable-1.0 {28 sqlite3 db2 ./test.db29 set dummy {}30} {}31 32# Create a permanent table.33#34do_test temptable-1.1 {35 execsql {CREATE TABLE t1(a,b,c);}36 execsql {INSERT INTO t1 VALUES(1,2,3);}37 execsql {SELECT * FROM t1}38} {1 2 3}39do_test temptable-1.2 {40 catch {db2 eval {SELECT * FROM sqlite_master}}41 db2 eval {SELECT * FROM t1}42} {1 2 3}43do_test temptable-1.3 {44 execsql {SELECT name FROM sqlite_master}45} {t1}46do_test temptable-1.4 {47 db2 eval {SELECT name FROM sqlite_master}48} {t1}49 50# Create a temporary table. Verify that only one of the two51# processes can see it.52#53do_test temptable-1.5 {54 db2 eval {55 CREATE TEMP TABLE t2(x,y,z);56 INSERT INTO t2 VALUES(4,5,6);57 }58 db2 eval {SELECT * FROM t2}59} {4 5 6}60do_test temptable-1.6 {61 catch {execsql {SELECT * FROM sqlite_master}}62 catchsql {SELECT * FROM t2}63} {1 {no such table: t2}}64do_test temptable-1.7 {65 catchsql {INSERT INTO t2 VALUES(8,9,0);}66} {1 {no such table: t2}}67do_test temptable-1.8 {68 db2 eval {INSERT INTO t2 VALUES(8,9,0);}69 db2 eval {SELECT * FROM t2 ORDER BY x}70} {4 5 6 8 9 0}71do_test temptable-1.9 {72 db2 eval {DELETE FROM t2 WHERE x==8}73 db2 eval {SELECT * FROM t2 ORDER BY x}74} {4 5 6}75do_test temptable-1.10 {76 db2 eval {DELETE FROM t2}77 db2 eval {SELECT * FROM t2}78} {}79do_test temptable-1.11 {80 db2 eval {81 INSERT INTO t2 VALUES(7,6,5);82 INSERT INTO t2 VALUES(4,3,2);83 SELECT * FROM t2 ORDER BY x;84 }85} {4 3 2 7 6 5}86do_test temptable-1.12 {87 db2 eval {DROP TABLE t2;}88 set r [catch {db2 eval {SELECT * FROM t2}} msg]89 lappend r $msg90} {1 {no such table: t2}}91 92# Make sure temporary tables work with transactions93#94do_test temptable-2.1 {95 execsql {96 BEGIN TRANSACTION;97 CREATE TEMPORARY TABLE t2(x,y);98 INSERT INTO t2 VALUES(1,2);99 SELECT * FROM t2;100 }101} {1 2}102do_test temptable-2.2 {103 execsql {ROLLBACK}104 catchsql {SELECT * FROM t2}105} {1 {no such table: t2}}106do_test temptable-2.3 {107 execsql {108 BEGIN TRANSACTION;109 CREATE TEMPORARY TABLE t2(x,y);110 INSERT INTO t2 VALUES(1,2);111 SELECT * FROM t2;112 }113} {1 2}114do_test temptable-2.4 {115 execsql {COMMIT}116 catchsql {SELECT * FROM t2}117} {0 {1 2}}118do_test temptable-2.5 {119 set r [catch {db2 eval {SELECT * FROM t2}} msg]120 lappend r $msg121} {1 {no such table: t2}}122 123# Make sure indices on temporary tables are also temporary.124#125do_test temptable-3.1 {126 execsql {127 CREATE INDEX i2 ON t2(x);128 SELECT name FROM sqlite_master WHERE type='index';129 }130} {}131do_test temptable-3.2 {132 execsql {133 SELECT y FROM t2 WHERE x=1;134 }135} {2}136do_test temptable-3.3 {137 execsql {138 DROP INDEX i2;139 SELECT y FROM t2 WHERE x=1;140 }141} {2}142do_test temptable-3.4 {143 execsql {144 CREATE INDEX i2 ON t2(x);145 DROP TABLE t2;146 }147 catchsql {DROP INDEX i2}148} {1 {no such index: i2}}149 150# Check for correct name collision processing. A name collision can151# occur when process A creates a temporary table T then process B152# creates a permanent table also named T. The temp table in process A153# hides the existence of the permanent table.154#155do_test temptable-4.1 {156 execsql {157 CREATE TEMP TABLE t2(x,y);158 INSERT INTO t2 VALUES(10,20);159 SELECT * FROM t2;160 } db2161} {10 20}162do_test temptable-4.2 {163 execsql {164 CREATE TABLE t2(x,y,z);165 INSERT INTO t2 VALUES(9,8,7);166 SELECT * FROM t2;167 }168} {9 8 7}169do_test temptable-4.3 {170 catchsql {171 SELECT * FROM t2;172 } db2173} {0 {10 20}}174do_test temptable-4.4.1 {175 catchsql {176 SELECT * FROM temp.t2;177 } db2178} {0 {10 20}}179do_test temptable-4.4.2 {180 catchsql {181 SELECT * FROM main.t2;182 } db2183} {0 {9 8 7}}184#do_test temptable-4.4.3 {185# catchsql {186# SELECT name FROM main.sqlite_master WHERE type='table';187# } db2188#} {1 {database schema has changed}}189do_test temptable-4.4.4 {190 catchsql {191 SELECT name FROM main.sqlite_master WHERE type='table';192 } db2193} {0 {t1 t2}}194do_test temptable-4.4.5 {195 catchsql {196 SELECT * FROM main.t2;197 } db2198} {0 {9 8 7}}199do_test temptable-4.4.6 {200 # TEMP takes precedence over MAIN201 catchsql {202 SELECT * FROM t2;203 } db2204} {0 {10 20}}205do_test temptable-4.5 {206 catchsql {207 DROP TABLE t2; -- should drop TEMP208 SELECT * FROM t2; -- data should be from MAIN209 } db2210} {0 {9 8 7}}211do_test temptable-4.6 {212 db2 close213 sqlite3 db2 ./test.db214 catchsql {215 SELECT * FROM t2;216 } db2217} {0 {9 8 7}}218do_test temptable-4.7 {219 catchsql {220 DROP TABLE t2;221 SELECT * FROM t2;222 }223} {1 {no such table: t2}}224do_test temptable-4.8 {225 db2 close226 sqlite3 db2 ./test.db227 execsql {228 CREATE TEMP TABLE t2(x unique,y);229 INSERT INTO t2 VALUES(1,2);230 SELECT * FROM t2;231 } db2232} {1 2}233do_test temptable-4.9 {234 execsql {235 CREATE TABLE t2(x unique, y);236 INSERT INTO t2 VALUES(3,4);237 SELECT * FROM t2;238 }239} {3 4}240do_test temptable-4.10.1 {241 catchsql {242 SELECT * FROM t2;243 } db2244} {0 {1 2}}245# Update: The schema is reloaded in test temptable-4.10.1. And tclsqlite.c246# handles it and retries the query anyway.247# do_test temptable-4.10.2 {248# catchsql {249# SELECT name FROM sqlite_master WHERE type='table'250# } db2251# } {1 {database schema has changed}}252do_test temptable-4.10.3 {253 catchsql {254 SELECT name FROM sqlite_master WHERE type='table'255 } db2256} {0 {t1 t2}}257do_test temptable-4.11 {258 execsql {259 SELECT * FROM t2;260 } db2261} {1 2}262do_test temptable-4.12 {263 execsql {264 SELECT * FROM t2;265 }266} {3 4}267do_test temptable-4.13 {268 catchsql {269 DROP TABLE t2; -- drops TEMP.T2270 SELECT * FROM t2; -- uses MAIN.T2271 } db2272} {0 {3 4}}273do_test temptable-4.14 {274 execsql {275 SELECT * FROM t2;276 }277} {3 4}278do_test temptable-4.15 {279 db2 close280 sqlite3 db2 ./test.db281 execsql {282 SELECT * FROM t2;283 } db2284} {3 4}285 286# Now create a temporary table in db2 and a permanent index in db. The287# temporary table in db2 should mask the name of the permanent index,288# but the permanent index should still be accessible and should still289# be updated when its corresponding table changes.290#291do_test temptable-5.1 {292 execsql {293 CREATE TEMP TABLE mask(a,b,c)294 } db2295 if {[permutation]=="prepare"} { db2 cache flush }296 execsql {297 CREATE INDEX mask ON t2(x);298 SELECT * FROM t2;299 }300} {3 4}301#do_test temptable-5.2 {302# catchsql {303# SELECT * FROM t2;304# } db2305#} {1 {database schema has changed}}306do_test temptable-5.3 {307 catchsql {308 SELECT * FROM t2;309 } db2310} {0 {3 4}}311do_test temptable-5.4 {312 execsql {313 SELECT y FROM t2 WHERE x=3314 }315} {4}316do_test temptable-5.5 {317 execsql {318 SELECT y FROM t2 WHERE x=3319 } db2320} {4}321do_test temptable-5.6 {322 execsql {323 INSERT INTO t2 VALUES(1,2);324 SELECT y FROM t2 WHERE x=1;325 } db2326} {2}327do_test temptable-5.7 {328 execsql {329 SELECT y FROM t2 WHERE x=3330 } db2331} {4}332do_test temptable-5.8 {333 execsql {334 SELECT y FROM t2 WHERE x=1;335 }336} {2}337do_test temptable-5.9 {338 execsql {339 SELECT y FROM t2 WHERE x=3340 }341} {4}342 343db2 close344 345# Test for correct operation of read-only databases346#347do_test temptable-6.1 {348 execsql {349 CREATE TABLE t8(x);350 INSERT INTO t8 VALUES('xyzzy');351 SELECT * FROM t8;352 }353} {xyzzy}354do_test temptable-6.2 {355 db close356 catch {file attributes test.db -permissions 0444}357 catch {file attributes test.db -readonly 1}358 sqlite3 db test.db359 if {[file writable test.db]} {360 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"361 }362 execsql {363 SELECT * FROM t8;364 }365} {xyzzy}366do_test temptable-6.3 {367 if {[file writable test.db]} {368 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"369 }370 catchsql {371 CREATE TABLE t9(x,y);372 }373} {1 {attempt to write a readonly database}}374do_test temptable-6.4 {375 catchsql {376 CREATE TEMP TABLE t9(x,y);377 }378} {0 {}}379do_test temptable-6.5 {380 catchsql {381 INSERT INTO t9 VALUES(1,2);382 SELECT * FROM t9;383 }384} {0 {1 2}}385do_test temptable-6.6 {386 if {[file writable test.db]} {387 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"388 }389 catchsql {390 INSERT INTO t8 VALUES('hello');391 SELECT * FROM t8;392 }393} {1 {attempt to write a readonly database}}394do_test temptable-6.7 {395 catchsql {396 SELECT * FROM t8,t9;397 }398} {0 {xyzzy 1 2}}399do_test temptable-6.8 {400 db close401 sqlite3 db test.db402 catchsql {403 SELECT * FROM t8,t9;404 }405} {1 {no such table: t9}}406 407forcedelete test2.db test2.db-journal408ifcapable attach {409 do_test temptable-7.1 {410 catchsql {411 ATTACH 'test2.db' AS two;412 CREATE TEMP TABLE two.abc(x,y);413 }414 } {1 {temporary table name must be unqualified}}415}416 417# Need to do the following for tcl 8.5 on mac. On that configuration, the418# -readonly flag is taken so seriously that a subsequent [forcedelete]419# (required before the next test file can be executed) will fail.420#421catch {file attributes test.db -readonly 0}422 423do_test temptable-8.0 {424 db close425 catch {forcedelete test.db}426 sqlite3 db test.db427} {}428do_test temptable-8.1 {429 execsql { CREATE TEMP TABLE tbl2(a, b); }430 execsql {431 CREATE TABLE tbl(a, b);432 INSERT INTO tbl VALUES(1, 2);433 }434 execsql {SELECT * FROM tbl}435} {1 2}436do_test temptable-8.2 {437 execsql { CREATE TEMP TABLE tbl(a, b); }438 execsql {SELECT * FROM tbl}439} {}440 441finish_test442