Debian Codegraph @

test/snapshot.test

raw
1 # 2015 December 7
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 this file is the sqlite3_snapshot_xxx() APIs.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 ifcapable !snapshot {finish_test; return}
18 set testprefix snapshot
19
20 # This test does not work with the inmemory_journal permutation. The reason
21 # is that each connection opened as part of this permutation executes
22 # "PRAGMA journal_mode=memory", which fails if the database is in wal mode
23 # and there are one or more existing connections.
24 if {[permutation]=="inmemory_journal"} {
25 finish_test
26 return
27 }
28
29 foreach {tn tcl} {
30 1 {
31 proc snapshot_get {DB DBNAME} {
32 uplevel [list sqlite3_snapshot_get $DB $DBNAME]
33 }
34 proc snapshot_open {DB DBNAME SNAPSHOT} {
35 uplevel [list sqlite3_snapshot_open $DB $DBNAME $SNAPSHOT]
36 }
37 proc snapshot_free {SNAPSHOT} {
38 uplevel [list sqlite3_snapshot_free $SNAPSHOT]
39 }
40 proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} {
41 uplevel [list sqlite3_snapshot_cmp $SNAPSHOT1 $SNAPSHOT2]
42 }
43 }
44
45 2 {
46 proc snapshot_get {DB DBNAME} {
47 uplevel [list sqlite3_snapshot_get_blob $DB $DBNAME]
48 }
49 proc snapshot_open {DB DBNAME SNAPSHOT} {
50 uplevel [list sqlite3_snapshot_open_blob $DB $DBNAME $SNAPSHOT]
51 }
52 proc snapshot_free {SNAPSHOT} {
53 }
54 proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} {
55 uplevel [list sqlite3_snapshot_cmp_blob $SNAPSHOT1 $SNAPSHOT2]
56 }
57 }
58 } {
59
60 reset_db
61 eval $tcl
62
63 #-------------------------------------------------------------------------
64 # Check some error conditions in snapshot_get(). It is an error if:
65 #
66 # 1) snapshot_get() is called on a non-WAL database, or
67 # 2) there is an open write transaction on the database.
68 # 3) the database handle is in auto-commit mode
69 #
70 do_execsql_test $tn.1.0 {
71 CREATE TABLE t1(a, b);
72 INSERT INTO t1 VALUES(1, 2);
73 INSERT INTO t1 VALUES(3, 4);
74 }
75
76 do_test $tn.1.1.1 {
77 execsql { BEGIN; SELECT * FROM t1; }
78 list [catch { snapshot_get db main } msg] $msg
79 } {1 SQLITE_ERROR}
80 do_execsql_test $tn.1.1.2 COMMIT
81
82 do_test $tn.1.2.1 {
83 execsql {
84 PRAGMA journal_mode = WAL;
85 BEGIN;
86 INSERT INTO t1 VALUES(5, 6);
87 INSERT INTO t1 VALUES(7, 8);
88 }
89 list [catch { snapshot_get db main } msg] $msg
90 } {1 SQLITE_ERROR}
91 do_execsql_test $tn.1.2.2 COMMIT
92
93 do_test $tn.1.3.1 {
94 list [catch { snapshot_get db main } msg] $msg
95 } {1 SQLITE_ERROR}
96 do_test $tn.1.3.2 {
97 db trans { set snap [snapshot_get db main] }
98 snapshot_free $snap
99 } {}
100
101 #-------------------------------------------------------------------------
102 # Check that a simple case works. Reuse the database created by the
103 # block of tests above.
104 #
105 do_execsql_test $tn.2.1.0 {
106 BEGIN;
107 SELECT * FROM t1;
108 } {1 2 3 4 5 6 7 8}
109
110 do_test $tn.2.1.1 {
111 set snapshot [snapshot_get db main]
112 execsql {
113 COMMIT;
114 INSERT INTO t1 VALUES(9, 10);
115 SELECT * FROM t1;
116 }
117 } {1 2 3 4 5 6 7 8 9 10}
118
119 do_test $tn.2.1.2 {
120 execsql BEGIN
121 snapshot_open db main $snapshot
122 execsql {
123 SELECT * FROM t1;
124 }
125 } {1 2 3 4 5 6 7 8}
126
127 do_test $tn.2.1.3 {
128 snapshot_free $snapshot
129 execsql COMMIT
130 } {}
131
132 do_test $tn.2.2.0 {
133 sqlite3 db2 test.db
134 execsql {
135 BEGIN;
136 SELECT * FROM t1;
137 } db2
138 } {1 2 3 4 5 6 7 8 9 10}
139
140 do_test $tn.2.2.1 {
141 set snapshot [snapshot_get db2 main]
142 execsql {
143 INSERT INTO t1 VALUES(11, 12);
144 SELECT * FROM t1;
145 }
146 } {1 2 3 4 5 6 7 8 9 10 11 12}
147
148 do_test $tn.2.2.2 {
149 execsql BEGIN
150 snapshot_open db main $snapshot
151 execsql {
152 SELECT * FROM t1;
153 }
154 } {1 2 3 4 5 6 7 8 9 10}
155
156 do_test $tn.2.2.3 {
157 snapshot_free $snapshot
158 execsql COMMIT
159 execsql COMMIT db2
160 db2 close
161 } {}
162
163 do_test $tn.2.3.1 {
164 execsql { DELETE FROM t1 WHERE a>6 }
165 db trans { set snapshot [snapshot_get db main] }
166 execsql {
167 INSERT INTO t1 VALUES('a', 'b');
168 INSERT INTO t1 VALUES('c', 'd');
169 SELECT * FROM t1;
170 }
171 } {1 2 3 4 5 6 a b c d}
172 do_test $tn.2.3.2 {
173 execsql BEGIN
174 snapshot_open db main $snapshot
175 execsql { SELECT * FROM t1 }
176 } {1 2 3 4 5 6}
177
178 do_test $tn.2.3.3 {
179 catchsql {
180 INSERT INTO t1 VALUES('x','y')
181 }
182 } {1 {database is locked}}
183 do_test $tn.2.3.4 {
184 execsql COMMIT
185 snapshot_free $snapshot
186 } {}
187
188 #-------------------------------------------------------------------------
189 # Check some errors in snapshot_open(). It is an error if:
190 #
191 # 1) the db is in auto-commit mode,
192 # 2) the db has an open (read or write) transaction,
193 # 3) the db is not a wal database,
194 #
195 # Reuse the database created by earlier tests.
196 #
197 do_execsql_test $tn.3.0.0 {
198 CREATE TABLE t2(x, y);
199 INSERT INTO t2 VALUES('a', 'b');
200 INSERT INTO t2 VALUES('c', 'd');
201 BEGIN;
202 SELECT * FROM t2;
203 } {a b c d}
204 do_test $tn.3.0.1 {
205 set snapshot [snapshot_get db main]
206 execsql { COMMIT }
207 execsql { INSERT INTO t2 VALUES('e', 'f'); }
208 } {}
209
210 do_test $tn.3.1 {
211 list [catch {snapshot_open db main $snapshot } msg] $msg
212 } {1 SQLITE_ERROR}
213
214 do_test $tn.3.2.1 {
215 execsql {
216 BEGIN;
217 SELECT * FROM t2;
218 }
219 } {a b c d e f}
220
221 # Update - it is no longer an error to have a read-transaction open,
222 # provided there are no active SELECT statements.
223 do_test $tn.3.2.2a {
224 db eval "SELECT * FROM t2" {
225 set res [list [catch {snapshot_open db main $snapshot } msg] $msg]
226 break
227 }
228 set res
229 } {1 SQLITE_ERROR}
230 do_test $tn.3.2.2b {
231 snapshot_open db main $snapshot
232 } {}
233
234 do_test $tn.3.2.3 {
235 execsql {
236 COMMIT;
237 BEGIN;
238 INSERT INTO t2 VALUES('g', 'h');
239 }
240 list [catch {snapshot_open db main $snapshot } msg] $msg
241 } {1 SQLITE_ERROR}
242 do_execsql_test $tn.3.2.4 COMMIT
243
244 do_test $tn.3.3.1a {
245 execsql { PRAGMA journal_mode = DELETE }
246 execsql { BEGIN }
247 list [catch {snapshot_open db main $snapshot } msg] $msg
248 } {1 SQLITE_ERROR}
249
250 do_test $tn.3.3.1b {
251 execsql { COMMIT ; BEGIN ; SELECT * FROM t2 }
252 list [catch {snapshot_open db main $snapshot } msg] $msg
253 } {1 SQLITE_ERROR}
254
255 do_test $tn.$tn.3.3.2 {
256 snapshot_free $snapshot
257 execsql COMMIT
258 } {}
259
260 #-------------------------------------------------------------------------
261 # Check that SQLITE_ERROR_SNAPSHOT is returned if the specified snapshot
262 # no longer exists because the wal file has been checkpointed.
263 #
264 # 1. Reading a snapshot from the middle of a wal file is not possible
265 # after the wal file has been checkpointed.
266 #
267 # 2. That a snapshot from the end of a wal file can not be read once
268 # the wal file has been wrapped.
269 #
270 do_execsql_test $tn.4.1.0 {
271 PRAGMA journal_mode = wal;
272 CREATE TABLE t3(i, j);
273 INSERT INTO t3 VALUES('o', 't');
274 INSERT INTO t3 VALUES('t', 'f');
275 BEGIN;
276 SELECT * FROM t3;
277 } {wal o t t f}
278
279 do_test $tn.4.1.1 {
280 set snapshot [snapshot_get db main]
281 execsql COMMIT
282 } {}
283 do_test $tn.4.1.2 {
284 execsql {
285 INSERT INTO t3 VALUES('f', 's');
286 BEGIN;
287 }
288 snapshot_open db main $snapshot
289 execsql { SELECT * FROM t3 }
290 } {o t t f}
291
292 do_test $tn.4.1.3 {
293 execsql {
294 COMMIT;
295 PRAGMA wal_checkpoint;
296 BEGIN;
297 }
298 list [catch {snapshot_open db main $snapshot} msg] $msg
299 } {1 SQLITE_ERROR_SNAPSHOT}
300 do_test $tn.4.1.4 {
301 snapshot_free $snapshot
302 execsql COMMIT
303 } {}
304
305 do_test $tn.4.2.1 {
306 execsql {
307 INSERT INTO t3 VALUES('s', 'e');
308 INSERT INTO t3 VALUES('n', 't');
309 BEGIN;
310 SELECT * FROM t3;
311 }
312 } {o t t f f s s e n t}
313 do_test $tn.4.2.2 {
314 set snapshot [snapshot_get db main]
315 execsql {
316 COMMIT;
317 PRAGMA wal_checkpoint;
318 BEGIN;
319 }
320 snapshot_open db main $snapshot
321 execsql { SELECT * FROM t3 }
322 } {o t t f f s s e n t}
323 do_test $tn.4.2.3 {
324 execsql {
325 COMMIT;
326 INSERT INTO t3 VALUES('e', 't');
327 BEGIN;
328 }
329 list [catch {snapshot_open db main $snapshot} msg] $msg
330 } {1 SQLITE_ERROR_SNAPSHOT}
331 do_test $tn.4.2.4 {
332 snapshot_free $snapshot
333 } {}
334
335 #-------------------------------------------------------------------------
336 # Check that SQLITE_BUSY is returned if a checkpoint is running when
337 # sqlite3_snapshot_open() is called.
338 #
339 reset_db
340 db close
341 testvfs tvfs
342 sqlite3 db test.db -vfs tvfs
343
344 do_execsql_test $tn.5.1 {
345 PRAGMA journal_mode = wal;
346 CREATE TABLE x1(x, xx, xxx);
347 INSERT INTO x1 VALUES('z', 'zz', 'zzz');
348 BEGIN;
349 SELECT * FROM x1;
350 } {wal z zz zzz}
351
352 do_test $tn.5.2 {
353 set ::snapshot [snapshot_get db main]
354 sqlite3 db2 test.db -vfs tvfs
355 execsql {
356 INSERT INTO x1 VALUES('a', 'aa', 'aaa');
357 COMMIT;
358 }
359 } {}
360
361 set t53 0
362 proc write_callback {args} {
363 do_test $tn.5.3.[incr ::t53] {
364 execsql BEGIN
365 list [catch { snapshot_open db main $::snapshot } msg] $msg
366 } {1 SQLITE_BUSY}
367 catchsql COMMIT
368 }
369
370 tvfs filter xWrite
371 tvfs script write_callback
372 db2 eval { PRAGMA wal_checkpoint }
373 db close
374 db2 close
375 tvfs delete
376 snapshot_free $snapshot
377
378 #-------------------------------------------------------------------------
379 # Test that sqlite3_snapshot_get() may be called immediately after
380 # "BEGIN; PRAGMA user_version;". And that sqlite3_snapshot_open() may
381 # be called after opening the db handle and running the script
382 # "PRAGMA user_version; BEGIN".
383 reset_db
384 do_execsql_test $tn.6.1 {
385 PRAGMA journal_mode = wal;
386 CREATE TABLE x1(x, xx, xxx);
387 INSERT INTO x1 VALUES('z', 'zz', 'zzz');
388 BEGIN;
389 PRAGMA user_version;
390 } {wal 0}
391 do_test $tn.6.2 {
392 set ::snapshot [snapshot_get db main]
393 execsql {
394 INSERT INTO x1 VALUES('a', 'aa', 'aaa');
395 COMMIT;
396 }
397 } {}
398 do_test $tn.6.3 {
399 sqlite3 db2 test.db
400 db2 eval "PRAGMA user_version ; BEGIN"
401 snapshot_open db2 main $::snapshot
402 db2 eval { SELECT * FROM x1 }
403 } {z zz zzz}
404 do_test $tn.6.4 {
405 db2 close
406 sqlite3 db2 test.db
407 db2 eval "PRAGMA application_id"
408 db2 eval "BEGIN"
409 snapshot_open db2 main $::snapshot
410 db2 eval { SELECT * FROM x1 }
411 } {z zz zzz}
412
413 do_test $tn.6.5 {
414 db2 close
415 sqlite3 db2 test.db
416 db2 eval "BEGIN"
417 list [catch {snapshot_open db2 main $::snapshot} msg] $msg
418 } {1 SQLITE_ERROR}
419
420 snapshot_free $snapshot
421
422 #-------------------------------------------------------------------------
423 # The following tests investigate the sqlite3_snapshot_cmp() API.
424 #
425
426 # Compare snapshots $p1 and $p2, checking that the result is $r.
427 #
428 proc do_snapshot_cmp_test {tn p1 p2 r} {
429 uplevel [list do_test $tn.1 [list snapshot_cmp $p1 $p2] $r]
430 uplevel [list do_test $tn.2 [list snapshot_cmp $p2 $p1] [expr $r*-1]]
431 uplevel [list do_test $tn.3 [list snapshot_cmp $p1 $p1] 0]
432 uplevel [list do_test $tn.4 [list snapshot_cmp $p2 $p2] 0]
433 }
434
435 catch { db2 close }
436 reset_db
437
438 do_execsql_test $tn.7.1 {
439 PRAGMA journal_mode = wal;
440 CREATE TABLE t1(x);
441 } wal
442
443 do_test $tn.7.1.2 {
444 execsql { BEGIN ; PRAGMA application_id }
445 set p1 [snapshot_get db main]
446 execsql {
447 INSERT INTO t1 VALUES(10);
448 COMMIT;
449 }
450 execsql { BEGIN ; PRAGMA application_id }
451 set p2 [snapshot_get db main]
452 execsql COMMIT
453 } {}
454
455 do_snapshot_cmp_test $tn.7.1.3 $p1 $p2 -1
456 snapshot_free $p1
457 snapshot_free $p2
458
459 do_execsql_test $tn.7.2.1 {
460 INSERT INTO t1 VALUES(11);
461 INSERT INTO t1 VALUES(12);
462 INSERT INTO t1 VALUES(13);
463 BEGIN;
464 PRAGMA application_id;
465 } {0}
466 do_test $tn.7.2.2 {
467 set p1 [snapshot_get db main]
468 execsql {
469 COMMIT;
470 INSERT INTO t1 VALUES(14);
471 PRAGMA wal_checkpoint;
472 BEGIN;
473 PRAGMA application_id;
474 }
475 set p2 [snapshot_get db main]
476 execsql COMMIT
477 } {}
478
479 do_snapshot_cmp_test $tn.7.2.3 $p1 $p2 -1
480 snapshot_free $p2
481
482 do_test $tn.7.3.1 {
483 execsql {
484 INSERT INTO t1 VALUES(14);
485 BEGIN;
486 PRAGMA application_id;
487 }
488 set p2 [snapshot_get db main]
489 execsql COMMIT
490 } {}
491
492 do_snapshot_cmp_test $tn.7.3.2 $p1 $p2 -1
493 snapshot_free $p1
494 snapshot_free $p2
495 }
496
497 finish_test