/ Check-in [526545c4]
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_preupdate_new() API, for retrieving the new.* values from within a pre-update callback.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 526545c49f64d9063d1b888cfc14ece62fa3c13c
User & Date: dan 2011-03-16 19:59:19
Context
2011-03-17
19:20
Change to the session module to use user-defined primary keys instead of rowids when collecting changes. check-in: 6614cfcb user: dan tags: sessions
2011-03-16
19:59
Add the sqlite3_preupdate_new() API, for retrieving the new.* values from within a pre-update callback. check-in: 526545c4 user: dan tags: sessions
09:49
Remove the sqlite3_transaction_hook() API. check-in: b0015a1c user: dan tags: sessions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/session/sqlite3session.h.

11
12
13
14
15
16
17
18





19



















20
21
22
23
24
25
26












27
28
29
30
31
32
33
..
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

#include "sqlite3.h"

typedef struct sqlite3_session sqlite3_session;
typedef struct sqlite3_changeset_iter sqlite3_changeset_iter;

/*
** Create a session object. This session object will record changes to





** database zDb attached to connection db.



















*/
int sqlite3session_create(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of db (e.g. "main") */
  sqlite3_session **ppSession     /* OUT: New session object */
);













/*
** Enable or disable the recording of changes by a session object. When
** enabled, a session object records changes made to the database. When
** disabled - it does not. A newly created session object is enabled.
**
** Passing zero to this function disables the session. Passing a value
** greater than zero enables it. Passing a value less than zero is a 
................................................................................
*/
int sqlite3session_attach(
  sqlite3_session *pSession,      /* Session object */
  const char *zTab                /* Table name */
);

/*
** Obtain a changeset object containing all changes recorded by the 
** session object passed as the first argument.




**

** It is the responsibility of the caller to eventually free the buffer 
** using sqlite3_free().
*/
int sqlite3session_changeset(
  sqlite3_session *pSession,      /* Session object */
  int *pnChangeset,               /* OUT: Size of buffer at *ppChangeset */
  void **ppChangeset              /* OUT: Buffer containing changeset */
);

/*
** Delete a session object previously allocated using sqlite3session_create().
*/
void sqlite3session_delete(sqlite3_session *pSession);

/*
** Create an iterator used to iterate through the contents of a changeset.
*/
int sqlite3changeset_start(
  sqlite3_changeset_iter **ppIter,
  int nChangeset, 
  void *pChangeset







|
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







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







 







|
|
>
>
>
>

>
|
|







<
<
<
<
<







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
..
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

#include "sqlite3.h"

typedef struct sqlite3_session sqlite3_session;
typedef struct sqlite3_changeset_iter sqlite3_changeset_iter;

/*
** Create a new session object attached to database handle db. If successful,
** a pointer to the new object is written to *ppSession and SQLITE_OK is
** returned. If an error occurs, *ppSession is set to NULL and an SQLite
** error code (e.g. [SQLITE_NOMEM]) is returned.
**
** It is possible to create multiple session objects attached to a single
** database handle.
**
** Session objects created using this function should be deleted using the
** [sqlite3session_delete()] function before the database handle that they
** are attached to is itself closed. If the database handle is closed before
** the session object is deleted, then the results of calling any session
** module function, including [sqlite3session_delete()] on the session object
** are undefined.
**
** Because the session module uses the [sqlite3_preupdate_hook()] API, it
** is not possible for an application to register a pre-update hook on a
** database handle that has one or more session objects attached. Nor is
** it possible to create a session object attached to a database handle for
** which a pre-update hook is already defined. The results of attempting 
** either of these things are undefined.
**
** The session object will be used to create changesets for tables in
** database zDb, where zDb is either "main", or "temp", or the name of an
** attached database. It is not an error if database zDb does not exist
** to the database when the session object is created.
*/
int sqlite3session_create(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of db (e.g. "main") */
  sqlite3_session **ppSession     /* OUT: New session object */
);

/*
** Delete a session object previously allocated using 
** [sqlite3session_create()]. Once a session object has been deleted, the
** results of attempting to use pSession with any other session module
** function are undefined.
**
** Session objects must be deleted before the database handle to which they
** are attached is closed. Refer to the documentation for 
** [sqlite3session_create()] for details.
*/
void sqlite3session_delete(sqlite3_session *pSession);

/*
** Enable or disable the recording of changes by a session object. When
** enabled, a session object records changes made to the database. When
** disabled - it does not. A newly created session object is enabled.
**
** Passing zero to this function disables the session. Passing a value
** greater than zero enables it. Passing a value less than zero is a 
................................................................................
*/
int sqlite3session_attach(
  sqlite3_session *pSession,      /* Session object */
  const char *zTab                /* Table name */
);

/*
** Obtain a changeset containing changes to the tables attached to the 
** session object passed as the first argument. If successful, 
** set *ppChangeset to point to a buffer containing the changeset 
** and *pnChangeset to the size of the changeset in bytes before returning
** SQLITE_OK. If an error occurs, set both *ppChangeset and *pnChangeset to
** zero and return an SQLite error code.
**
** Following a successful call to this function, it is the responsibility of
** the caller to eventually free the buffer that *ppChangeset points to using
** [sqlite3_free()].
*/
int sqlite3session_changeset(
  sqlite3_session *pSession,      /* Session object */
  int *pnChangeset,               /* OUT: Size of buffer at *ppChangeset */
  void **ppChangeset              /* OUT: Buffer containing changeset */
);






/*
** Create an iterator used to iterate through the contents of a changeset.
*/
int sqlite3changeset_start(
  sqlite3_changeset_iter **ppIter,
  int nChangeset, 
  void *pChangeset

Changes to src/sqlite.h.in.

6362
6363
6364
6365
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
6381
6382
6383
    char const *zName,            /* Table name */
    sqlite3_int64 iKey1,          /* Rowid of row about to be deleted/updated */
    sqlite3_int64 iKey2           /* New rowid value (for a rowid UPDATE) */
  ),
  void*
);
SQLITE_EXPERIMENTAL int sqlite3_preupdate_old(sqlite3 *, int, sqlite3_value **);
SQLITE_EXPERIMENTAL int sqlite3_preupdate_modified(sqlite3 *, int, int *);
SQLITE_EXPERIMENTAL int sqlite3_preupdate_count(sqlite3 *);

