/ Check-in [76175199]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test cases to verify that multiple virtual tables can be updated within a trigger and that xSync, xCommit, and xRollback are never called except following xBegin or xCreate. Ticket #3083. (CVS 5064)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 76175199ac2fda57e616eb386ba0bad6aa9f74b4
User & Date: drh 2008-04-28 20:27:54
Context
2008-04-28
20:35
Allow SQLITE_MAX_COLUMN to be set to zero at compile-time in order to disable the checks. Also SQLITE_MAX_EXPR_DEPTH. Ticket #3069. (CVS 5065) check-in: e6f71abb user: drh tags: trunk
20:27
Add test cases to verify that multiple virtual tables can be updated within a trigger and that xSync, xCommit, and xRollback are never called except following xBegin or xCreate. Ticket #3083. (CVS 5064) check-in: 76175199 user: drh tags: trunk
18:46
Make sure that transactions are started on all virtual tables that changes in a single statement, not just the first. Ticket #3083. Need to add test cases. (CVS 5063) check-in: 133b7ee5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/test8.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Code for testing the virtual table interfaces.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16         -** $Id: test8.c,v 1.61 2008/03/17 09:36:45 danielk1977 Exp $
           16  +** $Id: test8.c,v 1.62 2008/04/28 20:27:54 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   #include "tcl.h"
    20     20   #include <stdlib.h>
    21     21   #include <string.h>
    22     22   
    23     23   #ifndef SQLITE_OMIT_VIRTUALTABLE
................................................................................
    57     57   */
    58     58   struct echo_vtab {
    59     59     sqlite3_vtab base;
    60     60     Tcl_Interp *interp;     /* Tcl interpreter containing debug variables */
    61     61     sqlite3 *db;            /* Database connection */
    62     62   
    63     63     int isPattern;
           64  +  int inTransaction;      /* True if within a transaction */
    64     65     char *zThis;            /* Name of the echo table */
    65     66     char *zTableName;       /* Name of the real table */
    66     67     char *zLogName;         /* Name of the log table */
    67     68     int nCol;               /* Number of columns in the real table */
    68     69     int *aIndex;            /* Array of size nCol. True if column has an index */
    69     70     char **aCol;            /* Array of size nCol. Column names */
    70     71   };
................................................................................
   462    463       }
   463    464     }
   464    465   
   465    466     if( *ppVtab && rc!=SQLITE_OK ){
   466    467       echoDestructor(*ppVtab);
   467    468       *ppVtab = 0;
   468    469     }
          470  +
          471  +  if( rc==SQLITE_OK ){
          472  +    (*(echo_vtab**)ppVtab)->inTransaction = 1;
          473  +  }
   469    474   
   470    475     return rc;
   471    476   }
   472    477   
   473    478   /* 
   474    479   ** Echo virtual table module xConnect method.
   475    480   */
