Stop Manually Importing Your ERD Export into jOOQ

ERD (Entity Relationship Diagrams) are a great way of designing and visualising your database model. There is a variety of vendors offering free and commercial ERD tools. Vertabelo by E-Point is a SaaS product where you can design and manage your database schema online. For instance, the jOOQ example database can be modelled as such:

jOOQ Sample Database
jOOQ Sample Database
.

The most important aspect of such an ERD tool, however, is its import / export functionality. Not only can an existing schema be reverse-engineered, but you can also export it in SQL or XML format. This is great news for early adopters of the upcoming jOOQ 3.5, which will finally support importing file-based schema definitions, e.g. using an XML representation of your INFORMATION_SCHEMA.

XSLT to the rescue

In “modern” times where people put JSON everywhere and XML has become evil, people might have forgotten the power of XML and its tool chain. In this case, it will be very trivial to transform the Vertabelo export format into the jOOQ import format:

Export

<Tables>
    <Table Id="t1">
        <Name>LANGUAGE</Name>
        <Description></Description>
        <Columns>
            <Column Id="c1">
                <Name>ID</Name>
                <Type>integer</Type>
                <Nullable>false</Nullable>
                <PK>true</PK>
            </Column>
            <!-- ... -->

A full export file can be seen here.

Import

<information_schema>
    <schemata>
        <schema>
            <schema_name>PUBLIC</schema_name>
        </schema>
    </schemata>
    <tables>
        <table>
            <table_schema>PUBLIC</table_schema>
            <table_name>LANGUAGE</table_name>
        </table>
        <!-- ... -->
    </tables>
    <columns>
        <column>
            <table_schema>PUBLIC</table_schema>
            <table_name>LANGUAGE</table_name>
            <column_name>ID</column_name>
            <data_type>integer</data_type>
            <ordinal_position>1</ordinal_position>
            <is_nullable>false</is_nullable>
        </column>
        <!-- ... -->
    </columns>
    <table_constraints>
        <table_constraint>
            <constraint_schema>PUBLIC</constraint_schema>
            <constraint_name>PK_LANGUAGE</constraint_name>
            <constraint_type>PRIMARY KEY</constraint_type>
            <table_schema>PUBLIC</table_schema>
            <table_name>LANGUAGE</table_name>
        </table_constraint>
        <!-- ... -->

While the Vertabelo export format organised hierarchically, the jOOQ import format is a flat XML representation of the relevant SQL standard INFORMATION SCHEMA tables, as implemented by a variety of databases such as H2, HSQLDB, MySQL, PostgreSQL, SQL Server. The transformation can be done easily with XSLT, looking something like this:

<xsl:template match="/">
    <xsl:key name="schema" 
        match="/DatabaseModel/Tables/Table/Properties/Property[Name = 'Schema']" use="." />

    <information_schema xmlns="http://www.jooq.org/xsd/jooq-meta-3.5.0.xsd">
        <schemata>
            <xsl:apply-templates 
                select="/DatabaseModel/Tables/Table/Properties/Property[Name = 'Schema'][generate-id() = generate-id(key('schema', .)[1])]" 
                mode="schema"/>
        </schemata>
        
        <tables>
            <xsl:apply-templates 
                select="/DatabaseModel/Tables/Table" 
                mode="table"/>
        </tables>
        
        <columns>
            <xsl:apply-templates 
                select="/DatabaseModel/Tables/Table/Columns/Column" 
                mode="column"/>
        </columns>

<!-- ... -->
<xsl:template match="Table" mode="table">
    <table>
        <table_schema>
            <xsl:value-of select="Properties/Property[Name = 'Schema']/Value"/>
        </table_schema>
        <table_name>
            <xsl:value-of select="Name"/>
        </table_name>
    </table>
</xsl:template>

<xsl:template match="Column" mode="column">
    <xsl:variable name="Id" select="@Id"/>
    
    <column>
        <table_schema>
            <xsl:value-of select="ancestor::Table/Properties/Property[Name = 'Schema']/Value"/>
        </table_schema>
        <table_name>
            <xsl:value-of select="ancestor::Table/Name"/>
        </table_name>
        <column_name>
            <xsl:value-of select="Name"/>
        </column_name>
        <data_type>
            <xsl:value-of select="Type"/>
        </data_type>
        <ordinal_position>
            <xsl:value-of select="1 + count(preceding-sibling::Column)"/>
        </ordinal_position>
        <is_nullable>
            <xsl:value-of select="Nullable"/>
        </is_nullable>
    </column>
</xsl:template>

The full XSL file can be seen here.

Configuring Maven

All we need now is to put the Vertabelo export file somewhere in src/main/resources, and transform it with the Codehaus xml-maven-plugin like so:

<!-- The XSLT plugin transforming XML files prior to code generation -->
<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>xml-maven-plugin</artifactId>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>transform</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <transformationSets>
            <transformationSet>
                <dir>src/main/resources</dir>
                <includes>
                    <include>vertabelo-export.xml</include>
                </includes>
                <stylesheet>src/main/resources/vertabelo-2-jooq.xsl</stylesheet>
            </transformationSet>
        </transformationSets>
    </configuration>
</plugin>

The output is then available to the jOOQ code generator in the target directory:

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>

    <executions>
        <execution>
            <id>generate-h2</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>
                <generator>
                    <name>org.jooq.util.DefaultGenerator</name>
                    <database>
                        <!-- We're using the new jOOQ 3.5 XMLDatabase here -->
                        <name>org.jooq.util.xml.XMLDatabase</name>
                        <properties>
                            <property>
                                <key>dialect</key>
                                <value>H2</value>
                            </property>
                            <property>
                                <key>xml-file</key>
                                <value>target/generated-resources/xml/xslt/vertabelo-export.xml</value>
                            </property>
                        </properties>
                        <inputSchema>PUBLIC</inputSchema>
                    </database>
                    <generate>
                        <deprecated>false</deprecated>
                        <instanceFields>true</instanceFields>
                    </generate>
                    <target>
                        <packageName>org.jooq.example.db.h2</packageName>
                        <directory>target/generated-sources/jooq-h2</directory>
                    </target>
                </generator>
            </configuration>
        </execution>
    </executions>
</plugin>

That’s it!

This obviously works with other ERD tools than Vertabelo as well. It should be just as easy to write your own XSL file for your own tool. All you need to do is generate valid XML according to the new http://www.jooq.org/xsd/jooq-meta-3.5.0.xsd schema.

See the pom.xml file here, for details, or download the full example project from GitHub.