Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add missing comments to fkey.c. Also, change the terminology used for comments and names in fkey.c from "referenced/referencing" to "parent/child". This is arguably less correct, but is easier to follow. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
540c2d18e14c277b55f95729fbafc04c |
User & Date: | dan 2009-09-23 08:43:36.000 |
Context
2009-09-23
| ||
12:06 | Add extra tests for foreign key support. (check-in: 7d086afe69 user: dan tags: trunk) | |
08:43 | Add missing comments to fkey.c. Also, change the terminology used for comments and names in fkey.c from "referenced/referencing" to "parent/child". This is arguably less correct, but is easier to follow. (check-in: 540c2d18e1 user: dan tags: trunk) | |
03:01 | Do not run ON UPDATE actions of a foreign key constraint unless at least one column value really does change. (check-in: 71ac8e28e5 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
2222 2223 2224 2225 2226 2227 2228 | pFKey->aCol[i].zCol = z; memcpy(z, pToCol->a[i].zName, n); z[n] = 0; z += n+1; } } pFKey->isDeferred = 0; | | | < | 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 | pFKey->aCol[i].zCol = z; memcpy(z, pToCol->a[i].zName, n); z[n] = 0; z += n+1; } } pFKey->isDeferred = 0; pFKey->aAction[0] = (u8)(flags & 0xff); /* ON DELETE action */ pFKey->aAction[1] = (u8)((flags >> 8 ) & 0xff); /* ON UPDATE action */ pNextTo = (FKey *)sqlite3HashInsert(&p->pSchema->fkeyHash, pFKey->zTo, sqlite3Strlen30(pFKey->zTo), (void *)pFKey ); if( pNextTo==pFKey ){ db->mallocFailed = 1; goto fk_end; |
︙ | ︙ |
Changes to src/fkey.c.
︙ | ︙ | |||
44 45 46 47 48 49 50 | ** 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: ** | | | | | | | | | | | | | < | | > > > > > > > > > > > | | > > | | > > | > > > > > > > > | < | < | < > | | < | > > | | | > | < < > | > | | | | | | | | | | | | | | | | | | | | 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 | ** 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, throw an ** exception for an immediate FK, or increment the counter for a ** deferred FK. ** ** I.2) For each deferred 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 deferred 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, throw an exception for an ** immediate FK, or increment the counter for a deferred FK. ** ** 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. ** ** 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 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 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. */ static int locateFkeyIndex( 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 ); /* 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) The FK is explicitly mapped to "rowid", "oid" or "_rowid_", or ** 2) There is an explicit INTEGER PRIMARY KEY column and the FK is ** implicitly mapped to the primary key of table pParent, or ** 3) The FK is explicitly mapped to a column declared as INTEGER ** PRIMARY KEY. */ if( zKey && sqlite3IsRowid(zKey) ) return 0; 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 *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int)); if( !aiCol ) return 1; *paiCol = aiCol; } for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){ /* 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 (Index.autoIndex==2). */ if( pIdx->autoIndex==2 ){ if( aiCol ) memcpy(aiCol, pIdx->aiColumn, sizeof(int)*nCol); 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. */ int i, j; for(i=0; i<nCol; i++){ char *zIdxCol = pParent->aCol[pIdx->aiColumn[i]].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; |
︙ | ︙ | |||
233 234 235 236 237 238 239 | return 1; } *ppIdx = pIdx; return 0; } | > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | | | | | | | | | | | | 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 | return 1; } *ppIdx = pIdx; return 0; } /* ** This function is called when a row is inserted into the child table of ** foreign key constraint pFKey and, if pFKey is deferred, when a row is ** deleted from the child table of 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 Throw a "foreign key constraint failed" exception. ** ** INSERT deferred Increment the "deferred constraint counter". ** ** DELETE deferred Decrement the "deferred constraint counter". ** ** This function is never called for a delete on the child table of an ** immediate foreign key constraint. 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 /* If deferred FK, increment counter by this */ ){ 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 */ assert( pFKey->isDeferred || nIncr==1 ); /* Check if any of the key columns in the child table row are ** NULL. If any are, then the constraint is satisfied. No need ** to search for a matching row in the parent table. */ for(i=0; i<pFKey->nCol; i++){ int iReg = aiCol[i] + regData + 1; sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); } if( pIdx==0 ){ /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY ** column of the parent table (table pTab). */ int iReg = pFKey->aCol[0].iFrom + regData + 1; sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iReg); sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk); sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); }else{ int regRec = sqlite3GetTempReg(pParse); |
︙ | ︙ | |||
303 304 305 306 307 308 309 | pParse, OE_Abort, "foreign key constraint failed", P4_STATIC ); } sqlite3VdbeResolveLabel(v, iOk); } | > > > > > > > > > > > > > > > > > > > > > > > > > | | | | | | | 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | pParse, OE_Abort, "foreign key constraint failed", P4_STATIC ); } sqlite3VdbeResolveLabel(v, iOk); } /* ** This function is called to generate code executed when a row is deleted ** from the parent table of foreign key constraint pFKey and, if pFKey is ** deferred, when a row is inserted into the same table. When generating ** code for an SQL UPDATE operation, this function may be called twice - ** once to "delete" the old row and once to "insert" the new row. ** ** The code generated by this function scans through the rows in the child ** table that correspond to the parent table row being deleted or inserted. ** For each child row found, one of the following actions is taken: ** ** Operation | FK type | Action taken ** -------------------------------------------------------------------------- ** DELETE immediate Throw a "foreign key constraint failed" exception. ** ** 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". ** ** This function is never called for an INSERT operation on the parent table ** of an immediate foreign key constraint. These operations are identified in ** the comment at the top of this file (fkey.c) as "I.2" and "D.2". */ static void fkScanChildren( Parse *pParse, /* Parse context */ SrcList *pSrc, /* SrcList containing the table to scan */ Index *pIdx, /* Foreign key index */ FKey *pFKey, /* Foreign key relationship */ int *aiCol, /* Map from pIdx cols to child table cols */ int regData, /* Referenced table data starts here */ int nIncr /* Amount to increment deferred counter by */ ){ sqlite3 *db = pParse->db; /* Database handle */ int i; /* Iterator variable */ Expr *pWhere = 0; /* WHERE clause to scan with */ NameContext sNameContext; /* Context used to resolve WHERE clause */ WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */ for(i=0; i<pFKey->nCol; i++){ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ Expr *pEq; /* Expression (pLeft = pRight) */ int iCol; /* Index of column in child table */ const char *zCol; /* Name of column in child table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); if( pLeft ){ pLeft->iTable = (pIdx ? (regData+pIdx->aiColumn[i]+1) : regData); } iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; if( iCol<0 ){ |
︙ | ︙ | |||
370 371 372 373 374 375 376 | /* Clean up the WHERE clause constructed above. */ sqlite3ExprDelete(db, pWhere); } /* ** This function returns a pointer to the head of a linked list of FK | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 | /* Clean up the WHERE clause constructed above. */ sqlite3ExprDelete(db, pWhere); } /* ** This function returns a pointer to the head of a linked list of FK ** constraints for which table pTab is the parent table. 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). */ static FKey *fkRefering(Table *pTab){ int nName = sqlite3Strlen30(pTab->zName); return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName); } /* ** 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); sqlite3ExprDelete(dbMem, p->pWhen); sqlite3DbFree(dbMem, p); } } /* ** 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 UPDATE operation, regOld is the first in an array of (pTab->nCol+1) ** registers containing the old rowid and column values of the row being ** updated, and regNew is the first in an array of the same size containing ** the corresponding new values. Parameter pChanges is passed the list of ** columns being updated by the statement. ** ** 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. ** ** If an error occurs, an error message is left in the pParse structure. */ void sqlite3FkCheck( Parse *pParse, /* Parse context */ Table *pTab, /* Row is being deleted from this table */ ExprList *pChanges, /* Changed columns if this is an UPDATE */ int regOld, /* Previous row data is stored here */ int regNew /* New row data is stored here */ ){ |
︙ | ︙ | |||
421 422 423 424 425 426 427 | /* If foreign-keys are disabled, this function is a no-op. */ if( (db->flags&SQLITE_ForeignKeys)==0 ) return; v = sqlite3GetVdbe(pParse); iDb = sqlite3SchemaToIndex(db, pTab->pSchema); zDb = db->aDb[iDb].zName; | | > | > > > | | | | | 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 | /* If foreign-keys are disabled, this function is a no-op. */ if( (db->flags&SQLITE_ForeignKeys)==0 ) return; v = sqlite3GetVdbe(pParse); iDb = sqlite3SchemaToIndex(db, pTab->pSchema); zDb = db->aDb[iDb].zName; /* 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; /* If this is a DELETE operation and the foreign key is not deferred, ** nothing to do. A DELETE on the child table cannot cause the FK ** constraint to fail. */ if( pFKey->isDeferred==0 && regNew==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. */ pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb); if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ) return; assert( pFKey->nCol==1 || (aiFree && pIdx) ); /* If the key does not overlap with the pChanges list, skip this FK. */ if( pChanges ){ /* TODO */ |
︙ | ︙ | |||
457 458 459 460 461 462 463 | } for(i=0; i<pFKey->nCol; i++){ if( aiCol[i]==pTab->iPKey ){ aiCol[i] = -1; } } | | | | | | | 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 | } for(i=0; i<pFKey->nCol; i++){ if( aiCol[i]==pTab->iPKey ){ aiCol[i] = -1; } } /* 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 && pFKey->isDeferred ){ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1); } if( regNew!=0 ){ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1); } sqlite3DbFree(db, aiFree); } /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){ |
︙ | ︙ | |||
490 491 492 493 494 495 496 | ** 2) an UPDATE operation and the FK action is a trigger-action, or ** 3) a DELETE operation and the FK action is a trigger-action. ** ** A "trigger-action" is one of CASCADE, SET DEFAULT or SET NULL. */ if( pFKey->isDeferred==0 ){ if( regOld==0 ) continue; /* 1 */ | | | | | | | | | | | | < | 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 | ** 2) an UPDATE operation and the FK action is a trigger-action, or ** 3) a DELETE operation and the FK action is a trigger-action. ** ** A "trigger-action" is one of CASCADE, SET DEFAULT or SET NULL. */ if( pFKey->isDeferred==0 ){ if( regOld==0 ) continue; /* 1 */ if( regNew!=0 && pFKey->aAction[1]>OE_Restrict ) continue; /* 2 */ if( regNew==0 && pFKey->aAction[0]>OE_Restrict ) continue; /* 3 */ } if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return; assert( aiCol || pFKey->nCol==1 ); /* Check if this update statement has modified any of the child key ** columns for this foreign key constraint. If it has not, there is ** no need to search the child table for rows in violation. This is ** just an optimization. Things would work fine without this check. */ if( pChanges ){ /* TODO */ } /* Create a SrcList structure containing a single table (the table ** the foreign key that refers to this table is attached to). This ** is required for the sqlite3WhereXXX() interface. */ pSrc = sqlite3SrcListAppend(db, 0, 0, 0); if( pSrc ){ pSrc->a->pTab = pFKey->pFrom; pSrc->a->pTab->nRef++; pSrc->a->iCursor = pParse->nTab++; /* If this is an UPDATE, and none of the columns associated with this ** FK have been modified, do not scan the child table. Unlike the ** compile-time test implemented above, this is not just an ** optimization. It is required so that immediate foreign keys do not ** throw exceptions when the user executes a statement like: ** ** UPDATE refd_table SET refd_column = refd_column */ if( pChanges ){ int i; int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1; for(i=0; i<pFKey->nCol; i++){ int iOff = (pIdx ? pIdx->aiColumn[i] : -1) + 1; sqlite3VdbeAddOp3(v, OP_Ne, regOld+iOff, iJump, regNew+iOff); } iGoto = sqlite3VdbeAddOp0(v, OP_Goto); } if( regNew!=0 && pFKey->isDeferred ){ fkScanChildren(pParse, pSrc, pIdx, pFKey, aiCol, regNew, -1); } if( regOld!=0 ){ /* If there is a RESTRICT action configured for the current operation ** on the parent table of this FK, then throw an exception ** immediately if the FK constraint is violated, even if this is a ** deferred trigger. That's what RESTRICT means. To defer checking ** the constraint, the FK should specify NO ACTION (represented ** using OE_None). NO ACTION is the default. */ fkScanChildren(pParse, pSrc, pIdx, pFKey, aiCol, regOld, pFKey->aAction[pChanges!=0]!=OE_Restrict ); } if( pChanges ){ sqlite3VdbeJumpHere(v, iGoto); } sqlite3SrcListDelete(db, pSrc); |
︙ | ︙ | |||
615 616 617 618 619 620 621 622 | if( pChanges || p->isDeferred ) return 1; } if( fkRefering(pTab) ) return 1; } return 0; } static Trigger *fkActionTrigger( | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > < | | < < < < | | | | | 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 | if( pChanges || p->isDeferred ) return 1; } if( fkRefering(pTab) ) return 1; } return 0; } /* ** This function is called when an UPDATE or DELETE operation is being ** compiled on table pTab, which is the parent table of foreign-key pFKey. ** If the current operation is an UPDATE, then the pChanges parameter is ** passed a pointer to the list of columns being modified. If it is a ** DELETE, pChanges is passed a NULL pointer. ** ** It returns a pointer to a Trigger structure containing a trigger ** equivalent to the ON UPDATE or ON DELETE action specified by pFKey. ** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is ** returned (these actions require no special handling by the triggers ** sub-system, code for them is created by fkScanChildren()). ** ** For example, if pFKey is the foreign key and pTab is table "p" in ** the following schema: ** ** CREATE TABLE p(pk PRIMARY KEY); ** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE); ** ** then the returned trigger structure is equivalent to: ** ** CREATE TRIGGER ... DELETE ON p BEGIN ** DELETE FROM c WHERE ck = old.pk; ** END; ** ** The returned pointer is cached as part of the foreign key object. It ** is eventually freed along with the rest of the foreign key object by ** sqlite3FkDelete(). */ static Trigger *fkActionTrigger( Parse *pParse, /* Parse context */ Table *pTab, /* Table being updated or deleted from */ FKey *pFKey, /* Foreign key to get action for */ ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */ ){ sqlite3 *db = pParse->db; /* Database handle */ int action; /* One of OE_None, OE_Cascade etc. */ Trigger *pTrigger; /* Trigger definition to return */ int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */ action = pFKey->aAction[iAction]; pTrigger = pFKey->apTrigger[iAction]; assert( OE_SetNull>OE_Restrict && OE_SetDflt>OE_Restrict ); assert( OE_Cascade>OE_Restrict && OE_None<OE_Restrict ); if( action>OE_Restrict && !pTrigger ){ u8 enableLookaside; /* Copy of db->lookaside.bEnabled */ char const *zFrom; /* Name of child table */ int nFrom; /* Length in bytes of zFrom */ Index *pIdx = 0; /* Parent key index for this FK */ int *aiCol = 0; /* child table cols -> parent key cols */ TriggerStep *pStep; /* First (only) step of trigger program */ Expr *pWhere = 0; /* WHERE clause of trigger step */ ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */ int i; /* Iterator variable */ Expr *pWhen = 0; /* WHEN clause for the trigger */ if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0; assert( aiCol || pFKey->nCol==1 ); for(i=0; i<pFKey->nCol; i++){ Token tOld = { "old", 3 }; /* Literal "old" token */ Token tNew = { "new", 3 }; /* Literal "new" token */ Token tFromCol; /* Name of column in child table */ Token tToCol; /* Name of column in parent table */ int iFromCol; /* Idx of column in child table */ Expr *pEq; /* tFromCol = OLD.tToCol */ iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid"; tFromCol.z = iFromCol<0 ? "oid" : pFKey->pFrom->aCol[iFromCol].zName; tToCol.n = sqlite3Strlen30(tToCol.z); |
︙ | ︙ | |||
762 763 764 765 766 767 768 | return 0; } pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE; pStep->pTrig = pTrigger; pTrigger->pSchema = pTab->pSchema; pTrigger->pTabSchema = pTab->pSchema; | < < | | < < < < < < | 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 | return 0; } pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE; pStep->pTrig = pTrigger; pTrigger->pSchema = pTab->pSchema; pTrigger->pTabSchema = pTab->pSchema; pFKey->apTrigger[iAction] = pTrigger; pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE); } return pTrigger; } /* ** This function is called when deleting or updating a row to implement |
︙ | ︙ | |||
829 830 831 832 833 834 835 | } if( pFKey->pNextTo ){ pFKey->pNextTo->pPrevTo = pFKey->pPrevTo; } /* Delete any triggers created to implement actions for this FK. */ #ifndef SQLITE_OMIT_TRIGGER | | | | 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 | } if( pFKey->pNextTo ){ pFKey->pNextTo->pPrevTo = pFKey->pPrevTo; } /* Delete any triggers created to implement actions for this FK. */ #ifndef SQLITE_OMIT_TRIGGER fkTriggerDelete(pTab->dbMem, pFKey->apTrigger[0]); fkTriggerDelete(pTab->dbMem, pFKey->apTrigger[1]); #endif /* Delete the memory allocated for the FK structure. */ pNext = pFKey->pNextFrom; sqlite3DbFree(pTab->dbMem, pFKey); } } #endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */ |
Changes to src/parse.y.
︙ | ︙ | |||
310 311 312 313 314 315 316 | // The next group of rules parses the arguments to a REFERENCES clause // that determine if the referential integrity checking is deferred or // or immediate and which determine what action to take if a ref-integ // check fails. // %type refargs {int} | | | 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 | // The next group of rules parses the arguments to a REFERENCES clause // that determine if the referential integrity checking is deferred or // or immediate and which determine what action to take if a ref-integ // check fails. // %type refargs {int} refargs(A) ::= . { A = OE_None * 0x000101; } refargs(A) ::= refargs(X) refarg(Y). { A = (X & ~Y.mask) | Y.value; } %type refarg {struct {int value; int mask;}} refarg(A) ::= MATCH nm. { A.value = 0; A.mask = 0x000000; } refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; } refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; } %type refact {int} refact(A) ::= SET NULL. { A = OE_SetNull; } |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
977 978 979 980 981 982 983 | sqlite3VdbeSetColName(v, 5, COLNAME_NAME, "on_update", SQLITE_STATIC); sqlite3VdbeSetColName(v, 6, COLNAME_NAME, "on_delete", SQLITE_STATIC); sqlite3VdbeSetColName(v, 7, COLNAME_NAME, "match", SQLITE_STATIC); while(pFK){ int j; for(j=0; j<pFK->nCol; j++){ char *zCol = pFK->aCol[j].zCol; | | | | 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 | sqlite3VdbeSetColName(v, 5, COLNAME_NAME, "on_update", SQLITE_STATIC); sqlite3VdbeSetColName(v, 6, COLNAME_NAME, "on_delete", SQLITE_STATIC); sqlite3VdbeSetColName(v, 7, COLNAME_NAME, "match", SQLITE_STATIC); while(pFK){ int j; for(j=0; j<pFK->nCol; j++){ char *zCol = pFK->aCol[j].zCol; char *zOnDelete = (char *)actionName(pFK->aAction[0]); char *zOnUpdate = (char *)actionName(pFK->aAction[1]); sqlite3VdbeAddOp2(v, OP_Integer, i, 1); sqlite3VdbeAddOp2(v, OP_Integer, j, 2); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pFK->zTo, 0); sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, pTab->aCol[pFK->aCol[j].iFrom].zName, 0); sqlite3VdbeAddOp4(v, zCol ? OP_String8 : OP_Null, 0, 5, 0, zCol, 0); sqlite3VdbeAddOp4(v, OP_String8, 0, 6, 0, zOnUpdate, 0); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1286 1287 1288 1289 1290 1291 1292 | Table *pFrom; /* The table that contains the REFERENCES clause */ FKey *pNextFrom; /* Next foreign key in pFrom */ char *zTo; /* Name of table that the key points to */ FKey *pNextTo; /* Next foreign key on table named zTo */ FKey *pPrevTo; /* Previous foreign key on table named zTo */ int nCol; /* Number of columns in this key */ u8 isDeferred; /* True if constraint checking is deferred till COMMIT */ | < < < | | | 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 | Table *pFrom; /* The table that contains the REFERENCES clause */ FKey *pNextFrom; /* Next foreign key in pFrom */ char *zTo; /* Name of table that the key points to */ FKey *pNextTo; /* Next foreign key on table named zTo */ FKey *pPrevTo; /* Previous foreign key on table named zTo */ int nCol; /* Number of columns in this key */ u8 isDeferred; /* True if constraint checking is deferred till COMMIT */ u8 aAction[2]; /* ON DELETE and ON UPDATE actions, respectively */ Trigger *apTrigger[2]; /* Triggers for aAction[] actions */ struct sColMap { /* Mapping of columns in pFrom to columns in zTo */ int iFrom; /* Index of column in pFrom */ char *zCol; /* Name of column in zTo. If 0 use PRIMARY KEY */ } aCol[1]; /* One entry for each of nCol column s */ }; /* |
︙ | ︙ |