Using jOOQ 3.14 Synthetic Foreign Keys to Write Implicit Joins on Views

Posted on by lukaseder

jOOQ has supported one of JPQL’s most cool features for a while now: implicit joins. Using jOOQ, you can navigate your to-one relationships in a type safe way, generating LEFT JOIN operations implicitly without the effort of having to keep thinking about join predicates, and the correct join order. Consider this Sakila database query here, if SQL supported implicit joins natively:

SELECT
  cu.first_name,
  cu.last_name
FROM customer AS cu
WHERE cu.address.city.country.country = 'Switzerland'

It translates to this query in native SQL:

SELECT
  cu.first_name,
  cu.last_name
FROM customer AS cu
JOIN address AS ad ON cu.address_id = ad.address_id
JOIN city AS ci ON a.city_id = ci.city_id
JOIN country AS co ON ci.country_id = co.country_id
WHERE co.country = 'Switzerland'

Note: Inner joins are supported starting from jOOQ 3.14, depending on whether the foreign key is mandatory / not null. The default behaviour is to produce LEFT JOIN which are the correct way to implicitly join optional foreign keys.

Implicit joins aren’t a silver bullet. Not every JOIN graph can be completely transformed into implicit join usage, and not every implicit join usage is more readable than native SQL JOIN graphs. But to have this option is great. Especially, when your keys are composite keys.

Classic joins on views

In classic relational design, surrogate keys are often avoided, and I think we should still avoid them in many cases. Even if you don’t agree, you may occasionally work on a schema where there are few to no surrogate keys. One such example is the standard SQL INFORMATION_SCHEMA which is implemented, for example, in H2, HSQLDB, MariaDB, MySQL, PostgreSQL, or SQL Server.

For example, when querying HSQLDB’s DOMAIN_CONSTRAINTS view to reverse engineer DOMAIN types. The jOOQ query for that used to be:

Domains d = DOMAINS.as("d");
DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");
CheckConstraints cc = CHECK_CONSTRAINTS.as("cc");

