xref: /sqlite-3.40.0/test/savepoint.test (revision 78d41832) 
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.1 2008/12/17 17:30:26 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
268finish_test
269
270