TableRetriever.java

/* 
 *
 * SchemaCrawler
 * http://sourceforge.net/projects/schemacrawler
 * Copyright (c) 2000-2012, 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.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.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 MutableDatabase database)
    throws SQLException
  {
    super(retrieverConnection, database);
  }

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

  }

  void retrieveForeignKeys(final MutableTable table)
    throws SQLException
  {

    final NamedObjectList<MutableForeignKey> foreignKeys = new NamedObjectList<MutableForeignKey>();
    MetadataResultSet results;

    final DatabaseMetaData metaData = getMetaData();
    try
    {
      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);
    }
    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
  {
    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 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 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;
  }

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

  }

  private void retrieveIndices2(final MutableTable table, final boolean unique)
    throws SQLException
  {

    MetadataResultSet results = null;
    try
    {
      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);
    }
    finally
    {
      if (results != null)
      {
        results.close();
      }
    }

  }

}