/*

**

** The author disclaims copyright to this source code. In place of

** a legal notice, here is a blessing:

**

** May you do good and not evil.

** May you find forgiveness for yourself and forgive others.

** May you share freely, never taking more than you give.

**

*************************************************************************

** This file contains code used by the compiler to add foreign key

** support to compiled SQL statements.

*/

#include "sqliteInt.h"

#ifndef SQLITE_OMIT_FOREIGN_KEY

#ifndef SQLITE_OMIT_TRIGGER

/*

** Deferred and Immediate FKs

** --------------------------

**

** Foreign keys in SQLite come in two flavours: deferred and immediate.

** If an immediate foreign key constraint is violated,

** SQLITE_CONSTRAINT_FOREIGNKEY is returned and the current

** statement transaction rolled back. If a

** deferred foreign key constraint is violated, no action is taken

** immediately. However if the application attempts to commit the

** transaction before fixing the constraint violation, the attempt fails.

**

** Deferred constraints are implemented using a simple counter associated

** with the database handle. The counter is set to zero each time a

** database transaction is opened. Each time a statement is executed

** that causes a foreign key violation, the counter is incremented. Each

** time a statement is executed that removes an existing violation from

** the database, the counter is decremented. When the transaction is

** committed, the commit fails if the current value of the counter is

** greater than zero. This scheme has two big drawbacks:

**

** * When a commit fails due to a deferred foreign key constraint,

** there is no way to tell which foreign constraint is not satisfied,

** or which row it is not satisfied for.

**

** * If the database contains foreign key violations when the

** transaction is opened, this may cause the mechanism to malfunction.

**

** Despite these problems, this approach is adopted as it seems simpler

** than the alternatives.

**

** INSERT operations:

**

** I.1) For each FK for which the table is the child table, search

** the parent table for a match. If none is found increment the

** constraint counter.

**

** I.2) For each FK for which the table is the parent table,

** search the child table for rows that correspond to the new

** row in the parent table. Decrement the counter for each row

** found (as the constraint is now satisfied).

**

** DELETE operations:

**

** D.1) For each FK for which the table is the child table,

** search the parent table for a row that corresponds to the

** deleted row in the child table. If such a row is not found,

** decrement the counter.

**

** D.2) For each FK for which the table is the parent table, search

** the child table for rows that correspond to the deleted row

** in the parent table. For each found increment the counter.

**

** UPDATE operations:

**

** An UPDATE command requires that all 4 steps above are taken, but only

** for FK constraints for which the affected columns are actually

** modified (values must be compared at runtime).

**

** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.

** This simplifies the implementation a bit.

**

** For the purposes of immediate FK constraints, the OR REPLACE conflict

** resolution is considered to delete rows before the new row is inserted.

** If a delete caused by OR REPLACE violates an FK constraint, an exception

** is thrown, even if the FK constraint would be satisfied after the new

** row is inserted.

**

** Immediate constraints are usually handled similarly. The only difference

** is that the counter used is stored as part of each individual statement

** object (struct Vdbe). If, after the statement has run, its immediate

** constraint counter is greater than zero,

** it returns SQLITE_CONSTRAINT_FOREIGNKEY

** and the statement transaction is rolled back. An exception is an INSERT

** statement that inserts a single row only (no triggers). In this case,

** instead of using a counter, an exception is thrown immediately if the

** INSERT violates a foreign key constraint. This is necessary as such

** an INSERT does not open a statement transaction.

**

** TODO: How should dropping a table be handled? How should renaming a

** table be handled?

**

**

** Query API Notes

** ---------------

**

** Before coding an UPDATE or DELETE row operation, the code-generator

** for those two operations needs to know whether or not the operation

** requires any FK processing and, if so, which columns of the original

** row are required by the FK processing VDBE code (i.e. if FKs were

** implemented using triggers, which of the old.* columns would be

** accessed). No information is required by the code-generator before

** coding an INSERT operation. The functions used by the UPDATE/DELETE

** generation code to query for this information are:

**

** sqlite3FkRequired() - Test to see if FK processing is required.

** sqlite3FkOldmask() - Query for the set of required old.* columns.

**

**

** Externally accessible module functions

** --------------------------------------

**

** sqlite3FkCheck() - Check for foreign key violations.

** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.

** sqlite3FkDelete() - Delete an FKey structure.

*/