/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif







|
|













6362
6363
6364
6365
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379
6380
6381
6382
6383
    char const *zName,            /* Table name */
    sqlite3_int64 iKey1,          /* Rowid of row about to be deleted/updated */
    sqlite3_int64 iKey2           /* New rowid value (for a rowid UPDATE) */
  ),
  void*
);
SQLITE_EXPERIMENTAL int sqlite3_preupdate_old(sqlite3 *, int, sqlite3_value **);
SQLITE_EXPERIMENTAL int sqlite3_preupdate_count(sqlite3 *);
SQLITE_EXPERIMENTAL int sqlite3_preupdate_new(sqlite3 *, int, sqlite3_value **);

/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif

Changes to src/tclsqlite.c.

2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
....
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880

2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896


2897
2898
2899
2900
2901
2902
2903

2904
2905
2906
2907
2908
2909
2910
      }
    }
#endif
    break;
  }

  case DB_PREUPDATE: {
    static const char *azSub[] = {"count", "hook", "modified", "old", 0};
    enum DbPreupdateSubCmd {
      PRE_COUNT, PRE_HOOK, PRE_MODIFIED, PRE_OLD
    };
    int iSub;

    if( objc<3 ){
      Tcl_WrongNumArgs(interp, 2, objv, "SUB-COMMAND ?ARGS?");
    }
    if( Tcl_GetIndexFromObj(interp, objv[2], azSub, "sub-command", 0, &iSub) ){
................................................................................
          Tcl_WrongNumArgs(interp, 2, objv, "hook ?SCRIPT?");
          return TCL_ERROR;
        }
        DbHookCmd(interp, pDb, (objc==4 ? objv[3] : 0), &pDb->pPreUpdateHook);
        break;
      }

      case PRE_MODIFIED:
      case PRE_OLD: {
        int iIdx;

        if( objc!=4 ){
          Tcl_WrongNumArgs(interp, 3, objv, "INDEX");
          return TCL_ERROR;
        }
        if( Tcl_GetIntFromObj(interp, objv[3], &iIdx) ){
          return TCL_ERROR;
        }

        if( iSub==PRE_MODIFIED ){
          int iRes;
          rc = sqlite3_preupdate_modified(pDb->db, iIdx, &iRes);
          if( rc==SQLITE_OK ) Tcl_SetObjResult(interp, Tcl_NewIntObj(iRes));
        }else{
          sqlite3_value *pValue;
          assert( iSub==PRE_OLD );
          rc = sqlite3_preupdate_old(pDb->db, iIdx, &pValue);


          if( rc==SQLITE_OK ){
            Tcl_Obj *pObj = Tcl_NewStringObj(sqlite3_value_text(pValue), -1);
            Tcl_SetObjResult(interp, pObj);
          }
        }

        if( rc!=SQLITE_OK ){

          Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
          return TCL_ERROR;
        }
      }
    }

    break;







|

|







 







|


>








|
<
|
<

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







2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
....
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890

2891

2892

2893
2894
2895
2896
2897
2898
2899




2900
2901
2902
2903
2904
2905
2906
2907
      }
    }