for (Record record : create()
    .select(
        d.DOMAIN_SCHEMA,
        d.DOMAIN_NAME,
        d.DATA_TYPE,
        d.CHARACTER_MAXIMUM_LENGTH,
        d.NUMERIC_PRECISION,
        d.NUMERIC_SCALE,
        d.DOMAIN_DEFAULT,
        cc.CHECK_CLAUSE)
    .from(d)
    .join(dc)
        .on(row(d.DOMAIN_CATALOG, d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
        .eq(dc.DOMAIN_CATALOG, dc.DOMAIN_SCHEMA, dc.DOMAIN_NAME))
    .join(cc)
        .on(row(dc.CONSTRAINT_CATALOG,
                dc.CONSTRAINT_SCHEMA,
                dc.CONSTRAINT_NAME)
        .eq(cc.CONSTRAINT_CATALOG,
                cc.CONSTRAINT_SCHEMA,
                cc.CONSTRAINT_NAME))
    .where(d.DOMAIN_SCHEMA.in(getInputSchemata()))
    .orderBy(d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
) { ... }

So, the query joined the many-to-many relationship between DOMAINS - DOMAIN_CONSTRAINTS - CHECK_CONSTRAINTS to get all the information required for generating domain types.

These views are not updatable, nor do they have any constraint information associated with them, but what if we were able to define synthetic constraints? jOOQ has supported synthetic primary keys to help make views updatable.

Synthetic foreign keys

Starting with jOOQ 3.14, we’ve reworked the way synthetic keys work, and the commercial editions will support synthetic foreign keys as well.

You can specify a configuration like this:

<configuration>
  <generator>
    <database>
      <syntheticObjects>
        <primaryKeys>
          <primaryKey>
            <tables>
              CHECK_CONSTRAINTS|CONSTRAINTS|TABLE_CONSTRAINTS
            </tables>
            <fields>
              <field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
            </fields>
          </primaryKey>
          <primaryKey>
            <tables>DOMAINS</tables>
            <fields>
              <field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
            </fields>
          </primaryKey>
        </primaryKeys>
        <foreignKeys>
          <foreignKey>
            <tables>DOMAIN_CONSTRAINTS</tables>
            <fields>
              <field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
            </fields>
            <referencedTable>CHECK_CONSTRAINTS</referencedTable>
          </foreignKey>
          <foreignKey>
            <tables>DOMAIN_CONSTRAINTS</tables>
            <fields>
              <field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
            </fields>
            <referencedTable>DOMAINS</referencedTable>
          </foreignKey>
        </foreignKeys>
      </syntheticObjects>
    </database>
  </generator>
</configuration>

And already jOOQ’s code generator will think that these views were tables that actually had constraints like the below:

ALTER TABLE CHECK_CONSTRAINTS
  ADD PRIMARY KEY (
    CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
  );

ALTER TABLE DOMAINS
  ADD PRIMARY KEY (
    DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
  );

ALTER TABLE DOMAIN_CONSTRAINTS
  ADD FOREIGN KEY (
    CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
  )
  REFERENCES CHECK_CONSTRAINTS;

ALTER TABLE DOMAIN_CONSTRAINTS
  ADD FOREIGN KEY (
    DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
  )
  REFERENCES DOMAINS;

More sophisticated configuration is possible, e.g. to assign names to constraints, to have composite constraints using field ordering differing from the ordering in the table, or foreign keys referencing unique keys rather than primary keys. For a full description of what’s available, please refer to the manual.

With the above synthetic meta data available to the code generator, all the numerous goodies are now available on views as well, including:

Let’s look at

Implicit joins

Implicit joins are now also possible on these views, meaning:

  1. You’ll never have to remember including all the key columns in join predicates anymore (bye bye accidental cartesian products)
  2. Your code will still be correct in case your composite key changes to something else!

So, this is more than just merely a convenience thing, it’s also a correctness thing. Our query from before can now be written like this, in a much more concise way:

DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");

for (Record record : create()
    .select(
        dc.domains().DOMAIN_SCHEMA,
        dc.domains().DOMAIN_NAME,
        dc.domains().DATA_TYPE,
        dc.domains().CHARACTER_MAXIMUM_LENGTH,
        dc.domains().NUMERIC_PRECISION,
        dc.domains().NUMERIC_SCALE,
        dc.domains().DOMAIN_DEFAULT,
        dc.checkConstraints().CHECK_CLAUSE)
    .from(dc)
    .where(dc.domains().DOMAIN_SCHEMA.in(getInputSchemata()))
    .orderBy(dc.domains().DOMAIN_SCHEMA, dc.domains().DOMAIN_NAME)
) { ... }

Notice how we’re using the relationship table as the only table to put in the FROM clause. This way, we can navigate in both directions of the to-one relationships from DOMAIN_CONSTRAINTS -> DOMAINS and DOMAIN_CONSTRAINTS -> CHECK_CONSTRAINTS. The resulting SQL query is equivalent to the previous one, but all the nastiness of joining by 3-column-composite-keys is gone. I personally find this much more readable.

Future work

So far, only to-one relationships can be navigated this way. JPQL also offers navigating to-many relationships with a few restrictions. This is a slippery slope. When offering to-many relationships, some use-cases are obvious, but the semantics of others is less so. For example, it is not a good idea to let the SELECT clause produce more (or less) rows depending on the presence of a projected column. This is why jOOQ so far produced only LEFT JOIN for implicit joins, because that would guarantee that an implicitly joined column does not reduce the number of rows because of an INNER JOIN not producing any matches.

Nevertheless, there is a lot that we can still add in #7536, including:

  • Implicit to-many joins in the FROM clause, where they don’t cause any trouble
  • Implicit joins in DML
  • Parser support to offer this functionality also in https://www.jooq.org/translate and to everyone working with jOOQ SQL through jOOQ’s ParsingConnection

And much more!

Published by lukaseder

I made jOOQ

Leave a Reply