schemacrawler

SchemaCrawler Java API: add new options to SchemaRetrievalOptions

I'd like to be able to get columns on which the table I'm getting metadata on has constraints. In order to do that, I'd have to look up the value of the columns field for a Constraint (or, in my case, MutableTableConstraint), and constraints are retrieved via table.tableConstraints call (or, in Java, getTableConstraints()).

However, whether the columns list is actually populated seems to depend on SchemaRetrievalOptions (or, more precisely, on the result of a informationSchemaViews.hasQuery(CONSTRAINT_COLUMN_USAGE) call in method retrieveTableConstraintsColumns in file TableConstraintRetriever.java of the SchemaCrawler sources; informationSchemaViews seems to be constructed from SchemaRetrievalOptions in some way), which is constructed from the connection parameter of the SchemaCrawlerUtility.getCatalog() method, unlike SchemaCrawlerOptions, which are constructed manually by the user.

SchemaRetrievalOptions depend on the particular DB that is used: if the schemacrawler-postgresql dependency is included, the options will depend on the contents of that .jar file; and that file has files like postgresql.information_schema/CHECK_CONSTRAINTS.sql, etc, which is where some of the retrieval options come from.

So, the question is, is there a way for me to influence, programmatically, SchemaRetrievalOptions, to include an option for CONSTRAINT_COLUMN_USAGE? Apparently the command line interface of SchemaCrawler can achieve this by using a config file, but I'm using the Java API and the .jar files corresponding to each DB dialect, and I can't really manually update the .jar with the .sql file containing the SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE query that I need (I mean, for local usage I can, but not for distribution).

(SchemaCrawler version: 16.9.3)

Solution

  • So, the question is, is there a way for me to influence, programmatically, SchemaRetrievalOptions, to include an option for CONSTRAINT_COLUMN_USAGE?

    There are few ways that I can think of that you can influence how constraint columns are retrieved. Here are some ideas, in no particular order:

    1. If you have developed a INFORMATION_SCHEMA or other data dictionary view query that you think is generally useful for a given database dialect, please submit an enhancement request to the SchemaCrawler project on GitHub.
    2. If you like, you can create your custom database plugin, bundle that in a jar, and make that jar available on SchemaCrawler's classpath.
    3. You can programatically build InformationSchemaViews. For some example code to get you started, please see DatabaseInfoRetrieverTest.java on how to build them, and SchemaCrawlerUtility.java for how to use them.

    Sualeh Fatehi, SchemaCrawler