You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2014/05/17 01:16:13 UTC
svn commit: r1595384 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dag
Date: Fri May 16 23:16:13 2014
New Revision: 1595384
URL: http://svn.apache.org/r1595384
Log:
DERBY-6576 A immediate Fk constraint blows up iff its referenced PK is deferred and we modify a duplicate key column
Patch derby-6576-3. We need to keep better track of how many rows are
deleted/modified of each key when we are doing deferred code path
deletes since the actual deletes doesn't happen until after all the
checking.
The patch does this by using a disk based hash table. Then after going
through all the rows/keys to be deleted/updated and adding them to the
hash table (one entry per unique key, with a counter), we compare the
number of key duplicate instances present with the number of (planned)
deletes. Unless at least one row would remain in the referenced table,
we have a violation of the foreign key constraint.
This method is used for both deletes in deferred row processing mode
and for updates, which always has this mode in the presence of foreign
key).
For deletes in direct delete code path, we simply check that at least
one row would remain when we do the delete.
Added extra tests, including a test that uses a compound fk/pk to
check that the index mapping happens correctly when we save the keys
to the temporary hash table.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteCascadeResultSet.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteCascadeResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteCascadeResultSet.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteCascadeResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteCascadeResultSet.java Fri May 16 23:16:13 2014
@@ -126,12 +126,13 @@ class DeleteCascadeResultSet extends Del
SQLState.LANG_NO_ROW_FOUND));
}
- runFkChecker(true); //check for only RESTRICT referential action rule violations
+ runFkChecker(true, true); // check for only RESTRICT referential
+ // action rule violations
Hashtable<String,String> mntHashTable = new Hashtable<String,String>(); //Hash Table to identify mutiple node for same table cases.
mergeRowHolders(mntHashTable);
fireBeforeTriggers(mntHashTable);
deleteDeferredRows();
- runFkChecker(false); //check for all constraint violations
+ runFkChecker(false, false); // check for all constraint violations
rowChangerFinish();
fireAfterTriggers();
}finally
@@ -291,7 +292,8 @@ class DeleteCascadeResultSet extends Del
@Override
- void runFkChecker(boolean restrictCheckOnly) throws StandardException
+ void runFkChecker(boolean restrictCheckOnly, boolean postCheck)
+ throws StandardException
{
//run the Foreign key or primary key Checker on the dependent tables
@@ -299,17 +301,19 @@ class DeleteCascadeResultSet extends Del
{
if(dependentResultSets[i] instanceof UpdateResultSet)
{
- ((UpdateResultSet) dependentResultSets[i]).runChecker(restrictCheckOnly);
+ ((UpdateResultSet) dependentResultSets[i]).runChecker(
+ restrictCheckOnly, postCheck);
}
else{
- ((DeleteCascadeResultSet)dependentResultSets[i]).runFkChecker(restrictCheckOnly);
+ ((DeleteCascadeResultSet)dependentResultSets[i]).runFkChecker(
+ restrictCheckOnly, postCheck);
}
}
//If there is more than one node for the same table
//only one node does all foreign key checks.
if(mainNodeForTable)
- super.runFkChecker(restrictCheckOnly);
+ super.runFkChecker(restrictCheckOnly, postCheck);
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java Fri May 16 23:16:13 2014
@@ -148,10 +148,11 @@ class DeleteResultSet extends DMLWriteRe
*/
if (constants.deferred)
{
- runFkChecker(true); //check for only RESTRICT referential action rule violations
+ runFkChecker(true, true); // check for only RESTRICT referential
+ // action rule violations
fireBeforeTriggers();
deleteDeferredRows();
- runFkChecker(false); //check for all constraint violations
+ runFkChecker(false, false); //check for all constraint violations
// apply
rc.finish();
fireAfterTriggers();
@@ -389,7 +390,11 @@ class DeleteResultSet extends DMLWriteRe
{
if (fkChecker != null)
{
- fkChecker.doPKCheck(activation, row, false);
+ // Argument "2" below: If a PK referenced by an FK is
+ // deferred, require at least two rows to be present in the
+ // primary table since we are deleting one of them below,
+ // and we need at least one to fulfill the constraint.
+ fkChecker.doPKCheck(activation, row, false, false, 2);
}
baseRowLocation =
@@ -537,7 +542,8 @@ class DeleteResultSet extends DMLWriteRe
// make sure foreign key constraints are not violated
- void runFkChecker(boolean restrictCheckOnly) throws StandardException
+ void runFkChecker(boolean restrictCheckOnly, boolean postCheck)
+ throws StandardException
{
if (fkChecker != null)
@@ -554,11 +560,27 @@ class DeleteResultSet extends DMLWriteRe
rs.open();
ExecRow defRLRow;
+
while ((defRLRow = rs.getNextRow()) != null)
{
+ // Argument "1" below: If a PK referenced by an FK is
+ // deferred, require at least one to be present in the
+ // primary table since we have deleted the row unless
+ // postCheck == true, in which the call to postChecks does
+ // the actual checking, and we need at least one to fulfill
+ // the constraint.
fkChecker.doPKCheck(
- activation, defRLRow, restrictCheckOnly);
+ activation,
+ defRLRow,
+ restrictCheckOnly,
+ postCheck,
+ 1);
}
+
+ if (postCheck) {
+ fkChecker.postCheck();
+ }
+
} finally
{
rs.close();
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ForeignKeyRIChecker.java Fri May 16 23:16:13 2014
@@ -75,13 +75,21 @@ public class ForeignKeyRIChecker extends
* @param a the activation
* @param row the row to check
* @param restrictCheckOnly
+ * {@code true} if the check is relevant only for RESTRICTED
+ * referential action.
+ * @param postCheck
+ * dummy (interface obligation only)
+ * @param deferredRowReq
+ * dummy (interface obligation only)
*
* @exception StandardException on unexpected error, or
* on a foreign key violation
*/
void doCheck(Activation a,
ExecRow row,
- boolean restrictCheckOnly) throws StandardException
+ boolean restrictCheckOnly,
+ boolean postCheck,
+ int deferredRowReq) throws StandardException
{
if(restrictCheckOnly) //RESTRICT rule checks are not valid here.
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericRIChecker.java Fri May 16 23:16:13 2014
@@ -57,6 +57,8 @@ public abstract class GenericRIChecker
private final Hashtable<Long,ScanController> scanControllers;
protected final int numColumns;
+ protected int[] identityMap;
+
final IndexRow indexQualifierRow;
/**
@@ -91,21 +93,25 @@ public abstract class GenericRIChecker
/**
* Check the validity of this row
*
- * @param a the activation
- * @param row the row to check
- * @param restrictCheckOnly If {@code true}, only perform check if the
- * constraint action is RESTRICT.
+ * @param a The activation
+ * @param row The row to check
+ * @param restrictCheckOnly
+ * {@code true} if the check is relevant only for RESTRICTED
+ * referential action.
+ * @param postCheck
+ * For referenced keys: if {@code true}, rows are not yet
+ * deleted, so do the check in the case of deferred PK later
+ * @param deferredRowReq
+ * For referenced keys: The required number of duplicates that
+ * need to be present. Only used if {@code postCheck==false}.
*
* @exception StandardException on error
*/
abstract void doCheck(Activation a,
ExecRow row,
- boolean restrictCheckOnly) throws StandardException;
-
- public void doCheck(Activation a, ExecRow row) throws StandardException
- {
- doCheck(a, row, false); //Check all the referential Actions
- }
+ boolean restrictCheckOnly,
+ boolean postCheck,
+ int deferredRowReq) throws StandardException;
/**
* Get a scan controller positioned using searchRow as
@@ -209,6 +215,9 @@ public abstract class GenericRIChecker
* Are any of the fields null in the row passed
* in. The only fields that are checked are those
* corresponding to the colArray in fkInfo.
+ *
+ * @param baseRow the row to check for null fields
+ * @return {@code true} if any are null
*/
boolean isAnyFieldNull(ExecRow baseRow)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RISetChecker.java Fri May 16 23:16:13 2014
@@ -86,28 +86,64 @@ public class RISetChecker
* that there is no row that matches the values in
* the passed in row.
*
- * @param row the row to check
+ * @param a The activation
+ * @param row The row to check
+ * @param restrictCheckOnly
+ * {@code true} if the check is relevant only for RESTRICTED
+ * referential action.
+ * @param postCheck
+ * For referenced keys: if {@code true}, rows are not yet
+ * deleted, so do the check in the case of deferred PK later.
+ * @param deferredRowReq
+ * For referenced keys: The required number of duplicates that
+ * need to be present. Only used if {@code postCheck==false}.
*
* @exception StandardException on unexpected error, or
* on a primary/unique key violation
*/
public void doPKCheck(Activation a,
ExecRow row,
- boolean restrictCheckOnly) throws StandardException
+ boolean restrictCheckOnly,
+ boolean postCheck,
+ int deferredRowReq) throws StandardException
{
if (checkers == null)
return;
- for (int i = 0; i < checkers.length; i++)
- {
- if (checkers[i] instanceof ReferencedKeyRIChecker)
- {
- checkers[i].doCheck(a, row,restrictCheckOnly);
- }
- }
+ for (GenericRIChecker checker : checkers) {
+ if (checker instanceof ReferencedKeyRIChecker) {
+ checker.doCheck(a,
+ row,
+ restrictCheckOnly,
+ postCheck,
+ deferredRowReq);
+ }
+ }
}
- /**
+ public void postCheck() throws StandardException
+ {
+ if (checkers == null) {
+ return;
+ }
+
+ for (int i = 0; i < checkers.length; i++) {
+ postCheck(i);
+ }
+ }
+
+ public void postCheck(int index) throws StandardException
+ {
+ if (checkers == null) {
+ return;
+ }
+
+ if (checkers[index] instanceof ReferencedKeyRIChecker) {
+ ((ReferencedKeyRIChecker)checkers[index]).postCheck();
+ }
+ }
+
+ /**
* Check that everything in the row is ok, i.e.
* that there are no foreign keys in the passed
* in row that have invalid values.
@@ -127,7 +163,7 @@ public class RISetChecker
{
if (checkers[i] instanceof ForeignKeyRIChecker)
{
- checkers[i].doCheck(a, row);
+ checkers[i].doCheck(a, row, false, false, 0);
}
}
}
@@ -137,7 +173,16 @@ public class RISetChecker
*
* @param a the activation
* @param index index into fkInfo
- * @param row the row to check
+ * @param row the row to check
+ * @param restrictCheckOnly
+ * {@code true} if the check is relevant only for RESTRICTED
+ * referential action.
+ * @param postCheck
+ * For referenced keys: if {@code true}, rows are not yet
+ * deleted, so do the check in the case of deferred PK later
+ * @param deferredRowReq
+ * For referenced keys: the required number of duplicates that
+ * need to be present. Only used if {@code postCheck==false}.
*
* @exception StandardException on unexpected error, or
* on a primary/unique key violation
@@ -145,7 +190,9 @@ public class RISetChecker
public void doRICheck(Activation a,
int index,
ExecRow row,
- boolean restrictCheckOnly) throws StandardException
+ boolean restrictCheckOnly,
+ boolean postCheck,
+ int deferredRowReq) throws StandardException
{
if (SanityManager.DEBUG)
{
@@ -161,7 +208,8 @@ public class RISetChecker
}
}
- checkers[index].doCheck(a, row, restrictCheckOnly);
+ checkers[index].doCheck(
+ a, row, restrictCheckOnly, postCheck, deferredRowReq);
}
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ReferencedKeyRIChecker.java Fri May 16 23:16:13 2014
@@ -21,6 +21,7 @@
package org.apache.derby.impl.sql.execute;
+import java.util.Enumeration;
import org.apache.derby.catalog.UUID;
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.iapi.reference.SQLState;
@@ -30,9 +31,12 @@ import org.apache.derby.iapi.sql.Stateme
import org.apache.derby.iapi.sql.StatementUtil;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
import org.apache.derby.iapi.sql.execute.ExecRow;
+import org.apache.derby.iapi.store.access.BackingStoreHashtable;
+import org.apache.derby.iapi.store.access.KeyHasher;
import org.apache.derby.iapi.store.access.ScanController;
import org.apache.derby.iapi.store.access.TransactionController;
import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.SQLLongint;
import org.apache.derby.shared.common.sanity.SanityManager;
/**
@@ -46,7 +50,20 @@ import org.apache.derby.shared.common.sa
public class ReferencedKeyRIChecker extends GenericRIChecker
{
private ScanController refKeyIndexScan = null;
- private DataValueDescriptor[] refKey = new DataValueDescriptor[numColumns];
+
+ /**
+ * Key mapping used when storing referenced (PK, unique) keys under
+ * deferred row processing and deferred key constraint (PK, unique).
+ */
+ private final DataValueDescriptor[] refKey =
+ new DataValueDescriptor[numColumns];
+
+ /**
+ * We save away keys with a counter in this hash table, so we know how many
+ * instances of a key (duplicates) have been deleted/modified, cf usage
+ * in {@link #postCheck()}. Initialized on demand.
+ */
+ private BackingStoreHashtable deletedKeys = null;
/**
* @param lcc the language connection context
@@ -81,14 +98,24 @@ public class ReferencedKeyRIChecker exte
* @param a the activation
* @param row the row to check
* @param restrictCheckOnly
- *
+ * {@code true} if the check is relevant only for RESTRICTED
+ * referential action.
+ * @param postCheck
+ * For referenced keys: if {@code true}, rows are not yet
+ * deleted, so do the check in the case of deferred PK later.
+ * @param deferredRowReq
+ * For referenced keys: The required number of duplicates that
+ * need to be present. Only used if {@code postCheck==false}.
+ *
* @exception StandardException on unexpected error, or
* on a primary/unique key violation
*/
@Override
void doCheck(Activation a,
ExecRow row,
- boolean restrictCheckOnly) throws StandardException
+ boolean restrictCheckOnly,
+ boolean postCheck,
+ int deferredRowReq) throws StandardException
{
/*
** If any of the columns are null, then the
@@ -106,9 +133,14 @@ public class ReferencedKeyRIChecker exte
if (lcc.isEffectivelyDeferred(
lcc.getCurrentSQLSessionContext(a),
fkInfo.refConglomNumber)) {
- // It *is* deferred, go see if we have more than one row
- if (isDuplicated(row)) {
- return;
+ if (postCheck) {
+ rememberKey(row);
+ } else {
+ // It *is* a deferred constraint and it is *not* a deferred
+ // rows code path, so go see if we have enough rows
+ if (isDuplicated(row, deferredRowReq)) {
+ return;
+ }
}
}
}
@@ -170,15 +202,120 @@ public class ReferencedKeyRIChecker exte
}
}
- private boolean isDuplicated(ExecRow row)
+
+ private void rememberKey(ExecRow rememberRow) throws StandardException {
+ if (deletedKeys == null) {
+ // key: all columns (these are index rows, or a row containing a
+ // row location)
+ identityMap = new int[numColumns];
+
+ for (int i = 0; i < numColumns; i++) {
+ identityMap[i] = i;
+ }
+
+ deletedKeys = new BackingStoreHashtable(
+ tc,
+ null,
+ identityMap,
+ true, // remove duplicates: no need for more copies:
+ // one is enough to know what to look for on commit
+ -1,
+ HashScanResultSet.DEFAULT_MAX_CAPACITY,
+ HashScanResultSet.DEFAULT_INITIAL_CAPACITY,
+ HashScanResultSet.DEFAULT_MAX_CAPACITY,
+ false,
+ false);
+
+ }
+
+ DataValueDescriptor[] row = rememberRow.getRowArray();
+ for (int i = 0; i < numColumns; i++) {
+ refKey[i] = row[fkInfo.colArray[i] - 1];
+ }
+
+ Object hashKey = KeyHasher.buildHashKey(refKey, identityMap);
+
+ DataValueDescriptor[] savedRow =
+ (DataValueDescriptor[])deletedKeys.remove(hashKey);
+
+ if (savedRow == null) {
+ savedRow = new DataValueDescriptor[numColumns + 1];
+ System.arraycopy(refKey, 0, savedRow, 0, numColumns);
+ savedRow[numColumns] = new SQLLongint(1);
+ } else {
+ savedRow[numColumns] = new SQLLongint(
+ ((SQLLongint)savedRow[numColumns]).getLong() + 1);
+ }
+
+ deletedKeys.putRow(false, savedRow, null);
+ }
+
+ /**
+ * Check that we have at least one more row in the referenced
+ * table table containing a key than the number of seen deletes of that key.
+ * Only used when the referenced constraint id deferred.
+ *
+ * @throws StandardException Standard error policy
+ */
+ public void postCheck() throws StandardException
+ {
+ if (!fkInfo.refConstraintIsDeferrable) {
+ return;
+ }
+
+ if (deletedKeys != null) {
+ final Enumeration<?> e = deletedKeys.elements();
+
+ while (e.hasMoreElements()) {
+ final DataValueDescriptor[] row =
+ (DataValueDescriptor[])e.nextElement();
+ final DataValueDescriptor[] key =
+ new DataValueDescriptor[row.length - 1];
+ System.arraycopy(row, 0, key, 0, key.length);
+
+ // The number of times this key is to be deleted,
+ // we need at least one more if for Fk constraint to hold.
+ final long requiredCount = row[row.length - 1].getLong() + 1;
+
+ if (!isDuplicated(key, requiredCount)) {
+ int[] oneBasedIdentityMap = new int[numColumns];
+
+ for (int i = 0; i < numColumns; i++) {
+ // Column numbers are numbere from 1 and
+ // call to RowUtil.toString below expects that
+ // convention.
+ oneBasedIdentityMap[i] = i + 1;
+ }
+
+ StandardException se = StandardException.newException(
+ SQLState.LANG_FK_VIOLATION,
+ fkInfo.fkConstraintNames[0],
+ fkInfo.tableName,
+ StatementUtil.typeName(fkInfo.stmtType),
+ RowUtil.toString(row, oneBasedIdentityMap));
+ throw se;
+ }
+ }
+ }
+ }
+
+
+ private boolean isDuplicated(ExecRow row, int deferredRowReq)
throws StandardException {
final DataValueDescriptor[] indexRowArray = row.getRowArray();
for (int i = 0; i < numColumns; i++)
{
+ // map the columns into the PK form
refKey[i] = indexRowArray[fkInfo.colArray[i] - 1];
}
+ return isDuplicated(refKey, deferredRowReq);
+ }
+
+
+ private boolean isDuplicated(DataValueDescriptor[] key, long deferredRowReq)
+ throws StandardException {
if (refKeyIndexScan == null) {
refKeyIndexScan = tc.openScan(
fkInfo.refConglomNumber,
@@ -188,37 +325,40 @@ public class ReferencedKeyRIChecker exte
// record locking
TransactionController.ISOLATION_READ_COMMITTED_NOHOLDLOCK,
(FormatableBitSet)null, // retrieve all fields
- refKey, // startKeyValue
+ key, // startKeyValue
ScanController.GE, // startSearchOp
null, // qualified
- refKey, // stopKeyValue
+ key, // stopKeyValue
ScanController.GT); // stopSearchOp
} else {
refKeyIndexScan.reopenScan(
- refKey, // startKeyValue
+ key, // startKeyValue
ScanController.GE, // startSearchOp
null, // qualifier
- refKey, // stopKeyValue
+ key, // stopKeyValue
ScanController.GT); // stopSearchOp
}
- if (refKeyIndexScan.next()) {
- if (refKeyIndexScan.next()) {
- // two matching rows found, all ok
- return true;
- } // else exactly one row contains key
- } else {
- // No rows contain key
+
+ boolean foundRow = refKeyIndexScan.next();
+
+ while (--deferredRowReq > 0 && foundRow) {
+ foundRow =refKeyIndexScan.next();
}
- return false;
+ if (deferredRowReq == 0 && foundRow) {
+ return true;
+ } else {
+ return false;
+ }
}
/**
- * Clean up all scan controllers
+ * Clean up all scan controllers and other resources
*
* @exception StandardException on error
*/
+ @Override
void close()
throws StandardException {
@@ -227,6 +367,13 @@ public class ReferencedKeyRIChecker exte
refKeyIndexScan = null;
}
+ if (deletedKeys != null) {
+ deletedKeys.close();
+ deletedKeys = null;
+ }
+
+ identityMap = null;
+
super.close();
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java Fri May 16 23:16:13 2014
@@ -267,12 +267,13 @@ class UpdateResultSet extends DMLWriteRe
if (deferred)
{
- runChecker(true); //check for only RESTRICT referential action rule violations
+ runChecker(true, true); // check for only RESTRICT referential
+ // action rule violations
fireBeforeTriggers();
updateDeferredRows();
/* Apply deferred inserts to unique indexes */
rowChanger.finish();
- runChecker(false); //check for all violations
+ runChecker(false, false); // check for all violations
fireAfterTriggers();
}
@@ -926,7 +927,8 @@ class UpdateResultSet extends DMLWriteRe
- void runChecker(boolean restrictCheckOnly) throws StandardException
+ void runChecker(boolean restrictCheckOnly, boolean postCheck)
+ throws StandardException
{
/*
@@ -956,16 +958,33 @@ class UpdateResultSet extends DMLWriteRe
** For each delete row
*/
deletedRows.open();
+
while ((deletedRow = deletedRows.getNextRow()) != null)
{
if (!foundRow(deletedRow,
fkInfoArray[i].colArray,
insertedRowHolder))
- {
- riChecker.doRICheck(
- activation, i, deletedRow, restrictCheckOnly);
+ {
+ // Argument "1" below: If a PK referenced by an FK
+ // is deferred, require at least one to be present
+ // in the primary table since we have modified the
+ // row's PK, unless postCheck == true, in which the
+ // call to postChecks does the actual checking, and
+ // we need at least one row intact to fulfill the
+ // constraint.
+ riChecker.doRICheck(
+ activation,
+ i,
+ deletedRow,
+ restrictCheckOnly,
+ postCheck,
+ 1);
}
}
+
+ if (postCheck) {
+ riChecker.postCheck(i);
+ }
}
finally
{
@@ -1008,9 +1027,14 @@ class UpdateResultSet extends DMLWriteRe
deletedRowHolder))
{
riChecker.doRICheck(
- activation, i, insertedRow, restrictCheckOnly);
+ activation,
+ i,
+ insertedRow,
+ restrictCheckOnly,
+ postCheck, // N/A, not referenced key
+ 0); // N/A, not referenced key
}
- }
+ }
}
finally
{
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java?rev=1595384&r1=1595383&r2=1595384&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java Fri May 16 23:16:13 2014
@@ -805,21 +805,33 @@ public class ForeignKeysDeferrableTest e
* The referenced constraint (in the referenced table) is a deferred unique
* or primary key constraint. This test concerns what happens if this is
* deferred, i.e. duplicate keys are allowed temporarily, and one or more
- * of them is deleted. The foreign key constraint itself could be deferred
- * or not. If this is also deferred, we'd have no issue, cf. the
- * explanation in DERBY-6559, as all checking happens later, typically at
- * commit. But it is is <em>not</em> deferred, we needed to adjust FK
- * checking at delete/update time to <b>not</b> throw foreign key violation
- * exception if a duplicate exists; otherwise we'd throw a foreign key
- * violation where none exists. The remaining row(s) will fulfill the
- * requirement. We will only check if the last such row is deleted or its
- * key modified.
+ * of them is deleted or updated. The foreign key constraint itself could
+ * be deferred or not. If this is also deferred, we'd have no issue,
+ * cf. the explanation in DERBY-6559, as all checking happens later,
+ * typically at commit. But if it is <em>not</em> deferred, we needed to
+ * adjust FK checking at delete/update time to <b>not</b> throw foreign key
+ * violation exception if a duplicate exists; otherwise we'd throw a
+ * foreign key violation where none exists. The remaining row(s) will
+ * fulfill the requirement. We will only check if the last such row is
+ * deleted or its key modified.
+ *
+ * Complicating this processing is that the delete result set has two code
+ * paths, with with deferred row processing, and one with direct row
+ * processing. Update result sets are again handled differently, but these
+ * are only ever deferred row processing in the presence of a FK on the
+ * row. The test cases below try to exhaust these code paths. See {@link
+ * org.apache.derby.impl.sql.execute.ReferencedKeyRIChecker#doCheck} and
+ * {@link
+ * org.apache.derby.impl.sql.execute.ReferencedKeyRIChecker#postCheck}.
*
* @throws SQLException
*/
public void testFKPlusUnique() throws SQLException {
Statement s = createStatement(
- ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ ResultSet.TYPE_FORWARD_ONLY,
+ ResultSet.CONCUR_UPDATABLE);
+
+ ResultSet rs = null;
try {
s.executeUpdate(
@@ -839,7 +851,7 @@ public class ForeignKeysDeferrableTest e
// What happens when we delete one copy before commit?
// Even though we have ON DELETE restrict action, there is another
// row that would satisfy the constraint.
- ResultSet rs = s.executeQuery("select * from ref_t");
+ rs = s.executeQuery("select * from ref_t");
rs.next();
rs.deleteRow();
rs.next();
@@ -868,13 +880,159 @@ public class ForeignKeysDeferrableTest e
assertSQLState(LANG_FK_VIOLATION, e);
}
+ s.executeUpdate("insert into ref_t values (1,4), (1,5)");
+
+ // direct delete code path
+ assertStatementError(LANG_FK_VIOLATION, s, "delete from ref_t");
+
+ // deferred delete code path: not ok
+ assertStatementError(LANG_FK_VIOLATION, s,
+ "delete from ref_t where i = 1 and " +
+ " i in (select i from ref_t)");
+
+ // deferred code path: OK
+ s.executeUpdate("delete from ref_t where i = 1 and " +
+ " i in (select i from ref_t) and j >= 4");
+
+ s.executeUpdate("insert into ref_t values (1,4), (1,5)");
+ s.executeUpdate("delete from ref_t where j >= 4");
JDBC.assertFullResultSet(
s.executeQuery("select * from ref_t"),
new String[][]{{"1", "3"}});
commit();
+ //
+ // Try similar with update rather than delete. In this
+ // case there is only ever a deferred code path, so separate
+ // teste cases as for delete (above) are not relevant.
+ //
+ s.executeUpdate("insert into ref_t values (1,4)");
+ s.executeUpdate("update ref_t set i = 2 where j = 4");
+ s.executeUpdate("insert into ref_t values (1,4)");
+ assertStatementError(LANG_FK_VIOLATION,
+ s,
+ "update ref_t set i = 2");
+
+ rs = s.executeQuery("select * from ref_t");
+ rs.next();
+ rs.updateInt(1, 3);
+ rs.updateRow();
+ rs.close();
+ commit();
+
+ dropTable("t");
+ dropTable("ref_t");
+ commit();
+
+ // Delete (deferred processing code path) with more complex FKs and
+ // more dups with different keys, so we can execise the postCheck
+ // mechanism in ReferencedKeyRIChecker including row/key mappings
+ s.executeUpdate(
+ "create table ref_t(c char(1), i int, j int, k int," +
+ " constraint c primary key (k, i) initially deferred)");
+ s.executeUpdate(
+ "create table t(l bigint, i int, j int, k int," +
+ " constraint c2 foreign key(i,k) references ref_t(k, i))");
+
+ // key (1, 100) has 3 dups, key (3,100) has two dups
+ s.executeUpdate("insert into ref_t values " +
+ "('a', 100, -1, 1)," +
+ "('a', 100, -2, 1)," +
+ "('a', 100, -3, 1)," +
+ "('a', 100, -1, 2)," +
+ "('a', 100, -2, 3)," +
+ "('a', 100, -3, 3)");
+
+ s.executeUpdate("insert into t values " +
+ "(-11, 1, -4, 100)," +
+ "(-12, 2, -5, 100)," +
+ "(-13, 3, -6, 100)");
+
+ // This should throw using the postCheck mechanism.
+ try {
+ s.executeUpdate(
+ "delete from ref_t where j < -1 and " +
+ " k in (select k from ref_t)");
+ fail();
+ } catch (SQLException e) {
+ assertSQLState(LANG_FK_VIOLATION, e);
+ String expected =
+ "DELETE on table 'REF_T' caused a violation" +
+ " of foreign key constraint 'C2' for key (3,100). " +
+ "The statement has been rolled back.";
+ assertEquals(expected, e.getMessage());
+ }
+
+ // These should be ok (using the postCheck mechanism), since they
+ // both leave one row in ref_t to satisfy the constraint.
+ s.executeUpdate(
+ "delete from ref_t where j < -1 and " +
+ " k in (select k from ref_t where k < 3)");
+ s.executeUpdate(
+ "delete from ref_t where j < -2 and " +
+ " k in (select k from ref_t where k >= 3)");
+
+ commit();
+
+ //
+ // Do the same exercise but now with update instead of delete
+ //
+ dropTable("t");
+ dropTable("ref_t");
+ commit();
+
+ s.executeUpdate(
+ "create table ref_t(c char(1), i int, j int, k int," +
+ " constraint c primary key (k, i) initially deferred)");
+ s.executeUpdate(
+ "create table t(l bigint, i int, j int, k int," +
+ " constraint c2 foreign key(i,k) references ref_t(k, i))");
+
+ // key (1, 100) has 3 dups, key (3,100) has two dups
+ s.executeUpdate("insert into ref_t values " +
+ "('a', 100, -1, 1)," +
+ "('a', 100, -2, 1)," +
+ "('a', 100, -3, 1)," +
+ "('a', 100, -1, 2)," +
+ "('a', 100, -2, 3)," +
+ "('a', 100, -3, 3)");
+
+ s.executeUpdate("insert into t values " +
+ "(-11, 1, -4, 100)," +
+ "(-12, 2, -5, 100)," +
+ "(-13, 3, -6, 100)");
+
+ // This should throw using the postCheck mechanism.
+ try {
+ s.executeUpdate(
+ "update ref_t set k=k*100 where j < -1 and " +
+ " k in (select k from ref_t)");
+ fail();
+ } catch (SQLException e) {
+ assertSQLState(LANG_FK_VIOLATION, e);
+ String expected =
+ "UPDATE on table 'REF_T' caused a violation" +
+ " of foreign key constraint 'C2' for key (3,100). " +
+ "The statement has been rolled back.";
+ assertEquals(expected, e.getMessage());
+ }
+
+ // These should be ok (using the postCheck mechanism), since they
+ // both leave one row in ref_t to satisfy the constraint.
+ s.executeUpdate(
+ "update ref_t set k=k*100 where j < -1 and " +
+ " k in (select k from ref_t where k < 3)");
+ s.executeUpdate(
+ "update ref_t set k=k*100 where j < -2 and " +
+ " k in (select k from ref_t where k >= 3)");
+
+ commit();
+
} finally {
+ if (rs != null) {
+ rs.close();
+ }
dropTable("t");
dropTable("ref_t");
commit();