/ Check-in [36229018]
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 the sqlite3_update_hook() API. (CVS 2820)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 36229018817eebfbfca7a66d2285e4faf7b39845
User & Date: danielk1977 2005-12-15 15:22:09
Context
2005-12-15
22:34
Fix the utf8 to utf16 conversion routine for short strings. Bug introduced by check-in (2817). (CVS 2821) check-in: 4fba2db3 user: drh tags: trunk
15:22
Add the sqlite3_update_hook() API. (CVS 2820) check-in: 36229018 user: danielk1977 tags: trunk
10:50
Move malloc(), free(), realloc() and allocationSize() into the Os vtbl. (CVS 2819) check-in: 81a41f66 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** in order to generate code for DELETE FROM statements.
    14     14   **
    15         -** $Id: delete.c,v 1.112 2005/12/06 12:52:59 danielk1977 Exp $
           15  +** $Id: delete.c,v 1.113 2005/12/15 15:22:09 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Look up every table that is named in pSrc.  If any table is not found,
    21     21   ** add an error message to pParse->zErrMsg and return NULL.  If all tables
    22     22   ** are found, return a pointer to the last table.
................................................................................
   211    211         addr = sqlite3VdbeAddOp(v, OP_AddImm, 1, 0);
   212    212         sqlite3VdbeAddOp(v, OP_Next, iCur, addr);
   213    213         sqlite3VdbeResolveLabel(v, endOfLoop);
   214    214         sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
   215    215       }
   216    216       if( !isView ){
   217    217         sqlite3VdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb);
          218  +      if( !pParse->nested ){
          219  +        sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
          220  +      }
   218    221         for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   219    222           sqlite3VdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb);
   220    223         }
   221    224       }
   222    225     }
   223    226   
   224    227     /* The usual case: There is a WHERE clause so we have to scan through
................................................................................
   376    379     int iCur,          /* Cursor number for the table */
   377    380     int count          /* Increment the row change counter */
   378    381   ){
   379    382     int addr;
   380    383     addr = sqlite3VdbeAddOp(v, OP_NotExists, iCur, 0);
   381    384     sqlite3GenerateRowIndexDelete(db, v, pTab, iCur, 0);
   382    385     sqlite3VdbeAddOp(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0));
          386  +  if( count ){
          387  +    sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
          388  +  }
   383    389     sqlite3VdbeJumpHere(v, addr);
   384    390   }
   385    391   
   386    392   /*
   387    393   ** This routine generates VDBE code that causes the deletion of all
   388    394   ** index entries associated with a single row of a single table.
   389    395   **

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.150 2005/12/06 12:52:59 danielk1977 Exp $
           15  +** $Id: insert.c,v 1.151 2005/12/15 15:22:09 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Set P3 of the most recently inserted opcode to a column affinity
    21     21   ** string for index pIdx. A column affinity string has one character
    22     22   ** for each column in the table, according to the affinity of the column:
................................................................................
  1075   1075       sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
  1076   1076       sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0);
  1077   1077     }
  1078   1078   #endif
  1079   1079     if( pParse->nested ){
  1080   1080       pik_flags = 0;
  1081   1081     }else{
  1082         -    pik_flags = (OPFLAG_NCHANGE|(isUpdate?0:OPFLAG_LASTROWID));
         1082  +    pik_flags = OPFLAG_NCHANGE;
         1083  +    pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
  1083   1084     }
  1084   1085     sqlite3VdbeAddOp(v, OP_Insert, base, pik_flags);
         1086  +  if( !pParse->nested ){
         1087  +    sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
         1088  +  }
  1085   1089     
  1086   1090     if( isUpdate && rowidChng ){
  1087   1091       sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
  1088   1092     }
  1089   1093   }
  1090   1094   
  1091   1095   /*

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.309 2005/12/15 03:04:10 drh Exp $
           17  +** $Id: main.c,v 1.310 2005/12/15 15:22:09 danielk1977 Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   #include <ctype.h>
    22     22   
    23     23   /*
    24     24   ** The following constant value is used by the SQLITE_BIGENDIAN and
................................................................................
   543    543     void *pArg                /* Argument to the function */
   544    544   ){
   545    545     void *pOld = db->pCommitArg;
   546    546     db->xCommitCallback = xCallback;
   547    547     db->pCommitArg = pArg;
   548    548     return pOld;
   549    549   }
          550  +
          551  +/*
          552  +** Register a callback to be invoked each time a row is updated,
          553  +** inserted or deleted using this database connection.
          554  +*/
          555  +void sqlite3_update_hook(
          556  +  sqlite3 *db,              /* Attach the hook to this database */
          557  +  void (*xCallback)(void*,int,char const *,char const *,sqlite_int64),
          558  +  void *pArg                /* Argument to the function */
          559  +){
          560  +  db->xUpdateCallback = xCallback;
          561  +  db->pUpdateArg = pArg;
          562  +}
   550    563   
   551    564   
   552    565   /*
   553    566   ** This routine is called to create a connection to a database BTree
   554    567   ** driver.  If zFilename is the name of a file, then that file is
   555    568   ** opened and used.  If zFilename is the magic name ":memory:" then
   556    569   ** the database is stored in memory (and is thus forgotten as soon as

Changes to src/sqlite.h.in.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This header file defines the interface that the SQLite library
    13     13   ** presents to client programs.
    14     14   **
    15         -** @(#) $Id: sqlite.h.in,v 1.145 2005/12/15 10:11:32 danielk1977 Exp $
           15  +** @(#) $Id: sqlite.h.in,v 1.146 2005/12/15 15:22:09 danielk1977 Exp $
    16     16   */
    17     17   #ifndef _SQLITE3_H_
    18     18   #define _SQLITE3_H_
    19     19   #include <stdarg.h>     /* Needed for the definition of va_list */
    20     20   
    21     21   /*
    22     22   ** Make sure we can call this stuff from C++.
................................................................................
  1287   1287   ** allocated anyway and the current operation proceeds.
  1288   1288   **
  1289   1289   ** This function is only available if the library was compiled without the 
  1290   1290   ** SQLITE_OMIT_SOFTHEAPLIMIT option set.
  1291   1291   */
  1292   1292   void sqlite3_soft_heap_limit(int);
  1293   1293   
  1294         -
  1295         -int sqlite3_set_io_routine(int, void *);
  1296         -void *sqlite3_get_io_routine(int);
         1294  +/*
         1295  +** Register a callback function with the database connection identified by the 
         1296  +** first argument to be invoked whenever a row is updated, inserted or deleted.
         1297  +** Any callback set by a previous call to this function for the same 
         1298  +** database connection is overridden.
         1299  +**
         1300  +** The second argument is a pointer to the function to invoke when a 
         1301  +** row is updated, inserted or deleted. The first argument to the callback is
         1302  +** a copy of the third argument to sqlite3_update_hook. The second callback 
         1303  +** argument is one of SQLITE_INSERT, SQLITE_DELETE or SQLITE_UPDATE, depending
         1304  +** on the operation that caused the callback to be invoked. The third and 
         1305  +** fourth arguments to the callback contain pointers to the database and 
         1306  +** table name containing the affected row. The final callback parameter is 
         1307  +** the rowid of the row. In the case of an update, this is the rowid after 
         1308  +** the update takes place.
         1309  +**
         1310  +** The update hook is not invoked when internal system tables are
         1311  +** modified (i.e. sqlite_master and sqlite_sequence).
         1312  +*/
         1313  +void sqlite3_update_hook(
         1314  +  sqlite3*, 
         1315  +  void(*)(void *,int ,char const *,char const *,sqlite_int64),
         1316  +  void*
         1317  +);
  1297   1318   
  1298   1319   /*
  1299   1320   ** Undo the hack that converts floating point types to integer for
  1300   1321   ** builds on processors without floating point support.
  1301   1322   */
  1302   1323   #ifdef SQLITE_OMIT_FLOATING_POINT
  1303   1324   # undef double
  1304   1325   #endif
  1305   1326   
  1306   1327   #ifdef __cplusplus
  1307   1328   }  /* End of the 'extern "C"' block */
  1308   1329   #endif
  1309   1330   #endif

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.436 2005/12/15 10:11:32 danielk1977 Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.437 2005/12/15 15:22:09 danielk1977 Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
    21     21   ** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
   437    437     int activeVdbeCnt;            /* Number of vdbes currently executing */
   438    438     void (*xTrace)(void*,const char*);        /* Trace function */
   439    439     void *pTraceArg;                          /* Argument to the trace function */
   440    440     void (*xProfile)(void*,const char*,u64);  /* Profiling function */
   441    441     void *pProfileArg;                        /* Argument to profile function */
   442    442     void *pCommitArg;             /* Argument to xCommitCallback() */   
   443    443     int (*xCommitCallback)(void*);/* Invoked at every commit. */
          444  +  void *pUpdateArg;
          445  +  void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64);
   444    446     void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*);
   445    447     void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*);
   446    448     void *pCollNeededArg;
   447    449     sqlite3_value *pErr;          /* Most recent error message */
   448    450     char *zErrMsg;                /* Most recent error message (UTF-8 encoded) */
   449    451     char *zErrMsg16;              /* Most recent error message (UTF-16 encoded) */
   450    452   #ifndef SQLITE_OMIT_AUTHORIZATION
