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