Annotation of embedaddon/sqlite3/test/jrnlmode.test, revision 1.1.1.1
1.1 misho 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.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]
192:
193: do_test jrnlmode-1.99 {
194: execsql {
195: DETACH aux1;
196: DETACH aux2;
197: DETACH aux3;
198: }
199: } {}
200: }
201:
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}
220:
221: do_test jrnlmode-2.2 {
222: file size test.db-journal
223: } {0}
224:
225: do_test jrnlmode-2.3 {
226: execsql {
227: SELECT * FROM abc;
228: }
229: } {1 2 3}
230:
231: do_test jrnlmode-2.4 {
232: file size test.db-journal
233: } {0}
234:
235: do_test jrnlmode-2.5 {
236: execsql {
237: SELECT * FROM def;
238: }
239: } {4 5 6}
240:
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
248:
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: } {}
260:
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: }
270:
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}
283:
284: do_test jrnlmode-4.2 {
285: execsql { PRAGMA journal_mode = off }
286: } {off}
287:
288: do_test jrnlmode-4.3 {
289: execsql { INSERT INTO abc VALUES(1, 2, randomblob(2000)) }
290: } {}
291:
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: } {}
297:
298: integrity_check jrnlmode-4.5
299: }
300:
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
308:
309: do_test jrnlmode-5.1 {
310: execsql {pragma page_size=1024}
311: execsql {pragma journal_mode=persist}
312: } {persist}
313:
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}
339:
340: do_test jrnlmode-5.8 {
341: execsql {
342: ATTACH 'test3.db' AS aux2;
343: PRAGMA aux2.journal_mode=persist;
344: }
345: } {persist}
346:
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}
386:
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}
398:
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}
410:
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}
424:
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}
439:
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: }
457:
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"} {
462:
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: }
510:
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: }
526:
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}
557:
558: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to jrnlmode.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test