................................................................................
  1230   1232   };
  1231   1233   
  1232   1234   /*
  1233   1235   ** Bitfield flags for P2 value in OP_Insert and OP_Delete
  1234   1236   */
  1235   1237   #define OPFLAG_NCHANGE   1    /* Set to update db->nChange */
  1236   1238   #define OPFLAG_LASTROWID 2    /* Set to update db->lastRowid */
         1239  +#define OPFLAG_ISUPDATE  4    /* This OP_Insert is an sql UPDATE */
  1237   1240   
  1238   1241   /*
  1239   1242    * Each trigger present in the database schema is stored as an instance of
  1240   1243    * struct Trigger. 
  1241   1244    *
  1242   1245    * Pointers to instances of struct Trigger are stored in two ways.
  1243   1246    * 1. In the "trigHash" hash table (part of the sqlite3* that represents the 

Changes to src/tclsqlite.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** A TCL Interface to SQLite
    13     13   **
    14         -** $Id: tclsqlite.c,v 1.138 2005/12/15 10:11:32 danielk1977 Exp $
           14  +** $Id: tclsqlite.c,v 1.139 2005/12/15 15:22:10 danielk1977 Exp $
    15     15   */
    16     16   #ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */
    17     17   
    18     18   #include "sqliteInt.h"
    19     19   #include "hash.h"
    20     20   #include "tcl.h"
    21     21   #include <stdlib.h>
