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 28 set testdir [file dirname $argv0] 29 source $testdir/tester.tcl 30 ifcapable !wal {finish_test ; return } 31 32 db close 33 34 set seed 0 35 set REPEATS 100 36 37 # walcrash-1.* 38 # 39 for {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 # 76 for {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 # 145 for {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 # 173 for {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 # 214 for {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 # 264 for {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 295 finish_test 296 297
