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 forcedelete abc.db 131 db backup abc.db 132 sqlite3 db2 abc.db 133 string compare [sig db] [sig db2] 134} {0} 135 136do_test walbak-2.3 { 137 sqlite3_backup B db2 main db main 138 B step 50 139 execsql { UPDATE t1 SET b = randomblob(500) } 140 list [B step 1000] [B finish] 141} {SQLITE_DONE SQLITE_OK} 142do_test walbak-2.4 { 143 string compare [sig db] [sig db2] 144} {0} 145 146do_test walbak-2.5 { 147 db close 148 sqlite3 db test.db 149 execsql { PRAGMA cache_size = 10 } 150 sqlite3_backup B db2 main db main 151 B step 50 152 execsql { 153 BEGIN; 154 UPDATE t1 SET b = randomblob(500); 155 } 156 expr [file size test.db-wal] > 10*1024 157} {1} 158do_test walbak-2.6 { 159 B step 1000 160} {SQLITE_BUSY} 161do_test walbak-2.7 { 162 execsql COMMIT 163 list [B step 1000] [B finish] 164} {SQLITE_DONE SQLITE_OK} 165do_test walbak-2.8 { 166 string compare [sig db] [sig db2] 167} {0} 168 169do_test walbak-2.9 { 170 db close 171 sqlite3 db test.db 172 execsql { PRAGMA cache_size = 10 } 173 sqlite3_backup B db2 main db main 174 B step 50 175 execsql { 176 BEGIN; 177 UPDATE t1 SET b = randomblob(500); 178 } 179 expr [file size test.db-wal] > 10*1024 180} {1} 181do_test walbak-2.10 { 182 B step 1000 183} {SQLITE_BUSY} 184do_test walbak-2.11 { 185 execsql ROLLBACK 186set sigB [sig db] 187 list [B step 1000] [B finish] 188} {SQLITE_DONE SQLITE_OK} 189do_test walbak-2.12 { 190 string compare [sig db] [sig db2] 191} {0} 192db2 close 193db close 194 195#------------------------------------------------------------------------- 196# Run some backup operations to copy back and forth between WAL and: 197# 198# walbak-3.1.*: an in-memory database 199# 200# walbak-3.2.*: a temporary database 201# 202# walbak-3.3.*: a database in rollback mode. 203# 204# walbak-3.4.*: a database in rollback mode that (initially) uses a 205# different page-size. 206# 207# Check that this does not confuse any connected clients. 208# 209foreach {tn setup} { 210 1 { 211 sqlite3 db test.db 212 sqlite3 db2 :memory: 213 db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } 214 db2 eval { PRAGMA page_size = 1024 } 215 } 216 217 2 { 218 sqlite3 db test.db 219 sqlite3 db2 "" 220 db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } 221 db2 eval { PRAGMA page_size = 1024 } 222 } 223 224 3 { 225 sqlite3 db test.db 226 sqlite3 db2 test.db2 227 db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } 228 db2 eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = PERSIST } 229 } 230 231 4 { 232 sqlite3 db test.db 233 sqlite3 db2 test.db2 234 db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } 235 db2 eval { 236 PRAGMA page_size = 2048; 237 PRAGMA journal_mode = PERSIST; 238 CREATE TABLE xx(x); 239 } 240 } 241 242} { 243 if {$tn==4 && [sqlite3 -has-codec]} continue 244 foreach f [glob -nocomplain test.db*] { forcedelete $f } 245 246 eval $setup 247 248 do_test walbak-3.$tn.1 { 249 execsql { 250 CREATE TABLE t1(a, b); 251 INSERT INTO t1 VALUES(1, 2); 252 INSERT INTO t1 VALUES(3, 4); 253 SELECT * FROM t1; 254 } 255 } {1 2 3 4} 256 257 do_test walbak-3.$tn.2 { 258 sqlite3_backup B db2 main db main 259 B step 10000 260 B finish 261 execsql { SELECT * FROM t1 } db2 262 } {1 2 3 4} 263 264 do_test walbak-3.$tn.3 { 265 execsql { 266 INSERT INTO t1 VALUES(5, 6); 267 INSERT INTO t1 VALUES(7, 8); 268 SELECT * FROM t1; 269 } db2 270 } {1 2 3 4 5 6 7 8} 271 272 do_test walbak-3.$tn.4 { 273 sqlite3_backup B db main db2 main 274 B step 10000 275 B finish 276 execsql { SELECT * FROM t1 } 277 } {1 2 3 4 5 6 7 8} 278 279 # Check that [db] is still in WAL mode. 280 do_test walbak-3.$tn.5 { 281 execsql { PRAGMA journal_mode } 282 } {wal} 283 do_test walbak-3.$tn.6 { 284 execsql { PRAGMA wal_checkpoint } 285 hexio_read test.db 18 2 286 } {0202} 287 288 # If it was not an in-memory database, check that [db2] is still in 289 # rollback mode. 290 if {[file exists test.db2]} { 291 do_test walbak-3.$tn.7 { 292 execsql { PRAGMA journal_mode } db2 293 } {wal} 294 do_test walbak-3.$tn.8 { 295 execsql { PRAGMA wal_checkpoint } 296 hexio_read test.db 18 2 297 } {0202} 298 } 299 300 db close 301 db2 close 302} 303 304#------------------------------------------------------------------------- 305# Test that the following holds when a backup operation is run: 306# 307# Source | Destination inital | Destination final 308# --------------------------------------------------- 309# Rollback Rollback Rollback 310# Rollback WAL WAL 311# WAL Rollback WAL 312# WAL WAL WAL 313# 314foreach {tn src dest dest_final} { 315 1 delete delete delete 316 2 delete wal wal 317 3 wal delete wal 318 4 wal wal wal 319} { 320 catch { db close } 321 catch { db2 close } 322 forcedelete test.db test.db2 323 324 do_test walbak-4.$tn.1 { 325 sqlite3 db test.db 326 db eval "PRAGMA journal_mode = $src" 327 db eval { 328 CREATE TABLE t1(a, b); 329 INSERT INTO t1 VALUES('I', 'II'); 330 INSERT INTO t1 VALUES('III', 'IV'); 331 } 332 333 sqlite3 db2 test.db2 334 db2 eval "PRAGMA journal_mode = $dest" 335 db2 eval { 336 CREATE TABLE t2(x, y); 337 INSERT INTO t2 VALUES('1', '2'); 338 INSERT INTO t2 VALUES('3', '4'); 339 } 340 } {} 341 342 do_test walbak-4.$tn.2 { execsql { PRAGMA journal_mode } db } $src 343 do_test walbak-4.$tn.3 { execsql { PRAGMA journal_mode } db2 } $dest 344 345 do_test walbak-4.$tn.4 { db backup test.db2 } {} 346 do_test walbak-4.$tn.5 { 347 execsql { SELECT * FROM t1 } db2 348 } {I II III IV} 349 do_test walbak-4.$tn.5 { execsql { PRAGMA journal_mode } db2 } $dest_final 350 351 352 db2 close 353 do_test walbak-4.$tn.6 { file exists test.db2-wal } 0 354 sqlite3 db2 test.db2 355 do_test walbak-4.$tn.7 { execsql { PRAGMA journal_mode } db2 } $dest_final 356} 357 358 359finish_test 360