Debian Codegraph @

test/jrnlmode.test

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