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 ** in order to generate code for DELETE FROM statements.
14 */
15 #include "sqliteInt.h"
16
17 /*
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.
24 **
25 ** The following fields are initialized appropriate in pSrc:
26 **
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
29 **
30 */
sqlite3SrcListLookup(Parse * pParse,SrcList * pSrc)31 Table *sqlite3SrcListLookup(Parse *pParse, SrcList *pSrc){
32 SrcItem *pItem = pSrc->a;
33 Table *pTab;
34 assert( pItem && pSrc->nSrc>=1 );
35 pTab = sqlite3LocateTableItem(pParse, 0, pItem);
36 sqlite3DeleteTable(pParse->db, pItem->pTab);
37 pItem->pTab = pTab;
38 if( pTab ){
39 pTab->nTabRef++;
40 if( pItem->fg.isIndexedBy && sqlite3IndexedByLookup(pParse, pItem) ){
41 pTab = 0;
42 }
43 }
44 return pTab;
45 }
46
47 /* Return true if table pTab is read-only.
48 **
49 ** A table is read-only if any of the following are true:
50 **
51 ** 1) It is a virtual table and no implementation of the xUpdate method
52 ** has been provided
53 **
54 ** 2) It is a system table (i.e. sqlite_schema), this call is not
55 ** part of a nested parse and writable_schema pragma has not
56 ** been specified
57 **
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.
61 */
tabIsReadOnly(Parse * pParse,Table * pTab)62 static int tabIsReadOnly(Parse *pParse, Table *pTab){
63 sqlite3 *db;
64 if( IsVirtual(pTab) ){
65 return sqlite3GetVTable(pParse->db, pTab)->pMod->pModule->xUpdate==0;
66 }
67 if( (pTab->tabFlags & (TF_Readonly|TF_Shadow))==0 ) return 0;
68 db = pParse->db;
69 if( (pTab->tabFlags & TF_Readonly)!=0 ){
70 return sqlite3WritableSchema(db)==0 && pParse->nested==0;
71 }
72 assert( pTab->tabFlags & TF_Shadow );
73 return sqlite3ReadOnlyShadowTables(db);
74 }
75
76 /*
77 ** Check to make sure the given table is writable. If it is not
78 ** writable, generate an error message and return 1. If it is
79 ** writable return 0;
80 */
sqlite3IsReadOnly(Parse * pParse,Table * pTab,int viewOk)81 int sqlite3IsReadOnly(Parse *pParse, Table *pTab, int viewOk){
82 if( tabIsReadOnly(pParse, pTab) ){
83 sqlite3ErrorMsg(pParse, "table %s may not be modified", pTab->zName);
84 return 1;
85 }
86 #ifndef SQLITE_OMIT_VIEW
87 if( !viewOk && pTab->pSelect ){
88 sqlite3ErrorMsg(pParse,"cannot modify %s because it is a view",pTab->zName);
89 return 1;
90 }
91 #endif
92 return 0;
93 }
94
95
96 #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
97 /*
98 ** Evaluate a view and store its result in an ephemeral table. The
99 ** pWhere argument is an optional WHERE clause that restricts the
100 ** set of rows in the view that are to be added to the ephemeral table.
101 */
sqlite3MaterializeView(Parse * pParse,Table * pView,Expr * pWhere,ExprList * pOrderBy,Expr * pLimit,int iCur)102 void sqlite3MaterializeView(
103 Parse *pParse, /* Parsing context */
104 Table *pView, /* View definition */
105 Expr *pWhere, /* Optional WHERE clause to be added */
106 ExprList *pOrderBy, /* Optional ORDER BY clause */
107 Expr *pLimit, /* Optional LIMIT clause */
108 int iCur /* Cursor number for ephemeral table */
109 ){
110 SelectDest dest;
111 Select *pSel;
112 SrcList *pFrom;
113 sqlite3 *db = pParse->db;
114 int iDb = sqlite3SchemaToIndex(db, pView->pSchema);
115 pWhere = sqlite3ExprDup(db, pWhere, 0);
116 pFrom = sqlite3SrcListAppend(pParse, 0, 0, 0);
117 if( pFrom ){
118 assert( pFrom->nSrc==1 );
119 pFrom->a[0].zName = sqlite3DbStrDup(db, pView->zName);
120 pFrom->a[0].zDatabase = sqlite3DbStrDup(db, db->aDb[iDb].zDbSName);
121 assert( pFrom->a[0].pOn==0 );
122 assert( pFrom->a[0].pUsing==0 );
123 }
124 pSel = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, pOrderBy,
125 SF_IncludeHidden, pLimit);
126 sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
127 sqlite3Select(pParse, pSel, &dest);
128 sqlite3SelectDelete(db, pSel);
129 }
130 #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
131
132 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
133 /*
134 ** Generate an expression tree to implement the WHERE, ORDER BY,
135 ** and LIMIT/OFFSET portion of DELETE and UPDATE statements.
136 **
137 ** DELETE FROM table_wxyz WHERE a<5 ORDER BY a LIMIT 1;
138 ** \__________________________/
139 ** pLimitWhere (pInClause)
140 */
sqlite3LimitWhere(Parse * pParse,SrcList * pSrc,Expr * pWhere,ExprList * pOrderBy,Expr * pLimit,char * zStmtType)141 Expr *sqlite3LimitWhere(
142 Parse *pParse, /* The parser context */
143 SrcList *pSrc, /* the FROM clause -- which tables to scan */
144 Expr *pWhere, /* The WHERE clause. May be null */
145 ExprList *pOrderBy, /* The ORDER BY clause. May be null */
146 Expr *pLimit, /* The LIMIT clause. May be null */
147 char *zStmtType /* Either DELETE or UPDATE. For err msgs. */
148 ){
149 sqlite3 *db = pParse->db;
150 Expr *pLhs = NULL; /* LHS of IN(SELECT...) operator */
151 Expr *pInClause = NULL; /* WHERE rowid IN ( select ) */
152 ExprList *pEList = NULL; /* Expression list contaning only pSelectRowid */
153 SrcList *pSelectSrc = NULL; /* SELECT rowid FROM x ... (dup of pSrc) */
154 Select *pSelect = NULL; /* Complete SELECT tree */
155 Table *pTab;
156
157 /* Check that there isn't an ORDER BY without a LIMIT clause.
158 */
159 if( pOrderBy && pLimit==0 ) {
160 sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType);
161 sqlite3ExprDelete(pParse->db, pWhere);
162 sqlite3ExprListDelete(pParse->db, pOrderBy);
163 return 0;
164 }
165
166 /* We only need to generate a select expression if there
167 ** is a limit/offset term to enforce.
168 */
169 if( pLimit == 0 ) {
170 return pWhere;
171 }
172
173 /* Generate a select expression tree to enforce the limit/offset
174 ** term for the DELETE or UPDATE statement. For example:
175 ** DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
176 ** becomes:
177 ** DELETE FROM table_a WHERE rowid IN (
178 ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
179 ** );
180 */
181
182 pTab = pSrc->a[0].pTab;
183 if( HasRowid(pTab) ){
184 pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0);
185 pEList = sqlite3ExprListAppend(
186 pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0)
187 );
188 }else{
189 Index *pPk = sqlite3PrimaryKeyIndex(pTab);
190 if( pPk->nKeyCol==1 ){
191 const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
192 pLhs = sqlite3Expr(db, TK_ID, zName);
193 pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName));
194 }else{
195 int i;
196 for(i=0; i<pPk->nKeyCol; i++){
197 Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName);
198 pEList = sqlite3ExprListAppend(pParse, pEList, p);
199 }
200 pLhs = sqlite3PExpr(pParse, TK_VECTOR, 0, 0);
201 if( pLhs ){
202 pLhs->x.pList = sqlite3ExprListDup(db, pEList, 0);
203 }
204 }
205 }
206
207 /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree
208 ** and the SELECT subtree. */
209 pSrc->a[0].pTab = 0;
210 pSelectSrc = sqlite3SrcListDup(db, pSrc, 0);
211 pSrc->a[0].pTab = pTab;
212 if( pSrc->a[0].fg.isIndexedBy ){
213 pSrc->a[0].u2.pIBIndex = 0;
214 pSrc->a[0].fg.isIndexedBy = 0;
215 sqlite3DbFree(db, pSrc->a[0].u1.zIndexedBy);
216 }else if( pSrc->a[0].fg.isCte ){
217 pSrc->a[0].u2.pCteUse->nUse++;
218 }
219
220 /* generate the SELECT expression tree. */
221 pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0,
222 pOrderBy,0,pLimit
223 );
224
225 /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */
226 pInClause = sqlite3PExpr(pParse, TK_IN, pLhs, 0);
227 sqlite3PExprAddSelect(pParse, pInClause, pSelect);
228 return pInClause;
229 }
230 #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) */
231 /* && !defined(SQLITE_OMIT_SUBQUERY) */
232
233 /*
234 ** Generate code for a DELETE FROM statement.
235 **
236 ** DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
237 ** \________/ \________________/
238 ** pTabList pWhere
239 */
sqlite3DeleteFrom(Parse * pParse,SrcList * pTabList,Expr * pWhere,ExprList * pOrderBy,Expr * pLimit)240 void sqlite3DeleteFrom(
241 Parse *pParse, /* The parser context */
242 SrcList *pTabList, /* The table from which we should delete things */
243 Expr *pWhere, /* The WHERE clause. May be null */
244 ExprList *pOrderBy, /* ORDER BY clause. May be null */
245 Expr *pLimit /* LIMIT clause. May be null */
246 ){
247 Vdbe *v; /* The virtual database engine */
248 Table *pTab; /* The table from which records will be deleted */
249 int i; /* Loop counter */
250 WhereInfo *pWInfo; /* Information about the WHERE clause */
251 Index *pIdx; /* For looping over indices of the table */
252 int iTabCur; /* Cursor number for the table */
253 int iDataCur = 0; /* VDBE cursor for the canonical data source */
254 int iIdxCur = 0; /* Cursor number of the first index */
255 int nIdx; /* Number of indices */
256 sqlite3 *db; /* Main database structure */
257 AuthContext sContext; /* Authorization context */
258 NameContext sNC; /* Name context to resolve expressions in */
259 int iDb; /* Database number */
260 int memCnt = 0; /* Memory cell used for change counting */
261 int rcauth; /* Value returned by authorization callback */
262 int eOnePass; /* ONEPASS_OFF or _SINGLE or _MULTI */
263 int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */
264 u8 *aToOpen = 0; /* Open cursor iTabCur+j if aToOpen[j] is true */
265 Index *pPk; /* The PRIMARY KEY index on the table */
266 int iPk = 0; /* First of nPk registers holding PRIMARY KEY value */
267 i16 nPk = 1; /* Number of columns in the PRIMARY KEY */
268 int iKey; /* Memory cell holding key of row to be deleted */
269 i16 nKey; /* Number of memory cells in the row key */
270 int iEphCur = 0; /* Ephemeral table holding all primary key values */
271 int iRowSet = 0; /* Register for rowset of rows to delete */
272 int addrBypass = 0; /* Address of jump over the delete logic */
273 int addrLoop = 0; /* Top of the delete loop */
274 int addrEphOpen = 0; /* Instruction to open the Ephemeral table */
275 int bComplex; /* True if there are triggers or FKs or
276 ** subqueries in the WHERE clause */
277
278 #ifndef SQLITE_OMIT_TRIGGER
279 int isView; /* True if attempting to delete from a view */
280 Trigger *pTrigger; /* List of table triggers, if required */
281 #endif
282
283 memset(&sContext, 0, sizeof(sContext));
284 db = pParse->db;
285 if( pParse->nErr || db->mallocFailed ){
286 goto delete_from_cleanup;
287 }
288 assert( pTabList->nSrc==1 );
289
290
291 /* Locate the table which we want to delete. This table has to be
292 ** put in an SrcList structure because some of the subroutines we
293 ** will be calling are designed to work with multiple tables and expect
294 ** an SrcList* parameter instead of just a Table* parameter.
295 */
296 pTab = sqlite3SrcListLookup(pParse, pTabList);
297 if( pTab==0 ) goto delete_from_cleanup;
298
299 /* Figure out if we have any triggers and if the table being
300 ** deleted from is a view
301 */
302 #ifndef SQLITE_OMIT_TRIGGER
303 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
304 isView = pTab->pSelect!=0;
305 #else
306 # define pTrigger 0
307 # define isView 0
308 #endif
309 bComplex = pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0);
310 #ifdef SQLITE_OMIT_VIEW
311 # undef isView
312 # define isView 0
313 #endif
314
315 #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
316 if( !isView ){
317 pWhere = sqlite3LimitWhere(
318 pParse, pTabList, pWhere, pOrderBy, pLimit, "DELETE"
319 );
320 pOrderBy = 0;
321 pLimit = 0;
322 }
323 #endif
324
325 /* If pTab is really a view, make sure it has been initialized.
326 */
327 if( sqlite3ViewGetColumnNames(pParse, pTab) ){
328 goto delete_from_cleanup;
329 }
330
331 if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
332 goto delete_from_cleanup;
333 }
334 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
335 assert( iDb<db->nDb );
336 rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0,
337 db->aDb[iDb].zDbSName);
338 assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
339 if( rcauth==SQLITE_DENY ){
340 goto delete_from_cleanup;
341 }
342 assert(!isView || pTrigger);
343
344 /* Assign cursor numbers to the table and all its indices.
345 */
346 assert( pTabList->nSrc==1 );
347 iTabCur = pTabList->a[0].iCursor = pParse->nTab++;
348 for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
349 pParse->nTab++;
350 }
351
352 /* Start the view context
353 */
354 if( isView ){
355 sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
356 }
357
358 /* Begin generating code.
359 */
360 v = sqlite3GetVdbe(pParse);
361 if( v==0 ){
362 goto delete_from_cleanup;
363 }
364 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
365 sqlite3BeginWriteOperation(pParse, bComplex, iDb);
366
367 /* If we are trying to delete from a view, realize that view into
368 ** an ephemeral table.
369 */
370 #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
371 if( isView ){
372 sqlite3MaterializeView(pParse, pTab,
373 pWhere, pOrderBy, pLimit, iTabCur
374 );
375 iDataCur = iIdxCur = iTabCur;
376 pOrderBy = 0;
377 pLimit = 0;
378 }
379 #endif
380
381 /* Resolve the column names in the WHERE clause.
382 */
383 memset(&sNC, 0, sizeof(sNC));
384 sNC.pParse = pParse;
385 sNC.pSrcList = pTabList;
386 if( sqlite3ResolveExprNames(&sNC, pWhere) ){
387 goto delete_from_cleanup;
388 }
389
390 /* Initialize the counter of the number of rows deleted, if
391 ** we are counting rows.
392 */
393 if( (db->flags & SQLITE_CountRows)!=0
394 && !pParse->nested
395 && !pParse->pTriggerTab
396 && !pParse->bReturning
397 ){
398 memCnt = ++pParse->nMem;
399 sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
400 }
401
402 #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
403 /* Special case: A DELETE without a WHERE clause deletes everything.
404 ** It is easier just to erase the whole table. Prior to version 3.6.5,
405 ** this optimization caused the row change count (the value returned by
406 ** API function sqlite3_count_changes) to be set incorrectly.
407 **
408 ** The "rcauth==SQLITE_OK" terms is the
409 ** IMPLEMENTATION-OF: R-17228-37124 If the action code is SQLITE_DELETE and
410 ** the callback returns SQLITE_IGNORE then the DELETE operation proceeds but
411 ** the truncate optimization is disabled and all rows are deleted
412 ** individually.
413 */
414 if( rcauth==SQLITE_OK
415 && pWhere==0
416 && !bComplex
417 && !IsVirtual(pTab)
418 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
419 && db->xPreUpdateCallback==0
420 #endif
421 ){
422 assert( !isView );
423 sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName);
424 if( HasRowid(pTab) ){
425 sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt ? memCnt : -1,
426 pTab->zName, P4_STATIC);
427 }
428 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
429 assert( pIdx->pSchema==pTab->pSchema );
430 sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
431 }
432 }else
433 #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
434 {
435 u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
436 if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
437 wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
438 if( HasRowid(pTab) ){
439 /* For a rowid table, initialize the RowSet to an empty set */
440 pPk = 0;
441 nPk = 1;
442 iRowSet = ++pParse->nMem;
443 sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
444 }else{
445 /* For a WITHOUT ROWID table, create an ephemeral table used to
446 ** hold all primary keys for rows to be deleted. */
447 pPk = sqlite3PrimaryKeyIndex(pTab);
448 assert( pPk!=0 );
449 nPk = pPk->nKeyCol;
450 iPk = pParse->nMem+1;
451 pParse->nMem += nPk;
452 iEphCur = pParse->nTab++;
453 addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk);
454 sqlite3VdbeSetP4KeyInfo(pParse, pPk);
455 }
456
457 /* Construct a query to find the rowid or primary key for every row
458 ** to be deleted, based on the WHERE clause. Set variable eOnePass
459 ** to indicate the strategy used to implement this delete:
460 **
461 ** ONEPASS_OFF: Two-pass approach - use a FIFO for rowids/PK values.
462 ** ONEPASS_SINGLE: One-pass approach - at most one row deleted.
463 ** ONEPASS_MULTI: One-pass approach - any number of rows may be deleted.
464 */
465 pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1);
466 if( pWInfo==0 ) goto delete_from_cleanup;
467 eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
468 assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI );
469 assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF );
470 if( eOnePass!=ONEPASS_SINGLE ) sqlite3MultiWrite(pParse);
471 if( sqlite3WhereUsesDeferredSeek(pWInfo) ){
472 sqlite3VdbeAddOp1(v, OP_FinishSeek, iTabCur);
473 }
474
475 /* Keep track of the number of rows to be deleted */
476 if( memCnt ){
477 sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
478 }
479
480 /* Extract the rowid or primary key for the current row */
481 if( pPk ){
482 for(i=0; i<nPk; i++){
483 assert( pPk->aiColumn[i]>=0 );
484 sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur,
485 pPk->aiColumn[i], iPk+i);
486 }
487 iKey = iPk;
488 }else{
489 iKey = ++pParse->nMem;
490 sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, -1, iKey);
491 }
492
493 if( eOnePass!=ONEPASS_OFF ){
494 /* For ONEPASS, no need to store the rowid/primary-key. There is only
495 ** one, so just keep it in its register(s) and fall through to the
496 ** delete code. */
497 nKey = nPk; /* OP_Found will use an unpacked key */
498 aToOpen = sqlite3DbMallocRawNN(db, nIdx+2);
499 if( aToOpen==0 ){
500 sqlite3WhereEnd(pWInfo);
501 goto delete_from_cleanup;
502 }
503 memset(aToOpen, 1, nIdx+1);
504 aToOpen[nIdx+1] = 0;
505 if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
506 if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
507 if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen);
508 addrBypass = sqlite3VdbeMakeLabel(pParse);
509 }else{
510 if( pPk ){
511 /* Add the PK key for this row to the temporary table */
512 iKey = ++pParse->nMem;
513 nKey = 0; /* Zero tells OP_Found to use a composite key */
514 sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
515 sqlite3IndexAffinityStr(pParse->db, pPk), nPk);
516 sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEphCur, iKey, iPk, nPk);
517 }else{
518 /* Add the rowid of the row to be deleted to the RowSet */
519 nKey = 1; /* OP_DeferredSeek always uses a single rowid */
520 sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey);
521 }
522 sqlite3WhereEnd(pWInfo);
523 }
524
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
528 ** triggers.
529 */
530 if( !isView ){
531 int iAddrOnce = 0;
532 if( eOnePass==ONEPASS_MULTI ){
533 iAddrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
534 }
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 ){
541 sqlite3VdbeJumpHereOrPopInst(v, iAddrOnce);
542 }
543 }
544
545 /* Set up a loop over the rowids/primary-keys that were found in the
546 ** where-clause loop above.
547 */
548 if( eOnePass!=ONEPASS_OFF ){
549 assert( nKey==nPk ); /* OP_Found will use an unpacked key */
550 if( !IsVirtual(pTab) && aToOpen[iDataCur-iTabCur] ){
551 assert( pPk!=0 || pTab->pSelect!=0 );
552 sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey);
553 VdbeCoverage(v);
554 }
555 }else if( pPk ){
556 addrLoop = sqlite3VdbeAddOp1(v, OP_Rewind, iEphCur); VdbeCoverage(v);
557 if( IsVirtual(pTab) ){
558 sqlite3VdbeAddOp3(v, OP_Column, iEphCur, 0, iKey);
559 }else{
560 sqlite3VdbeAddOp2(v, OP_RowData, iEphCur, iKey);
561 }
562 assert( nKey==0 ); /* OP_Found will use a composite key */
563 }else{
564 addrLoop = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, 0, iKey);
565 VdbeCoverage(v);
566 assert( nKey==1 );
567 }
568
569 /* Delete the row */
570 #ifndef SQLITE_OMIT_VIRTUALTABLE
571 if( IsVirtual(pTab) ){
572 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
573 sqlite3VtabMakeWritable(pParse, pTab);
574 assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE );
575 sqlite3MayAbort(pParse);
576 if( eOnePass==ONEPASS_SINGLE ){
577 sqlite3VdbeAddOp1(v, OP_Close, iTabCur);
578 if( sqlite3IsToplevel(pParse) ){
579 pParse->isMultiWrite = 0;
580 }
581 }
582 sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iKey, pVTab, P4_VTAB);
583 sqlite3VdbeChangeP5(v, OE_Abort);
584 }else
585 #endif
586 {
587 int count = (pParse->nested==0); /* True to count changes */
588 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
589 iKey, nKey, count, OE_Default, eOnePass, aiCurOnePass[1]);
590 }
591
592 /* End of the loop over all rowids/primary-keys. */
593 if( eOnePass!=ONEPASS_OFF ){
594 sqlite3VdbeResolveLabel(v, addrBypass);
595 sqlite3WhereEnd(pWInfo);
596 }else if( pPk ){
597 sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v);
598 sqlite3VdbeJumpHere(v, addrLoop);
599 }else{
600 sqlite3VdbeGoto(v, addrLoop);
601 sqlite3VdbeJumpHere(v, addrLoop);
602 }
603 } /* End non-truncate path */
604
605 /* Update the sqlite_sequence table by storing the content of the
606 ** maximum rowid counter values recorded while inserting into
607 ** autoincrement tables.
608 */
609 if( pParse->nested==0 && pParse->pTriggerTab==0 ){
610 sqlite3AutoincrementEnd(pParse);
611 }
612
613 /* Return the number of rows that were deleted. If this routine is
614 ** generating code because of a call to sqlite3NestedParse(), do not
615 ** invoke the callback function.
616 */
617 if( memCnt ){
618 sqlite3VdbeAddOp2(v, OP_ChngCntRow, memCnt, 1);
619 sqlite3VdbeSetNumCols(v, 1);
620 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
621 }
622
623 delete_from_cleanup:
624 sqlite3AuthContextPop(&sContext);
625 sqlite3SrcListDelete(db, pTabList);
626 sqlite3ExprDelete(db, pWhere);
627 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
628 sqlite3ExprListDelete(db, pOrderBy);
629 sqlite3ExprDelete(db, pLimit);
630 #endif
631 sqlite3DbFree(db, aToOpen);
632 return;
633 }
634 /* Make sure "isView" and other macros defined above are undefined. Otherwise
635 ** they may interfere with compilation of other functions in this file
636 ** (or in another file, if this file becomes part of the amalgamation). */
637 #ifdef isView
638 #undef isView
639 #endif
640 #ifdef pTrigger
641 #undef pTrigger
642 #endif
643
644 /*
645 ** This routine generates VDBE code that causes a single row of a
646 ** single table to be deleted. Both the original table entry and
647 ** all indices are removed.
648 **
649 ** Preconditions:
650 **
651 ** 1. iDataCur is an open cursor on the btree that is the canonical data
652 ** store for the table. (This will be either the table itself,
653 ** in the case of a rowid table, or the PRIMARY KEY index in the case
654 ** of a WITHOUT ROWID table.)
655 **
656 ** 2. Read/write cursors for all indices of pTab must be open as
657 ** cursor number iIdxCur+i for the i-th index.
658 **
659 ** 3. The primary key for the row to be deleted must be stored in a
660 ** sequence of nPk memory cells starting at iPk. If nPk==0 that means
661 ** that a search record formed from OP_MakeRecord is contained in the
662 ** single memory location iPk.
663 **
664 ** eMode:
665 ** Parameter eMode may be passed either ONEPASS_OFF (0), ONEPASS_SINGLE, or
666 ** ONEPASS_MULTI. If eMode is not ONEPASS_OFF, then the cursor
667 ** iDataCur already points to the row to delete. If eMode is ONEPASS_OFF
668 ** then this function must seek iDataCur to the entry identified by iPk
669 ** and nPk before reading from it.
670 **
671 ** If eMode is ONEPASS_MULTI, then this call is being made as part
672 ** of a ONEPASS delete that affects multiple rows. In this case, if
673 ** iIdxNoSeek is a valid cursor number (>=0) and is not the same as
674 ** iDataCur, then its position should be preserved following the delete
675 ** operation. Or, if iIdxNoSeek is not a valid cursor number, the
676 ** position of iDataCur should be preserved instead.
677 **
678 ** iIdxNoSeek:
679 ** If iIdxNoSeek is a valid cursor number (>=0) not equal to iDataCur,
680 ** then it identifies an index cursor (from within array of cursors
681 ** starting at iIdxCur) that already points to the index entry to be deleted.
682 ** Except, this optimization is disabled if there are BEFORE triggers since
683 ** the trigger body might have moved the cursor.
684 */
sqlite3GenerateRowDelete(Parse * pParse,Table * pTab,Trigger * pTrigger,int iDataCur,int iIdxCur,int iPk,i16 nPk,u8 count,u8 onconf,u8 eMode,int iIdxNoSeek)685 void sqlite3GenerateRowDelete(
686 Parse *pParse, /* Parsing context */
687 Table *pTab, /* Table containing the row to be deleted */
688 Trigger *pTrigger, /* List of triggers to (potentially) fire */
689 int iDataCur, /* Cursor from which column data is extracted */
690 int iIdxCur, /* First index cursor */
691 int iPk, /* First memory cell containing the PRIMARY KEY */
692 i16 nPk, /* Number of PRIMARY KEY memory cells */
693 u8 count, /* If non-zero, increment the row change counter */
694 u8 onconf, /* Default ON CONFLICT policy for triggers */
695 u8 eMode, /* ONEPASS_OFF, _SINGLE, or _MULTI. See above */
696 int iIdxNoSeek /* Cursor number of cursor that does not need seeking */
697 ){
698 Vdbe *v = pParse->pVdbe; /* Vdbe */
699 int iOld = 0; /* First register in OLD.* array */
700 int iLabel; /* Label resolved to end of generated code */
701 u8 opSeek; /* Seek opcode */
702
703 /* Vdbe is guaranteed to have been allocated by this stage. */
704 assert( v );
705 VdbeModuleComment((v, "BEGIN: GenRowDel(%d,%d,%d,%d)",
706 iDataCur, iIdxCur, iPk, (int)nPk));
707
708 /* Seek cursor iCur to the row to delete. If this row no longer exists
709 ** (this can happen if a trigger program has already deleted it), do
710 ** not attempt to delete it or fire any DELETE triggers. */
711 iLabel = sqlite3VdbeMakeLabel(pParse);
712 opSeek = HasRowid(pTab) ? OP_NotExists : OP_NotFound;
713 if( eMode==ONEPASS_OFF ){
714 sqlite3VdbeAddOp4Int(v, opSeek, iDataCur, iLabel, iPk, nPk);
715 VdbeCoverageIf(v, opSeek==OP_NotExists);
716 VdbeCoverageIf(v, opSeek==OP_NotFound);
717 }
718
719 /* If there are any triggers to fire, allocate a range of registers to
720 ** use for the old.* references in the triggers. */
721 if( sqlite3FkRequired(pParse, pTab, 0, 0) || pTrigger ){
722 u32 mask; /* Mask of OLD.* columns in use */
723 int iCol; /* Iterator used while populating OLD.* */
724 int addrStart; /* Start of BEFORE trigger programs */
725
726 /* TODO: Could use temporary registers here. Also could attempt to
727 ** avoid copying the contents of the rowid register. */
728 mask = sqlite3TriggerColmask(
729 pParse, pTrigger, 0, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onconf
730 );
731 mask |= sqlite3FkOldmask(pParse, pTab);
732 iOld = pParse->nMem+1;
733 pParse->nMem += (1 + pTab->nCol);
734
735 /* Populate the OLD.* pseudo-table register array. These values will be
736 ** used by any BEFORE and AFTER triggers that exist. */
737 sqlite3VdbeAddOp2(v, OP_Copy, iPk, iOld);
738 for(iCol=0; iCol<pTab->nCol; iCol++){
739 testcase( mask!=0xffffffff && iCol==31 );
740 testcase( mask!=0xffffffff && iCol==32 );
741 if( mask==0xffffffff || (iCol<=31 && (mask & MASKBIT32(iCol))!=0) ){
742 int kk = sqlite3TableColumnToStorage(pTab, iCol);
743 sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, iCol, iOld+kk+1);
744 }
745 }
746
747 /* Invoke BEFORE DELETE trigger programs. */
748 addrStart = sqlite3VdbeCurrentAddr(v);
749 sqlite3CodeRowTrigger(pParse, pTrigger,
750 TK_DELETE, 0, TRIGGER_BEFORE, pTab, iOld, onconf, iLabel
751 );
752
753 /* If any BEFORE triggers were coded, then seek the cursor to the
754 ** row to be deleted again. It may be that the BEFORE triggers moved
755 ** the cursor or already deleted the row that the cursor was
756 ** pointing to.
757 **
758 ** Also disable the iIdxNoSeek optimization since the BEFORE trigger
759 ** may have moved that cursor.
760 */
761 if( addrStart<sqlite3VdbeCurrentAddr(v) ){
762 sqlite3VdbeAddOp4Int(v, opSeek, iDataCur, iLabel, iPk, nPk);
763 VdbeCoverageIf(v, opSeek==OP_NotExists);
764 VdbeCoverageIf(v, opSeek==OP_NotFound);
765 testcase( iIdxNoSeek>=0 );
766 iIdxNoSeek = -1;
767 }
768
769 /* Do FK processing. This call checks that any FK constraints that
770 ** refer to this table (i.e. constraints attached to other tables)
771 ** are not violated by deleting this row. */
772 sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0);
773 }
774
775 /* Delete the index and table entries. Skip this step if pTab is really
776 ** a view (in which case the only effect of the DELETE statement is to
777 ** fire the INSTEAD OF triggers).
778 **
779 ** If variable 'count' is non-zero, then this OP_Delete instruction should
780 ** invoke the update-hook. The pre-update-hook, on the other hand should
781 ** be invoked unless table pTab is a system table. The difference is that
782 ** the update-hook is not invoked for rows removed by REPLACE, but the
783 ** pre-update-hook is.
784 */
785 if( pTab->pSelect==0 ){
786 u8 p5 = 0;
787 sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,iIdxNoSeek);
788 sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, (count?OPFLAG_NCHANGE:0));
789 if( pParse->nested==0 || 0==sqlite3_stricmp(pTab->zName, "sqlite_stat1") ){
790 sqlite3VdbeAppendP4(v, (char*)pTab, P4_TABLE);
791 }
792 if( eMode!=ONEPASS_OFF ){
793 sqlite3VdbeChangeP5(v, OPFLAG_AUXDELETE);
794 }
795 if( iIdxNoSeek>=0 && iIdxNoSeek!=iDataCur ){
796 sqlite3VdbeAddOp1(v, OP_Delete, iIdxNoSeek);
797 }
798 if( eMode==ONEPASS_MULTI ) p5 |= OPFLAG_SAVEPOSITION;
799 sqlite3VdbeChangeP5(v, p5);
800 }
801
802 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
803 ** handle rows (possibly in other tables) that refer via a foreign key
804 ** to the row just deleted. */
805 sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0);
806
807 /* Invoke AFTER DELETE trigger programs. */
808 sqlite3CodeRowTrigger(pParse, pTrigger,
809 TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel
810 );
811
812 /* Jump here if the row had already been deleted before any BEFORE
813 ** trigger programs were invoked. Or if a trigger program throws a
814 ** RAISE(IGNORE) exception. */
815 sqlite3VdbeResolveLabel(v, iLabel);
816 VdbeModuleComment((v, "END: GenRowDel()"));
817 }
818
819 /*
820 ** This routine generates VDBE code that causes the deletion of all
821 ** index entries associated with a single row of a single table, pTab
822 **
823 ** Preconditions:
824 **
825 ** 1. A read/write cursor "iDataCur" must be open on the canonical storage
826 ** btree for the table pTab. (This will be either the table itself
827 ** for rowid tables or to the primary key index for WITHOUT ROWID
828 ** tables.)
829 **
830 ** 2. Read/write cursors for all indices of pTab must be open as
831 ** cursor number iIdxCur+i for the i-th index. (The pTab->pIndex
832 ** index is the 0-th index.)
833 **
834 ** 3. The "iDataCur" cursor must be already be positioned on the row
835 ** that is to be deleted.
836 */
sqlite3GenerateRowIndexDelete(Parse * pParse,Table * pTab,int iDataCur,int iIdxCur,int * aRegIdx,int iIdxNoSeek)837 void sqlite3GenerateRowIndexDelete(
838 Parse *pParse, /* Parsing and code generating context */
839 Table *pTab, /* Table containing the row to be deleted */
840 int iDataCur, /* Cursor of table holding data. */
841 int iIdxCur, /* First index cursor */
842 int *aRegIdx, /* Only delete if aRegIdx!=0 && aRegIdx[i]>0 */
843 int iIdxNoSeek /* Do not delete from this cursor */
844 ){
845 int i; /* Index loop counter */
846 int r1 = -1; /* Register holding an index key */
847 int iPartIdxLabel; /* Jump destination for skipping partial index entries */
848 Index *pIdx; /* Current index */
849 Index *pPrior = 0; /* Prior index */
850 Vdbe *v; /* The prepared statement under construction */
851 Index *pPk; /* PRIMARY KEY index, or NULL for rowid tables */
852
853 v = pParse->pVdbe;
854 pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
855 for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
856 assert( iIdxCur+i!=iDataCur || pPk==pIdx );
857 if( aRegIdx!=0 && aRegIdx[i]==0 ) continue;
858 if( pIdx==pPk ) continue;
859 if( iIdxCur+i==iIdxNoSeek ) continue;
860 VdbeModuleComment((v, "GenRowIdxDel for %s", pIdx->zName));
861 r1 = sqlite3GenerateIndexKey(pParse, pIdx, iDataCur, 0, 1,
862 &iPartIdxLabel, pPrior, r1);
863 sqlite3VdbeAddOp3(v, OP_IdxDelete, iIdxCur+i, r1,
864 pIdx->uniqNotNull ? pIdx->nKeyCol : pIdx->nColumn);
865 sqlite3VdbeChangeP5(v, 1); /* Cause IdxDelete to error if no entry found */
866 sqlite3ResolvePartIdxLabel(pParse, iPartIdxLabel);
867 pPrior = pIdx;
868 }
869 }
870
871 /*
872 ** Generate code that will assemble an index key and stores it in register
873 ** regOut. The key with be for index pIdx which is an index on pTab.
874 ** iCur is the index of a cursor open on the pTab table and pointing to
875 ** the entry that needs indexing. If pTab is a WITHOUT ROWID table, then
876 ** iCur must be the cursor of the PRIMARY KEY index.
877 **
878 ** Return a register number which is the first in a block of
879 ** registers that holds the elements of the index key. The
880 ** block of registers has already been deallocated by the time
881 ** this routine returns.
882 **
883 ** If *piPartIdxLabel is not NULL, fill it in with a label and jump
884 ** to that label if pIdx is a partial index that should be skipped.
885 ** The label should be resolved using sqlite3ResolvePartIdxLabel().
886 ** A partial index should be skipped if its WHERE clause evaluates
887 ** to false or null. If pIdx is not a partial index, *piPartIdxLabel
888 ** will be set to zero which is an empty label that is ignored by
889 ** sqlite3ResolvePartIdxLabel().
890 **
891 ** The pPrior and regPrior parameters are used to implement a cache to
892 ** avoid unnecessary register loads. If pPrior is not NULL, then it is
893 ** a pointer to a different index for which an index key has just been
894 ** computed into register regPrior. If the current pIdx index is generating
895 ** its key into the same sequence of registers and if pPrior and pIdx share
896 ** a column in common, then the register corresponding to that column already
897 ** holds the correct value and the loading of that register is skipped.
898 ** This optimization is helpful when doing a DELETE or an INTEGRITY_CHECK
899 ** on a table with multiple indices, and especially with the ROWID or
900 ** PRIMARY KEY columns of the index.
901 */
sqlite3GenerateIndexKey(Parse * pParse,Index * pIdx,int iDataCur,int regOut,int prefixOnly,int * piPartIdxLabel,Index * pPrior,int regPrior)902 int sqlite3GenerateIndexKey(
903 Parse *pParse, /* Parsing context */
904 Index *pIdx, /* The index for which to generate a key */
905 int iDataCur, /* Cursor number from which to take column data */
906 int regOut, /* Put the new key into this register if not 0 */
907 int prefixOnly, /* Compute only a unique prefix of the key */
908 int *piPartIdxLabel, /* OUT: Jump to this label to skip partial index */
909 Index *pPrior, /* Previously generated index key */
910 int regPrior /* Register holding previous generated key */
911 ){
912 Vdbe *v = pParse->pVdbe;
913 int j;
914 int regBase;
915 int nCol;
916
917 if( piPartIdxLabel ){
918 if( pIdx->pPartIdxWhere ){
919 *piPartIdxLabel = sqlite3VdbeMakeLabel(pParse);
920 pParse->iSelfTab = iDataCur + 1;
921 sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel,
922 SQLITE_JUMPIFNULL);
923 pParse->iSelfTab = 0;
924 pPrior = 0; /* Ticket a9efb42811fa41ee 2019-11-02;
925 ** pPartIdxWhere may have corrupted regPrior registers */
926 }else{
927 *piPartIdxLabel = 0;
928 }
929 }
930 nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn;
931 regBase = sqlite3GetTempRange(pParse, nCol);
932 if( pPrior && (regBase!=regPrior || pPrior->pPartIdxWhere) ) pPrior = 0;
933 for(j=0; j<nCol; j++){
934 if( pPrior
935 && pPrior->aiColumn[j]==pIdx->aiColumn[j]
936 && pPrior->aiColumn[j]!=XN_EXPR
937 ){
938 /* This column was already computed by the previous index */
939 continue;
940 }
941 sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iDataCur, j, regBase+j);
942 /* If the column affinity is REAL but the number is an integer, then it
943 ** might be stored in the table as an integer (using a compact
944 ** representation) then converted to REAL by an OP_RealAffinity opcode.
945 ** But we are getting ready to store this value back into an index, where
946 ** it should be converted by to INTEGER again. So omit the OP_RealAffinity
947 ** opcode if it is present */
948 sqlite3VdbeDeletePriorOpcode(v, OP_RealAffinity);
949 }
950 if( regOut ){
951 sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regOut);
952 }
953 sqlite3ReleaseTempRange(pParse, regBase, nCol);
954 return regBase;
955 }
956
957 /*
958 ** If a prior call to sqlite3GenerateIndexKey() generated a jump-over label
959 ** because it was a partial index, then this routine should be called to
960 ** resolve that label.
961 */
sqlite3ResolvePartIdxLabel(Parse * pParse,int iLabel)962 void sqlite3ResolvePartIdxLabel(Parse *pParse, int iLabel){
963 if( iLabel ){
964 sqlite3VdbeResolveLabel(pParse->pVdbe, iLabel);
965 }
966 }
967