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