/* ** 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; }
static void updateVirtualTable( Parse *pParse, SrcList *pSrc, Table *pTab, ExprList *pChanges, Expr *pRowid, int *aXRef, Expr *pWhere ){ Vdbe *v = pParse->pVdbe; ExprList *pEList = 0; Select *pSelect = 0; Expr *pExpr; int ephemTab; int i; int addr; int iReg; sqlite3 *db = pParse->db; const char *pVTab = (const char*)sqlite3GetVTable(db, pTab); SelectDest dest; pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, "_rowid_")); if( pRowid ){ pEList = sqlite3ExprListAppend(pParse, pEList, sqlite3ExprDup(db, pRowid, 0)); } assert( pTab->iPKey<0 ); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]>=0 ){ pExpr = sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0); }else{ pExpr = sqlite3Expr(db, TK_ID, pTab->aCol[i].zName); } pEList = sqlite3ExprListAppend(pParse, pEList, pExpr); } pSelect = sqlite3SelectNew(pParse, pEList, pSrc, pWhere, 0, 0, 0, 0, 0, 0); assert( v ); ephemTab = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, pTab->nCol+1+(pRowid!=0)); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); sqlite3SelectDestInit(&dest, SRT_Table, ephemTab); sqlite3Select(pParse, pSelect, &dest); iReg = ++pParse->nMem; pParse->nMem += pTab->nCol+1; addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); sqlite3VdbeAddOp3(v, OP_Column, ephemTab, 0, iReg); sqlite3VdbeAddOp3(v, OP_Column, ephemTab, (pRowid?1:0), iReg+1); for(i=0; i<pTab->nCol; i++){ sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i+1+(pRowid!=0), iReg+2+i); } sqlite3VtabMakeWritable(pParse, pTab); sqlite3VdbeAddOp4(v, OP_VUpdate, 0, pTab->nCol+2, iReg, pVTab, P4_VTAB); sqlite3MayAbort(pParse); sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); sqlite3VdbeJumpHere(v, addr); sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0); sqlite3SelectDelete(db, pSelect); }
/* ** 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; }
/* ** 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 error messages. */ ){ 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); pParse->parseError = 1; goto limit_where_cleanup_2; } /* 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 = sqlite3Expr(pParse->db, TK_ROW, 0, 0, 0); if( pSelectRowid == 0 ) goto limit_where_cleanup_2; pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid, 0); if( pEList == 0 ) goto limit_where_cleanup_2; /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc); if( pSelectSrc == 0 ) { sqlite3ExprListDelete(pParse->db, pEList); goto limit_where_cleanup_2; } /* 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 = sqlite3Expr(pParse->db, TK_ROW, 0, 0, 0); if( pWhereRowid == 0 ) goto limit_where_cleanup_1; pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); if( pInClause == 0 ) goto limit_where_cleanup_1; pInClause->pSelect = pSelect; sqlite3ExprSetHeight(pParse, pInClause); return pInClause; /* something went wrong. clean up anything allocated. */ limit_where_cleanup_1: sqlite3SelectDelete(pParse->db, pSelect); return 0; limit_where_cleanup_2: sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pOrderBy); sqlite3ExprDelete(pParse->db, pLimit); sqlite3ExprDelete(pParse->db, pOffset); return 0; }
/* ** Generate code for an UPDATE of a virtual table. ** ** The strategy is that we create an ephemerial table that contains ** for each row to be changed: ** ** (A) The original rowid of that row. ** (B) The revised rowid for the row. (note1) ** (C) The content of every column in the row. ** ** Then we loop over this ephemeral table and for each row in ** the ephermeral table call VUpdate. ** ** When finished, drop the ephemeral table. ** ** (note1) Actually, if we know in advance that (A) is always the same ** as (B) we only store (A), then duplicate (A) when pulling ** it out of the ephemeral table before calling VUpdate. */ static void updateVirtualTable( Parse *pParse, /* The parsing context */ SrcList *pSrc, /* The virtual table to be modified */ Table *pTab, /* The virtual table */ ExprList *pChanges, /* The columns to change in the UPDATE statement */ Expr *pRowid, /* Expression used to recompute the rowid */ int *aXRef, /* Mapping from columns of pTab to entries in pChanges */ Expr *pWhere /* WHERE clause of the UPDATE statement */ ){ Vdbe *v = pParse->pVdbe; /* Virtual machine under construction */ ExprList *pEList = 0; /* The result set of the SELECT statement */ Select *pSelect = 0; /* The SELECT statement */ Expr *pExpr; /* Temporary expression */ int ephemTab; /* Table holding the result of the SELECT */ int i; /* Loop counter */ int addr; /* Address of top of loop */ int iReg; /* First register in set passed to OP_VUpdate */ sqlite3 *db = pParse->db; /* Database connection */ const char *pVTab = (const char*)sqlite3GetVTable(db, pTab); SelectDest dest; /* Construct the SELECT statement that will find the new values for ** all updated rows. */ pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, "_rowid_")); if( pRowid ){ pEList = sqlite3ExprListAppend(pParse, pEList, sqlite3ExprDup(db, pRowid, 0)); } assert( pTab->iPKey<0 ); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]>=0 ){ pExpr = sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0); }else{ pExpr = sqlite3Expr(db, TK_ID, pTab->aCol[i].zName); } pEList = sqlite3ExprListAppend(pParse, pEList, pExpr); } pSelect = sqlite3SelectNew(pParse, pEList, pSrc, pWhere, 0, 0, 0, 0, 0, 0); /* Create the ephemeral table into which the update results will ** be stored. */ assert( v ); ephemTab = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, pTab->nCol+1+(pRowid!=0)); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); /* fill the ephemeral table */ sqlite3SelectDestInit(&dest, SRT_Table, ephemTab); sqlite3Select(pParse, pSelect, &dest); /* Generate code to scan the ephemeral table and call VUpdate. */ iReg = ++pParse->nMem; pParse->nMem += pTab->nCol+1; addr = sqlite3VdbeAddOp2(v, OP_Rewind, ephemTab, 0); sqlite3VdbeAddOp3(v, OP_Column, ephemTab, 0, iReg); sqlite3VdbeAddOp3(v, OP_Column, ephemTab, (pRowid?1:0), iReg+1); for(i=0; i<pTab->nCol; i++){ sqlite3VdbeAddOp3(v, OP_Column, ephemTab, i+1+(pRowid!=0), iReg+2+i); } sqlite3VtabMakeWritable(pParse, pTab); sqlite3VdbeAddOp4(v, OP_VUpdate, 0, pTab->nCol+2, iReg, pVTab, P4_VTAB); sqlite3MayAbort(pParse); sqlite3VdbeAddOp2(v, OP_Next, ephemTab, addr+1); sqlite3VdbeJumpHere(v, addr); sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0); /* Cleanup */ sqlite3SelectDelete(db, pSelect); }
/* ** 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); } }
/* ** Check to see if the given expression is a LIKE or GLOB operator that ** can be optimized using inequality constraints. Return TRUE if it is ** so and false if not. ** ** In order for the operator to be optimizible, the RHS must be a string ** literal that does not begin with a wildcard. The LHS must be a column ** that may only be NULL, a string, or a BLOB, never a number. (This means ** that virtual tables cannot participate in the LIKE optimization.) The ** collating sequence for the column on the LHS must be appropriate for ** the operator. */ static int isLikeOrGlob( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* Test this expression */ Expr **ppPrefix, /* Pointer to TK_STRING expression with pattern prefix */ int *pisComplete, /* True if the only wildcard is % in the last character */ int *pnoCase /* True if uppercase is equivalent to lowercase */ ){ const char *z = 0; /* String on RHS of LIKE operator */ Expr *pRight, *pLeft; /* Right and left size of LIKE operator */ ExprList *pList; /* List of operands to the LIKE operator */ int c; /* One character in z[] */ int cnt; /* Number of non-wildcard prefix characters */ char wc[3]; /* Wildcard characters */ sqlite3 *db = pParse->db; /* Database connection */ sqlite3_value *pVal = 0; int op; /* Opcode of pRight */ if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){ return 0; } #ifdef SQLITE_EBCDIC if( *pnoCase ) return 0; #endif pList = pExpr->x.pList; pLeft = pList->a[1].pExpr; if( pLeft->op!=TK_COLUMN || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT || IsVirtual(pLeft->pTab) /* Value might be numeric */ ){ /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must ** be the name of an indexed column with TEXT affinity. */ return 0; } assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */ pRight = sqlite3ExprSkipCollate(pList->a[0].pExpr); op = pRight->op; if( op==TK_VARIABLE ){ Vdbe *pReprepare = pParse->pReprepare; int iCol = pRight->iColumn; pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_BLOB); if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){ z = (char *)sqlite3_value_text(pVal); } sqlite3VdbeSetVarmask(pParse->pVdbe, iCol); assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER ); }else if( op==TK_STRING ){ z = pRight->u.zToken; } if( z ){ cnt = 0; while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){ cnt++; } if( cnt!=0 && 255!=(u8)z[cnt-1] ){ Expr *pPrefix; *pisComplete = c==wc[0] && z[cnt+1]==0; pPrefix = sqlite3Expr(db, TK_STRING, z); if( pPrefix ) pPrefix->u.zToken[cnt] = 0; *ppPrefix = pPrefix; if( op==TK_VARIABLE ){ Vdbe *v = pParse->pVdbe; sqlite3VdbeSetVarmask(v, pRight->iColumn); if( *pisComplete && pRight->u.zToken[1] ){ /* If the rhs of the LIKE expression is a variable, and the current ** value of the variable means there is no need to invoke the LIKE ** function, then no OP_Variable will be added to the program. ** This causes problems for the sqlite3_bind_parameter_name() ** API. To work around them, add a dummy OP_Variable here. */ int r1 = sqlite3GetTempReg(pParse); sqlite3ExprCodeTarget(pParse, pRight, r1); sqlite3VdbeChangeP3(v, sqlite3VdbeCurrentAddr(v)-1, 0); sqlite3ReleaseTempReg(pParse, r1); } } }else{ z = 0; } } sqlite3ValueFree(pVal); return (z!=0); }