Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | If an update does not modify any child or parent key columns, omit foreign key processing for the statement. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
edff3500058eb8ad2381f855ef7a09ec |
| User & Date: | dan 2009-10-01 16:09:04 |
Context
|
2009-10-01
| ||
| 17:13 | Add test cases to cover the branches added by the previous commit. check-in: aaa005b6 user: dan tags: trunk | |
| 16:09 | If an update does not modify any child or parent key columns, omit foreign key processing for the statement. check-in: edff3500 user: dan tags: trunk | |
| 04:35 | Add tests to check that FK support interacts with count-changes correctly. check-in: 5b836615 user: dan tags: trunk | |
Changes
Changes to src/delete.c.
338 338 339 339 #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION 340 340 /* Special case: A DELETE without a WHERE clause deletes everything. 341 341 ** It is easier just to erase the whole table. Prior to version 3.6.5, 342 342 ** this optimization caused the row change count (the value returned by 343 343 ** API function sqlite3_count_changes) to be set incorrectly. */ 344 344 if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 345 - && 0==sqlite3FkRequired(pParse, pTab, 0) 345 + && 0==sqlite3FkRequired(pParse, pTab, 0, 0) 346 346 ){ 347 347 assert( !isView ); 348 348 sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt, 349 349 pTab->zName, P4_STATIC); 350 350 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 351 351 assert( pIdx->pSchema==pTab->pSchema ); 352 352 sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb); ................................................................................ 488 488 ** (this can happen if a trigger program has already deleted it), do 489 489 ** not attempt to delete it or fire any DELETE triggers. */ 490 490 iLabel = sqlite3VdbeMakeLabel(v); 491 491 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); 492 492 493 493 /* If there are any triggers to fire, allocate a range of registers to 494 494 ** use for the old.* references in the triggers. */ 495 - if( sqlite3FkRequired(pParse, pTab, 0) || pTrigger ){ 495 + if( sqlite3FkRequired(pParse, pTab, 0, 0) || pTrigger ){ 496 496 u32 mask; /* Mask of OLD.* columns in use */ 497 497 int iCol; /* Iterator used while populating OLD.* */ 498 498 499 499 /* TODO: Could use temporary registers here. Also could attempt to 500 500 ** avoid copying the contents of the rowid register. */ 501 501 mask = sqlite3TriggerOldmask(pParse, pTrigger, 0, pTab, onconf); 502 - mask |= sqlite3FkOldmask(pParse, pTab, 0); 502 + mask |= sqlite3FkOldmask(pParse, pTab); 503 503 iOld = pParse->nMem+1; 504 504 pParse->nMem += (1 + pTab->nCol); 505 505 506 506 /* Populate the OLD.* pseudo-table register array. These values will be 507 507 ** used by any BEFORE and AFTER triggers that exist. */ 508 508 sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld); 509 509 for(iCol=0; iCol<pTab->nCol; iCol++){ ................................................................................ 524 524 ** being deleted. Do not attempt to delete the row a second time, and 525 525 ** do not fire AFTER triggers. */ 526 526 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); 527 527 528 528 /* Do FK processing. This call checks that any FK constraints that 529 529 ** refer to this table (i.e. constraints attached to other tables) 530 530 ** are not violated by deleting this row. */ 531 - sqlite3FkCheck(pParse, pTab, 0, iOld, 0); 531 + sqlite3FkCheck(pParse, pTab, iOld, 0); 532 532 } 533 533 534 534 /* Delete the index and table entries. Skip this step if pTab is really 535 535 ** a view (in which case the only effect of the DELETE statement is to 536 536 ** fire the INSTEAD OF triggers). */ 537 537 if( pTab->pSelect==0 ){ 538 538 sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
Changes to src/fkey.c.
668 668 ** For an INSERT operation, regOld is passed zero and regNew is passed the 669 669 ** first register of an array of (pTab->nCol+1) registers containing the new 670 670 ** row data. 671 671 ** 672 672 ** For an UPDATE operation, this function is called twice. Once before 673 673 ** the original record is deleted from the table using the calling convention 674 674 ** described for DELETE. Then again after the original record is deleted 675 -** but before the new record is inserted using the INSERT convention. In 676 -** both cases parameter pChanges is passed the list of columns being 677 -** updated by the statement. 675 +** but before the new record is inserted using the INSERT convention. 678 676 */ 679 677 void sqlite3FkCheck( 680 678 Parse *pParse, /* Parse context */ 681 679 Table *pTab, /* Row is being deleted from this table */ 682 - ExprList *pChanges, /* Changed columns if this is an UPDATE */ 683 680 int regOld, /* Previous row data is stored here */ 684 681 int regNew /* New row data is stored here */ 685 682 ){ 686 683 sqlite3 *db = pParse->db; /* Database handle */ 687 684 Vdbe *v; /* VM to write code to */ 688 685 FKey *pFKey; /* Used to iterate through FKs */ 689 686 int iDb; /* Index of database containing pTab */ ................................................................................ 721 718 } 722 719 if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){ 723 720 if( !isIgnoreErrors || db->mallocFailed ) return; 724 721 continue; 725 722 } 726 723 assert( pFKey->nCol==1 || (aiFree && pIdx) ); 727 724 728 - /* If the key does not overlap with the pChanges list, skip this FK. */ 729 - if( pChanges ){ 730 - /* TODO */ 731 - } 732 - 733 725 if( aiFree ){ 734 726 aiCol = aiFree; 735 727 }else{ 736 728 iCol = pFKey->aCol[0].iFrom; 737 729 aiCol = &iCol; 738 730 } 739 731 for(i=0; i<pFKey->nCol; i++){ ................................................................................ 778 770 779 771 if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ 780 772 if( !isIgnoreErrors || db->mallocFailed ) return; 781 773 continue; 782 774 } 783 775 assert( aiCol || pFKey->nCol==1 ); 784 776 785 - /* Check if this update statement has modified any of the child key 786 - ** columns for this foreign key constraint. If it has not, there is 787 - ** no need to search the child table for rows in violation. This is 788 - ** just an optimization. Things would work fine without this check. */ 789 - if( pChanges ){ 790 - /* TODO */ 791 - } 792 - 793 777 /* Create a SrcList structure containing a single table (the table 794 778 ** the foreign key that refers to this table is attached to). This 795 779 ** is required for the sqlite3WhereXXX() interface. */ 796 780 pSrc = sqlite3SrcListAppend(db, 0, 0, 0); 797 781 if( pSrc ){ 798 782 pSrc->a->pTab = pFKey->pFrom; 799 783 pSrc->a->pTab->nRef++; ................................................................................ 818 802 } 819 803 } 820 804 821 805 #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) 822 806 823 807 /* 824 808 ** This function is called before generating code to update or delete a 825 -** row contained in table pTab. If the operation is an update, then 826 -** pChanges is a pointer to the list of columns to modify. If this is a 827 -** delete, then pChanges is NULL. 809 +** row contained in table pTab. 828 810 */ 829 811 u32 sqlite3FkOldmask( 830 812 Parse *pParse, /* Parse context */ 831 - Table *pTab, /* Table being modified */ 832 - ExprList *pChanges /* Non-NULL for UPDATE operations */ 813 + Table *pTab /* Table being modified */ 833 814 ){ 834 815 u32 mask = 0; 835 816 if( pParse->db->flags&SQLITE_ForeignKeys ){ 836 817 FKey *p; 837 818 int i; 838 819 for(p=pTab->pFKey; p; p=p->pNextFrom){ 839 820 for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom); ................................................................................ 847 828 } 848 829 } 849 830 return mask; 850 831 } 851 832 852 833 /* 853 834 ** This function is called before generating code to update or delete a 854 -** row contained in table pTab. If the operation is an update, then 855 -** pChanges is a pointer to the list of columns to modify. If this is a 856 -** delete, then pChanges is NULL. 835 +** row contained in table pTab. If the operation is a DELETE, then 836 +** parameter aChange is passed a NULL value. For an UPDATE, aChange points 837 +** to an array of size N, where N is the number of columns in table pTab. 838 +** If the i'th column is not modified by the UPDATE, then the corresponding 839 +** entry in the aChange[] array is set to -1. If the column is modified, 840 +** the value is 0 or greater. Parameter chngRowid is set to true if the 841 +** UPDATE statement modifies the rowid fields of the table. 857 842 ** 858 843 ** If any foreign key processing will be required, this function returns 859 844 ** true. If there is no foreign key related processing, this function 860 845 ** returns false. 861 846 */ 862 847 int sqlite3FkRequired( 863 848 Parse *pParse, /* Parse context */ 864 849 Table *pTab, /* Table being modified */ 865 - ExprList *pChanges /* Non-NULL for UPDATE operations */ 850 + int *aChange, /* Non-NULL for UPDATE operations */ 851 + int chngRowid /* True for UPDATE that affects rowid */ 866 852 ){ 867 853 if( pParse->db->flags&SQLITE_ForeignKeys ){ 868 - if( sqlite3FkReferences(pTab) || pTab->pFKey ) return 1; 854 + if( !aChange ){ 855 + /* A DELETE operation. Foreign key processing is required if the 856 + ** table in question is either the child or parent table for any 857 + ** foreign key constraint. */ 858 + return (sqlite3FkReferences(pTab) || pTab->pFKey); 859 + }else{ 860 + /* This is an UPDATE. Foreign key processing is only required if the 861 + ** operation modifies one or more child or parent key columns. */ 862 + int i; 863 + FKey *p; 864 + 865 + /* Check if any child key columns are being modified. */ 866 + for(p=pTab->pFKey; p; p=p->pNextFrom){ 867 + for(i=0; i<p->nCol; i++){ 868 + int iChildKey = p->aCol[i].iFrom; 869 + if( aChange[iChildKey]>=0 ) return 1; 870 + if( iChildKey==pTab->iPKey && chngRowid ) return 1; 871 + } 872 + } 873 + 874 + /* Check if any parent key columns are being modified. */ 875 + for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ 876 + for(i=0; i<p->nCol; i++){ 877 + char *zKey = p->aCol[i].zCol; 878 + int iKey; 879 + for(iKey=0; iKey<pTab->nCol; iKey++){ 880 + Column *pCol = &pTab->aCol[iKey]; 881 + if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) : pCol->isPrimKey) ){ 882 + if( aChange[iKey]>=0 ) return 1; 883 + if( iKey==pTab->iPKey && chngRowid ) return 1; 884 + } 885 + } 886 + } 887 + } 888 + } 869 889 } 870 890 return 0; 871 891 } 872 892 873 893 /* 874 894 ** This function is called when an UPDATE or DELETE operation is being 875 895 ** compiled on table pTab, which is the parent table of foreign-key pFKey.
Changes to src/insert.c.
975 975 }else 976 976 #endif 977 977 { 978 978 int isReplace; /* Set to true if constraints may cause a replace */ 979 979 sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, 980 980 keyColumn>=0, 0, onError, endOfLoop, &isReplace 981 981 ); 982 - sqlite3FkCheck(pParse, pTab, 0, 0, regIns); 982 + sqlite3FkCheck(pParse, pTab, 0, regIns); 983 983 sqlite3CompleteInsertion( 984 984 pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 985 985 ); 986 986 } 987 987 } 988 988 989 989 /* Update the count of rows that are inserted ................................................................................ 1267 1267 ** the index b-tree entries only. The table b-tree entry will be 1268 1268 ** replaced by the new entry when it is inserted. */ 1269 1269 Trigger *pTrigger = 0; 1270 1270 if( pParse->db->flags&SQLITE_RecTriggers ){ 1271 1271 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); 1272 1272 } 1273 1273 sqlite3MultiWrite(pParse); 1274 - if( pTrigger || sqlite3FkRequired(pParse, pTab, 0) ){ 1274 + if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){ 1275 1275 sqlite3GenerateRowDelete( 1276 1276 pParse, pTab, baseCur, regRowid, 0, pTrigger, OE_Replace 1277 1277 ); 1278 1278 }else{ 1279 1279 sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0); 1280 1280 } 1281 1281 seenReplace = 1;
Changes to src/sqliteInt.h.
2945 2945 ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign 2946 2946 ** key functionality is available. If OMIT_TRIGGER is defined but 2947 2947 ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In 2948 2948 ** this case foreign keys are parsed, but no other functionality is 2949 2949 ** provided (enforcement of FK constraints requires the triggers sub-system). 2950 2950 */ 2951 2951 #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) 2952 - void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int); 2952 + void sqlite3FkCheck(Parse*, Table*, int, int); 2953 2953 void sqlite3FkDropTable(Parse*, SrcList *, Table*); 2954 2954 void sqlite3FkActions(Parse*, Table*, ExprList*, int); 2955 - int sqlite3FkRequired(Parse*, Table*, ExprList*); 2956 - u32 sqlite3FkOldmask(Parse*, Table*, ExprList*); 2955 + int sqlite3FkRequired(Parse*, Table*, int*, int); 2956 + u32 sqlite3FkOldmask(Parse*, Table*); 2957 2957 FKey *sqlite3FkReferences(Table *); 2958 2958 #else 2959 2959 #define sqlite3FkActions(a,b,c,d) 2960 - #define sqlite3FkCheck(a,b,c,d,e) 2960 + #define sqlite3FkCheck(a,b,c,d) 2961 2961 #define sqlite3FkDropTable(a,b,c) 2962 - #define sqlite3FkOldmask(a,b,c) 0 2963 - #define sqlite3FkRequired(a,b,c) 0 2962 + #define sqlite3FkOldmask(a,b) 0 2963 + #define sqlite3FkRequired(a,b,c,d) 0 2964 2964 #endif 2965 2965 #ifndef SQLITE_OMIT_FOREIGN_KEY 2966 2966 void sqlite3FkDelete(Table*); 2967 2967 #else 2968 2968 #define sqlite3FkDelete(a) 2969 2969 #endif 2970 2970
Changes to src/update.c.
155 155 # define isView 0 156 156 #endif 157 157 #ifdef SQLITE_OMIT_VIEW 158 158 # undef isView 159 159 # define isView 0 160 160 #endif 161 161 162 - hasFK = sqlite3FkRequired(pParse, pTab, pChanges); 163 - 164 162 if( sqlite3ViewGetColumnNames(pParse, pTab) ){ 165 163 goto update_cleanup; 166 164 } 167 165 if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){ 168 166 goto update_cleanup; 169 167 } 170 168 aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol ); ................................................................................ 225 223 goto update_cleanup; 226 224 }else if( rc==SQLITE_IGNORE ){ 227 225 aXRef[j] = -1; 228 226 } 229 227 } 230 228 #endif 231 229 } 230 + 231 + hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngRowid); 232 232 233 233 /* Allocate memory for the array aRegIdx[]. There is one entry in the 234 234 ** array for each index associated with table being updated. Fill in 235 235 ** the value with a register number for indices that are to be used 236 236 ** and with zero for unused indices. 237 237 */ 238 238 for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} ................................................................................ 385 385 sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); 386 386 sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); 387 387 } 388 388 389 389 /* If there are triggers on this table, populate an array of registers 390 390 ** with the required old.* column data. */ 391 391 if( hasFK || pTrigger ){ 392 - u32 oldmask = sqlite3FkOldmask(pParse, pTab, pChanges); 392 + u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0); 393 393 oldmask |= sqlite3TriggerOldmask(pParse, pTrigger, pChanges, pTab, onError); 394 394 for(i=0; i<pTab->nCol; i++){ 395 395 if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){ 396 396 sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i); 397 397 sqlite3ColumnDefault(v, pTab, i, regOld+i); 398 398 }else{ 399 399 sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); ................................................................................ 441 441 if( !isView ){ 442 442 443 443 /* Do constraint checks. */ 444 444 sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, 445 445 aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); 446 446 447 447 /* Do FK constraint checks. */ 448 - sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, 0); 448 + if( hasFK ){ 449 + sqlite3FkCheck(pParse, pTab, regOldRowid, 0); 450 + } 449 451 450 452 /* Delete the index entries associated with the current record. */ 451 453 j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); 452 454 sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); 453 455 454 456 /* If changing the record number, delete the old record. */ 455 457 if( hasFK || chngRowid ){ 456 458 sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); 457 459 } 458 460 sqlite3VdbeJumpHere(v, j1); 459 461 460 - sqlite3FkCheck(pParse, pTab, pChanges, 0, regNewRowid); 462 + if( hasFK ){ 463 + sqlite3FkCheck(pParse, pTab, 0, regNewRowid); 464 + } 461 465 462 466 /* Insert the new index entries and the new record. */ 463 467 sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); 464 468 465 469 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 466 470 ** handle rows (possibly in other tables) that refer via a foreign key 467 471 ** to the row just updated. */ 468 - sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); 472 + if( hasFK ){ 473 + sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); 474 + } 469 475 } 470 476 471 477 /* Increment the row counter 472 478 */ 473 479 if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ 474 480 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); 475 481 }
Changes to test/fkey2.test.
66 66 # to check if this has repaired an outstanding violation. 67 67 # 68 68 # fkey2-16.*: Test that rows that refer to themselves may be inserted, 69 69 # updated and deleted. 70 70 # 71 71 # fkey2-17.*: Test that the "count_changes" pragma does not interfere with 72 72 # FK constraint processing. 73 +# 74 +# fkey2-18.*: Test that the authorization callback is invoked when processing 75 +# FK constraints. 73 76 # 74 77 # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey 75 78 # command. Recycled to test the built-in implementation. 76 79 # 77 80 78 81 79 82 proc drop_all_tables {{db db}} { ................................................................................ 132 135 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 133 136 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 134 137 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}} 135 138 4.11 "DELETE FROM t7 WHERE b=1" {1 {foreign key constraint failed}} 136 139 4.12 "UPDATE t7 SET b = 2" {1 {foreign key constraint failed}} 137 140 4.13 "UPDATE t7 SET b = 1" {0 {}} 138 141 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}} 142 + 4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}} 143 + 4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}} 139 144 140 145 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}} 141 146 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}} 142 147 } 143 148 144 149 do_test fkey2-1.1.0 { 145 150 execsql [string map {/D/ {}} $FkeySimpleSchema] ................................................................................ 1338 1343 } {1} 1339 1344 do_test fkey2-17.2.9 { 1340 1345 expr [db total_changes] - $nTotal 1341 1346 } {4} 1342 1347 do_test fkey2-17.2.10 { 1343 1348 execsql { SELECT * FROM high ; SELECT * FROM low } 1344 1349 } {} 1350 + 1351 +#------------------------------------------------------------------------- 1352 +# Test that the authorization callback works. 1353 +# 1345 1354 1346 1355 1347 1356 execsql { PRAGMA count_changes = 0 } 1348 1357 #------------------------------------------------------------------------- 1349 1358 # The following block of tests, those prefixed with "fkey2-genfkey.", are 1350 1359 # the same tests that were used to test the ".genfkey" command provided 1351 1360 # by the shell tool. So these tests show that the built-in foreign key
Changes to test/fkey3.test.
1 -# 2001 September 15 1 +# 2009 September 15 2 2 # 3 3 # The author disclaims copyright to this source code. In place of 4 4 # a legal notice, here is a blessing: 5 5 # 6 6 # May you do good and not evil. 7 7 # May you find forgiveness for yourself and forgive others. 8 8 # May you share freely, never taking more than you give.