schemacrawler.crawl.TableRetriever Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of schemacrawler Show documentation
Show all versions of schemacrawler Show documentation
SchemaCrawler is an open-source Java API that makes working with database metadata as easy as working with plain old Java objects. SchemaCrawler is also a database schema discovery and comprehension, and schema documentation tool. You can search for database schema objects using regular expressions, and output the schema and data in a readable text format. The output is designed to be diff-ed against other database schemas.
/*
*
* SchemaCrawler
* http://sourceforge.net/projects/schemacrawler
* Copyright (c) 2000-2015, Sualeh Fatehi.
*
* This library is free software; you can redistribute it and/or modify it under the terms
* of the GNU Lesser General Public License as published by the Free Software Foundation;
* either version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
* Boston, MA 02111-1307, USA.
*
*/
package schemacrawler.crawl;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;
import schemacrawler.filter.InclusionRuleFilter;
import schemacrawler.schema.Column;
import schemacrawler.schema.ForeignKeyDeferrability;
import schemacrawler.schema.ForeignKeyUpdateRule;
import schemacrawler.schema.IndexColumnSortSequence;
import schemacrawler.schema.IndexType;
import schemacrawler.schema.Schema;
import schemacrawler.schema.SchemaReference;
import schemacrawler.schema.Table;
import schemacrawler.schema.TableType;
import schemacrawler.schemacrawler.InclusionRule;
import schemacrawler.schemacrawler.SchemaCrawlerSQLException;
import sf.util.Utility;
/**
* A retriever uses database metadata to get the details about the
* database tables.
*
* @author Sualeh Fatehi
*/
final class TableRetriever
extends AbstractRetriever
{
private static final Logger LOGGER = Logger.getLogger(TableRetriever.class
.getName());
TableRetriever(final RetrieverConnection retrieverConnection,
final MutableCatalog catalog)
throws SQLException
{
super(retrieverConnection, catalog);
}
void retrieveColumns(final MutableTable table,
final InclusionRule columnInclusionRule)
throws SQLException
{
final InclusionRuleFilter columnFilter = new InclusionRuleFilter<>(columnInclusionRule,
true);
if (columnFilter.isExcludeAll())
{
LOGGER.log(Level.INFO,
"Not retrieving columns, since this was not requested");
return;
}
try (final MetadataResultSet results = new MetadataResultSet(getMetaData()
.getColumns(unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getName()),
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 */);
// 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 (columnFilter.test(column) && 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 boolean isAutoIncremented = results
.getBoolean("IS_AUTOINCREMENT");
final boolean isGenerated = results.getBoolean("IS_GENERATEDCOLUMN");
final String remarks = results.getString("REMARKS");
column.setOrdinalPosition(ordinalPosition);
column.setColumnDataType(lookupOrCreateColumnDataType(table
.getSchema(), dataType, typeName));
column.setSize(size);
column.setDecimalDigits(decimalDigits);
column.setNullable(isNullable);
column.setAutoIncremented(isAutoIncremented);
column.setGenerated(isGenerated);
column.setRemarks(remarks);
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);
}
}
void retrieveForeignKeys(final MutableTable table)
throws SQLException
{
final NamedObjectList foreignKeys = new NamedObjectList<>();
MetadataResultSet results;
final DatabaseMetaData metaData = getMetaData();
try
{
results = new MetadataResultSet(metaData.getImportedKeys(unquotedName(table
.getSchema()
.getCatalogName()),
unquotedName(table
.getSchema()
.getName()),
unquotedName(table
.getName())));
createForeignKeys(results, foreignKeys);
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 forign keys for table "
+ table,
e);
}
}
void retrieveIndices(final MutableTable table, final boolean unique)
throws SQLException
{
SQLException sqlEx = null;
try
{
retrieveIndices1(table, unique);
}
catch (final SQLException e)
{
sqlEx = e;
}
if (sqlEx != null)
{
try
{
sqlEx = null;
retrieveIndices2(table, unique);
}
catch (final SQLException e)
{
sqlEx = e;
}
}
if (sqlEx != null)
{
throw sqlEx;
}
}
void retrievePrimaryKey(final MutableTable table)
throws SQLException
{
try (final MetadataResultSet results = new MetadataResultSet(getMetaData()
.getPrimaryKeys(unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getName()),
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.markAsPartOfPrimaryKey();
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);
}
}
void retrieveTables(final String catalogName,
final String schemaName,
final String tableNamePattern,
final Collection tableTypes,
final InclusionRule tableInclusionRule)
throws SQLException
{
final InclusionRuleFilter tableFilter = new InclusionRuleFilter<>(tableInclusionRule,
false);
if (tableFilter.isExcludeAll())
{
LOGGER.log(Level.INFO,
"Not retrieving tables, since this was not requested");
return;
}
final TableTypes supportedTableTypes = getRetrieverConnection()
.getTableTypes();
final String[] filteredTableTypes = supportedTableTypes
.filterUnknown(tableTypes);
LOGGER.log(Level.FINER, String
.format("Retrieving table types: %s",
filteredTableTypes == null? "<>": Arrays
.asList(filteredTableTypes)));
try (final MetadataResultSet results = new MetadataResultSet(getMetaData()
.getTables(unquotedName(catalogName),
unquotedName(schemaName),
tableNamePattern,
filteredTableTypes));)
{
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 String tableTypeString = results.getString("TABLE_TYPE");
final String remarks = results.getString("REMARKS");
final SchemaReference schemaReference = new SchemaReference(catalogName,
schemaName);
final Schema schema = catalog.getSchema(schemaReference.getFullName());
final TableType tableType = supportedTableTypes
.lookupTableType(tableTypeString);
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);
}
}
}
}
private void createForeignKeys(final MetadataResultSet results,
final NamedObjectList 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);
// Make a direct connection between the two columns
if (pkColumn != null && fkColumn != null)
{
if (Utility.isBlank(foreignKeyName))
{
foreignKeyName = String.format("SC_%s_%s",
Integer.toHexString(pkColumn
.getFullName().hashCode())
.toUpperCase(),
Integer.toHexString(fkColumn
.getFullName().hashCode())
.toUpperCase());
}
MutableForeignKey foreignKey = foreignKeys.lookup(foreignKeyName);
if (foreignKey == null)
{
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 (fkColumn instanceof ColumnPartial)
{
((ColumnPartial) fkColumn).setReferencedColumn(pkColumn);
((TablePartial) fkColumn.getParent()).addForeignKey(foreignKey);
}
if (pkColumn instanceof MutableColumn)
{
((MutableTable) pkColumn.getParent()).addForeignKey(foreignKey);
}
else if (pkColumn instanceof ColumnPartial)
{
((TablePartial) pkColumn.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"));
LOGGER.log(Level.FINER, String.format("Retrieving index: %s.%s",
table.getFullName(),
indexName));
// Work-around PostgreSQL JDBC driver bugs by unquoting column
// names first
// #3480 -
// http://www.postgresql.org/message-id/[email protected]
// #6253 -
// http://www.postgresql.org/message-id/[email protected]
final String columnName = quotedName(unquotedName(results
.getString("COLUMN_NAME")));
if (Utility.isBlank(columnName))
{
continue;
}
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)
{
if (Utility.isBlank(indexName))
{
indexName = String.format("SC_%s",
Integer.toHexString(column.getFullName()
.hashCode()).toUpperCase());
}
MutableIndex index = table.getIndex(indexName);
if (index == null)
{
index = new MutableIndex(table, indexName);
table.addIndex(index);
}
column.markAsPartOfIndex();
final MutableIndexColumn indexColumn = new MutableIndexColumn(index,
column);
indexColumn.setIndexOrdinalPosition(ordinalPosition);
indexColumn.setSortSequence(sortSequence);
//
index.addColumn(indexColumn);
index.setUnique(uniqueIndex);
index.setIndexType(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;
}
/**
* 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 Column lookupOrCreateColumn(final String catalogName,
final String schemaName,
final String tableName,
final String columnName)
{
final boolean supportsCatalogs = getRetrieverConnection()
.isSupportsCatalogs();
Column column = null;
final Schema schema = new SchemaReference(supportsCatalogs? catalogName
: null,
schemaName);
if (schema != null)
{
Table table = catalog
.getTable(new SchemaReference(catalogName, schemaName), tableName);
if (table != null)
{
column = table.getColumn(columnName);
}
if (column == null)
{
// Create the table and column, but do not add it to the schema
table = new TablePartial(schema, tableName);
column = new ColumnPartial(table, columnName);
((TablePartial) table).addColumn(column);
LOGGER
.log(Level.FINER,
String
.format("Creating column reference for a column that is referenced by a foreign key: %s",
column.getFullName()));
}
}
return column;
}
private void retrieveIndices1(final MutableTable table, final boolean unique)
throws SQLException
{
try (final MetadataResultSet results = new MetadataResultSet(getMetaData()
.getIndexInfo(unquotedName(table.getSchema().getCatalogName()),
unquotedName(table.getSchema().getName()),
unquotedName(table.getName()),
unique,
true/* approximate */));)
{
createIndices(table, results);
}
catch (final SQLException e)
{
throw new SchemaCrawlerSQLException("Could not retrieve indices for table "
+ table,
e);
}
}
private void retrieveIndices2(final MutableTable table, final boolean unique)
throws SQLException
{
try (final MetadataResultSet results = new MetadataResultSet(getMetaData()
.getIndexInfo(null, null, table.getName(), unique, true/* approximate */));)
{
createIndices(table, results);
}
catch (final SQLException e)
{
throw new SchemaCrawlerSQLException("Could not retrieve indices for table "
+ table,
e);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy