1# 2010 April 13 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 "blocking-checkpoint" 13# operations. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $testdir/lock_common.tcl 19source $testdir/wal_common.tcl 20ifcapable !wal {finish_test ; return } 21do_not_use_codec 22 23set testprefix wal5 24 25proc db_page_count {{file test.db}} { expr [file size $file] / 1024 } 26proc wal_page_count {{file test.db}} { wal_frame_count ${file}-wal 1024 } 27 28 29# A checkpoint may be requested either using the C API or by executing 30# an SQL PRAGMA command. To test both methods, all tests in this file are 31# run twice - once using each method to request checkpoints. 32# 33foreach {testprefix do_wal_checkpoint} { 34 35 wal5-pragma { 36 proc do_wal_checkpoint { dbhandle args } { 37 array set a $args 38 foreach key [array names a] { 39 if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" } 40 } 41 42 set sql "PRAGMA " 43 if {[info exists a(-db)]} { append sql "$a(-db)." } 44 append sql "wal_checkpoint" 45 if {[info exists a(-mode)]} { append sql " = $a(-mode)" } 46 47 uplevel [list $dbhandle eval $sql] 48 } 49 } 50 51 wal5-capi { 52 proc do_wal_checkpoint { dbhandle args } { 53 set a(-mode) passive 54 array set a $args 55 foreach key [array names a] { 56 if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" } 57 } 58 59 set vals {restart full truncate} 60 if {[lsearch -exact $vals $a(-mode)]<0} { set a(-mode) passive } 61 62 set cmd [list sqlite3_wal_checkpoint_v2 $dbhandle $a(-mode)] 63 if {[info exists a(-db)]} { lappend sql $a(-db) } 64 65 uplevel $cmd 66 } 67 } 68} { 69 70 eval $do_wal_checkpoint 71 72 do_multiclient_test tn { 73 74 set ::nBusyHandler 0 75 set ::busy_handler_script "" 76 proc busyhandler {n} { 77 incr ::nBusyHandler 78 eval $::busy_handler_script 79 return 0 80 } 81 82 proc reopen_all {} { 83 code1 {db close} 84 code2 {db2 close} 85 code3 {db3 close} 86 87 code1 {sqlite3 db test.db} 88 code2 {sqlite3 db2 test.db} 89 code3 {sqlite3 db3 test.db} 90 91 sql1 { PRAGMA synchronous = NORMAL } 92 code1 { db busy busyhandler } 93 } 94 95 do_test 1.$tn.1 { 96 reopen_all 97 sql1 { 98 PRAGMA page_size = 1024; 99 PRAGMA auto_vacuum = 0; 100 CREATE TABLE t1(x, y); 101 PRAGMA journal_mode = WAL; 102 INSERT INTO t1 VALUES(1, zeroblob(1200)); 103 INSERT INTO t1 VALUES(2, zeroblob(1200)); 104 INSERT INTO t1 VALUES(3, zeroblob(1200)); 105 } 106 expr [file size test.db] / 1024 107 } {2} 108 109 # Have connection 2 grab a read-lock on the current snapshot. 110 do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3} 111 112 # Attempt a checkpoint. 113 do_test 1.$tn.3 { 114 code1 { do_wal_checkpoint db } 115 list [db_page_count] [wal_page_count] 116 } {5 9} 117 118 # Write to the db again. The log cannot wrap because of the lock still 119 # held by connection 2. The busy-handler has not yet been invoked. 120 do_test 1.$tn.4 { 121 sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) } 122 list [db_page_count] [wal_page_count] $::nBusyHandler 123 } {5 12 0} 124 125 # Now do a blocking-checkpoint. Set the busy-handler up so that connection 126 # 2 releases its lock on the 6th invocation. The checkpointer should then 127 # proceed to checkpoint the entire log file. Next write should go to the 128 # start of the log file. 129 # 130 set ::busy_handler_script { if {$n==5} { sql2 COMMIT } } 131 do_test 1.$tn.5 { 132 code1 { do_wal_checkpoint db -mode restart } 133 list [db_page_count] [wal_page_count] $::nBusyHandler 134 } {6 12 6} 135 do_test 1.$tn.6 { 136 set ::nBusyHandler 0 137 sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) } 138 list [db_page_count] [wal_page_count] $::nBusyHandler 139 } {6 12 0} 140 141 do_test 1.$tn.7 { 142 reopen_all 143 list [db_page_count] [wal_page_count] $::nBusyHandler 144 } [expr {[nonzero_reserved_bytes]?"/# # 0/":"7 0 0"}] 145 146 do_test 1.$tn.8 { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5} 147 do_test 1.$tn.9 { 148 sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) } 149 list [db_page_count] [wal_page_count] $::nBusyHandler 150 } [expr {[nonzero_reserved_bytes]?"/# # #/":"7 5 0"}] 151 do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6} 152 153 set ::busy_handler_script { 154 if {$n==5} { sql2 COMMIT } 155 if {$n==6} { set ::db_file_size [db_page_count] } 156 if {$n==7} { sql3 COMMIT } 157 } 158 do_test 1.$tn.11 { 159 code1 { do_wal_checkpoint db -mode restart } 160 list [db_page_count] [wal_page_count] $::nBusyHandler 161 } [expr {[nonzero_reserved_bytes]?"/# # #/":"10 5 8"}] 162 do_test 1.$tn.12 { set ::db_file_size } 10 163 } 164 165 #------------------------------------------------------------------------- 166 # This block of tests explores checkpoint operations on more than one 167 # database file. 168 # 169 proc setup_and_attach_aux {} { 170 sql1 { ATTACH 'test.db2' AS aux } 171 sql2 { ATTACH 'test.db2' AS aux } 172 sql3 { ATTACH 'test.db2' AS aux } 173 sql1 { 174 PRAGMA aux.auto_vacuum = 0; 175 PRAGMA main.auto_vacuum = 0; 176 PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL; 177 PRAGMA aux.page_size=1024; PRAGMA aux.journal_mode=WAL; 178 } 179 } 180 181 proc file_page_counts {} { 182 list [db_page_count test.db ] \ 183 [wal_page_count test.db ] \ 184 [db_page_count test.db2] \ 185 [wal_page_count test.db2] 186 } 187 188 # Test that executing "PRAGMA wal_checkpoint" checkpoints all attached 189 # databases, not just the main db. In capi mode, check that this is 190 # true if a NULL pointer is passed to wal_checkpoint_v2() in place of a 191 # database name. 192 do_multiclient_test tn { 193 setup_and_attach_aux 194 do_test 2.1.$tn.1 { 195 sql1 { 196 CREATE TABLE t1(a, b); 197 INSERT INTO t1 VALUES(1, 2); 198 CREATE TABLE aux.t2(a, b); 199 INSERT INTO t2 VALUES(1, 2); 200 } 201 } {} 202 do_test 2.2.$tn.2 { file_page_counts } {1 3 1 3} 203 do_test 2.1.$tn.3 { code1 { do_wal_checkpoint db } } {0 3 3} 204 do_test 2.1.$tn.4 { file_page_counts } {2 3 2 3} 205 } 206 207 do_multiclient_test tn { 208 setup_and_attach_aux 209 do_test 2.2.$tn.1 { 210 execsql { 211 CREATE TABLE t1(a, b); 212 INSERT INTO t1 VALUES(1, 2); 213 CREATE TABLE aux.t2(a, b); 214 INSERT INTO t2 VALUES(1, 2); 215 INSERT INTO t2 VALUES(3, 4); 216 } 217 } {} 218 do_test 2.2.$tn.2 { file_page_counts } {1 3 1 4} 219 do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} 220 do_test 2.2.$tn.4 { code1 { do_wal_checkpoint db -mode restart } } {1 3 3} 221 do_test 2.2.$tn.5 { file_page_counts } {2 3 2 4} 222 } 223 224 do_multiclient_test tn { 225 setup_and_attach_aux 226 do_test 2.3.$tn.1 { 227 execsql { 228 CREATE TABLE t1(a, b); 229 INSERT INTO t1 VALUES(1, 2); 230 CREATE TABLE aux.t2(a, b); 231 INSERT INTO t2 VALUES(1, 2); 232 } 233 } {} 234 do_test 2.3.$tn.2 { file_page_counts } {1 3 1 3} 235 do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} 236 do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {} 237 do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {} 238 do_test 2.3.$tn.6 { file_page_counts } {1 4 1 4} 239 do_test 2.3.$tn.7 { code1 { do_wal_checkpoint db -mode full } } {1 4 3} 240 241 # The checkpoint above only writes page 1 of the db file. The other 242 # page (page 2) is locked by the read-transaction opened by the 243 # [sql2] commmand above. So normally, the db is 1 page in size here. 244 # However, in mmap() mode, the db is pre-allocated to 2 pages at the 245 # start of the checkpoint, even though page 2 cannot be written. 246 set nDb 2 247 if {[permutation]!="mmap"} {set nDb 1} 248 ifcapable !mmap {set nDb 1} 249 do_test 2.3.$tn.8 { file_page_counts } [list $nDb 4 2 4] 250 } 251 252 # Check that checkpoints block on the correct locks. And respond correctly 253 # if they cannot obtain those locks. There are three locks that a checkpoint 254 # may block on (in the following order): 255 # 256 # 1. The writer lock: FULL and RESTART checkpoints block until any writer 257 # process releases its lock. 258 # 259 # 2. Readers using part of the log file. FULL and RESTART checkpoints block 260 # until readers using part (but not all) of the log file have finished. 261 # 262 # 3. Readers using any of the log file. After copying data into the 263 # database file, RESTART checkpoints block until readers using any part 264 # of the log file have finished. 265 # 266 # This test case involves running a checkpoint while there exist other 267 # processes holding all three types of locks. 268 # 269 foreach {tn1 checkpoint busy_on ckpt_expected expected} { 270 1 PASSIVE - {0 3 3} - 271 2 TYPO - {0 3 3} - 272 273 3 FULL - {0 4 4} 2 274 4 FULL 1 {1 3 3} 1 275 5 FULL 2 {1 4 3} 2 276 6 FULL 3 {0 4 4} 2 277 278 7 RESTART - {0 4 4} 3 279 8 RESTART 1 {1 3 3} 1 280 9 RESTART 2 {1 4 3} 2 281 10 RESTART 3 {1 4 4} 3 282 283 11 TRUNCATE - {0 0 0} 3 284 12 TRUNCATE 1 {1 3 3} 1 285 13 TRUNCATE 2 {1 4 3} 2 286 14 TRUNCATE 3 {1 4 4} 3 287 288 } { 289 do_multiclient_test tn { 290 setup_and_attach_aux 291 292 proc busyhandler {x} { 293 set ::max_busyhandler $x 294 if {$::busy_on!="-" && $x==$::busy_on} { return 1 } 295 switch -- $x { 296 1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" } 297 2 { sql3 "COMMIT" } 298 3 { sql2 "COMMIT" } 299 } 300 return 0 301 } 302 set ::max_busyhandler - 303 304 do_test 2.4.$tn1.$tn.1 { 305 sql1 { 306 CREATE TABLE t1(a, b); 307 INSERT INTO t1 VALUES(1, 2); 308 } 309 sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) } 310 sql3 { BEGIN; SELECT * FROM t1 } 311 } {1 2} 312 313 do_test 2.4.$tn1.$tn.2 { 314 code1 { db busy busyhandler } 315 code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] } 316 } $ckpt_expected 317 do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected 318 } 319 } 320 321 322 do_multiclient_test tn { 323 324 code1 $do_wal_checkpoint 325 code2 $do_wal_checkpoint 326 code3 $do_wal_checkpoint 327 328 do_test 3.$tn.1 { 329 sql1 { 330 PRAGMA auto_vacuum = 0; 331 PRAGMA journal_mode = WAL; 332 PRAGMA synchronous = normal; 333 CREATE TABLE t1(x, y); 334 } 335 336 sql2 { PRAGMA journal_mode } 337 sql3 { PRAGMA journal_mode } 338 } {wal} 339 340 do_test 3.$tn.2 { code2 { do_wal_checkpoint db2 } } {0 2 2} 341 342 do_test 3.$tn.3 { code2 { do_wal_checkpoint db2 } } {0 2 2} 343 344 do_test 3.$tn.4 { code3 { do_wal_checkpoint db3 } } {0 2 2} 345 346 code1 {db close} 347 code2 {db2 close} 348 code3 {db3 close} 349 350 code1 {sqlite3 db test.db} 351 code2 {sqlite3 db2 test.db} 352 code3 {sqlite3 db3 test.db} 353 354 do_test 3.$tn.5 { sql3 { PRAGMA journal_mode } } {wal} 355 356 do_test 3.$tn.6 { code3 { do_wal_checkpoint db3 } } {0 0 0} 357 } 358 359 # Test SQLITE_CHECKPOINT_TRUNCATE. 360 # 361 do_multiclient_test tn { 362 363 code1 $do_wal_checkpoint 364 code2 $do_wal_checkpoint 365 code3 $do_wal_checkpoint 366 367 do_test 4.$tn.1 { 368 sql1 { 369 PRAGMA page_size = 1024; 370 PRAGMA auto_vacuum = 0; 371 PRAGMA journal_mode = WAL; 372 PRAGMA synchronous = normal; 373 CREATE TABLE t1(x, y); 374 CREATE INDEX i1 ON t1(x, y); 375 INSERT INTO t1 VALUES(1, 2); 376 INSERT INTO t1 VALUES(3, 4); 377 } 378 file size test.db-wal 379 } [wal_file_size 8 1024] 380 381 do_test 4.$tn.2 { do_wal_checkpoint db -mode truncate } {0 0 0} 382 do_test 4.$tn.3 { file size test.db-wal } 0 383 384 do_test 4.$tn.4 { 385 sql2 { SELECT * FROM t1 } 386 } {1 2 3 4} 387 388 do_test 4.$tn.5 { 389 sql2 { INSERT INTO t1 VALUES('a', 'b') } 390 file size test.db-wal 391 } [wal_file_size 2 1024] 392 393 } 394 395 # Test that FULL, RESTART and TRUNCATE callbacks block on other clients 396 # and truncate the wal file as required even if the entire wal file has 397 # already been checkpointed when they are invoked. 398 # 399 do_multiclient_test tn { 400 401 code1 $do_wal_checkpoint 402 code2 $do_wal_checkpoint 403 code3 $do_wal_checkpoint 404 405 do_test 5.$tn.1 { 406 sql1 { 407 PRAGMA page_size = 1024; 408 PRAGMA auto_vacuum = 0; 409 PRAGMA journal_mode = WAL; 410 PRAGMA synchronous = normal; 411 CREATE TABLE t1(x, y); 412 CREATE INDEX i1 ON t1(x, y); 413 INSERT INTO t1 VALUES(1, 2); 414 INSERT INTO t1 VALUES(3, 4); 415 INSERT INTO t1 VALUES(5, 6); 416 } 417 file size test.db-wal 418 } [wal_file_size 10 1024] 419 420 do_test 5.$tn.2 { 421 sql2 { BEGIN; SELECT * FROM t1 } 422 } {1 2 3 4 5 6} 423 424 do_test 5.$tn.3 { do_wal_checkpoint db -mode passive } {0 10 10} 425 426 do_test 5.$tn.4 { 427 sql3 { BEGIN; INSERT INTO t1 VALUES(7, 8); } 428 } {} 429 430 do_test 5.$tn.5 { do_wal_checkpoint db -mode passive } {0 10 10} 431 do_test 5.$tn.6 { do_wal_checkpoint db -mode full } {1 10 10} 432 433 do_test 5.$tn.7 { sql3 { ROLLBACK } } {} 434 435 do_test 5.$tn.8 { do_wal_checkpoint db -mode full } {0 10 10} 436 do_test 5.$tn.9 { do_wal_checkpoint db -mode truncate } {1 10 10} 437 438 do_test 5.$tn.10 { 439 file size test.db-wal 440 } [wal_file_size 10 1024] 441 442 proc xBusyHandler {n} { sql2 { COMMIT } ; return 0 } 443 db busy xBusyHandler 444 445 do_test 5.$tn.11 { do_wal_checkpoint db -mode truncate } {0 0 0} 446 do_test 5.$tn.12 { file size test.db-wal } 0 447 448 do_test 5.$tn.13 { 449 sql1 { 450 INSERT INTO t1 VALUES(7, 8); 451 INSERT INTO t1 VALUES(9, 10); 452 SELECT * FROM t1; 453 } 454 } {1 2 3 4 5 6 7 8 9 10} 455 456 do_test 5.$tn.14 { 457 sql2 { BEGIN; SELECT * FROM t1 } 458 } {1 2 3 4 5 6 7 8 9 10} 459 460 proc xBusyHandler {n} { return 1 } 461 do_test 5.$tn.15 { do_wal_checkpoint db -mode truncate } {1 4 4} 462 do_test 5.$tn.16 { file size test.db-wal } [wal_file_size 4 1024] 463 464 do_test 5.$tn.17 { do_wal_checkpoint db -mode restart } {1 4 4} 465 466 proc xBusyHandler {n} { sql2 { COMMIT } ; return 0 } 467 db busy xBusyHandler 468 do_test 5.$tn.18 { do_wal_checkpoint db -mode restart } {0 4 4} 469 do_test 5.$tn.19 { file size test.db-wal } [wal_file_size 4 1024] 470 471 do_test 5.$tn.20 { do_wal_checkpoint db -mode truncate } {0 0 0} 472 do_test 5.$tn.21 { file size test.db-wal } 0 473 } 474 475} 476 477 478finish_test 479