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 ** in order to generate code for DELETE FROM statements.
15 #include "sqliteInt.h"
18 ** While a SrcList can in general represent multiple tables and subqueries
19 ** (as in the FROM clause of a SELECT statement) in this case it contains
20 ** the name of a single table, as one might find in an INSERT, DELETE,
21 ** or UPDATE statement. Look up that table in the symbol table and
22 ** return a pointer. Set an error message and return NULL if the table
23 ** name is not found or if any other error occurs.
25 ** The following fields are initialized appropriate in pSrc:
27 ** pSrc->a[0].pTab Pointer to the Table object
28 ** pSrc->a[0].pIndex Pointer to the INDEXED BY index, if there is one
31 Table
*sqlite3SrcListLookup(Parse
*pParse
, SrcList
*pSrc
){
32 struct SrcList_item
*pItem
= pSrc
->a
;
34 assert( pItem
&& pSrc
->nSrc
==1 );
35 pTab
= sqlite3LocateTableItem(pParse
, 0, pItem
);
36 sqlite3DeleteTable(pParse
->db
, pItem
->pTab
);
41 if( sqlite3IndexedByLookup(pParse
, pItem
) ){
47 /* Return true if table pTab is read-only.
49 ** A table is read-only if any of the following are true:
51 ** 1) It is a virtual table and no implementation of the xUpdate method
54 ** 2) It is a system table (i.e. sqlite_master), this call is not
55 ** part of a nested parse and writable_schema pragma has not
58 ** 3) The table is a shadow table, the database connection is in
59 ** defensive mode, and the current sqlite3_prepare()
60 ** is for a top-level SQL statement.
62 static int tabIsReadOnly(Parse
*pParse
, Table
*pTab
){
64 if( IsVirtual(pTab
) ){
65 return sqlite3GetVTable(pParse
->db
, pTab
)->pMod
->pModule
->xUpdate
==0;
67 if( (pTab
->tabFlags
& (TF_Readonly
|TF_Shadow
))==0 ) return 0;
69 if( (pTab
->tabFlags
& TF_Readonly
)!=0 ){
70 return sqlite3WritableSchema(db
)==0 && pParse
->nested
==0;
72 assert( pTab
->tabFlags
& TF_Shadow
);
73 return (db
->flags
& SQLITE_Defensive
)!=0
74 #ifndef SQLITE_OMIT_VIRTUALTABLE
81 ** Check to make sure the given table is writable. If it is not
82 ** writable, generate an error message and return 1. If it is
85 int sqlite3IsReadOnly(Parse
*pParse
, Table
*pTab
, int viewOk
){
86 if( tabIsReadOnly(pParse
, pTab
) ){
87 sqlite3ErrorMsg(pParse
, "table %s may not be modified", pTab
->zName
);
90 #ifndef SQLITE_OMIT_VIEW
91 if( !viewOk
&& pTab
->pSelect
){
92 sqlite3ErrorMsg(pParse
,"cannot modify %s because it is a view",pTab
->zName
);
100 #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
102 ** Evaluate a view and store its result in an ephemeral table. The
103 ** pWhere argument is an optional WHERE clause that restricts the
104 ** set of rows in the view that are to be added to the ephemeral table.
106 void sqlite3MaterializeView(
107 Parse
*pParse
, /* Parsing context */
108 Table
*pView
, /* View definition */
109 Expr
*pWhere
, /* Optional WHERE clause to be added */
110 ExprList
*pOrderBy
, /* Optional ORDER BY clause */
111 Expr
*pLimit
, /* Optional LIMIT clause */
112 int iCur
/* Cursor number for ephemeral table */
117 sqlite3
*db
= pParse
->db
;
118 int iDb
= sqlite3SchemaToIndex(db
, pView
->pSchema
);
119 pWhere
= sqlite3ExprDup(db
, pWhere
, 0);
120 pFrom
= sqlite3SrcListAppend(pParse
, 0, 0, 0);
122 assert( pFrom
->nSrc
==1 );
123 pFrom
->a
[0].zName
= sqlite3DbStrDup(db
, pView
->zName
);
124 pFrom
->a
[0].zDatabase
= sqlite3DbStrDup(db
, db
->aDb
[iDb
].zDbSName
);
125 assert( pFrom
->a
[0].pOn
==0 );
126 assert( pFrom
->a
[0].pUsing
==0 );
128 pSel
= sqlite3SelectNew(pParse
, 0, pFrom
, pWhere
, 0, 0, pOrderBy
,
129 SF_IncludeHidden
, pLimit
);
130 sqlite3SelectDestInit(&dest
, SRT_EphemTab
, iCur
);
131 sqlite3Select(pParse
, pSel
, &dest
);
132 sqlite3SelectDelete(db
, pSel
);
134 #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
136 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
138 ** Generate an expression tree to implement the WHERE, ORDER BY,
139 ** and LIMIT/OFFSET portion of DELETE and UPDATE statements.
141 ** DELETE FROM table_wxyz WHERE a<5 ORDER BY a LIMIT 1;
142 ** \__________________________/
143 ** pLimitWhere (pInClause)
145 Expr
*sqlite3LimitWhere(
146 Parse
*pParse
, /* The parser context */
147 SrcList
*pSrc
, /* the FROM clause -- which tables to scan */
148 Expr
*pWhere
, /* The WHERE clause. May be null */
149 ExprList
*pOrderBy
, /* The ORDER BY clause. May be null */
150 Expr
*pLimit
, /* The LIMIT clause. May be null */
151 char *zStmtType
/* Either DELETE or UPDATE. For err msgs. */
153 sqlite3
*db
= pParse
->db
;
154 Expr
*pLhs
= NULL
; /* LHS of IN(SELECT...) operator */
155 Expr
*pInClause
= NULL
; /* WHERE rowid IN ( select ) */
156 ExprList
*pEList
= NULL
; /* Expression list contaning only pSelectRowid */
157 SrcList
*pSelectSrc
= NULL
; /* SELECT rowid FROM x ... (dup of pSrc) */
158 Select
*pSelect
= NULL
; /* Complete SELECT tree */
161 /* Check that there isn't an ORDER BY without a LIMIT clause.
163 if( pOrderBy
&& pLimit
==0 ) {
164 sqlite3ErrorMsg(pParse
, "ORDER BY without LIMIT on %s", zStmtType
);
165 sqlite3ExprDelete(pParse
->db
, pWhere
);
166 sqlite3ExprListDelete(pParse
->db
, pOrderBy
);
170 /* We only need to generate a select expression if there
171 ** is a limit/offset term to enforce.
177 /* Generate a select expression tree to enforce the limit/offset
178 ** term for the DELETE or UPDATE statement. For example:
179 ** DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
181 ** DELETE FROM table_a WHERE rowid IN (
182 ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
186 pTab
= pSrc
->a
[0].pTab
;
187 if( HasRowid(pTab
) ){
188 pLhs
= sqlite3PExpr(pParse
, TK_ROW
, 0, 0);
189 pEList
= sqlite3ExprListAppend(
190 pParse
, 0, sqlite3PExpr(pParse
, TK_ROW
, 0, 0)
193 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
194 if( pPk
->nKeyCol
==1 ){
195 const char *zName
= pTab
->aCol
[pPk
->aiColumn
[0]].zName
;
196 pLhs
= sqlite3Expr(db
, TK_ID
, zName
);
197 pEList
= sqlite3ExprListAppend(pParse
, 0, sqlite3Expr(db
, TK_ID
, zName
));
200 for(i
=0; i
<pPk
->nKeyCol
; i
++){
201 Expr
*p
= sqlite3Expr(db
, TK_ID
, pTab
->aCol
[pPk
->aiColumn
[i
]].zName
);
202 pEList
= sqlite3ExprListAppend(pParse
, pEList
, p
);
204 pLhs
= sqlite3PExpr(pParse
, TK_VECTOR
, 0, 0);
206 pLhs
->x
.pList
= sqlite3ExprListDup(db
, pEList
, 0);
211 /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree
212 ** and the SELECT subtree. */
214 pSelectSrc
= sqlite3SrcListDup(pParse
->db
, pSrc
, 0);
215 pSrc
->a
[0].pTab
= pTab
;
216 pSrc
->a
[0].pIBIndex
= 0;
218 /* generate the SELECT expression tree. */
219 pSelect
= sqlite3SelectNew(pParse
, pEList
, pSelectSrc
, pWhere
, 0 ,0,
223 /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */
224 pInClause
= sqlite3PExpr(pParse
, TK_IN
, pLhs
, 0);
225 sqlite3PExprAddSelect(pParse
, pInClause
, pSelect
);
228 #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) */
229 /* && !defined(SQLITE_OMIT_SUBQUERY) */
232 ** Generate code for a DELETE FROM statement.
234 ** DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
235 ** \________/ \________________/
238 void sqlite3DeleteFrom(
239 Parse
*pParse
, /* The parser context */
240 SrcList
*pTabList
, /* The table from which we should delete things */
241 Expr
*pWhere
, /* The WHERE clause. May be null */
242 ExprList
*pOrderBy
, /* ORDER BY clause. May be null */
243 Expr
*pLimit
/* LIMIT clause. May be null */
245 Vdbe
*v
; /* The virtual database engine */
246 Table
*pTab
; /* The table from which records will be deleted */
247 int i
; /* Loop counter */
248 WhereInfo
*pWInfo
; /* Information about the WHERE clause */
249 Index
*pIdx
; /* For looping over indices of the table */
250 int iTabCur
; /* Cursor number for the table */
251 int iDataCur
= 0; /* VDBE cursor for the canonical data source */
252 int iIdxCur
= 0; /* Cursor number of the first index */
253 int nIdx
; /* Number of indices */
254 sqlite3
*db
; /* Main database structure */
255 AuthContext sContext
; /* Authorization context */
256 NameContext sNC
; /* Name context to resolve expressions in */
257 int iDb
; /* Database number */
258 int memCnt
= 0; /* Memory cell used for change counting */
259 int rcauth
; /* Value returned by authorization callback */
260 int eOnePass
; /* ONEPASS_OFF or _SINGLE or _MULTI */
261 int aiCurOnePass
[2]; /* The write cursors opened by WHERE_ONEPASS */
262 u8
*aToOpen
= 0; /* Open cursor iTabCur+j if aToOpen[j] is true */
263 Index
*pPk
; /* The PRIMARY KEY index on the table */
264 int iPk
= 0; /* First of nPk registers holding PRIMARY KEY value */
265 i16 nPk
= 1; /* Number of columns in the PRIMARY KEY */
266 int iKey
; /* Memory cell holding key of row to be deleted */
267 i16 nKey
; /* Number of memory cells in the row key */
268 int iEphCur
= 0; /* Ephemeral table holding all primary key values */
269 int iRowSet
= 0; /* Register for rowset of rows to delete */
270 int addrBypass
= 0; /* Address of jump over the delete logic */
271 int addrLoop
= 0; /* Top of the delete loop */
272 int addrEphOpen
= 0; /* Instruction to open the Ephemeral table */
273 int bComplex
; /* True if there are triggers or FKs or
274 ** subqueries in the WHERE clause */
276 #ifndef SQLITE_OMIT_TRIGGER
277 int isView
; /* True if attempting to delete from a view */
278 Trigger
*pTrigger
; /* List of table triggers, if required */
281 memset(&sContext
, 0, sizeof(sContext
));
283 if( pParse
->nErr
|| db
->mallocFailed
){
284 goto delete_from_cleanup
;
286 assert( pTabList
->nSrc
==1 );
289 /* Locate the table which we want to delete. This table has to be
290 ** put in an SrcList structure because some of the subroutines we
291 ** will be calling are designed to work with multiple tables and expect
292 ** an SrcList* parameter instead of just a Table* parameter.
294 pTab
= sqlite3SrcListLookup(pParse
, pTabList
);
295 if( pTab
==0 ) goto delete_from_cleanup
;
297 /* Figure out if we have any triggers and if the table being
298 ** deleted from is a view
300 #ifndef SQLITE_OMIT_TRIGGER
301 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
302 isView
= pTab
->pSelect
!=0;
307 bComplex
= pTrigger
|| sqlite3FkRequired(pParse
, pTab
, 0, 0);
308 #ifdef SQLITE_OMIT_VIEW
313 #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
315 pWhere
= sqlite3LimitWhere(
316 pParse
, pTabList
, pWhere
, pOrderBy
, pLimit
, "DELETE"
323 /* If pTab is really a view, make sure it has been initialized.
325 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ){
326 goto delete_from_cleanup
;
329 if( sqlite3IsReadOnly(pParse
, pTab
, (pTrigger
?1:0)) ){
330 goto delete_from_cleanup
;
332 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
333 assert( iDb
<db
->nDb
);
334 rcauth
= sqlite3AuthCheck(pParse
, SQLITE_DELETE
, pTab
->zName
, 0,
335 db
->aDb
[iDb
].zDbSName
);
336 assert( rcauth
==SQLITE_OK
|| rcauth
==SQLITE_DENY
|| rcauth
==SQLITE_IGNORE
);
337 if( rcauth
==SQLITE_DENY
){
338 goto delete_from_cleanup
;
340 assert(!isView
|| pTrigger
);
342 /* Assign cursor numbers to the table and all its indices.
344 assert( pTabList
->nSrc
==1 );
345 iTabCur
= pTabList
->a
[0].iCursor
= pParse
->nTab
++;
346 for(nIdx
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, nIdx
++){
350 /* Start the view context
353 sqlite3AuthContextPush(pParse
, &sContext
, pTab
->zName
);
356 /* Begin generating code.
358 v
= sqlite3GetVdbe(pParse
);
360 goto delete_from_cleanup
;
362 if( pParse
->nested
==0 ) sqlite3VdbeCountChanges(v
);
363 sqlite3BeginWriteOperation(pParse
, bComplex
, iDb
);
365 /* If we are trying to delete from a view, realize that view into
366 ** an ephemeral table.
368 #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
370 sqlite3MaterializeView(pParse
, pTab
,
371 pWhere
, pOrderBy
, pLimit
, iTabCur
373 iDataCur
= iIdxCur
= iTabCur
;
379 /* Resolve the column names in the WHERE clause.
381 memset(&sNC
, 0, sizeof(sNC
));
383 sNC
.pSrcList
= pTabList
;
384 if( sqlite3ResolveExprNames(&sNC
, pWhere
) ){
385 goto delete_from_cleanup
;
388 /* Initialize the counter of the number of rows deleted, if
389 ** we are counting rows.
391 if( (db
->flags
& SQLITE_CountRows
)!=0
393 && !pParse
->pTriggerTab
395 memCnt
= ++pParse
->nMem
;
396 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, memCnt
);
399 #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
400 /* Special case: A DELETE without a WHERE clause deletes everything.
401 ** It is easier just to erase the whole table. Prior to version 3.6.5,
402 ** this optimization caused the row change count (the value returned by
403 ** API function sqlite3_count_changes) to be set incorrectly.
405 ** The "rcauth==SQLITE_OK" terms is the
406 ** IMPLEMENTATION-OF: R-17228-37124 If the action code is SQLITE_DELETE and
407 ** the callback returns SQLITE_IGNORE then the DELETE operation proceeds but
408 ** the truncate optimization is disabled and all rows are deleted
411 if( rcauth
==SQLITE_OK
415 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
416 && db
->xPreUpdateCallback
==0
420 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, 1, pTab
->zName
);
421 if( HasRowid(pTab
) ){
422 sqlite3VdbeAddOp4(v
, OP_Clear
, pTab
->tnum
, iDb
, memCnt
? memCnt
: -1,
423 pTab
->zName
, P4_STATIC
);
425 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
426 assert( pIdx
->pSchema
==pTab
->pSchema
);
427 sqlite3VdbeAddOp2(v
, OP_Clear
, pIdx
->tnum
, iDb
);
430 #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
432 u16 wcf
= WHERE_ONEPASS_DESIRED
|WHERE_DUPLICATES_OK
|WHERE_SEEK_TABLE
;
433 if( sNC
.ncFlags
& NC_VarSelect
) bComplex
= 1;
434 wcf
|= (bComplex
? 0 : WHERE_ONEPASS_MULTIROW
);
435 if( HasRowid(pTab
) ){
436 /* For a rowid table, initialize the RowSet to an empty set */
439 iRowSet
= ++pParse
->nMem
;
440 sqlite3VdbeAddOp2(v
, OP_Null
, 0, iRowSet
);
442 /* For a WITHOUT ROWID table, create an ephemeral table used to
443 ** hold all primary keys for rows to be deleted. */
444 pPk
= sqlite3PrimaryKeyIndex(pTab
);
447 iPk
= pParse
->nMem
+1;
449 iEphCur
= pParse
->nTab
++;
450 addrEphOpen
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, iEphCur
, nPk
);
451 sqlite3VdbeSetP4KeyInfo(pParse
, pPk
);
454 /* Construct a query to find the rowid or primary key for every row
455 ** to be deleted, based on the WHERE clause. Set variable eOnePass
456 ** to indicate the strategy used to implement this delete:
458 ** ONEPASS_OFF: Two-pass approach - use a FIFO for rowids/PK values.
459 ** ONEPASS_SINGLE: One-pass approach - at most one row deleted.
460 ** ONEPASS_MULTI: One-pass approach - any number of rows may be deleted.
462 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, 0, 0, wcf
, iTabCur
+1);
463 if( pWInfo
==0 ) goto delete_from_cleanup
;
464 eOnePass
= sqlite3WhereOkOnePass(pWInfo
, aiCurOnePass
);
465 assert( IsVirtual(pTab
)==0 || eOnePass
!=ONEPASS_MULTI
);
466 assert( IsVirtual(pTab
) || bComplex
|| eOnePass
!=ONEPASS_OFF
);
467 if( eOnePass
!=ONEPASS_SINGLE
) sqlite3MultiWrite(pParse
);
469 /* Keep track of the number of rows to be deleted */
471 sqlite3VdbeAddOp2(v
, OP_AddImm
, memCnt
, 1);
474 /* Extract the rowid or primary key for the current row */
476 for(i
=0; i
<nPk
; i
++){
477 assert( pPk
->aiColumn
[i
]>=0 );
478 sqlite3ExprCodeGetColumnOfTable(v
, pTab
, iTabCur
,
479 pPk
->aiColumn
[i
], iPk
+i
);
483 iKey
= ++pParse
->nMem
;
484 sqlite3ExprCodeGetColumnOfTable(v
, pTab
, iTabCur
, -1, iKey
);
487 if( eOnePass
!=ONEPASS_OFF
){
488 /* For ONEPASS, no need to store the rowid/primary-key. There is only
489 ** one, so just keep it in its register(s) and fall through to the
491 nKey
= nPk
; /* OP_Found will use an unpacked key */
492 aToOpen
= sqlite3DbMallocRawNN(db
, nIdx
+2);
494 sqlite3WhereEnd(pWInfo
);
495 goto delete_from_cleanup
;
497 memset(aToOpen
, 1, nIdx
+1);
499 if( aiCurOnePass
[0]>=0 ) aToOpen
[aiCurOnePass
[0]-iTabCur
] = 0;
500 if( aiCurOnePass
[1]>=0 ) aToOpen
[aiCurOnePass
[1]-iTabCur
] = 0;
501 if( addrEphOpen
) sqlite3VdbeChangeToNoop(v
, addrEphOpen
);
504 /* Add the PK key for this row to the temporary table */
505 iKey
= ++pParse
->nMem
;
506 nKey
= 0; /* Zero tells OP_Found to use a composite key */
507 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, iPk
, nPk
, iKey
,
508 sqlite3IndexAffinityStr(pParse
->db
, pPk
), nPk
);
509 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iEphCur
, iKey
, iPk
, nPk
);
511 /* Add the rowid of the row to be deleted to the RowSet */
512 nKey
= 1; /* OP_DeferredSeek always uses a single rowid */
513 sqlite3VdbeAddOp2(v
, OP_RowSetAdd
, iRowSet
, iKey
);
517 /* If this DELETE cannot use the ONEPASS strategy, this is the
518 ** end of the WHERE loop */
519 if( eOnePass
!=ONEPASS_OFF
){
520 addrBypass
= sqlite3VdbeMakeLabel(pParse
);
522 sqlite3WhereEnd(pWInfo
);
525 /* Unless this is a view, open cursors for the table we are
526 ** deleting from and all its indices. If this is a view, then the
527 ** only effect this statement has is to fire the INSTEAD OF
532 if( eOnePass
==ONEPASS_MULTI
){
533 iAddrOnce
= sqlite3VdbeAddOp0(v
, OP_Once
); VdbeCoverage(v
);
535 testcase( IsVirtual(pTab
) );
536 sqlite3OpenTableAndIndices(pParse
, pTab
, OP_OpenWrite
, OPFLAG_FORDELETE
,
537 iTabCur
, aToOpen
, &iDataCur
, &iIdxCur
);
538 assert( pPk
|| IsVirtual(pTab
) || iDataCur
==iTabCur
);
539 assert( pPk
|| IsVirtual(pTab
) || iIdxCur
==iDataCur
+1 );
540 if( eOnePass
==ONEPASS_MULTI
) sqlite3VdbeJumpHere(v
, iAddrOnce
);
543 /* Set up a loop over the rowids/primary-keys that were found in the
544 ** where-clause loop above.
546 if( eOnePass
!=ONEPASS_OFF
){
547 assert( nKey
==nPk
); /* OP_Found will use an unpacked key */
548 if( !IsVirtual(pTab
) && aToOpen
[iDataCur
-iTabCur
] ){
549 assert( pPk
!=0 || pTab
->pSelect
!=0 );
550 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iDataCur
, addrBypass
, iKey
, nKey
);
554 addrLoop
= sqlite3VdbeAddOp1(v
, OP_Rewind
, iEphCur
); VdbeCoverage(v
);
555 if( IsVirtual(pTab
) ){
556 sqlite3VdbeAddOp3(v
, OP_Column
, iEphCur
, 0, iKey
);
558 sqlite3VdbeAddOp2(v
, OP_RowData
, iEphCur
, iKey
);
560 assert( nKey
==0 ); /* OP_Found will use a composite key */
562 addrLoop
= sqlite3VdbeAddOp3(v
, OP_RowSetRead
, iRowSet
, 0, iKey
);
568 #ifndef SQLITE_OMIT_VIRTUALTABLE
569 if( IsVirtual(pTab
) ){
570 const char *pVTab
= (const char *)sqlite3GetVTable(db
, pTab
);
571 sqlite3VtabMakeWritable(pParse
, pTab
);
572 assert( eOnePass
==ONEPASS_OFF
|| eOnePass
==ONEPASS_SINGLE
);
573 sqlite3MayAbort(pParse
);
574 if( eOnePass
==ONEPASS_SINGLE
){
575 sqlite3VdbeAddOp1(v
, OP_Close
, iTabCur
);
576 if( sqlite3IsToplevel(pParse
) ){
577 pParse
->isMultiWrite
= 0;
580 sqlite3VdbeAddOp4(v
, OP_VUpdate
, 0, 1, iKey
, pVTab
, P4_VTAB
);
581 sqlite3VdbeChangeP5(v
, OE_Abort
);
585 int count
= (pParse
->nested
==0); /* True to count changes */
586 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
587 iKey
, nKey
, count
, OE_Default
, eOnePass
, aiCurOnePass
[1]);
590 /* End of the loop over all rowids/primary-keys. */
591 if( eOnePass
!=ONEPASS_OFF
){
592 sqlite3VdbeResolveLabel(v
, addrBypass
);
593 sqlite3WhereEnd(pWInfo
);
595 sqlite3VdbeAddOp2(v
, OP_Next
, iEphCur
, addrLoop
+1); VdbeCoverage(v
);
596 sqlite3VdbeJumpHere(v
, addrLoop
);
598 sqlite3VdbeGoto(v
, addrLoop
);
599 sqlite3VdbeJumpHere(v
, addrLoop
);
601 } /* End non-truncate path */
603 /* Update the sqlite_sequence table by storing the content of the
604 ** maximum rowid counter values recorded while inserting into
605 ** autoincrement tables.
607 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 ){
608 sqlite3AutoincrementEnd(pParse
);
611 /* Return the number of rows that were deleted. If this routine is
612 ** generating code because of a call to sqlite3NestedParse(), do not
613 ** invoke the callback function.
616 sqlite3VdbeAddOp2(v
, OP_ResultRow
, memCnt
, 1);
617 sqlite3VdbeSetNumCols(v
, 1);
618 sqlite3VdbeSetColName(v
, 0, COLNAME_NAME
, "rows deleted", SQLITE_STATIC
);
622 sqlite3AuthContextPop(&sContext
);
623 sqlite3SrcListDelete(db
, pTabList
);
624 sqlite3ExprDelete(db
, pWhere
);
625 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
626 sqlite3ExprListDelete(db
, pOrderBy
);
627 sqlite3ExprDelete(db
, pLimit
);
629 sqlite3DbFree(db
, aToOpen
);
632 /* Make sure "isView" and other macros defined above are undefined. Otherwise
633 ** they may interfere with compilation of other functions in this file
634 ** (or in another file, if this file becomes part of the amalgamation). */
643 ** This routine generates VDBE code that causes a single row of a
644 ** single table to be deleted. Both the original table entry and
645 ** all indices are removed.
649 ** 1. iDataCur is an open cursor on the btree that is the canonical data
650 ** store for the table. (This will be either the table itself,
651 ** in the case of a rowid table, or the PRIMARY KEY index in the case
652 ** of a WITHOUT ROWID table.)
654 ** 2. Read/write cursors for all indices of pTab must be open as
655 ** cursor number iIdxCur+i for the i-th index.
657 ** 3. The primary key for the row to be deleted must be stored in a
658 ** sequence of nPk memory cells starting at iPk. If nPk==0 that means
659 ** that a search record formed from OP_MakeRecord is contained in the
660 ** single memory location iPk.
663 ** Parameter eMode may be passed either ONEPASS_OFF (0), ONEPASS_SINGLE, or
664 ** ONEPASS_MULTI. If eMode is not ONEPASS_OFF, then the cursor
665 ** iDataCur already points to the row to delete. If eMode is ONEPASS_OFF
666 ** then this function must seek iDataCur to the entry identified by iPk
667 ** and nPk before reading from it.
669 ** If eMode is ONEPASS_MULTI, then this call is being made as part
670 ** of a ONEPASS delete that affects multiple rows. In this case, if
671 ** iIdxNoSeek is a valid cursor number (>=0) and is not the same as
672 ** iDataCur, then its position should be preserved following the delete
673 ** operation. Or, if iIdxNoSeek is not a valid cursor number, the
674 ** position of iDataCur should be preserved instead.
677 ** If iIdxNoSeek is a valid cursor number (>=0) not equal to iDataCur,
678 ** then it identifies an index cursor (from within array of cursors
679 ** starting at iIdxCur) that already points to the index entry to be deleted.
680 ** Except, this optimization is disabled if there are BEFORE triggers since
681 ** the trigger body might have moved the cursor.
683 void sqlite3GenerateRowDelete(
684 Parse
*pParse
, /* Parsing context */
685 Table
*pTab
, /* Table containing the row to be deleted */
686 Trigger
*pTrigger
, /* List of triggers to (potentially) fire */
687 int iDataCur
, /* Cursor from which column data is extracted */
688 int iIdxCur
, /* First index cursor */
689 int iPk
, /* First memory cell containing the PRIMARY KEY */
690 i16 nPk
, /* Number of PRIMARY KEY memory cells */
691 u8 count
, /* If non-zero, increment the row change counter */
692 u8 onconf
, /* Default ON CONFLICT policy for triggers */
693 u8 eMode
, /* ONEPASS_OFF, _SINGLE, or _MULTI. See above */
694 int iIdxNoSeek
/* Cursor number of cursor that does not need seeking */
696 Vdbe
*v
= pParse
->pVdbe
; /* Vdbe */
697 int iOld
= 0; /* First register in OLD.* array */
698 int iLabel
; /* Label resolved to end of generated code */
699 u8 opSeek
; /* Seek opcode */
701 /* Vdbe is guaranteed to have been allocated by this stage. */
703 VdbeModuleComment((v
, "BEGIN: GenRowDel(%d,%d,%d,%d)",
704 iDataCur
, iIdxCur
, iPk
, (int)nPk
));
706 /* Seek cursor iCur to the row to delete. If this row no longer exists
707 ** (this can happen if a trigger program has already deleted it), do
708 ** not attempt to delete it or fire any DELETE triggers. */
709 iLabel
= sqlite3VdbeMakeLabel(pParse
);
710 opSeek
= HasRowid(pTab
) ? OP_NotExists
: OP_NotFound
;
711 if( eMode
==ONEPASS_OFF
){
712 sqlite3VdbeAddOp4Int(v
, opSeek
, iDataCur
, iLabel
, iPk
, nPk
);
713 VdbeCoverageIf(v
, opSeek
==OP_NotExists
);
714 VdbeCoverageIf(v
, opSeek
==OP_NotFound
);
717 /* If there are any triggers to fire, allocate a range of registers to
718 ** use for the old.* references in the triggers. */
719 if( sqlite3FkRequired(pParse
, pTab
, 0, 0) || pTrigger
){
720 u32 mask
; /* Mask of OLD.* columns in use */
721 int iCol
; /* Iterator used while populating OLD.* */
722 int addrStart
; /* Start of BEFORE trigger programs */
724 /* TODO: Could use temporary registers here. Also could attempt to
725 ** avoid copying the contents of the rowid register. */
726 mask
= sqlite3TriggerColmask(
727 pParse
, pTrigger
, 0, 0, TRIGGER_BEFORE
|TRIGGER_AFTER
, pTab
, onconf
729 mask
|= sqlite3FkOldmask(pParse
, pTab
);
730 iOld
= pParse
->nMem
+1;
731 pParse
->nMem
+= (1 + pTab
->nCol
);
733 /* Populate the OLD.* pseudo-table register array. These values will be
734 ** used by any BEFORE and AFTER triggers that exist. */
735 sqlite3VdbeAddOp2(v
, OP_Copy
, iPk
, iOld
);
736 for(iCol
=0; iCol
<pTab
->nCol
; iCol
++){
737 testcase( mask
!=0xffffffff && iCol
==31 );
738 testcase( mask
!=0xffffffff && iCol
==32 );
739 if( mask
==0xffffffff || (iCol
<=31 && (mask
& MASKBIT32(iCol
))!=0) ){
740 sqlite3ExprCodeGetColumnOfTable(v
, pTab
, iDataCur
, iCol
, iOld
+iCol
+1);
744 /* Invoke BEFORE DELETE trigger programs. */
745 addrStart
= sqlite3VdbeCurrentAddr(v
);
746 sqlite3CodeRowTrigger(pParse
, pTrigger
,
747 TK_DELETE
, 0, TRIGGER_BEFORE
, pTab
, iOld
, onconf
, iLabel
750 /* If any BEFORE triggers were coded, then seek the cursor to the
751 ** row to be deleted again. It may be that the BEFORE triggers moved
752 ** the cursor or already deleted the row that the cursor was
755 ** Also disable the iIdxNoSeek optimization since the BEFORE trigger
756 ** may have moved that cursor.
758 if( addrStart
<sqlite3VdbeCurrentAddr(v
) ){
759 sqlite3VdbeAddOp4Int(v
, opSeek
, iDataCur
, iLabel
, iPk
, nPk
);
760 VdbeCoverageIf(v
, opSeek
==OP_NotExists
);
761 VdbeCoverageIf(v
, opSeek
==OP_NotFound
);
762 testcase( iIdxNoSeek
>=0 );
766 /* Do FK processing. This call checks that any FK constraints that
767 ** refer to this table (i.e. constraints attached to other tables)
768 ** are not violated by deleting this row. */
769 sqlite3FkCheck(pParse
, pTab
, iOld
, 0, 0, 0);
772 /* Delete the index and table entries. Skip this step if pTab is really
773 ** a view (in which case the only effect of the DELETE statement is to
774 ** fire the INSTEAD OF triggers).
776 ** If variable 'count' is non-zero, then this OP_Delete instruction should
777 ** invoke the update-hook. The pre-update-hook, on the other hand should
778 ** be invoked unless table pTab is a system table. The difference is that
779 ** the update-hook is not invoked for rows removed by REPLACE, but the
780 ** pre-update-hook is.
782 if( pTab
->pSelect
==0 ){
784 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
,0,iIdxNoSeek
);
785 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, (count
?OPFLAG_NCHANGE
:0));
786 if( pParse
->nested
==0 || 0==sqlite3_stricmp(pTab
->zName
, "sqlite_stat1") ){
787 sqlite3VdbeAppendP4(v
, (char*)pTab
, P4_TABLE
);
789 if( eMode
!=ONEPASS_OFF
){
790 sqlite3VdbeChangeP5(v
, OPFLAG_AUXDELETE
);
792 if( iIdxNoSeek
>=0 && iIdxNoSeek
!=iDataCur
){
793 sqlite3VdbeAddOp1(v
, OP_Delete
, iIdxNoSeek
);
795 if( eMode
==ONEPASS_MULTI
) p5
|= OPFLAG_SAVEPOSITION
;
796 sqlite3VdbeChangeP5(v
, p5
);
799 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
800 ** handle rows (possibly in other tables) that refer via a foreign key
801 ** to the row just deleted. */
802 sqlite3FkActions(pParse
, pTab
, 0, iOld
, 0, 0);
804 /* Invoke AFTER DELETE trigger programs. */
805 sqlite3CodeRowTrigger(pParse
, pTrigger
,
806 TK_DELETE
, 0, TRIGGER_AFTER
, pTab
, iOld
, onconf
, iLabel
809 /* Jump here if the row had already been deleted before any BEFORE
810 ** trigger programs were invoked. Or if a trigger program throws a
811 ** RAISE(IGNORE) exception. */
812 sqlite3VdbeResolveLabel(v
, iLabel
);
813 VdbeModuleComment((v
, "END: GenRowDel()"));
817 ** This routine generates VDBE code that causes the deletion of all
818 ** index entries associated with a single row of a single table, pTab
822 ** 1. A read/write cursor "iDataCur" must be open on the canonical storage
823 ** btree for the table pTab. (This will be either the table itself
824 ** for rowid tables or to the primary key index for WITHOUT ROWID
827 ** 2. Read/write cursors for all indices of pTab must be open as
828 ** cursor number iIdxCur+i for the i-th index. (The pTab->pIndex
829 ** index is the 0-th index.)
831 ** 3. The "iDataCur" cursor must be already be positioned on the row
832 ** that is to be deleted.
834 void sqlite3GenerateRowIndexDelete(
835 Parse
*pParse
, /* Parsing and code generating context */
836 Table
*pTab
, /* Table containing the row to be deleted */
837 int iDataCur
, /* Cursor of table holding data. */
838 int iIdxCur
, /* First index cursor */
839 int *aRegIdx
, /* Only delete if aRegIdx!=0 && aRegIdx[i]>0 */
840 int iIdxNoSeek
/* Do not delete from this cursor */
842 int i
; /* Index loop counter */
843 int r1
= -1; /* Register holding an index key */
844 int iPartIdxLabel
; /* Jump destination for skipping partial index entries */
845 Index
*pIdx
; /* Current index */
846 Index
*pPrior
= 0; /* Prior index */
847 Vdbe
*v
; /* The prepared statement under construction */
848 Index
*pPk
; /* PRIMARY KEY index, or NULL for rowid tables */
851 pPk
= HasRowid(pTab
) ? 0 : sqlite3PrimaryKeyIndex(pTab
);
852 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; i
++, pIdx
=pIdx
->pNext
){
853 assert( iIdxCur
+i
!=iDataCur
|| pPk
==pIdx
);
854 if( aRegIdx
!=0 && aRegIdx
[i
]==0 ) continue;
855 if( pIdx
==pPk
) continue;
856 if( iIdxCur
+i
==iIdxNoSeek
) continue;
857 VdbeModuleComment((v
, "GenRowIdxDel for %s", pIdx
->zName
));
858 r1
= sqlite3GenerateIndexKey(pParse
, pIdx
, iDataCur
, 0, 1,
859 &iPartIdxLabel
, pPrior
, r1
);
860 sqlite3VdbeAddOp3(v
, OP_IdxDelete
, iIdxCur
+i
, r1
,
861 pIdx
->uniqNotNull
? pIdx
->nKeyCol
: pIdx
->nColumn
);
862 sqlite3ResolvePartIdxLabel(pParse
, iPartIdxLabel
);
868 ** Generate code that will assemble an index key and stores it in register
869 ** regOut. The key with be for index pIdx which is an index on pTab.
870 ** iCur is the index of a cursor open on the pTab table and pointing to
871 ** the entry that needs indexing. If pTab is a WITHOUT ROWID table, then
872 ** iCur must be the cursor of the PRIMARY KEY index.
874 ** Return a register number which is the first in a block of
875 ** registers that holds the elements of the index key. The
876 ** block of registers has already been deallocated by the time
877 ** this routine returns.
879 ** If *piPartIdxLabel is not NULL, fill it in with a label and jump
880 ** to that label if pIdx is a partial index that should be skipped.
881 ** The label should be resolved using sqlite3ResolvePartIdxLabel().
882 ** A partial index should be skipped if its WHERE clause evaluates
883 ** to false or null. If pIdx is not a partial index, *piPartIdxLabel
884 ** will be set to zero which is an empty label that is ignored by
885 ** sqlite3ResolvePartIdxLabel().
887 ** The pPrior and regPrior parameters are used to implement a cache to
888 ** avoid unnecessary register loads. If pPrior is not NULL, then it is
889 ** a pointer to a different index for which an index key has just been
890 ** computed into register regPrior. If the current pIdx index is generating
891 ** its key into the same sequence of registers and if pPrior and pIdx share
892 ** a column in common, then the register corresponding to that column already
893 ** holds the correct value and the loading of that register is skipped.
894 ** This optimization is helpful when doing a DELETE or an INTEGRITY_CHECK
895 ** on a table with multiple indices, and especially with the ROWID or
896 ** PRIMARY KEY columns of the index.
898 int sqlite3GenerateIndexKey(
899 Parse
*pParse
, /* Parsing context */
900 Index
*pIdx
, /* The index for which to generate a key */
901 int iDataCur
, /* Cursor number from which to take column data */
902 int regOut
, /* Put the new key into this register if not 0 */
903 int prefixOnly
, /* Compute only a unique prefix of the key */
904 int *piPartIdxLabel
, /* OUT: Jump to this label to skip partial index */
905 Index
*pPrior
, /* Previously generated index key */
906 int regPrior
/* Register holding previous generated key */
908 Vdbe
*v
= pParse
->pVdbe
;
913 if( piPartIdxLabel
){
914 if( pIdx
->pPartIdxWhere
){
915 *piPartIdxLabel
= sqlite3VdbeMakeLabel(pParse
);
916 pParse
->iSelfTab
= iDataCur
+ 1;
917 sqlite3ExprIfFalseDup(pParse
, pIdx
->pPartIdxWhere
, *piPartIdxLabel
,
919 pParse
->iSelfTab
= 0;
924 nCol
= (prefixOnly
&& pIdx
->uniqNotNull
) ? pIdx
->nKeyCol
: pIdx
->nColumn
;
925 regBase
= sqlite3GetTempRange(pParse
, nCol
);
926 if( pPrior
&& (regBase
!=regPrior
|| pPrior
->pPartIdxWhere
) ) pPrior
= 0;
927 for(j
=0; j
<nCol
; j
++){
929 && pPrior
->aiColumn
[j
]==pIdx
->aiColumn
[j
]
930 && pPrior
->aiColumn
[j
]!=XN_EXPR
932 /* This column was already computed by the previous index */
935 sqlite3ExprCodeLoadIndexColumn(pParse
, pIdx
, iDataCur
, j
, regBase
+j
);
936 /* If the column affinity is REAL but the number is an integer, then it
937 ** might be stored in the table as an integer (using a compact
938 ** representation) then converted to REAL by an OP_RealAffinity opcode.
939 ** But we are getting ready to store this value back into an index, where
940 ** it should be converted by to INTEGER again. So omit the OP_RealAffinity
941 ** opcode if it is present */
942 sqlite3VdbeDeletePriorOpcode(v
, OP_RealAffinity
);
945 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
, nCol
, regOut
);
946 if( pIdx
->pTable
->pSelect
){
947 const char *zAff
= sqlite3IndexAffinityStr(pParse
->db
, pIdx
);
948 sqlite3VdbeChangeP4(v
, -1, zAff
, P4_TRANSIENT
);
951 sqlite3ReleaseTempRange(pParse
, regBase
, nCol
);
956 ** If a prior call to sqlite3GenerateIndexKey() generated a jump-over label
957 ** because it was a partial index, then this routine should be called to
958 ** resolve that label.
960 void sqlite3ResolvePartIdxLabel(Parse
*pParse
, int iLabel
){
962 sqlite3VdbeResolveLabel(pParse
->pVdbe
, iLabel
);