................................................................................
    95     95     char *zCommit;             /* The commit hook callback routine */
    96     96     char *zTrace;              /* The trace callback routine */
    97     97     char *zProfile;            /* The profile callback routine */
    98     98     char *zProgress;           /* The progress callback routine */
    99     99     char *zAuth;               /* The authorization callback routine */
   100    100     char *zNull;               /* Text to substitute for an SQL NULL value */
   101    101     SqlFunc *pFunc;            /* List of SQL functions */
          102  +  Tcl_Obj *pUpdateHook;      /* Update hook script (if any) */
   102    103     SqlCollate *pCollate;      /* List of SQL collation functions */
   103    104     int rc;                    /* Return code of most recent sqlite3_exec() */
   104    105     Tcl_Obj *pCollateNeeded;   /* Collation needed script */
   105    106     SqlPreparedStmt *stmtList; /* List of prepared statements*/
   106    107     SqlPreparedStmt *stmtLast; /* Last statement in the list */
   107    108     int maxStmt;               /* The next maximum number of stmtList */
   108    109     int nStmt;                 /* Number of statements in stmtList */
................................................................................
   206    207     }
   207    208     if( pDb->zAuth ){
   208    209       Tcl_Free(pDb->zAuth);
   209    210     }
   210    211     if( pDb->zNull ){
   211    212       Tcl_Free(pDb->zNull);
   212    213     }
          214  +  if( pDb->pUpdateHook ){
          215  +    Tcl_DecrRefCount(pDb->pUpdateHook);
          216  +  }
          217  +  if( pDb->pCollateNeeded ){
          218  +    Tcl_DecrRefCount(pDb->pCollateNeeded);
          219  +  }
   213    220     Tcl_Free((char*)pDb);
   214    221   }
   215    222   
   216    223   /*
   217    224   ** This routine is called when a database file is locked while trying
   218    225   ** to execute SQL.
   219    226   */
