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 if {$a(-mode)!="restart" && $a(-mode)!="full"} { set a(-mode) passive } 59 60 set cmd [list sqlite3_wal_checkpoint_v2 $dbhandle $a(-mode)] 61 if {[info exists a(-db)]} { lappend sql $a(-db) } 62 63 uplevel $cmd 64 } 65 } 66} { 67 68 eval $do_wal_checkpoint 69 70 do_multiclient_test tn { 71 72 set ::nBusyHandler 0 73 set ::busy_handler_script "" 74 proc busyhandler {n} { 75 incr ::nBusyHandler 76 eval $::busy_handler_script 77 return 0 78 } 79 80 proc reopen_all {} { 81 code1 {db close} 82 code2 {db2 close} 83 code3 {db3 close} 84 85 code1 {sqlite3 db test.db} 86 code2 {sqlite3 db2 test.db} 87 code3 {sqlite3 db3 test.db} 88 89 sql1 { PRAGMA synchronous = NORMAL } 90 code1 { db busy busyhandler } 91 } 92 93 do_test 1.$tn.1 { 94 reopen_all 95 sql1 { 96 PRAGMA page_size = 1024; 97 PRAGMA auto_vacuum = 0; 98 CREATE TABLE t1(x, y); 99 PRAGMA journal_mode = WAL; 100 INSERT INTO t1 VALUES(1, zeroblob(1200)); 101 INSERT INTO t1 VALUES(2, zeroblob(1200)); 102 INSERT INTO t1 VALUES(3, zeroblob(1200)); 103 } 104 expr [file size test.db] / 1024 105 } {2} 106 107 # Have connection 2 grab a read-lock on the current snapshot. 108 do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3} 109 110 # Attempt a checkpoint. 111 do_test 1.$tn.3 { 112 code1 { do_wal_checkpoint db } 113 list [db_page_count] [wal_page_count] 114 } {5 9} 115 116 # Write to the db again. The log cannot wrap because of the lock still 117 # held by connection 2. The busy-handler has not yet been invoked. 118 do_test 1.$tn.4 { 119 sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) } 120 list [db_page_count] [wal_page_count] $::nBusyHandler 121 } {5 12 0} 122 123 # Now do a blocking-checkpoint. Set the busy-handler up so that connection 124 # 2 releases its lock on the 6th invocation. The checkpointer should then 125 # proceed to checkpoint the entire log file. Next write should go to the 126 # start of the log file. 127 # 128 set ::busy_handler_script { if {$n==5} { sql2 COMMIT } } 129 do_test 1.$tn.5 { 130 code1 { do_wal_checkpoint db -mode restart } 131 list [db_page_count] [wal_page_count] $::nBusyHandler 132 } {6 12 6} 133 do_test 1.$tn.6 { 134 set ::nBusyHandler 0 135 sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) } 136 list [db_page_count] [wal_page_count] $::nBusyHandler 137 } {6 12 0} 138 139 do_test 1.$tn.7 { 140 reopen_all 141 list [db_page_count] [wal_page_count] $::nBusyHandler 142 } {7 0 0} 143 144 do_test 1.$tn.8 { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5} 145 do_test 1.$tn.9 { 146 sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) } 147 list [db_page_count] [wal_page_count] $::nBusyHandler 148 } {7 5 0} 149 do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6} 150 151 set ::busy_handler_script { 152 if {$n==5} { sql2 COMMIT } 153 if {$n==6} { set ::db_file_size [db_page_count] } 154 if {$n==7} { sql3 COMMIT } 155 } 156 do_test 1.$tn.11 { 157 code1 { do_wal_checkpoint db -mode restart } 158 list [db_page_count] [wal_page_count] $::nBusyHandler 159 } {10 5 8} 160 do_test 1.$tn.12 { set ::db_file_size } 10 161 } 162 163 #------------------------------------------------------------------------- 164 # This block of tests explores checkpoint operations on more than one 165 # database file. 166 # 167 proc setup_and_attach_aux {} { 168 sql1 { ATTACH 'test.db2' AS aux } 169 sql2 { ATTACH 'test.db2' AS aux } 170 sql3 { ATTACH 'test.db2' AS aux } 171 sql1 { 172 PRAGMA aux.auto_vacuum = 0; 173 PRAGMA main.auto_vacuum = 0; 174 PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL; 175 PRAGMA aux.page_size=1024; PRAGMA aux.journal_mode=WAL; 176 } 177 } 178 179 proc file_page_counts {} { 180 list [db_page_count test.db ] \ 181 [wal_page_count test.db ] \ 182 [db_page_count test.db2] \ 183 [wal_page_count test.db2] 184 } 185 186 # Test that executing "PRAGMA wal_checkpoint" checkpoints all attached 187 # databases, not just the main db. In capi mode, check that this is 188 # true if a NULL pointer is passed to wal_checkpoint_v2() in place of a 189 # database name. 190 do_multiclient_test tn { 191 setup_and_attach_aux 192 do_test 2.1.$tn.1 { 193 sql1 { 194 CREATE TABLE t1(a, b); 195 INSERT INTO t1 VALUES(1, 2); 196 CREATE TABLE aux.t2(a, b); 197 INSERT INTO t2 VALUES(1, 2); 198 } 199 } {} 200 do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5} 201 do_test 2.1.$tn.3 { code1 { do_wal_checkpoint db } } {0 5 5} 202 do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5} 203 } 204 205 do_multiclient_test tn { 206 setup_and_attach_aux 207 do_test 2.2.$tn.1 { 208 execsql { 209 CREATE TABLE t1(a, b); 210 INSERT INTO t1 VALUES(1, 2); 211 CREATE TABLE aux.t2(a, b); 212 INSERT INTO t2 VALUES(1, 2); 213 INSERT INTO t2 VALUES(3, 4); 214 } 215 } {} 216 do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7} 217 do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} 218 do_test 2.2.$tn.4 { code1 { do_wal_checkpoint db -mode restart } } {1 5 5} 219 do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7} 220 } 221 222 do_multiclient_test tn { 223 setup_and_attach_aux 224 do_test 2.3.$tn.1 { 225 execsql { 226 CREATE TABLE t1(a, b); 227 INSERT INTO t1 VALUES(1, 2); 228 CREATE TABLE aux.t2(a, b); 229 INSERT INTO t2 VALUES(1, 2); 230 } 231 } {} 232 do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5} 233 do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} 234 do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {} 235 do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {} 236 do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7} 237 do_test 2.3.$tn.7 { code1 { do_wal_checkpoint db -mode full } } {1 7 5} 238 if {$tcl_platform(platform) == "windows"} { 239 # on unix, the size_hint is a no-op if no chunk size is set. 240 # the windows implementation does not have a similar check, 241 # and because of this, the db file size has an extra page. 242 do_test 2.3.$tn.8 { file_page_counts } {2 7 2 7} 243 } { 244 do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7} 245 } 246 } 247 248 # Check that checkpoints block on the correct locks. And respond correctly 249 # if they cannot obtain those locks. There are three locks that a checkpoint 250 # may block on (in the following order): 251 # 252 # 1. The writer lock: FULL and RESTART checkpoints block until any writer 253 # process releases its lock. 254 # 255 # 2. Readers using part of the log file. FULL and RESTART checkpoints block 256 # until readers using part (but not all) of the log file have finished. 257 # 258 # 3. Readers using any of the log file. After copying data into the 259 # database file, RESTART checkpoints block until readers using any part 260 # of the log file have finished. 261 # 262 # This test case involves running a checkpoint while there exist other 263 # processes holding all three types of locks. 264 # 265 foreach {tn1 checkpoint busy_on ckpt_expected expected} { 266 1 PASSIVE - {0 5 5} - 267 2 TYPO - {0 5 5} - 268 269 3 FULL - {0 7 7} 2 270 4 FULL 1 {1 5 5} 1 271 5 FULL 2 {1 7 5} 2 272 6 FULL 3 {0 7 7} 2 273 274 7 RESTART - {0 7 7} 3 275 8 RESTART 1 {1 5 5} 1 276 9 RESTART 2 {1 7 5} 2 277 10 RESTART 3 {1 7 7} 3 278 279 } { 280 do_multiclient_test tn { 281 setup_and_attach_aux 282 283 proc busyhandler {x} { 284 set ::max_busyhandler $x 285 if {$::busy_on!="-" && $x==$::busy_on} { return 1 } 286 switch -- $x { 287 1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" } 288 2 { sql3 "COMMIT" } 289 3 { sql2 "COMMIT" } 290 } 291 return 0 292 } 293 set ::max_busyhandler - 294 295 do_test 2.4.$tn1.$tn.1 { 296 sql1 { 297 CREATE TABLE t1(a, b); 298 INSERT INTO t1 VALUES(1, 2); 299 } 300 sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) } 301 sql3 { BEGIN; SELECT * FROM t1 } 302 } {1 2} 303 304 do_test 2.4.$tn1.$tn.2 { 305 code1 { db busy busyhandler } 306 code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] } 307 } $ckpt_expected 308 do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected 309 } 310 } 311 312 313 do_multiclient_test tn { 314 315 code1 $do_wal_checkpoint 316 code2 $do_wal_checkpoint 317 code3 $do_wal_checkpoint 318 319 do_test 3.$tn.1 { 320 sql1 { 321 PRAGMA auto_vacuum = 0; 322 PRAGMA journal_mode = WAL; 323 PRAGMA synchronous = normal; 324 CREATE TABLE t1(x, y); 325 } 326 327 sql2 { PRAGMA journal_mode } 328 sql3 { PRAGMA journal_mode } 329 } {wal} 330 331 do_test 3.$tn.2 { code2 { do_wal_checkpoint db2 } } {0 2 2} 332 333 do_test 3.$tn.3 { code2 { do_wal_checkpoint db2 } } {0 2 2} 334 335 do_test 3.$tn.4 { code3 { do_wal_checkpoint db3 } } {0 2 2} 336 337 code1 {db close} 338 code2 {db2 close} 339 code3 {db3 close} 340 341 code1 {sqlite3 db test.db} 342 code2 {sqlite3 db2 test.db} 343 code3 {sqlite3 db3 test.db} 344 345 do_test 3.$tn.5 { sql3 { PRAGMA journal_mode } } {wal} 346 347 do_test 3.$tn.6 { code3 { do_wal_checkpoint db3 } } {0 0 0} 348 } 349} 350 351 352finish_test 353 354