Debian Codegraph @

test/savepoint.test

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