#endif
    break;
  }

  case DB_PREUPDATE: {
    static const char *azSub[] = {"count", "hook", "new", "old", 0};
    enum DbPreupdateSubCmd {
      PRE_COUNT, PRE_HOOK, PRE_NEW, PRE_OLD
    };
    int iSub;

    if( objc<3 ){
      Tcl_WrongNumArgs(interp, 2, objv, "SUB-COMMAND ?ARGS?");
    }
    if( Tcl_GetIndexFromObj(interp, objv[2], azSub, "sub-command", 0, &iSub) ){
................................................................................
          Tcl_WrongNumArgs(interp, 2, objv, "hook ?SCRIPT?");
          return TCL_ERROR;
        }
        DbHookCmd(interp, pDb, (objc==4 ? objv[3] : 0), &pDb->pPreUpdateHook);
        break;
      }

      case PRE_NEW:
      case PRE_OLD: {
        int iIdx;
        sqlite3_value *pValue;
        if( objc!=4 ){
          Tcl_WrongNumArgs(interp, 3, objv, "INDEX");
          return TCL_ERROR;
        }
        if( Tcl_GetIntFromObj(interp, objv[3], &iIdx) ){
          return TCL_ERROR;
        }

        if( iSub==PRE_OLD ){

          rc = sqlite3_preupdate_old(pDb->db, iIdx, &pValue);

        }else{

          assert( iSub==PRE_NEW );
          rc = sqlite3_preupdate_new(pDb->db, iIdx, &pValue);
        }

        if( rc==SQLITE_OK ){
          Tcl_Obj *pObj = Tcl_NewStringObj(sqlite3_value_text(pValue), -1);
          Tcl_SetObjResult(interp, pObj);




        }else{
          Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
          return TCL_ERROR;
        }
      }
    }

    break;

Changes to src/update.c.

489
490
491
492
493
494
495





496

497
498
499
500
501
502
503
    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the rowid value, or if there are foreign key constraints
    ** to process, delete the old record. Otherwise, add a noop OP_Delete
    ** to invoke the pre-update hook.





    */

    sqlite3VdbeAddOp3(v, OP_Delete, iCur,
        OPFLAG_ISUPDATE | ((hasFK || chngRowid) ? 0 : OPFLAG_ISNOOP),
        regNewRowid
    );
    if( !pParse->nested ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
    }







>
>
>
>
>

>







489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the rowid value, or if there are foreign key constraints
    ** to process, delete the old record. Otherwise, add a noop OP_Delete
    ** to invoke the pre-update hook.
    **
    ** That (regNew==regnewRowid+1) is true is also important for the 
    ** pre-update hook. If hte caller invokes preupdate_new(), the returned
    ** value is copied from memory cell (regNewRowid+1+iCol), where iCol
    ** is the column index supplied by the user.
    */
    assert( regNew==regNewRowid+1 );
    sqlite3VdbeAddOp3(v, OP_Delete, iCur,
        OPFLAG_ISUPDATE | ((hasFK || chngRowid) ? 0 : OPFLAG_ISNOOP),
        regNewRowid
    );
    if( !pParse->nested ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
    }

Changes to src/vdbe.c.

