Add the sqlite3_update_hook() API. (CVS 2820)

FossilOrigin-Name: 36229018817eebfbfca7a66d2285e4faf7b39845

8 files changed, 194 insertions, 20 deletions

diff --git a/src/delete.c b/src/delete.c
index 75f10d115..259e0f5a8 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** in order to generate code for DELETE FROM statements.
 **
-** $Id: delete.c,v 1.112 2005/12/06 12:52:59 danielk1977 Exp $
+** $Id: delete.c,v 1.113 2005/12/15 15:22:09 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 
@@ -215,6 +215,9 @@ void sqlite3DeleteFrom(
     }
     if( !isView ){
       sqlite3VdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb);
+      if( !pParse->nested ){
+        sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
+      }
       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
         sqlite3VdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb);
       }
@@ -380,6 +383,9 @@ void sqlite3GenerateRowDelete(
   addr = sqlite3VdbeAddOp(v, OP_NotExists, iCur, 0);
   sqlite3GenerateRowIndexDelete(db, v, pTab, iCur, 0);
   sqlite3VdbeAddOp(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0));
+  if( count ){
+    sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
+  }
   sqlite3VdbeJumpHere(v, addr);
 }
 
diff --git a/src/insert.c b/src/insert.c
index af5854df4..bf7c594d3 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle INSERT statements in SQLite.
 **
-** $Id: insert.c,v 1.150 2005/12/06 12:52:59 danielk1977 Exp $
+** $Id: insert.c,v 1.151 2005/12/15 15:22:09 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 
@@ -1079,9 +1079,13 @@ void sqlite3CompleteInsertion(
   if( pParse->nested ){
     pik_flags = 0;
   }else{
-    pik_flags = (OPFLAG_NCHANGE|(isUpdate?0:OPFLAG_LASTROWID));
+    pik_flags = OPFLAG_NCHANGE;
+    pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
   }
   sqlite3VdbeAddOp(v, OP_Insert, base, pik_flags);
+  if( !pParse->nested ){
+    sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
+  }
   
   if( isUpdate && rowidChng ){
     sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
diff --git a/src/main.c b/src/main.c
index 5117200c9..8bc6bb885 100644
--- a/src/main.c
+++ b/src/main.c
@@ -14,7 +14,7 @@
 ** other files are for internal use by SQLite and should not be
 ** accessed by users of the library.
 **
-** $Id: main.c,v 1.309 2005/12/15 03:04:10 drh Exp $
+** $Id: main.c,v 1.310 2005/12/15 15:22:09 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 #include "os.h"
@@ -548,6 +548,19 @@ void *sqlite3_commit_hook(
   return pOld;
 }
 
+/*
+** Register a callback to be invoked each time a row is updated,
+** inserted or deleted using this database connection.
+*/
+void sqlite3_update_hook(
+  sqlite3 *db,              /* Attach the hook to this database */
+  void (*xCallback)(void*,int,char const *,char const *,sqlite_int64),
+  void *pArg                /* Argument to the function */
+){
+  db->xUpdateCallback = xCallback;
+  db->pUpdateArg = pArg;
+}
+
 
 /*
 ** This routine is called to create a connection to a database BTree
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 27551e533..86d75d26d 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -12,7 +12,7 @@
 ** This header file defines the interface that the SQLite library
 ** presents to client programs.
 **
-** @(#) $Id: sqlite.h.in,v 1.145 2005/12/15 10:11:32 danielk1977 Exp $
+** @(#) $Id: sqlite.h.in,v 1.146 2005/12/15 15:22:09 danielk1977 Exp $
 */
 #ifndef _SQLITE3_H_
 #define _SQLITE3_H_
@@ -1291,9 +1291,30 @@ sqlite3 *sqlite3_db_handle(sqlite3_stmt*);
 */
 void sqlite3_soft_heap_limit(int);
 
-
-int sqlite3_set_io_routine(int, void *);
-void *sqlite3_get_io_routine(int);
+/*
+** Register a callback function with the database connection identified by the 
+** first argument to be invoked whenever a row is updated, inserted or deleted.
+** Any callback set by a previous call to this function for the same 
+** database connection is overridden.
+**
+** The second argument is a pointer to the function to invoke when a 
+** row is updated, inserted or deleted. The first argument to the callback is
+** a copy of the third argument to sqlite3_update_hook. The second callback 
+** argument is one of SQLITE_INSERT, SQLITE_DELETE or SQLITE_UPDATE, depending
+** on the operation that caused the callback to be invoked. The third and 
+** fourth arguments to the callback contain pointers to the database and 
+** table name containing the affected row. The final callback parameter is 
+** the rowid of the row. In the case of an update, this is the rowid after 
+** the update takes place.
+**
+** The update hook is not invoked when internal system tables are
+** modified (i.e. sqlite_master and sqlite_sequence).
+*/
+void sqlite3_update_hook(
+  sqlite3*, 
+  void(*)(void *,int ,char const *,char const *,sqlite_int64),
+  void*
+);
 
 /*
 ** Undo the hack that converts floating point types to integer for
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index daf146da0..5cd15242b 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
 *************************************************************************
 ** Internal interface definitions for SQLite.
 **
-** @(#) $Id: sqliteInt.h,v 1.436 2005/12/15 10:11:32 danielk1977 Exp $
+** @(#) $Id: sqliteInt.h,v 1.437 2005/12/15 15:22:09 danielk1977 Exp $
 */
 #ifndef _SQLITEINT_H_
 #define _SQLITEINT_H_
@@ -441,6 +441,8 @@ struct sqlite3 {
   void *pProfileArg;                        /* Argument to profile function */
   void *pCommitArg;             /* Argument to xCommitCallback() */   
   int (*xCommitCallback)(void*);/* Invoked at every commit. */
+  void *pUpdateArg;
+  void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64);
   void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*);
   void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*);
   void *pCollNeededArg;
