| blob | f7cf3fa6a320360e3a924e26cb350db14e5607bc |
2 #pragma ident "%Z%%M% %I% %E% SMI"
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 $
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
23 # Delete the preexisting database to avoid the special setup
24 # that the "all.test" script does.
25 #
26 db close
27 file delete test.db
28 set DB [sqlite db test.db]
30 do_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}
38 do_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}
47 do_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}
57 do_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}
66 do_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}
75 do_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}
84 do_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}
94 do_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}
103 do_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}
112 do_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}
122 do_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}
131 do_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}
140 do_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}
150 do_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}
159 do_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}
170 do_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 } {}
177 do_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}
184 # Make sure we can read the schema when empty_result_callbacks are
185 # turned on. Ticket #406
186 do_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}
195 do_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)}
209 do_test pragma-2.4 {
210 sqlite_datatypes $::DB {
211 SELECT 1, 'hello', NULL
212 }
213 } {NUMERIC TEXT TEXT}
214 do_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}
219 do_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)}
225 do_test pragma-2.7 {
226 sqlite_datatypes $::DB {
227 SELECT d,e FROM t1 UNION SELECT a,c FROM t1
228 }
229 } {INTEGER WHATEVER}
230 do_test pragma-2.8 {
231 sqlite_datatypes $::DB {
232 SELECT d,e FROM t1 EXCEPT SELECT c,e FROM t1
233 }
234 } {WHATEVER BLOB}
235 do_test pragma-2.9 {
236 sqlite_datatypes $::DB {
237 SELECT d,e FROM t1 INTERSECT SELECT c,e FROM t1
238 }
239 } {WHATEVER BLOB}
240 do_test pragma-2.10 {
241 sqlite_datatypes $::DB {
242 SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
243 }
244 } {WHATEVER BLOB}
246 # Construct a corrupted index and make sure the integrity_check
247 # pragma finds it.
248 #
249 if {![sqlite -has-codec]} {
250 do_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}
261 do_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
274 # Test the temp_store and default_temp_store pragmas
275 #
276 do_test pragma-4.2 {
277 execsql {
278 PRAGMA temp_store='default';
279 PRAGMA temp_store;
280 }
281 } {0}
282 do_test pragma-4.3 {
283 execsql {
284 PRAGMA temp_store='file';
285 PRAGMA temp_store;
286 }
287 } {1}
288 do_test pragma-4.4 {
289 execsql {
290 PRAGMA temp_store='memory';
291 PRAGMA temp_store;
292 }
293 } {2}
294 do_test pragma-4.5 {
295 execsql {
296 PRAGMA default_temp_store='default';
297 PRAGMA default_temp_store;
298 }
299 } {0}
300 do_test pragma-4.6 {
301 execsql {
302 PRAGMA temp_store;
303 }
304 } {2}
305 do_test pragma-4.7 {
306 db close
307 sqlite db test.db
308 execsql {
309 PRAGMA temp_store;
310 }
311 } {0}
312 do_test pragma-4.8 {
313 execsql {
314 PRAGMA default_temp_store;
315 }
316 } {0}
317 do_test pragma-4.9 {
318 execsql {
319 PRAGMA default_temp_store='file';
320 PRAGMA default_temp_store;
321 }
322 } {1}
323 do_test pragma-4.10 {
324 execsql {
325 PRAGMA temp_store;
326 }
327 } {0}
328 do_test pragma-4.11 {
329 db close
330 sqlite db test.db
331 execsql {
332 PRAGMA temp_store;
333 }
334 } {1}
335 do_test pragma-4.12 {
336 execsql {
337 PRAGMA default_temp_store;
338 }
339 } {1}
340 do_test pragma-4.13 {
341 execsql {
342 PRAGMA default_temp_store='memory';
343 PRAGMA default_temp_store;
344 }
345 } {2}
346 do_test pragma-4.14 {
347 execsql {
348 PRAGMA temp_store;
349 }
350 } {1}
351 do_test pragma-4.15 {
352 db close
353 sqlite db test.db
354 execsql {
355 PRAGMA temp_store;
356 }
357 } {2}
358 do_test pragma-4.16 {
359 execsql {
360 PRAGMA default_temp_store;
361 }
362 } {2}
363 do_test pragma-4.17 {
364 execsql {
365 PRAGMA temp_store='file';
366 PRAGMA temp_store
367 }
368 } {1}
369 do_test pragma-4.18 {
370 execsql {
371 PRAGMA default_temp_store
372 }
373 } {2}
374 do_test pragma-4.19 {
375 db close
376 sqlite db test.db
377 execsql {
378 PRAGMA temp_store
379 }
380 } {2}
382 # Changing the TEMP_STORE deletes any existing temporary tables
383 #
384 do_test pragma-4.20 {
385 execsql {SELECT name FROM sqlite_temp_master}
386 } {}
387 do_test pragma-4.21 {
388 execsql {
389 CREATE TEMP TABLE test1(a,b,c);
390 SELECT name FROM sqlite_temp_master;
391 }
392 } {test1}
393 do_test pragma-4.22 {
394 execsql {
395 PRAGMA temp_store='file';
396 SELECT name FROM sqlite_temp_master;
397 }
398 } {}
399 do_test pragma-4.23 {
400 execsql {
401 CREATE TEMP TABLE test1(a,b,c);
402 SELECT name FROM sqlite_temp_master;
403 }
404 } {test1}
405 do_test pragma-4.24 {
406 execsql {
407 PRAGMA temp_store='memory';
408 SELECT name FROM sqlite_temp_master;
409 }
410 } {}
411 do_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}}
418 catchsql {COMMIT}
420 finish_test