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 18set ::testprefix walro 19 20# These tests are only going to work on unix. 21# 22if {$::tcl_platform(platform) != "unix"} { 23 finish_test 24 return 25} 26 27# And only if the build is WAL-capable. 28# 29ifcapable !wal { 30 finish_test 31 return 32} 33 34do_multiclient_test tn { 35 36 # Close all connections and delete the database. 37 # 38 code1 { db close } 39 code2 { db2 close } 40 code3 { db3 close } 41 forcedelete test.db 42 forcedelete walro 43 44 # Do not run tests with the connections in the same process. 45 # 46 if {$tn==2} continue 47 48 foreach c {code1 code2 code3} { 49 $c { 50 sqlite3_shutdown 51 sqlite3_config_uri 1 52 } 53 } 54 55 file mkdir walro 56 57 do_test 1.1.1 { 58 code2 { sqlite3 db2 test.db } 59 sql2 { 60 PRAGMA auto_vacuum = 0; 61 PRAGMA journal_mode = WAL; 62 CREATE TABLE t1(x, y); 63 INSERT INTO t1 VALUES('a', 'b'); 64 } 65 file exists test.db-shm 66 } {1} 67 68 do_test 1.1.2 { 69 file attributes test.db-shm -permissions r--r--r-- 70 code1 { sqlite3 db file:test.db?readonly_shm=1 } 71 } {} 72 73 do_test 1.1.3 { sql1 "SELECT * FROM t1" } {a b} 74 do_test 1.1.4 { sql2 "INSERT INTO t1 VALUES('c', 'd')" } {} 75 do_test 1.1.5 { sql1 "SELECT * FROM t1" } {a b c d} 76 77 # Check that the read-only connection cannot write or checkpoint the db. 78 # 79 do_test 1.1.6 { 80 csql1 "INSERT INTO t1 VALUES('e', 'f')" 81 } {1 {attempt to write a readonly database}} 82 do_test 1.1.7 { 83 csql1 "PRAGMA wal_checkpoint" 84 } {1 {attempt to write a readonly database}} 85 86 do_test 1.1.9 { sql2 "INSERT INTO t1 VALUES('e', 'f')" } {} 87 do_test 1.1.10 { sql1 "SELECT * FROM t1" } {a b c d e f} 88 89 do_test 1.1.11 { 90 sql2 { 91 INSERT INTO t1 VALUES('g', 'h'); 92 PRAGMA wal_checkpoint; 93 } 94 set {} {} 95 } {} 96 do_test 1.1.12 { sql1 "SELECT * FROM t1" } {a b c d e f g h} 97 do_test 1.1.13 { sql2 "INSERT INTO t1 VALUES('i', 'j')" } {} 98 99 do_test 1.2.1 { 100 code2 { db2 close } 101 code1 { db close } 102 list [file exists test.db-wal] [file exists test.db-shm] 103 } {1 1} 104 105 do_test 1.2.2 { 106 code1 { sqlite3 db file:test.db?readonly_shm=1 } 107 list [catch { sql1 { SELECT * FROM t1 } } msg] $msg 108 } {0 {a b c d e f g h i j}} 109 110 do_test 1.2.3 { 111 code1 { db close } 112 file attributes test.db-shm -permissions rw-r--r-- 113 hexio_write test.db-shm 0 01020304 114 file attributes test.db-shm -permissions r--r--r-- 115 code1 { sqlite3 db file:test.db?readonly_shm=1 } 116 csql1 { SELECT * FROM t1 } 117 } {0 {a b c d e f g h i j}} 118 do_test 1.2.4 { 119 code1 { sqlite3_extended_errcode db } 120 } {SQLITE_OK} 121 122 do_test 1.2.5 { 123 file attributes test.db-shm -permissions rw-r--r-- 124 code2 { sqlite3 db2 test.db } 125 sql2 "SELECT * FROM t1" 126 } {a b c d e f g h i j} 127 file attributes test.db-shm -permissions r--r--r-- 128 do_test 1.2.6 { sql1 "SELECT * FROM t1" } {a b c d e f g h i j} 129 130 do_test 1.2.7 { 131 sql2 { 132 PRAGMA wal_checkpoint; 133 INSERT INTO t1 VALUES('k', 'l'); 134 } 135 set {} {} 136 } {} 137 do_test 1.2.8 { sql1 "SELECT * FROM t1" } {a b c d e f g h i j k l} 138 139 # Now check that if the readonly_shm option is not supplied, or if it 140 # is set to zero, it is not possible to connect to the database without 141 # read-write access to the shm. 142 # 143 # UPDATE: os_unix.c now opens the *-shm file in readonly mode 144 # automatically. 145 # 146 do_test 1.3.1 { 147 code1 { db close } 148 code1 { sqlite3 db test.db } 149 csql1 { SELECT * FROM t1 } 150 } {0 {a b c d e f g h i j k l}} 151 152 # Also test that if the -shm file can be opened for read/write access, 153 # it is not if readonly_shm=1 is present in the URI. 154 do_test 1.3.2.1 { 155 code1 { db close } 156 code2 { db2 close } 157 file exists test.db-shm 158 } {0} 159 do_test 1.3.2.2 { 160 code1 { sqlite3 db file:test.db?readonly_shm=1 } 161 csql1 { SELECT * FROM sqlite_master } 162 } {1 {unable to open database file}} 163 do_test 1.3.2.3 { 164 code1 { db close } 165 close [open test.db-shm w] 166 file attributes test.db-shm -permissions r--r--r-- 167 code1 { sqlite3 db file:test.db?readonly_shm=1 } 168 csql1 { SELECT * FROM t1 } 169 } {0 {a b c d e f g h i j k l}} 170 do_test 1.3.2.4 { 171 code1 { sqlite3_extended_errcode db } 172 } {SQLITE_OK} 173 174 #----------------------------------------------------------------------- 175 # Test cases 1.4.* check that checkpoints and log wraps don't prevent 176 # read-only connections from reading the database. 177 do_test 1.4.1 { 178 code1 { db close } 179 forcedelete test.db-shm 180 file exists test.db-shm 181 } {0} 182 183 # Open one read-only and one read-write connection. Write some data 184 # and then run a checkpoint using the read-write connection. Then 185 # check the read-only connection can still read. 186 do_test 1.4.2 { 187 code1 { sqlite3 db file:test.db?readonly_shm=1 } 188 code2 { sqlite3 db2 test.db } 189 csql2 { 190 INSERT INTO t1 VALUES(1, 2); 191 INSERT INTO t1 VALUES(3, 4); 192 INSERT INTO t1 VALUES(5, 6); 193 PRAGMA wal_checkpoint; 194 } 195 } {0 {0 3 3}} 196 do_test 1.4.3 { 197 csql1 { SELECT * FROM t1 } 198 } {0 {a b c d e f g h i j k l 1 2 3 4 5 6}} 199 200 # Using the read-write connection, open a transaction and write lots 201 # of data - causing a cache spill and a log wrap. Then check that the 202 # read-only connection can still read the database. 203 do_test 1.4.4.1 { 204 csql2 { 205 PRAGMA cache_size = 10; 206 BEGIN; 207 CREATE TABLE t2(x, y); 208 INSERT INTO t2 VALUES('abc', 'xyz'); 209 INSERT INTO t2 SELECT x||y, y||x FROM t2; 210 INSERT INTO t2 SELECT x||y, y||x FROM t2; 211 INSERT INTO t2 SELECT x||y, y||x FROM t2; 212 INSERT INTO t2 SELECT x||y, y||x FROM t2; 213 INSERT INTO t2 SELECT x||y, y||x FROM t2; 214 INSERT INTO t2 SELECT x||y, y||x FROM t2; 215 INSERT INTO t2 SELECT x||y, y||x FROM t2; 216 INSERT INTO t2 SELECT x||y, y||x FROM t2; 217 INSERT INTO t2 SELECT x||y, y||x FROM t2; 218 } 219 file size test.db-wal 220 } [expr {[nonzero_reserved_bytes]?148848:147800}] 221 do_test 1.4.4.2 { 222 csql1 { SELECT * FROM t1 } 223 } {0 {a b c d e f g h i j k l 1 2 3 4 5 6}} 224 do_test 1.4.4.3 { 225 csql2 COMMIT 226 csql1 { SELECT count(*) FROM t2 } 227 } {0 512} 228 do_test 1.4.5 { 229 code2 { db2 close } 230 code1 { db close } 231 } {} 232} 233 234forcedelete test.db 235 236#----------------------------------------------------------------------- 237# Test cases 2.* check that a read-only connection may read the 238# database file while a checkpoint operation is ongoing. 239# 240do_multiclient_test tn { 241 242 # Close all connections and delete the database. 243 # 244 code1 { db close } 245 code2 { db2 close } 246 code3 { db3 close } 247 forcedelete test.db 248 forcedelete walro 249 250 # Do not run tests with the connections in the same process. 251 # 252 if {$tn==2} continue 253 254 foreach c {code1 code2 code3} { 255 $c { 256 sqlite3_shutdown 257 sqlite3_config_uri 1 258 } 259 } 260 261 proc tv_hook {x file args} { 262 if {[file tail $file]=="test.db-wal"} { 263 do_test 2.1.2 { 264 code2 { sqlite3 db2 file:test.db?readonly_shm=1 } 265 csql2 { SELECT count(*) FROM t2 } 266 } {0 4} 267 do_test 2.1.3 { 268 code2 { db2 close } 269 } {} 270 } 271 } 272 273 do_test 2.1.1 { 274 testvfs tv -default 1 -fullshm 1 275 tv script tv_hook 276 tv filter {} 277 code1 { sqlite3 db test.db } 278 csql1 { 279 PRAGMA auto_vacuum = 0; 280 PRAGMA journal_mode = WAL; 281 BEGIN; 282 CREATE TABLE t2(x, y); 283 INSERT INTO t2 VALUES('abc', 'xyz'); 284 INSERT INTO t2 SELECT x||y, y||x FROM t2; 285 INSERT INTO t2 SELECT x||y, y||x FROM t2; 286 COMMIT; 287 } 288 } {0 wal} 289 290 tv filter xSync 291 set res [csql1 { PRAGMA wal_checkpoint }] 292 do_test 2.1.4 { set res } {0 {0 2 2}} 293 294 do_test 2.1.5 { 295 code1 { db close } 296 code1 { tv delete } 297 } {} 298} 299 300finish_test 301