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 do_test 1.2.2 { 105 code1 { sqlite3 db file:test.db?readonly_shm=1 } 106 sql1 { SELECT * FROM t1 } 107 } {a b c d e f g h i j} 108 109 do_test 1.2.3 { 110 code1 { db close } 111 file attributes test.db-shm -permissions rw-r--r-- 112 hexio_write test.db-shm 0 01020304 113 file attributes test.db-shm -permissions r--r--r-- 114 code1 { sqlite3 db file:test.db?readonly_shm=1 } 115 csql1 { SELECT * FROM t1 } 116 } {1 {attempt to write a readonly database}} 117 do_test 1.2.4 { 118 code1 { sqlite3_extended_errcode db } 119 } {SQLITE_READONLY_RECOVERY} 120 121 do_test 1.2.5 { 122 file attributes test.db-shm -permissions rw-r--r-- 123 code2 { sqlite3 db2 test.db } 124 sql2 "SELECT * FROM t1" 125 } {a b c d e f g h i j} 126 file attributes test.db-shm -permissions r--r--r-- 127 do_test 1.2.6 { sql1 "SELECT * FROM t1" } {a b c d e f g h i j} 128 129 do_test 1.2.7 { 130 sql2 { 131 PRAGMA wal_checkpoint; 132 INSERT INTO t1 VALUES('k', 'l'); 133 } 134 set {} {} 135 } {} 136 do_test 1.2.8 { sql1 "SELECT * FROM t1" } {a b c d e f g h i j k l} 137 138 # Now check that if the readonly_shm option is not supplied, or if it 139 # is set to zero, it is not possible to connect to the database without 140 # read-write access to the shm. 141 do_test 1.3.1 { 142 code1 { db close } 143 code1 { sqlite3 db test.db } 144 csql1 { SELECT * FROM t1 } 145 } {1 {unable to open database file}} 146 147 # Also test that if the -shm file can be opened for read/write access, 148 # it is not if readonly_shm=1 is present in the URI. 149 do_test 1.3.2.1 { 150 code1 { db close } 151 code2 { db2 close } 152 file exists test.db-shm 153 } {0} 154 do_test 1.3.2.2 { 155 code1 { sqlite3 db file:test.db?readonly_shm=1 } 156 csql1 { SELECT * FROM sqlite_master } 157 } {1 {unable to open database file}} 158 do_test 1.3.2.3 { 159 code1 { db close } 160 close [open test.db-shm w] 161 file attributes test.db-shm -permissions r--r--r-- 162 code1 { sqlite3 db file:test.db?readonly_shm=1 } 163 csql1 { SELECT * FROM t1 } 164 } {1 {attempt to write a readonly database}} 165 do_test 1.3.2.4 { 166 code1 { sqlite3_extended_errcode db } 167 } {SQLITE_READONLY_RECOVERY} 168 169 #----------------------------------------------------------------------- 170 # Test cases 1.4.* check that checkpoints and log wraps don't prevent 171 # read-only connections from reading the database. 172 do_test 1.4.1 { 173 code1 { db close } 174 forcedelete test.db-shm 175 file exists test.db-shm 176 } {0} 177 178 # Open one read-only and one read-write connection. Write some data 179 # and then run a checkpoint using the read-write connection. Then 180 # check the read-only connection can still read. 181 do_test 1.4.2 { 182 code1 { sqlite3 db file:test.db?readonly_shm=1 } 183 code2 { sqlite3 db2 test.db } 184 csql2 { 185 INSERT INTO t1 VALUES(1, 2); 186 INSERT INTO t1 VALUES(3, 4); 187 INSERT INTO t1 VALUES(5, 6); 188 PRAGMA wal_checkpoint; 189 } 190 } {0 {0 3 3}} 191 do_test 1.4.3 { 192 csql1 { SELECT * FROM t1 } 193 } {0 {a b c d e f g h i j k l 1 2 3 4 5 6}} 194 195 # Using the read-write connection, open a transaction and write lots 196 # of data - causing a cache spill and a log wrap. Then check that the 197 # read-only connection can still read the database. 198 do_test 1.4.4.1 { 199 csql2 { 200 PRAGMA cache_size = 10; 201 BEGIN; 202 CREATE TABLE t2(x, y); 203 INSERT INTO t2 VALUES('abc', 'xyz'); 204 INSERT INTO t2 SELECT x||y, y||x FROM t2; 205 INSERT INTO t2 SELECT x||y, y||x FROM t2; 206 INSERT INTO t2 SELECT x||y, y||x FROM t2; 207 INSERT INTO t2 SELECT x||y, y||x FROM t2; 208 INSERT INTO t2 SELECT x||y, y||x FROM t2; 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 } 214 file size test.db-wal 215 } {147800} 216 do_test 1.4.4.2 { 217 csql1 { SELECT * FROM t1 } 218 } {0 {a b c d e f g h i j k l 1 2 3 4 5 6}} 219 do_test 1.4.4.3 { 220 csql2 COMMIT 221 csql1 { SELECT count(*) FROM t2 } 222 } {0 512} 223 do_test 1.4.5 { 224 code2 { db2 close } 225 code1 { db close } 226 } {} 227} 228 229forcedelete test.db 230 231#----------------------------------------------------------------------- 232# Test cases 2.* check that a read-only connection may read the 233# database file while a checkpoint operation is ongoing. 234# 235do_multiclient_test tn { 236 237 # Close all connections and delete the database. 238 # 239 code1 { db close } 240 code2 { db2 close } 241 code3 { db3 close } 242 forcedelete test.db 243 forcedelete walro 244 245 # Do not run tests with the connections in the same process. 246 # 247 if {$tn==2} continue 248 249 foreach c {code1 code2 code3} { 250 $c { 251 sqlite3_shutdown 252 sqlite3_config_uri 1 253 } 254 } 255 256 proc tv_hook {x file args} { 257 if {[file tail $file]=="test.db-wal"} { 258 do_test 2.1.2 { 259 code2 { sqlite3 db2 file:test.db?readonly_shm=1 } 260 csql2 { SELECT count(*) FROM t2 } 261 } {0 4} 262 do_test 2.1.3 { 263 code2 { db2 close } 264 } {} 265 } 266 } 267 268 do_test 2.1.1 { 269 testvfs tv -default 1 -fullshm 1 270 tv script tv_hook 271 tv filter {} 272 code1 { sqlite3 db test.db } 273 csql1 { 274 PRAGMA auto_vacuum = 0; 275 PRAGMA journal_mode = WAL; 276 BEGIN; 277 CREATE TABLE t2(x, y); 278 INSERT INTO t2 VALUES('abc', 'xyz'); 279 INSERT INTO t2 SELECT x||y, y||x FROM t2; 280 INSERT INTO t2 SELECT x||y, y||x FROM t2; 281 COMMIT; 282 } 283 } {0 wal} 284 285 tv filter xSync 286 set res [csql1 { PRAGMA wal_checkpoint }] 287 do_test 2.1.4 { set res } {0 {0 2 2}} 288 289 do_test 2.1.5 { 290 code1 { db close } 291 code1 { tv delete } 292 } {} 293} 294 295finish_test 296