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 forcedelete 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.6.1 { 83 hexio_read test.db 18 2 84} {0202} 85do_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]] 93do_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]] 97do_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# 105proc sig {{db db}} { 106 $db eval { 107 PRAGMA integrity_check; 108 SELECT md5sum(a, b) FROM t1; 109 } 110} 111db close 112delete_file test.db 113sqlite3 db test.db 114do_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} {} 129do_test walbak-2.2 { 130 db backup abc.db 131 sqlite3 db2 abc.db 132 string compare [sig db] [sig db2] 133} {0} 134 135do_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} 141do_test walbak-2.4 { 142 string compare [sig db] [sig db2] 143} {0} 144 145do_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} 157do_test walbak-2.6 { 158 B step 1000 159} {SQLITE_BUSY} 160do_test walbak-2.7 { 161 execsql COMMIT 162 list [B step 1000] [B finish] 163} {SQLITE_DONE SQLITE_OK} 164do_test walbak-2.8 { 165 string compare [sig db] [sig db2] 166} {0} 167 168do_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} 180do_test walbak-2.10 { 181 B step 1000 182} {SQLITE_BUSY} 183do_test walbak-2.11 { 184 execsql ROLLBACK 185set sigB [sig db] 186 list [B step 1000] [B finish] 187} {SQLITE_DONE SQLITE_OK} 188do_test walbak-2.12 { 189 string compare [sig db] [sig db2] 190} {0} 191db2 close 192db 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# 208foreach {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# 312foreach {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 357finish_test 358