Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix some comments in fkey.c. Add tests to fkey8.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | experimental-fk-actions |
Files: | files | file ages | folders |
SHA1: |
210cb2a6aaf780365064a26c0c99926b |
User & Date: | dan 2014-12-17 14:38:45.643 |
Context
2014-12-17
| ||
15:03 | Experimental opimizations to speed up FK constraint CASCADE and SET NULL action processing. (check-in: 8c5dd6cc25 user: dan tags: trunk) | |
14:38 | Fix some comments in fkey.c. Add tests to fkey8.test. (Closed-Leaf check-in: 210cb2a6aa user: dan tags: experimental-fk-actions) | |
2014-12-16
| ||
20:13 | Experimental opimizations to speed up FK constraint CASCADE and SET NULL action processing. Requires further testing. (check-in: 35a20a5f22 user: dan tags: experimental-fk-actions) | |
Changes
Changes to src/fkey.c.
︙ | ︙ | |||
625 626 627 628 629 630 631 | /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE | < | | | 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 | /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. For each row found, increment either the deferred or immediate ** foreign key constraint counter. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0); sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); if( pWInfo ){ sqlite3WhereEnd(pWInfo); } /* Clean up the WHERE clause constructed above. */ |
︙ | ︙ | |||
959 960 961 962 963 964 965 | } if( regNew!=0 && !isSetNullAction(pParse, pFKey) ){ /* A row is being added to the child table. If a parent row cannot ** be found, adding the child row has violated the FK constraint. ** ** If this operation is being performed as part of a trigger program ** that is actually a "SET NULL" action belonging to this very | | | | 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 | } if( regNew!=0 && !isSetNullAction(pParse, pFKey) ){ /* A row is being added to the child table. If a parent row cannot ** be found, adding the child row has violated the FK constraint. ** ** If this operation is being performed as part of a trigger program ** that is actually a "SET NULL" action belonging to this very ** foreign key, then omit this scan altogether. As all child key ** values are guaranteed to be NULL, it is not possible for adding ** this row to cause an FK violation. */ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1, bIgnore); } sqlite3DbFree(db, aiFree); } /* Loop through all the foreign key constraints that refer to this table. |
︙ | ︙ | |||
1011 1012 1013 1014 1015 1016 1017 | if( regNew!=0 ){ fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1); } if( regOld!=0 ){ int eAction = pFKey->aAction[aChange!=0]; fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1); /* If this is a deferred FK constraint, or a CASCADE or SET NULL | | > > > > | > > > > > > > > > | | 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 | if( regNew!=0 ){ fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1); } if( regOld!=0 ){ int eAction = pFKey->aAction[aChange!=0]; fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1); /* If this is a deferred FK constraint, or a CASCADE or SET NULL ** action applies, then any foreign key violations caused by ** removing the parent key will be rectified by the action trigger. ** So do not set the "may-abort" flag in this case. ** ** Note 1: If the FK is declared "ON UPDATE CASCADE", then the ** may-abort flag will eventually be set on this statement anyway ** (when this function is called as part of processing the UPDATE ** within the action trigger). ** ** Note 2: At first glance it may seem like SQLite could simply omit ** all OP_FkCounter related scans when either CASCADE or SET NULL ** applies. The trouble starts if the CASCADE or SET NULL action ** trigger causes other triggers or action rules attached to the ** child table to fire. In these cases the fk constraint counters ** might be set incorrectly if any OP_FkCounter related scans are ** omitted. */ if( !pFKey->isDeferred && eAction!=OE_Cascade && eAction!=OE_SetNull ){ sqlite3MayAbort(pParse); } } pItem->zName = 0; sqlite3SrcListDelete(db, pSrc); } |
︙ | ︙ |
Changes to test/fkey8.test.
︙ | ︙ | |||
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE); } 5.2 0 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL); } } { drop_all_tables do_test 1.$tn { execsql $schema set stmt [sqlite3_prepare_v2 db $sql -1 dummy] set ret [uses_stmt_journal $stmt] sqlite3_finalize $stmt set ret } $use_stmt } | > > > > > > > > > > > > > > > > > > > > > > | < | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE); } 5.2 0 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL); } 5.3 1 "DELETE FROM p1" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT); } 6.1 1 "UPDATE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); } 6.2 0 "UPDATE OR IGNORE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); } 6.3 1 "UPDATE OR IGNORE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c); } 6.4 1 "UPDATE OR IGNORE p1 SET a = ?" { CREATE TABLE p1(a PRIMARY KEY); CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c); } } { drop_all_tables do_test 1.$tn { execsql $schema set stmt [sqlite3_prepare_v2 db $sql -1 dummy] set ret [uses_stmt_journal $stmt] sqlite3_finalize $stmt set ret } $use_stmt } finish_test |