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