/*

** VDBE Calling Convention

** -----------------------

**

** Example:

**

** For the following INSERT statement:

**

** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);

** INSERT INTO t1 VALUES(1, 2, 3.1);

**

** Register (x): 2 (type integer)

** Register (x+1): 1 (type integer)

** Register (x+2): NULL (type NULL)

** Register (x+3): 3.1 (type real)

*/

/*

** A foreign key constraint requires that the key columns in the parent

** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.

** Given that pParent is the parent table for foreign key constraint pFKey,

** search the schema for a unique index on the parent key columns.

**

** If successful, zero is returned. If the parent key is an INTEGER PRIMARY

** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx

** is set to point to the unique index.

**

** If the parent key consists of a single column (the foreign key constraint

** is not a composite foreign key), output variable *paiCol is set to NULL.

** Otherwise, it is set to point to an allocated array of size N, where

** N is the number of columns in the parent key. The first element of the

** array is the index of the child table column that is mapped by the FK

** constraint to the parent table column stored in the left-most column

** of index *ppIdx. The second element of the array is the index of the

** child table column that corresponds to the second left-most column of

** *ppIdx, and so on.

**

** If the required index cannot be found, either because:

**

** 1) The named parent key columns do not exist, or

**

** 2) The named parent key columns do exist, but are not subject to a

** UNIQUE or PRIMARY KEY constraint, or

**

** 3) No parent key columns were provided explicitly as part of the

** foreign key definition, and the parent table does not have a

** PRIMARY KEY, or

**

** 4) No parent key columns were provided explicitly as part of the

** foreign key definition, and the PRIMARY KEY of the parent table

** consists of a different number of columns to the child key in

** the child table.

**

** then non-zero is returned, and a "foreign key mismatch" error loaded

** into pParse. If an OOM error occurs, non-zero is returned and the

** pParse->db->mallocFailed flag is set.

*/

int sqlite3FkLocateIndex(

Parse *pParse, /* Parse context to store any error in */

Table *pParent, /* Parent table of FK constraint pFKey */

FKey *pFKey, /* Foreign key to find index for */

Index **ppIdx, /* OUT: Unique index on parent table */

int **paiCol /* OUT: Map of index columns in pFKey */

){

Index *pIdx = 0; /* Value to return via *ppIdx */

int *aiCol = 0; /* Value to return via *paiCol */

int nCol = pFKey->nCol; /* Number of columns in parent key */

char *zKey = pFKey->aCol[0].zCol; /* Name of left-most parent key column */

/* The caller is responsible for zeroing output parameters. */

assert( ppIdx && *ppIdx==0 );

assert( !paiCol || *paiCol==0 );

assert( pParse );

/* If this is a non-composite (single column) foreign key, check if it

** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx

** and *paiCol set to zero and return early.

**

** Otherwise, for a composite foreign key (more than one column), allocate

** space for the aiCol array (returned via output parameter *paiCol).

** Non-composite foreign keys do not require the aiCol array.

*/

if( nCol==1 ){

/* The FK maps to the IPK if any of the following are true:

**

** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly

** mapped to the primary key of table pParent, or

** 2) The FK is explicitly mapped to a column declared as INTEGER

** PRIMARY KEY.

*/

if( pParent->iPKey>=0 ){

if( !zKey ) return 0;

if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;

}

}else if( paiCol ){

assert( nCol>1 );

aiCol = (int *)sqlite3DbMallocRawNN(pParse->db, nCol*sizeof(int));

if( !aiCol ) return 1;

*paiCol = aiCol;

}

for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){

if( pIdx->nKeyCol==nCol && IsUniqueIndex(pIdx) && pIdx->pPartIdxWhere==0 ){

/* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number

** of columns. If each indexed column corresponds to a foreign key

** column of pFKey, then this index is a winner. */

if( zKey==0 ){

/* If zKey is NULL, then this foreign key is implicitly mapped to

** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be

** identified by the test. */

if( IsPrimaryKeyIndex(pIdx) ){

if( aiCol ){

int i;

for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;

}

break;

}

}else{

/* If zKey is non-NULL, then this foreign key was declared to

** map to an explicit list of columns in table pParent. Check if this

** index matches those columns. Also, check that the index uses

** the default collation sequences for each column. */

int i, j;

for(i=0; i<nCol; i++){

i16 iCol = pIdx->aiColumn[i]; /* Index of column in parent tbl */

const char *zDfltColl; /* Def. collation for column */

char *zIdxCol; /* Name of indexed column */

if( iCol<0 ) break; /* No foreign keys against expression indexes */

/* If the index uses a collation sequence that is different from

** the default collation sequence for the column, this index is

** unusable. Bail out early in this case. */

zDfltColl = pParent->aCol[iCol].zColl;

if( !zDfltColl ) zDfltColl = sqlite3StrBINARY;

if( sqlite3StrICmp(pIdx->azColl[i], zDfltColl) ) break;

zIdxCol = pParent->aCol[iCol].zName;

for(j=0; j<nCol; j++){

if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){

if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;

break;

}

}

if( j==nCol ) break;

}

if( i==nCol ) break; /* pIdx is usable */

}

}

}