................................................................................
   292    299   
   293    300     rc = Tcl_Eval(pDb->interp, pDb->zCommit);
   294    301     if( rc!=TCL_OK || atoi(Tcl_GetStringResult(pDb->interp)) ){
   295    302       return 1;
   296    303     }
   297    304     return 0;
   298    305   }
          306  +
          307  +static void DbUpdateHandler(
          308  +  void *p, 
          309  +  int op,
          310  +  const char *zDb, 
          311  +  const char *zTbl, 
          312  +  sqlite_int64 rowid
          313  +){
          314  +  SqliteDb *pDb = (SqliteDb *)p;
          315  +  Tcl_Obj *pCmd;
          316  +
          317  +  assert( pDb->pUpdateHook );
          318  +  assert( op==SQLITE_INSERT || op==SQLITE_UPDATE || op==SQLITE_DELETE );
          319  +
          320  +  pCmd = Tcl_DuplicateObj(pDb->pUpdateHook);
          321  +  Tcl_IncrRefCount(pCmd);
          322  +  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewStringObj(
          323  +    ( (op==SQLITE_INSERT)?"INSERT":(op==SQLITE_UPDATE)?"UPDATE":"DELETE"), -1));
          324  +  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewStringObj(zDb, -1));
          325  +  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewStringObj(zTbl, -1));
          326  +  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewWideIntObj(rowid));
          327  +  Tcl_EvalObjEx(pDb->interp, pCmd, TCL_EVAL_DIRECT);
          328  +}
   299    329   
   300    330   static void tclCollateNeeded(
   301    331     void *pCtx,
   302    332     sqlite3 *db,
   303    333     int enc,
   304    334     const char *zName
   305    335   ){
................................................................................
   621    651       "changes",            "close",             "collate",
   622    652       "collation_needed",   "commit_hook",       "complete",
   623    653       "copy",               "errorcode",         "eval",
   624    654       "exists",             "function",          "last_insert_rowid",
   625    655       "nullvalue",          "onecolumn",         "profile",
   626    656       "progress",           "rekey",             "soft_heap_limit",
   627    657       "timeout",            "total_changes",     "trace",
   628         -    "transaction",        "version",            0                    
          658  +    "transaction",        "update_hook",       "version",
          659  +    0                    
   629    660     };
   630    661     enum DB_enum {
   631    662       DB_AUTHORIZER,        DB_BUSY,             DB_CACHE,
   632    663       DB_CHANGES,           DB_CLOSE,            DB_COLLATE,
   633    664       DB_COLLATION_NEEDED,  DB_COMMIT_HOOK,      DB_COMPLETE,
   634    665       DB_COPY,              DB_ERRORCODE,        DB_EVAL,
   635    666       DB_EXISTS,            DB_FUNCTION,         DB_LAST_INSERT_ROWID,
   636    667       DB_NULLVALUE,         DB_ONECOLUMN,        DB_PROFILE,
   637    668       DB_PROGRESS,          DB_REKEY,            DB_SOFT_HEAP_LIMIT,
   638    669       DB_TIMEOUT,           DB_TOTAL_CHANGES,    DB_TRACE,
   639         -    DB_TRANSACTION,       DB_VERSION
          670  +    DB_TRANSACTION,       DB_UPDATE_HOOK,      DB_VERSION
   640    671     };
   641    672     /* don't leave trailing commas on DB_enum, it confuses the AIX xlc compiler */
   642    673   
   643    674     if( objc<2 ){
   644    675       Tcl_WrongNumArgs(interp, 1, objv, "SUBCOMMAND ...");
   645    676       return TCL_ERROR;
   646    677     }
