1# 2011 May 09 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# 12# This file contains tests for using WAL databases in read-only mode. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17source $testdir/lock_common.tcl 18source $testdir/wal_common.tcl 19set ::testprefix walro2 20 21# And only if the build is WAL-capable. 22# 23ifcapable !wal { 24 finish_test 25 return 26} 27 28proc copy_to_test2 {bZeroShm} { 29 forcecopy test.db test.db2 30 forcecopy test.db-wal test.db2-wal 31 if {$bZeroShm} { 32 forcedelete test.db2-shm 33 set fd [open test.db2-shm w] 34 seek $fd [expr [file size test.db-shm]-1] 35 puts -nonewline $fd "\0" 36 close $fd 37 } else { 38 forcecopy test.db-shm test.db2-shm 39 } 40} 41 42# Most systems allocate the *-shm file in 32KB trunks. But on UNIX systems 43# for which the getpagesize() call returns greater than 32K, the *-shm 44# file is allocated in page-sized units (since you cannot mmap part of 45# a page). The following code sets variable $MINSHMSZ to the smallest 46# possible *-shm file (i.e. the greater of 32KB and the system page-size). 47# 48do_execsql_test 0.0 { 49 PRAGMA journal_mode = wal; 50 CREATE TABLE t1(x); 51} {wal} 52set MINSHMSZ [file size test.db-shm] 53set dfltpgsz [db one {PRAGMA page_size}] 54 55foreach bZeroShm {0 1} { 56 for {set pgsz 512} {$pgsz<=65536} {set pgsz [expr {$pgsz*2}]} { 57 set TN [expr $bZeroShm+1]-$pgsz 58 do_multiclient_test tn { 59 60 # Close all connections and delete the database. 61 # 62 code1 { db close } 63 code2 { db2 close } 64 code3 { db3 close } 65 forcedelete test.db 66 67 # Do not run tests with the connections in the same process. 68 # 69 if {$tn==2} continue 70 71 foreach c {code1 code2 code3} { 72 $c { 73 sqlite3_shutdown 74 sqlite3_config_uri 1 75 } 76 } 77 78 do_test $TN.1.1 { 79 code2 { sqlite3 db2 test.db } 80 sql2 "PRAGMA page_size=$::pgsz" 81 sql2 { 82 CREATE TABLE t1(x, y); 83 PRAGMA journal_mode = WAL; 84 INSERT INTO t1 VALUES('a', 'b'); 85 INSERT INTO t1 VALUES('c', 'd'); 86 } 87 file exists test.db-shm 88 } {1} 89 90 do_test $TN.1.2.1 { 91 copy_to_test2 $bZeroShm 92 code1 { 93 sqlite3 db file:test.db2?readonly_shm=1 94 } 95 96 sql1 { SELECT * FROM t1 } 97 } {a b c d} 98 do_test $TN.1.2.2 { 99 sql1 { SELECT * FROM t1 } 100 } {a b c d} 101 102 do_test $TN.1.3.1 { 103 code3 { sqlite3 db3 test.db2 } 104 sql3 { SELECT * FROM t1 } 105 } {a b c d} 106 107 do_test $TN.1.3.2 { 108 sql1 { SELECT * FROM t1 } 109 } {a b c d} 110 111 code1 { db close } 112 code2 { db2 close } 113 code3 { db3 close } 114 115 do_test $TN.2.1 { 116 code2 { sqlite3 db2 test.db } 117 sql2 "PRAGMA page_size=$::pgsz;" 118 sql2 { 119 INSERT INTO t1 VALUES('e', 'f'); 120 INSERT INTO t1 VALUES('g', 'h'); 121 } 122 file exists test.db-shm 123 } {1} 124 125 do_test $TN.2.2 { 126 copy_to_test2 $bZeroShm 127 code1 { 128 sqlite3 db file:test.db2?readonly_shm=1 129 } 130 sql1 { 131 BEGIN; 132 SELECT * FROM t1; 133 } 134 } {a b c d e f g h} 135 136 do_test $TN.2.3.1 { 137 code3 { sqlite3 db3 test.db2 } 138 sql3 { SELECT * FROM t1 } 139 } {a b c d e f g h} 140 do_test $TN.2.3.2 { 141 sql3 { INSERT INTO t1 VALUES('i', 'j') } 142 code3 { db3 close } 143 sql1 { COMMIT } 144 } {} 145 do_test $TN.2.3.3 { 146 sql1 { SELECT * FROM t1 } 147 } {a b c d e f g h i j} 148 149 150 #----------------------------------------------------------------------- 151 # 3.1.*: That a readonly_shm connection can read a database file if both 152 # the *-wal and *-shm files are zero bytes in size. 153 # 154 # 3.2.*: That it flushes the cache if, between transactions on a db with a 155 # zero byte *-wal file, some other connection modifies the db, then 156 # does "PRAGMA wal_checkpoint=truncate" to truncate the wal file 157 # back to zero bytes in size. 158 # 159 # 3.3.*: That, if between transactions some other process wraps the wal 160 # file, the readonly_shm client reruns recovery. 161 # 162 catch { code1 { db close } } 163 catch { code2 { db2 close } } 164 catch { code3 { db3 close } } 165 do_test $TN.3.1.0 { 166 list [file exists test.db-wal] [file exists test.db-shm] 167 } {0 0} 168 do_test $TN.3.1.1 { 169 close [open test.db-wal w] 170 close [open test.db-shm w] 171 code1 { 172 sqlite3 db file:test.db?readonly_shm=1 173 } 174 sql1 { SELECT * FROM t1 } 175 } {a b c d e f g h} 176 177 do_test $TN.3.2.0 { 178 list [file size test.db-wal] [file size test.db-shm] 179 } {0 0} 180 do_test $TN.3.2.1 { 181 code2 { sqlite3 db2 test.db } 182 sql2 { INSERT INTO t1 VALUES(1, 2) ; PRAGMA wal_checkpoint=truncate } 183 code2 { db2 close } 184 sql1 { SELECT * FROM t1 } 185 } {a b c d e f g h 1 2} 186 if {$pgsz!=$dfltpgsz} continue 187 do_test $TN.3.2.2 { 188 list [file size test.db-wal] [file size test.db-shm] 189 } [list 0 $MINSHMSZ] 190 do_test $TN.3.3.0 { 191 code2 { sqlite3 db2 test.db } 192 sql2 { 193 INSERT INTO t1 VALUES(3, 4); 194 INSERT INTO t1 VALUES(5, 6); 195 INSERT INTO t1 VALUES(7, 8); 196 INSERT INTO t1 VALUES(9, 10); 197 } 198 code2 { db2 close } 199 code1 { db close } 200 list [file size test.db-wal] [file size test.db-shm] 201 } [list [wal_file_size 4 1024] $MINSHMSZ] 202 do_test $TN.3.3.1 { 203 code1 { sqlite3 db file:test.db?readonly_shm=1 } 204 sql1 { SELECT * FROM t1 } 205 } {a b c d e f g h 1 2 3 4 5 6 7 8 9 10} 206 do_test $TN.3.3.2 { 207 code2 { sqlite3 db2 test.db } 208 sql2 { 209 PRAGMA wal_checkpoint; 210 DELETE FROM t1; 211 INSERT INTO t1 VALUES('i', 'ii'); 212 } 213 code2 { db2 close } 214 list [file size test.db-wal] [file size test.db-shm] 215 } [list [wal_file_size 4 1024] $MINSHMSZ] 216 do_test $TN.3.3.3 { 217 sql1 { SELECT * FROM t1 } 218 } {i ii} 219 220 #----------------------------------------------------------------------- 221 # 222 # 223 catch { code1 { db close } } 224 catch { code2 { db2 close } } 225 catch { code3 { db3 close } } 226 227 do_test $TN.4.0 { 228 code1 { forcedelete test.db } 229 code1 { sqlite3 db test.db } 230 sql1 { 231 PRAGMA journal_mode = wal; 232 CREATE TABLE t1(x); 233 INSERT INTO t1 VALUES('hello'); 234 INSERT INTO t1 VALUES('world'); 235 } 236 237 copy_to_test2 $bZeroShm 238 239 code1 { db close } 240 } {} 241 242 do_test $TN.4.1.1 { 243 code2 { sqlite3 db2 file:test.db2?readonly_shm=1 } 244 sql2 { SELECT * FROM t1 } 245 } {hello world} 246 247 do_test $TN.4.1.2 { 248 code3 { sqlite3 db3 test.db2 } 249 sql3 { 250 INSERT INTO t1 VALUES('!'); 251 PRAGMA wal_checkpoint = truncate; 252 } 253 code3 { db3 close } 254 } {} 255 do_test $TN.4.1.3 { 256 sql2 { SELECT * FROM t1 } 257 } {hello world !} 258 259 catch { code1 { db close } } 260 catch { code2 { db2 close } } 261 catch { code3 { db3 close } } 262 263 do_test $TN.4.2.1 { 264 code1 { sqlite3 db test.db } 265 sql1 { 266 INSERT INTO t1 VALUES('!'); 267 INSERT INTO t1 VALUES('!'); 268 269 PRAGMA cache_size = 10; 270 CREATE TABLE t2(x); 271 272 BEGIN; 273 WITH s(i) AS ( 274 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<500 275 ) 276 INSERT INTO t2 SELECT randomblob(500) FROM s; 277 SELECT count(*) FROM t2; 278 } 279 } {500} 280 set sz [file size test.db-wal] 281 do_test $TN.4.2.2.(sz=$sz) { 282 expr {$sz>400000} 283 } {1} 284 do_test $TN.4.2.4 { 285 file_control_persist_wal db 1; db close 286 287 copy_to_test2 $bZeroShm 288 code2 { sqlite3 db2 file:test.db2?readonly_shm=1 } 289 sql2 { 290 SELECT * FROM t1; 291 SELECT count(*) FROM t2; 292 } 293 } {hello world ! ! 0} 294 295 #----------------------------------------------------------------------- 296 # 297 # 298 catch { code1 { db close } } 299 catch { code2 { db2 close } } 300 catch { code3 { db3 close } } 301 302 do_test $TN.5.0 { 303 code1 { forcedelete test.db } 304 code1 { sqlite3 db test.db } 305 sql1 { 306 PRAGMA journal_mode = wal; 307 CREATE TABLE t1(x); 308 INSERT INTO t1 VALUES('hello'); 309 INSERT INTO t1 VALUES('world'); 310 INSERT INTO t1 VALUES('!'); 311 INSERT INTO t1 VALUES('world'); 312 INSERT INTO t1 VALUES('hello'); 313 } 314 315 copy_to_test2 $bZeroShm 316 317 code1 { db close } 318 } {} 319 320 do_test $TN.5.1 { 321 code2 { sqlite3 db2 file:test.db2?readonly_shm=1 } 322 sql2 { 323 SELECT * FROM t1; 324 } 325 } {hello world ! world hello} 326 327 do_test $TN.5.2 { 328 code1 { 329 proc handle_read {op args} { 330 if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} { 331 set ::res2 [sql2 { SELECT * FROM t1 }] 332 } 333 puts "$msg xRead $args" 334 return "SQLITE_OK" 335 } 336 testvfs tvfs -fullshm 1 337 338 sqlite3 db file:test.db2?vfs=tvfs 339 db eval { SELECT * FROM sqlite_master } 340 341 tvfs filter xRead 342 tvfs script handle_read 343 } 344 sql1 { 345 PRAGMA wal_checkpoint = truncate; 346 } 347 code1 { set ::res2 } 348 } {hello world ! world hello} 349 350 do_test $TN.5.3 { 351 code1 { db close } 352 code1 { tvfs delete } 353 } {} 354 355 #----------------------------------------------------------------------- 356 # 357 # 358 catch { code1 { db close } } 359 catch { code2 { db2 close } } 360 catch { code3 { db3 close } } 361 362 do_test $TN.6.1 { 363 code1 { forcedelete test.db } 364 code1 { sqlite3 db test.db } 365 sql1 { 366 PRAGMA journal_mode = wal; 367 CREATE TABLE t1(x); 368 INSERT INTO t1 VALUES('hello'); 369 INSERT INTO t1 VALUES('world'); 370 INSERT INTO t1 VALUES('!'); 371 INSERT INTO t1 VALUES('world'); 372 INSERT INTO t1 VALUES('hello'); 373 } 374 375 copy_to_test2 $bZeroShm 376 377 code1 { db close } 378 } {} 379 380 do_test $TN.6.2 { 381 code1 { 382 set ::nRem 5 383 proc handle_read {op args} { 384 if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} { 385 incr ::nRem -1 386 if {$::nRem==0} { 387 code2 { sqlite3 db2 test.db2 } 388 sql2 { PRAGMA wal_checkpoint = truncate } 389 } 390 } 391 return "SQLITE_OK" 392 } 393 testvfs tvfs -fullshm 1 394 395 tvfs filter xRead 396 tvfs script handle_read 397 398 sqlite3 db file:test.db2?readonly_shm=1&vfs=tvfs 399 db eval { SELECT * FROM t1 } 400 } 401 } {hello world ! world hello} 402 403 do_test $TN.6.3 { 404 code1 { db close } 405 code1 { tvfs delete } 406 } {} 407 } 408 } ;# for pgsz 409} ;# foreach bZeroShm 410 411finish_test 412