[
https://issues.apache.org/jira/browse/DERBY-532?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13910884#comment-13910884
]
Dag H. Wanvik edited comment on DERBY-532 at 2/24/14 10:17 PM:
---------------------------------------------------------------
Uploading a first patch {color:red}derby-532-check-constraints-1{color} which
implements deferred check constraints and supporting tests. Comments are
welcome.
The high level approach is as follows. When a violation occurs, we note the
row location in the base table of the offending row. At commit time (or when
switching a constraint to immediate), we revisit those rows using the row
locations if they are still valid, and validate those rows again. This is
achieved by positioning to the saved row locations in combination with a
specially crafted result set:
{color:blue}ValidateCheckConstraintResultSet{color} (see
{color:blue}ProjectRestrictResultSet#getNextRowCore{color}) which positions to
the offending base row using
{color:blue}ValidateCheckConstraintResultSet#positionScanAtRowLocation{color}
before letting {color:blue}ValidateCheckConstraintResultSet{color} read the
row. If the row locations are no longer valid, e.g. an intervening compress
happened, we do a full table scan to verify the constraints instead.
Adding a constraint in deferred constraint mode is currently sub-optimal, since
we currently do a full table scan via an internally generated
{color:blue}"SELECT .. WHERE NOT <constraints>"{color}, and we don't have a way
the get at the row locations of the offending rows in this case. I might add a
specially tailored result set for that purpose later.
Normally, when a row is inserted or updated, we execute a generated method
which combines evaluation of all check constraints on the table relevant for
the inserted or updated columns. This evaluation is performed using McCarthy
boolean evaluation (short-circuits as soon as result is known). This isn't
optimal for deferred constraints, as we'd need to assume all constraints were
violated in such a case. The implementation replaces the short-circuited
evaluation with a full evaluation, so we can remember exactly which constraints
were violated, cf. {color:blue}AndNoShortCircuitNode{color} and
{color:blue}SQLBoolean#throwExceptionIfImmediateAndFalse{color}. A violation in
{color:blue}throwExceptionIfImmediateAndFalse{color} when we have a deferred
constraint is noted ({color:blue}DMLWriteResultSet#rememberConstraint{color}
implemented by {color:blue}UpdateResultSet{color} and
{color:blue}InsertResultSet{color}) by adding the violation to a list for that
row. After the insert/update is completed, the set of violations is remembered
for posterity, cf. {color:blue}InsertResultSet#normalInsertCode{color} and
{color:blue}UpdateResultSet#collectAffectedRows{color} by inspecting the lists
({color:blue}#violatingCheckConstraints{color}).
Note that we currently do not note which constraints were violated *for each
individual row*, only per table in the transaction. This means that we visit
potentially more rows over again when a single constraint is changed to
immediate. This could be improved further by storing the set of violated
constraints along with the row location.
For bulk insert and deferred (see panel 1 below) insert row processing there is
special code paths, cf.
{color:blue}InsertResultSet#offendingRowLocation{color} which is invoked via a
callback from {color:blue}HeapController#load{color} and another path in
{color:blue}InsertResultSet#normalInsertCode{color} respectively.
For update, the code for deferred treatment is in in one of
{color:blue}UpdateResultSet#collectAffectedRows{color} and
{color:blue}UpdateResultSet#updateDeferredRows{color} depending on whether
there are triggers.
The existing test {color:blue}ConstraintCharacteristcsTest{color} has been
built out by adding check constraint to those fixture for which it is relevant,
as well as adding new ones which are only relevant for check constraints.
{quote} {color:blue}*1*{color} This "deferred" refers to Derby special handling
of rows in certain situation, for example when doing an insert which uses the
same table as a source result set, we need to make sure we don't get confused
and see the incrementally inserted rows "again" as we process the original
result set, essentially we do a snapshot of the source result set, hence
"deferred rows". {quote}
All regressions passed.
Detailed code comments:
{quote}
M java/engine/org/apache/derby/iapi/sql/conn/SQLSessionContext.java
M java/engine/org/apache/derby/impl/sql/conn/SQLSessionContextImpl.java
M java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java
M
java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java
D java/engine/org/apache/derby/impl/sql/execute/DeferredDuplicates.java
A java/engine/org/apache/derby/impl/sql/execute/DeferredConstraintsMemory.java
{quote}
Extended and refactored slightly existing mechanism for deferred primary
key/unique constraints to also cater for check constraints. Since the hash key
we used for the memory of primary key and unique constraints was the
conglomerate id of the indexes, and those are guaranteed to be disjoint from
the conglomerate ids of the base tables having deferred constraints, we can use
the same hash table to find the "memory" in the form of the disk based hash
table (BackingStoreHashtable), cf. LCC#getDeferredHashTables.--
{quote}
M java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java-
{quote}
Code to drop any deferred constraints memory in the transaction when a
constraint is dropped.-
{quote}
M java/engine/org/apache/derby/impl/store/access/heap/HeapController.java
{quote}
Call back added for bulk insert in the presence of deferrable check constraints.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/NoPutResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
M java/engine/org/apache/derby/iapi/store/access/RowLocationRetRowSource.java
{quote}
Extra plumbing to be able to signal to HeapController that we need to do a
callback with the inserted row location (for bulk insert)
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/TargetResultSet.java
{quote}
Extra interface method, offendingRowLocation. Only implemented with meaningful
semantics for NoPutResultSetImpl which calls it for its targetResultSet, an
InsertResultSet.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
{quote}
More parameters to getProjectRestrictResult set to do the magic mention in the
overview for that result set, pass along schema and table name to
InsertResultSet so we can remember them for check violations. They are used to
produced checking SQL statements. This may be a bit fragile, since a rename
schema or table could make those invalid. However, there is presently no RENAME
SCHEMA in Derby and the RENAME TABLE is illegal in certain cases, notably if
there is a check constraint defined on it, so the solution should be OK for
now. Also adds an interface method, getValidateCheckConstraintResultSet, to
allow the execution run-time to build one of those, cf. code generation logic
in NestedLoopStrategy#resultSetMethodName.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/RowChanger.java
M java/engine/org/apache/derby/impl/sql/execute/RowChangerImpl.java
{quote}
Extra parameter to insertRow to get at the row location if needed.
{quote}
M java/engine/org/apache/derby/iapi/store/access/BackingStoreHashtable.java
M java/engine/org/apache/derby/iapi/store/access/ScanController.java
{quote}
Javadoc fixes.
{quote}
M java/engine/org/apache/derby/iapi/types/BooleanDataValue.java
M java/engine/org/apache/derby/iapi/types/SQLBoolean.java
{quote}
Extra method throwExceptionIfImmediateAndFalse used by deferred check
constraints to make a note of all violated constraints as evaluated by the
generated method. Picked up by InsertResultSet or UpdateResultSet.
{quote}
A java/engine/org/apache/derby/impl/sql/compile/AndNoShortCircuitNode.java
M java/engine/org/apache/derby/impl/sql/compile/AndNode.java
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
{quote}
AndNoShortCircuitNode is used to represent a non-McCarthy evaluation of the
combined check constraints. See usage in DMLModStatementNode#generateCheckTree.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
{quote}
Extra dummy parameter added for call to super#bindConstraints
(DMLModStatementNode). Only used by insert.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
{quote}
Pick up the DERBY_PROPERTIES value for property "validateCheckConstraint =
<conlomerateId>" we provide to the checking query (internal syntax only)
generated by DeferredConstraintsMemory#validateCheck. The conglomerate id is
used to retrieve the violating rows information set up by
ProjectRestrictResultSet#openCore to drive ValidateCheckConstraintResultSet.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
{quote}
Boolean member variable to know if we have a deferrable check constraint; also
pass only schema and table name to the result set. Passed on to the
InsertConstantAction from which InsertResultSet can pick it up.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
{quote}
Logic to keep track of whether we are used by the special internal query to
check violated check constraints. In this case we also do not push the check
predicates down to store for simpler handling.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
{quote}
Code to parse a long value from "--DERBY-PROPERTIES" property.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/SetConstraintsNode.java
{quote}
Extra code to comply with the sane mode parse tree printing conventions.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/TestConstraintNode.java
{quote}
Handle different code generation for deferrable check contraints.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
{quote}
Pass on more info: schema and table name + small refactoring.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
{quote}
Handle the new internal query to validate violated check constraints. Cf. query
in DeferredConstraintsMemory#validateCheck.
{quote}
M
java/engine/org/apache/derby/impl/sql/execute/AlterConstraintConstantAction.java
M
java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java
{quote}
Open up for check constraints.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/ConstraintConstantAction.java
{quote}
ATCA: Special handling of adding a deferred check constraint: need different
code path to get the UUID of constraint soon enough to be able to note any
constraint violations. CCA: note any violation and remember it. We'd like to
remember that row locations of the offending rows here, but not done for now,
so at checking time, we'll need a full table scan. This can be improved upon,
see code comment.
{quote}
M
java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
M java/engine/org/apache/derby/impl/sql/execute/UpdateConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java
{quote}
Pass on more info to InsertConstantAction and UpdateConstantAction needed by
the result sets.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
{quote}
Drives the checking for check constraints, and picks up the result. If we have
violations and deferred constraints, we remember that. Also some refactorings
to avoid local variables shadowing globals.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
{quote}
Drives the checking for check constraints, and picks up the result. If we have
violations and deferred constraints, we remember that.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
{quote}
Removed unused method.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java
{quote}
Drive the special result set, ValidateCheckConstraintResultSet by positioning
it correctly for each row retrieved, using the remembered row locations from
violation time.
{quote}
M
java/engine/org/apache/derby/impl/sql/execute/SetConstraintsConstantAction.java
{quote}
Added logic for check constraints. Also added a new check that the user don't
specify the same constraint twice, cf new test case for it.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/TableScanResultSet.java
{quote}
Make some members protected rather than private, to let the new result set
ValidateCheckConstraintResultSet inherit from it.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderResultSet.java
M java/engine/org/apache/derby/impl/store/access/sort/MergeScanRowSource.java
M java/engine/org/apache/derby/impl/store/access/sort/SortBufferRowSource.java
M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java
M java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
{quote}
Boiler plate to comply with interface (not used).
{quote}
M java/engine/org/apache/derby/impl/sql/execute/UniqueIndexSortObserver.java
M
java/engine/org/apache/derby/impl/sql/execute/UniqueWithDuplicateNullsIndexSortObserver.java
M java/engine/org/apache/derby/impl/sql/execute/IndexChanger.java
M java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java
{quote}
Refactoring only.
{quote}
A
java/engine/org/apache/derby/impl/sql/execute/ValidateCheckConstraintResultSet.java
{quote}
The new result we use to check violating rows only based on row location
{quote}
M java/engine/org/apache/derby/iapi/sql/compile/JoinStrategy.java
M java/engine/org/apache/derby/impl/sql/compile/HashJoinStrategy.java
M java/engine/org/apache/derby/impl/sql/compile/NestedLoopJoinStrategy.java
{quote}
New boolean to signal that we want ValidateCheckConstraintResultSet
{quote}
M java/engine/org/apache/derby/jdbc/EmbedXAResource.java
M java/engine/org/apache/derby/jdbc/XATransactionState.java
{quote}
Extra logic to handle check constraints (already had it for primary key and
unique).
{quote}
M java/engine/org/apache/derby/iapi/error/ExceptionUtil.java
{quote}
Utility method to determine if an exception if a transaction deferred
constraint violation. Needed by the XA code.
{quote}
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
{quote}
New error messages
{quote}
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java
{quote}
New test cases and extension of present ones to include check constraints
{quote}
M
java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
{quote}
Extension of present test cases to include check constraints.
was (Author: dagw):
Uploading a first patch {code:red}derby-532-check-constraints-1{color} which
implements deferred check constraints and supporting tests. Comments are
welcome.
The high level approach is as follows. When a violation occurs, we note the
row location in the base table of the offending row. At commit time (or when
switching a constraint to immediate), we revisit those rows using the row
locations if they are still valid, and validate those rows again. This is
achieved by positioning to the saved row locations in combination with a
specially crafted result set:
{color:blue}ValidateCheckConstraintResultSet{color} (see
{color:blue}ProjectRestrictResultSet#getNextRowCore{color}) which positions to
the offending base row using
{color:blue}ValidateCheckConstraintResultSet#positionScanAtRowLocation{color}
before letting {color:blue}ValidateCheckConstraintResultSet{color} read the
row. If the row locations are no longer valid, e.g. an intervening compress
happened, we do a full table scan to verify the constraints instead.
Adding a constraint in deferred constraint mode is currently sub-optimal, since
we currently do a full table scan via an internally generated
{color:blue}"SELECT .. WHERE NOT <constraints>"{color}, and we don't have a way
the get at the row locations of the offending rows in this case. I might add a
specially tailored result set for that purpose later.
Normally, when a row is inserted or updated, we execute a generated method
which combines evaluation of all check constraints on the table relevant for
the inserted or updated columns. This evaluation is performed using McCarthy
boolean evaluation (short-circuits as soon as result is known). This isn't
optimal for deferred constraints, as we'd need to assume all constraints were
violated in such a case. The implementation replaces the short-circuited
evaluation with a full evaluation, so we can remember exactly which constraints
were violated, cf. {color:blue}AndNoShortCircuitNode{color} and
{color:blue}SQLBoolean#throwExceptionIfImmediateAndFalse{color}. A violation in
{color:blue}throwExceptionIfImmediateAndFalse{color} when we have a deferred
constraint is noted ({color:blue}DMLWriteResultSet#rememberConstraint{color}
implemented by {color:blue}UpdateResultSet{color} and
{color:blue}InsertResultSet{color}) by adding the violation to a list for that
row. After the insert/update is completed, the set of violations is remembered
for posterity, cf. {color:blue}InsertResultSet#normalInsertCode{color} and
{color:blue}UpdateResultSet#collectAffectedRows{color} by inspecting the lists
({color:blue}#violatingCheckConstraints{color}).
Note that we currently do not note which constraints were violated *for each
individual row*, only per table in the transaction. This means that we visit
potentially more rows over again when a single constraint is changed to
immediate. This could be improved further by storing the set of violated
constraints along with the row location.
For bulk insert and deferred (see panel 1 below) insert row processing there is
special code paths, cf.
{color:blue}InsertResultSet#offendingRowLocation{color} which is invoked via a
callback from {color:blue}HeapController#load{color} and another path in
{color:blue}InsertResultSet#normalInsertCode{color} respectively.
For update, the code for deferred treatment is in in one of
{color:blue}UpdateResultSet#collectAffectedRows{color} and
{color:blue}UpdateResultSet#updateDeferredRows{color} depending on whether
there are triggers.
The existing test {color:blue}ConstraintCharacteristcsTest{color} has been
built out by adding check constraint to those fixture for which it is relevant,
as well as adding new ones which are only relevant for check constraints.
{quote} {color:blue}*1*{color} This "deferred" refers to Derby special handling
of rows in certain situation, for example when doing an insert which uses the
same table as a source result set, we need to make sure we don't get confused
and see the incrementally inserted rows "again" as we process the original
result set, essentially we do a snapshot of the source result set, hence
"deferred rows". {quote}
All regressions passed.
Detailed code comments:
{quote}
M java/engine/org/apache/derby/iapi/sql/conn/SQLSessionContext.java
M java/engine/org/apache/derby/impl/sql/conn/SQLSessionContextImpl.java
M java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java
M
java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java
D java/engine/org/apache/derby/impl/sql/execute/DeferredDuplicates.java
A java/engine/org/apache/derby/impl/sql/execute/DeferredConstraintsMemory.java
{quote}
Extended and refactored slightly existing mechanism for deferred primary
key/unique constraints to also cater for check constraints. Since the hash key
we used for the memory of primary key and unique constraints was the
conglomerate id of the indexes, and those are guaranteed to be disjoint from
the conglomerate ids of the base tables having deferred constraints, we can use
the same hash table to find the "memory" in the form of the disk based hash
table (BackingStoreHashtable), cf. LCC#getDeferredHashTables.--
{quote}
M java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java-
{quote}
Code to drop any deferred constraints memory in the transaction when a
constraint is dropped.-
{quote}
M java/engine/org/apache/derby/impl/store/access/heap/HeapController.java
{quote}
Call back added for bulk insert in the presence of deferrable check constraints.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/NoPutResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
M java/engine/org/apache/derby/iapi/store/access/RowLocationRetRowSource.java
{quote}
Extra plumbing to be able to signal to HeapController that we need to do a
callback with the inserted row location (for bulk insert)
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/TargetResultSet.java
{quote}
Extra interface method, offendingRowLocation. Only implemented with meaningful
semantics for NoPutResultSetImpl which calls it for its targetResultSet, an
InsertResultSet.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
{quote}
More parameters to getProjectRestrictResult set to do the magic mention in the
overview for that result set, pass along schema and table name to
InsertResultSet so we can remember them for check violations. They are used to
produced checking SQL statements. This may be a bit fragile, since a rename
schema or table could make those invalid. However, there is presently no RENAME
SCHEMA in Derby and the RENAME TABLE is illegal in certain cases, notably if
there is a check constraint defined on it, so the solution should be OK for
now. Also adds an interface method, getValidateCheckConstraintResultSet, to
allow the execution run-time to build one of those, cf. code generation logic
in NestedLoopStrategy#resultSetMethodName.
{quote}
M java/engine/org/apache/derby/iapi/sql/execute/RowChanger.java
M java/engine/org/apache/derby/impl/sql/execute/RowChangerImpl.java
{quote}
Extra parameter to insertRow to get at the row location if needed.
{quote}
M java/engine/org/apache/derby/iapi/store/access/BackingStoreHashtable.java
M java/engine/org/apache/derby/iapi/store/access/ScanController.java
{quote}
Javadoc fixes.
{quote}
M java/engine/org/apache/derby/iapi/types/BooleanDataValue.java
M java/engine/org/apache/derby/iapi/types/SQLBoolean.java
{quote}
Extra method throwExceptionIfImmediateAndFalse used by deferred check
constraints to make a note of all violated constraints as evaluated by the
generated method. Picked up by InsertResultSet or UpdateResultSet.
{quote}
A java/engine/org/apache/derby/impl/sql/compile/AndNoShortCircuitNode.java
M java/engine/org/apache/derby/impl/sql/compile/AndNode.java
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
{quote}
AndNoShortCircuitNode is used to represent a non-McCarthy evaluation of the
combined check constraints. See usage in DMLModStatementNode#generateCheckTree.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
{quote}
Extra dummy parameter added for call to super#bindConstraints
(DMLModStatementNode). Only used by insert.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
{quote}
Pick up the DERBY_PROPERTIES value for property "validateCheckConstraint =
<conlomerateId>" we provide to the checking query (internal syntax only)
generated by DeferredConstraintsMemory#validateCheck. The conglomerate id is
used to retrieve the violating rows information set up by
ProjectRestrictResultSet#openCore to drive ValidateCheckConstraintResultSet.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
{quote}
Boolean member variable to know if we have a deferrable check constraint; also
pass only schema and table name to the result set. Passed on to the
InsertConstantAction from which InsertResultSet can pick it up.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
{quote}
Logic to keep track of whether we are used by the special internal query to
check violated check constraints. In this case we also do not push the check
predicates down to store for simpler handling.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
{quote}
Code to parse a long value from "--DERBY-PROPERTIES" property.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/SetConstraintsNode.java
{quote}
Extra code to comply with the sane mode parse tree printing conventions.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/TestConstraintNode.java
{quote}
Handle different code generation for deferrable check contraints.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
{quote}
Pass on more info: schema and table name + small refactoring.
{quote}
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
{quote}
Handle the new internal query to validate violated check constraints. Cf. query
in DeferredConstraintsMemory#validateCheck.
{quote}
M
java/engine/org/apache/derby/impl/sql/execute/AlterConstraintConstantAction.java
M
java/engine/org/apache/derby/impl/sql/execute/CreateConstraintConstantAction.java
{quote}
Open up for check constraints.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/ConstraintConstantAction.java
{quote}
ATCA: Special handling of adding a deferred check constraint: need different
code path to get the UUID of constraint soon enough to be able to note any
constraint violations. CCA: note any violation and remember it. We'd like to
remember that row locations of the offending rows here, but not done for now,
so at checking time, we'll need a full table scan. This can be improved upon,
see code comment.
{quote}
M
java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
M java/engine/org/apache/derby/impl/sql/execute/UpdateConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java
{quote}
Pass on more info to InsertConstantAction and UpdateConstantAction needed by
the result sets.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
{quote}
Drives the checking for check constraints, and picks up the result. If we have
violations and deferred constraints, we remember that. Also some refactorings
to avoid local variables shadowing globals.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
{quote}
Drives the checking for check constraints, and picks up the result. If we have
violations and deferred constraints, we remember that.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
{quote}
Removed unused method.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/ProjectRestrictResultSet.java
{quote}
Drive the special result set, ValidateCheckConstraintResultSet by positioning
it correctly for each row retrieved, using the remembered row locations from
violation time.
{quote}
M
java/engine/org/apache/derby/impl/sql/execute/SetConstraintsConstantAction.java
{quote}
Added logic for check constraints. Also added a new check that the user don't
specify the same constraint twice, cf new test case for it.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/TableScanResultSet.java
{quote}
Make some members protected rather than private, to let the new result set
ValidateCheckConstraintResultSet inherit from it.
{quote}
M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderResultSet.java
M java/engine/org/apache/derby/impl/store/access/sort/MergeScanRowSource.java
M java/engine/org/apache/derby/impl/store/access/sort/SortBufferRowSource.java
M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java
M java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
{quote}
Boiler plate to comply with interface (not used).
{quote}
M java/engine/org/apache/derby/impl/sql/execute/UniqueIndexSortObserver.java
M
java/engine/org/apache/derby/impl/sql/execute/UniqueWithDuplicateNullsIndexSortObserver.java
M java/engine/org/apache/derby/impl/sql/execute/IndexChanger.java
M java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java
{quote}
Refactoring only.
{quote}
A
java/engine/org/apache/derby/impl/sql/execute/ValidateCheckConstraintResultSet.java
{quote}
The new result we use to check violating rows only based on row location
{quote}
M java/engine/org/apache/derby/iapi/sql/compile/JoinStrategy.java
M java/engine/org/apache/derby/impl/sql/compile/HashJoinStrategy.java
M java/engine/org/apache/derby/impl/sql/compile/NestedLoopJoinStrategy.java
{quote}
New boolean to signal that we want ValidateCheckConstraintResultSet
{quote}
M java/engine/org/apache/derby/jdbc/EmbedXAResource.java
M java/engine/org/apache/derby/jdbc/XATransactionState.java
{quote}
Extra logic to handle check constraints (already had it for primary key and
unique).
{quote}
M java/engine/org/apache/derby/iapi/error/ExceptionUtil.java
{quote}
Utility method to determine if an exception if a transaction deferred
constraint violation. Needed by the XA code.
{quote}
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
{quote}
New error messages
{quote}
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java
{quote}
New test cases and extension of present ones to include check constraints
{quote}
M
java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
{quote}
Extension of present test cases to include check constraints.
> 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
> 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-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-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.1.5#6160)
