db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-532) Support deferrable constraints
Date Fri, 25 Apr 2014 17:55:25 GMT

    [ https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13981319#comment-13981319
] 

Dag H. Wanvik edited comment on DERBY-532 at 4/25/14 5:55 PM:
--------------------------------------------------------------

Patch derby-532-fk-6. Smaller changes relative to #5.
I intend to commit this version if no comments are made.

{panel:title=Patch details|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE}
M       java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java
M       java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java

Move logic related to constraints checking to
DeferredConstraintsMemory. 

M       java/engine/org/apache/derby/impl/sql/execute/DeferredConstraintsMemory.java

New logic from LanguageConnectionContext; added logic for the case of
foreign key constraints violation memory and checking. Make old "if"s
on object type object oriented: new interface methods in
ValidationInfo: possiblyValidateOnReturn and validateConstraint which
are implemented differently for each constraint type.

M       java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
M       java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
M       java/engine/org/apache/derby/impl/sql/execute/ConstraintConstantAction.java

More fk info to bulk insert; smaller refactorings.

M       java/engine/org/apache/derby/iapi/sql/dictionary/ReferencedKeyConstraintDescriptor.java

Change the method hasNonSelfReferencingFK to getNonSelfReferencingFK;
we need to get at them, see AlterTableConstantAction for truncate.

M       java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java

Refactored logic to DeferredConstraintsMemory#compressOrTruncate.
Allow truncate (if deferred and NO_ACTION constraints only) of
deferenced table.

M       java/engine/org/apache/derby/iapi/types/SQLBoolean.java
M       java/engine/org/apache/derby/impl/sql/execute/IndexChanger.java
M       java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java

Minor refactorings.

M       java/engine/org/apache/derby/impl/sql/execute/FKInfo.java

Extended with information about deferred constraints; conglomerate ids and constrain ids.

M       java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java

More information collection to support extended FKInfo, see above.

M       java/engine/org/apache/derby/impl/sql/compile/TableElementList.java

Extra predicates to avoid logic for unique and primary key constraints to inferere with
deferred foreign keys.

M       java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java

Remove foreign keys as "not supported", minor refactorings.

M       java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java

Added arguments, refactorings.

M       java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java

This is where we actually defer when we see a fk violation at insert/update of referencing
table.

M       java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java

Minor interface changes: new member variable: lcc

M       java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java

This is where we actually defer when we see a fk violation at bulk insert
into referencing table.


M       java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java

Minor interface changes; added parameters.

M       java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java

This is where we actually defer when we see a fk violation at delete or update of
a row in the referenced table.

M       java/engine/org/apache/derby/impl/sql/execute/SetConstraintsConstantAction.java

Make SET CONSTRAINTS work also for foreign keys.

M       java/shared/org/apache/derby/shared/common/reference/SQLState.java
M       java/engine/org/apache/derby/loc/messages.xml

New error messages.

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java

Extended existing deferrable constraints to also work for foreign constraints.

A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java

New tests only for foreign key constraints.

M       java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java

Remove asserts for not implemented on deferrable foreign key
constraints, check basic sanity iff hard upgraded.

M       java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Added assertCommitError; minor changes to support fk testing.
{panel}




was (Author: dagw):
Patch derby-532-fk-6. Smaller changes relative to #5.
I intend to commit version this if no comments are made.

{panel:title=Patch details|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE}
M       java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java
M       java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java

Move logic related to constraints checking to
DeferredConstraintsMemory. 

M       java/engine/org/apache/derby/impl/sql/execute/DeferredConstraintsMemory.java

New logic from LanguageConnectionContext; added logic for the case of
foreign key constraints violation memory and checking. Make old "if"s
on object type object oriented: new interface methods in
ValidationInfo: possiblyValidateOnReturn and validateConstraint which
are implemented differently for each constraint type.

M       java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
M       java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
M       java/engine/org/apache/derby/impl/sql/execute/ConstraintConstantAction.java

More fk info to bulk insert; smaller refactorings.

M       java/engine/org/apache/derby/iapi/sql/dictionary/ReferencedKeyConstraintDescriptor.java

Change the method hasNonSelfReferencingFK to getNonSelfReferencingFK;
we need to get at them, see AlterTableConstantAction for truncate.

M       java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java

Refactored logic to DeferredConstraintsMemory#compressOrTruncate.
Allow truncate (if deferred and NO_ACTION constraints only) of
deferenced table.

M       java/engine/org/apache/derby/iapi/types/SQLBoolean.java
M       java/engine/org/apache/derby/impl/sql/execute/IndexChanger.java
M       java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java

Minor refactorings.

M       java/engine/org/apache/derby/impl/sql/execute/FKInfo.java

Extended with information about deferred constraints; conglomerate ids and constrain ids.

M       java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java

More information collection to support extended FKInfo, see above.

