Revision 1576367

DERBY-532 Support deferrable constraints

Patch derby-532-check-constraints-2 which implements deferred CHECK
constraints and supporting tests.

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: ValidateCheckConstraintResultSet (see
ProjectRestrictResultSet#getNextRowCore) which positions to the offending base
row using ValidateCheckConstraintResultSet#positionScanAtRowLocation before
letting ValidateCheckConstraintResultSet 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 "SELECT .. WHERE
NOT <constraints>", 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. AndNoShortCircuitNode and SQLBoolean#throwExceptionIfImmediateAndFalse. A
violation in throwExceptionIfImmediateAndFalse when we have a deferred
constraint is noted (DMLWriteResultSet#rememberConstraint implemented by
UpdateResultSet and InsertResultSet) 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. InsertResultSet#normalInsertCode and
UpdateResultSet#collectAffectedRows by inspecting the lists
(#violatingCheckConstraints).

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.  InsertResultSet#offendingRowLocation which is invoked
via a callback from HeapController#load and another path in
InsertResultSet#normalInsertCode respectively.

For update, the code for deferred treatment is in in one of
UpdateResultSet#collectAffectedRows and UpdateResultSet#updateDeferredRows
depending on whether there are triggers.

The existing test ConstraintCharacteristcsTest 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.

[1] 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".

All regressions passed.

Detailed code comments:

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

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.--

M java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java-

Code to drop any deferred constraints memory in the transaction when a
constraint is dropped.-

M java/engine/org/apache/derby/impl/store/access/heap/HeapController.java

Call back added for bulk insert in the presence of deferrable check constraints.

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

Extra plumbing to be able to signal to HeapController that we need to do a
callback with the inserted row location (for bulk insert)

M java/engine/org/apache/derby/iapi/sql/execute/TargetResultSet.java

Extra interface method, offendingRowLocation. Only implemented with meaningful
semantics for NoPutResultSetImpl which calls it for its targetResultSet, an
InsertResultSet.

M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java

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.

M java/engine/org/apache/derby/iapi/sql/execute/RowChanger.java
M java/engine/org/apache/derby/impl/sql/execute/RowChangerImpl.java

Extra parameter to insertRow to get at the row location if needed.

M java/engine/org/apache/derby/iapi/store/access/BackingStoreHashtable.java
M java/engine/org/apache/derby/iapi/store/access/ScanController.java

Javadoc fixes.

M java/engine/org/apache/derby/iapi/types/BooleanDataValue.java
M java/engine/org/apache/derby/iapi/types/SQLBoolean.java

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.

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

AndNoShortCircuitNode is used to represent a non-McCarthy evaluation of the
combined check constraints. See usage in DMLModStatementNode#generateCheckTree.

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

Extra dummy parameter added for call to super#bindConstraints
(DMLModStatementNode). Only used by insert.

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

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.

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

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.

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

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.

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

Code to parse a long value from "--DERBY-PROPERTIES" property.

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

Extra code to comply with the sane mode parse tree printing conventions.

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

Handle different code generation for deferrable check contraints.

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

Pass on more info: schema and table name + small refactoring.

M  java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Handle the new internal query to validate violated check constraints. Cf. query
in DeferredConstraintsMemory#validateCheck.

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

Open up for check constraints.

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

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.

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

Pass on more info to InsertConstantAction and UpdateConstantAction needed by the
result sets.

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

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.

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

Drives the checking for check constraints, and picks up the result. If we have
violations and deferred constraints, we remember that.

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

Removed unused method.

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

Drive the special result set, ValidateCheckConstraintResultSet by positioning it
correctly for each row retrieved, using the remembered row locations from
violation time.

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

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.

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

Make some members protected rather than private, to let the new result set
ValidateCheckConstraintResultSet inherit from it.

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

Boiler plate to comply with interface (not used).

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

Refactoring only.

A java/engine/org/apache/derby/impl/sql/execute/ValidateCheckConstraintResultSet.java

The new result we use to check violating rows only based on row location

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

New boolean to signal that we want ValidateCheckConstraintResultSet

M java/engine/org/apache/derby/jdbc/EmbedXAResource.java
M java/engine/org/apache/derby/jdbc/XATransactionState.java

Extra logic to handle check constraints (already had it for primary key and unique).

M java/engine/org/apache/derby/iapi/error/ExceptionUtil.java

Utility method to determine if an exception if a transaction deferred constraint
violation. Needed by the XA code.

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

New error messages

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

New test cases and extension of present ones to include check constraints

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

Extension of present test cases to include check constraints.