| blob | 042c5558947da07f5691bad94bd39cbc0928b5ae |
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 $
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 source $testdir/lock_common.tcl
17 source $testdir/malloc_common.tcl
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
100 #------------------------------------------------------------------------
101 # These tests - savepoint-2.* - test rollbacks and releases of savepoints
102 # with a very simple data set.
103 #
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}
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
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}
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}
202 do_test savepoint-3.3 {
203 execsql { ROLLBACK TO "transaction" }
204 execsql { PRAGMA lock_status }
205 } {main reserved temp closed}
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}
212 do_test savepoint-3.5 {
213 execsql { RELEASE "transaction" }
214 execsql { PRAGMA lock_status }
215 } {main unlocked temp closed}
216 }
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)}}
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
278 #------------------------------------------------------------------------
279 # Test some logic errors to do with the savepoint feature.
280 #
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 {}}
297 do_test savepoint-5.2 {
298 execsql {RELEASE abc}
299 catchsql {RELEASE abc}
300 } {1 {no such savepoint: abc}}
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 } {}
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
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
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 } {}
393 integrity_check savepoint-6.2
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 } {}
412 integrity_check savepoint-6.4
414 wal_check_journal_mode savepoint-6.5
415 }
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
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}
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
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 }
508 execsql { PRAGMA integrity_check }
509 } {ok}
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));
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
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 } {}
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
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 {} {}}
577 proc auth {args} {
578 eval lappend ::authdata $args
579 return SQLITE_DENY
580 }
581 db auth auth
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}}
599 catch { db eval ROLLBACK }
600 db auth ""
601 }
603 #-------------------------------------------------------------------------
604 # The following tests - savepoint-10.* - test the interaction of
605 # savepoints and ATTACH statements.
606 #
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 } {}
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]
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]
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]
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 }
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}
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
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
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 }
911 db close
912 file delete test.db
913 do_multiclient_test tn {
914 do_test savepoint-14.$tn.1 {
915 sql1 {
916 CREATE TABLE foo(x);
917 INSERT INTO foo VALUES(1);
918 INSERT INTO foo VALUES(2);
919 }
920 sql2 {
921 BEGIN;
922 SELECT * FROM foo;
923 }
924 } {1 2}
925 do_test savepoint-14.$tn.2 {
926 sql1 {
927 SAVEPOINT one;
928 INSERT INTO foo VALUES(1);
929 }
930 csql1 { RELEASE one }
931 } {1 {database is locked}}
932 do_test savepoint-14.$tn.3 {
933 sql1 { ROLLBACK TO one }
934 sql2 { COMMIT }
935 sql1 { RELEASE one }
936 } {}
938 do_test savepoint-14.$tn.4 {
939 sql2 {
940 BEGIN;
941 SELECT * FROM foo;
942 }
943 } {1 2}
944 do_test savepoint-14.$tn.5 {
945 sql1 {
946 SAVEPOINT one;
947 INSERT INTO foo VALUES(1);
948 }
949 csql1 { RELEASE one }
950 } {1 {database is locked}}
951 do_test savepoint-14.$tn.6 {
952 sql2 { COMMIT }
953 sql1 {
954 ROLLBACK TO one;
955 INSERT INTO foo VALUES(3);
956 INSERT INTO foo VALUES(4);
957 INSERT INTO foo VALUES(5);
958 RELEASE one;
959 }
960 } {}
961 do_test savepoint-14.$tn.7 {
962 sql2 { CREATE INDEX fooidx ON foo(x); }
963 sql3 { PRAGMA integrity_check }
964 } {ok}
965 }
967 do_multiclient_test tn {
968 do_test savepoint-15.$tn.1 {
969 sql1 {
970 CREATE TABLE foo(x);
971 INSERT INTO foo VALUES(1);
972 INSERT INTO foo VALUES(2);
973 }
974 sql2 { BEGIN; SELECT * FROM foo; }
975 } {1 2}
976 do_test savepoint-15.$tn.2 {
977 sql1 {
978 PRAGMA locking_mode = EXCLUSIVE;
979 BEGIN;
980 INSERT INTO foo VALUES(3);
981 }
982 csql1 { COMMIT }
983 } {1 {database is locked}}
984 do_test savepoint-15.$tn.3 {
985 sql1 { ROLLBACK }
986 sql2 { COMMIT }
987 sql1 {
988 INSERT INTO foo VALUES(3);
989 PRAGMA locking_mode = NORMAL;
990 INSERT INTO foo VALUES(4);
991 }
992 sql2 { CREATE INDEX fooidx ON foo(x); }
993 sql3 { PRAGMA integrity_check }
994 } {ok}
995 }
997 do_multiclient_test tn {
998 do_test savepoint-16.$tn.1 {
999 sql1 {
1000 CREATE TABLE foo(x);
1001 INSERT INTO foo VALUES(1);
1002 INSERT INTO foo VALUES(2);
1003 }
1004 } {}
1005 do_test savepoint-16.$tn.2 {
1007 db eval {SELECT * FROM foo} {
1008 sql1 { INSERT INTO foo VALUES(3) }
1009 sql2 { SELECT * FROM foo }
1010 sql1 { INSERT INTO foo VALUES(4) }
1011 break
1012 }
1014 sql2 { CREATE INDEX fooidx ON foo(x); }
1015 sql3 { PRAGMA integrity_check }
1016 } {ok}
1017 do_test savepoint-16.$tn.3 {
1018 sql1 { SELECT * FROM foo }
1019 } {1 2 3 4}
1020 }
1022 #-------------------------------------------------------------------------
1023 # This next block of tests verifies that a problem reported on the mailing
1024 # list has been resolved. At one point the second "CREATE TABLE t6" would
1025 # fail as table t6 still existed in the internal cache of the db schema
1026 # (even though it had been removed from the database by the ROLLBACK
1027 # command).
1028 #
1029 sqlite3 db test.db
1030 do_execsql_test savepoint-17.1 {
1031 BEGIN;
1032 CREATE TABLE t6(a, b);
1033 INSERT INTO t6 VALUES(1, 2);
1034 SAVEPOINT one;
1035 INSERT INTO t6 VALUES(3, 4);
1036 ROLLBACK TO one;
1037 SELECT * FROM t6;
1038 ROLLBACK;
1039 } {1 2}
1041 do_execsql_test savepoint-17.2 {
1042 CREATE TABLE t6(a, b);
1043 } {}
1045 finish_test