Annotation of embedaddon/sqlite3/test/tkt3718.test, revision 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