/trunk/test/tkt3718.test |
@@ -0,0 +1,230 @@ |
# 2001 September 15 |
# |
# The author disclaims copyright to this source code. In place of |
# a legal notice, here is a blessing: |
# |
# May you do good and not evil. |
# May you find forgiveness for yourself and forgive others. |
# May you share freely, never taking more than you give. |
# |
#*********************************************************************** |
# This file implements regression tests for SQLite library. The |
# focus of this file is testing the execution of SQL statements from |
# within callbacks generated by VMs that themselves open statement |
# transactions. |
# |
# $Id: tkt3718.test,v 1.2 2009/06/05 17:09:12 drh Exp $ |
|
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
|
do_test tkt3718-1.1 { |
execsql { |
CREATE TABLE t1(a PRIMARY KEY, b); |
INSERT INTO t1 VALUES(1, 'one'); |
INSERT INTO t1 VALUES(2, 'two'); |
INSERT INTO t1 VALUES(3, 'three'); |
INSERT INTO t1 VALUES(4, 'four'); |
INSERT INTO t1 VALUES(5, 'five'); |
CREATE TABLE t2(a PRIMARY KEY, b); |
} |
} {} |
|
# SQL scalar function: |
# |
# f1(<arg>) |
# |
# Uses database handle [db] to execute "SELECT f2(<arg>)". Returns either |
# the results or error message from the "SELECT f2(<arg>)" query to the |
# caller. |
# |
proc f1 {args} { |
set a [lindex $args 0] |
catch { db eval {SELECT f2($a)} } msg |
set msg |
} |
|
# SQL scalar function: |
# |
# f2(<arg>) |
# |
# Return the value of <arg>. Unless <arg> is "three", in which case throw |
# an exception. |
# |
proc f2 {args} { |
set a [lindex $args 0] |
if {$a == "three"} { error "Three!!" } |
return $a |
} |
|
db func f1 f1 |
db func f2 f2 |
|
# The second INSERT statement below uses the f1 user function such that |
# half-way through the INSERT operation f1() will run an SQL statement |
# that throws an exception. At one point, before #3718 was fixed, this |
# caused the statement transaction belonging to the INSERT statement to |
# be rolled back. The result was that some (but not all) of the rows that |
# should have been inserted went missing. |
# |
do_test tkt3718-1.2 { |
execsql { |
BEGIN; |
INSERT INTO t2 SELECT a, b FROM t1; |
INSERT INTO t2 SELECT a+5, f1(b) FROM t1; |
COMMIT; |
} |
execsql { |
SELECT a FROM t2; |
} |
} {1 2 3 4 5 6 7 8 9 10} |
|
# This test turns on the count_changes pragma (causing DML statements to |
# return SQLITE_ROW once, with a single integer result value reporting the |
# number of rows affected by the statement). It then executes an INSERT |
# statement that requires a statement journal. After stepping the statement |
# once, so that it returns SQLITE_ROW, a second SQL statement that throws an |
# exception is run. At one point, before #3718 was fixed, this caused the |
# statement transaction belonging to the INSERT statement to be rolled back. |
# The result was that none of the rows were actually inserted. |
# |
# |
do_test tkt3718-1.3 { |
execsql { |
DELETE FROM t2 WHERE a > 5; |
PRAGMA count_changes = 1; |
BEGIN; |
} |
db eval {INSERT INTO t2 SELECT a+5, b||'+5' FROM t1} { |
catch { db eval {SELECT f2('three')} } msg |
} |
execsql { |
COMMIT; |
SELECT a FROM t2; |
} |
} {1 2 3 4 5 6 7 8 9 10} |
|
do_test tkt3718-1.4 { |
execsql {pragma count_changes=0} |
} {} |
|
# This SQL function executes the SQL specified as an argument against |
# database [db]. |
# |
proc sql {doit zSql} { |
if {$doit} { catchsql $zSql } |
} |
db func sql [list sql] |
|
# The following tests, tkt3718-2.*, test that a nested statement |
# transaction can be successfully committed or reverted without |
# affecting the parent statement transaction. |
# |
do_test tkt3718-2.1 { |
execsql { SELECT sql(1, 'DELETE FROM t2 WHERE a = '||a ) FROM t2 WHERE a>5 } |
execsql { SELECT a from t2 } |
} {1 2 3 4 5} |
do_test tkt3718-2.2 { |
execsql { |
DELETE FROM t2 WHERE a > 5; |
BEGIN; |
INSERT INTO t2 SELECT a+5, sql(a==3, |
'INSERT INTO t2 SELECT a+10, f2(b) FROM t1' |
) FROM t1; |
} |
execsql { |
COMMIT; |
SELECT a FROM t2; |
} |
} {1 2 3 4 5 6 7 8 9 10} |
do_test tkt3718-2.3 { |
execsql { |
DELETE FROM t2 WHERE a > 5; |
BEGIN; |
INSERT INTO t2 SELECT a+5, sql(a==3, |
'INSERT INTO t2 SELECT a+10, b FROM t1' |
) FROM t1; |
COMMIT; |
} |
execsql { SELECT a FROM t2 ORDER BY a+0} |
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} |
integrity_check tkt3718.2-4 |
|
# The next set of tests, tkt3718-3.*, test that a statement transaction |
# that has a committed statement transaction nested inside of it can |
# be committed or reverted. |
# |
foreach {tn io ii results} { |
1 0 10 {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} |
2 1 10 {6 7 8 9 10 16 17 18 19 20} |
3 0 11 {1 2 3 4 5 6 7 8 9 10 16 17 18 19 20} |
4 1 11 {6 7 8 9 10 16 17 18 19 20} |
} { |
do_test tkt3718-3.$tn { |
execsql { |
DELETE FROM t2; |
INSERT INTO t2 SELECT a+5, b FROM t1; |
INSERT INTO t2 SELECT a+15, b FROM t1; |
} |
|
catchsql " |
BEGIN; |
INSERT INTO t2 SELECT a+$io, sql(a==3, |
'INSERT INTO t2 SELECT a+$ii, b FROM t1' |
) FROM t1; |
" |
|
execsql { COMMIT } |
|
execsql { SELECT a FROM t2 ORDER BY a+0} |
} $results |
|
integrity_check tkt3718-3.$tn.integrity |
} |
|
# This is the same test as tkt3718-3.*, but with 3 levels of nesting. |
# |
foreach {tn i1 i2 i3 results} { |
1 0 10 20 {5 10 15 20 25 30} |
2 0 10 21 {5 10 15 20 30} |
3 0 11 20 {5 10 20 30} |
4 0 11 21 {5 10 20 30} |
5 1 10 20 {10 20 30} |
6 1 10 21 {10 20 30} |
7 1 11 20 {10 20 30} |
8 1 11 21 {10 20 30} |
} { |
do_test tkt3718-4.$tn { |
execsql { |
DELETE FROM t2; |
INSERT INTO t2 SELECT a+5, b FROM t1; |
INSERT INTO t2 SELECT a+15, b FROM t1; |
INSERT INTO t2 SELECT a+25, b FROM t1; |
} |
|
catchsql " |
BEGIN; |
INSERT INTO t2 SELECT a+$i1, sql(a==3, |
'INSERT INTO t2 SELECT a+$i2, sql(a==3, |
''INSERT INTO t2 SELECT a+$i3, b FROM t1'' |
) FROM t1' |
) FROM t1; |
" |
|
execsql { COMMIT } |
|
execsql { SELECT a FROM t2 WHERE (a%5)==0 ORDER BY a+0} |
} $results |
|
do_test tkt3718-4.$tn.extra { |
execsql { |
SELECT |
(SELECT sum(a) FROM t2)==(SELECT sum(a*5-10) FROM t2 WHERE (a%5)==0) |
} |
} {1} |
|
integrity_check tkt3718-4.$tn.integrity |
} |
|
|
finish_test |