000001 /*
000002 **
000003 ** The author disclaims copyright to this source code. In place of
000004 ** a legal notice, here is a blessing:
000005 **
000006 ** May you do good and not evil.
000007 ** May you find forgiveness for yourself and forgive others.
000008 ** May you share freely, never taking more than you give.
000009 **
000010 *************************************************************************
000011 ** This file contains code used by the compiler to add foreign key
000012 ** support to compiled SQL statements.
000013 */
000014 #include "sqliteInt.h"
000015
000016 #ifndef SQLITE_OMIT_FOREIGN_KEY
000017 #ifndef SQLITE_OMIT_TRIGGER
000018
000019 /*
000020 ** Deferred and Immediate FKs
000021 ** --------------------------
000022 **
000023 ** Foreign keys in SQLite come in two flavours: deferred and immediate.
000024 ** If an immediate foreign key constraint is violated,
000025 ** SQLITE_CONSTRAINT_FOREIGNKEY is returned and the current
000026 ** statement transaction rolled back. If a
000027 ** deferred foreign key constraint is violated, no action is taken
000028 ** immediately. However if the application attempts to commit the
000029 ** transaction before fixing the constraint violation, the attempt fails.
000030 **
000031 ** Deferred constraints are implemented using a simple counter associated
000032 ** with the database handle. The counter is set to zero each time a
000033 ** database transaction is opened. Each time a statement is executed
000034 ** that causes a foreign key violation, the counter is incremented. Each
000035 ** time a statement is executed that removes an existing violation from
000036 ** the database, the counter is decremented. When the transaction is
000037 ** committed, the commit fails if the current value of the counter is
000038 ** greater than zero. This scheme has two big drawbacks:
000039 **
000040 ** * When a commit fails due to a deferred foreign key constraint,
000041 ** there is no way to tell which foreign constraint is not satisfied,
000042 ** or which row it is not satisfied for.
000043 **
000044 ** * If the database contains foreign key violations when the
000045 ** transaction is opened, this may cause the mechanism to malfunction.
000046 **
000047 ** Despite these problems, this approach is adopted as it seems simpler
000048 ** than the alternatives.
000049 **
000050 ** INSERT operations:
000051 **
000052 ** I.1) For each FK for which the table is the child table, search
000053 ** the parent table for a match. If none is found increment the
000054 ** constraint counter.
000055 **
000056 ** I.2) For each FK for which the table is the parent table,
000057 ** search the child table for rows that correspond to the new
000058 ** row in the parent table. Decrement the counter for each row
000059 ** found (as the constraint is now satisfied).
000060 **
000061 ** DELETE operations:
000062 **
000063 ** D.1) For each FK for which the table is the child table,
000064 ** search the parent table for a row that corresponds to the
000065 ** deleted row in the child table. If such a row is not found,
000066 ** decrement the counter.
000067 **
000068 ** D.2) For each FK for which the table is the parent table, search
000069 ** the child table for rows that correspond to the deleted row
000070 ** in the parent table. For each found increment the counter.
000071 **
000072 ** UPDATE operations:
000073 **
000074 ** An UPDATE command requires that all 4 steps above are taken, but only
000075 ** for FK constraints for which the affected columns are actually
000076 ** modified (values must be compared at runtime).
000077 **
000078 ** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
000079 ** This simplifies the implementation a bit.
000080 **
000081 ** For the purposes of immediate FK constraints, the OR REPLACE conflict
000082 ** resolution is considered to delete rows before the new row is inserted.
000083 ** If a delete caused by OR REPLACE violates an FK constraint, an exception
000084 ** is thrown, even if the FK constraint would be satisfied after the new
000085 ** row is inserted.
000086 **
000087 ** Immediate constraints are usually handled similarly. The only difference
000088 ** is that the counter used is stored as part of each individual statement
000089 ** object (struct Vdbe). If, after the statement has run, its immediate
000090 ** constraint counter is greater than zero,
000091 ** it returns SQLITE_CONSTRAINT_FOREIGNKEY
000092 ** and the statement transaction is rolled back. An exception is an INSERT
000093 ** statement that inserts a single row only (no triggers). In this case,
000094 ** instead of using a counter, an exception is thrown immediately if the
000095 ** INSERT violates a foreign key constraint. This is necessary as such
000096 ** an INSERT does not open a statement transaction.
000097 **
000098 ** TODO: How should dropping a table be handled? How should renaming a
000099 ** table be handled?
000100 **
000101 **
000102 ** Query API Notes
000103 ** ---------------
000104 **
000105 ** Before coding an UPDATE or DELETE row operation, the code-generator
000106 ** for those two operations needs to know whether or not the operation
000107 ** requires any FK processing and, if so, which columns of the original
000108 ** row are required by the FK processing VDBE code (i.e. if FKs were
000109 ** implemented using triggers, which of the old.* columns would be
000110 ** accessed). No information is required by the code-generator before
000111 ** coding an INSERT operation. The functions used by the UPDATE/DELETE
000112 ** generation code to query for this information are:
000113 **
000114 ** sqlite3FkRequired() - Test to see if FK processing is required.
000115 ** sqlite3FkOldmask() - Query for the set of required old.* columns.
000116 **
000117 **
000118 ** Externally accessible module functions
000119 ** --------------------------------------
000120 **
000121 ** sqlite3FkCheck() - Check for foreign key violations.
000122 ** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.
000123 ** sqlite3FkDelete() - Delete an FKey structure.
000124 */
000125
000126 /*
000127 ** VDBE Calling Convention
000128 ** -----------------------
000129 **
000130 ** Example:
000131 **
000132 ** For the following INSERT statement:
000133 **
000134 ** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
000135 ** INSERT INTO t1 VALUES(1, 2, 3.1);
000136 **
000137 ** Register (x): 2 (type integer)
000138 ** Register (x+1): 1 (type integer)
000139 ** Register (x+2): NULL (type NULL)
000140 ** Register (x+3): 3.1 (type real)
000141 */
000142
000143 /*
000144 ** A foreign key constraint requires that the key columns in the parent
000145 ** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
000146 ** Given that pParent is the parent table for foreign key constraint pFKey,
000147 ** search the schema for a unique index on the parent key columns.
000148 **
000149 ** If successful, zero is returned. If the parent key is an INTEGER PRIMARY
000150 ** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx
000151 ** is set to point to the unique index.
000152 **
000153 ** If the parent key consists of a single column (the foreign key constraint
000154 ** is not a composite foreign key), output variable *paiCol is set to NULL.
000155 ** Otherwise, it is set to point to an allocated array of size N, where
000156 ** N is the number of columns in the parent key. The first element of the
000157 ** array is the index of the child table column that is mapped by the FK
000158 ** constraint to the parent table column stored in the left-most column
000159 ** of index *ppIdx. The second element of the array is the index of the
000160 ** child table column that corresponds to the second left-most column of
000161 ** *ppIdx, and so on.
000162 **
000163 ** If the required index cannot be found, either because:
000164 **
000165 ** 1) The named parent key columns do not exist, or
000166 **
000167 ** 2) The named parent key columns do exist, but are not subject to a
000168 ** UNIQUE or PRIMARY KEY constraint, or
000169 **
000170 ** 3) No parent key columns were provided explicitly as part of the
000171 ** foreign key definition, and the parent table does not have a
000172 ** PRIMARY KEY, or
000173 **
000174 ** 4) No parent key columns were provided explicitly as part of the
000175 ** foreign key definition, and the PRIMARY KEY of the parent table
000176 ** consists of a different number of columns to the child key in
000177 ** the child table.
000178 **
000179 ** then non-zero is returned, and a "foreign key mismatch" error loaded
000180 ** into pParse. If an OOM error occurs, non-zero is returned and the
000181 ** pParse->db->mallocFailed flag is set.
000182 */
000183 int sqlite3FkLocateIndex(
000184 Parse *pParse, /* Parse context to store any error in */
000185 Table *pParent, /* Parent table of FK constraint pFKey */
000186 FKey *pFKey, /* Foreign key to find index for */
000187 Index **ppIdx, /* OUT: Unique index on parent table */
000188 int **paiCol /* OUT: Map of index columns in pFKey */
000189 ){
000190 Index *pIdx = 0; /* Value to return via *ppIdx */
000191 int *aiCol = 0; /* Value to return via *paiCol */
000192 int nCol = pFKey->nCol; /* Number of columns in parent key */
000193 char *zKey = pFKey->aCol[0].zCol; /* Name of left-most parent key column */
000194
000195 /* The caller is responsible for zeroing output parameters. */
000196 assert( ppIdx && *ppIdx==0 );
000197 assert( !paiCol || *paiCol==0 );
000198 assert( pParse );
000199
000200 /* If this is a non-composite (single column) foreign key, check if it
000201 ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx
000202 ** and *paiCol set to zero and return early.
000203 **
000204 ** Otherwise, for a composite foreign key (more than one column), allocate
000205 ** space for the aiCol array (returned via output parameter *paiCol).
000206 ** Non-composite foreign keys do not require the aiCol array.
000207 */
000208 if( nCol==1 ){
000209 /* The FK maps to the IPK if any of the following are true:
000210 **
000211 ** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly
000212 ** mapped to the primary key of table pParent, or
000213 ** 2) The FK is explicitly mapped to a column declared as INTEGER
000214 ** PRIMARY KEY.
000215 */
000216 if( pParent->iPKey>=0 ){
000217 if( !zKey ) return 0;
000218 if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;
000219 }
000220 }else if( paiCol ){
000221 assert( nCol>1 );
000222 aiCol = (int *)sqlite3DbMallocRawNN(pParse->db, nCol*sizeof(int));
000223 if( !aiCol ) return 1;
000224 *paiCol = aiCol;
000225 }
000226
000227 for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){
000228 if( pIdx->nKeyCol==nCol && IsUniqueIndex(pIdx) && pIdx->pPartIdxWhere==0 ){
000229 /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
000230 ** of columns. If each indexed column corresponds to a foreign key
000231 ** column of pFKey, then this index is a winner. */
000232
000233 if( zKey==0 ){
000234 /* If zKey is NULL, then this foreign key is implicitly mapped to
000235 ** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be
000236 ** identified by the test. */
000237 if( IsPrimaryKeyIndex(pIdx) ){
000238 if( aiCol ){
000239 int i;
000240 for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;
000241 }
000242 break;
000243 }
000244 }else{
000245 /* If zKey is non-NULL, then this foreign key was declared to
000246 ** map to an explicit list of columns in table pParent. Check if this
000247 ** index matches those columns. Also, check that the index uses
000248 ** the default collation sequences for each column. */
000249 int i, j;
000250 for(i=0; i<nCol; i++){
000251 i16 iCol = pIdx->aiColumn[i]; /* Index of column in parent tbl */
000252 const char *zDfltColl; /* Def. collation for column */
000253 char *zIdxCol; /* Name of indexed column */
000254
000255 if( iCol<0 ) break; /* No foreign keys against expression indexes */
000256
000257 /* If the index uses a collation sequence that is different from
000258 ** the default collation sequence for the column, this index is
000259 ** unusable. Bail out early in this case. */
000260 zDfltColl = pParent->aCol[iCol].zColl;
000261 if( !zDfltColl ) zDfltColl = sqlite3StrBINARY;
000262 if( sqlite3StrICmp(pIdx->azColl[i], zDfltColl) ) break;
000263
000264 zIdxCol = pParent->aCol[iCol].zName;
000265 for(j=0; j<nCol; j++){
000266 if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
000267 if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
000268 break;
000269 }
000270 }
000271 if( j==nCol ) break;
000272 }
000273 if( i==nCol ) break; /* pIdx is usable */
000274 }
000275 }
000276 }
000277
000278 if( !pIdx ){
000279 if( !pParse->disableTriggers ){
000280 sqlite3ErrorMsg(pParse,
000281 "foreign key mismatch - \"%w\" referencing \"%w\"",
000282 pFKey->pFrom->zName, pFKey->zTo);
000283 }
000284 sqlite3DbFree(pParse->db, aiCol);
000285 return 1;
000286 }
000287
000288 *ppIdx = pIdx;
000289 return 0;
000290 }
000291
000292 /*
000293 ** This function is called when a row is inserted into or deleted from the
000294 ** child table of foreign key constraint pFKey. If an SQL UPDATE is executed
000295 ** on the child table of pFKey, this function is invoked twice for each row
000296 ** affected - once to "delete" the old row, and then again to "insert" the
000297 ** new row.
000298 **
000299 ** Each time it is called, this function generates VDBE code to locate the
000300 ** row in the parent table that corresponds to the row being inserted into
000301 ** or deleted from the child table. If the parent row can be found, no
000302 ** special action is taken. Otherwise, if the parent row can *not* be
000303 ** found in the parent table:
000304 **
000305 ** Operation | FK type | Action taken
000306 ** --------------------------------------------------------------------------
000307 ** INSERT immediate Increment the "immediate constraint counter".
000308 **
000309 ** DELETE immediate Decrement the "immediate constraint counter".
000310 **
000311 ** INSERT deferred Increment the "deferred constraint counter".
000312 **
000313 ** DELETE deferred Decrement the "deferred constraint counter".
000314 **
000315 ** These operations are identified in the comment at the top of this file
000316 ** (fkey.c) as "I.1" and "D.1".
000317 */
000318 static void fkLookupParent(
000319 Parse *pParse, /* Parse context */
000320 int iDb, /* Index of database housing pTab */
000321 Table *pTab, /* Parent table of FK pFKey */
000322 Index *pIdx, /* Unique index on parent key columns in pTab */
000323 FKey *pFKey, /* Foreign key constraint */
000324 int *aiCol, /* Map from parent key columns to child table columns */
000325 int regData, /* Address of array containing child table row */
000326 int nIncr, /* Increment constraint counter by this */
000327 int isIgnore /* If true, pretend pTab contains all NULL values */
000328 ){
000329 int i; /* Iterator variable */
000330 Vdbe *v = sqlite3GetVdbe(pParse); /* Vdbe to add code to */
000331 int iCur = pParse->nTab - 1; /* Cursor number to use */
000332 int iOk = sqlite3VdbeMakeLabel(pParse); /* jump here if parent key found */
000333
000334 sqlite3VdbeVerifyAbortable(v,
000335 (!pFKey->isDeferred
000336 && !(pParse->db->flags & SQLITE_DeferFKs)
000337 && !pParse->pToplevel
000338 && !pParse->isMultiWrite) ? OE_Abort : OE_Ignore);
000339
000340 /* If nIncr is less than zero, then check at runtime if there are any
000341 ** outstanding constraints to resolve. If there are not, there is no need
000342 ** to check if deleting this row resolves any outstanding violations.
000343 **
000344 ** Check if any of the key columns in the child table row are NULL. If
000345 ** any are, then the constraint is considered satisfied. No need to
000346 ** search for a matching row in the parent table. */
000347 if( nIncr<0 ){
000348 sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);
000349 VdbeCoverage(v);
000350 }
000351 for(i=0; i<pFKey->nCol; i++){
000352 int iReg = sqlite3TableColumnToStorage(pFKey->pFrom,aiCol[i]) + regData + 1;
000353 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); VdbeCoverage(v);
000354 }
000355
000356 if( isIgnore==0 ){
000357 if( pIdx==0 ){
000358 /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
000359 ** column of the parent table (table pTab). */
000360 int iMustBeInt; /* Address of MustBeInt instruction */
000361 int regTemp = sqlite3GetTempReg(pParse);
000362
000363 /* Invoke MustBeInt to coerce the child key value to an integer (i.e.
000364 ** apply the affinity of the parent key). If this fails, then there
000365 ** is no matching parent key. Before using MustBeInt, make a copy of
000366 ** the value. Otherwise, the value inserted into the child key column
000367 ** will have INTEGER affinity applied to it, which may not be correct. */
000368 sqlite3VdbeAddOp2(v, OP_SCopy,
000369 sqlite3TableColumnToStorage(pFKey->pFrom,aiCol[0])+1+regData, regTemp);
000370 iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0);
000371 VdbeCoverage(v);
000372
000373 /* If the parent table is the same as the child table, and we are about
000374 ** to increment the constraint-counter (i.e. this is an INSERT operation),
000375 ** then check if the row being inserted matches itself. If so, do not
000376 ** increment the constraint-counter. */
000377 if( pTab==pFKey->pFrom && nIncr==1 ){
000378 sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp); VdbeCoverage(v);
000379 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
000380 }
000381
000382 sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
000383 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp); VdbeCoverage(v);
000384 sqlite3VdbeGoto(v, iOk);
000385 sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
000386 sqlite3VdbeJumpHere(v, iMustBeInt);
000387 sqlite3ReleaseTempReg(pParse, regTemp);
000388 }else{
000389 int nCol = pFKey->nCol;
000390 int regTemp = sqlite3GetTempRange(pParse, nCol);
000391 int regRec = sqlite3GetTempReg(pParse);
000392
000393 sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
000394 sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
000395 for(i=0; i<nCol; i++){
000396 sqlite3VdbeAddOp2(v, OP_Copy,
000397 sqlite3TableColumnToStorage(pFKey->pFrom, aiCol[i])+1+regData,
000398 regTemp+i);
000399 }
000400
000401 /* If the parent table is the same as the child table, and we are about
000402 ** to increment the constraint-counter (i.e. this is an INSERT operation),
000403 ** then check if the row being inserted matches itself. If so, do not
000404 ** increment the constraint-counter.
000405 **
000406 ** If any of the parent-key values are NULL, then the row cannot match
000407 ** itself. So set JUMPIFNULL to make sure we do the OP_Found if any
000408 ** of the parent-key values are NULL (at this point it is known that
000409 ** none of the child key values are).
000410 */
000411 if( pTab==pFKey->pFrom && nIncr==1 ){
000412 int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1;
000413 for(i=0; i<nCol; i++){
000414 int iChild = sqlite3TableColumnToStorage(pFKey->pFrom,aiCol[i])
000415 +1+regData;
000416 int iParent = 1+regData;
000417 iParent += sqlite3TableColumnToStorage(pIdx->pTable,
000418 pIdx->aiColumn[i]);
000419 assert( pIdx->aiColumn[i]>=0 );
000420 assert( aiCol[i]!=pTab->iPKey );
000421 if( pIdx->aiColumn[i]==pTab->iPKey ){
000422 /* The parent key is a composite key that includes the IPK column */
000423 iParent = regData;
000424 }
000425 sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent); VdbeCoverage(v);
000426 sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
000427 }
000428 sqlite3VdbeGoto(v, iOk);
000429 }
000430
000431 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, nCol, regRec,
000432 sqlite3IndexAffinityStr(pParse->db,pIdx), nCol);
000433 sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); VdbeCoverage(v);
000434
000435 sqlite3ReleaseTempReg(pParse, regRec);
000436 sqlite3ReleaseTempRange(pParse, regTemp, nCol);
000437 }
000438 }
000439
000440 if( !pFKey->isDeferred && !(pParse->db->flags & SQLITE_DeferFKs)
000441 && !pParse->pToplevel
000442 && !pParse->isMultiWrite
000443 ){
000444 /* Special case: If this is an INSERT statement that will insert exactly
000445 ** one row into the table, raise a constraint immediately instead of
000446 ** incrementing a counter. This is necessary as the VM code is being
000447 ** generated for will not open a statement transaction. */
000448 assert( nIncr==1 );
000449 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
000450 OE_Abort, 0, P4_STATIC, P5_ConstraintFK);
000451 }else{
000452 if( nIncr>0 && pFKey->isDeferred==0 ){
000453 sqlite3MayAbort(pParse);
000454 }
000455 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
000456 }
000457
000458 sqlite3VdbeResolveLabel(v, iOk);
000459 sqlite3VdbeAddOp1(v, OP_Close, iCur);
000460 }
000461
000462
000463 /*
000464 ** Return an Expr object that refers to a memory register corresponding
000465 ** to column iCol of table pTab.
000466 **
000467 ** regBase is the first of an array of register that contains the data
000468 ** for pTab. regBase itself holds the rowid. regBase+1 holds the first
000469 ** column. regBase+2 holds the second column, and so forth.
000470 */
000471 static Expr *exprTableRegister(
000472 Parse *pParse, /* Parsing and code generating context */
000473 Table *pTab, /* The table whose content is at r[regBase]... */
000474 int regBase, /* Contents of table pTab */
000475 i16 iCol /* Which column of pTab is desired */
000476 ){
000477 Expr *pExpr;
000478 Column *pCol;
000479 const char *zColl;
000480 sqlite3 *db = pParse->db;
000481
000482 pExpr = sqlite3Expr(db, TK_REGISTER, 0);
000483 if( pExpr ){
000484 if( iCol>=0 && iCol!=pTab->iPKey ){
000485 pCol = &pTab->aCol[iCol];
000486 pExpr->iTable = regBase + sqlite3TableColumnToStorage(pTab,iCol) + 1;
000487 pExpr->affExpr = pCol->affinity;
000488 zColl = pCol->zColl;
000489 if( zColl==0 ) zColl = db->pDfltColl->zName;
000490 pExpr = sqlite3ExprAddCollateString(pParse, pExpr, zColl);
000491 }else{
000492 pExpr->iTable = regBase;
000493 pExpr->affExpr = SQLITE_AFF_INTEGER;
000494 }
000495 }
000496 return pExpr;
000497 }
000498
000499 /*
000500 ** Return an Expr object that refers to column iCol of table pTab which
000501 ** has cursor iCur.
000502 */
000503 static Expr *exprTableColumn(
000504 sqlite3 *db, /* The database connection */
000505 Table *pTab, /* The table whose column is desired */
000506 int iCursor, /* The open cursor on the table */
000507 i16 iCol /* The column that is wanted */
000508 ){
000509 Expr *pExpr = sqlite3Expr(db, TK_COLUMN, 0);
000510 if( pExpr ){
000511 pExpr->y.pTab = pTab;
000512 pExpr->iTable = iCursor;
000513 pExpr->iColumn = iCol;
000514 }
000515 return pExpr;
000516 }
000517
000518 /*
000519 ** This function is called to generate code executed when a row is deleted
000520 ** from the parent table of foreign key constraint pFKey and, if pFKey is
000521 ** deferred, when a row is inserted into the same table. When generating
000522 ** code for an SQL UPDATE operation, this function may be called twice -
000523 ** once to "delete" the old row and once to "insert" the new row.
000524 **
000525 ** Parameter nIncr is passed -1 when inserting a row (as this may decrease
000526 ** the number of FK violations in the db) or +1 when deleting one (as this
000527 ** may increase the number of FK constraint problems).
000528 **
000529 ** The code generated by this function scans through the rows in the child
000530 ** table that correspond to the parent table row being deleted or inserted.
000531 ** For each child row found, one of the following actions is taken:
000532 **
000533 ** Operation | FK type | Action taken
000534 ** --------------------------------------------------------------------------
000535 ** DELETE immediate Increment the "immediate constraint counter".
000536 ** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
000537 ** throw a "FOREIGN KEY constraint failed" exception.
000538 **
000539 ** INSERT immediate Decrement the "immediate constraint counter".
000540 **
000541 ** DELETE deferred Increment the "deferred constraint counter".
000542 ** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
000543 ** throw a "FOREIGN KEY constraint failed" exception.
000544 **
000545 ** INSERT deferred Decrement the "deferred constraint counter".
000546 **
000547 ** These operations are identified in the comment at the top of this file
000548 ** (fkey.c) as "I.2" and "D.2".
000549 */
000550 static void fkScanChildren(
000551 Parse *pParse, /* Parse context */
000552 SrcList *pSrc, /* The child table to be scanned */
000553 Table *pTab, /* The parent table */
000554 Index *pIdx, /* Index on parent covering the foreign key */
000555 FKey *pFKey, /* The foreign key linking pSrc to pTab */
000556 int *aiCol, /* Map from pIdx cols to child table cols */
000557 int regData, /* Parent row data starts here */
000558 int nIncr /* Amount to increment deferred counter by */
000559 ){
000560 sqlite3 *db = pParse->db; /* Database handle */
000561 int i; /* Iterator variable */
000562 Expr *pWhere = 0; /* WHERE clause to scan with */
000563 NameContext sNameContext; /* Context used to resolve WHERE clause */
000564 WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */
000565 int iFkIfZero = 0; /* Address of OP_FkIfZero */
000566 Vdbe *v = sqlite3GetVdbe(pParse);
000567
000568 assert( pIdx==0 || pIdx->pTable==pTab );
000569 assert( pIdx==0 || pIdx->nKeyCol==pFKey->nCol );
000570 assert( pIdx!=0 || pFKey->nCol==1 );
000571 assert( pIdx!=0 || HasRowid(pTab) );
000572
000573 if( nIncr<0 ){
000574 iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
000575 VdbeCoverage(v);
000576 }
000577
000578 /* Create an Expr object representing an SQL expression like:
000579 **
000580 ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
000581 **
000582 ** The collation sequence used for the comparison should be that of
000583 ** the parent key columns. The affinity of the parent key column should
000584 ** be applied to each child key value before the comparison takes place.
000585 */
000586 for(i=0; i<pFKey->nCol; i++){
000587 Expr *pLeft; /* Value from parent table row */
000588 Expr *pRight; /* Column ref to child table */
000589 Expr *pEq; /* Expression (pLeft = pRight) */
000590 i16 iCol; /* Index of column in child table */
000591 const char *zCol; /* Name of column in child table */
000592
000593 iCol = pIdx ? pIdx->aiColumn[i] : -1;
000594 pLeft = exprTableRegister(pParse, pTab, regData, iCol);
000595 iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
000596 assert( iCol>=0 );
000597 zCol = pFKey->pFrom->aCol[iCol].zName;
000598 pRight = sqlite3Expr(db, TK_ID, zCol);
000599 pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight);
000600 pWhere = sqlite3ExprAnd(pParse, pWhere, pEq);
000601 }
000602
000603 /* If the child table is the same as the parent table, then add terms
000604 ** to the WHERE clause that prevent this entry from being scanned.
000605 ** The added WHERE clause terms are like this:
000606 **
000607 ** $current_rowid!=rowid
000608 ** NOT( $current_a==a AND $current_b==b AND ... )
000609 **
000610 ** The first form is used for rowid tables. The second form is used
000611 ** for WITHOUT ROWID tables. In the second form, the *parent* key is
000612 ** (a,b,...). Either the parent or primary key could be used to
000613 ** uniquely identify the current row, but the parent key is more convenient
000614 ** as the required values have already been loaded into registers
000615 ** by the caller.
000616 */
000617 if( pTab==pFKey->pFrom && nIncr>0 ){
000618 Expr *pNe; /* Expression (pLeft != pRight) */
000619 Expr *pLeft; /* Value from parent table row */
000620 Expr *pRight; /* Column ref to child table */
000621 if( HasRowid(pTab) ){
000622 pLeft = exprTableRegister(pParse, pTab, regData, -1);
000623 pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, -1);
000624 pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight);
000625 }else{
000626 Expr *pEq, *pAll = 0;
000627 assert( pIdx!=0 );
000628 for(i=0; i<pIdx->nKeyCol; i++){
000629 i16 iCol = pIdx->aiColumn[i];
000630 assert( iCol>=0 );
000631 pLeft = exprTableRegister(pParse, pTab, regData, iCol);
000632 pRight = sqlite3Expr(db, TK_ID, pTab->aCol[iCol].zName);
000633 pEq = sqlite3PExpr(pParse, TK_IS, pLeft, pRight);
000634 pAll = sqlite3ExprAnd(pParse, pAll, pEq);
000635 }
000636 pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0);
000637 }
000638 pWhere = sqlite3ExprAnd(pParse, pWhere, pNe);
000639 }
000640
000641 /* Resolve the references in the WHERE clause. */
000642 memset(&sNameContext, 0, sizeof(NameContext));
000643 sNameContext.pSrcList = pSrc;
000644 sNameContext.pParse = pParse;
000645 sqlite3ResolveExprNames(&sNameContext, pWhere);
000646
000647 /* Create VDBE to loop through the entries in pSrc that match the WHERE
000648 ** clause. For each row found, increment either the deferred or immediate
000649 ** foreign key constraint counter. */
000650 if( pParse->nErr==0 ){
000651 pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
000652 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
000653 if( pWInfo ){
000654 sqlite3WhereEnd(pWInfo);
000655 }
000656 }
000657
000658 /* Clean up the WHERE clause constructed above. */
000659 sqlite3ExprDelete(db, pWhere);
000660 if( iFkIfZero ){
000661 sqlite3VdbeJumpHere(v, iFkIfZero);
000662 }
000663 }
000664
000665 /*
000666 ** This function returns a linked list of FKey objects (connected by
000667 ** FKey.pNextTo) holding all children of table pTab. For example,
000668 ** given the following schema:
000669 **
000670 ** CREATE TABLE t1(a PRIMARY KEY);
000671 ** CREATE TABLE t2(b REFERENCES t1(a);
000672 **
000673 ** Calling this function with table "t1" as an argument returns a pointer
000674 ** to the FKey structure representing the foreign key constraint on table
000675 ** "t2". Calling this function with "t2" as the argument would return a
000676 ** NULL pointer (as there are no FK constraints for which t2 is the parent
000677 ** table).
000678 */
000679 FKey *sqlite3FkReferences(Table *pTab){
000680 return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName);
000681 }
000682
000683 /*
000684 ** The second argument is a Trigger structure allocated by the
000685 ** fkActionTrigger() routine. This function deletes the Trigger structure
000686 ** and all of its sub-components.
000687 **
000688 ** The Trigger structure or any of its sub-components may be allocated from
000689 ** the lookaside buffer belonging to database handle dbMem.
000690 */
000691 static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
000692 if( p ){
000693 TriggerStep *pStep = p->step_list;
000694 sqlite3ExprDelete(dbMem, pStep->pWhere);
000695 sqlite3ExprListDelete(dbMem, pStep->pExprList);
000696 sqlite3SelectDelete(dbMem, pStep->pSelect);
000697 sqlite3ExprDelete(dbMem, p->pWhen);
000698 sqlite3DbFree(dbMem, p);
000699 }
000700 }
000701
000702 /*
000703 ** This function is called to generate code that runs when table pTab is
000704 ** being dropped from the database. The SrcList passed as the second argument
000705 ** to this function contains a single entry guaranteed to resolve to
000706 ** table pTab.
000707 **
000708 ** Normally, no code is required. However, if either
000709 **
000710 ** (a) The table is the parent table of a FK constraint, or
000711 ** (b) The table is the child table of a deferred FK constraint and it is
000712 ** determined at runtime that there are outstanding deferred FK
000713 ** constraint violations in the database,
000714 **
000715 ** then the equivalent of "DELETE FROM <tbl>" is executed before dropping
000716 ** the table from the database. Triggers are disabled while running this
000717 ** DELETE, but foreign key actions are not.
000718 */
000719 void sqlite3FkDropTable(Parse *pParse, SrcList *pName, Table *pTab){
000720 sqlite3 *db = pParse->db;
000721 if( (db->flags&SQLITE_ForeignKeys) && !IsVirtual(pTab) ){
000722 int iSkip = 0;
000723 Vdbe *v = sqlite3GetVdbe(pParse);
000724
000725 assert( v ); /* VDBE has already been allocated */
000726 assert( pTab->pSelect==0 ); /* Not a view */
000727 if( sqlite3FkReferences(pTab)==0 ){
000728 /* Search for a deferred foreign key constraint for which this table
000729 ** is the child table. If one cannot be found, return without
000730 ** generating any VDBE code. If one can be found, then jump over
000731 ** the entire DELETE if there are no outstanding deferred constraints
000732 ** when this statement is run. */
000733 FKey *p;
000734 for(p=pTab->pFKey; p; p=p->pNextFrom){
000735 if( p->isDeferred || (db->flags & SQLITE_DeferFKs) ) break;
000736 }
000737 if( !p ) return;
000738 iSkip = sqlite3VdbeMakeLabel(pParse);
000739 sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip); VdbeCoverage(v);
000740 }
000741
000742 pParse->disableTriggers = 1;
000743 sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0, 0, 0);
000744 pParse->disableTriggers = 0;
000745
000746 /* If the DELETE has generated immediate foreign key constraint
000747 ** violations, halt the VDBE and return an error at this point, before
000748 ** any modifications to the schema are made. This is because statement
000749 ** transactions are not able to rollback schema changes.
000750 **
000751 ** If the SQLITE_DeferFKs flag is set, then this is not required, as
000752 ** the statement transaction will not be rolled back even if FK
000753 ** constraints are violated.
000754 */
000755 if( (db->flags & SQLITE_DeferFKs)==0 ){
000756 sqlite3VdbeVerifyAbortable(v, OE_Abort);
000757 sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
000758 VdbeCoverage(v);
000759 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
000760 OE_Abort, 0, P4_STATIC, P5_ConstraintFK);
000761 }
000762
000763 if( iSkip ){
000764 sqlite3VdbeResolveLabel(v, iSkip);
000765 }
000766 }
000767 }
000768
000769
000770 /*
000771 ** The second argument points to an FKey object representing a foreign key
000772 ** for which pTab is the child table. An UPDATE statement against pTab
000773 ** is currently being processed. For each column of the table that is
000774 ** actually updated, the corresponding element in the aChange[] array
000775 ** is zero or greater (if a column is unmodified the corresponding element
000776 ** is set to -1). If the rowid column is modified by the UPDATE statement
000777 ** the bChngRowid argument is non-zero.
000778 **
000779 ** This function returns true if any of the columns that are part of the
000780 ** child key for FK constraint *p are modified.
000781 */
000782 static int fkChildIsModified(
000783 Table *pTab, /* Table being updated */
000784 FKey *p, /* Foreign key for which pTab is the child */
000785 int *aChange, /* Array indicating modified columns */
000786 int bChngRowid /* True if rowid is modified by this update */
000787 ){
000788 int i;
000789 for(i=0; i<p->nCol; i++){
000790 int iChildKey = p->aCol[i].iFrom;
000791 if( aChange[iChildKey]>=0 ) return 1;
000792 if( iChildKey==pTab->iPKey && bChngRowid ) return 1;
000793 }
000794 return 0;
000795 }
000796
000797 /*
000798 ** The second argument points to an FKey object representing a foreign key
000799 ** for which pTab is the parent table. An UPDATE statement against pTab
000800 ** is currently being processed. For each column of the table that is
000801 ** actually updated, the corresponding element in the aChange[] array
000802 ** is zero or greater (if a column is unmodified the corresponding element
000803 ** is set to -1). If the rowid column is modified by the UPDATE statement
000804 ** the bChngRowid argument is non-zero.
000805 **
000806 ** This function returns true if any of the columns that are part of the
000807 ** parent key for FK constraint *p are modified.
000808 */
000809 static int fkParentIsModified(
000810 Table *pTab,
000811 FKey *p,
000812 int *aChange,
000813 int bChngRowid
000814 ){
000815 int i;
000816 for(i=0; i<p->nCol; i++){
000817 char *zKey = p->aCol[i].zCol;
000818 int iKey;
000819 for(iKey=0; iKey<pTab->nCol; iKey++){
000820 if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){
000821 Column *pCol = &pTab->aCol[iKey];
000822 if( zKey ){
000823 if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1;
000824 }else if( pCol->colFlags & COLFLAG_PRIMKEY ){
000825 return 1;
000826 }
000827 }
000828 }
000829 }
000830 return 0;
000831 }
000832
000833 /*
000834 ** Return true if the parser passed as the first argument is being
000835 ** used to code a trigger that is really a "SET NULL" action belonging
000836 ** to trigger pFKey.
000837 */
000838 static int isSetNullAction(Parse *pParse, FKey *pFKey){
000839 Parse *pTop = sqlite3ParseToplevel(pParse);
000840 if( pTop->pTriggerPrg ){
000841 Trigger *p = pTop->pTriggerPrg->pTrigger;
000842 if( (p==pFKey->apTrigger[0] && pFKey->aAction[0]==OE_SetNull)
000843 || (p==pFKey->apTrigger[1] && pFKey->aAction[1]==OE_SetNull)
000844 ){
000845 return 1;
000846 }
000847 }
000848 return 0;
000849 }
000850
000851 /*
000852 ** This function is called when inserting, deleting or updating a row of
000853 ** table pTab to generate VDBE code to perform foreign key constraint
000854 ** processing for the operation.
000855 **
000856 ** For a DELETE operation, parameter regOld is passed the index of the
000857 ** first register in an array of (pTab->nCol+1) registers containing the
000858 ** rowid of the row being deleted, followed by each of the column values
000859 ** of the row being deleted, from left to right. Parameter regNew is passed
000860 ** zero in this case.
000861 **
000862 ** For an INSERT operation, regOld is passed zero and regNew is passed the
000863 ** first register of an array of (pTab->nCol+1) registers containing the new
000864 ** row data.
000865 **
000866 ** For an UPDATE operation, this function is called twice. Once before
000867 ** the original record is deleted from the table using the calling convention
000868 ** described for DELETE. Then again after the original record is deleted
000869 ** but before the new record is inserted using the INSERT convention.
000870 */
000871 void sqlite3FkCheck(
000872 Parse *pParse, /* Parse context */
000873 Table *pTab, /* Row is being deleted from this table */
000874 int regOld, /* Previous row data is stored here */
000875 int regNew, /* New row data is stored here */
000876 int *aChange, /* Array indicating UPDATEd columns (or 0) */
000877 int bChngRowid /* True if rowid is UPDATEd */
000878 ){
000879 sqlite3 *db = pParse->db; /* Database handle */
000880 FKey *pFKey; /* Used to iterate through FKs */
000881 int iDb; /* Index of database containing pTab */
000882 const char *zDb; /* Name of database containing pTab */
000883 int isIgnoreErrors = pParse->disableTriggers;
000884
000885 /* Exactly one of regOld and regNew should be non-zero. */
000886 assert( (regOld==0)!=(regNew==0) );
000887
000888 /* If foreign-keys are disabled, this function is a no-op. */
000889 if( (db->flags&SQLITE_ForeignKeys)==0 ) return;
000890
000891 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
000892 zDb = db->aDb[iDb].zDbSName;
000893
000894 /* Loop through all the foreign key constraints for which pTab is the
000895 ** child table (the table that the foreign key definition is part of). */
000896 for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
000897 Table *pTo; /* Parent table of foreign key pFKey */
000898 Index *pIdx = 0; /* Index on key columns in pTo */
000899 int *aiFree = 0;
000900 int *aiCol;
000901 int iCol;
000902 int i;
000903 int bIgnore = 0;
000904
000905 if( aChange
000906 && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0
000907 && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0
000908 ){
000909 continue;
000910 }
000911
000912 /* Find the parent table of this foreign key. Also find a unique index
000913 ** on the parent key columns in the parent table. If either of these
000914 ** schema items cannot be located, set an error in pParse and return
000915 ** early. */
000916 if( pParse->disableTriggers ){
000917 pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
000918 }else{
000919 pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
000920 }
000921 if( !pTo || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
000922 assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) );
000923 if( !isIgnoreErrors || db->mallocFailed ) return;
000924 if( pTo==0 ){
000925 /* If isIgnoreErrors is true, then a table is being dropped. In this
000926 ** case SQLite runs a "DELETE FROM xxx" on the table being dropped
000927 ** before actually dropping it in order to check FK constraints.
000928 ** If the parent table of an FK constraint on the current table is
000929 ** missing, behave as if it is empty. i.e. decrement the relevant
000930 ** FK counter for each row of the current table with non-NULL keys.
000931 */
000932 Vdbe *v = sqlite3GetVdbe(pParse);
000933 int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1;
000934 for(i=0; i<pFKey->nCol; i++){
000935 int iFromCol, iReg;
000936 iFromCol = pFKey->aCol[i].iFrom;
000937 iReg = sqlite3TableColumnToStorage(pFKey->pFrom,iFromCol) + regOld+1;
000938 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iJump); VdbeCoverage(v);
000939 }
000940 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, -1);
000941 }
000942 continue;
000943 }
000944 assert( pFKey->nCol==1 || (aiFree && pIdx) );
000945
000946 if( aiFree ){
000947 aiCol = aiFree;
000948 }else{
000949 iCol = pFKey->aCol[0].iFrom;
000950 aiCol = &iCol;
000951 }
000952 for(i=0; i<pFKey->nCol; i++){
000953 if( aiCol[i]==pTab->iPKey ){
000954 aiCol[i] = -1;
000955 }
000956 assert( pIdx==0 || pIdx->aiColumn[i]>=0 );
000957 #ifndef SQLITE_OMIT_AUTHORIZATION
000958 /* Request permission to read the parent key columns. If the
000959 ** authorization callback returns SQLITE_IGNORE, behave as if any
000960 ** values read from the parent table are NULL. */
000961 if( db->xAuth ){
000962 int rcauth;
000963 char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName;
000964 rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb);
000965 bIgnore = (rcauth==SQLITE_IGNORE);
000966 }
000967 #endif
000968 }
000969
000970 /* Take a shared-cache advisory read-lock on the parent table. Allocate
000971 ** a cursor to use to search the unique index on the parent key columns
000972 ** in the parent table. */
000973 sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
000974 pParse->nTab++;
000975
000976 if( regOld!=0 ){
000977 /* A row is being removed from the child table. Search for the parent.
000978 ** If the parent does not exist, removing the child row resolves an
000979 ** outstanding foreign key constraint violation. */
000980 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1, bIgnore);
000981 }
000982 if( regNew!=0 && !isSetNullAction(pParse, pFKey) ){
000983 /* A row is being added to the child table. If a parent row cannot
000984 ** be found, adding the child row has violated the FK constraint.
000985 **
000986 ** If this operation is being performed as part of a trigger program
000987 ** that is actually a "SET NULL" action belonging to this very
000988 ** foreign key, then omit this scan altogether. As all child key
000989 ** values are guaranteed to be NULL, it is not possible for adding
000990 ** this row to cause an FK violation. */
000991 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1, bIgnore);
000992 }
000993
000994 sqlite3DbFree(db, aiFree);
000995 }
000996
000997 /* Loop through all the foreign key constraints that refer to this table.
000998 ** (the "child" constraints) */
000999 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
001000 Index *pIdx = 0; /* Foreign key index for pFKey */
001001 SrcList *pSrc;
001002 int *aiCol = 0;
001003
001004 if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){
001005 continue;
001006 }
001007
001008 if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs)
001009 && !pParse->pToplevel && !pParse->isMultiWrite
001010 ){
001011 assert( regOld==0 && regNew!=0 );
001012 /* Inserting a single row into a parent table cannot cause (or fix)
001013 ** an immediate foreign key violation. So do nothing in this case. */
001014 continue;
001015 }
001016
001017 if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
001018 if( !isIgnoreErrors || db->mallocFailed ) return;
001019 continue;
001020 }
001021 assert( aiCol || pFKey->nCol==1 );
001022
001023 /* Create a SrcList structure containing the child table. We need the
001024 ** child table as a SrcList for sqlite3WhereBegin() */
001025 pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
001026 if( pSrc ){
001027 struct SrcList_item *pItem = pSrc->a;
001028 pItem->pTab = pFKey->pFrom;
001029 pItem->zName = pFKey->pFrom->zName;
001030 pItem->pTab->nTabRef++;
001031 pItem->iCursor = pParse->nTab++;
001032
001033 if( regNew!=0 ){
001034 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1);
001035 }
001036 if( regOld!=0 ){
001037 int eAction = pFKey->aAction[aChange!=0];
001038 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1);
001039 /* If this is a deferred FK constraint, or a CASCADE or SET NULL
001040 ** action applies, then any foreign key violations caused by
001041 ** removing the parent key will be rectified by the action trigger.
001042 ** So do not set the "may-abort" flag in this case.
001043 **
001044 ** Note 1: If the FK is declared "ON UPDATE CASCADE", then the
001045 ** may-abort flag will eventually be set on this statement anyway
001046 ** (when this function is called as part of processing the UPDATE
001047 ** within the action trigger).
001048 **
001049 ** Note 2: At first glance it may seem like SQLite could simply omit
001050 ** all OP_FkCounter related scans when either CASCADE or SET NULL
001051 ** applies. The trouble starts if the CASCADE or SET NULL action
001052 ** trigger causes other triggers or action rules attached to the
001053 ** child table to fire. In these cases the fk constraint counters
001054 ** might be set incorrectly if any OP_FkCounter related scans are
001055 ** omitted. */
001056 if( !pFKey->isDeferred && eAction!=OE_Cascade && eAction!=OE_SetNull ){
001057 sqlite3MayAbort(pParse);
001058 }
001059 }
001060 pItem->zName = 0;
001061 sqlite3SrcListDelete(db, pSrc);
001062 }
001063 sqlite3DbFree(db, aiCol);
001064 }
001065 }
001066
001067 #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
001068
001069 /*
001070 ** This function is called before generating code to update or delete a
001071 ** row contained in table pTab.
001072 */
001073 u32 sqlite3FkOldmask(
001074 Parse *pParse, /* Parse context */
001075 Table *pTab /* Table being modified */
001076 ){
001077 u32 mask = 0;
001078 if( pParse->db->flags&SQLITE_ForeignKeys ){
001079 FKey *p;
001080 int i;
001081 for(p=pTab->pFKey; p; p=p->pNextFrom){
001082 for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
001083 }
001084 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
001085 Index *pIdx = 0;
001086 sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
001087 if( pIdx ){
001088 for(i=0; i<pIdx->nKeyCol; i++){
001089 assert( pIdx->aiColumn[i]>=0 );
001090 mask |= COLUMN_MASK(pIdx->aiColumn[i]);
001091 }
001092 }
001093 }
001094 }
001095 return mask;
001096 }
001097
001098
001099 /*
001100 ** This function is called before generating code to update or delete a
001101 ** row contained in table pTab. If the operation is a DELETE, then
001102 ** parameter aChange is passed a NULL value. For an UPDATE, aChange points
001103 ** to an array of size N, where N is the number of columns in table pTab.
001104 ** If the i'th column is not modified by the UPDATE, then the corresponding
001105 ** entry in the aChange[] array is set to -1. If the column is modified,
001106 ** the value is 0 or greater. Parameter chngRowid is set to true if the
001107 ** UPDATE statement modifies the rowid fields of the table.
001108 **
001109 ** If any foreign key processing will be required, this function returns
001110 ** non-zero. If there is no foreign key related processing, this function
001111 ** returns zero.
001112 **
001113 ** For an UPDATE, this function returns 2 if:
001114 **
001115 ** * There are any FKs for which pTab is the child and the parent table, or
001116 ** * the UPDATE modifies one or more parent keys for which the action is
001117 ** not "NO ACTION" (i.e. is CASCADE, SET DEFAULT or SET NULL).
001118 **
001119 ** Or, assuming some other foreign key processing is required, 1.
001120 */
001121 int sqlite3FkRequired(
001122 Parse *pParse, /* Parse context */
001123 Table *pTab, /* Table being modified */
001124 int *aChange, /* Non-NULL for UPDATE operations */
001125 int chngRowid /* True for UPDATE that affects rowid */
001126 ){
001127 int eRet = 0;
001128 if( pParse->db->flags&SQLITE_ForeignKeys ){
001129 if( !aChange ){
001130 /* A DELETE operation. Foreign key processing is required if the
001131 ** table in question is either the child or parent table for any
001132 ** foreign key constraint. */
001133 eRet = (sqlite3FkReferences(pTab) || pTab->pFKey);
001134 }else{
001135 /* This is an UPDATE. Foreign key processing is only required if the
001136 ** operation modifies one or more child or parent key columns. */
001137 FKey *p;
001138
001139 /* Check if any child key columns are being modified. */
001140 for(p=pTab->pFKey; p; p=p->pNextFrom){
001141 if( 0==sqlite3_stricmp(pTab->zName, p->zTo) ) return 2;
001142 if( fkChildIsModified(pTab, p, aChange, chngRowid) ){
001143 eRet = 1;
001144 }
001145 }
001146
001147 /* Check if any parent key columns are being modified. */
001148 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
001149 if( fkParentIsModified(pTab, p, aChange, chngRowid) ){
001150 if( p->aAction[1]!=OE_None ) return 2;
001151 eRet = 1;
001152 }
001153 }
001154 }
001155 }
001156 return eRet;
001157 }
001158
001159 /*
001160 ** This function is called when an UPDATE or DELETE operation is being
001161 ** compiled on table pTab, which is the parent table of foreign-key pFKey.
001162 ** If the current operation is an UPDATE, then the pChanges parameter is
001163 ** passed a pointer to the list of columns being modified. If it is a
001164 ** DELETE, pChanges is passed a NULL pointer.
001165 **
001166 ** It returns a pointer to a Trigger structure containing a trigger
001167 ** equivalent to the ON UPDATE or ON DELETE action specified by pFKey.
001168 ** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is
001169 ** returned (these actions require no special handling by the triggers
001170 ** sub-system, code for them is created by fkScanChildren()).
001171 **
001172 ** For example, if pFKey is the foreign key and pTab is table "p" in
001173 ** the following schema:
001174 **
001175 ** CREATE TABLE p(pk PRIMARY KEY);
001176 ** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE);
001177 **
001178 ** then the returned trigger structure is equivalent to:
001179 **
001180 ** CREATE TRIGGER ... DELETE ON p BEGIN
001181 ** DELETE FROM c WHERE ck = old.pk;
001182 ** END;
001183 **
001184 ** The returned pointer is cached as part of the foreign key object. It
001185 ** is eventually freed along with the rest of the foreign key object by
001186 ** sqlite3FkDelete().
001187 */
001188 static Trigger *fkActionTrigger(
001189 Parse *pParse, /* Parse context */
001190 Table *pTab, /* Table being updated or deleted from */
001191 FKey *pFKey, /* Foreign key to get action for */
001192 ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */
001193 ){
001194 sqlite3 *db = pParse->db; /* Database handle */
001195 int action; /* One of OE_None, OE_Cascade etc. */
001196 Trigger *pTrigger; /* Trigger definition to return */
001197 int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */
001198
001199 action = pFKey->aAction[iAction];
001200 if( action==OE_Restrict && (db->flags & SQLITE_DeferFKs) ){
001201 return 0;
001202 }
001203 pTrigger = pFKey->apTrigger[iAction];
001204
001205 if( action!=OE_None && !pTrigger ){
001206 char const *zFrom; /* Name of child table */
001207 int nFrom; /* Length in bytes of zFrom */
001208 Index *pIdx = 0; /* Parent key index for this FK */
001209 int *aiCol = 0; /* child table cols -> parent key cols */
001210 TriggerStep *pStep = 0; /* First (only) step of trigger program */
001211 Expr *pWhere = 0; /* WHERE clause of trigger step */
001212 ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */
001213 Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */
001214 int i; /* Iterator variable */
001215 Expr *pWhen = 0; /* WHEN clause for the trigger */
001216
001217 if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
001218 assert( aiCol || pFKey->nCol==1 );
001219
001220 for(i=0; i<pFKey->nCol; i++){
001221 Token tOld = { "old", 3 }; /* Literal "old" token */
001222 Token tNew = { "new", 3 }; /* Literal "new" token */
001223 Token tFromCol; /* Name of column in child table */
001224 Token tToCol; /* Name of column in parent table */
001225 int iFromCol; /* Idx of column in child table */
001226 Expr *pEq; /* tFromCol = OLD.tToCol */
001227
001228 iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
001229 assert( iFromCol>=0 );
001230 assert( pIdx!=0 || (pTab->iPKey>=0 && pTab->iPKey<pTab->nCol) );
001231 assert( pIdx==0 || pIdx->aiColumn[i]>=0 );
001232 sqlite3TokenInit(&tToCol,
001233 pTab->aCol[pIdx ? pIdx->aiColumn[i] : pTab->iPKey].zName);
001234 sqlite3TokenInit(&tFromCol, pFKey->pFrom->aCol[iFromCol].zName);
001235
001236 /* Create the expression "OLD.zToCol = zFromCol". It is important
001237 ** that the "OLD.zToCol" term is on the LHS of the = operator, so
001238 ** that the affinity and collation sequence associated with the
001239 ** parent table are used for the comparison. */
001240 pEq = sqlite3PExpr(pParse, TK_EQ,
001241 sqlite3PExpr(pParse, TK_DOT,
001242 sqlite3ExprAlloc(db, TK_ID, &tOld, 0),
001243 sqlite3ExprAlloc(db, TK_ID, &tToCol, 0)),
001244 sqlite3ExprAlloc(db, TK_ID, &tFromCol, 0)
001245 );
001246 pWhere = sqlite3ExprAnd(pParse, pWhere, pEq);
001247
001248 /* For ON UPDATE, construct the next term of the WHEN clause.
001249 ** The final WHEN clause will be like this:
001250 **
001251 ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
001252 */
001253 if( pChanges ){
001254 pEq = sqlite3PExpr(pParse, TK_IS,
001255 sqlite3PExpr(pParse, TK_DOT,
001256 sqlite3ExprAlloc(db, TK_ID, &tOld, 0),
001257 sqlite3ExprAlloc(db, TK_ID, &tToCol, 0)),
001258 sqlite3PExpr(pParse, TK_DOT,
001259 sqlite3ExprAlloc(db, TK_ID, &tNew, 0),
001260 sqlite3ExprAlloc(db, TK_ID, &tToCol, 0))
001261 );
001262 pWhen = sqlite3ExprAnd(pParse, pWhen, pEq);
001263 }
001264
001265 if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){
001266 Expr *pNew;
001267 if( action==OE_Cascade ){
001268 pNew = sqlite3PExpr(pParse, TK_DOT,
001269 sqlite3ExprAlloc(db, TK_ID, &tNew, 0),
001270 sqlite3ExprAlloc(db, TK_ID, &tToCol, 0));
001271 }else if( action==OE_SetDflt ){
001272 Column *pCol = pFKey->pFrom->aCol + iFromCol;
001273 Expr *pDflt;
001274 if( pCol->colFlags & COLFLAG_GENERATED ){
001275 testcase( pCol->colFlags & COLFLAG_VIRTUAL );
001276 testcase( pCol->colFlags & COLFLAG_STORED );
001277 pDflt = 0;
001278 }else{
001279 pDflt = pCol->pDflt;
001280 }
001281 if( pDflt ){
001282 pNew = sqlite3ExprDup(db, pDflt, 0);
001283 }else{
001284 pNew = sqlite3ExprAlloc(db, TK_NULL, 0, 0);
001285 }
001286 }else{
001287 pNew = sqlite3ExprAlloc(db, TK_NULL, 0, 0);
001288 }
001289 pList = sqlite3ExprListAppend(pParse, pList, pNew);
001290 sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
001291 }
001292 }
001293 sqlite3DbFree(db, aiCol);
001294
001295 zFrom = pFKey->pFrom->zName;
001296 nFrom = sqlite3Strlen30(zFrom);
001297
001298 if( action==OE_Restrict ){
001299 Token tFrom;
001300 Expr *pRaise;
001301
001302 tFrom.z = zFrom;
001303 tFrom.n = nFrom;
001304 pRaise = sqlite3Expr(db, TK_RAISE, "FOREIGN KEY constraint failed");
001305 if( pRaise ){
001306 pRaise->affExpr = OE_Abort;
001307 }
001308 pSelect = sqlite3SelectNew(pParse,
001309 sqlite3ExprListAppend(pParse, 0, pRaise),
001310 sqlite3SrcListAppend(pParse, 0, &tFrom, 0),
001311 pWhere,
001312 0, 0, 0, 0, 0
001313 );
001314 pWhere = 0;
001315 }
001316
001317 /* Disable lookaside memory allocation */
001318 DisableLookaside;
001319
001320 pTrigger = (Trigger *)sqlite3DbMallocZero(db,
001321 sizeof(Trigger) + /* struct Trigger */
001322 sizeof(TriggerStep) + /* Single step in trigger program */
001323 nFrom + 1 /* Space for pStep->zTarget */
001324 );
001325 if( pTrigger ){
001326 pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
001327 pStep->zTarget = (char *)&pStep[1];
001328 memcpy((char *)pStep->zTarget, zFrom, nFrom);
001329
001330 pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
001331 pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
001332 pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
001333 if( pWhen ){
001334 pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0);
001335 pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);
001336 }
001337 }
001338
001339 /* Re-enable the lookaside buffer, if it was disabled earlier. */
001340 EnableLookaside;
001341
001342 sqlite3ExprDelete(db, pWhere);
001343 sqlite3ExprDelete(db, pWhen);
001344 sqlite3ExprListDelete(db, pList);
001345 sqlite3SelectDelete(db, pSelect);
001346 if( db->mallocFailed==1 ){
001347 fkTriggerDelete(db, pTrigger);
001348 return 0;
001349 }
001350 assert( pStep!=0 );
001351 assert( pTrigger!=0 );
001352
001353 switch( action ){
001354 case OE_Restrict:
001355 pStep->op = TK_SELECT;
001356 break;
001357 case OE_Cascade:
001358 if( !pChanges ){
001359 pStep->op = TK_DELETE;
001360 break;
001361 }
001362 default:
001363 pStep->op = TK_UPDATE;
001364 }
001365 pStep->pTrig = pTrigger;
001366 pTrigger->pSchema = pTab->pSchema;
001367 pTrigger->pTabSchema = pTab->pSchema;
001368 pFKey->apTrigger[iAction] = pTrigger;
001369 pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE);
001370 }
001371
001372 return pTrigger;
001373 }
001374
001375 /*
001376 ** This function is called when deleting or updating a row to implement
001377 ** any required CASCADE, SET NULL or SET DEFAULT actions.
001378 */
001379 void sqlite3FkActions(
001380 Parse *pParse, /* Parse context */
001381 Table *pTab, /* Table being updated or deleted from */
001382 ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */
001383 int regOld, /* Address of array containing old row */
001384 int *aChange, /* Array indicating UPDATEd columns (or 0) */
001385 int bChngRowid /* True if rowid is UPDATEd */
001386 ){
001387 /* If foreign-key support is enabled, iterate through all FKs that
001388 ** refer to table pTab. If there is an action associated with the FK
001389 ** for this operation (either update or delete), invoke the associated
001390 ** trigger sub-program. */
001391 if( pParse->db->flags&SQLITE_ForeignKeys ){
001392 FKey *pFKey; /* Iterator variable */
001393 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
001394 if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){
001395 Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges);
001396 if( pAct ){
001397 sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0);
001398 }
001399 }
001400 }
001401 }
001402 }
001403
001404 #endif /* ifndef SQLITE_OMIT_TRIGGER */
001405
001406 /*
001407 ** Free all memory associated with foreign key definitions attached to
001408 ** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
001409 ** hash table.
001410 */
001411 void sqlite3FkDelete(sqlite3 *db, Table *pTab){
001412 FKey *pFKey; /* Iterator variable */
001413 FKey *pNext; /* Copy of pFKey->pNextFrom */
001414
001415 assert( db==0 || IsVirtual(pTab)
001416 || sqlite3SchemaMutexHeld(db, 0, pTab->pSchema) );
001417 for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){
001418
001419 /* Remove the FK from the fkeyHash hash table. */
001420 if( !db || db->pnBytesFreed==0 ){
001421 if( pFKey->pPrevTo ){
001422 pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
001423 }else{
001424 void *p = (void *)pFKey->pNextTo;
001425 const char *z = (p ? pFKey->pNextTo->zTo : pFKey->zTo);
001426 sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, p);
001427 }
001428 if( pFKey->pNextTo ){
001429 pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
001430 }
001431 }
001432
001433 /* EV: R-30323-21917 Each foreign key constraint in SQLite is
001434 ** classified as either immediate or deferred.
001435 */
001436 assert( pFKey->isDeferred==0 || pFKey->isDeferred==1 );
001437
001438 /* Delete any triggers created to implement actions for this FK. */
001439 #ifndef SQLITE_OMIT_TRIGGER
001440 fkTriggerDelete(db, pFKey->apTrigger[0]);
001441 fkTriggerDelete(db, pFKey->apTrigger[1]);
001442 #endif
001443
001444 pNext = pFKey->pNextFrom;
001445 sqlite3DbFree(db, pFKey);
001446 }
001447 }
001448 #endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */