/* ** Process a DELETE FROM statement. */ void sqliteDeleteFrom( 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 */ const char *zDb; /* Name of database holding pTab */ int end, addr; /* A couple addresses of generated code */ int i; /* Loop counter */ WhereInfo *pWInfo; /* Information about the WHERE clause */ Index *pIdx; /* For looping over indices of the table */ int iCur; /* VDBE Cursor number for pTab */ sqlite *db; /* Main database structure */ int isView; /* True if attempting to delete from a view */ AuthContext sContext; /* Authorization context */ int row_triggers_exist = 0; /* True if any triggers exist */ int before_triggers; /* True if there are BEFORE triggers */ int after_triggers; /* True if there are AFTER triggers */ int oldIdx = -1; /* Cursor for the OLD table of AFTER triggers */ sContext.pParse = 0; if( pParse->nErr || sqlite_malloc_failed ){ pTabList = 0; goto delete_from_cleanup; } db = pParse->db; 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 = sqliteSrcListLookup(pParse, pTabList); if( pTab==0 ) goto delete_from_cleanup; before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_DELETE, TK_BEFORE, TK_ROW, 0); after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_DELETE, TK_AFTER, TK_ROW, 0); row_triggers_exist = before_triggers || after_triggers; isView = pTab->pSelect!=0; if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){ goto delete_from_cleanup; } assert( pTab->iDb<db->nDb ); zDb = db->aDb[pTab->iDb].zName; if( sqliteAuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){ goto delete_from_cleanup; } /* If pTab is really a view, make sure it has been initialized. */ if( isView && sqliteViewGetColumnNames(pParse, pTab) ){ goto delete_from_cleanup; } /* Allocate a cursor used to store the old.* data for a trigger. */ if( row_triggers_exist ){ oldIdx = pParse->nTab++; } /* Resolve the column names in all the expressions. */ assert( pTabList->nSrc==1 ); iCur = pTabList->a[0].iCursor = pParse->nTab++; if( pWhere ){ if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){ goto delete_from_cleanup; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto delete_from_cleanup; } } /* Start the view context */ if( isView ){ sqliteAuthContextPush(pParse, &sContext, pTab->zName); } /* Begin generating code. */ v = sqliteGetVdbe(pParse); if( v==0 ){ goto delete_from_cleanup; } sqliteBeginWriteOperation(pParse, row_triggers_exist, pTab->iDb); /* If we are trying to delete from a view, construct that view into ** a temporary table. */ if( isView ){ Select *pView = sqliteSelectDup(pTab->pSelect); sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0); sqliteSelectDelete(pView); } /* Initialize the counter of the number of rows deleted, if ** we are counting rows. */ if( db->flags & SQLITE_CountRows ){ sqliteVdbeAddOp(v, OP_Integer, 0, 0); } /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Note, however, that ** this means that the row change count will be incorrect. */ if( pWhere==0 && !row_triggers_exist ){ if( db->flags & SQLITE_CountRows ){ /* If counting rows deleted, just count the total number of ** entries in the table. */ int endOfLoop = sqliteVdbeMakeLabel(v); int addr; if( !isView ){ sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0); sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum); } sqliteVdbeAddOp(v, OP_Rewind, iCur, sqliteVdbeCurrentAddr(v)+2); addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0); sqliteVdbeAddOp(v, OP_Next, iCur, addr); sqliteVdbeResolveLabel(v, endOfLoop); sqliteVdbeAddOp(v, OP_Close, iCur, 0); } if( !isView ){ sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb); } } } /* The usual case: There is a WHERE clause so we have to scan through ** the table and pick which records to delete. */ else{ /* Begin the database scan */ pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0); if( pWInfo==0 ) goto delete_from_cleanup; /* Remember the key of every item to be deleted. */ sqliteVdbeAddOp(v, OP_ListWrite, 0, 0); if( db->flags & SQLITE_CountRows ){ sqliteVdbeAddOp(v, OP_AddImm, 1, 0); } /* End the database scan loop. */ sqliteWhereEnd(pWInfo); /* Open the pseudo-table used to store OLD if there are triggers. */ if( row_triggers_exist ){ sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0); } /* Delete every item whose key was written to the list during the ** database scan. We have to delete items after the scan is complete ** because deleting an item can change the scan order. */ sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); end = sqliteVdbeMakeLabel(v); /* This is the beginning of the delete loop when there are ** row triggers. */ if( row_triggers_exist ){ addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); sqliteVdbeAddOp(v, OP_Dup, 0, 0); if( !isView ){ sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0); sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum); } sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0); sqliteVdbeAddOp(v, OP_Recno, iCur, 0); sqliteVdbeAddOp(v, OP_RowData, iCur, 0); sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0); if( !isView ){ sqliteVdbeAddOp(v, OP_Close, iCur, 0); } sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default, addr); } if( !isView ){ /* Open cursors for the table we are deleting from and all its ** indices. If there are row triggers, this happens inside the ** OP_ListRead loop because the cursor have to all be closed ** before the trigger fires. If there are no row triggers, the ** cursors are opened only once on the outside the loop. */ pParse->nTab = iCur + 1; sqliteOpenTableAndIndices(pParse, pTab, iCur); /* This is the beginning of the delete loop when there are no ** row triggers */ if( !row_triggers_exist ){ addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end); } /* Delete the row */ sqliteGenerateRowDelete(db, v, pTab, iCur, pParse->trigStack==0); } /* If there are row triggers, close all cursors then invoke ** the AFTER triggers */ if( row_triggers_exist ){ if( !isView ){ for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum); } sqliteVdbeAddOp(v, OP_Close, iCur, 0); } sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default, addr); } /* End of the delete loop */ sqliteVdbeAddOp(v, OP_Goto, 0, addr); sqliteVdbeResolveLabel(v, end); sqliteVdbeAddOp(v, OP_ListReset, 0, 0); /* Close the cursors after the loop if there are no row triggers */ if( !row_triggers_exist ){ for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum); } sqliteVdbeAddOp(v, OP_Close, iCur, 0); pParse->nTab = iCur; } } sqliteVdbeAddOp(v, OP_SetCounts, 0, 0); sqliteEndWriteOperation(pParse); /* ** Return the number of rows that were deleted. */ if( db->flags & SQLITE_CountRows ){ sqliteVdbeAddOp(v, OP_ColumnName, 0, 1); sqliteVdbeChangeP3(v, -1, "rows deleted", P3_STATIC); sqliteVdbeAddOp(v, OP_Callback, 1, 0); } delete_from_cleanup: sqliteAuthContextPop(&sContext); sqliteSrcListDelete(pTabList); sqliteExprDelete(pWhere); return; }
/* ** Generate code to do a constraint check prior to an INSERT or an UPDATE. ** ** When this routine is called, the stack contains (from bottom to top) ** the following values: ** ** 1. The recno of the row to be updated before the update. This ** value is omitted unless we are doing an UPDATE that involves a ** change to the record number. ** ** 2. The recno of the row after the update. ** ** 3. The data in the first column of the entry after the update. ** ** i. Data from middle columns... ** ** N. The data in the last column of the entry after the update. ** ** The old recno shown as entry (1) above is omitted unless both isUpdate ** and recnoChng are 1. isUpdate is true for UPDATEs and false for ** INSERTs and recnoChng is true if the record number is being changed. ** ** The code generated by this routine pushes additional entries onto ** the stack which are the keys for new index entries for the new record. ** The order of index keys is the same as the order of the indices on ** the pTable->pIndex list. A key is only created for index i if ** aIdxUsed!=0 and aIdxUsed[i]!=0. ** ** This routine also generates code to check constraints. NOT NULL, ** CHECK, and UNIQUE constraints are all checked. If a constraint fails, ** then the appropriate action is performed. There are five possible ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE. ** ** Constraint type Action What Happens ** --------------- ---------- ---------------------------------------- ** any ROLLBACK The current transaction is rolled back and ** sqlite_exec() returns immediately with a ** return code of SQLITE_CONSTRAINT. ** ** any ABORT Back out changes from the current command ** only (do not do a complete rollback) then ** cause sqlite_exec() to return immediately ** with SQLITE_CONSTRAINT. ** ** any FAIL Sqlite_exec() returns immediately with a ** return code of SQLITE_CONSTRAINT. The ** transaction is not rolled back and any ** prior changes are retained. ** ** any IGNORE The record number and data is popped from ** the stack and there is an immediate jump ** to label ignoreDest. ** ** NOT NULL REPLACE The NULL value is replace by the default ** value for that column. If the default value ** is NULL, the action is the same as ABORT. ** ** UNIQUE REPLACE The other row that conflicts with the row ** being inserted is removed. ** ** CHECK REPLACE Illegal. The results in an exception. ** ** Which action to take is determined by the overrideError parameter. ** Or if overrideError==OE_Default, then the pParse->onError parameter ** is used. Or if pParse->onError==OE_Default then the onError value ** for the constraint is used. ** ** The calling routine must open a read/write cursor for pTab with ** cursor number "base". All indices of pTab must also have open ** read/write cursors with cursor number base+i for the i-th cursor. ** Except, if there is no possibility of a REPLACE action then ** cursors do not need to be open for indices where aIdxUsed[i]==0. ** ** If the isUpdate flag is true, it means that the "base" cursor is ** initially pointing to an entry that is being updated. The isUpdate ** flag causes extra code to be generated so that the "base" cursor ** is still pointing at the same entry after the routine returns. ** Without the isUpdate flag, the "base" cursor might be moved. */ void sqliteGenerateConstraintChecks( Parse *pParse, /* The parser context */ Table *pTab, /* the table into which we are inserting */ int base, /* Index of a read/write cursor pointing at pTab */ char *aIdxUsed, /* Which indices are used. NULL means all are used */ int recnoChng, /* True if the record number will change */ int isUpdate, /* True for UPDATE, False for INSERT */ int overrideError, /* Override onError to this if not OE_Default */ int ignoreDest /* Jump to this label on an OE_Ignore resolution */ ){ int i; Vdbe *v; int nCol; int onError; int addr; int extra; int iCur; Index *pIdx; int seenReplace = 0; int jumpInst1, jumpInst2; int contAddr; int hasTwoRecnos = (isUpdate && recnoChng); v = sqliteGetVdbe(pParse); assert( v!=0 ); assert( pTab->pSelect==0 ); /* This table is not a VIEW */ nCol = pTab->nCol; /* Test all NOT NULL constraints. */ for(i=0; i<nCol; i++){ if( i==pTab->iPKey ){ continue; } onError = pTab->aCol[i].notNull; if( onError==OE_None ) continue; if( overrideError!=OE_Default ){ onError = overrideError; }else if( pParse->db->onError!=OE_Default ){ onError = pParse->db->onError; }else if( onError==OE_Default ){ onError = OE_Abort; } if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){ onError = OE_Abort; } sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1); addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0); switch( onError ){ case OE_Rollback: case OE_Abort: case OE_Fail: { char *zMsg = 0; sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError); sqliteSetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName, " may not be NULL", (char*)0); sqliteVdbeChangeP3(v, -1, zMsg, P3_DYNAMIC); break; } case OE_Ignore: { sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0); sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest); break; } case OE_Replace: { sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC); sqliteVdbeAddOp(v, OP_Push, nCol-i, 0); break; } default: assert(0); } sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v)); } /* Test all CHECK constraints */ /**** TBD ****/ /* If we have an INTEGER PRIMARY KEY, make sure the primary key ** of the new record does not previously exist. Except, if this ** is an UPDATE and the primary key is not changing, that is OK. */ if( recnoChng ){ onError = pTab->keyConf; if( overrideError!=OE_Default ){ onError = overrideError; }else if( pParse->db->onError!=OE_Default ){ onError = pParse->db->onError; }else if( onError==OE_Default ){ onError = OE_Abort; } if( isUpdate ){ sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1); sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1); jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0); } sqliteVdbeAddOp(v, OP_Dup, nCol, 1); jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0); switch( onError ){ default: { onError = OE_Abort; /* Fall thru into the next case */ } case OE_Rollback: case OE_Abort: case OE_Fail: { sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, "PRIMARY KEY must be unique", P3_STATIC); break; } case OE_Replace: { sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0); if( isUpdate ){ sqliteVdbeAddOp(v, OP_Dup, nCol+hasTwoRecnos, 1); sqliteVdbeAddOp(v, OP_MoveTo, base, 0); } seenReplace = 1; break; } case OE_Ignore: { assert( seenReplace==0 ); sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0); sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest); break; } } contAddr = sqliteVdbeCurrentAddr(v); sqliteVdbeChangeP2(v, jumpInst2, contAddr); if( isUpdate ){ sqliteVdbeChangeP2(v, jumpInst1, contAddr); sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1); sqliteVdbeAddOp(v, OP_MoveTo, base, 0); } } /* Test all UNIQUE constraints by creating entries for each UNIQUE ** index and making sure that duplicate entries do not already exist. ** Add the new records to the indices as we go. */ extra = -1; for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){ if( aIdxUsed && aIdxUsed[iCur]==0 ) continue; /* Skip unused indices */ extra++; /* Create a key for accessing the index entry */ sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1); for(i=0; i<pIdx->nColumn; i++){ int idx = pIdx->aiColumn[i]; if( idx==pTab->iPKey ){ sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1); }else{ sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1); } } jumpInst1 = sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0); if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx); /* Find out what action to take in case there is an indexing conflict */ onError = pIdx->onError; if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */ if( overrideError!=OE_Default ){ onError = overrideError; }else if( pParse->db->onError!=OE_Default ){ onError = pParse->db->onError; }else if( onError==OE_Default ){ onError = OE_Abort; } if( seenReplace ){ if( onError==OE_Ignore ) onError = OE_Replace; else if( onError==OE_Fail ) onError = OE_Abort; } /* Check to see if the new index entry will be unique */ sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1); jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0); /* Generate code that executes if the new index entry is not unique */ switch( onError ){ case OE_Rollback: case OE_Abort: case OE_Fail: { int j, n1, n2; char zErrMsg[200]; strcpy(zErrMsg, pIdx->nColumn>1 ? "columns " : "column "); n1 = strlen(zErrMsg); for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){ char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName; n2 = strlen(zCol); if( j>0 ){ strcpy(&zErrMsg[n1], ", "); n1 += 2; } if( n1+n2>sizeof(zErrMsg)-30 ){ strcpy(&zErrMsg[n1], "..."); n1 += 3; break; }else{ strcpy(&zErrMsg[n1], zCol); n1 += n2; } } strcpy(&zErrMsg[n1], pIdx->nColumn>1 ? " are not unique" : " is not unique"); sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0); break; } case OE_Ignore: { assert( seenReplace==0 ); sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0); sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest); break; } case OE_Replace: { sqliteGenerateRowDelete(pParse->db, v, pTab, base, 0); if( isUpdate ){ sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1); sqliteVdbeAddOp(v, OP_MoveTo, base, 0); } seenReplace = 1; break; } default: assert(0); } contAddr = sqliteVdbeCurrentAddr(v); #if NULL_DISTINCT_FOR_UNIQUE sqliteVdbeChangeP2(v, jumpInst1, contAddr); #endif sqliteVdbeChangeP2(v, jumpInst2, contAddr); } }