You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by to...@apache.org on 2007/01/09 08:41:42 UTC
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
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");
}
}