/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/test8.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
57
58
59
60
61
62
63

64
65
66
67
68
69
70
...
462
463
464
465
466
467
468




469
470
471
472
473
474
475
...
878
879
880
881
882
883
884




885
886
887
888
889
890
891
...
997
998
999
1000
1001
1002
1003




1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016



1017
1018
1019
1020
1021
1022
1023




1024
1025
1026
1027
1028
1029
1030
....
1033
1034
1035
1036
1037
1038
1039

1040





1041
1042
1043



1044
1045
1046







1047


1048
1049
1050
1051
1052
1053
1054
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the virtual table interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test8.c,v 1.61 2008/03/17 09:36:45 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

#ifndef SQLITE_OMIT_VIRTUALTABLE
................................................................................
*/
struct echo_vtab {
  sqlite3_vtab base;
  Tcl_Interp *interp;     /* Tcl interpreter containing debug variables */
  sqlite3 *db;            /* Database connection */

  int isPattern;

  char *zThis;            /* Name of the echo table */
  char *zTableName;       /* Name of the real table */
  char *zLogName;         /* Name of the log table */
  int nCol;               /* Number of columns in the real table */
  int *aIndex;            /* Array of size nCol. True if column has an index */
  char **aCol;            /* Array of size nCol. Column names */
};
................................................................................
    }
  }

  if( *ppVtab && rc!=SQLITE_OK ){
    echoDestructor(*ppVtab);
    *ppVtab = 0;
  }





  return rc;
}

/* 
** Echo virtual table module xConnect method.
*/
................................................................................
  sqlite3_stmt *pStmt;
  char *z = 0;               /* SQL statement to execute */
  int bindArgZero = 0;       /* True to bind apData[0] to sql var no. nData */
  int bindArgOne = 0;        /* True to bind apData[1] to sql var no. 1 */
  int i;                     /* Counter variable used by for loops */

  assert( nData==pVtab->nCol+2 || nData==1 );





  /* If apData[0] is an integer and nData>1 then do an UPDATE */
  if( nData>1 && sqlite3_value_type(apData[0])==SQLITE_INTEGER ){
    char *zSep = " SET";
    z = sqlite3_mprintf("UPDATE %Q", pVtab->zTableName);
    if( !z ){
      rc = SQLITE_NOMEM;
................................................................................
  return (z?SQLITE_OK:SQLITE_NOMEM);
}
static int echoBegin(sqlite3_vtab *tab){
  int rc;
  echo_vtab *pVtab = (echo_vtab *)tab;
  Tcl_Interp *interp = pVtab->interp;
  const char *zVal; 





  rc = echoTransactionCall(tab, "xBegin");

  if( rc==SQLITE_OK ){
    /* Check if the $::echo_module_begin_fail variable is defined. If it is,
    ** and it is set to the name of the real table underlying this virtual
    ** echo module table, then cause this xSync operation to fail.
    */
    zVal = Tcl_GetVar(interp, "echo_module_begin_fail", TCL_GLOBAL_ONLY);
    if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){
      rc = SQLITE_ERROR;
    }
  }



  return rc;
}
static int echoSync(sqlite3_vtab *tab){
  int rc;
  echo_vtab *pVtab = (echo_vtab *)tab;
  Tcl_Interp *interp = pVtab->interp;
  const char *zVal; 





  rc = echoTransactionCall(tab, "xSync");

  if( rc==SQLITE_OK ){
    /* Check if the $::echo_module_sync_fail variable is defined. If it is,
    ** and it is set to the name of the real table underlying this virtual
    ** echo module table, then cause this xSync operation to fail.
................................................................................
    if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){
      rc = -1;
    }
  }
  return rc;
}
static int echoCommit(sqlite3_vtab *tab){

  int rc;





  sqlite3FaultBenign(SQLITE_FAULTINJECTOR_MALLOC, 1);
  rc = echoTransactionCall(tab, "xCommit");
  sqlite3FaultBenign(SQLITE_FAULTINJECTOR_MALLOC, 0);



  return rc;
}
static int echoRollback(sqlite3_vtab *tab){







  return echoTransactionCall(tab, "xRollback");


}

/*
** Implementation of "GLOB" function on the echo module.  Pass
** all arguments to the ::echo_glob_overload procedure of TCL
** and return the result of that procedure as a string.
*/







|







 







>







 







>
>
>
>







 







>
>
>
>







 







>
>
>
>













>
>
>







>
>
>
>







 







>

>
>
>
>
>



>
>
>



>
>
>
>
>
>
>
|
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
...
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
...
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
....
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
....
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the virtual table interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test8.c,v 1.62 2008/04/28 20:27:54 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

#ifndef SQLITE_OMIT_VIRTUALTABLE
................................................................................
*/
struct echo_vtab {
  sqlite3_vtab base;
  Tcl_Interp *interp;     /* Tcl interpreter containing debug variables */
  sqlite3 *db;            /* Database connection */

  int isPattern;
  int inTransaction;      /* True if within a transaction */
  char *zThis;            /* Name of the echo table */
  char *zTableName;       /* Name of the real table */
  char *zLogName;         /* Name of the log table */
  int nCol;               /* Number of columns in the real table */
  int *aIndex;            /* Array of size nCol. True if column has an index */
  char **aCol;            /* Array of size nCol. Column names */
};
................................................................................
    }
  }

  if( *ppVtab && rc!=SQLITE_OK ){
    echoDestructor(*ppVtab);
    *ppVtab = 0;
  }

  if( rc==SQLITE_OK ){
    (*(echo_vtab**)ppVtab)->inTransaction = 1;
  }

  return rc;
}

