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 | 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. */ |
︙ |
Changes to src/fkey.c.
︙ | |||
678 679 680 681 682 683 684 685 686 687 688 689 690 691 | 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 | 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 */ |
︙ | |||
729 730 731 732 733 734 735 736 737 738 739 740 741 742 | 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 | 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 | 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 | 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. */ |
︙ | |||
1165 1166 1167 1168 1169 1170 1171 | 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 */ |
︙ |
Changes to src/insert.c.
︙ | |||
1027 1028 1029 1030 1031 1032 1033 | 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 ); |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
3205 3206 3207 3208 3209 3210 3211 | 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) |
︙ |
Changes to src/update.c.
︙ | |||
484 485 486 487 488 489 490 | 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 ){ |
︙ |
Added test/fkey7.test.
|
Changes to test/permutations.test.
︙ | |||
505 506 507 508 509 510 511 | 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 |
︙ |