gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/walbak.test (about) 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 forcedelete 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.6.1 { 83 hexio_read test.db 18 2 84 } {0202} 85 do_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]] 93 do_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]] 97 do_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 # 105 proc sig {{db db}} { 106 $db eval { 107 PRAGMA integrity_check; 108 SELECT md5sum(a, b) FROM t1; 109 } 110 } 111 db close 112 delete_file test.db 113 sqlite3 db test.db 114 do_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 } {} 129 do_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 136 do_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} 142 do_test walbak-2.4 { 143 string compare [sig db] [sig db2] 144 } {0} 145 146 do_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} 158 do_test walbak-2.6 { 159 B step 1000 160 } {SQLITE_BUSY} 161 do_test walbak-2.7 { 162 execsql COMMIT 163 list [B step 1000] [B finish] 164 } {SQLITE_DONE SQLITE_OK} 165 do_test walbak-2.8 { 166 string compare [sig db] [sig db2] 167 } {0} 168 169 do_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} 181 do_test walbak-2.10 { 182 B step 1000 183 } {SQLITE_BUSY} 184 do_test walbak-2.11 { 185 execsql ROLLBACK 186 set sigB [sig db] 187 list [B step 1000] [B finish] 188 } {SQLITE_DONE SQLITE_OK} 189 do_test walbak-2.12 { 190 string compare [sig db] [sig db2] 191 } {0} 192 db2 close 193 db 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 # 209 foreach {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 # 314 foreach {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 359 finish_test