| blob | 8055e61d9e3ee7c249c382e26d7ebbf51d2d6066 |
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.1 {
307 execsql {SAVEPOINT def}
308 set fd [db incrblob -readonly blobs x 1]
309 set rc [catch {seek $fd 0;read $fd} res]
310 lappend rc $res
311 } {0 {hellontyeight character blob}}
312 do_test savepoint-5.3.2.2 {
313 catchsql {ROLLBACK TO def}
314 } {0 {}}
315 do_test savepoint-5.3.2.3 {
316 set rc [catch {seek $fd 0; read $fd} res]
317 set rc
318 } {0}
319 do_test savepoint-5.3.3 {
320 catchsql {RELEASE def}
321 } {0 {}}
322 do_test savepoint-5.3.4 {
323 close $fd
324 execsql {savepoint def}
325 set fd [db incrblob blobs x 1]
326 catchsql {release def}
327 } {1 {cannot release savepoint - SQL statements in progress}}
328 do_test savepoint-5.3.5 {
329 close $fd
330 execsql {release abc}
331 } {}
333 # Rollback mode:
334 #
335 # Open a savepoint transaction and insert a row into the database. Then,
336 # using a second database handle, open a read-only transaction on the
337 # database file. Check that the savepoint transaction cannot be committed
338 # until after the read-only transaction has been closed.
339 #
340 # WAL mode:
341 #
342 # As above, except that the savepoint transaction can be successfully
343 # committed before the read-only transaction has been closed.
344 #
345 do_test savepoint-5.4.1 {
346 execsql {
347 SAVEPOINT main;
348 INSERT INTO blobs VALUES('another blob');
349 }
350 } {}
351 do_test savepoint-5.4.2 {
352 sqlite3 db2 test.db
353 execsql { BEGIN ; SELECT count(*) FROM blobs } db2
354 } {1}
355 if {[wal_is_wal_mode]} {
356 do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}}
357 do_test savepoint-5.4.4 { db2 close } {}
358 } else {
359 do_test savepoint-5.4.3 {
360 catchsql { RELEASE main }
361 } {1 {database is locked}}
362 do_test savepoint-5.4.4 {
363 db2 close
364 catchsql { RELEASE main }
365 } {0 {}}
366 }
367 do_test savepoint-5.4.5 {
368 execsql { SELECT x FROM blobs WHERE rowid = 2 }
369 } {{another blob}}
370 do_test savepoint-5.4.6 {
371 execsql { SELECT count(*) FROM blobs }
372 } {2}
373 }
374 wal_check_journal_mode savepoint-5.5
376 #-------------------------------------------------------------------------
377 # The following tests, savepoint-6.*, test an incr-vacuum inside of a
378 # couple of nested savepoints.
379 #
380 ifcapable {autovacuum && pragma} {
381 db close
382 forcedelete test.db
383 sqlite3 db test.db
385 do_test savepoint-6.1 {
386 execsql { PRAGMA auto_vacuum = incremental }
387 wal_set_journal_mode
388 execsql {
389 CREATE TABLE t1(a, b, c);
390 CREATE INDEX i1 ON t1(a, b);
391 BEGIN;
392 INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400));
393 }
394 set r "randstr(10,400)"
395 for {set ii 0} {$ii < 10} {incr ii} {
396 execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1"
397 }
398 execsql { COMMIT }
399 } {}
401 integrity_check savepoint-6.2
403 do_test savepoint-6.3 {
404 execsql {
405 PRAGMA cache_size = 10;
406 BEGIN;
407 UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0;
408 SAVEPOINT one;
409 DELETE FROM t1 WHERE rowid%2;
410 PRAGMA incr_vacuum;
411 SAVEPOINT two;
412 INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1;
413 DELETE FROM t1 WHERE rowid%2;
414 PRAGMA incr_vacuum;
415 ROLLBACK TO one;
416 COMMIT;
417 }
418 } {}
420 integrity_check savepoint-6.4
422 wal_check_journal_mode savepoint-6.5
423 }
425 #-------------------------------------------------------------------------
426 # The following tests, savepoint-7.*, attempt to break the logic
427 # surrounding savepoints by growing and shrinking the database file.
428 #
429 db close
430 forcedelete test.db
431 sqlite3 db test.db
433 do_test savepoint-7.1 {
434 execsql { PRAGMA auto_vacuum = incremental }
435 wal_set_journal_mode
436 execsql {
437 PRAGMA cache_size = 10;
438 BEGIN;
439 CREATE TABLE t1(a PRIMARY KEY, b);
440 INSERT INTO t1(a) VALUES('alligator');
441 INSERT INTO t1(a) VALUES('angelfish');
442 INSERT INTO t1(a) VALUES('ant');
443 INSERT INTO t1(a) VALUES('antelope');
444 INSERT INTO t1(a) VALUES('ape');
445 INSERT INTO t1(a) VALUES('baboon');
446 INSERT INTO t1(a) VALUES('badger');
447 INSERT INTO t1(a) VALUES('bear');
448 INSERT INTO t1(a) VALUES('beetle');
449 INSERT INTO t1(a) VALUES('bird');
450 INSERT INTO t1(a) VALUES('bison');
451 UPDATE t1 SET b = randstr(1000,1000);
452 UPDATE t1 SET b = b||randstr(1000,1000);
453 UPDATE t1 SET b = b||randstr(1000,1000);
454 UPDATE t1 SET b = b||randstr(10,1000);
455 COMMIT;
456 }
457 expr ([execsql { PRAGMA page_count }] > 20)
458 } {1}
459 do_test savepoint-7.2.1 {
460 execsql {
461 BEGIN;
462 SAVEPOINT one;
463 CREATE TABLE t2(a, b);
464 INSERT INTO t2 SELECT a, b FROM t1;
465 ROLLBACK TO one;
466 }
467 execsql {
468 PRAGMA integrity_check;
469 }
470 } {ok}
471 do_test savepoint-7.2.2 {
472 execsql {
473 COMMIT;
474 PRAGMA integrity_check;
475 }
476 } {ok}
478 do_test savepoint-7.3.1 {
479 execsql {
480 CREATE TABLE t2(a, b);
481 INSERT INTO t2 SELECT a, b FROM t1;
482 }
483 } {}
484 do_test savepoint-7.3.2 {
485 execsql {
486 BEGIN;
487 SAVEPOINT one;
488 DELETE FROM t2;
489 PRAGMA incremental_vacuum;
490 SAVEPOINT two;
491 INSERT INTO t2 SELECT a, b FROM t1;
492 ROLLBACK TO two;
493 COMMIT;
494 }
495 execsql { PRAGMA integrity_check }
496 } {ok}
497 wal_check_journal_mode savepoint-7.3.3
499 do_test savepoint-7.4.1 {
500 db close
501 forcedelete test.db
502 sqlite3 db test.db
503 execsql { PRAGMA auto_vacuum = incremental }
504 wal_set_journal_mode
505 execsql {
506 CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
507 INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000));
508 BEGIN;
509 DELETE FROM t1;
510 SAVEPOINT one;
511 PRAGMA incremental_vacuum;
512 ROLLBACK TO one;
513 COMMIT;
514 }
516 execsql { PRAGMA integrity_check }
517 } {ok}
519 do_test savepoint-7.5.1 {
520 execsql {
521 PRAGMA incremental_vacuum;
522 CREATE TABLE t5(x, y);
523 INSERT INTO t5 VALUES(1, randstr(1000,1000));
524 INSERT INTO t5 VALUES(2, randstr(1000,1000));
525 INSERT INTO t5 VALUES(3, randstr(1000,1000));
527 BEGIN;
528 INSERT INTO t5 VALUES(4, randstr(1000,1000));
529 INSERT INTO t5 VALUES(5, randstr(1000,1000));
530 DELETE FROM t5 WHERE x=1 OR x=2;
531 SAVEPOINT one;
532 PRAGMA incremental_vacuum;
533 SAVEPOINT two;
534 INSERT INTO t5 VALUES(1, randstr(1000,1000));
535 INSERT INTO t5 VALUES(2, randstr(1000,1000));
536 ROLLBACK TO two;
537 ROLLBACK TO one;
538 COMMIT;
539 PRAGMA integrity_check;
540 }
541 } {ok}
542 do_test savepoint-7.5.2 {
543 execsql {
544 DROP TABLE t5;
545 }
546 } {}
547 wal_check_journal_mode savepoint-7.5.3
549 # Test oddly named and quoted savepoints.
550 #
551 do_test savepoint-8-1 {
552 execsql { SAVEPOINT "save1" }
553 execsql { RELEASE save1 }
554 } {}
555 do_test savepoint-8-2 {
556 execsql { SAVEPOINT "Including whitespace " }
557 execsql { RELEASE "including Whitespace " }
558 } {}
560 # Test that the authorization callback works.
561 #
562 ifcapable auth {
563 proc auth {args} {
564 eval lappend ::authdata [lrange $args 0 4]
565 return SQLITE_OK
566 }
567 db auth auth
569 do_test savepoint-9.1 {
570 set ::authdata [list]
571 execsql { SAVEPOINT sp1 }
572 set ::authdata
573 } {SQLITE_SAVEPOINT BEGIN sp1 {} {}}
574 do_test savepoint-9.2 {
575 set ::authdata [list]
576 execsql { ROLLBACK TO sp1 }
577 set ::authdata
578 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}}
579 do_test savepoint-9.3 {
580 set ::authdata [list]
581 execsql { RELEASE sp1 }
582 set ::authdata
583 } {SQLITE_SAVEPOINT RELEASE sp1 {} {}}
585 proc auth {args} {
586 eval lappend ::authdata [lrange $args 0 4]
587 return SQLITE_DENY
588 }
589 db auth auth
591 do_test savepoint-9.4 {
592 set ::authdata [list]
593 set res [catchsql { SAVEPOINT sp1 }]
594 concat $::authdata $res
595 } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}}
596 do_test savepoint-9.5 {
597 set ::authdata [list]
598 set res [catchsql { ROLLBACK TO sp1 }]
599 concat $::authdata $res
600 } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}}
601 do_test savepoint-9.6 {
602 set ::authdata [list]
603 set res [catchsql { RELEASE sp1 }]
604 concat $::authdata $res
605 } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}}
607 catch { db eval ROLLBACK }
608 db auth ""
609 }
611 #-------------------------------------------------------------------------
612 # The following tests - savepoint-10.* - test the interaction of
613 # savepoints and ATTACH statements.
614 #
616 # First make sure it is not possible to attach or detach a database while
617 # a savepoint is open (it is not possible if any transaction is open).
618 #
619 do_test savepoint-10.1.1 {
620 catchsql {
621 SAVEPOINT one;
622 ATTACH 'test2.db' AS aux;
623 }
624 } {1 {cannot ATTACH database within transaction}}
625 do_test savepoint-10.1.2 {
626 execsql {
627 RELEASE one;
628 ATTACH 'test2.db' AS aux;
629 }
630 catchsql {
631 SAVEPOINT one;
632 DETACH aux;
633 }
634 } {1 {cannot DETACH database within transaction}}
635 do_test savepoint-10.1.3 {
636 execsql {
637 RELEASE one;
638 DETACH aux;
639 }
640 } {}
642 # The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3
643 # And the following set of tests is only really interested in the status
644 # of the aux1 and aux2 locks. So record the current lock status of
645 # TEMP for use in the answers.
646 set templockstate [lindex [db eval {PRAGMA lock_status}] 3]
649 if {[wal_is_wal_mode]==0} {
650 do_test savepoint-10.2.1 {
651 forcedelete test3.db
652 forcedelete test2.db
653 execsql {
654 ATTACH 'test2.db' AS aux1;
655 ATTACH 'test3.db' AS aux2;
656 DROP TABLE t1;
657 CREATE TABLE main.t1(x, y);
658 CREATE TABLE aux1.t2(x, y);
659 CREATE TABLE aux2.t3(x, y);
660 SELECT name FROM sqlite_master;
661 SELECT name FROM aux1.sqlite_master;
662 SELECT name FROM aux2.sqlite_master;
663 }
664 } {t1 t2 t3}
665 do_test savepoint-10.2.2 {
666 execsql { PRAGMA lock_status }
667 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
669 do_test savepoint-10.2.3 {
670 execsql {
671 SAVEPOINT one;
672 INSERT INTO t1 VALUES(1, 2);
673 PRAGMA lock_status;
674 }
675 } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked]
676 do_test savepoint-10.2.4 {
677 execsql {
678 INSERT INTO t3 VALUES(3, 4);
679 PRAGMA lock_status;
680 }
681 } [list main reserved temp $templockstate aux1 unlocked aux2 reserved]
682 do_test savepoint-10.2.5 {
683 execsql {
684 SAVEPOINT two;
685 INSERT INTO t2 VALUES(5, 6);
686 PRAGMA lock_status;
687 }
688 } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
689 do_test savepoint-10.2.6 {
690 execsql { SELECT * FROM t2 }
691 } {5 6}
692 do_test savepoint-10.2.7 {
693 execsql { ROLLBACK TO two }
694 execsql { SELECT * FROM t2 }
695 } {}
696 do_test savepoint-10.2.8 {
697 execsql { PRAGMA lock_status }
698 } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
699 do_test savepoint-10.2.9 {
700 execsql { SELECT 'a', * FROM t1 ; SELECT 'b', * FROM t3 }
701 } {a 1 2 b 3 4}
702 do_test savepoint-10.2.9 {
703 execsql {
704 INSERT INTO t2 VALUES(5, 6);
705 RELEASE one;
706 }
707 execsql {
708 SELECT * FROM t1;
709 SELECT * FROM t2;
710 SELECT * FROM t3;
711 }
712 } {1 2 5 6 3 4}
713 do_test savepoint-10.2.9 {
714 execsql { PRAGMA lock_status }
715 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
717 do_test savepoint-10.2.10 {
718 execsql {
719 SAVEPOINT one;
720 INSERT INTO t1 VALUES('a', 'b');
721 SAVEPOINT two;
722 INSERT INTO t2 VALUES('c', 'd');
723 SAVEPOINT three;
724 INSERT INTO t3 VALUES('e', 'f');
725 }
726 execsql {
727 SELECT * FROM t1;
728 SELECT * FROM t2;
729 SELECT * FROM t3;
730 }
731 } {1 2 a b 5 6 c d 3 4 e f}
732 do_test savepoint-10.2.11 {
733 execsql { ROLLBACK TO two }
734 execsql {
735 SELECT * FROM t1;
736 SELECT * FROM t2;
737 SELECT * FROM t3;
738 }
739 } {1 2 a b 5 6 3 4}
740 do_test savepoint-10.2.12 {
741 execsql {
742 INSERT INTO t3 VALUES('g', 'h');
743 ROLLBACK TO two;
744 }
745 execsql {
746 SELECT * FROM t1;
747 SELECT * FROM t2;
748 SELECT * FROM t3;
749 }
750 } {1 2 a b 5 6 3 4}
751 do_test savepoint-10.2.13 {
752 execsql { ROLLBACK }
753 execsql {
754 SELECT * FROM t1;
755 SELECT * FROM t2;
756 SELECT * FROM t3;
757 }
758 } {1 2 5 6 3 4}
759 do_test savepoint-10.2.14 {
760 execsql { PRAGMA lock_status }
761 } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
762 }
764 #-------------------------------------------------------------------------
765 # The following tests - savepoint-11.* - test the interaction of
766 # savepoints and creating or dropping tables and indexes in
767 # auto-vacuum mode.
768 #
769 do_test savepoint-11.1 {
770 db close
771 forcedelete test.db
772 sqlite3 db test.db
773 execsql { PRAGMA auto_vacuum = full; }
774 wal_set_journal_mode
775 execsql {
776 CREATE TABLE t1(a, b, UNIQUE(a, b));
777 INSERT INTO t1 VALUES(1, randstr(1000,1000));
778 INSERT INTO t1 VALUES(2, randstr(1000,1000));
779 }
780 } {}
781 do_test savepoint-11.2 {
782 execsql {
783 SAVEPOINT one;
784 CREATE TABLE t2(a, b, UNIQUE(a, b));
785 SAVEPOINT two;
786 CREATE TABLE t3(a, b, UNIQUE(a, b));
787 }
788 } {}
789 integrity_check savepoint-11.3
790 do_test savepoint-11.4 {
791 execsql { ROLLBACK TO two }
792 } {}
793 integrity_check savepoint-11.5
794 do_test savepoint-11.6 {
795 execsql {
796 CREATE TABLE t3(a, b, UNIQUE(a, b));
797 ROLLBACK TO one;
798 }
799 } {}
800 integrity_check savepoint-11.7
801 do_test savepoint-11.8 {
802 execsql { ROLLBACK }
803 execsql { PRAGMA wal_checkpoint }
804 file size test.db
805 } {8192}
807 do_test savepoint-11.9 {
808 execsql {
809 DROP TABLE IF EXISTS t1;
810 DROP TABLE IF EXISTS t2;
811 DROP TABLE IF EXISTS t3;
812 }
813 } {}
814 do_test savepoint-11.10 {
815 execsql {
816 BEGIN;
817 CREATE TABLE t1(a, b);
818 CREATE TABLE t2(x, y);
819 INSERT INTO t2 VALUES(1, 2);
820 SAVEPOINT one;
821 INSERT INTO t2 VALUES(3, 4);
822 SAVEPOINT two;
823 DROP TABLE t1;
824 ROLLBACK TO two;
825 }
826 execsql {SELECT * FROM t2}
827 } {1 2 3 4}
828 do_test savepoint-11.11 {
829 execsql COMMIT
830 } {}
831 do_test savepoint-11.12 {
832 execsql {SELECT * FROM t2}
833 } {1 2 3 4}
834 wal_check_journal_mode savepoint-11.13
836 #-------------------------------------------------------------------------
837 # The following tests - savepoint-12.* - test the interaction of
838 # savepoints and "ON CONFLICT ROLLBACK" clauses.
839 #
840 do_test savepoint-12.1 {
841 execsql {
842 CREATE TABLE t4(a PRIMARY KEY, b);
843 INSERT INTO t4 VALUES(1, 'one');
844 }
845 } {}
846 do_test savepoint-12.2 {
847 # The final statement of the following SQL hits a constraint when the
848 # conflict handling mode is "OR ROLLBACK" and there are a couple of
849 # open savepoints. At one point this would fail to clear the internal
850 # record of the open savepoints, resulting in an assert() failure
851 # later on.
852 #
853 catchsql {
854 BEGIN;
855 INSERT INTO t4 VALUES(2, 'two');
856 SAVEPOINT sp1;
857 INSERT INTO t4 VALUES(3, 'three');
858 SAVEPOINT sp2;
859 INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
860 }
861 } {1 {UNIQUE constraint failed: t4.a}}
862 do_test savepoint-12.3 {
863 sqlite3_get_autocommit db
864 } {1}
865 do_test savepoint-12.4 {
866 execsql { SAVEPOINT one }
867 } {}
868 wal_check_journal_mode savepoint-12.5
870 #-------------------------------------------------------------------------
871 # The following tests - savepoint-13.* - test the interaction of
872 # savepoints and "journal_mode = off".
873 #
874 if {[wal_is_wal_mode]==0} {
875 do_test savepoint-13.1 {
876 db close
877 catch {forcedelete test.db}
878 sqlite3 db test.db
879 execsql {
880 BEGIN;
881 CREATE TABLE t1(a PRIMARY KEY, b);
882 INSERT INTO t1 VALUES(1, 2);
883 COMMIT;
884 PRAGMA journal_mode = off;
885 }
886 } {off}
887 do_test savepoint-13.2 {
888 execsql {
889 BEGIN;
890 INSERT INTO t1 VALUES(3, 4);
891 INSERT INTO t1 SELECT a+4,b+4 FROM t1;
892 COMMIT;
893 }
894 } {}
895 do_test savepoint-13.3 {
896 execsql {
897 BEGIN;
898 INSERT INTO t1 VALUES(9, 10);
899 SAVEPOINT s1;
900 INSERT INTO t1 VALUES(11, 12);
901 COMMIT;
902 }
903 } {}
904 do_test savepoint-13.4 {
905 execsql {
906 BEGIN;
907 INSERT INTO t1 VALUES(13, 14);
908 SAVEPOINT s1;
909 INSERT INTO t1 VALUES(15, 16);
910 ROLLBACK TO s1;
911 ROLLBACK;
912 SELECT * FROM t1;
913 }
914 } {1 2 3 4 5 6 7 8 9 10 11 12}
915 }
917 db close
918 delete_file test.db
919 do_multiclient_test tn {
920 do_test savepoint-14.$tn.1 {
921 sql1 {
922 CREATE TABLE foo(x);
923 INSERT INTO foo VALUES(1);
924 INSERT INTO foo VALUES(2);
925 }
926 sql2 {
927 BEGIN;
928 SELECT * FROM foo;
929 }
930 } {1 2}
931 do_test savepoint-14.$tn.2 {
932 sql1 {
933 SAVEPOINT one;
934 INSERT INTO foo VALUES(1);
935 }
936 csql1 { RELEASE one }
937 } {1 {database is locked}}
938 do_test savepoint-14.$tn.3 {
939 sql1 { ROLLBACK TO one }
940 sql2 { COMMIT }
941 sql1 { RELEASE one }
942 } {}
944 do_test savepoint-14.$tn.4 {
945 sql2 {
946 BEGIN;
947 SELECT * FROM foo;
948 }
949 } {1 2}
950 do_test savepoint-14.$tn.5 {
951 sql1 {
952 SAVEPOINT one;
953 INSERT INTO foo VALUES(1);
954 }
955 csql1 { RELEASE one }
956 } {1 {database is locked}}
957 do_test savepoint-14.$tn.6 {
958 sql2 { COMMIT }
959 sql1 {
960 ROLLBACK TO one;
961 INSERT INTO foo VALUES(3);
962 INSERT INTO foo VALUES(4);
963 INSERT INTO foo VALUES(5);
964 RELEASE one;
965 }
966 } {}
967 do_test savepoint-14.$tn.7 {
968 sql2 { CREATE INDEX fooidx ON foo(x); }
969 sql3 { PRAGMA integrity_check }
970 } {ok}
971 }
973 do_multiclient_test tn {
974 do_test savepoint-15.$tn.1 {
975 sql1 {
976 CREATE TABLE foo(x);
977 INSERT INTO foo VALUES(1);
978 INSERT INTO foo VALUES(2);
979 }
980 sql2 { BEGIN; SELECT * FROM foo; }
981 } {1 2}
982 do_test savepoint-15.$tn.2 {
983 sql1 {
984 PRAGMA locking_mode = EXCLUSIVE;
985 BEGIN;
986 INSERT INTO foo VALUES(3);
987 }
988 csql1 { COMMIT }
989 } {1 {database is locked}}
990 do_test savepoint-15.$tn.3 {
991 sql1 { ROLLBACK }
992 sql2 { COMMIT }
993 sql1 {
994 INSERT INTO foo VALUES(3);
995 PRAGMA locking_mode = NORMAL;
996 INSERT INTO foo VALUES(4);
997 }
998 sql2 { CREATE INDEX fooidx ON foo(x); }
999 sql3 { PRAGMA integrity_check }
1000 } {ok}
1001 }
1003 do_multiclient_test tn {
1004 do_test savepoint-16.$tn.1 {
1005 sql1 {
1006 CREATE TABLE foo(x);
1007 INSERT INTO foo VALUES(1);
1008 INSERT INTO foo VALUES(2);
1009 }
1010 } {}
1011 do_test savepoint-16.$tn.2 {
1013 db eval {SELECT * FROM foo} {
1014 sql1 { INSERT INTO foo VALUES(3) }
1015 sql2 { SELECT * FROM foo }
1016 sql1 { INSERT INTO foo VALUES(4) }
1017 break
1018 }
1020 sql2 { CREATE INDEX fooidx ON foo(x); }
1021 sql3 { PRAGMA integrity_check }
1022 } {ok}
1023 do_test savepoint-16.$tn.3 {
1024 sql1 { SELECT * FROM foo }
1025 } {1 2 3 4}
1026 }
1028 #-------------------------------------------------------------------------
1029 # This next block of tests verifies that a problem reported on the mailing
1030 # list has been resolved. At one point the second "CREATE TABLE t6" would
1031 # fail as table t6 still existed in the internal cache of the db schema
1032 # (even though it had been removed from the database by the ROLLBACK
1033 # command).
1034 #
1035 sqlite3 db test.db
1036 do_execsql_test savepoint-17.1 {
1037 BEGIN;
1038 CREATE TABLE t6(a, b);
1039 INSERT INTO t6 VALUES(1, 2);
1040 SAVEPOINT one;
1041 INSERT INTO t6 VALUES(3, 4);
1042 ROLLBACK TO one;
1043 SELECT * FROM t6;
1044 ROLLBACK;
1045 } {1 2}
1047 do_execsql_test savepoint-17.2 {
1048 CREATE TABLE t6(a, b);
1049 } {}
1051 finish_test