3877
3878
3879
3880
3881
3882
3883
3884
3885
3886
3887
3888
3889
3890
3891
3892
3893
....
3981
3982
3983
3984
3985
3986
3987
3988
3989
3990
3991
3992
3993
3994
3995
  }

  /* Invoke the pre-update hook, if any */
  if( db->xPreUpdateCallback 
   && pOp->p4.z 
   && (!(pOp->p5 & OPFLAG_ISUPDATE) || pC->rowidIsValid==0)
  ){
    sqlite3VdbePreUpdateHook(p, pC,
      pC->rowidIsValid ? op : SQLITE_INSERT, zDb, zTbl, iKey, iKey
    );
  }

  if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++;
  if( pOp->p5 & OPFLAG_LASTROWID ) db->lastRowid = iKey;
  if( pData->flags & MEM_Null ){
    pData->z = 0;
    pData->n = 0;
................................................................................

  /* Invoke the pre-update-hook if required. */
  if( db->xPreUpdateCallback && pOp->p4.z ){
    assert( !(opflags & OPFLAG_ISUPDATE) || (aMem[pOp->p3].flags & MEM_Int) );
    sqlite3VdbePreUpdateHook(p, pC,
        (opflags & OPFLAG_ISUPDATE) ? SQLITE_UPDATE : SQLITE_DELETE, 
        zDb, zTbl, iKey,
        (opflags & OPFLAG_ISUPDATE) ? aMem[pOp->p3].u.i : iKey
    );
  }

  if( opflags & OPFLAG_ISNOOP ) break;

  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  rc = sqlite3BtreeDelete(pC->pCursor);







|
<
<







 







|







3877
3878
3879
3880
3881
3882
3883
3884


3885
3886
3887
3888
3889
3890
3891
....
3979
3980
3981
3982
3983
3984
3985
3986
3987
3988
3989
3990
3991
3992
3993
  }

  /* Invoke the pre-update hook, if any */
  if( db->xPreUpdateCallback 
   && pOp->p4.z 
   && (!(pOp->p5 & OPFLAG_ISUPDATE) || pC->rowidIsValid==0)
  ){
    sqlite3VdbePreUpdateHook(p, pC, SQLITE_INSERT, zDb, zTbl, iKey, pOp->p2);


  }

  if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++;
  if( pOp->p5 & OPFLAG_LASTROWID ) db->lastRowid = iKey;
  if( pData->flags & MEM_Null ){
    pData->z = 0;
    pData->n = 0;
................................................................................

  /* Invoke the pre-update-hook if required. */
  if( db->xPreUpdateCallback && pOp->p4.z ){
    assert( !(opflags & OPFLAG_ISUPDATE) || (aMem[pOp->p3].flags & MEM_Int) );
    sqlite3VdbePreUpdateHook(p, pC,
        (opflags & OPFLAG_ISUPDATE) ? SQLITE_UPDATE : SQLITE_DELETE, 
        zDb, zTbl, iKey,
        pOp->p3
    );
  }

  if( opflags & OPFLAG_ISNOOP ) break;

  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  rc = sqlite3BtreeDelete(pC->pCursor);

Changes to src/vdbeInt.h.

332
333
334
335
336
337
338

339
340
341
342
343



344
345
346
347
348
349
350
...
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
#define VDBE_MAGIC_DEAD     0xb606c3c8    /* The VDBE has been deallocated */

/*
** Structure used to store the context required by the 
** sqlite3_preupdate_*() API functions.
*/
struct PreUpdate {

  VdbeCursor *pCsr;               /* Cursor to read old values from */
  int op;                         /* One of SQLITE_INSERT, UPDATE, DELETE */
  u8 *aRecord;                    /* old.* database record */
  KeyInfo keyinfo;
  UnpackedRecord *pUnpacked;      /* Unpacked version of aRecord[] */



};

/*
** Function prototypes
*/
void sqlite3VdbeFreeCursor(Vdbe *, VdbeCursor*);
void sqliteVdbePopStack(Vdbe*,int);
................................................................................
const char *sqlite3OpcodeName(int);
int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
int sqlite3VdbeCloseStatement(Vdbe *, int);
void sqlite3VdbeFrameDelete(VdbeFrame*);
int sqlite3VdbeFrameRestore(VdbeFrame *);
void sqlite3VdbeMemStoreType(Mem *pMem);
void sqlite3VdbePreUpdateHook(
    Vdbe *, VdbeCursor *, int, const char*, const char*, i64, i64);

#ifdef SQLITE_DEBUG
void sqlite3VdbeMemPrepareToChange(Vdbe*,Mem*);
#endif

#ifndef SQLITE_OMIT_FOREIGN_KEY
int sqlite3VdbeCheckFk(Vdbe *, int);







>





>
>
>







 







|







332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
...
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
#define VDBE_MAGIC_DEAD     0xb606c3c8    /* The VDBE has been deallocated */

/*
** Structure used to store the context required by the 
** sqlite3_preupdate_*() API functions.
*/
struct PreUpdate {
  Vdbe *v;
  VdbeCursor *pCsr;               /* Cursor to read old values from */
  int op;                         /* One of SQLITE_INSERT, UPDATE, DELETE */
  u8 *aRecord;                    /* old.* database record */
  KeyInfo keyinfo;
  UnpackedRecord *pUnpacked;      /* Unpacked version of aRecord[] */
  UnpackedRecord *pNewUnpacked;   /* Unpacked version of new.* record */
  int iNewReg;                    /* Register for new.* values */
  Mem *aNew;                      /* Array of new.* values */
};

/*
** Function prototypes
*/
void sqlite3VdbeFreeCursor(Vdbe *, VdbeCursor*);
void sqliteVdbePopStack(Vdbe*,int);
................................................................................
const char *sqlite3OpcodeName(int);
int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
int sqlite3VdbeCloseStatement(Vdbe *, int);
void sqlite3VdbeFrameDelete(VdbeFrame*);
int sqlite3VdbeFrameRestore(VdbeFrame *);
void sqlite3VdbeMemStoreType(Mem *pMem);
void sqlite3VdbePreUpdateHook(
    Vdbe *, VdbeCursor *, int, const char*, const char*, i64, int);

#ifdef SQLITE_DEBUG
void sqlite3VdbeMemPrepareToChange(Vdbe*,Mem*);
#endif

#ifndef SQLITE_OMIT_FOREIGN_KEY
int sqlite3VdbeCheckFk(Vdbe *, int);

Changes to src/vdbeapi.c.

1325
1326
1327
1328
1329
1330
1331




1332
1333
1334
1335


1336
1337
1338
1339
1340
1341
1342
1343
1344

1345
1346
1347
1348
1349
1350
1351
....
1368
1369
1370
1371
1372
1373
1374




1375
1376
1377
1378
1379
1380





1381
1382

1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393












































1394
1395
1396
1397
1398
1399
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  Vdbe *pVdbe = (Vdbe*)pStmt;
  int v = pVdbe->aCounter[op-1];
  if( resetFlag ) pVdbe->aCounter[op-1] = 0;
  return v;
}





