| blob | b1007c490819e00546d7b5db8ceb2273842ec0b1 |
1 # 2008 April 17
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. The focus
12 # of these tests is the journal mode pragma.
13 #
14 # $Id: jrnlmode.test,v 1.16 2009/06/05 17:09:12 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 ifcapable {!pager_pragmas} {
20 finish_test
21 return
22 }
24 if {[info exists TEMP_STORE] && $TEMP_STORE>=2} {
25 set temp_persist memory
26 set temp_delete memory
27 set temp_truncate memory
28 set temp_off off
29 } else {
30 set temp_persist persist
31 set temp_delete delete
32 set temp_truncate truncate
33 set temp_off off
34 }
36 proc temp_journal_mode {newmode} {
37 if {[info exists ::TEMP_STORE] && $::TEMP_STORE>=2} {
38 if {$newmode ne "off" && $newmode ne "memory"} {
39 execsql {PRAGMA temp.journal_mode}
40 set newmode [db one {PRAGMA temp.journal_mode}]
41 }
42 }
43 set newmode
44 }
46 #----------------------------------------------------------------------
47 # Test cases jrnlmode-1.X test the PRAGMA logic.
48 #
49 do_test jrnlmode-1.0 {
50 execsql {
51 PRAGMA journal_mode;
52 PRAGMA main.journal_mode;
53 PRAGMA temp.journal_mode;
54 }
55 } [list delete delete [temp_journal_mode delete]]
56 do_test jrnlmode-1.1 {
57 execsql {
58 PRAGMA journal_mode = persist;
59 }
60 } {persist}
61 do_test jrnlmode-1.2 {
62 execsql {
63 PRAGMA journal_mode;
64 PRAGMA main.journal_mode;
65 PRAGMA temp.journal_mode;
66 }
67 } [list persist persist [temp_journal_mode persist]]
68 do_test jrnlmode-1.4 {
69 execsql {
70 PRAGMA journal_mode = off;
71 }
72 } {off}
73 do_test jrnlmode-1.5 {
74 execsql {
75 PRAGMA journal_mode;
76 PRAGMA main.journal_mode;
77 PRAGMA temp.journal_mode;
78 }
79 } [list off off [temp_journal_mode off]]
80 do_test jrnlmode-1.6 {
81 execsql {
82 PRAGMA journal_mode = delete;
83 }
84 } {delete}
85 do_test jrnlmode-1.7 {
86 execsql {
87 PRAGMA journal_mode;
88 PRAGMA main.journal_mode;
89 PRAGMA Temp.journal_mode;
90 }
91 } [list delete delete [temp_journal_mode delete]]
92 do_test jrnlmode-1.7.1 {
93 execsql {
94 PRAGMA journal_mode = truncate;
95 }
96 } {truncate}
97 do_test jrnlmode-1.7.2 {
98 execsql {
99 PRAGMA journal_mode;
100 PRAGMA main.journal_mode;
101 PRAGMA temp.journal_mode;
102 }
103 } [list truncate truncate [temp_journal_mode truncate]]
104 do_test jrnlmode-1.8 {
105 execsql {
106 PRAGMA journal_mode = off;
107 PRAGMA journal_mode = invalid;
108 }
109 } {off off}
110 ifcapable attach {
111 do_test jrnlmode-1.9 {
112 execsql {
113 PRAGMA journal_mode = PERSIST;
114 ATTACH ':memory:' as aux1;
115 }
116 execsql {
117 PRAGMA main.journal_mode;
118 PRAGMA aux1.journal_mode;
119 }
120 } {persist memory}
121 do_test jrnlmode-1.10 {
122 execsql {
123 PRAGMA main.journal_mode = OFF;
124 }
125 execsql {
126 PRAGMA main.journal_mode;
127 PRAGMA temp.journal_mode;
128 PRAGMA aux1.journal_mode;
129 }
130 } [list off [temp_journal_mode persist] memory]
131 do_test jrnlmode-1.11 {
132 execsql {
133 PRAGMA journal_mode;
134 }
135 } {off}
136 do_test jrnlmode-1.12 {
137 execsql {
138 ATTACH ':memory:' as aux2;
139 }
140 execsql {
141 PRAGMA main.journal_mode;
142 PRAGMA aux1.journal_mode;
143 PRAGMA aux2.journal_mode;
144 }
145 } {off memory memory}
146 do_test jrnlmode-1.13 {
147 # The journal-mode used by in-memory databases cannot be changed.
148 execsql {
149 PRAGMA aux1.journal_mode = DELETE;
150 }
151 execsql {
152 PRAGMA main.journal_mode;
153 PRAGMA aux1.journal_mode;
154 PRAGMA aux2.journal_mode;
155 }
156 } {off memory memory}
157 do_test jrnlmode-1.14 {
158 execsql {
159 PRAGMA journal_mode = delete;
160 }
161 execsql {
162 PRAGMA main.journal_mode;
163 PRAGMA temp.journal_mode;
164 PRAGMA aux1.journal_mode;
165 PRAGMA aux2.journal_mode;
166 }
167 } [list delete [temp_journal_mode delete] memory memory]
168 do_test jrnlmode-1.15 {
169 execsql {
170 ATTACH ':memory:' as aux3;
171 }
172 execsql {
173 PRAGMA main.journal_mode;
174 PRAGMA temp.journal_mode;
175 PRAGMA aux1.journal_mode;
176 PRAGMA aux2.journal_mode;
177 PRAGMA aux3.journal_mode;
178 }
179 } [list delete [temp_journal_mode delete] memory memory memory]
180 do_test jrnlmode-1.16 {
181 execsql {
182 PRAGMA journal_mode = TRUNCATE;
183 }
184 execsql {
185 PRAGMA main.journal_mode;
186 PRAGMA temp.journal_mode;
187 PRAGMA aux1.journal_mode;
188 PRAGMA aux2.journal_mode;
189 PRAGMA aux3.journal_mode;
190 }
191 } [list truncate [temp_journal_mode truncate] memory memory memory]
193 do_test jrnlmode-1.99 {
194 execsql {
195 DETACH aux1;
196 DETACH aux2;
197 DETACH aux3;
198 }
199 } {}
200 }
202 ifcapable attach {
203 forcedelete test2.db
204 do_test jrnlmode-2.1 {
205 execsql {
206 ATTACH 'test2.db' AS aux;
207 PRAGMA main.journal_mode = persist;
208 PRAGMA aux.journal_mode = persist;
209 CREATE TABLE abc(a, b, c);
210 CREATE TABLE aux.def(d, e, f);
211 }
212 execsql {
213 BEGIN;
214 INSERT INTO abc VALUES(1, 2, 3);
215 INSERT INTO def VALUES(4, 5, 6);
216 COMMIT;
217 }
218 list [file exists test.db-journal] [file exists test2.db-journal]
219 } {1 1}
221 do_test jrnlmode-2.2 {
222 file size test.db-journal
223 } {0}
225 do_test jrnlmode-2.3 {
226 execsql {
227 SELECT * FROM abc;
228 }
229 } {1 2 3}
231 do_test jrnlmode-2.4 {
232 file size test.db-journal
233 } {0}
235 do_test jrnlmode-2.5 {
236 execsql {
237 SELECT * FROM def;
238 }
239 } {4 5 6}
241 #----------------------------------------------------------------------
242 # Test caes jrnlmode-3.X verify that ticket #3127 has been fixed.
243 #
244 db close
245 forcedelete test2.db
246 forcedelete test.db
247 sqlite3 db test.db
249 do_test jrnlmode-3.1 {
250 execsql {
251 CREATE TABLE x(n INTEGER);
252 ATTACH 'test2.db' AS a;
253 create table a.x ( n integer );
254 insert into a.x values(1);
255 insert into a.x values (2);
256 insert into a.x values (3);
257 insert into a.x values (4);
258 }
259 } {}
261 do_test jrnlmode-3.2 {
262 execsql { PRAGMA journal_mode=off; }
263 execsql {
264 BEGIN IMMEDIATE;
265 INSERT OR IGNORE INTO main.x SELECT * FROM a.x;
266 COMMIT;
267 }
268 } {}
269 }
271 ifcapable autovacuum&&pragma {
272 db close
273 forcedelete test.db
274 sqlite3 db test.db
275 do_test jrnlmode-4.1 {
276 execsql {
277 PRAGMA cache_size = 1;
278 PRAGMA auto_vacuum = 1;
279 CREATE TABLE abc(a, b, c);
280 }
281 execsql { PRAGMA page_count }
282 } {3}
284 do_test jrnlmode-4.2 {
285 execsql { PRAGMA journal_mode = off }
286 } {off}
288 do_test jrnlmode-4.3 {
289 execsql { INSERT INTO abc VALUES(1, 2, randomblob(2000)) }
290 } {}
292 # This will attempt to truncate the database file. Check that this
293 # is not a problem when journal_mode=off.
294 do_test jrnlmode-4.4 {
295 execsql { DELETE FROM abc }
296 } {}
298 integrity_check jrnlmode-4.5
299 }
301 #------------------------------------------------------------------------
302 # The following test caes, jrnlmode-5.*, test the journal_size_limit
303 # pragma.
304 ifcapable pragma {
305 db close
306 forcedelete test.db test2.db test3.db
307 sqlite3 db test.db
309 do_test jrnlmode-5.1 {
310 execsql {pragma page_size=1024}
311 execsql {pragma journal_mode=persist}
312 } {persist}
314 do_test jrnlmode-5.2 {
315 execsql { PRAGMA journal_size_limit }
316 } {-1}
317 do_test jrnlmode-5.3 {
318 execsql {
319 ATTACH 'test2.db' AS aux;
320 PRAGMA aux.journal_mode=persist;
321 PRAGMA aux.journal_size_limit;
322 }
323 } {persist -1}
324 do_test jrnlmode-5.4.1 {
325 execsql { PRAGMA aux.journal_size_limit = 999999999999 }
326 } {999999999999}
327 do_test jrnlmode-5.4.2 {
328 execsql { PRAGMA aux.journal_size_limit = 10240 }
329 } {10240}
330 do_test jrnlmode-5.5 {
331 execsql { PRAGMA main.journal_size_limit = 20480 }
332 } {20480}
333 do_test jrnlmode-5.6 {
334 execsql { PRAGMA journal_size_limit }
335 } {20480}
336 do_test jrnlmode-5.7 {
337 execsql { PRAGMA aux.journal_size_limit }
338 } {10240}
340 do_test jrnlmode-5.8 {
341 execsql {
342 ATTACH 'test3.db' AS aux2;
343 PRAGMA aux2.journal_mode=persist;
344 }
345 } {persist}
347 do_test jrnlmode-5.9 {
348 execsql {
349 CREATE TABLE main.t1(a, b, c);
350 CREATE TABLE aux.t2(a, b, c);
351 CREATE TABLE aux2.t3(a, b, c);
352 }
353 } {}
354 do_test jrnlmode-5.10 {
355 list \
356 [file exists test.db-journal] \
357 [file exists test2.db-journal] \
358 [file exists test3.db-journal]
359 } {1 1 1}
360 do_test jrnlmode-5.11 {
361 execsql {
362 BEGIN;
363 INSERT INTO t3 VALUES(randomblob(1000),randomblob(1000),randomblob(1000));
364 INSERT INTO t3
365 SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
366 INSERT INTO t3
367 SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
368 INSERT INTO t3
369 SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
370 INSERT INTO t3
371 SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
372 INSERT INTO t3
373 SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
374 INSERT INTO t2 SELECT * FROM t3;
375 INSERT INTO t1 SELECT * FROM t2;
376 COMMIT;
377 }
378 list \
379 [file exists test.db-journal] \
380 [file exists test2.db-journal] \
381 [file exists test3.db-journal] \
382 [file size test.db-journal] \
383 [file size test2.db-journal] \
384 [file size test3.db-journal]
385 } {1 1 1 0 0 0}
387 do_test jrnlmode-5.12 {
388 execsql {
389 BEGIN;
390 UPDATE t1 SET a = randomblob(1000);
391 }
392 expr {[file size test.db-journal]>30000}
393 } {1}
394 do_test jrnlmode-5.13 {
395 execsql COMMIT
396 file size test.db-journal
397 } {20480}
399 do_test jrnlmode-5.14 {
400 execsql {
401 BEGIN;
402 UPDATE t2 SET a = randomblob(1000);
403 }
404 expr {[file size test2.db-journal]>30000}
405 } {1}
406 do_test jrnlmode-5.15 {
407 execsql COMMIT
408 file size test2.db-journal
409 } {10240}
411 do_test jrnlmode-5.16 {
412 execsql {
413 BEGIN;
414 UPDATE t3 SET a = randomblob(1000);
415 }
416 set journalsize [file size test3.db-journal]
417 expr {$journalsize>30000}
418 } {1}
419 do_test jrnlmode-5.17 {
420 execsql COMMIT
421 set sz [file size test3.db-journal]
422 expr {$sz>=$journalsize}
423 } {1}
425 do_test jrnlmode-5.18 {
426 execsql {
427 PRAGMA journal_size_limit = -4;
428 BEGIN;
429 UPDATE t1 SET a = randomblob(1000);
430 }
431 set journalsize [file size test.db-journal]
432 expr {$journalsize>30000}
433 } {1}
434 do_test jrnlmode-5.19 {
435 execsql COMMIT
436 set sz [file size test.db-journal]
437 expr {$sz>=$journalsize}
438 } {1}
440 # Test a size-limit of 0.
441 #
442 do_test jrnlmode-5.20 {
443 execsql {
444 PRAGMA journal_size_limit = 0;
445 BEGIN;
446 UPDATE t1 SET a = randomblob(1000);
447 }
448 } {0}
449 do_test jrnlmode-5.21 {
450 expr {[file size test.db-journal] > 1024}
451 } {1}
452 do_test jrnlmode-5.22 {
453 execsql COMMIT
454 list [file exists test.db-journal] [file size test.db-journal]
455 } {1 0}
456 }
458 ifcapable pragma {
459 # These tests are not run as part of the "journaltest" permutation,
460 # as the test_journal.c layer is incompatible with in-memory journaling.
461 if {[permutation] ne "journaltest"} {
463 do_test jrnlmode-6.1 {
464 execsql {
465 PRAGMA journal_mode = truncate;
466 CREATE TABLE t4(a, b);
467 BEGIN;
468 INSERT INTO t4 VALUES(1, 2);
469 PRAGMA journal_mode = memory;
470 }
471 } {truncate truncate}
472 do_test jrnlmode-6.2 {
473 file exists test.db-journal
474 } {1}
475 do_test jrnlmode-6.3 {
476 execsql {
477 COMMIT;
478 SELECT * FROM t4;
479 }
480 } {1 2}
481 do_test jrnlmode-6.4 {
482 file exists test.db-journal
483 } {1}
484 do_test jrnlmode-6.5 {
485 execsql {
486 PRAGMA journal_mode = MEMORY;
487 BEGIN;
488 INSERT INTO t4 VALUES(3, 4);
489 }
490 file exists test.db-journal
491 } {0}
492 do_test jrnlmode-6.7 {
493 execsql {
494 COMMIT;
495 SELECT * FROM t4;
496 }
497 } {1 2 3 4}
498 do_test jrnlmode-6.8 {
499 file exists test.db-journal
500 } {0}
501 do_test jrnlmode-6.9 {
502 execsql {
503 PRAGMA journal_mode = DELETE;
504 BEGIN IMMEDIATE; INSERT INTO t4 VALUES(1,2); COMMIT;
505 }
506 file exists test.db-journal
507 } {0}
508 }
509 }
511 ifcapable pragma {
512 catch { db close }
513 do_test jrnlmode-7.1 {
514 foreach f [glob -nocomplain test.db*] { forcedelete $f }
515 sqlite3 db test.db
516 execsql {
517 PRAGMA journal_mode = memory;
518 PRAGMA auto_vacuum = 0;
519 PRAGMA page_size = 1024;
520 PRAGMA user_version = 5;
521 PRAGMA user_version;
522 }
523 } {memory 5}
524 do_test jrnlmode-7.2 { file size test.db } {1024}
525 }
527 do_execsql_test jrnlmode-8.1 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}
528 do_execsql_test jrnlmode-8.2 { CREATE TABLE t1(x) } {}
529 do_execsql_test jrnlmode-8.3 { INSERT INTO t1 VALUES(123) } {}
530 do_execsql_test jrnlmode-8.4 { SELECT * FROM t1 } {123}
531 do_execsql_test jrnlmode-8.5 { PRAGMA journal_mode=PERSIST } {persist}
532 do_execsql_test jrnlmode-8.6 { PRAGMA journal_mode=DELETE } {delete}
533 do_execsql_test jrnlmode-8.7 { PRAGMA journal_mode=TRUNCATE } {truncate}
534 do_execsql_test jrnlmode-8.8 { PRAGMA journal_mode=DELETE } {delete}
535 do_execsql_test jrnlmode-8.9 { CREATE TABLE t2(y) } {}
536 do_execsql_test jrnlmode-8.10 { INSERT INTO t2 VALUES(456) } {}
537 do_execsql_test jrnlmode-8.11 { SELECT * FROM t1, t2 } {123 456}
538 do_execsql_test jrnlmode-8.12 { PRAGMA locking_mode=NORMAL } {normal}
539 do_execsql_test jrnlmode-8.13 { PRAGMA journal_mode=PERSIST } {persist}
540 do_execsql_test jrnlmode-8.14 { PRAGMA journal_mode=TRUNCATE } {truncate}
541 do_execsql_test jrnlmode-8.15 { PRAGMA journal_mode=PERSIST } {persist}
542 do_execsql_test jrnlmode-8.16 { PRAGMA journal_mode=DELETE } {delete}
543 do_execsql_test jrnlmode-8.17 { PRAGMA journal_mode=TRUNCATE } {truncate}
544 do_execsql_test jrnlmode-8.18 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}
545 do_execsql_test jrnlmode-8.19 { CREATE TABLE t3(z) } {}
546 do_execsql_test jrnlmode-8.20 { BEGIN IMMEDIATE } {}
547 do_execsql_test jrnlmode-8.21 { PRAGMA journal_mode=DELETE } {delete}
548 do_execsql_test jrnlmode-8.22 { COMMIT } {}
549 do_execsql_test jrnlmode-8.23 { PRAGMA journal_mode=DELETE } {delete}
550 do_execsql_test jrnlmode-8.24 { PRAGMA journal_mode=TRUNCATE } {truncate}
551 do_execsql_test jrnlmode-8.25 { PRAGMA locking_mode=NORMAL } {normal}
552 do_execsql_test jrnlmode-8.26 { CREATE TABLE t4(w) } {}
553 do_execsql_test jrnlmode-8.27 { BEGIN IMMEDIATE } {}
554 do_execsql_test jrnlmode-8.28 { PRAGMA journal_mode=DELETE } {delete}
555 do_execsql_test jrnlmode-8.29 { COMMIT } {}
556 do_execsql_test jrnlmode-8.30 { PRAGMA journal_mode=DELETE } {delete}
558 finish_test