Jooq: Add code generator option to generate an embeddable record per primary / unique key

[lukaseder]

Most databases use the same surrogate key type in all tables: BIGINT.

It's now perfectly possible to make meaningless comparisons like:

SELECT *
FROM book
JOIN author WHERE book.id = author.id

Clearly, the two id columns are not really comparable, because matches are purely accidental. Much better:

SELECT *
FROM book
JOIN author WHERE book.author_id = author.id

The foreign key on book.author_id enforces semantic equivalence. Likewise, we could compare book.author_id = brochure.author_id because both foreign keys point to the same primary key.

It would be interesting to generate a AuthorKey type that wraps the entire primary key (composite or non-composite) hide the implementation details (BIGINT) from the user. This would lead to much more type safe queries.

Features

• [x] This feature can be opted into using a new flag <embeddablePrimaryKeys/> and <embeddableUniqueKeys/> (was: <primaryKeyTypes/>)
  
  
  
  • [x] The flag turns on the feature, but some additional regular expressions should allow for specifying to which constraints this is applied (fully qualified constraint names contain the table name)
  
  
  • [x] ~It should be possible to specify which of the matches will replace the fields: #10481 - Another way to opt out of some possible limitations in this feature, without removing the embedded keys entirely~ (we might do that later, if requested / required)
  
  
