wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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