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