xref: /illumos-gate/usr/src/lib/libsqlite/test/pragma.test (revision 533affcbc7fc4d0c8132976ea454aaa715fe2307) 
1#
2# 2002 March 6
3#
4# The author disclaims copyright to this source code.  In place of
5# a legal notice, here is a blessing:
6#
7#    May you do good and not evil.
8#    May you find forgiveness for yourself and forgive others.
9#    May you share freely, never taking more than you give.
10#
11#***********************************************************************
12# This file implements regression tests for SQLite library.
13#
14# This file implements tests for the PRAGMA command.
15#
16# $Id: pragma.test,v 1.9 2004/04/23 17:04:45 drh Exp $
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21# Delete the preexisting database to avoid the special setup
22# that the "all.test" script does.
23#
24db close
25file delete test.db
26set DB [sqlite db test.db]
27
28do_test pragma-1.1 {
29  execsql {
30    PRAGMA cache_size;
31    PRAGMA default_cache_size;
32    PRAGMA synchronous;
33    PRAGMA default_synchronous;
34  }
35} {2000 2000 1 1}
36do_test pragma-1.2 {
37  execsql {
38    PRAGMA cache_size=1234;
39    PRAGMA cache_size;
40    PRAGMA default_cache_size;
41    PRAGMA synchronous;
42    PRAGMA default_synchronous;
43  }
44} {1234 2000 1 1}
45do_test pragma-1.3 {
46  db close
47  sqlite db test.db
48  execsql {
49    PRAGMA cache_size;
50    PRAGMA default_cache_size;
51    PRAGMA synchronous;
52    PRAGMA default_synchronous;
53  }
54} {2000 2000 1 1}
55do_test pragma-1.4 {
56  execsql {
57    PRAGMA synchronous=OFF;
58    PRAGMA cache_size;
59    PRAGMA default_cache_size;
60    PRAGMA synchronous;
61    PRAGMA default_synchronous;
62  }
63} {2000 2000 0 1}
64do_test pragma-1.5 {
65  execsql {
66    PRAGMA cache_size=4321;
67    PRAGMA cache_size;
68    PRAGMA default_cache_size;
69    PRAGMA synchronous;
70    PRAGMA default_synchronous;
71  }
72} {4321 2000 0 1}
73do_test pragma-1.6 {
74  execsql {
75    PRAGMA synchronous=ON;
76    PRAGMA cache_size;
77    PRAGMA default_cache_size;
78    PRAGMA synchronous;
79    PRAGMA default_synchronous;
80  }
81} {4321 2000 1 1}
82do_test pragma-1.7 {
83  db close
84  sqlite db test.db
85  execsql {
86    PRAGMA cache_size;
87    PRAGMA default_cache_size;
88    PRAGMA synchronous;
89    PRAGMA default_synchronous;
90  }
91} {2000 2000 1 1}
92do_test pragma-1.8 {
93  execsql {
94    PRAGMA default_synchronous=OFF;
95    PRAGMA cache_size;
96    PRAGMA default_cache_size;
97    PRAGMA synchronous;
98    PRAGMA default_synchronous;
99  }
100} {2000 2000 0 0}
101do_test pragma-1.9 {
102  execsql {
103    PRAGMA default_cache_size=123;
104    PRAGMA cache_size;
105    PRAGMA default_cache_size;
106    PRAGMA synchronous;
107    PRAGMA default_synchronous;
108  }
109} {123 123 0 0}
110do_test pragma-1.10 {
111  db close
112  set ::DB [sqlite db test.db]
113  execsql {
114    PRAGMA cache_size;
115    PRAGMA default_cache_size;
116    PRAGMA synchronous;
117    PRAGMA default_synchronous;
118  }
119} {123 123 0 0}
120do_test pragma-1.11 {
121  execsql {
122    PRAGMA synchronous=NORMAL;
123    PRAGMA cache_size;
124    PRAGMA default_cache_size;
125    PRAGMA synchronous;
126    PRAGMA default_synchronous;
127  }
128} {123 123 1 0}
129do_test pragma-1.12 {
130  execsql {
131    PRAGMA synchronous=FULL;
132    PRAGMA cache_size;
133    PRAGMA default_cache_size;
134    PRAGMA synchronous;
135    PRAGMA default_synchronous;
136  }
137} {123 123 2 0}
138do_test pragma-1.13 {
139  db close
140  set ::DB [sqlite db test.db]
141  execsql {
142    PRAGMA cache_size;
143    PRAGMA default_cache_size;
144    PRAGMA synchronous;
145    PRAGMA default_synchronous;
146  }
147} {123 123 0 0}
148do_test pragma-1.14 {
149  execsql {
150    PRAGMA default_synchronous=FULL;
151    PRAGMA cache_size;
152    PRAGMA default_cache_size;
153    PRAGMA synchronous;
154    PRAGMA default_synchronous;
155  }
156} {123 123 2 2}
157do_test pragma-1.15 {
158  db close
159  set ::DB [sqlite db test.db]
160  execsql {
161    PRAGMA cache_size;
162    PRAGMA default_cache_size;
163    PRAGMA synchronous;
164    PRAGMA default_synchronous;
165  }
166} {123 123 2 2}
167
168do_test pragma-2.1 {
169  execsql {
170    PRAGMA show_datatypes=on;
171    PRAGMA empty_result_callbacks=off;
172  }
173  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
174} {}
175do_test pragma-2.2 {
176  execsql {
177    PRAGMA empty_result_callbacks=on;
178  }
179  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
180} {text text text integer text}
181
182# Make sure we can read the schema when empty_result_callbacks are
183# turned on. Ticket #406
184do_test pragma-2.2.1 {
185  execsql {
186    BEGIN;
187    CREATE TABLE tabx(a,b,c,d);
188    ROLLBACK;
189    SELECT count(*) FROM sqlite_master;
190  }
191} {0}
192
193do_test pragma-2.3 {
194  execsql {
195    CREATE TABLE t1(
196       a INTEGER,
197       b TEXT,
198       c WHATEVER,
199       d CLOB,
200       e BLOB,
201       f VARCHAR(123),
202       g nVaRcHaR(432)
203    );
204  }
205  sqlite_datatypes $::DB {SELECT * FROM t1}
206} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
207do_test pragma-2.4 {
208  sqlite_datatypes $::DB {
209     SELECT 1, 'hello', NULL
210  }
211} {NUMERIC TEXT TEXT}
212do_test pragma-2.5 {
213  sqlite_datatypes $::DB {
214     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
215  }
216} {NUMERIC TEXT TEXT}
217do_test pragma-2.6 {
218  execsql {
219    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
220  }
221  sqlite_datatypes $::DB {SELECT * FROM v1}
222} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
223do_test pragma-2.7 {
224  sqlite_datatypes $::DB {
225    SELECT d,e FROM t1 UNION SELECT a,c FROM t1
226  }
227} {INTEGER WHATEVER}
228do_test pragma-2.8 {
229  sqlite_datatypes $::DB {
230    SELECT d,e FROM t1 EXCEPT SELECT c,e FROM t1
231  }
232} {WHATEVER BLOB}
233do_test pragma-2.9 {
234  sqlite_datatypes $::DB {
235    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM t1
236  }
237} {WHATEVER BLOB}
238do_test pragma-2.10 {
239  sqlite_datatypes $::DB {
240    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
241  }
242} {WHATEVER BLOB}
243
244# Construct a corrupted index and make sure the integrity_check
245# pragma finds it.
246#
247if {![sqlite -has-codec]} {
248do_test pragma-3.1 {
249  execsql {
250    BEGIN;
251    CREATE TABLE t2(a,b,c);
252    CREATE INDEX i2 ON t2(a);
253    INSERT INTO t2 VALUES(11,2,3);
254    INSERT INTO t2 VALUES(22,3,4);
255    COMMIT;
256    SELECT rowid, * from t2;
257  }
258} {1 11 2 3 2 22 3 4}
259do_test pragma-3.2 {
260  set rootpage [execsql {SELECT rootpage FROM sqlite_master WHERE name='i2'}]
261  set db [btree_open test.db]
262  btree_begin_transaction $db
263  set c [btree_cursor $db $rootpage 1]
264  btree_first $c
265  btree_delete $c
266  btree_commit $db
267  btree_close $db
268  execsql {PRAGMA integrity_check}
269} {{rowid 1 missing from index i2} {wrong # of entries in index i2}}
270}; # endif has-codec
271
272# Test the temp_store and default_temp_store pragmas
273#
274do_test pragma-4.2 {
275  execsql {
276    PRAGMA temp_store='default';
277    PRAGMA temp_store;
278  }
279} {0}
280do_test pragma-4.3 {
281  execsql {
282    PRAGMA temp_store='file';
283    PRAGMA temp_store;
284  }
285} {1}
286do_test pragma-4.4 {
287  execsql {
288    PRAGMA temp_store='memory';
289    PRAGMA temp_store;
290  }
291} {2}
292do_test pragma-4.5 {
293  execsql {
294    PRAGMA default_temp_store='default';
295    PRAGMA default_temp_store;
296  }
297} {0}
298do_test pragma-4.6 {
299  execsql {
300    PRAGMA temp_store;
301  }
302} {2}
303do_test pragma-4.7 {
304  db close
305  sqlite db test.db
306  execsql {
307    PRAGMA temp_store;
308  }
309} {0}
310do_test pragma-4.8 {
311  execsql {
312    PRAGMA default_temp_store;
313  }
314} {0}
315do_test pragma-4.9 {
316  execsql {
317    PRAGMA default_temp_store='file';
318    PRAGMA default_temp_store;
319  }
320} {1}
321do_test pragma-4.10 {
322  execsql {
323    PRAGMA temp_store;
324  }
325} {0}
326do_test pragma-4.11 {
327  db close
328  sqlite db test.db
329  execsql {
330    PRAGMA temp_store;
331  }
332} {1}
333do_test pragma-4.12 {
334  execsql {
335    PRAGMA default_temp_store;
336  }
337} {1}
338do_test pragma-4.13 {
339  execsql {
340    PRAGMA default_temp_store='memory';
341    PRAGMA default_temp_store;
342  }
343} {2}
344do_test pragma-4.14 {
345  execsql {
346    PRAGMA temp_store;
347  }
348} {1}
349do_test pragma-4.15 {
350  db close
351  sqlite db test.db
352  execsql {
353    PRAGMA temp_store;
354  }
355} {2}
356do_test pragma-4.16 {
357  execsql {
358    PRAGMA default_temp_store;
359  }
360} {2}
361do_test pragma-4.17 {
362  execsql {
363    PRAGMA temp_store='file';
364    PRAGMA temp_store
365  }
366} {1}
367do_test pragma-4.18 {
368  execsql {
369    PRAGMA default_temp_store
370  }
371} {2}
372do_test pragma-4.19 {
373  db close
374  sqlite db test.db
375  execsql {
376    PRAGMA temp_store
377  }
378} {2}
379
380# Changing the TEMP_STORE deletes any existing temporary tables
381#
382do_test pragma-4.20 {
383  execsql {SELECT name FROM sqlite_temp_master}
384} {}
385do_test pragma-4.21 {
386  execsql {
387    CREATE TEMP TABLE test1(a,b,c);
388    SELECT name FROM sqlite_temp_master;
389  }
390} {test1}
391do_test pragma-4.22 {
392  execsql {
393    PRAGMA temp_store='file';
394    SELECT name FROM sqlite_temp_master;
395  }
396} {}
397do_test pragma-4.23 {
398  execsql {
399    CREATE TEMP TABLE test1(a,b,c);
400    SELECT name FROM sqlite_temp_master;
401  }
402} {test1}
403do_test pragma-4.24 {
404  execsql {
405    PRAGMA temp_store='memory';
406    SELECT name FROM sqlite_temp_master;
407  }
408} {}
409do_test pragma-4.25 {
410  catchsql {
411    BEGIN;
412    PRAGMA temp_store='default';
413    COMMIT;
414  }
415} {1 {temporary storage cannot be changed from within a transaction}}
416catchsql {COMMIT}
417
418finish_test
419