if( !pIdx ){

if( !pParse->disableTriggers ){

sqlite3ErrorMsg(pParse,

"foreign key mismatch - \"%w\" referencing \"%w\"",

pFKey->pFrom->zName, pFKey->zTo);

}

sqlite3DbFree(pParse->db, aiCol);

return 1;

}

*ppIdx = pIdx;

return 0;

}

/*

** This function is called when a row is inserted into or deleted from the

** child table of foreign key constraint pFKey. If an SQL UPDATE is executed

** on the child table of pFKey, this function is invoked twice for each row

** affected - once to "delete" the old row, and then again to "insert" the

** new row.

**

** Each time it is called, this function generates VDBE code to locate the

** row in the parent table that corresponds to the row being inserted into

** or deleted from the child table. If the parent row can be found, no

** special action is taken. Otherwise, if the parent row can *not* be

** found in the parent table:

**

** Operation | FK type | Action taken

** --------------------------------------------------------------------------

** INSERT immediate Increment the "immediate constraint counter".

**

** DELETE immediate Decrement the "immediate constraint counter".

**

** INSERT deferred Increment the "deferred constraint counter".

**

** DELETE deferred Decrement the "deferred constraint counter".

**

** These operations are identified in the comment at the top of this file

** (fkey.c) as "I.1" and "D.1".

*/

static void fkLookupParent(

Parse *pParse, /* Parse context */

int iDb, /* Index of database housing pTab */

Table *pTab, /* Parent table of FK pFKey */

Index *pIdx, /* Unique index on parent key columns in pTab */

FKey *pFKey, /* Foreign key constraint */

int *aiCol, /* Map from parent key columns to child table columns */

int regData, /* Address of array containing child table row */

int nIncr, /* Increment constraint counter by this */

int isIgnore /* If true, pretend pTab contains all NULL values */

){

int i; /* Iterator variable */

Vdbe *v = sqlite3GetVdbe(pParse); /* Vdbe to add code to */

int iCur = pParse->nTab - 1; /* Cursor number to use */

int iOk = sqlite3VdbeMakeLabel(v); /* jump here if parent key found */

sqlite3VdbeVerifyAbortable(v,

(!pFKey->isDeferred

&& !(pParse->db->flags & SQLITE_DeferFKs)

&& !pParse->pToplevel

&& !pParse->isMultiWrite) ? OE_Abort : OE_Ignore);

/* If nIncr is less than zero, then check at runtime if there are any

** outstanding constraints to resolve. If there are not, there is no need

** to check if deleting this row resolves any outstanding violations.

**

** Check if any of the key columns in the child table row are NULL. If

** any are, then the constraint is considered satisfied. No need to

** search for a matching row in the parent table. */

if( nIncr<0 ){

sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);

VdbeCoverage(v);

}

