Back to index

php5  5.3.10
delete.c
Go to the documentation of this file.
00001 /*
00002 ** 2001 September 15
00003 **
00004 ** The author disclaims copyright to this source code.  In place of
00005 ** a legal notice, here is a blessing:
00006 **
00007 **    May you do good and not evil.
00008 **    May you find forgiveness for yourself and forgive others.
00009 **    May you share freely, never taking more than you give.
00010 **
00011 *************************************************************************
00012 ** This file contains C code routines that are called by the parser
00013 ** to handle DELETE FROM statements.
00014 **
00015 ** $Id: delete.c 195361 2005-09-07 15:11:33Z iliaa $
00016 */
00017 #include "sqliteInt.h"
00018 
00019 /*
00020 ** Look up every table that is named in pSrc.  If any table is not found,
00021 ** add an error message to pParse->zErrMsg and return NULL.  If all tables
00022 ** are found, return a pointer to the last table.
00023 */
00024 Table *sqliteSrcListLookup(Parse *pParse, SrcList *pSrc){
00025   Table *pTab = 0;
00026   int i;
00027   for(i=0; i<pSrc->nSrc; i++){
00028     const char *zTab = pSrc->a[i].zName;
00029     const char *zDb = pSrc->a[i].zDatabase;
00030     pTab = sqliteLocateTable(pParse, zTab, zDb);
00031     pSrc->a[i].pTab = pTab;
00032   }
00033   return pTab;
00034 }
00035 
00036 /*
00037 ** Check to make sure the given table is writable.  If it is not
00038 ** writable, generate an error message and return 1.  If it is
00039 ** writable return 0;
00040 */
00041 int sqliteIsReadOnly(Parse *pParse, Table *pTab, int viewOk){
00042   if( pTab->readOnly ){
00043     sqliteErrorMsg(pParse, "table %s may not be modified", pTab->zName);
00044     return 1;
00045   }
00046   if( !viewOk && pTab->pSelect ){
00047     sqliteErrorMsg(pParse, "cannot modify %s because it is a view",pTab->zName);
00048     return 1;
00049   }
00050   return 0;
00051 }
00052 
00053 /*
00054 ** Process a DELETE FROM statement.
00055 */
00056 void sqliteDeleteFrom(
00057   Parse *pParse,         /* The parser context */
00058   SrcList *pTabList,     /* The table from which we should delete things */
00059   Expr *pWhere           /* The WHERE clause.  May be null */
00060 ){
00061   Vdbe *v;               /* The virtual database engine */
00062   Table *pTab;           /* The table from which records will be deleted */
00063   const char *zDb;       /* Name of database holding pTab */
00064   int end, addr;         /* A couple addresses of generated code */
00065   int i;                 /* Loop counter */
00066   WhereInfo *pWInfo;     /* Information about the WHERE clause */
00067   Index *pIdx;           /* For looping over indices of the table */
00068   int iCur;              /* VDBE Cursor number for pTab */
00069   sqlite *db;            /* Main database structure */
00070   int isView;            /* True if attempting to delete from a view */
00071   AuthContext sContext;  /* Authorization context */
00072 
00073   int row_triggers_exist = 0;  /* True if any triggers exist */
00074   int before_triggers;         /* True if there are BEFORE triggers */
00075   int after_triggers;          /* True if there are AFTER triggers */
00076   int oldIdx = -1;             /* Cursor for the OLD table of AFTER triggers */
00077 
00078   sContext.pParse = 0;
00079   if( pParse->nErr || sqlite_malloc_failed ){
00080     pTabList = 0;
00081     goto delete_from_cleanup;
00082   }
00083   db = pParse->db;
00084   assert( pTabList->nSrc==1 );
00085 
00086   /* Locate the table which we want to delete.  This table has to be
00087   ** put in an SrcList structure because some of the subroutines we
00088   ** will be calling are designed to work with multiple tables and expect
00089   ** an SrcList* parameter instead of just a Table* parameter.
00090   */
00091   pTab = sqliteSrcListLookup(pParse, pTabList);
00092   if( pTab==0 )  goto delete_from_cleanup;
00093   before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, 
00094                          TK_DELETE, TK_BEFORE, TK_ROW, 0);
00095   after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, 
00096                          TK_DELETE, TK_AFTER, TK_ROW, 0);
00097   row_triggers_exist = before_triggers || after_triggers;
00098   isView = pTab->pSelect!=0;
00099   if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
00100     goto delete_from_cleanup;
00101   }
00102   assert( pTab->iDb<db->nDb );
00103   zDb = db->aDb[pTab->iDb].zName;
00104   if( sqliteAuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){
00105     goto delete_from_cleanup;
00106   }
00107 
00108   /* If pTab is really a view, make sure it has been initialized.
00109   */
00110   if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
00111     goto delete_from_cleanup;
00112   }
00113 
00114   /* Allocate a cursor used to store the old.* data for a trigger.
00115   */
00116   if( row_triggers_exist ){ 
00117     oldIdx = pParse->nTab++;
00118   }
00119 
00120   /* Resolve the column names in all the expressions.
00121   */
00122   assert( pTabList->nSrc==1 );
00123   iCur = pTabList->a[0].iCursor = pParse->nTab++;
00124   if( pWhere ){
00125     if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
00126       goto delete_from_cleanup;
00127     }
00128     if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
00129       goto delete_from_cleanup;
00130     }
00131   }
00132 
00133   /* Start the view context
00134   */
00135   if( isView ){
00136     sqliteAuthContextPush(pParse, &sContext, pTab->zName);
00137   }
00138 
00139   /* Begin generating code.
00140   */
00141   v = sqliteGetVdbe(pParse);
00142   if( v==0 ){
00143     goto delete_from_cleanup;
00144   }
00145   sqliteBeginWriteOperation(pParse, row_triggers_exist, pTab->iDb);
00146 
00147   /* If we are trying to delete from a view, construct that view into
00148   ** a temporary table.
00149   */
00150   if( isView ){
00151     Select *pView = sqliteSelectDup(pTab->pSelect);
00152     sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
00153     sqliteSelectDelete(pView);
00154   }
00155 
00156   /* Initialize the counter of the number of rows deleted, if
00157   ** we are counting rows.
00158   */
00159   if( db->flags & SQLITE_CountRows ){
00160     sqliteVdbeAddOp(v, OP_Integer, 0, 0);
00161   }
00162 
00163   /* Special case: A DELETE without a WHERE clause deletes everything.
00164   ** It is easier just to erase the whole table.  Note, however, that
00165   ** this means that the row change count will be incorrect.
00166   */
00167   if( pWhere==0 && !row_triggers_exist ){
00168     if( db->flags & SQLITE_CountRows ){
00169       /* If counting rows deleted, just count the total number of
00170       ** entries in the table. */
00171       int endOfLoop = sqliteVdbeMakeLabel(v);
00172       int addr;
00173       if( !isView ){
00174         sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
00175         sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
00176       }
00177       sqliteVdbeAddOp(v, OP_Rewind, iCur, sqliteVdbeCurrentAddr(v)+2);
00178       addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
00179       sqliteVdbeAddOp(v, OP_Next, iCur, addr);
00180       sqliteVdbeResolveLabel(v, endOfLoop);
00181       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00182     }
00183     if( !isView ){
00184       sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb);
00185       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
00186         sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb);
00187       }
00188     }
00189   }
00190 
00191   /* The usual case: There is a WHERE clause so we have to scan through
00192   ** the table and pick which records to delete.
00193   */
00194   else{
00195     /* Begin the database scan
00196     */
00197     pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
00198     if( pWInfo==0 ) goto delete_from_cleanup;
00199 
00200     /* Remember the key of every item to be deleted.
00201     */
00202     sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
00203     if( db->flags & SQLITE_CountRows ){
00204       sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
00205     }
00206 
00207     /* End the database scan loop.
00208     */
00209     sqliteWhereEnd(pWInfo);
00210 
00211     /* Open the pseudo-table used to store OLD if there are triggers.
00212     */
00213     if( row_triggers_exist ){
00214       sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0);
00215     }
00216 
00217     /* Delete every item whose key was written to the list during the
00218     ** database scan.  We have to delete items after the scan is complete
00219     ** because deleting an item can change the scan order.
00220     */
00221     sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
00222     end = sqliteVdbeMakeLabel(v);
00223 
00224     /* This is the beginning of the delete loop when there are
00225     ** row triggers.
00226     */
00227     if( row_triggers_exist ){
00228       addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
00229       sqliteVdbeAddOp(v, OP_Dup, 0, 0);
00230       if( !isView ){
00231         sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
00232         sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
00233       }
00234       sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
00235 
00236       sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
00237       sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
00238       sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
00239       if( !isView ){
00240         sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00241       }
00242 
00243       sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
00244           oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
00245          addr);
00246     }
00247 
00248     if( !isView ){
00249       /* Open cursors for the table we are deleting from and all its
00250       ** indices.  If there are row triggers, this happens inside the
00251       ** OP_ListRead loop because the cursor have to all be closed
00252       ** before the trigger fires.  If there are no row triggers, the
00253       ** cursors are opened only once on the outside the loop.
00254       */
00255       pParse->nTab = iCur + 1;
00256       sqliteOpenTableAndIndices(pParse, pTab, iCur);
00257 
00258       /* This is the beginning of the delete loop when there are no
00259       ** row triggers */
00260       if( !row_triggers_exist ){ 
00261         addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
00262       }
00263 
00264       /* Delete the row */
00265       sqliteGenerateRowDelete(db, v, pTab, iCur, pParse->trigStack==0);
00266     }
00267 
00268     /* If there are row triggers, close all cursors then invoke
00269     ** the AFTER triggers
00270     */
00271     if( row_triggers_exist ){
00272       if( !isView ){
00273         for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
00274           sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
00275         }
00276         sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00277       }
00278       sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
00279           oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
00280          addr);
00281     }
00282 
00283     /* End of the delete loop */
00284     sqliteVdbeAddOp(v, OP_Goto, 0, addr);
00285     sqliteVdbeResolveLabel(v, end);
00286     sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
00287 
00288     /* Close the cursors after the loop if there are no row triggers */
00289     if( !row_triggers_exist ){
00290       for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
00291         sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
00292       }
00293       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00294       pParse->nTab = iCur;
00295     }
00296   }
00297   sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
00298   sqliteEndWriteOperation(pParse);
00299 
00300   /*
00301   ** Return the number of rows that were deleted.
00302   */
00303   if( db->flags & SQLITE_CountRows ){
00304     sqliteVdbeAddOp(v, OP_ColumnName, 0, 1);
00305     sqliteVdbeChangeP3(v, -1, "rows deleted", P3_STATIC);
00306     sqliteVdbeAddOp(v, OP_Callback, 1, 0);
00307   }
00308 
00309 delete_from_cleanup:
00310   sqliteAuthContextPop(&sContext);
00311   sqliteSrcListDelete(pTabList);
00312   sqliteExprDelete(pWhere);
00313   return;
00314 }
00315 
00316 /*
00317 ** This routine generates VDBE code that causes a single row of a
00318 ** single table to be deleted.
00319 **
00320 ** The VDBE must be in a particular state when this routine is called.
00321 ** These are the requirements:
00322 **
00323 **   1.  A read/write cursor pointing to pTab, the table containing the row
00324 **       to be deleted, must be opened as cursor number "base".
00325 **
00326 **   2.  Read/write cursors for all indices of pTab must be open as
00327 **       cursor number base+i for the i-th index.
00328 **
00329 **   3.  The record number of the row to be deleted must be on the top
00330 **       of the stack.
00331 **
00332 ** This routine pops the top of the stack to remove the record number
00333 ** and then generates code to remove both the table record and all index
00334 ** entries that point to that record.
00335 */
00336 void sqliteGenerateRowDelete(
00337   sqlite *db,        /* The database containing the index */
00338   Vdbe *v,           /* Generate code into this VDBE */
00339   Table *pTab,       /* Table containing the row to be deleted */
00340   int iCur,          /* Cursor number for the table */
00341   int count          /* Increment the row change counter */
00342 ){
00343   int addr;
00344   addr = sqliteVdbeAddOp(v, OP_NotExists, iCur, 0);
00345   sqliteGenerateRowIndexDelete(db, v, pTab, iCur, 0);
00346   sqliteVdbeAddOp(v, OP_Delete, iCur,
00347     (count?OPFLAG_NCHANGE:0) | OPFLAG_CSCHANGE);
00348   sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
00349 }
00350 
00351 /*
00352 ** This routine generates VDBE code that causes the deletion of all
00353 ** index entries associated with a single row of a single table.
00354 **
00355 ** The VDBE must be in a particular state when this routine is called.
00356 ** These are the requirements:
00357 **
00358 **   1.  A read/write cursor pointing to pTab, the table containing the row
00359 **       to be deleted, must be opened as cursor number "iCur".
00360 **
00361 **   2.  Read/write cursors for all indices of pTab must be open as
00362 **       cursor number iCur+i for the i-th index.
00363 **
00364 **   3.  The "iCur" cursor must be pointing to the row that is to be
00365 **       deleted.
00366 */
00367 void sqliteGenerateRowIndexDelete(
00368   sqlite *db,        /* The database containing the index */
00369   Vdbe *v,           /* Generate code into this VDBE */
00370   Table *pTab,       /* Table containing the row to be deleted */
00371   int iCur,          /* Cursor number for the table */
00372   char *aIdxUsed     /* Only delete if aIdxUsed!=0 && aIdxUsed[i]!=0 */
00373 ){
00374   int i;
00375   Index *pIdx;
00376 
00377   for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
00378     int j;
00379     if( aIdxUsed!=0 && aIdxUsed[i-1]==0 ) continue;
00380     sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
00381     for(j=0; j<pIdx->nColumn; j++){
00382       int idx = pIdx->aiColumn[j];
00383       if( idx==pTab->iPKey ){
00384         sqliteVdbeAddOp(v, OP_Dup, j, 0);
00385       }else{
00386         sqliteVdbeAddOp(v, OP_Column, iCur, idx);
00387       }
00388     }
00389     sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
00390     if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
00391     sqliteVdbeAddOp(v, OP_IdxDelete, iCur+i, 0);
00392   }
00393 }