/ Check-in [e68e4282]
Login

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

Overview
Comment:Make sqlite3_count_changes() and total_changes() work with "DELETE FROM ". (CVS 5844)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e68e4282adb9003aa297d033aeb5d9cadee215cd
User & Date: danielk1977 2008-10-27 13:59:34
Context
2008-10-27
15:34
If an SQLITE_DELETE authorization callback returns SQLITE_IGNORE, proceed with the delete operation but disable the truncate optimization. (CVS 5845) check-in: 65a2e131 user: danielk1977 tags: trunk
13:59
Make sqlite3_count_changes() and total_changes() work with "DELETE FROM ". (CVS 5844)
check-in: e68e4282 user: danielk1977 tags: trunk
08:24
Remove some if() conditions that are always true from delete.c. (CVS 5843) check-in: 297ad90d user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

     5      5   ** a legal notice, here is a blessing:
     6      6   **
     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         -** $Id: btree.c,v 1.525 2008/10/08 17:58:49 danielk1977 Exp $
           12  +** $Id: btree.c,v 1.526 2008/10/27 13:59:34 danielk1977 Exp $
    13     13   **
    14     14   ** This file implements a external (disk-based) database using BTrees.
    15     15   ** See the header comment on "btreeInt.h" for additional information.
    16     16   ** Including a description of file format and an overview of operation.
    17     17   */
    18     18   #include "btreeInt.h"
    19     19   
................................................................................
  6183   6183   ** Erase the given database page and all its children.  Return
  6184   6184   ** the page to the freelist.
  6185   6185   */
  6186   6186   static int clearDatabasePage(
  6187   6187     BtShared *pBt,           /* The BTree that contains the table */
  6188   6188     Pgno pgno,            /* Page number to clear */
  6189   6189     MemPage *pParent,     /* Parent page.  NULL for the root */
  6190         -  int freePageFlag      /* Deallocate page if true */
         6190  +  int freePageFlag,     /* Deallocate page if true */
         6191  +  int *pnChange
  6191   6192   ){
  6192   6193     MemPage *pPage = 0;
  6193   6194     int rc;
  6194   6195     unsigned char *pCell;
  6195   6196     int i;
  6196   6197   
  6197   6198     assert( sqlite3_mutex_held(pBt->mutex) );
................................................................................
  6200   6201     }
  6201   6202   
  6202   6203     rc = getAndInitPage(pBt, pgno, &pPage);
  6203   6204     if( rc ) goto cleardatabasepage_out;
  6204   6205     for(i=0; i<pPage->nCell; i++){
  6205   6206       pCell = findCell(pPage, i);
  6206   6207       if( !pPage->leaf ){
  6207         -      rc = clearDatabasePage(pBt, get4byte(pCell), pPage, 1);
         6208  +      rc = clearDatabasePage(pBt, get4byte(pCell), pPage, 1, pnChange);
  6208   6209         if( rc ) goto cleardatabasepage_out;
  6209   6210       }
  6210   6211       rc = clearCell(pPage, pCell);
  6211   6212       if( rc ) goto cleardatabasepage_out;
  6212   6213     }
  6213   6214     if( !pPage->leaf ){
  6214         -    rc = clearDatabasePage(pBt, get4byte(&pPage->aData[8]), pPage, 1);
         6215  +    rc = clearDatabasePage(pBt, get4byte(&pPage->aData[8]), pPage, 1, pnChange);
  6215   6216       if( rc ) goto cleardatabasepage_out;
         6217  +  }else if( pnChange ){
         6218  +    assert( pPage->intKey );
         6219  +    *pnChange += pPage->nCell;
  6216   6220     }
  6217   6221     if( freePageFlag ){
  6218   6222       rc = freePage(pPage);
  6219   6223     }else if( (rc = sqlite3PagerWrite(pPage->pDbPage))==0 ){
  6220   6224       zeroPage(pPage, pPage->aData[0] | PTF_LEAF);
  6221   6225     }
  6222   6226   