int sqlite3_preupdate_old(sqlite3 *db, int iIdx, sqlite3_value **ppValue){
  PreUpdate *p = db->pPreUpdate;
  int rc = SQLITE_OK;



  if( !p || p->op==SQLITE_INSERT ){
    rc = SQLITE_MISUSE_BKPT;
    goto preupdate_old_out;
  }
  if( iIdx>=p->pCsr->nField || iIdx<0 ){
    rc = SQLITE_RANGE;
    goto preupdate_old_out;
  }


  if( p->pUnpacked==0 ){
    u32 nRecord;
    u8 *aRecord;

    rc = sqlite3BtreeDataSize(p->pCsr->pCursor, &nRecord);
    if( rc!=SQLITE_OK ) goto preupdate_old_out;
    aRecord = sqlite3DbMallocRaw(db, nRecord);
................................................................................
  }

 preupdate_old_out:
  sqlite3Error(db, rc, 0);
  return sqlite3ApiExit(db, rc);
}





int sqlite3_preupdate_count(sqlite3 *db){
  PreUpdate *p = db->pPreUpdate;
  return (p ? p->pCsr->nField : 0);
}

int sqlite3_preupdate_modified(sqlite3 *db, int iIdx, int *pbMod){





  PreUpdate *p = db->pPreUpdate;
  int rc = SQLITE_OK;


  if( !p || p->op!=SQLITE_UPDATE ){
    rc = SQLITE_MISUSE_BKPT;
    goto preupdate_mod_out;
  }
  if( iIdx>=p->pCsr->nField || iIdx<0 ){
    rc = SQLITE_RANGE;
    goto preupdate_mod_out;
  }
  *pbMod = 1;













































 preupdate_mod_out:
  sqlite3Error(db, rc, 0);
  return sqlite3ApiExit(db, rc);
}









>
>
>
>




>
>









>







 







>
>
>
>





<
>
>
>
>
>


>

|

|



|

<

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|





1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
....
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390

1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407

1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  Vdbe *pVdbe = (Vdbe*)pStmt;
  int v = pVdbe->aCounter[op-1];
  if( resetFlag ) pVdbe->aCounter[op-1] = 0;
  return v;
}

/*
** This function is called from within a pre-update callback to retrieve
** a field of the row currently being updated or deleted.
*/
int sqlite3_preupdate_old(sqlite3 *db, int iIdx, sqlite3_value **ppValue){
  PreUpdate *p = db->pPreUpdate;
  int rc = SQLITE_OK;

  /* Test that this call is being made from within an SQLITE_DELETE or
  ** SQLITE_UPDATE pre-update callback, and that iIdx is within range. */
  if( !p || p->op==SQLITE_INSERT ){
    rc = SQLITE_MISUSE_BKPT;
    goto preupdate_old_out;
  }
  if( iIdx>=p->pCsr->nField || iIdx<0 ){
    rc = SQLITE_RANGE;
    goto preupdate_old_out;
  }

  /* If the old.* record has not yet been loaded into memory, do so now. */
  if( p->pUnpacked==0 ){
    u32 nRecord;
    u8 *aRecord;

    rc = sqlite3BtreeDataSize(p->pCsr->pCursor, &nRecord);
    if( rc!=SQLITE_OK ) goto preupdate_old_out;
    aRecord = sqlite3DbMallocRaw(db, nRecord);
................................................................................
  }

 preupdate_old_out:
  sqlite3Error(db, rc, 0);
  return sqlite3ApiExit(db, rc);
}

/*
** This function is called from within a pre-update callback to retrieve
** the number of columns in the row being updated, deleted or inserted.
*/
int sqlite3_preupdate_count(sqlite3 *db){
  PreUpdate *p = db->pPreUpdate;
  return (p ? p->pCsr->nField : 0);
}