/* 
** Echo virtual table module xConnect method.
*/
................................................................................
  sqlite3_stmt *pStmt;
  char *z = 0;               /* SQL statement to execute */
  int bindArgZero = 0;       /* True to bind apData[0] to sql var no. nData */
  int bindArgOne = 0;        /* True to bind apData[1] to sql var no. 1 */
  int i;                     /* Counter variable used by for loops */

  assert( nData==pVtab->nCol+2 || nData==1 );

  /* Ticket #3083 - make sure we always start a transaction prior to
  ** making any changes to a virtual table */
  assert( pVtab->inTransaction );

  /* If apData[0] is an integer and nData>1 then do an UPDATE */
  if( nData>1 && sqlite3_value_type(apData[0])==SQLITE_INTEGER ){
    char *zSep = " SET";
    z = sqlite3_mprintf("UPDATE %Q", pVtab->zTableName);
    if( !z ){
      rc = SQLITE_NOMEM;
................................................................................
  return (z?SQLITE_OK:SQLITE_NOMEM);
}
static int echoBegin(sqlite3_vtab *tab){
  int rc;
  echo_vtab *pVtab = (echo_vtab *)tab;
  Tcl_Interp *interp = pVtab->interp;
  const char *zVal; 

  /* Ticket #3083 - do not start a transaction if we are already in
  ** a transaction */
  assert( !pVtab->inTransaction );

  rc = echoTransactionCall(tab, "xBegin");

  if( rc==SQLITE_OK ){
    /* Check if the $::echo_module_begin_fail variable is defined. If it is,
    ** and it is set to the name of the real table underlying this virtual
    ** echo module table, then cause this xSync operation to fail.
    */
    zVal = Tcl_GetVar(interp, "echo_module_begin_fail", TCL_GLOBAL_ONLY);
    if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){
      rc = SQLITE_ERROR;
    }
  }
  if( rc==SQLITE_OK ){
    pVtab->inTransaction = 1;
  }
  return rc;
}
static int echoSync(sqlite3_vtab *tab){
  int rc;
  echo_vtab *pVtab = (echo_vtab *)tab;
  Tcl_Interp *interp = pVtab->interp;
  const char *zVal; 

  /* Ticket #3083 - Only call xSync if we have previously started a
  ** transaction */
  assert( pVtab->inTransaction );

  rc = echoTransactionCall(tab, "xSync");

  if( rc==SQLITE_OK ){
    /* Check if the $::echo_module_sync_fail variable is defined. If it is,
    ** and it is set to the name of the real table underlying this virtual
    ** echo module table, then cause this xSync operation to fail.
................................................................................
    if( zVal && 0==strcmp(zVal, pVtab->zTableName) ){
      rc = -1;
    }
  }
  return rc;
}
static int echoCommit(sqlite3_vtab *tab){
  echo_vtab *pVtab = (echo_vtab*)tab;
  int rc;

  /* Ticket #3083 - Only call xCommit if we have previously started
  ** a transaction */
  assert( pVtab->inTransaction );

  sqlite3FaultBenign(SQLITE_FAULTINJECTOR_MALLOC, 1);
  rc = echoTransactionCall(tab, "xCommit");
  sqlite3FaultBenign(SQLITE_FAULTINJECTOR_MALLOC, 0);
  if( rc==SQLITE_OK ){
    pVtab->inTransaction = 0;
  }
  return rc;
}
static int echoRollback(sqlite3_vtab *tab){
  int rc;
  echo_vtab *pVtab = (echo_vtab*)tab;

  /* Ticket #3083 - Only call xRollback if we have previously started
  ** a transaction */
  assert( pVtab->inTransaction );

  rc = echoTransactionCall(tab, "xRollback");
  pVtab->inTransaction = 0;
  return rc;
}

/*
** Implementation of "GLOB" function on the echo module.  Pass
** all arguments to the ::echo_glob_overload procedure of TCL
** and return the result of that procedure as a string.
*/

Added test/vtabC.test.





































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
# 2008 April 10
#
# 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 is verifying that the xUpdate, xSync, xCommit
# and xRollback methods are only invoked after an xBegin or xCreate.
# Ticket #3083.
#
# $Id: vtabC.test,v 1.1 2008/04/28 20:27:54 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab {
  finish_test
  return
}


