wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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