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