CODEWITHSUNDEEP
javasqljooq
John Wilson
John Wilson

Reputation: 185

Can jooq be used to compare if two schemas are identical?

Can jooq be used to compare if two schemas are identical? Not the data but that the tables, constraints, permissions etc are the same?

Upvotes: 2

Views: 393

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

You can compare all the things that are represented in the jOOQ API, including:

  • Catalogs (SQL Server only)
  • Schemas
  • Tables
  • Columns and their types
  • User defined types
  • Sequences
  • Identities
  • Constraints (including primary keys, unique keys, foreign keys, and check constraints)

What's not supported (yet, in jOOQ 3.9)

An example algorithm

... that compares two versions of the H2 PUBLIC schema:

assertEquals(v1.Public.PUBLIC, v2.Public.PUBLIC);

List<Table<?>> tables1 = v1.Public.PUBLIC.getTables();
List<Table<?>> tables2 = v2.Public.PUBLIC.getTables();
assertEquals(tables1, tables2);

for (int i = 0; i < tables1.size(); i++) {
    Table<?> table1 = tables1.get(i);
    Table<?> table2 = tables2.get(i);

    assertArrayEquals(format("Fields differ for %s and %s", table1, table2),
        table1.fields(), table2.fields());
    assertEquals(format("Primary keys differ for %s and %s", table1, table2),
        table1.getPrimaryKey(), table2.getPrimaryKey());
    assertEquals(format("Schemas differ for %s and %s", table1, table2),
        table1.getSchema(), table2.getSchema());
    assertEquals(format("Identities differ for %s and %s", table1, table2),
        table1.getIdentity(), table2.getIdentity());
    assertEquals(format("Keys differ for %s and %s", table1, table2),
        table1.getKeys(), table2.getKeys());
    assertEquals(format("References differ for %s and %s", table1, table2),
        table1.getReferences(), table2.getReferences());
}

If you want to compare two different schemas for the same content

It'll get a bit more tricky because the jOOQ QueryPart.equals() implementations are usually based on either:

  • toString() comparisons
  • Qualified name comparisons

Since two different schemas will have different qualified names, you'll have to adapt the above algorithm and compare each object's getName() result, rather than the entire object.

Comparing XML content

Maybe that'll be easier for you... jOOQ 3.9 introduced the DSLContext.informationSchema() methods with #5460. These methods return JAXB-annotated classes, which can be marshalled to an XML string:

StringWriter writer = new StringWriter();
JAXB.marshal(ctx.informationSchema(mySchema), writer);
System.out.println(writer.toString());

And you could compare the two versions with any XML tool, e.g. with XSLT

If you just want to compare things manually

If you have two versions of the generated code, you could simply create a diff between the two directories or use your VCS to show you the diff ;-) Perhaps that's good enough.

Upvotes: 2

Related Questions