db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1593557 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 09 May 2014 14:58:00 GMT
Author: dag
Date: Fri May  9 14:58:00 2014
New Revision: 1593557

URL: http://svn.apache.org/r1593557
Log:
DERBY-6559 A immediate Fk constraint blows up iff its referenced PK is deferred and we delete
a duplicate

Patch derby-6559 changes ReferencedKeyRIChecker to omit checking
dependent tables iff the referenced key is deferred and has rows with
duplicate keys one of whom is attempted deleted. So, in effect, the
check in such a case happens only if the row is "the last of its
kind", i.e. the last row having a particular referenced key. Added
tests for this behavior. To determine whether we have duplicates, we
open an extra scan on the index.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/FKInfo.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/ReferencedKeyRIChecker.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/compile/DMLModStatementNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java?rev=1593557&r1=1593556&r2=1593557&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
Fri May  9 14:58:00 2014
@@ -33,7 +33,6 @@ import org.apache.derby.iapi.services.cl
 import org.apache.derby.iapi.services.compiler.MethodBuilder;
 import org.apache.derby.iapi.services.context.ContextManager;
 import org.apache.derby.iapi.services.io.FormatableBitSet;
-import org.apache.derby.shared.common.sanity.SanityManager;
 import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.OptimizerFactory;
@@ -50,16 +49,17 @@ import org.apache.derby.iapi.sql.diction
 import org.apache.derby.iapi.sql.dictionary.ConstraintDescriptorList;
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.ForeignKeyConstraintDescriptor;
-import org.apache.derby.iapi.sql.dictionary.TriggerDescriptorList;
 import org.apache.derby.iapi.sql.dictionary.IndexRowGenerator;
 import org.apache.derby.iapi.sql.dictionary.ReferencedKeyConstraintDescriptor;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TriggerDescriptor;
+import org.apache.derby.iapi.sql.dictionary.TriggerDescriptorList;
 import org.apache.derby.iapi.store.access.TransactionController;
 import org.apache.derby.iapi.types.DataTypeDescriptor;
 import org.apache.derby.impl.sql.execute.FKInfo;
 import org.apache.derby.impl.sql.execute.TriggerInfo;