................................................................................
   878    883     sqlite3_stmt *pStmt;
   879    884     char *z = 0;               /* SQL statement to execute */
   880    885     int bindArgZero = 0;       /* True to bind apData[0] to sql var no. nData */
   881    886     int bindArgOne = 0;        /* True to bind apData[1] to sql var no. 1 */
   882    887     int i;                     /* Counter variable used by for loops */
   883    888   
   884    889     assert( nData==pVtab->nCol+2 || nData==1 );
          890  +
          891  +  /* Ticket #3083 - make sure we always start a transaction prior to
          892  +  ** making any changes to a virtual table */
          893  +  assert( pVtab->inTransaction );
   885    894   
   886    895     /* If apData[0] is an integer and nData>1 then do an UPDATE */
   887    896     if( nData>1 && sqlite3_value_type(apData[0])==SQLITE_INTEGER ){
   888    897       char *zSep = " SET";
   889    898       z = sqlite3_mprintf("UPDATE %Q", pVtab->zTableName);
   890    899       if( !z ){
   891    900         rc = SQLITE_NOMEM;
................................................................................
   997   1006     return (z?SQLITE_OK:SQLITE_NOMEM);
   998   1007   }
   999   1008   static int echoBegin(sqlite3_vtab *tab){
  1000   1009     int rc;
  1001   1010     echo_vtab *pVtab = (echo_vtab *)tab;
  1002   1011     Tcl_Interp *interp = pVtab->interp;
  1003   1012     const char *zVal; 
         1013  +
         1014  +  /* Ticket #3083 - do not start a transaction if we are already in
         1015  +  ** a transaction */
         1016  +  assert( !pVtab->inTransaction );
  1004   1017   
  1005   1018     rc = echoTransactionCall(tab, "xBegin");
  1006   1019   
  1007   1020     if( rc==SQLITE_OK ){
  1008   1021       /* Check if the $::echo_module_begin_fail variable is defined. If it is,
  1009   1022       ** and it is set to the name of the real table underlying this virtual
  1010   1023       ** echo module table, then cause this xSync operation to fail.
  1011   1024       */
  1012   1025       zVal = Tcl_GetVar(interp, "echo_module_begin_fail", TCL_GLOBAL_ONLY);
  1013   1026       if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){
  1014   1027         rc = SQLITE_ERROR;
  1015   1028       }
  1016   1029     }
         1030  +  if( rc==SQLITE_OK ){
         1031  +    pVtab->inTransaction = 1;
         1032  +  }
  1017   1033     return rc;
  1018   1034   }
  1019   1035   static int echoSync(sqlite3_vtab *tab){
  1020   1036     int rc;
  1021   1037     echo_vtab *pVtab = (echo_vtab *)tab;
  1022   1038     Tcl_Interp *interp = pVtab->interp;
  1023   1039     const char *zVal; 
         1040  +
         1041  +  /* Ticket #3083 - Only call xSync if we have previously started a
         1042  +  ** transaction */
         1043  +  assert( pVtab->inTransaction );
  1024   1044   
  1025   1045     rc = echoTransactionCall(tab, "xSync");
  1026   1046   
  1027   1047     if( rc==SQLITE_OK ){
  1028   1048       /* Check if the $::echo_module_sync_fail variable is defined. If it is,
  1029   1049       ** and it is set to the name of the real table underlying this virtual
  1030   1050       ** echo module table, then cause this xSync operation to fail.
................................................................................
  1033   1053       if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){
  1034   1054         rc = -1;
  1035   1055       }
  1036   1056     }
  1037   1057     return rc;
  1038   1058   }
  1039   1059   static int echoCommit(sqlite3_vtab *tab){
         1060  +  echo_vtab *pVtab = (echo_vtab*)tab;
  1040   1061     int rc;
         1062  +
         1063  +  /* Ticket #3083 - Only call xCommit if we have previously started
         1064  +  ** a transaction */
         1065  +  assert( pVtab->inTransaction );
         1066  +
  1041   1067     sqlite3FaultBenign(SQLITE_FAULTINJECTOR_MALLOC, 1);
  1042   1068     rc = echoTransactionCall(tab, "xCommit");
  1043   1069     sqlite3FaultBenign(SQLITE_FAULTINJECTOR_MALLOC, 0);
         1070  +  if( rc==SQLITE_OK ){
         1071  +    pVtab->inTransaction = 0;
         1072  +  }
  1044   1073     return rc;
  1045   1074   }
  1046   1075   static int echoRollback(sqlite3_vtab *tab){
  1047         -  return echoTransactionCall(tab, "xRollback");
         1076  +  int rc;
         1077  +  echo_vtab *pVtab = (echo_vtab*)tab;
         1078  +
         1079  +  /* Ticket #3083 - Only call xRollback if we have previously started
         1080  +  ** a transaction */
         1081  +  assert( pVtab->inTransaction );
         1082  +
         1083  +  rc = echoTransactionCall(tab, "xRollback");
         1084  +  pVtab->inTransaction = 0;
         1085  +  return rc;
  1048   1086   }
  1049   1087   
  1050   1088   /*
  1051   1089   ** Implementation of "GLOB" function on the echo module.  Pass
  1052   1090   ** all arguments to the ::echo_glob_overload procedure of TCL
  1053   1091   ** and return the result of that procedure as a string.
  1054   1092   */

Added test/vtabC.test.

            1  +# 2008 April 10
            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 is verifying that the xUpdate, xSync, xCommit
           13  +# and xRollback methods are only invoked after an xBegin or xCreate.
           14  +# Ticket #3083.
           15  +#
           16  +# $Id: vtabC.test,v 1.1 2008/04/28 20:27:54 drh Exp $
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +ifcapable !vtab {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +
           27  +# N will be the number of virtual tables we have defined.
           28  +#
           29  +unset -nocomplain N
           30  +for {set N 1} {$N<=20} {incr N} {
           31  +  db close
           32  +  file delete -force test.db test.db-journal
           33  +  sqlite3 db test.db
           34  +  register_echo_module [sqlite3_connection_pointer db]
           35  +
           36  +  # Create $N tables and $N virtual tables to echo them.
           37  +  #
           38  +  unset -nocomplain tablist
           39  +  set tablist {}
           40  +  do_test vtabC-1.$N.1 {
           41  +    for {set i 1} {$i<=$::N} {incr i} {
           42  +      execsql "CREATE TABLE t${i}(x)"
           43  +      execsql "CREATE VIRTUAL TABLE vt$i USING echo(t$i)"
           44  +      lappend ::tablist t$i vt$i
           45  +    }
           46  +    execsql {SELECT count(*) FROM sqlite_master}
           47  +  } [expr {$N*2}]
           48  +  do_test vtabC-1.$N.2 {
           49  +    execsql {SELECT name FROM sqlite_master}
           50  +  } $tablist
           51  +
           52  +  # Create a table m and add triggers to make changes on all
           53  +  # of the virtual tables when m is changed.
           54  +  #
           55  +  do_test vtabC-1.$N.3 {
           56  +    execsql {CREATE TABLE m(a)}
           57  +    set sql "CREATE TRIGGER rins AFTER INSERT ON m BEGIN\n"
           58  +    for {set i 1} {$i<=$::N} {incr i} {
           59  +      append sql "  INSERT INTO vt$i VALUES(NEW.a+$i);\n"
           60  +    }
           61  +    append sql "END;"
           62  +    execsql $sql
           63  +    execsql {SELECT count(*) FROM sqlite_master}
           64  +  } [expr {$N*2+2}]
           65  +  do_test vtabC-1.$N.4 {
           66  +    execsql {
           67  +      INSERT INTO m VALUES(1000);
           68  +      SELECT * FROM m;
           69  +    }
           70  +  } {1000}
           71  +  for {set j 1} {$j<=$::N} {incr j} {
           72  +    do_test vtabC-1.$N.5.$j {
           73  +      execsql "SELECT * FROM t$::j"
           74  +    } [expr {$j+1000}]
           75  +    do_test vtabC-1.$N.6.$j {
           76  +      execsql "SELECT * FROM vt$::j"
           77  +    } [expr {$j+1000}]
           78  +  }
           79  +  do_test vtabC-1.$N.7 {
           80  +    set sql "CREATE TRIGGER rins2 BEFORE INSERT ON m BEGIN\n"
           81  +    for {set i 1} {$i<=$::N} {incr i} {
           82  +      append sql "  INSERT INTO vt$i VALUES(NEW.a+$i*100);\n"
           83  +    }
           84  +    for {set i 1} {$i<=$::N} {incr i} {
           85  +      append sql "  INSERT INTO vt$i VALUES(NEW.a+$i*10000);\n"
           86  +    }
           87  +    append sql "END;"
           88  +    execsql $sql
           89  +    execsql {SELECT count(*) FROM sqlite_master}
           90  +  } [expr {$N*2+3}]
           91  +  do_test vtabC-1.$N.8 {
           92  +    execsql {
           93  +      INSERT INTO m VALUES(9000000);
           94  +      SELECT * FROM m;
           95  +    }
           96  +  } {1000 9000000}
           97  +  unset -nocomplain res
           98  +  for {set j 1} {$j<=$::N} {incr j} {
           99  +    set res [expr {$j+1000}]
          100  +    lappend res [expr {$j*100+9000000}]
          101  +    lappend res [expr {$j*10000+9000000}]
          102  +    lappend res [expr {$j+9000000}]
          103  +    do_test vtabC-1.$N.9.$j {
          104  +      execsql "SELECT * FROM t$::j"
          105  +    } $res
          106  +    do_test vtabC-1.$N.10.$j {
          107  +      execsql "SELECT * FROM vt$::j"
          108  +    } $res
          109  +  }
          110  +}
          111  +unset -nocomplain res N i j
          112  +
          113  +
          114  +finish_test