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 28do_multiclient_test tn { 29 30 31 set ::nBusyHandler 0 32 set ::busy_handler_script "" 33 proc busyhandler {n} { 34 incr ::nBusyHandler 35 eval $::busy_handler_script 36 return 0 37 } 38 39 proc reopen_all {} { 40 code1 {db close} 41 code2 {db2 close} 42 code3 {db3 close} 43 code1 {sqlite3 db test.db} 44 code2 {sqlite3 db2 test.db} 45 code3 {sqlite3 db3 test.db} 46 sql1 { PRAGMA synchronous = NORMAL } 47 code1 { db busy busyhandler } 48 } 49 50 do_test 1.$tn.1 { 51 reopen_all 52 sql1 { 53 PRAGMA page_size = 1024; 54 PRAGMA auto_vacuum = 0; 55 CREATE TABLE t1(x, y); 56 PRAGMA journal_mode = WAL; 57 INSERT INTO t1 VALUES(1, zeroblob(1200)); 58 INSERT INTO t1 VALUES(2, zeroblob(1200)); 59 INSERT INTO t1 VALUES(3, zeroblob(1200)); 60 } 61 expr [file size test.db] / 1024 62 } {2} 63 64 # Have connection 2 grab a read-lock on the current snapshot. 65 do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3} 66 67 # Attempt a checkpoint. 68 do_test 1.$tn.3 { 69 sql1 { PRAGMA wal_checkpoint } 70 list [db_page_count] [wal_page_count] 71 } {5 9} 72 73 # Write to the db again. The log cannot wrap because of the lock still 74 # held by connection 2. The busy-handler has not yet been invoked. 75 do_test 1.$tn.4 { 76 sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) } 77 list [db_page_count] [wal_page_count] $::nBusyHandler 78 } {5 12 0} 79 80 # Now do a blocking-checkpoint. Set the busy-handler up so that connection 81 # 2 releases its lock on the 6th invocation. The checkpointer should then 82 # proceed to checkpoint the entire log file. Next write should go to the 83 # start of the log file. 84 # 85 set ::busy_handler_script { if {$n==5} { sql2 COMMIT } } 86 do_test 1.$tn.5 { 87 sql1 { PRAGMA wal_checkpoint = RESTART } 88 list [db_page_count] [wal_page_count] $::nBusyHandler 89 } {6 12 6} 90 do_test 1.$tn.6 { 91 set ::nBusyHandler 0 92 sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) } 93 list [db_page_count] [wal_page_count] $::nBusyHandler 94 } {6 12 0} 95 96 do_test 1.$tn.7 { 97 reopen_all 98 list [db_page_count] [wal_page_count] $::nBusyHandler 99 } {7 0 0} 100 101 do_test 1.$tn.8 { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5} 102 do_test 1.$tn.9 { 103 sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) } 104 list [db_page_count] [wal_page_count] $::nBusyHandler 105 } {7 5 0} 106 do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6} 107 108 set ::busy_handler_script { 109 if {$n==5} { sql2 COMMIT } 110 if {$n==6} { set ::db_file_size [db_page_count] } 111 if {$n==7} { sql3 COMMIT } 112 } 113 do_test 1.$tn.11 { 114 sql1 { PRAGMA wal_checkpoint = RESTART } 115 list [db_page_count] [wal_page_count] $::nBusyHandler 116 } {10 5 8} 117 do_test 1.$tn.12 { set ::db_file_size } 10 118} 119 120 121#------------------------------------------------------------------------- 122# This block of tests explores checkpoint operations on more than one 123# database file. 124# 125proc setup_and_attach_aux {} { 126 sql1 { ATTACH 'test.db2' AS aux } 127 sql2 { ATTACH 'test.db2' AS aux } 128 sql3 { ATTACH 'test.db2' AS aux } 129 sql1 { 130 PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL; 131 PRAGMA aux.page_size=1024; PRAGMA aux.journal_mode=WAL; 132 } 133} 134 135proc file_page_counts {} { 136 list [db_page_count test.db ] \ 137 [wal_page_count test.db ] \ 138 [db_page_count test.db2] \ 139 [wal_page_count test.db2] 140} 141 142# Test that executing "PRAGMA wal_checkpoint" checkpoints all attached 143# databases, not just the main db. 144# 145do_multiclient_test tn { 146 setup_and_attach_aux 147 do_test 2.1.$tn.1 { 148 sql1 { 149 CREATE TABLE t1(a, b); 150 INSERT INTO t1 VALUES(1, 2); 151 CREATE TABLE aux.t2(a, b); 152 INSERT INTO t2 VALUES(1, 2); 153 } 154 } {} 155 do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5} 156 do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5} 157 do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5} 158} 159 160do_multiclient_test tn { 161 setup_and_attach_aux 162 do_test 2.2.$tn.1 { 163 execsql { 164 CREATE TABLE t1(a, b); 165 INSERT INTO t1 VALUES(1, 2); 166 CREATE TABLE aux.t2(a, b); 167 INSERT INTO t2 VALUES(1, 2); 168 INSERT INTO t2 VALUES(3, 4); 169 } 170 } {} 171 do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7} 172 do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} 173 do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5} 174 do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7} 175} 176 177do_multiclient_test tn { 178 setup_and_attach_aux 179 do_test 2.3.$tn.1 { 180 execsql { 181 CREATE TABLE t1(a, b); 182 INSERT INTO t1 VALUES(1, 2); 183 CREATE TABLE aux.t2(a, b); 184 INSERT INTO t2 VALUES(1, 2); 185 } 186 } {} 187 do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5} 188 do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} 189 do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {} 190 do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {} 191 do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7} 192 do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5} 193 do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7} 194} 195 196# Check that checkpoints block on the correct locks. And respond correctly 197# if they cannot obtain those locks. There are three locks that a checkpoint 198# may block on (in the following order): 199# 200# 1. The writer lock: FULL and RESTART checkpoints block until any writer 201# process releases its lock. 202# 203# 2. Readers using part of the log file. FULL and RESTART checkpoints block 204# until readers using part (but not all) of the log file have finished. 205# 206# 3. Readers using any of the log file. After copying data into the 207# database file, RESTART checkpoints block until readers using any part 208# of the log file have finished. 209# 210# This test case involves running a checkpoint while there exist other 211# processes holding all three types of locks. 212# 213foreach {tn1 checkpoint busy_on ckpt_expected expected} { 214 1 PASSIVE - {0 5 5} - 215 2 TYPO - {0 5 5} - 216 217 3 FULL - {0 7 7} 2 218 4 FULL 1 {1 5 5} 1 219 5 FULL 2 {1 7 5} 2 220 6 FULL 3 {0 7 7} 2 221 222 7 RESTART - {0 7 7} 3 223 8 RESTART 1 {1 5 5} 1 224 9 RESTART 2 {1 7 5} 2 225 10 RESTART 3 {1 7 7} 3 226 227} { 228 do_multiclient_test tn { 229 setup_and_attach_aux 230 231 proc busyhandler {x} { 232 set ::max_busyhandler $x 233 if {$::busy_on!="-" && $x==$::busy_on} { return 1 } 234 switch -- $x { 235 1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" } 236 2 { sql3 "COMMIT" } 237 3 { sql2 "COMMIT" } 238 } 239 return 0 240 } 241 set ::max_busyhandler - 242 243 do_test 2.4.$tn1.$tn.1 { 244 sql1 { 245 CREATE TABLE t1(a, b); 246 INSERT INTO t1 VALUES(1, 2); 247 } 248 sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) } 249 sql3 { BEGIN; SELECT * FROM t1 } 250 } {1 2} 251 252 do_test 2.4.$tn1.$tn.2 { 253 code1 { db busy busyhandler } 254 sql1 "PRAGMA wal_checkpoint = $checkpoint" 255 } $ckpt_expected 256 do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected 257 } 258} 259 260 261finish_test 262 263