................................................................................
  1831   1862         const char *zEnd;
  1832   1863         if( rc==TCL_ERROR ){
  1833   1864           zEnd = "ROLLBACK";
  1834   1865         } else {
  1835   1866           zEnd = "COMMIT";
  1836   1867         }
  1837   1868         sqlite3_exec(pDb->db, zEnd, 0, 0, 0);
         1869  +    }
         1870  +    break;
         1871  +  }
         1872  +
         1873  +  /*
         1874  +  **    $db update_hook ?script?
         1875  +  */
         1876  +  case DB_UPDATE_HOOK: {
         1877  +    if( objc!=2 && objc!=3 ){
         1878  +       Tcl_WrongNumArgs(interp, 2, objv, "?SCRIPT?");
         1879  +       return TCL_ERROR;
         1880  +    }
         1881  +    if( pDb->pUpdateHook ){
         1882  +      Tcl_SetObjResult(interp, pDb->pUpdateHook);
         1883  +      if( objc==3 ){
         1884  +        Tcl_DecrRefCount(pDb->pUpdateHook);
         1885  +        pDb->pUpdateHook = 0;
         1886  +      }
         1887  +    }
         1888  +    if( objc==3 ){
         1889  +      if( Tcl_GetCharLength(objv[2])>0 ){
         1890  +        pDb->pUpdateHook = objv[2];
         1891  +        Tcl_IncrRefCount(pDb->pUpdateHook);
         1892  +        sqlite3_update_hook(pDb->db, DbUpdateHandler, pDb);
         1893  +      }else{
         1894  +        sqlite3_update_hook(pDb->db, 0, 0);
         1895  +      }
  1838   1896       }
  1839   1897       break;
  1840   1898     }
  1841   1899   
  1842   1900     /*    $db version
  1843   1901     **
  1844   1902     ** Return the version string for this database.

Changes to src/vdbe.c.

    39     39   **
    40     40   ** Various scripts scan this source file in order to generate HTML
    41     41   ** documentation, headers files, or other derived files.  The formatting
    42     42   ** of the code in this file is, therefore, important.  See other comments
    43     43   ** in this file for details.  If in doubt, do not deviate from existing
    44     44   ** commenting and indentation practices when changing or adding code.
    45     45   **
    46         -** $Id: vdbe.c,v 1.504 2005/12/09 20:02:06 drh Exp $
           46  +** $Id: vdbe.c,v 1.505 2005/12/15 15:22:10 danielk1977 Exp $
    47     47   */
    48     48   #include "sqliteInt.h"
    49     49   #include "os.h"
    50     50   #include <ctype.h>
    51     51   #include "vdbeInt.h"
    52     52   
    53     53   /*
................................................................................
   156    156     *ppTos = pTos;
   157    157   }
   158    158   
   159    159   /*
   160    160   ** Allocate cursor number iCur.  Return a pointer to it.  Return NULL
   161    161   ** if we run out of memory.
   162    162   */
   163         -static Cursor *allocateCursor(Vdbe *p, int iCur){
          163  +static Cursor *allocateCursor(Vdbe *p, int iCur, int iDb){
   164    164     Cursor *pCx;
   165    165     assert( iCur<p->nCursor );
   166    166     if( p->apCsr[iCur] ){
   167    167       sqlite3VdbeFreeCursor(p->apCsr[iCur]);
   168    168     }
   169    169     p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) );
          170  +  if( pCx ){
          171  +    pCx->iDb = iDb;
          172  +  }
   170    173     return pCx;
   171    174   }
   172    175   
   173    176   /*
   174    177   ** Processing is determine by the affinity parameter:
   175    178   **
   176    179   ** SQLITE_AFF_INTEGER:
................................................................................
  2521   2524       sqlite3VdbeMemIntegerify(pTos);
  2522   2525       p2 = pTos->i;
  2523   2526       assert( (pTos->flags & MEM_Dyn)==0 );
  2524   2527       pTos--;
  2525   2528       assert( p2>=2 );
  2526   2529     }
  2527   2530     assert( i>=0 );
  2528         -  pCur = allocateCursor(p, i);
         2531  +  pCur = allocateCursor(p, i, iDb);
  2529   2532     if( pCur==0 ) goto no_mem;
  2530   2533     pCur->nullRow = 1;
  2531   2534     if( pX==0 ) break;
  2532   2535     /* We always provide a key comparison function.  If the table being
  2533   2536     ** opened is of type INTKEY, the comparision function will be ignored. */
  2534   2537     rc = sqlite3BtreeCursor(pX, p2, wrFlag,
  2535   2538              sqlite3VdbeRecordCompare, pOp->p3,
................................................................................
  2599   2602   ** if P3 is not 0.  If P3 is not NULL, it points to a KeyInfo structure
  2600   2603   ** that defines the format of keys in the index.
  2601   2604   */
  2602   2605   case OP_OpenVirtual: {       /* no-push */
  2603   2606     int i = pOp->p1;
  2604   2607     Cursor *pCx;
  2605   2608     assert( i>=0 );
  2606         -  pCx = allocateCursor(p, i);
         2609  +  pCx = allocateCursor(p, i, -1);
  2607   2610     if( pCx==0 ) goto no_mem;
  2608   2611     pCx->nullRow = 1;
  2609   2612     rc = sqlite3BtreeFactory(db, 0, 1, TEMP_PAGES, &pCx->pBt);
  2610   2613     if( rc==SQLITE_OK ){
  2611   2614       rc = sqlite3BtreeBeginTrans(pCx->pBt, 1);
  2612   2615     }
  2613   2616     if( rc==SQLITE_OK ){
................................................................................
  2651   2654   ** A pseudo-table created by this opcode is useful for holding the
  2652   2655   ** NEW or OLD tables in a trigger.
  2653   2656   */
  2654   2657   case OP_OpenPseudo: {       /* no-push */
  2655   2658     int i = pOp->p1;
  2656   2659     Cursor *pCx;
  2657   2660     assert( i>=0 );
  2658         -  pCx = allocateCursor(p, i);
         2661  +  pCx = allocateCursor(p, i, -1);
  2659   2662     if( pCx==0 ) goto no_mem;
  2660   2663     pCx->nullRow = 1;
  2661   2664     pCx->pseudoTable = 1;
  2662   2665     pCx->pIncrKey = &pCx->bogusIncrKey;
  2663   2666     pCx->isTable = 1;
  2664   2667     pCx->isIndex = 0;
  2665   2668     break;
................................................................................
  3190   3193     }
  3191   3194     pTos++;
  3192   3195     pTos->i = v;
  3193   3196     pTos->flags = MEM_Int;
  3194   3197     break;
  3195   3198   }
  3196   3199   
  3197         -/* Opcode: Insert P1 P2 *
         3200  +/* Opcode: Insert P1 P2 P3
  3198   3201   **
  3199   3202   ** Write an entry into the table of cursor P1.  A new entry is
  3200   3203   ** created if it doesn't already exist or the data for an existing
  3201   3204   ** entry is overwritten.  The data is the value on the top of the
  3202   3205   ** stack.  The key is the next value down on the stack.  The key must
  3203   3206   ** be an integer.  The stack is popped twice by this instruction.
  3204   3207   **
................................................................................
  3257   3260   #ifndef SQLITE_OMIT_TRIGGER
  3258   3261       }
  3259   3262   #endif
  3260   3263       
  3261   3264       pC->rowidIsValid = 0;
  3262   3265       pC->deferredMoveto = 0;
  3263   3266       pC->cacheValid = 0;
         3267  +
         3268  +    /* Invoke the update-hook if required. */
         3269  +    if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p3 ){
         3270  +      const char *zDb = db->aDb[pC->iDb].zName;
         3271  +      const char *zTbl = pOp->p3;
         3272  +      int op = ((pOp->p2 & OPFLAG_ISUPDATE) ? SQLITE_UPDATE : SQLITE_INSERT);
         3273  +      assert( pC->isTable );
         3274  +      db->xUpdateCallback(db->pUpdateArg, op, zDb, zTbl, iKey);
         3275  +      assert( pC->iDb>=0 );
         3276  +    }
  3264   3277     }
  3265   3278     popStack(&pTos, 2);
         3279  +
  3266   3280     break;
  3267   3281   }
  3268   3282   
  3269         -/* Opcode: Delete P1 P2 *
         3283  +/* Opcode: Delete P1 P2 P3
  3270   3284   **
  3271   3285   ** Delete the record at which the P1 cursor is currently pointing.
  3272   3286   **
  3273   3287   ** The cursor will be left pointing at either the next or the previous
  3274   3288   ** record in the table. If it is left pointing at the next record, then
  3275   3289   ** the next Next instruction will be a no-op.  Hence it is OK to delete
  3276   3290   ** a record from within an Next loop.
................................................................................
  3283   3297   case OP_Delete: {        /* no-push */
  3284   3298     int i = pOp->p1;
  3285   3299     Cursor *pC;
  3286   3300     assert( i>=0 && i<p->nCursor );
  3287   3301     pC = p->apCsr[i];
  3288   3302     assert( pC!=0 );
  3289   3303     if( pC->pCursor!=0 ){
         3304  +    i64 iKey;
         3305  +
         3306  +    /* If the update-hook will be invoked, set iKey to the rowid of the
         3307  +    ** row being deleted.
         3308  +    */
         3309  +    if( db->xUpdateCallback && pOp->p3 ){
         3310  +      assert( pC->isTable );
         3311  +      if( pC->rowidIsValid ){
         3312  +        iKey = pC->lastRowid;
         3313  +      }else{
         3314  +        rc = sqlite3BtreeKeySize(pC->pCursor, &iKey);
         3315  +        if( rc ){
         3316  +          goto abort_due_to_error;
         3317  +        }
         3318  +        iKey = keyToInt(iKey);
         3319  +      }
         3320  +    }
         3321  +
  3290   3322       rc = sqlite3VdbeCursorMoveto(pC);
  3291   3323       if( rc ) goto abort_due_to_error;
  3292   3324       rc = sqlite3BtreeDelete(pC->pCursor);
  3293   3325       pC->nextRowidValid = 0;
  3294   3326       pC->cacheValid = 0;
         3327  +
         3328  +    /* Invoke the update-hook if required. */
         3329  +    if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p3 ){
         3330  +      const char *zDb = db->aDb[pC->iDb].zName;
         3331  +      const char *zTbl = pOp->p3;
         3332  +      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, zTbl, iKey);
         3333  +      assert( pC->iDb>=0 );
         3334  +    }
  3295   3335     }
  3296   3336     if( pOp->p2 & OPFLAG_NCHANGE ) p->nChange++;
  3297   3337     break;
  3298   3338   }
  3299   3339   
  3300   3340   /* Opcode: ResetCount P1 * *
  3301   3341   **
................................................................................
  3822   3862   ** The table being clear is in the main database file if P2==0.  If
  3823   3863   ** P2==1 then the table to be clear is in the auxiliary database file
  3824   3864   ** that is used to store tables create using CREATE TEMPORARY TABLE.
  3825   3865   **
  3826   3866   ** See also: Destroy
  3827   3867   */
  3828   3868   case OP_Clear: {        /* no-push */
         3869  +  Btree *pBt = db->aDb[pOp->p2].pBt;
         3870  +  if( db->xUpdateCallback && pOp->p3 ){
         3871  +    const char *zDb = db->aDb[pOp->p2].zName;
         3872  +    const char *zTbl = pOp->p3;
         3873  +    BtCursor *pCur = 0;
         3874  +    int fin = 0;
         3875  +
         3876  +    rc = sqlite3BtreeCursor(pBt, pOp->p1, 0, 0, 0, &pCur);
         3877  +    if( rc!=SQLITE_OK ){
         3878  +      goto abort_due_to_error;
         3879  +    }
         3880  +    for(
         3881  +      rc=sqlite3BtreeFirst(pCur, &fin); 
         3882  +      rc==SQLITE_OK && !fin; 
         3883  +      rc=sqlite3BtreeNext(pCur, &fin)
         3884  +    ){
         3885  +      i64 iKey;
         3886  +      rc = sqlite3BtreeKeySize(pCur, &iKey);
         3887  +      if( rc ){
         3888  +        break;
         3889  +      }
         3890  +      iKey = keyToInt(iKey);
         3891  +      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, zTbl, iKey);
         3892  +    }
         3893  +    sqlite3BtreeCloseCursor(pCur);
         3894  +    if( rc!=SQLITE_OK ){
         3895  +      goto abort_due_to_error;
         3896  +    }
         3897  +  }
  3829   3898     rc = sqlite3BtreeClearTable(db->aDb[pOp->p2].pBt, pOp->p1);
  3830   3899     break;
  3831   3900   }
  3832   3901   
  3833   3902   /* Opcode: CreateTable P1 * *
  3834   3903   **
  3835   3904   ** Allocate a new table in the main database file if P2==0 or in the
................................................................................
  4339   4408     if( !pOp->p1 ){
  4340   4409       sqlite3ExpirePreparedStatements(db);
  4341   4410     }else{
  4342   4411       p->expired = 1;
  4343   4412     }
  4344   4413     break;
  4345   4414   }
  4346         -
  4347   4415   
  4348   4416   /* An other opcode is illegal...
  4349   4417   */
  4350   4418   default: {
  4351   4419     assert( 0 );
  4352   4420     break;
  4353   4421   }