for(i=0; i<pFKey->nCol; i++){

int iReg = aiCol[i] + regData + 1;

sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); VdbeCoverage(v);

}

if( isIgnore==0 ){

if( pIdx==0 ){

/* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY

** column of the parent table (table pTab). */

int iMustBeInt; /* Address of MustBeInt instruction */

int regTemp = sqlite3GetTempReg(pParse);

/* Invoke MustBeInt to coerce the child key value to an integer (i.e.

** apply the affinity of the parent key). If this fails, then there

** is no matching parent key. Before using MustBeInt, make a copy of

** the value. Otherwise, the value inserted into the child key column

** will have INTEGER affinity applied to it, which may not be correct. */

sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0]+1+regData, regTemp);

iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0);

VdbeCoverage(v);

/* If the parent table is the same as the child table, and we are about

** to increment the constraint-counter (i.e. this is an INSERT operation),

** then check if the row being inserted matches itself. If so, do not

** increment the constraint-counter. */

if( pTab==pFKey->pFrom && nIncr==1 ){

sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp); VdbeCoverage(v);

sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);

}

sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);

sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp); VdbeCoverage(v);

sqlite3VdbeGoto(v, iOk);

sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);

sqlite3VdbeJumpHere(v, iMustBeInt);

sqlite3ReleaseTempReg(pParse, regTemp);

}else{

int nCol = pFKey->nCol;

int regTemp = sqlite3GetTempRange(pParse, nCol);

int regRec = sqlite3GetTempReg(pParse);

sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);

sqlite3VdbeSetP4KeyInfo(pParse, pIdx);

for(i=0; i<nCol; i++){

sqlite3VdbeAddOp2(v, OP_Copy, aiCol[i]+1+regData, regTemp+i);

}

/* If the parent table is the same as the child table, and we are about

** to increment the constraint-counter (i.e. this is an INSERT operation),

** then check if the row being inserted matches itself. If so, do not

** increment the constraint-counter.

**

** If any of the parent-key values are NULL, then the row cannot match

** itself. So set JUMPIFNULL to make sure we do the OP_Found if any

** of the parent-key values are NULL (at this point it is known that

** none of the child key values are).

*/

if( pTab==pFKey->pFrom && nIncr==1 ){

int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1;

for(i=0; i<nCol; i++){

int iChild = aiCol[i]+1+regData;

int iParent = pIdx->aiColumn[i]+1+regData;

assert( pIdx->aiColumn[i]>=0 );

assert( aiCol[i]!=pTab->iPKey );

if( pIdx->aiColumn[i]==pTab->iPKey ){

/* The parent key is a composite key that includes the IPK column */

iParent = regData;

}

sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent); VdbeCoverage(v);

sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);

}

sqlite3VdbeGoto(v, iOk);

}

sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, nCol, regRec,

sqlite3IndexAffinityStr(pParse->db,pIdx), nCol);

sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); VdbeCoverage(v);

sqlite3ReleaseTempReg(pParse, regRec);

sqlite3ReleaseTempRange(pParse, regTemp, nCol);

}

}

if( !pFKey->isDeferred && !(pParse->db->flags & SQLITE_DeferFKs)

&& !pParse->pToplevel

&& !pParse->isMultiWrite

){

/* Special case: If this is an INSERT statement that will insert exactly

** one row into the table, raise a constraint immediately instead of

** incrementing a counter. This is necessary as the VM code is being

** generated for will not open a statement transaction. */

assert( nIncr==1 );

sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,

OE_Abort, 0, P4_STATIC, P5_ConstraintFK);

}else{

if( nIncr>0 && pFKey->isDeferred==0 ){

sqlite3MayAbort(pParse);

}

sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);

}

sqlite3VdbeResolveLabel(v, iOk);

sqlite3VdbeAddOp1(v, OP_Close, iCur);

}

