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*/
27Table *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*/
44int 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*/
59void 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
312delete_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*/
339void 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*/
370void 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