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