/*
** Generate code for a DELETE FROM statement.
**
** DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
** \________/ \________________/
** pTabList pWhere
*/
void sqlite3DeleteFrom(
Parse *pParse, /* The parser context */
SrcList *pTabList, /* The table from which we should delete things */
Expr *pWhere /* The WHERE clause. May be null */
){
Vdbe *v; /* The virtual database engine */
Table *pTab; /* The table from which records will be deleted */
int i; /* Loop counter */
WhereInfo *pWInfo; /* Information about the WHERE clause */
Index *pIdx; /* For looping over indices of the table */
int iTabCur; /* Cursor number for the table */
int iDataCur = 0; /* VDBE cursor for the canonical data source */
int iIdxCur = 0; /* Cursor number of the first index */
int nIdx; /* Number of indices */
sqlite3 *db; /* Main database structure */
AuthContext sContext; /* Authorization context */
NameContext sNC; /* Name context to resolve expressions in */
int iDb; /* Database number */
int memCnt = -1; /* Memory cell used for change counting */
int rcauth; /* Value returned by authorization callback */
int eOnePass; /* ONEPASS_OFF or _SINGLE or _MULTI */
int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */
u8 *aToOpen = 0; /* Open cursor iTabCur+j if aToOpen[j] is true */
Index *pPk; /* The PRIMARY KEY index on the table */
int iPk = 0; /* First of nPk registers holding PRIMARY KEY value */
i16 nPk = 1; /* Number of columns in the PRIMARY KEY */
int iKey; /* Memory cell holding key of row to be deleted */
i16 nKey; /* Number of memory cells in the row key */
int iEphCur = 0; /* Ephemeral table holding all primary key values */
int iRowSet = 0; /* Register for rowset of rows to delete */
int addrBypass = 0; /* Address of jump over the delete logic */
int addrLoop = 0; /* Top of the delete loop */
int addrEphOpen = 0; /* Instruction to open the Ephemeral table */
int bComplex; /* True if there are triggers or FKs or
** subqueries in the WHERE clause */
#ifndef SQLITE_OMIT_TRIGGER
int isView; /* True if attempting to delete from a view */
Trigger *pTrigger; /* List of table triggers, if required */
#endif
memset(&sContext, 0, sizeof(sContext));
db = pParse->db;
if( pParse->nErr || db->mallocFailed ){
goto delete_from_cleanup;
}
assert( pTabList->nSrc==1 );
/* Locate the table which we want to delete. This table has to be
** put in an SrcList structure because some of the subroutines we
** will be calling are designed to work with multiple tables and expect
** an SrcList* parameter instead of just a Table* parameter.
*/
pTab = sqlite3SrcListLookup(pParse, pTabList);
if( pTab==0 ) goto delete_from_cleanup;
/* Figure out if we have any triggers and if the table being
** deleted from is a view
*/
#ifndef SQLITE_OMIT_TRIGGER
pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
isView = pTab->pSelect!=0;
bComplex = pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0);
#else
# define pTrigger 0
# define isView 0
#endif
#ifdef SQLITE_OMIT_VIEW
# undef isView
# define isView 0
#endif
/* If pTab is really a view, make sure it has been initialized.
*/
if( sqlite3ViewGetColumnNames(pParse, pTab) ){
goto delete_from_cleanup;
}
if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
goto delete_from_cleanup;
}
iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
assert( iDb<db->nDb );
rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0,
db->aDb[iDb].zDbSName);
assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
if( rcauth==SQLITE_DENY ){
goto delete_from_cleanup;
}
assert(!isView || pTrigger);
/* Assign cursor numbers to the table and all its indices.
*/
assert( pTabList->nSrc==1 );
iTabCur = pTabList->a[0].iCursor = pParse->nTab++;
for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
pParse->nTab++;
}
/* Start the view context
*/
if( isView ){
sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
}
/* Begin generating code.
*/
v = sqlite3GetVdbe(pParse);
if( v==0 ){
goto delete_from_cleanup;
}
if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
sqlite3BeginWriteOperation(pParse, 1, iDb);
/* If we are trying to delete from a view, realize that view into
** an ephemeral table.
*/
#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
if( isView ){
sqlite3MaterializeView(pParse, pTab, pWhere, iTabCur);
iDataCur = iIdxCur = iTabCur;
}
#endif
/* Resolve the column names in the WHERE clause.
*/
memset(&sNC, 0, sizeof(sNC));
sNC.pParse = pParse;
sNC.pSrcList = pTabList;
if( sqlite3ResolveExprNames(&sNC, pWhere) ){
goto delete_from_cleanup;
}
/* Initialize the counter of the number of rows deleted, if
** we are counting rows.
*/
if( db->flags & SQLITE_CountRows ){
memCnt = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
}
#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
/* Special case: A DELETE without a WHERE clause deletes everything.
** It is easier just to erase the whole table. Prior to version 3.6.5,
** this optimization caused the row change count (the value returned by
** API function sqlite3_count_changes) to be set incorrectly. */
if( rcauth==SQLITE_OK
&& pWhere==0
&& !bComplex
&& !IsVirtual(pTab)
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
&& db->xPreUpdateCallback==0
#endif
){
assert( !isView );
sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName);
if( HasRowid(pTab) ){
sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
pTab->zName, P4_STATIC);
}
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
assert( pIdx->pSchema==pTab->pSchema );
sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
}
}else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
{
u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK|WHERE_SEEK_TABLE;
if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
if( HasRowid(pTab) ){
/* For a rowid table, initialize the RowSet to an empty set */
pPk = 0;
nPk = 1;
iRowSet = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
}else{
/* For a WITHOUT ROWID table, create an ephemeral table used to
** hold all primary keys for rows to be deleted. */
pPk = sqlite3PrimaryKeyIndex(pTab);
assert( pPk!=0 );
nPk = pPk->nKeyCol;
iPk = pParse->nMem+1;
pParse->nMem += nPk;
iEphCur = pParse->nTab++;
addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk);
sqlite3VdbeSetP4KeyInfo(pParse, pPk);
}
/* Construct a query to find the rowid or primary key for every row
** to be deleted, based on the WHERE clause. Set variable eOnePass
** to indicate the strategy used to implement this delete:
**
** ONEPASS_OFF: Two-pass approach - use a FIFO for rowids/PK values.
** ONEPASS_SINGLE: One-pass approach - at most one row deleted.
** ONEPASS_MULTI: One-pass approach - any number of rows may be deleted.
*/
pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1);
if( pWInfo==0 ) goto delete_from_cleanup;
eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI );
assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF );
/* Keep track of the number of rows to be deleted */
if( db->flags & SQLITE_CountRows ){
sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
}
/* Extract the rowid or primary key for the current row */
if( pPk ){
for(i=0; i<nPk; i++){
assert( pPk->aiColumn[i]>=0 );
sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur,
pPk->aiColumn[i], iPk+i);
}
iKey = iPk;
}else{
iKey = pParse->nMem + 1;
iKey = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iKey, 0);
if( iKey>pParse->nMem ) pParse->nMem = iKey;
}
if( eOnePass!=ONEPASS_OFF ){
/* For ONEPASS, no need to store the rowid/primary-key. There is only
** one, so just keep it in its register(s) and fall through to the
** delete code. */
nKey = nPk; /* OP_Found will use an unpacked key */
aToOpen = sqlite3DbMallocRawNN(db, nIdx+2);
if( aToOpen==0 ){
sqlite3WhereEnd(pWInfo);
goto delete_from_cleanup;
}
memset(aToOpen, 1, nIdx+1);
aToOpen[nIdx+1] = 0;
if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen);
}else{
if( pPk ){
/* Add the PK key for this row to the temporary table */
iKey = ++pParse->nMem;
nKey = 0; /* Zero tells OP_Found to use a composite key */
sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
sqlite3IndexAffinityStr(pParse->db, pPk), nPk);
sqlite3VdbeAddOp2(v, OP_IdxInsert, iEphCur, iKey);
}else{
/* Add the rowid of the row to be deleted to the RowSet */
nKey = 1; /* OP_Seek always uses a single rowid */
sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey);
}
}
/* If this DELETE cannot use the ONEPASS strategy, this is the
** end of the WHERE loop */
if( eOnePass!=ONEPASS_OFF ){
addrBypass = sqlite3VdbeMakeLabel(v);
}else{
sqlite3WhereEnd(pWInfo);
}
/* Unless this is a view, open cursors for the table we are
** deleting from and all its indices. If this is a view, then the
** only effect this statement has is to fire the INSTEAD OF
** triggers.
*/
if( !isView ){
int iAddrOnce = 0;
if( eOnePass==ONEPASS_MULTI ){
iAddrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
}
testcase( IsVirtual(pTab) );
sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, OPFLAG_FORDELETE,
iTabCur, aToOpen, &iDataCur, &iIdxCur);
assert( pPk || IsVirtual(pTab) || iDataCur==iTabCur );
assert( pPk || IsVirtual(pTab) || iIdxCur==iDataCur+1 );
if( eOnePass==ONEPASS_MULTI ) sqlite3VdbeJumpHere(v, iAddrOnce);
}
/* Set up a loop over the rowids/primary-keys that were found in the
** where-clause loop above.
*/
if( eOnePass!=ONEPASS_OFF ){
assert( nKey==nPk ); /* OP_Found will use an unpacked key */
if( !IsVirtual(pTab) && aToOpen[iDataCur-iTabCur] ){
assert( pPk!=0 || pTab->pSelect!=0 );
sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey);
VdbeCoverage(v);
}
}else if( pPk ){
addrLoop = sqlite3VdbeAddOp1(v, OP_Rewind, iEphCur); VdbeCoverage(v);
sqlite3VdbeAddOp2(v, OP_RowKey, iEphCur, iKey);
assert( nKey==0 ); /* OP_Found will use a composite key */
}else{
addrLoop = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, 0, iKey);
VdbeCoverage(v);
assert( nKey==1 );
}
/* Delete the row */
#ifndef SQLITE_OMIT_VIRTUALTABLE
if( IsVirtual(pTab) ){
const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
sqlite3VtabMakeWritable(pParse, pTab);
sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iKey, pVTab, P4_VTAB);
sqlite3VdbeChangeP5(v, OE_Abort);
assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE );
sqlite3MayAbort(pParse);
if( eOnePass==ONEPASS_SINGLE && sqlite3IsToplevel(pParse) ){
pParse->isMultiWrite = 0;
}
}else
#endif
{
int count = (pParse->nested==0); /* True to count changes */
int iIdxNoSeek = -1;
if( bComplex==0 && aiCurOnePass[1]!=iDataCur ){
iIdxNoSeek = aiCurOnePass[1];
}
sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek);
}
/* End of the loop over all rowids/primary-keys. */
if( eOnePass!=ONEPASS_OFF ){
sqlite3VdbeResolveLabel(v, addrBypass);
sqlite3WhereEnd(pWInfo);
}else if( pPk ){
sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v);
sqlite3VdbeJumpHere(v, addrLoop);
}else{
sqlite3VdbeGoto(v, addrLoop);
sqlite3VdbeJumpHere(v, addrLoop);
}
/* Close the cursors open on the table and its indexes. */
if( !isView && !IsVirtual(pTab) ){
if( !pPk ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur);
for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
sqlite3VdbeAddOp1(v, OP_Close, iIdxCur + i);
}
}
} /* End non-truncate path */
/* Update the sqlite_sequence table by storing the content of the
** maximum rowid counter values recorded while inserting into
** autoincrement tables.
*/
if( pParse->nested==0 && pParse->pTriggerTab==0 ){
sqlite3AutoincrementEnd(pParse);
}
/* Return the number of rows that were deleted. If this routine is
** generating code because of a call to sqlite3NestedParse(), do not
** invoke the callback function.
*/
if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){
sqlite3VdbeAddOp2(v, OP_ResultRow, memCnt, 1);
sqlite3VdbeSetNumCols(v, 1);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
}
delete_from_cleanup:
sqlite3AuthContextPop(&sContext);
sqlite3SrcListDelete(db, pTabList);
sqlite3ExprDelete(db, pWhere);
sqlite3DbFree(db, aToOpen);
return;
}
/*
** Generate code for an UPDATE of a virtual table.
**
** There are two possible strategies - the default and the special
** "onepass" strategy. Onepass is only used if the virtual table
** implementation indicates that pWhere may match at most one row.
**
** The default strategy is to create an ephemeral table that contains
** for each row to be changed:
**
** (A) The original rowid of that row.
** (B) The revised rowid for the row.
** (C) The content of every column in the row.
**
** Then loop through the contents of this ephemeral table executing a
** VUpdate for each row. When finished, drop the ephemeral table.
**
** The "onepass" strategy does not use an ephemeral table. Instead, it
** stores the same values (A, B and C above) in a register array and
** makes a single invocation of VUpdate.
*/
static void updateVirtualTable(
Parse *pParse, /* The parsing context */
SrcList *pSrc, /* The virtual table to be modified */
Table *pTab, /* The virtual table */
ExprList *pChanges, /* The columns to change in the UPDATE statement */
Expr *pRowid, /* Expression used to recompute the rowid */
int *aXRef, /* Mapping from columns of pTab to entries in pChanges */
Expr *pWhere, /* WHERE clause of the UPDATE statement */
int onError /* ON CONFLICT strategy */
){
Vdbe *v = pParse->pVdbe; /* Virtual machine under construction */
int ephemTab; /* Table holding the result of the SELECT */
int i; /* Loop counter */
sqlite3 *db = pParse->db; /* Database connection */
const char *pVTab = (const char*)sqlite3GetVTable(db, pTab);
WhereInfo *pWInfo;
int nArg = 2 + pTab->nCol; /* Number of arguments to VUpdate */
int regArg; /* First register in VUpdate arg array */
int regRec; /* Register in which to assemble record */
int regRowid; /* Register for ephem table rowid */
int iCsr = pSrc->a[0].iCursor; /* Cursor used for virtual table scan */
int aDummy[2]; /* Unused arg for sqlite3WhereOkOnePass() */
int bOnePass; /* True to use onepass strategy */
int addr; /* Address of OP_OpenEphemeral */
/* Allocate nArg registers to martial the arguments to VUpdate. Then
** create and open the ephemeral table in which the records created from
** these arguments will be temporarily stored. */
assert( v );
ephemTab = pParse->nTab++;
addr= sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, nArg);
regArg = pParse->nMem + 1;
pParse->nMem += nArg;
regRec = ++pParse->nMem;
regRowid = ++pParse->nMem;
/* Start scanning the virtual table */
pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0,0,WHERE_ONEPASS_DESIRED,0);
if( pWInfo==0 ) return;
/* Populate the argument registers. */
sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg);
if( pRowid ){
sqlite3ExprCode(pParse, pRowid, regArg+1);
}else{
sqlite3VdbeAddOp2(v, OP_Rowid, iCsr, regArg+1);
}
for(i=0; i<pTab->nCol; i++){
if( aXRef[i]>=0 ){
sqlite3ExprCode(pParse, pChanges->a[aXRef[i]].pExpr, regArg+2+i);
}else{
sqlite3VdbeAddOp3(v, OP_VColumn, iCsr, i, regArg+2+i);
}
}
bOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy);
if( bOnePass ){
/* If using the onepass strategy, no-op out the OP_OpenEphemeral coded
** above. Also, if this is a top-level parse (not a trigger), clear the
** multi-write flag so that the VM does not open a statement journal */
sqlite3VdbeChangeToNoop(v, addr);
if( sqlite3IsToplevel(pParse) ){
pParse->isMultiWrite = 0;
}
}else{
/* Create a record from the argument register contents and insert it into
** the ephemeral table. */
sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, ephemTab, regRec, regRowid);
}
if( bOnePass==0 ){
/* End the virtual table scan */
sqlite3WhereEnd(pWInfo);
/* Begin scannning through the ephemeral table. */
addr = sqlite3VdbeAddOp1(v, OP_Rewind, ephemTab); VdbeCoverage(v);
/* Extract arguments from the current row of the ephemeral table and
** invoke the VUpdate method. */
for(i=0; i<nArg; i++){
sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i, regArg+i);
}
}
sqlite3VtabMakeWritable(pParse, pTab);
sqlite3VdbeAddOp4(v, OP_VUpdate, 0, nArg, regArg, pVTab, P4_VTAB);
sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
sqlite3MayAbort(pParse);
/* End of the ephemeral table scan. Or, if using the onepass strategy,
** jump to here if the scan visited zero rows. */
if( bOnePass==0 ){
sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); VdbeCoverage(v);
sqlite3VdbeJumpHere(v, addr);
sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);
}else{
sqlite3WhereEnd(pWInfo);
}
}
