| 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 |