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