Annotation of embedaddon/sqlite3/test/tkt3718.test, revision 1.1.1.1
1.1 misho 1: # 2001 September 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: # This file implements regression tests for SQLite library. The
12: # focus of this file is testing the execution of SQL statements from
13: # within callbacks generated by VMs that themselves open statement
14: # transactions.
15: #
16: # $Id: tkt3718.test,v 1.2 2009/06/05 17:09:12 drh Exp $
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: do_test tkt3718-1.1 {
22: execsql {
23: CREATE TABLE t1(a PRIMARY KEY, b);
24: INSERT INTO t1 VALUES(1, 'one');
25: INSERT INTO t1 VALUES(2, 'two');
26: INSERT INTO t1 VALUES(3, 'three');
27: INSERT INTO t1 VALUES(4, 'four');
28: INSERT INTO t1 VALUES(5, 'five');
29: CREATE TABLE t2(a PRIMARY KEY, b);
30: }
31: } {}
32:
33: # SQL scalar function:
34: #
35: # f1(<arg>)
36: #
37: # Uses database handle [db] to execute "SELECT f2(<arg>)". Returns either
38: # the results or error message from the "SELECT f2(<arg>)" query to the
39: # caller.
40: #
41: proc f1 {args} {
42: set a [lindex $args 0]
43: catch { db eval {SELECT f2($a)} } msg
44: set msg
45: }
46:
47: # SQL scalar function:
48: #
49: # f2(<arg>)
50: #
51: # Return the value of <arg>. Unless <arg> is "three", in which case throw
52: # an exception.
53: #
54: proc f2 {args} {
55: set a [lindex $args 0]
56: if {$a == "three"} { error "Three!!" }
57: return $a
58: }
59:
60: db func f1 f1
61: db func f2 f2
62:
63: # The second INSERT statement below uses the f1 user function such that
64: # half-way through the INSERT operation f1() will run an SQL statement
65: # that throws an exception. At one point, before #3718 was fixed, this
66: # caused the statement transaction belonging to the INSERT statement to
67: # be rolled back. The result was that some (but not all) of the rows that
68: # should have been inserted went missing.
69: #
70: do_test tkt3718-1.2 {
71: execsql {
72: BEGIN;
73: INSERT INTO t2 SELECT a, b FROM t1;
74: INSERT INTO t2 SELECT a+5, f1(b) FROM t1;
75: COMMIT;
76: }
77: execsql {
78: SELECT a FROM t2;
79: }
80: } {1 2 3 4 5 6 7 8 9 10}
81:
82: # This test turns on the count_changes pragma (causing DML statements to
83: # return SQLITE_ROW once, with a single integer result value reporting the
84: # number of rows affected by the statement). It then executes an INSERT
85: # statement that requires a statement journal. After stepping the statement
86: # once, so that it returns SQLITE_ROW, a second SQL statement that throws an
87: # exception is run. At one point, before #3718 was fixed, this caused the
88: # statement transaction belonging to the INSERT statement to be rolled back.
89: # The result was that none of the rows were actually inserted.
90: #
91: #
92: do_test tkt3718-1.3 {
93: execsql {
94: DELETE FROM t2 WHERE a > 5;
95: PRAGMA count_changes = 1;
96: BEGIN;
97: }
98: db eval {INSERT INTO t2 SELECT a+5, b||'+5' FROM t1} {
99: catch { db eval {SELECT f2('three')} } msg
100: }
101: execsql {
102: COMMIT;
103: SELECT a FROM t2;
104: }
105: } {1 2 3 4 5 6 7 8 9 10}
106:
107: do_test tkt3718-1.4 {
108: execsql {pragma count_changes=0}
109: } {}
110:
111: # This SQL function executes the SQL specified as an argument against
112: # database [db].
113: #
114: proc sql {doit zSql} {
115: if {$doit} { catchsql $zSql }
116: }
117: db func sql [list sql]
118:
119: # The following tests, tkt3718-2.*, test that a nested statement
120: # transaction can be successfully committed or reverted without
121: # affecting the parent statement transaction.
122: #
123: do_test tkt3718-2.1 {
124: execsql { SELECT sql(1, 'DELETE FROM t2 WHERE a = '||a ) FROM t2 WHERE a>5 }
125: execsql { SELECT a from t2 }
126: } {1 2 3 4 5}
127: do_test tkt3718-2.2 {
128: execsql {
129: DELETE FROM t2 WHERE a > 5;
130: BEGIN;
131: INSERT INTO t2 SELECT a+5, sql(a==3,
132: 'INSERT INTO t2 SELECT a+10, f2(b) FROM t1'
133: ) FROM t1;
134: }
135: execsql {
136: COMMIT;
137: SELECT a FROM t2;
138: }
139: } {1 2 3 4 5 6 7 8 9 10}
140: do_test tkt3718-2.3 {
141: execsql {
142: DELETE FROM t2 WHERE a > 5;
143: BEGIN;
144: INSERT INTO t2 SELECT a+5, sql(a==3,
145: 'INSERT INTO t2 SELECT a+10, b FROM t1'
146: ) FROM t1;
147: COMMIT;
148: }
149: execsql { SELECT a FROM t2 ORDER BY a+0}
150: } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}
151: integrity_check tkt3718.2-4
152:
153: # The next set of tests, tkt3718-3.*, test that a statement transaction
154: # that has a committed statement transaction nested inside of it can
155: # be committed or reverted.
156: #
157: foreach {tn io ii results} {
158: 1 0 10 {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
159: 2 1 10 {6 7 8 9 10 16 17 18 19 20}
160: 3 0 11 {1 2 3 4 5 6 7 8 9 10 16 17 18 19 20}
161: 4 1 11 {6 7 8 9 10 16 17 18 19 20}
162: } {
163: do_test tkt3718-3.$tn {
164: execsql {
165: DELETE FROM t2;
166: INSERT INTO t2 SELECT a+5, b FROM t1;
167: INSERT INTO t2 SELECT a+15, b FROM t1;
168: }
169:
170: catchsql "
171: BEGIN;
172: INSERT INTO t2 SELECT a+$io, sql(a==3,
173: 'INSERT INTO t2 SELECT a+$ii, b FROM t1'
174: ) FROM t1;
175: "
176:
177: execsql { COMMIT }
178:
179: execsql { SELECT a FROM t2 ORDER BY a+0}
180: } $results
181:
182: integrity_check tkt3718-3.$tn.integrity
183: }
184:
185: # This is the same test as tkt3718-3.*, but with 3 levels of nesting.
186: #
187: foreach {tn i1 i2 i3 results} {
188: 1 0 10 20 {5 10 15 20 25 30}
189: 2 0 10 21 {5 10 15 20 30}
190: 3 0 11 20 {5 10 20 30}
191: 4 0 11 21 {5 10 20 30}
192: 5 1 10 20 {10 20 30}
193: 6 1 10 21 {10 20 30}
194: 7 1 11 20 {10 20 30}
195: 8 1 11 21 {10 20 30}
196: } {
197: do_test tkt3718-4.$tn {
198: execsql {
199: DELETE FROM t2;
200: INSERT INTO t2 SELECT a+5, b FROM t1;
201: INSERT INTO t2 SELECT a+15, b FROM t1;
202: INSERT INTO t2 SELECT a+25, b FROM t1;
203: }
204:
205: catchsql "
206: BEGIN;
207: INSERT INTO t2 SELECT a+$i1, sql(a==3,
208: 'INSERT INTO t2 SELECT a+$i2, sql(a==3,
209: ''INSERT INTO t2 SELECT a+$i3, b FROM t1''
210: ) FROM t1'
211: ) FROM t1;
212: "
213:
214: execsql { COMMIT }
215:
216: execsql { SELECT a FROM t2 WHERE (a%5)==0 ORDER BY a+0}
217: } $results
218:
219: do_test tkt3718-4.$tn.extra {
220: execsql {
221: SELECT
222: (SELECT sum(a) FROM t2)==(SELECT sum(a*5-10) FROM t2 WHERE (a%5)==0)
223: }
224: } {1}
225:
226: integrity_check tkt3718-4.$tn.integrity
227: }
228:
229:
230: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to tkt3718.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test