/*
** This function is called from within a pre-update callback to retrieve
** a field of the row currently being updated or inserted.
*/
int sqlite3_preupdate_new(sqlite3 *db, int iIdx, sqlite3_value **ppValue){
  PreUpdate *p = db->pPreUpdate;
  int rc = SQLITE_OK;
  Mem *pMem;

  if( !p || p->op==SQLITE_DELETE ){
    rc = SQLITE_MISUSE_BKPT;
    goto preupdate_new_out;
  }
  if( iIdx>=p->pCsr->nField || iIdx<0 ){
    rc = SQLITE_RANGE;
    goto preupdate_new_out;
  }


  if( p->op==SQLITE_INSERT ){
    /* For an INSERT, memory cell p->iNewReg contains the serialized record
    ** that is being inserted. Deserialize it. */
    UnpackedRecord *pUnpack = p->pNewUnpacked;
    if( !pUnpack ){
      Mem *pData = &p->v->aMem[p->iNewReg];
      rc = sqlite3VdbeMemExpandBlob(pData);
      if( rc!=SQLITE_OK ) goto preupdate_new_out;
      pUnpack = sqlite3VdbeRecordUnpack(&p->keyinfo, pData->n, pData->z, 0, 0);
      if( !pUnpack ){
        rc = SQLITE_NOMEM;
        goto preupdate_new_out;
      }
      p->pNewUnpacked = pUnpack;
    }
    if( iIdx>=pUnpack->nField ){
      pMem = (sqlite3_value *)columnNullValue();
    }else{
      pMem = &pUnpack->aMem[iIdx];
      sqlite3VdbeMemStoreType(pMem);
    }
  }else{
    /* For an UPDATE, memory cell (p->iNewReg+1+iIdx) contains the required
    ** value. Make a copy of the cell contents and return a pointer to it.
    ** It is not safe to return a pointer to the memory cell itself as the
    ** caller may modify the value text encoding.
    */
    assert( p->op==SQLITE_UPDATE );
    if( !p->aNew ){
      p->aNew = (Mem *)sqlite3DbMallocZero(db, sizeof(Mem) * p->pCsr->nField);
      if( !p->aNew ){
        rc = SQLITE_NOMEM;
        goto preupdate_new_out;
      }
    }
    pMem = &p->aNew[iIdx];
    if( pMem->flags==0 ){
      rc = sqlite3VdbeMemCopy(pMem, &p->v->aMem[p->iNewReg+1+iIdx]);
      if( rc!=SQLITE_OK ) goto preupdate_new_out;
      sqlite3VdbeMemStoreType(pMem);
    }
  }
  *ppValue = pMem;

 preupdate_new_out:
  sqlite3Error(db, rc, 0);
  return sqlite3ApiExit(db, rc);
}


Changes to src/vdbeaux.c.

3172
3173
3174
3175
3176
3177
3178
3179
3180
3181

3182
3183
3184
3185







3186
3187

3188
3189
3190
3191
3192
3193
3194
3195
3196
3197


3198




3199




void sqlite3VdbePreUpdateHook(
  Vdbe *v,                        /* Vdbe pre-update hook is invoked by */
  VdbeCursor *pCsr,               /* Cursor to grab old.* values from */
  int op,                         /* SQLITE_INSERT, UPDATE or DELETE */
  const char *zDb,                /* Database name */
  const char *zTbl,               /* Table name */
  i64 iKey1,                      /* Initial key value */
  i64 iKey2                       /* Final key value */
){
  sqlite3 *db = v->db;


  PreUpdate preupdate;
  memset(&preupdate, 0, sizeof(PreUpdate));








  preupdate.pCsr = pCsr;
  preupdate.op = op;

  preupdate.keyinfo.db = db;
  preupdate.keyinfo.enc = ENC(db);
  preupdate.keyinfo.nField = pCsr->nField;
  db->pPreUpdate = &preupdate;
  db->xPreUpdateCallback(db->pPreUpdateArg, db, op, zDb, zTbl, iKey1, iKey2);
  db->pPreUpdate = 0;
  sqlite3DbFree(db, preupdate.aRecord);
  if( preupdate.pUnpacked ){
    sqlite3VdbeDeleteUnpackedRecord(preupdate.pUnpacked);
  }


}
















|


>




>
>
>
>
>
>
>


>










>
>
|
>
>
>
>
|
>
>
>
>
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181
3182
3183
3184
3185
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
void sqlite3VdbePreUpdateHook(
  Vdbe *v,                        /* Vdbe pre-update hook is invoked by */
  VdbeCursor *pCsr,               /* Cursor to grab old.* values from */
  int op,                         /* SQLITE_INSERT, UPDATE or DELETE */
  const char *zDb,                /* Database name */
  const char *zTbl,               /* Table name */
  i64 iKey1,                      /* Initial key value */
  int iReg                        /* Register for new.* record */
){
  sqlite3 *db = v->db;
  i64 iKey2;

  PreUpdate preupdate;
  memset(&preupdate, 0, sizeof(PreUpdate));

  if( op==SQLITE_UPDATE ){
    iKey2 = v->aMem[iReg].u.i;
  }else{
    iKey2 = iKey1;
  }

  preupdate.v = v;
  preupdate.pCsr = pCsr;
  preupdate.op = op;
  preupdate.iNewReg = iReg;
  preupdate.keyinfo.db = db;
  preupdate.keyinfo.enc = ENC(db);
  preupdate.keyinfo.nField = pCsr->nField;
  db->pPreUpdate = &preupdate;
  db->xPreUpdateCallback(db->pPreUpdateArg, db, op, zDb, zTbl, iKey1, iKey2);
  db->pPreUpdate = 0;
  sqlite3DbFree(db, preupdate.aRecord);
  if( preupdate.pUnpacked ){
    sqlite3VdbeDeleteUnpackedRecord(preupdate.pUnpacked);
  }
  if( preupdate.pNewUnpacked ){
    sqlite3VdbeDeleteUnpackedRecord(preupdate.pNewUnpacked);
  }
  if( preupdate.aNew ){
    int i;
    for(i=0; i<pCsr->nField; i++){
      sqlite3VdbeMemRelease(&preupdate.aNew[i]);
    }
    sqlite3_free(preupdate.aNew);
  }
}

