/ Check-in [daea156e]
Login

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

Overview
Comment:Enhancements to the DELETE command (CVS 194)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:daea156e2430762e683ff5460f9f8bb3204ae168
User & Date: drh 2001-03-20 22:05:00
Context
2001-03-20
22:15
Version 1.0.27 (CVS 476) check-in: 833291c2 user: drh tags: trunk
22:05
Enhancements to the DELETE command (CVS 194) check-in: daea156e user: drh tags: trunk
13:00
Version 1.0.26 (CVS 477) check-in: 99f9ea41 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -1.0.26
            1  +1.0.27

Changes to src/dbbe.c.

    26     26   ** sqlite and the code that does the actually reading and writing
    27     27   ** of information to the disk.
    28     28   **
    29     29   ** This file uses GDBM as the database backend.  It should be
    30     30   ** relatively simple to convert to a different database such
    31     31   ** as NDBM, SDBM, or BerkeleyDB.
    32     32   **
    33         -** $Id: dbbe.c,v 1.24 2001/03/20 12:55:14 drh Exp $
           33  +** $Id: dbbe.c,v 1.25 2001/03/20 22:05:00 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <unistd.h>
    37     37   
    38     38   /*
    39     39   ** This routine opens a new database.  It looks at the first
    40     40   ** few characters of the database name to try to determine what
................................................................................
    60     60     if( strncmp(zName, "memory:", 7)==0 ){
    61     61       extern Dbbe *sqliteMemOpen(const char*,int,int,char**);
    62     62       return sqliteMemOpen(&zName[7], writeFlag, createFlag, pzErrMsg);
    63     63     }
    64     64     return sqliteGdbmOpen(zName, writeFlag, createFlag, pzErrMsg);
    65     65   }
    66     66   
    67         -/*
    68         -** Open a temporary file.  The file should be deleted when closed.
    69         -**
    70         -** Note that we can't use the old Unix trick of opening the file
    71         -** and then immediately unlinking the file.  That works great
    72         -** under Unix, but fails when we try to port to Windows.
    73         -*/
    74         -int sqliteDbbeOpenTempFile(const char *zDir, Dbbe *pBe, FILE **ppFile){
    75         -  char *zFile;         /* Full name of the temporary file */
    76         -  char zBuf[50];       /* Base name of the temporary file */
    77         -  int i;               /* Loop counter */
    78         -  int limit;           /* Prevent an infinite loop */
    79         -  int rc = SQLITE_OK;  /* Value returned by this function */
    80         -
    81         -  for(i=0; i<pBe->nTemp; i++){
    82         -    if( pBe->apTemp[i]==0 ) break;
    83         -  }
    84         -  if( i>=pBe->nTemp ){
    85         -    pBe->nTemp++;
    86         -    pBe->apTemp = sqliteRealloc(pBe->apTemp, pBe->nTemp*sizeof(FILE*) );
    87         -    pBe->azTemp = sqliteRealloc(pBe->azTemp, pBe->nTemp*sizeof(char*) );
    88         -  }
    89         -  if( pBe->apTemp==0 ){
    90         -    *ppFile = 0;
    91         -    return SQLITE_NOMEM;
    92         -  }
    93         -  limit = 4;
    94         -  zFile = 0;
    95         -  do{
    96         -    sqliteRandomName(zBuf, "/_temp_file_");
    97         -    sqliteFree(zFile);
    98         -    zFile = 0;
    99         -    sqliteSetString(&zFile, zDir, zBuf, 0);
   100         -  }while( access(zFile,0)==0 && limit-- >= 0 );
   101         -#if OS_WIN
   102         -  *ppFile = pBe->apTemp[i] = fopen(zFile, "w+b");
   103         -#else
   104         -  *ppFile = pBe->apTemp[i] = fopen(zFile, "w+");
   105         -#endif
   106         -  if( pBe->apTemp[i]==0 ){
   107         -    rc = SQLITE_ERROR;
   108         -    sqliteFree(zFile);
   109         -    pBe->azTemp[i] = 0;
   110         -  }else{
   111         -    pBe->azTemp[i] = zFile;
   112         -  }
   113         -  return rc;
   114         -}
   115         -
   116         -/*
   117         -** Close a temporary file opened using sqliteGdbmOpenTempFile()
   118         -*/
   119         -void sqliteDbbeCloseTempFile(Dbbe *pBe, FILE *f){
   120         -  int i;
   121         -  for(i=0; i<pBe->nTemp; i++){
   122         -    if( pBe->apTemp[i]==f ){
   123         -      unlink(pBe->azTemp[i]);
   124         -      sqliteFree(pBe->azTemp[i]);
   125         -      pBe->apTemp[i] = 0;
   126         -      pBe->azTemp[i] = 0;
   127         -      break;
   128         -    }
   129         -  }
   130         -  fclose(f);
   131         -}
   132         -
   133         -/*
   134         -** Close all temporary files that happen to still be open.
   135         -** This routine is called when the database is being closed.
   136         -*/
   137         -void sqliteDbbeCloseAllTempFiles(Dbbe *pBe){
   138         -  int i;
   139         -  for(i=0; i<pBe->nTemp; i++){
   140         -    if( pBe->apTemp[i]!=0 ){
   141         -      unlink(pBe->azTemp[i]);
   142         -      fclose(pBe->apTemp[i]);
   143         -      sqliteFree(pBe->azTemp[i]);
   144         -      pBe->apTemp[i] = 0;
   145         -      pBe->azTemp[i] = 0;
   146         -      break;
   147         -    }
   148         -  }
   149         -  sqliteFree(pBe->azTemp);
   150         -  sqliteFree(pBe->apTemp);
   151         -}
   152         -
   153     67   /*
   154     68   ** Translate the name of an SQL table (or index) into the name 
   155     69   ** of a file that holds the key/data pairs for that table or
   156     70   ** index.  Space to hold the filename is obtained from
   157     71   ** sqliteMalloc() and must be freed by the calling function.
   158     72   **
   159     73   ** zDir is the name of the directory in which the file should

Changes to src/dbbe.h.

    24     24   ** This file defines the interface to the database backend (Dbbe).
    25     25   **
    26     26   ** The database backend is designed to be as general as possible
    27     27   ** so that it can easily be replaced by a different backend.
    28     28   ** This library was originally designed to support the following
    29     29   ** backends: GDBM, NDBM, SDBM, Berkeley DB.
    30     30   **
    31         -** $Id: dbbe.h,v 1.10 2001/01/15 22:51:10 drh Exp $
           31  +** $Id: dbbe.h,v 1.11 2001/03/20 22:05:00 drh Exp $
    32     32   */
    33     33   #ifndef _SQLITE_DBBE_H_
    34     34   #define _SQLITE_DBBE_H_
    35     35   #include <stdio.h>
    36     36   
    37     37   /*
    38     38   ** The database backend supports two opaque structures.  A Dbbe is
................................................................................
   146    146     /* Write an entry into a table.  If another entry already exists with
   147    147     ** the same key, the old entry is discarded first.
   148    148     */
   149    149     int (*Put)(DbbeCursor*, int nKey, char *pKey, int nData, char *pData);
   150    150   
   151    151     /* Remove an entry from the table */
   152    152     int (*Delete)(DbbeCursor*, int nKey, char *pKey);
   153         -
   154         -  /* Open a file suitable for temporary storage */
   155         -  int (*OpenTempFile)(Dbbe*, FILE**);
   156         -
   157         -  /* Close a temporary file */
   158         -  void (*CloseTempFile)(Dbbe *, FILE *);
   159    153   };
   160    154   
   161    155   /*
   162    156   ** This is the structure returned by sqliteDbbeOpen().  It contains
   163    157   ** information common to all the different backend drivers.
   164    158   **
   165    159   ** The information in this structure (with the exception the method
................................................................................
   166    160   ** pointers in the Dbbe.x field) is intended to be visible to
   167    161   ** the backend drivers only.  Users should not access or modify
   168    162   ** this structure in any way other than the read the method pointers
   169    163   ** in Dbbe.x.
   170    164   */
   171    165   struct Dbbe {
   172    166     struct DbbeMethods *x; /* Backend-specific methods for database access */
   173         -  int nTemp;             /* Number of temporary files created */
   174         -  FILE **apTemp;         /* Space to hold temporary file pointers */
   175         -  char **azTemp;         /* Names of the temporary files */
          167  +  /* There used to be other information here, but it has since
          168  +  ** been removed.  */
   176    169   };
   177    170   
   178    171   #endif /* defined(_SQLITE_DBBE_H_) */

