Return to tkt3718.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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