Changes to test/hook.test.

401
402
403
404
405
406
407
408
409
410
411






412
413
414
415
416
417
418
...
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
...
493
494
495
496
497
498
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
535
536
537
538
539
540
541
542
...
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
...
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
...
624
625
626
627
628
629
630
631
632
633
634
635
    set ::preupdate
  "] [list $X]
}

proc preupdate_hook {args} {
  set type [lindex $args 0]
  eval lappend ::preupdate $args
  if {$type != "SQLITE_INSERT"} {
    for {set i 0} {$i < [db preupdate count]} {incr i} {
      lappend ::preupdate [db preupdate old $i]
    }






  }
}

db close
forcedelete test.db
sqlite3 db test.db
db preupdate hook preupdate_hook
................................................................................
  INSERT INTO t3 VALUES(4, 16);
  INSERT INTO t3 VALUES(5, 25);
  INSERT INTO t3 VALUES(6, 36);
} 

do_preupdate_test 7.1.1 {
  INSERT INTO t1 VALUES('x', 'y')
} {INSERT main t1 1 1}

# 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.
do_preupdate_test 7.1.2.1 {
  INSERT INTO t1 SELECT y, x FROM t2;
} {INSERT main t1 2 2 INSERT main t1 3 3}
do_preupdate_test 7.1.2.2 {
  INSERT INTO t1 SELECT * FROM t2;
} {INSERT main t1 4 4 INSERT main t1 5 5}

do_preupdate_test 7.1.3 {
  REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
} {
  DELETE main t1 1 1   x y
  INSERT main t1 1 1
}

do_preupdate_test 7.1.4 {
  REPLACE INTO t3 VALUES(4, NULL);
} {
  DELETE main t3 1 1   4 16
  INSERT main t3 4 4
}

do_preupdate_test 7.1.5 {
  REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
} {
  DELETE main t3 2 2  5 25
  DELETE main t3 3 3  6 36
  INSERT main t3 2 2
}

do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}

do_preupdate_test 7.2.1 {
  DELETE FROM t1 WHERE rowid = 3
} {
................................................................................
  INSERT INTO t3 VALUES(5, 25);
  INSERT INTO t3 VALUES(6, 36);
}

do_preupdate_test 7.3.1 {
  UPDATE t2 SET y = y||y;
} {
  UPDATE main t2 1 1   a b
  UPDATE main t2 2 2   c d
}

do_preupdate_test 7.3.2 {
  UPDATE t2 SET rowid = rowid-1;
} {
  UPDATE main t2 1 0   a bb
  UPDATE main t2 2 1   c dd
}

do_preupdate_test 7.3.3 {
  UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
} {
  DELETE main t2 1 1   c dd
  UPDATE main t2 0 1   a bb
}

do_preupdate_test 7.3.4.1 {
  UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
} {
  DELETE main t3 2 2   5 25
  UPDATE main t3 3 3   6 36
}

do_execsql_test 7.3.4.2 {
  INSERT INTO t3 VALUES(10, 100);
  SELECT rowid, * FROM t3;
} {1 4 16   3 5 36   4 10 100}

do_preupdate_test 7.3.5 {
  UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;
} {
  DELETE main t3 1 1    4  16
  DELETE main t3 3 3    5  36
  UPDATE main t3 4 1   10 100
}

do_execsql_test 7.4.1.0 {
  CREATE TABLE t4(a, b);
  INSERT INTO t4 VALUES('a', 1);
  INSERT INTO t4 VALUES('b', 2);
  INSERT INTO t4 VALUES('c', 3);
................................................................................
    DELETE FROM t5 WHERE b = 1;
  END;
}
do_preupdate_test 7.4.2.1 {
  UPDATE t5 SET b = 4 WHERE a = 'c'
} {
  DELETE main t5 1 1   a 1
  UPDATE main t5 3 3   c 3
}

do_execsql_test 7.4.2.2 {
  INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
}

do_preupdate_test 7.4.2.3 {
................................................................................
} {
  DELETE main t7 1 1   one two {}
}

do_preupdate_test 7.5.1.2 {
  UPDATE t7 SET b = 'five'
} {
  UPDATE main t7 2 2   three four {}
}

do_execsql_test 7.5.2.0 {
  CREATE TABLE t8(a, b);
  INSERT INTO t8 VALUES('one', 'two');
  INSERT INTO t8 VALUES('three', 'four');
  ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';
................................................................................
  DELETE FROM t8 WHERE a = 'one'
} {
  DELETE main t8 1 1   one two xxx
}
do_preupdate_test 7.5.2.2 {
  UPDATE t8 SET b = 'five'
} {
  UPDATE main t8 2 2   three four xxx
}

finish_test








|



>
>
>
>
>
>







 







|




|


|





|






|







|







 







|
|





|
|






|






|












|







 







|







 







|







 







|




