1# 2# 2001 October 7 3# 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# This file implements regression tests for SQLite library. 13# 14# This file implements tests for temporary tables and indices. 15# 16# $Id: temptable.test,v 1.11 2004/02/14 16:31:04 drh Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Create an alternative connection to the database 22# 23do_test temptable-1.0 { 24 sqlite db2 ./test.db 25 set dummy {} 26} {} 27 28# Create a permanent table. 29# 30do_test temptable-1.1 { 31 execsql {CREATE TABLE t1(a,b,c);} 32 execsql {INSERT INTO t1 VALUES(1,2,3);} 33 execsql {SELECT * FROM t1} 34} {1 2 3} 35do_test temptable-1.2 { 36 catch {db2 eval {SELECT * FROM sqlite_master}} 37 db2 eval {SELECT * FROM t1} 38} {1 2 3} 39do_test temptable-1.3 { 40 execsql {SELECT name FROM sqlite_master} 41} {t1} 42do_test temptable-1.4 { 43 db2 eval {SELECT name FROM sqlite_master} 44} {t1} 45 46# Create a temporary table. Verify that only one of the two 47# processes can see it. 48# 49do_test temptable-1.5 { 50 db2 eval { 51 CREATE TEMP TABLE t2(x,y,z); 52 INSERT INTO t2 VALUES(4,5,6); 53 } 54 db2 eval {SELECT * FROM t2} 55} {4 5 6} 56do_test temptable-1.6 { 57 catch {execsql {SELECT * FROM sqlite_master}} 58 catchsql {SELECT * FROM t2} 59} {1 {no such table: t2}} 60do_test temptable-1.7 { 61 catchsql {INSERT INTO t2 VALUES(8,9,0);} 62} {1 {no such table: t2}} 63do_test temptable-1.8 { 64 db2 eval {INSERT INTO t2 VALUES(8,9,0);} 65 db2 eval {SELECT * FROM t2 ORDER BY x} 66} {4 5 6 8 9 0} 67do_test temptable-1.9 { 68 db2 eval {DELETE FROM t2 WHERE x==8} 69 db2 eval {SELECT * FROM t2 ORDER BY x} 70} {4 5 6} 71do_test temptable-1.10 { 72 db2 eval {DELETE FROM t2} 73 db2 eval {SELECT * FROM t2} 74} {} 75do_test temptable-1.11 { 76 db2 eval { 77 INSERT INTO t2 VALUES(7,6,5); 78 INSERT INTO t2 VALUES(4,3,2); 79 SELECT * FROM t2 ORDER BY x; 80 } 81} {4 3 2 7 6 5} 82do_test temptable-1.12 { 83 db2 eval {DROP TABLE t2;} 84 set r [catch {db2 eval {SELECT * FROM t2}} msg] 85 lappend r $msg 86} {1 {no such table: t2}} 87 88# Make sure temporary tables work with transactions 89# 90do_test temptable-2.1 { 91 execsql { 92 BEGIN TRANSACTION; 93 CREATE TEMPORARY TABLE t2(x,y); 94 INSERT INTO t2 VALUES(1,2); 95 SELECT * FROM t2; 96 } 97} {1 2} 98do_test temptable-2.2 { 99 execsql {ROLLBACK} 100 catchsql {SELECT * FROM t2} 101} {1 {no such table: t2}} 102do_test temptable-2.3 { 103 execsql { 104 BEGIN TRANSACTION; 105 CREATE TEMPORARY TABLE t2(x,y); 106 INSERT INTO t2 VALUES(1,2); 107 SELECT * FROM t2; 108 } 109} {1 2} 110do_test temptable-2.4 { 111 execsql {COMMIT} 112 catchsql {SELECT * FROM t2} 113} {0 {1 2}} 114do_test temptable-2.5 { 115 set r [catch {db2 eval {SELECT * FROM t2}} msg] 116 lappend r $msg 117} {1 {no such table: t2}} 118 119# Make sure indices on temporary tables are also temporary. 120# 121do_test temptable-3.1 { 122 execsql { 123 CREATE INDEX i2 ON t2(x); 124 SELECT name FROM sqlite_master WHERE type='index'; 125 } 126} {} 127do_test temptable-3.2 { 128 execsql { 129 SELECT y FROM t2 WHERE x=1; 130 } 131} {2} 132do_test temptable-3.3 { 133 execsql { 134 DROP INDEX i2; 135 SELECT y FROM t2 WHERE x=1; 136 } 137} {2} 138do_test temptable-3.4 { 139 execsql { 140 CREATE INDEX i2 ON t2(x); 141 DROP TABLE t2; 142 } 143 catchsql {DROP INDEX i2} 144} {1 {no such index: i2}} 145 146# Check for correct name collision processing. A name collision can 147# occur when process A creates a temporary table T then process B 148# creates a permanent table also named T. The temp table in process A 149# hides the existance of the permanent table. 150# 151do_test temptable-4.1 { 152 execsql { 153 CREATE TEMP TABLE t2(x,y); 154 INSERT INTO t2 VALUES(10,20); 155 SELECT * FROM t2; 156 } db2 157} {10 20} 158do_test temptable-4.2 { 159 execsql { 160 CREATE TABLE t2(x,y,z); 161 INSERT INTO t2 VALUES(9,8,7); 162 SELECT * FROM t2; 163 } 164} {9 8 7} 165do_test temptable-4.3 { 166 catchsql { 167 SELECT * FROM t2; 168 } db2 169} {0 {10 20}} 170do_test temptable-4.4.1 { 171 catchsql { 172 SELECT * FROM temp.t2; 173 } db2 174} {0 {10 20}} 175do_test temptable-4.4.2 { 176 catchsql { 177 SELECT * FROM main.t2; 178 } db2 179} {1 {no such table: main.t2}} 180#do_test temptable-4.4.3 { 181# catchsql { 182# SELECT name FROM main.sqlite_master WHERE type='table'; 183# } db2 184#} {1 {database schema has changed}} 185do_test temptable-4.4.4 { 186 catchsql { 187 SELECT name FROM main.sqlite_master WHERE type='table'; 188 } db2 189} {0 {t1 t2}} 190do_test temptable-4.4.5 { 191 catchsql { 192 SELECT * FROM main.t2; 193 } db2 194} {0 {9 8 7}} 195do_test temptable-4.4.6 { 196 # TEMP takes precedence over MAIN 197 catchsql { 198 SELECT * FROM t2; 199 } db2 200} {0 {10 20}} 201do_test temptable-4.5 { 202 catchsql { 203 DROP TABLE t2; -- should drop TEMP 204 SELECT * FROM t2; -- data should be from MAIN 205 } db2 206} {0 {9 8 7}} 207do_test temptable-4.6 { 208 db2 close 209 sqlite db2 ./test.db 210 catchsql { 211 SELECT * FROM t2; 212 } db2 213} {0 {9 8 7}} 214do_test temptable-4.7 { 215 catchsql { 216 DROP TABLE t2; 217 SELECT * FROM t2; 218 } 219} {1 {no such table: t2}} 220do_test temptable-4.8 { 221 db2 close 222 sqlite db2 ./test.db 223 execsql { 224 CREATE TEMP TABLE t2(x unique,y); 225 INSERT INTO t2 VALUES(1,2); 226 SELECT * FROM t2; 227 } db2 228} {1 2} 229do_test temptable-4.9 { 230 execsql { 231 CREATE TABLE t2(x unique, y); 232 INSERT INTO t2 VALUES(3,4); 233 SELECT * FROM t2; 234 } 235} {3 4} 236do_test temptable-4.10.1 { 237 catchsql { 238 SELECT * FROM t2; 239 } db2 240} {0 {1 2}} 241#do_test temptable-4.10.2 { 242# catchsql { 243# SELECT name FROM sqlite_master WHERE type='table' 244# } db2 245#} {1 {database schema has changed}} 246do_test temptable-4.10.3 { 247 catchsql { 248 SELECT name FROM sqlite_master WHERE type='table' 249 } db2 250} {0 {t1 t2}} 251do_test temptable-4.11 { 252 execsql { 253 SELECT * FROM t2; 254 } db2 255} {1 2} 256do_test temptable-4.12 { 257 execsql { 258 SELECT * FROM t2; 259 } 260} {3 4} 261do_test temptable-4.13 { 262 catchsql { 263 DROP TABLE t2; -- drops TEMP.T2 264 SELECT * FROM t2; -- uses MAIN.T2 265 } db2 266} {0 {3 4}} 267do_test temptable-4.14 { 268 execsql { 269 SELECT * FROM t2; 270 } 271} {3 4} 272do_test temptable-4.15 { 273 db2 close 274 sqlite db2 ./test.db 275 execsql { 276 SELECT * FROM t2; 277 } db2 278} {3 4} 279 280# Now create a temporary table in db2 and a permanent index in db. The 281# temporary table in db2 should mask the name of the permanent index, 282# but the permanent index should still be accessible and should still 283# be updated when its corresponding table changes. 284# 285do_test temptable-5.1 { 286 execsql { 287 CREATE TEMP TABLE mask(a,b,c) 288 } db2 289 execsql { 290 CREATE INDEX mask ON t2(x); 291 SELECT * FROM t2; 292 } 293} {3 4} 294#do_test temptable-5.2 { 295# catchsql { 296# SELECT * FROM t2; 297# } db2 298#} {1 {database schema has changed}} 299do_test temptable-5.3 { 300 catchsql { 301 SELECT * FROM t2; 302 } db2 303} {0 {3 4}} 304do_test temptable-5.4 { 305 execsql { 306 SELECT y FROM t2 WHERE x=3 307 } 308} {4} 309do_test temptable-5.5 { 310 execsql { 311 SELECT y FROM t2 WHERE x=3 312 } db2 313} {4} 314do_test temptable-5.6 { 315 execsql { 316 INSERT INTO t2 VALUES(1,2); 317 SELECT y FROM t2 WHERE x=1; 318 } db2 319} {2} 320do_test temptable-5.7 { 321 execsql { 322 SELECT y FROM t2 WHERE x=3 323 } db2 324} {4} 325do_test temptable-5.8 { 326 execsql { 327 SELECT y FROM t2 WHERE x=1; 328 } 329} {2} 330do_test temptable-5.9 { 331 execsql { 332 SELECT y FROM t2 WHERE x=3 333 } 334} {4} 335 336db2 close 337 338# Test for correct operation of read-only databases 339# 340do_test temptable-6.1 { 341 execsql { 342 CREATE TABLE t8(x); 343 INSERT INTO t8 VALUES('xyzzy'); 344 SELECT * FROM t8; 345 } 346} {xyzzy} 347do_test temptable-6.2 { 348 db close 349 catch {file attributes test.db -permissions 0444} 350 catch {file attributes test.db -readonly 1} 351 sqlite db test.db 352 if {[file writable test.db]} { 353 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user" 354 } 355 execsql { 356 SELECT * FROM t8; 357 } 358} {xyzzy} 359do_test temptable-6.3 { 360 if {[file writable test.db]} { 361 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user" 362 } 363 catchsql { 364 CREATE TABLE t9(x,y); 365 } 366} {1 {attempt to write a readonly database}} 367do_test temptable-6.4 { 368 catchsql { 369 CREATE TEMP TABLE t9(x,y); 370 } 371} {0 {}} 372do_test temptable-6.5 { 373 catchsql { 374 INSERT INTO t9 VALUES(1,2); 375 SELECT * FROM t9; 376 } 377} {0 {1 2}} 378do_test temptable-6.6 { 379 if {[file writable test.db]} { 380 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user" 381 } 382 catchsql { 383 INSERT INTO t8 VALUES('hello'); 384 SELECT * FROM t8; 385 } 386} {1 {attempt to write a readonly database}} 387do_test temptable-6.7 { 388 catchsql { 389 SELECT * FROM t8,t9; 390 } 391} {0 {xyzzy 1 2}} 392do_test temptable-6.8 { 393 db close 394 sqlite db test.db 395 catchsql { 396 SELECT * FROM t8,t9; 397 } 398} {1 {no such table: t9}} 399 400finish_test 401