wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 April 19 |
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 | # This file implements regression tests for SQLite library. The |
||
12 | # focus of this file is testing the operation of the library in |
||
13 | # "PRAGMA journal_mode=WAL" mode. |
||
14 | # |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | source $testdir/malloc_common.tcl |
||
19 | |||
20 | # If the library was compiled without WAL support, check that the |
||
21 | # "PRAGMA journal_mode=WAL" treats "WAL" as an unrecognized mode. |
||
22 | # |
||
23 | ifcapable !wal { |
||
24 | |||
25 | do_test walmode-0.1 { |
||
26 | execsql { PRAGMA journal_mode = wal } |
||
27 | } {delete} |
||
28 | do_test walmode-0.2 { |
||
29 | execsql { PRAGMA main.journal_mode = wal } |
||
30 | } {delete} |
||
31 | do_test walmode-0.3 { |
||
32 | execsql { PRAGMA main.journal_mode } |
||
33 | } {delete} |
||
34 | |||
35 | finish_test |
||
36 | return |
||
37 | } |
||
38 | |||
39 | do_test walmode-1.1 { |
||
40 | set sqlite_sync_count 0 |
||
41 | execsql { PRAGMA page_size = 1024 } |
||
42 | execsql { PRAGMA journal_mode = wal } |
||
43 | } {wal} |
||
44 | do_test walmode-1.2 { |
||
45 | file size test.db |
||
46 | } {1024} |
||
47 | |||
48 | set expected_sync_count 3 |
||
49 | if {$::tcl_platform(platform)!="windows"} { |
||
50 | ifcapable dirsync { |
||
51 | incr expected_sync_count |
||
52 | } |
||
53 | } |
||
54 | do_test walmode-1.3 { |
||
55 | set sqlite_sync_count |
||
56 | } $expected_sync_count |
||
57 | |||
58 | do_test walmode-1.4 { |
||
59 | file exists test.db-wal |
||
60 | } {0} |
||
61 | do_test walmode-1.5 { |
||
62 | execsql { CREATE TABLE t1(a, b) } |
||
63 | file size test.db |
||
64 | } {1024} |
||
65 | do_test walmode-1.6 { |
||
66 | file exists test.db-wal |
||
67 | } {1} |
||
68 | do_test walmode-1.7 { |
||
69 | db close |
||
70 | file exists test.db-wal |
||
71 | } {0} |
||
72 | |||
73 | # There is now a database file with the read and write versions set to 2 |
||
74 | # in the file system. This file should default to WAL mode. |
||
75 | # |
||
76 | do_test walmode-2.1 { |
||
77 | sqlite3 db test.db |
||
78 | file exists test.db-wal |
||
79 | } {0} |
||
80 | do_test walmode-2.2 { |
||
81 | execsql { SELECT * FROM sqlite_master } |
||
82 | file exists test.db-wal |
||
83 | } {1} |
||
84 | do_test walmode-2.3 { |
||
85 | db close |
||
86 | file exists test.db-wal |
||
87 | } {0} |
||
88 | |||
89 | # If the first statement executed is "PRAGMA journal_mode = wal", and |
||
90 | # the file is already configured for WAL (read and write versions set |
||
91 | # to 2), then there should be no need to write the database. The |
||
92 | # statement should cause the client to connect to the log file. |
||
93 | # |
||
94 | set sqlite_sync_count 0 |
||
95 | do_test walmode-3.1 { |
||
96 | sqlite3 db test.db |
||
97 | execsql { PRAGMA journal_mode = wal } |
||
98 | } {wal} |
||
99 | do_test walmode-3.2 { |
||
100 | list $sqlite_sync_count [file exists test.db-wal] [file size test.db-wal] |
||
101 | } {0 1 0} |
||
102 | |||
103 | # Test that changing back to journal_mode=persist works. |
||
104 | # |
||
105 | do_test walmode-4.1 { |
||
106 | execsql { INSERT INTO t1 VALUES(1, 2) } |
||
107 | execsql { PRAGMA journal_mode = persist } |
||
108 | } {persist} |
||
109 | do_test walmode-4.2 { |
||
110 | list [file exists test.db-journal] [file exists test.db-wal] |
||
111 | } {1 0} |
||
112 | do_test walmode-4.3 { |
||
113 | execsql { SELECT * FROM t1 } |
||
114 | } {1 2} |
||
115 | do_test walmode-4.4 { |
||
116 | db close |
||
117 | sqlite3 db test.db |
||
118 | execsql { SELECT * FROM t1 } |
||
119 | } {1 2} |
||
120 | do_test walmode-4.5 { |
||
121 | list [file exists test.db-journal] [file exists test.db-wal] |
||
122 | } {1 0} |
||
123 | |||
124 | # Test that nothing goes wrong if a connection is prevented from changing |
||
125 | # from WAL to rollback mode because a second connection has the database |
||
126 | # open. Or from rollback to WAL. |
||
127 | # |
||
128 | do_test walmode-4.6 { |
||
129 | sqlite3 db2 test.db |
||
130 | execsql { PRAGMA main.journal_mode } db2 |
||
131 | } {delete} |
||
132 | do_test walmode-4.7 { |
||
133 | execsql { PRAGMA main.journal_mode = wal } db |
||
134 | } {wal} |
||
135 | do_test walmode-4.8 { |
||
136 | execsql { SELECT * FROM t1 } db2 |
||
137 | } {1 2} |
||
138 | do_test walmode-4.9 { |
||
139 | catchsql { PRAGMA journal_mode = delete } db |
||
140 | } {1 {database is locked}} |
||
141 | do_test walmode-4.10 { |
||
142 | execsql { PRAGMA main.journal_mode } db |
||
143 | } {wal} |
||
144 | |||
145 | do_test walmode-4.11 { |
||
146 | db2 close |
||
147 | execsql { PRAGMA journal_mode = delete } db |
||
148 | } {delete} |
||
149 | do_test walmode-4.12 { |
||
150 | execsql { PRAGMA main.journal_mode } db |
||
151 | } {delete} |
||
152 | do_test walmode-4.13 { |
||
153 | list [file exists test.db-journal] [file exists test.db-wal] |
||
154 | } {0 0} |
||
155 | do_test walmode-4.14 { |
||
156 | sqlite3 db2 test.db |
||
157 | execsql { |
||
158 | BEGIN; |
||
159 | SELECT * FROM t1; |
||
160 | } db2 |
||
161 | } {1 2} |
||
162 | |||
163 | do_test walmode-4.16 { execsql { PRAGMA main.journal_mode } db } {delete} |
||
164 | do_test walmode-4.17 { execsql { PRAGMA main.journal_mode } db2 } {delete} |
||
165 | |||
166 | do_test walmode-4.17 { |
||
167 | catchsql { PRAGMA main.journal_mode = wal } db |
||
168 | } {1 {database is locked}} |
||
169 | do_test walmode-4.18 { |
||
170 | execsql { PRAGMA main.journal_mode } db |
||
171 | } {delete} |
||
172 | catch { db close } |
||
173 | catch { db2 close } |
||
174 | |||
175 | # Test that it is not possible to change a temporary or in-memory database |
||
176 | # to WAL mode. WAL mode is for persistent file-backed databases only. |
||
177 | # |
||
178 | # walmode-5.1.*: Try to set journal_mode=WAL on [sqlite3 db :memory:] database. |
||
179 | # walmode-5.2.*: Try to set journal_mode=WAL on [sqlite3 db ""] database. |
||
180 | # walmode-5.3.*: Try to set temp.journal_mode=WAL. |
||
181 | # |
||
182 | do_test walmode-5.1.1 { |
||
183 | sqlite3 db :memory: |
||
184 | execsql { PRAGMA main.journal_mode } |
||
185 | } {memory} |
||
186 | do_test walmode-5.1.2 { |
||
187 | execsql { PRAGMA main.journal_mode = wal } |
||
188 | } {memory} |
||
189 | do_test walmode-5.1.3 { |
||
190 | execsql { |
||
191 | BEGIN; |
||
192 | CREATE TABLE t1(a, b); |
||
193 | INSERT INTO t1 VALUES(1, 2); |
||
194 | COMMIT; |
||
195 | SELECT * FROM t1; |
||
196 | PRAGMA main.journal_mode; |
||
197 | } |
||
198 | } {1 2 memory} |
||
199 | do_test walmode-5.1.4 { |
||
200 | execsql { PRAGMA main.journal_mode = wal } |
||
201 | } {memory} |
||
202 | do_test walmode-5.1.5 { |
||
203 | execsql { |
||
204 | INSERT INTO t1 VALUES(3, 4); |
||
205 | SELECT * FROM t1; |
||
206 | PRAGMA main.journal_mode; |
||
207 | } |
||
208 | } {1 2 3 4 memory} |
||
209 | |||
210 | if {$TEMP_STORE>=2} { |
||
211 | set tempJrnlMode memory |
||
212 | } else { |
||
213 | set tempJrnlMode delete |
||
214 | } |
||
215 | do_test walmode-5.2.1 { |
||
216 | sqlite3 db "" |
||
217 | execsql { PRAGMA main.journal_mode } |
||
218 | } $tempJrnlMode |
||
219 | do_test walmode-5.2.2 { |
||
220 | execsql { PRAGMA main.journal_mode = wal } |
||
221 | } $tempJrnlMode |
||
222 | do_test walmode-5.2.3 { |
||
223 | execsql { |
||
224 | BEGIN; |
||
225 | CREATE TABLE t1(a, b); |
||
226 | INSERT INTO t1 VALUES(1, 2); |
||
227 | COMMIT; |
||
228 | SELECT * FROM t1; |
||
229 | PRAGMA main.journal_mode; |
||
230 | } |
||
231 | } [list 1 2 $tempJrnlMode] |
||
232 | do_test walmode-5.2.4 { |
||
233 | execsql { PRAGMA main.journal_mode = wal } |
||
234 | } $tempJrnlMode |
||
235 | do_test walmode-5.2.5 { |
||
236 | execsql { |
||
237 | INSERT INTO t1 VALUES(3, 4); |
||
238 | SELECT * FROM t1; |
||
239 | PRAGMA main.journal_mode; |
||
240 | } |
||
241 | } [list 1 2 3 4 $tempJrnlMode] |
||
242 | |||
243 | do_test walmode-5.3.1 { |
||
244 | sqlite3 db test.db |
||
245 | execsql { PRAGMA temp.journal_mode } |
||
246 | } $tempJrnlMode |
||
247 | do_test walmode-5.3.2 { |
||
248 | execsql { PRAGMA temp.journal_mode = wal } |
||
249 | } $tempJrnlMode |
||
250 | do_test walmode-5.3.3 { |
||
251 | execsql { |
||
252 | BEGIN; |
||
253 | CREATE TEMP TABLE t1(a, b); |
||
254 | INSERT INTO t1 VALUES(1, 2); |
||
255 | COMMIT; |
||
256 | SELECT * FROM t1; |
||
257 | PRAGMA temp.journal_mode; |
||
258 | } |
||
259 | } [list 1 2 $tempJrnlMode] |
||
260 | do_test walmode-5.3.4 { |
||
261 | execsql { PRAGMA temp.journal_mode = wal } |
||
262 | } $tempJrnlMode |
||
263 | do_test walmode-5.3.5 { |
||
264 | execsql { |
||
265 | INSERT INTO t1 VALUES(3, 4); |
||
266 | SELECT * FROM t1; |
||
267 | PRAGMA temp.journal_mode; |
||
268 | } |
||
269 | } [list 1 2 3 4 $tempJrnlMode] |
||
270 | |||
271 | |||
272 | #------------------------------------------------------------------------- |
||
273 | # Test changing to WAL mode from journal_mode=off or journal_mode=memory |
||
274 | # |
||
275 | foreach {tn mode} { |
||
276 | 1 off |
||
277 | 2 memory |
||
278 | 3 persist |
||
279 | 4 delete |
||
280 | 5 truncate |
||
281 | } { |
||
282 | do_test walmode-6.$tn { |
||
283 | faultsim_delete_and_reopen |
||
284 | execsql " |
||
285 | PRAGMA journal_mode = $mode; |
||
286 | PRAGMA journal_mode = wal; |
||
287 | " |
||
288 | } [list $mode wal] |
||
289 | } |
||
290 | db close |
||
291 | |||
292 | #------------------------------------------------------------------------- |
||
293 | # Test the effect of a "PRAGMA journal_mode" command being the first |
||
294 | # thing executed by a new connection. This means that the schema is not |
||
295 | # loaded when sqlite3_prepare_v2() is called to compile the statement. |
||
296 | # |
||
297 | do_test walmode-7.0 { |
||
298 | file delete -force test.db |
||
299 | sqlite3 db test.db |
||
300 | execsql { |
||
301 | PRAGMA journal_mode = WAL; |
||
302 | CREATE TABLE t1(a, b); |
||
303 | } |
||
304 | } {wal} |
||
305 | foreach {tn sql result} { |
||
306 | 1 "PRAGMA journal_mode" wal |
||
307 | 2 "PRAGMA main.journal_mode" wal |
||
308 | 3 "PRAGMA journal_mode = delete" delete |
||
309 | 4 "PRAGMA journal_mode" delete |
||
310 | 5 "PRAGMA main.journal_mode" delete |
||
311 | 6 "PRAGMA journal_mode = wal" wal |
||
312 | 7 "PRAGMA journal_mode" wal |
||
313 | 8 "PRAGMA main.journal_mode" wal |
||
314 | |||
315 | 9 "PRAGMA journal_mode" wal |
||
316 | 10 "PRAGMA main.journal_mode" wal |
||
317 | 11 "PRAGMA main.journal_mode = delete" delete |
||
318 | 12 "PRAGMA journal_mode" delete |
||
319 | 13 "PRAGMA main.journal_mode" delete |
||
320 | 14 "PRAGMA main.journal_mode = wal" wal |
||
321 | 15 "PRAGMA journal_mode" wal |
||
322 | 16 "PRAGMA main.journal_mode" wal |
||
323 | } { |
||
324 | do_test walmode-7.$tn { |
||
325 | db close |
||
326 | sqlite3 db test.db |
||
327 | execsql $sql |
||
328 | } $result |
||
329 | } |
||
330 | db close |
||
331 | |||
332 | #------------------------------------------------------------------------- |
||
333 | # Test the effect of a "PRAGMA journal_mode" command on an attached |
||
334 | # database. |
||
335 | # |
||
336 | faultsim_delete_and_reopen |
||
337 | do_execsql_test walmode-8.1 { |
||
338 | CREATE TABLE t1(a, b); |
||
339 | PRAGMA journal_mode = WAL; |
||
340 | ATTACH 'test.db2' AS two; |
||
341 | CREATE TABLE two.t2(a, b); |
||
342 | } {wal} |
||
343 | do_execsql_test walmode-8.2 { PRAGMA main.journal_mode } {wal} |
||
344 | do_execsql_test walmode-8.3 { PRAGMA two.journal_mode } {delete} |
||
345 | do_execsql_test walmode-8.4 { PRAGMA two.journal_mode = DELETE } {delete} |
||
346 | |||
347 | db close |
||
348 | sqlite3 db test.db |
||
349 | do_execsql_test walmode-8.5 { ATTACH 'test.db2' AS two } {} |
||
350 | do_execsql_test walmode-8.6 { PRAGMA main.journal_mode } {wal} |
||
351 | do_execsql_test walmode-8.7 { PRAGMA two.journal_mode } {delete} |
||
352 | do_execsql_test walmode-8.8 { INSERT INTO two.t2 DEFAULT VALUES } {} |
||
353 | do_execsql_test walmode-8.9 { PRAGMA two.journal_mode } {delete} |
||
354 | do_execsql_test walmode-8.10 { INSERT INTO t1 DEFAULT VALUES } {} |
||
355 | do_execsql_test walmode-8.11 { PRAGMA main.journal_mode } {wal} |
||
356 | do_execsql_test walmode-8.12 { PRAGMA journal_mode } {wal} |
||
357 | |||
358 | # Change to WAL mode on test2.db and make sure (in the tests that follow) |
||
359 | # that this mode change persists. |
||
360 | do_test walmode-8.x1 { |
||
361 | execsql { |
||
362 | PRAGMA two.journal_mode=WAL; |
||
363 | PRAGMA two.journal_mode; |
||
364 | } |
||
365 | } {wal wal} |
||
366 | |||
367 | db close |
||
368 | sqlite3 db test.db |
||
369 | do_execsql_test walmode-8.13 { PRAGMA journal_mode = WAL } {wal} |
||
370 | do_execsql_test walmode-8.14 { ATTACH 'test.db2' AS two } {} |
||
371 | do_execsql_test walmode-8.15 { PRAGMA main.journal_mode } {wal} |
||
372 | do_execsql_test walmode-8.16 { PRAGMA two.journal_mode } {wal} |
||
373 | do_execsql_test walmode-8.17 { INSERT INTO two.t2 DEFAULT VALUES } {} |
||
374 | do_execsql_test walmode-8.18 { PRAGMA two.journal_mode } {wal} |
||
375 | |||
376 | sqlite3 db2 test.db2 |
||
377 | do_test walmode-8.19 { execsql { PRAGMA main.journal_mode } db2 } {wal} |
||
378 | db2 close |
||
379 | |||
380 | do_execsql_test walmode-8.20 { PRAGMA journal_mode = DELETE } {delete} |
||
381 | do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {delete} |
||
382 | do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {delete} |
||
383 | do_execsql_test walmode-8.21 { PRAGMA journal_mode = WAL } {wal} |
||
384 | do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {wal} |
||
385 | do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {wal} |
||
386 | |||
387 | finish_test |