jOOQ - Does jOOQ support definition files or creation of SQL creation scripts?

Question

In our project, concepts are definied in a configuration file. To give an example:

<concepts>
    <concept name="person">
        <property name="age" type="integer"/>
        ...
    </concept>
    ...
</concepts>

Although this has not much to do with SQL, this configuration file happens to be mappable to SQL tables, columns, ...

Starting from this configuration file, I need to be able to do 2 things:

• generation SQL creation scripts (CREATE TABLE person ( ... )).
• generation of jOOQ POJOs, tables, ... which can be used by the developers

I would like to start using jOOQ in this project. Does jOOQ support any kind of generation (both SQL creation scripts and its POJOs, tables, ...) which does not start from an existing database? I looked through the documentation, but could not find much.

If not, I am contemplating between two options:

1. generation of jOOQ POJOs, tables, ... based on the configuration file (custom development)
2. generation of SQL creation scripts based on jOOQ POJOs, tables, .... as generated in step 1 (custom development)

or

1. generation of SQL creation scripts based on the configuration file (custom development)
2. execute these scripts in an embeddable database, such as H2 or SQLite (pretty straight-forward) (although the scripts will also be executed in the 'real' database, it's probably best to use an in-memory database here, to avoid any dependencies)
3. generation of jOOQ POJOs, tables, ... based on this database (provided by jOOQ library)

Although I believe fhe first option requires more effort, it has currently my favor, since step 3 in the second option might introduce loss of information.

Answer 1

This should obviously be solved with XSLT

Generate the SQL script:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:template match="/">
        <result>
            <xsl:apply-templates select="concepts/concept"/>
        </result>
    </xsl:template>

    <xsl:template match="concept">
        <xsl:text>CREATE TABLE </xsl:text>
        <xsl:value-of select="@name"/>
        <xsl:text>(</xsl:text>
        <xsl:apply-templates select="property"/>
        <xsl:text>
);
</xsl:text>
    </xsl:template>

    <xsl:template match="property">
        <xsl:choose>
            <xsl:when test="position() > 1">
                <xsl:text>
, </xsl:text>
            </xsl:when>
            <xsl:otherwise>
                <xsl:text>
  </xsl:text>
            </xsl:otherwise>
        </xsl:choose>
        <xsl:value-of select="@name"/>
        <xsl:text> </xsl:text>
        <xsl:value-of select="@type"/>
    </xsl:template>
</xsl:stylesheet>

Generate the jOOQ meta XML

jOOQ-meta supports importing schema meta information from XML using the XMLDatabase

<configuration>
    <generator>
        <database>
            <name>org.jooq.util.xml.XMLDatabase</name>
            <properties>
                <property>
                    <key>dialect</key>
                    <value>ORACLE</value>
                </property>
                <property>
                    <key>xml-file</key>
                    <value>src/main/resources/concepts-transformed.xml</value>
                </property>
            </properties>

Just transform your XML file into the following format: http://www.jooq.org/xsd/jooq-meta-3.5.4.xsd

... e.g. using the following XSLT:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:variable name="schema" select="'MY_SCHEMA'"/>

    <xsl:template match="/">
        <information_schema xmlns="http://www.jooq.org/xsd/jooq-meta-3.5.4.xsd">
            <schemata>
                <schema>
                    <schema_name><xsl:value-of select="$schema"/></schema_name>
                </schema>
            </schemata>
            <tables>
                <xsl:apply-templates select="concepts/concept"/>
            </tables>
            <columns>
                <xsl:apply-templates select="concepts/concept/property"/>
            </columns>
        </information_schema>
    </xsl:template>

    <xsl:template match="concept">
        <table>
            <schema_name><xsl:value-of select="$schema"/></schema_name>
            <table_name><xsl:value-of select="@name"/></table_name>
        </table>
    </xsl:template>

    <xsl:template match="property">
        <column>
            <schema_name><xsl:value-of select="$schema"/></schema_name>
            <table_name><xsl:value-of select="../@name"/></table_name>
            <column_name><xsl:value-of select="@name"/></column_name>
            <data_type><xsl:value-of select="@type"/></data_type>
        </column>
    </xsl:template>
</xsl:stylesheet>