@@ -1234,6 +1236,7 @@ struct AuthContext {
 */
 #define OPFLAG_NCHANGE   1    /* Set to update db->nChange */
 #define OPFLAG_LASTROWID 2    /* Set to update db->lastRowid */
+#define OPFLAG_ISUPDATE  4    /* This OP_Insert is an sql UPDATE */
 
 /*
  * Each trigger present in the database schema is stored as an instance of
diff --git a/src/tclsqlite.c b/src/tclsqlite.c
index af6c9101a..b862df3f0 100644
--- a/src/tclsqlite.c
+++ b/src/tclsqlite.c
@@ -11,7 +11,7 @@
 *************************************************************************
 ** A TCL Interface to SQLite
 **
-** $Id: tclsqlite.c,v 1.138 2005/12/15 10:11:32 danielk1977 Exp $
+** $Id: tclsqlite.c,v 1.139 2005/12/15 15:22:10 danielk1977 Exp $
 */
 #ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */
 
@@ -99,6 +99,7 @@ struct SqliteDb {
   char *zAuth;               /* The authorization callback routine */
   char *zNull;               /* Text to substitute for an SQL NULL value */
   SqlFunc *pFunc;            /* List of SQL functions */
+  Tcl_Obj *pUpdateHook;      /* Update hook script (if any) */
   SqlCollate *pCollate;      /* List of SQL collation functions */
   int rc;                    /* Return code of most recent sqlite3_exec() */
   Tcl_Obj *pCollateNeeded;   /* Collation needed script */
@@ -210,6 +211,12 @@ static void DbDeleteCmd(void *db){
   if( pDb->zNull ){
     Tcl_Free(pDb->zNull);
   }
+  if( pDb->pUpdateHook ){
+    Tcl_DecrRefCount(pDb->pUpdateHook);
+  }
+  if( pDb->pCollateNeeded ){
+    Tcl_DecrRefCount(pDb->pCollateNeeded);
+  }
   Tcl_Free((char*)pDb);
 }
 
@@ -297,6 +304,29 @@ static int DbCommitHandler(void *cd){
   return 0;
 }
 
+static void DbUpdateHandler(
+  void *p, 
+  int op,
+  const char *zDb, 
+  const char *zTbl, 
+  sqlite_int64 rowid
+){
+  SqliteDb *pDb = (SqliteDb *)p;
+  Tcl_Obj *pCmd;
+
+  assert( pDb->pUpdateHook );
+  assert( op==SQLITE_INSERT || op==SQLITE_UPDATE || op==SQLITE_DELETE );
+
+  pCmd = Tcl_DuplicateObj(pDb->pUpdateHook);
+  Tcl_IncrRefCount(pCmd);
+  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewStringObj(
+    ( (op==SQLITE_INSERT)?"INSERT":(op==SQLITE_UPDATE)?"UPDATE":"DELETE"), -1));
+  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewStringObj(zDb, -1));
+  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewStringObj(zTbl, -1));
+  Tcl_ListObjAppendElement(0, pCmd, Tcl_NewWideIntObj(rowid));
+  Tcl_EvalObjEx(pDb->interp, pCmd, TCL_EVAL_DIRECT);
+}
+
 static void tclCollateNeeded(
   void *pCtx,
   sqlite3 *db,
@@ -625,7 +655,8 @@ static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){
     "nullvalue",          "onecolumn",         "profile",
     "progress",           "rekey",             "soft_heap_limit",
     "timeout",            "total_changes",     "trace",
-    "transaction",        "version",            0                    
+    "transaction",        "update_hook",       "version",
+    0                    
   };
   enum DB_enum {
     DB_AUTHORIZER,        DB_BUSY,             DB_CACHE,
@@ -636,7 +667,7 @@ static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){
     DB_NULLVALUE,         DB_ONECOLUMN,        DB_PROFILE,
     DB_PROGRESS,          DB_REKEY,            DB_SOFT_HEAP_LIMIT,
     DB_TIMEOUT,           DB_TOTAL_CHANGES,    DB_TRACE,
-    DB_TRANSACTION,       DB_VERSION
+    DB_TRANSACTION,       DB_UPDATE_HOOK,      DB_VERSION
   };
   /* don't leave trailing commas on DB_enum, it confuses the AIX xlc compiler */
 
