Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add Meta.diff(Meta):Queries to generate a migration script between two Meta versions #9425

Open
lukaseder opened this issue Oct 23, 2019 · 7 comments

Comments

@lukaseder
Copy link
Member

@lukaseder lukaseder commented Oct 23, 2019

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
  • Schemas
    • Schema additions
    • Schema removals
  • Tables
    • Table additions
    • Table removals
    • Column data type changes
      • Nullability
      • Default values
      • Precision / scale
      • Length
      • Identities
      • Collations
      • Character sets
    • Column additions
    • Column removals
    • Column reorderings (only if respectColumnOrder is set)
    • Primary and unique constraints
      • Additions
      • Removals
      • Renames
    • Foreign key constraints
      • Additions
      • Removals
      • Renames
    • Check constraints
      • Additions
      • Removals
      • Renames
    • Index additions
    • Index removals
    • Index renames
  • Views
    • View additions
    • View removals
    • View replacements
  • Types
  • Sequences
    • Sequence additions
    • Sequence removals
    • Sequence flag changes (depends on #7752)
  • Comments
    • Comment additions
    • Comment removals

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

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

DDLDiffConfiguration

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

  • alterTableAddMultiple: To add multiple columns and constraints in one single statement (default: false)
  • alterTableDropMultiple: To drop multiple columns and constraints in one single statement (default: false)
  • dropSchemaCascade: To use the CASCADE syntax when dropping schemas (default: false)
  • 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)
  • createOrReplaceView: Whether modified views should be replaced, or dropped and created (default: false)
  • respectColumnOrder: Whether column order is relevant (default: false)
@lukaseder lukaseder added this to the Version 3.13.0 milestone Oct 23, 2019
@lukaseder lukaseder self-assigned this Oct 23, 2019
@lukaseder lukaseder added this to To do in 3.13 DDL interpretation via automation Oct 23, 2019
lukaseder added a commit that referenced this issue Oct 23, 2019
lukaseder added a commit that referenced this issue Oct 23, 2019
- Do not use lambda expressions
lukaseder added a commit that referenced this issue Oct 23, 2019
lukaseder added a commit that referenced this issue Oct 23, 2019
lukaseder added a commit that referenced this issue Oct 23, 2019
@knutwannheden

This comment has been minimized.

Copy link
Contributor

@knutwannheden knutwannheden commented Oct 23, 2019

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 added a commit that referenced this issue Oct 23, 2019
@lukaseder

This comment has been minimized.

Copy link
Member Author

@lukaseder lukaseder commented Oct 23, 2019

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

This comment has been minimized.

Copy link
Contributor

@knutwannheden knutwannheden commented Oct 23, 2019

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

This comment has been minimized.

Copy link
Contributor

@knutwannheden knutwannheden commented Oct 23, 2019

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 added a commit that referenced this issue Oct 23, 2019
@lukaseder

This comment has been minimized.

Copy link
Member Author

@lukaseder lukaseder commented Oct 23, 2019

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

This comment has been minimized.

Copy link
Member Author

@lukaseder lukaseder commented Oct 23, 2019

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

@lukaseder lukaseder moved this from To do to In progress in 3.13 DDL interpretation Oct 23, 2019
@lukaseder

This comment has been minimized.

Copy link
Member Author

@lukaseder lukaseder commented Oct 25, 2019

Meta.apply(Queries) was implemented with #9436

lukaseder added a commit that referenced this issue Oct 25, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.