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.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: ifcapable !tempdb {
21: finish_test
22: return
23: }
24:
25: # Create an alternative connection to the database
26: #
27: do_test temptable-1.0 {
28: sqlite3 db2 ./test.db
29: set dummy {}
30: } {}
31:
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}
49:
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}}
91:
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}}
122:
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}}
149:
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 existance 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}
285:
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}
342:
343: db2 close
344:
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}}
406:
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: }
416:
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}
422:
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: } {}
440:
441: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to temptable.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test