Programmatically diff databases against master file

[mdewilde]

We find ourselves with the following use case: we'd like to diff databases against a master schema. We have a variety of instances running on databases that are not mutually accessible, as they are located in different data centers behind firewalls. A variety of reasons make even SSH tunnelling impossible.

The intuitive approach seems be to generate the schema on one master database, and load and compare that schema as required elsewhere.

I envision using JSON for this, as writing and parsing JSON is well supported in Java.

I've not yet been able to create the JSON programmatically:

public void generate(DataSource ds) throws Exception {

    SchemaCrawlerOptions options = new SchemaCrawlerOptions();
    options.setSchemaInfoLevel(SchemaInfoLevelBuilder.standard());
    options.setSchemaInclusionRule(new SchemaInclusionRule("our_db_name")); 

    OutputOptions outputOptions = new OutputOptions(TextOutputFormat.json.getFormat());
    StringWriter writer = new StringWriter();
    outputOptions.setWriter(writer);

    DatabaseConnector connector = new MySQLDatabaseConnector();

    DatabaseSpecificOverrideOptions o = new DatabaseSpecificOverrideOptionsBuilder().toOptions();

    Executable executable = connector.newExecutable("?????"); // XXX unclear what command is needed

    executable.setOutputOptions(outputOptions);
    executable.setSchemaCrawlerOptions(options);
    executable.execute(ds.getConnection(), o);

    System.out.println(writer.toString());

}

This ends with a syntax error. As noted above, it's not clear to me what command is expected.

My questions are this:

• is there a way to export a schema to JSON programmatically?
• is it possible to recreate the schema object hierarchy from such a JSON file?

[adriens]

Hi, why not using liquibase for that ?

[schemacrawler]

@mdewilde Marceau, please read the SchemaCrawler website thoroughly, including reviewing the code on the code examples page. This will show you how to generate JSON. Just rename the output file with a .json extension.

JSON is not the best way to create an in memory Java object model. You should look at the offline snapshots functionality for that.

If you need help, please see the consulting options.