blob: cae52db6d36f6c5c5cb9078cde060073546913fd [file] [log] [blame]
dan4edc6bf2011-05-10 17:31:29 +00001# 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
shaneh05fd9aa2011-06-17 15:55:32 +000020# These tests are only going to work on unix.
21#
22if {$::tcl_platform(platform) != "unix"} {
23 finish_test
24 return
25}
dan4edc6bf2011-05-10 17:31:29 +000026
dan52091322011-09-24 05:55:36 +000027# And only if the build is WAL-capable.
28#
29ifcapable !wal {
30 finish_test
31 return
32}
33
dan4edc6bf2011-05-10 17:31:29 +000034do_multiclient_test tn {
dan4edc6bf2011-05-10 17:31:29 +000035
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
dan98c91902014-02-24 19:49:36 +000043
44 # Do not run tests with the connections in the same process.
45 #
46 if {$tn==2} continue
dan4edc6bf2011-05-10 17:31:29 +000047
danb6d2f9c2011-05-11 14:57:33 +000048 foreach c {code1 code2 code3} {
49 $c {
50 sqlite3_shutdown
51 sqlite3_config_uri 1
52 }
53 }
54
dan4edc6bf2011-05-10 17:31:29 +000055 file mkdir walro
56
57 do_test 1.1.1 {
58 code2 { sqlite3 db2 test.db }
59 sql2 {
dan81f07402012-08-07 14:18:18 +000060 PRAGMA auto_vacuum = 0;
dan4edc6bf2011-05-10 17:31:29 +000061 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--
danb6d2f9c2011-05-11 14:57:33 +000070 code1 { sqlite3 db file:test.db?readonly_shm=1 }
dan4edc6bf2011-05-10 17:31:29 +000071 } {}
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}
danab04eff2017-10-26 17:34:50 +0000104
dan4edc6bf2011-05-10 17:31:29 +0000105 do_test 1.2.2 {
danb6d2f9c2011-05-11 14:57:33 +0000106 code1 { sqlite3 db file:test.db?readonly_shm=1 }
danab04eff2017-10-26 17:34:50 +0000107 list [catch { sql1 { SELECT * FROM t1 } } msg] $msg
dan11caf4f2017-11-04 18:10:03 +0000108 } {0 {a b c d e f g h i j}}
dan4edc6bf2011-05-10 17:31:29 +0000109
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--
danb6d2f9c2011-05-11 14:57:33 +0000115 code1 { sqlite3 db file:test.db?readonly_shm=1 }
dan4edc6bf2011-05-10 17:31:29 +0000116 csql1 { SELECT * FROM t1 }
dan11caf4f2017-11-04 18:10:03 +0000117 } {0 {a b c d e f g h i j}}
dan4edc6bf2011-05-10 17:31:29 +0000118 do_test 1.2.4 {
119 code1 { sqlite3_extended_errcode db }
dan11caf4f2017-11-04 18:10:03 +0000120 } {SQLITE_OK}
dan4edc6bf2011-05-10 17:31:29 +0000121
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}
dan3640db52011-05-11 15:53:16 +0000138
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.
danf12ba662017-11-07 15:43:52 +0000142 #
143 # UPDATE: os_unix.c now opens the *-shm file in readonly mode
144 # automatically.
145 #
dan3640db52011-05-11 15:53:16 +0000146 do_test 1.3.1 {
147 code1 { db close }
148 code1 { sqlite3 db test.db }
149 csql1 { SELECT * FROM t1 }
danf12ba662017-11-07 15:43:52 +0000150 } {0 {a b c d e f g h i j k l}}
dan3640db52011-05-11 15:53:16 +0000151
152 # Also test that if the -shm file can be opened for read/write access,
drh3ec4a0c2011-10-11 18:18:54 +0000153 # it is not if readonly_shm=1 is present in the URI.
dan3640db52011-05-11 15:53:16 +0000154 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 }
drh3ec4a0c2011-10-11 18:18:54 +0000161 csql1 { SELECT * FROM sqlite_master }
162 } {1 {unable to open database file}}
dan3640db52011-05-11 15:53:16 +0000163 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 }
dan11caf4f2017-11-04 18:10:03 +0000169 } {0 {a b c d e f g h i j k l}}
dan3640db52011-05-11 15:53:16 +0000170 do_test 1.3.2.4 {
171 code1 { sqlite3_extended_errcode db }
dan11caf4f2017-11-04 18:10:03 +0000172 } {SQLITE_OK}
dan5373b762012-07-17 14:37:12 +0000173
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
drh7da56b42016-03-14 18:34:42 +0000220 } [expr {[nonzero_reserved_bytes]?148848:147800}]
dan5373b762012-07-17 14:37:12 +0000221 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 {
dan5373b762012-07-17 14:37:12 +0000241
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
dan98c91902014-02-24 19:49:36 +0000249
250 # Do not run tests with the connections in the same process.
251 #
252 if {$tn==2} continue
dan5373b762012-07-17 14:37:12 +0000253
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 {
dan81f07402012-08-07 14:18:18 +0000279 PRAGMA auto_vacuum = 0;
dan5373b762012-07-17 14:37:12 +0000280 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 } {}
dan4edc6bf2011-05-10 17:31:29 +0000298}
299
300finish_test