Debian Codegraph @

test/e_wal.test

raw
1 # 2011 May 06
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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix e_wal
16
17 db close
18 forcedelete test.db-shm
19 testvfs oldvfs -iversion 1
20
21
22 # EVIDENCE-OF: R-58297-14483 WAL databases can be created, read, and
23 # written even if shared memory is unavailable as long as the
24 # locking_mode is set to EXCLUSIVE before the first attempted access.
25 #
26 # EVIDENCE-OF: R-00449-33772 This feature allows WAL databases to be
27 # created, read, and written by legacy VFSes that lack the "version 2"
28 # shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on
29 # the sqlite3_io_methods object.
30 #
31 # 1.1: "create" tests.
32 # 1.2: "read" tests.
33 # 1.3: "write" tests.
34 #
35 # All three done with VFS "oldvfs", which has iVersion==1 and so does
36 # not support shared memory.
37 #
38 sqlite3 db test.db -vfs oldvfs
39 do_execsql_test 1.1.1 {
40 PRAGMA journal_mode = WAL;
41 } {delete}
42 do_execsql_test 1.1.2 {
43 PRAGMA locking_mode = EXCLUSIVE;
44 PRAGMA journal_mode = WAL;
45 } {exclusive wal}
46 do_execsql_test 1.1.3 {
47 CREATE TABLE t1(x, y);
48 INSERT INTO t1 VALUES(1, 2);
49 } {}
50 do_test 1.1.4 {
51 list [file exists test.db-shm] [file exists test.db-wal]
52 } {0 1}
53
54 do_test 1.2.1 {
55 db close
56 sqlite3 db test.db -vfs oldvfs
57 catchsql { SELECT * FROM t1 }
58 } {1 {unable to open database file}}
59 do_test 1.2.2 {
60 execsql { PRAGMA locking_mode = EXCLUSIVE }
61 execsql { SELECT * FROM t1 }
62 } {1 2}
63 do_test 1.2.3 {
64 list [file exists test.db-shm] [file exists test.db-wal]
65 } {0 1}
66
67 do_test 1.3.1 {
68 db close
69 sqlite3 db test.db -vfs oldvfs
70 catchsql { INSERT INTO t1 VALUES(3, 4) }
71 } {1 {unable to open database file}}
72 do_test 1.3.2 {
73 execsql { PRAGMA locking_mode = EXCLUSIVE }
74 execsql { INSERT INTO t1 VALUES(3, 4) }
75 execsql { SELECT * FROM t1 }
76 } {1 2 3 4}
77 do_test 1.3.3 {
78 list [file exists test.db-shm] [file exists test.db-wal]
79 } {0 1}
80
81 # EVIDENCE-OF: R-31969-57825 If EXCLUSIVE locking mode is set prior to
82 # the first WAL-mode database access, then SQLite never attempts to call
83 # any of the shared-memory methods and hence no shared-memory wal-index
84 # is ever created.
85 #
86 db close
87 sqlite3 db test.db
88 do_execsql_test 2.1.1 {
89 PRAGMA locking_mode = EXCLUSIVE;
90 SELECT * FROM t1;
91 } {exclusive 1 2 3 4}
92 do_test 2.1.2 {
93 list [file exists test.db-shm] [file exists test.db-wal]
94 } {0 1}
95
96 # EVIDENCE-OF: R-36328-16367 In that case, the database connection
97 # remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts
98 # to change the locking mode using "PRAGMA locking_mode=NORMAL;" are
99 # no-ops.
100 #
101 do_execsql_test 2.2.1 {
102 PRAGMA locking_mode = NORMAL;
103 SELECT * FROM t1;
104 } {exclusive 1 2 3 4}
105 do_test 2.2.2 {
106 sqlite3 db2 test.db
107 catchsql {SELECT * FROM t1} db2
108 } {1 {database is locked}}
109 db2 close
110
111 # EVIDENCE-OF: R-63522-46088 The only way to change out of EXCLUSIVE
112 # locking mode is to first change out of WAL journal mode.
113 #
114 do_execsql_test 2.3.1 {
115 PRAGMA journal_mode = DELETE;
116 SELECT * FROM t1;
117 } {delete 1 2 3 4}
118 do_test 2.3.2 {
119 sqlite3 db2 test.db
120 catchsql {SELECT * FROM t1} db2
121 } {1 {database is locked}}
122 do_execsql_test 2.3.3 {
123 PRAGMA locking_mode = NORMAL;
124 SELECT * FROM t1;
125 } {normal 1 2 3 4}
126 do_test 2.3.4 {
127 sqlite3 db2 test.db
128 catchsql {SELECT * FROM t1} db2
129 } {0 {1 2 3 4}}
130 db2 close
131 db close
132
133
134 # EVIDENCE-OF: R-57239-11845 If NORMAL locking mode is in effect for the
135 # first WAL-mode database access, then the shared-memory wal-index is
136 # created.
137 #
138 do_test 3.0 {
139 sqlite3 db test.db
140 execsql { PRAGMA journal_mode = WAL }
141 db close
142 } {}
143 do_test 3.1 {
144 sqlite3 db test.db
145 execsql { SELECT * FROM t1 }
146 list [file exists test.db-shm] [file exists test.db-wal]
147 } {1 1}
148
149 # EVIDENCE-OF: R-13779-07711 As long as exactly one connection is using
150 # a shared-memory wal-index, the locking mode can be changed freely
151 # between NORMAL and EXCLUSIVE.
152 #
153 do_execsql_test 3.2.1 {
154 PRAGMA locking_mode = EXCLUSIVE;
155 PRAGMA locking_mode = NORMAL;
156 PRAGMA locking_mode = EXCLUSIVE;
157 INSERT INTO t1 VALUES(5, 6);
158 } {exclusive normal exclusive}
159 do_test 3.2.2 {
160 sqlite3 db2 test.db
161 catchsql { SELECT * FROM t1 } db2
162 } {1 {database is locked}}
163
164 # EVIDENCE-OF: R-10993-11647 It is only when the shared-memory wal-index
165 # is omitted, when the locking mode is EXCLUSIVE prior to the first
166 # WAL-mode database access, that the locking mode is stuck in EXCLUSIVE.
167 #
168 do_execsql_test 3.2.3 {
169 PRAGMA locking_mode = NORMAL;
170 SELECT * FROM t1;
171 } {normal 1 2 3 4 5 6}
172 do_test 3.2.4 {
173 catchsql { SELECT * FROM t1 } db2
174 } {0 {1 2 3 4 5 6}}
175
176 do_catchsql_test 3.2.5 {
177 PRAGMA locking_mode = EXCLUSIVE;
178 INSERT INTO t1 VALUES(7, 8);
179 } {1 {database is locked}}
180
181 db2 close
182
183 # EVIDENCE-OF: R-46197-42811 This means that the underlying VFS must
184 # support the "version 2" shared-memory.
185 #
186 # EVIDENCE-OF: R-55316-21772 If the VFS does not support shared-memory
187 # methods, then the attempt to open a database that is already in WAL
188 # mode, or the attempt convert a database into WAL mode, will fail.
189 #
190 db close
191 do_test 3.4.1 {
192 sqlite3 db test.db -vfs oldvfs
193 catchsql { SELECT * FROM t1 }
194 } {1 {unable to open database file}}
195 db close
196 do_test 3.4.2 {
197 forcedelete test.db2
198 sqlite3 db test.db2 -vfs oldvfs
199 catchsql { PRAGMA journal_mode = WAL }
200 } {0 delete}
201 db close
202
203
204 # EVIDENCE-OF: R-45540-25505 To prevent older versions of SQLite (prior
205 # to version 3.7.0, 2010-07-22) from trying to recover a WAL-mode
206 # database (and making matters worse) the database file format version
207 # numbers (bytes 18 and 19 in the database header) are increased from 1
208 # to 2 in WAL mode.
209 #
210 reset_db
211 do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) }
212 do_test 4.1.2 { hexio_read test.db 18 2 } {0101}
213 do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal}
214 do_test 4.1.4 { hexio_read test.db 18 2 } {0202}
215
216
217 # EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode
218 # using a pragma such as this: PRAGMA journal_mode=DELETE;
219 #
220 do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {}
221 do_test 4.2.2 { file exists test.db-wal } {1}
222 do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete}
223 do_test 4.2.4 { file exists test.db-wal } {0}
224
225 # EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode
226 # changes the database file format version numbers back to 1 so that
227 # older versions of SQLite can once again access the database file.
228 #
229 do_test 4.3 { hexio_read test.db 18 2 } {0101}
230
231 finish_test