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