/*

** Return an Expr object that refers to a memory register corresponding

** to column iCol of table pTab.

**

** regBase is the first of an array of register that contains the data

** for pTab. regBase itself holds the rowid. regBase+1 holds the first

** column. regBase+2 holds the second column, and so forth.

*/

static Expr *exprTableRegister(

Parse *pParse, /* Parsing and code generating context */

Table *pTab, /* The table whose content is at r[regBase]... */

int regBase, /* Contents of table pTab */

i16 iCol /* Which column of pTab is desired */

){

Expr *pExpr;

Column *pCol;

const char *zColl;

sqlite3 *db = pParse->db;

pExpr = sqlite3Expr(db, TK_REGISTER, 0);

if( pExpr ){

if( iCol>=0 && iCol!=pTab->iPKey ){

pCol = &pTab->aCol[iCol];

pExpr->iTable = regBase + iCol + 1;

pExpr->affinity = pCol->affinity;

zColl = pCol->zColl;

if( zColl==0 ) zColl = db->pDfltColl->zName;

pExpr = sqlite3ExprAddCollateString(pParse, pExpr, zColl);

}else{

pExpr->iTable = regBase;

pExpr->affinity = SQLITE_AFF_INTEGER;

}

}

return pExpr;

}

/*

** Return an Expr object that refers to column iCol of table pTab which

** has cursor iCur.

*/

static Expr *exprTableColumn(

sqlite3 *db, /* The database connection */

Table *pTab, /* The table whose column is desired */

int iCursor, /* The open cursor on the table */

i16 iCol /* The column that is wanted */

){

Expr *pExpr = sqlite3Expr(db, TK_COLUMN, 0);

if( pExpr ){

pExpr->y.pTab = pTab;

pExpr->iTable = iCursor;

pExpr->iColumn = iCol;

}

return pExpr;

}

/*

** 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.

**

** Parameter nIncr is passed -1 when inserting a row (as this may decrease

** the number of FK violations in the db) or +1 when deleting one (as this

** may increase the number of FK constraint problems).

**

** 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, /* The child table to be scanned */

Table *pTab, /* The parent table */

Index *pIdx, /* Index on parent covering the foreign key */

FKey *pFKey, /* The foreign key linking pSrc to pTab */

int *aiCol, /* Map from pIdx cols to child table cols */

int regData, /* Parent row 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==0 || pIdx->pTable==pTab );

assert( pIdx==0 || pIdx->nKeyCol==pFKey->nCol );

assert( pIdx!=0 || pFKey->nCol==1 );

assert( pIdx!=0 || HasRowid(pTab) );

if( nIncr<0 ){

iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);

VdbeCoverage(v);

}

/* 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) */

i16 iCol; /* Index of column in child table */

const char *zCol; /* Name of column in child table */

iCol = pIdx ? pIdx->aiColumn[i] : -1;

pLeft = exprTableRegister(pParse, pTab, regData, iCol);

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);

pWhere = sqlite3ExprAnd(db, pWhere, pEq);

}

/* If the child table is the same as the parent table, then add terms

** to the WHERE clause that prevent this entry from being scanned.

** The added WHERE clause terms are like this:

**

** $current_rowid!=rowid

** NOT( $current_a==a AND $current_b==b AND ... )

**

** The first form is used for rowid tables. The second form is used

** for WITHOUT ROWID tables. In the second form, the primary key is

** (a,b,...)

*/

if( pTab==pFKey->pFrom && nIncr>0 ){

Expr *pNe; /* Expression (pLeft != pRight) */

Expr *pLeft; /* Value from parent table row */

Expr *pRight; /* Column ref to child table */

if( HasRowid(pTab) ){

pLeft = exprTableRegister(pParse, pTab, regData, -1);

pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, -1);

pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight);

}else{

Expr *pEq, *pAll = 0;

Index *pPk = sqlite3PrimaryKeyIndex(pTab);

assert( pIdx!=0 );

for(i=0; i<pPk->nKeyCol; i++){

i16 iCol = pIdx->aiColumn[i];

assert( iCol>=0 );

pLeft = exprTableRegister(pParse, pTab, regData, iCol);

pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, iCol);

pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);