• [x] The generated embeddable type corresponds to the PK / UK name. This includes all FKs referencing the PK / UK
• [x] The type is boxed / unboxed as a nested record (a nested POJO would be possible, but nested records are more in the spirit of SQL, see also #2360)

Caveats / details

• [x] The rules from #10481 <replacesFields/> apply, including e.g. (will be implemented through #10481):
  
  
  
  • [x] Embeddable types are applied for transitive references, too, e.g. when a FK and PK coincide
  
  
  • [x] FKs referencing multiple PKs / UKs cannot be mapped, unless the UKs all reference the same embeddable type, themselves (e.g. through transitive referencing)
  
  
• [x] ~Explicit embeddable configurations override this feature~. There's no need to override this feature, since we can have overlapping embeddables.
• [x] The naming of this type must be governed by GeneratorStrategy, like that of any EmbeddableDefinition
• [x] If <jpaAnnotations/> are generated, then @Embedded, @Embeddable annotations should be generated as well. Will be implemented separately: #6518.
• [x] Support this also in the scala / kotlin generator
• [x] Embeddables can overlap (e.g. overlapping UKs). We must be able to handle this in all statements. See #10499

Known issues

• [x] Reference the generated key record from generated DAOs (postponed to #10527)

Previous, rejected ideas

• [x] ~Types are generated as nested classes inside of tables (this can be overridden through: #6948)~
• [x] ~The Keys.java file should have generated convenience constructors~
• [x] ~The type is boxed / unboxed using a Converter (for now. Composite keys will work differently, see #6946).~ The type is a nested record, so no converter is involved. Custom converters on individual columns of composite and single-column keys can still apply
• [x] ~A <forcedType/> option that allows for applying the primary key type and its converters also to views, table-valued functions, etc.~ This wouldn't work for multi-column embeddables, and would be otherwise quite a confusing mix of features. Forced types are applied to physical columns before embeddables are applied to "logical columns".
• [x] ~While the EmbeddableDefinition is named after the PK/UK/FK, the DefaultGeneratorStrategy should perform the usual checks and produce the referenced table name as a field name (similar to implicit joins)~. This sounds great for FKs at first, but the PK/UK still needs to be named after itself, which means the benefit is less optimal. We'll wait for user-feedback for alternative naming strategies of embedded keys.

The implementation is not independent of #6946. For forwards compatibility, composite types have to be supported from the beginning, and they can only be supported as nested records, which means that this feature depends on:

• https://github.com/jOOQ/jOOQ/issues/2360 (Add support for nested records, such as Record2)
• https://github.com/jOOQ/jOOQ/issues/2530 (Add support for "fetch groups" or "embedded record types", where a group of fields is defined as a reusable RowN type across various tables)

---

See also:
https://groups.google.com/forum/#!topic/jooq-user/53RZqoewa3g

[etiennestuder]

That would be a very useful addition, @lukaseder. If you are going to implement this, it would be great to keep custom id types in mind. For example, in our apps, we have a consistent pattern in place how we name and structure these id types.

[lukaseder]

@etiennestuder Yes of course. This is implied by the bullets:

• The naming of this type must be governed by GeneratorStrategy (allowing for referencing pre-existing types)
• The type is boxed / unboxed as a nested record (allowing for mapping / unmapping being done via the existing record (un)mapper mechanism)

The details need to be worked out, of course.

[srollinet]

So if I understand it right, the generator will be able to generate JPA annotated POJOs from the DB like

@Entity
public class Project {
    @EmbeddedId ProjectId id;
}
@Embeddable
Class ProjectId {
    int departmentId;
    long projectId;
}

This is a great news, thanks to make it happen!

[lukaseder]

@srollinet Yes, that is the #6518 subtask

[lukaseder]

A workaround in PostgreSQL would be to use more strongly typed keys directly in the database. This would then be transparent for jOOQ and jOOQ's code generator:

create type pk_a as (id bigint);
create type pk_b as (id bigint);

create table t_a(id pk_a primary key);
create table t_b(id pk_b primary key, a pk_a references t_a);

insert into t_a values(row(1)::pk_a);
insert into t_b values(row(2)::pk_b, row(1)::pk_a);

[etiennestuder]

Interesting. As usual, your sql knowledge is way beyond what I have in my bag.

[lukaseder]

@etiennestuder I just put this here as a reminder to my future self, to experiment with this a bit more. I'm sure it has tons of caveats, otherwise, more people would probably use this feature for that purpose. :-)

[etiennestuder]

Thanks @lukaseder. Looking forward to what your future self will cook up.

[lukaseder]

Short feedback - I'm currently implementing #2530, which is a prerequisite for this. It will be a huge change, but I am quite positive already that this will be feasible.

[etiennestuder]

Thanks for the heads-up. Sounds promising, @lukaseder.

[pedro-borges]

This is very promising work Lucas, will make code safer and more readable, not to mention plain shorter. However, are you considering dealing with polymorphism?
For example when tables A, B, ... have specially generated ID keys, and table X has a generic REFERENCE_ID and DISCRIMINATOR columns?
So that we can somehow use X.REFERENCE_ID.eq(A.ID).where(X.DISCRIMINATOR.eq(“A”) ?
Or do you suggest such cases should be dealt with with explicit converters in code?

[lukaseder]

@pedro-borges I'm assuming you do have several optional/nullable foreign key constraints on X.REFERENCE_ID for each case, which would be a prerequisite. Even then, the proper type of the column would be A|B|...|Z, i.e. an untagged union, which Java does not support yet, and I don't think we'll get that any time soon.

Alternatives include:

• Marking the column type as Field<?>, which would be correct, but perhaps not very useful in queries.
• Generating several alternatives of the column, one for each type, which would align well with the fact that foreign keys are allowed to overlap anyway

We'll definitely need to think about this case and a few other edge cases.

As a general side note: I recommend not using this practice in your schema design, as it has been observed many times to lead to bad performance. Which is a good reminder to investigate this more thoroughly and blog about it.

[lukaseder]

The embeddable type feature (#2530) proves to be a lot more difficult to implement than first expected. We'll postpone the feature, and features built on top of it, to 3.13.

[axelfontaine]

The property is present in 3.13.2, but I can't seem to get this to work at all (H2 with IDENTITY or UUID PKs). What are the preconditions to make this work today?

A few additional notes:

• this should also be possible for UUID primary keys
• in the Kotlin generator this could be elegantly implemented as type aliases

[lukaseder]

This feature is far from being implemented, see the bullet list in the issue description.

this should also be possible for UUID primary keys

Yes, UUID will not be a special case for this feature.

in the Kotlin generator this could be elegantly implemented as type aliases

An alias is the same thing as the type it describes. It provides no additional type safety, perhaps a bit of documentation. E.g. this compiles in kotlin:

typealias T = Field<String>

val t1: T = DSL.inline("abc")
val t2: Field<String> = t1;
val t3: T = t2;

But the point here is to produce an actual type, which is incompatible with the underlying database / JDBC type, so if you have a BIGINT BOOK.ID column, you will want this new BookId type to be incompatible with Long, otherwise it would be possible to compare it with completely unrelated ID columns.

[etiennestuder]

@etiennestuder Yes of course. This is implied by the bullets:

• The naming of this type must be governed by GeneratorStrategy (allowing for referencing pre-existing types)
• The type is boxed / unboxed as a nested record (allowing for mapping / unmapping being done via the existing record (un)mapper mechanism)

The details need to be worked out, of course.

@lukaseder Do you have a jOOQ example on generating such id custom types?

[lukaseder]

@etiennestuder A quick example is given here in the latest dev version of the manual:
https://www.jooq.org/doc/dev/manual/code-generation/codegen-embeddable-types/codegen-embedded-keys/

The manual is currently being re-built. The configuration requires a regex matching applicable constraint names (e.g. .* for all), not a boolean.

Or was your question specifically about the GeneratorStrategy for renaming the generated class names?

[lukaseder]

Ultimately, this is "just" a convenient feature that auto-generates embeddable type configurations for all primary keys (and their referenced foreign keys), handling all the gazillion edge cases that appear when replacing columns by embeddables, and when having overlapping embeddables:
https://www.jooq.org/doc/dev/manual/code-generation/codegen-embeddable-types/codegen-embeddable-types-configuration/

[etiennestuder]

@lukaseder I've been playing around with it this morning but fail at the following:

Using jOOQ 3.14.1 OSS edition, I defined the following embeddable on existing database and table:

embeddables {
  embeddable {
    schema = 'public'
    name = 'IMP_IDS'
    tables = 'impression'
    fields {
      field {
        expression = 'imp_id'
      }
      field {
        expression = 'imp_public_id'
      }
    }
  }
}

I then do a jOOQ-based db query like this:

  .select(
    IMPRESSION.IMP_IDS, // the embeddable type
    IMPRESSION.IMP_NAME
)

If I then want to extract the values from the returned record, I get null values if I get them like this:

  r.getValue(IMPRESSION.IMP_IDS).getImpId(),
  r.getValue(IMPRESSION.IMP_IDS).getImpPublicId()

I get the correct values if I extract the values like this:

  r.getValue(0, ImpIdsRecord.class).getImpId(),
  r.getValue(0, ImpIdsRecord.class).getImpPublicId()

There is no sample for a select of an embeddable type in the docs here, but I applied my best to get it right. Does the described behavior of jOOQ ring a bell?

[lukaseder]

Thanks, @etiennestuder. Your code looks right, this is a bug. The behaviour should be the same as if you run this (activating field replacement from the commercial distribution):

ImpressionRecord r = db.selectFrom(IMPRESSION).fetchAny();
r.getImpIds().getImpId();
r.getImpIds().getImpPublicId();

I'll look into this separately: https://github.com/jOOQ/jOOQ/issues/10828. It's probably not related to embeddable records per primary keys (this issue), because that feature is from the commercial distribution, and embeddable primary key records auto-enable the field replacement flag, which I believe should work here.

[etiennestuder]

@lukaseder Thanks for the insights, @lukaseder. I'm happy to confirm the fix of #10828 once it is released.