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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18source $testdir/wal_common.tcl
19source $testdir/malloc_common.tcl
20
21do_not_use_codec
22
23ifcapable !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#
38do_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}
50do_test walbak-1.1 {
51  file delete -force bak.db bak.db-journal bak.db-wal
52  db backup bak.db
53  file size bak.db
54} [expr 3*1024]
55do_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}
62do_test walbak-1.3 {
63  execsql { PRAGMA integrity_check } db2
64} {ok}
65db2 close
66
67# Try a VACUUM on a WAL database.
68#
69do_test walbak-1.4 {
70  execsql { 
71    VACUUM;
72    PRAGMA main.journal_mode;
73  }
74} {wal}
75do_test walbak-1.5 {
76  list [file size test.db] [file size test.db-wal]
77} [list 1024 [wal_file_size 6 1024]]
78do_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]]
82do_test walbak-1.7 {
83  execsql { 
84    CREATE TABLE t2(a, b);
85    INSERT INTO t2 SELECT * FROM t1;
86    DROP TABLE t1;
87  }
88  list [file size test.db] [file size test.db-wal]
89} [list [expr 3*1024] [wal_file_size 6 1024]]
90do_test walbak-1.8 {
91  execsql { VACUUM }
92  list [file size test.db] [file size test.db-wal]
93} [list [expr 3*1024] [wal_file_size 8 1024]]
94do_test walbak-1.9 {
95  execsql { PRAGMA wal_checkpoint }
96  list [file size test.db] [file size test.db-wal]
97} [list [expr 2*1024] [wal_file_size 8 1024]]
98
99#-------------------------------------------------------------------------
100# Backups when the source db is modified mid-backup.
101#
102proc sig {{db db}} {
103  $db eval { 
104    PRAGMA integrity_check;
105    SELECT md5sum(a, b) FROM t1; 
106  }
107}
108db close
109file delete test.db
110sqlite3 db test.db
111do_test walbak-2.1 {
112  execsql { PRAGMA journal_mode = WAL }
113  execsql {
114    CREATE TABLE t1(a PRIMARY KEY, b);
115    BEGIN;
116      INSERT INTO t1 VALUES(randomblob(500), randomblob(500));
117      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /*  2 */
118      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /*  4 */
119      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /*  8 */
120      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 16 */
121      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 32 */
122      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 64 */
123    COMMIT;
124  }
125} {}
126do_test walbak-2.2 {
127  db backup abc.db
128  sqlite3 db2 abc.db
129  string compare [sig db] [sig db2]
130} {0}
131
132do_test walbak-2.3 {
133  sqlite3_backup B db2 main db main
134  B step 50
135  execsql { UPDATE t1 SET b = randomblob(500) }
136  list [B step 1000] [B finish]
137} {SQLITE_DONE SQLITE_OK}
138do_test walbak-2.4 {
139  string compare [sig db] [sig db2]
140} {0}
141
142do_test walbak-2.5 {
143  db close
144  sqlite3 db test.db
145  execsql { PRAGMA cache_size = 10 }
146  sqlite3_backup B db2 main db main
147  B step 50
148  execsql {
149    BEGIN;
150      UPDATE t1 SET b = randomblob(500);
151  }
152  expr [file size test.db-wal] > 10*1024
153} {1}
154do_test walbak-2.6 {
155  B step 1000
156} {SQLITE_BUSY}
157do_test walbak-2.7 {
158  execsql COMMIT
159  list [B step 1000] [B finish]
160} {SQLITE_DONE SQLITE_OK}
161do_test walbak-2.8 {
162  string compare [sig db] [sig db2]
163} {0}
164
165do_test walbak-2.9 {
166  db close
167  sqlite3 db test.db
168  execsql { PRAGMA cache_size = 10 }
169  sqlite3_backup B db2 main db main
170  B step 50
171  execsql {
172    BEGIN;
173      UPDATE t1 SET b = randomblob(500);
174  }
175  expr [file size test.db-wal] > 10*1024
176} {1}
177do_test walbak-2.10 {
178  B step 1000
179} {SQLITE_BUSY}
180do_test walbak-2.11 {
181  execsql ROLLBACK
182set sigB [sig db]
183  list [B step 1000] [B finish]
184} {SQLITE_DONE SQLITE_OK}
185do_test walbak-2.12 {
186  string compare [sig db] [sig db2]
187} {0}
188db2 close
189db close
190
191#-------------------------------------------------------------------------
192# Run some backup operations to copy back and forth between WAL and:
193#
194#   walbak-3.1.*: an in-memory database
195#
196#   walbak-3.2.*: a temporary database
197#
198#   walbak-3.3.*: a database in rollback mode.
199#
200#   walbak-3.4.*: a database in rollback mode that (initially) uses a 
201#                 different page-size.
202#
203# Check that this does not confuse any connected clients.
204#
205foreach {tn setup} {
206  1 {
207    sqlite3 db  test.db
208    sqlite3 db2 :memory:
209    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
210    db2 eval { PRAGMA page_size = 1024 }
211  }
212
213  2 {
214    sqlite3 db  test.db
215    sqlite3 db2 ""
216    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
217    db2 eval { PRAGMA page_size = 1024 }
218  }
219
220  3 {
221    sqlite3 db  test.db
222    sqlite3 db2 test.db2
223    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
224    db2 eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = PERSIST }
225  }
226
227  4 {
228    sqlite3 db  test.db
229    sqlite3 db2 test.db2
230    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
231    db2 eval { 
232      PRAGMA page_size = 2048;
233      PRAGMA journal_mode = PERSIST;
234      CREATE TABLE xx(x);
235    }
236  }
237
238} {
239  foreach f [glob -nocomplain test.db*] { file delete -force $f }
240
241  eval $setup
242
243  do_test walbak-3.$tn.1 {
244    execsql {
245      CREATE TABLE t1(a, b);
246      INSERT INTO t1 VALUES(1, 2);
247      INSERT INTO t1 VALUES(3, 4);
248      SELECT * FROM t1;
249    }
250  } {1 2 3 4}
251
252  do_test walbak-3.$tn.2 {
253    sqlite3_backup B db2 main db main
254    B step 10000
255    B finish
256    execsql { SELECT * FROM t1 } db2
257  } {1 2 3 4}
258
259  do_test walbak-3.$tn.3 {
260    execsql {
261      INSERT INTO t1 VALUES(5, 6);
262      INSERT INTO t1 VALUES(7, 8);
263      SELECT * FROM t1;
264    } db2
265  } {1 2 3 4 5 6 7 8}
266
267  do_test walbak-3.$tn.4 {
268    sqlite3_backup B db main db2 main
269    B step 10000
270    B finish
271    execsql { SELECT * FROM t1 }
272  } {1 2 3 4 5 6 7 8}
273
274  db  close
275  db2 close
276}
277
278
279finish_test
280