# N will be the number of virtual tables we have defined.
#
unset -nocomplain N
for {set N 1} {$N<=20} {incr N} {
  db close
  file delete -force test.db test.db-journal
  sqlite3 db test.db
  register_echo_module [sqlite3_connection_pointer db]

  # Create $N tables and $N virtual tables to echo them.
  #
  unset -nocomplain tablist
  set tablist {}
  do_test vtabC-1.$N.1 {
    for {set i 1} {$i<=$::N} {incr i} {
      execsql "CREATE TABLE t${i}(x)"
      execsql "CREATE VIRTUAL TABLE vt$i USING echo(t$i)"
      lappend ::tablist t$i vt$i
    }
    execsql {SELECT count(*) FROM sqlite_master}
  } [expr {$N*2}]
  do_test vtabC-1.$N.2 {
    execsql {SELECT name FROM sqlite_master}
  } $tablist

  # Create a table m and add triggers to make changes on all
  # of the virtual tables when m is changed.
  #
  do_test vtabC-1.$N.3 {
    execsql {CREATE TABLE m(a)}
    set sql "CREATE TRIGGER rins AFTER INSERT ON m BEGIN\n"
    for {set i 1} {$i<=$::N} {incr i} {
      append sql "  INSERT INTO vt$i VALUES(NEW.a+$i);\n"
    }
    append sql "END;"
    execsql $sql
    execsql {SELECT count(*) FROM sqlite_master}
  } [expr {$N*2+2}]
  do_test vtabC-1.$N.4 {
    execsql {
      INSERT INTO m VALUES(1000);
      SELECT * FROM m;
    }
  } {1000}
  for {set j 1} {$j<=$::N} {incr j} {
    do_test vtabC-1.$N.5.$j {
      execsql "SELECT * FROM t$::j"
    } [expr {$j+1000}]
    do_test vtabC-1.$N.6.$j {
      execsql "SELECT * FROM vt$::j"
    } [expr {$j+1000}]
  }
  do_test vtabC-1.$N.7 {
    set sql "CREATE TRIGGER rins2 BEFORE INSERT ON m BEGIN\n"
    for {set i 1} {$i<=$::N} {incr i} {
      append sql "  INSERT INTO vt$i VALUES(NEW.a+$i*100);\n"
    }
    for {set i 1} {$i<=$::N} {incr i} {
      append sql "  INSERT INTO vt$i VALUES(NEW.a+$i*10000);\n"
    }
    append sql "END;"
    execsql $sql
    execsql {SELECT count(*) FROM sqlite_master}
  } [expr {$N*2+3}]
  do_test vtabC-1.$N.8 {
    execsql {
      INSERT INTO m VALUES(9000000);
      SELECT * FROM m;
    }
  } {1000 9000000}
  unset -nocomplain res
  for {set j 1} {$j<=$::N} {incr j} {
    set res [expr {$j+1000}]
    lappend res [expr {$j*100+9000000}]
    lappend res [expr {$j*10000+9000000}]
    lappend res [expr {$j+9000000}]
    do_test vtabC-1.$N.9.$j {
      execsql "SELECT * FROM t$::j"
    } $res
    do_test vtabC-1.$N.10.$j {
      execsql "SELECT * FROM vt$::j"
    } $res
  }
}
unset -nocomplain res N i j


finish_test