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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
edff3500058eb8ad2381f855ef7a09ec |
User & Date: | dan 2009-10-01 16:09:04.000 |
Context
2009-10-01
| ||
17:13 | Add test cases to cover the branches added by the previous commit. (check-in: aaa005b6da 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: edff350005 user: dan tags: trunk) | |
04:35 | Add tests to check that FK support interacts with count-changes correctly. (check-in: 5b8366154b user: dan tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | |||
338 339 340 341 342 343 344 | 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 | - + | #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by ** API function sqlite3_count_changes) to be set incorrectly. */ if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) |
︙ | |||
488 489 490 491 492 493 494 | 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 | - + - + | ** (this can happen if a trigger program has already deleted it), do ** not attempt to delete it or fire any DELETE triggers. */ iLabel = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); /* If there are any triggers to fire, allocate a range of registers to ** use for the old.* references in the triggers. */ |
︙ | |||
524 525 526 527 528 529 530 | 524 525 526 527 528 529 530 531 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. */ |
︙ |
Changes to src/fkey.c.
︙ | |||
668 669 670 671 672 673 674 | 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 | - + - - - | ** 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. ** ** For an UPDATE operation, this function is called twice. Once before ** the original record is deleted from the table using the calling convention ** described for DELETE. Then again after the original record is deleted |
︙ | |||
721 722 723 724 725 726 727 | 718 719 720 721 722 723 724 725 726 727 728 729 730 731 | - - - - - | } if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){ if( !isIgnoreErrors || db->mallocFailed ) return; continue; } assert( pFKey->nCol==1 || (aiFree && pIdx) ); |
︙ | |||
778 779 780 781 782 783 784 | 770 771 772 773 774 775 776 777 778 779 780 781 782 783 | - - - - - - - - | if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ if( !isIgnoreErrors || db->mallocFailed ) return; continue; } assert( aiCol || pFKey->nCol==1 ); |
︙ | |||
818 819 820 821 822 823 824 | 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 | - + - - - + - | } } #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) /* ** This function is called before generating code to update or delete a |
︙ | |||
847 848 849 850 851 852 853 | 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 | - - - + + + + + + + - + + + + + + - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | } } return mask; } /* ** This function is called before generating code to update or delete a |
︙ |
Changes to src/insert.c.
︙ | |||
975 976 977 978 979 980 981 | 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 | - + | }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 ); |
︙ | |||
1267 1268 1269 1270 1271 1272 1273 | 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 | - + | ** the index b-tree entries only. The table b-tree entry will be ** replaced by the new entry when it is inserted. */ Trigger *pTrigger = 0; if( pParse->db->flags&SQLITE_RecTriggers ){ pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); } sqlite3MultiWrite(pParse); |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
2945 2946 2947 2948 2949 2950 2951 | 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 | - + - - + + - + - - + + | ** 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.
︙ | |||
155 156 157 158 159 160 161 | 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | - - | # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif |
︙ | |||
225 226 227 228 229 230 231 232 233 234 235 236 237 238 | 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | + + | goto update_cleanup; }else if( rc==SQLITE_IGNORE ){ aXRef[j] = -1; } } #endif } hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngRowid); /* Allocate memory for the array aRegIdx[]. There is one entry in the ** array for each index associated with table being updated. Fill in ** the value with a register number for indices that are to be used ** and with zero for unused indices. */ for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} |
︙ | |||
385 386 387 388 389 390 391 | 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 | - + | sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } /* If there are triggers on this table, populate an array of registers ** with the required old.* column data. */ if( hasFK || pTrigger ){ |
︙ | |||
441 442 443 444 445 446 447 | 441 442 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 | + - + + + - + + + - + + | if( !isView ){ /* Do constraint checks. */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Do FK constraint checks. */ if( hasFK ){ |
︙ |
Changes to test/fkey2.test.
︙ | |||
66 67 68 69 70 71 72 73 74 75 76 77 78 79 | 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | + + + | # to check if this has repaired an outstanding violation. # # fkey2-16.*: Test that rows that refer to themselves may be inserted, # updated and deleted. # # fkey2-17.*: Test that the "count_changes" pragma does not interfere with # FK constraint processing. # # fkey2-18.*: Test that the authorization callback is invoked when processing # FK constraints. # # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey # command. Recycled to test the built-in implementation. # proc drop_all_tables {{db db}} { |
︙ | |||
132 133 134 135 136 137 138 139 140 141 142 143 144 145 | 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | + + | 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}} 4.11 "DELETE FROM t7 WHERE b=1" {1 {foreign key constraint failed}} 4.12 "UPDATE t7 SET b = 2" {1 {foreign key constraint failed}} 4.13 "UPDATE t7 SET b = 1" {0 {}} 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}} 4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}} 4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}} 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}} 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}} } do_test fkey2-1.1.0 { execsql [string map {/D/ {}} $FkeySimpleSchema] |
︙ | |||
1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 | 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 | + + + + | } {1} do_test fkey2-17.2.9 { expr [db total_changes] - $nTotal } {4} do_test fkey2-17.2.10 { execsql { SELECT * FROM high ; SELECT * FROM low } } {} #------------------------------------------------------------------------- # Test that the authorization callback works. # execsql { PRAGMA count_changes = 0 } #------------------------------------------------------------------------- # The following block of tests, those prefixed with "fkey2-genfkey.", are # the same tests that were used to test the ".genfkey" command provided # by the shell tool. So these tests show that the built-in foreign key |
︙ |
Changes to test/fkey3.test.
| 1 2 3 4 5 6 7 8 | - + |
|
︙ |