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 the library in 13# "PRAGMA journal_mode=WAL" mode. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $testdir/lock_common.tcl 19source $testdir/wal_common.tcl 20source $testdir/malloc_common.tcl 21ifcapable !wal {finish_test ; return } 22 23set a_string_counter 1 24proc a_string {n} { 25 global a_string_counter 26 incr a_string_counter 27 string range [string repeat "${a_string_counter}." $n] 1 $n 28} 29db func a_string a_string 30 31#------------------------------------------------------------------------- 32# When a rollback or savepoint rollback occurs, the client may remove 33# elements from one of the hash tables in the wal-index. This block 34# of test cases tests that nothing appears to go wrong when this is 35# done. 36# 37do_test wal3-1.0 { 38 execsql { 39 PRAGMA page_size = 1024; 40 PRAGMA auto_vacuum = off; 41 PRAGMA synchronous = normal; 42 PRAGMA journal_mode = WAL; 43 PRAGMA wal_autocheckpoint = 0; 44 BEGIN; 45 CREATE TABLE t1(x); 46 INSERT INTO t1 VALUES( a_string(800) ); /* 1 */ 47 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */ 48 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */ 49 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */ 50 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ 51 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ 52 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 64 */ 53 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 128*/ 54 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 256 */ 55 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 512 */ 56 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 1024 */ 57 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2048 */ 58 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 2000; /* 4048 */ 59 COMMIT; 60 PRAGMA cache_size = 10; 61 } 62 wal_frame_count test.db-wal 1024 63} 4086 64 65for {set i 1} {$i < 20} {incr i} { 66 67 do_test wal3-1.$i.1 { 68 set str [a_string 800] 69 execsql { UPDATE t1 SET x = $str WHERE rowid = $i } 70 lappend L [wal_frame_count test.db-wal 1024] 71 execsql { 72 BEGIN; 73 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 100; 74 ROLLBACK; 75 PRAGMA integrity_check; 76 } 77 } {ok} 78 79 # Check that everything looks OK from the point of view of an 80 # external connection. 81 # 82 sqlite3 db2 test.db 83 do_test wal3-1.$i.2 { 84 execsql { SELECT count(*) FROM t1 } db2 85 } 4048 86 do_test wal3-1.$i.3 { 87 execsql { SELECT x FROM t1 WHERE rowid = $i } 88 } $str 89 do_test wal3-1.$i.4 { 90 execsql { PRAGMA integrity_check } db2 91 } {ok} 92 db2 close 93 94 # Check that the file-system in its current state can be recovered. 95 # 96 file copy -force test.db test2.db 97 file copy -force test.db-wal test2.db-wal 98 file delete -force test2.db-journal 99 sqlite3 db2 test2.db 100 do_test wal3-1.$i.5 { 101 execsql { SELECT count(*) FROM t1 } db2 102 } 4048 103 do_test wal3-1.$i.6 { 104 execsql { SELECT x FROM t1 WHERE rowid = $i } 105 } $str 106 do_test wal3-1.$i.7 { 107 execsql { PRAGMA integrity_check } db2 108 } {ok} 109 db2 close 110} 111 112db close 113foreach code [list { 114 proc code2 {tcl} { uplevel #0 $tcl } 115 proc code3 {tcl} { uplevel #0 $tcl } 116 set tn singleproc 117} { 118 set ::code2_chan [launch_testfixture] 119 set ::code3_chan [launch_testfixture] 120 proc code2 {tcl} { testfixture $::code2_chan $tcl } 121 proc code3 {tcl} { testfixture $::code3_chan $tcl } 122 set tn multiproc 123}] { 124 file delete -force test.db test.db-wal test.db-journal 125 sqlite3 db test.db 126 eval $code 127 128 # Open connections [db2] and [db3]. Depending on which iteration this 129 # is, the connections may be created in this interpreter, or in 130 # interpreters running in other OS processes. As such, the [db2] and [db3] 131 # commands should only be accessed within [code2] and [code3] blocks, 132 # respectively. 133 # 134 code2 { sqlite3 db2 test.db ; db2 eval { PRAGMA journal_mode = WAL } } 135 code3 { sqlite3 db3 test.db ; db3 eval { PRAGMA journal_mode = WAL } } 136 137 # Shorthand commands. Execute SQL using database connection [db], [db2] 138 # or [db3]. Return the results. 139 # 140 proc sql {sql} { db eval $sql } 141 proc sql2 {sql} { code2 [list db2 eval $sql] } 142 proc sql3 {sql} { code3 [list db3 eval $sql] } 143 144 do_test wal3-2.$tn.1 { 145 sql { 146 PRAGMA page_size = 1024; 147 PRAGMA auto_vacuum = OFF; 148 PRAGMA journal_mode = WAL; 149 } 150 sql { 151 CREATE TABLE t1(a, b); 152 INSERT INTO t1 VALUES(1, 'one'); 153 BEGIN; 154 SELECT * FROM t1; 155 } 156 } {1 one} 157 do_test wal3-2.$tn.2 { 158 sql2 { 159 CREATE TABLE t2(a, b); 160 INSERT INTO t2 VALUES(2, 'two'); 161 BEGIN; 162 SELECT * FROM t2; 163 } 164 } {2 two} 165 do_test wal3-2.$tn.3 { 166 sql3 { 167 CREATE TABLE t3(a, b); 168 INSERT INTO t3 VALUES(3, 'three'); 169 BEGIN; 170 SELECT * FROM t3; 171 } 172 } {3 three} 173 174 # Try to checkpoint the database using [db]. It should be possible to 175 # checkpoint everything except the table added by [db3] (checkpointing 176 # these frames would clobber the snapshot currently being used by [db2]). 177 # 178 # After [db2] has committed, a checkpoint can copy the entire log to the 179 # database file. Checkpointing after [db3] has committed is therefore a 180 # no-op, as the entire log has already been backfilled. 181 # 182 do_test wal3-2.$tn.4 { 183 sql { 184 COMMIT; 185 PRAGMA wal_checkpoint; 186 } 187 file size test.db 188 } [expr 3*1024] 189 do_test wal3-2.$tn.5 { 190 sql2 { 191 COMMIT; 192 PRAGMA wal_checkpoint; 193 } 194 file size test.db 195 } [expr 4*1024] 196 do_test wal3-2.$tn.6 { 197 sql3 { 198 COMMIT; 199 PRAGMA wal_checkpoint; 200 } 201 file size test.db 202 } [expr 4*1024] 203 204 catch { db close } 205 catch { code2 { db2 close } } 206 catch { code3 { db3 close } } 207 catch { close $::code2_chan } 208 catch { close $::code3_chan } 209} 210catch {db close} 211 212#------------------------------------------------------------------------- 213# Test that that for the simple test: 214# 215# CREATE TABLE x(y); 216# INSERT INTO x VALUES('z'); 217# PRAGMA wal_checkpoint; 218# 219# in WAL mode the xSync method is invoked as expected for each of 220# synchronous=off, synchronous=normal and synchronous=full. 221# 222foreach {tn syncmode synccount} { 223 1 off 224 {} 225 2 normal 226 {test.db-wal normal test.db normal} 227 3 full 228 {test.db-wal normal test.db-wal normal test.db-wal normal test.db normal} 229} { 230 231 proc sync_counter {args} { 232 foreach {method filename id flags} $args break 233 lappend ::syncs [file tail $filename] $flags 234 } 235 do_test wal3-3.$tn { 236 file delete -force test.db test.db-wal test.db-journal 237 238 testvfs T 239 T filter {} 240 T script sync_counter 241 sqlite3 db test.db -vfs T 242 243 execsql "PRAGMA synchronous = $syncmode" 244 execsql { PRAGMA journal_mode = WAL } 245 246 set ::syncs [list] 247 T filter xSync 248 execsql { 249 CREATE TABLE x(y); 250 INSERT INTO x VALUES('z'); 251 PRAGMA wal_checkpoint; 252 } 253 T filter {} 254 set ::syncs 255 } $synccount 256 257 db close 258 T delete 259} 260 261#------------------------------------------------------------------------- 262# When recovering the contents of a WAL file, a process obtains the WRITER 263# lock, then locks all other bytes before commencing recovery. If it fails 264# to lock all other bytes (because some other process is holding a read 265# lock) it should retry up to 100 times. Then return SQLITE_PROTOCOL to the 266# caller. Test this (test case wal3-4.3). 267# 268# Also test the effect of hitting an SQLITE_BUSY while attempting to obtain 269# the WRITER lock (should be the same). Test case wal3-4.4. 270# 271proc lock_callback {method filename handle lock} { 272 lappend ::locks $lock 273} 274do_test wal3-4.1 { 275 testvfs T 276 T filter xShmLock 277 T script lock_callback 278 set ::locks [list] 279 sqlite3 db test.db -vfs T 280 execsql { SELECT * FROM x } 281 lrange $::locks 0 3 282} [list {0 1 lock exclusive} {1 7 lock exclusive} \ 283 {1 7 unlock exclusive} {0 1 unlock exclusive} \ 284] 285do_test wal3-4.2 { 286 db close 287 set ::locks [list] 288 sqlite3 db test.db -vfs T 289 execsql { SELECT * FROM x } 290 lrange $::locks 0 3 291} [list {0 1 lock exclusive} {1 7 lock exclusive} \ 292 {1 7 unlock exclusive} {0 1 unlock exclusive} \ 293] 294proc lock_callback {method filename handle lock} { 295 if {$lock == "1 7 lock exclusive"} { return SQLITE_BUSY } 296 return SQLITE_OK 297} 298puts " Warning: This next test case causes SQLite to call xSleep(1) 100 times." 299puts " Normally this equates to a 100ms delay, but if SQLite is built on unix" 300puts " without HAVE_USLEEP defined, it may be 100 seconds." 301do_test wal3-4.3 { 302 db close 303 set ::locks [list] 304 sqlite3 db test.db -vfs T 305 catchsql { SELECT * FROM x } 306} {1 {locking protocol}} 307 308puts " Warning: Same again!" 309proc lock_callback {method filename handle lock} { 310 if {$lock == "0 1 lock exclusive"} { return SQLITE_BUSY } 311 return SQLITE_OK 312} 313do_test wal3-4.4 { 314 db close 315 set ::locks [list] 316 sqlite3 db test.db -vfs T 317 catchsql { SELECT * FROM x } 318} {1 {locking protocol}} 319db close 320T delete 321 322 323#------------------------------------------------------------------------- 324# Only one client may run recovery at a time. Test this mechanism. 325# 326# When client-2 tries to open a read transaction while client-1 is 327# running recovery, it fails to obtain a lock on an aReadMark[] slot 328# (because they are all locked by recovery). It then tries to obtain 329# a shared lock on the RECOVER lock to see if there really is a 330# recovery running or not. 331# 332# This block of tests checks the effect of an SQLITE_BUSY or SQLITE_IOERR 333# being returned when client-2 attempts a shared lock on the RECOVER byte. 334# 335# An SQLITE_BUSY should be converted to an SQLITE_BUSY_RECOVERY. An 336# SQLITE_IOERR should be returned to the caller. 337# 338do_test wal3-5.1 { 339 faultsim_delete_and_reopen 340 execsql { 341 PRAGMA journal_mode = WAL; 342 CREATE TABLE t1(a, b); 343 INSERT INTO t1 VALUES(1, 2); 344 INSERT INTO t1 VALUES(3, 4); 345 } 346 faultsim_save_and_close 347} {} 348 349testvfs T -default 1 350T script method_callback 351 352proc method_callback {method args} { 353 if {$method == "xShmBarrier"} { 354 incr ::barrier_count 355 if {$::barrier_count == 1} { 356 # This code is executed within the xShmBarrier() callback invoked 357 # by the client running recovery as part of writing the recovered 358 # wal-index header. If a second client attempts to access the 359 # database now, it reads a corrupt (partially written) wal-index 360 # header. But it cannot even get that far, as the first client 361 # is still holding all the locks (recovery takes an exclusive lock 362 # on *all* db locks, preventing access by any other client). 363 # 364 # If global variable ::wal3_do_lockfailure is non-zero, then set 365 # things up so that an IO error occurs within an xShmLock() callback 366 # made by the second client (aka [db2]). 367 # 368 sqlite3 db2 test.db 369 if { $::wal3_do_lockfailure } { T filter xShmLock } 370 set ::testrc [ catch { db2 eval "SELECT * FROM t1" } ::testmsg ] 371 T filter {} 372 db2 close 373 } 374 } 375 376 if {$method == "xShmLock"} { 377 foreach {file handle spec} $args break 378 if { $spec == "2 1 lock shared" } { 379 return SQLITE_IOERR 380 } 381 } 382 383 return SQLITE_OK 384} 385 386# Test a normal SQLITE_BUSY return. 387# 388T filter xShmBarrier 389set testrc "" 390set testmsg "" 391set barrier_count 0 392set wal3_do_lockfailure 0 393do_test wal3-5.2 { 394 faultsim_restore_and_reopen 395 execsql { SELECT * FROM t1 } 396} {1 2 3 4} 397do_test wal3-5.3 { 398 list $::testrc $::testmsg 399} {1 {database is locked}} 400db close 401 402# Test an SQLITE_IOERR return. 403# 404T filter xShmBarrier 405set barrier_count 0 406set wal3_do_lockfailure 1 407set testrc "" 408set testmsg "" 409do_test wal3-5.4 { 410 faultsim_restore_and_reopen 411 execsql { SELECT * FROM t1 } 412} {1 2 3 4} 413do_test wal3-5.5 { 414 list $::testrc $::testmsg 415} {1 {disk I/O error}} 416 417db close 418T delete 419 420#------------------------------------------------------------------------- 421# When opening a read-transaction on a database 422# 423 424finish_test 425 426