wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2007 March 24 |
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 focus |
||
12 | # of these tests is exclusive access mode (i.e. the thing activated by |
||
13 | # "PRAGMA locking_mode = EXCLUSIVE"). |
||
14 | # |
||
15 | # $Id: exclusive.test,v 1.15 2009/06/26 12:30:40 danielk1977 Exp $ |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | ifcapable {!pager_pragmas} { |
||
21 | finish_test |
||
22 | return |
||
23 | } |
||
24 | |||
25 | file delete -force test2.db-journal |
||
26 | file delete -force test2.db |
||
27 | file delete -force test3.db-journal |
||
28 | file delete -force test3.db |
||
29 | file delete -force test4.db-journal |
||
30 | file delete -force test4.db |
||
31 | |||
32 | #---------------------------------------------------------------------- |
||
33 | # Test cases exclusive-1.X test the PRAGMA logic. |
||
34 | # |
||
35 | do_test exclusive-1.0 { |
||
36 | execsql { |
||
37 | pragma locking_mode; |
||
38 | pragma main.locking_mode; |
||
39 | pragma temp.locking_mode; |
||
40 | } |
||
41 | } [list normal normal exclusive] |
||
42 | do_test exclusive-1.1 { |
||
43 | execsql { |
||
44 | pragma locking_mode = exclusive; |
||
45 | } |
||
46 | } {exclusive} |
||
47 | do_test exclusive-1.2 { |
||
48 | execsql { |
||
49 | pragma locking_mode; |
||
50 | pragma main.locking_mode; |
||
51 | pragma temp.locking_mode; |
||
52 | } |
||
53 | } [list exclusive exclusive exclusive] |
||
54 | do_test exclusive-1.3 { |
||
55 | execsql { |
||
56 | pragma locking_mode = normal; |
||
57 | } |
||
58 | } {normal} |
||
59 | do_test exclusive-1.4 { |
||
60 | execsql { |
||
61 | pragma locking_mode; |
||
62 | pragma main.locking_mode; |
||
63 | pragma temp.locking_mode; |
||
64 | } |
||
65 | } [list normal normal exclusive] |
||
66 | do_test exclusive-1.5 { |
||
67 | execsql { |
||
68 | pragma locking_mode = invalid; |
||
69 | } |
||
70 | } {normal} |
||
71 | do_test exclusive-1.6 { |
||
72 | execsql { |
||
73 | pragma locking_mode; |
||
74 | pragma main.locking_mode; |
||
75 | pragma temp.locking_mode; |
||
76 | } |
||
77 | } [list normal normal exclusive] |
||
78 | ifcapable attach { |
||
79 | do_test exclusive-1.7 { |
||
80 | execsql { |
||
81 | pragma locking_mode = exclusive; |
||
82 | ATTACH 'test2.db' as aux; |
||
83 | } |
||
84 | execsql { |
||
85 | pragma main.locking_mode; |
||
86 | pragma aux.locking_mode; |
||
87 | } |
||
88 | } {exclusive exclusive} |
||
89 | do_test exclusive-1.8 { |
||
90 | execsql { |
||
91 | pragma main.locking_mode = normal; |
||
92 | } |
||
93 | execsql { |
||
94 | pragma main.locking_mode; |
||
95 | pragma temp.locking_mode; |
||
96 | pragma aux.locking_mode; |
||
97 | } |
||
98 | } [list normal exclusive exclusive] |
||
99 | do_test exclusive-1.9 { |
||
100 | execsql { |
||
101 | pragma locking_mode; |
||
102 | } |
||
103 | } {exclusive} |
||
104 | do_test exclusive-1.10 { |
||
105 | execsql { |
||
106 | ATTACH 'test3.db' as aux2; |
||
107 | } |
||
108 | execsql { |
||
109 | pragma main.locking_mode; |
||
110 | pragma aux.locking_mode; |
||
111 | pragma aux2.locking_mode; |
||
112 | } |
||
113 | } {normal exclusive exclusive} |
||
114 | do_test exclusive-1.11 { |
||
115 | execsql { |
||
116 | pragma aux.locking_mode = normal; |
||
117 | } |
||
118 | execsql { |
||
119 | pragma main.locking_mode; |
||
120 | pragma aux.locking_mode; |
||
121 | pragma aux2.locking_mode; |
||
122 | } |
||
123 | } {normal normal exclusive} |
||
124 | do_test exclusive-1.12 { |
||
125 | execsql { |
||
126 | pragma locking_mode = normal; |
||
127 | } |
||
128 | execsql { |
||
129 | pragma main.locking_mode; |
||
130 | pragma temp.locking_mode; |
||
131 | pragma aux.locking_mode; |
||
132 | pragma aux2.locking_mode; |
||
133 | } |
||
134 | } [list normal exclusive normal normal] |
||
135 | do_test exclusive-1.13 { |
||
136 | execsql { |
||
137 | ATTACH 'test4.db' as aux3; |
||
138 | } |
||
139 | execsql { |
||
140 | pragma main.locking_mode; |
||
141 | pragma temp.locking_mode; |
||
142 | pragma aux.locking_mode; |
||
143 | pragma aux2.locking_mode; |
||
144 | pragma aux3.locking_mode; |
||
145 | } |
||
146 | } [list normal exclusive normal normal normal] |
||
147 | |||
148 | do_test exclusive-1.99 { |
||
149 | execsql { |
||
150 | DETACH aux; |
||
151 | DETACH aux2; |
||
152 | DETACH aux3; |
||
153 | } |
||
154 | } {} |
||
155 | } |
||
156 | |||
157 | #---------------------------------------------------------------------- |
||
158 | # Test cases exclusive-2.X verify that connections in exclusive |
||
159 | # locking_mode do not relinquish locks. |
||
160 | # |
||
161 | do_test exclusive-2.0 { |
||
162 | execsql { |
||
163 | CREATE TABLE abc(a, b, c); |
||
164 | INSERT INTO abc VALUES(1, 2, 3); |
||
165 | PRAGMA locking_mode = exclusive; |
||
166 | } |
||
167 | } {exclusive} |
||
168 | do_test exclusive-2.1 { |
||
169 | sqlite3 db2 test.db |
||
170 | execsql { |
||
171 | INSERT INTO abc VALUES(4, 5, 6); |
||
172 | SELECT * FROM abc; |
||
173 | } db2 |
||
174 | } {1 2 3 4 5 6} |
||
175 | do_test exclusive-2.2 { |
||
176 | # This causes connection 'db' (in exclusive mode) to establish |
||
177 | # a shared-lock on the db. The other connection should now be |
||
178 | # locked out as a writer. |
||
179 | execsql { |
||
180 | SELECT * FROM abc; |
||
181 | } db |
||
182 | } {1 2 3 4 5 6} |
||
183 | do_test exclusive-2.4 { |
||
184 | execsql { |
||
185 | SELECT * FROM abc; |
||
186 | } db2 |
||
187 | } {1 2 3 4 5 6} |
||
188 | do_test exclusive-2.5 { |
||
189 | catchsql { |
||
190 | INSERT INTO abc VALUES(7, 8, 9); |
||
191 | } db2 |
||
192 | } {1 {database is locked}} |
||
193 | sqlite3_soft_heap_limit 0 |
||
194 | do_test exclusive-2.6 { |
||
195 | # Because connection 'db' only has a shared-lock, the other connection |
||
196 | # will be able to get a RESERVED, but will fail to upgrade to EXCLUSIVE. |
||
197 | execsql { |
||
198 | BEGIN; |
||
199 | INSERT INTO abc VALUES(7, 8, 9); |
||
200 | } db2 |
||
201 | catchsql { |
||
202 | COMMIT |
||
203 | } db2 |
||
204 | } {1 {database is locked}} |
||
205 | do_test exclusive-2.7 { |
||
206 | catchsql { |
||
207 | COMMIT |
||
208 | } db2 |
||
209 | } {1 {database is locked}} |
||
210 | do_test exclusive-2.8 { |
||
211 | execsql { |
||
212 | ROLLBACK; |
||
213 | } db2 |
||
214 | } {} |
||
215 | sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) |
||
216 | |||
217 | do_test exclusive-2.9 { |
||
218 | # Write the database to establish the exclusive lock with connection 'db. |
||
219 | execsql { |
||
220 | INSERT INTO abc VALUES(7, 8, 9); |
||
221 | } db |
||
222 | catchsql { |
||
223 | SELECT * FROM abc; |
||
224 | } db2 |
||
225 | } {1 {database is locked}} |
||
226 | do_test exclusive-2.10 { |
||
227 | # Changing the locking-mode does not release any locks. |
||
228 | execsql { |
||
229 | PRAGMA locking_mode = normal; |
||
230 | } db |
||
231 | catchsql { |
||
232 | SELECT * FROM abc; |
||
233 | } db2 |
||
234 | } {1 {database is locked}} |
||
235 | do_test exclusive-2.11 { |
||
236 | # After changing the locking mode, accessing the db releases locks. |
||
237 | execsql { |
||
238 | SELECT * FROM abc; |
||
239 | } db |
||
240 | execsql { |
||
241 | SELECT * FROM abc; |
||
242 | } db2 |
||
243 | } {1 2 3 4 5 6 7 8 9} |
||
244 | db2 close |
||
245 | |||
246 | #---------------------------------------------------------------------- |
||
247 | # Tests exclusive-3.X - test that a connection in exclusive mode |
||
248 | # truncates instead of deletes the journal file when committing |
||
249 | # a transaction. |
||
250 | # |
||
251 | # These tests are not run on windows because the windows backend |
||
252 | # opens the journal file for exclusive access, preventing its contents |
||
253 | # from being inspected externally. |
||
254 | # |
||
255 | if {$tcl_platform(platform) != "windows"} { |
||
256 | |||
257 | # Return a list of two booleans (either 0 or 1). The first is true |
||
258 | # if the named file exists. The second is true only if the file |
||
259 | # exists and the first 28 bytes contain at least one non-zero byte. |
||
260 | # |
||
261 | proc filestate {fname} { |
||
262 | set exists 0 |
||
263 | set content 0 |
||
264 | if {[file exists $fname]} { |
||
265 | set exists 1 |
||
266 | set hdr [hexio_read $fname 0 28] |
||
267 | set content [expr {0==[string match $hdr [string repeat 0 56]]}] |
||
268 | } |
||
269 | list $exists $content |
||
270 | } |
||
271 | |||
272 | do_test exclusive-3.0 { |
||
273 | filestate test.db-journal |
||
274 | } {0 0} |
||
275 | do_test exclusive-3.1 { |
||
276 | execsql { |
||
277 | PRAGMA locking_mode = exclusive; |
||
278 | BEGIN; |
||
279 | DELETE FROM abc; |
||
280 | } |
||
281 | filestate test.db-journal |
||
282 | } {1 1} |
||
283 | do_test exclusive-3.2 { |
||
284 | execsql { |
||
285 | COMMIT; |
||
286 | } |
||
287 | filestate test.db-journal |
||
288 | } {1 0} |
||
289 | do_test exclusive-3.3 { |
||
290 | execsql { |
||
291 | INSERT INTO abc VALUES('A', 'B', 'C'); |
||
292 | SELECT * FROM abc; |
||
293 | } |
||
294 | } {A B C} |
||
295 | do_test exclusive-3.4 { |
||
296 | execsql { |
||
297 | BEGIN; |
||
298 | UPDATE abc SET a = 1, b = 2, c = 3; |
||
299 | ROLLBACK; |
||
300 | SELECT * FROM abc; |
||
301 | } |
||
302 | } {A B C} |
||
303 | do_test exclusive-3.5 { |
||
304 | filestate test.db-journal |
||
305 | } {1 0} |
||
306 | do_test exclusive-3.6 { |
||
307 | execsql { |
||
308 | PRAGMA locking_mode = normal; |
||
309 | SELECT * FROM abc; |
||
310 | } |
||
311 | filestate test.db-journal |
||
312 | } {0 0} |
||
313 | } |
||
314 | |||
315 | #---------------------------------------------------------------------- |
||
316 | # Tests exclusive-4.X - test that rollback works correctly when |
||
317 | # in exclusive-access mode. |
||
318 | # |
||
319 | |||
320 | # The following procedure computes a "signature" for table "t3". If |
||
321 | # T3 changes in any way, the signature should change. |
||
322 | # |
||
323 | # This is used to test ROLLBACK. We gather a signature for t3, then |
||
324 | # make lots of changes to t3, then rollback and take another signature. |
||
325 | # The two signatures should be the same. |
||
326 | # |
||
327 | proc signature {} { |
||
328 | return [db eval {SELECT count(*), md5sum(x) FROM t3}] |
||
329 | } |
||
330 | |||
331 | do_test exclusive-4.0 { |
||
332 | execsql { PRAGMA locking_mode = exclusive; } |
||
333 | execsql { PRAGMA default_cache_size = 10; } |
||
334 | execsql { |
||
335 | BEGIN; |
||
336 | CREATE TABLE t3(x TEXT); |
||
337 | INSERT INTO t3 VALUES(randstr(10,400)); |
||
338 | INSERT INTO t3 VALUES(randstr(10,400)); |
||
339 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
340 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
341 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
342 | INSERT INTO t3 SELECT randstr(10,400) FROM t3; |
||
343 | COMMIT; |
||
344 | } |
||
345 | execsql {SELECT count(*) FROM t3;} |
||
346 | } {32} |
||
347 | |||
348 | set ::X [signature] |
||
349 | do_test exclusive-4.1 { |
||
350 | execsql { |
||
351 | BEGIN; |
||
352 | DELETE FROM t3 WHERE random()%10!=0; |
||
353 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
354 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
355 | SELECT count(*) FROM t3; |
||
356 | ROLLBACK; |
||
357 | } |
||
358 | signature |
||
359 | } $::X |
||
360 | |||
361 | do_test exclusive-4.2 { |
||
362 | execsql { |
||
363 | BEGIN; |
||
364 | DELETE FROM t3 WHERE random()%10!=0; |
||
365 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
366 | DELETE FROM t3 WHERE random()%10!=0; |
||
367 | INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; |
||
368 | ROLLBACK; |
||
369 | } |
||
370 | signature |
||
371 | } $::X |
||
372 | |||
373 | do_test exclusive-4.3 { |
||
374 | execsql { |
||
375 | INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; |
||
376 | } |
||
377 | } {} |
||
378 | |||
379 | do_test exclusive-4.4 { |
||
380 | catch {set ::X [signature]} |
||
381 | } {0} |
||
382 | do_test exclusive-4.5 { |
||
383 | execsql { |
||
384 | PRAGMA locking_mode = NORMAL; |
||
385 | DROP TABLE t3; |
||
386 | DROP TABLE abc; |
||
387 | } |
||
388 | } {normal} |
||
389 | |||
390 | #---------------------------------------------------------------------- |
||
391 | # Tests exclusive-5.X - test that statement journals are truncated |
||
392 | # instead of deleted when in exclusive access mode. |
||
393 | # |
||
394 | |||
395 | # Close and reopen the database so that the temp database is no |
||
396 | # longer active. |
||
397 | # |
||
398 | db close |
||
399 | sqlite3 db test.db |
||
400 | |||
401 | # if we're using proxy locks, we use 3 filedescriptors for a db |
||
402 | # that is open but NOT writing changes, normally |
||
403 | # sqlite uses 1 (proxy locking adds the conch and the local lock) |
||
404 | set using_proxy 0 |
||
405 | foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { |
||
406 | set using_proxy $value |
||
407 | } |
||
408 | set extrafds 0 |
||
409 | if {$using_proxy!=0} { |
||
410 | set extrafds 2 |
||
411 | } |
||
412 | |||
413 | do_test exclusive-5.0 { |
||
414 | execsql { |
||
415 | CREATE TABLE abc(a UNIQUE, b UNIQUE, c UNIQUE); |
||
416 | BEGIN; |
||
417 | INSERT INTO abc VALUES(1, 2, 3); |
||
418 | INSERT INTO abc SELECT a+1, b+1, c+1 FROM abc; |
||
419 | } |
||
420 | } {} |
||
421 | do_test exclusive-5.1 { |
||
422 | # Three files are open: The db, journal and statement-journal. |
||
423 | set sqlite_open_file_count |
||
424 | expr $sqlite_open_file_count-$extrafds |
||
425 | } [expr 3 - ($TEMP_STORE>=2)] |
||
426 | do_test exclusive-5.2 { |
||
427 | execsql { |
||
428 | COMMIT; |
||
429 | } |
||
430 | # One file open: the db. |
||
431 | set sqlite_open_file_count |
||
432 | expr $sqlite_open_file_count-$extrafds |
||
433 | } {1} |
||
434 | do_test exclusive-5.3 { |
||
435 | execsql { |
||
436 | PRAGMA locking_mode = exclusive; |
||
437 | BEGIN; |
||
438 | INSERT INTO abc VALUES(5, 6, 7); |
||
439 | } |
||
440 | # Two files open: the db and journal. |
||
441 | set sqlite_open_file_count |
||
442 | expr $sqlite_open_file_count-$extrafds |
||
443 | } {2} |
||
444 | do_test exclusive-5.4 { |
||
445 | execsql { |
||
446 | INSERT INTO abc SELECT a+10, b+10, c+10 FROM abc; |
||
447 | } |
||
448 | # Three files are open: The db, journal and statement-journal. |
||
449 | set sqlite_open_file_count |
||
450 | expr $sqlite_open_file_count-$extrafds |
||
451 | } [expr 3 - ($TEMP_STORE>=2)] |
||
452 | do_test exclusive-5.5 { |
||
453 | execsql { |
||
454 | COMMIT; |
||
455 | } |
||
456 | # Three files are still open: The db, journal and statement-journal. |
||
457 | set sqlite_open_file_count |
||
458 | expr $sqlite_open_file_count-$extrafds |
||
459 | } [expr 3 - ($TEMP_STORE>=2)] |
||
460 | do_test exclusive-5.6 { |
||
461 | execsql { |
||
462 | PRAGMA locking_mode = normal; |
||
463 | SELECT * FROM abc; |
||
464 | } |
||
465 | } {normal 1 2 3 2 3 4 5 6 7 11 12 13 12 13 14 15 16 17} |
||
466 | do_test exclusive-5.7 { |
||
467 | # Just the db open. |
||
468 | set sqlite_open_file_count |
||
469 | expr $sqlite_open_file_count-$extrafds |
||
470 | } {1} |
||
471 | |||
472 | #------------------------------------------------------------------------- |
||
473 | |||
474 | do_execsql_test exclusive-6.1 { |
||
475 | CREATE TABLE t4(a, b); |
||
476 | INSERT INTO t4 VALUES('Eden', 1955); |
||
477 | BEGIN; |
||
478 | INSERT INTO t4 VALUES('Macmillan', 1957); |
||
479 | INSERT INTO t4 VALUES('Douglas-Home', 1963); |
||
480 | INSERT INTO t4 VALUES('Wilson', 1964); |
||
481 | } |
||
482 | do_test exclusive-6.2 { |
||
483 | forcedelete test2.db test2.db-journal |
||
484 | file copy test.db test2.db |
||
485 | file copy test.db-journal test2.db-journal |
||
486 | sqlite3 db test2.db |
||
487 | } {} |
||
488 | |||
489 | do_execsql_test exclusive-6.3 { |
||
490 | PRAGMA locking_mode = EXCLUSIVE; |
||
491 | SELECT * FROM t4; |
||
492 | } {exclusive Eden 1955} |
||
493 | |||
494 | do_test exclusive-6.4 { |
||
495 | db close |
||
496 | forcedelete test.db test.db-journal |
||
497 | set fd [open test.db-journal w] |
||
498 | puts $fd x |
||
499 | close $fd |
||
500 | sqlite3 db test.db |
||
501 | } {} |
||
502 | |||
503 | do_execsql_test exclusive-6.5 { |
||
504 | PRAGMA locking_mode = EXCLUSIVE; |
||
505 | SELECT * FROM sqlite_master; |
||
506 | } {exclusive} |
||
507 | |||
508 | finish_test |
||
509 |