1: # 2010 April 22
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
12: # focus of this file is testing the operation of the library in
13: # "PRAGMA journal_mode=WAL" mode.
14: #
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18: source $testdir/wal_common.tcl
19: source $testdir/malloc_common.tcl
20:
21: do_not_use_codec
22:
23: ifcapable !wal {finish_test ; return }
24:
25:
26: # Test organization:
27: #
28: # walback-1.*: Simple tests.
29: #
30: # walback-2.*: Test backups when the source db is modified mid-backup.
31: #
32: # walback-3.*: Backup of WAL sources into rollback destinations, and
33: # vice-versa.
34: #
35:
36: # Make sure a simple backup from a WAL database works.
37: #
38: do_test walbak-1.0 {
39: execsql {
40: PRAGMA synchronous = NORMAL;
41: PRAGMA page_size = 1024;
42: PRAGMA auto_vacuum = 0;
43: PRAGMA journal_mode = wal;
44: BEGIN;
45: CREATE TABLE t1(a PRIMARY KEY, b);
46: INSERT INTO t1 VALUES('I', 'one');
47: COMMIT;
48: }
49: } {wal}
50: do_test walbak-1.1 {
51: forcedelete bak.db bak.db-journal bak.db-wal
52: db backup bak.db
53: file size bak.db
54: } [expr 3*1024]
55: do_test walbak-1.2 {
56: sqlite3 db2 bak.db
57: execsql {
58: SELECT * FROM t1;
59: PRAGMA main.journal_mode;
60: } db2
61: } {I one wal}
62: do_test walbak-1.3 {
63: execsql { PRAGMA integrity_check } db2
64: } {ok}
65: db2 close
66:
67: # Try a VACUUM on a WAL database.
68: #
69: do_test walbak-1.4 {
70: execsql {
71: VACUUM;
72: PRAGMA main.journal_mode;
73: }
74: } {wal}
75: do_test walbak-1.5 {
76: list [file size test.db] [file size test.db-wal]
77: } [list 1024 [wal_file_size 6 1024]]
78: do_test walbak-1.6 {
79: execsql { PRAGMA wal_checkpoint }
80: list [file size test.db] [file size test.db-wal]
81: } [list [expr 3*1024] [wal_file_size 6 1024]]
82: do_test walbak-1.6.1 {
83: hexio_read test.db 18 2
84: } {0202}
85: do_test walbak-1.7 {
86: execsql {
87: CREATE TABLE t2(a, b);
88: INSERT INTO t2 SELECT * FROM t1;
89: DROP TABLE t1;
90: }
91: list [file size test.db] [file size test.db-wal]
92: } [list [expr 3*1024] [wal_file_size 6 1024]]
93: do_test walbak-1.8 {
94: execsql { VACUUM }
95: list [file size test.db] [file size test.db-wal]
96: } [list [expr 3*1024] [wal_file_size 8 1024]]
97: do_test walbak-1.9 {
98: execsql { PRAGMA wal_checkpoint }
99: list [file size test.db] [file size test.db-wal]
100: } [list [expr 2*1024] [wal_file_size 8 1024]]
101:
102: #-------------------------------------------------------------------------
103: # Backups when the source db is modified mid-backup.
104: #
105: proc sig {{db db}} {
106: $db eval {
107: PRAGMA integrity_check;
108: SELECT md5sum(a, b) FROM t1;
109: }
110: }
111: db close
112: delete_file test.db
113: sqlite3 db test.db
114: do_test walbak-2.1 {
115: execsql { PRAGMA journal_mode = WAL }
116: execsql {
117: CREATE TABLE t1(a PRIMARY KEY, b);
118: BEGIN;
119: INSERT INTO t1 VALUES(randomblob(500), randomblob(500));
120: INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 2 */
121: INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 4 */
122: INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 8 */
123: INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 16 */
124: INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 32 */
125: INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 64 */
126: COMMIT;
127: }
128: } {}
129: do_test walbak-2.2 {
130: db backup abc.db
131: sqlite3 db2 abc.db
132: string compare [sig db] [sig db2]
133: } {0}
134:
135: do_test walbak-2.3 {
136: sqlite3_backup B db2 main db main
137: B step 50
138: execsql { UPDATE t1 SET b = randomblob(500) }
139: list [B step 1000] [B finish]
140: } {SQLITE_DONE SQLITE_OK}
141: do_test walbak-2.4 {
142: string compare [sig db] [sig db2]
143: } {0}
144:
145: do_test walbak-2.5 {
146: db close
147: sqlite3 db test.db
148: execsql { PRAGMA cache_size = 10 }
149: sqlite3_backup B db2 main db main
150: B step 50
151: execsql {
152: BEGIN;
153: UPDATE t1 SET b = randomblob(500);
154: }
155: expr [file size test.db-wal] > 10*1024
156: } {1}
157: do_test walbak-2.6 {
158: B step 1000
159: } {SQLITE_BUSY}
160: do_test walbak-2.7 {
161: execsql COMMIT
162: list [B step 1000] [B finish]
163: } {SQLITE_DONE SQLITE_OK}
164: do_test walbak-2.8 {
165: string compare [sig db] [sig db2]
166: } {0}
167:
168: do_test walbak-2.9 {
169: db close
170: sqlite3 db test.db
171: execsql { PRAGMA cache_size = 10 }
172: sqlite3_backup B db2 main db main
173: B step 50
174: execsql {
175: BEGIN;
176: UPDATE t1 SET b = randomblob(500);
177: }
178: expr [file size test.db-wal] > 10*1024
179: } {1}
180: do_test walbak-2.10 {
181: B step 1000
182: } {SQLITE_BUSY}
183: do_test walbak-2.11 {
184: execsql ROLLBACK
185: set sigB [sig db]
186: list [B step 1000] [B finish]
187: } {SQLITE_DONE SQLITE_OK}
188: do_test walbak-2.12 {
189: string compare [sig db] [sig db2]
190: } {0}
191: db2 close
192: db close
193:
194: #-------------------------------------------------------------------------
195: # Run some backup operations to copy back and forth between WAL and:
196: #
197: # walbak-3.1.*: an in-memory database
198: #
199: # walbak-3.2.*: a temporary database
200: #
201: # walbak-3.3.*: a database in rollback mode.
202: #
203: # walbak-3.4.*: a database in rollback mode that (initially) uses a
204: # different page-size.
205: #
206: # Check that this does not confuse any connected clients.
207: #
208: foreach {tn setup} {
209: 1 {
210: sqlite3 db test.db
211: sqlite3 db2 :memory:
212: db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
213: db2 eval { PRAGMA page_size = 1024 }
214: }
215:
216: 2 {
217: sqlite3 db test.db
218: sqlite3 db2 ""
219: db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
220: db2 eval { PRAGMA page_size = 1024 }
221: }
222:
223: 3 {
224: sqlite3 db test.db
225: sqlite3 db2 test.db2
226: db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
227: db2 eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = PERSIST }
228: }
229:
230: 4 {
231: sqlite3 db test.db
232: sqlite3 db2 test.db2
233: db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
234: db2 eval {
235: PRAGMA page_size = 2048;
236: PRAGMA journal_mode = PERSIST;
237: CREATE TABLE xx(x);
238: }
239: }
240:
241: } {
242: foreach f [glob -nocomplain test.db*] { forcedelete $f }
243:
244: eval $setup
245:
246: do_test walbak-3.$tn.1 {
247: execsql {
248: CREATE TABLE t1(a, b);
249: INSERT INTO t1 VALUES(1, 2);
250: INSERT INTO t1 VALUES(3, 4);
251: SELECT * FROM t1;
252: }
253: } {1 2 3 4}
254:
255: do_test walbak-3.$tn.2 {
256: sqlite3_backup B db2 main db main
257: B step 10000
258: B finish
259: execsql { SELECT * FROM t1 } db2
260: } {1 2 3 4}
261:
262: do_test walbak-3.$tn.3 {
263: execsql {
264: INSERT INTO t1 VALUES(5, 6);
265: INSERT INTO t1 VALUES(7, 8);
266: SELECT * FROM t1;
267: } db2
268: } {1 2 3 4 5 6 7 8}
269:
270: do_test walbak-3.$tn.4 {
271: sqlite3_backup B db main db2 main
272: B step 10000
273: B finish
274: execsql { SELECT * FROM t1 }
275: } {1 2 3 4 5 6 7 8}
276:
277: # Check that [db] is still in WAL mode.
278: do_test walbak-3.$tn.5 {
279: execsql { PRAGMA journal_mode }
280: } {wal}
281: do_test walbak-3.$tn.6 {
282: execsql { PRAGMA wal_checkpoint }
283: hexio_read test.db 18 2
284: } {0202}
285:
286: # If it was not an in-memory database, check that [db2] is still in
287: # rollback mode.
288: if {[file exists test.db2]} {
289: do_test walbak-3.$tn.7 {
290: execsql { PRAGMA journal_mode } db2
291: } {wal}
292: do_test walbak-3.$tn.8 {
293: execsql { PRAGMA wal_checkpoint }
294: hexio_read test.db 18 2
295: } {0202}
296: }
297:
298: db close
299: db2 close
300: }
301:
302: #-------------------------------------------------------------------------
303: # Test that the following holds when a backup operation is run:
304: #
305: # Source | Destination inital | Destination final
306: # ---------------------------------------------------
307: # Rollback Rollback Rollback
308: # Rollback WAL WAL
309: # WAL Rollback WAL
310: # WAL WAL WAL
311: #
312: foreach {tn src dest dest_final} {
313: 1 delete delete delete
314: 2 delete wal wal
315: 3 wal delete wal
316: 4 wal wal wal
317: } {
318: catch { db close }
319: catch { db2 close }
320: forcedelete test.db test.db2
321:
322: do_test walbak-4.$tn.1 {
323: sqlite3 db test.db
324: db eval "PRAGMA journal_mode = $src"
325: db eval {
326: CREATE TABLE t1(a, b);
327: INSERT INTO t1 VALUES('I', 'II');
328: INSERT INTO t1 VALUES('III', 'IV');
329: }
330:
331: sqlite3 db2 test.db2
332: db2 eval "PRAGMA journal_mode = $dest"
333: db2 eval {
334: CREATE TABLE t2(x, y);
335: INSERT INTO t2 VALUES('1', '2');
336: INSERT INTO t2 VALUES('3', '4');
337: }
338: } {}
339:
340: do_test walbak-4.$tn.2 { execsql { PRAGMA journal_mode } db } $src
341: do_test walbak-4.$tn.3 { execsql { PRAGMA journal_mode } db2 } $dest
342:
343: do_test walbak-4.$tn.4 { db backup test.db2 } {}
344: do_test walbak-4.$tn.5 {
345: execsql { SELECT * FROM t1 } db2
346: } {I II III IV}
347: do_test walbak-4.$tn.5 { execsql { PRAGMA journal_mode } db2 } $dest_final
348:
349:
350: db2 close
351: do_test walbak-4.$tn.6 { file exists test.db2-wal } 0
352: sqlite3 db2 test.db2
353: do_test walbak-4.$tn.7 { execsql { PRAGMA journal_mode } db2 } $dest_final
354: }
355:
356:
357: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to walbak.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