db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@apache.org
Subject svn commit: r494338 - in /db/ddlutils/trunk/src: java/org/apache/ddlutils/io/DataToDatabaseSink.java java/org/apache/ddlutils/model/Table.java test/org/apache/ddlutils/io/TestMisc.java
Date Tue, 09 Jan 2007 07:41:42 GMT
Author: tomdz
Date: Mon Jan  8 23:41:41 2007
New Revision: 494338

URL: http://svn.apache.org/viewvc?view=rev&rev=494338
Log:
Added handling of self references pointing to the currently inserted row

Modified:
    db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java
    db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java
    db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java?view=diff&rev=494338&r1=494337&r2=494338
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java Mon Jan  8 23:41:41
2007
@@ -68,6 +68,10 @@
     private int _batchSize = 1024;
     /** Stores the tables that are target of a foreign key. */
     private HashSet _fkTables = new HashSet();
+    /** Contains the tables that have a self-referencing foreign key to a (partially) identity
primary key. */
+    private HashSet _tablesWithSelfIdentityReference = new HashSet();
+    /** Contains the tables that have a self-referencing foreign key that is required. */
+    private HashSet _tablesWithRequiredSelfReference = new HashSet();
     /** Maps original to processed identities. */
     private HashMap _identityMap = new HashMap();
     /** Stores the objects that are waiting for other objects to be inserted. */