Changes to src/dbbegdbm.c.

    26     26   ** sqlite and the code that does the actually reading and writing
    27     27   ** of information to the disk.
    28     28   **
    29     29   ** This file uses GDBM as the database backend.  It should be
    30     30   ** relatively simple to convert to a different database such
    31     31   ** as NDBM, SDBM, or BerkeleyDB.
    32     32   **
    33         -** $Id: dbbegdbm.c,v 1.3 2001/01/15 22:51:10 drh Exp $
           33  +** $Id: dbbegdbm.c,v 1.4 2001/03/20 22:05:00 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <gdbm.h>
    37     37   #include <sys/stat.h>
    38     38   #include <unistd.h>
    39     39   #include <ctype.h>
    40     40   #include <time.h>
................................................................................
   106    106     BeFile *pFile, *pNext;
   107    107     for(pFile=pBe->pOpen; pFile; pFile=pNext){
   108    108       pNext = pFile->pNext;
   109    109       gdbm_close(pFile->dbf);
   110    110       memset(pFile, 0, sizeof(*pFile));   
   111    111       sqliteFree(pFile);
   112    112     }
   113         -  sqliteDbbeCloseAllTempFiles(pDbbe);
   114    113     memset(pBe, 0, sizeof(*pBe));
   115    114     sqliteFree(pBe);
   116    115   }
   117    116   
   118    117   /*
   119    118   ** Translate the name of an SQL table (or index) into the name 
   120    119   ** of a file that holds the key/data pairs for that table or
................................................................................
   537    536     key.dsize = nKey;
   538    537     key.dptr = pKey;
   539    538     rc = gdbm_delete(pCursr->pFile->dbf, key);
   540    539     if( rc ) rc = SQLITE_ERROR;
   541    540     return rc;
   542    541   }
   543    542   
   544         -/*
   545         -** Open a temporary file.  The file is located in the same directory
   546         -** as the rest of the database.
   547         -*/
   548         -static int sqliteGdbmOpenTempFile(Dbbe *pDbbe, FILE **ppFile){
   549         -  Dbbex *pBe = (Dbbex*)pDbbe;
   550         -  return sqliteDbbeOpenTempFile(pBe->zDir, pDbbe, ppFile);
   551         -}
   552         -
   553    543   /*
   554    544   ** This variable contains pointers to all of the access methods
   555    545   ** used to implement the GDBM backend.
   556    546   */
   557    547   static struct DbbeMethods gdbmMethods = {
   558    548     /* n         Close */   sqliteGdbmClose,
   559    549     /*      OpenCursor */   sqliteGdbmOpenCursor,
................................................................................
   569    559     /*       KeyLength */   sqliteGdbmKeyLength,
   570    560     /*      DataLength */   sqliteGdbmDataLength,
   571    561     /*         NextKey */   sqliteGdbmNextKey,
   572    562     /*          Rewind */   sqliteGdbmRewind,
   573    563     /*             New */   sqliteGdbmNew,
   574    564     /*             Put */   sqliteGdbmPut,
   575    565     /*          Delete */   sqliteGdbmDelete,
   576         -  /*    OpenTempFile */   sqliteGdbmOpenTempFile,
   577         -  /*   CloseTempFile */   sqliteDbbeCloseTempFile
   578    566   };
   579    567   
   580    568   
   581    569   /*
   582    570   ** This routine opens a new database.  For the GDBM driver
   583    571   ** implemented here, the database name is the name of the directory
   584    572   ** containing all the files of the database.

Changes to src/dbbemem.c.

    24     24   ** This file contains code to implement the database backend (DBBE)
    25     25   ** for sqlite.  The database backend is the interface between
    26     26   ** sqlite and the code that does the actually reading and writing
    27     27   ** of information to the disk.
    28     28   **
    29     29   ** This file uses an in-memory hash table as the database backend. 
    30     30   **
    31         -** $Id: dbbemem.c,v 1.10 2001/03/20 12:57:57 drh Exp $
           31  +** $Id: dbbemem.c,v 1.11 2001/03/20 22:05:00 drh Exp $
    32     32   */
    33     33   #include "sqliteInt.h"
    34     34   #include <sys/stat.h>
    35     35   #include <unistd.h>
    36     36   #include <ctype.h>
    37     37   #include <time.h>
    38     38   