401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
...
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
...
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
535
536
537
538
539
540
541
542
543
544
545
546
547
548
...
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
...
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
...
630
631
632
633
634
635
636
637
638
639
640
641
    set ::preupdate
  "] [list $X]
}

proc preupdate_hook {args} {
  set type [lindex $args 0]
  eval lappend ::preupdate $args
  if {$type != "INSERT"} {
    for {set i 0} {$i < [db preupdate count]} {incr i} {
      lappend ::preupdate [db preupdate old $i]
    }
  }
  if {$type != "DELETE"} {
    for {set i 0} {$i < [db preupdate count]} {incr i} {
      set rc [catch { db preupdate new $i } v]
      lappend ::preupdate $v
    }
  }
}

db close
forcedelete test.db
sqlite3 db test.db
db preupdate hook preupdate_hook
................................................................................
  INSERT INTO t3 VALUES(4, 16);
  INSERT INTO t3 VALUES(5, 25);
  INSERT INTO t3 VALUES(6, 36);
} 

do_preupdate_test 7.1.1 {
  INSERT INTO t1 VALUES('x', 'y')
} {INSERT main t1 1 1  x y}

# 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.
do_preupdate_test 7.1.2.1 {
  INSERT INTO t1 SELECT y, x FROM t2;
} {INSERT main t1 2 2 b a   INSERT main t1 3 3 d c}
do_preupdate_test 7.1.2.2 {
  INSERT INTO t1 SELECT * FROM t2;
} {INSERT main t1 4 4 a b   INSERT main t1 5 5 c d}

do_preupdate_test 7.1.3 {
  REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
} {
  DELETE main t1 1 1   x y
  INSERT main t1 1 1   1 1
}

do_preupdate_test 7.1.4 {
  REPLACE INTO t3 VALUES(4, NULL);
} {
  DELETE main t3 1 1   4 16
  INSERT main t3 4 4   4 {}
}

do_preupdate_test 7.1.5 {
  REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
} {
  DELETE main t3 2 2  5 25
  DELETE main t3 3 3  6 36
  INSERT main t3 2 2  6 {}
}

do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}

do_preupdate_test 7.2.1 {
  DELETE FROM t1 WHERE rowid = 3
} {
................................................................................
  INSERT INTO t3 VALUES(5, 25);
  INSERT INTO t3 VALUES(6, 36);
}

do_preupdate_test 7.3.1 {
  UPDATE t2 SET y = y||y;
} {
  UPDATE main t2 1 1   a b  a bb
  UPDATE main t2 2 2   c d  c dd
}

do_preupdate_test 7.3.2 {
  UPDATE t2 SET rowid = rowid-1;
} {
  UPDATE main t2 1 0   a bb  a bb
  UPDATE main t2 2 1   c dd  c dd
}

do_preupdate_test 7.3.3 {
  UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
} {
  DELETE main t2 1 1   c dd
  UPDATE main t2 0 1   a bb  a bb
}

do_preupdate_test 7.3.4.1 {
  UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
} {
  DELETE main t3 2 2   5 25
  UPDATE main t3 3 3   6 36  5 36
}

do_execsql_test 7.3.4.2 {
  INSERT INTO t3 VALUES(10, 100);
  SELECT rowid, * FROM t3;
} {1 4 16   3 5 36   4 10 100}

do_preupdate_test 7.3.5 {
  UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;
} {
  DELETE main t3 1 1    4  16
  DELETE main t3 3 3    5  36
  UPDATE main t3 4 1   10 100  5 100
}

do_execsql_test 7.4.1.0 {
  CREATE TABLE t4(a, b);
  INSERT INTO t4 VALUES('a', 1);
  INSERT INTO t4 VALUES('b', 2);
  INSERT INTO t4 VALUES('c', 3);
................................................................................
    DELETE FROM t5 WHERE b = 1;
  END;
}
do_preupdate_test 7.4.2.1 {
  UPDATE t5 SET b = 4 WHERE a = 'c'
} {
  DELETE main t5 1 1   a 1
  UPDATE main t5 3 3   c 3  c 4
}

do_execsql_test 7.4.2.2 {
  INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
}

do_preupdate_test 7.4.2.3 {
................................................................................
} {
  DELETE main t7 1 1   one two {}
}

do_preupdate_test 7.5.1.2 {
  UPDATE t7 SET b = 'five'
} {
  UPDATE main t7 2 2   three four {}  three five {}
}

do_execsql_test 7.5.2.0 {
  CREATE TABLE t8(a, b);
  INSERT INTO t8 VALUES('one', 'two');
  INSERT INTO t8 VALUES('three', 'four');
  ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';
................................................................................
  DELETE FROM t8 WHERE a = 'one'
} {
  DELETE main t8 1 1   one two xxx
}
do_preupdate_test 7.5.2.2 {
  UPDATE t8 SET b = 'five'
} {
  UPDATE main t8 2 2   three four xxx  three five xxx
}

finish_test