private String readGraphVizOpts(final Config config) {
final String scGraphVizOptsCfg = getStringValue(config, GRAPH_GRAPHVIZ_OPTS, "");
if (!Utility.isBlank(scGraphVizOptsCfg)) {
LOGGER.log(
Level.CONFIG,
"Using additional GraphViz command-line options from config, " + scGraphVizOptsCfg);
return scGraphVizOptsCfg;
}
final String scGraphVizOptsProp = System.getProperty(SC_GRAPHVIZ_OPTS);
if (!Utility.isBlank(scGraphVizOptsProp)) {
LOGGER.log(
Level.CONFIG,
"Using additional GraphViz command-line options from SC_GRAPHVIZ_OPTS system property, "
+ scGraphVizOptsProp);
return scGraphVizOptsProp;
}
final String scGraphVizOptsEnv = System.getenv(SC_GRAPHVIZ_OPTS);
if (!Utility.isBlank(scGraphVizOptsEnv)) {
LOGGER.log(
Level.CONFIG,
"Using additional GraphViz command-line options from SC_GRAPHVIZ_OPTS environmental variable, "
+ scGraphVizOptsEnv);
return scGraphVizOptsEnv;
}
return "";
}
private void buildFullName() {
if (fullName == null) {
final StringBuilder buffer = new StringBuilder();
if (schema != null && !Utility.isBlank(schema.getFullName())) {
buffer.append(schema.getFullName()).append(".");
}
if (!Utility.isBlank(getName())) {
buffer.append(getName());
}
fullName = buffer.toString();
}
}
private void createIndices(final MutableTable table, final MetadataResultSet results)
throws SQLException {
try {
while (results.next()) {
// "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
String indexName = quotedName(results.getString("INDEX_NAME"));
if (Utility.isBlank(indexName)) {
indexName = UNKNOWN;
}
LOGGER.log(
Level.FINER, String.format("Retrieving index: %s.%s", table.getFullName(), indexName));
final String columnName = quotedName(results.getString("COLUMN_NAME"));
if (Utility.isBlank(columnName)) {
continue;
}
MutableIndex index = table.getIndex(indexName);
if (index == null) {
index = new MutableIndex(table, indexName);
table.addIndex(index);
}
final boolean uniqueIndex = !results.getBoolean("NON_UNIQUE");
final int type = results.getInt("TYPE", IndexType.unknown.getId());
final int ordinalPosition = results.getInt("ORDINAL_POSITION", 0);
final IndexColumnSortSequence sortSequence =
IndexColumnSortSequence.valueOfFromCode(results.getString("ASC_OR_DESC"));
final int cardinality = results.getInt("CARDINALITY", 0);
final int pages = results.getInt("PAGES", 0);
final MutableColumn column = table.getColumn(columnName);
if (column != null) {
column.setPartOfUniqueIndex(uniqueIndex);
final MutableIndexColumn indexColumn = new MutableIndexColumn(index, column);
indexColumn.setIndexOrdinalPosition(ordinalPosition);
indexColumn.setSortSequence(sortSequence);
//
index.addColumn(indexColumn);
index.setUnique(uniqueIndex);
index.setType(IndexType.valueOf(type));
index.setCardinality(cardinality);
index.setPages(pages);
index.addAttributes(results.getAttributes());
}
}
} finally {
results.close();
}
}
/**
* Setup the schema.
*
* @param dataSource Datasource
* @param schemas Schema names
*/
private static void setupSchema(
final DatabaseConnectionOptions dataSource, final String... schemas) {
Connection connection = null;
Statement statement = null;
try {
if (dataSource != null) {
connection = dataSource.getConnection();
connection.setAutoCommit(true);
statement = connection.createStatement();
for (final String schema : schemas) {
for (final String scriptType :
new String[] {
"pre_schema", "schema", "post_schema", "data",
}) {
final String scriptResource =
String.format("/testdatabase/%s.%s.sql", schema, scriptType)
.toLowerCase(Locale.ENGLISH);
final String sqlScript = Utility.readResourceFully(scriptResource);
if (!Utility.isBlank(sqlScript)) {
for (final String sql : sqlScript.split(";")) {
if (!Utility.isBlank(sql)) {
statement.executeUpdate(sql);
}
}
}
}
}
connection.close();
}
} catch (final SQLException e) {
System.err.println(e.getMessage());
LOGGER.log(Level.WARNING, e.getMessage(), e);
} finally {
if (statement != null) {
try {
statement.close();
} catch (final SQLException e) {
LOGGER.log(Level.WARNING, "", e);
}
}
if (connection != null) {
try {
connection.close();
} catch (final SQLException e) {
LOGGER.log(Level.WARNING, "", e);
}
}
}
}
private SchemaCrawlerCommandLine(
final String configResource, final ConnectionOptions connectionOptions, final String... args)
throws SchemaCrawlerException {
if (args == null || args.length == 0) {
throw new IllegalArgumentException("No command line arguments provided");
}
command = new CommandParser(args).getOptions().toString();
outputOptions = new OutputOptionsParser(args).getOptions();
if (!Utility.isBlank(configResource)) {
config = Config.load(SchemaCrawlerCommandLine.class.getResourceAsStream(configResource));
this.connectionOptions = new BundledDriverConnectionOptionsParser(args, config).getOptions();
} else {
if (args != null && args.length > 0) {
config = new ConfigParser(args).getOptions();
} else {
config = new Config();
}
if (connectionOptions != null) {
this.connectionOptions = connectionOptions;
} else {
ConnectionOptions parsedConnectionOptions =
new CommandLineConnectionOptionsParser(args, config).getOptions();
if (parsedConnectionOptions == null) {
parsedConnectionOptions = new ConfigConnectionOptionsParser(args, config).getOptions();
}
this.connectionOptions = parsedConnectionOptions;
}
}
schemaCrawlerOptions = new SchemaCrawlerOptionsParser(args, config).getOptions();
}
private static void showHelp(final String helpResource) {
if (sf.util.Utility.isBlank(helpResource)
|| SchemaCrawlerHelpCommandLine.class.getResource(helpResource) == null) {
return;
}
final String helpText = Utility.readResourceFully(helpResource);
System.out.println(helpText);
}
private void createForeignKeys(
final MetadataResultSet results, final NamedObjectList<MutableForeignKey> foreignKeys)
throws SQLException {
try {
while (results.next()) {
String foreignKeyName = quotedName(results.getString("FK_NAME"));
if (Utility.isBlank(foreignKeyName)) {
foreignKeyName = UNKNOWN;
}
LOGGER.log(Level.FINER, "Retrieving foreign key: " + foreignKeyName);
final String pkTableCatalogName = quotedName(results.getString("PKTABLE_CAT"));
final String pkTableSchemaName = quotedName(results.getString("PKTABLE_SCHEM"));
final String pkTableName = quotedName(results.getString("PKTABLE_NAME"));
final String pkColumnName = quotedName(results.getString("PKCOLUMN_NAME"));
final String fkTableCatalogName = quotedName(results.getString("FKTABLE_CAT"));
final String fkTableSchemaName = quotedName(results.getString("FKTABLE_SCHEM"));
final String fkTableName = quotedName(results.getString("FKTABLE_NAME"));
final String fkColumnName = quotedName(results.getString("FKCOLUMN_NAME"));
MutableForeignKey foreignKey = foreignKeys.lookup(foreignKeyName);
if (foreignKey == null) {
foreignKey = new MutableForeignKey(foreignKeyName);
foreignKeys.add(foreignKey);
}
final int keySequence = results.getInt("KEY_SEQ", 0);
final int updateRule = results.getInt("UPDATE_RULE", ForeignKeyUpdateRule.unknown.getId());
final int deleteRule = results.getInt("DELETE_RULE", ForeignKeyUpdateRule.unknown.getId());
final int deferrability =
results.getInt("DEFERRABILITY", ForeignKeyDeferrability.unknown.getId());
final MutableColumn pkColumn =
lookupOrCreateColumn(pkTableCatalogName, pkTableSchemaName, pkTableName, pkColumnName);
final MutableColumn fkColumn =
lookupOrCreateColumn(fkTableCatalogName, fkTableSchemaName, fkTableName, fkColumnName);
// Make a direct connection between the two columns
if (pkColumn != null && fkColumn != null) {
foreignKey.addColumnPair(keySequence, pkColumn, fkColumn);
foreignKey.setUpdateRule(ForeignKeyUpdateRule.valueOf(updateRule));
foreignKey.setDeleteRule(ForeignKeyUpdateRule.valueOf(deleteRule));
foreignKey.setDeferrability(ForeignKeyDeferrability.valueOf(deferrability));
foreignKey.addAttributes(results.getAttributes());
fkColumn.setReferencedColumn(pkColumn);
((MutableTable) pkColumn.getParent()).addForeignKey(foreignKey);
((MutableTable) fkColumn.getParent()).addForeignKey(foreignKey);
}
}
} finally {
results.close();
}
}
private static void showHelp(final String helpResource) {
final String helpResourceName;
if (sf.util.Utility.isBlank(helpResource)
|| SchemaCrawlerHelpCommandLine.class.getResource(helpResource) == null) {
helpResourceName = "/help/DefaultExecutable.txt";
} else {
helpResourceName = helpResource;
}
final String helpText = Utility.readResourceFully(helpResourceName);
System.out.println(helpText);
}
/**
* Loads the SchemaCrawler configuration, and override configuration, from properties files.
*
* @param configFilenames Configuration file name.
* @return Configuration properties.
*/
public static Config load(final String... configFilenames) {
Properties configProperties = new Properties();
if (configFilenames != null) {
for (final String configFilename : configFilenames) {
if (!Utility.isBlank(configFilename)) {
configProperties = loadProperties(configProperties, new File(configFilename));
}
}
}
return new Config(configProperties);
}
/**
* Gets the Java type mapping for a data type. If no mapping exists, returns null. If a class name
* is passed in, it overrides the mapping in the type map.
*
* @param typeName Type name to find a mapping for.
* @param className Overridden class name
* @return Mapped class
*/
public Class<?> get(final String typeName, final String className) {
if (Utility.isBlank(className)) {
return sqlTypeMap.get(typeName);
} else {
try {
return Class.forName(className);
} catch (final ClassNotFoundException e) {
LOGGER.log(Level.WARNING, "Could not obtain class mapping for data type " + typeName, e);
return null;
}
}
}
/**
* {@inheritDoc}
*
* @see schemacrawler.tools.integration.TemplatedSchemaRenderer#render(java.lang.String,
* schemacrawler.schema.Schema, java.io.Writer)
*/
@Override
protected void render(
final Connection connection,
final String scriptFileName,
final Database database,
final Writer writer)
throws ExecutionException {
if (sf.util.Utility.isBlank(scriptFileName)) {
throw new ExecutionException("No script file provided");
}
final Reader reader;
final File scriptFile = new File(scriptFileName);
if (scriptFile.exists() && scriptFile.canRead()) {
try {
reader = new FileReader(scriptFile);
} catch (final FileNotFoundException e) {
throw new ExecutionException("Cannot load script, " + scriptFile.getAbsolutePath());
}
} else {
final InputStream inputStream =
ScriptRenderer.class.getResourceAsStream("/" + scriptFileName);
if (inputStream != null) {
reader = new InputStreamReader(inputStream);
} else {
throw new ExecutionException("Cannot load script, " + scriptFileName);
}
}
try {
// Create a new instance of the engine
final ScriptEngineManager scriptEngineManager = new ScriptEngineManager();
ScriptEngine scriptEngine =
scriptEngineManager.getEngineByExtension(FileUtility.getFileExtension(scriptFile));
if (scriptEngine == null) {
scriptEngine = scriptEngineManager.getEngineByName("JavaScript");
}
if (scriptEngine == null) {
throw new ExecutionException("Script engine not found");
}
// Set up the context
scriptEngine.getContext().setWriter(writer);
scriptEngine.put("database", database);
scriptEngine.put("connection", connection);
// Evaluate the script
scriptEngine.eval(reader);
} catch (final ScriptException e) {
throw new ExecutionException("Could not evaluate script", e);
}
}
@Override
public DatabaseConnectionOptions getOptions() throws SchemaCrawlerException {
parse(
new Option[] {
optionDriver, optionConnectionUrl, optionUser, optionPassword,
});
final DatabaseConnectionOptions conenctionOptions;
if (optionDriver.isFound() && optionConnectionUrl.isFound()) {
final String jdbcDriverClassName = optionDriver.getValue();
final String connectionUrl = optionConnectionUrl.getValue();
if (Utility.isBlank(jdbcDriverClassName) || Utility.isBlank(connectionUrl)) {
conenctionOptions = null;
} else {
conenctionOptions = new DatabaseConnectionOptions(jdbcDriverClassName, connectionUrl);
conenctionOptions.setUser(optionUser.getValue());
conenctionOptions.setPassword(optionPassword.getValue());
}
} else {
conenctionOptions = null;
}
return conenctionOptions;
}
@Test
public void routineDefinitions() throws Exception {
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setRoutinesSql("SELECT * FROM INFORMATION_SCHEMA.ROUTINES");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
final Database database = getDatabase(schemaCrawlerOptions);
final Schema schema = new SchemaReference("PUBLIC", "BOOKS");
final Routine[] routines = database.getRoutines(schema).toArray(new Routine[0]);
assertEquals("Wrong number of routines", 4, routines.length);
for (final Routine routine : routines) {
assertFalse(
"Routine definition not found, for " + routine, Utility.isBlank(routine.getDefinition()));
}
}
void setGraphOutputFormat(final String outputFormat) {
graphOutputFormat = outputFormat;
final List<String> outputFormats =
Arrays.asList(
"canon",
"cmap",
"cmapx",
"cmapx_np",
"dot",
"eps",
"fig",
"gd",
"gd2",
"gif",
"gv",
"imap",
"imap_np",
"ismap",
"jpe",
"jpeg",
"jpg",
"pdf",
"plain",
"plain-ext",
"png",
"ps",
"ps2",
"svg",
"svgz",
"tk",
"vml",
"vmlz",
"vrml",
"wbmp",
"xdot");
if (Utility.isBlank(graphOutputFormat) || !outputFormats.contains(graphOutputFormat)) {
graphOutputFormat = "png";
}
}
/**
* Loads objects from command line options. Optionally loads the config from the classpath.
*
* @param args Command line arguments.
* @param helpOptions Help options.
* @param configResource Config resource.
* @throws SchemaCrawlerException On an exception
*/
SchemaCrawlerHelpCommandLine(
final String[] args, final HelpOptions helpOptions, final String configResource)
throws SchemaCrawlerException {
if (args == null) {
throw new IllegalArgumentException("No command line arguments provided");
}
if (helpOptions == null) {
throw new SchemaCrawlerException("No help options provided");
}
this.helpOptions = helpOptions;
hideConfig = !Utility.isBlank(configResource);
if (args.length == 0) {
command = null;
} else {
final CommandParser commandParser = new CommandParser();
commandParser.parse(args);
command = commandParser.getOptions().toString();
}
}
private void createForeignKeys(
final MetadataResultSet results, final NamedObjectList<MutableForeignKey> foreignKeys)
throws SQLException {
try {
while (results.next()) {
String foreignKeyName = quotedName(results.getString("FK_NAME"));
LOGGER.log(Level.FINER, "Retrieving foreign key: " + foreignKeyName);
final String pkTableCatalogName = quotedName(results.getString("PKTABLE_CAT"));
final String pkTableSchemaName = quotedName(results.getString("PKTABLE_SCHEM"));
final String pkTableName = quotedName(results.getString("PKTABLE_NAME"));
final String pkColumnName = quotedName(results.getString("PKCOLUMN_NAME"));
final String fkTableCatalogName = quotedName(results.getString("FKTABLE_CAT"));
final String fkTableSchemaName = quotedName(results.getString("FKTABLE_SCHEM"));
final String fkTableName = quotedName(results.getString("FKTABLE_NAME"));
final String fkColumnName = quotedName(results.getString("FKCOLUMN_NAME"));
final int keySequence = results.getInt("KEY_SEQ", 0);
final int updateRule = results.getInt("UPDATE_RULE", ForeignKeyUpdateRule.unknown.getId());
final int deleteRule = results.getInt("DELETE_RULE", ForeignKeyUpdateRule.unknown.getId());
final int deferrability =
results.getInt("DEFERRABILITY", ForeignKeyDeferrability.unknown.getId());
final Column pkColumn =
lookupOrCreateColumn(pkTableCatalogName, pkTableSchemaName, pkTableName, pkColumnName);
final Column fkColumn =
lookupOrCreateColumn(fkTableCatalogName, fkTableSchemaName, fkTableName, fkColumnName);
final boolean isPkColumnPartial = pkColumn instanceof ColumnPartial;
final boolean isFkColumnPartial = fkColumn instanceof ColumnPartial;
if (pkColumn == null || fkColumn == null || (isFkColumnPartial && isPkColumnPartial)) {
continue;
}
if (Utility.isBlank(foreignKeyName)) {
foreignKeyName = MetaDataUtility.constructForeignKeyName(pkColumn, fkColumn);
}
final Optional<MutableForeignKey> foreignKeyOptional = foreignKeys.lookup(foreignKeyName);
final MutableForeignKey foreignKey;
if (foreignKeyOptional.isPresent()) {
foreignKey = foreignKeyOptional.get();
} else {
foreignKey = new MutableForeignKey(foreignKeyName);
foreignKeys.add(foreignKey);
}
foreignKey.addColumnReference(keySequence, pkColumn, fkColumn);
foreignKey.setUpdateRule(ForeignKeyUpdateRule.valueOf(updateRule));
foreignKey.setDeleteRule(ForeignKeyUpdateRule.valueOf(deleteRule));
foreignKey.setDeferrability(ForeignKeyDeferrability.valueOf(deferrability));
foreignKey.addAttributes(results.getAttributes());
if (fkColumn instanceof MutableColumn) {
((MutableColumn) fkColumn).setReferencedColumn(pkColumn);
((MutableTable) fkColumn.getParent()).addForeignKey(foreignKey);
} else if (isFkColumnPartial) {
((ColumnPartial) fkColumn).setReferencedColumn(pkColumn);
((TablePartial) fkColumn.getParent()).addForeignKey(foreignKey);
}
if (pkColumn instanceof MutableColumn) {
((MutableTable) pkColumn.getParent()).addForeignKey(foreignKey);
} else if (isPkColumnPartial) {
((TablePartial) pkColumn.getParent()).addForeignKey(foreignKey);
}
}
} finally {
results.close();
}
}
/**
* Retrieves a check constraint information from the database, in the INFORMATION_SCHEMA format.
*
* @throws SQLException On a SQL exception
*/
void retrieveCheckConstraintInformation() throws SQLException {
final Map<String, MutableCheckConstraint> checkConstraintsMap = new HashMap<>();
final InformationSchemaViews informationSchemaViews =
getRetrieverConnection().getInformationSchemaViews();
if (!informationSchemaViews.hasTableConstraintsSql()) {
LOGGER.log(Level.FINE, "Table constraints SQL statement was not provided");
return;
}
final String tableConstraintsInformationSql = informationSchemaViews.getTableConstraintsSql();
final Connection connection = getDatabaseConnection();
try (final Statement statement = connection.createStatement();
final MetadataResultSet results =
new MetadataResultSet(statement.executeQuery(tableConstraintsInformationSql)); ) {
while (results.next()) {
final String catalogName = quotedName(results.getString("CONSTRAINT_CATALOG"));
final String schemaName = quotedName(results.getString("CONSTRAINT_SCHEMA"));
final String constraintName = quotedName(results.getString("CONSTRAINT_NAME"));
LOGGER.log(Level.FINER, "Retrieving constraint: " + constraintName);
// "TABLE_CATALOG", "TABLE_SCHEMA"
final String tableName = quotedName(results.getString("TABLE_NAME"));
final MutableTable table = lookupTable(catalogName, schemaName, tableName);
if (table == null) {
LOGGER.log(
Level.FINE,
String.format("Cannot find table, %s.%s.%s", catalogName, schemaName, tableName));
continue;
}
final String constraintType = results.getString("CONSTRAINT_TYPE");
final boolean deferrable = results.getBoolean("IS_DEFERRABLE");
final boolean initiallyDeferred = results.getBoolean("INITIALLY_DEFERRED");
if (constraintType.equalsIgnoreCase("check")) {
final MutableCheckConstraint checkConstraint =
new MutableCheckConstraint(table, constraintName);
checkConstraint.setDeferrable(deferrable);
checkConstraint.setInitiallyDeferred(initiallyDeferred);
checkConstraint.addAttributes(results.getAttributes());
// Add to map, since we will need this later
final String constraintFullName = table.getSchema().getFullName() + "." + constraintName;
checkConstraintsMap.put(constraintFullName, checkConstraint);
}
}
} catch (final Exception e) {
LOGGER.log(Level.WARNING, "Could not retrieve check constraint information", e);
return;
}
if (!informationSchemaViews.hasCheckConstraintsSql()) {
LOGGER.log(Level.FINE, "Check constraints SQL statement was not provided");
return;
}
final String checkConstraintInformationSql = informationSchemaViews.getCheckConstraintsSql();
// Get check constraint definitions
try (final Statement statement = connection.createStatement();
final MetadataResultSet results =
new MetadataResultSet(statement.executeQuery(checkConstraintInformationSql)); ) {
while (results.next()) {
final String catalogName = quotedName(results.getString("CONSTRAINT_CATALOG"));
final String schemaName = quotedName(results.getString("CONSTRAINT_SCHEMA"));
final String constraintName = quotedName(results.getString("CONSTRAINT_NAME"));
LOGGER.log(Level.FINER, "Retrieving constraint definition: " + constraintName);
String definition = results.getString("CHECK_CLAUSE");
final String constraintFullName =
new SchemaReference(catalogName, schemaName) + "." + constraintName;
final MutableCheckConstraint checkConstraint = checkConstraintsMap.get(constraintFullName);
if (checkConstraint == null) {
LOGGER.log(Level.FINEST, "Could not add check constraint to table: " + constraintName);
continue;
}
final String text = checkConstraint.getDefinition();
if (!Utility.isBlank(text)) {
definition = checkConstraint.getDefinition() + definition;
}
checkConstraint.setDefinition(definition);
}
} catch (final Exception e) {
LOGGER.log(Level.WARNING, "Could not retrieve check constraints", e);
}
// Add check constraints to tables
final Collection<MutableCheckConstraint> checkConstraintsCollection =
checkConstraintsMap.values();
for (final MutableCheckConstraint checkConstraint : checkConstraintsCollection) {
final MutableTable table = (MutableTable) checkConstraint.getParent();
table.addCheckConstraint(checkConstraint);
}
}