xref: /sqlite-3.40.0/test/snapshot_up.test (revision 6ab91a7a) 
1# 2018 August 6
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#
12# Tests for calling sqlite3_snapshot_open() when there is already
13# a read transaction open on the database.
14#
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18ifcapable !snapshot {finish_test; return}
19set testprefix snapshot_up
20
21# This test does not work with the inmemory_journal permutation. The reason
22# is that each connection opened as part of this permutation executes
23# "PRAGMA journal_mode=memory", which fails if the database is in wal mode
24# and there are one or more existing connections.
25if {[permutation]=="inmemory_journal"} {
26  finish_test
27  return
28}
29
30do_execsql_test 1.0 {
31  CREATE TABLE t1(a, b, c);
32  PRAGMA journal_mode = wal;
33  INSERT INTO t1 VALUES(1, 2, 3);
34  INSERT INTO t1 VALUES(4, 5, 6);
35  INSERT INTO t1 VALUES(7, 8, 9);
36} {wal}
37
38do_test 1.1 {
39  execsql BEGIN
40  set ::snap1 [sqlite3_snapshot_get db main]
41  execsql COMMIT
42  execsql { INSERT INTO t1 VALUES(10, 11, 12); }
43  execsql BEGIN
44  set ::snap2 [sqlite3_snapshot_get db main]
45  execsql COMMIT
46  execsql { INSERT INTO t1 VALUES(13, 14, 15); }
47  execsql BEGIN
48  set ::snap3 [sqlite3_snapshot_get db main]
49  execsql COMMIT
50} {}
51
52do_execsql_test 1.2 {
53  BEGIN;
54    SELECT * FROM t1
55} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}
56
57do_test 1.3 {
58  sqlite3_snapshot_open db main $::snap1
59  execsql { SELECT * FROM t1 }
60} {1 2 3 4 5 6 7 8 9}
61
62do_test 1.4 {
63  sqlite3_snapshot_open db main $::snap2
64  execsql { SELECT * FROM t1 }
65} {1 2 3 4 5 6 7 8 9 10 11 12}
66
67do_test 1.5 {
68  sqlite3 db2 test.db
69  execsql { PRAGMA wal_checkpoint } db2
70} {0 5 4}
71
72do_execsql_test 1.6 {
73  SELECT * FROM t1
74} {1 2 3 4 5 6 7 8 9 10 11 12}
75
76do_test 1.7 {
77  list [catch { sqlite3_snapshot_open db main $::snap1 } msg] $msg
78} {1 SQLITE_ERROR_SNAPSHOT}
79
80do_execsql_test 1.8 {
81  SELECT * FROM t1
82} {1 2 3 4 5 6 7 8 9 10 11 12}
83
84do_test 1.9 {
85  execsql { COMMIT ; BEGIN }
86  list [catch { sqlite3_snapshot_open db main $::snap1 } msg] $msg
87} {1 SQLITE_ERROR_SNAPSHOT}
88
89do_test 1.10 {
90  execsql { COMMIT }
91  execsql {
92    PRAGMA wal_checkpoint;
93    DELETE FROM t1 WHERE a = 1;
94  } db2
95  execsql BEGIN
96  set ::snap4 [sqlite3_snapshot_get db main]
97  execsql COMMIT
98  execsql {
99    DELETE FROM t1 WHERE a = 4;
100  } db2
101} {}
102
103do_test 1.11 {
104  execsql {
105    BEGIN;
106      SELECT * FROM t1
107  }
108} {7 8 9 10 11 12 13 14 15}
109do_test 1.12 {
110  sqlite3_snapshot_open db main $::snap4
111  execsql { SELECT * FROM t1 }
112} {4 5 6 7 8 9 10 11 12 13 14 15}
113
114do_test 1.13 {
115  list [catch { sqlite3_snapshot_open db main $::snap3 } msg] $msg
116} {1 SQLITE_ERROR_SNAPSHOT}
117do_test 1.14 {
118  execsql { SELECT * FROM t1 }
119} {4 5 6 7 8 9 10 11 12 13 14 15}
120
121db close
122db2 close
123sqlite3 db test.db
124do_execsql_test 1.15 {
125  BEGIN;
126    SELECT * FROM t1
127} {7 8 9 10 11 12 13 14 15}
128do_test 1.16 {
129  list [catch { sqlite3_snapshot_open db main $::snap4 } msg] $msg
130} {1 SQLITE_ERROR_SNAPSHOT}
131do_execsql_test 1.17 { COMMIT }
132
133sqlite3_snapshot_free $::snap1
134sqlite3_snapshot_free $::snap2
135sqlite3_snapshot_free $::snap3
136sqlite3_snapshot_free $::snap4
137
138#-------------------------------------------------------------------------
139catch { db close }
140sqlite3 db test.db
141sqlite3 db2 test.db
142sqlite3 db3 test.db
143
144proc xBusy {args} { return 1 }
145db3 busy xBusy
146
147do_test 2.1 {
148  execsql { INSERT INTO t1 VALUES(16, 17, 18) } db2
149  execsql BEGIN
150  set ::snap1 [sqlite3_snapshot_get db main]
151  execsql COMMIT
152  execsql { INSERT INTO t1 VALUES(19, 20, 21) } db2
153  execsql BEGIN
154  set ::snap2 [sqlite3_snapshot_get db main]
155  execsql COMMIT
156  set {} {}
157} {}
158
159do_execsql_test -db db2 2.2 {
160  BEGIN;
161    INSERT INTO t1 VALUES(19, 20, 21);
162}
163
164do_test 2.3 {
165  execsql BEGIN
166  sqlite3_snapshot_open db main $::snap1
167  execsql { SELECT * FROM t1 }
168} {7 8 9 10 11 12 13 14 15 16 17 18}
169
170proc xBusy {args} {
171  set ::res [list [catch { sqlite3_snapshot_open db main $::snap2 } msg] $msg]
172  return 1
173}
174db3 busy xBusy
175do_test 2.4 {
176  execsql {PRAGMA wal_checkpoint = restart} db3
177  set ::res
178} {1 SQLITE_BUSY}
179
180sqlite3_snapshot_free $::snap1
181sqlite3_snapshot_free $::snap2
182
183finish_test
184