@@ -83,6 +87,33 @@
     {
         _platform = platform;
         _model    = model;
+        for (int tableIdx = 0; tableIdx < model.getTableCount(); tableIdx++)
+        {
+            Table      table     = model.getTable(tableIdx);
+            ForeignKey selfRefFk = table.getSelfReferencingForeignKey();
+
+            if (selfRefFk != null)
+            {
+                Column[] pkColumns = table.getPrimaryKeyColumns();
+
+                for (int idx = 0; idx < pkColumns.length; idx++)
+                {
+                    if (pkColumns[idx].isAutoIncrement())
+                    {
+                        _tablesWithSelfIdentityReference.add(table);
+                        break;
+                    }
+                }
+                for (int idx = 0; idx < selfRefFk.getReferenceCount(); idx++)
+                {
+                    if (selfRefFk.getReference(idx).getLocalColumn().isRequired())
+                    {
+                        _tablesWithRequiredSelfReference.add(table);
+                        break;
+                    }
+                }
+            }
+        }
     }
 
     /**
@@ -254,7 +285,8 @@
      */
     public void addBean(DynaBean bean) throws DataSinkException
     {
-        Table table = _model.getDynaClassFor(bean).getTable();
+        Table    table        = _model.getDynaClassFor(bean).getTable();
+        Identity origIdentity = buildIdentityFromPKs(table, bean);
 
         if (_ensureFkOrder && (table.getForeignKeyCount() > 0))
         {
@@ -265,10 +297,10 @@
                 ForeignKey fk         = table.getForeignKey(idx);
                 Identity   fkIdentity = buildIdentityFromFK(table, fk, bean);
 
-                if (fkIdentity != null)
+                if ((fkIdentity != null) && !fkIdentity.equals(origIdentity))
                 {
                     Identity processedIdentity = (Identity)_identityMap.get(fkIdentity);
-    
+
                     if (processedIdentity != null)
                     {
                         updateFKColumns(bean, fkIdentity.getForeignKeyName(), processedIdentity);
@@ -300,8 +332,6 @@
             }
         }
 
-        Identity origIdentity = buildIdentityFromPKs(table, bean);
-
         insertBeanIntoDatabase(table, bean);
 
         if (_ensureFkOrder && _fkTables.contains(table))
@@ -408,7 +438,57 @@
     {
         try
         {
-            _platform.insert(_connection, _model, bean);
+            boolean    needTwoStepInsert = false;
+            ForeignKey selfRefFk         = null;
+
+            if (!_platform.isIdentityOverrideOn() &&
+                _tablesWithSelfIdentityReference.contains(table))
+            {
+                selfRefFk = table.getSelfReferencingForeignKey();
+
+                // in case of a self-reference (fk points to the very row that we're inserting)
+                // and (at least) one of the pk columns is an identity column, we first need
+                // to insert the row with the fk columns set to null
+                Identity pkIdentity = buildIdentityFromPKs(table, bean);
+                Identity fkIdentity = buildIdentityFromFK(table, selfRefFk, bean);
+
+                if (pkIdentity.equals(fkIdentity))
+                {
+                    if (_tablesWithRequiredSelfReference.contains(table))
+                    {
+                        throw new DataSinkException("Can only insert rows with fk pointing
to themselves when all fk columns can be NULL (row pk is " + pkIdentity + ")");
+                    }
+                    else
+                    {
+                        needTwoStepInsert = true;
+                    }
+                }
+            }
+
+            if (needTwoStepInsert)
+            {
+                // we first insert the bean without the fk, then in the second step we update
the bean
+                // with the row with the identity pk values
+                ArrayList fkValues = new ArrayList();
+
+                for (int idx = 0; idx < selfRefFk.getReferenceCount(); idx++)
+                {
+                    String columnName = selfRefFk.getReference(idx).getLocalColumnName();
+
+                    fkValues.add(bean.get(columnName));
+                    bean.set(columnName, null);
+                }
+                _platform.insert(_connection, _model, bean);
+                for (int idx = 0; idx < selfRefFk.getReferenceCount(); idx++)
+                {
+                    bean.set(selfRefFk.getReference(idx).getLocalColumnName(), fkValues.get(idx));
+                }
+                _platform.update(_connection, _model, bean);
+            }
+            else
+            {
+                _platform.insert(_connection, _model, bean);
+            }
             if (!_connection.getAutoCommit())
             {
                 _connection.commit();

Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java?view=diff&rev=494338&r1=494337&r2=494338
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/model/Table.java Mon Jan  8 23:41:41 2007
@@ -673,6 +673,25 @@
     }
 
     /**
+     * Returns the foreign key referencing this table if it exists.
+     * 
+     * @return The self-referencing foreign key if any
+     */
+    public ForeignKey getSelfReferencingForeignKey()
+    {
+        for (int idx = 0; idx < getForeignKeyCount(); idx++)
+        {
+            ForeignKey fk = getForeignKey(idx);
+
+            if (this.equals(fk.getForeignTable()))
+            {
+                return fk;
+            }
+        }
+        return null;
+    }
+
+    /**
      * Returns the primary key columns of this table.
      * 
      * @return The primary key columns

Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java
URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java?view=diff&rev=494338&r1=494337&r2=494338
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java Mon Jan  8 23:41:41 2007
@@ -26,6 +26,7 @@
 
 import junit.framework.Test;
 
+import org.apache.commons.beanutils.DynaBean;
 import org.dom4j.Document;
 import org.dom4j.Element;
 import org.dom4j.io.SAXReader;
@@ -49,7 +50,7 @@
     }
 
     /**
-     * Tests the backup of restore of a table with an identity column and a foreign key to
+     * Tests the backup and restore of a table with an identity column and a foreign key
to
      * it when identity override is turned on.
      */
     public void testIdentityOverrideOn() throws Exception
@@ -156,7 +157,7 @@
     }
 
     /**
-     * Tests the backup of restore of a table with an identity column and a foreign key to
+     * Tests the backup and restore of a table with an identity column and a foreign key
to
      * it when identity override is turned off.
      */
     public void testIdentityOverrideOff() throws Exception
@@ -263,5 +264,179 @@
         assertEquals(new Integer(1), beans.get(0), "fk");
         assertEquals(new Integer(2), beans.get(1), "pk");
         assertEquals(new Integer(3), beans.get(1), "fk");
+    }
+
+    /**
+     * Tests the backup and restore of a table with an identity column and a foreign key
to
+     * itself while identity override is off.
+     */
+    public void testSelfReferenceIdentityOverrideOff() throws Exception
+    {
+        final String modelXml = 
+            "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+
+            "<database name='roundtriptest'>\n"+
+            "  <table name='misc'>\n"+
+            "    <column name='pk' type='INTEGER' primaryKey='true' required='true' autoIncrement='true'/>\n"+
+            "    <column name='fk' type='INTEGER' required='false'/>\n"+
+            "    <foreign-key name='test' foreignTable='misc'>\n"+
+            "      <reference local='fk' foreign='pk'/>\n"+
+            "    </foreign-key>\n"+
+            "  </table>\n"+
+            "</database>";
+
+        createDatabase(modelXml);
+
+        getPlatform().setIdentityOverrideOn(false);
+
+        insertRow("misc", new Object[] { new Integer(1), null });
+        insertRow("misc", new Object[] { new Integer(2), new Integer(1) });
+        insertRow("misc", new Object[] { new Integer(3), new Integer(2) });
+        insertRow("misc", new Object[] { new Integer(4), new Integer(4) });
+
+        StringWriter   stringWriter = new StringWriter();
+        DatabaseDataIO dataIO       = new DatabaseDataIO();
+
+        dataIO.writeDataToXML(getPlatform(), stringWriter, "UTF-8");
+
+        String    dataAsXml = stringWriter.toString();
+        SAXReader reader    = new SAXReader();
+        Document  testDoc   = reader.read(new InputSource(new StringReader(dataAsXml)));
+
+        List miscRows = testDoc.selectNodes("//misc");
+
+        if (miscRows.size() > 0)
+        {
+            assertEquals(4, miscRows.size());
+            assertEquals("1", ((Element)miscRows.get(0)).attributeValue("pk"));
+            assertNull(((Element)miscRows.get(0)).attributeValue("fk"));
+            assertEquals("2", ((Element)miscRows.get(1)).attributeValue("pk"));
+            assertEquals("1", ((Element)miscRows.get(1)).attributeValue("fk"));
+            assertEquals("3", ((Element)miscRows.get(2)).attributeValue("pk"));
+            assertEquals("2", ((Element)miscRows.get(2)).attributeValue("fk"));
+            assertEquals("4", ((Element)miscRows.get(3)).attributeValue("pk"));
+            assertEquals("4", ((Element)miscRows.get(3)).attributeValue("fk"));
+        }
+        else
+        {
+            miscRows = testDoc.selectNodes("//MISC");
+
+            assertEquals(4, miscRows.size());
+            assertEquals("1", ((Element)miscRows.get(0)).attributeValue("PK"));
+            assertNull(((Element)miscRows.get(0)).attributeValue("FK"));
+            assertEquals("2", ((Element)miscRows.get(1)).attributeValue("PK"));
+            assertEquals("1", ((Element)miscRows.get(1)).attributeValue("FK"));
+            assertEquals("3", ((Element)miscRows.get(2)).attributeValue("PK"));
+            assertEquals("2", ((Element)miscRows.get(2)).attributeValue("FK"));
+            assertEquals("4", ((Element)miscRows.get(3)).attributeValue("PK"));
+            assertEquals("4", ((Element)miscRows.get(3)).attributeValue("FK"));
+        }
+
+        dropDatabase();
+        createDatabase(modelXml);
+
+        StringReader stringReader = new StringReader(dataAsXml);
+
+        dataIO.writeDataToDatabase(getPlatform(), new Reader[] { stringReader });
+
+        List beans = getRows("misc");
+
+        assertEquals(new Integer(1), beans.get(0), "pk");
+        assertNull(((DynaBean)beans.get(0)).get("fk"));
+        assertEquals(new Integer(2), beans.get(1), "pk");
+        assertEquals(new Integer(1), beans.get(1), "fk");
+        assertEquals(new Integer(3), beans.get(2), "pk");
+        assertEquals(new Integer(2), beans.get(2), "fk");
+        assertEquals(new Integer(4), beans.get(3), "pk");
+        assertEquals(new Integer(4), beans.get(3), "fk");
+    }
+
+    /**
+     * Tests the backup and restore of a table with an identity column and a foreign key
to
+     * itself while identity override is off.
+     */
+    public void testSelfReferenceIdentityOverrideOn() throws Exception
+    {
+        if (!getPlatformInfo().isIdentityOverrideAllowed())
+        {
+            // TODO: for testing these platforms, we need deleteRows
+            return;
+        }
+
+        final String modelXml = 
+            "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+
+            "<database name='roundtriptest'>\n"+
+            "  <table name='misc'>\n"+
+            "    <column name='pk' type='INTEGER' primaryKey='true' required='true' autoIncrement='true'/>\n"+
+            "    <column name='fk' type='INTEGER' required='false'/>\n"+
+            "    <foreign-key name='test' foreignTable='misc'>\n"+
+            "      <reference local='fk' foreign='pk'/>\n"+
+            "    </foreign-key>\n"+
+            "  </table>\n"+
+            "</database>";
+
+        createDatabase(modelXml);
+
+        getPlatform().setIdentityOverrideOn(true);
+
+        insertRow("misc", new Object[] { new Integer(10), null });
+        insertRow("misc", new Object[] { new Integer(11), new Integer(10) });
+        insertRow("misc", new Object[] { new Integer(12), new Integer(11) });
+        insertRow("misc", new Object[] { new Integer(13), new Integer(13) });
+
+        StringWriter   stringWriter = new StringWriter();
+        DatabaseDataIO dataIO       = new DatabaseDataIO();
+
+        dataIO.writeDataToXML(getPlatform(), stringWriter, "UTF-8");
+
+        String    dataAsXml = stringWriter.toString();
+        SAXReader reader    = new SAXReader();
+        Document  testDoc   = reader.read(new InputSource(new StringReader(dataAsXml)));
+
+        List miscRows = testDoc.selectNodes("//misc");
+
+        if (miscRows.size() > 0)
+        {
+            assertEquals(4, miscRows.size());
+            assertEquals("10", ((Element)miscRows.get(0)).attributeValue("pk"));
+            assertNull(((Element)miscRows.get(0)).attributeValue("fk"));
+            assertEquals("11", ((Element)miscRows.get(1)).attributeValue("pk"));
+            assertEquals("10", ((Element)miscRows.get(1)).attributeValue("fk"));
+            assertEquals("12", ((Element)miscRows.get(2)).attributeValue("pk"));
+            assertEquals("11", ((Element)miscRows.get(2)).attributeValue("fk"));
+            assertEquals("13", ((Element)miscRows.get(3)).attributeValue("pk"));
+            assertEquals("13", ((Element)miscRows.get(3)).attributeValue("fk"));
+        }
+        else
+        {
+            miscRows = testDoc.selectNodes("//MISC");
+
+            assertEquals(4, miscRows.size());
+            assertEquals("10", ((Element)miscRows.get(0)).attributeValue("PK"));
+            assertNull(((Element)miscRows.get(0)).attributeValue("FK"));
+            assertEquals("11", ((Element)miscRows.get(1)).attributeValue("PK"));
+            assertEquals("10", ((Element)miscRows.get(1)).attributeValue("FK"));
+            assertEquals("12", ((Element)miscRows.get(2)).attributeValue("PK"));
+            assertEquals("11", ((Element)miscRows.get(2)).attributeValue("FK"));
+            assertEquals("13", ((Element)miscRows.get(3)).attributeValue("PK"));
+            assertEquals("13", ((Element)miscRows.get(3)).attributeValue("FK"));
+        }
+
+        dropDatabase();
+        createDatabase(modelXml);
+
+        StringReader stringReader = new StringReader(dataAsXml);
+
+        dataIO.writeDataToDatabase(getPlatform(), new Reader[] { stringReader });
+
+        List beans = getRows("misc");
+
+        assertEquals(new Integer(10), beans.get(0), "pk");
+        assertNull(((DynaBean)beans.get(0)).get("fk"));
+        assertEquals(new Integer(11), beans.get(1), "pk");
+        assertEquals(new Integer(10), beans.get(1), "fk");
+        assertEquals(new Integer(12), beans.get(2), "pk");
+        assertEquals(new Integer(11), beans.get(2), "fk");
+        assertEquals(new Integer(13), beans.get(3), "pk");
+        assertEquals(new Integer(13), beans.get(3), "fk");
     }
 }



Mime
View raw message