Jooq: Add Meta.migrateTo(Meta):Queries to generate a migration script between two Meta versions

[lukaseder]

org.jooq.Meta instances are becoming increasingly important representations of runtime schema meta information. Historically, they were implemented purely using JDBC's DatabaseMetaData, which queries the actual database dictionary views directly. But we now also have implementations taking:

• Arbitrary jOOQ meta objects, including Catalog, Schema, Table
• The jOOQ InformationSchema object, which can be read from XML
• DDL statements that are parsed and interpreted

The above implementations are all immutable and do not depend on a live database connection. With those immutable implementations, we can now compare two versions of the meta model, and try to derive a migration script between the two versions

Things that can be detected automatically:

• [ ] Catalogs
  
  
  
  • [ ] Catalog additions
  
  
  • [ ] Catalog removals
  
  
• [x] Schemas
  
  
  
  • [x] Schema additions
  
  
  • [x] Schema removals
  
  
• [x] Tables
  
  
  
  • [x] Table additions
  
  
  • [x] Table removals
  
  
  • [ ] Column data type changes
  
  
  • [x] Nullability
  
  
  • [x] Default values
  
  
  • [x] Precision / scale
  
  
  • [x] Length
  
  
  • [x] Type
  
  
  • [ ] Identities
  
  
  • [ ] Collations
  
  
  • [ ] Character sets
  
  
  • [x] Column additions
  
  
  • [x] Column removals
  
  
  • [ ] Column reorderings (only if respectColumnOrder is set)
  
  
  • [x] Primary and unique constraints
  
  
  • [x] Additions
  
  
  • [x] Removals
  
  
  • [x] Renames
  
  
  • [x] Foreign key constraints
  
  
  • [x] Additions
  
  
  • [x] Removals
  
  
  • [x] Renames
  
  
  • [x] Check constraints
  
  
  • [x] Additions
  
  
  • [x] Removals
  
  
  • [x] Renames
  
  
  • [x] Index additions
  
  
  • [x] Index removals
  
  
  • [x] Index renames
  
  
• [x] Views
  
  
  
  • [x] View additions
  
  
  • [x] View removals
  
  
  • [x] View replacements
  
  
• [ ] Types
  
  
  
  • [ ] Enum types (Meta needs additional API to find these types: https://github.com/jOOQ/jOOQ/issues/9509)
  
  
  • [ ] Enum value additions: https://www.postgresql.org/docs/current/sql-altertype.html
  
  
  • [ ] Enum value renaming: https://www.postgresql.org/docs/current/sql-altertype.html
  
  
  • [ ] UDTs (?)
  
  
• [x] Sequences
  
  
  
  • [x] Sequence additions
  
  
  • [x] Sequence removals
  
  
  • [x] Sequence flag changes (depends on #7752)
  
  
• [x] Comments
  
  
  
  • [x] Comment additions
  
  
  • [x] Comment removals
  
  

Things that can be detected using some heuristics (based on likeliness):

• [ ] Renaming
  
  
  
  • [ ] Catalog renames
  
  
  • [ ] Schema renames
  
  
  • [ ] Table renames
  
  
  • [ ] Column renames
  
  
  • [ ] View renames
  
  
  • [ ] Sequence renames
  
  
• [ ] Moving
  
  
  
  • [ ] Moving an entire schema between catalogs
  
  
  • [ ] Moving an entire table between schemas
  
  

MigrationConfiguration

Just like the DDLExportConfiguration, we should have a MigrationConfiguration that governs the style of produced queries. Some examples:

• [x] alterTableAddMultiple: To add multiple columns and constraints in one single statement (default: false)
• [x] alterTableDropMultiple: To drop multiple columns and constraints in one single statement (default: false)
• [x] dropSchemaCascade: To use the CASCADE syntax when dropping schemas (default: false)
• [x] dropTableCascade: To use the CASCADE syntax when dropping tables (default: false)
• [ ] alterTableDropCascade: To use the CASCADE syntax when dropping columns or constraints (default: false)
• [ ] dropTypeCascade: To use the CASCADE syntax when dropping types (default: false)
• [x] createOrReplaceView: Whether modified views should be replaced, or dropped and created (default: false)
• [ ] respectColumnOrder: Whether column order is relevant (default: false)

Out of scope for 3.13

Some more advanced things are out of scope for 3.13 and will be implemented later, tracked as #9656

[knutwannheden]

I think it could also be interesting to allow the user to "guide" the diff, in case it gets something wrong. E.g. when the diff results in a RENAME rather than in a DROP / CREATE pair or vice versa.

I suppose the user could already achieve this using Queries#concat() (with some calls to ddl() and meta()). But possibly we should also add a Meta#combine(Meta) (or concat()) as a counterpart to diff(), which would make this much easier.

[lukaseder]

I think it could also be interesting to allow the user to "guide" the diff, in case it gets something wrong. E.g. when the diff results in a RENAME rather than in a DROP / CREATE pair or vice versa.

Yes, clearly the "heuristics" I've mentioned should be configurable

I suppose the user could already achieve this using Queries#concat() (with some calls to ddl() and meta()). But possibly we should also add a Meta#combine(Meta) (or concat()) as a counterpart to diff(), which would make this much easier.

Not sure if I understand this?

[knutwannheden]

Not sure if I understand this?

To guide the rename heuristics the user could for instance take the Meta and "manually" rename a table by using the Meta as a baseline with the DDLInterpreter and interpret an ALTER TABLE ... RENAME to produce a new Meta. Then the diff would not have to apply the heuristic anymore.

I was first thinking that a new Meta#combine(Meta) which essentially does a "union" of two Meta would be useful here, but that of course doesn't make sense. Something like Meta#apply(Query...) (or Meta#transform(Query...)) would make more sense.

Possibly Meta#combine(Meta) could also be useful for other use cases, but I guess that remains to be seen.

[knutwannheden]

So a utility method like this is what I had in mind:

    public Meta apply(Query... queries) {
        return dsl().meta(ddl().concat(dsl().queries(queries)).queries());
    }

[lukaseder]

To guide the rename heuristics the user could for instance take the Meta and "manually" rename a table by using the Meta as a baseline with the DDLInterpreter and interpret an ALTER TABLE ... RENAME to produce a new Meta. Then the diff would not have to apply the heuristic anymore.

Ah yes, that's something I also had in mind. In fact, I even thought of the same name as you for the method 👌

public Meta apply(Query... queries) { ... }

I was first thinking that a new Meta#combine(Meta) which essentially does a "union" of two Meta would be useful here, but that of course doesn't make sense.

Well, we already do that when we apply several information_schema XML files, but I agree I'm not convinced of the usefulness of this.

We'll see if we need it for Meta in general.

[lukaseder]

In fact, I had already implemented Meta.apply() in the Diff tests 😉

[lukaseder]

Meta.apply(Queries) was implemented with #9436

[lukaseder]

I'm running a poll about method naming, which is probably too confusing:
https://twitter.com/lukaseder/status/1204689766324854785

A better name for the method will be discussed soon

[lukaseder]

We'll use the term A.migrateTo(B) to describe a migration from A to B. This applies to both Meta.migrateTo(Meta) and Version.migrateTo(Version)

[lukaseder]

After careful thought, I've decided that this is going to be a very central feature of the new migration APIs. Adoption of these APIs depends on the success of this feature, which is why we'll be open sourcing it.

The distinction between editions will be done on a more fine-grained level.