/**
* Given a two d array of doubles, create a table.
*
* @param data
* @return
*/
public static ExampleTable getTable(
double[][] data,
String[] inputNames,
String[] outputNames,
int[] inputs,
int[] outputs,
int count) {
Column[] cols = new Column[data.length];
int index = 0;
for (int i = 0; i < inputs.length; i++, index++) {
if (data.length != count) {
double[] tmp = new double[count];
System.arraycopy(data[index], 0, tmp, 0, count);
data[index] = tmp;
}
cols[index] = new DoubleColumn(data[index]);
cols[index].setLabel(inputNames[i]);
}
for (int i = 0; i < outputs.length; i++, index++) {
if (data.length != count) {
double[] tmp = new double[count];
System.arraycopy(data[index], 0, tmp, 0, count);
data[index] = tmp;
}
cols[index] = new DoubleColumn(data[index]);
cols[index].setLabel(outputNames[i]);
}
MutableTable mt = new MutableTableImpl(cols);
ExampleTable et = mt.toExampleTable();
et.setInputFeatures(inputs);
et.setOutputFeatures(outputs);
return et;
}
/**
* Retrieves additional table attributes from the database.
*
* @throws SQLException On a SQL exception
*/
void retrieveAdditionalTableAttributes() throws SQLException {
final InformationSchemaViews informationSchemaViews =
getRetrieverConnection().getInformationSchemaViews();
if (!informationSchemaViews.hasAdditionalTableAttributesSql()) {
LOGGER.log(Level.FINE, "Additional table attributes SQL statement was not provided");
return;
}
final String tableAttributesSql = informationSchemaViews.getAdditionalTableAttributesSql();
final Connection connection = getDatabaseConnection();
try (final Statement statement = connection.createStatement();
final MetadataResultSet results =
new MetadataResultSet(statement.executeQuery(tableAttributesSql)); ) {
while (results.next()) {
final String catalogName = quotedName(results.getString("TABLE_CATALOG"));
final String schemaName = quotedName(results.getString("TABLE_SCHEMA"));
final String tableName = quotedName(results.getString("TABLE_NAME"));
LOGGER.log(Level.FINER, "Retrieving additional table attributes: " + tableName);
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;
}
table.addAttributes(results.getAttributes());
}
} catch (final Exception e) {
LOGGER.log(Level.WARNING, "Could not retrieve additional table attributes", e);
}
}
/**
* Looks up a column in the database. If the column and table are not found, they are created, and
* added to the schema. This is prevent foreign key relationships from having a null pointer.
*/
private MutableColumn lookupOrCreateColumn(
final String catalogName,
final String schemaName,
final String tableName,
final String columnName) {
final boolean supportsCatalogs =
getRetrieverConnection().getDatabaseSystemParameters().isSupportsCatalogs();
MutableColumn column = null;
final MutableSchema schema = lookupSchema(supportsCatalogs ? catalogName : null, schemaName);
if (schema != null) {
MutableTable table = schema.getTable(tableName);
if (table != null) {
column = table.getColumn(columnName);
}
if (column == null) {
// Create the table, but do not add it to the schema
table = new MutableTable(schema, tableName);
column = new MutableColumn(table, columnName);
table.addColumn(column);
LOGGER.log(
Level.FINER,
String.format(
"Creating new column that is referenced by a foreign key: %s",
column.getFullName()));
}
}
return column;
}
private void createPrivileges(final MetadataResultSet results, final boolean privilegesForColumn)
throws SQLException {
while (results.next()) {
final String catalogName = quotedName(results.getString("TABLE_CAT"));
final String schemaName = quotedName(results.getString("TABLE_SCHEM"));
final String tableName = quotedName(results.getString("TABLE_NAME"));
final String columnName;
if (privilegesForColumn) {
columnName = quotedName(results.getString("COLUMN_NAME"));
} else {
columnName = null;
}
final MutableTable table = lookupTable(catalogName, schemaName, tableName);
if (table == null) {
continue;
}
final MutableColumn column = table.getColumn(columnName);
if (privilegesForColumn && column == null) {
continue;
}
final String privilegeName = results.getString("PRIVILEGE");
final String grantor = results.getString("GRANTOR");
final String grantee = results.getString("GRANTEE");
final boolean isGrantable = results.getBoolean("IS_GRANTABLE");
final MutablePrivilege<?> privilege;
if (privilegesForColumn) {
final MutablePrivilege<Column> columnPrivilege = column.getPrivilege(privilegeName);
if (columnPrivilege == null) {
privilege = new MutablePrivilege<>(column, privilegeName);
column.addPrivilege((MutablePrivilege<Column>) privilege);
} else {
privilege = columnPrivilege;
}
} else {
final MutablePrivilege<Table> tablePrivilege = table.getPrivilege(privilegeName);
if (tablePrivilege == null) {
privilege = new MutablePrivilege<>(table, privilegeName);
table.addPrivilege((MutablePrivilege<Table>) privilege);
} else {
privilege = tablePrivilege;
}
}
privilege.addGrant(grantor, grantee, isGrantable);
privilege.addAttributes(results.getAttributes());
if (privilegesForColumn) {
column.addPrivilege((MutablePrivilege<Column>) privilege);
} else {
table.addPrivilege((MutablePrivilege<Table>) privilege);
}
}
}
void retrieveTables(
final String catalogName,
final String schemaName,
final String tableNamePattern,
final TableType[] tableTypes,
final InclusionRule tableInclusionRule)
throws SQLException {
if (tableInclusionRule == null || tableInclusionRule.equals(InclusionRule.EXCLUDE_ALL)) {
return;
}
MetadataResultSet results = null;
try {
results =
new MetadataResultSet(
getMetaData()
.getTables(
unquotedName(catalogName),
unquotedName(schemaName),
tableNamePattern,
TableType.toStrings(tableTypes)));
while (results.next()) {
// "TABLE_CAT", "TABLE_SCHEM"
final String tableName = quotedName(results.getString("TABLE_NAME"));
LOGGER.log(Level.FINER, String.format("Retrieving table: %s.%s", schemaName, tableName));
final TableType tableType = results.getEnum("TABLE_TYPE", TableType.unknown);
final String remarks = results.getString("REMARKS");
final MutableSchema schema = lookupSchema(catalogName, schemaName);
if (schema == null) {
LOGGER.log(
Level.FINE, String.format("Cannot find schema, %s.%s", catalogName, schemaName));
continue;
}
final MutableTable table;
if (tableType == TableType.view) {
table = new MutableView(schema, tableName);
} else {
table = new MutableTable(schema, tableName);
}
if (tableInclusionRule.include(table.getFullName())) {
table.setType(tableType);
table.setRemarks(remarks);
schema.addTable(table);
}
}
} finally {
if (results != null) {
results.close();
}
}
}
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();
}
}
private MutableColumn lookupOrCreateColumn(
final MutableTable table, final String columnName, final boolean add) {
MutableColumn column = null;
if (table != null) {
column = table.getColumn(columnName);
}
if (column == null) {
column = new MutableColumn(table, columnName);
if (add) {
LOGGER.log(Level.FINER, String.format("Adding column to table: %s", column.getFullName()));
table.addColumn(column);
}
}
return column;
}
/**
* Rearrange the subarray A[p..r] in place.
*
* @param A the array to rearrange
* @param p the beginning index
* @param r the ending index
* @param t the Table to swap rows for
* @return the partition point
*/
private int partition(long[] A, int p, int r, MutableTable t) {
long x = A[p];
boolean xMissing = this.isValueMissing(p);
int i = p - 1;
int j = r + 1;
while (true) {
if (xMissing) {
j--;
do {
i++;
} while (!this.isValueMissing(i));
} else {
do {
j--;
} while (this.isValueMissing(j) || (A[j] > x));
do {
i++;
} while (!this.isValueMissing(i) && (A[i] < x));
}
if (i < j) {
if (t == null) this.swapRows(i, j);
else t.swapRows(i, j);
} else return j;
}
}
void retrievePrimaryKey(final MutableTable table) throws SQLException {
MetadataResultSet results = null;
try {
results =
new MetadataResultSet(
getMetaData()
.getPrimaryKeys(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getSchemaName()),
unquotedName(table.getName())));
MutablePrimaryKey primaryKey;
while (results.next()) {
// "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
final String columnName = quotedName(results.getString("COLUMN_NAME"));
final String primaryKeyName = quotedName(results.getString("PK_NAME"));
final int keySequence = Integer.parseInt(results.getString("KEY_SEQ"));
primaryKey = table.getPrimaryKey();
if (primaryKey == null) {
primaryKey = new MutablePrimaryKey(table, primaryKeyName);
}
// Register primary key information
final MutableColumn column = table.getColumn(columnName);
if (column != null) {
column.setPartOfPrimaryKey(true);
final MutableIndexColumn indexColumn = new MutableIndexColumn(primaryKey, column);
indexColumn.setSortSequence(IndexColumnSortSequence.ascending);
indexColumn.setIndexOrdinalPosition(keySequence);
//
primaryKey.addColumn(indexColumn);
}
table.setPrimaryKey(primaryKey);
}
} catch (final SQLException e) {
throw new SchemaCrawlerSQLException("Could not retrieve primary keys for table " + table, e);
} finally {
if (results != null) {
results.close();
}
}
}
private void retrieveForeignKeysUsingDatabaseMetadata(
final NamedObjectList<MutableTable> allTables) throws SchemaCrawlerSQLException {
final NamedObjectList<MutableForeignKey> foreignKeys = new NamedObjectList<>();
for (final MutableTable table : allTables) {
if (table instanceof View) {
continue;
}
final DatabaseMetaData metaData = getMetaData();
try (final MetadataResultSet results =
new MetadataResultSet(
metaData.getImportedKeys(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getName()),
unquotedName(table.getName()))); ) {
createForeignKeys(results, foreignKeys);
} catch (final SQLException e) {
throw new SchemaCrawlerSQLException(
"Could not retrieve foreign keys for table " + table, e);
}
// We need to get exported keys as well, since if only a single
// table is
// selected, we have not retrieved it's keys that are imported by
// other
// tables.
try (final MetadataResultSet results =
new MetadataResultSet(
metaData.getExportedKeys(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getName()),
unquotedName(table.getName()))); ) {
createForeignKeys(results, foreignKeys);
} catch (final SQLException e) {
throw new SchemaCrawlerSQLException(
"Could not retrieve foreign keys for table " + table, e);
}
}
}
void retrieveForeignKeys(final MutableTable table) throws SQLException {
final NamedObjectList<MutableForeignKey> foreignKeys = new NamedObjectList<MutableForeignKey>();
MetadataResultSet results;
final DatabaseMetaData metaData = getMetaData();
results =
new MetadataResultSet(
metaData.getImportedKeys(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getSchemaName()),
unquotedName(table.getName())));
createForeignKeys(results, foreignKeys);
results =
new MetadataResultSet(
metaData.getExportedKeys(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getSchemaName()),
unquotedName(table.getName())));
createForeignKeys(results, foreignKeys);
}
private void retrieveIndices1(final MutableTable table, final boolean unique)
throws SQLException {
MetadataResultSet results = null;
try {
results =
new MetadataResultSet(
getMetaData()
.getIndexInfo(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getSchemaName()),
unquotedName(table.getName()),
unique,
true /* approximate */));
createIndices(table, results);
} catch (final SQLException e) {
throw new SchemaCrawlerSQLException("Could not retrieve indices for table " + table, e);
} finally {
if (results != null) {
results.close();
}
}
}
void retrieveColumns(final MutableTable table, final InclusionRule columnInclusionRule)
throws SQLException {
MetadataResultSet results = null;
try {
results =
new MetadataResultSet(
getMetaData()
.getColumns(
unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getSchemaName()),
unquotedName(table.getName()),
null));
while (results.next()) {
// Get the "COLUMN_DEF" value first as it the Oracle drivers
// don't handle it properly otherwise.
// http://issues.apache.org/jira/browse/DDLUTILS-29?page=all
final String defaultValue = results.getString("COLUMN_DEF");
//
final String columnCatalogName = quotedName(results.getString("TABLE_CAT"));
final String schemaName = quotedName(results.getString("TABLE_SCHEM"));
final String tableName = quotedName(results.getString("TABLE_NAME"));
final String columnName = quotedName(results.getString("COLUMN_NAME"));
LOGGER.log(Level.FINER, String.format("Retrieving column: %s.%s", tableName, columnName));
MutableColumn column;
column = lookupOrCreateColumn(table, columnName, false /* add */);
final String columnFullName = column.getFullName();
// Note: If the table name contains an underscore character,
// this is a wildcard character. We need to do another check to
// see if the table name matches.
if (columnInclusionRule.include(columnFullName)
&& table.getName().equals(tableName)
&& belongsToSchema(table, columnCatalogName, schemaName)) {
column = lookupOrCreateColumn(table, columnName, true /* add */);
final int ordinalPosition = results.getInt("ORDINAL_POSITION", 0);
final int dataType = results.getInt("DATA_TYPE", 0);
final String typeName = results.getString("TYPE_NAME");
final int size = results.getInt("COLUMN_SIZE", 0);
final int decimalDigits = results.getInt("DECIMAL_DIGITS", 0);
final boolean isNullable =
results.getInt("NULLABLE", DatabaseMetaData.columnNullableUnknown)
== DatabaseMetaData.columnNullable;
final String remarks = results.getString("REMARKS");
column.setOrdinalPosition(ordinalPosition);
column.setType(
lookupOrCreateColumnDataType((MutableSchema) table.getSchema(), dataType, typeName));
column.setSize(size);
column.setDecimalDigits(decimalDigits);
column.setRemarks(remarks);
column.setNullable(isNullable);
if (defaultValue != null) {
column.setDefaultValue(defaultValue);
}
column.addAttributes(results.getAttributes());
table.addColumn(column);
}
}
} catch (final SQLException e) {
throw new SchemaCrawlerSQLException("Could not retrieve columns for table " + table, e);
} finally {
if (results != null) {
results.close();
}
}
}
@Test
public void tableEquals() {
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setShowStoredProcedures(true);
final Catalog catalog = testUtility.getCatalog(schemaCrawlerOptions);
assertNotNull("Could not obtain catalog", catalog);
assertTrue("Could not find any schemas", catalog.getSchemas().length > 0);
final Schema schema = catalog.getSchema("PUBLIC");
assertNotNull("Could not obtain schema", schema);
assertTrue("Could not find any tables", schema.getTables().length > 0);
assertTrue("Could not find any procedures", schema.getProcedures().length > 0);
// Try negative test
final Table table0 = schema.getTables()[0];
assertTrue("Could not find any columns", table0.getColumns().length > 0);
final MutableTable table1 =
new MutableTable(table0.getCatalogName(), table0.getSchemaName(), "Test Table 1");
final MutableTable table2 =
new MutableTable(table0.getCatalogName(), table0.getSchemaName(), "Test Table 2");
final PrimaryKey primaryKey = table0.getPrimaryKey();
table1.setPrimaryKey(primaryKey);
table2.setPrimaryKey(primaryKey);
for (final Column column : table0.getColumns()) {
table1.addColumn((MutableColumn) column);
table2.addColumn((MutableColumn) column);
}
for (final Index index : table0.getIndices()) {
table1.addIndex((MutableIndex) index);
table2.addIndex((MutableIndex) index);
}
for (final ForeignKey fk : table0.getForeignKeys()) {
table1.addForeignKey((MutableForeignKey) fk);
table2.addForeignKey((MutableForeignKey) fk);
}
for (final Trigger trigger : table0.getTriggers()) {
table1.addTrigger((MutableTrigger) trigger);
table2.addTrigger((MutableTrigger) trigger);
}
for (final Privilege privilege : table0.getPrivileges()) {
table1.addPrivilege((MutablePrivilege) privilege);
table2.addPrivilege((MutablePrivilege) privilege);
}
for (final CheckConstraint checkConstraint : table0.getCheckConstraints()) {
table1.addCheckConstraint((MutableCheckConstraint) checkConstraint);
table2.addCheckConstraint((MutableCheckConstraint) checkConstraint);
}
assertFalse("Tables should not be equal", table1.equals(table2));
}
private static void crawlTables(
final MutableCatalog catalog,
final RetrieverConnection retrieverConnection,
final SchemaCrawlerOptions options)
throws SchemaCrawlerException {
final SchemaInfoLevel infoLevel = options.getSchemaInfoLevel();
final boolean retrieveTables = infoLevel.isRetrieveTables();
if (!retrieveTables) {
LOGGER.log(Level.INFO, "Not retrieving tables, since this was not requested");
return;
}
final StopWatch stopWatch = new StopWatch("crawlTables");
LOGGER.log(Level.INFO, "Crawling tables");
final TableRetriever retriever;
final TableColumnRetriever columnRetriever;
final ForeignKeyRetriever fkRetriever;
final TableExtRetriever retrieverExtra;
try {
retriever = new TableRetriever(retrieverConnection, catalog);
columnRetriever = new TableColumnRetriever(retrieverConnection, catalog);
fkRetriever = new ForeignKeyRetriever(retrieverConnection, catalog);
retrieverExtra = new TableExtRetriever(retrieverConnection, catalog);
stopWatch.time(
"retrieveTables",
() -> {
for (final Schema schema : retriever.getSchemas()) {
retriever.retrieveTables(
schema.getCatalogName(),
schema.getName(),
options.getTableNamePattern(),
options.getTableTypes(),
options.getTableInclusionRule());
}
return null;
});
final NamedObjectList<MutableTable> allTables = catalog.getAllTables();
stopWatch.time(
"retrieveColumns",
() -> {
if (infoLevel.isRetrieveTableColumns()) {
columnRetriever.retrieveColumns(allTables, options.getColumnInclusionRule());
}
return null;
});
stopWatch.time(
"retrieveForeignKeys",
() -> {
if (infoLevel.isRetrieveForeignKeys()) {
if (infoLevel.isRetrieveTableColumns()) {
fkRetriever.retrieveForeignKeys(allTables);
}
} else {
LOGGER.log(
Level.WARNING,
"Foreign-keys are not being retrieved, so tables cannot be sorted using the natural sort order");
}
return null;
});
stopWatch.time(
"filterAndSortTables",
() -> {
// Filter the list of tables based on grep criteria, and
// parent-child relationships
final Predicate<Table> tableFilter = tableFilter(options);
((Reducible) catalog).reduce(Table.class, new TablesReducer(options, tableFilter));
// Sort the remaining tables
final TablesGraph tablesGraph = new TablesGraph(allTables);
tablesGraph.setTablesSortIndexes();
return null;
});
stopWatch.time(
"retrieveIndexes",
() -> {
LOGGER.log(Level.INFO, "Retrieving primary keys and indexes");
for (final MutableTable table : allTables) {
final boolean isView = table instanceof MutableView;
if (!isView && infoLevel.isRetrieveTableColumns()) {
retriever.retrievePrimaryKey(table);
if (infoLevel.isRetrieveIndexes()) {
retriever.retrieveIndexes(table, true);
retriever.retrieveIndexes(table, false);
//
table.replacePrimaryKey();
}
}
}
return null;
});
stopWatch.time(
"retrieveTableConstraintInformation",
() -> {
if (infoLevel.isRetrieveTableConstraintInformation()) {
retrieverExtra.retrieveTableConstraintInformation();
}
return null;
});
stopWatch.time(
"retrieveTriggerInformation",
() -> {
if (infoLevel.isRetrieveTriggerInformation()) {
retrieverExtra.retrieveTriggerInformation();
}
return null;
});
stopWatch.time(
"retrieveViewInformation",
() -> {
if (infoLevel.isRetrieveViewInformation()) {
retrieverExtra.retrieveViewInformation();
}
return null;
});
stopWatch.time(
"retrieveTableDefinitions",
() -> {
if (infoLevel.isRetrieveTableDefinitionsInformation()) {
retrieverExtra.retrieveTableDefinitions();
}
return null;
});
stopWatch.time(
"retrieveIndexInformation",
() -> {
if (infoLevel.isRetrieveIndexInformation()) {
retrieverExtra.retrieveIndexInformation();
}
return null;
});
stopWatch.time(
"retrieveAdditionalTableAttributes",
() -> {
if (infoLevel.isRetrieveAdditionalTableAttributes()) {
retrieverExtra.retrieveAdditionalTableAttributes();
}
return null;
});
stopWatch.time(
"retrieveTablePrivileges",
() -> {
if (infoLevel.isRetrieveTablePrivileges()) {
retrieverExtra.retrieveTablePrivileges();
}
return null;
});
stopWatch.time(
"retrieveAdditionalColumnAttributes",
() -> {
if (infoLevel.isRetrieveAdditionalColumnAttributes()) {
retrieverExtra.retrieveAdditionalColumnAttributes();
}
return null;
});
stopWatch.time(
"retrieveTableColumnPrivileges",
() -> {
if (infoLevel.isRetrieveTableColumnPrivileges()) {
retrieverExtra.retrieveTableColumnPrivileges();
}
return null;
});
LOGGER.log(Level.INFO, stopWatch.toString());
} catch (final Exception e) {
if (e instanceof SchemaCrawlerSQLException) {
throw new SchemaCrawlerException(e.getMessage(), e.getCause());
} else if (e instanceof SchemaCrawlerException) {
throw (SchemaCrawlerException) e;
} else {
throw new SchemaCrawlerException("Exception retrieving table information", e);
}
}
}
/**
* 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);
}
}
/**
* Retrieves a trigger information from the database, in the INFORMATION_SCHEMA format.
*
* @throws SQLException On a SQL exception
*/
void retrieveTriggerInformation() throws SQLException {
final InformationSchemaViews informationSchemaViews =
getRetrieverConnection().getInformationSchemaViews();
if (!informationSchemaViews.hasTriggerSql()) {
LOGGER.log(Level.FINE, "Trigger definition SQL statement was not provided");
return;
}
final String triggerInformationSql = informationSchemaViews.getTriggersSql();
final Connection connection = getDatabaseConnection();
try (final Statement statement = connection.createStatement();
final MetadataResultSet results =
new MetadataResultSet(statement.executeQuery(triggerInformationSql)); ) {
while (results.next()) {
final String catalogName = quotedName(results.getString("TRIGGER_CATALOG"));
final String schemaName = quotedName(results.getString("TRIGGER_SCHEMA"));
final String triggerName = quotedName(results.getString("TRIGGER_NAME"));
LOGGER.log(Level.FINER, "Retrieving trigger: " + triggerName);
// "EVENT_OBJECT_CATALOG", "EVENT_OBJECT_SCHEMA"
final String tableName = results.getString("EVENT_OBJECT_TABLE");
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 EventManipulationType eventManipulationType =
results.getEnum("EVENT_MANIPULATION", EventManipulationType.unknown);
final int actionOrder = results.getInt("ACTION_ORDER", 0);
final String actionCondition = results.getString("ACTION_CONDITION");
final String actionStatement = results.getString("ACTION_STATEMENT");
final ActionOrientationType actionOrientation =
results.getEnum("ACTION_ORIENTATION", ActionOrientationType.unknown);
String conditionTimingString = results.getString("ACTION_TIMING");
if (conditionTimingString == null) {
conditionTimingString = results.getString("CONDITION_TIMING");
}
final ConditionTimingType conditionTiming =
ConditionTimingType.valueOfFromValue(conditionTimingString);
MutableTrigger trigger = table.lookupTrigger(triggerName);
if (trigger == null) {
trigger = new MutableTrigger(table, triggerName);
}
trigger.setEventManipulationType(eventManipulationType);
trigger.setActionOrder(actionOrder);
trigger.appendActionCondition(actionCondition);
trigger.appendActionStatement(actionStatement);
trigger.setActionOrientation(actionOrientation);
trigger.setConditionTiming(conditionTiming);
trigger.addAttributes(results.getAttributes());
// Add trigger to the table
table.addTrigger(trigger);
}
} catch (final Exception e) {
LOGGER.log(Level.WARNING, "Could not retrieve triggers", e);
}
}
void retrieveTables(
final Schema schema,
final String tableNamePattern,
final Collection<String> tableTypes,
final InclusionRule tableInclusionRule)
throws SQLException {
requireNonNull(schema, "No schema provided");
final InclusionRuleFilter<Table> tableFilter =
new InclusionRuleFilter<>(tableInclusionRule, false);
if (tableFilter.isExcludeAll()) {
LOGGER.log(Level.INFO, "Not retrieving tables, since this was not requested");
return;
}
final Optional<Schema> schemaOptional = catalog.lookupSchema(schema.getFullName());
if (!schemaOptional.isPresent()) {
LOGGER.log(
Level.INFO,
new StringFormat(
"Cannot locate schema, so not retrieving tables for schema: %s", schema));
return;
}
LOGGER.log(Level.INFO, new StringFormat("Retrieving tables for schema: %s", schema));
final TableTypes supportedTableTypes = getRetrieverConnection().getTableTypes();
final String[] filteredTableTypes = supportedTableTypes.filterUnknown(tableTypes);
LOGGER.log(
Level.FINER,
new StringFormat(
"Retrieving table types: %s",
filteredTableTypes == null ? "<<all>>" : Arrays.asList(filteredTableTypes)));
final String catalogName = schema.getCatalogName();
final String schemaName = schema.getName();
try (final MetadataResultSet results =
new MetadataResultSet(
getMetaData()
.getTables(
unquotedName(catalogName),
unquotedName(schemaName),
tableNamePattern,
filteredTableTypes)); ) {
results.setDescription("retrieveTables");
while (results.next()) {
// "TABLE_CAT", "TABLE_SCHEM"
final String tableName = quotedName(results.getString("TABLE_NAME"));
LOGGER.log(Level.FINE, String.format("Retrieving table: %s.%s", schema, tableName));
final String tableTypeString = results.getString("TABLE_TYPE");
final String remarks = results.getString("REMARKS");
final TableType tableType =
supportedTableTypes.lookupTableType(tableTypeString).orElse(TableType.UNKNOWN);
if (tableType.equals(TableType.UNKNOWN)) {
LOGGER.log(
Level.FINE,
new StringFormat(
"Unknown table type, %s, for %s.%s", tableTypeString, schema, tableName));
}
final MutableTable table;
if (tableType.isView()) {
table = new MutableView(schema, tableName);
} else {
table = new MutableTable(schema, tableName);
}
if (tableFilter.test(table)) {
table.setTableType(tableType);
table.setRemarks(remarks);
catalog.addTable(table);
}
}
}
}