M       java/engine/org/apache/derby/impl/sql/compile/TableElementList.java

Extra predicates to avoid logic for unique and primary key constraints to inferere with
deferred foreign keys.

M       java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java

Remove foreign keys as "not supported", minor refactorings.

M       java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java

Added arguments, refactorings.

M       java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java

This is where we actually defer when we see a fk violation at insert/update of referencing
table.

M       java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java

Minor interface changes: new member variable: lcc

M       java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java

This is where we actually defer when we see a fk violation at bulk insert
into referencing table.


M       java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java

Minor interface changes; added parameters.

M       java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java

This is where we actually defer when we see a fk violation at delete or update of
a row in the referenced table.

M       java/engine/org/apache/derby/impl/sql/execute/SetConstraintsConstantAction.java

Make SET CONSTRAINTS work also for foreign keys.

M       java/shared/org/apache/derby/shared/common/reference/SQLState.java
M       java/engine/org/apache/derby/loc/messages.xml

New error messages.

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java

Extended existing deferrable constraints to also work for foreign constraints.

A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java

New tests only for foreign key constraints.

M       java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java

Remove asserts for not implemented on deferrable foreign key
constraints, check basic sanity iff hard upgraded.

M       java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Added assertCommitError; minor changes to support fk testing.
{panel}



> Support deferrable constraints
> ------------------------------
>
>                 Key: DERBY-532
>                 URL: https://issues.apache.org/jira/browse/DERBY-532
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Jörg von Frantzius
>            Assignee: Dag H. Wanvik
>              Labels: derby_triage10_11
>             Fix For: 10.11.0.0
>
>         Attachments: IndexDescriptor.html, IndexDescriptorImpl.html, IndexRowGenerator.html,
SortObserver.html, deferredConstraints.html, deferredConstraints.html, deferredConstraints.html,
deferredConstraints.html, deferredConstraints.html, derby-532-allow-pk-unique-1.diff, derby-532-allow-pk-unique-1.status,
derby-532-check-constraints-1.diff, derby-532-check-constraints-1.stat, derby-532-check-constraints-2.diff,
derby-532-check-constraints-2.stat, derby-532-fix-drop-not-nullable.diff, derby-532-fix-drop-not-nullable.status,
derby-532-fix-metadata-1.diff, derby-532-fix-metadata-1.status, derby-532-fk-1.diff, derby-532-fk-3.diff,
derby-532-fk-3.stat, derby-532-fk-4.diff, derby-532-fk-5.diff, derby-532-fk-5.stat, derby-532-fk-6.diff,
derby-532-fk-6.stat, derby-532-fk-baseline-2.diff, derby-532-fk-baseline.diff, derby-532-import-1.diff,
derby-532-import-1.status, derby-532-import-2.diff, derby-532-import-3.diff, derby-532-import-3.status,
derby-532-more-tests-1.diff, derby-532-more-tests-1.stat, derby-532-nullableUniqueFix.diff,
derby-532-nullableUniqueFix.status, derby-532-post-scan-1.diff, derby-532-post-scan-1.stat,
derby-532-post-scan-2.diff, derby-532-post-scan-2.stat, derby-532-post-scan-3.diff, derby-532-post-scan-3.stat,
derby-532-post-scan-4.diff, derby-532-post-scan-4.stat, derby-532-serializable-scan-1.diff,
derby-532-serializable-scan-2.diff, derby-532-serializable-scan-2.stat, derby-532-syntax-binding-dict-1.diff,
derby-532-syntax-binding-dict-1.status, derby-532-syntax-binding-dict-2.diff, derby-532-syntax-binding-dict-2.status,
derby-532-syntax-binding-dict-all-1.diff, derby-532-test-speedup.diff, derby-532-test-speedup.status,
derby-532-test-with-default-deferrable-all-over.diff, derby-532-testAlterConstraintInvalidation.diff,
derby-532-testAlterConstraintInvalidation.status, derby-532-unique-pk-1.diff, derby-532-unique-pk-1.status,
derby-532-unique-pk-2.diff, derby-532-unique-pk-3.diff, derby-532-unique-pk-3.status, derby-532-upgrade-1.diff,
derby-532-upgrade-1.status, derby-532-upgrade-1b.diff, derby-532-xa-1.diff, derby-532-xa-2.diff,
derby-532-xa-3.diff, derby-532-xa-3.status
>
>
> In many situations it is desirable to have constraints checking taking place only at
transaction commit time, and not before. If e.g. there is a chain of foreign key constraints
between tables, insert statements have to be ordered to avoid constraint violations. If foreign
key references are circular, the DML has to be split into insert statements and subsequent
update statements by the user.
> In other words, with deferred constraints checking, life is much easier for the user.
Also it can create problems with softwares such as object-relational mapping tools that are
not prepared for statement ordering and thus depend on deferred constraints checking.



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message