................................................................................
  6229   6233   ** Delete all information from a single table in the database.  iTable is
  6230   6234   ** the page number of the root of the table.  After this routine returns,
  6231   6235   ** the root page is empty, but still exists.
  6232   6236   **
  6233   6237   ** This routine will fail with SQLITE_LOCKED if there are any open
  6234   6238   ** read cursors on the table.  Open write cursors are moved to the
  6235   6239   ** root of the table.
         6240  +**
         6241  +** If pnChange is not NULL, then table iTable must be an intkey table. The
         6242  +** integer value pointed to by pnChange is incremented by the number of
         6243  +** entries in the table.
  6236   6244   */
  6237         -int sqlite3BtreeClearTable(Btree *p, int iTable){
         6245  +int sqlite3BtreeClearTable(Btree *p, int iTable, int *pnChange){
  6238   6246     int rc;
  6239   6247     BtShared *pBt = p->pBt;
  6240   6248     sqlite3BtreeEnter(p);
  6241   6249     pBt->db = p->db;
  6242   6250     if( p->inTrans!=TRANS_WRITE ){
  6243   6251       rc = pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR;
  6244   6252     }else if( (rc = checkReadLocks(p, iTable, 0, 1))!=SQLITE_OK ){
  6245   6253       /* nothing to do */
  6246   6254     }else if( SQLITE_OK!=(rc = saveAllCursors(pBt, iTable, 0)) ){
  6247   6255       /* nothing to do */
  6248   6256     }else{
  6249         -    rc = clearDatabasePage(pBt, (Pgno)iTable, 0, 0);
         6257  +    rc = clearDatabasePage(pBt, (Pgno)iTable, 0, 0, pnChange);
  6250   6258     }
  6251   6259     sqlite3BtreeLeave(p);
  6252   6260     return rc;
  6253   6261   }
  6254   6262   
  6255   6263   /*
  6256   6264   ** Erase all information in a table and add the root of the table to
................................................................................
  6290   6298     */
  6291   6299     if( pBt->pCursor ){
  6292   6300       return SQLITE_LOCKED;
  6293   6301     }
  6294   6302   
  6295   6303     rc = sqlite3BtreeGetPage(pBt, (Pgno)iTable, &pPage, 0);
  6296   6304     if( rc ) return rc;
  6297         -  rc = sqlite3BtreeClearTable(p, iTable);
         6305  +  rc = sqlite3BtreeClearTable(p, iTable, 0);
  6298   6306     if( rc ){
  6299   6307       releasePage(pPage);
  6300   6308       return rc;
  6301   6309     }
  6302   6310   
  6303   6311     *piMoved = 0;
  6304   6312   

Changes to src/btree.h.

     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 B-Tree file
    13     13   ** subsystem.  See comments in the source code for a detailed description
    14     14   ** of what each interface routine does.
    15     15   **
    16         -** @(#) $Id: btree.h,v 1.104 2008/10/08 17:58:49 danielk1977 Exp $
           16  +** @(#) $Id: btree.h,v 1.105 2008/10/27 13:59:34 danielk1977 Exp $
    17     17   */
    18     18   #ifndef _BTREE_H_
    19     19   #define _BTREE_H_
    20     20   
    21     21   /* TODO: This definition is just included so other modules compile. It
    22     22   ** needs to be revisited.
    23     23   */
................................................................................
   113    113   ** of the following flags:
   114    114   */
   115    115   #define BTREE_INTKEY     1    /* Table has only 64-bit signed integer keys */
   116    116   #define BTREE_ZERODATA   2    /* Table has keys only - no data */
   117    117   #define BTREE_LEAFDATA   4    /* Data stored in leaves only.  Implies INTKEY */
   118    118   
   119    119   int sqlite3BtreeDropTable(Btree*, int, int*);
   120         -int sqlite3BtreeClearTable(Btree*, int);
          120  +int sqlite3BtreeClearTable(Btree*, int, int*);
   121    121   int sqlite3BtreeGetMeta(Btree*, int idx, u32 *pValue);
   122    122   int sqlite3BtreeUpdateMeta(Btree*, int idx, u32 value);
   123    123   void sqlite3BtreeTripAllCursors(Btree*, int);
   124    124   
   125    125   int sqlite3BtreeCursor(
   126    126     Btree*,                              /* BTree containing table to open */
   127    127     int iTable,                          /* Index of root page */

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.183 2008/10/27 08:24:38 danielk1977 Exp $
           15  +** $Id: delete.c,v 1.184 2008/10/27 13:59:34 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.
................................................................................
   229    229     Index *pIdx;           /* For looping over indices of the table */
   230    230     int iCur;              /* VDBE Cursor number for pTab */
   231    231     sqlite3 *db;           /* Main database structure */
   232    232     AuthContext sContext;  /* Authorization context */
   233    233     int oldIdx = -1;       /* Cursor for the OLD table of AFTER triggers */
   234    234     NameContext sNC;       /* Name context to resolve expressions in */
   235    235     int iDb;               /* Database number */
   236         -  int memCnt = 0;        /* Memory cell used for change counting */
          236  +  int memCnt = -1;       /* Memory cell used for change counting */
   237    237   
   238    238   #ifndef SQLITE_OMIT_TRIGGER
   239    239     int isView;                  /* True if attempting to delete from a view */
   240    240     int triggers_exist = 0;      /* True if any triggers exist */
   241    241   #endif
   242    242     int iBeginAfterTrigger;      /* Address of after trigger program */
   243    243     int iEndAfterTrigger;        /* Exit of after trigger program */
................................................................................
   368    368   #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
   369    369     /* Special case: A DELETE without a WHERE clause deletes everything.
   370    370     ** It is easier just to erase the whole table.  Note, however, that
   371    371     ** this means that the row change count will be incorrect.
   372    372     */
   373    373     if( pWhere==0 && !triggers_exist && !IsVirtual(pTab) ){
   374    374       assert( !isView );
   375         -    if( db->flags & SQLITE_CountRows ){
   376         -      /* If counting rows deleted, just count the total number of
   377         -      ** entries in the table. */
   378         -      int addr2;
   379         -      sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
   380         -      sqlite3VdbeAddOp2(v, OP_Rewind, iCur, sqlite3VdbeCurrentAddr(v)+2);
   381         -      addr2 = sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   382         -      sqlite3VdbeAddOp2(v, OP_Next, iCur, addr2);
   383         -      sqlite3VdbeAddOp1(v, OP_Close, iCur);
   384         -    }
   385         -    sqlite3VdbeAddOp2(v, OP_Clear, pTab->tnum, iDb);
          375  +    sqlite3VdbeAddOp3(v, OP_Clear, pTab->tnum, iDb, memCnt);
   386    376       if( !pParse->nested ){
   387    377         sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
   388    378       }
   389    379       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   390    380         assert( pIdx->pSchema==pTab->pSchema );
   391    381         sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
   392    382       }

Changes to src/test3.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Code for testing the btree.c module in SQLite.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16         -** $Id: test3.c,v 1.101 2008/08/13 19:11:48 drh Exp $
           16  +** $Id: test3.c,v 1.102 2008/10/27 13:59:34 danielk1977 Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   #include "btreeInt.h"
    20     20   #include "tcl.h"
    21     21   #include <stdlib.h>
    22     22   #include <string.h>
    23     23   
................................................................................
   385    385       Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
   386    386          " ID TABLENUM\"", 0);
   387    387       return TCL_ERROR;
   388    388     }
   389    389     pBt = sqlite3TestTextToPtr(argv[1]);
   390    390     if( Tcl_GetInt(interp, argv[2], &iTable) ) return TCL_ERROR;
   391    391     sqlite3BtreeEnter(pBt);
   392         -  rc = sqlite3BtreeClearTable(pBt, iTable);
          392  +  rc = sqlite3BtreeClearTable(pBt, iTable, 0);
   393    393     sqlite3BtreeLeave(pBt);
   394    394     if( rc!=SQLITE_OK ){
   395    395       Tcl_AppendResult(interp, errorName(rc), 0);
   396    396       return TCL_ERROR;
   397    397     }
   398    398     return TCL_OK;
   399    399   }

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.782 2008/10/08 17:58:49 danielk1977 Exp $
           46  +** $Id: vdbe.c,v 1.783 2008/10/27 13:59:34 danielk1977 Exp $
    47     47   */
    48     48   #include "sqliteInt.h"
    49     49   #include <ctype.h>
    50     50   #include "vdbeInt.h"
    51     51   
    52     52   /*
    53     53   ** The following global variable is incremented every time a cursor
................................................................................
  4038   4038         sqlite3RootPageMoved(&db->aDb[iDb], iMoved, pOp->p1);
  4039   4039       }
  4040   4040   #endif
  4041   4041     }
  4042   4042     break;
  4043   4043   }
  4044   4044   
  4045         -/* Opcode: Clear P1 P2 *
         4045  +/* Opcode: Clear P1 P2 P3
  4046   4046   **
  4047   4047   ** Delete all contents of the database table or index whose root page
  4048   4048   ** in the database file is given by P1.  But, unlike Destroy, do not
  4049   4049   ** remove the table or index from the database file.
  4050   4050   **
  4051   4051   ** The table being clear is in the main database file if P2==0.  If
  4052   4052   ** P2==1 then the table to be clear is in the auxiliary database file
  4053   4053   ** that is used to store tables create using CREATE TEMPORARY TABLE.
  4054   4054   **
         4055  +** If the P3 value is non-zero, then the table refered to must be an
         4056  +** intkey table (an SQL table, not an index). In this case the row change 
         4057  +** count is incremented by the number of rows in the table being cleared. 
         4058  +** If P3 is greater than zero, then the value stored in register P3 is
         4059  +** also incremented by the number of rows in the table being cleared.
         4060  +**
  4055   4061   ** See also: Destroy
  4056   4062   */
  4057   4063   case OP_Clear: {
         4064  +  int nChange = 0;
  4058   4065     assert( (p->btreeMask & (1<<pOp->p2))!=0 );
  4059         -  rc = sqlite3BtreeClearTable(db->aDb[pOp->p2].pBt, pOp->p1);
         4066  +  rc = sqlite3BtreeClearTable(
         4067  +      db->aDb[pOp->p2].pBt, pOp->p1, (pOp->p3 ? &nChange : 0)
         4068  +  );
         4069  +  if( pOp->p3 ){
         4070  +    p->nChange += nChange;
         4071  +    if( pOp->p3>0 ){
         4072  +      p->aMem[pOp->p3].u.i += nChange;
         4073  +    }
         4074  +  }
  4060   4075     break;
  4061   4076   }
  4062   4077   
  4063   4078   /* Opcode: CreateTable P1 P2 * * *
  4064   4079   **
  4065   4080   ** Allocate a new table in the main database file if P1==0 or in the
  4066   4081   ** auxiliary database file if P1==1 or in an attached database if

Changes to test/laststmtchanges.test.

            1  +#
     1      2   # The author disclaims copyright to this source code.  In place of
     2      3   # a legal notice, here is a blessing:
     3      4   #
     4      5   #    May you do good and not evil.
     5      6   #    May you find forgiveness for yourself and forgive others.
     6      7   #    May you share freely, never taking more than you give.
     7      8   #
................................................................................
    15     16   #         statement).
    16     17   # Note 2: changes() is changed within the context of a trigger much like 
    17     18   #         last_insert_rowid() (see lastinsert.test), but is restored once
    18     19   #         the trigger exits.
    19     20   # Note 3: changes() is not changed by a change to a view (since everything
    20     21   #         is done within instead of trigger context).
    21     22   #
           23  +# $Id: laststmtchanges.test,v 1.7 2008/10/27 13:59:34 danielk1977 Exp $
    22     24   
    23     25   set testdir [file dirname $argv0]
    24     26   source $testdir/tester.tcl
    25     27   
    26     28   # ----------------------------------------------------------------------------
    27     29   # 1.x - basic tests (no triggers)
    28     30   
................................................................................
   274    276       catchsql {
   275    277           select n from n2;
   276    278       }
   277    279   } {0 {0 1 0 3}}
   278    280   
   279    281   } ;# ifcapable view
   280    282   
          283  +
          284  +# ----------------------------------------------------------------------------
          285  +# 6.x - Test "DELETE FROM <table>" in the absence of triggers
          286  +#
          287  +do_test laststmtchanges-6.1 {
          288  +  execsql {
          289  +    CREATE TABLE t3(a, b, c);
          290  +    INSERT INTO t3 VALUES(1, 2, 3);
          291  +    INSERT INTO t3 VALUES(4, 5, 6);
          292  +  }
          293  +} {}
          294  +do_test laststmtchanges-6.2 {
          295  +  execsql {
          296  +    BEGIN;
          297  +    DELETE FROM t3;
          298  +    SELECT changes();
          299  +  }
          300  +} {2}
          301  +do_test laststmtchanges-6.3 {
          302  +  execsql {
          303  +    ROLLBACK;
          304  +    BEGIN;
          305  +    DELETE FROM t3 WHERE a IS NOT NULL;
          306  +    SELECT changes();
          307  +  }
          308  +} {2}
          309  +do_test laststmtchanges-6.4 {
          310  +  execsql {
          311  +    ROLLBACK;
          312  +    CREATE INDEX t3_i1 ON t3(a);
          313  +    BEGIN;
          314  +    DELETE FROM t3;
          315  +    SELECT changes();
          316  +  }
          317  +} {2}
          318  +do_test laststmtchanges-6.5 {
          319  +  execsql { ROLLBACK }
          320  +  set nTotalChange [execsql {SELECT total_changes()}]
          321  +  expr 0
          322  +} {0}
          323  +do_test laststmtchanges-6.6 {
          324  +  execsql {
          325  +    SELECT total_changes();
          326  +    DELETE FROM t3;
          327  +    SELECT total_changes();
          328  +  }
          329  +} [list $nTotalChange [expr $nTotalChange+2]]
          330  +
   281    331   finish_test