/**
* 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);
}
}
}
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 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;
}
/**
* Retrieves additional column attributes from the database.
*
* @throws SQLException On a SQL exception
*/
void retrieveAdditionalColumnAttributes() throws SQLException {
final InformationSchemaViews informationSchemaViews =
getRetrieverConnection().getInformationSchemaViews();
if (!informationSchemaViews.hasAdditionalColumnAttributesSql()) {
LOGGER.log(Level.FINE, "Additional column attributes SQL statement was not provided");
return;
}
final String columnAttributesSql = informationSchemaViews.getAdditionalColumnAttributesSql();
final Connection connection = getDatabaseConnection();
try (final Statement statement = connection.createStatement();
final MetadataResultSet results =
new MetadataResultSet(statement.executeQuery(columnAttributesSql)); ) {
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"));
final String columnName = quotedName(results.getString("COLUMN_NAME"));
LOGGER.log(Level.FINER, "Retrieving additional column attributes: " + columnName);
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 MutableColumn column = table.getColumn(columnName);
if (column == null) {
LOGGER.log(
Level.FINE,
String.format(
"Cannot find column, %s.%s.%s.%s",
catalogName, schemaName, tableName, columnName));
continue;
}
column.addAttributes(results.getAttributes());
}
} catch (final Exception e) {
LOGGER.log(Level.WARNING, "Could not retrieve additional column attributes", e);
}
}
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();
}
}
}
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();
}
}
}
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();
}
}
