/* * * SchemaCrawler * http://sourceforge.net/projects/schemacrawler * Copyright (c) 2000-2009, 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.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Collection; import java.util.HashMap; import java.util.Locale; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import schemacrawler.schema.ForeignKeyDeferrability; import schemacrawler.schema.ForeignKeyUpdateRule; import schemacrawler.schema.IndexColumnSortSequence; import schemacrawler.schema.IndexType; import schemacrawler.schema.TableType; import schemacrawler.schemacrawler.InclusionRule; import schemacrawler.schemacrawler.InformationSchemaViews; /** * 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) throws SQLException { super(retrieverConnection); } /** * Retrieves a list of columns from the database, for the table * specified. * * @param table * Table for which data is required. * @throws SQLException * On a SQL exception */ void retrieveColumns(final NamedObjectList<MutableTable> tables, final MutableTable table, final InclusionRule columnInclusionRule, final ColumnDataTypes columnDataTypes) throws SQLException { final String schemaName = table.getSchemaName(); final MetadataResultSet results = new MetadataResultSet(getRetrieverConnection() .getMetaData().getColumns(getRetrieverConnection().getCatalogName(), schemaName, table.getName(), null)); try { 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 tableName = results.getString("TABLE_NAME"); final String columnName = results.getString(COLUMN_NAME); final MutableColumn column = lookupOrCreateColumn(tables, schemaName, tableName, columnName, false); 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)) { LOGGER.log(Level.FINEST, "Retrieving column: " + columnName); 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(columnDataTypes.lookupOrCreateColumnDataType(column, 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); } } } finally { results.close(); } } void retrieveForeignKeys(final NamedObjectList<MutableTable> tables, final MutableTable table) throws SQLException { final String schemaName = table.getSchemaName(); final Map<String, MutableForeignKey> foreignKeysMap = new HashMap<String, MutableForeignKey>(); MetadataResultSet results; final String catalogName = getRetrieverConnection().getCatalogName(); final DatabaseMetaData metaData = getRetrieverConnection().getMetaData(); results = new MetadataResultSet(metaData.getImportedKeys(catalogName, schemaName, table.getName())); createForeignKeys(results, tables, table, foreignKeysMap); results = new MetadataResultSet(metaData.getExportedKeys(catalogName, schemaName, table.getName())); createForeignKeys(results, tables, table, foreignKeysMap); final Collection<MutableForeignKey> foreignKeyCollection = foreignKeysMap .values(); for (final MutableForeignKey foreignKey: foreignKeyCollection) { table.addForeignKey(foreignKey); } } void retrieveIndices(final MutableTable table, final boolean unique) throws SQLException { final Map<String, MutableIndex> indicesMap = new HashMap<String, MutableIndex>(); final InformationSchemaViews informationSchemaViews = getRetrieverConnection() .getInformationSchemaViews(); MetadataResultSet results = null; if (informationSchemaViews.hasIndexInfoSql()) { final String indexInfoSql = informationSchemaViews.getIndexInfo() .getQueryForTable(table); LOGGER.log(Level.FINE, "Using getIndexInfo SQL:\n" + indexInfoSql); final Connection connection = getDatabaseConnection(); final Statement statement = connection.createStatement(); results = new MetadataResultSet(statement.executeQuery(indexInfoSql)); } else { LOGGER.log(Level.FINE, "Using getIndexInfo()"); results = new MetadataResultSet(getRetrieverConnection().getMetaData() .getIndexInfo(getRetrieverConnection().getCatalogName(), table.getSchemaName(), table.getName(), unique, true/* approximate */)); } createIndices(results, table, indicesMap); final Collection<MutableIndex> indexCollection = indicesMap.values(); for (final MutableIndex index: indexCollection) { if (index.getColumns().length > 0) { table.addIndex(index); } } } void retrievePrimaryKeys(final MutableTable table) throws SQLException { final String tableName = table.getName(); // final String getCatalog() = table.getCatalogName(); final String schema = table.getSchemaName(); ResultSet results = null; try { results = getRetrieverConnection().getMetaData() .getPrimaryKeys(getRetrieverConnection().getCatalogName(), schema, tableName); MutablePrimaryKey primaryKey = null; while (results.next()) { if (primaryKey == null) { final String primaryKeyName = results.getString("PK_NAME"); primaryKey = new MutablePrimaryKey(primaryKeyName, table); } final String columnName = results.getString(COLUMN_NAME); final int keySequence = Integer.parseInt(results.getString(KEY_SEQ)); // register primary key information final MutableColumn column = table.lookupColumn(columnName); if (column != null) { column.setPartOfPrimaryKey(true); final MutableIndexColumn indexColumn = new MutableIndexColumn(primaryKey, column); indexColumn.setSortSequence(IndexColumnSortSequence.ascending); // primaryKey.addColumn(keySequence, indexColumn); } } table.setPrimaryKey(primaryKey); } finally { if (results != null) { results.close(); } } } /** * Retrieves table metadata according to the parameters specified. No * column metadata is retrieved, for reasons of efficiency. * * @param tableTypes * Array of table types * @param tablePatternInclude * Table name pattern for table * @param useRegExpPattern * True is the table name pattern is a regular expression; * false if the table name pattern is the JDBC pattern * @return A list of tables in the database that matech the pattern * @throws SQLException * On a SQL exception */ NamedObjectList<MutableTable> retrieveTables(final TableType[] tableTypes, final InclusionRule tableInclusionRule) throws SQLException { final NamedObjectList<MutableTable> tables = new NamedObjectList<MutableTable>(NamedObjectSort.alphabetical); final String catalogName = getRetrieverConnection().getCatalogName(); final ResultSet results = getRetrieverConnection().getMetaData() .getTables(catalogName, getRetrieverConnection().getSchemaPattern(), "%", TableType.toStrings(tableTypes)); try { results.setFetchSize(FETCHSIZE); } catch (final NullPointerException e) { // Need this catch for the JDBC/ ODBC driver LOGGER.log(Level.WARNING, "", e); } try { while (results.next()) { // final String catalogName = results.getString("TABLE_CAT"); String schema = results.getString("TABLE_SCHEM"); if (schema == null) { schema = ""; } final String tableName = results.getString(TABLE_NAME); LOGGER.log(Level.FINEST, "Retrieving table: " + tableName); final TableType tableType = TableType.valueOf(results .getString("TABLE_TYPE").toLowerCase(Locale.ENGLISH)); final String remarks = results.getString(REMARKS); if (tableInclusionRule.include(tableName)) { final MutableTable table; if (tableType == TableType.view) { table = new MutableView(catalogName, schema, tableName); } else { table = new MutableTable(catalogName, schema, tableName); } table.setType(tableType); table.setRemarks(remarks); tables.add(table); } } } finally { results.close(); } return tables; } /** * @param results * @param tablesMap * @param table * @param foreignKeysMap * @throws SQLException */ private void createForeignKeys(final MetadataResultSet results, final NamedObjectList<MutableTable> tables, final MutableTable table, final Map<String, MutableForeignKey> foreignKeysMap) throws SQLException { try { while (results.next()) { String foreignKeyName = results.getString("FK_NAME"); if (foreignKeyName == null || foreignKeyName.length() == 0) { foreignKeyName = UNKNOWN; } MutableForeignKey foreignKey = foreignKeysMap.get(foreignKeyName); if (foreignKey == null) { foreignKey = new MutableForeignKey(table.getCatalogName(), table .getSchemaName(), foreignKeyName); foreignKeysMap.put(foreignKeyName, foreignKey); } final String pkTableSchema = results.getString("PKTABLE_SCHEM"); final String pkTableName = results.getString("PKTABLE_NAME"); final String pkColumnName = results.getString("PKCOLUMN_NAME"); final String fkTableName = results.getString("FKTABLE_NAME"); final String fkColumnName = 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 MutableColumn pkColumn = lookupOrCreateColumn(tables, pkTableSchema, pkTableName, pkColumnName, true); final MutableColumn fkColumn = lookupOrCreateColumn(tables, pkTableSchema, fkTableName, fkColumnName, true); // Make a direct connection between the two columns fkColumn.setReferencedColumn(pkColumn); foreignKey.addColumnPair(keySequence, pkColumn, fkColumn); foreignKey.setUpdateRule(ForeignKeyUpdateRule.valueOf(updateRule)); foreignKey.setDeleteRule(ForeignKeyUpdateRule.valueOf(deleteRule)); foreignKey.setDeferrability(ForeignKeyDeferrability .valueOf(deferrability)); foreignKey.addAttributes(results.getAttributes()); } } finally { results.close(); } } private void createIndices(final MetadataResultSet results, final MutableTable table, final Map<String, MutableIndex> indicesMap) throws SQLException { try { while (results.next()) { String indexName = results.getString("INDEX_NAME"); if (indexName == null || indexName.length() == 0) { indexName = UNKNOWN; } MutableIndex index = indicesMap.get(indexName); if (index == null) { index = new MutableIndex(table, indexName); indicesMap.put(indexName, index); } final String columnName = results.getString(COLUMN_NAME); if (columnName == null || columnName.trim().length() == 0) { 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 String sortSequence = results.getString("ASC_OR_DESC"); final int cardinality = results.getInt("CARDINALITY", 0); final int pages = results.getInt("PAGES", 0); final MutableColumn column = table.lookupColumn(columnName); if (column != null) { column.setPartOfUniqueIndex(uniqueIndex); final MutableIndexColumn indexColumn = new MutableIndexColumn(index, column); indexColumn.setIndexOrdinalPosition(ordinalPosition); indexColumn.setSortSequence(IndexColumnSortSequence .valueOfFromCode(sortSequence)); // index.addColumn(ordinalPosition, 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 NamedObjectList<MutableTable> tables, final String schemaName, final String tableName, final String columnName, final boolean add) { final String catalogName = getRetrieverConnection().getCatalogName(); MutableColumn column = null; MutableTable table = tables.lookup(catalogName, schemaName, tableName); if (table != null) { column = table.lookupColumn(columnName); } else { table = new MutableTable(catalogName, schemaName, tableName); if (add) { tables.add(table); } } if (column == null) { column = new MutableColumn(table, columnName); if (add) { table.addColumn(column); } } return column; } }