@@ -1839,6 +1870,33 @@ static int DbObjCmd(void *cd, Tcl_Interp *interp, int objc,Tcl_Obj *const*objv){
     break;
   }
 
+  /*
+  **    $db update_hook ?script?
+  */
+  case DB_UPDATE_HOOK: {
+    if( objc!=2 && objc!=3 ){
+       Tcl_WrongNumArgs(interp, 2, objv, "?SCRIPT?");
+       return TCL_ERROR;
+    }
+    if( pDb->pUpdateHook ){
+      Tcl_SetObjResult(interp, pDb->pUpdateHook);
+      if( objc==3 ){
+        Tcl_DecrRefCount(pDb->pUpdateHook);
+        pDb->pUpdateHook = 0;
+      }
+    }
+    if( objc==3 ){
+      if( Tcl_GetCharLength(objv[2])>0 ){
+        pDb->pUpdateHook = objv[2];
+        Tcl_IncrRefCount(pDb->pUpdateHook);
+        sqlite3_update_hook(pDb->db, DbUpdateHandler, pDb);
+      }else{
+        sqlite3_update_hook(pDb->db, 0, 0);
+      }
+    }
+    break;
+  }
+
   /*    $db version
   **
   ** Return the version string for this database.
diff --git a/src/vdbe.c b/src/vdbe.c
index 5370531bd..95e024d20 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -43,7 +43,7 @@
 ** in this file for details.  If in doubt, do not deviate from existing
 ** commenting and indentation practices when changing or adding code.
 **
-** $Id: vdbe.c,v 1.504 2005/12/09 20:02:06 drh Exp $
+** $Id: vdbe.c,v 1.505 2005/12/15 15:22:10 danielk1977 Exp $
 */
 #include "sqliteInt.h"
 #include "os.h"
@@ -160,13 +160,16 @@ static void popStack(Mem **ppTos, int N){
 ** Allocate cursor number iCur.  Return a pointer to it.  Return NULL
 ** if we run out of memory.
 */
-static Cursor *allocateCursor(Vdbe *p, int iCur){
+static Cursor *allocateCursor(Vdbe *p, int iCur, int iDb){
   Cursor *pCx;
   assert( iCur<p->nCursor );
   if( p->apCsr[iCur] ){
     sqlite3VdbeFreeCursor(p->apCsr[iCur]);
   }
   p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) );
+  if( pCx ){
+    pCx->iDb = iDb;
+  }
   return pCx;
 }
 
@@ -2525,7 +2528,7 @@ case OP_OpenWrite: {       /* no-push */
     assert( p2>=2 );
   }
   assert( i>=0 );
-  pCur = allocateCursor(p, i);
+  pCur = allocateCursor(p, i, iDb);
   if( pCur==0 ) goto no_mem;
   pCur->nullRow = 1;
   if( pX==0 ) break;
