SQLite

Check-in [d5a608d0a4]
Login

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

Overview
Comment:Allow database writes from within virtual table module xSync() callbacks. (CVS 3334)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d5a608d0a412e13dfced6a3827574a2cff802f25
User & Date: danielk1977 2006-07-25 15:14:53.000
Context
2006-07-26
01:39
Initial attempt at making sqlite3_interrupt() work even when called from a separate thread. (CVS 3335) (check-in: 35fd67d7a0 user: drh tags: trunk)
2006-07-25
15:14
Allow database writes from within virtual table module xSync() callbacks. (CVS 3334) (check-in: d5a608d0a4 user: danielk1977 tags: trunk)
2006-07-17
00:19
Fix lemon so that it does not crash on a empty reduce action. Ticket #1892. (CVS 3333) (check-in: 4207ebc4e1 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbeaux.c.
864
865
866
867
868
869
870

871

872
873
874
875
876
877
878
    sqlite3BtreeClose(pCx->pBt);
  }
#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( pCx->pVtabCursor ){
    sqlite3_vtab_cursor *pVtabCursor = pCx->pVtabCursor;
    const sqlite3_module *pModule = pCx->pModule;
    p->inVtabMethod = 1;

    pModule->xClose(pVtabCursor);

    p->inVtabMethod = 0;
  }
#endif
  sqliteFree(pCx->pData);
  sqliteFree(pCx->aType);
  sqliteFree(pCx);
}







>

>







864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
    sqlite3BtreeClose(pCx->pBt);
  }
#ifndef SQLITE_OMIT_VIRTUALTABLE
  if( pCx->pVtabCursor ){
    sqlite3_vtab_cursor *pVtabCursor = pCx->pVtabCursor;
    const sqlite3_module *pModule = pCx->pModule;
    p->inVtabMethod = 1;
    sqlite3SafetyOff(p->db);
    pModule->xClose(pVtabCursor);
    sqlite3SafetyOn(p->db);
    p->inVtabMethod = 0;
  }
#endif
  sqliteFree(pCx->pData);
  sqliteFree(pCx->aType);
  sqliteFree(pCx);
}
979
980
981
982
983
984
985

















