wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2008 December 15 |
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 | # $Id: savepoint.test,v 1.13 2009/07/18 08:30:45 danielk1977 Exp $ |
||
13 | |||
14 | set testdir [file dirname $argv0] |
||
15 | source $testdir/tester.tcl |
||
16 | source $testdir/lock_common.tcl |
||
17 | source $testdir/malloc_common.tcl |
||
18 | |||
19 | #---------------------------------------------------------------------- |
||
20 | # The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE |
||
21 | # and ROLLBACK TO comands are correctly parsed, and that the auto-commit |
||
22 | # flag is correctly set and unset as a result. |
||
23 | # |
||
24 | do_test savepoint-1.1 { |
||
25 | wal_set_journal_mode |
||
26 | execsql { |
||
27 | SAVEPOINT sp1; |
||
28 | RELEASE sp1; |
||
29 | } |
||
30 | } {} |
||
31 | do_test savepoint-1.2 { |
||
32 | execsql { |
||
33 | SAVEPOINT sp1; |
||
34 | ROLLBACK TO sp1; |
||
35 | } |
||
36 | } {} |
||
37 | do_test savepoint-1.3 { |
||
38 | execsql { SAVEPOINT sp1 } |
||
39 | db close |
||
40 | } {} |
||
41 | sqlite3 db test.db |
||
42 | do_test savepoint-1.4.1 { |
||
43 | execsql { |
||
44 | SAVEPOINT sp1; |
||
45 | SAVEPOINT sp2; |
||
46 | RELEASE sp1; |
||
47 | } |
||
48 | sqlite3_get_autocommit db |
||
49 | } {1} |
||
50 | do_test savepoint-1.4.2 { |
||
51 | execsql { |
||
52 | SAVEPOINT sp1; |
||
53 | SAVEPOINT sp2; |
||
54 | RELEASE sp2; |
||
55 | } |
||
56 | sqlite3_get_autocommit db |
||
57 | } {0} |
||
58 | do_test savepoint-1.4.3 { |
||
59 | execsql { RELEASE sp1 } |
||
60 | sqlite3_get_autocommit db |
||
61 | } {1} |
||
62 | do_test savepoint-1.4.4 { |
||
63 | execsql { |
||
64 | SAVEPOINT sp1; |
||
65 | SAVEPOINT sp2; |
||
66 | ROLLBACK TO sp1; |
||
67 | } |
||
68 | sqlite3_get_autocommit db |
||
69 | } {0} |
||
70 | do_test savepoint-1.4.5 { |
||
71 | execsql { RELEASE SAVEPOINT sp1 } |
||
72 | sqlite3_get_autocommit db |
||
73 | } {1} |
||
74 | do_test savepoint-1.4.6 { |
||
75 | execsql { |
||
76 | SAVEPOINT sp1; |
||
77 | SAVEPOINT sp2; |
||
78 | SAVEPOINT sp3; |
||
79 | ROLLBACK TO SAVEPOINT sp3; |
||
80 | ROLLBACK TRANSACTION TO sp2; |
||
81 | ROLLBACK TRANSACTION TO SAVEPOINT sp1; |
||
82 | } |
||
83 | sqlite3_get_autocommit db |
||
84 | } {0} |
||
85 | do_test savepoint-1.4.7 { |
||
86 | execsql { RELEASE SAVEPOINT SP1 } |
||
87 | sqlite3_get_autocommit db |
||
88 | } {1} |
||
89 | do_test savepoint-1.5 { |
||
90 | execsql { |
||
91 | SAVEPOINT sp1; |
||
92 | ROLLBACK TO sp1; |
||
93 | } |
||
94 | } {} |
||
95 | do_test savepoint-1.6 { |
||
96 | execsql COMMIT |
||
97 | } {} |
||
98 | wal_check_journal_mode savepoint-1.7 |
||
99 | |||
100 | #------------------------------------------------------------------------ |
||
101 | # These tests - savepoint-2.* - test rollbacks and releases of savepoints |
||
102 | # with a very simple data set. |
||
103 | # |
||
104 | |||
105 | do_test savepoint-2.1 { |
||
106 | execsql { |
||
107 | CREATE TABLE t1(a, b, c); |
||
108 | BEGIN; |
||
109 | INSERT INTO t1 VALUES(1, 2, 3); |
||
110 | SAVEPOINT one; |
||
111 | UPDATE t1 SET a = 2, b = 3, c = 4; |
||
112 | } |
||
113 | execsql { SELECT * FROM t1 } |
||
114 | } {2 3 4} |
||
115 | do_test savepoint-2.2 { |
||
116 | execsql { |
||
117 | ROLLBACK TO one; |
||
118 | } |
||
119 | execsql { SELECT * FROM t1 } |
||
120 | } {1 2 3} |
||
121 | do_test savepoint-2.3 { |
||
122 | execsql { |
||
123 | INSERT INTO t1 VALUES(4, 5, 6); |
||
124 | } |
||
125 | execsql { SELECT * FROM t1 } |
||
126 | } {1 2 3 4 5 6} |
||
127 | do_test savepoint-2.4 { |
||
128 | execsql { |
||
129 | ROLLBACK TO one; |
||
130 | } |
||
131 | execsql { SELECT * FROM t1 } |
||
132 | } {1 2 3} |
||
133 | |||
134 | |||
135 | do_test savepoint-2.5 { |
||
136 | execsql { |
||
137 | INSERT INTO t1 VALUES(7, 8, 9); |
||
138 | SAVEPOINT two; |
||
139 | INSERT INTO t1 VALUES(10, 11, 12); |
||
140 | } |
||
141 | execsql { SELECT * FROM t1 } |
||
142 | } {1 2 3 7 8 9 10 11 12} |
||
143 | do_test savepoint-2.6 { |
||
144 | execsql { |
||
145 | ROLLBACK TO two; |
||
146 | } |
||
147 | execsql { SELECT * FROM t1 } |
||
148 | } {1 2 3 7 8 9} |
||
149 | do_test savepoint-2.7 { |
||
150 | execsql { |
||
151 | INSERT INTO t1 VALUES(10, 11, 12); |
||
152 | } |
||
153 | execsql { SELECT * FROM t1 } |
||
154 | } {1 2 3 7 8 9 10 11 12} |
||
155 | do_test savepoint-2.8 { |
||
156 | execsql { |
||
157 | ROLLBACK TO one; |
||
158 | } |
||
159 | execsql { SELECT * FROM t1 } |
||
160 | } {1 2 3} |
||
161 | do_test savepoint-2.9 { |
||
162 | execsql { |
||
163 | INSERT INTO t1 VALUES('a', 'b', 'c'); |
||
164 | SAVEPOINT two; |
||
165 | INSERT INTO t1 VALUES('d', 'e', 'f'); |
||
166 | } |
||
167 | execsql { SELECT * FROM t1 } |
||
168 | } {1 2 3 a b c d e f} |
||
169 | do_test savepoint-2.10 { |
||
170 | execsql { |
||
171 | RELEASE two; |
||
172 | } |
||
173 | execsql { SELECT * FROM t1 } |
||
174 | } {1 2 3 a b c d e f} |
||
175 | do_test savepoint-2.11 { |
||
176 | execsql { |
||
177 | ROLLBACK; |
||
178 | } |
||
179 | execsql { SELECT * FROM t1 } |
||
180 | } {} |
||
181 | wal_check_journal_mode savepoint-2.12 |
||
182 | |||
183 | #------------------------------------------------------------------------ |
||
184 | # This block of tests - savepoint-3.* - test that when a transaction |
||
185 | # savepoint is rolled back, locks are not released from database files. |
||
186 | # And that when a transaction savepoint is released, they are released. |
||
187 | # |
||
188 | # These tests do not work in WAL mode. WAL mode does not take RESERVED |
||
189 | # locks on the database file. |
||
190 | # |
||
191 | if {[wal_is_wal_mode]==0} { |
||
192 | do_test savepoint-3.1 { |
||
193 | execsql { SAVEPOINT "transaction" } |
||
194 | execsql { PRAGMA lock_status } |
||
195 | } {main unlocked temp closed} |
||
196 | |||
197 | do_test savepoint-3.2 { |
||
198 | execsql { INSERT INTO t1 VALUES(1, 2, 3) } |
||
199 | execsql { PRAGMA lock_status } |
||
200 | } {main reserved temp closed} |
||
201 | |||
202 | do_test savepoint-3.3 { |
||
203 | execsql { ROLLBACK TO "transaction" } |
||
204 | execsql { PRAGMA lock_status } |
||
205 | } {main reserved temp closed} |
||
206 | |||
207 | do_test savepoint-3.4 { |
||
208 | execsql { INSERT INTO t1 VALUES(1, 2, 3) } |
||
209 | execsql { PRAGMA lock_status } |
||
210 | } {main reserved temp closed} |
||
211 | |||
212 | do_test savepoint-3.5 { |
||
213 | execsql { RELEASE "transaction" } |
||
214 | execsql { PRAGMA lock_status } |
||
215 | } {main unlocked temp closed} |
||
216 | } |
||
217 | |||
218 | #------------------------------------------------------------------------ |
||
219 | # Test that savepoints that include schema modifications are handled |
||
220 | # correctly. Test cases savepoint-4.*. |
||
221 | # |
||
222 | do_test savepoint-4.1 { |
||
223 | execsql { |
||
224 | CREATE TABLE t2(d, e, f); |
||
225 | SELECT sql FROM sqlite_master; |
||
226 | } |
||
227 | } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}} |
||
228 | do_test savepoint-4.2 { |
||
229 | execsql { |
||
230 | BEGIN; |
||
231 | CREATE TABLE t3(g,h); |
||
232 | INSERT INTO t3 VALUES('I', 'II'); |
||
233 | SAVEPOINT one; |
||
234 | DROP TABLE t3; |
||
235 | } |
||
236 | } {} |
||
237 | do_test savepoint-4.3 { |
||
238 | execsql { |
||
239 | CREATE TABLE t3(g, h, i); |
||
240 | INSERT INTO t3 VALUES('III', 'IV', 'V'); |
||
241 | } |
||
242 | execsql {SELECT * FROM t3} |
||
243 | } {III IV V} |
||
244 | do_test savepoint-4.4 { |
||
245 | execsql { ROLLBACK TO one; } |
||
246 | execsql {SELECT * FROM t3} |
||
247 | } {I II} |
||
248 | do_test savepoint-4.5 { |
||
249 | execsql { |
||
250 | ROLLBACK; |
||
251 | SELECT sql FROM sqlite_master; |
||
252 | } |
||
253 | } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}} |
||
254 | |||
255 | do_test savepoint-4.6 { |
||
256 | execsql { |
||
257 | BEGIN; |
||
258 | INSERT INTO t1 VALUES('o', 't', 't'); |
||
259 | SAVEPOINT sp1; |
||
260 | CREATE TABLE t3(a, b, c); |
||
261 | INSERT INTO t3 VALUES('z', 'y', 'x'); |
||
262 | } |
||
263 | execsql {SELECT * FROM t3} |
||
264 | } {z y x} |
||
265 | do_test savepoint-4.7 { |
||
266 | execsql { |
||
267 | ROLLBACK TO sp1; |
||
268 | CREATE TABLE t3(a); |
||
269 | INSERT INTO t3 VALUES('value'); |
||
270 | } |
||
271 | execsql {SELECT * FROM t3} |
||
272 | } {value} |
||
273 | do_test savepoint-4.8 { |
||
274 | execsql COMMIT |
||
275 | } {} |
||
276 | wal_check_journal_mode savepoint-4.9 |
||
277 | |||
278 | #------------------------------------------------------------------------ |
||
279 | # Test some logic errors to do with the savepoint feature. |
||
280 | # |
||
281 | |||
282 | ifcapable incrblob { |
||
283 | do_test savepoint-5.1.1 { |
||
284 | execsql { |
||
285 | CREATE TABLE blobs(x); |
||
286 | INSERT INTO blobs VALUES('a twentyeight character blob'); |
||
287 | } |
||
288 | set fd [db incrblob blobs x 1] |
||
289 | puts -nonewline $fd "hello" |
||
290 | catchsql {SAVEPOINT abc} |
||
291 | } {1 {cannot open savepoint - SQL statements in progress}} |
||
292 | do_test savepoint-5.1.2 { |
||
293 | close $fd |
||
294 | catchsql {SAVEPOINT abc} |
||
295 | } {0 {}} |
||
296 | |||
297 | do_test savepoint-5.2 { |
||
298 | execsql {RELEASE abc} |
||
299 | catchsql {RELEASE abc} |
||
300 | } {1 {no such savepoint: abc}} |
||
301 | |||
302 | do_test savepoint-5.3.1 { |
||
303 | execsql {SAVEPOINT abc} |
||
304 | catchsql {ROLLBACK TO def} |
||
305 | } {1 {no such savepoint: def}} |
||
306 | do_test savepoint-5.3.2 { |
||
307 | execsql {SAVEPOINT def} |
||
308 | set fd [db incrblob -readonly blobs x 1] |
||
309 | catchsql {ROLLBACK TO def} |
||
310 | } {1 {cannot rollback savepoint - SQL statements in progress}} |
||
311 | do_test savepoint-5.3.3 { |
||
312 | catchsql {RELEASE def} |
||
313 | } {0 {}} |
||
314 | do_test savepoint-5.3.4 { |
||
315 | close $fd |
||
316 | execsql {savepoint def} |
||
317 | set fd [db incrblob blobs x 1] |
||
318 | catchsql {release def} |
||
319 | } {1 {cannot release savepoint - SQL statements in progress}} |
||
320 | do_test savepoint-5.3.5 { |
||
321 | close $fd |
||
322 | execsql {release abc} |
||
323 | } {} |
||
324 | |||
325 | # Rollback mode: |
||
326 | # |
||
327 | # Open a savepoint transaction and insert a row into the database. Then, |
||
328 | # using a second database handle, open a read-only transaction on the |
||
329 | # database file. Check that the savepoint transaction cannot be committed |
||
330 | # until after the read-only transaction has been closed. |
||
331 | # |
||
332 | # WAL mode: |
||
333 | # |
||
334 | # As above, except that the savepoint transaction can be successfully |
||
335 | # committed before the read-only transaction has been closed. |
||
336 | # |
||
337 | do_test savepoint-5.4.1 { |
||
338 | execsql { |
||
339 | SAVEPOINT main; |
||
340 | INSERT INTO blobs VALUES('another blob'); |
||
341 | } |
||
342 | } {} |
||
343 | do_test savepoint-5.4.2 { |
||
344 | sqlite3 db2 test.db |
||
345 | execsql { BEGIN ; SELECT count(*) FROM blobs } db2 |
||
346 | } {1} |
||
347 | if {[wal_is_wal_mode]} { |
||
348 | do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}} |
||
349 | do_test savepoint-5.4.4 { db2 close } {} |
||
350 | } else { |
||
351 | do_test savepoint-5.4.3 { |
||
352 | catchsql { RELEASE main } |
||
353 | } {1 {database is locked}} |
||
354 | do_test savepoint-5.4.4 { |
||
355 | db2 close |
||
356 | catchsql { RELEASE main } |
||
357 | } {0 {}} |
||
358 | } |
||
359 | do_test savepoint-5.4.5 { |
||
360 | execsql { SELECT x FROM blobs WHERE rowid = 2 } |
||
361 | } {{another blob}} |
||
362 | do_test savepoint-5.4.6 { |
||
363 | execsql { SELECT count(*) FROM blobs } |
||
364 | } {2} |
||
365 | } |
||
366 | wal_check_journal_mode savepoint-5.5 |
||
367 | |||
368 | #------------------------------------------------------------------------- |
||
369 | # The following tests, savepoint-6.*, test an incr-vacuum inside of a |
||
370 | # couple of nested savepoints. |
||
371 | # |
||
372 | ifcapable {autovacuum && pragma} { |
||
373 | db close |
||
374 | file delete -force test.db |
||
375 | sqlite3 db test.db |
||
376 | |||
377 | do_test savepoint-6.1 { |
||
378 | execsql { PRAGMA auto_vacuum = incremental } |
||
379 | wal_set_journal_mode |
||
380 | execsql { |
||
381 | CREATE TABLE t1(a, b, c); |
||
382 | CREATE INDEX i1 ON t1(a, b); |
||
383 | BEGIN; |
||
384 | INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400)); |
||
385 | } |
||
386 | set r "randstr(10,400)" |
||
387 | for {set ii 0} {$ii < 10} {incr ii} { |
||
388 | execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1" |
||
389 | } |
||
390 | execsql { COMMIT } |
||
391 | } {} |
||
392 | |||
393 | integrity_check savepoint-6.2 |
||
394 | |||
395 | do_test savepoint-6.3 { |
||
396 | execsql { |
||
397 | PRAGMA cache_size = 10; |
||
398 | BEGIN; |
||
399 | UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0; |
||
400 | SAVEPOINT one; |
||
401 | DELETE FROM t1 WHERE rowid%2; |
||
402 | PRAGMA incr_vacuum; |
||
403 | SAVEPOINT two; |
||
404 | INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1; |
||
405 | DELETE FROM t1 WHERE rowid%2; |
||
406 | PRAGMA incr_vacuum; |
||
407 | ROLLBACK TO one; |
||
408 | COMMIT; |
||
409 | } |
||
410 | } {} |
||
411 | |||
412 | integrity_check savepoint-6.4 |
||
413 | |||
414 | wal_check_journal_mode savepoint-6.5 |
||
415 | } |
||
416 | |||
417 | #------------------------------------------------------------------------- |
||
418 | # The following tests, savepoint-7.*, attempt to break the logic |
||
419 | # surrounding savepoints by growing and shrinking the database file. |
||
420 | # |
||
421 | db close |
||
422 | file delete -force test.db |
||
423 | sqlite3 db test.db |
||
424 | |||
425 | do_test savepoint-7.1 { |
||
426 | execsql { PRAGMA auto_vacuum = incremental } |
||
427 | wal_set_journal_mode |
||
428 | execsql { |
||
429 | PRAGMA cache_size = 10; |
||
430 | BEGIN; |
||
431 | CREATE TABLE t1(a PRIMARY KEY, b); |
||
432 | INSERT INTO t1(a) VALUES('alligator'); |
||
433 | INSERT INTO t1(a) VALUES('angelfish'); |
||
434 | INSERT INTO t1(a) VALUES('ant'); |
||
435 | INSERT INTO t1(a) VALUES('antelope'); |
||
436 | INSERT INTO t1(a) VALUES('ape'); |
||
437 | INSERT INTO t1(a) VALUES('baboon'); |
||
438 | INSERT INTO t1(a) VALUES('badger'); |
||
439 | INSERT INTO t1(a) VALUES('bear'); |
||
440 | INSERT INTO t1(a) VALUES('beetle'); |
||
441 | INSERT INTO t1(a) VALUES('bird'); |
||
442 | INSERT INTO t1(a) VALUES('bison'); |
||
443 | UPDATE t1 SET b = randstr(1000,1000); |
||
444 | UPDATE t1 SET b = b||randstr(1000,1000); |
||
445 | UPDATE t1 SET b = b||randstr(1000,1000); |
||
446 | UPDATE t1 SET b = b||randstr(10,1000); |
||
447 | COMMIT; |
||
448 | } |
||
449 | expr ([execsql { PRAGMA page_count }] > 20) |
||
450 | } {1} |
||
451 | do_test savepoint-7.2.1 { |
||
452 | execsql { |
||
453 | BEGIN; |
||
454 | SAVEPOINT one; |
||
455 | CREATE TABLE t2(a, b); |
||
456 | INSERT INTO t2 SELECT a, b FROM t1; |
||
457 | ROLLBACK TO one; |
||
458 | } |
||
459 | execsql { |
||
460 | PRAGMA integrity_check; |
||
461 | } |
||
462 | } {ok} |
||
463 | do_test savepoint-7.2.2 { |
||
464 | execsql { |
||
465 | COMMIT; |
||
466 | PRAGMA integrity_check; |
||
467 | } |
||
468 | } {ok} |
||
469 | |||
470 | do_test savepoint-7.3.1 { |
||
471 | execsql { |
||
472 | CREATE TABLE t2(a, b); |
||
473 | INSERT INTO t2 SELECT a, b FROM t1; |
||
474 | } |
||
475 | } {} |
||
476 | do_test savepoint-7.3.2 { |
||
477 | execsql { |
||
478 | BEGIN; |
||
479 | SAVEPOINT one; |
||
480 | DELETE FROM t2; |
||
481 | PRAGMA incremental_vacuum; |
||
482 | SAVEPOINT two; |
||
483 | INSERT INTO t2 SELECT a, b FROM t1; |
||
484 | ROLLBACK TO two; |
||
485 | COMMIT; |
||
486 | } |
||
487 | execsql { PRAGMA integrity_check } |
||
488 | } {ok} |
||
489 | wal_check_journal_mode savepoint-7.3.3 |
||
490 | |||
491 | do_test savepoint-7.4.1 { |
||
492 | db close |
||
493 | file delete -force test.db |
||
494 | sqlite3 db test.db |
||
495 | execsql { PRAGMA auto_vacuum = incremental } |
||
496 | wal_set_journal_mode |
||
497 | execsql { |
||
498 | CREATE TABLE t1(a, b, PRIMARY KEY(a, b)); |
||
499 | INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000)); |
||
500 | BEGIN; |
||
501 | DELETE FROM t1; |
||
502 | SAVEPOINT one; |
||
503 | PRAGMA incremental_vacuum; |
||
504 | ROLLBACK TO one; |
||
505 | COMMIT; |
||
506 | } |
||
507 | |||
508 | execsql { PRAGMA integrity_check } |
||
509 | } {ok} |
||
510 | |||
511 | do_test savepoint-7.5.1 { |
||
512 | execsql { |
||
513 | PRAGMA incremental_vacuum; |
||
514 | CREATE TABLE t5(x, y); |
||
515 | INSERT INTO t5 VALUES(1, randstr(1000,1000)); |
||
516 | INSERT INTO t5 VALUES(2, randstr(1000,1000)); |
||
517 | INSERT INTO t5 VALUES(3, randstr(1000,1000)); |
||
518 | |||
519 | BEGIN; |
||
520 | INSERT INTO t5 VALUES(4, randstr(1000,1000)); |
||
521 | INSERT INTO t5 VALUES(5, randstr(1000,1000)); |
||
522 | DELETE FROM t5 WHERE x=1 OR x=2; |
||
523 | SAVEPOINT one; |
||
524 | PRAGMA incremental_vacuum; |
||
525 | SAVEPOINT two; |
||
526 | INSERT INTO t5 VALUES(1, randstr(1000,1000)); |
||
527 | INSERT INTO t5 VALUES(2, randstr(1000,1000)); |
||
528 | ROLLBACK TO two; |
||
529 | ROLLBACK TO one; |
||
530 | COMMIT; |
||
531 | PRAGMA integrity_check; |
||
532 | } |
||
533 | } {ok} |
||
534 | do_test savepoint-7.5.2 { |
||
535 | execsql { |
||
536 | DROP TABLE t5; |
||
537 | } |
||
538 | } {} |
||
539 | wal_check_journal_mode savepoint-7.5.3 |
||
540 | |||
541 | # Test oddly named and quoted savepoints. |
||
542 | # |
||
543 | do_test savepoint-8-1 { |
||
544 | execsql { SAVEPOINT "save1" } |
||
545 | execsql { RELEASE save1 } |
||
546 | } {} |
||
547 | do_test savepoint-8-2 { |
||
548 | execsql { SAVEPOINT "Including whitespace " } |
||
549 | execsql { RELEASE "including Whitespace " } |
||
550 | } {} |
||
551 | |||
552 | # Test that the authorization callback works. |
||
553 | # |
||
554 | ifcapable auth { |
||
555 | proc auth {args} { |
||
556 | eval lappend ::authdata $args |
||
557 | return SQLITE_OK |
||
558 | } |
||
559 | db auth auth |
||
560 | |||
561 | do_test savepoint-9.1 { |
||
562 | set ::authdata [list] |
||
563 | execsql { SAVEPOINT sp1 } |
||
564 | set ::authdata |
||
565 | } {SQLITE_SAVEPOINT BEGIN sp1 {} {}} |
||
566 | do_test savepoint-9.2 { |
||
567 | set ::authdata [list] |
||
568 | execsql { ROLLBACK TO sp1 } |
||
569 | set ::authdata |
||
570 | } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}} |
||
571 | do_test savepoint-9.3 { |
||
572 | set ::authdata [list] |
||
573 | execsql { RELEASE sp1 } |
||
574 | set ::authdata |
||
575 | } {SQLITE_SAVEPOINT RELEASE sp1 {} {}} |
||
576 | |||
577 | proc auth {args} { |
||
578 | eval lappend ::authdata $args |
||
579 | return SQLITE_DENY |
||
580 | } |
||
581 | db auth auth |
||
582 | |||
583 | do_test savepoint-9.4 { |
||
584 | set ::authdata [list] |
||
585 | set res [catchsql { SAVEPOINT sp1 }] |
||
586 | concat $::authdata $res |
||
587 | } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}} |
||
588 | do_test savepoint-9.5 { |
||
589 | set ::authdata [list] |
||
590 | set res [catchsql { ROLLBACK TO sp1 }] |
||
591 | concat $::authdata $res |
||
592 | } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}} |
||
593 | do_test savepoint-9.6 { |
||
594 | set ::authdata [list] |
||
595 | set res [catchsql { RELEASE sp1 }] |
||
596 | concat $::authdata $res |
||
597 | } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}} |
||
598 | |||
599 | catch { db eval ROLLBACK } |
||
600 | db auth "" |
||
601 | } |
||
602 | |||
603 | #------------------------------------------------------------------------- |
||
604 | # The following tests - savepoint-10.* - test the interaction of |
||
605 | # savepoints and ATTACH statements. |
||
606 | # |
||
607 | |||
608 | # First make sure it is not possible to attach or detach a database while |
||
609 | # a savepoint is open (it is not possible if any transaction is open). |
||
610 | # |
||
611 | do_test savepoint-10.1.1 { |
||
612 | catchsql { |
||
613 | SAVEPOINT one; |
||
614 | ATTACH 'test2.db' AS aux; |
||
615 | } |
||
616 | } {1 {cannot ATTACH database within transaction}} |
||
617 | do_test savepoint-10.1.2 { |
||
618 | execsql { |
||
619 | RELEASE one; |
||
620 | ATTACH 'test2.db' AS aux; |
||
621 | } |
||
622 | catchsql { |
||
623 | SAVEPOINT one; |
||
624 | DETACH aux; |
||
625 | } |
||
626 | } {1 {cannot DETACH database within transaction}} |
||
627 | do_test savepoint-10.1.3 { |
||
628 | execsql { |
||
629 | RELEASE one; |
||
630 | DETACH aux; |
||
631 | } |
||
632 | } {} |
||
633 | |||
634 | # The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3 |
||
635 | # And the following set of tests is only really interested in the status |
||
636 | # of the aux1 and aux2 locks. So record the current lock status of |
||
637 | # TEMP for use in the answers. |
||
638 | set templockstate [lindex [db eval {PRAGMA lock_status}] 3] |
||
639 | |||
640 | |||
641 | if {[wal_is_wal_mode]==0} { |
||
642 | do_test savepoint-10.2.1 { |
||
643 | file delete -force test3.db |
||
644 | file delete -force test2.db |
||
645 | execsql { |
||
646 | ATTACH 'test2.db' AS aux1; |
||
647 | ATTACH 'test3.db' AS aux2; |
||
648 | DROP TABLE t1; |
||
649 | CREATE TABLE main.t1(x, y); |
||
650 | CREATE TABLE aux1.t2(x, y); |
||
651 | CREATE TABLE aux2.t3(x, y); |
||
652 | SELECT name FROM sqlite_master |
||
653 | UNION ALL |
||
654 | SELECT name FROM aux1.sqlite_master |
||
655 | UNION ALL |
||
656 | SELECT name FROM aux2.sqlite_master; |
||
657 | } |
||
658 | } {t1 t2 t3} |
||
659 | do_test savepoint-10.2.2 { |
||
660 | execsql { PRAGMA lock_status } |
||
661 | } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] |
||
662 | |||
663 | do_test savepoint-10.2.3 { |
||
664 | execsql { |
||
665 | SAVEPOINT one; |
||
666 | INSERT INTO t1 VALUES(1, 2); |
||
667 | PRAGMA lock_status; |
||
668 | } |
||
669 | } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked] |
||
670 | do_test savepoint-10.2.4 { |
||
671 | execsql { |
||
672 | INSERT INTO t3 VALUES(3, 4); |
||
673 | PRAGMA lock_status; |
||
674 | } |
||
675 | } [list main reserved temp $templockstate aux1 unlocked aux2 reserved] |
||
676 | do_test savepoint-10.2.5 { |
||
677 | execsql { |
||
678 | SAVEPOINT two; |
||
679 | INSERT INTO t2 VALUES(5, 6); |
||
680 | PRAGMA lock_status; |
||
681 | } |
||
682 | } [list main reserved temp $templockstate aux1 reserved aux2 reserved] |
||
683 | do_test savepoint-10.2.6 { |
||
684 | execsql { SELECT * FROM t2 } |
||
685 | } {5 6} |
||
686 | do_test savepoint-10.2.7 { |
||
687 | execsql { ROLLBACK TO two } |
||
688 | execsql { SELECT * FROM t2 } |
||
689 | } {} |
||
690 | do_test savepoint-10.2.8 { |
||
691 | execsql { PRAGMA lock_status } |
||
692 | } [list main reserved temp $templockstate aux1 reserved aux2 reserved] |
||
693 | do_test savepoint-10.2.9 { |
||
694 | execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 } |
||
695 | } {a 1 2 b 3 4} |
||
696 | do_test savepoint-10.2.9 { |
||
697 | execsql { |
||
698 | INSERT INTO t2 VALUES(5, 6); |
||
699 | RELEASE one; |
||
700 | } |
||
701 | execsql { |
||
702 | SELECT * FROM t1; |
||
703 | SELECT * FROM t2; |
||
704 | SELECT * FROM t3; |
||
705 | } |
||
706 | } {1 2 5 6 3 4} |
||
707 | do_test savepoint-10.2.9 { |
||
708 | execsql { PRAGMA lock_status } |
||
709 | } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] |
||
710 | |||
711 | do_test savepoint-10.2.10 { |
||
712 | execsql { |
||
713 | SAVEPOINT one; |
||
714 | INSERT INTO t1 VALUES('a', 'b'); |
||
715 | SAVEPOINT two; |
||
716 | INSERT INTO t2 VALUES('c', 'd'); |
||
717 | SAVEPOINT three; |
||
718 | INSERT INTO t3 VALUES('e', 'f'); |
||
719 | } |
||
720 | execsql { |
||
721 | SELECT * FROM t1; |
||
722 | SELECT * FROM t2; |
||
723 | SELECT * FROM t3; |
||
724 | } |
||
725 | } {1 2 a b 5 6 c d 3 4 e f} |
||
726 | do_test savepoint-10.2.11 { |
||
727 | execsql { ROLLBACK TO two } |
||
728 | execsql { |
||
729 | SELECT * FROM t1; |
||
730 | SELECT * FROM t2; |
||
731 | SELECT * FROM t3; |
||
732 | } |
||
733 | } {1 2 a b 5 6 3 4} |
||
734 | do_test savepoint-10.2.12 { |
||
735 | execsql { |
||
736 | INSERT INTO t3 VALUES('g', 'h'); |
||
737 | ROLLBACK TO two; |
||
738 | } |
||
739 | execsql { |
||
740 | SELECT * FROM t1; |
||
741 | SELECT * FROM t2; |
||
742 | SELECT * FROM t3; |
||
743 | } |
||
744 | } {1 2 a b 5 6 3 4} |
||
745 | do_test savepoint-10.2.13 { |
||
746 | execsql { ROLLBACK } |
||
747 | execsql { |
||
748 | SELECT * FROM t1; |
||
749 | SELECT * FROM t2; |
||
750 | SELECT * FROM t3; |
||
751 | } |
||
752 | } {1 2 5 6 3 4} |
||
753 | do_test savepoint-10.2.14 { |
||
754 | execsql { PRAGMA lock_status } |
||
755 | } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked] |
||
756 | } |
||
757 | |||
758 | #------------------------------------------------------------------------- |
||
759 | # The following tests - savepoint-11.* - test the interaction of |
||
760 | # savepoints and creating or dropping tables and indexes in |
||
761 | # auto-vacuum mode. |
||
762 | # |
||
763 | do_test savepoint-11.1 { |
||
764 | db close |
||
765 | file delete -force test.db |
||
766 | sqlite3 db test.db |
||
767 | execsql { PRAGMA auto_vacuum = full; } |
||
768 | wal_set_journal_mode |
||
769 | execsql { |
||
770 | CREATE TABLE t1(a, b, UNIQUE(a, b)); |
||
771 | INSERT INTO t1 VALUES(1, randstr(1000,1000)); |
||
772 | INSERT INTO t1 VALUES(2, randstr(1000,1000)); |
||
773 | } |
||
774 | } {} |
||
775 | do_test savepoint-11.2 { |
||
776 | execsql { |
||
777 | SAVEPOINT one; |
||
778 | CREATE TABLE t2(a, b, UNIQUE(a, b)); |
||
779 | SAVEPOINT two; |
||
780 | CREATE TABLE t3(a, b, UNIQUE(a, b)); |
||
781 | } |
||
782 | } {} |
||
783 | integrity_check savepoint-11.3 |
||
784 | do_test savepoint-11.4 { |
||
785 | execsql { ROLLBACK TO two } |
||
786 | } {} |
||
787 | integrity_check savepoint-11.5 |
||
788 | do_test savepoint-11.6 { |
||
789 | execsql { |
||
790 | CREATE TABLE t3(a, b, UNIQUE(a, b)); |
||
791 | ROLLBACK TO one; |
||
792 | } |
||
793 | } {} |
||
794 | integrity_check savepoint-11.7 |
||
795 | do_test savepoint-11.8 { |
||
796 | execsql { ROLLBACK } |
||
797 | execsql { PRAGMA wal_checkpoint } |
||
798 | file size test.db |
||
799 | } {8192} |
||
800 | |||
801 | do_test savepoint-11.9 { |
||
802 | execsql { |
||
803 | DROP TABLE IF EXISTS t1; |
||
804 | DROP TABLE IF EXISTS t2; |
||
805 | DROP TABLE IF EXISTS t3; |
||
806 | } |
||
807 | } {} |
||
808 | do_test savepoint-11.10 { |
||
809 | execsql { |
||
810 | BEGIN; |
||
811 | CREATE TABLE t1(a, b); |
||
812 | CREATE TABLE t2(x, y); |
||
813 | INSERT INTO t2 VALUES(1, 2); |
||
814 | SAVEPOINT one; |
||
815 | INSERT INTO t2 VALUES(3, 4); |
||
816 | SAVEPOINT two; |
||
817 | DROP TABLE t1; |
||
818 | ROLLBACK TO two; |
||
819 | } |
||
820 | execsql {SELECT * FROM t2} |
||
821 | } {1 2 3 4} |
||
822 | do_test savepoint-11.11 { |
||
823 | execsql COMMIT |
||
824 | } {} |
||
825 | do_test savepoint-11.12 { |
||
826 | execsql {SELECT * FROM t2} |
||
827 | } {1 2 3 4} |
||
828 | wal_check_journal_mode savepoint-11.13 |
||
829 | |||
830 | #------------------------------------------------------------------------- |
||
831 | # The following tests - savepoint-12.* - test the interaction of |
||
832 | # savepoints and "ON CONFLICT ROLLBACK" clauses. |
||
833 | # |
||
834 | do_test savepoint-12.1 { |
||
835 | execsql { |
||
836 | CREATE TABLE t4(a PRIMARY KEY, b); |
||
837 | INSERT INTO t4 VALUES(1, 'one'); |
||
838 | } |
||
839 | } {} |
||
840 | do_test savepoint-12.2 { |
||
841 | # The final statement of the following SQL hits a constraint when the |
||
842 | # conflict handling mode is "OR ROLLBACK" and there are a couple of |
||
843 | # open savepoints. At one point this would fail to clear the internal |
||
844 | # record of the open savepoints, resulting in an assert() failure |
||
845 | # later on. |
||
846 | # |
||
847 | catchsql { |
||
848 | BEGIN; |
||
849 | INSERT INTO t4 VALUES(2, 'two'); |
||
850 | SAVEPOINT sp1; |
||
851 | INSERT INTO t4 VALUES(3, 'three'); |
||
852 | SAVEPOINT sp2; |
||
853 | INSERT OR ROLLBACK INTO t4 VALUES(1, 'one'); |
||
854 | } |
||
855 | } {1 {column a is not unique}} |
||
856 | do_test savepoint-12.3 { |
||
857 | sqlite3_get_autocommit db |
||
858 | } {1} |
||
859 | do_test savepoint-12.4 { |
||
860 | execsql { SAVEPOINT one } |
||
861 | } {} |
||
862 | wal_check_journal_mode savepoint-12.5 |
||
863 | |||
864 | #------------------------------------------------------------------------- |
||
865 | # The following tests - savepoint-13.* - test the interaction of |
||
866 | # savepoints and "journal_mode = off". |
||
867 | # |
||
868 | if {[wal_is_wal_mode]==0} { |
||
869 | do_test savepoint-13.1 { |
||
870 | db close |
||
871 | catch {file delete -force test.db} |
||
872 | sqlite3 db test.db |
||
873 | execsql { |
||
874 | BEGIN; |
||
875 | CREATE TABLE t1(a PRIMARY KEY, b); |
||
876 | INSERT INTO t1 VALUES(1, 2); |
||
877 | COMMIT; |
||
878 | PRAGMA journal_mode = off; |
||
879 | } |
||
880 | } {off} |
||
881 | do_test savepoint-13.2 { |
||
882 | execsql { |
||
883 | BEGIN; |
||
884 | INSERT INTO t1 VALUES(3, 4); |
||
885 | INSERT INTO t1 SELECT a+4,b+4 FROM t1; |
||
886 | COMMIT; |
||
887 | } |
||
888 | } {} |
||
889 | do_test savepoint-13.3 { |
||
890 | execsql { |
||
891 | BEGIN; |
||
892 | INSERT INTO t1 VALUES(9, 10); |
||
893 | SAVEPOINT s1; |
||
894 | INSERT INTO t1 VALUES(11, 12); |
||
895 | COMMIT; |
||
896 | } |
||
897 | } {} |
||
898 | do_test savepoint-13.4 { |
||
899 | execsql { |
||
900 | BEGIN; |
||
901 | INSERT INTO t1 VALUES(13, 14); |
||
902 | SAVEPOINT s1; |
||
903 | INSERT INTO t1 VALUES(15, 16); |
||
904 | ROLLBACK TO s1; |
||
905 | ROLLBACK; |
||
906 | SELECT * FROM t1; |
||
907 | } |
||
908 | } {1 2 3 4 5 6 7 8 9 10 11 12} |
||
909 | } |
||
910 | |||
911 | db close |
||
912 | file delete test.db |
||
913 | if 0 { |
||
914 | do_multiclient_test tn { |
||
915 | do_test savepoint-14.$tn.1 { |
||
916 | sql1 { |
||
917 | CREATE TABLE foo(x); |
||
918 | INSERT INTO foo VALUES(1); |
||
919 | INSERT INTO foo VALUES(2); |
||
920 | } |
||
921 | sql2 { |
||
922 | BEGIN; |
||
923 | SELECT * FROM foo; |
||
924 | } |
||
925 | } {1 2} |
||
926 | do_test savepoint-14.$tn.2 { |
||
927 | sql1 { |
||
928 | SAVEPOINT one; |
||
929 | INSERT INTO foo VALUES(1); |
||
930 | } |
||
931 | csql1 { RELEASE one } |
||
932 | } {1 {database is locked}} |
||
933 | do_test savepoint-14.$tn.3 { |
||
934 | sql1 { ROLLBACK TO one } |
||
935 | sql2 { COMMIT } |
||
936 | sql1 { RELEASE one } |
||
937 | } {} |
||
938 | |||
939 | do_test savepoint-14.$tn.4 { |
||
940 | sql2 { |
||
941 | BEGIN; |
||
942 | SELECT * FROM foo; |
||
943 | } |
||
944 | } {1 2} |
||
945 | do_test savepoint-14.$tn.5 { |
||
946 | sql1 { |
||
947 | SAVEPOINT one; |
||
948 | INSERT INTO foo VALUES(1); |
||
949 | } |
||
950 | csql1 { RELEASE one } |
||
951 | } {1 {database is locked}} |
||
952 | do_test savepoint-14.$tn.6 { |
||
953 | sql2 { COMMIT } |
||
954 | sql1 { |
||
955 | ROLLBACK TO one; |
||
956 | INSERT INTO foo VALUES(3); |
||
957 | INSERT INTO foo VALUES(4); |
||
958 | INSERT INTO foo VALUES(5); |
||
959 | RELEASE one; |
||
960 | } |
||
961 | } {} |
||
962 | do_test savepoint-14.$tn.7 { |
||
963 | sql2 { CREATE INDEX fooidx ON foo(x); } |
||
964 | sql3 { PRAGMA integrity_check } |
||
965 | } {ok} |
||
966 | } |
||
967 | |||
968 | do_multiclient_test tn { |
||
969 | do_test savepoint-15.$tn.1 { |
||
970 | sql1 { |
||
971 | CREATE TABLE foo(x); |
||
972 | INSERT INTO foo VALUES(1); |
||
973 | INSERT INTO foo VALUES(2); |
||
974 | } |
||
975 | sql2 { BEGIN; SELECT * FROM foo; } |
||
976 | } {1 2} |
||
977 | do_test savepoint-15.$tn.2 { |
||
978 | sql1 { |
||
979 | PRAGMA locking_mode = EXCLUSIVE; |
||
980 | BEGIN; |
||
981 | INSERT INTO foo VALUES(3); |
||
982 | } |
||
983 | csql1 { COMMIT } |
||
984 | } {1 {database is locked}} |
||
985 | do_test savepoint-15.$tn.3 { |
||
986 | sql1 { ROLLBACK } |
||
987 | sql2 { COMMIT } |
||
988 | sql1 { |
||
989 | INSERT INTO foo VALUES(3); |
||
990 | PRAGMA locking_mode = NORMAL; |
||
991 | INSERT INTO foo VALUES(4); |
||
992 | } |
||
993 | sql2 { CREATE INDEX fooidx ON foo(x); } |
||
994 | sql3 { PRAGMA integrity_check } |
||
995 | } {ok} |
||
996 | } |
||
997 | |||
998 | do_multiclient_test tn { |
||
999 | do_test savepoint-16.$tn.1 { |
||
1000 | sql1 { |
||
1001 | CREATE TABLE foo(x); |
||
1002 | INSERT INTO foo VALUES(1); |
||
1003 | INSERT INTO foo VALUES(2); |
||
1004 | } |
||
1005 | } {} |
||
1006 | do_test savepoint-16.$tn.2 { |
||
1007 | |||
1008 | db eval {SELECT * FROM foo} { |
||
1009 | sql1 { INSERT INTO foo VALUES(3) } |
||
1010 | sql2 { SELECT * FROM foo } |
||
1011 | sql1 { INSERT INTO foo VALUES(4) } |
||
1012 | break |
||
1013 | } |
||
1014 | |||
1015 | sql2 { CREATE INDEX fooidx ON foo(x); } |
||
1016 | sql3 { PRAGMA integrity_check } |
||
1017 | } {ok} |
||
1018 | do_test savepoint-16.$tn.3 { |
||
1019 | sql1 { SELECT * FROM foo } |
||
1020 | } {1 2 3 4} |
||
1021 | } |
||
1022 | } |
||
1023 | #------------------------------------------------------------------------- |
||
1024 | # This next block of tests verifies that a problem reported on the mailing |
||
1025 | # list has been resolved. At one point the second "CREATE TABLE t6" would |
||
1026 | # fail as table t6 still existed in the internal cache of the db schema |
||
1027 | # (even though it had been removed from the database by the ROLLBACK |
||
1028 | # command). |
||
1029 | # |
||
1030 | sqlite3 db test.db |
||
1031 | do_execsql_test savepoint-17.1 { |
||
1032 | BEGIN; |
||
1033 | CREATE TABLE t6(a, b); |
||
1034 | INSERT INTO t6 VALUES(1, 2); |
||
1035 | SAVEPOINT one; |
||
1036 | INSERT INTO t6 VALUES(3, 4); |
||
1037 | ROLLBACK TO one; |
||
1038 | SELECT * FROM t6; |
||
1039 | ROLLBACK; |
||
1040 | } {1 2} |
||
1041 | |||
1042 | do_execsql_test savepoint-17.2 { |
||
1043 | CREATE TABLE t6(a, b); |
||
1044 | } {} |
||
1045 | |||
1046 | finish_test |