| blob | 93ff24bc6cb16b773ac01f6aa9ea84d63a3fbae9 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
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 $
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
23 # Create an alternative connection to the database
24 #
25 do_test temptable-1.0 {
26 sqlite db2 ./test.db
27 set dummy {}
28 } {}
30 # Create a permanent table.
31 #
32 do_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}
37 do_test temptable-1.2 {
38 catch {db2 eval {SELECT * FROM sqlite_master}}
39 db2 eval {SELECT * FROM t1}
40 } {1 2 3}
41 do_test temptable-1.3 {
42 execsql {SELECT name FROM sqlite_master}
43 } {t1}
44 do_test temptable-1.4 {
45 db2 eval {SELECT name FROM sqlite_master}
46 } {t1}
48 # Create a temporary table. Verify that only one of the two
49 # processes can see it.
50 #
51 do_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}
58 do_test temptable-1.6 {
59 catch {execsql {SELECT * FROM sqlite_master}}
60 catchsql {SELECT * FROM t2}
61 } {1 {no such table: t2}}
62 do_test temptable-1.7 {
63 catchsql {INSERT INTO t2 VALUES(8,9,0);}
64 } {1 {no such table: t2}}
65 do_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}
69 do_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}
73 do_test temptable-1.10 {
74 db2 eval {DELETE FROM t2}
75 db2 eval {SELECT * FROM t2}
76 } {}
77 do_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}
84 do_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}}
90 # Make sure temporary tables work with transactions
91 #
92 do_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}
100 do_test temptable-2.2 {
101 execsql {ROLLBACK}
102 catchsql {SELECT * FROM t2}
103 } {1 {no such table: t2}}
104 do_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}
112 do_test temptable-2.4 {
113 execsql {COMMIT}
114 catchsql {SELECT * FROM t2}
115 } {0 {1 2}}
116 do_test temptable-2.5 {
117 set r [catch {db2 eval {SELECT * FROM t2}} msg]
118 lappend r $msg
119 } {1 {no such table: t2}}
121 # Make sure indices on temporary tables are also temporary.
122 #
123 do_test temptable-3.1 {
124 execsql {
125 CREATE INDEX i2 ON t2(x);
126 SELECT name FROM sqlite_master WHERE type='index';
127 }
128 } {}
129 do_test temptable-3.2 {
130 execsql {
131 SELECT y FROM t2 WHERE x=1;
132 }
133 } {2}
134 do_test temptable-3.3 {
135 execsql {
136 DROP INDEX i2;
137 SELECT y FROM t2 WHERE x=1;
138 }
139 } {2}
140 do_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}}
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 #
153 do_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}
160 do_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}
167 do_test temptable-4.3 {
168 catchsql {
169 SELECT * FROM t2;
170 } db2
171 } {0 {10 20}}
172 do_test temptable-4.4.1 {
173 catchsql {
174 SELECT * FROM temp.t2;
175 } db2
176 } {0 {10 20}}
177 do_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}}
187 do_test temptable-4.4.4 {
188 catchsql {
189 SELECT name FROM main.sqlite_master WHERE type='table';
190 } db2
191 } {0 {t1 t2}}
192 do_test temptable-4.4.5 {
193 catchsql {
194 SELECT * FROM main.t2;
195 } db2
196 } {0 {9 8 7}}
197 do_test temptable-4.4.6 {
198 # TEMP takes precedence over MAIN
199 catchsql {
200 SELECT * FROM t2;
201 } db2
202 } {0 {10 20}}
203 do_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}}
209 do_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}}
216 do_test temptable-4.7 {
217 catchsql {
218 DROP TABLE t2;
219 SELECT * FROM t2;
220 }
221 } {1 {no such table: t2}}
222 do_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}
231 do_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}
238 do_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}}
248 do_test temptable-4.10.3 {
249 catchsql {
250 SELECT name FROM sqlite_master WHERE type='table'
251 } db2
252 } {0 {t1 t2}}
253 do_test temptable-4.11 {
254 execsql {
255 SELECT * FROM t2;
256 } db2
257 } {1 2}
258 do_test temptable-4.12 {
259 execsql {
260 SELECT * FROM t2;
261 }
262 } {3 4}
263 do_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}}
269 do_test temptable-4.14 {
270 execsql {
271 SELECT * FROM t2;
272 }
273 } {3 4}
274 do_test temptable-4.15 {
275 db2 close
276 sqlite db2 ./test.db
277 execsql {
278 SELECT * FROM t2;
279 } db2
280 } {3 4}
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 #
287 do_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}}
301 do_test temptable-5.3 {
302 catchsql {
303 SELECT * FROM t2;
304 } db2
305 } {0 {3 4}}
306 do_test temptable-5.4 {
307 execsql {
308 SELECT y FROM t2 WHERE x=3
309 }
310 } {4}
311 do_test temptable-5.5 {
312 execsql {
313 SELECT y FROM t2 WHERE x=3
314 } db2
315 } {4}
316 do_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}
322 do_test temptable-5.7 {
323 execsql {
324 SELECT y FROM t2 WHERE x=3
325 } db2
326 } {4}
327 do_test temptable-5.8 {
328 execsql {
329 SELECT y FROM t2 WHERE x=1;
330 }
331 } {2}
332 do_test temptable-5.9 {
333 execsql {
334 SELECT y FROM t2 WHERE x=3
335 }
336 } {4}
338 db2 close
340 # Test for correct operation of read-only databases
341 #
342 do_test temptable-6.1 {
343 execsql {
344 CREATE TABLE t8(x);
345 INSERT INTO t8 VALUES('xyzzy');
346 SELECT * FROM t8;
347 }
348 } {xyzzy}
349 do_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}
361 do_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}}
369 do_test temptable-6.4 {
370 catchsql {
371 CREATE TEMP TABLE t9(x,y);
372 }
373 } {0 {}}
374 do_test temptable-6.5 {
375 catchsql {
376 INSERT INTO t9 VALUES(1,2);
377 SELECT * FROM t9;
378 }
379 } {0 {1 2}}
380 do_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}}
389 do_test temptable-6.7 {
390 catchsql {
391 SELECT * FROM t8,t9;
392 }
393 } {0 {xyzzy 1 2}}
394 do_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}}
402 finish_test