pAll = sqlite3ExprAnd(db, pAll, pEq);

}

pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0);

}

pWhere = sqlite3ExprAnd(db, pWhere, pNe);

}

/* 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. For each row found, increment either the deferred or immediate

** foreign key constraint counter. */

if( pParse->nErr==0 ){

pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);

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);

}

}

/*

** This function returns a linked list of FKey objects (connected by

** FKey.pNextTo) holding all children of table pTab. For example,

** given the following schema:

**

** CREATE TABLE t1(a PRIMARY KEY);

** CREATE TABLE t2(b REFERENCES t1(a);

**

** Calling this function with table "t1" as an argument returns a pointer

** to the FKey structure representing the foreign key constraint on table

** "t2". Calling this function with "t2" as the argument would return a

** NULL pointer (as there are no FK constraints for which t2 is the parent

** table).

*/

FKey *sqlite3FkReferences(Table *pTab){

return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName);

}

/*

** The second argument is a Trigger structure allocated by the

** fkActionTrigger() routine. This function deletes the Trigger structure

** and all of its sub-components.

**

** The Trigger structure or any of its sub-components may be allocated from

** the lookaside buffer belonging to database handle dbMem.

*/

static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){

if( p ){

TriggerStep *pStep = p->step_list;

sqlite3ExprDelete(dbMem, pStep->pWhere);

sqlite3ExprListDelete(dbMem, pStep->pExprList);

sqlite3SelectDelete(dbMem, pStep->pSelect);

sqlite3ExprDelete(dbMem, p->pWhen);

sqlite3DbFree(dbMem, p);

}

}

/*

** This function is called to generate code that runs when table pTab is

** being dropped from the database. The SrcList passed as the second argument

** to this function contains a single entry guaranteed to resolve to

** table pTab.

**

** Normally, no code is required. However, if either

**

** (a) The table is the parent table of a FK constraint, or

** (b) The table is the child table of a deferred FK constraint and it is

** determined at runtime that there are outstanding deferred FK

** constraint violations in the database,

**

** then the equivalent of "DELETE FROM <tbl>" is executed before dropping

** the table from the database. Triggers are disabled while running this

** DELETE, but foreign key actions are not.

*/

void sqlite3FkDropTable(Parse *pParse, SrcList *pName, Table *pTab){

sqlite3 *db = pParse->db;

if( (db->flags&SQLITE_ForeignKeys) && !IsVirtual(pTab) ){

int iSkip = 0;

Vdbe *v = sqlite3GetVdbe(pParse);

assert( v ); /* VDBE has already been allocated */

assert( pTab->pSelect==0 ); /* Not a view */

if( sqlite3FkReferences(pTab)==0 ){

/* Search for a deferred foreign key constraint for which this table

** is the child table. If one cannot be found, return without

** generating any VDBE code. If one can be found, then jump over

** the entire DELETE if there are no outstanding deferred constraints

** when this statement is run. */

FKey *p;

for(p=pTab->pFKey; p; p=p->pNextFrom){

if( p->isDeferred || (db->flags & SQLITE_DeferFKs) ) break;

}

if( !p ) return;

iSkip = sqlite3VdbeMakeLabel(v);

sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip); VdbeCoverage(v);

}

pParse->disableTriggers = 1;

sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0, 0, 0);

pParse->disableTriggers = 0;

/* If the DELETE has generated immediate foreign key constraint

** violations, halt the VDBE and return an error at this point, before

** any modifications to the schema are made. This is because statement

** transactions are not able to rollback schema changes.

**

** If the SQLITE_DeferFKs flag is set, then this is not required, as

** the statement transaction will not be rolled back even if FK

** constraints are violated.

*/

if( (db->flags & SQLITE_DeferFKs)==0 ){

sqlite3VdbeVerifyAbortable(v, OE_Abort);

sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);

VdbeCoverage(v);

sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,

