Fix known limitations of embeddable types

[lukaseder]

This is a continuation of #8353, #10527, and #11975, which fixed a lot of issues already in jOOQ 3.14 / 3.15 / 3.19

Runtime library (known issues)

• [ ] Support for UPDATE .. SET emb1 = (SELECT emb2 ..).
  • [ ] Emulation using SET (a, b) = (SELECT x, y), see #10522 and #10523
• [ ] INSERT .. VALUES ((SELECT emb)) doesn't work, if embeddable has more than one column.
• [ ] ORDER BY issues
  • [ ] ORDER BY .. NULLS FIRST / LAST emulations
  • [ ] SEEK
• [ ] There needs to be a way to create Field<? extends EmbeddableRecord> that is not a Val, but a RowField (let's wait for use-cases)
• [ ] Record::fromArray flattens embeddables, even if there are embeddable fields in the record. There are probably other unmappers that also don't work.
• [ ] Aliasing produces numerous edge cases where a single column is aliased, but in generated SQL there are multiple columns.
  • [ ] Aliasing embeddables in SELECT embeddable AS e
  • [ ] Aliasing embeddables through derived column lists FROM (SELECT embeddable FROM t) AS t (a, b, c)
  • [ ] Apply this optimisation once aliasing has been settled: #10540
• [ ] Record.from() and Record.into() don't work when fields are renamed, see https://github.com/jOOQ/jOOQ/issues/11178
• [x] Projecting TableAsField or RowAsField containing embeddeables: https://github.com/jOOQ/jOOQ/issues/15991
• [ ] Projecting an embeddable domain from a path: https://github.com/jOOQ/jOOQ/issues/16036
• [x] Returning embeddables containing computed/defaulted values from UpdatableRecord: https://github.com/jOOQ/jOOQ/issues/16114
• [ ] Using embeddables sharing the same name in RETURNING: https://github.com/jOOQ/jOOQ/issues/16187
• [x] Using embeddables in predicates within MULTISET projections: https://github.com/jOOQ/jOOQ/issues/16310
• [x] Using embeddables with SelectIsNull: https://github.com/jOOQ/jOOQ/issues/16319
• [x] Projecting them in DSLContext::fetchCount: https://github.com/jOOQ/jOOQ/issues/16326

Runtime library (not integration tested yet)

• [ ] Overlapping embeddables
  • [ ] In INSERT .. SELECT
  • [ ] In MERGE
  • [ ] In ALTER TABLE
• [ ] <replaceFields/> feature:
  • [ ] Mixing embeddables that replace fields with embeddables that don't replace fields
  • [ ] Let them overlap in addition to them co-existing
  • [ ] UpdatableRecord.key() calls (see also code generation issue, below)
  • [ ] Table.getPrimaryKey() and similar methods
• [ ] Use embeddable types in expressions
  • [ ] Functions like COALESCE(), NVL(), etc.
  • [ ] JSON functions
  • [ ] XML functions
• [ ] Working with readonly types contained in embeddables: https://github.com/jOOQ/jOOQ/issues/9864
• [ ] Table projections containing embeddables (see https://github.com/jOOQ/jOOQ/issues/4727)

Code generator (known issues)

• [ ] DAOs
  • [ ] Generate fetchOneByXYZ() methods in DAOs for <embeddablePrimaryKeys/> (is it possible?): https://github.com/jOOQ/jOOQ/issues/11188
  • [ ] Reference the <embeddablePrimaryKeys/> generated key record from generated DAOs as well
• [x] Generate interfaces if requested (currently, the IXYZ interface is declared on the EmbeddableRecord, but not generated)
  • [x] Immutable interfaces: https://github.com/jOOQ/jOOQ/issues/17178
  • [x] https://github.com/jOOQ/jOOQ/issues/14003
  • [x] https://github.com/jOOQ/jOOQ/issues/17179
  • [x] https://github.com/jOOQ/jOOQ/issues/17180
• [x] Generate POJOs if requested
  • [x] Immutable POJOs
• [ ] Overlapping embeddables should be able to nest, optionally. E.g. a UK on (A, B, C) and an FK on (A, B) should produce an embeddable type of the structural form ((A, B), C)
• [ ] Generated overriden UpdatableRecord.key() method does not produce correct type
• [ ] Embeddable domains with POJOs: https://github.com/jOOQ/jOOQ/issues/11716
• [ ] Embeddable types with converters: https://github.com/jOOQ/jOOQ/issues/11833
• [ ] https://github.com/jOOQ/jOOQ/issues/13059
• [ ] https://github.com/jOOQ/jOOQ/issues/16379

Code generator (not integration tested yet)

• [ ] Embeddables containing actual UDTRecord types
• [ ] UDTRecord types containing embeddables (probably won't be supported)
• [ ] Embeddables containing array types
• [ ] Embeddable domains overlapping with embeddable PKs / UKs / FKs
• [ ] Embeddable domains overlapping with custom embeddables

[alf]

Is it possible to find the parent table of an embedded primary key given just the embedded record?

[lukaseder]

@alf Embedded keys are just a code generation convenience feature, where the code generator makes sure that primary/unique constraints will generate the same embeddable type as the referencing foreign keys. There's no runtime information generated about the types being derived from keys.

You can, of course, query the generated code using Java reflection to find out who references an embedded type, and then implement heuristics, knowing your own code generation configuration.

I guess it could make sense, in some cases, to maintain meta information about this relationship. If you're interested in that, could you please create a new feature request with details about your use-case?

[alf]

Created feature request: https://github.com/jOOQ/jOOQ/issues/16803