xref: /sqlite-3.40.0/test/savepoint.test (revision fc158bf9) 
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.9 2009/01/07 08:12:16 danielk1977 Exp $
13
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16
17
18#----------------------------------------------------------------------
19# The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE
20# and ROLLBACK TO comands are correctly parsed, and that the auto-commit
21# flag is correctly set and unset as a result.
22#
23do_test savepoint-1.1 {
24  execsql {
25    SAVEPOINT sp1;
26    RELEASE sp1;
27  }
28} {}
29do_test savepoint-1.2 {
30  execsql {
31    SAVEPOINT sp1;
32    ROLLBACK TO sp1;
33  }
34} {}
35do_test savepoint-1.3 {
36  execsql { SAVEPOINT sp1 }
37  db close
38} {}
39sqlite3 db test.db
40do_test savepoint-1.4.1 {
41  execsql {
42    SAVEPOINT sp1;
43    SAVEPOINT sp2;
44    RELEASE sp1;
45  }
46  sqlite3_get_autocommit db
47} {1}
48do_test savepoint-1.4.2 {
49  execsql {
50    SAVEPOINT sp1;
51    SAVEPOINT sp2;
52    RELEASE sp2;
53  }
54  sqlite3_get_autocommit db
55} {0}
56do_test savepoint-1.4.3 {
57  execsql { RELEASE sp1 }
58  sqlite3_get_autocommit db
59} {1}
60do_test savepoint-1.4.4 {
61  execsql {
62    SAVEPOINT sp1;
63    SAVEPOINT sp2;
64    ROLLBACK TO sp1;
65  }
66  sqlite3_get_autocommit db
67} {0}
68do_test savepoint-1.4.5 {
69  execsql { RELEASE SAVEPOINT sp1 }
70  sqlite3_get_autocommit db
71} {1}
72do_test savepoint-1.4.6 {
73  execsql {
74    SAVEPOINT sp1;
75    SAVEPOINT sp2;
76    SAVEPOINT sp3;
77    ROLLBACK TO SAVEPOINT sp3;
78    ROLLBACK TRANSACTION TO sp2;
79    ROLLBACK TRANSACTION TO SAVEPOINT sp1;
80  }
81  sqlite3_get_autocommit db
82} {0}
83do_test savepoint-1.4.7 {
84  execsql { RELEASE SAVEPOINT SP1 }
85  sqlite3_get_autocommit db
86} {1}
87do_test savepoint-1.5 {
88  execsql {
89    SAVEPOINT sp1;
90    ROLLBACK TO sp1;
91  }
92} {}
93do_test savepoint-1.6 {
94  execsql COMMIT
95} {}
96
97#------------------------------------------------------------------------
98# These tests - savepoint-2.* - test rollbacks and releases of savepoints
99# with a very simple data set.
100#
101
102do_test savepoint-2.1 {
103  execsql {
104    CREATE TABLE t1(a, b, c);
105    BEGIN;
106    INSERT INTO t1 VALUES(1, 2, 3);
107    SAVEPOINT one;
108    UPDATE t1 SET a = 2, b = 3, c = 4;
109  }
110  execsql { SELECT * FROM t1 }
111} {2 3 4}
112do_test savepoint-2.2 {
113  execsql {
114    ROLLBACK TO one;
115  }
116  execsql { SELECT * FROM t1 }
117} {1 2 3}
118do_test savepoint-2.3 {
119  execsql {
120    INSERT INTO t1 VALUES(4, 5, 6);
121  }
122  execsql { SELECT * FROM t1 }
123} {1 2 3 4 5 6}
124do_test savepoint-2.4 {
125  execsql {
126    ROLLBACK TO one;
127  }
128  execsql { SELECT * FROM t1 }
129} {1 2 3}
130
131
132do_test savepoint-2.5 {
133  execsql {
134    INSERT INTO t1 VALUES(7, 8, 9);
135    SAVEPOINT two;
136    INSERT INTO t1 VALUES(10, 11, 12);
137  }
138  execsql { SELECT * FROM t1 }
139} {1 2 3 7 8 9 10 11 12}
140do_test savepoint-2.6 {
141  execsql {
142    ROLLBACK TO two;
143  }
144  execsql { SELECT * FROM t1 }
145} {1 2 3 7 8 9}
146do_test savepoint-2.7 {
147  execsql {
148    INSERT INTO t1 VALUES(10, 11, 12);
149  }
150  execsql { SELECT * FROM t1 }
151} {1 2 3 7 8 9 10 11 12}
152do_test savepoint-2.8 {
153  execsql {
154    ROLLBACK TO one;
155  }
156  execsql { SELECT * FROM t1 }
157} {1 2 3}
158do_test savepoint-2.9 {
159  execsql {
160    INSERT INTO t1 VALUES('a', 'b', 'c');
161    SAVEPOINT two;
162    INSERT INTO t1 VALUES('d', 'e', 'f');
163  }
164  execsql { SELECT * FROM t1 }
165} {1 2 3 a b c d e f}
166do_test savepoint-2.10 {
167  execsql {
168    RELEASE two;
169  }
170  execsql { SELECT * FROM t1 }
171} {1 2 3 a b c d e f}
172do_test savepoint-2.11 {
173  execsql {
174    ROLLBACK;
175  }
176  execsql { SELECT * FROM t1 }
177} {}
178
179#------------------------------------------------------------------------
180# This block of tests - savepoint-3.* - test that when a transaction
181# savepoint is rolled back, locks are not released from database files.
182# And that when a transaction savepoint is released, they are released.
183#
184do_test savepoint-3.1 {
185  execsql { SAVEPOINT "transaction" }
186  execsql { PRAGMA lock_status }
187} {main unlocked temp closed}
188
189do_test savepoint-3.2 {
190  execsql { INSERT INTO t1 VALUES(1, 2, 3) }
191  execsql { PRAGMA lock_status }
192} {main reserved temp closed}
193
194do_test savepoint-3.3 {
195  execsql { ROLLBACK TO "transaction" }
196  execsql { PRAGMA lock_status }
197} {main reserved temp closed}
198
199do_test savepoint-3.4 {
200  execsql { INSERT INTO t1 VALUES(1, 2, 3) }
201  execsql { PRAGMA lock_status }
202} {main reserved temp closed}
203
204do_test savepoint-3.5 {
205  execsql { RELEASE "transaction" }
206  execsql { PRAGMA lock_status }
207} {main unlocked temp closed}
208
209#------------------------------------------------------------------------
210# Test that savepoints that include schema modifications are handled
211# correctly. Test cases savepoint-4.*.
212#
213do_test savepoint-4.1 {
214  execsql {
215    CREATE TABLE t2(d, e, f);
216    SELECT sql FROM sqlite_master;
217  }
218} {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
219do_test savepoint-4.2 {
220  execsql {
221    BEGIN;
222    CREATE TABLE t3(g,h);
223    INSERT INTO t3 VALUES('I', 'II');
224    SAVEPOINT one;
225    DROP TABLE t3;
226  }
227} {}
228do_test savepoint-4.3 {
229  execsql {
230    CREATE TABLE t3(g, h, i);
231    INSERT INTO t3 VALUES('III', 'IV', 'V');
232  }
233  execsql {SELECT * FROM t3}
234} {III IV V}
235do_test savepoint-4.4 {
236  execsql { ROLLBACK TO one; }
237  execsql {SELECT * FROM t3}
238} {I II}
239do_test savepoint-4.5 {
240  execsql {
241    ROLLBACK;
242    SELECT sql FROM sqlite_master;
243  }
244} {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
245
246do_test savepoint-4.6 {
247  execsql {
248    BEGIN;
249    INSERT INTO t1 VALUES('o', 't', 't');
250    SAVEPOINT sp1;
251    CREATE TABLE t3(a, b, c);
252    INSERT INTO t3 VALUES('z', 'y', 'x');
253  }
254  execsql {SELECT * FROM t3}
255} {z y x}
256do_test savepoint-4.7 {
257  execsql {
258    ROLLBACK TO sp1;
259    CREATE TABLE t3(a);
260    INSERT INTO t3 VALUES('value');
261  }
262  execsql {SELECT * FROM t3}
263} {value}
264do_test savepoint-4.8 {
265  execsql COMMIT
266} {}
267
268#------------------------------------------------------------------------
269# Test some logic errors to do with the savepoint feature.
270#
271
272do_test savepoint-5.1.1 {
273  execsql {
274    CREATE TABLE blobs(x);
275    INSERT INTO blobs VALUES('a twentyeight character blob');
276  }
277  set fd [db incrblob blobs x 1]
278  puts -nonewline $fd "hello"
279  catchsql {SAVEPOINT abc}
280} {1 {cannot open savepoint - SQL statements in progress}}
281do_test savepoint-5.1.2 {
282  close $fd
283  catchsql {SAVEPOINT abc}
284} {0 {}}
285
286do_test savepoint-5.2 {
287  execsql  {RELEASE abc}
288  catchsql {RELEASE abc}
289} {1 {no such savepoint: abc}}
290
291do_test savepoint-5.3.1 {
292  execsql  {SAVEPOINT abc}
293  catchsql {ROLLBACK TO def}
294} {1 {no such savepoint: def}}
295do_test savepoint-5.3.2 {
296  execsql  {SAVEPOINT def}
297  set fd [db incrblob -readonly blobs x 1]
298  catchsql {ROLLBACK TO def}
299} {1 {cannot rollback savepoint - SQL statements in progress}}
300do_test savepoint-5.3.3 {
301  catchsql  {RELEASE def}
302} {0 {}}
303do_test savepoint-5.3.4 {
304  close $fd
305  execsql  {savepoint def}
306  set fd [db incrblob blobs x 1]
307  catchsql {release def}
308} {1 {cannot release savepoint - SQL statements in progress}}
309do_test savepoint-5.3.5 {
310  close $fd
311  execsql {release abc}
312} {}
313
314do_test savepoint-5.4.1 {
315  execsql {
316    SAVEPOINT main;
317    INSERT INTO blobs VALUES('another blob');
318  }
319} {}
320do_test savepoint-5.4.2 {
321  sqlite3 db2 test.db
322  execsql { BEGIN ; SELECT * FROM blobs } db2
323  catchsql { RELEASE main }
324} {1 {database is locked}}
325do_test savepoint-5.4.3 {
326  db2 close
327  catchsql { RELEASE main }
328} {0 {}}
329do_test savepoint-5.4.4 {
330  execsql { SELECT x FROM blobs WHERE rowid = 2 }
331} {{another blob}}
332
333#-------------------------------------------------------------------------
334# The following tests, savepoint-6.*, test an incr-vacuum inside of a
335# couple of nested savepoints.
336#
337ifcapable {autovacuum && pragma} {
338  db close
339  file delete -force test.db
340  sqlite3 db test.db
341
342  do_test savepoint-6.1 {
343    execsql {
344      PRAGMA auto_vacuum = incremental;
345      CREATE TABLE t1(a, b, c);
346      CREATE INDEX i1 ON t1(a, b);
347      BEGIN;
348      INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400));
349    }
350    set r "randstr(10,400)"
351    for {set ii 0} {$ii < 10} {incr ii} {
352      execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1"
353    }
354    execsql { COMMIT }
355  } {}
356
357  integrity_check savepoint-6.2
358
359  do_test savepoint-6.3 {
360    execsql {
361      PRAGMA cache_size = 10;
362      BEGIN;
363        UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0;
364        SAVEPOINT one;
365          DELETE FROM t1 WHERE rowid%2;
366          PRAGMA incr_vacuum;
367          SAVEPOINT two;
368            INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1;
369            DELETE FROM t1 WHERE rowid%2;
370            PRAGMA incr_vacuum;
371        ROLLBACK TO one;
372      COMMIT;
373    }
374  } {}
375
376  integrity_check savepoint-6.4
377}
378
379#-------------------------------------------------------------------------
380# The following tests, savepoint-7.*, attempt to break the logic
381# surrounding savepoints by growing and shrinking the database file.
382#
383db close
384file delete -force test.db
385sqlite3 db test.db
386
387do_test savepoint-7.1 {
388  execsql {
389    PRAGMA auto_vacuum = incremental;
390    PRAGMA cache_size = 10;
391    BEGIN;
392    CREATE TABLE t1(a PRIMARY KEY, b);
393      INSERT INTO t1(a) VALUES('alligator');
394      INSERT INTO t1(a) VALUES('angelfish');
395      INSERT INTO t1(a) VALUES('ant');
396      INSERT INTO t1(a) VALUES('antelope');
397      INSERT INTO t1(a) VALUES('ape');
398      INSERT INTO t1(a) VALUES('baboon');
399      INSERT INTO t1(a) VALUES('badger');
400      INSERT INTO t1(a) VALUES('bear');
401      INSERT INTO t1(a) VALUES('beetle');
402      INSERT INTO t1(a) VALUES('bird');
403      INSERT INTO t1(a) VALUES('bison');
404      UPDATE t1 SET b =    randstr(1000,1000);
405      UPDATE t1 SET b = b||randstr(1000,1000);
406      UPDATE t1 SET b = b||randstr(1000,1000);
407      UPDATE t1 SET b = b||randstr(10,1000);
408    COMMIT;
409  }
410  expr ([execsql { PRAGMA page_count }] > 20)
411} {1}
412do_test savepoint-7.2.1 {
413  execsql {
414    BEGIN;
415      SAVEPOINT one;
416      CREATE TABLE t2(a, b);
417      INSERT INTO t2 SELECT a, b FROM t1;
418      ROLLBACK TO one;
419  }
420  execsql {
421    PRAGMA integrity_check;
422  }
423} {ok}
424do_test savepoint-7.2.2 {
425  execsql {
426    COMMIT;
427    PRAGMA integrity_check;
428  }
429} {ok}
430
431do_test savepoint-7.3.1 {
432  execsql {
433    CREATE TABLE t2(a, b);
434    INSERT INTO t2 SELECT a, b FROM t1;
435  }
436} {}
437do_test savepoint-7.3.2 {
438  execsql {
439    BEGIN;
440      SAVEPOINT one;
441        DELETE FROM t2;
442        PRAGMA incremental_vacuum;
443        SAVEPOINT two;
444          INSERT INTO t2 SELECT a, b FROM t1;
445        ROLLBACK TO two;
446    COMMIT;
447  }
448  execsql { PRAGMA integrity_check }
449} {ok}
450
451do_test savepoint-7.4.1 {
452  db close
453  file delete -force test.db
454  sqlite3 db test.db
455  execsql {
456    PRAGMA auto_vacuum = incremental;
457    CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
458    INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000));
459    BEGIN;
460      DELETE FROM t1;
461      SAVEPOINT one;
462      PRAGMA incremental_vacuum;
463      ROLLBACK TO one;
464    COMMIT;
465  }
466
467  execsql { PRAGMA integrity_check }
468} {ok}
469
470do_test savepoint-7.5.1 {
471  execsql {
472    PRAGMA incremental_vacuum;
473    CREATE TABLE t5(x, y);
474    INSERT INTO t5 VALUES(1, randstr(1000,1000));
475    INSERT INTO t5 VALUES(2, randstr(1000,1000));
476    INSERT INTO t5 VALUES(3, randstr(1000,1000));
477
478    BEGIN;
479      INSERT INTO t5 VALUES(4, randstr(1000,1000));
480      INSERT INTO t5 VALUES(5, randstr(1000,1000));
481      DELETE FROM t5 WHERE x=1 OR x=2;
482      SAVEPOINT one;
483        PRAGMA incremental_vacuum;
484        SAVEPOINT two;
485          INSERT INTO t5 VALUES(1, randstr(1000,1000));
486          INSERT INTO t5 VALUES(2, randstr(1000,1000));
487        ROLLBACK TO two;
488      ROLLBACK TO one;
489    COMMIT;
490    PRAGMA integrity_check;
491  }
492} {ok}
493do_test savepoint-7.5.2 {
494  execsql {
495    DROP TABLE t5;
496  }
497} {}
498
499# Test oddly named and quoted savepoints.
500#
501do_test savepoint-8-1 {
502  execsql { SAVEPOINT "save1" }
503  execsql { RELEASE save1 }
504} {}
505do_test savepoint-8-2 {
506  execsql { SAVEPOINT "Including whitespace " }
507  execsql { RELEASE "including Whitespace " }
508} {}
509
510# Test that the authorization callback works.
511#
512ifcapable auth {
513  proc auth {args} {
514    eval lappend ::authdata $args
515    return SQLITE_OK
516  }
517  db auth auth
518
519  do_test savepoint-9.1 {
520    set ::authdata [list]
521    execsql { SAVEPOINT sp1 }
522    set ::authdata
523  } {SQLITE_SAVEPOINT BEGIN sp1 {} {}}
524  do_test savepoint-9.2 {
525    set ::authdata [list]
526    execsql { ROLLBACK TO sp1 }
527    set ::authdata
528  } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}}
529  do_test savepoint-9.3 {
530    set ::authdata [list]
531    execsql { RELEASE sp1 }
532    set ::authdata
533  } {SQLITE_SAVEPOINT RELEASE sp1 {} {}}
534
535  proc auth {args} {
536    eval lappend ::authdata $args
537    return SQLITE_DENY
538  }
539  db auth auth
540
541  do_test savepoint-9.4 {
542    set ::authdata [list]
543    set res [catchsql { SAVEPOINT sp1 }]
544    concat $::authdata $res
545  } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}}
546  do_test savepoint-9.5 {
547    set ::authdata [list]
548    set res [catchsql { ROLLBACK TO sp1 }]
549    concat $::authdata $res
550  } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}}
551  do_test savepoint-9.6 {
552    set ::authdata [list]
553    set res [catchsql { RELEASE sp1 }]
554    concat $::authdata $res
555  } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}}
556
557  catch { db eval ROLLBACK }
558  db auth ""
559}
560
561#-------------------------------------------------------------------------
562# The following tests - savepoint-10.* - test the interaction of
563# savepoints and ATTACH statements.
564#
565
566# First make sure it is not possible to attach or detach a database while
567# a savepoint is open (it is not possible if any transaction is open).
568#
569do_test savepoint-10.1.1 {
570  catchsql {
571    SAVEPOINT one;
572    ATTACH 'test2.db' AS aux;
573  }
574} {1 {cannot ATTACH database within transaction}}
575do_test savepoint-10.1.2 {
576  execsql {
577    RELEASE one;
578    ATTACH 'test2.db' AS aux;
579  }
580  catchsql {
581    SAVEPOINT one;
582    DETACH aux;
583  }
584} {1 {cannot DETACH database within transaction}}
585do_test savepoint-10.1.3 {
586  execsql {
587    RELEASE one;
588    DETACH aux;
589  }
590} {}
591
592do_test savepoint-10.2.1 {
593  file delete -force test3.db
594  file delete -force test2.db
595  execsql {
596    ATTACH 'test2.db' AS aux1;
597    ATTACH 'test3.db' AS aux2;
598    DROP TABLE t1;
599    CREATE TABLE main.t1(x, y);
600    CREATE TABLE aux1.t2(x, y);
601    CREATE TABLE aux2.t3(x, y);
602    SELECT name FROM sqlite_master
603      UNION ALL
604    SELECT name FROM aux1.sqlite_master
605      UNION ALL
606    SELECT name FROM aux2.sqlite_master;
607  }
608} {t1 t2 t3}
609do_test savepoint-10.2.2 {
610  execsql { PRAGMA lock_status }
611} {main unlocked temp unlocked aux1 unlocked aux2 unlocked}
612
613do_test savepoint-10.2.3 {
614  execsql {
615    SAVEPOINT one;
616    INSERT INTO t1 VALUES(1, 2);
617    PRAGMA lock_status;
618  }
619} {main reserved temp unlocked aux1 unlocked aux2 unlocked}
620do_test savepoint-10.2.4 {
621  execsql {
622    INSERT INTO t3 VALUES(3, 4);
623    PRAGMA lock_status;
624  }
625} {main reserved temp unlocked aux1 unlocked aux2 reserved}
626do_test savepoint-10.2.5 {
627  execsql {
628    SAVEPOINT two;
629    INSERT INTO t2 VALUES(5, 6);
630    PRAGMA lock_status;
631  }
632} {main reserved temp unlocked aux1 reserved aux2 reserved}
633do_test savepoint-10.2.6 {
634  execsql { SELECT * FROM t2 }
635} {5 6}
636do_test savepoint-10.2.7 {
637  execsql { ROLLBACK TO two }
638  execsql { SELECT * FROM t2 }
639} {}
640do_test savepoint-10.2.8 {
641  execsql { PRAGMA lock_status }
642} {main reserved temp unlocked aux1 reserved aux2 reserved}
643do_test savepoint-10.2.9 {
644  execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 }
645} {a 1 2 b 3 4}
646do_test savepoint-10.2.9 {
647  execsql {
648    INSERT INTO t2 VALUES(5, 6);
649    RELEASE one;
650  }
651  execsql {
652    SELECT * FROM t1;
653    SELECT * FROM t2;
654    SELECT * FROM t3;
655  }
656} {1 2 5 6 3 4}
657do_test savepoint-10.2.9 {
658  execsql { PRAGMA lock_status }
659} {main unlocked temp unlocked aux1 unlocked aux2 unlocked}
660
661do_test savepoint-10.2.10 {
662  execsql {
663    SAVEPOINT one;
664      INSERT INTO t1 VALUES('a', 'b');
665      SAVEPOINT two;
666        INSERT INTO t2 VALUES('c', 'd');
667        SAVEPOINT three;
668          INSERT INTO t3 VALUES('e', 'f');
669  }
670  execsql {
671    SELECT * FROM t1;
672    SELECT * FROM t2;
673    SELECT * FROM t3;
674  }
675} {1 2 a b 5 6 c d 3 4 e f}
676do_test savepoint-10.2.11 {
677  execsql { ROLLBACK TO two }
678  execsql {
679    SELECT * FROM t1;
680    SELECT * FROM t2;
681    SELECT * FROM t3;
682  }
683} {1 2 a b 5 6 3 4}
684do_test savepoint-10.2.12 {
685  execsql {
686    INSERT INTO t3 VALUES('g', 'h');
687    ROLLBACK TO two;
688  }
689  execsql {
690    SELECT * FROM t1;
691    SELECT * FROM t2;
692    SELECT * FROM t3;
693  }
694} {1 2 a b 5 6 3 4}
695do_test savepoint-10.2.13 {
696  execsql { ROLLBACK }
697  execsql {
698    SELECT * FROM t1;
699    SELECT * FROM t2;
700    SELECT * FROM t3;
701  }
702} {1 2 5 6 3 4}
703do_test savepoint-10.2.14 {
704  execsql { PRAGMA lock_status }
705} {main unlocked temp unlocked aux1 unlocked aux2 unlocked}
706
707#-------------------------------------------------------------------------
708# The following tests - savepoint-11.* - test the interaction of
709# savepoints and creating or dropping tables and indexes in
710# auto-vacuum mode.
711#
712do_test savepoint-11.1 {
713  db close
714  file delete -force test.db
715  sqlite3 db test.db
716  execsql {
717    PRAGMA auto_vacuum = full;
718    CREATE TABLE t1(a, b, UNIQUE(a, b));
719    INSERT INTO t1 VALUES(1, randstr(1000,1000));
720    INSERT INTO t1 VALUES(2, randstr(1000,1000));
721  }
722} {}
723do_test savepoint-11.2 {
724  execsql {
725    SAVEPOINT one;
726      CREATE TABLE t2(a, b, UNIQUE(a, b));
727      SAVEPOINT two;
728        CREATE TABLE t3(a, b, UNIQUE(a, b));
729  }
730} {}
731integrity_check savepoint-11.3
732do_test savepoint-11.4 {
733  execsql { ROLLBACK TO two }
734} {}
735integrity_check savepoint-11.5
736do_test savepoint-11.6 {
737  execsql {
738    CREATE TABLE t3(a, b, UNIQUE(a, b));
739    ROLLBACK TO one;
740  }
741} {}
742integrity_check savepoint-11.7
743do_test savepoint-11.6 {
744  execsql { ROLLBACK }
745  file size test.db
746} {8192}
747
748#-------------------------------------------------------------------------
749# The following tests - savepoint-12.* - test the interaction of
750# savepoints and "ON CONFLICT ROLLBACK" clauses.
751#
752do_test savepoint-12.1 {
753  execsql {
754    CREATE TABLE t4(a PRIMARY KEY, b);
755    INSERT INTO t4 VALUES(1, 'one');
756  }
757} {}
758do_test savepoint-12.2 {
759  # The final statement of the following SQL hits a constraint when the
760  # conflict handling mode is "OR ROLLBACK" and there are a couple of
761  # open savepoints. At one point this would fail to clear the internal
762  # record of the open savepoints, resulting in an assert() failure
763  # later on.
764  #
765  catchsql {
766    BEGIN;
767      INSERT INTO t4 VALUES(2, 'two');
768      SAVEPOINT sp1;
769        INSERT INTO t4 VALUES(3, 'three');
770        SAVEPOINT sp2;
771          INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
772  }
773} {1 {column a is not unique}}
774do_test savepoint-12.3 {
775  sqlite3_get_autocommit db
776} {1}
777do_test savepoint-12.4 {
778  execsql { SAVEPOINT one }
779} {}
780
781finish_test
782
783