/* ** Construct a trigger step that implements an UPDATE statement and return ** a pointer to that trigger step. The parser calls this routine when it ** sees an UPDATE statement inside the body of a CREATE TRIGGER. */ TriggerStep *sqlite3TriggerUpdateStep( sqlite3 *db, /* The database connection */ Token *pTableName, /* Name of the table to be updated */ ExprList *pEList, /* The SET clause: list of column and new values */ Expr *pWhere, /* The WHERE clause */ u8 orconf /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */ ){ TriggerStep *pTriggerStep; pTriggerStep = triggerStepAllocate(db, TK_UPDATE, pTableName); if (pTriggerStep){ pTriggerStep->pExprList = sqlite3ExprListDup(db, pEList, EXPRDUP_REDUCE); pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); pTriggerStep->orconf = orconf; } sqlite3ExprListDelete(db, pEList); sqlite3ExprDelete(db, pWhere); return pTriggerStep; }
/* ** Construct a trigger step that implements an UPDATE statement and return ** a pointer to that trigger step. The parser calls this routine when it ** sees an UPDATE statement inside the body of a CREATE TRIGGER. */ TriggerStep *sqlite3TriggerUpdateStep( sqlite3 *db, /* The database connection */ Token *pTableName, /* Name of the table to be updated */ ExprList *pEList, /* The SET clause: list of column and new values */ Expr *pWhere, /* The WHERE clause */ int orconf /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */ ){ TriggerStep *pTriggerStep = (TriggerStep*)sqlite3DbMallocZero(db, sizeof(TriggerStep)); if( pTriggerStep==0 ){ sqlite3ExprListDelete(pEList); sqlite3ExprDelete(pWhere); return 0; } pTriggerStep->op = TK_UPDATE; pTriggerStep->target = *pTableName; pTriggerStep->pExprList = pEList; pTriggerStep->pWhere = pWhere; pTriggerStep->orconf = orconf; sqlitePersistTriggerStep(db, pTriggerStep); return pTriggerStep; }
/* ** Construct a trigger step that implements a DELETE statement and return ** a pointer to that trigger step. The parser calls this routine when it ** sees a DELETE statement inside the body of a CREATE TRIGGER. */ TriggerStep *sqlite3TriggerDeleteStep( Parse *pParse, /* Parser */ Token *pTableName, /* The table from which rows are deleted */ Expr *pWhere, /* The WHERE clause */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ sqlite3 *db = pParse->db; TriggerStep *pTriggerStep; pTriggerStep = triggerStepAllocate(pParse, TK_DELETE, pTableName,zStart,zEnd); if( pTriggerStep ){ if( IN_RENAME_OBJECT ){ pTriggerStep->pWhere = pWhere; pWhere = 0; }else{ pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); } pTriggerStep->orconf = OE_Default; } sqlite3ExprDelete(db, pWhere); return pTriggerStep; }
/* ** This is called by the parser when it sees a CREATE TRIGGER statement ** up to the point of the BEGIN before the trigger actions. A Trigger ** structure is generated based on the information available and stored ** in pParse->pNewTrigger. After the trigger actions have been parsed, the ** sqlite3FinishTrigger() function is called to complete the trigger ** construction process. */ SQLITE_PRIVATE void sqlite3BeginTrigger( Parse *pParse, /* The parse context of the CREATE TRIGGER statement */ Token *pName1, /* The name of the trigger */ Token *pName2, /* The name of the trigger */ int tr_tm, /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD */ int op, /* One of TK_INSERT, TK_UPDATE, TK_DELETE */ IdList *pColumns, /* column list if this is an UPDATE OF trigger */ SrcList *pTableName,/* The name of the table/view the trigger applies to */ Expr *pWhen, /* WHEN clause */ int isTemp, /* True if the TEMPORARY keyword is present */ int noErr /* Suppress errors if the trigger already exists */ ){ Trigger *pTrigger = 0; /* The new trigger */ Table *pTab; /* Table that the trigger fires off of */ char *zName = 0; /* Name of the trigger */ sqlite3 *db = pParse->db; /* The database connection */ int iDb; /* The database to store the trigger in */ Token *pName; /* The unqualified db name */ DbFixer sFix; /* State vector for the DB fixer */ int iTabDb; /* Index of the database holding pTab */ assert( pName1!=0 ); /* pName1->z might be NULL, but not pName1 itself */ assert( pName2!=0 ); assert( op==TK_INSERT || op==TK_UPDATE || op==TK_DELETE ); assert( op>0 && op<0xff ); if( isTemp ){ /* If TEMP was specified, then the trigger name may not be qualified. */ if( pName2->n>0 ){ sqlite3ErrorMsg(pParse, "temporary trigger may not have qualified name"); goto trigger_cleanup; } iDb = 1; pName = pName1; }else{ /* Figure out the db that the trigger will be created in */ iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pName); if( iDb<0 ){ goto trigger_cleanup; } } if( !pTableName || db->mallocFailed ){ goto trigger_cleanup; } /* A long-standing parser bug is that this syntax was allowed: ** ** CREATE TRIGGER attached.demo AFTER INSERT ON attached.tab .... ** ^^^^^^^^ ** ** To maintain backwards compatibility, ignore the database ** name on pTableName if we are reparsing our of SQLITE_MASTER. */ if( db->init.busy && iDb!=1 ){ sqlite3DbFree(db, pTableName->a[0].zDatabase); pTableName->a[0].zDatabase = 0; } /* If the trigger name was unqualified, and the table is a temp table, ** then set iDb to 1 to create the trigger in the temporary database. ** If sqlite3SrcListLookup() returns 0, indicating the table does not ** exist, the error is caught by the block below. */ pTab = sqlite3SrcListLookup(pParse, pTableName); if( db->init.busy==0 && pName2->n==0 && pTab && pTab->pSchema==db->aDb[1].pSchema ){ iDb = 1; } /* Ensure the table name matches database name and that the table exists */ if( db->mallocFailed ) goto trigger_cleanup; assert( pTableName->nSrc==1 ); sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName); if( sqlite3FixSrcList(&sFix, pTableName) ){ goto trigger_cleanup; } pTab = sqlite3SrcListLookup(pParse, pTableName); if( !pTab ){ /* The table does not exist. */ if( db->init.iDb==1 ){ /* Ticket #3810. ** Normally, whenever a table is dropped, all associated triggers are ** dropped too. But if a TEMP trigger is created on a non-TEMP table ** and the table is dropped by a different database connection, the ** trigger is not visible to the database connection that does the ** drop so the trigger cannot be dropped. This results in an ** "orphaned trigger" - a trigger whose associated table is missing. */ db->init.orphanTrigger = 1; } goto trigger_cleanup; } if( IsVirtual(pTab) ){ sqlite3ErrorMsg(pParse, "cannot create triggers on virtual tables"); goto trigger_cleanup; } /* Check that the trigger name is not reserved and that no trigger of the ** specified name exists */ zName = sqlite3NameFromToken(db, pName); if( !zName || SQLITE_OK!=sqlite3CheckObjectName(pParse, zName) ){ goto trigger_cleanup; } assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); if( sqlite3HashFind(&(db->aDb[iDb].pSchema->trigHash), zName, sqlite3Strlen30(zName)) ){ if( !noErr ){ sqlite3ErrorMsg(pParse, "trigger %T already exists", pName); }else{ assert( !db->init.busy ); sqlite3CodeVerifySchema(pParse, iDb); } goto trigger_cleanup; } /* Do not create a trigger on a system table */ if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){ sqlite3ErrorMsg(pParse, "cannot create trigger on system table"); pParse->nErr++; goto trigger_cleanup; } /* INSTEAD of triggers are only for views and views only support INSTEAD ** of triggers. */ if( pTab->pSelect && tr_tm!=TK_INSTEAD ){ sqlite3ErrorMsg(pParse, "cannot create %s trigger on view: %S", (tr_tm == TK_BEFORE)?"BEFORE":"AFTER", pTableName, 0); goto trigger_cleanup; } if( !pTab->pSelect && tr_tm==TK_INSTEAD ){ sqlite3ErrorMsg(pParse, "cannot create INSTEAD OF" " trigger on table: %S", pTableName, 0); goto trigger_cleanup; } iTabDb = sqlite3SchemaToIndex(db, pTab->pSchema); #ifndef SQLITE_OMIT_AUTHORIZATION { int code = SQLITE_CREATE_TRIGGER; const char *zDb = db->aDb[iTabDb].zName; const char *zDbTrig = isTemp ? db->aDb[1].zName : zDb; if( iTabDb==1 || isTemp ) code = SQLITE_CREATE_TEMP_TRIGGER; if( sqlite3AuthCheck(pParse, code, zName, pTab->zName, zDbTrig) ){ goto trigger_cleanup; } if( sqlite3AuthCheck(pParse, SQLITE_INSERT, SCHEMA_TABLE(iTabDb),0,zDb)){ goto trigger_cleanup; } } #endif /* INSTEAD OF triggers can only appear on views and BEFORE triggers ** cannot appear on views. So we might as well translate every ** INSTEAD OF trigger into a BEFORE trigger. It simplifies code ** elsewhere. */ if (tr_tm == TK_INSTEAD){ tr_tm = TK_BEFORE; } /* Build the Trigger object */ pTrigger = (Trigger*)sqlite3DbMallocZero(db, sizeof(Trigger)); if( pTrigger==0 ) goto trigger_cleanup; pTrigger->zName = zName; zName = 0; pTrigger->table = sqlite3DbStrDup(db, pTableName->a[0].zName); pTrigger->pSchema = db->aDb[iDb].pSchema; pTrigger->pTabSchema = pTab->pSchema; pTrigger->op = (u8)op; pTrigger->tr_tm = tr_tm==TK_BEFORE ? TRIGGER_BEFORE : TRIGGER_AFTER; pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE); pTrigger->pColumns = sqlite3IdListDup(db, pColumns); assert( pParse->pNewTrigger==0 ); pParse->pNewTrigger = pTrigger; trigger_cleanup: sqlite3DbFree(db, zName); sqlite3SrcListDelete(db, pTableName); sqlite3IdListDelete(db, pColumns); sqlite3ExprDelete(db, pWhen); if( !pParse->pNewTrigger ){ sqlite3DeleteTrigger(db, pTrigger); }else{ assert( pParse->pNewTrigger==pTrigger ); } }
/* ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up ** that name in the set of source tables in pSrcList and make the pExpr ** expression node refer back to that source column. The following changes ** are made to pExpr: ** ** pExpr->iDb Set the index in db->aDb[] of the database X ** (even if X is implied). ** pExpr->iTable Set to the cursor number for the table obtained ** from pSrcList. ** pExpr->pTab Points to the Table structure of X.Y (even if ** X and/or Y are implied.) ** pExpr->iColumn Set to the column number within the table. ** pExpr->op Set to TK_COLUMN. ** pExpr->pLeft Any expression this points to is deleted ** pExpr->pRight Any expression this points to is deleted. ** ** The zDb variable is the name of the database (the "X"). This value may be ** NULL meaning that name is of the form Y.Z or Z. Any available database ** can be used. The zTable variable is the name of the table (the "Y"). This ** value can be NULL if zDb is also NULL. If zTable is NULL it ** means that the form of the name is Z and that columns from any table ** can be used. ** ** If the name cannot be resolved unambiguously, leave an error message ** in pParse and return WRC_Abort. Return WRC_Prune on success. */ static int lookupName( Parse *pParse, /* The parsing context */ const char *zDb, /* Name of the database containing table, or NULL */ const char *zTab, /* Name of table containing column, or NULL */ const char *zCol, /* Name of the column. */ NameContext *pNC, /* The name context used to resolve the name */ Expr *pExpr /* Make this EXPR node point to the selected column */ ){ int i, j; /* Loop counters */ int cnt = 0; /* Number of matching column names */ int cntTab = 0; /* Number of matching table names */ int nSubquery = 0; /* How many levels of subquery */ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ int isTrigger = 0; /* True if resolved to a trigger column */ Table *pTab = 0; /* Table hold the row */ Column *pCol; /* A column of pTab */ assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) ); /* Initialize the node to no-match */ pExpr->iTable = -1; pExpr->pTab = 0; ExprSetVVAProperty(pExpr, EP_NoReduce); /* Translate the schema name in zDb into a pointer to the corresponding ** schema. If not found, pSchema will remain NULL and nothing will match ** resulting in an appropriate error message toward the end of this routine */ if( zDb ){ testcase( pNC->ncFlags & NC_PartIdx ); testcase( pNC->ncFlags & NC_IsCheck ); if( (pNC->ncFlags & (NC_PartIdx|NC_IsCheck))!=0 ){ /* Silently ignore database qualifiers inside CHECK constraints and partial ** indices. Do not raise errors because that might break legacy and ** because it does not hurt anything to just ignore the database name. */ zDb = 0; }else{ for(i=0; i<db->nDb; i++){ assert( db->aDb[i].zName ); if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){ pSchema = db->aDb[i].pSchema; break; } } } } /* Start at the inner-most context and move outward until a match is found */ while( pNC && cnt==0 ){ ExprList *pEList; SrcList *pSrcList = pNC->pSrcList; if( pSrcList ){ for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){ pTab = pItem->pTab; assert( pTab!=0 && pTab->zName!=0 ); assert( pTab->nCol>0 ); if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){ int hit = 0; pEList = pItem->pSelect->pEList; for(j=0; j<pEList->nExpr; j++){ if( sqlite3MatchSpanName(pEList->a[j].zSpan, zCol, zTab, zDb) ){ cnt++; cntTab = 2; pMatch = pItem; pExpr->iColumn = j; hit = 1; } } if( hit || zTab==0 ) continue; } if( zDb && pTab->pSchema!=pSchema ){ continue; } if( zTab ){ const char *zTabName = pItem->zAlias ? pItem->zAlias : pTab->zName; assert( zTabName!=0 ); if( sqlite3StrICmp(zTabName, zTab)!=0 ){ continue; } } if( 0==(cntTab++) ){ pMatch = pItem; } for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){ if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ /* If there has been exactly one prior match and this match ** is for the right-hand table of a NATURAL JOIN or is in a ** USING clause, then skip this match. */ if( cnt==1 ){ if( pItem->jointype & JT_NATURAL ) continue; if( nameInUsingClause(pItem->pUsing, zCol) ) continue; } cnt++; pMatch = pItem; /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */ pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j; break; } } } if( pMatch ){ pExpr->iTable = pMatch->iCursor; pExpr->pTab = pMatch->pTab; pSchema = pExpr->pTab->pSchema; } } /* if( pSrcList ) */ #ifndef SQLITE_OMIT_TRIGGER /* If we have not already resolved the name, then maybe ** it is a new.* or old.* trigger argument reference */ if( zDb==0 && zTab!=0 && cntTab==0 && pParse->pTriggerTab!=0 ){ int op = pParse->eTriggerOp; assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT ); if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){ pExpr->iTable = 1; pTab = pParse->pTriggerTab; }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){ pExpr->iTable = 0; pTab = pParse->pTriggerTab; }else{ pTab = 0; } if( pTab ){ int iCol; pSchema = pTab->pSchema; cntTab++; for(iCol=0, pCol=pTab->aCol; iCol<pTab->nCol; iCol++, pCol++){ if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ if( iCol==pTab->iPKey ){ iCol = -1; } break; } } if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && HasRowid(pTab) ){ /* IMP: R-24309-18625 */ /* IMP: R-44911-55124 */ iCol = -1; } if( iCol<pTab->nCol ){ cnt++; if( iCol<0 ){ pExpr->affinity = SQLITE_AFF_INTEGER; }else if( pExpr->iTable==0 ){ testcase( iCol==31 ); testcase( iCol==32 ); pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); }else{ testcase( iCol==31 ); testcase( iCol==32 ); pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); } pExpr->iColumn = (i16)iCol; pExpr->pTab = pTab; isTrigger = 1; } } } #endif /* !defined(SQLITE_OMIT_TRIGGER) */ /* ** Perhaps the name is a reference to the ROWID */ if( cnt==0 && cntTab==1 && pMatch && sqlite3IsRowid(zCol) && HasRowid(pMatch->pTab) ){ cnt = 1; pExpr->iColumn = -1; /* IMP: R-44911-55124 */ pExpr->affinity = SQLITE_AFF_INTEGER; } /* ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z ** might refer to an result-set alias. This happens, for example, when ** we are resolving names in the WHERE clause of the following command: ** ** SELECT a+b AS x FROM table WHERE x<10; ** ** In cases like this, replace pExpr with a copy of the expression that ** forms the result set entry ("a+b" in the example) and return immediately. ** Note that the expression in the result set should have already been ** resolved by the time the WHERE clause is resolved. ** ** The ability to use an output result-set column in the WHERE, GROUP BY, ** or HAVING clauses, or as part of a larger expression in the ORDRE BY ** clause is not standard SQL. This is a (goofy) SQLite extension, that ** is supported for backwards compatibility only. TO DO: Issue a warning ** on sqlite3_log() whenever the capability is used. */ if( (pEList = pNC->pEList)!=0 && zTab==0 && cnt==0 ){ for(j=0; j<pEList->nExpr; j++){ char *zAs = pEList->a[j].zName; if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){ Expr *pOrig; assert( pExpr->pLeft==0 && pExpr->pRight==0 ); assert( pExpr->x.pList==0 ); assert( pExpr->x.pSelect==0 ); pOrig = pEList->a[j].pExpr; if( (pNC->ncFlags&NC_AllowAgg)==0 && ExprHasProperty(pOrig, EP_Agg) ){ sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs); return WRC_Abort; } resolveAlias(pParse, pEList, j, pExpr, "", nSubquery); cnt = 1; pMatch = 0; assert( zTab==0 && zDb==0 ); goto lookupname_end; } } } /* Advance to the next name context. The loop will exit when either ** we have a match (cnt>0) or when we run out of name contexts. */ if( cnt==0 ){ pNC = pNC->pNext; nSubquery++; } } /* ** If X and Y are NULL (in other words if only the column name Z is ** supplied) and the value of Z is enclosed in double-quotes, then ** Z is a string literal if it doesn't match any column names. In that ** case, we need to return right away and not make any changes to ** pExpr. ** ** Because no reference was made to outer contexts, the pNC->nRef ** fields are not changed in any context. */ if( cnt==0 && zTab==0 && ExprHasProperty(pExpr,EP_DblQuoted) ){ pExpr->op = TK_STRING; pExpr->pTab = 0; return WRC_Prune; } /* ** cnt==0 means there was not match. cnt>1 means there were two or ** more matches. Either way, we have an error. */ if( cnt!=1 ){ const char *zErr; zErr = cnt==0 ? "no such column" : "ambiguous column name"; if( zDb ){ sqlite3ErrorMsg(pParse, "%s: %s.%s.%s", zErr, zDb, zTab, zCol); }else if( zTab ){ sqlite3ErrorMsg(pParse, "%s: %s.%s", zErr, zTab, zCol); }else{ sqlite3ErrorMsg(pParse, "%s: %s", zErr, zCol); } pParse->checkSchema = 1; pTopNC->nErr++; } /* If a column from a table in pSrcList is referenced, then record ** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes ** bit 0 to be set. Column 1 sets bit 1. And so forth. If the ** column number is greater than the number of bits in the bitmask ** then set the high-order bit of the bitmask. */ if( pExpr->iColumn>=0 && pMatch!=0 ){ int n = pExpr->iColumn; testcase( n==BMS-1 ); if( n>=BMS ){ n = BMS-1; } assert( pMatch->iCursor==pExpr->iTable ); pMatch->colUsed |= ((Bitmask)1)<<n; } /* Clean up and return */ sqlite3ExprDelete(db, pExpr->pLeft); pExpr->pLeft = 0; sqlite3ExprDelete(db, pExpr->pRight); pExpr->pRight = 0; pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN); lookupname_end: if( cnt==1 ){ assert( pNC!=0 ); if( pExpr->op!=TK_AS ){ sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList); } /* Increment the nRef value on all name contexts from TopNC up to ** the point where the name matched. */ for(;;){ assert( pTopNC!=0 ); pTopNC->nRef++; if( pTopNC==pNC ) break; pTopNC = pTopNC->pNext; } return WRC_Prune; } else { return WRC_Abort; } }
/* ** Generate an expression tree to implement the WHERE, ORDER BY, ** and LIMIT/OFFSET portion of DELETE and UPDATE statements. ** ** DELETE FROM table_wxyz WHERE a<5 ORDER BY a LIMIT 1; ** \__________________________/ ** pLimitWhere (pInClause) */ Expr *sqlite3LimitWhere( Parse *pParse, /* The parser context */ SrcList *pSrc, /* the FROM clause -- which tables to scan */ Expr *pWhere, /* The WHERE clause. May be null */ ExprList *pOrderBy, /* The ORDER BY clause. May be null */ Expr *pLimit, /* The LIMIT clause. May be null */ Expr *pOffset, /* The OFFSET clause. May be null */ char *zStmtType /* Either DELETE or UPDATE. For err msgs. */ ){ Expr *pWhereRowid = NULL; /* WHERE rowid .. */ Expr *pInClause = NULL; /* WHERE rowid IN ( select ) */ Expr *pSelectRowid = NULL; /* SELECT rowid ... */ ExprList *pEList = NULL; /* Expression list contaning only pSelectRowid */ SrcList *pSelectSrc = NULL; /* SELECT rowid FROM x ... (dup of pSrc) */ Select *pSelect = NULL; /* Complete SELECT tree */ /* Check that there isn't an ORDER BY without a LIMIT clause. */ if( pOrderBy && (pLimit == 0) ) { sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType); goto limit_where_cleanup; } /* We only need to generate a select expression if there ** is a limit/offset term to enforce. */ if( pLimit == 0 ) { /* if pLimit is null, pOffset will always be null as well. */ assert( pOffset == 0 ); return pWhere; } /* Generate a select expression tree to enforce the limit/offset ** term for the DELETE or UPDATE statement. For example: ** DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** becomes: ** DELETE FROM table_a WHERE rowid IN ( ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** ); */ pSelectRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); if( pSelectRowid == 0 ) goto limit_where_cleanup; pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid); if( pEList == 0 ) goto limit_where_cleanup; /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0); if( pSelectSrc == 0 ) { sqlite3ExprListDelete(pParse->db, pEList); goto limit_where_cleanup; } /* generate the SELECT expression tree. */ pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0, pOrderBy,0,pLimit,pOffset); if( pSelect == 0 ) return 0; /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */ pWhereRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); pInClause = pWhereRowid ? sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0) : 0; sqlite3PExprAddSelect(pParse, pInClause, pSelect); return pInClause; limit_where_cleanup: sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pOrderBy); sqlite3ExprDelete(pParse->db, pLimit); sqlite3ExprDelete(pParse->db, pOffset); return 0; }
/* ** This is called by the parser when it sees a CREATE TRIGGER statement ** up to the point of the BEGIN before the trigger actions. A Trigger ** structure is generated based on the information available and stored ** in pParse->pNewTrigger. After the trigger actions have been parsed, the ** sqlite3FinishTrigger() function is called to complete the trigger ** construction process. */ void sqlite3BeginTrigger( Parse *pParse, /* The parse context of the CREATE TRIGGER statement */ Token *pName1, /* The name of the trigger */ Token *pName2, /* The name of the trigger */ int tr_tm, /* One of TK_BEFORE, TK_AFTER, TK_INSTEAD */ int op, /* One of TK_INSERT, TK_UPDATE, TK_DELETE */ IdList *pColumns, /* column list if this is an UPDATE OF trigger */ SrcList *pTableName,/* The name of the table/view the trigger applies to */ int foreach, /* One of TK_ROW or TK_STATEMENT */ Expr *pWhen, /* WHEN clause */ int isTemp /* True if the TEMPORARY keyword is present */ ){ Trigger *pTrigger; Table *pTab; char *zName = 0; /* Name of the trigger */ sqlite *db = pParse->db; int iDb; /* The database to store the trigger in */ Token *pName; /* The unqualified db name */ DbFixer sFix; if( isTemp ){ /* If TEMP was specified, then the trigger name may not be qualified. */ if( pName2 && pName2->n>0 ){ sqlite3ErrorMsg(pParse, "temporary trigger may not have qualified name"); goto trigger_cleanup; } iDb = 1; pName = pName1; }else{ /* Figure out the db that the the trigger will be created in */ iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pName); if( iDb<0 ){ goto trigger_cleanup; } } /* If the trigger name was unqualified, and the table is a temp table, ** then set iDb to 1 to create the trigger in the temporary database. ** If sqlite3SrcListLookup() returns 0, indicating the table does not ** exist, the error is caught by the block below. */ if( !pTableName || sqlite3_malloc_failed ) goto trigger_cleanup; pTab = sqlite3SrcListLookup(pParse, pTableName); if( pName2->n==0 && pTab && pTab->iDb==1 ){ iDb = 1; } /* Ensure the table name matches database name and that the table exists */ if( sqlite3_malloc_failed ) goto trigger_cleanup; assert( pTableName->nSrc==1 ); if( sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName) && sqlite3FixSrcList(&sFix, pTableName) ){ goto trigger_cleanup; } pTab = sqlite3SrcListLookup(pParse, pTableName); if( !pTab ){ /* The table does not exist. */ goto trigger_cleanup; } /* Check that the trigger name is not reserved and that no trigger of the ** specified name exists */ zName = sqlite3NameFromToken(pName); if( !zName || SQLITE_OK!=sqlite3CheckObjectName(pParse, zName) ){ goto trigger_cleanup; } if( sqlite3HashFind(&(db->aDb[iDb].trigHash), zName,pName->n+1) ){ sqlite3ErrorMsg(pParse, "trigger %T already exists", pName); goto trigger_cleanup; } /* Do not create a trigger on a system table */ if( (iDb!=1 && sqlite3StrICmp(pTab->zName, MASTER_NAME)==0) || (iDb==1 && sqlite3StrICmp(pTab->zName, TEMP_MASTER_NAME)==0) ){ sqlite3ErrorMsg(pParse, "cannot create trigger on system table"); pParse->nErr++; goto trigger_cleanup; } /* INSTEAD of triggers are only for views and views only support INSTEAD ** of triggers. */ if( pTab->pSelect && tr_tm!=TK_INSTEAD ){ sqlite3ErrorMsg(pParse, "cannot create %s trigger on view: %S", (tr_tm == TK_BEFORE)?"BEFORE":"AFTER", pTableName, 0); goto trigger_cleanup; } if( !pTab->pSelect && tr_tm==TK_INSTEAD ){ sqlite3ErrorMsg(pParse, "cannot create INSTEAD OF" " trigger on table: %S", pTableName, 0); goto trigger_cleanup; } #ifndef SQLITE_OMIT_AUTHORIZATION { int code = SQLITE_CREATE_TRIGGER; const char *zDb = db->aDb[pTab->iDb].zName; const char *zDbTrig = isTemp ? db->aDb[1].zName : zDb; if( pTab->iDb==1 || isTemp ) code = SQLITE_CREATE_TEMP_TRIGGER; if( sqlite3AuthCheck(pParse, code, zName, pTab->zName, zDbTrig) ){ goto trigger_cleanup; } if( sqlite3AuthCheck(pParse, SQLITE_INSERT, SCHEMA_TABLE(pTab->iDb), 0, zDb)){ goto trigger_cleanup; } } #endif /* INSTEAD OF triggers can only appear on views and BEFORE triggers ** cannot appear on views. So we might as well translate every ** INSTEAD OF trigger into a BEFORE trigger. It simplifies code ** elsewhere. */ if (tr_tm == TK_INSTEAD){ tr_tm = TK_BEFORE; } /* Build the Trigger object */ pTrigger = (Trigger*)sqliteMalloc(sizeof(Trigger)); if( pTrigger==0 ) goto trigger_cleanup; pTrigger->name = zName; zName = 0; pTrigger->table = sqliteStrDup(pTableName->a[0].zName); if( sqlite3_malloc_failed ) goto trigger_cleanup; pTrigger->iDb = iDb; pTrigger->iTabDb = pTab->iDb; pTrigger->op = op; pTrigger->tr_tm = tr_tm; pTrigger->pWhen = sqlite3ExprDup(pWhen); pTrigger->pColumns = sqlite3IdListDup(pColumns); pTrigger->foreach = foreach; sqlite3TokenCopy(&pTrigger->nameToken,pName); assert( pParse->pNewTrigger==0 ); pParse->pNewTrigger = pTrigger; trigger_cleanup: sqliteFree(zName); sqlite3SrcListDelete(pTableName); sqlite3IdListDelete(pColumns); sqlite3ExprDelete(pWhen); }
/* ** 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 */ const char *zDb; /* Name of database holding pTab */ int end, addr = 0; /* 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 */ sqlite3 *db; /* Main database structure */ AuthContext sContext; /* Authorization context */ int oldIdx = -1; /* Cursor for the OLD table of AFTER triggers */ NameContext sNC; /* Name context to resolve expressions in */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to delete from a view */ int triggers_exist = 0; /* True if any triggers exist */ #endif sContext.pParse = 0; if( pParse->nErr || sqlite3_malloc_failed ){ 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 = 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 triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0); isView = pTab->pSelect!=0; #else # define triggers_exist 0 # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){ goto delete_from_cleanup; } assert( pTab->iDb<db->nDb ); zDb = db->aDb[pTab->iDb].zName; if( sqlite3AuthCheck(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 && sqlite3ViewGetColumnNames(pParse, pTab) ){ goto delete_from_cleanup; } /* Allocate a cursor used to store the old.* data for a trigger. */ if( triggers_exist ){ oldIdx = pParse->nTab++; } /* Resolve the column names in the WHERE clause. */ assert( pTabList->nSrc==1 ); iCur = pTabList->a[0].iCursor = pParse->nTab++; memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; if( sqlite3ExprResolveNames(&sNC, pWhere) ){ goto delete_from_cleanup; } /* 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, triggers_exist, pTab->iDb); /* If we are trying to delete from a view, construct that view into ** a temporary table. */ if( isView ){ Select *pView = sqlite3SelectDup(pTab->pSelect); sqlite3Select(pParse, pView, SRT_TempTable, iCur, 0, 0, 0, 0); sqlite3SelectDelete(pView); } /* Initialize the counter of the number of rows deleted, if ** we are counting rows. */ if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp(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 && !triggers_exist ){ if( db->flags & SQLITE_CountRows ){ /* If counting rows deleted, just count the total number of ** entries in the table. */ int endOfLoop = sqlite3VdbeMakeLabel(v); int addr; if( !isView ){ sqlite3OpenTableForReading(v, iCur, pTab); } sqlite3VdbeAddOp(v, OP_Rewind, iCur, sqlite3VdbeCurrentAddr(v)+2); addr = sqlite3VdbeAddOp(v, OP_AddImm, 1, 0); sqlite3VdbeAddOp(v, OP_Next, iCur, addr); sqlite3VdbeResolveLabel(v, endOfLoop); sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } if( !isView ){ sqlite3VdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ sqlite3VdbeAddOp(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{ /* Ensure all required collation sequences are available. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( sqlite3CheckIndexCollSeq(pParse, pIdx) ){ goto delete_from_cleanup; } } /* Begin the database scan */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0); if( pWInfo==0 ) goto delete_from_cleanup; /* Remember the rowid of every item to be deleted. */ sqlite3VdbeAddOp(v, OP_Recno, iCur, 0); sqlite3VdbeAddOp(v, OP_ListWrite, 0, 0); if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp(v, OP_AddImm, 1, 0); } /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); /* Open the pseudo-table used to store OLD if there are triggers. */ if( triggers_exist ){ sqlite3VdbeAddOp(v, OP_OpenPseudo, oldIdx, 0); sqlite3VdbeAddOp(v, OP_SetNumColumns, oldIdx, pTab->nCol); } /* 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. */ sqlite3VdbeAddOp(v, OP_ListRewind, 0, 0); end = sqlite3VdbeMakeLabel(v); /* This is the beginning of the delete loop when there are ** row triggers. */ if( triggers_exist ){ addr = sqlite3VdbeAddOp(v, OP_ListRead, 0, end); if( !isView ){ sqlite3VdbeAddOp(v, OP_Dup, 0, 0); sqlite3OpenTableForReading(v, iCur, pTab); } sqlite3VdbeAddOp(v, OP_MoveGe, iCur, 0); sqlite3VdbeAddOp(v, OP_Recno, iCur, 0); sqlite3VdbeAddOp(v, OP_RowData, iCur, 0); sqlite3VdbeAddOp(v, OP_PutIntKey, oldIdx, 0); if( !isView ){ sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } (void)sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TRIGGER_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. */ sqlite3OpenTableAndIndices(pParse, pTab, iCur, OP_OpenWrite); /* This is the beginning of the delete loop when there are no ** row triggers */ if( !triggers_exist ){ addr = sqlite3VdbeAddOp(v, OP_ListRead, 0, end); } /* Delete the row */ sqlite3GenerateRowDelete(db, v, pTab, iCur, pParse->nested==0); } /* If there are row triggers, close all cursors then invoke ** the AFTER triggers */ if( triggers_exist ){ if( !isView ){ for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqlite3VdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum); } sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } (void)sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default, addr); } /* End of the delete loop */ sqlite3VdbeAddOp(v, OP_Goto, 0, addr); sqlite3VdbeResolveLabel(v, end); sqlite3VdbeAddOp(v, OP_ListReset, 0, 0); /* Close the cursors after the loop if there are no row triggers */ if( !triggers_exist ){ for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqlite3VdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum); } sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } } /* ** 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==0 && !pParse->trigStack ){ sqlite3VdbeAddOp(v, OP_Callback, 1, 0); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, "rows deleted", P3_STATIC); } delete_from_cleanup: sqlite3AuthContextPop(&sContext); sqlite3SrcListDelete(pTabList); sqlite3ExprDelete(pWhere); return; }
/* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere */ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ Expr *pWhere, /* The WHERE clause. May be null */ int onError /* How to handle constraint errors */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ int addrTop = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ Vdbe *v; /* The virtual database engine */ Index *pIdx; /* For looping over indices */ Index *pPk; /* The PRIMARY KEY index for WITHOUT ROWID tables */ int nIdx; /* Number of indices that need updating */ int iBaseCur; /* Base cursor number */ int iDataCur; /* Cursor for the canonical data btree */ int iIdxCur; /* Cursor for the first index */ sqlite3 *db; /* The database structure */ int *aRegIdx = 0; /* One register assigned to each index to be updated */ int *aXRef = 0; /* aXRef[i] is the index in pChanges->a[] of the ** an expression for the i-th column of the table. ** aXRef[i]==-1 if the i-th column is not changed. */ u8 *aToOpen; /* 1 for tables and indices to be opened */ u8 chngPk; /* PRIMARY KEY changed in a WITHOUT ROWID table */ u8 chngRowid; /* Rowid changed in a normal table */ u8 chngKey; /* Either chngPk or chngRowid */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int okOnePass; /* True for one-pass algorithm without the FIFO */ int hasFK; /* True if foreign key processing is required */ int labelBreak; /* Jump here to break out of UPDATE loop */ int labelContinue; /* Jump here to continue next step of UPDATE loop */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True when updating a view (INSTEAD OF trigger) */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */ #endif int newmask; /* Mask of NEW.* columns accessed by BEFORE triggers */ int iEph = 0; /* Ephemeral table holding all primary key values */ int nKey = 0; /* Number of elements in regKey for WITHOUT ROWID */ int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */ /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid = 0; /* The old rowid */ int regNewRowid = 0; /* The new rowid */ int regNew = 0; /* Content of the NEW.* table in triggers */ int regOld = 0; /* Content of OLD.* table in triggers */ int regRowSet = 0; /* Rowset of rows to be updated */ int regKey = 0; /* composite PRIMARY KEY value */ memset(&sContext, 0, sizeof(sContext)); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto update_cleanup; } assert( pTabList->nSrc==1 ); /* Locate the table which we want to update. */ pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); /* Figure out if we have any triggers and if the table being ** updated is a view. */ #ifndef SQLITE_OMIT_TRIGGER pTrigger = sqlite3TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask); isView = pTab->pSelect!=0; assert( pTrigger || tmask==0 ); #else # define pTrigger 0 # define isView 0 # define tmask 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ goto update_cleanup; } /* Allocate a cursors for the main database table and for all indices. ** The index cursors might not be used, but if they are used they ** need to occur right after the database cursor. So go ahead and ** allocate enough space, just in case. */ pTabList->a[0].iCursor = iBaseCur = iDataCur = pParse->nTab++; iIdxCur = iDataCur+1; pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab); for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){ if( IsPrimaryKeyIndex(pIdx) && pPk!=0 ){ iDataCur = pParse->nTab; pTabList->a[0].iCursor = iDataCur; } pParse->nTab++; } /* Allocate space for aXRef[], aRegIdx[], and aToOpen[]. ** Initialize aXRef[] and aToOpen[] to their default values. */ aXRef = sqlite3DbMallocRaw(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 ); if( aXRef==0 ) goto update_cleanup; aRegIdx = aXRef+pTab->nCol; aToOpen = (u8*)(aRegIdx+nIdx); memset(aToOpen, 1, nIdx+1); aToOpen[nIdx+1] = 0; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = chngPk = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ if( j==pTab->iPKey ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else if( pPk && (pTab->aCol[j].colFlags & COLFLAG_PRIMKEY)!=0 ){ chngPk = 1; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( pPk==0 && sqlite3IsRowid(pChanges->a[i].zName) ){ j = -1; chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); pParse->checkSchema = 1; goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, j<0 ? "ROWID" : pTab->aCol[j].zName, db->aDb[iDb].zName); if( rc==SQLITE_DENY ){ goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } assert( (chngRowid & chngPk)==0 ); assert( chngRowid==0 || chngRowid==1 ); assert( chngPk==0 || chngPk==1 ); chngKey = chngRowid + chngPk; /* The SET expressions are not actually used inside the WHERE loop. ** So reset the colUsed mask */ pTabList->a[0].colUsed = 0; hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey); /* There is one entry in the aRegIdx[] array for each index on the table ** being updated. Fill in aRegIdx[] with a register number that will hold ** the key for accessing each index. ** ** FIXME: Be smarter about omitting indexes that use expressions. */ for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngKey || hasFK || pIdx->pPartIdxWhere || pIdx==pPk ){ reg = ++pParse->nMem; }else{ reg = 0; for(i=0; i<pIdx->nKeyCol; i++){ i16 iIdxCol = pIdx->aiColumn[i]; if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){ reg = ++pParse->nMem; break; } } } if( reg==0 ) aToOpen[j+1] = 0; aRegIdx[j] = reg; } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); /* Allocate required registers. */ if( !IsVirtual(pTab) ){ regRowSet = ++pParse->nMem; regOldRowid = regNewRowid = ++pParse->nMem; if( chngPk || pTrigger || hasFK ){ regOld = pParse->nMem + 1; pParse->nMem += pTab->nCol; } if( chngKey || pTrigger || hasFK ){ regNewRowid = ++pParse->nMem; } regNew = pParse->nMem + 1; pParse->nMem += pTab->nCol; } /* Start the view context. */ if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } /* If we are trying to update a view, realize that view into ** an ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iDataCur); } #endif /* Resolve the column names in all the expressions in the ** WHERE clause. */ if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } #ifndef SQLITE_OMIT_VIRTUALTABLE /* Virtual tables must be handled separately */ if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere, onError); goto update_cleanup; } #endif /* Begin the database scan */ if( HasRowid(pTab) ){ sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur ); if( pWInfo==0 ) goto update_cleanup; okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); /* Remember the rowid of every item to be updated. */ sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid); if( !okOnePass ){ sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); } /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); }else{ int iPk; /* First of nPk memory cells holding PRIMARY KEY value */ i16 nPk; /* Number of components of the PRIMARY KEY */ int addrOpen; /* Address of the OpenEphemeral instruction */ assert( pPk!=0 ); nPk = pPk->nKeyCol; iPk = pParse->nMem+1; pParse->nMem += nPk; regKey = ++pParse->nMem; iEph = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_Null, 0, iPk); addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur); if( pWInfo==0 ) goto update_cleanup; okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); for(i=0; i<nPk; i++){ assert( pPk->aiColumn[i]>=0 ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i], iPk+i); } if( okOnePass ){ sqlite3VdbeChangeToNoop(v, addrOpen); nKey = nPk; regKey = iPk; }else{ sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey, sqlite3IndexAffinityStr(db, pPk), nPk); sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey); } sqlite3WhereEnd(pWInfo); } /* Initialize the count of updated rows */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } labelBreak = sqlite3VdbeMakeLabel(v); if( !isView ){ /* ** Open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ if( onError==OE_Replace ){ memset(aToOpen, 1, nIdx+1); }else{ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_Replace ){ memset(aToOpen, 1, nIdx+1); break; } } } if( okOnePass ){ if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0; if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0; } sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, iBaseCur, aToOpen, 0, 0); } /* Top of the update loop */ if( okOnePass ){ if( aToOpen[iDataCur-iBaseCur] && !isView ){ assert( pPk ); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey); VdbeCoverageNeverTaken(v); } labelContinue = labelBreak; sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak); VdbeCoverageIf(v, pPk==0); VdbeCoverageIf(v, pPk!=0); }else if( pPk ){ labelContinue = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v); addrTop = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0); VdbeCoverage(v); }else{ labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, labelBreak, regOldRowid); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } /* If the record number will change, set register regNewRowid to ** contain the new value. If the record number is not being modified, ** then regNewRowid is the same register as regOldRowid, which is ** already populated. */ assert( chngKey || pTrigger || hasFK || regOldRowid==regNewRowid ); if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); VdbeCoverage(v); } /* Compute the old pre-UPDATE content of the row being changed, if that ** information is needed */ if( chngPk || hasFK || pTrigger ){ u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); oldmask |= sqlite3TriggerColmask(pParse, pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( oldmask==0xffffffff || (i<32 && (oldmask & MASKBIT32(i))!=0) || (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0 ){ testcase( oldmask!=0xffffffff && i==31 ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regOld+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); } } if( chngRowid==0 && pPk==0 ){ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid); } } /* Populate the array of registers beginning at regNew with the new ** row data. This array is used to check constants, create the new ** table and index records, and as the values for any new.* references ** made by triggers. ** ** If there are one or more BEFORE triggers, then do not populate the ** registers associated with columns that are (a) not modified by ** this UPDATE statement and (b) not accessed by new.* references. The ** values for registers not modified by the UPDATE must be reloaded from ** the database after the BEFORE triggers are fired anyway (as the trigger ** may have modified them). So not loading those that are not going to ** be used eliminates some redundant opcodes. */ newmask = sqlite3TriggerColmask( pParse, pTrigger, pChanges, 1, TRIGGER_BEFORE, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){ /* This branch loads the value of a column that will not be changed ** into a register. This is done if there are no BEFORE triggers, or ** if there are one or more BEFORE triggers that use this value via ** a new.* reference in a trigger program. */ testcase( i==31 ); testcase( i==32 ); sqlite3ExprCodeGetColumnToReg(pParse, pTab, i, iDataCur, regNew+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); } } } /* Fire any BEFORE UPDATE triggers. This happens before constraints are ** verified. One could argue that this is wrong. */ if( tmask&TRIGGER_BEFORE ){ sqlite3TableAffinity(v, pTab, regNew); sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, regOldRowid, onError, labelContinue); /* The row-trigger may have deleted the row being updated. In this ** case, jump to the next row. No updates or AFTER triggers are ** required. This behavior - what happens when the row being updated ** is deleted or renamed by a BEFORE trigger - is left undefined in the ** documentation. */ if( pPk ){ sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey); VdbeCoverage(v); }else{ sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } /* If it did not delete it, the row-trigger may still have modified ** some of the columns of the row being updated. Load the values for ** all columns not modified by the update statement into their ** registers in case this has happened. */ for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 && i!=pTab->iPKey ){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regNew+i); } } } if( !isView ){ int addr1 = 0; /* Address of jump instruction */ int bReplace = 0; /* True if REPLACE conflict resolution might happen */ /* Do constraint checks. */ assert( regOldRowid>0 ); sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace); /* Do FK constraint checks. */ if( hasFK ){ sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey); } /* Delete the index entries associated with the current record. */ if( bReplace || chngKey ){ if( pPk ){ addr1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, 0, regKey, nKey); }else{ addr1 = sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, 0, regOldRowid); } VdbeCoverageNeverTaken(v); } sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx, -1); /* If changing the record number, delete the old record. */ if( hasFK || chngKey || pPk!=0 ){ sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, 0); } if( bReplace || chngKey ){ sqlite3VdbeJumpHere(v, addr1); } if( hasFK ){ sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey); } /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, regNewRowid, aRegIdx, 1, 0, 0); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just updated. */ if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); } } /* Increment the row counter */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue); /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ if( okOnePass ){ /* Nothing to do at end-of-loop for a single-pass */ }else if( pPk ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop); VdbeCoverage(v); }else{ sqlite3VdbeGoto(v, labelContinue); } sqlite3VdbeResolveLabel(v, labelBreak); /* Close all tables */ for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ assert( aRegIdx ); if( aToOpen[i+1] ){ sqlite3VdbeAddOp2(v, OP_Close, iIdxCur+i, 0); } } if( iDataCur<iIdxCur ) sqlite3VdbeAddOp2(v, OP_Close, iDataCur, 0); /* 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 changed. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( (db->flags&SQLITE_CountRows) && !pParse->pTriggerTab && !pParse->nested ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */ sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pChanges); sqlite3ExprDelete(db, pWhere); return; }
/* ** This procedure generates VDBE code for a single invocation of either the ** sqlite_detach() or sqlite_attach() SQL user functions. */ static void codeAttach( Parse *pParse, /* The parser context */ int type, /* Either SQLITE_ATTACH or SQLITE_DETACH */ const char *zFunc, /* Either "sqlite_attach" or "sqlite_detach */ int nFunc, /* Number of args to pass to zFunc */ Expr *pAuthArg, /* Expression to pass to authorization callback */ Expr *pFilename, /* Name of database file */ Expr *pDbname, /* Name of the database to use internally */ Expr *pKey /* Database key for encryption extension */ ){ int rc; NameContext sName; Vdbe *v; FuncDef *pFunc; sqlite3* db = pParse->db; #ifndef SQLITE_OMIT_AUTHORIZATION assert( sqlite3ThreadDataReadOnly()->mallocFailed || pAuthArg ); if( pAuthArg ){ char *zAuthArg = sqlite3NameFromToken(&pAuthArg->span); if( !zAuthArg ){ goto attach_end; } rc = sqlite3AuthCheck(pParse, type, zAuthArg, 0, 0); sqliteFree(zAuthArg); if(rc!=SQLITE_OK ){ goto attach_end; } } #endif /* SQLITE_OMIT_AUTHORIZATION */ memset(&sName, 0, sizeof(NameContext)); sName.pParse = pParse; if( SQLITE_OK!=(rc = resolveAttachExpr(&sName, pFilename)) || SQLITE_OK!=(rc = resolveAttachExpr(&sName, pDbname)) || SQLITE_OK!=(rc = resolveAttachExpr(&sName, pKey)) ){ pParse->nErr++; goto attach_end; } v = sqlite3GetVdbe(pParse); sqlite3ExprCode(pParse, pFilename); sqlite3ExprCode(pParse, pDbname); sqlite3ExprCode(pParse, pKey); assert(v || sqlite3ThreadDataReadOnly()->mallocFailed); if( v ){ sqlite3VdbeAddOp(v, OP_Function, 0, nFunc); pFunc = sqlite3FindFunction(db, zFunc, strlen(zFunc), nFunc, SQLITE_UTF8,0); sqlite3VdbeChangeP3(v, -1, (char *)pFunc, P3_FUNCDEF); /* Code an OP_Expire. For an ATTACH statement, set P1 to true (expire this ** statement only). For DETACH, set it to false (expire all existing ** statements). */ sqlite3VdbeAddOp(v, OP_Expire, (type==SQLITE_ATTACH), 0); } attach_end: sqlite3ExprDelete(pFilename); sqlite3ExprDelete(pDbname); sqlite3ExprDelete(pKey); }
/* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere */ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ Expr *pWhere, /* The WHERE clause. May be null */ int onError /* How to handle constraint errors */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ int addr = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ Vdbe *v; /* The virtual database engine */ Index *pIdx; /* For looping over indices */ int nIdx; /* Number of indices that need updating */ int iCur; /* VDBE Cursor number of pTab */ sqlite3 *db; /* The database structure */ int *aRegIdx = 0; /* One register assigned to each index to be updated */ int *aXRef = 0; /* aXRef[i] is the index in pChanges->a[] of the ** an expression for the i-th column of the table. ** aXRef[i]==-1 if the i-th column is not changed. */ int chngRowid; /* True if the record number is being changed */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int okOnePass; /* True for one-pass algorithm without the FIFO */ int hasFK; /* True if foreign key processing is required */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True when updating a view (INSTEAD OF trigger) */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */ #endif int newmask; /* Mask of NEW.* columns accessed by BEFORE triggers */ /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid; /* The old rowid */ int regNewRowid; /* The new rowid */ int regNew; int regOld = 0; int regRowSet = 0; /* Rowset of rows to be updated */ int regRec; /* Register used for new table record to insert */ memset(&sContext, 0, sizeof(sContext)); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto update_cleanup; } assert( pTabList->nSrc==1 ); /* Locate the table which we want to update. */ pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); /* Figure out if we have any triggers and if the table being ** updated is a view. */ #ifndef SQLITE_OMIT_TRIGGER pTrigger = sqlite3TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask); isView = pTab->pSelect!=0; assert( pTrigger || tmask==0 ); #else # define pTrigger 0 # define isView 0 # define tmask 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ goto update_cleanup; } aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol ); if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* Allocate a cursors for the main database table and for all indices. ** The index cursors might not be used, but if they are used they ** need to occur right after the database cursor. So go ahead and ** allocate enough space, just in case. */ pTabList->a[0].iCursor = iCur = pParse->nTab++; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ pParse->nTab++; } /* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ if( j==pTab->iPKey ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pChanges->a[i].zName) ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); pParse->checkSchema = 1; goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, pTab->aCol[j].zName, db->aDb[iDb].zName); if( rc==SQLITE_DENY ){ goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngRowid); /* Allocate memory for the array aRegIdx[]. There is one entry in the ** array for each index associated with table being updated. Fill in ** the value with a register number for indices that are to be used ** and with zero for unused indices. */ for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} if( nIdx>0 ){ aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx ); if( aRegIdx==0 ) goto update_cleanup; } for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngRowid ){ reg = ++pParse->nMem; }else{ reg = 0; for(i=0; i<pIdx->nColumn; i++){ if( aXRef[pIdx->aiColumn[i]]>=0 ){ reg = ++pParse->nMem; break; } } } aRegIdx[j] = reg; } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); #ifndef SQLITE_OMIT_VIRTUALTABLE /* Virtual tables must be handled separately */ if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere); pWhere = 0; pTabList = 0; goto update_cleanup; } #endif /* Allocate required registers. */ regOldRowid = regNewRowid = ++pParse->nMem; if( pTrigger || hasFK ){ regOld = pParse->nMem + 1; pParse->nMem += pTab->nCol; } if( chngRowid || pTrigger || hasFK ){ regNewRowid = ++pParse->nMem; } regNew = pParse->nMem + 1; pParse->nMem += pTab->nCol; regRec = ++pParse->nMem; /* Start the view context. */ if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } /* If we are trying to update a view, realize that view into ** a ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); } #endif /* Resolve the column names in all the expressions in the ** WHERE clause. */ if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere,0, WHERE_ONEPASS_DESIRED); if( pWInfo==0 ) goto update_cleanup; okOnePass = pWInfo->okOnePass; /* Remember the rowid of every item to be updated. */ sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid); if( !okOnePass ){ regRowSet = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); } /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); /* Initialize the count of updated rows */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } if( !isView ){ /* ** Open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ if( !okOnePass ) sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); if( onError==OE_Replace ){ openAll = 1; }else{ openAll = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_Replace ){ openAll = 1; break; } } } for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aRegIdx[i]>0 ){ KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); sqlite3VdbeAddOp4(v, OP_OpenWrite, iCur+i+1, pIdx->tnum, iDb, (char*)pKey, P4_KEYINFO_HANDOFF); assert( pParse->nTab>iCur+i+1 ); } } } /* Top of the update loop */ if( okOnePass ){ int a1 = sqlite3VdbeAddOp1(v, OP_NotNull, regOldRowid); addr = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, a1); }else{ addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid); } /* Make cursor iCur point to the record that is being updated. If ** this record does not exist for some reason (deleted by a trigger, ** for example, then jump to the next iteration of the RowSet loop. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* If the record number will change, set register regNewRowid to ** contain the new value. If the record number is not being modified, ** then regNewRowid is the same register as regOldRowid, which is ** already populated. */ assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid ); if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } /* If there are triggers on this table, populate an array of registers ** with the required old.* column data. */ if( hasFK || pTrigger ){ u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); oldmask |= sqlite3TriggerColmask(pParse, pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, i, regOld+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); } } if( chngRowid==0 ){ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid); } } /* Populate the array of registers beginning at regNew with the new ** row data. This array is used to check constaints, create the new ** table and index records, and as the values for any new.* references ** made by triggers. ** ** If there are one or more BEFORE triggers, then do not populate the ** registers associated with columns that are (a) not modified by ** this UPDATE statement and (b) not accessed by new.* references. The ** values for registers not modified by the UPDATE must be reloaded from ** the database after the BEFORE triggers are fired anyway (as the trigger ** may have modified them). So not loading those that are not going to ** be used eliminates some redundant opcodes. */ newmask = sqlite3TriggerColmask( pParse, pTrigger, pChanges, 1, TRIGGER_BEFORE, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){ /* This branch loads the value of a column that will not be changed ** into a register. This is done if there are no BEFORE triggers, or ** if there are one or more BEFORE triggers that use this value via ** a new.* reference in a trigger program. */ testcase( i==31 ); testcase( i==32 ); sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regNew+i); sqlite3ColumnDefault(v, pTab, i, regNew+i); } } } /* Fire any BEFORE UPDATE triggers. This happens before constraints are ** verified. One could argue that this is wrong. */ if( tmask&TRIGGER_BEFORE ){ sqlite3VdbeAddOp2(v, OP_Affinity, regNew, pTab->nCol); sqlite3TableAffinityStr(v, pTab); sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, regOldRowid, onError, addr); /* The row-trigger may have deleted the row being updated. In this ** case, jump to the next row. No updates or AFTER triggers are ** required. This behaviour - what happens when the row being updated ** is deleted or renamed by a BEFORE trigger - is left undefined in the ** documentation. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* If it did not delete it, the row-trigger may still have modified ** some of the columns of the row being updated. Load the values for ** all columns not modified by the update statement into their ** registers in case this has happened. */ for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 && i!=pTab->iPKey ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regNew+i); sqlite3ColumnDefault(v, pTab, i, regNew+i); } } } if( !isView ){ int j1; /* Address of jump instruction */ /* Do constraint checks. */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Do FK constraint checks. */ if( hasFK ){ sqlite3FkCheck(pParse, pTab, regOldRowid, 0); } /* Delete the index entries associated with the current record. */ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); /* If changing the record number, delete the old record. */ if( hasFK || chngRowid ){ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); if( hasFK ){ sqlite3FkCheck(pParse, pTab, 0, regNewRowid); } /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just updated. */ if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); } } /* Increment the row counter */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, addr); /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeJumpHere(v, addr); /* Close all tables */ for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aRegIdx[i]>0 ){ sqlite3VdbeAddOp2(v, OP_Close, iCur+i+1, 0); } } sqlite3VdbeAddOp2(v, OP_Close, iCur, 0); /* 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 changed. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( (db->flags&SQLITE_CountRows) && !pParse->pTriggerTab && !pParse->nested ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); sqlite3DbFree(db, aRegIdx); sqlite3DbFree(db, aXRef); sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pChanges); sqlite3ExprDelete(db, pWhere); return; }
static void codeAttach( Parse *pParse, int type, FuncDef const *pFunc, Expr *pAuthArg, Expr *pFilename, Expr *pDbname, Expr *pKey ){ int rc; NameContext sName; Vdbe *v; sqlite3* db = pParse->db; int regArgs; memset(&sName, 0, sizeof(NameContext)); sName.pParse = pParse; if( SQLITE_OK!=(rc = resolveAttachExpr(&sName, pFilename)) || SQLITE_OK!=(rc = resolveAttachExpr(&sName, pDbname)) || SQLITE_OK!=(rc = resolveAttachExpr(&sName, pKey)) ){ pParse->nErr++; goto attach_end; } #ifndef SQLITE_OMIT_AUTHORIZATION if( pAuthArg ){ char *zAuthArg; if( pAuthArg->op==TK_STRING ){ zAuthArg = pAuthArg->u.zToken; }else{ zAuthArg = 0; } rc = sqlite3AuthCheck(pParse, type, zAuthArg, 0, 0); if(rc!=SQLITE_OK ){ goto attach_end; } } #endif v = sqlite3GetVdbe(pParse); regArgs = sqlite3GetTempRange(pParse, 4); sqlite3ExprCode(pParse, pFilename, regArgs); sqlite3ExprCode(pParse, pDbname, regArgs+1); sqlite3ExprCode(pParse, pKey, regArgs+2); assert( v || db->mallocFailed ); if( v ){ sqlite3VdbeAddOp3(v, OP_Function, 0, regArgs+3-pFunc->nArg, regArgs+3); assert( pFunc->nArg==-1 || (pFunc->nArg&0xff)==pFunc->nArg ); sqlite3VdbeChangeP5(v, (u8)(pFunc->nArg)); sqlite3VdbeChangeP4(v, -1, (char *)pFunc, P4_FUNCDEF); sqlite3VdbeAddOp1(v, OP_Expire, (type==SQLITE_ATTACH)); } attach_end: sqlite3ExprDelete(db, pFilename); sqlite3ExprDelete(db, pDbname); sqlite3ExprDelete(db, pKey); }
/* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere */ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ Expr *pWhere, /* The WHERE clause. May be null */ int onError /* How to handle constraint errors */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ int addr = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ Vdbe *v; /* The virtual database engine */ Index *pIdx; /* For looping over indices */ int nIdx; /* Number of indices that need updating */ int iCur; /* VDBE Cursor number of pTab */ sqlite3 *db; /* The database structure */ int *aRegIdx = 0; /* One register assigned to each index to be updated */ int *aXRef = 0; /* aXRef[i] is the index in pChanges->a[] of the ** an expression for the i-th column of the table. ** aXRef[i]==-1 if the i-th column is not changed. */ int chngRowid; /* True if the record number is being changed */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int j1; /* Addresses of jump instructions */ int okOnePass; /* True for one-pass algorithm without the FIFO */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* Trying to update a view */ int triggers_exist = 0; /* True if any row triggers exist */ #endif int iBeginAfterTrigger = 0; /* Address of after trigger program */ int iEndAfterTrigger = 0; /* Exit of after trigger program */ int iBeginBeforeTrigger = 0; /* Address of before trigger program */ int iEndBeforeTrigger = 0; /* Exit of before trigger program */ u32 old_col_mask = 0; /* Mask of OLD.* columns in use */ u32 new_col_mask = 0; /* Mask of NEW.* columns in use */ int newIdx = -1; /* index of trigger "new" temp table */ int oldIdx = -1; /* index of trigger "old" temp table */ /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid; /* The old rowid */ int regNewRowid; /* The new rowid */ int regData; /* New data for the row */ int regRowSet = 0; /* Rowset of rows to be updated */ sContext.pParse = 0; db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto update_cleanup; } assert( pTabList->nSrc==1 ); /* Locate the table which we want to update. */ pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); /* Figure out if we have any triggers and if the table being ** updated is a view */ #ifndef SQLITE_OMIT_TRIGGER triggers_exist = sqlite3TriggersExist(pTab, TK_UPDATE, pChanges); isView = pTab->pSelect!=0; #else # define triggers_exist 0 # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){ goto update_cleanup; } if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol ); if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* If there are FOR EACH ROW triggers, allocate cursors for the ** special OLD and NEW tables */ if( triggers_exist ){ newIdx = pParse->nTab++; oldIdx = pParse->nTab++; } /* Allocate a cursors for the main database table and for all indices. ** The index cursors might not be used, but if they are used they ** need to occur right after the database cursor. So go ahead and ** allocate enough space, just in case. */ pTabList->a[0].iCursor = iCur = pParse->nTab++; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ pParse->nTab++; } /* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ if( j==pTab->iPKey ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pChanges->a[i].zName) ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, pTab->aCol[j].zName, db->aDb[iDb].zName); if( rc==SQLITE_DENY ){ goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } /* Allocate memory for the array aRegIdx[]. There is one entry in the ** array for each index associated with table being updated. Fill in ** the value with a register number for indices that are to be used ** and with zero for unused indices. */ for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} if( nIdx>0 ){ aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx ); if( aRegIdx==0 ) goto update_cleanup; } for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngRowid ){ reg = ++pParse->nMem; }else{ reg = 0; for(i=0; i<pIdx->nColumn; i++){ if( aXRef[pIdx->aiColumn[i]]>=0 ){ reg = ++pParse->nMem; break; } } } aRegIdx[j] = reg; } /* Allocate a block of register used to store the change record ** sent to sqlite3GenerateConstraintChecks(). There are either ** one or two registers for holding the rowid. One rowid register ** is used if chngRowid is false and two are used if chngRowid is ** true. Following these are pTab->nCol register holding column ** data. */ regOldRowid = regNewRowid = pParse->nMem + 1; pParse->nMem += pTab->nCol + 1; if( chngRowid ){ regNewRowid++; pParse->nMem++; } regData = regNewRowid+1; /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); #ifndef SQLITE_OMIT_VIRTUALTABLE /* Virtual tables must be handled separately */ if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere); pWhere = 0; pTabList = 0; goto update_cleanup; } #endif /* Start the view context */ if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } /* Generate the code for triggers. */ if( triggers_exist ){ int iGoto; /* Create pseudo-tables for NEW and OLD */ sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pTab->nCol); sqlite3VdbeAddOp2(v, OP_OpenPseudo, oldIdx, 0); sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pTab->nCol); sqlite3VdbeAddOp2(v, OP_OpenPseudo, newIdx, 0); iGoto = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0); addr = sqlite3VdbeMakeLabel(v); iBeginBeforeTrigger = sqlite3VdbeCurrentAddr(v); if( sqlite3CodeRowTrigger(pParse, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, newIdx, oldIdx, onError, addr, &old_col_mask, &new_col_mask) ){ goto update_cleanup; } iEndBeforeTrigger = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0); iBeginAfterTrigger = sqlite3VdbeCurrentAddr(v); if( sqlite3CodeRowTrigger(pParse, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, newIdx, oldIdx, onError, addr, &old_col_mask, &new_col_mask) ){ goto update_cleanup; } iEndAfterTrigger = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0); sqlite3VdbeJumpHere(v, iGoto); } /* If we are trying to update a view, realize that view into ** a ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); } #endif /* Resolve the column names in all the expressions in the ** WHERE clause. */ if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, WHERE_ONEPASS_DESIRED, 0); if( pWInfo==0 ) goto update_cleanup; okOnePass = pWInfo->okOnePass; /* Remember the rowid of every item to be updated. */ sqlite3VdbeAddOp2(v, IsVirtual(pTab)?OP_VRowid:OP_Rowid, iCur, regOldRowid); if( !okOnePass ){ regRowSet = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); } /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); /* Initialize the count of updated rows */ if( db->flags & SQLITE_CountRows && !pParse->trigStack ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } if( !isView && !IsVirtual(pTab) ){ /* ** Open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ if( !okOnePass ) sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); if( onError==OE_Replace ){ openAll = 1; }else{ openAll = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_Replace ){ openAll = 1; break; } } } for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aRegIdx[i]>0 ){ KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); sqlite3VdbeAddOp4(v, OP_OpenWrite, iCur+i+1, pIdx->tnum, iDb, (char*)pKey, P4_KEYINFO_HANDOFF); assert( pParse->nTab>iCur+i+1 ); } } } /* Jump back to this point if a trigger encounters an IGNORE constraint. */ if( triggers_exist ){ sqlite3VdbeResolveLabel(v, addr); } /* Top of the update loop */ if( okOnePass ){ int a1 = sqlite3VdbeAddOp1(v, OP_NotNull, regOldRowid); addr = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, a1); }else{ addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid); } if( triggers_exist ){ int regRowid; int regRow; int regCols; /* Make cursor iCur point to the record that is being updated. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* Generate the OLD table */ regRowid = sqlite3GetTempReg(pParse); regRow = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regRowid); if( !old_col_mask ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regRow); }else{ sqlite3VdbeAddOp2(v, OP_RowData, iCur, regRow); } sqlite3VdbeAddOp3(v, OP_Insert, oldIdx, regRow, regRowid); /* Generate the NEW table */ if( chngRowid ){ sqlite3ExprCodeAndCache(pParse, pRowidExpr, regRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid); }else{ sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regRowid); } regCols = sqlite3GetTempRange(pParse, pTab->nCol); for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regCols+i); continue; } j = aXRef[i]; if( new_col_mask&((u32)1<<i) || new_col_mask==0xffffffff ){ if( j<0 ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regCols+i); sqlite3ColumnDefault(v, pTab, i); }else{ sqlite3ExprCodeAndCache(pParse, pChanges->a[j].pExpr, regCols+i); } }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regCols+i); } } sqlite3VdbeAddOp3(v, OP_MakeRecord, regCols, pTab->nCol, regRow); if( !isView ){ sqlite3TableAffinityStr(v, pTab); sqlite3ExprCacheAffinityChange(pParse, regCols, pTab->nCol); } sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol); /* if( pParse->nErr ) goto update_cleanup; */ sqlite3VdbeAddOp3(v, OP_Insert, newIdx, regRow, regRowid); sqlite3ReleaseTempReg(pParse, regRowid); sqlite3ReleaseTempReg(pParse, regRow); sqlite3VdbeAddOp2(v, OP_Goto, 0, iBeginBeforeTrigger); sqlite3VdbeJumpHere(v, iEndBeforeTrigger); } if( !isView && !IsVirtual(pTab) ){ /* Loop over every record that needs updating. We have to load ** the old data for each record to be updated because some columns ** might not change and we will need to copy the old value. ** Also, the old data is needed to delete the old index entries. ** So make the cursor point at the old record. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* If the record number will change, push the record number as it ** will be after the update. (The old record number is currently ** on top of the stack.) */ if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } /* Compute new data for this record. */ for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regData+i); continue; } j = aXRef[i]; if( j<0 ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regData+i); sqlite3ColumnDefault(v, pTab, i); }else{ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regData+i); } } /* Do constraint checks */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, chngRowid, 1, onError, addr); /* Delete the old indices for the current record. */ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); /* If changing the record number, delete the old record. */ if( chngRowid ){ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); /* Create the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, -1, 0); } /* Increment the row counter */ if( db->flags & SQLITE_CountRows && !pParse->trigStack){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } /* If there are triggers, close all the cursors after each iteration ** through the loop. The fire the after triggers. */ if( triggers_exist ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, iBeginAfterTrigger); sqlite3VdbeJumpHere(v, iEndAfterTrigger); } /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeJumpHere(v, addr); /* Close all tables */ for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aRegIdx[i]>0 ){ sqlite3VdbeAddOp2(v, OP_Close, iCur+i+1, 0); } } sqlite3VdbeAddOp2(v, OP_Close, iCur, 0); if( triggers_exist ){ sqlite3VdbeAddOp2(v, OP_Close, newIdx, 0); sqlite3VdbeAddOp2(v, OP_Close, oldIdx, 0); } /* ** Return the number of rows that were changed. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( db->flags & SQLITE_CountRows && !pParse->trigStack && pParse->nested==0 ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); sqlite3DbFree(db, aRegIdx); sqlite3DbFree(db, aXRef); sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pChanges); sqlite3ExprDelete(db, pWhere); return; }
/* ** This function is called when an UPDATE or DELETE operation is being ** compiled on table pTab, which is the parent table of foreign-key pFKey. ** If the current operation is an UPDATE, then the pChanges parameter is ** passed a pointer to the list of columns being modified. If it is a ** DELETE, pChanges is passed a NULL pointer. ** ** It returns a pointer to a Trigger structure containing a trigger ** equivalent to the ON UPDATE or ON DELETE action specified by pFKey. ** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is ** returned (these actions require no special handling by the triggers ** sub-system, code for them is created by fkScanChildren()). ** ** For example, if pFKey is the foreign key and pTab is table "p" in ** the following schema: ** ** CREATE TABLE p(pk PRIMARY KEY); ** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE); ** ** then the returned trigger structure is equivalent to: ** ** CREATE TRIGGER ... DELETE ON p BEGIN ** DELETE FROM c WHERE ck = old.pk; ** END; ** ** The returned pointer is cached as part of the foreign key object. It ** is eventually freed along with the rest of the foreign key object by ** sqlite3FkDelete(). */ static Trigger *fkActionTrigger( Parse *pParse, /* Parse context */ Table *pTab, /* Table being updated or deleted from */ FKey *pFKey, /* Foreign key to get action for */ ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */ ){ sqlite3 *db = pParse->db; /* Database handle */ int action; /* One of OE_None, OE_Cascade etc. */ Trigger *pTrigger; /* Trigger definition to return */ int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */ action = pFKey->aAction[iAction]; pTrigger = pFKey->apTrigger[iAction]; if( action!=OE_None && !pTrigger ){ u8 enableLookaside; /* Copy of db->lookaside.bEnabled */ char const *zFrom; /* Name of child table */ int nFrom; /* Length in bytes of zFrom */ Index *pIdx = 0; /* Parent key index for this FK */ int *aiCol = 0; /* child table cols -> parent key cols */ TriggerStep *pStep = 0; /* First (only) step of trigger program */ Expr *pWhere = 0; /* WHERE clause of trigger step */ ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */ Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */ int i; /* Iterator variable */ Expr *pWhen = 0; /* WHEN clause for the trigger */ if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0; assert( aiCol || pFKey->nCol==1 ); for(i=0; i<pFKey->nCol; i++){ Token tOld = { "old", 3 }; /* Literal "old" token */ Token tNew = { "new", 3 }; /* Literal "new" token */ Token tFromCol; /* Name of column in child table */ Token tToCol; /* Name of column in parent table */ int iFromCol; /* Idx of column in child table */ Expr *pEq; /* tFromCol = OLD.tToCol */ iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; assert( iFromCol>=0 ); tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid"; tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName; tToCol.n = sqlite3Strlen30(tToCol.z); tFromCol.n = sqlite3Strlen30(tFromCol.z); /* Create the expression "OLD.zToCol = zFromCol". It is important ** that the "OLD.zToCol" term is on the LHS of the = operator, so ** that the affinity and collation sequence associated with the ** parent table are used for the comparison. */ pEq = sqlite3PExpr(pParse, TK_EQ, sqlite3PExpr(pParse, TK_DOT, sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld), sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol) , 0), sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol) , 0); pWhere = sqlite3ExprAnd(db, pWhere, pEq); /* For ON UPDATE, construct the next term of the WHEN clause. ** The final WHEN clause will be like this: ** ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN) */ if( pChanges ){ pEq = sqlite3PExpr(pParse, TK_IS, sqlite3PExpr(pParse, TK_DOT, sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld), sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol), 0), sqlite3PExpr(pParse, TK_DOT, sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew), sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol), 0), 0); pWhen = sqlite3ExprAnd(db, pWhen, pEq); } if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){ Expr *pNew; if( action==OE_Cascade ){ pNew = sqlite3PExpr(pParse, TK_DOT, sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew), sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol) , 0); }else if( action==OE_SetDflt ){ Expr *pDflt = pFKey->pFrom->aCol[iFromCol].pDflt; if( pDflt ){ pNew = sqlite3ExprDup(db, pDflt, 0); }else{ pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0); } }else{ pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0); } pList = sqlite3ExprListAppend(pParse, pList, pNew); sqlite3ExprListSetName(pParse, pList, &tFromCol, 0); } } sqlite3DbFree(db, aiCol); zFrom = pFKey->pFrom->zName; nFrom = sqlite3Strlen30(zFrom); if( action==OE_Restrict ){ Token tFrom; Expr *pRaise; tFrom.z = zFrom; tFrom.n = nFrom; pRaise = sqlite3Expr(db, TK_RAISE, "foreign key constraint failed"); if( pRaise ){ pRaise->affinity = OE_Abort; } pSelect = sqlite3SelectNew(pParse, sqlite3ExprListAppend(pParse, 0, pRaise), sqlite3SrcListAppend(db, 0, &tFrom, 0), pWhere, 0, 0, 0, 0, 0, 0 ); pWhere = 0; } /* Disable lookaside memory allocation */ enableLookaside = db->lookaside.bEnabled; db->lookaside.bEnabled = 0; pTrigger = (Trigger *)sqlite3DbMallocZero(db, sizeof(Trigger) + /* struct Trigger */ sizeof(TriggerStep) + /* Single step in trigger program */ nFrom + 1 /* Space for pStep->target.z */ ); if( pTrigger ){ pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1]; pStep->target.z = (char *)&pStep[1]; pStep->target.n = nFrom; memcpy((char *)pStep->target.z, zFrom, nFrom); pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE); pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); if( pWhen ){ pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0); pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE); } } /* Re-enable the lookaside buffer, if it was disabled earlier. */ db->lookaside.bEnabled = enableLookaside; sqlite3ExprDelete(db, pWhere); sqlite3ExprDelete(db, pWhen); sqlite3ExprListDelete(db, pList); sqlite3SelectDelete(db, pSelect); if( db->mallocFailed==1 ){ fkTriggerDelete(db, pTrigger); return 0; } assert( pStep!=0 ); switch( action ){ case OE_Restrict: pStep->op = TK_SELECT; break; case OE_Cascade: if( !pChanges ){ pStep->op = TK_DELETE; break; } default: pStep->op = TK_UPDATE; } pStep->pTrig = pTrigger; pTrigger->pSchema = pTab->pSchema; pTrigger->pTabSchema = pTab->pSchema; pFKey->apTrigger[iAction] = pTrigger; pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE); } return pTrigger; }
/* ** This function is called to generate code executed when a row is deleted ** from the parent table of foreign key constraint pFKey and, if pFKey is ** deferred, when a row is inserted into the same table. When generating ** code for an SQL UPDATE operation, this function may be called twice - ** once to "delete" the old row and once to "insert" the new row. ** ** The code generated by this function scans through the rows in the child ** table that correspond to the parent table row being deleted or inserted. ** For each child row found, one of the following actions is taken: ** ** Operation | FK type | Action taken ** -------------------------------------------------------------------------- ** DELETE immediate Increment the "immediate constraint counter". ** Or, if the ON (UPDATE|DELETE) action is RESTRICT, ** throw a "foreign key constraint failed" exception. ** ** INSERT immediate Decrement the "immediate constraint counter". ** ** DELETE deferred Increment the "deferred constraint counter". ** Or, if the ON (UPDATE|DELETE) action is RESTRICT, ** throw a "foreign key constraint failed" exception. ** ** INSERT deferred Decrement the "deferred constraint counter". ** ** These operations are identified in the comment at the top of this file ** (fkey.c) as "I.2" and "D.2". */ static void fkScanChildren( Parse *pParse, /* Parse context */ SrcList *pSrc, /* SrcList containing the table to scan */ Table *pTab, Index *pIdx, /* Foreign key index */ FKey *pFKey, /* Foreign key relationship */ int *aiCol, /* Map from pIdx cols to child table cols */ int regData, /* Referenced table data starts here */ int nIncr /* Amount to increment deferred counter by */ ){ sqlite3 *db = pParse->db; /* Database handle */ int i; /* Iterator variable */ Expr *pWhere = 0; /* WHERE clause to scan with */ NameContext sNameContext; /* Context used to resolve WHERE clause */ WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */ int iFkIfZero = 0; /* Address of OP_FkIfZero */ Vdbe *v = sqlite3GetVdbe(pParse); assert( !pIdx || pIdx->pTable==pTab ); if( nIncr<0 ){ iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0); } /* Create an Expr object representing an SQL expression like: ** ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ... ** ** The collation sequence used for the comparison should be that of ** the parent key columns. The affinity of the parent key column should ** be applied to each child key value before the comparison takes place. */ for(i=0; i<pFKey->nCol; i++){ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ Expr *pEq; /* Expression (pLeft = pRight) */ int iCol; /* Index of column in child table */ const char *zCol; /* Name of column in child table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); if( pLeft ){ /* Set the collation sequence and affinity of the LHS of each TK_EQ ** expression to the parent key column defaults. */ if( pIdx ){ Column *pCol; iCol = pIdx->aiColumn[i]; pCol = &pTab->aCol[iCol]; if( pTab->iPKey==iCol ) iCol = -1; pLeft->iTable = regData+iCol+1; pLeft->affinity = pCol->affinity; pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl); }else{ pLeft->iTable = regData; pLeft->affinity = SQLITE_AFF_INTEGER; } } iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; assert( iCol>=0 ); zCol = pFKey->pFrom->aCol[iCol].zName; pRight = sqlite3Expr(db, TK_ID, zCol); pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0); pWhere = sqlite3ExprAnd(db, pWhere, pEq); } /* If the child table is the same as the parent table, and this scan ** is taking place as part of a DELETE operation (operation D.2), omit the ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE ** clause, where $rowid is the rowid of the row being deleted. */ if( pTab==pFKey->pFrom && nIncr>0 ){ Expr *pEq; /* Expression (pLeft = pRight) */ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); pRight = sqlite3Expr(db, TK_COLUMN, 0); if( pLeft && pRight ){ pLeft->iTable = regData; pLeft->affinity = SQLITE_AFF_INTEGER; pRight->iTable = pSrc->a[0].iCursor; pRight->iColumn = -1; } pEq = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0); pWhere = sqlite3ExprAnd(db, pWhere, pEq); } /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0); if( nIncr>0 && pFKey->isDeferred==0 ){ sqlite3ParseToplevel(pParse)->mayAbort = 1; } sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); if( pWInfo ){ sqlite3WhereEnd(pWInfo); } /* Clean up the WHERE clause constructed above. */ sqlite3ExprDelete(db, pWhere); if( iFkIfZero ){ sqlite3VdbeJumpHere(v, iFkIfZero); } }
/* ** The input to this routine is an WhereTerm structure with only the ** "pExpr" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the WhereTerm ** structure. ** ** If the expression is of the form "<expr> <op> X" it gets commuted ** to the standard form of "X <op> <expr>". ** ** If the expression is of the form "X <op> Y" where both X and Y are ** columns, then the original expression is unchanged and a new virtual ** term of the form "Y <op> X" is added to the WHERE clause and ** analyzed separately. The original term is marked with TERM_COPIED ** and the new term is marked with TERM_DYNAMIC (because it's pExpr ** needs to be freed with the WhereClause) and TERM_VIRTUAL (because it ** is a commuted copy of a prior term.) The original term has nChild=1 ** and the copy has idxParent set to the index of the original term. */ static void exprAnalyze( SrcList *pSrc, /* the FROM clause */ WhereClause *pWC, /* the WHERE clause */ int idxTerm /* Index of the term to be analyzed */ ){ WhereInfo *pWInfo = pWC->pWInfo; /* WHERE clause processing context */ WhereTerm *pTerm; /* The term to be analyzed */ WhereMaskSet *pMaskSet; /* Set of table index masks */ Expr *pExpr; /* The expression to be analyzed */ Bitmask prereqLeft; /* Prerequesites of the pExpr->pLeft */ Bitmask prereqAll; /* Prerequesites of pExpr */ Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */ Expr *pStr1 = 0; /* RHS of LIKE/GLOB operator */ int isComplete = 0; /* RHS of LIKE/GLOB ends with wildcard */ int noCase = 0; /* uppercase equivalent to lowercase */ int op; /* Top-level operator. pExpr->op */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection */ if( db->mallocFailed ){ return; } pTerm = &pWC->a[idxTerm]; pMaskSet = &pWInfo->sMaskSet; pExpr = pTerm->pExpr; assert( pExpr->op!=TK_AS && pExpr->op!=TK_COLLATE ); prereqLeft = sqlite3WhereExprUsage(pMaskSet, pExpr->pLeft); op = pExpr->op; if( op==TK_IN ){ assert( pExpr->pRight==0 ); if( ExprHasProperty(pExpr, EP_xIsSelect) ){ pTerm->prereqRight = exprSelectUsage(pMaskSet, pExpr->x.pSelect); }else{ pTerm->prereqRight = sqlite3WhereExprListUsage(pMaskSet, pExpr->x.pList); } }else if( op==TK_ISNULL ){ pTerm->prereqRight = 0; }else{ pTerm->prereqRight = sqlite3WhereExprUsage(pMaskSet, pExpr->pRight); } prereqAll = sqlite3WhereExprUsage(pMaskSet, pExpr); if( ExprHasProperty(pExpr, EP_FromJoin) ){ Bitmask x = sqlite3WhereGetMask(pMaskSet, pExpr->iRightJoinTable); prereqAll |= x; extraRight = x-1; /* ON clause terms may not be used with an index ** on left table of a LEFT JOIN. Ticket #3015 */ } pTerm->prereqAll = prereqAll; pTerm->leftCursor = -1; pTerm->iParent = -1; pTerm->eOperator = 0; if( allowedOp(op) ){ Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft); Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight); u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV; if( pLeft->op==TK_COLUMN ){ pTerm->leftCursor = pLeft->iTable; pTerm->u.leftColumn = pLeft->iColumn; pTerm->eOperator = operatorMask(op) & opMask; } if( op==TK_IS ) pTerm->wtFlags |= TERM_IS; if( pRight && pRight->op==TK_COLUMN ){ WhereTerm *pNew; Expr *pDup; u16 eExtraOp = 0; /* Extra bits for pNew->eOperator */ if( pTerm->leftCursor>=0 ){ int idxNew; pDup = sqlite3ExprDup(db, pExpr, 0); if( db->mallocFailed ){ sqlite3ExprDelete(db, pDup); return; } idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC); if( idxNew==0 ) return; pNew = &pWC->a[idxNew]; markTermAsChild(pWC, idxNew, idxTerm); if( op==TK_IS ) pNew->wtFlags |= TERM_IS; pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; if( termIsEquivalence(pParse, pDup) ){ pTerm->eOperator |= WO_EQUIV; eExtraOp = WO_EQUIV; } }else{ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); pLeft = sqlite3ExprSkipCollate(pDup->pLeft); pNew->leftCursor = pLeft->iTable; pNew->u.leftColumn = pLeft->iColumn; testcase( (prereqLeft | extraRight) != prereqLeft ); pNew->prereqRight = prereqLeft | extraRight; pNew->prereqAll = prereqAll; pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask; } } #ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION /* If a term is the BETWEEN operator, create two new virtual terms ** that define the range that the BETWEEN implements. For example: ** ** a BETWEEN b AND c ** ** is converted into: ** ** (a BETWEEN b AND c) AND (a>=b) AND (a<=c) ** ** The two new terms are added onto the end of the WhereClause object. ** The new terms are "dynamic" and are children of the original BETWEEN ** term. That means that if the BETWEEN term is coded, the children are ** skipped. Or, if the children are satisfied by an index, the original ** BETWEEN term is skipped. */ else if( pExpr->op==TK_BETWEEN && pWC->op==TK_AND ){ ExprList *pList = pExpr->x.pList; int i; static const u8 ops[] = {TK_GE, TK_LE}; assert( pList!=0 ); assert( pList->nExpr==2 ); for(i=0; i<2; i++){ Expr *pNewExpr; int idxNew; pNewExpr = sqlite3PExpr(pParse, ops[i], sqlite3ExprDup(db, pExpr->pLeft, 0), sqlite3ExprDup(db, pList->a[i].pExpr, 0), 0); transferJoinMarkings(pNewExpr, pExpr); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew==0 ); exprAnalyze(pSrc, pWC, idxNew); pTerm = &pWC->a[idxTerm]; markTermAsChild(pWC, idxNew, idxTerm); } } #endif /* SQLITE_OMIT_BETWEEN_OPTIMIZATION */ #if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY) /* Analyze a term that is composed of two or more subterms connected by ** an OR operator. */ else if( pExpr->op==TK_OR ){ assert( pWC->op==TK_AND ); exprAnalyzeOrTerm(pSrc, pWC, idxTerm); pTerm = &pWC->a[idxTerm]; } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* Add constraints to reduce the search space on a LIKE or GLOB ** operator. ** ** A like pattern of the form "x LIKE 'aBc%'" is changed into constraints ** ** x>='ABC' AND x<'abd' AND x LIKE 'aBc%' ** ** The last character of the prefix "abc" is incremented to form the ** termination condition "abd". If case is not significant (the default ** for LIKE) then the lower-bound is made all uppercase and the upper- ** bound is made all lowercase so that the bounds also work when comparing ** BLOBs. */ if( pWC->op==TK_AND && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase) ){ Expr *pLeft; /* LHS of LIKE/GLOB operator */ Expr *pStr2; /* Copy of pStr1 - RHS of LIKE/GLOB operator */ Expr *pNewExpr1; Expr *pNewExpr2; int idxNew1; int idxNew2; const char *zCollSeqName; /* Name of collating sequence */ const u16 wtFlags = TERM_LIKEOPT | TERM_VIRTUAL | TERM_DYNAMIC; pLeft = pExpr->x.pList->a[1].pExpr; pStr2 = sqlite3ExprDup(db, pStr1, 0); /* Convert the lower bound to upper-case and the upper bound to ** lower-case (upper-case is less than lower-case in ASCII) so that ** the range constraints also work for BLOBs */ if( noCase && !pParse->db->mallocFailed ){ int i; char c; pTerm->wtFlags |= TERM_LIKE; for(i=0; (c = pStr1->u.zToken[i])!=0; i++){ pStr1->u.zToken[i] = sqlite3Toupper(c); pStr2->u.zToken[i] = sqlite3Tolower(c); } } if( !db->mallocFailed ){ u8 c, *pC; /* Last character before the first wildcard */ pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1]; c = *pC; if( noCase ){ /* The point is to increment the last character before the first ** wildcard. But if we increment '@', that will push it into the ** alphabetic range where case conversions will mess up the ** inequality. To avoid this, make sure to also run the full ** LIKE on all candidate expressions by clearing the isComplete flag */ if( c=='A'-1 ) isComplete = 0; c = sqlite3UpperToLower[c]; } *pC = c + 1; } zCollSeqName = noCase ? "NOCASE" : "BINARY"; pNewExpr1 = sqlite3ExprDup(db, pLeft, 0); pNewExpr1 = sqlite3PExpr(pParse, TK_GE, sqlite3ExprAddCollateString(pParse,pNewExpr1,zCollSeqName), pStr1, 0); transferJoinMarkings(pNewExpr1, pExpr); idxNew1 = whereClauseInsert(pWC, pNewExpr1, wtFlags); testcase( idxNew1==0 ); exprAnalyze(pSrc, pWC, idxNew1); pNewExpr2 = sqlite3ExprDup(db, pLeft, 0); pNewExpr2 = sqlite3PExpr(pParse, TK_LT, sqlite3ExprAddCollateString(pParse,pNewExpr2,zCollSeqName), pStr2, 0); transferJoinMarkings(pNewExpr2, pExpr); idxNew2 = whereClauseInsert(pWC, pNewExpr2, wtFlags); testcase( idxNew2==0 ); exprAnalyze(pSrc, pWC, idxNew2); pTerm = &pWC->a[idxTerm]; if( isComplete ){ markTermAsChild(pWC, idxNew1, idxTerm); markTermAsChild(pWC, idxNew2, idxTerm); } } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ #ifndef SQLITE_OMIT_VIRTUALTABLE /* Add a WO_MATCH auxiliary term to the constraint set if the ** current expression is of the form: column MATCH expr. ** This information is used by the xBestIndex methods of ** virtual tables. The native query optimizer does not attempt ** to do anything with MATCH functions. */ if( isMatchOfColumn(pExpr) ){ int idxNew; Expr *pRight, *pLeft; WhereTerm *pNewTerm; Bitmask prereqColumn, prereqExpr; pRight = pExpr->x.pList->a[0].pExpr; pLeft = pExpr->x.pList->a[1].pExpr; prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight); prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft); if( (prereqExpr & prereqColumn)==0 ){ Expr *pNewExpr; pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 0, sqlite3ExprDup(db, pRight, 0), 0); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew==0 ); pNewTerm = &pWC->a[idxNew]; pNewTerm->prereqRight = prereqExpr; pNewTerm->leftCursor = pLeft->iTable; pNewTerm->u.leftColumn = pLeft->iColumn; pNewTerm->eOperator = WO_MATCH; markTermAsChild(pWC, idxNew, idxTerm); pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 /* When sqlite_stat3 histogram data is available an operator of the ** form "x IS NOT NULL" can sometimes be evaluated more efficiently ** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a ** virtual term of that form. ** ** Note that the virtual term must be tagged with TERM_VNULL. */ if( pExpr->op==TK_NOTNULL && pExpr->pLeft->op==TK_COLUMN && pExpr->pLeft->iColumn>=0 && OptimizationEnabled(db, SQLITE_Stat34) ){ Expr *pNewExpr; Expr *pLeft = pExpr->pLeft; int idxNew; WhereTerm *pNewTerm; pNewExpr = sqlite3PExpr(pParse, TK_GT, sqlite3ExprDup(db, pLeft, 0), sqlite3PExpr(pParse, TK_NULL, 0, 0, 0), 0); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL); if( idxNew ){ pNewTerm = &pWC->a[idxNew]; pNewTerm->prereqRight = 0; pNewTerm->leftCursor = pLeft->iTable; pNewTerm->u.leftColumn = pLeft->iColumn; pNewTerm->eOperator = WO_GT; markTermAsChild(pWC, idxNew, idxTerm); pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ /* Prevent ON clause terms of a LEFT JOIN from being used to drive ** an index for tables to the left of the join. */ pTerm->prereqRight |= extraRight; }
/* ** Create and populate a new TriggerPrg object with a sub-program ** implementing trigger pTrigger with ON CONFLICT policy orconf. */ static TriggerPrg *codeRowTrigger( Parse *pParse, /* Current parse context */ Trigger *pTrigger, /* Trigger to code */ Table *pTab, /* The table pTrigger is attached to */ int orconf /* ON CONFLICT policy to code trigger program with */ ){ Parse *pTop = sqlite3ParseToplevel(pParse); sqlite3 *db = pParse->db; /* Database handle */ TriggerPrg *pPrg; /* Value to return */ Expr *pWhen = 0; /* Duplicate of trigger WHEN expression */ Vdbe *v; /* Temporary VM */ NameContext sNC; /* Name context for sub-vdbe */ SubProgram *pProgram = 0; /* Sub-vdbe for trigger program */ Parse *pSubParse; /* Parse context for sub-vdbe */ int iEndTrigger = 0; /* Label to jump to if WHEN is false */ assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) ); assert( pTop->pVdbe ); /* Allocate the TriggerPrg and SubProgram objects. To ensure that they ** are freed if an error occurs, link them into the Parse.pTriggerPrg ** list of the top-level Parse object sooner rather than later. */ pPrg = sqlite3DbMallocZero(db, sizeof(TriggerPrg)); if( !pPrg ) return 0; pPrg->pNext = pTop->pTriggerPrg; pTop->pTriggerPrg = pPrg; pPrg->pProgram = pProgram = sqlite3DbMallocZero(db, sizeof(SubProgram)); if( !pProgram ) return 0; sqlite3VdbeLinkSubProgram(pTop->pVdbe, pProgram); pPrg->pTrigger = pTrigger; pPrg->orconf = orconf; pPrg->aColmask[0] = 0xffffffff; pPrg->aColmask[1] = 0xffffffff; /* Allocate and populate a new Parse context to use for coding the ** trigger sub-program. */ pSubParse = sqlite3StackAllocZero(db, sizeof(Parse)); if( !pSubParse ) return 0; memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pSubParse; pSubParse->db = db; pSubParse->pTriggerTab = pTab; pSubParse->pToplevel = pTop; pSubParse->zAuthContext = pTrigger->zName; pSubParse->eTriggerOp = pTrigger->op; pSubParse->nQueryLoop = pParse->nQueryLoop; v = sqlite3GetVdbe(pSubParse); if( v ){ VdbeComment((v, "Start: %s.%s (%s %s%s%s ON %s)", pTrigger->zName, onErrorText(orconf), (pTrigger->tr_tm==TRIGGER_BEFORE ? "BEFORE" : "AFTER"), (pTrigger->op==TK_UPDATE ? "UPDATE" : ""), (pTrigger->op==TK_INSERT ? "INSERT" : ""), (pTrigger->op==TK_DELETE ? "DELETE" : ""), pTab->zName )); #ifndef SQLITE_OMIT_TRACE sqlite3VdbeChangeP4(v, -1, sqlite3MPrintf(db, "-- TRIGGER %s", pTrigger->zName), P4_DYNAMIC ); #endif /* If one was specified, code the WHEN clause. If it evaluates to false ** (or NULL) the sub-vdbe is immediately halted by jumping to the ** OP_Halt inserted at the end of the program. */ if( pTrigger->pWhen ){ pWhen = sqlite3ExprDup(db, pTrigger->pWhen, 0); if( SQLITE_OK==sqlite3ResolveExprNames(&sNC, pWhen) && db->mallocFailed==0 ){ iEndTrigger = sqlite3VdbeMakeLabel(v); sqlite3ExprIfFalse(pSubParse, pWhen, iEndTrigger, SQLITE_JUMPIFNULL); } sqlite3ExprDelete(db, pWhen); } /* Code the trigger program into the sub-vdbe. */ codeTriggerProgram(pSubParse, pTrigger->step_list, orconf); /* Insert an OP_Halt at the end of the sub-program. */ if( iEndTrigger ){ sqlite3VdbeResolveLabel(v, iEndTrigger); } sqlite3VdbeAddOp0(v, OP_Halt); VdbeComment((v, "End: %s.%s", pTrigger->zName, onErrorText(orconf))); transferParseError(pParse, pSubParse); if( db->mallocFailed==0 ){ pProgram->aOp = sqlite3VdbeTakeOpArray(v, &pProgram->nOp, &pTop->nMaxArg); } pProgram->nMem = pSubParse->nMem; pProgram->nCsr = pSubParse->nTab; pProgram->nOnce = pSubParse->nOnce; pProgram->token = (void *)pTrigger; pPrg->aColmask[0] = pSubParse->oldmask; pPrg->aColmask[1] = pSubParse->newmask; sqlite3VdbeDelete(v); } assert( !pSubParse->pAinc && !pSubParse->pZombieTab ); assert( !pSubParse->pTriggerPrg && !pSubParse->nMaxArg ); sqlite3StackFree(db, pSubParse); return pPrg; }
/* ** 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 */ const char *zDb; /* Name of database holding pTab */ int end, addr = 0; /* 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 */ sqlite3 *db; /* Main database structure */ AuthContext sContext; /* Authorization context */ int oldIdx = -1; /* Cursor for the OLD table of AFTER triggers */ 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 */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to delete from a view */ int triggers_exist = 0; /* True if any triggers exist */ #endif int iBeginAfterTrigger = 0; /* Address of after trigger program */ int iEndAfterTrigger = 0; /* Exit of after trigger program */ int iBeginBeforeTrigger = 0; /* Address of before trigger program */ int iEndBeforeTrigger = 0; /* Exit of before trigger program */ u32 old_col_mask = 0; /* Mask of OLD.* columns in use */ sContext.pParse = 0; 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 triggers_exist = sqlite3TriggersExist(pTab, TK_DELETE, 0); isView = pTab->pSelect!=0; #else # define triggers_exist 0 # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){ goto delete_from_cleanup; } iDb = sqlite3SchemaToIndex(db, pTab->pSchema); assert( iDb<db->nDb ); zDb = db->aDb[iDb].zName; rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb); assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE ); if( rcauth==SQLITE_DENY ){ goto delete_from_cleanup; } assert(!isView || triggers_exist); /* If pTab is really a view, make sure it has been initialized. */ if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto delete_from_cleanup; } /* Allocate a cursor used to store the old.* data for a trigger. */ if( triggers_exist ){ oldIdx = pParse->nTab++; } /* Assign cursor number to the table and all its indices. */ assert( pTabList->nSrc==1 ); iCur = pTabList->a[0].iCursor = pParse->nTab++; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 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, triggers_exist, iDb); if( triggers_exist ){ int orconf = ((pParse->trigStack)?pParse->trigStack->orconf:OE_Default); int iGoto = sqlite3VdbeAddOp0(v, OP_Goto); addr = sqlite3VdbeMakeLabel(v); iBeginBeforeTrigger = sqlite3VdbeCurrentAddr(v); (void)sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TRIGGER_BEFORE, pTab, -1, oldIdx, orconf, addr, &old_col_mask, 0); iEndBeforeTrigger = sqlite3VdbeAddOp0(v, OP_Goto); iBeginAfterTrigger = sqlite3VdbeCurrentAddr(v); (void)sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, oldIdx, orconf, addr, &old_col_mask, 0); iEndAfterTrigger = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, iGoto); } /* If we are trying to delete from a view, realize that view into ** a ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); } #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. Note, however, that ** this means that the row change count will be incorrect. */ if( rcauth==SQLITE_OK && pWhere==0 && !triggers_exist && !IsVirtual(pTab) ){ assert( !isView ); sqlite3VdbeAddOp3(v, OP_Clear, pTab->tnum, iDb, memCnt); if( !pParse->nested ){ sqlite3VdbeChangeP4(v, -1, 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 */ /* The usual case: There is a WHERE clause so we have to scan through ** the table and pick which records to delete. */ { int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ /* Begin the database scan */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0); if( pWInfo==0 ) goto delete_from_cleanup; /* Remember the rowid of every item to be deleted. */ sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid, iCur, iRowid); sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iRowid); if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); /* Open the pseudo-table used to store OLD if there are triggers. */ if( triggers_exist ){ sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pTab->nCol); sqlite3VdbeAddOp1(v, OP_OpenPseudo, oldIdx); } /* 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. */ end = sqlite3VdbeMakeLabel(v); if( !isView ){ /* Open cursors for the table we are deleting from and ** all its indices. */ sqlite3OpenTableAndIndices(pParse, pTab, iCur, OP_OpenWrite); } /* This is the beginning of the delete loop. If a trigger encounters ** an IGNORE constraint, it jumps back to here. */ if( triggers_exist ){ sqlite3VdbeResolveLabel(v, addr); } addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, iRowid); if( triggers_exist ){ int iData = ++pParse->nMem; /* For storing row data of OLD table */ /* If the record is no longer present in the table, jump to the ** next iteration of the loop through the contents of the fifo. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, iRowid); /* Populate the OLD.* pseudo-table */ if( old_col_mask ){ sqlite3VdbeAddOp2(v, OP_RowData, iCur, iData); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, iData); } sqlite3VdbeAddOp3(v, OP_Insert, oldIdx, iData, iRowid); /* Jump back and run the BEFORE triggers */ sqlite3VdbeAddOp2(v, OP_Goto, 0, iBeginBeforeTrigger); sqlite3VdbeJumpHere(v, iEndBeforeTrigger); } if( !isView ){ /* Delete the row */ #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ const char *pVtab = (const char *)pTab->pVtab; sqlite3VtabMakeWritable(pParse, pTab); sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVtab, P4_VTAB); }else #endif { sqlite3GenerateRowDelete(pParse, pTab, iCur, iRowid, pParse->nested==0); } } /* If there are row triggers, close all cursors then invoke ** the AFTER triggers */ if( triggers_exist ){ /* Jump back and run the AFTER triggers */ sqlite3VdbeAddOp2(v, OP_Goto, 0, iBeginAfterTrigger); sqlite3VdbeJumpHere(v, iEndAfterTrigger); } /* End of the delete loop */ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeResolveLabel(v, end); /* Close the cursors after the loop if there are no row triggers */ if( !isView && !IsVirtual(pTab) ){ for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqlite3VdbeAddOp2(v, OP_Close, iCur + i, pIdx->tnum); } sqlite3VdbeAddOp1(v, OP_Close, iCur); } } /* ** 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==0 && !pParse->trigStack ){ 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); return; }
/* ** Generate an expression tree to implement the WHERE, ORDER BY, ** and LIMIT/OFFSET portion of DELETE and UPDATE statements. ** ** DELETE FROM table_wxyz WHERE a<5 ORDER BY a LIMIT 1; ** \__________________________/ ** pLimitWhere (pInClause) */ Expr *sqlite3LimitWhere( Parse *pParse, /* The parser context */ SrcList *pSrc, /* the FROM clause -- which tables to scan */ Expr *pWhere, /* The WHERE clause. May be null */ ExprList *pOrderBy, /* The ORDER BY clause. May be null */ Expr *pLimit, /* The LIMIT clause. May be null */ char *zStmtType /* Either DELETE or UPDATE. For err msgs. */ ){ sqlite3 *db = pParse->db; Expr *pLhs = NULL; /* LHS of IN(SELECT...) operator */ Expr *pInClause = NULL; /* WHERE rowid IN ( select ) */ ExprList *pEList = NULL; /* Expression list contaning only pSelectRowid */ SrcList *pSelectSrc = NULL; /* SELECT rowid FROM x ... (dup of pSrc) */ Select *pSelect = NULL; /* Complete SELECT tree */ Table *pTab; /* Check that there isn't an ORDER BY without a LIMIT clause. */ if( pOrderBy && pLimit==0 ) { sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType); sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pOrderBy); return 0; } /* We only need to generate a select expression if there ** is a limit/offset term to enforce. */ if( pLimit == 0 ) { return pWhere; } /* Generate a select expression tree to enforce the limit/offset ** term for the DELETE or UPDATE statement. For example: ** DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** becomes: ** DELETE FROM table_a WHERE rowid IN ( ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** ); */ pTab = pSrc->a[0].pTab; if( HasRowid(pTab) ){ pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0); pEList = sqlite3ExprListAppend( pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0) ); }else{ Index *pPk = sqlite3PrimaryKeyIndex(pTab); if( pPk->nKeyCol==1 ){ const char *zName = pTab->aCol[pPk->aiColumn[0]].zName; pLhs = sqlite3Expr(db, TK_ID, zName); pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName)); }else{ int i; for(i=0; i<pPk->nKeyCol; i++){ Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName); pEList = sqlite3ExprListAppend(pParse, pEList, p); } pLhs = sqlite3PExpr(pParse, TK_VECTOR, 0, 0); if( pLhs ){ pLhs->x.pList = sqlite3ExprListDup(db, pEList, 0); } } } /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ pSrc->a[0].pTab = 0; pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0); pSrc->a[0].pTab = pTab; pSrc->a[0].pIBIndex = 0; /* generate the SELECT expression tree. */ pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0, pOrderBy,0,pLimit ); /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */ pInClause = sqlite3PExpr(pParse, TK_IN, pLhs, 0); sqlite3PExprAddSelect(pParse, pInClause, pSelect); return pInClause; }
void sqlite3Update( Parse *pParse, SrcList *pTabList, ExprList *pChanges, Expr *pWhere, int onError ){ int i, j; Table *pTab; int addr = 0; WhereInfo *pWInfo; Vdbe *v; Index *pIdx; int nIdx; int iCur; sqlite3 *db; int *aRegIdx = 0; int *aXRef = 0; int chngRowid; Expr *pRowidExpr = 0; int openAll = 0; AuthContext sContext; NameContext sNC; int iDb; int okOnePass; int hasFK; #ifndef SQLITE_OMIT_TRIGGER int isView; Trigger *pTrigger; int tmask; #endif int newmask; int regRowCount = 0; int regOldRowid; int regNewRowid; int regNew; int regOld = 0; int regRowSet = 0; memset(&sContext, 0, sizeof(sContext)); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto update_cleanup; } assert( pTabList->nSrc==1 ); pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); #ifndef SQLITE_OMIT_TRIGGER pTrigger = sqlite3TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask); isView = pTab->pSelect!=0; assert( pTrigger || tmask==0 ); #else # define pTrigger 0 # define isView 0 # define tmask 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ goto update_cleanup; } aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol ); if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; pTabList->a[0].iCursor = iCur = pParse->nTab++; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ pParse->nTab++; } memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; chngRowid = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ if( j==pTab->iPKey ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pChanges->a[i].zName) ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); pParse->checkSchema = 1; goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, pTab->aCol[j].zName, db->aDb[iDb].zName); if( rc==SQLITE_DENY ){ goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngRowid); for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} if( nIdx>0 ){ aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx ); if( aRegIdx==0 ) goto update_cleanup; } for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngRowid ){ reg = ++pParse->nMem; }else{ reg = 0; for(i=0; i<pIdx->nColumn; i++){ if( aXRef[pIdx->aiColumn[i]]>=0 ){ reg = ++pParse->nMem; break; } } } aRegIdx[j] = reg; } v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere); pWhere = 0; pTabList = 0; goto update_cleanup; } #endif regOldRowid = regNewRowid = ++pParse->nMem; if( pTrigger || hasFK ){ regOld = pParse->nMem + 1; pParse->nMem += pTab->nCol; } if( chngRowid || pTrigger || hasFK ){ regNewRowid = ++pParse->nMem; } regNew = pParse->nMem + 1; pParse->nMem += pTab->nCol; if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); } #endif if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere,0, WHERE_ONEPASS_DESIRED); if( pWInfo==0 ) goto update_cleanup; okOnePass = pWInfo->okOnePass; sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid); if( !okOnePass ){ regRowSet = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); } sqlite3WhereEnd(pWInfo); if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } if( !isView ){ if( !okOnePass ) sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); if( onError==OE_Replace ){ openAll = 1; }else{ openAll = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_Replace ){ openAll = 1; break; } } } for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aRegIdx[i]>0 ){ KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); sqlite3VdbeAddOp4(v, OP_OpenWrite, iCur+i+1, pIdx->tnum, iDb, (char*)pKey, P4_KEYINFO_HANDOFF); assert( pParse->nTab>iCur+i+1 ); } } } if( okOnePass ){ int a1 = sqlite3VdbeAddOp1(v, OP_NotNull, regOldRowid); addr = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, a1); }else{ addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid); } sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid ); if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } if( hasFK || pTrigger ){ u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); oldmask |= sqlite3TriggerColmask(pParse, pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 || oldmask==0xffffffff || (i<32 && (oldmask & (1<<i))) ){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, i, regOld+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); } } if( chngRowid==0 ){ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid); } } newmask = sqlite3TriggerColmask( pParse, pTrigger, pChanges, 1, TRIGGER_BEFORE, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask&(1<<i)) ){ testcase( i==31 ); testcase( i==32 ); sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regNew+i); sqlite3ColumnDefault(v, pTab, i, regNew+i); } } } if( tmask&TRIGGER_BEFORE ){ sqlite3VdbeAddOp2(v, OP_Affinity, regNew, pTab->nCol); sqlite3TableAffinityStr(v, pTab); sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, regOldRowid, onError, addr); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 && i!=pTab->iPKey ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regNew+i); sqlite3ColumnDefault(v, pTab, i, regNew+i); } } } if( !isView ){ int j1; sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); if( hasFK ){ sqlite3FkCheck(pParse, pTab, regOldRowid, 0); } j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); if( hasFK || chngRowid ){ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); if( hasFK ){ sqlite3FkCheck(pParse, pTab, 0, regNewRowid); } sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); } } if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, addr); sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeJumpHere(v, addr); for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aRegIdx[i]>0 ){ sqlite3VdbeAddOp2(v, OP_Close, iCur+i+1, 0); } } sqlite3VdbeAddOp2(v, OP_Close, iCur, 0); if( pParse->nested==0 && pParse->pTriggerTab==0 ){ sqlite3AutoincrementEnd(pParse); } if( (db->flags&SQLITE_CountRows) && !pParse->pTriggerTab && !pParse->nested ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); sqlite3DbFree(db, aRegIdx); sqlite3DbFree(db, aXRef); sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pChanges); sqlite3ExprDelete(db, pWhere); return; }
/* ** 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 */ const char *zDb; /* Name of database holding pTab */ int end, addr = 0; /* 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 */ 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 */ #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; #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 ); zDb = db->aDb[iDb].zName; rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb); assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE ); if( rcauth==SQLITE_DENY ){ goto delete_from_cleanup; } assert(!isView || pTrigger); /* Assign cursor number to the table and all its indices. */ assert( pTabList->nSrc==1 ); iCur = pTabList->a[0].iCursor = pParse->nTab++; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 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 ** a ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); } #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 && !pTrigger && !IsVirtual(pTab) && 0==sqlite3FkRequired(pParse, pTab, 0, 0) ){ assert( !isView ); 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 */ /* The usual case: There is a WHERE clause so we have to scan through ** the table and pick which records to delete. */ { int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int regRowid; /* Actual register containing rowids */ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK, 0 ); if( pWInfo==0 ) goto delete_from_cleanup; regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid, 0); sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid); if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } sqlite3WhereEnd(pWInfo); /* 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. */ end = sqlite3VdbeMakeLabel(v); /* 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 ){ sqlite3OpenTableAndIndices(pParse, pTab, iCur, OP_OpenWrite); } addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, iRowid); /* 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, iRowid, pVTab, P4_VTAB); sqlite3VdbeChangeP5(v, OE_Abort); sqlite3MayAbort(pParse); }else #endif { int count = (pParse->nested==0); /* True to count changes */ sqlite3GenerateRowDelete(pParse, pTab, iCur, iRowid, count, pTrigger, OE_Default); } /* End of the delete loop */ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeResolveLabel(v, end); /* Close the cursors open on the table and its indexes. */ if( !isView && !IsVirtual(pTab) ){ for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqlite3VdbeAddOp2(v, OP_Close, iCur + i, pIdx->tnum); } sqlite3VdbeAddOp1(v, OP_Close, iCur); } } /* 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); return; }
/* ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up ** that name in the set of source tables in pSrcList and make the pExpr ** expression node refer back to that source column. The following changes ** are made to pExpr: ** ** pExpr->iDb Set the index in db->aDb[] of the database X ** (even if X is implied). ** pExpr->iTable Set to the cursor number for the table obtained ** from pSrcList. ** pExpr->pTab Points to the Table structure of X.Y (even if ** X and/or Y are implied.) ** pExpr->iColumn Set to the column number within the table. ** pExpr->op Set to TK_COLUMN. ** pExpr->pLeft Any expression this points to is deleted ** pExpr->pRight Any expression this points to is deleted. ** ** The zDb variable is the name of the database (the "X"). This value may be ** NULL meaning that name is of the form Y.Z or Z. Any available database ** can be used. The zTable variable is the name of the table (the "Y"). This ** value can be NULL if zDb is also NULL. If zTable is NULL it ** means that the form of the name is Z and that columns from any table ** can be used. ** ** If the name cannot be resolved unambiguously, leave an error message ** in pParse and return WRC_Abort. Return WRC_Prune on success. */ static int lookupName( Parse *pParse, /* The parsing context */ const char *zDb, /* Name of the database containing table, or NULL */ const char *zTab, /* Name of table containing column, or NULL */ const char *zCol, /* Name of the column. */ NameContext *pNC, /* The name context used to resolve the name */ Expr *pExpr /* Make this EXPR node point to the selected column */ ){ int i, j; /* Loop counters */ int cnt = 0; /* Number of matching column names */ int cntTab = 0; /* Number of matching table names */ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ int isTrigger = 0; assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ assert( ~ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) ); /* Initialize the node to no-match */ pExpr->iTable = -1; pExpr->pTab = 0; ExprSetIrreducible(pExpr); /* Start at the inner-most context and move outward until a match is found */ while( pNC && cnt==0 ){ ExprList *pEList; SrcList *pSrcList = pNC->pSrcList; if( pSrcList ){ for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){ Table *pTab; int iDb; Column *pCol; pTab = pItem->pTab; assert( pTab!=0 && pTab->zName!=0 ); iDb = sqlite3SchemaToIndex(db, pTab->pSchema); assert( pTab->nCol>0 ); if( zTab ){ if( pItem->zAlias ){ char *zTabName = pItem->zAlias; if( sqlite3StrICmp(zTabName, zTab)!=0 ) continue; }else{ char *zTabName = pTab->zName; if( NEVER(zTabName==0) || sqlite3StrICmp(zTabName, zTab)!=0 ){ continue; } if( zDb!=0 && sqlite3StrICmp(db->aDb[iDb].zName, zDb)!=0 ){ continue; } } } if( 0==(cntTab++) ){ pExpr->iTable = pItem->iCursor; pExpr->pTab = pTab; pSchema = pTab->pSchema; pMatch = pItem; } for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){ if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ IdList *pUsing; cnt++; pExpr->iTable = pItem->iCursor; pExpr->pTab = pTab; pMatch = pItem; pSchema = pTab->pSchema; /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */ pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j; if( i<pSrcList->nSrc-1 ){ if( pItem[1].jointype & JT_NATURAL ){ /* If this match occurred in the left table of a natural join, ** then skip the right table to avoid a duplicate match */ pItem++; i++; }else if( (pUsing = pItem[1].pUsing)!=0 ){ /* If this match occurs on a column that is in the USING clause ** of a join, skip the search of the right table of the join ** to avoid a duplicate match there. */ int k; for(k=0; k<pUsing->nId; k++){ if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){ pItem++; i++; break; } } } } break; } } } } #ifndef SQLITE_OMIT_TRIGGER /* If we have not already resolved the name, then maybe ** it is a new.* or old.* trigger argument reference */ if( zDb==0 && zTab!=0 && cnt==0 && pParse->pTriggerTab!=0 ){ int op = pParse->eTriggerOp; Table *pTab = 0; assert( op==TK_DELETE || op==TK_UPDATE || op==TK_INSERT ); if( op!=TK_DELETE && sqlite3StrICmp("new",zTab) == 0 ){ pExpr->iTable = 1; pTab = pParse->pTriggerTab; }else if( op!=TK_INSERT && sqlite3StrICmp("old",zTab)==0 ){ pExpr->iTable = 0; pTab = pParse->pTriggerTab; } if( pTab ){ int iCol; pSchema = pTab->pSchema; cntTab++; for(iCol=0; iCol<pTab->nCol; iCol++){ Column *pCol = &pTab->aCol[iCol]; if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ if( iCol==pTab->iPKey ){ iCol = -1; } break; } } if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) ){ iCol = -1; /* IMP: R-44911-55124 */ } if( iCol<pTab->nCol ){ cnt++; if( iCol<0 ){ pExpr->affinity = SQLITE_AFF_INTEGER; }else if( pExpr->iTable==0 ){ testcase( iCol==31 ); testcase( iCol==32 ); pParse->oldmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); }else{ testcase( iCol==31 ); testcase( iCol==32 ); pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<<iCol)); } pExpr->iColumn = (i16)iCol; pExpr->pTab = pTab; isTrigger = 1; } } } #endif /* !defined(SQLITE_OMIT_TRIGGER) */ /* ** Perhaps the name is a reference to the ROWID */ if( cnt==0 && cntTab==1 && sqlite3IsRowid(zCol) ){ cnt = 1; pExpr->iColumn = -1; /* IMP: R-44911-55124 */ pExpr->affinity = SQLITE_AFF_INTEGER; } /* ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z ** might refer to an result-set alias. This happens, for example, when ** we are resolving names in the WHERE clause of the following command: ** ** SELECT a+b AS x FROM table WHERE x<10; ** ** In cases like this, replace pExpr with a copy of the expression that ** forms the result set entry ("a+b" in the example) and return immediately. ** Note that the expression in the result set should have already been ** resolved by the time the WHERE clause is resolved. */ if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){ for(j=0; j<pEList->nExpr; j++){ char *zAs = pEList->a[j].zName; if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){ Expr *pOrig; assert( pExpr->pLeft==0 && pExpr->pRight==0 ); assert( pExpr->x.pList==0 ); assert( pExpr->x.pSelect==0 ); pOrig = pEList->a[j].pExpr; if( !pNC->allowAgg && ExprHasProperty(pOrig, EP_Agg) ){ sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs); return WRC_Abort; } resolveAlias(pParse, pEList, j, pExpr, ""); cnt = 1; pMatch = 0; assert( zTab==0 && zDb==0 ); goto lookupname_end; } } } /* Advance to the next name context. The loop will exit when either ** we have a match (cnt>0) or when we run out of name contexts. */ if( cnt==0 ){ pNC = pNC->pNext; } } /* ** If X and Y are NULL (in other words if only the column name Z is ** supplied) and the value of Z is enclosed in double-quotes, then ** Z is a string literal if it doesn't match any column names. In that ** case, we need to return right away and not make any changes to ** pExpr. ** ** Because no reference was made to outer contexts, the pNC->nRef ** fields are not changed in any context. */ if( cnt==0 && zTab==0 && ExprHasProperty(pExpr,EP_DblQuoted) ){ pExpr->op = TK_STRING; pExpr->pTab = 0; return WRC_Prune; } /* ** cnt==0 means there was not match. cnt>1 means there were two or ** more matches. Either way, we have an error. */ if( cnt!=1 ){ const char *zErr; zErr = cnt==0 ? "no such column" : "ambiguous column name"; if( zDb ){ sqlite3ErrorMsg(pParse, "%s: %s.%s.%s", zErr, zDb, zTab, zCol); }else if( zTab ){ sqlite3ErrorMsg(pParse, "%s: %s.%s", zErr, zTab, zCol); }else{ sqlite3ErrorMsg(pParse, "%s: %s", zErr, zCol); } pTopNC->nErr++; } /* If a column from a table in pSrcList is referenced, then record ** this fact in the pSrcList.a[].colUsed bitmask. Column 0 causes ** bit 0 to be set. Column 1 sets bit 1. And so forth. If the ** column number is greater than the number of bits in the bitmask ** then set the high-order bit of the bitmask. */ if( pExpr->iColumn>=0 && pMatch!=0 ){ int n = pExpr->iColumn; testcase( n==BMS-1 ); if( n>=BMS ){ n = BMS-1; } assert( pMatch->iCursor==pExpr->iTable ); pMatch->colUsed |= ((Bitmask)1)<<n; } /* Clean up and return */ sqlite3ExprDelete(db, pExpr->pLeft); pExpr->pLeft = 0; sqlite3ExprDelete(db, pExpr->pRight); pExpr->pRight = 0; pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN); lookupname_end: if( cnt==1 ){ assert( pNC!=0 ); sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList); /* Increment the nRef value on all name contexts from TopNC up to ** the point where the name matched. */ for(;;){ assert( pTopNC!=0 ); pTopNC->nRef++; if( pTopNC==pNC ) break; pTopNC = pTopNC->pNext; } return WRC_Prune; } else { return WRC_Abort; } }
/* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere */ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ Expr *pWhere, /* The WHERE clause. May be null */ int onError /* How to handle constraint errors */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ int addr = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ Vdbe *v; /* The virtual database engine */ Index *pIdx; /* For looping over indices */ int nIdx; /* Number of indices that need updating */ int nIdxTotal; /* Total number of indices */ int iCur; /* VDBE Cursor number of pTab */ sqlite3 *db; /* The database structure */ Index **apIdx = 0; /* An array of indices that need updating too */ char *aIdxUsed = 0; /* aIdxUsed[i]==1 if the i-th index is used */ int *aXRef = 0; /* aXRef[i] is the index in pChanges->a[] of the ** an expression for the i-th column of the table. ** aXRef[i]==-1 if the i-th column is not changed. */ int chngRecno; /* True if the record number is being changed */ Expr *pRecnoExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* Trying to update a view */ int triggers_exist = 0; /* True if any row triggers exist */ #endif int newIdx = -1; /* index of trigger "new" temp table */ int oldIdx = -1; /* index of trigger "old" temp table */ sContext.pParse = 0; if( pParse->nErr || sqlite3_malloc_failed ) goto update_cleanup; db = pParse->db; assert( pTabList->nSrc==1 ); /* Locate the table which we want to update. */ pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; /* Figure out if we have any triggers and if the table being ** updated is a view */ #ifndef SQLITE_OMIT_TRIGGER triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_UPDATE, pChanges); isView = pTab->pSelect!=0; #else # define triggers_exist 0 # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){ goto update_cleanup; } if( isView ){ if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } } aXRef = sqliteMallocRaw( sizeof(int) * pTab->nCol ); if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* If there are FOR EACH ROW triggers, allocate cursors for the ** special OLD and NEW tables */ if( triggers_exist ){ newIdx = pParse->nTab++; oldIdx = pParse->nTab++; } /* Allocate a cursors for the main database table and for all indices. ** The index cursors might not be used, but if they are used they ** need to occur right after the database cursor. So go ahead and ** allocate enough space, just in case. */ pTabList->a[0].iCursor = iCur = pParse->nTab++; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ pParse->nTab++; } /* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRecno = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqlite3ExprResolveNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ if( j==pTab->iPKey ){ chngRecno = 1; pRecnoExpr = pChanges->a[i].pExpr; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( sqlite3IsRowid(pChanges->a[i].zName) ){ chngRecno = 1; pRecnoExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, pTab->aCol[j].zName, db->aDb[pTab->iDb].zName); if( rc==SQLITE_DENY ){ goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } /* Allocate memory for the array apIdx[] and fill it with pointers to every ** index that needs to be updated. Indices only need updating if their ** key includes one of the columns named in pChanges or if the record ** number of the original table entry is changing. */ for(nIdx=nIdxTotal=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdxTotal++){ if( chngRecno ){ i = 0; }else { for(i=0; i<pIdx->nColumn; i++){ if( aXRef[pIdx->aiColumn[i]]>=0 ) break; } } if( i<pIdx->nColumn ) nIdx++; } if( nIdxTotal>0 ){ apIdx = sqliteMallocRaw( sizeof(Index*) * nIdx + nIdxTotal ); if( apIdx==0 ) goto update_cleanup; aIdxUsed = (char*)&apIdx[nIdx]; } for(nIdx=j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ if( chngRecno ){ i = 0; }else{ for(i=0; i<pIdx->nColumn; i++){ if( aXRef[pIdx->aiColumn[i]]>=0 ) break; } } if( i<pIdx->nColumn ){ if( sqlite3CheckIndexCollSeq(pParse, pIdx) ) goto update_cleanup; apIdx[nIdx++] = pIdx; aIdxUsed[j] = 1; }else{ aIdxUsed[j] = 0; } } /* Resolve the column names in all the expressions in the ** WHERE clause. */ if( sqlite3ExprResolveNames(&sNC, pWhere) ){ goto update_cleanup; } /* Start the view context */ if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, pTab->iDb); /* If we are trying to update a view, construct that view into ** a temporary table. */ if( isView ){ Select *pView; pView = sqlite3SelectDup(pTab->pSelect); sqlite3Select(pParse, pView, SRT_TempTable, iCur, 0, 0, 0, 0); sqlite3SelectDelete(pView); } /* Begin the database scan */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0); if( pWInfo==0 ) goto update_cleanup; /* Remember the index of every item to be updated. */ sqlite3VdbeAddOp(v, OP_Recno, iCur, 0); sqlite3VdbeAddOp(v, OP_ListWrite, 0, 0); /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); /* Initialize the count of updated rows */ if( db->flags & SQLITE_CountRows && !pParse->trigStack ){ sqlite3VdbeAddOp(v, OP_Integer, 0, 0); } if( triggers_exist ){ /* Create pseudo-tables for NEW and OLD */ sqlite3VdbeAddOp(v, OP_OpenPseudo, oldIdx, 0); sqlite3VdbeAddOp(v, OP_SetNumColumns, oldIdx, pTab->nCol); sqlite3VdbeAddOp(v, OP_OpenPseudo, newIdx, 0); sqlite3VdbeAddOp(v, OP_SetNumColumns, newIdx, pTab->nCol); /* The top of the update loop for when there are triggers. */ sqlite3VdbeAddOp(v, OP_ListRewind, 0, 0); addr = sqlite3VdbeAddOp(v, OP_ListRead, 0, 0); sqlite3VdbeAddOp(v, OP_Dup, 0, 0); /* Open a cursor and make it point to the record that is ** being updated. */ sqlite3VdbeAddOp(v, OP_Dup, 0, 0); if( !isView ){ sqlite3OpenTableForReading(v, iCur, pTab); } sqlite3VdbeAddOp(v, OP_MoveGe, iCur, 0); /* Generate the OLD table */ sqlite3VdbeAddOp(v, OP_Recno, iCur, 0); sqlite3VdbeAddOp(v, OP_RowData, iCur, 0); sqlite3VdbeAddOp(v, OP_PutIntKey, oldIdx, 0); /* Generate the NEW table */ if( chngRecno ){ sqlite3ExprCodeAndCache(pParse, pRecnoExpr); }else{ sqlite3VdbeAddOp(v, OP_Recno, iCur, 0); } for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp(v, OP_String8, 0, 0); continue; } j = aXRef[i]; if( j<0 ){ sqlite3VdbeAddOp(v, OP_Column, iCur, i); sqlite3ColumnDefault(v, pTab, i); }else{ sqlite3ExprCodeAndCache(pParse, pChanges->a[j].pExpr); } } sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); if( !isView ){ sqlite3TableAffinityStr(v, pTab); } if( pParse->nErr ) goto update_cleanup; sqlite3VdbeAddOp(v, OP_PutIntKey, newIdx, 0); if( !isView ){ sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } /* Fire the BEFORE and INSTEAD OF triggers */ if( sqlite3CodeRowTrigger(pParse, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, newIdx, oldIdx, onError, addr) ){ goto update_cleanup; } } if( !isView ){ /* ** Open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0); sqlite3VdbeAddOp(v, OP_OpenWrite, iCur, pTab->tnum); sqlite3VdbeAddOp(v, OP_SetNumColumns, iCur, pTab->nCol); if( onError==OE_Replace ){ openAll = 1; }else{ openAll = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_Replace ){ openAll = 1; break; } } } for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aIdxUsed[i] ){ sqlite3VdbeAddOp(v, OP_Integer, pIdx->iDb, 0); sqlite3VdbeOp3(v, OP_OpenWrite, iCur+i+1, pIdx->tnum, (char*)&pIdx->keyInfo, P3_KEYINFO); assert( pParse->nTab>iCur+i+1 ); } } /* Loop over every record that needs updating. We have to load ** the old data for each record to be updated because some columns ** might not change and we will need to copy the old value. ** Also, the old data is needed to delete the old index entires. ** So make the cursor point at the old record. */ if( !triggers_exist ){ sqlite3VdbeAddOp(v, OP_ListRewind, 0, 0); addr = sqlite3VdbeAddOp(v, OP_ListRead, 0, 0); sqlite3VdbeAddOp(v, OP_Dup, 0, 0); } sqlite3VdbeAddOp(v, OP_NotExists, iCur, addr); /* If the record number will change, push the record number as it ** will be after the update. (The old record number is currently ** on top of the stack.) */ if( chngRecno ){ sqlite3ExprCode(pParse, pRecnoExpr); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); } /* Compute new data for this record. */ for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp(v, OP_String8, 0, 0); continue; } j = aXRef[i]; if( j<0 ){ sqlite3VdbeAddOp(v, OP_Column, iCur, i); sqlite3ColumnDefault(v, pTab, i); }else{ sqlite3ExprCode(pParse, pChanges->a[j].pExpr); } } /* Do constraint checks */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, aIdxUsed, chngRecno, 1, onError, addr); /* Delete the old indices for the current record. */ sqlite3GenerateRowIndexDelete(db, v, pTab, iCur, aIdxUsed); /* If changing the record number, delete the old record. */ if( chngRecno ){ sqlite3VdbeAddOp(v, OP_Delete, iCur, 0); } /* Create the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iCur, aIdxUsed, chngRecno, 1, -1); } /* Increment the row counter */ if( db->flags & SQLITE_CountRows && !pParse->trigStack){ sqlite3VdbeAddOp(v, OP_AddImm, 1, 0); } /* If there are triggers, close all the cursors after each iteration ** through the loop. The fire the after triggers. */ if( triggers_exist ){ if( !isView ){ for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aIdxUsed[i] ) sqlite3VdbeAddOp(v, OP_Close, iCur+i+1, 0); } sqlite3VdbeAddOp(v, OP_Close, iCur, 0); } if( sqlite3CodeRowTrigger(pParse, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, newIdx, oldIdx, onError, addr) ){ goto update_cleanup; } } /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ sqlite3VdbeAddOp(v, OP_Goto, 0, addr); sqlite3VdbeChangeP2(v, addr, sqlite3VdbeCurrentAddr(v)); sqlite3VdbeAddOp(v, OP_ListReset, 0, 0); /* Close all tables if there were no FOR EACH ROW triggers */ if( !triggers_exist ){ for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aIdxUsed[i] ){ sqlite3VdbeAddOp(v, OP_Close, iCur+i+1, 0); } } sqlite3VdbeAddOp(v, OP_Close, iCur, 0); }else{ sqlite3VdbeAddOp(v, OP_Close, newIdx, 0); sqlite3VdbeAddOp(v, OP_Close, oldIdx, 0); } /* ** Return the number of rows that were changed. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( db->flags & SQLITE_CountRows && !pParse->trigStack && pParse->nested==0 ){ sqlite3VdbeAddOp(v, OP_Callback, 1, 0); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, "rows updated", P3_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); sqliteFree(apIdx); sqliteFree(aXRef); sqlite3SrcListDelete(pTabList); sqlite3ExprListDelete(pChanges); sqlite3ExprDelete(pWhere); return; }
/* ** Analyze the ORDER BY clause in a compound SELECT statement. Modify ** each term of the ORDER BY clause is a constant integer between 1 ** and N where N is the number of columns in the compound SELECT. ** ** ORDER BY terms that are already an integer between 1 and N are ** unmodified. ORDER BY terms that are integers outside the range of ** 1 through N generate an error. ORDER BY terms that are expressions ** are matched against result set expressions of compound SELECT ** beginning with the left-most SELECT and working toward the right. ** At the first match, the ORDER BY expression is transformed into ** the integer column number. ** ** Return the number of errors seen. */ static int resolveCompoundOrderBy( Parse *pParse, /* Parsing context. Leave error messages here */ Select *pSelect /* The SELECT statement containing the ORDER BY */ ){ int i; ExprList *pOrderBy; ExprList *pEList; sqlite3 *db; int moreToDo = 1; pOrderBy = pSelect->pOrderBy; if( pOrderBy==0 ) return 0; db = pParse->db; #if SQLITE_MAX_COLUMN if( pOrderBy->nExpr>db->aLimit[SQLITE_LIMIT_COLUMN] ){ sqlite3ErrorMsg(pParse, "too many terms in ORDER BY clause"); return 1; } #endif for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].done = 0; } pSelect->pNext = 0; while( pSelect->pPrior ){ pSelect->pPrior->pNext = pSelect; pSelect = pSelect->pPrior; } while( pSelect && moreToDo ){ struct ExprList_item *pItem; moreToDo = 0; pEList = pSelect->pEList; assert( pEList!=0 ); for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ int iCol = -1; Expr *pE, *pDup; if( pItem->done ) continue; pE = pItem->pExpr; if( sqlite3ExprIsInteger(pE, &iCol) ){ if( iCol<=0 || iCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr); return 1; } }else{ iCol = resolveAsName(pParse, pEList, pE); if( iCol==0 ){ pDup = sqlite3ExprDup(db, pE, 0); if( !db->mallocFailed ){ assert(pDup); iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup); } sqlite3ExprDelete(db, pDup); } } if( iCol>0 ){ CollSeq *pColl = pE->pColl; int flags = pE->flags & EP_ExpCollate; sqlite3ExprDelete(db, pE); pItem->pExpr = pE = sqlite3Expr(db, TK_INTEGER, 0); if( pE==0 ) return 1; pE->pColl = pColl; pE->flags |= EP_IntValue | flags; pE->u.iValue = iCol; pItem->iCol = (u16)iCol; pItem->done = 1; }else{ moreToDo = 1; } } pSelect = pSelect->pNext; } for(i=0; i<pOrderBy->nExpr; i++){ if( pOrderBy->a[i].done==0 ){ sqlite3ErrorMsg(pParse, "%r ORDER BY term does not match any " "column in the result set", i+1); return 1; } } return 0; }
/* ** This procedure generates VDBE code for a single invocation of either the ** sqlite_detach() or sqlite_attach() SQL user functions. */ static void codeAttach( Parse *pParse, /* The parser context */ int type, /* Either SQLITE_ATTACH or SQLITE_DETACH */ FuncDef *pFunc, /* FuncDef wrapper for detachFunc() or attachFunc() */ Expr *pAuthArg, /* Expression to pass to authorization callback */ Expr *pFilename, /* Name of database file */ Expr *pDbname, /* Name of the database to use internally */ Expr *pKey /* Database key for encryption extension */ ){ int rc; NameContext sName; Vdbe *v; sqlite3* db = pParse->db; int regArgs; memset(&sName, 0, sizeof(NameContext)); sName.pParse = pParse; if( SQLITE_OK!=(rc = resolveAttachExpr(&sName, pFilename)) || SQLITE_OK!=(rc = resolveAttachExpr(&sName, pDbname)) || SQLITE_OK!=(rc = resolveAttachExpr(&sName, pKey)) ){ pParse->nErr++; goto attach_end; } #ifndef SQLITE_OMIT_AUTHORIZATION if( pAuthArg ){ char *zAuthArg = pAuthArg->u.zToken; if( NEVER(zAuthArg==0) ){ goto attach_end; } rc = sqlite3AuthCheck(pParse, type, zAuthArg, 0, 0); if(rc!=SQLITE_OK ){ goto attach_end; } } #endif /* SQLITE_OMIT_AUTHORIZATION */ v = sqlite3GetVdbe(pParse); regArgs = sqlite3GetTempRange(pParse, 4); sqlite3ExprCode(pParse, pFilename, regArgs); sqlite3ExprCode(pParse, pDbname, regArgs+1); sqlite3ExprCode(pParse, pKey, regArgs+2); assert( v || db->mallocFailed ); if( v ){ sqlite3VdbeAddOp3(v, OP_Function, 0, regArgs+3-pFunc->nArg, regArgs+3); assert( pFunc->nArg==-1 || (pFunc->nArg&0xff)==pFunc->nArg ); sqlite3VdbeChangeP5(v, (u8)(pFunc->nArg)); sqlite3VdbeChangeP4(v, -1, (char *)pFunc, P4_FUNCDEF); /* Code an OP_Expire. For an ATTACH statement, set P1 to true (expire this ** statement only). For DETACH, set it to false (expire all existing ** statements). */ sqlite3VdbeAddOp1(v, OP_Expire, (type==SQLITE_ATTACH)); } attach_end: sqlite3ExprDelete(db, pFilename); sqlite3ExprDelete(db, pDbname); sqlite3ExprDelete(db, pKey); }
/* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere */ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ Expr *pWhere, /* The WHERE clause. May be null */ int onError, /* How to handle constraint errors */ ExprList *pOrderBy, /* ORDER BY clause. May be null */ Expr *pLimit, /* LIMIT clause. May be null */ Upsert *pUpsert /* ON CONFLICT clause, or null */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ int addrTop = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ Vdbe *v; /* The virtual database engine */ Index *pIdx; /* For looping over indices */ Index *pPk; /* The PRIMARY KEY index for WITHOUT ROWID tables */ int nIdx; /* Number of indices that need updating */ int iBaseCur; /* Base cursor number */ int iDataCur; /* Cursor for the canonical data btree */ int iIdxCur; /* Cursor for the first index */ sqlite3 *db; /* The database structure */ int *aRegIdx = 0; /* First register in array assigned to each index */ int *aXRef = 0; /* aXRef[i] is the index in pChanges->a[] of the ** an expression for the i-th column of the table. ** aXRef[i]==-1 if the i-th column is not changed. */ u8 *aToOpen; /* 1 for tables and indices to be opened */ u8 chngPk; /* PRIMARY KEY changed in a WITHOUT ROWID table */ u8 chngRowid; /* Rowid changed in a normal table */ u8 chngKey; /* Either chngPk or chngRowid */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int eOnePass; /* ONEPASS_XXX value from where.c */ int hasFK; /* True if foreign key processing is required */ int labelBreak; /* Jump here to break out of UPDATE loop */ int labelContinue; /* Jump here to continue next step of UPDATE loop */ int flags; /* Flags for sqlite3WhereBegin() */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True when updating a view (INSTEAD OF trigger) */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */ #endif int newmask; /* Mask of NEW.* columns accessed by BEFORE triggers */ int iEph = 0; /* Ephemeral table holding all primary key values */ int nKey = 0; /* Number of elements in regKey for WITHOUT ROWID */ int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */ int addrOpen = 0; /* Address of OP_OpenEphemeral */ int iPk = 0; /* First of nPk cells holding PRIMARY KEY value */ i16 nPk = 0; /* Number of components of the PRIMARY KEY */ int bReplace = 0; /* True if REPLACE conflict resolution might happen */ /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid = 0; /* The old rowid */ int regNewRowid = 0; /* The new rowid */ int regNew = 0; /* Content of the NEW.* table in triggers */ int regOld = 0; /* Content of OLD.* table in triggers */ int regRowSet = 0; /* Rowset of rows to be updated */ int regKey = 0; /* composite PRIMARY KEY value */ memset(&sContext, 0, sizeof(sContext)); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto update_cleanup; } assert( pTabList->nSrc==1 ); /* Locate the table which we want to update. */ pTab = sqlite3SrcListLookup(pParse, pTabList); if( pTab==0 ) goto update_cleanup; iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); /* Figure out if we have any triggers and if the table being ** updated is a view. */ #ifndef SQLITE_OMIT_TRIGGER pTrigger = sqlite3TriggersExist(pParse, pTab, TK_UPDATE, pChanges, &tmask); isView = pTab->pSelect!=0; assert( pTrigger || tmask==0 ); #else # define pTrigger 0 # define isView 0 # define tmask 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT if( !isView ){ pWhere = sqlite3LimitWhere( pParse, pTabList, pWhere, pOrderBy, pLimit, "UPDATE" ); pOrderBy = 0; pLimit = 0; } #endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ goto update_cleanup; } /* Allocate a cursors for the main database table and for all indices. ** The index cursors might not be used, but if they are used they ** need to occur right after the database cursor. So go ahead and ** allocate enough space, just in case. */ iBaseCur = iDataCur = pParse->nTab++; iIdxCur = iDataCur+1; pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab); testcase( pPk!=0 && pPk!=pTab->pIndex ); for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){ if( pPk==pIdx ){ iDataCur = pParse->nTab; } pParse->nTab++; } if( pUpsert ){ /* On an UPSERT, reuse the same cursors already opened by INSERT */ iDataCur = pUpsert->iDataCur; iIdxCur = pUpsert->iIdxCur; pParse->nTab = iBaseCur; } pTabList->a[0].iCursor = iDataCur; /* Allocate space for aXRef[], aRegIdx[], and aToOpen[]. ** Initialize aXRef[] and aToOpen[] to their default values. */ aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 ); if( aXRef==0 ) goto update_cleanup; aRegIdx = aXRef+pTab->nCol; aToOpen = (u8*)(aRegIdx+nIdx); memset(aToOpen, 1, nIdx+1); aToOpen[nIdx+1] = 0; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* Initialize the name-context */ memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; sNC.pSrcList = pTabList; sNC.uNC.pUpsert = pUpsert; sNC.ncFlags = NC_UUpsert; /* Resolve the column names in all the expressions of the ** of the UPDATE statement. Also find the column index ** for each column to be updated in the pChanges array. For each ** column to be updated, make sure we have authorization to change ** that column. */ chngRowid = chngPk = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqlite3ResolveExprNames(&sNC, pChanges->a[i].pExpr) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqlite3StrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ if( j==pTab->iPKey ){ chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else if( pPk && (pTab->aCol[j].colFlags & COLFLAG_PRIMKEY)!=0 ){ chngPk = 1; } aXRef[j] = i; break; } } if( j>=pTab->nCol ){ if( pPk==0 && sqlite3IsRowid(pChanges->a[i].zName) ){ j = -1; chngRowid = 1; pRowidExpr = pChanges->a[i].pExpr; }else{ sqlite3ErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName); pParse->checkSchema = 1; goto update_cleanup; } } #ifndef SQLITE_OMIT_AUTHORIZATION { int rc; rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName, j<0 ? "ROWID" : pTab->aCol[j].zName, db->aDb[iDb].zDbSName); if( rc==SQLITE_DENY ){ goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } assert( (chngRowid & chngPk)==0 ); assert( chngRowid==0 || chngRowid==1 ); assert( chngPk==0 || chngPk==1 ); chngKey = chngRowid + chngPk; /* The SET expressions are not actually used inside the WHERE loop. ** So reset the colUsed mask. Unless this is a virtual table. In that ** case, set all bits of the colUsed mask (to ensure that the virtual ** table implementation makes all columns available). */ pTabList->a[0].colUsed = IsVirtual(pTab) ? ALLBITS : 0; hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey); /* There is one entry in the aRegIdx[] array for each index on the table ** being updated. Fill in aRegIdx[] with a register number that will hold ** the key for accessing each index. */ if( onError==OE_Replace ) bReplace = 1; for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngKey || hasFK>1 || pIdx==pPk || indexWhereClauseMightChange(pIdx,aXRef,chngRowid) ){ reg = ++pParse->nMem; pParse->nMem += pIdx->nColumn; }else{ reg = 0; for(i=0; i<pIdx->nKeyCol; i++){ if( indexColumnIsBeingUpdated(pIdx, i, aXRef, chngRowid) ){ reg = ++pParse->nMem; pParse->nMem += pIdx->nColumn; if( onError==OE_Default && pIdx->onError==OE_Replace ){ bReplace = 1; } break; } } } if( reg==0 ) aToOpen[j+1] = 0; aRegIdx[j] = reg; } if( bReplace ){ /* If REPLACE conflict resolution might be invoked, open cursors on all ** indexes in case they are needed to delete records. */ memset(aToOpen, 1, nIdx+1); } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, pTrigger || hasFK, iDb); /* Allocate required registers. */ if( !IsVirtual(pTab) ){ regRowSet = ++pParse->nMem; regOldRowid = regNewRowid = ++pParse->nMem; if( chngPk || pTrigger || hasFK ){ regOld = pParse->nMem + 1; pParse->nMem += pTab->nCol; } if( chngKey || pTrigger || hasFK ){ regNewRowid = ++pParse->nMem; } regNew = pParse->nMem + 1; pParse->nMem += pTab->nCol; } /* Start the view context. */ if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } /* If we are trying to update a view, realize that view into ** an ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, pOrderBy, pLimit, iDataCur ); pOrderBy = 0; pLimit = 0; } #endif /* Resolve the column names in all the expressions in the ** WHERE clause. */ if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } #ifndef SQLITE_OMIT_VIRTUALTABLE /* Virtual tables must be handled separately */ if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere, onError); goto update_cleanup; } #endif /* Jump to labelBreak to abandon further processing of this UPDATE */ labelContinue = labelBreak = sqlite3VdbeMakeLabel(pParse); /* Not an UPSERT. Normal processing. Begin by ** initialize the count of updated rows */ if( (db->flags&SQLITE_CountRows)!=0 && !pParse->pTriggerTab && !pParse->nested && pUpsert==0 ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } if( HasRowid(pTab) ){ sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); }else{ assert( pPk!=0 ); nPk = pPk->nKeyCol; iPk = pParse->nMem+1; pParse->nMem += nPk; regKey = ++pParse->nMem; if( pUpsert==0 ){ iEph = pParse->nTab++; sqlite3VdbeAddOp3(v, OP_Null, 0, iPk, iPk+nPk-1); addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } } if( pUpsert ){ /* If this is an UPSERT, then all cursors have already been opened by ** the outer INSERT and the data cursor should be pointing at the row ** that is to be updated. So bypass the code that searches for the ** row(s) to be updated. */ pWInfo = 0; eOnePass = ONEPASS_SINGLE; sqlite3ExprIfFalse(pParse, pWhere, labelBreak, SQLITE_JUMPIFNULL); }else{ /* Begin the database scan. ** ** Do not consider a single-pass strategy for a multi-row update if ** there are any triggers or foreign keys to process, or rows may ** be deleted as a result of REPLACE conflict handling. Any of these ** things might disturb a cursor being used to scan through the table ** or index, causing a single-pass approach to malfunction. */ flags = WHERE_ONEPASS_DESIRED|WHERE_SEEK_UNIQ_TABLE; if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){ flags |= WHERE_ONEPASS_MULTIROW; } pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur); if( pWInfo==0 ) goto update_cleanup; /* A one-pass strategy that might update more than one row may not ** be used if any column of the index used for the scan is being ** updated. Otherwise, if there is an index on "b", statements like ** the following could create an infinite loop: ** ** UPDATE t1 SET b=b+1 WHERE b>? ** ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI ** strategy that uses an index for which one or more columns are being ** updated. */ eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); if( eOnePass!=ONEPASS_SINGLE ){ sqlite3MultiWrite(pParse); if( eOnePass==ONEPASS_MULTI ){ int iCur = aiCurOnePass[1]; if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){ eOnePass = ONEPASS_OFF; } assert( iCur!=iDataCur || !HasRowid(pTab) ); } } } if( HasRowid(pTab) ){ /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF ** mode, write the rowid into the FIFO. In either of the one-pass modes, ** leave it in register regOldRowid. */ sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid); if( eOnePass==ONEPASS_OFF ){ sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); } }else{ /* Read the PK of the current row into an array of registers. In ** ONEPASS_OFF mode, serialize the array into a record and store it in ** the ephemeral table. Or, in ONEPASS_SINGLE or MULTI mode, change ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table ** is not required) and leave the PK fields in the array of registers. */ for(i=0; i<nPk; i++){ assert( pPk->aiColumn[i]>=0 ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur,pPk->aiColumn[i],iPk+i); } if( eOnePass ){ if( addrOpen ) sqlite3VdbeChangeToNoop(v, addrOpen); nKey = nPk; regKey = iPk; }else{ sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey, sqlite3IndexAffinityStr(db, pPk), nPk); sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk); } } if( pUpsert==0 ){ if( eOnePass!=ONEPASS_MULTI ){ sqlite3WhereEnd(pWInfo); } if( !isView ){ int addrOnce = 0; /* Open every index that needs updating. */ if( eOnePass!=ONEPASS_OFF ){ if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0; if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0; } if( eOnePass==ONEPASS_MULTI && (nIdx-(aiCurOnePass[1]>=0))>0 ){ addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); } sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, iBaseCur, aToOpen, 0, 0); if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce); } /* Top of the update loop */ if( eOnePass!=ONEPASS_OFF ){ if( !isView && aiCurOnePass[0]!=iDataCur && aiCurOnePass[1]!=iDataCur ){ assert( pPk ); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey,nKey); VdbeCoverage(v); } if( eOnePass!=ONEPASS_SINGLE ){ labelContinue = sqlite3VdbeMakeLabel(pParse); } sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak); VdbeCoverageIf(v, pPk==0); VdbeCoverageIf(v, pPk!=0); }else if( pPk ){ labelContinue = sqlite3VdbeMakeLabel(pParse); sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v); addrTop = sqlite3VdbeAddOp2(v, OP_RowData, iEph, regKey); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0); VdbeCoverage(v); }else{ labelContinue = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet,labelBreak, regOldRowid); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } } /* If the rowid value will change, set register regNewRowid to ** contain the new value. If the rowid is not being modified, ** then regNewRowid is the same register as regOldRowid, which is ** already populated. */ assert( chngKey || pTrigger || hasFK || regOldRowid==regNewRowid ); if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); VdbeCoverage(v); } /* Compute the old pre-UPDATE content of the row being changed, if that ** information is needed */ if( chngPk || hasFK || pTrigger ){ u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); oldmask |= sqlite3TriggerColmask(pParse, pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( oldmask==0xffffffff || (i<32 && (oldmask & MASKBIT32(i))!=0) || (pTab->aCol[i].colFlags & COLFLAG_PRIMKEY)!=0 ){ testcase( oldmask!=0xffffffff && i==31 ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regOld+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); } } if( chngRowid==0 && pPk==0 ){ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid); } } /* Populate the array of registers beginning at regNew with the new ** row data. This array is used to check constants, create the new ** table and index records, and as the values for any new.* references ** made by triggers. ** ** If there are one or more BEFORE triggers, then do not populate the ** registers associated with columns that are (a) not modified by ** this UPDATE statement and (b) not accessed by new.* references. The ** values for registers not modified by the UPDATE must be reloaded from ** the database after the BEFORE triggers are fired anyway (as the trigger ** may have modified them). So not loading those that are not going to ** be used eliminates some redundant opcodes. */ newmask = sqlite3TriggerColmask( pParse, pTrigger, pChanges, 1, TRIGGER_BEFORE, pTab, onError ); for(i=0; i<pTab->nCol; i++){ if( i==pTab->iPKey ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); }else{ j = aXRef[i]; if( j>=0 ){ sqlite3ExprCode(pParse, pChanges->a[j].pExpr, regNew+i); }else if( 0==(tmask&TRIGGER_BEFORE) || i>31 || (newmask & MASKBIT32(i)) ){ /* This branch loads the value of a column that will not be changed ** into a register. This is done if there are no BEFORE triggers, or ** if there are one or more BEFORE triggers that use this value via ** a new.* reference in a trigger program. */ testcase( i==31 ); testcase( i==32 ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regNew+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regNew+i); } } } /* Fire any BEFORE UPDATE triggers. This happens before constraints are ** verified. One could argue that this is wrong. */ if( tmask&TRIGGER_BEFORE ){ sqlite3TableAffinity(v, pTab, regNew); sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_BEFORE, pTab, regOldRowid, onError, labelContinue); /* The row-trigger may have deleted the row being updated. In this ** case, jump to the next row. No updates or AFTER triggers are ** required. This behavior - what happens when the row being updated ** is deleted or renamed by a BEFORE trigger - is left undefined in the ** documentation. */ if( pPk ){ sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue,regKey,nKey); VdbeCoverage(v); }else{ sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, labelContinue, regOldRowid); VdbeCoverage(v); } /* After-BEFORE-trigger-reload-loop: ** If it did not delete it, the BEFORE trigger may still have modified ** some of the columns of the row being updated. Load the values for ** all columns not modified by the update statement into their registers ** in case this has happened. Only unmodified columns are reloaded. ** The values computed for modified columns use the values before the ** BEFORE trigger runs. See test case trigger1-18.0 (added 2018-04-26) ** for an example. */ for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 && i!=pTab->iPKey ){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regNew+i); } } } if( !isView ){ int addr1 = 0; /* Address of jump instruction */ /* Do constraint checks. */ assert( regOldRowid>0 ); sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace, aXRef, 0); /* Do FK constraint checks. */ if( hasFK ){ sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey); } /* Delete the index entries associated with the current record. */ if( bReplace || chngKey ){ if( pPk ){ addr1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, 0, regKey, nKey); }else{ addr1 = sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, 0, regOldRowid); } VdbeCoverageNeverTaken(v); } sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx, -1); /* If changing the rowid value, or if there are foreign key constraints ** to process, delete the old record. Otherwise, add a noop OP_Delete ** to invoke the pre-update hook. ** ** That (regNew==regnewRowid+1) is true is also important for the ** pre-update hook. If the caller invokes preupdate_new(), the returned ** value is copied from memory cell (regNewRowid+1+iCol), where iCol ** is the column index supplied by the user. */ assert( regNew==regNewRowid+1 ); #ifdef SQLITE_ENABLE_PREUPDATE_HOOK sqlite3VdbeAddOp3(v, OP_Delete, iDataCur, OPFLAG_ISUPDATE | ((hasFK>1 || chngKey) ? 0 : OPFLAG_ISNOOP), regNewRowid ); if( eOnePass==ONEPASS_MULTI ){ assert( hasFK==0 && chngKey==0 ); sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION); } if( !pParse->nested ){ sqlite3VdbeAppendP4(v, pTab, P4_TABLE); } #else if( hasFK>1 || chngKey ){ sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, 0); } #endif if( bReplace || chngKey ){ sqlite3VdbeJumpHere(v, addr1); } if( hasFK ){ sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey); } /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion( pParse, pTab, iDataCur, iIdxCur, regNewRowid, aRegIdx, OPFLAG_ISUPDATE | (eOnePass==ONEPASS_MULTI ? OPFLAG_SAVEPOSITION : 0), 0, 0 ); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just updated. */ if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); } } /* Increment the row counter */ if( regRowCount ){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue); /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ if( eOnePass==ONEPASS_SINGLE ){ /* Nothing to do at end-of-loop for a single-pass */ }else if( eOnePass==ONEPASS_MULTI ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3WhereEnd(pWInfo); }else if( pPk ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop); VdbeCoverage(v); }else{ sqlite3VdbeGoto(v, labelContinue); } sqlite3VdbeResolveLabel(v, labelBreak); /* 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 && pUpsert==0 ){ sqlite3AutoincrementEnd(pParse); } /* ** Return the number of rows that were changed, if we are tracking ** that information. */ if( regRowCount ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */ sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pChanges); sqlite3ExprDelete(db, pWhere); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) sqlite3ExprListDelete(db, pOrderBy); sqlite3ExprDelete(db, pLimit); #endif return; }
/* ** This is called to code FOR EACH ROW triggers. ** ** When the code that this function generates is executed, the following ** must be true: ** ** 1. No cursors may be open in the main database. (But newIdx and oldIdx ** can be indices of cursors in temporary tables. See below.) ** ** 2. If the triggers being coded are ON INSERT or ON UPDATE triggers, then ** a temporary vdbe cursor (index newIdx) must be open and pointing at ** a row containing values to be substituted for new.* expressions in the ** trigger program(s). ** ** 3. If the triggers being coded are ON DELETE or ON UPDATE triggers, then ** a temporary vdbe cursor (index oldIdx) must be open and pointing at ** a row containing values to be substituted for old.* expressions in the ** trigger program(s). ** */ int sqlite3CodeRowTrigger( Parse *pParse, /* Parse context */ int op, /* One of TK_UPDATE, TK_INSERT, TK_DELETE */ ExprList *pChanges, /* Changes list for any UPDATE OF triggers */ int tr_tm, /* One of TK_BEFORE, TK_AFTER */ Table *pTab, /* The table to code triggers from */ int newIdx, /* The indice of the "new" row to access */ int oldIdx, /* The indice of the "old" row to access */ int orconf, /* ON CONFLICT policy */ int ignoreJump /* Instruction to jump to for RAISE(IGNORE) */ ){ Trigger * pTrigger; TriggerStack * pTriggerStack; assert(op == TK_UPDATE || op == TK_INSERT || op == TK_DELETE); assert(tr_tm == TK_BEFORE || tr_tm == TK_AFTER ); assert(newIdx != -1 || oldIdx != -1); pTrigger = pTab->pTrigger; while( pTrigger ){ int fire_this = 0; /* determine whether we should code this trigger */ if( pTrigger->op == op && pTrigger->tr_tm == tr_tm && pTrigger->foreach == TK_ROW ){ fire_this = 1; pTriggerStack = pParse->trigStack; while( pTriggerStack ){ if( pTriggerStack->pTrigger == pTrigger ){ fire_this = 0; } pTriggerStack = pTriggerStack->pNext; } if( op == TK_UPDATE && pTrigger->pColumns && !checkColumnOverLap(pTrigger->pColumns, pChanges) ){ fire_this = 0; } } if( fire_this && (pTriggerStack = sqliteMalloc(sizeof(TriggerStack)))!=0 ){ int endTrigger; SrcList dummyTablist; Expr * whenExpr; AuthContext sContext; dummyTablist.nSrc = 0; /* Push an entry on to the trigger stack */ pTriggerStack->pTrigger = pTrigger; pTriggerStack->newIdx = newIdx; pTriggerStack->oldIdx = oldIdx; pTriggerStack->pTab = pTab; pTriggerStack->pNext = pParse->trigStack; pTriggerStack->ignoreJump = ignoreJump; pParse->trigStack = pTriggerStack; sqlite3AuthContextPush(pParse, &sContext, pTrigger->name); /* code the WHEN clause */ endTrigger = sqlite3VdbeMakeLabel(pParse->pVdbe); whenExpr = sqlite3ExprDup(pTrigger->pWhen); if( sqlite3ExprResolveIds(pParse, &dummyTablist, 0, whenExpr) ){ pParse->trigStack = pParse->trigStack->pNext; sqliteFree(pTriggerStack); sqlite3ExprDelete(whenExpr); return 1; } sqlite3ExprIfFalse(pParse, whenExpr, endTrigger, 1); sqlite3ExprDelete(whenExpr); sqlite3VdbeAddOp(pParse->pVdbe, OP_ContextPush, 0, 0); codeTriggerProgram(pParse, pTrigger->step_list, orconf); sqlite3VdbeAddOp(pParse->pVdbe, OP_ContextPop, 0, 0); /* Pop the entry off the trigger stack */ pParse->trigStack = pParse->trigStack->pNext; sqlite3AuthContextPop(&sContext); sqliteFree(pTriggerStack); sqlite3VdbeResolveLabel(pParse->pVdbe, endTrigger); } pTrigger = pTrigger->pNext; } return 0; }
/* ** This is called to code FOR EACH ROW triggers. ** ** When the code that this function generates is executed, the following ** must be true: ** ** 1. No cursors may be open in the main database. (But newIdx and oldIdx ** can be indices of cursors in temporary tables. See below.) ** ** 2. If the triggers being coded are ON INSERT or ON UPDATE triggers, then ** a temporary vdbe cursor (index newIdx) must be open and pointing at ** a row containing values to be substituted for new.* expressions in the ** trigger program(s). ** ** 3. If the triggers being coded are ON DELETE or ON UPDATE triggers, then ** a temporary vdbe cursor (index oldIdx) must be open and pointing at ** a row containing values to be substituted for old.* expressions in the ** trigger program(s). ** */ int sqlite3CodeRowTrigger( Parse *pParse, /* Parse context */ int op, /* One of TK_UPDATE, TK_INSERT, TK_DELETE */ ExprList *pChanges, /* Changes list for any UPDATE OF triggers */ int tr_tm, /* One of TRIGGER_BEFORE, TRIGGER_AFTER */ Table *pTab, /* The table to code triggers from */ int newIdx, /* The indice of the "new" row to access */ int oldIdx, /* The indice of the "old" row to access */ int orconf, /* ON CONFLICT policy */ int ignoreJump /* Instruction to jump to for RAISE(IGNORE) */ ){ Trigger *p; TriggerStack trigStackEntry; assert(op == TK_UPDATE || op == TK_INSERT || op == TK_DELETE); assert(tr_tm == TRIGGER_BEFORE || tr_tm == TRIGGER_AFTER ); assert(newIdx != -1 || oldIdx != -1); for(p=pTab->pTrigger; p; p=p->pNext){ int fire_this = 0; sqlite3 *db = pParse->db; /* Determine whether we should code this trigger */ if( p->op==op && p->tr_tm==tr_tm && (p->pSchema==p->pTabSchema || p->pSchema==db->aDb[1].pSchema) && (op!=TK_UPDATE||!p->pColumns||checkColumnOverLap(p->pColumns,pChanges)) ){ TriggerStack *pS; /* Pointer to trigger-stack entry */ for(pS=pParse->trigStack; pS && p!=pS->pTrigger; pS=pS->pNext){} if( !pS ){ fire_this = 1; } #if 0 /* Give no warning for recursive triggers. Just do not do them */ else{ sqlite3ErrorMsg(pParse, "recursive triggers not supported (%s)", p->name); return SQLITE_ERROR; } #endif } if( fire_this ){ int endTrigger; Expr * whenExpr; AuthContext sContext; NameContext sNC; memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; /* Push an entry on to the trigger stack */ trigStackEntry.pTrigger = p; trigStackEntry.newIdx = newIdx; trigStackEntry.oldIdx = oldIdx; trigStackEntry.pTab = pTab; trigStackEntry.pNext = pParse->trigStack; trigStackEntry.ignoreJump = ignoreJump; pParse->trigStack = &trigStackEntry; sqlite3AuthContextPush(pParse, &sContext, p->name); /* code the WHEN clause */ endTrigger = sqlite3VdbeMakeLabel(pParse->pVdbe); whenExpr = sqlite3ExprDup(db, p->pWhen); if( db->mallocFailed || sqlite3ExprResolveNames(&sNC, whenExpr) ){ pParse->trigStack = trigStackEntry.pNext; sqlite3ExprDelete(whenExpr); return 1; } sqlite3ExprIfFalse(pParse, whenExpr, endTrigger, 1); sqlite3ExprDelete(whenExpr); codeTriggerProgram(pParse, p->step_list, orconf); /* Pop the entry off the trigger stack */ pParse->trigStack = trigStackEntry.pNext; sqlite3AuthContextPop(&sContext); sqlite3VdbeResolveLabel(pParse->pVdbe, endTrigger); } } return 0; }
/* ** 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; }
/* ** This is called to code FOR EACH ROW triggers. ** ** When the code that this function generates is executed, the following ** must be true: ** ** 1. No cursors may be open in the main database. (But newIdx and oldIdx ** can be indices of cursors in temporary tables. See below.) ** ** 2. If the triggers being coded are ON INSERT or ON UPDATE triggers, then ** a temporary vdbe cursor (index newIdx) must be open and pointing at ** a row containing values to be substituted for new.* expressions in the ** trigger program(s). ** ** 3. If the triggers being coded are ON DELETE or ON UPDATE triggers, then ** a temporary vdbe cursor (index oldIdx) must be open and pointing at ** a row containing values to be substituted for old.* expressions in the ** trigger program(s). ** ** If they are not NULL, the piOldColMask and piNewColMask output variables ** are set to values that describe the columns used by the trigger program ** in the OLD.* and NEW.* tables respectively. If column N of the ** pseudo-table is read at least once, the corresponding bit of the output ** mask is set. If a column with an index greater than 32 is read, the ** output mask is set to the special value 0xffffffff. ** */ int sqlite3CodeRowTrigger( Parse *pParse, /* Parse context */ Trigger *pTrigger, /* List of triggers on table pTab */ int op, /* One of TK_UPDATE, TK_INSERT, TK_DELETE */ ExprList *pChanges, /* Changes list for any UPDATE OF triggers */ int tr_tm, /* One of TRIGGER_BEFORE, TRIGGER_AFTER */ Table *pTab, /* The table to code triggers from */ int newIdx, /* The indice of the "new" row to access */ int oldIdx, /* The indice of the "old" row to access */ int orconf, /* ON CONFLICT policy */ int ignoreJump, /* Instruction to jump to for RAISE(IGNORE) */ u32 *piOldColMask, /* OUT: Mask of columns used from the OLD.* table */ u32 *piNewColMask /* OUT: Mask of columns used from the NEW.* table */ ){ Trigger *p; sqlite3 *db = pParse->db; TriggerStack trigStackEntry; trigStackEntry.oldColMask = 0; trigStackEntry.newColMask = 0; assert(op == TK_UPDATE || op == TK_INSERT || op == TK_DELETE); assert(tr_tm == TRIGGER_BEFORE || tr_tm == TRIGGER_AFTER ); assert(newIdx != -1 || oldIdx != -1); for(p=pTrigger; p; p=p->pNext){ int fire_this = 0; /* Sanity checking: The schema for the trigger and for the table are ** always defined. The trigger must be in the same schema as the table ** or else it must be a TEMP trigger. */ assert( p->pSchema!=0 ); assert( p->pTabSchema!=0 ); assert( p->pSchema==p->pTabSchema || p->pSchema==db->aDb[1].pSchema ); /* Determine whether we should code this trigger */ if( p->op==op && p->tr_tm==tr_tm && checkColumnOverlap(p->pColumns,pChanges) ){ TriggerStack *pS; /* Pointer to trigger-stack entry */ for(pS=pParse->trigStack; pS && p!=pS->pTrigger; pS=pS->pNext){} if( !pS ){ fire_this = 1; } #if 0 /* Give no warning for recursive triggers. Just do not do them */ else{ sqlite3ErrorMsg(pParse, "recursive triggers not supported (%s)", p->name); return SQLITE_ERROR; } #endif } if( fire_this ){ int endTrigger; Expr * whenExpr; AuthContext sContext; NameContext sNC; #ifndef SQLITE_OMIT_TRACE sqlite3VdbeAddOp4(pParse->pVdbe, OP_Trace, 0, 0, 0, sqlite3MPrintf(db, "-- TRIGGER %s", p->name), P4_DYNAMIC); #endif memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; /* Push an entry on to the trigger stack */ trigStackEntry.pTrigger = p; trigStackEntry.newIdx = newIdx; trigStackEntry.oldIdx = oldIdx; trigStackEntry.pTab = pTab; trigStackEntry.pNext = pParse->trigStack; trigStackEntry.ignoreJump = ignoreJump; pParse->trigStack = &trigStackEntry; sqlite3AuthContextPush(pParse, &sContext, p->name); /* code the WHEN clause */ endTrigger = sqlite3VdbeMakeLabel(pParse->pVdbe); whenExpr = sqlite3ExprDup(db, p->pWhen, 0); if( db->mallocFailed || sqlite3ResolveExprNames(&sNC, whenExpr) ){ pParse->trigStack = trigStackEntry.pNext; sqlite3ExprDelete(db, whenExpr); return 1; } sqlite3ExprIfFalse(pParse, whenExpr, endTrigger, SQLITE_JUMPIFNULL); sqlite3ExprDelete(db, whenExpr); codeTriggerProgram(pParse, p->step_list, orconf); /* Pop the entry off the trigger stack */ pParse->trigStack = trigStackEntry.pNext; sqlite3AuthContextPop(&sContext); sqlite3VdbeResolveLabel(pParse->pVdbe, endTrigger); } } if( piOldColMask ) *piOldColMask |= trigStackEntry.oldColMask; if( piNewColMask ) *piNewColMask |= trigStackEntry.newColMask; return 0; }