OE_Abort, 0, P4_STATIC, P5_ConstraintFK);

}

if( iSkip ){

sqlite3VdbeResolveLabel(v, iSkip);

}

}

}

/*

** The second argument points to an FKey object representing a foreign key

** for which pTab is the child table. An UPDATE statement against pTab

** is currently being processed. For each column of the table that is

** actually updated, the corresponding element in the aChange[] array

** is zero or greater (if a column is unmodified the corresponding element

** is set to -1). If the rowid column is modified by the UPDATE statement

** the bChngRowid argument is non-zero.

**

** This function returns true if any of the columns that are part of the

** child key for FK constraint *p are modified.

*/

static int fkChildIsModified(

Table *pTab, /* Table being updated */

FKey *p, /* Foreign key for which pTab is the child */

int *aChange, /* Array indicating modified columns */

int bChngRowid /* True if rowid is modified by this update */

){

int i;

for(i=0; i<p->nCol; i++){

int iChildKey = p->aCol[i].iFrom;

if( aChange[iChildKey]>=0 ) return 1;

if( iChildKey==pTab->iPKey && bChngRowid ) return 1;

}

return 0;

}

/*

** The second argument points to an FKey object representing a foreign key

** for which pTab is the parent table. An UPDATE statement against pTab

** is currently being processed. For each column of the table that is

** actually updated, the corresponding element in the aChange[] array

** is zero or greater (if a column is unmodified the corresponding element

** is set to -1). If the rowid column is modified by the UPDATE statement

** the bChngRowid argument is non-zero.

**

** This function returns true if any of the columns that are part of the

** parent key for FK constraint *p are modified.

*/

static int fkParentIsModified(

Table *pTab,

FKey *p,

int *aChange,

int bChngRowid

){

int i;

for(i=0; i<p->nCol; i++){

char *zKey = p->aCol[i].zCol;

int iKey;

for(iKey=0; iKey<pTab->nCol; iKey++){

if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){

Column *pCol = &pTab->aCol[iKey];

if( zKey ){

if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1;

}else if( pCol->colFlags & COLFLAG_PRIMKEY ){

return 1;

}

}

}

}

return 0;

}

/*

** Return true if the parser passed as the first argument is being

** used to code a trigger that is really a "SET NULL" action belonging

** to trigger pFKey.

*/

static int isSetNullAction(Parse *pParse, FKey *pFKey){

Parse *pTop = sqlite3ParseToplevel(pParse);

if( pTop->pTriggerPrg ){

Trigger *p = pTop->pTriggerPrg->pTrigger;

if( (p==pFKey->apTrigger[0] && pFKey->aAction[0]==OE_SetNull)

|| (p==pFKey->apTrigger[1] && pFKey->aAction[1]==OE_SetNull)

){

return 1;

}

}

return 0;

}

/*

** This function is called when inserting, deleting or updating a row of

** table pTab to generate VDBE code to perform foreign key constraint

** processing for the operation.

**

** For a DELETE operation, parameter regOld is passed the index of the

** first register in an array of (pTab->nCol+1) registers containing the

** rowid of the row being deleted, followed by each of the column values

** of the row being deleted, from left to right. Parameter regNew is passed

** zero in this case.

**

** For an INSERT operation, regOld is passed zero and regNew is passed the

** first register of an array of (pTab->nCol+1) registers containing the new

** row data.

**

** For an UPDATE operation, this function is called twice. Once before

** the original record is deleted from the table using the calling convention

** described for DELETE. Then again after the original record is deleted

** but before the new record is inserted using the INSERT convention.

*/