................................................................................
   395    395     MTable *pTble;
   396    396     ArrayElem *j;
   397    397     for(j=ArrayFirst(&pBe->tables); j; j=ArrayNext(j)){
   398    398       pTble = ArrayData(j);
   399    399       deleteMTable(pTble);
   400    400     }
   401    401     ArrayClear(&pBe->tables);
   402         -  sqliteDbbeCloseAllTempFiles(pDbbe);
   403    402     memset(pBe, 0, sizeof(*pBe));
   404    403     sqliteFree(pBe);
   405    404   }
   406    405   
   407    406   /*
   408    407   ** Translate the name of an SQL table (or index) into its
   409    408   ** canonical name.
................................................................................
   712    711     data = ArrayInsert(&pCursr->pTble->data, key, data);
   713    712     if( data.p ){
   714    713       sqliteFree(data.p);
   715    714     }
   716    715     return SQLITE_OK;
   717    716   }
   718    717   
   719         -/*
   720         -** Open a temporary file.  The file is located in the current working
   721         -** directory.
   722         -*/
   723         -static int sqliteMemOpenTempFile(Dbbe *pDbbe, FILE **ppFile){
   724         -#if OS_UNIX
   725         -  const char *zTemps[] = { "/usr/tmp", "/var/tmp", "/tmp", "/temp", 0};
   726         -#endif
   727         -#if OS_WIN
   728         -  const char *zTemps[] = { "/temp", "c:/temp", "c:", "d:", "e:", 0};
   729         -#endif
   730         -  const char *zDir;
   731         -  int i;
   732         -  struct stat statbuf;
   733         -  for(i=0; zTemps[i]; i++){
   734         -    zDir = zTemps[i];
   735         -    if( stat(zDir, &statbuf)==0 && S_ISDIR(statbuf.st_mode) 
   736         -      && access(zDir, W_OK|X_OK)==0 ){
   737         -        break;
   738         -    }
   739         -  }
   740         -  if( zDir==0 ) zDir = ".";
   741         -  return sqliteDbbeOpenTempFile(zDir, pDbbe, ppFile);
   742         -}
   743         -
   744    718   /*
   745    719   ** This variable contains pointers to all of the access methods
   746    720   ** used to implement the MEMORY backend.
   747    721   */
   748    722   static struct DbbeMethods memoryMethods = {
   749    723     /* n         Close */   sqliteMemClose,
   750    724     /*      OpenCursor */   sqliteMemOpenCursor,
................................................................................
   760    734     /*       KeyLength */   sqliteMemKeyLength,
   761    735     /*      DataLength */   sqliteMemDataLength,
   762    736     /*         NextKey */   sqliteMemNextKey,
   763    737     /*          Rewind */   sqliteMemRewind,
   764    738     /*             New */   sqliteMemNew,
   765    739     /*             Put */   sqliteMemPut,
   766    740     /*          Delete */   sqliteMemDelete,
   767         -  /*    OpenTempFile */   sqliteMemOpenTempFile,
   768         -  /*   CloseTempFile */   sqliteDbbeCloseTempFile
   769    741   };
   770    742   
   771    743   /*
   772    744   ** This routine opens a new database.  For the GDBM driver
   773    745   ** implemented here, the database name is the name of the directory
   774    746   ** containing all the files of the database.
   775    747   **

Changes to src/delete.c.

    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** This file contains C code routines that are called by the parser
    25     25   ** to handle DELETE FROM statements.
    26     26   **
    27         -** $Id: delete.c,v 1.7 2001/01/15 22:51:10 drh Exp $
           27  +** $Id: delete.c,v 1.8 2001/03/20 22:05:00 drh Exp $
    28     28   */
    29     29   #include "sqliteInt.h"
    30     30   
    31     31   /*
    32     32   ** Process a DELETE FROM statement.
    33     33   */
    34     34   void sqliteDeleteFrom(
................................................................................
    81     81     }
    82     82   
    83     83     /* Begin generating code.
    84     84     */
    85     85     v = sqliteGetVdbe(pParse);
    86     86     if( v==0 ) goto delete_from_cleanup;
    87     87   
    88         -  /* Begin the database scan
    89         -  */
    90         -  sqliteVdbeAddOp(v, OP_ListOpen, 0, 0, 0, 0);
    91         -  pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1);
    92         -  if( pWInfo==0 ) goto delete_from_cleanup;
    93         -
    94         -  /* Remember the key of every item to be deleted.
           88  +  /* Special case: A DELETE without a WHERE clause deletes everything.
           89  +  ** It is easier just to deleted the database files directly.
    95     90     */
    96         -  sqliteVdbeAddOp(v, OP_ListWrite, 0, 0, 0, 0);
    97         -
    98         -  /* End the database scan loop.
    99         -  */
   100         -  sqliteWhereEnd(pWInfo);
   101         -
   102         -  /* Delete every item whose key was written to the list during the
   103         -  ** database scan.  We have to delete items after the scan is complete
   104         -  ** because deleting an item can change the scan order.
   105         -  */
   106         -  base = pParse->nTab;
   107         -  sqliteVdbeAddOp(v, OP_ListRewind, 0, 0, 0, 0);
   108         -  sqliteVdbeAddOp(v, OP_OpenTbl, base, 1, pTab->zName, 0);
   109         -  for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   110         -    sqliteVdbeAddOp(v, OP_OpenIdx, base+i, 1, pIdx->zName, 0);
   111         -  }
   112         -  end = sqliteVdbeMakeLabel(v);
   113         -  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end, 0, 0);
   114         -  if( pTab->pIndex ){
   115         -    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
   116         -    sqliteVdbeAddOp(v, OP_Fetch, base, 0, 0, 0);
   117         -    for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   118         -      int j;
   119         -      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
   120         -      for(j=0; j<pIdx->nColumn; j++){
   121         -        sqliteVdbeAddOp(v, OP_Field, base, pIdx->aiColumn[j], 0, 0);
   122         -      }
   123         -      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
   124         -      sqliteVdbeAddOp(v, OP_DeleteIdx, base+i, 0, 0, 0);
           91  +  if( pWhere==0 ){
           92  +    sqliteVdbeAddOp(v, OP_Destroy, 0, 0, pTab->zName, 0);
           93  +    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
           94  +      sqliteVdbeAddOp(v, OP_Destroy, 0, 0, pIdx->zName, 0);
   125     95       }
   126     96     }
   127         -  sqliteVdbeAddOp(v, OP_Delete, base, 0, 0, 0);
   128         -  sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
   129         -  sqliteVdbeAddOp(v, OP_ListClose, 0, 0, 0, end);
           97  +
           98  +  /* The usual case: There is a WHERE clause so we have to scan through
           99  +  ** the table an pick which records to delete.
          100  +  */
          101  +  else{
          102  +    /* Begin the database scan
          103  +    */
          104  +    sqliteVdbeAddOp(v, OP_ListOpen, 0, 0, 0, 0);
          105  +    pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1);
          106  +    if( pWInfo==0 ) goto delete_from_cleanup;
          107  +
          108  +    /* Remember the key of every item to be deleted.
          109  +    */
          110  +    sqliteVdbeAddOp(v, OP_ListWrite, 0, 0, 0, 0);
          111  +
          112  +    /* End the database scan loop.
          113  +    */
          114  +    sqliteWhereEnd(pWInfo);
          115  +
          116  +    /* Delete every item whose key was written to the list during the
          117  +    ** database scan.  We have to delete items after the scan is complete
          118  +    ** because deleting an item can change the scan order.
          119  +    */
          120  +    base = pParse->nTab;
          121  +    sqliteVdbeAddOp(v, OP_ListRewind, 0, 0, 0, 0);
          122  +    sqliteVdbeAddOp(v, OP_OpenTbl, base, 1, pTab->zName, 0);
          123  +    for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          124  +      sqliteVdbeAddOp(v, OP_OpenIdx, base+i, 1, pIdx->zName, 0);
          125  +    }
          126  +    end = sqliteVdbeMakeLabel(v);
          127  +    addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end, 0, 0);
          128  +    if( pTab->pIndex ){
          129  +      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
          130  +      sqliteVdbeAddOp(v, OP_Fetch, base, 0, 0, 0);
          131  +      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          132  +        int j;
          133  +        sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
          134  +        for(j=0; j<pIdx->nColumn; j++){
          135  +          sqliteVdbeAddOp(v, OP_Field, base, pIdx->aiColumn[j], 0, 0);
          136  +        }
          137  +        sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
          138  +        sqliteVdbeAddOp(v, OP_DeleteIdx, base+i, 0, 0, 0);
          139  +      }
          140  +    }
          141  +    sqliteVdbeAddOp(v, OP_Delete, base, 0, 0, 0);
          142  +    sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
          143  +    sqliteVdbeAddOp(v, OP_ListClose, 0, 0, 0, end);
          144  +  }
   130    145   
   131    146   delete_from_cleanup:
   132    147     sqliteIdListDelete(pTabList);
   133    148     sqliteExprDelete(pWhere);
   134    149     return;
   135    150   }

