wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 $
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 execsql {
296 CREATE INDEX mask ON t2(x);
297 SELECT * FROM t2;
298 }
299 } {3 4}
300 #do_test temptable-5.2 {
301 # catchsql {
302 # SELECT * FROM t2;
303 # } db2
304 #} {1 {database schema has changed}}
305 do_test temptable-5.3 {
306 catchsql {
307 SELECT * FROM t2;
308 } db2
309 } {0 {3 4}}
310 do_test temptable-5.4 {
311 execsql {
312 SELECT y FROM t2 WHERE x=3
313 }
314 } {4}
315 do_test temptable-5.5 {
316 execsql {
317 SELECT y FROM t2 WHERE x=3
318 } db2
319 } {4}
320 do_test temptable-5.6 {
321 execsql {
322 INSERT INTO t2 VALUES(1,2);
323 SELECT y FROM t2 WHERE x=1;
324 } db2
325 } {2}
326 do_test temptable-5.7 {
327 execsql {
328 SELECT y FROM t2 WHERE x=3
329 } db2
330 } {4}
331 do_test temptable-5.8 {
332 execsql {
333 SELECT y FROM t2 WHERE x=1;
334 }
335 } {2}
336 do_test temptable-5.9 {
337 execsql {
338 SELECT y FROM t2 WHERE x=3
339 }
340 } {4}
341  
342 db2 close
343  
344 # Test for correct operation of read-only databases
345 #
346 do_test temptable-6.1 {
347 execsql {
348 CREATE TABLE t8(x);
349 INSERT INTO t8 VALUES('xyzzy');
350 SELECT * FROM t8;
351 }
352 } {xyzzy}
353 do_test temptable-6.2 {
354 db close
355 catch {file attributes test.db -permissions 0444}
356 catch {file attributes test.db -readonly 1}
357 sqlite3 db test.db
358 if {[file writable test.db]} {
359 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
360 }
361 execsql {
362 SELECT * FROM t8;
363 }
364 } {xyzzy}
365 do_test temptable-6.3 {
366 if {[file writable test.db]} {
367 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
368 }
369 catchsql {
370 CREATE TABLE t9(x,y);
371 }
372 } {1 {attempt to write a readonly database}}
373 do_test temptable-6.4 {
374 catchsql {
375 CREATE TEMP TABLE t9(x,y);
376 }
377 } {0 {}}
378 do_test temptable-6.5 {
379 catchsql {
380 INSERT INTO t9 VALUES(1,2);
381 SELECT * FROM t9;
382 }
383 } {0 {1 2}}
384 do_test temptable-6.6 {
385 if {[file writable test.db]} {
386 error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
387 }
388 catchsql {
389 INSERT INTO t8 VALUES('hello');
390 SELECT * FROM t8;
391 }
392 } {1 {attempt to write a readonly database}}
393 do_test temptable-6.7 {
394 catchsql {
395 SELECT * FROM t8,t9;
396 }
397 } {0 {xyzzy 1 2}}
398 do_test temptable-6.8 {
399 db close
400 sqlite3 db test.db
401 catchsql {
402 SELECT * FROM t8,t9;
403 }
404 } {1 {no such table: t9}}
405  
406 file delete -force test2.db test2.db-journal
407 ifcapable attach {
408 do_test temptable-7.1 {
409 catchsql {
410 ATTACH 'test2.db' AS two;
411 CREATE TEMP TABLE two.abc(x,y);
412 }
413 } {1 {temporary table name must be unqualified}}
414 }
415  
416 # Need to do the following for tcl 8.5 on mac. On that configuration, the
417 # -readonly flag is taken so seriously that a subsequent [file delete -force]
418 # (required before the next test file can be executed) will fail.
419 #
420 catch {file attributes test.db -readonly 0}
421  
422 do_test temptable-8.0 {
423 db close
424 catch {file delete -force test.db}
425 sqlite3 db test.db
426 } {}
427 do_test temptable-8.1 {
428 execsql { CREATE TEMP TABLE tbl2(a, b); }
429 execsql {
430 CREATE TABLE tbl(a, b);
431 INSERT INTO tbl VALUES(1, 2);
432 }
433 execsql {SELECT * FROM tbl}
434 } {1 2}
435 do_test temptable-8.2 {
436 execsql { CREATE TEMP TABLE tbl(a, b); }
437 execsql {SELECT * FROM tbl}
438 } {}
439  
440 finish_test