void sqlite3FkCheck(

Parse *pParse, /* Parse context */

Table *pTab, /* Row is being deleted from this table */

int regOld, /* Previous row data is stored here */

int regNew, /* New row data is stored here */

int *aChange, /* Array indicating UPDATEd columns (or 0) */

int bChngRowid /* True if rowid is UPDATEd */

){

sqlite3 *db = pParse->db; /* Database handle */

FKey *pFKey; /* Used to iterate through FKs */

int iDb; /* Index of database containing pTab */

const char *zDb; /* Name of database containing pTab */

int isIgnoreErrors = pParse->disableTriggers;

/* Exactly one of regOld and regNew should be non-zero. */

assert( (regOld==0)!=(regNew==0) );

/* If foreign-keys are disabled, this function is a no-op. */

if( (db->flags&SQLITE_ForeignKeys)==0 ) return;

iDb = sqlite3SchemaToIndex(db, pTab->pSchema);

zDb = db->aDb[iDb].zDbSName;

/* Loop through all the foreign key constraints for which pTab is the

** child table (the table that the foreign key definition is part of). */

for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){

Table *pTo; /* Parent table of foreign key pFKey */

Index *pIdx = 0; /* Index on key columns in pTo */

int *aiFree = 0;

int *aiCol;

int iCol;

int i;

int bIgnore = 0;

if( aChange

&& sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0

&& fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0

){

continue;

}

/* Find the parent table of this foreign key. Also find a unique index

** on the parent key columns in the parent table. If either of these

** schema items cannot be located, set an error in pParse and return

** early. */

if( pParse->disableTriggers ){

pTo = sqlite3FindTable(db, pFKey->zTo, zDb);

}else{

pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);

}

if( !pTo || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){

assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) );

if( !isIgnoreErrors || db->mallocFailed ) return;

if( pTo==0 ){

/* If isIgnoreErrors is true, then a table is being dropped. In this

** case SQLite runs a "DELETE FROM xxx" on the table being dropped

** before actually dropping it in order to check FK constraints.

** If the parent table of an FK constraint on the current table is

** missing, behave as if it is empty. i.e. decrement the relevant

** FK counter for each row of the current table with non-NULL keys.

*/

Vdbe *v = sqlite3GetVdbe(pParse);

int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1;

for(i=0; i<pFKey->nCol; i++){

int iReg = pFKey->aCol[i].iFrom + regOld + 1;

sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iJump); VdbeCoverage(v);

}

sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, -1);

}

continue;

}

assert( pFKey->nCol==1 || (aiFree && pIdx) );

if( aiFree ){

aiCol = aiFree;

}else{

iCol = pFKey->aCol[0].iFrom;

aiCol = &iCol;

}

for(i=0; i<pFKey->nCol; i++){

if( aiCol[i]==pTab->iPKey ){

aiCol[i] = -1;

}

assert( pIdx==0 || pIdx->aiColumn[i]>=0 );

#ifndef SQLITE_OMIT_AUTHORIZATION

/* Request permission to read the parent key columns. If the

** authorization callback returns SQLITE_IGNORE, behave as if any

** values read from the parent table are NULL. */

if( db->xAuth ){

int rcauth;

char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName;

rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb);

bIgnore = (rcauth==SQLITE_IGNORE);

}

#endif

}

/* Take a shared-cache advisory read-lock on the parent table. Allocate

** a cursor to use to search the unique index on the parent key columns

** in the parent table. */

sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);

pParse->nTab++;

if( regOld!=0 ){

/* A row is being removed from the child table. Search for the parent.

** If the parent does not exist, removing the child row resolves an

** outstanding foreign key constraint violation. */

fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1, bIgnore);

}

if( regNew!=0 && !isSetNullAction(pParse, pFKey) ){

/* A row is being added to the child table. If a parent row cannot

** be found, adding the child row has violated the FK constraint.

**

** If this operation is being performed as part of a trigger program

** that is actually a "SET NULL" action belonging to this very

** foreign key, then omit this scan altogether. As all child key

** values are guaranteed to be NULL, it is not possible for adding

** this row to cause an FK violation. */

fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1, bIgnore);

}

sqlite3DbFree(db, aiFree);

}

/* Loop through all the foreign key constraints that refer to this table.

** (the "child" constraints) */

for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){

Index *pIdx = 0; /* Foreign key index for pFKey */

SrcList *pSrc;

int *aiCol = 0;

if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){

continue;

}

if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs)

&& !pParse->pToplevel && !pParse->isMultiWrite

){