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:
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>
<!-- ... -->
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="https://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>
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 https://www.jooq.org/xsd/jooq-meta-3.5.0.xsd schema.
Great idea! I would like to mention that you can use Vertabelo API to add some extra automation to the whole process. Using API you can build downloading Vertabelo XML file into build process.
Read more about API on Vertabelo blog – http://www.vertabelo.com/blog/vertabelo-news/vertabelo-api
That sounds awesome! Would be a reasonable extension for a follow-up post.
In fact, a follow-up post could be written easily if there were a publicly available sample database with publicly available API key. The point is that users would like to try out the complete feature set for free:
Do you have such a publicly available demo account?
I am wondering if the transformation cannot be done more easily in Java with jOOX or jsoup ?
I mean that if you often write XSL, it may be ok for you to write this. But it you don’t, the transformation in Java might be a lot easier.
Thank you for sharing this anyway !
Good point. I personally love XSL and I think it’s the best tool for this kind of job. The same transformation would take a lot more lines of code with jOOX / jsoup. But maybe, I’ll give it a shot in a follow-up article
We don’t have one demo account. But it is simple to achieve what you need with any Vertabelo account:
* Create an account
* Create a demo model
* Enable public link to demo model (it is an option on model details screen). It allows to share a model in read only mode to your audience. You can also embed Vertabelo diagram into your blog post – diagram will change automaticly during editing it. Details are on Vertabelo blog http://www.vertabelo.com/blog/notes-from-the-lab/how-to-embed-diagram-on-the-internet
* Enable access to Vertabelo API (http://www.vertabelo.com/blog/vertabelo-news/vertabelo-api) and share an API key with your readers.
Yes, I’ve seen the “public link” feature and that’s nice for embedding the diagram in blog posts (which I will certainly do in the future). But the API feature is not available in my free price plan:
Probably that’s OK, as this isn’t really a feature one would expect from a free price plan…
Yeah. I’ve forgotten to mention here that Vertabelo API is available only in paid account plans.
On Vertabelo blog we’ve published article about details of Vertabelo database model XML file format.
http://www.vertabelo.com/blog/documentation/vertabelo-xml