+import org.apache.derby.shared.common.sanity.SanityManager;
 
 /**
  * A DMLStatement for a table modification: to wit, INSERT
@@ -1011,11 +1011,12 @@ abstract class DMLModStatementNode exten
 				continue;
 			}
 
-			TableDescriptor	pktd = refcd.getTableDescriptor();
-			UUID pkuuid = refcd.getIndexId();
-			ConglomerateDescriptor pkIndexConglom = pktd.getConglomerateDescriptor(pkuuid);
+            final TableDescriptor   pktd = refcd.getTableDescriptor();
+            final UUID pkIndexId = refcd.getIndexId();
+            final ConglomerateDescriptor pkIndexConglom =
+                    pktd.getConglomerateDescriptor(pkIndexId);
 
-			TableDescriptor refTd = cd.getTableDescriptor();
+            final TableDescriptor refTd = cd.getTableDescriptor();
 
             fkList.add(
                 new FKInfo(
@@ -1024,14 +1025,16 @@ abstract class DMLModStatementNode exten
                     refTd.getName(),        // table being modified
                     statementType,          // INSERT|UPDATE|DELETE
                     type,                   // FOREIGN_KEY|REFERENCED_KEY
-                    pkuuid,                 // referenced backing index uuid
+                    pkIndexId,              // referenced backing index uuid
                     pkIndexConglom.getConglomerateNumber(),
                                             // referenced backing index conglom
+                    refcd.deferrable(),     // referenced constraint is
+                                            // deferrable?
                     uuids,                  // fk backing index uuids
                     conglomNumbers,         // fk backing index congloms
                     isSelfReferencingFK,    // is self ref array of bool
                     remapReferencedColumns(cd, rowMap),
-                                            // column referened by key
+                                            // columns referenced by key
                     dd.getRowLocationTemplate(getLanguageConnectionContext(),
                                               refTd),
                                             // row location template for table

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/FKInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/FKInfo.java?rev=1593557&r1=1593556&r2=1593557&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/FKInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/FKInfo.java Fri May
 9 14:58:00 2014
@@ -72,6 +72,7 @@ public class FKInfo implements Formatabl
     int                 type;
     UUID                refUUID; // index index conglomerate uuid
     long                refConglomNumber;
+    boolean             refConstraintIsDeferrable;
     int                 stmtType;
     RowLocation         rowLocation;
 
@@ -99,8 +100,10 @@ public class FKInfo implements Formatabl
 	 * @param tableName	the name of the table being modified
 	 * @param stmtType	the type of the statement: e.g. StatementType.INSERT
 	 * @param type either FKInfo.REFERENCED_KEY or FKInfo.FOREIGN_KEY
-	 * @param refUUID UUID of the referenced constraint
+     * @param refUUID UUID of the referenced constraint's supporting index
      * @param refConglomNumber conglomerate number of the referenced key
+     * @param refConstraintIsDeferrable {@code true} iff the referenced key
+     *                                  constraint is deferrable
 	 * @param fkUUIDs an array of fkUUIDs of backing indexes.  if
 	 *			FOREIGN_KEY, then just one element, the backing
      *          index of the referenced keys.  if REFERENCED_KEY,
@@ -117,6 +120,7 @@ public class FKInfo implements Formatabl
 	 *			used to pass in a template row to tc.openScan()
      * @param raRules referential action rules
      * @param deferrable the corresponding constraint is deferrable
+     * @param fkIds the foreign key constraints' uuids.
 	 */
 	public FKInfo(
 					String[]			fkConstraintNames,
@@ -126,6 +130,7 @@ public class FKInfo implements Formatabl
 					int					type,
 					UUID				refUUID,
 					long				refConglomNumber,
+                    boolean             refConstraintIsDeferrable,
 					UUID[]				fkUUIDs,
 					long[]				fkConglomNumbers,
 					boolean[]			fkIsSelfReferencing,
@@ -143,6 +148,7 @@ public class FKInfo implements Formatabl
 		this.type = type;
 		this.refUUID = refUUID;
 		this.refConglomNumber = refConglomNumber;
+        this.refConstraintIsDeferrable = refConstraintIsDeferrable;
         this.fkUUIDs = ArrayUtil.copy(fkUUIDs);
         this.fkConglomNumbers = ArrayUtil.copy(fkConglomNumbers);
         this.fkIsSelfReferencing = ArrayUtil.copy(fkIsSelfReferencing);

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=1593557&r1=1593556&r2=1593557&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  9 14:58:00 2014
@@ -56,7 +56,7 @@ public abstract class GenericRIChecker
     protected BackingStoreHashtable deferredRowsHashTable;
 
     private final Hashtable<Long,ScanController> scanControllers;
-    private final int numColumns;
+    protected final int numColumns;
     final IndexRow indexQualifierRow;
 
 	/**

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=1593557&r1=1593556&r2=1593557&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  9 14:58:00 2014
@@ -24,14 +24,15 @@ package org.apache.derby.impl.sql.execut
 import org.apache.derby.catalog.UUID;
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.services.io.FormatableBitSet;
 import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.iapi.sql.StatementUtil;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
-import org.apache.derby.iapi.sql.execute.ExecIndexRow;
 import org.apache.derby.iapi.sql.execute.ExecRow;
 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.shared.common.sanity.SanityManager;
 
 /**
@@ -44,6 +45,9 @@ import org.apache.derby.shared.common.sa
  */
 public class ReferencedKeyRIChecker extends GenericRIChecker
 {
+    private ScanController refKeyIndexScan = null;
+    private DataValueDescriptor[] refKey = new DataValueDescriptor[numColumns];
+
 	/**
      * @param lcc       the language connection context
 	 * @param tc		the xact controller
@@ -81,6 +85,7 @@ public class ReferencedKeyRIChecker exte
 	 * @exception StandardException on unexpected error, or
 	 *		on a primary/unique key violation
 	 */
+    @Override
     void doCheck(Activation a,
                  ExecRow row,
                  boolean restrictCheckOnly) throws StandardException
@@ -94,6 +99,20 @@ public class ReferencedKeyRIChecker exte
 			return;
 		}
 
+        if (fkInfo.refConstraintIsDeferrable) {
+            // We may have more than one row if the referenced constraint is
+            // deferred, if so, all is good: no foreign key constraints can be
+            // violated. DERBY-6559
+            if (lcc.isEffectivelyDeferred(
+                    lcc.getCurrentSQLSessionContext(a),
+                    fkInfo.refConglomNumber)) {
+                // It *is* deferred, go see if we have more than one row
+                if (isDuplicated(row)) {
+                    return;
+                }
+            }
+        }
+
 		/*
 		** Otherwise, should be no rows found.
 	 	** Check each conglomerate.
@@ -150,6 +169,67 @@ public class ReferencedKeyRIChecker exte
 			scan.next();
 		}
 	}
+
+    private boolean isDuplicated(ExecRow row)
+            throws StandardException {
+        final DataValueDescriptor[] indexRowArray = row.getRowArray();
+
+        for (int i = 0; i < numColumns; i++)
+        {
+            refKey[i] = indexRowArray[fkInfo.colArray[i] - 1];
+        }
+
+        if (refKeyIndexScan == null) {
+            refKeyIndexScan = tc.openScan(
+                    fkInfo.refConglomNumber,
+                    false,                  // no hold over commit
+                    0,                      // read only
+                    TransactionController.MODE_RECORD,
+                                            // record locking
+                    TransactionController.ISOLATION_READ_COMMITTED_NOHOLDLOCK,
+                    (FormatableBitSet)null, // retrieve all fields
+                    refKey,                 // startKeyValue
+                    ScanController.GE,      // startSearchOp
+                    null,                   // qualified
+                    refKey,                 // stopKeyValue
+                    ScanController.GT);     // stopSearchOp
+        } else {
+            refKeyIndexScan.reopenScan(
+                      refKey,             // startKeyValue
+                      ScanController.GE,  // startSearchOp
+                      null,               // qualifier
+                      refKey,             // 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
+        }
+
+        return false;
+    }
+
+    /**
+     * Clean up all scan controllers
+     *
+     * @exception StandardException on error
+     */
+    void close()
+        throws StandardException {
+
+        if (refKeyIndexScan != null) {
+            refKeyIndexScan.close();
+            refKeyIndexScan = null;
+        }
+
+        super.close();
+    }
+
 }
 
 

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=1593557&r1=1593556&r2=1593557&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  9 14:58:00 2014
@@ -21,13 +21,12 @@
 
 package org.apache.derbyTesting.functionTests.tests.lang;
 
