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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 source $testdir/lock_common.tcl 19 source $testdir/wal_common.tcl 20 ifcapable !wal {finish_test ; return } 21 22 set testprefix wal5 23 24 proc db_page_count {{file test.db}} { expr [file size $file] / 1024 } 25 proc 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 # 32 foreach {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 do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7} 239 } 240 241 # Check that checkpoints block on the correct locks. And respond correctly 242 # if they cannot obtain those locks. There are three locks that a checkpoint 243 # may block on (in the following order): 244 # 245 # 1. The writer lock: FULL and RESTART checkpoints block until any writer 246 # process releases its lock. 247 # 248 # 2. Readers using part of the log file. FULL and RESTART checkpoints block 249 # until readers using part (but not all) of the log file have finished. 250 # 251 # 3. Readers using any of the log file. After copying data into the 252 # database file, RESTART checkpoints block until readers using any part 253 # of the log file have finished. 254 # 255 # This test case involves running a checkpoint while there exist other 256 # processes holding all three types of locks. 257 # 258 foreach {tn1 checkpoint busy_on ckpt_expected expected} { 259 1 PASSIVE - {0 5 5} - 260 2 TYPO - {0 5 5} - 261 262 3 FULL - {0 7 7} 2 263 4 FULL 1 {1 5 5} 1 264 5 FULL 2 {1 7 5} 2 265 6 FULL 3 {0 7 7} 2 266 267 7 RESTART - {0 7 7} 3 268 8 RESTART 1 {1 5 5} 1 269 9 RESTART 2 {1 7 5} 2 270 10 RESTART 3 {1 7 7} 3 271 272 } { 273 do_multiclient_test tn { 274 setup_and_attach_aux 275 276 proc busyhandler {x} { 277 set ::max_busyhandler $x 278 if {$::busy_on!="-" && $x==$::busy_on} { return 1 } 279 switch -- $x { 280 1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" } 281 2 { sql3 "COMMIT" } 282 3 { sql2 "COMMIT" } 283 } 284 return 0 285 } 286 set ::max_busyhandler - 287 288 do_test 2.4.$tn1.$tn.1 { 289 sql1 { 290 CREATE TABLE t1(a, b); 291 INSERT INTO t1 VALUES(1, 2); 292 } 293 sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) } 294 sql3 { BEGIN; SELECT * FROM t1 } 295 } {1 2} 296 297 do_test 2.4.$tn1.$tn.2 { 298 code1 { db busy busyhandler } 299 code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] } 300 } $ckpt_expected 301 do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected 302 } 303 } 304 305 306 do_multiclient_test tn { 307 308 code1 $do_wal_checkpoint 309 code2 $do_wal_checkpoint 310 code3 $do_wal_checkpoint 311 312 do_test 3.$tn.1 { 313 sql1 { 314 PRAGMA auto_vacuum = 0; 315 PRAGMA journal_mode = WAL; 316 PRAGMA synchronous = normal; 317 CREATE TABLE t1(x, y); 318 } 319 320 sql2 { PRAGMA journal_mode } 321 sql3 { PRAGMA journal_mode } 322 } {wal} 323 324 do_test 3.$tn.2 { code2 { do_wal_checkpoint db2 } } {0 2 2} 325 326 do_test 3.$tn.3 { code2 { do_wal_checkpoint db2 } } {0 2 2} 327 328 do_test 3.$tn.4 { code3 { do_wal_checkpoint db3 } } {0 2 2} 329 330 code1 {db close} 331 code2 {db2 close} 332 code3 {db3 close} 333 334 code1 {sqlite3 db test.db} 335 code2 {sqlite3 db2 test.db} 336 code3 {sqlite3 db3 test.db} 337 338 do_test 3.$tn.5 { sql3 { PRAGMA journal_mode } } {wal} 339 340 do_test 3.$tn.6 { code3 { do_wal_checkpoint db3 } } {0 0 0} 341 } 342 } 343 344 345 finish_test 346 347
