Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | When preparing an UPDATE statement, avoid generating VDBE code for those foreign key related actions and constraint checks that may be seen to be unnecessary by considering the subset of table columns potentially modified by the UPDATE. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
e940b5de49baa1d6a4cf859fbbc0e0df |
| User & Date: | dan 2013-09-05 18:40:29 |
|
2013-09-06
| ||
| 00:40 | Make sure the destination WhereLoop is left in a sane state when an OOM fault occurs inside of whereLoopXfer(). (check-in: a99a53b8 user: drh tags: trunk) | |
|
2013-09-05
| ||
| 18:40 | When preparing an UPDATE statement, avoid generating VDBE code for those foreign key related actions and constraint checks that may be seen to be unnecessary by considering the subset of table columns potentially modified by the UPDATE. (check-in: e940b5de user: dan tags: trunk) | |
|
2013-09-04
| ||
| 18:14 | Rearrange the order of conditions in an "if" statement to facilitate testing. (check-in: 8462fb43 user: drh tags: trunk) | |
Changes to src/delete.c.
532 532 ** being deleted. Do not attempt to delete the row a second time, and 533 533 ** do not fire AFTER triggers. */ 534 534 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); 535 535 536 536 /* Do FK processing. This call checks that any FK constraints that 537 537 ** refer to this table (i.e. constraints attached to other tables) 538 538 ** are not violated by deleting this row. */ 539 - sqlite3FkCheck(pParse, pTab, iOld, 0); 539 + sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0); 540 540 } 541 541 542 542 /* Delete the index and table entries. Skip this step if pTab is really 543 543 ** a view (in which case the only effect of the DELETE statement is to 544 544 ** fire the INSTEAD OF triggers). */ 545 545 if( pTab->pSelect==0 ){ 546 546 sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0); ................................................................................ 549 549 sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT); 550 550 } 551 551 } 552 552 553 553 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 554 554 ** handle rows (possibly in other tables) that refer via a foreign key 555 555 ** to the row just deleted. */ 556 - sqlite3FkActions(pParse, pTab, 0, iOld); 556 + sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0); 557 557 558 558 /* Invoke AFTER DELETE trigger programs. */ 559 559 sqlite3CodeRowTrigger(pParse, pTrigger, 560 560 TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel 561 561 ); 562 562 563 563 /* Jump here if the row had already been deleted before any BEFORE
Changes to src/fkey.c.
678 678 679 679 if( iSkip ){ 680 680 sqlite3VdbeResolveLabel(v, iSkip); 681 681 } 682 682 } 683 683 } 684 684 685 + 686 +/* 687 +** The second argument points to an FKey object representing a foreign key 688 +** for which pTab is the child table. An UPDATE statement against pTab 689 +** is currently being processed. For each column of the table that is 690 +** actually updated, the corresponding element in the aChange[] array 691 +** is zero or greater (if a column is unmodified the corresponding element 692 +** is set to -1). If the rowid column is modified by the UPDATE statement 693 +** the bChngRowid argument is non-zero. 694 +** 695 +** This function returns true if any of the columns that are part of the 696 +** child key for FK constraint *p are modified. 697 +*/ 698 +static int fkChildIsModified( 699 + Table *pTab, /* Table being updated */ 700 + FKey *p, /* Foreign key for which pTab is the child */ 701 + int *aChange, /* Array indicating modified columns */ 702 + int bChngRowid /* True if rowid is modified by this update */ 703 +){ 704 + int i; 705 + for(i=0; i<p->nCol; i++){ 706 + int iChildKey = p->aCol[i].iFrom; 707 + if( aChange[iChildKey]>=0 ) return 1; 708 + if( iChildKey==pTab->iPKey && bChngRowid ) return 1; 709 + } 710 + return 0; 711 +} 712 + 713 +/* 714 +** The second argument points to an FKey object representing a foreign key 715 +** for which pTab is the parent table. An UPDATE statement against pTab 716 +** is currently being processed. For each column of the table that is 717 +** actually updated, the corresponding element in the aChange[] array 718 +** is zero or greater (if a column is unmodified the corresponding element 719 +** is set to -1). If the rowid column is modified by the UPDATE statement 720 +** the bChngRowid argument is non-zero. 721 +** 722 +** This function returns true if any of the columns that are part of the 723 +** parent key for FK constraint *p are modified. 724 +*/ 725 +static int fkParentIsModified( 726 + Table *pTab, 727 + FKey *p, 728 + int *aChange, 729 + int bChngRowid 730 +){ 731 + int i; 732 + for(i=0; i<p->nCol; i++){ 733 + char *zKey = p->aCol[i].zCol; 734 + int iKey; 735 + for(iKey=0; iKey<pTab->nCol; iKey++){ 736 + if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){ 737 + Column *pCol = &pTab->aCol[iKey]; 738 + if( zKey ){ 739 + if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1; 740 + }else if( pCol->colFlags & COLFLAG_PRIMKEY ){ 741 + return 1; 742 + } 743 + } 744 + } 745 + } 746 + return 0; 747 +} 748 + 685 749 /* 686 750 ** This function is called when inserting, deleting or updating a row of 687 751 ** table pTab to generate VDBE code to perform foreign key constraint 688 752 ** processing for the operation. 689 753 ** 690 754 ** For a DELETE operation, parameter regOld is passed the index of the 691 755 ** first register in an array of (pTab->nCol+1) registers containing the ................................................................................ 702 766 ** described for DELETE. Then again after the original record is deleted 703 767 ** but before the new record is inserted using the INSERT convention. 704 768 */ 705 769 void sqlite3FkCheck( 706 770 Parse *pParse, /* Parse context */ 707 771 Table *pTab, /* Row is being deleted from this table */ 708 772 int regOld, /* Previous row data is stored here */ 709 - int regNew /* New row data is stored here */ 773 + int regNew, /* New row data is stored here */ 774 + int *aChange, /* Array indicating UPDATEd columns (or 0) */ 775 + int bChngRowid /* True if rowid is UPDATEd */ 710 776 ){ 711 777 sqlite3 *db = pParse->db; /* Database handle */ 712 778 FKey *pFKey; /* Used to iterate through FKs */ 713 779 int iDb; /* Index of database containing pTab */ 714 780 const char *zDb; /* Name of database containing pTab */ 715 781 int isIgnoreErrors = pParse->disableTriggers; 716 782 ................................................................................ 729 795 Table *pTo; /* Parent table of foreign key pFKey */ 730 796 Index *pIdx = 0; /* Index on key columns in pTo */ 731 797 int *aiFree = 0; 732 798 int *aiCol; 733 799 int iCol; 734 800 int i; 735 801 int isIgnore = 0; 802 + 803 + if( aChange 804 + && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0 805 + && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 806 + ){ 807 + continue; 808 + } 736 809 737 810 /* Find the parent table of this foreign key. Also find a unique index 738 811 ** on the parent key columns in the parent table. If either of these 739 812 ** schema items cannot be located, set an error in pParse and return 740 813 ** early. */ 741 814 if( pParse->disableTriggers ){ 742 815 pTo = sqlite3FindTable(db, pFKey->zTo, zDb); ................................................................................ 811 884 } 812 885 813 886 /* Loop through all the foreign key constraints that refer to this table */ 814 887 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ 815 888 Index *pIdx = 0; /* Foreign key index for pFKey */ 816 889 SrcList *pSrc; 817 890 int *aiCol = 0; 891 + 892 + if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ 893 + continue; 894 + } 818 895 819 896 if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 820 897 && !pParse->pToplevel && !pParse->isMultiWrite 821 898 ){ 822 899 assert( regOld==0 && regNew!=0 ); 823 900 /* Inserting a single row into a parent table cannot cause an immediate 824 901 ** foreign key violation. So do nothing in this case. */ ................................................................................ 884 961 if( pIdx ){ 885 962 for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]); 886 963 } 887 964 } 888 965 } 889 966 return mask; 890 967 } 968 + 891 969 892 970 /* 893 971 ** This function is called before generating code to update or delete a 894 972 ** row contained in table pTab. If the operation is a DELETE, then 895 973 ** parameter aChange is passed a NULL value. For an UPDATE, aChange points 896 974 ** to an array of size N, where N is the number of columns in table pTab. 897 975 ** If the i'th column is not modified by the UPDATE, then the corresponding ................................................................................ 914 992 /* A DELETE operation. Foreign key processing is required if the 915 993 ** table in question is either the child or parent table for any 916 994 ** foreign key constraint. */ 917 995 return (sqlite3FkReferences(pTab) || pTab->pFKey); 918 996 }else{ 919 997 /* This is an UPDATE. Foreign key processing is only required if the 920 998 ** operation modifies one or more child or parent key columns. */ 921 - int i; 922 999 FKey *p; 923 1000 924 1001 /* Check if any child key columns are being modified. */ 925 1002 for(p=pTab->pFKey; p; p=p->pNextFrom){ 926 - for(i=0; i<p->nCol; i++){ 927 - int iChildKey = p->aCol[i].iFrom; 928 - if( aChange[iChildKey]>=0 ) return 1; 929 - if( iChildKey==pTab->iPKey && chngRowid ) return 1; 930 - } 1003 + if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1; 931 1004 } 932 1005 933 1006 /* Check if any parent key columns are being modified. */ 934 1007 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ 935 - for(i=0; i<p->nCol; i++){ 936 - char *zKey = p->aCol[i].zCol; 937 - int iKey; 938 - for(iKey=0; iKey<pTab->nCol; iKey++){ 939 - Column *pCol = &pTab->aCol[iKey]; 940 - if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) 941 - : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){ 942 - if( aChange[iKey]>=0 ) return 1; 943 - if( iKey==pTab->iPKey && chngRowid ) return 1; 944 - } 945 - } 946 - } 1008 + if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1; 947 1009 } 948 1010 } 949 1011 } 950 1012 return 0; 951 1013 } 952 1014 953 1015 /* ................................................................................ 1165 1227 ** This function is called when deleting or updating a row to implement 1166 1228 ** any required CASCADE, SET NULL or SET DEFAULT actions. 1167 1229 */ 1168 1230 void sqlite3FkActions( 1169 1231 Parse *pParse, /* Parse context */ 1170 1232 Table *pTab, /* Table being updated or deleted from */ 1171 1233 ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */ 1172 - int regOld /* Address of array containing old row */ 1234 + int regOld, /* Address of array containing old row */ 1235 + int *aChange, /* Array indicating UPDATEd columns (or 0) */ 1236 + int bChngRowid /* True if rowid is UPDATEd */ 1173 1237 ){ 1174 1238 /* If foreign-key support is enabled, iterate through all FKs that 1175 1239 ** refer to table pTab. If there is an action associated with the FK 1176 1240 ** for this operation (either update or delete), invoke the associated 1177 1241 ** trigger sub-program. */ 1178 1242 if( pParse->db->flags&SQLITE_ForeignKeys ){ 1179 1243 FKey *pFKey; /* Iterator variable */ 1180 1244 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ 1181 - Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); 1182 - if( pAction ){ 1183 - sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); 1245 + if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){ 1246 + Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges); 1247 + if( pAct ){ 1248 + sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0); 1249 + } 1184 1250 } 1185 1251 } 1186 1252 } 1187 1253 } 1188 1254 1189 1255 #endif /* ifndef SQLITE_OMIT_TRIGGER */ 1190 1256
Changes to src/insert.c.
1027 1027 }else 1028 1028 #endif 1029 1029 { 1030 1030 int isReplace; /* Set to true if constraints may cause a replace */ 1031 1031 sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, 1032 1032 keyColumn>=0, 0, onError, endOfLoop, &isReplace 1033 1033 ); 1034 - sqlite3FkCheck(pParse, pTab, 0, regIns); 1034 + sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); 1035 1035 sqlite3CompleteInsertion( 1036 1036 pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 1037 1037 ); 1038 1038 } 1039 1039 } 1040 1040 1041 1041 /* Update the count of rows that are inserted
Changes to src/sqliteInt.h.
3205 3205 ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign 3206 3206 ** key functionality is available. If OMIT_TRIGGER is defined but 3207 3207 ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In 3208 3208 ** this case foreign keys are parsed, but no other functionality is 3209 3209 ** provided (enforcement of FK constraints requires the triggers sub-system). 3210 3210 */ 3211 3211 #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) 3212 - void sqlite3FkCheck(Parse*, Table*, int, int); 3212 + void sqlite3FkCheck(Parse*, Table*, int, int, int*, int); 3213 3213 void sqlite3FkDropTable(Parse*, SrcList *, Table*); 3214 - void sqlite3FkActions(Parse*, Table*, ExprList*, int); 3214 + void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int); 3215 3215 int sqlite3FkRequired(Parse*, Table*, int*, int); 3216 3216 u32 sqlite3FkOldmask(Parse*, Table*); 3217 3217 FKey *sqlite3FkReferences(Table *); 3218 3218 #else 3219 - #define sqlite3FkActions(a,b,c,d) 3219 + #define sqlite3FkActions(a,b,c,d,e,f) 3220 3220 #define sqlite3FkCheck(a,b,c,d) 3221 3221 #define sqlite3FkDropTable(a,b,c) 3222 3222 #define sqlite3FkOldmask(a,b) 0 3223 - #define sqlite3FkRequired(a,b,c,d) 0 3223 + #define sqlite3FkRequired(a,b,c,d,e,f) 0 3224 3224 #endif 3225 3225 #ifndef SQLITE_OMIT_FOREIGN_KEY 3226 3226 void sqlite3FkDelete(sqlite3 *, Table*); 3227 3227 int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**); 3228 3228 #else 3229 3229 #define sqlite3FkDelete(a,b) 3230 3230 #define sqlite3FkLocateIndex(a,b,c,d,e)
Changes to src/update.c.
484 484 485 485 /* Do constraint checks. */ 486 486 sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, 487 487 aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); 488 488 489 489 /* Do FK constraint checks. */ 490 490 if( hasFK ){ 491 - sqlite3FkCheck(pParse, pTab, regOldRowid, 0); 491 + sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid); 492 492 } 493 493 494 494 /* Delete the index entries associated with the current record. */ 495 495 j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); 496 496 sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); 497 497 498 498 /* If changing the record number, delete the old record. */ 499 499 if( hasFK || chngRowid ){ 500 500 sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); 501 501 } 502 502 sqlite3VdbeJumpHere(v, j1); 503 503 504 504 if( hasFK ){ 505 - sqlite3FkCheck(pParse, pTab, 0, regNewRowid); 505 + sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid); 506 506 } 507 507 508 508 /* Insert the new index entries and the new record. */ 509 509 sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); 510 510 511 511 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 512 512 ** handle rows (possibly in other tables) that refer via a foreign key 513 513 ** to the row just updated. */ 514 514 if( hasFK ){ 515 - sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); 515 + sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid); 516 516 } 517 517 } 518 518 519 519 /* Increment the row counter 520 520 */ 521 521 if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ 522 522 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
Added test/fkey7.test.
1 +# 2001 September 15 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. 12 +# 13 +# This file implements tests for foreign keys. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 +set testprefix fkey7 19 + 20 +ifcapable {!foreignkey} { 21 + finish_test 22 + return 23 +} 24 + 25 +do_execsql_test 1.1 { 26 + PRAGMA foreign_keys = 1; 27 + 28 + CREATE TABLE s1(a PRIMARY KEY, b); 29 + CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a)); 30 + 31 + CREATE TABLE c1(a, b REFERENCES par); 32 + CREATE TABLE c2(a, b REFERENCES par); 33 + CREATE TABLE c3(a, b REFERENCES par(c)); 34 +} 35 + 36 +proc auth {op tbl args} { 37 + if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 } 38 + return "SQLITE_OK" 39 +} 40 +db auth auth 41 +db cache size 0 42 +proc do_tblsread_test {tn sql tbllist} { 43 + array unset ::tbls 44 + uplevel [list execsql $sql] 45 + uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist] 46 +} 47 + 48 +do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1} 49 +do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par} 50 +do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par} 51 +do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1} 52 + 53 + 54 +finish_test
Changes to test/permutations.test.
505 505 test_suite "utf16" -description { 506 506 Run tests using UTF-16 databases 507 507 } -presql { 508 508 pragma encoding = 'UTF-16' 509 509 } -files { 510 510 alter.test alter3.test 511 511 analyze.test analyze3.test analyze4.test analyze5.test analyze6.test 512 - analyze7.test analyze8.test analyze9.test analyzeA.test 512 + analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test 513 513 auth.test bind.test blob.test capi2.test capi3.test collate1.test 514 514 collate2.test collate3.test collate4.test collate5.test collate6.test 515 515 conflict.test date.test delete.test expr.test fkey1.test func.test 516 516 hook.test index.test insert2.test insert.test interrupt.test in.test 517 517 intpkey.test ioerr.test join2.test join.test lastinsert.test 518 518 laststmtchanges.test limit.test lock2.test lock.test main.test 519 519 memdb.test minmax.test misc1.test misc2.test misc3.test notnull.test