+import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import junit.framework.Test;
 import junit.framework.TestSuite;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
-import static org.apache.derbyTesting.junit.BaseJDBCTestCase.usingDerbyNetClient;
-import static org.apache.derbyTesting.junit.BaseJDBCTestCase.usingEmbedded;
 import org.apache.derbyTesting.junit.JDBC;
 import org.apache.derbyTesting.junit.SupportFilesSetup;
 import static org.apache.derbyTesting.junit.TestConfiguration.clientServerSuite;
@@ -67,6 +66,8 @@ import static org.apache.derbyTesting.ju
  * RESTRICT is a stricter condition than ON UPDATE NO ACTION. ON UPDATE
  * RESTRICT prohibits an update to a particular row if there are any
  * matching rows; ON UPDATE NO ACTION does not perform its constraint
+ * check until the entire set of rows to be updated has been processed.
+ * <p/>
  * NOTE 54 - Ditto for DELETE.
  * <p/>
  * Line numbers in the comments refer to svn revision 1580845 of Derby trunk.
@@ -521,7 +522,7 @@ public class ForeignKeysDeferrableTest e
     public void testDeleteDirect() throws SQLException {
         Statement s = createStatement();
 
-        // Delete of child row is trivial, parent no affected.
+        // Delete of child row is trivial, parent not affected.
 
         // Parent
 
@@ -613,7 +614,7 @@ public class ForeignKeysDeferrableTest e
      */
         public void testDeleteDeferred() throws SQLException {
         Statement s = createStatement();
-        // Delete of child row is trivial, parent no affected.
+        // Delete of child row is trivial, parent not affected.
 
         // Parent
 
@@ -710,11 +711,11 @@ public class ForeignKeysDeferrableTest e
     }
 
     /**
-     * Insert using bulk import code path, i.e. IMPORT. Since IMPORT
+     * Insert using bulk insert code path, i.e. IMPORT. Since IMPORT
      * always performs a commit at the end, we strictly do no need to do
      * extra processing for deferrable constraints, but we do so
      * anyway to prepare for possible future lifting of this restriction to
-     * IMPORT. This behavior can no be observed externally, but we include
+     * IMPORT. This behavior can not be observed externally, but we include
      * the test here anyway as a baseline.
      *
      * @throws SQLException
@@ -800,6 +801,87 @@ 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.
+     *
+     * @throws SQLException
+     */
+    public void testFKPlusUnique() throws SQLException {
+        Statement s = createStatement(
+                ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+
+        try {
+            s.executeUpdate(
+                "create table ref_t(i int, j int, constraint ct " +
+                "    primary key(i) deferrable initially deferred)");
+            s.executeUpdate(
+                "create table t(i int unique not null, c char(1)," +
+                "    constraint c foreign key (i) references ref_t(i))");
+
+            s.executeUpdate("insert into ref_t values (1,2),(1,3),(1,4)");
+            s.executeUpdate("insert into t values (1, 'c')");
+
+            // Now, the child (referencing table) is referencing one of the the
+            // rows in the primary table whose value is 1, so the reference is
+            // ok.
+
+            // 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.next();
+            rs.deleteRow();
+            rs.next();
+            rs.deleteRow();
+            // Now there should be only one left, so the referenced table is
+            // OK.
+            commit();
+
+            // Try again, but this time with normal delete, not using cursors
+            s.executeUpdate("insert into ref_t values (1,5),(1,6)");
+            s.executeUpdate("delete from ref_t where j > 4 ");
+            commit();
+
+            // Try again, but this time delete both duplicate rows. The second
+            // delete should fail.
+            s.executeUpdate("insert into ref_t values (1,3)");
+            rs = s.executeQuery("select * from ref_t");
+            rs.next();
+            rs.deleteRow();
+            rs.next();
+
+            try {
+                rs.deleteRow();
+                fail();
+            } catch (SQLException e) {
+                assertSQLState(LANG_FK_VIOLATION, e);
+            }
+
+            JDBC.assertFullResultSet(
+                    s.executeQuery("select * from ref_t"),
+                    new String[][]{{"1", "3"}});
+
+            commit();
+
+        } finally {
+            dontThrow(s, "drop table t");
+            dontThrow(s, "drop table ref_t");
+            commit();
+        }
+
+    }
+
     private void dontThrow(Statement st, String stm) {
         try {
             st.executeUpdate(stm);



Mime
View raw message