@@ -2603,7 +2606,7 @@ case OP_OpenVirtual: {       /* no-push */
   int i = pOp->p1;
   Cursor *pCx;
   assert( i>=0 );
-  pCx = allocateCursor(p, i);
+  pCx = allocateCursor(p, i, -1);
   if( pCx==0 ) goto no_mem;
   pCx->nullRow = 1;
   rc = sqlite3BtreeFactory(db, 0, 1, TEMP_PAGES, &pCx->pBt);
@@ -2655,7 +2658,7 @@ case OP_OpenPseudo: {       /* no-push */
   int i = pOp->p1;
   Cursor *pCx;
   assert( i>=0 );
-  pCx = allocateCursor(p, i);
+  pCx = allocateCursor(p, i, -1);
   if( pCx==0 ) goto no_mem;
   pCx->nullRow = 1;
   pCx->pseudoTable = 1;
@@ -3194,7 +3197,7 @@ case OP_NewRowid: {
   break;
 }
 
-/* Opcode: Insert P1 P2 *
+/* Opcode: Insert P1 P2 P3
 **
 ** Write an entry into the table of cursor P1.  A new entry is
 ** created if it doesn't already exist or the data for an existing
@@ -3261,12 +3264,23 @@ case OP_Insert: {         /* no-push */
     pC->rowidIsValid = 0;
     pC->deferredMoveto = 0;
     pC->cacheValid = 0;
+
+    /* Invoke the update-hook if required. */
+    if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p3 ){
+      const char *zDb = db->aDb[pC->iDb].zName;
+      const char *zTbl = pOp->p3;
+      int op = ((pOp->p2 & OPFLAG_ISUPDATE) ? SQLITE_UPDATE : SQLITE_INSERT);
+      assert( pC->isTable );
+      db->xUpdateCallback(db->pUpdateArg, op, zDb, zTbl, iKey);
+      assert( pC->iDb>=0 );
+    }
   }
   popStack(&pTos, 2);
+
   break;
 }
 
-/* Opcode: Delete P1 P2 *
+/* Opcode: Delete P1 P2 P3
 **
 ** Delete the record at which the P1 cursor is currently pointing.
 **
@@ -3287,11 +3301,37 @@ case OP_Delete: {        /* no-push */
   pC = p->apCsr[i];
   assert( pC!=0 );
   if( pC->pCursor!=0 ){
+    i64 iKey;
+
+    /* If the update-hook will be invoked, set iKey to the rowid of the
+    ** row being deleted.
+    */
+    if( db->xUpdateCallback && pOp->p3 ){
+      assert( pC->isTable );
+      if( pC->rowidIsValid ){
+        iKey = pC->lastRowid;
+      }else{
+        rc = sqlite3BtreeKeySize(pC->pCursor, &iKey);
+        if( rc ){
+          goto abort_due_to_error;
+        }
+        iKey = keyToInt(iKey);
+      }
+    }
+
     rc = sqlite3VdbeCursorMoveto(pC);
     if( rc ) goto abort_due_to_error;
     rc = sqlite3BtreeDelete(pC->pCursor);
     pC->nextRowidValid = 0;
     pC->cacheValid = 0;
+
+    /* Invoke the update-hook if required. */
+    if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p3 ){
+      const char *zDb = db->aDb[pC->iDb].zName;
+      const char *zTbl = pOp->p3;
+      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, zTbl, iKey);
+      assert( pC->iDb>=0 );
+    }
   }
   if( pOp->p2 & OPFLAG_NCHANGE ) p->nChange++;
   break;
@@ -3826,6 +3866,35 @@ case OP_Destroy: {
 ** See also: Destroy
 */
 case OP_Clear: {        /* no-push */
+  Btree *pBt = db->aDb[pOp->p2].pBt;
+  if( db->xUpdateCallback && pOp->p3 ){
+    const char *zDb = db->aDb[pOp->p2].zName;
+    const char *zTbl = pOp->p3;
+    BtCursor *pCur = 0;
+    int fin = 0;
+
+    rc = sqlite3BtreeCursor(pBt, pOp->p1, 0, 0, 0, &pCur);
+    if( rc!=SQLITE_OK ){
+      goto abort_due_to_error;
+    }
+    for(
+      rc=sqlite3BtreeFirst(pCur, &fin); 
+      rc==SQLITE_OK && !fin; 
+      rc=sqlite3BtreeNext(pCur, &fin)
+    ){
+      i64 iKey;
+      rc = sqlite3BtreeKeySize(pCur, &iKey);
+      if( rc ){
+        break;
+      }
+      iKey = keyToInt(iKey);
+      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, zTbl, iKey);
+    }
+    sqlite3BtreeCloseCursor(pCur);
+    if( rc!=SQLITE_OK ){
+      goto abort_due_to_error;
+    }
+  }
   rc = sqlite3BtreeClearTable(db->aDb[pOp->p2].pBt, pOp->p1);
   break;
 }
@@ -4344,7 +4413,6 @@ case OP_Expire: {        /* no-push */
   break;
 }
 
-
 /* An other opcode is illegal...
 */
 default: {
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index a4db1f73a..10f3e0660 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -60,6 +60,7 @@ typedef unsigned char Bool;
 */
 struct Cursor {
   BtCursor *pCursor;    /* The cursor structure of the backend */
+  int iDb;              /* Index of cursor database in db->aDb[] (or -1) */
   i64 lastRowid;        /* Last rowid from a Next or NextIdx operation */
   i64 nextRowid;        /* Next rowid returned by OP_NewRowid */
   Bool zeroed;          /* True if zeroed out and ready for reuse */