Changes to src/sqliteInt.h.

    19     19   ** Author contact information:
    20     20   **   drh@hwaci.com
    21     21   **   http://www.hwaci.com/drh/
    22     22   **
    23     23   *************************************************************************
    24     24   ** Internal interface definitions for SQLite.
    25     25   **
    26         -** @(#) $Id: sqliteInt.h,v 1.36 2001/01/20 19:52:50 drh Exp $
           26  +** @(#) $Id: sqliteInt.h,v 1.37 2001/03/20 22:05:00 drh Exp $
    27     27   */
    28     28   #include "sqlite.h"
    29     29   #include "dbbe.h"
    30     30   #include "vdbe.h"
    31     31   #include "parse.h"
    32     32   #include <gdbm.h>
    33     33   #include <stdio.h>
................................................................................
   423    423   void sqliteExprResolveInSelect(Parse*, Expr*);
   424    424   int sqliteExprAnalyzeAggregates(Parse*, Expr*);
   425    425   void sqliteParseInfoReset(Parse*);
   426    426   Vdbe *sqliteGetVdbe(Parse*);
   427    427   int sqliteRandomByte(void);
   428    428   int sqliteRandomInteger(void);
   429    429   void sqliteRandomName(char*,char*);
   430         -int sqliteDbbeOpenTempFile(const char*, Dbbe*, FILE**);
   431         -void sqliteDbbeCloseTempFile(Dbbe*, FILE*);
   432         -void sqliteDbbeCloseAllTempFiles(Dbbe*);
   433    430   char *sqliteDbbeNameToFile(const char*,const char*,const char*);

Changes to src/vdbe.c.

    37     37   ** inplicit conversion from one type to the other occurs as necessary.
    38     38   ** 
    39     39   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    40     40   ** function which does the work of interpreting a VDBE program.
    41     41   ** But other routines are also provided to help in building up
    42     42   ** a program instruction by instruction.
    43     43   **
    44         -** $Id: vdbe.c,v 1.52 2001/02/19 23:23:39 drh Exp $
           44  +** $Id: vdbe.c,v 1.53 2001/03/20 22:05:00 drh Exp $
    45     45   */
    46     46   #include "sqliteInt.h"
    47     47   #include <unistd.h>
    48     48   #include <ctype.h>
    49     49   
    50     50   /*
    51     51   ** SQL is translated into a sequence of instructions to be
................................................................................
   163    163   };
   164    164   struct SetElem {
   165    165     SetElem *pHash;        /* Next element with the same hash on zKey */
   166    166     SetElem *pNext;        /* Next element in a list of them all */
   167    167     char zKey[1];          /* Value of this key */
   168    168   };
   169    169   
          170  +/*
          171  +** A Keylist is a bunch of keys into a table.  The keylist can
          172  +** grow without bound.  The keylist stores the keys of database
          173  +** records that need to be deleted.
          174  +*/
          175  +typedef struct Keylist Keylist;
          176  +struct Keylist {
          177  +  int nKey;         /* Number of slots in aKey[] */
          178  +  int nUsed;        /* Next unwritten slot in aKey[] */
          179  +  int nRead;        /* Next unread slot in aKey[] */
          180  +  Keylist *pNext;   /* Next block of keys */
          181  +  int aKey[1];      /* One or more keys.  Extra space allocated as needed */
          182  +};
          183  +
   170    184   /*
   171    185   ** An instance of the virtual machine
   172    186   */
   173    187   struct Vdbe {
   174    188     sqlite *db;         /* The whole database */
   175    189     Dbbe *pBe;          /* Opaque context structure used by DB backend */
   176    190     FILE *trace;        /* Write an execution trace here, if not NULL */
................................................................................
   184    198     int nStackAlloc;    /* Size of the stack */
   185    199     Stack *aStack;      /* The operand stack, except string values */
   186    200     char **zStack;      /* Text or binary values of the stack */
   187    201     char **azColName;   /* Becomes the 4th parameter to callbacks */
   188    202     int nCursor;        /* Number of slots in aCsr[] */
   189    203     Cursor *aCsr;       /* On element of this array for each open cursor */
   190    204     int nList;          /* Number of slots in apList[] */
   191         -  FILE **apList;      /* An open file for each list */
          205  +  Keylist **apList;   /* For each Keylist */
   192    206     int nSort;          /* Number of slots in apSort[] */
   193    207     Sorter **apSort;    /* An open sorter list */
   194    208     FILE *pFile;        /* At most one open file handler */
   195    209     int nField;         /* Number of file fields */
   196    210     char **azField;     /* Data for each file field */
   197    211     char *zLine;        /* A single line from the input file */
   198    212     int nLineAlloc;     /* Number of spaces allocated for zLine */
................................................................................
   680    694       for(i=oldAlloc; i<p->nStackAlloc; i++){
   681    695         p->zStack[i] = 0;
   682    696         p->aStack[i].flags = 0;
   683    697       }
   684    698     }
   685    699     return 0;
   686    700   }
          701  +
          702  +/*
          703  +** Delete a keylist
          704  +*/
          705  +static void KeylistFree(Keylist *p){
          706  +  while( p ){
          707  +    Keylist *pNext = p->pNext;
          708  +    sqliteFree(p);
          709  +    p = pNext;
          710  +  }
          711  +}
   687    712   
   688    713   /*
   689    714   ** Clean up the VM after execution.
   690    715   **
   691    716   ** This routine will automatically close any cursors, list, and/or
   692    717   ** sorters that were left open.
   693    718   */
