4
** The author disclaims copyright to this source code. In place of
5
** a legal notice, here is a blessing:
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.
11
*************************************************************************
12
** This file contains C code routines that are called by the parser
13
** to handle DELETE FROM statements.
15
** $Id: delete.c 326789 2004-07-07 21:25:56Z pahlibar $
17
#include "sqliteInt.h"
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.
24
Table *sqliteSrcListLookup(Parse *pParse, SrcList *pSrc){
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;
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
41
int sqliteIsReadOnly(Parse *pParse, Table *pTab, int viewOk){
43
sqliteErrorMsg(pParse, "table %s may not be modified", pTab->zName);
46
if( !viewOk && pTab->pSelect ){
47
sqliteErrorMsg(pParse, "cannot modify %s because it is a view",pTab->zName);
54
** Process a DELETE FROM statement.
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 */
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 */
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 */
79
if( pParse->nErr || sqlite_malloc_failed ){
81
goto delete_from_cleanup;
84
assert( pTabList->nSrc==1 );
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.
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;
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;
108
/* If pTab is really a view, make sure it has been initialized.
110
if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
111
goto delete_from_cleanup;
114
/* Allocate a cursor used to store the old.* data for a trigger.
116
if( row_triggers_exist ){
117
oldIdx = pParse->nTab++;
120
/* Resolve the column names in all the expressions.
122
assert( pTabList->nSrc==1 );
123
iCur = pTabList->a[0].iCursor = pParse->nTab++;
125
if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
126
goto delete_from_cleanup;
128
if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
129
goto delete_from_cleanup;
133
/* Start the view context
136
sqliteAuthContextPush(pParse, &sContext, pTab->zName);
139
/* Begin generating code.
141
v = sqliteGetVdbe(pParse);
143
goto delete_from_cleanup;
145
sqliteBeginWriteOperation(pParse, row_triggers_exist, pTab->iDb);
147
/* If we are trying to delete from a view, construct that view into
148
** a temporary table.
151
Select *pView = sqliteSelectDup(pTab->pSelect);
152
sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
153
sqliteSelectDelete(pView);
156
/* Initialize the counter of the number of rows deleted, if
157
** we are counting rows.
159
if( db->flags & SQLITE_CountRows ){
160
sqliteVdbeAddOp(v, OP_Integer, 0, 0);
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.
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);
174
sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
175
sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
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);
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);
191
/* The usual case: There is a WHERE clause so we have to scan through
192
** the table and pick which records to delete.
195
/* Begin the database scan
197
pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
198
if( pWInfo==0 ) goto delete_from_cleanup;
200
/* Remember the key of every item to be deleted.
202
sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
203
if( db->flags & SQLITE_CountRows ){
204
sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
207
/* End the database scan loop.
209
sqliteWhereEnd(pWInfo);
211
/* Open the pseudo-table used to store OLD if there are triggers.
213
if( row_triggers_exist ){
214
sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0);
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.
221
sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
222
end = sqliteVdbeMakeLabel(v);
224
/* This is the beginning of the delete loop when there are
227
if( row_triggers_exist ){
228
addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
229
sqliteVdbeAddOp(v, OP_Dup, 0, 0);
231
sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
232
sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
234
sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
236
sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
237
sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
238
sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
240
sqliteVdbeAddOp(v, OP_Close, iCur, 0);
243
sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1,
244
oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
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.
255
pParse->nTab = iCur + 1;
256
sqliteOpenTableAndIndices(pParse, pTab, iCur);
258
/* This is the beginning of the delete loop when there are no
260
if( !row_triggers_exist ){
261
addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
265
sqliteGenerateRowDelete(db, v, pTab, iCur, pParse->trigStack==0);
268
/* If there are row triggers, close all cursors then invoke
269
** the AFTER triggers
271
if( row_triggers_exist ){
273
for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
274
sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
276
sqliteVdbeAddOp(v, OP_Close, iCur, 0);
278
sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1,
279
oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
283
/* End of the delete loop */
284
sqliteVdbeAddOp(v, OP_Goto, 0, addr);
285
sqliteVdbeResolveLabel(v, end);
286
sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
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);
293
sqliteVdbeAddOp(v, OP_Close, iCur, 0);
297
sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
298
sqliteEndWriteOperation(pParse);
301
** Return the number of rows that were deleted.
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);
310
sqliteAuthContextPop(&sContext);
311
sqliteSrcListDelete(pTabList);
312
sqliteExprDelete(pWhere);
317
** This routine generates VDBE code that causes a single row of a
318
** single table to be deleted.
320
** The VDBE must be in a particular state when this routine is called.
321
** These are the requirements:
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".
326
** 2. Read/write cursors for all indices of pTab must be open as
327
** cursor number base+i for the i-th index.
329
** 3. The record number of the row to be deleted must be on the top
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.
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 */
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));
352
** This routine generates VDBE code that causes the deletion of all
353
** index entries associated with a single row of a single table.
355
** The VDBE must be in a particular state when this routine is called.
356
** These are the requirements:
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".
361
** 2. Read/write cursors for all indices of pTab must be open as
362
** cursor number iCur+i for the i-th index.
364
** 3. The "iCur" cursor must be pointing to the row that is to be
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 */
377
for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
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);
386
sqliteVdbeAddOp(v, OP_Column, iCur, idx);
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);