jOOQ 3.13 has been released with CockroachDB support, much more API and tooling for DDL management, and SQL:2011 temporal table support
Starting with this release, we will further embrace our support for parsing,
translating, executing, and now also interpreting DDL statements. The driving
force is better code generation support, but in the future, also better
integration with database change management systems like Flyway or Liquibase.
We’ve added support for a very promising new dialect: CockroachDB.
We’re starting to support one of SQL:2011’s most interesting features: temporal
tables for system and application time versioning, and we’ve generally improved
the way we build and distribute jOOQ to our paying customers.
SQL Dialects
We’ve worked together with a team of great engineers at CockroachDB to speed up
our newly added, formal CockroachDB support in the jOOQ Professional and
Enterprise Editions. While many users have successfully used the POSTGRES
dialect with CockroachDB for simple integrations, this new dialect is now fully
integration tested and supported for future jOOQ and CockroachDB versions. The
integration fixed a variety of issues and minor differences that we have
encountered in the code generator and the runtime library.
We’ve added support for a variety of features now available in Firebird 3.0+,
MariaDB 10.3+, MySQL 8+, and PostgreSQL 10+.
DDL interpretation
Our biggest investment has been made in the area of DDL interpretation. Since
jOOQ 3.9, we can parse more and more SQL syntax. A logical next step is to allow
for interpreting parsed (or ordinary, DSL constructed) DDL statements in order
to maintain an incremental, in-memory meta model of a database. This has a
variety of benefits, which we will take advantage of in this release and in the
future.
The main out of the box benefits in this version include:
- Being able to create a diff between two versions of a schema, programmatically through Meta.migrateTo(Meta), on the command line through DiffCLI (https://www.jooq.org/doc/latest/manual/sql-building/schema-diff-cli), or online, through our website: https://www.jooq.org/diff.
- Such a diff or the meta model itself can be used for ad-hoc introspections, or formally, for automated migrations by tools built on top of jOOQ. Future versions will offer such migrations out of the box.
- Unfortunately, we have not yet replaced our H2 backed DDLDatabase by our new DDL interpretation capabilities. This is a high priority for jOOQ 3.14, though
In the context of these investments, we have improved a variety of
infrastructure that were needed for these features and that users can benefit as
well. These improvements include:
- A new LiquibaseDatabase, which uses Liquibase behind the scenes to simulate a schema migration in memory (similar to the DDLDatabase for Flyway), to reverse engineer that for the code generator. If you’re using Liquibase, chances are that you no longer need to connect to an actual database to use jOOQ’s code generator. More info here: https://blog.jooq.org/how-to-simulate-a-liquibase-migration-using-h2/
- We now support the inclusion of SQL text in generated code and other parts of the runtime meta model, including for CHECK constraints and views
- A lot of improvements to the JDBC DatabaseMetaData backed org.jooq.Meta API have been implemented.
- A lot of additional DDL statement and syntax support has been added throughout the API, parser, and other tooling.
- The runtime meta model has been enhanced with new information, such as sequence flags, check constraints, table type information, and more.
Temporal table support
Perhaps the SQL:2011 standard’s most exciting “new” feature are temporal tables,
an enhancement on top of Oracle’s historic flashback query feature, and much
more.
In many systems, single, or bi temporal versioning of data is essential. The two
dimensions of versioning that are now supported in jOOQ are:
- System versioning, useful for backups and auditing, where every mutating operation results in a backup copy of the data in a managed backup table.
- Application versioning, useful for data that has a timely limited validity, such as a price for a product, which could have a temporary discount.
jOOQ 3.13 supports org.jooq.Period and related API in Select, Update, and Delete
to manipulate and query temporal tables in dialects that ship with native
support for these features. To different degrees, these dialects include:
- DB2
- MariaDB
- Oracle
- SQL Server
Emulations of incompletely supported syntax in the above dialects, as well as in
all other dialects will be added in future jOOQ versions, making temporal
validity a problem of the past.
More details here:
https://www.jooq.org/doc/3.13/manual/sql-building/table-expressions/temporal-tables/
This is a commercially licensed only feature.
Build improvements
We’ve invested a lot of time in improving our build automation using newer
Jenkins features that allow us to publish builds more frequently, especially
snapshot builds of upcoming minor releases for early adopters, as well as patch
releases for customers needing urgent fixes:
https://www.jooq.org/download/versions
These new releases also include trial versions for Java 6, 8, 11, without the
need to register for extended trials.
In addition to the above, we’ve improved and continue to improve our
documentation of features per edition, in the future.
Procedural API improvement
In jOOQ 3.12, we’ve introduced support for procedural languages, which we are
improving in every new release. jOOQ 3.13 adds support for additional
procedural statements, including:
- EXECUTE for dynamic SQL execution embedded in procedural blocks
- RETURN to return from functions
- Plain SQL statement support
In the future, we’re planning to support also the generation of functions,
procedures, and triggers through standard jOOQ API.
More details here:
https://www.jooq.org/doc/3.13/manual/sql-building/procedural-statements/
This is a commercially licensed only feature.
jOOQ-Refaster
Some of our users have had a lot of success using our checker-framework or
ErrorProne integrations, which allow for some static API usage analysis in
client code (e.g. plain SQL / SQLi risk, dialect compatiblity, etc.)
Starting from jOOQ 3.13, we’re also adding support for ErrorProne’s sub project
“refaster”, which allows for automatically rewriting bad API usage to something
better, such as for example:
- Migrate deprecated API usage like field.trim(), to its replacement trim(field)
- Migrate inefficient SQL usage like ctx.fetchCount(select) != 0 to ctx.fetchExists(select)
The complete list of changes can be found on our website:
https://www.jooq.org/notes