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.

Awesome SQL Trick: Constraints on Views

CHECK constraints are already pretty great when you want to sanitize your data. But there are some limitations to CHECK constraints, including the fact that they are applied to the table itself, when sometimes, you want to specify constraints that only apply in certain situations.

This can be done with the SQL standard WITH CHECK OPTION clause, which is implemented by at least Oracle and SQL Server. Here’s how to do that:

CREATE TABLE books (
  id    NUMBER(10)         NOT NULL,
  title VARCHAR2(100 CHAR) NOT NULL,
  price NUMBER(10, 2)      NOT NULL,
  
  CONSTRAINT pk_book PRIMARY KEY (id)
);
/

CREATE VIEW expensive_books
AS
SELECT id, title, price
FROM books
WHERE price > 100
WITH CHECK OPTION;
/

INSERT INTO books 
VALUES (1, '1984', 35.90);

INSERT INTO books 
VALUES (
  2, 
  'The Answer to Life, the Universe, and Everything',
  999.90
);

As you can see, expensive_books are all those books whose price is more than 100.00. This view will only report the second book:

SELECT * FROM expensive_books;

The above query yields:

ID TITLE                                       PRICE
-- ----------------------------------------- -------
 2 The Answer to Life, the Universe, and ...   999.9 

But now, that we have that CHECK OPTION, we can also prevent users from inserting “expensive books” that aren’t really expensive. For instance, let’s run this query:

INSERT INTO expensive_books 
VALUES (3, '10 Reasons why jOOQ is Awesome', 9.99);

This query won’t work now. We’re getting:

ORA-01402: view WITH CHECK OPTION where-clause violation

We also cannot update any of the “expensive books” to be non-expensive:

UPDATE expensive_books
SET price = 9.99;

This query results in the same ORA-01402 error message.

Inline WITH CHECK OPTION

In case you need to locally prevent bogus data from being inserted into a table, you can also use inline WITH CHECK OPTION clauses like so:

INSERT INTO (
  SELECT *
  FROM expensive_books
  WHERE price > 1000
  WITH CHECK OPTION
) really_expensive_books
VALUES (3, 'Modern Enterprise Software', 999.99);

And the above query again resutls in an ORA-01402 error.

Using SQL transformation to generate ad-hoc constraints

While CHECK OPTION is very useful for stored views, which can have proper grants for those users that may not access the underlying table directly, the inline CHECK OPTION is mainly useful when you transform dynamic SQL in an intermediate SQL transformation layer in your applciation.

This can be done with jOOQ’s SQL transformation capabilities, for instance, where you can watch out for a certain table in your SQL statements, and then centrally prevent bogus DML from being executed. This is a great way to implement multi-tenancy, if your database doesn’t natively support row-level security.

Stay tuned for a future blog post explaining how to transform your SQL with jOOQ to implement row-level security for any database.

jOOQ Newsletter: September 02, 2014 – Do You Really Need Support?

Do you really need support?

Our apologies. We hadn’t realised that we didn’t advertise the support-free jOOQ licenses, which we had been offering for quite a while now well enough on our website. So we have fixed that now.

We think that jOOQ is such a high quality, intuitive piece of software with a vibrant community that our customers might not even need us at Data Geekery to support them! That is why we have been offering support-less subscriptions where customers get to use the jOOQ Professional Edition or the jOOQ Enterprise Edition for 20% less than if they had our guaranteed reaction times.

All you need to do is enter the “NO SUPPORT” discount code with your next purchase, and start coding. More details here. Note that this will only remove our support guarantees, not the warranty. All upgrades and bugfixes are still included.

And while we’re at it, if you’re planning on purchasing 10 licenses or more, please contact us to learn about our high-volume tiered pricing model to further increase the value you’re getting out of jOOQ.

Data Geekery 1 Year Anniversary

Hooraay!

One year ago, on August 15 2013, Data Geekery GmbH was founded to provide commercial licensing and support for jOOQ. We’ve had exciting times behind us, and even more exciting times ahead of us. Here’s a quick wrap-up of what happend in the last year:

  • 2013-08-15: Data Geekery enters the Zurich trade register
  • 2013-10-09: jOOQ 3.2 is released under the new dual licensing strategy
  • 2013-10-29: jOOQ gets roughly 10% votes on this InfoQ poll
  • 2013-12-18: We’re having the 8th conference or JUG talk about jOOQ
  • 2014-12-31: Data Geekery is profitable. A Happy New Year, indeed!
  • 2014-01-01: Our monthly downloads have recovered from dual licensing
  • 2014-01-17: Our articles reach 1M reads on DZone
  • 2014-02-14: jOOQ 3.3 is released with Keyset pagination support
  • 2014-02-19: The 200th Stack Overflow question about jOOQ was asked
  • 2014-05-21: jOOQ is referenced from the RebelLabs reports
  • 2014-06-12: We’re having the 21st conference or JUG talk about jOOQ
  • 2014-06-20: jOOQ 3.4 is released with CTE, transactions, and DDL support
  • 2014-06-23: The 500th GitHub Star was added
  • 2014-07-01: Our monthly downloads have doubled compared to last year
  • 2014-08-08: The 400th blog post was published bringing the 650’000th hit

So, what’s next?

jOOQ is a big success story. Many minor frameworks by other “data geeks” copy jOOQ’s approach to writing internal domain-specific languages for a subset of SQL or of another query language. Examples are:

Being the industry’s leading type safe embedded SQL API, we’re going to continue pushing embedded SQL in Java, and SQL in general. Stay tuned for a very exciting second year of Data Geekery!

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world and we can hardly catch up with them! Here are:

Thanks for the shouts, guys! You make the jOOQ experience rock!

SQL Zone – The Dreaded COUNT(*) Function

COUNT(*) seems to be a practical way for many SQL developers to ensure that there is exactly one result record. No more, no less. But often, if you want exactly one record, you can achieve the same thing using a CASE expression along with anEXISTS predicate, which is likely to be much faster than the COUNT(*) alternative, because you probably don’t care about the exact number of records, only about the existence of such records.

Does that sound too abstract? Read this article here, and decide for yourself, if you find potential for optimisation in your code.

SQL Zone – Constraints on Views

If you’re using Oracle or SQL Server (or another standards-compliant database), you can put constraints (“CHECK OPTIONS”) on your database views. This can be extremely useful when you want to prevent users from inserting data into views that don’t match the view itself. Take this view for instance:

CREATE VIEW expensive_books
AS
SELECT id, title, price
FROM books
WHERE price > 100
WITH CHECK OPTION;

This view will not allow you to insert any books with a price lower than 100, because of the CHECK OPTION. An incredibly useful feature that will also be supported by the upcoming jOOQ 3.5.

Read this blog post for more information.

Upcoming Events

After a summer break, we’re back on the road!

Have you missed any of our previous jOOQ talks? Soon you’ll get another chance to hear us talk about jOOQ or SQL in general in any of these upcoming events:

Stay informed about 2014 events on www.jooq.org/news.