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