/** * 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(); } }