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