1# 2010 February 8
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 when
13# recovering a database following a simulated system failure in 
14# "PRAGMA journal_mode=WAL" mode.
15#
16
17#
18# These are 'warm-body' tests of database recovery used while developing 
19# the WAL code. They serve to prove that a few really simple cases work:
20#
21# walcrash-1.*: Recover a database.
22# walcrash-2.*: Recover a database where the failed transaction spanned more
23#               than one page.
24# walcrash-3.*: Recover multiple databases where the failed transaction 
25#               was a multi-file transaction.
26#
27
28set testdir [file dirname $argv0]
29source $testdir/tester.tcl
30ifcapable !wal {finish_test ; return }
31
32db close
33
34set seed 0
35set REPEATS 100
36
37# walcrash-1.*
38#
39for {set i 1} {$i < $REPEATS} {incr i} {
40  file delete -force test.db test.db-wal
41  do_test walcrash-1.$i.1 {
42    crashsql -delay 4 -file test.db-wal -seed [incr seed] {
43      PRAGMA journal_mode = WAL;
44      CREATE TABLE t1(a, b);
45      INSERT INTO t1 VALUES(1, 1);
46      INSERT INTO t1 VALUES(2, 3);
47      INSERT INTO t1 VALUES(3, 6);
48    }
49  } {1 {child process exited abnormally}}
50  do_test walcrash-1.$i.2 {
51    sqlite3 db test.db
52    execsql { SELECT sum(a)==max(b) FROM t1 }
53  } {1}
54  integrity_check walcrash-1.$i.3
55  db close
56  
57  do_test walcrash-1.$i.4 {
58    crashsql -delay 2 -file test.db-wal -seed [incr seed] {
59      INSERT INTO t1 VALUES(4, (SELECT sum(a) FROM t1) + 4);
60      INSERT INTO t1 VALUES(5, (SELECT sum(a) FROM t1) + 5);
61    }
62  } {1 {child process exited abnormally}}
63  do_test walcrash-1.$i.5 {
64    sqlite3 db test.db
65    execsql { SELECT sum(a)==max(b) FROM t1 }
66  } {1}
67  integrity_check walcrash-1.$i.6
68  do_test walcrash-1.$i.7 {
69    execsql { PRAGMA main.journal_mode }
70  } {wal}
71  db close
72}
73
74# walcrash-2.*
75#
76for {set i 1} {$i < $REPEATS} {incr i} {
77  file delete -force test.db test.db-wal
78  do_test walcrash-2.$i.1 {
79    crashsql -delay 4 -file test.db-wal -seed [incr seed] {
80      PRAGMA journal_mode = WAL;
81      CREATE TABLE t1(a PRIMARY KEY, b);
82      INSERT INTO t1 VALUES(1, 2);
83      INSERT INTO t1 VALUES(3, 4);
84      INSERT INTO t1 VALUES(5, 9);
85    }
86  } {1 {child process exited abnormally}}
87  do_test walcrash-2.$i.2 {
88    sqlite3 db test.db
89    execsql { SELECT sum(a)==max(b) FROM t1 }
90  } {1}
91  integrity_check walcrash-2.$i.3
92  db close
93  
94  do_test walcrash-2.$i.4 {
95    crashsql -delay 2 -file test.db-wal -seed [incr seed] {
96      INSERT INTO t1 VALUES(6, (SELECT sum(a) FROM t1) + 6);
97      INSERT INTO t1 VALUES(7, (SELECT sum(a) FROM t1) + 7);
98    }
99  } {1 {child process exited abnormally}}
100  do_test walcrash-2.$i.5 {
101    sqlite3 db test.db
102    execsql { SELECT sum(a)==max(b) FROM t1 }
103  } {1}
104  integrity_check walcrash-2.$i.6
105  do_test walcrash-2.$i.6 {
106    execsql { PRAGMA main.journal_mode }
107  } {wal}
108  db close
109}
110
111# walcrash-3.*
112#
113# for {set i 1} {$i < $REPEATS} {incr i} {
114#   file delete -force test.db test.db-wal
115#   file delete -force test2.db test2.db-wal
116# 
117#   do_test walcrash-3.$i.1 {
118#     crashsql -delay 2 -file test2.db-wal -seed [incr seed] {
119#       PRAGMA journal_mode = WAL;
120#       ATTACH 'test2.db' AS aux;
121#       CREATE TABLE t1(a PRIMARY KEY, b);
122#       CREATE TABLE aux.t2(a PRIMARY KEY, b);
123#       BEGIN;
124#         INSERT INTO t1 VALUES(1, 2);
125#         INSERT INTO t2 VALUES(1, 2);
126#       COMMIT;
127#     }
128#   } {1 {child process exited abnormally}}
129# 
130#   do_test walcrash-3.$i.2 {
131#     sqlite3_wal db test.db
132#     execsql { 
133#       ATTACH 'test2.db' AS aux;
134#       SELECT * FROM t1 EXCEPT SELECT * FROM t2;
135#     }
136#   } {}
137#   do_test walcrash-3.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
138#   do_test walcrash-3.$i.4 { execsql { PRAGMA aux.integrity_check  } } {ok}
139# 
140#   db close
141# }
142
143# walcrash-4.*
144#
145for {set i 1} {$i < $REPEATS} {incr i} {
146  file delete -force test.db test.db-wal
147  file delete -force test2.db test2.db-wal
148
149  do_test walcrash-4.$i.1 {
150    crashsql -delay 3 -file test.db-wal -seed [incr seed] -blocksize 4096 {
151      PRAGMA journal_mode = WAL;
152      PRAGMA page_size = 1024;
153      CREATE TABLE t1(a PRIMARY KEY, b);
154      INSERT INTO t1 VALUES(1, 2);
155      INSERT INTO t1 VALUES(3, 4);
156    }
157  } {1 {child process exited abnormally}}
158
159  do_test walcrash-4.$i.2 {
160    sqlite3 db test.db
161    execsql { 
162      SELECT * FROM t1 WHERE a = 1;
163    }
164  } {1 2}
165  do_test walcrash-4.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
166  do_test walcrash-4.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
167
168  db close
169}
170
171# walcrash-5.*
172#
173for {set i 1} {$i < $REPEATS} {incr i} {
174  file delete -force test.db test.db-wal
175  file delete -force test2.db test2.db-wal
176
177  do_test walcrash-5.$i.1 {
178    crashsql -delay 11 -file test.db-wal -seed [incr seed] -blocksize 4096 {
179      PRAGMA journal_mode = WAL;
180      PRAGMA page_size = 1024;
181      BEGIN;
182        CREATE TABLE t1(x PRIMARY KEY);
183        INSERT INTO t1 VALUES(randomblob(900));
184        INSERT INTO t1 VALUES(randomblob(900));
185        INSERT INTO t1 SELECT randomblob(900) FROM t1;           /* 4 */
186      COMMIT;
187      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 8 */
188      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
189      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
190      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 20 */
191      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 24 */
192      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 28 */
193      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 32 */
194
195      PRAGMA wal_checkpoint;
196      INSERT INTO t1 VALUES(randomblob(900));
197      INSERT INTO t1 VALUES(randomblob(900));
198      INSERT INTO t1 VALUES(randomblob(900));
199    }
200  } {1 {child process exited abnormally}}
201
202  do_test walcrash-5.$i.2 {
203    sqlite3 db test.db
204    execsql { SELECT count(*)==33 OR count(*)==34 FROM t1 WHERE x != 1 }
205  } {1}
206  do_test walcrash-5.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
207  do_test walcrash-5.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
208
209  db close
210}
211
212# walcrash-6.*
213#
214for {set i 1} {$i < $REPEATS} {incr i} {
215  file delete -force test.db test.db-wal
216  file delete -force test2.db test2.db-wal
217
218  do_test walcrash-6.$i.1 {
219    crashsql -delay 12 -file test.db-wal -seed [incr seed] -blocksize 512 {
220      PRAGMA journal_mode = WAL;
221      PRAGMA page_size = 1024;
222      BEGIN;
223        CREATE TABLE t1(x PRIMARY KEY);
224        INSERT INTO t1 VALUES(randomblob(900));
225        INSERT INTO t1 VALUES(randomblob(900));
226        INSERT INTO t1 SELECT randomblob(900) FROM t1;           /* 4 */
227      COMMIT;
228      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 8 */
229      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
230      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
231      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 20 */
232      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 24 */
233      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 28 */
234      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 32 */
235
236      PRAGMA wal_checkpoint;
237      INSERT INTO t1 VALUES(randomblob(900));
238      INSERT INTO t1 VALUES(randomblob(900));
239      INSERT INTO t1 VALUES(randomblob(900));
240    }
241  } {1 {child process exited abnormally}}
242
243  do_test walcrash-6.$i.2 {
244    sqlite3 db test.db
245    execsql { SELECT count(*)==34 OR count(*)==35 FROM t1 WHERE x != 1 }
246  } {1}
247  do_test walcrash-6.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
248  do_test walcrash-6.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
249
250  db close
251}
252
253#-------------------------------------------------------------------------
254# This test case simulates a crash while checkpointing the database. Page
255# 1 is one of the pages overwritten by the checkpoint. This is a special
256# case because it means the content of page 1 may be damaged. SQLite will
257# have to determine:
258#
259#   (a) that the database is a WAL database, and 
260#   (b) the database page-size
261#
262# based on the log file.
263#
264for {set i 1} {$i < $REPEATS} {incr i} {
265  file delete -force test.db test.db-wal
266
267  # Select a page-size for this test.
268  #
269  set pgsz [lindex {512 1024 2048 4096 8192 16384} [expr $i%6]]
270
271  do_test walcrash-7.$i.1 {
272    crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 "
273      PRAGMA page_size = $pgsz;
274      PRAGMA journal_mode = wal;
275      BEGIN;
276        CREATE TABLE t1(a, b);
277        INSERT INTO t1 VALUES(1, 2);
278      COMMIT;
279      PRAGMA wal_checkpoint;
280      CREATE INDEX i1 ON t1(a);
281      PRAGMA wal_checkpoint;
282    "
283  } {1 {child process exited abnormally}}
284
285  do_test walcrash-7.$i.2 {
286    sqlite3 db test.db
287    execsql { SELECT b FROM t1 WHERE a = 1 }
288  } {2}
289  do_test walcrash-7.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
290  do_test walcrash-7.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
291
292  db close
293}
294
295finish_test
296
297