................................................................................
   710    735         sqliteFree(p->aMem[i].z);
   711    736       }
   712    737     }
   713    738     sqliteFree(p->aMem);
   714    739     p->aMem = 0;
   715    740     p->nMem = 0;
   716    741     for(i=0; i<p->nList; i++){
   717         -    if( p->apList[i] ){
   718         -      p->pBe->x->CloseTempFile(p->pBe, p->apList[i]);
   719         -      p->apList[i] = 0;
   720         -    }
          742  +    KeylistFree(p->apList[i]);
          743  +    p->apList[i] = 0;
   721    744     }
   722    745     sqliteFree(p->apList);
   723    746     p->apList = 0;
   724    747     p->nList = 0;
   725    748     for(i=0; i<p->nSort; i++){
   726    749       Sorter *pSorter;
   727    750       while( (pSorter = p->apSort[i])!=0 ){
................................................................................
  2427   2450         case OP_Reorganize: {
  2428   2451           pBex->ReorganizeTable(pBe, pOp->p3);
  2429   2452           break;
  2430   2453         }
  2431   2454   
  2432   2455         /* Opcode: ListOpen P1 * *
  2433   2456         **
  2434         -      ** Open a file used for temporary storage of integer table keys.  P1
  2435         -      ** will server as a handle to this temporary file for future
  2436         -      ** interactions.  If another temporary file with the P1 handle is
  2437         -      ** already opened, the prior file is closed and a new one opened
         2457  +      ** Open a "List" structure used for temporary storage of integer 
         2458  +      ** table keys.  P1
         2459  +      ** will server as a handle to this list for future
         2460  +      ** interactions.  If another list with the P1 handle is
         2461  +      ** already opened, the prior list is closed and a new one opened
  2438   2462         ** in its place.
  2439   2463         */
  2440   2464         case OP_ListOpen: {
  2441   2465           int i = pOp->p1;
  2442   2466           VERIFY( if( i<0 ) goto bad_instruction; )
  2443   2467           if( i>=p->nList ){
  2444   2468             int j;
  2445         -          p->apList = sqliteRealloc( p->apList, (i+1)*sizeof(FILE*) );
         2469  +          p->apList = sqliteRealloc( p->apList, (i+1)*sizeof(Keylist*) );
  2446   2470             if( p->apList==0 ){ p->nList = 0; goto no_mem; }
  2447   2471             for(j=p->nList; j<=i; j++) p->apList[j] = 0;
  2448   2472             p->nList = i+1;
  2449   2473           }else if( p->apList[i] ){
  2450         -          pBex->CloseTempFile(pBe, p->apList[i]);
  2451         -        }
  2452         -        rc = pBex->OpenTempFile(pBe, &p->apList[i]);
  2453         -        if( rc!=SQLITE_OK ){
  2454         -          sqliteSetString(pzErrMsg, "unable to open a temporary file", 0);
         2474  +          KeylistFree(p->apList[i]);
         2475  +          p->apList[i] = 0;
  2455   2476           }
  2456   2477           break;
  2457   2478         }
  2458   2479   
  2459   2480         /* Opcode: ListWrite P1 * *
  2460   2481         **
  2461   2482         ** Write the integer on the top of the stack
  2462         -      ** into the temporary storage file P1.
         2483  +      ** into the temporary storage list P1.
  2463   2484         */
  2464   2485         case OP_ListWrite: {
  2465   2486           int i = pOp->p1;
  2466         -        VERIFY( if( i<0 ) goto bad_instruction; )
         2487  +        Keylist *pKeylist;
         2488  +        VERIFY( if( i<0 || i>=p->nList ) goto bad_instruction; )
  2467   2489           VERIFY( if( p->tos<0 ) goto not_enough_stack; )
  2468         -        if( VERIFY( i<p->nList && ) p->apList[i]!=0 ){
  2469         -          int val;
  2470         -          Integerify(p, p->tos);
  2471         -          val = aStack[p->tos].i;
  2472         -          POPSTACK;
  2473         -          fwrite(&val, sizeof(int), 1, p->apList[i]);
         2490  +        pKeylist = p->apList[i];
         2491  +        if( pKeylist==0 || pKeylist->nUsed>=pKeylist->nKey ){
         2492  +          pKeylist = sqliteMalloc( sizeof(Keylist)+999*sizeof(int) );
         2493  +          if( pKeylist==0 ) goto no_mem;
         2494  +          pKeylist->nKey = 1000;
         2495  +          pKeylist->nRead = 0;
         2496  +          pKeylist->nUsed = 0;
         2497  +          pKeylist->pNext = p->apList[i];
         2498  +          p->apList[i] = pKeylist;
  2474   2499           }
         2500  +        Integerify(p, p->tos);
         2501  +        pKeylist->aKey[pKeylist->nUsed++] = aStack[p->tos].i;
         2502  +        POPSTACK;
  2475   2503           break;
  2476   2504         }
  2477   2505   
  2478   2506         /* Opcode: ListRewind P1 * *
  2479   2507         **
  2480   2508         ** Rewind the temporary buffer P1 back to the beginning.
  2481   2509         */
  2482   2510         case OP_ListRewind: {
  2483   2511           int i = pOp->p1;
  2484   2512           VERIFY( if( i<0 ) goto bad_instruction; )
  2485         -        if( VERIFY( i<p->nList && ) p->apList[i]!=0 ){
  2486         -          rewind(p->apList[i]);
  2487         -        }
         2513  +        /* This is now a no-op */
  2488   2514           break;
  2489   2515         }
  2490   2516   
  2491   2517         /* Opcode: ListRead P1 P2 *
  2492   2518         **
  2493   2519         ** Attempt to read an integer from temporary storage buffer P1
  2494   2520         ** and push it onto the stack.  If the storage buffer is empty, 
  2495   2521         ** push nothing but instead jump to P2.
  2496   2522         */
  2497   2523         case OP_ListRead: {
  2498   2524           int i = pOp->p1;
  2499   2525           int val, amt;
  2500         -        VERIFY(if( i<0 || i>=p->nList || p->apList[i]==0 )goto bad_instruction;)
  2501         -        amt = fread(&val, sizeof(int), 1, p->apList[i]);
  2502         -        if( amt==1 ){
         2526  +        Keylist *pKeylist;
         2527  +        VERIFY(if( i<0 || i>=p->nList ) goto bad_instruction;)
         2528  +        pKeylist = p->apList[i];
         2529  +        if( pKeylist!=0 ){
         2530  +          VERIFY(
         2531  +            if( pKeylist->nRead<0 
         2532  +              || pKeylist->nRead>=pKeylist->nUsed
         2533  +              || pKeylist->nRead>=pKeylist->nKey ) goto bad_instruction;
         2534  +          )
  2503   2535             p->tos++;
  2504   2536             if( NeedStack(p, p->tos) ) goto no_mem;
  2505         -          aStack[p->tos].i = val;
         2537  +          aStack[p->tos].i = pKeylist->aKey[pKeylist->nRead++];
  2506   2538             aStack[p->tos].flags = STK_Int;
  2507   2539             zStack[p->tos] = 0;
         2540  +          if( pKeylist->nRead>=pKeylist->nUsed ){
         2541  +            p->apList[i] = pKeylist->pNext;
         2542  +            sqliteFree(pKeylist);
         2543  +          }
  2508   2544           }else{
  2509   2545             pc = pOp->p2 - 1;
  2510   2546           }
  2511   2547           break;
  2512   2548         }
  2513   2549   
  2514   2550         /* Opcode: ListClose P1 * *
  2515   2551         **
  2516   2552         ** Close the temporary storage buffer and discard its contents.
  2517   2553         */
  2518   2554         case OP_ListClose: {
  2519   2555           int i = pOp->p1;
  2520   2556           VERIFY( if( i<0 ) goto bad_instruction; )
  2521         -        if( VERIFY( i<p->nList && ) p->apList[i]!=0 ){
  2522         -          pBex->CloseTempFile(pBe, p->apList[i]);
  2523         -          p->apList[i] = 0;
  2524         -        }
         2557  +        VERIFY( if( i>=p->nList ) goto bad_instruction; )
         2558  +        KeylistFree(p->apList[i]);
         2559  +        p->apList[i] = 0;
  2525   2560           break;
  2526   2561         }
  2527   2562   
  2528   2563         /* Opcode: SortOpen P1 * *
  2529   2564         **
  2530   2565         ** Create a new sorter with index P1
  2531   2566         */

Changes to test/delete.test.

    19     19   #   drh@hwaci.com
    20     20   #   http://www.hwaci.com/drh/
    21     21   #
    22     22   #***********************************************************************
    23     23   # This file implements regression tests for SQLite library.  The
    24     24   # focus of this file is testing the DELETE FROM statement.
    25     25   #
    26         -# $Id: delete.test,v 1.7 2001/03/20 12:55:14 drh Exp $
           26  +# $Id: delete.test,v 1.8 2001/03/20 22:05:00 drh Exp $
    27     27   
    28     28   set testdir [file dirname $argv0]
    29     29   source $testdir/tester.tcl
    30     30   
    31     31   # Try to delete from a non-existant table.
    32     32   #
    33     33   do_test delete-1.1 {
................................................................................
   113    113     }
   114    114     execsql {SELECT f1 FROM table1 ORDER BY f1}
   115    115   } {42 44 47 48 50}
   116    116   do_test delete-5.7 {
   117    117     execsql "DELETE FROM table1 WHERE f1!=48"
   118    118     execsql {SELECT f1 FROM table1 ORDER BY f1}
   119    119   } {48}
          120  +
          121  +# Delete large quantities of data.  We want to test the List overflow
          122  +# mechanism in the vdbe.
          123  +#
          124  +do_test delete-6.1 {
          125  +  set fd [open data1.txt w]
          126  +  for {set i 1} {$i<=3000} {incr i} {
          127  +    puts $fd "[expr {$i}]\t[expr {$i*$i}]"
          128  +  }
          129  +  close $fd
          130  +  execsql {DELETE FROM table1}
          131  +  execsql {COPY table1 FROM 'data1.txt'}
          132  +  execsql {DELETE FROM table2}
          133  +  execsql {COPY table2 FROM 'data1.txt'}
          134  +  file delete data1.txt
          135  +  execsql {SELECT count(*) FROM table1}
          136  +} {3000}
          137  +do_test delete-6.2 {
          138  +  execsql {SELECT count(*) FROM table2}
          139  +} {3000}
          140  +do_test delete-6.3 {
          141  +  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
          142  +} {1 2 3 4 5 6 7 8 9}
          143  +do_test delete-6.4 {
          144  +  execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
          145  +} {1 2 3 4 5 6 7 8 9}
          146  +do_test delete-6.5 {
          147  +  execsql {DELETE FROM table1 WHERE f1>7}
          148  +  execsql {SELECT f1 FROM table1 ORDER BY f1}
          149  +} {1 2 3 4 5 6 7}
          150  +do_test delete-6.6 {
          151  +  execsql {DELETE FROM table2 WHERE f1>7}
          152  +  execsql {SELECT f1 FROM table2 ORDER BY f1}
          153  +} {1 2 3 4 5 6 7}
          154  +do_test delete-6.7 {
          155  +  execsql {DELETE FROM table1}
          156  +  execsql {SELECT f1 FROM table1}
          157  +} {}
          158  +do_test delete-6.8 {
          159  +  execsql {INSERT INTO table1 VALUES(2,3)}
          160  +  execsql {SELECT f1 FROM table1}
          161  +} {2}
          162  +do_test delete-6.9 {
          163  +  execsql {DELETE FROM table2}
          164  +  execsql {SELECT f1 FROM table2}
          165  +} {}
          166  +do_test delete-6.10 {
          167  +  execsql {INSERT INTO table2 VALUES(2,3)}
          168  +  execsql {SELECT f1 FROM table2}
          169  +} {2}
          170  +
          171  +
   120    172   
   121    173   finish_test

Changes to www/changes.tcl.

    12     12   }
    13     13   
    14     14   
    15     15   proc chng {date desc} {
    16     16     puts "<DT><B>$date</B></DT>"
    17     17     puts "<DD><P><UL>$desc</UL></P></DD>"
    18     18   }
           19  +
           20  +chng {2001 Mar 20 (1.0.27)} {
           21  +<li>When doing DELETE and UPDATE, the library used to write the record
           22  +    numbers of records to be deleted or updated into a temporary file.
           23  +    This is changed so that the record numbers are held in memory.</li>
           24  +<li>The DELETE command without a WHILE clause just removes the database
           25  +    files from the disk, rather than going through and deleting record
           26  +    by record.</li>
           27  +}
    19     28   
    20     29   chng {2001 Mar 20 (1.0.26)} {
    21     30   <li>A serious bug fixed on Windows.  Windows users should upgrade.
    22     31       No impact to Unix.</li>
    23     32   }
    24     33   
    25     34   chng {2001 Mar 15 (1.0.25)} {