blob: 75a8af214a587b7b0277730b613872921b30f145 [file] [log] [blame]
dan818b11a2015-12-07 14:33:07 +00001# 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#***********************************************************************
dan7d159792015-12-10 18:06:21 +000011# This file implements regression tests for SQLite library. The focus
12# of this file is the sqlite3_snapshot_xxx() APIs.
dan818b11a2015-12-07 14:33:07 +000013#
dan818b11a2015-12-07 14:33:07 +000014
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
drh68d28ea2015-12-11 03:20:39 +000017ifcapable !snapshot {finish_test; return}
dan818b11a2015-12-07 14:33:07 +000018set testprefix snapshot
19
dancc47eac2016-03-24 15:09:14 +000020# 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.
24if {[permutation]=="inmemory_journal"} {
25 finish_test
26 return
27}
28
dan25accbc2016-11-18 14:38:41 +000029foreach {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 }
dan818b11a2015-12-07 14:33:07 +000043 }
dan818b11a2015-12-07 14:33:07 +000044
dan25accbc2016-11-18 14:38:41 +000045 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 }
dan818b11a2015-12-07 14:33:07 +000057 }
dan25accbc2016-11-18 14:38:41 +000058} {
dan818b11a2015-12-07 14:33:07 +000059
dan25accbc2016-11-18 14:38:41 +000060 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.
danedace5d2016-11-18 18:43:39 +000068 # 3) the database handle is in auto-commit mode
dan25accbc2016-11-18 14:38:41 +000069 #
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);
dan3bf83cc2015-12-10 15:45:15 +000074 }
dan818b11a2015-12-07 14:33:07 +000075
dan25accbc2016-11-18 14:38:41 +000076 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}
danedace5d2016-11-18 18:43:39 +000080 do_execsql_test $tn.1.1.2 COMMIT
dan818b11a2015-12-07 14:33:07 +000081
dan25accbc2016-11-18 14:38:41 +000082 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}
danedace5d2016-11-18 18:43:39 +000091 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 } {}
dan25accbc2016-11-18 14:38:41 +0000100
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 {
dan65127cd2015-12-09 20:05:27 +0000106 BEGIN;
107 SELECT * FROM t1;
dan25accbc2016-11-18 14:38:41 +0000108 } {1 2 3 4 5 6 7 8}
dan65127cd2015-12-09 20:05:27 +0000109
dan25accbc2016-11-18 14:38:41 +0000110 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}
dan65127cd2015-12-09 20:05:27 +0000118
dan25accbc2016-11-18 14:38:41 +0000119 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}
dan65127cd2015-12-09 20:05:27 +0000126
dan25accbc2016-11-18 14:38:41 +0000127 do_test $tn.2.1.3 {
128 snapshot_free $snapshot
129 execsql COMMIT
130 } {}
dan65127cd2015-12-09 20:05:27 +0000131
dan25accbc2016-11-18 14:38:41 +0000132 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}
dan7116dc62015-12-10 20:03:08 +0000139
dan25accbc2016-11-18 14:38:41 +0000140 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}
dan7116dc62015-12-10 20:03:08 +0000147
dan25accbc2016-11-18 14:38:41 +0000148 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}
dan818b11a2015-12-07 14:33:07 +0000155
dan25accbc2016-11-18 14:38:41 +0000156 do_test $tn.2.2.3 {
157 snapshot_free $snapshot
158 execsql COMMIT
159 execsql COMMIT db2
160 db2 close
161 } {}
dan818b11a2015-12-07 14:33:07 +0000162
dan25accbc2016-11-18 14:38:41 +0000163 do_test $tn.2.3.1 {
164 execsql { DELETE FROM t1 WHERE a>6 }
danedace5d2016-11-18 18:43:39 +0000165 db trans { set snapshot [snapshot_get db main] }
dan25accbc2016-11-18 14:38:41 +0000166 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');
dan818b11a2015-12-07 14:33:07 +0000201 BEGIN;
202 SELECT * FROM t2;
dan25accbc2016-11-18 14:38:41 +0000203 } {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 } {}
dan818b11a2015-12-07 14:33:07 +0000209
dan25accbc2016-11-18 14:38:41 +0000210 do_test $tn.3.1 {
211 list [catch {snapshot_open db main $snapshot } msg] $msg
212 } {1 SQLITE_ERROR}
dan818b11a2015-12-07 14:33:07 +0000213
dan25accbc2016-11-18 14:38:41 +0000214 do_test $tn.3.2.1 {
215 execsql {
216 BEGIN;
217 SELECT * FROM t2;
218 }
219 } {a b c d e f}
danfa3d4c12018-08-06 17:12:36 +0000220
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
dan25accbc2016-11-18 14:38:41 +0000229 } {1 SQLITE_ERROR}
danfa3d4c12018-08-06 17:12:36 +0000230 do_test $tn.3.2.2b {
231 snapshot_open db main $snapshot
232 } {}
dan818b11a2015-12-07 14:33:07 +0000233
dan25accbc2016-11-18 14:38:41 +0000234 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}
danedace5d2016-11-18 18:43:39 +0000242 do_execsql_test $tn.3.2.4 COMMIT
dan818b11a2015-12-07 14:33:07 +0000243
danfa3d4c12018-08-06 17:12:36 +0000244 do_test $tn.3.3.1a {
dan25accbc2016-11-18 14:38:41 +0000245 execsql { PRAGMA journal_mode = DELETE }
246 execsql { BEGIN }
247 list [catch {snapshot_open db main $snapshot } msg] $msg
248 } {1 SQLITE_ERROR}
dan818b11a2015-12-07 14:33:07 +0000249
danfa3d4c12018-08-06 17:12:36 +0000250 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
dan25accbc2016-11-18 14:38:41 +0000255 do_test $tn.$tn.3.3.2 {
256 snapshot_free $snapshot
257 execsql COMMIT
258 } {}
dan818b11a2015-12-07 14:33:07 +0000259
dan25accbc2016-11-18 14:38:41 +0000260 #-------------------------------------------------------------------------
dan8d4b7a32018-08-31 19:00:16 +0000261 # Check that SQLITE_ERROR_SNAPSHOT is returned if the specified snapshot
dan25accbc2016-11-18 14:38:41 +0000262 # 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');
dan818b11a2015-12-07 14:33:07 +0000275 BEGIN;
276 SELECT * FROM t3;
dan25accbc2016-11-18 14:38:41 +0000277 } {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
dan8d4b7a32018-08-31 19:00:16 +0000299 } {1 SQLITE_ERROR_SNAPSHOT}
dan25accbc2016-11-18 14:38:41 +0000300 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
dan8d4b7a32018-08-31 19:00:16 +0000330 } {1 SQLITE_ERROR_SNAPSHOT}
dan25accbc2016-11-18 14:38:41 +0000331 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');
dan818b11a2015-12-07 14:33:07 +0000348 BEGIN;
dan25accbc2016-11-18 14:38:41 +0000349 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
dan818b11a2015-12-07 14:33:07 +0000368 }
dan818b11a2015-12-07 14:33:07 +0000369
dan25accbc2016-11-18 14:38:41 +0000370 tvfs filter xWrite
371 tvfs script write_callback
372 db2 eval { PRAGMA wal_checkpoint }
373 db close
drhd892ac92016-02-27 14:00:07 +0000374 db2 close
dan25accbc2016-11-18 14:38:41 +0000375 tvfs delete
376 snapshot_free $snapshot
drhd892ac92016-02-27 14:00:07 +0000377
dan25accbc2016-11-18 14:38:41 +0000378 #-------------------------------------------------------------------------
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}
drhd892ac92016-02-27 14:00:07 +0000412
dan25accbc2016-11-18 14:38:41 +0000413 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}
dan745c14e2015-12-10 19:44:34 +0000419
dan25accbc2016-11-18 14:38:41 +0000420 snapshot_free $snapshot
dan745be362016-04-12 15:14:25 +0000421
dan25accbc2016-11-18 14:38:41 +0000422 #-------------------------------------------------------------------------
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
dan745be362016-04-12 15:14:25 +0000495}
496
dan818b11a2015-12-07 14:33:07 +0000497finish_test