Changes to src/vdbeInt.h.

    56     56   ** If the Cursor.isTriggerRow flag is set it means that this cursor is
    57     57   ** really a single row that represents the NEW or OLD pseudo-table of
    58     58   ** a row trigger.  The data for the row is stored in Cursor.pData and
    59     59   ** the rowid is in Cursor.iKey.
    60     60   */
    61     61   struct Cursor {
    62     62     BtCursor *pCursor;    /* The cursor structure of the backend */
           63  +  int iDb;              /* Index of cursor database in db->aDb[] (or -1) */
    63     64     i64 lastRowid;        /* Last rowid from a Next or NextIdx operation */
    64     65     i64 nextRowid;        /* Next rowid returned by OP_NewRowid */
    65     66     Bool zeroed;          /* True if zeroed out and ready for reuse */
    66     67     Bool rowidIsValid;    /* True if lastRowid is valid */
    67     68     Bool atFirst;         /* True if pointing to first entry */
    68     69     Bool useRandomRowid;  /* Generate new record numbers semi-randomly */
    69     70     Bool nullRow;         /* True if pointing to a row with no data */

Changes to test/hook.test.

    10     10   #***********************************************************************
    11     11   # This file implements regression tests for TCL interface to the
    12     12   # SQLite library. 
    13     13   #
    14     14   # The focus of the tests in this file is the  following interface:
    15     15   #
    16     16   #      sqlite_commit_hook
           17  +#      sqlite_update_hook (tests hook-4 onwards)
    17     18   #
    18         -# $Id: hook.test,v 1.5 2004/06/29 12:39:08 drh Exp $
           19  +# $Id: hook.test,v 1.6 2005/12/15 15:22:10 danielk1977 Exp $
    19     20   
    20     21   set testdir [file dirname $argv0]
    21     22   source $testdir/tester.tcl
    22     23   
    23     24   do_test hook-1.2 {
    24     25     db commit_hook
    25     26   } {}
................................................................................
    85     86     db commit_hook {}
    86     87     set ::commit_cnt {}
    87     88     execsql {
    88     89       INSERT INTO t2 VALUES(7,8);
    89     90     }
    90     91     set ::commit_cnt
    91     92   } {}
           93  +
           94  +# Very simple tests. Test that the update hook is invoked correctly for INSERT,
           95  +# DELETE and UPDATE statements, including DELETE statements with no WHERE
           96  +# clause.
           97  +#
           98  +do_test hook-4.1 {
           99  +  catchsql {
          100  +    DROP TABLE t1;
          101  +  }
          102  +  execsql {
          103  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          104  +    INSERT INTO t1 VALUES(1, 'one');
          105  +    INSERT INTO t1 VALUES(2, 'two');
          106  +    INSERT INTO t1 VALUES(3, 'three');
          107  +  }
          108  +  db update_hook [list lappend ::update_hook]
          109  +} {}
          110  +do_test hook-4.2 {
          111  +  execsql {
          112  +    INSERT INTO t1 VALUES(4, 'four');
          113  +    DELETE FROM t1 WHERE b = 'two';
          114  +    UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
          115  +    DELETE FROM t1;
          116  +  }
          117  +  set ::update_hook
          118  +} [list \
          119  +    INSERT main t1 4 \
          120  +    DELETE main t1 2 \
          121  +    UPDATE main t1 1 \
          122  +    UPDATE main t1 3 \
          123  +    DELETE main t1 1 \
          124  +    DELETE main t1 3 \
          125  +    DELETE main t1 4 \
          126  +]
          127  +
          128  +# Check that the update-hook is invoked for rows modified by trigger
          129  +# bodies.
          130  +#
          131  +set ::update_hook {}
          132  +do_test hook-5.1 {
          133  +  catchsql {
          134  +    DROP TABLE t2;
          135  +  }
          136  +  execsql {
          137  +    CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
          138  +    CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
          139  +      INSERT INTO t2 VALUES(new.a, new.b);
          140  +      UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
          141  +      DELETE FROM t2 WHERE new.a = c;
          142  +    END;
          143  +  }
          144  +} {}
          145  +do_test hook-5.2 {
          146  +  execsql {
          147  +    INSERT INTO t1 VALUES(1, 'one');
          148  +    INSERT INTO t1 VALUES(2, 'two');
          149  +  }
          150  +  set ::update_hook
          151  +} [list \
          152  +    INSERT main t1 1 \
          153  +    INSERT main t2 1 \
          154  +    UPDATE main t2 1 \
          155  +    DELETE main t2 1 \
          156  +    INSERT main t1 2 \
          157  +    INSERT main t2 2 \
          158  +    UPDATE main t2 2 \
          159  +    DELETE main t2 2 \
          160  +]
          161  +
          162  +set ::update_hook {}
          163  +do_test hook-6.1 {
          164  +  file delete -force test2.db
          165  +  execsql {
          166  +    ATTACH 'test2.db' AS aux;
          167  +    CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
          168  +    INSERT INTO aux.t3 SELECT * FROM t1;
          169  +    UPDATE t3 SET b = 'two or so' WHERE a = 2;
          170  +    DELETE FROM t3;
          171  +  }
          172  +  set ::update_hook
          173  +} [list \
          174  +    INSERT aux t3 1 \
          175  +    INSERT aux t3 2 \
          176  +    UPDATE aux t3 2 \
          177  +    DELETE aux t3 1 \
          178  +    DELETE aux t3 2 \
          179  +]
          180  +
          181  +# Do some sorting, grouping, compound queries, population and depopulation 
          182  +# of indices, to make sure the update-hook is not invoked incorrectly.
          183  +#
          184  +set ::update_hook {}
          185  +do_test hook-7.1 {
          186  +  execsql {
          187  +    DROP TRIGGER t1_trigger;
          188  +    CREATE INDEX t1_i ON t1(b);
          189  +    INSERT INTO t1 VALUES(3, 'three');
          190  +    UPDATE t1 SET b = '';
          191  +    DELETE FROM t1 WHERE a > 1;
          192  +  }
          193  +  set ::update_hook
          194  +} [list \
          195  +    INSERT main t1 3 \
          196  +    UPDATE main t1 1 \
          197  +    UPDATE main t1 2 \
          198  +    UPDATE main t1 3 \
          199  +    DELETE main t1 2 \
          200  +    DELETE main t1 3 \
          201  +]
          202  +set ::update_hook {}
          203  +do_test hook-7.2 {
          204  +  execsql {
          205  +    SELECT * FROM t1 UNION SELECT * FROM t3;
          206  +    SELECT * FROM t1 UNION ALL SELECT * FROM t3;
          207  +    SELECT * FROM t1 INTERSECT SELECT * FROM t3;
          208  +    SELECT * FROM t1 EXCEPT SELECT * FROM t3;
          209  +    SELECT * FROM t1 ORDER BY b;
          210  +    SELECT * FROM t1 GROUP BY b;
          211  +  }
          212  +  set ::update_hook
          213  +} [list]
    92    214   
    93    215   finish_test
          216  +