| blob | 6a1e2b9867901100d83b59f05cc40f9ab90e2238 |
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.21 2009/06/16 17:49:36 drh Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable !tempdb {
21 finish_test
22 return
23 }
25 # Create an alternative connection to the database
26 #
27 do_test temptable-1.0 {
28 sqlite3 db2 ./test.db
29 set dummy {}
30 } {}
32 # Create a permanent table.
33 #
34 do_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}
39 do_test temptable-1.2 {
40 catch {db2 eval {SELECT * FROM sqlite_master}}
41 db2 eval {SELECT * FROM t1}
42 } {1 2 3}
43 do_test temptable-1.3 {
44 execsql {SELECT name FROM sqlite_master}
45 } {t1}
46 do_test temptable-1.4 {
47 db2 eval {SELECT name FROM sqlite_master}
48 } {t1}
50 # Create a temporary table. Verify that only one of the two
51 # processes can see it.
52 #
53 do_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}
60 do_test temptable-1.6 {
61 catch {execsql {SELECT * FROM sqlite_master}}
62 catchsql {SELECT * FROM t2}
63 } {1 {no such table: t2}}
64 do_test temptable-1.7 {
65 catchsql {INSERT INTO t2 VALUES(8,9,0);}
66 } {1 {no such table: t2}}
67 do_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}
71 do_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}
75 do_test temptable-1.10 {
76 db2 eval {DELETE FROM t2}
77 db2 eval {SELECT * FROM t2}
78 } {}
79 do_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}
86 do_test temptable-1.12 {
87 db2 eval {DROP TABLE t2;}
88 set r [catch {db2 eval {SELECT * FROM t2}} msg]
89 lappend r $msg
90 } {1 {no such table: t2}}
92 # Make sure temporary tables work with transactions
93 #
94 do_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}
102 do_test temptable-2.2 {
103 execsql {ROLLBACK}
104 catchsql {SELECT * FROM t2}
105 } {1 {no such table: t2}}
106 do_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}
114 do_test temptable-2.4 {
115 execsql {COMMIT}
116 catchsql {SELECT * FROM t2}
117 } {0 {1 2}}
118 do_test temptable-2.5 {
119 set r [catch {db2 eval {SELECT * FROM t2}} msg]
120 lappend r $msg
121 } {1 {no such table: t2}}
123 # Make sure indices on temporary tables are also temporary.
124 #
125 do_test temptable-3.1 {
126 execsql {
127 CREATE INDEX i2 ON t2(x);
128 SELECT name FROM sqlite_master WHERE type='index';
129 }
130 } {}
131 do_test temptable-3.2 {
132 execsql {
133 SELECT y FROM t2 WHERE x=1;
134 }
135 } {2}
136 do_test temptable-3.3 {
137 execsql {
138 DROP INDEX i2;
139 SELECT y FROM t2 WHERE x=1;
140 }
141 } {2}
142 do_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}}
150 # Check for correct name collision processing. A name collision can
151 # occur when process A creates a temporary table T then process B
152 # creates a permanent table also named T. The temp table in process A
153 # hides the existence of the permanent table.
154 #
155 do_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 } db2
161 } {10 20}
162 do_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}
169 do_test temptable-4.3 {
170 catchsql {
171 SELECT * FROM t2;
172 } db2
173 } {0 {10 20}}
174 do_test temptable-4.4.1 {
175 catchsql {
176 SELECT * FROM temp.t2;
177 } db2
178 } {0 {10 20}}
179 do_test temptable-4.4.2 {
180 catchsql {
181 SELECT * FROM main.t2;
182 } db2
183 } {0 {9 8 7}}
184 #do_test temptable-4.4.3 {
185 # catchsql {
186 # SELECT name FROM main.sqlite_master WHERE type='table';
187 # } db2
188 #} {1 {database schema has changed}}
189 do_test temptable-4.4.4 {
190 catchsql {
191 SELECT name FROM main.sqlite_master WHERE type='table';
192 } db2
193 } {0 {t1 t2}}
194 do_test temptable-4.4.5 {
195 catchsql {
196 SELECT * FROM main.t2;
197 } db2
198 } {0 {9 8 7}}
199 do_test temptable-4.4.6 {
200 # TEMP takes precedence over MAIN
201 catchsql {
202 SELECT * FROM t2;
203 } db2
204 } {0 {10 20}}
205 do_test temptable-4.5 {
206 catchsql {
207 DROP TABLE t2; -- should drop TEMP
208 SELECT * FROM t2; -- data should be from MAIN
209 } db2
210 } {0 {9 8 7}}
211 do_test temptable-4.6 {
212 db2 close
213 sqlite3 db2 ./test.db
214 catchsql {
215 SELECT * FROM t2;
216 } db2
217 } {0 {9 8 7}}
218 do_test temptable-4.7 {
219 catchsql {
220 DROP TABLE t2;
221 SELECT * FROM t2;
222 }
223 } {1 {no such table: t2}}
224 do_test temptable-4.8 {
225 db2 close
226 sqlite3 db2 ./test.db
227 execsql {
228 CREATE TEMP TABLE t2(x unique,y);
229 INSERT INTO t2 VALUES(1,2);
230 SELECT * FROM t2;
231 } db2
232 } {1 2}
233 do_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}
240 do_test temptable-4.10.1 {
241 catchsql {
242 SELECT * FROM t2;
243 } db2
244 } {0 {1 2}}
245 # Update: The schema is reloaded in test temptable-4.10.1. And tclsqlite.c
246 # 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 # } db2
251 # } {1 {database schema has changed}}
252 do_test temptable-4.10.3 {
253 catchsql {
254 SELECT name FROM sqlite_master WHERE type='table'
255 } db2
256 } {0 {t1 t2}}
257 do_test temptable-4.11 {
258 execsql {
259 SELECT * FROM t2;
260 } db2
261 } {1 2}
262 do_test temptable-4.12 {
263 execsql {
264 SELECT * FROM t2;
265 }
266 } {3 4}
267 do_test temptable-4.13 {
268 catchsql {
269 DROP TABLE t2; -- drops TEMP.T2
270 SELECT * FROM t2; -- uses MAIN.T2
271 } db2
272 } {0 {3 4}}
273 do_test temptable-4.14 {
274 execsql {
275 SELECT * FROM t2;
276 }
277 } {3 4}
278 do_test temptable-4.15 {
279 db2 close
280 sqlite3 db2 ./test.db
281 execsql {
282 SELECT * FROM t2;
283 } db2
284 } {3 4}
286 # Now create a temporary table in db2 and a permanent index in db. The
287 # temporary table in db2 should mask the name of the permanent index,
288 # but the permanent index should still be accessible and should still
289 # be updated when its corresponding table changes.
290 #
291 do_test temptable-5.1 {
292 execsql {
293 CREATE TEMP TABLE mask(a,b,c)
294 } db2
295 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 # } db2
305 #} {1 {database schema has changed}}
306 do_test temptable-5.3 {
307 catchsql {
308 SELECT * FROM t2;
309 } db2
310 } {0 {3 4}}
311 do_test temptable-5.4 {
312 execsql {
313 SELECT y FROM t2 WHERE x=3
314 }
315 } {4}
316 do_test temptable-5.5 {
317 execsql {
318 SELECT y FROM t2 WHERE x=3
319 } db2
320 } {4}
321 do_test temptable-5.6 {
322 execsql {
323 INSERT INTO t2 VALUES(1,2);
324 SELECT y FROM t2 WHERE x=1;
325 } db2
326 } {2}
327 do_test temptable-5.7 {
328 execsql {
329 SELECT y FROM t2 WHERE x=3
330 } db2
331 } {4}
332 do_test temptable-5.8 {
333 execsql {
334 SELECT y FROM t2 WHERE x=1;
335 }
336 } {2}
337 do_test temptable-5.9 {
338 execsql {
339 SELECT y FROM t2 WHERE x=3
340 }
341 } {4}
343 db2 close
345 # Test for correct operation of read-only databases
346 #
347 do_test temptable-6.1 {
348 execsql {
349 CREATE TABLE t8(x);
350 INSERT INTO t8 VALUES('xyzzy');
351 SELECT * FROM t8;
352 }
353 } {xyzzy}
354 do_test temptable-6.2 {
355 db close
356 catch {file attributes test.db -permissions 0444}
357 catch {file attributes test.db -readonly 1}
358 sqlite3 db test.db
359 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}
366 do_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}}
374 do_test temptable-6.4 {
375 catchsql {
376 CREATE TEMP TABLE t9(x,y);
377 }
378 } {0 {}}
379 do_test temptable-6.5 {
380 catchsql {
381 INSERT INTO t9 VALUES(1,2);
382 SELECT * FROM t9;
383 }
384 } {0 {1 2}}
385 do_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}}
394 do_test temptable-6.7 {
395 catchsql {
396 SELECT * FROM t8,t9;
397 }
398 } {0 {xyzzy 1 2}}
399 do_test temptable-6.8 {
400 db close
401 sqlite3 db test.db
402 catchsql {
403 SELECT * FROM t8,t9;
404 }
405 } {1 {no such table: t9}}
407 forcedelete test2.db test2.db-journal
408 ifcapable 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 }
417 # Need to do the following for tcl 8.5 on mac. On that configuration, the
418 # -readonly flag is taken so seriously that a subsequent [forcedelete]
419 # (required before the next test file can be executed) will fail.
420 #
421 catch {file attributes test.db -readonly 0}
423 do_test temptable-8.0 {
424 db close
425 catch {forcedelete test.db}
426 sqlite3 db test.db
427 } {}
428 do_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}
436 do_test temptable-8.2 {
437 execsql { CREATE TEMP TABLE tbl(a, b); }
438 execsql {SELECT * FROM tbl}
439 } {}
441 finish_test