986
987
988
989
990
991
992
*/
static int vdbeCommit(sqlite3 *db){
  int i;
  int nTrans = 0;  /* Number of databases with an active write-transaction */
  int rc = SQLITE_OK;
  int needXcommit = 0;


















  for(i=0; i<db->nDb; i++){ 
    Btree *pBt = db->aDb[i].pBt;
    if( pBt && sqlite3BtreeIsInTrans(pBt) ){
      needXcommit = 1;
      if( i!=1 ) nTrans++;
    }
  }







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
*/
static int vdbeCommit(sqlite3 *db){
  int i;
  int nTrans = 0;  /* Number of databases with an active write-transaction */
  int rc = SQLITE_OK;
  int needXcommit = 0;

  /* Before doing anything else, call the xSync() callback for any
  ** virtual module tables written in this transaction. This has to
  ** be done before determining whether a master journal file is 
  ** required, as an xSync() callback may add an attached database
  ** to the transaction.
  */
  rc = sqlite3VtabSync(db, rc);
  if( rc!=SQLITE_OK ){
    return rc;
  }

  /* This loop determines (a) if the commit hook should be invoked and
  ** (b) how many database files have open write transactions, not 
  ** including the temp database. (b) is important because if more than 
  ** one database file has an open write transaction, a master journal
  ** file is required for an atomic commit.
  */ 
  for(i=0; i<db->nDb; i++){ 
    Btree *pBt = db->aDb[i].pBt;
    if( pBt && sqlite3BtreeIsInTrans(pBt) ){
      needXcommit = 1;
      if( i!=1 ) nTrans++;
    }
  }
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
  if( 0==strlen(sqlite3BtreeGetFilename(db->aDb[0].pBt)) || nTrans<=1 ){
    for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
      Btree *pBt = db->aDb[i].pBt;
      if( pBt ){
        rc = sqlite3BtreeSync(pBt, 0);
      }
    }
    rc = sqlite3VtabSync(db, rc);

    /* Do the commit only if all databases successfully synced */
    if( rc==SQLITE_OK ){
      for(i=0; i<db->nDb; i++){
        Btree *pBt = db->aDb[i].pBt;
        if( pBt ){
          sqlite3BtreeCommit(pBt);







<







1032
1033
1034
1035
1036
1037
1038

1039
1040
1041
1042
1043
1044
1045
  if( 0==strlen(sqlite3BtreeGetFilename(db->aDb[0].pBt)) || nTrans<=1 ){
    for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
      Btree *pBt = db->aDb[i].pBt;
      if( pBt ){
        rc = sqlite3BtreeSync(pBt, 0);
      }
    }


    /* Do the commit only if all databases successfully synced */
    if( rc==SQLITE_OK ){
      for(i=0; i<db->nDb; i++){
        Btree *pBt = db->aDb[i].pBt;
        if( pBt ){
          sqlite3BtreeCommit(pBt);
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116


1117

1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
    **
    ** If the error occurs during the first call to sqlite3BtreeSync(),
    ** then there is a chance that the master journal file will be
    ** orphaned. But we cannot delete it, in case the master journal
    ** file name was written into the journal file before the failure
    ** occured.
    */
    for(i=0; i<db->nDb; i++){ 
      Btree *pBt = db->aDb[i].pBt;
      if( pBt && sqlite3BtreeIsInTrans(pBt) ){
        rc = sqlite3BtreeSync(pBt, zMaster);
        if( rc!=SQLITE_OK ){


          sqlite3OsClose(&master);

          sqliteFree(zMaster);
          return rc;
        }
      }
    }
    rc = sqlite3VtabSync(db, SQLITE_OK);
    if( rc!=SQLITE_OK ) return rc;
    sqlite3OsClose(&master);

    /* Delete the master journal file. This commits the transaction. After
    ** doing this the directory is synced again before any individual
    ** transaction files are deleted.
    */
    rc = sqlite3OsDelete(zMaster);
    assert( rc==SQLITE_OK );







|



<
>
>
|
>
|
|
|
<
<
<
<
<







1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133

1134
1135
1136
1137
1138
1139
1140





1141
1142
1143
1144
1145
1146
1147
    **
    ** If the error occurs during the first call to sqlite3BtreeSync(),
    ** then there is a chance that the master journal file will be
    ** orphaned. But we cannot delete it, in case the master journal
    ** file name was written into the journal file before the failure
    ** occured.
    */
    for(i=0; rc==SQLITE_OK && i<db->nDb; i++){ 
      Btree *pBt = db->aDb[i].pBt;
      if( pBt && sqlite3BtreeIsInTrans(pBt) ){
        rc = sqlite3BtreeSync(pBt, zMaster);

      }
    }
    sqlite3OsClose(&master);
    if( rc!=SQLITE_OK ){
      sqliteFree(zMaster);
      return rc;
    }






    /* Delete the master journal file. This commits the transaction. After
    ** doing this the directory is synced again before any individual
    ** transaction files are deleted.
    */
    rc = sqlite3OsDelete(zMaster);
    assert( rc==SQLITE_OK );
1422
1423
1424
1425
1426
1427
1428

1429

1430
1431
1432
1433
1434
1435
1436
    return SQLITE_MISUSE;
  }

  /* If the VM did not run to completion or if it encountered an
  ** error, then it might not have been halted properly.  So halt
  ** it now.
  */

  sqlite3VdbeHalt(p);


  /* If the VDBE has be run even partially, then transfer the error code
  ** and error message from the VDBE into the main database structure.  But
  ** if the VDBE has just been set to run but has not actually executed any
  ** instructions yet, leave the main database error information unchanged.
  */
  if( p->pc>=0 ){







>

>







1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
    return SQLITE_MISUSE;
  }

  /* If the VM did not run to completion or if it encountered an
  ** error, then it might not have been halted properly.  So halt
  ** it now.
  */
  sqlite3SafetyOn(p->db);
  sqlite3VdbeHalt(p);
  sqlite3SafetyOff(p->db);

  /* If the VDBE has be run even partially, then transfer the error code
  ** and error message from the VDBE into the main database structure.  But
  ** if the VDBE has just been set to run but has not actually executed any
  ** instructions yet, leave the main database error information unchanged.
  */
  if( p->pc>=0 ){
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
 
/*
** Clean up and delete a VDBE after execution.  Return an integer which is
** the result code.  Write any error message text into *pzErrMsg.
*/
int sqlite3VdbeFinalize(Vdbe *p){
  int rc = SQLITE_OK;

  if( p->magic==VDBE_MAGIC_RUN || p->magic==VDBE_MAGIC_HALT ){
    rc = sqlite3VdbeReset(p);
  }else if( p->magic!=VDBE_MAGIC_INIT ){
    return SQLITE_MISUSE;
  }
  sqlite3VdbeDelete(p);
  return rc;







<







1508
1509
1510
1511
1512
1513
1514

1515
1516
1517
1518
1519
1520
1521
 
/*
** Clean up and delete a VDBE after execution.  Return an integer which is
** the result code.  Write any error message text into *pzErrMsg.
*/
int sqlite3VdbeFinalize(Vdbe *p){
  int rc = SQLITE_OK;

  if( p->magic==VDBE_MAGIC_RUN || p->magic==VDBE_MAGIC_HALT ){
    rc = sqlite3VdbeReset(p);
  }else if( p->magic!=VDBE_MAGIC_INIT ){
    return SQLITE_MISUSE;
  }
  sqlite3VdbeDelete(p);
  return rc;
Changes to src/vtab.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2006 June 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 contains code used to help implement virtual tables.
**
** $Id: vtab.c,v 1.27 2006/07/08 18:09:15 drh Exp $
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
#include "sqliteInt.h"

/*
** External API function used to create a new virtual-table module.
*/













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2006 June 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 contains code used to help implement virtual tables.
**
** $Id: vtab.c,v 1.28 2006/07/25 15:14:53 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
#include "sqliteInt.h"

/*
** External API function used to create a new virtual-table module.
*/
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513

514

515
516
517
518
519
520
521




522
523
524
525
526
527
528
  }
  sqliteFree(db->aVTrans);
  db->nVTrans = 0;
  db->aVTrans = 0;
}

/*
** If argument rc is not SQLITE_OK, then return it and do nothing. 
** Otherwise, invoke the xSync method of all virtual tables in the 
** sqlite3.aVTrans array. Return the error code for the first error 
** that occurs, or SQLITE_OK if all xSync operations are successful.
*/
int sqlite3VtabSync(sqlite3 *db, int rc2){
  int i;
  int rc = SQLITE_OK;

  if( rc2!=SQLITE_OK ) return rc2;

  for(i=0; rc==SQLITE_OK && i<db->nVTrans && db->aVTrans[i]; i++){
    sqlite3_vtab *pVtab = db->aVTrans[i];
    int (*x)(sqlite3_vtab *);
    x = pVtab->pModule->xSync;
    if( x ){
      rc = x(pVtab);
    }




  }
  return rc;
}

/*
** Invoke the xRollback method of all virtual tables in the 
** sqlite3.aVTrans array. Then clear the array itself.







|







>

>







>
>
>
>







499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
  }
  sqliteFree(db->aVTrans);
  db->nVTrans = 0;
  db->aVTrans = 0;
}

/*
** If argument rc2 is not SQLITE_OK, then return it and do nothing. 
** Otherwise, invoke the xSync method of all virtual tables in the 
** sqlite3.aVTrans array. Return the error code for the first error 
** that occurs, or SQLITE_OK if all xSync operations are successful.
*/
int sqlite3VtabSync(sqlite3 *db, int rc2){
  int i;
  int rc = SQLITE_OK;
  int rcsafety;
  if( rc2!=SQLITE_OK ) return rc2;
  rc = sqlite3SafetyOff(db);
  for(i=0; rc==SQLITE_OK && i<db->nVTrans && db->aVTrans[i]; i++){
    sqlite3_vtab *pVtab = db->aVTrans[i];
    int (*x)(sqlite3_vtab *);
    x = pVtab->pModule->xSync;
    if( x ){
      rc = x(pVtab);
    }
  }
  rcsafety = sqlite3SafetyOn(db);
  if( rc==SQLITE_OK ){
    rc = rcsafety;
  }
  return rc;
}

/*
** Invoke the xRollback method of all virtual tables in the 
** sqlite3.aVTrans array. Then clear the array itself.
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
  Expr *pExpr     /* First argument to the function */
){
  Table *pTab;
  sqlite3_vtab *pVtab;
  sqlite3_module *pMod;
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  void *pArg;
  int rc;
  FuncDef *pNew;

  /* Check to see the left operand is a column in a virtual table */
  if( pExpr==0 ) return pDef;
  if( pExpr->op!=TK_COLUMN ) return pDef;
  pTab = pExpr->pTab;
  if( pTab==0 ) return pDef;
  if( !pTab->isVirtual ) return pDef;
  pVtab = pTab->pVtab;
  assert( pVtab!=0 );
  assert( pVtab->pModule!=0 );
  pMod = pVtab->pModule;
  if( pMod->xFindFunction==0 ) return pDef;
 
  /* Call the xFuncFunction method on the virtual table implementation
  ** to see if the implementation wants to overload this function */
  if( pMod->xFindFunction(pVtab, nArg, pDef->zName, &xFunc, &pArg)==0 ){
    return pDef;
  }







<











|







598
599
600
601
602
603
604

605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
  Expr *pExpr     /* First argument to the function */
){
  Table *pTab;
  sqlite3_vtab *pVtab;
  sqlite3_module *pMod;
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  void *pArg;

  FuncDef *pNew;

  /* Check to see the left operand is a column in a virtual table */
  if( pExpr==0 ) return pDef;
  if( pExpr->op!=TK_COLUMN ) return pDef;
  pTab = pExpr->pTab;
  if( pTab==0 ) return pDef;
  if( !pTab->isVirtual ) return pDef;
  pVtab = pTab->pVtab;
  assert( pVtab!=0 );
  assert( pVtab->pModule!=0 );
  pMod = (sqlite3_module *)pVtab->pModule;
  if( pMod->xFindFunction==0 ) return pDef;
 
  /* Call the xFuncFunction method on the virtual table implementation
  ** to see if the implementation wants to overload this function */
  if( pMod->xFindFunction(pVtab, nArg, pDef->zName, &xFunc, &pArg)==0 ){
    return pDef;
  }
Added test/vtab7.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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
# 2006 July 25
#
# 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 test is reading and writing to the database from within a
# virtual table xSync() callback.
#
# $Id: vtab7.test,v 1.1 2006/07/25 15:14:53 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return
}

# Register the echo module. Code inside the echo module appends elements
# to the global tcl list variable ::echo_module whenever SQLite invokes
# certain module callbacks. This includes the xSync(), xCommit() and 
# xRollback() callbacks. For each of these callback, two elements are
# appended to ::echo_module, as follows:
#
#     Module method        Elements appended to ::echo_module
#     -------------------------------------------------------
#     xSync()              xSync echo($tablename)
#     xCommit()            xCommit echo($tablename)
#     xRollback()          xRollback echo($tablename)
#     -------------------------------------------------------
#
# In each case, $tablename is replaced by the name of the real table (not
# the echo table). By setting up a tcl trace on the ::echo_module variable,
# code in this file arranges for a Tcl script to be executed from within
# the echo module xSync() callback.
#
register_echo_module [sqlite3_connection_pointer db]
trace add variable ::echo_module write echo_module_trace

# This Tcl proc is invoked whenever the ::echo_module variable is written.
#
proc echo_module_trace {args} {
  # Filter out writes to ::echo_module that are not xSync, xCommit or 
  # xRollback callbacks.
  if {[llength $::echo_module] < 2} return
  set x [lindex $::echo_module end-1]
  if {$x ne "xSync" && $x ne "xCommit" && $x ne "xRollback"} return

  regexp {^echo.(.*).$} [lindex $::echo_module end] dummy tablename
  # puts "Ladies and gentlemen, an $x on $tablename!"

  if {[info exists ::callbacks($x,$tablename)]} {
    eval $::callbacks($x,$tablename)
  }
}

# The following tests, vtab7-1.*, test that the trace callback on 
# ::echo_module is providing the expected tcl callbacks.
do_test vtab7-1.1 {
  execsql {
    CREATE TABLE abc(a, b, c);
    CREATE VIRTUAL TABLE abc2 USING echo(abc);
  }
} {}

do_test vtab7-1.2 {
  set ::callbacks(xSync,abc) {incr ::counter}
  set ::counter 0
  execsql {
    INSERT INTO abc2 VALUES(1, 2, 3);
  }
  set ::counter
} {1}

# Write to an existing database table from within an xSync callback.
do_test vtab7-2.1 {
  set ::callbacks(xSync,abc) {
    execsql {INSERT INTO log VALUES('xSync');}
  }
  execsql {
    CREATE TABLE log(msg);
    INSERT INTO abc2 VALUES(4, 5, 6);
    SELECT * FROM log;
  }
} {xSync}
do_test vtab7-2.3 {
  execsql {
    INSERT INTO abc2 VALUES(4, 5, 6);
    SELECT * FROM log;
  }
} {xSync xSync}
do_test vtab7-2.4 {
  execsql {
    INSERT INTO abc2 VALUES(4, 5, 6);
    SELECT * FROM log;
  }
} {xSync xSync xSync}

# Create a database table from within xSync callback.
do_test vtab7-2.5 {
  set ::callbacks(xSync,abc) {
    execsql { CREATE TABLE newtab(d, e, f); }
  }
  execsql {
    INSERT INTO abc2 VALUES(1, 2, 3);
    SELECT name FROM sqlite_master ORDER BY name;
  }
} {abc abc2 log newtab}

# Drop a database table from within xSync callback.
do_test vtab7-2.6 {
  set ::callbacks(xSync,abc) {
    execsql { DROP TABLE newtab }
  }
  execsql {
    INSERT INTO abc2 VALUES(1, 2, 3);
    SELECT name FROM sqlite_master ORDER BY name;
  }
} {abc abc2 log}

# Write to an attached database from xSync().
do_test vtab7-3.1 {
  file delete -force test2.db
  file delete -force test2.db-journal
  execsql {
    ATTACH 'test2.db' AS db2;
    CREATE TABLE db2.stuff(description, shape, color);
  }
  set ::callbacks(xSync,abc) {
    execsql { INSERT INTO db2.stuff VALUES('abc', 'square', 'green'); }
  }
  execsql {
    INSERT INTO abc2 VALUES(1, 2, 3);
    SELECT * from stuff;
  }
} {abc square green}

# UPDATE: The next test passes, but leaks memory. So leave it out.
#
# The following tests test that writing to the database from within
# the xCommit callback causes a misuse error.
# do_test vtab7-4.1 {
#   unset -nocomplain ::callbacks(xSync,abc)
#   set ::callbacks(xCommit,abc) {
#     execsql { INSERT INTO log VALUES('hello') }
#   }
#   catchsql {
#     INSERT INTO abc2 VALUES(1, 2, 3);
#   }
# } {1 {library routine called out of sequence}}


trace remove variable ::echo_module write echo_module_trace
unset -nocomplain ::callbacks

finish_test