Author: tomdz
Date: Thu Jan 25 23:30:40 2007
New Revision: 500166
URL: http://svn.apache.org/viewvc?view=rev&rev=500166
Log:
Changedthe write-to-XML algorithm slightly to produce better organized XML
Modified:
db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DatabaseDataIO.java
db/ddlutils/trunk/src/test/org/apache/ddlutils/io/RoundtripTestBase.java
db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DatabaseDataIO.java
URL:
http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DatabaseDataIO.java?view=diff&rev=500166&r1=500165&r2=500166
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DatabaseDataIO.java
(original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DatabaseDataIO.java Thu
Jan 25 23:30:40 2007
@@ -28,8 +28,12 @@
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
+import java.util.HashSet;
import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.ddlutils.DdlUtilsException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.model.Column;
@@ -365,89 +369,173 @@
*/
public void writeDataToXML(Platform platform, Database model, DataWriter
writer)
{
- Table[] tables = new Table[1];
-
registerConverters(writer.getConverterConfiguration());
- // TODO: An advanced algorithm could be employed here that writes
objects
- // related by foreign keys, in the correct order
- StringBuffer query = new StringBuffer();
+ // TODO: An advanced algorithm could be employed here that writes
individual
+ // objects related by foreign keys, in the correct order
+ List tables = sortTables(model.getTables());
writer.writeDocumentStart();
- for (int tableIdx = 0; tableIdx < model.getTableCount(); tableIdx++)
+ for (Iterator it = tables.iterator(); it.hasNext();)
{
- tables[0] = (Table)model.getTable(tableIdx);
- query.setLength(0);
- query.append("SELECT ");
+ writeDataForTableToXML(platform, model, (Table)it.next(), writer);
+ }
+ writer.writeDocumentEnd();
+ }
- Connection connection = null;
- String schema = null;
+ /**
+ * Sorts the given table according to their foreign key order.
+ *
+ * @param tables The tables
+ * @return The sorted tables
+ */
+ private List sortTables(Table[] tables)
+ {
+ ArrayList result = new ArrayList();
+ HashSet processed = new HashSet();
+ ListOrderedMap pending = new ListOrderedMap();
- if (_determineSchema)
+ for (int idx = 0; idx < tables.length; idx++)
+ {
+ Table table = tables[idx];
+
+ if (table.getForeignKeyCount() == 0)
{
- try
- {
- // TODO: Remove this once we have full support for schemas
- connection = platform.borrowConnection();
- schema =
platform.getModelReader().determineSchemaOf(connection, _schemaPattern,
tables[0]);
- }
- catch (SQLException ex)
- {
- // ignored
- }
- finally
+ result.add(table);
+ processed.add(table);
+ }
+ else
+ {
+ HashSet waitedFor = new HashSet();
+
+ for (int fkIdx = 0; fkIdx < table.getForeignKeyCount();
fkIdx++)
{
- if (connection != null)
+ Table waitedForTable =
table.getForeignKey(fkIdx).getForeignTable();
+
+ if (!table.equals(waitedForTable))
{
- try
- {
- connection.close();
- }
- catch (SQLException ex)
- {
- // ignored
- }
+ waitedFor.add(waitedForTable);
}
}
+ pending.put(table, waitedFor);
}
+ }
- Column[] columns = tables[0].getColumns();
+ HashSet newProcessed = new HashSet();
- for (int columnIdx = 0; columnIdx < columns.length; columnIdx++)
+ while (!processed.isEmpty() && !pending.isEmpty())
+ {
+ newProcessed.clear();
+ for (Iterator it = pending.entrySet().iterator(); it.hasNext();)
{
- if (columnIdx > 0)
- {
- query.append(",");
- }
- if (platform.isDelimitedIdentifierModeOn())
+ Map.Entry entry = (Map.Entry)it.next();
+ Table table = (Table)entry.getKey();
+ HashSet waitedFor = (HashSet)entry.getValue();
+
+ waitedFor.removeAll(processed);
+ if (waitedFor.isEmpty())
{
-
query.append(platform.getPlatformInfo().getDelimiterToken());
+ it.remove();
+ result.add(table);
+ newProcessed.add(table);
}
- query.append(columns[columnIdx].getName());
- if (platform.isDelimitedIdentifierModeOn())
+ }
+ processed.clear();
+
+ HashSet tmp = processed;
+
+ processed = newProcessed;
+ newProcessed = tmp;
+ }
+ // the remaining are within circular dependencies
+ for (Iterator it = pending.keySet().iterator(); it.hasNext();)
+ {
+ result.add(it.next());
+ }
+ return result;
+ }
+
+ /**
+ * Writes the data contained in a single table to XML.
+ *
+ * @param platform The platform
+ * @param model The database model
+ * @param writer The data writer
+ * @param tableIdx
+ */
+ private void writeDataForTableToXML(Platform platform, Database model,
Table table, DataWriter writer)
+ {
+ Table[] tables = { table };
+ StringBuffer query = new StringBuffer();
+
+ query.append("SELECT ");
+
+ Connection connection = null;
+ String schema = null;
+
+ if (_determineSchema)
+ {
+ try
+ {
+ // TODO: Remove this once we have full support for schemas
+ connection = platform.borrowConnection();
+ schema =
platform.getModelReader().determineSchemaOf(connection, _schemaPattern,
tables[0]);
+ }
+ catch (SQLException ex)
+ {
+ // ignored
+ }
+ finally
+ {
+ if (connection != null)
{
-
query.append(platform.getPlatformInfo().getDelimiterToken());
+ try
+ {
+ connection.close();
+ }
+ catch (SQLException ex)
+ {
+ // ignored
+ }
}
}
- query.append(" FROM ");
- if (platform.isDelimitedIdentifierModeOn())
+ }
+
+ Column[] columns = tables[0].getColumns();
+
+ for (int columnIdx = 0; columnIdx < columns.length; columnIdx++)
+ {
+ if (columnIdx > 0)
{
- query.append(platform.getPlatformInfo().getDelimiterToken());
+ query.append(",");
}
- if (schema != null)
+ if (platform.isDelimitedIdentifierModeOn())
{
- query.append(schema);
- query.append(".");
+ query.append(platform.getPlatformInfo().getDelimiterToken());
}
- query.append(tables[0].getName());
+ query.append(columns[columnIdx].getName());
if (platform.isDelimitedIdentifierModeOn())
{
query.append(platform.getPlatformInfo().getDelimiterToken());
}
-
- writer.write(platform.query(model, query.toString(), tables));
}
- writer.writeDocumentEnd();
+ query.append(" FROM ");
+ if (platform.isDelimitedIdentifierModeOn())
+ {
+ query.append(platform.getPlatformInfo().getDelimiterToken());
+ }
+ if (schema != null)
+ {
+ query.append(schema);
+ query.append(".");
+ }
+ query.append(tables[0].getName());
+ if (platform.isDelimitedIdentifierModeOn())
+ {
+ query.append(platform.getPlatformInfo().getDelimiterToken());
+ }
+
+ writer.write(platform.query(model, query.toString(), tables));
}
/**
Modified:
db/ddlutils/trunk/src/test/org/apache/ddlutils/io/RoundtripTestBase.java
URL:
http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/io/RoundtripTestBase.java?view=diff&rev=500166&r1=500165&r2=500166
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/io/RoundtripTestBase.java
(original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/io/RoundtripTestBase.java
Thu Jan 25 23:30:40 2007
@@ -301,7 +301,14 @@
value = new BinaryObjectsHelper().deserialize((byte[])value);
}
}
- assertEquals(expected, value);
+ if (expected == null)
+ {
+ assertNull(value);
+ }
+ else
+ {
+ assertEquals(expected, value);
+ }
}
/**
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=500166&r1=500165&r2=500166
==============================================================================
--- db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java (original)
+++ db/ddlutils/trunk/src/test/org/apache/ddlutils/io/TestMisc.java Thu Jan 25
23:30:40 2007
@@ -458,14 +458,15 @@
assertEquals(12, beans.size());
// this is the order of actual insertion (the fk order)
+ // expected insertion order is: 1, 4, 7, 10, 8, 2, 3, 5, 6, 9, 11, 12
assertEquals(new Integer(1), beans.get(0), "id");
assertNull(((DynaBean)beans.get(0)).get("parent_id"));
assertEquals(new Integer(4), beans.get(1), "id");
assertEquals(new Integer(1), beans.get(1), "parent_id");
- assertEquals(new Integer(10), beans.get(2), "id");
- assertEquals(new Integer(4), beans.get(2), "parent_id");
- assertEquals(new Integer(7), beans.get(3), "id");
- assertEquals(new Integer(1), beans.get(3), "parent_id");
+ assertEquals(new Integer(7), beans.get(2), "id");
+ assertEquals(new Integer(1), beans.get(2), "parent_id");
+ assertEquals(new Integer(10), beans.get(3), "id");
+ assertEquals(new Integer(4), beans.get(3), "parent_id");
assertEquals(new Integer(8), beans.get(4), "id");
assertEquals(new Integer(7), beans.get(4), "parent_id");
assertEquals(new Integer(2), beans.get(5), "id");
@@ -483,4 +484,154 @@
assertEquals(new Integer(12), beans.get(11), "id");
assertEquals(new Integer(11), beans.get(11), "parent_id");
}
+
+ /**
+ * Tests the backup and restore of several tables with complex
relationships with an identity column and a foreign key to
+ * itself while identity override is off.
+ */
+ public void testComplexTableModel() throws Exception
+ {
+ // A: self-reference (A1->A2)
+ // B: self- and foreign-reference (B1->B2|G1, B2->G2)
+ // C: circular reference involving more than one table (C1->D1,C2->D2)
+ // D: foreign-reference to F (D1->F1,D2)
+ // E: isolated table (E1)
+ // F: foreign-reference to C (F1->C2)
+ // G: no references (G1, G2)
+
+ final String modelXml =
+ "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+
+ "<database name='roundtriptest'>\n"+
+ " <table name='A'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " <column name='fk' type='INTEGER' required='false'/>\n"+
+ " <foreign-key name='AtoA' foreignTable='A'>\n"+
+ " <reference local='fk' foreign='pk'/>\n"+
+ " </foreign-key>\n"+
+ " </table>\n"+
+ " <table name='B'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " <column name='fk1' type='INTEGER' required='false'/>\n"+
+ " <column name='fk2' type='INTEGER' required='false'/>\n"+
+ " <foreign-key name='BtoB' foreignTable='B'>\n"+
+ " <reference local='fk1' foreign='pk'/>\n"+
+ " </foreign-key>\n"+
+ " <foreign-key name='BtoG' foreignTable='G'>\n"+
+ " <reference local='fk2' foreign='pk'/>\n"+
+ " </foreign-key>\n"+
+ " </table>\n"+
+ " <table name='C'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " <column name='fk' type='INTEGER' required='false'/>\n"+
+ " <foreign-key name='CtoD' foreignTable='D'>\n"+
+ " <reference local='fk' foreign='pk'/>\n"+
+ " </foreign-key>\n"+
+ " </table>\n"+
+ " <table name='D'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " <column name='fk' type='INTEGER' required='false'/>\n"+
+ " <foreign-key name='DtoF' foreignTable='F'>\n"+
+ " <reference local='fk' foreign='pk'/>\n"+
+ " </foreign-key>\n"+
+ " </table>\n"+
+ " <table name='E'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " </table>\n"+
+ " <table name='F'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " <column name='fk' type='INTEGER' required='false'/>\n"+
+ " <foreign-key name='FtoC' foreignTable='C'>\n"+
+ " <reference local='fk' foreign='pk'/>\n"+
+ " </foreign-key>\n"+
+ " </table>\n"+
+ " <table name='G'>\n"+
+ " <column name='pk' type='INTEGER' primaryKey='true'
required='true'/>\n"+
+ " </table>\n"+
+ "</database>";
+
+ createDatabase(modelXml);
+
+ getPlatform().setIdentityOverrideOn(false);
+
+ // this is the optimal insertion order
+ insertRow("E", new Object[] { new Integer(1) });
+ insertRow("G", new Object[] { new Integer(1) });
+ insertRow("G", new Object[] { new Integer(2) });
+ insertRow("A", new Object[] { new Integer(2), null });
+ insertRow("A", new Object[] { new Integer(1), new Integer(2) });
+ insertRow("B", new Object[] { new Integer(2), null, new
Integer(2) });
+ insertRow("B", new Object[] { new Integer(1), new Integer(2), new
Integer(1) });
+ insertRow("D", new Object[] { new Integer(2), null });
+ insertRow("C", new Object[] { new Integer(2), new Integer(2) });
+ insertRow("F", new Object[] { new Integer(1), new Integer(2) });
+ insertRow("D", new Object[] { new Integer(1), new Integer(1) });
+ insertRow("C", new Object[] { new Integer(1), new Integer(1) });
+
+ StringWriter stringWriter = new StringWriter();
+ DatabaseDataIO dataIO = new DatabaseDataIO();
+
+ dataIO.writeDataToXML(getPlatform(), stringWriter, "UTF-8");
+
+ String dataAsXml = stringWriter.toString();
+
+ // the somewhat optimized order that DdlUtils currently generates is:
+ // E1, G1, G2, A2, A1, B2, B1, C2, C1, D2, D1, F1
+ // note that the order per table is the insertion order above
+ SAXReader reader = new SAXReader();
+ Document testDoc = reader.read(new InputSource(new
StringReader(dataAsXml)));
+ boolean uppercase = false;
+ List rows = testDoc.selectNodes("/*/*");
+ String pkColumnName = "pk";
+
+ assertEquals(12, rows.size());
+ if (!"e".equals(((Element)rows.get(0)).getName()))
+ {
+ assertEquals("E", ((Element)rows.get(0)).getName());
+ uppercase = true;
+ }
+ if (!"pk".equals(((Element)rows.get(0)).attribute(0).getName()))
+ {
+ pkColumnName = pkColumnName.toUpperCase();
+ }
+ assertEquals("1", ((Element)rows.get(0)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "G" : "g", ((Element)rows.get(1)).getName());
+ assertEquals("1", ((Element)rows.get(1)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "G" : "g", ((Element)rows.get(2)).getName());
+ assertEquals("2", ((Element)rows.get(2)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "A" : "a", ((Element)rows.get(3)).getName());
+ assertEquals("2", ((Element)rows.get(3)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "A" : "a", ((Element)rows.get(4)).getName());
+ assertEquals("1", ((Element)rows.get(4)).attributeValue(pkColumnName));
+
+ assertEquals(uppercase ? "B" : "b", ((Element)rows.get(5)).getName());
+ assertEquals("2", ((Element)rows.get(5)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "B" : "b", ((Element)rows.get(6)).getName());
+ assertEquals("1", ((Element)rows.get(6)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "C" : "c", ((Element)rows.get(7)).getName());
+ assertEquals("2", ((Element)rows.get(7)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "C" : "c", ((Element)rows.get(8)).getName());
+ assertEquals("1", ((Element)rows.get(8)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "D" : "d", ((Element)rows.get(9)).getName());
+ assertEquals("2", ((Element)rows.get(9)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "D" : "d", ((Element)rows.get(10)).getName());
+ assertEquals("1",
((Element)rows.get(10)).attributeValue(pkColumnName));
+ assertEquals(uppercase ? "F" : "f", ((Element)rows.get(11)).getName());
+ assertEquals("1",
((Element)rows.get(11)).attributeValue(pkColumnName));
+
+ dropDatabase();
+ createDatabase(modelXml);
+
+ StringReader stringReader = new StringReader(dataAsXml);
+
+ dataIO.writeDataToDatabase(getPlatform(), new Reader[] { stringReader
});
+
+ assertEquals(2, getRows("A").size());
+ assertEquals(2, getRows("B").size());
+ assertEquals(2, getRows("C").size());
+ assertEquals(2, getRows("D").size());
+ assertEquals(1, getRows("E").size());
+ assertEquals(1, getRows("F").size());
+ assertEquals(2, getRows("G").size());
+ }
+
}
