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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 source $testdir/wal_common.tcl 19 source $testdir/malloc_common.tcl 20 21 do_not_use_codec 22 23 ifcapable !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 # 38 do_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} 50 do_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] 55 do_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} 62 do_test walbak-1.3 { 63 execsql { PRAGMA integrity_check } db2 64 } {ok} 65 db2 close 66 67 # Try a VACUUM on a WAL database. 68 # 69 do_test walbak-1.4 { 70 execsql { 71 VACUUM; 72 PRAGMA main.journal_mode; 73 } 74 } {wal} 75 do_test walbak-1.5 { 76 list [file size test.db] [file size test.db-wal] 77 } [list 1024 [wal_file_size 6 1024]] 78 do_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]] 82 do_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]] 90 do_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]] 94 do_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 # 102 proc sig {{db db}} { 103 $db eval { 104 PRAGMA integrity_check; 105 SELECT md5sum(a, b) FROM t1; 106 } 107 } 108 db close 109 file delete test.db 110 sqlite3 db test.db 111 do_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 } {} 126 do_test walbak-2.2 { 127 db backup abc.db 128 sqlite3 db2 abc.db 129 string compare [sig db] [sig db2] 130 } {0} 131 132 do_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} 138 do_test walbak-2.4 { 139 string compare [sig db] [sig db2] 140 } {0} 141 142 do_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} 154 do_test walbak-2.6 { 155 B step 1000 156 } {SQLITE_BUSY} 157 do_test walbak-2.7 { 158 execsql COMMIT 159 list [B step 1000] [B finish] 160 } {SQLITE_DONE SQLITE_OK} 161 do_test walbak-2.8 { 162 string compare [sig db] [sig db2] 163 } {0} 164 165 do_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} 177 do_test walbak-2.10 { 178 B step 1000 179 } {SQLITE_BUSY} 180 do_test walbak-2.11 { 181 execsql ROLLBACK 182 set sigB [sig db] 183 list [B step 1000] [B finish] 184 } {SQLITE_DONE SQLITE_OK} 185 do_test walbak-2.12 { 186 string compare [sig db] [sig db2] 187 } {0} 188 db2 close 189 db 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 # 205 foreach {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 279 finish_test 280
