1# 2001 October 7 2# 3# The author disclaims copyright to this source code. In place of 4# 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.4 2002/01/10 14:31:49 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Create an alternative connection to the database 21# 22do_test temptable-1.0 { 23 sqlite db2 ./test.db 24} {} 25 26# Create a permanent table. 27# 28do_test temptable-1.1 { 29 execsql {CREATE TABLE t1(a,b,c);} 30 execsql {INSERT INTO t1 VALUES(1,2,3);} 31 execsql {SELECT * FROM t1} 32} {1 2 3} 33do_test temptable-1.2 { 34 catch {db2 eval {SELECT * FROM sqlite_master}} 35 db2 eval {SELECT * FROM t1} 36} {1 2 3} 37do_test temptable-1.3 { 38 execsql {SELECT name FROM sqlite_master} 39} {t1} 40do_test temptable-1.4 { 41 db2 eval {SELECT name FROM sqlite_master} 42} {t1} 43 44# Create a temporary table. Verify that only one of the two 45# processes can see it. 46# 47do_test temptable-1.5 { 48 db2 eval { 49 CREATE TEMP TABLE t2(x,y,z); 50 INSERT INTO t2 VALUES(4,5,6); 51 } 52 db2 eval {SELECT * FROM t2} 53} {4 5 6} 54do_test temptable-1.6 { 55 catch {execsql {SELECT * FROM sqlite_master}} 56 catchsql {SELECT * FROM t2} 57} {1 {no such table: t2}} 58do_test temptable-1.7 { 59 catchsql {INSERT INTO t2 VALUES(8,9,0);} 60} {1 {no such table: t2}} 61do_test temptable-1.8 { 62 db2 eval {INSERT INTO t2 VALUES(8,9,0);} 63 db2 eval {SELECT * FROM t2 ORDER BY x} 64} {4 5 6 8 9 0} 65do_test temptable-1.9 { 66 db2 eval {DELETE FROM t2 WHERE x==8} 67 db2 eval {SELECT * FROM t2 ORDER BY x} 68} {4 5 6} 69do_test temptable-1.10 { 70 db2 eval {DELETE FROM t2} 71 db2 eval {SELECT * FROM t2} 72} {} 73do_test temptable-1.11 { 74 db2 eval { 75 INSERT INTO t2 VALUES(7,6,5); 76 INSERT INTO t2 VALUES(4,3,2); 77 SELECT * FROM t2 ORDER BY x; 78 } 79} {4 3 2 7 6 5} 80do_test temptable-1.12 { 81 db2 eval {DROP TABLE t2;} 82 set r [catch {db2 eval {SELECT * FROM t2}} msg] 83 lappend r $msg 84} {1 {no such table: t2}} 85 86# Make sure temporary tables work with transactions 87# 88do_test temptable-2.1 { 89 execsql { 90 BEGIN TRANSACTION; 91 CREATE TEMPORARY TABLE t2(x,y); 92 INSERT INTO t2 VALUES(1,2); 93 SELECT * FROM t2; 94 } 95} {1 2} 96do_test temptable-2.2 { 97 execsql {ROLLBACK} 98 catchsql {SELECT * FROM t2} 99} {1 {no such table: t2}} 100do_test temptable-2.3 { 101 execsql { 102 BEGIN TRANSACTION; 103 CREATE TEMPORARY TABLE t2(x,y); 104 INSERT INTO t2 VALUES(1,2); 105 SELECT * FROM t2; 106 } 107} {1 2} 108do_test temptable-2.4 { 109 execsql {COMMIT} 110 catchsql {SELECT * FROM t2} 111} {0 {1 2}} 112do_test temptable-2.5 { 113 set r [catch {db2 eval {SELECT * FROM t2}} msg] 114 lappend r $msg 115} {1 {no such table: t2}} 116 117 118# Make sure indices on temporary tables are also temporary. 119# 120do_test temptable-3.1 { 121 execsql { 122 CREATE INDEX i2 ON t2(x); 123 SELECT name FROM sqlite_master WHERE type='index'; 124 } 125} {} 126do_test temptable-3.2 { 127 execsql { 128 SELECT y FROM t2 WHERE x=1; 129 } 130} {2} 131do_test temptable-3.3 { 132 execsql { 133 DROP INDEX i2; 134 SELECT y FROM t2 WHERE x=1; 135 } 136} {2} 137do_test temptable-3.4 { 138 execsql { 139 CREATE INDEX i2 ON t2(x); 140 DROP TABLE t2; 141 } 142 catchsql {DROP INDEX i2} 143} {1 {no such index: i2}} 144 145# Check for correct name collision processing. A name collision can 146# occur when process A creates a temporary table T then process B 147# creates a permanent table also named T. The temp table in process A 148# hides the existance of the permanent table. 149# 150do_test temptable-4.1 { 151 execsql { 152 CREATE TEMP TABLE t2(x,y); 153 INSERT INTO t2 VALUES(10,20); 154 SELECT * FROM t2; 155 } db2 156} {10 20} 157do_test temptable-4.2 { 158 execsql { 159 CREATE TABLE t2(x,y,z); 160 INSERT INTO t2 VALUES(9,8,7); 161 SELECT * FROM t2; 162 } 163} {9 8 7} 164do_test temptable-4.3 { 165 catchsql { 166 SELECT * FROM t2; 167 } db2 168} {1 {database schema has changed}} 169do_test temptable-4.4 { 170 catchsql { 171 SELECT * FROM t2; 172 } db2 173} {0 {10 20}} 174do_test temptable-4.5 { 175 catchsql { 176 DROP TABLE t2; 177 SELECT * FROM t2; 178 } db2 179} {1 {no such table: t2}} 180do_test temptable-4.6 { 181 db2 close 182 sqlite db2 ./test.db 183 catchsql { 184 SELECT * FROM t2; 185 } db2 186} {0 {9 8 7}} 187do_test temptable-4.7 { 188 catchsql { 189 DROP TABLE t2; 190 SELECT * FROM t2; 191 } 192} {1 {no such table: t2}} 193do_test temptable-4.8 { 194 db2 close 195 sqlite db2 ./test.db 196 execsql { 197 CREATE TEMP TABLE t2(x unique,y); 198 INSERT INTO t2 VALUES(1,2); 199 SELECT * FROM t2; 200 } db2 201} {1 2} 202do_test temptable-4.9 { 203 execsql { 204 CREATE TABLE t2(x unique, y); 205 INSERT INTO t2 VALUES(3,4); 206 SELECT * FROM t2; 207 } 208} {3 4} 209do_test temptable-4.10 { 210 catchsql { 211 SELECT * FROM t2; 212 } db2 213} {1 {database schema has changed}} 214do_test temptable-4.11 { 215 execsql { 216 SELECT * FROM t2; 217 } db2 218} {1 2} 219do_test temptable-4.12 { 220 execsql { 221 SELECT * FROM t2; 222 } 223} {3 4} 224do_test temptable-4.13 { 225 catchsql { 226 DROP TABLE t2; 227 SELECT * FROM t2; 228 } db2 229} {1 {no such table: t2}} 230do_test temptable-4.14 { 231 execsql { 232 SELECT * FROM t2; 233 } 234} {3 4} 235do_test temptable-4.15 { 236 db2 close 237 sqlite db2 ./test.db 238 execsql { 239 SELECT * FROM t2; 240 } db2 241} {3 4} 242 243# Now create a temporary table in db2 and a permanent index in db. The 244# temporary table in db2 should mask the name of the permanent index, 245# but the permanent index should still be accessible and should still 246# be updated when its corresponding table changes. 247# 248do_test temptable-5.1 { 249 execsql { 250 CREATE TEMP TABLE mask(a,b,c) 251 } db2 252 execsql { 253 CREATE INDEX mask ON t2(x); 254 SELECT * FROM t2; 255 } 256} {3 4} 257do_test temptable-5.2 { 258 catchsql { 259 SELECT * FROM t2; 260 } db2 261} {1 {database schema has changed}} 262do_test temptable-5.3 { 263 catchsql { 264 SELECT * FROM t2; 265 } db2 266} {0 {3 4}} 267do_test temptable-5.4 { 268 execsql { 269 SELECT y FROM t2 WHERE x=3 270 } 271} {4} 272do_test temptable-5.5 { 273 execsql { 274 SELECT y FROM t2 WHERE x=3 275 } db2 276} {4} 277do_test temptable-5.6 { 278 execsql { 279 INSERT INTO t2 VALUES(1,2); 280 SELECT y FROM t2 WHERE x=1; 281 } db2 282} {2} 283do_test temptable-5.7 { 284 execsql { 285 SELECT y FROM t2 WHERE x=3 286 } db2 287} {4} 288do_test temptable-5.8 { 289 execsql { 290 SELECT y FROM t2 WHERE x=1; 291 } 292} {2} 293do_test temptable-5.9 { 294 execsql { 295 SELECT y FROM t2 WHERE x=3 296 } 297} {4} 298 299db2 close 300 301finish_test 302