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