xref: /PHP-5.3/ext/sqlite/libsqlite/src/delete.c (revision e563b4ea)
1 /*
2 ** 2001 September 15
3 **
4 ** The author disclaims copyright to this source code.  In place of
5 ** a legal notice, here is a blessing:
6 **
7 **    May you do good and not evil.
8 **    May you find forgiveness for yourself and forgive others.
9 **    May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle DELETE FROM statements.
14 **
15 ** $Id$
16 */
17 #include "sqliteInt.h"
18 
19 /*
20 ** Look up every table that is named in pSrc.  If any table is not found,
21 ** add an error message to pParse->zErrMsg and return NULL.  If all tables
22 ** are found, return a pointer to the last table.
23 */
sqliteSrcListLookup(Parse * pParse,SrcList * pSrc)24 Table *sqliteSrcListLookup(Parse *pParse, SrcList *pSrc){
25   Table *pTab = 0;
26   int i;
27   for(i=0; i<pSrc->nSrc; i++){
28     const char *zTab = pSrc->a[i].zName;
29     const char *zDb = pSrc->a[i].zDatabase;
30     pTab = sqliteLocateTable(pParse, zTab, zDb);
31     pSrc->a[i].pTab = pTab;
32   }
33   return pTab;
34 }
35 
36 /*
37 ** Check to make sure the given table is writable.  If it is not
38 ** writable, generate an error message and return 1.  If it is
39 ** writable return 0;
40 */
sqliteIsReadOnly(Parse * pParse,Table * pTab,int viewOk)41 int sqliteIsReadOnly(Parse *pParse, Table *pTab, int viewOk){
42   if( pTab->readOnly ){
43     sqliteErrorMsg(pParse, "table %s may not be modified", pTab->zName);
44     return 1;
45   }
46   if( !viewOk && pTab->pSelect ){
47     sqliteErrorMsg(pParse, "cannot modify %s because it is a view",pTab->zName);
48     return 1;
49   }
50   return 0;
51 }
52 
53 /*
54 ** Process a DELETE FROM statement.
55 */
sqliteDeleteFrom(Parse * pParse,SrcList * pTabList,Expr * pWhere)56 void sqliteDeleteFrom(
57   Parse *pParse,         /* The parser context */
58   SrcList *pTabList,     /* The table from which we should delete things */
59   Expr *pWhere           /* The WHERE clause.  May be null */
60 ){
61   Vdbe *v;               /* The virtual database engine */
62   Table *pTab;           /* The table from which records will be deleted */
63   const char *zDb;       /* Name of database holding pTab */
64   int end, addr;         /* A couple addresses of generated code */
65   int i;                 /* Loop counter */
66   WhereInfo *pWInfo;     /* Information about the WHERE clause */
67   Index *pIdx;           /* For looping over indices of the table */
68   int iCur;              /* VDBE Cursor number for pTab */
69   sqlite *db;            /* Main database structure */
70   int isView;            /* True if attempting to delete from a view */
71   AuthContext sContext;  /* Authorization context */
72 
73   int row_triggers_exist = 0;  /* True if any triggers exist */
74   int before_triggers;         /* True if there are BEFORE triggers */
75   int after_triggers;          /* True if there are AFTER triggers */
76   int oldIdx = -1;             /* Cursor for the OLD table of AFTER triggers */
77 
78   sContext.pParse = 0;
79   if( pParse->nErr || sqlite_malloc_failed ){
80     pTabList = 0;
81     goto delete_from_cleanup;
82   }
83   db = pParse->db;
84   assert( pTabList->nSrc==1 );
85 
86   /* Locate the table which we want to delete.  This table has to be
87   ** put in an SrcList structure because some of the subroutines we
88   ** will be calling are designed to work with multiple tables and expect
89   ** an SrcList* parameter instead of just a Table* parameter.
90   */
91   pTab = sqliteSrcListLookup(pParse, pTabList);
92   if( pTab==0 )  goto delete_from_cleanup;
93   before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger,
94                          TK_DELETE, TK_BEFORE, TK_ROW, 0);
95   after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger,
96                          TK_DELETE, TK_AFTER, TK_ROW, 0);
97   row_triggers_exist = before_triggers || after_triggers;
98   isView = pTab->pSelect!=0;
99   if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
100     goto delete_from_cleanup;
101   }
102   assert( pTab->iDb<db->nDb );
103   zDb = db->aDb[pTab->iDb].zName;
104   if( sqliteAuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){
105     goto delete_from_cleanup;
106   }
107 
108   /* If pTab is really a view, make sure it has been initialized.
109   */
110   if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
111     goto delete_from_cleanup;
112   }
113 
114   /* Allocate a cursor used to store the old.* data for a trigger.
115   */
116   if( row_triggers_exist ){
117     oldIdx = pParse->nTab++;
118   }
119 
120   /* Resolve the column names in all the expressions.
121   */
122   assert( pTabList->nSrc==1 );
123   iCur = pTabList->a[0].iCursor = pParse->nTab++;
124   if( pWhere ){
125     if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
126       goto delete_from_cleanup;
127     }
128     if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
129       goto delete_from_cleanup;
130     }
131   }
132 
133   /* Start the view context
134   */
135   if( isView ){
136     sqliteAuthContextPush(pParse, &sContext, pTab->zName);
137   }
138 
139   /* Begin generating code.
140   */
141   v = sqliteGetVdbe(pParse);
142   if( v==0 ){
143     goto delete_from_cleanup;
144   }
145   sqliteBeginWriteOperation(pParse, row_triggers_exist, pTab->iDb);
146 
147   /* If we are trying to delete from a view, construct that view into
148   ** a temporary table.
149   */
150   if( isView ){
151     Select *pView = sqliteSelectDup(pTab->pSelect);
152     sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
153     sqliteSelectDelete(pView);
154   }
155 
156   /* Initialize the counter of the number of rows deleted, if
157   ** we are counting rows.
158   */
159   if( db->flags & SQLITE_CountRows ){
160     sqliteVdbeAddOp(v, OP_Integer, 0, 0);
161   }
162 
163   /* Special case: A DELETE without a WHERE clause deletes everything.
164   ** It is easier just to erase the whole table.  Note, however, that
165   ** this means that the row change count will be incorrect.
166   */
167   if( pWhere==0 && !row_triggers_exist ){
168     if( db->flags & SQLITE_CountRows ){
169       /* If counting rows deleted, just count the total number of
170       ** entries in the table. */
171       int endOfLoop = sqliteVdbeMakeLabel(v);
172       int addr;
173       if( !isView ){
174         sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
175         sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
176       }
177       sqliteVdbeAddOp(v, OP_Rewind, iCur, sqliteVdbeCurrentAddr(v)+2);
178       addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
179       sqliteVdbeAddOp(v, OP_Next, iCur, addr);
180       sqliteVdbeResolveLabel(v, endOfLoop);
181       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
182     }
183     if( !isView ){
184       sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb);
185       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
186         sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb);
187       }
188     }
189   }
190 
191   /* The usual case: There is a WHERE clause so we have to scan through
192   ** the table and pick which records to delete.
193   */
194   else{
195     /* Begin the database scan
196     */
197     pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
198     if( pWInfo==0 ) goto delete_from_cleanup;
199 
200     /* Remember the key of every item to be deleted.
201     */
202     sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
203     if( db->flags & SQLITE_CountRows ){
204       sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
205     }
206 
207     /* End the database scan loop.
208     */
209     sqliteWhereEnd(pWInfo);
210 
211     /* Open the pseudo-table used to store OLD if there are triggers.
212     */
213     if( row_triggers_exist ){
214       sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0);
215     }
216 
217     /* Delete every item whose key was written to the list during the
218     ** database scan.  We have to delete items after the scan is complete
219     ** because deleting an item can change the scan order.
220     */
221     sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
222     end = sqliteVdbeMakeLabel(v);
223 
224     /* This is the beginning of the delete loop when there are
225     ** row triggers.
226     */
227     if( row_triggers_exist ){
228       addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
229       sqliteVdbeAddOp(v, OP_Dup, 0, 0);
230       if( !isView ){
231         sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
232         sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
233       }
234       sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
235 
236       sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
237       sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
238       sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
239       if( !isView ){
240         sqliteVdbeAddOp(v, OP_Close, iCur, 0);
241       }
242 
243       sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1,
244           oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
245 	  addr);
246     }
247 
248     if( !isView ){
249       /* Open cursors for the table we are deleting from and all its
250       ** indices.  If there are row triggers, this happens inside the
251       ** OP_ListRead loop because the cursor have to all be closed
252       ** before the trigger fires.  If there are no row triggers, the
253       ** cursors are opened only once on the outside the loop.
254       */
255       pParse->nTab = iCur + 1;
256       sqliteOpenTableAndIndices(pParse, pTab, iCur);
257 
258       /* This is the beginning of the delete loop when there are no
259       ** row triggers */
260       if( !row_triggers_exist ){
261         addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
262       }
263 
264       /* Delete the row */
265       sqliteGenerateRowDelete(db, v, pTab, iCur, pParse->trigStack==0);
266     }
267 
268     /* If there are row triggers, close all cursors then invoke
269     ** the AFTER triggers
270     */
271     if( row_triggers_exist ){
272       if( !isView ){
273         for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
274           sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
275         }
276         sqliteVdbeAddOp(v, OP_Close, iCur, 0);
277       }
278       sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1,
279           oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
280 	  addr);
281     }
282 
283     /* End of the delete loop */
284     sqliteVdbeAddOp(v, OP_Goto, 0, addr);
285     sqliteVdbeResolveLabel(v, end);
286     sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
287 
288     /* Close the cursors after the loop if there are no row triggers */
289     if( !row_triggers_exist ){
290       for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
291         sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
292       }
293       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
294       pParse->nTab = iCur;
295     }
296   }
297   sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
298   sqliteEndWriteOperation(pParse);
299 
300   /*
301   ** Return the number of rows that were deleted.
302   */
303   if( db->flags & SQLITE_CountRows ){
304     sqliteVdbeAddOp(v, OP_ColumnName, 0, 1);
305     sqliteVdbeChangeP3(v, -1, "rows deleted", P3_STATIC);
306     sqliteVdbeAddOp(v, OP_Callback, 1, 0);
307   }
308 
309 delete_from_cleanup:
310   sqliteAuthContextPop(&sContext);
311   sqliteSrcListDelete(pTabList);
312   sqliteExprDelete(pWhere);
313   return;
314 }
315 
316 /*
317 ** This routine generates VDBE code that causes a single row of a
318 ** single table to be deleted.
319 **
320 ** The VDBE must be in a particular state when this routine is called.
321 ** These are the requirements:
322 **
323 **   1.  A read/write cursor pointing to pTab, the table containing the row
324 **       to be deleted, must be opened as cursor number "base".
325 **
326 **   2.  Read/write cursors for all indices of pTab must be open as
327 **       cursor number base+i for the i-th index.
328 **
329 **   3.  The record number of the row to be deleted must be on the top
330 **       of the stack.
331 **
332 ** This routine pops the top of the stack to remove the record number
333 ** and then generates code to remove both the table record and all index
334 ** entries that point to that record.
335 */
sqliteGenerateRowDelete(sqlite * db,Vdbe * v,Table * pTab,int iCur,int count)336 void sqliteGenerateRowDelete(
337   sqlite *db,        /* The database containing the index */
338   Vdbe *v,           /* Generate code into this VDBE */
339   Table *pTab,       /* Table containing the row to be deleted */
340   int iCur,          /* Cursor number for the table */
341   int count          /* Increment the row change counter */
342 ){
343   int addr;
344   addr = sqliteVdbeAddOp(v, OP_NotExists, iCur, 0);
345   sqliteGenerateRowIndexDelete(db, v, pTab, iCur, 0);
346   sqliteVdbeAddOp(v, OP_Delete, iCur,
347     (count?OPFLAG_NCHANGE:0) | OPFLAG_CSCHANGE);
348   sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
349 }
350 
351 /*
352 ** This routine generates VDBE code that causes the deletion of all
353 ** index entries associated with a single row of a single table.
354 **
355 ** The VDBE must be in a particular state when this routine is called.
356 ** These are the requirements:
357 **
358 **   1.  A read/write cursor pointing to pTab, the table containing the row
359 **       to be deleted, must be opened as cursor number "iCur".
360 **
361 **   2.  Read/write cursors for all indices of pTab must be open as
362 **       cursor number iCur+i for the i-th index.
363 **
364 **   3.  The "iCur" cursor must be pointing to the row that is to be
365 **       deleted.
366 */
sqliteGenerateRowIndexDelete(sqlite * db,Vdbe * v,Table * pTab,int iCur,char * aIdxUsed)367 void sqliteGenerateRowIndexDelete(
368   sqlite *db,        /* The database containing the index */
369   Vdbe *v,           /* Generate code into this VDBE */
370   Table *pTab,       /* Table containing the row to be deleted */
371   int iCur,          /* Cursor number for the table */
372   char *aIdxUsed     /* Only delete if aIdxUsed!=0 && aIdxUsed[i]!=0 */
373 ){
374   int i;
375   Index *pIdx;
376 
377   for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
378     int j;
379     if( aIdxUsed!=0 && aIdxUsed[i-1]==0 ) continue;
380     sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
381     for(j=0; j<pIdx->nColumn; j++){
382       int idx = pIdx->aiColumn[j];
383       if( idx==pTab->iPKey ){
384         sqliteVdbeAddOp(v, OP_Dup, j, 0);
385       }else{
386         sqliteVdbeAddOp(v, OP_Column, iCur, idx);
387       }
388     }
389     sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
390     if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
391     sqliteVdbeAddOp(v, OP_IdxDelete, iCur+i, 0);
392   }
393 }
394