db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@apache.org
Subject svn commit: r500166 - in /db/ddlutils/trunk/src: java/org/apache/ddlutils/io/DatabaseDataIO.java test/org/apache/ddlutils/io/RoundtripTestBase.java test/org/apache/ddlutils/io/TestMisc.java
Date Fri, 26 Jan 2007 07:30:41 GMT
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());
+    }
+
 }



Mime
View raw message