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