Dynamic schema using builders

[juriad]

Use case:

We have a dynamic schema (user configurable in JSON). Application creates all tables/constraints/indices on the first start.

CreateTableConstraintStep allows specifying constraints which can easily be built by DSL.constraint() because ConstraintFinalStep extends Constraint.
The same is not true for indices. There are two groups of methods in DSL: DSL.index(Name) which creates an Index, and DSL.createIndex(...) which creates a query (due to CreateIndexFinalStep extends DDLQuery). It seems like the only way to construct a proper Index for CreateTableIndexStep is Internal#createIndex(...) and that one unfortunately lacks an option to set the where condition.

Possible solution you'd like to see:

It would be good to reuse the create builders to produce the appropriate static structure:

• ConstraintFinalStep already extends Constraint
• WindowSpecificationFinalStep already extends WindowSpecification
• CreateTableFinalStep could extend Table
• CreateViewFinalStep could extend Table
• CreateIndexFinalStep could extend Index
• CreateSequenceFinalStep could extend Sequence

Then it would be very easy to prepare all structures and use them for subsequent queries. It would also allow using DSLContext#ddl(...) methods as well as DSLContext#meta(...) and Meta#migrateTo(...).

There are a few gotchas:

• CreateIndexStep requires specifying the table which brings us to a chicken-egg problem; we cannot provide the table because we want to use the index in its definition.
• *FinalStep is deprecated
• it may be preferable to provide a method such as toTable() or toIndex() rather than employing inheritance.

Possible workarounds:

We use a couple of hacks:

• extend TableImpl and use of Internal (maybe CustomTable would work too but in the past it was not good enough)
• custom delegating implementation of IndexImpl which has a public constructor with where parameter

Alternatively use DSL::table and DSL::field a lot.

Versions:

• jOOQ: 1.15.1
• Java: 11
• Database (include vendor): Postgres 12+
• OS: Linux
• JDBC Driver (include name if inofficial driver):

[lukaseder]

Thanks a lot for your feature request. We have a classic case of https://xyproblem.info here. 😁

Let me explain: Your "Possible solution" does not sound like a good idea on various levels. Simple example, with your approach, we could now write:

selectFrom(createTable("t").column("i", INTEGER).join(U))

What would this even mean, for a CreateTableFinalStep type to be a Table, which can be joined? I could probably find other issues, but let's not dig any further into that solution that arose from your workarounds.

So, let's rewind. What I think you want is not a hacky extension or fusion of existing API, but a new API that allows for programmatically constructing what are currently internal meta data objects, and we already have a feature request for this: #7444 (only, so far, it was only about TableImpl, but it will obviously be about IndexImpl as well)

Closing this as a duplicate of #7444. Let me know if I misunderstood.