| blob | c000dfefa4d803891d3259f58a7dc9fe2a87e336 |
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 $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable {!pager_pragmas} {
21 finish_test
22 return
23 }
25 forcedelete test2.db-journal
26 forcedelete test2.db
27 forcedelete test3.db-journal
28 forcedelete test3.db
29 forcedelete test4.db-journal
30 forcedelete test4.db
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]
148 do_test exclusive-1.99 {
149 execsql {
150 DETACH aux;
151 DETACH aux2;
152 DETACH aux3;
153 }
154 } {}
155 }
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)
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
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"} {
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 }
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 }
315 #----------------------------------------------------------------------
316 # Tests exclusive-4.X - test that rollback works correctly when
317 # in exclusive-access mode.
318 #
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 }
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}
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
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
373 do_test exclusive-4.3 {
374 execsql {
375 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
376 }
377 } {}
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}
390 #----------------------------------------------------------------------
391 # Tests exclusive-5.X - test that statement journals are truncated
392 # instead of deleted when in exclusive access mode.
393 #
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
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 }
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}
472 #-------------------------------------------------------------------------
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 copy_file test.db test2.db
485 copy_file test.db-journal test2.db-journal
486 sqlite3 db test2.db
487 } {}
489 do_execsql_test exclusive-6.3 {
490 PRAGMA locking_mode = EXCLUSIVE;
491 SELECT * FROM t4;
492 } {exclusive Eden 1955}
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 } {}
503 do_execsql_test exclusive-6.5 {
504 PRAGMA locking_mode = EXCLUSIVE;
505 SELECT * FROM sqlite_master;
506 } {exclusive}
508 finish_test