com.vecna.dbDiff.builder.RelationalDatabaseBuilderImpl Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of dbDiff Show documentation
DB Diff is a database schema comparison tool
The newest version!
/**
 * Copyright 2011 Vecna Technologies, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you
 * may not use this file except in compliance with the License.  You may
 * obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
 * implied.  See the License for the specific language governing
 * permissions and limitations under the License.
 */

package com.vecna.dbDiff.builder;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.Future;

import com.google.common.base.Function;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.Collections2;
import com.google.common.collect.Lists;
import com.google.common.collect.Multimap;
import com.vecna.dbDiff.jdbc.MetadataFactory;
import com.vecna.dbDiff.model.CatalogSchema;
import com.vecna.dbDiff.model.ColumnType;
import com.vecna.dbDiff.model.TableType;
import com.vecna.dbDiff.model.db.Column;
import com.vecna.dbDiff.model.db.ForeignKey;
import com.vecna.dbDiff.model.relationalDb.InconsistentSchemaException;
import com.vecna.dbDiff.model.relationalDb.RelationalDatabase;
import com.vecna.dbDiff.model.relationalDb.RelationalIndex;
import com.vecna.dbDiff.model.relationalDb.RelationalTable;

/**
 * Builds a {@link RelationalDatabase} representation of a live database schema.
 *
 * @author [email protected]
 * @author [email protected]
 */
public class RelationalDatabaseBuilderImpl implements RelationalDatabaseBuilder {
  private final MetadataFactory m_metadataFactory;
  private ExecutorService m_executor = new ForkJoinPool();

  /**
   * Execute multiple tasks in parallel (scaling to the number of available cores). If an exception is thrown by one of the tasks, it is converted as specified below.
   * @param  task return type.
   * @param tasks tasks to execute.
   * @throws RelationalDatabaseReadException if one of the tasks throws a {@link SQLException} or a {@link RelationalDatabaseReadException}.
   * @throws InconsistentSchemaException if one of the tasks throws an {@link InconsistentSchemaException}.
   * @throws RuntimeException if one of the tasks throws any other exception.
   */
  private  void runInParallel(Collection> tasks) throws RelationalDatabaseReadException, InconsistentSchemaException, RuntimeException {
    Collection> futures;
    try {
      futures = m_executor.invokeAll(tasks);
    } catch (InterruptedException e) {
      throw new IllegalStateException(e);
    }

    for (Future future : futures) {
      try {
        future.get();
      } catch (ExecutionException e) {
        Throwable cause = e.getCause();
        if (cause instanceof RuntimeException) {
          throw (RuntimeException) cause;
        } else if (cause instanceof SQLException) {
          throw new RelationalDatabaseReadException(cause);
        } else {
          throw new RuntimeException(e);
        }
      } catch (InterruptedException e) {
        throw new IllegalStateException(e);
      }
    }
  }

  /**
   * Constructor that sets metadata based on a JDBC connection
   * @param metadataFactory a {@link MetadataFactory}.
   */
  public RelationalDatabaseBuilderImpl(MetadataFactory metadataFactory) {
    m_metadataFactory = metadataFactory;
  }

  /**
   * Retrieve all tables from a schema.
   * @param catalogSchema catalog/schema.
   * @return the tables.
   * @throws SQLException if thrown by the jdbc driver.
   */
  private List getTables(final CatalogSchema catalogSchema) throws SQLException {
    // Get the ResultSet of tables
    String[] tableTypes = {TableType.TABLE.name()};
    ResultSet rs = doGetTablesQuery(catalogSchema, tableTypes);

    // Build a set of Tables
    List tables = new ArrayList();

    while (rs.next()) {
      RelationalTable table = new RelationalTable(new CatalogSchema(rs.getString(1), rs.getString(2)), rs.getString(3));

      table.setType(rs.getString(4));
      table.setTypeName(rs.getString(5));

      tables.add(table);
    }

    return tables;
  }

  /**
   * Performs a metaData.getTables() query.
   * @param catalogSchema the desired catalog and schema names.
   * @param tableTypes the desired table types, specific for the particular implementation.
   * @return The ResultSet of the getTables() call.
   * @throws SQLException if thrown by the jdbc driver.
   */
  protected ResultSet doGetTablesQuery(CatalogSchema catalogSchema, String[] tableTypes) throws SQLException {
    return m_metadataFactory.getMetadata().getTables(catalogSchema.getCatalog(), catalogSchema.getSchema(), null, tableTypes);
  }

  /**
   * Retrieve column information for a table.
   * @param table the table.
   * @return ordered list of columns.
   * @throws SQLException if thrown by the jdbc driver.
   */
  private List getColumns(RelationalTable table) throws SQLException {
    ResultSet columnResultSet = m_metadataFactory.getMetadata().getColumns(table.getCatalogSchema().getCatalog(), table.getCatalogSchema().getSchema(), table.getName(), null);

    List columns = new LinkedList();
    while (columnResultSet.next()) {
      Column column = new Column(columnResultSet.getString(1), columnResultSet.getString(2),
                                 columnResultSet.getString(4), columnResultSet.getString(3));

      column.setColumnType(new ColumnType(columnResultSet.getInt(5), columnResultSet.getString(6)));
      column.setColumnSize(columnResultSet.getInt(7));

      //Nullability
      int nullable = columnResultSet.getInt(11);
      column.setIsNullable((DatabaseMetaData.columnNullable == nullable ? true
          : (DatabaseMetaData.columnNoNulls == nullable ? false : null)));

      column.setDefault(columnResultSet.getString(13));
      column.setOrdinal(columnResultSet.getInt(17));
      columns.add(column);
    }
    return columns;
  }

  /**
   * Retrieve foreign keys for a table.
   * @param table table.
   * @return ordered list of foreign keys.
   * @throws SQLException if thrown by the jdbc driver.
   */
  private List getForeignKeys(RelationalTable table) throws SQLException {
    ResultSet fkResultSet = m_metadataFactory.getMetadata().getImportedKeys(table.getCatalogSchema().getCatalog(), table.getCatalogSchema().getSchema(), table.getName());
    List fks = new LinkedList();
    while (fkResultSet.next()) {
      ForeignKey fk = new ForeignKey();
      fk.setFkName(fkResultSet.getString(12));

      fk.setFkCatalogSchema(new CatalogSchema(fkResultSet.getString(5), fkResultSet.getString(6)));
      fk.setFkTable(fkResultSet.getString(7));
      fk.setFkColumn(fkResultSet.getString(8));

      fk.setPkCatalogSchema(new CatalogSchema(fkResultSet.getString(1), fkResultSet.getString(2)));
      fk.setPkTable(fkResultSet.getString(3));
      fk.setPkColumn(fkResultSet.getString(4));

      fk.setKeySeq(fkResultSet.getString(9));
      fks.add(fk);
    }
    return fks;
  }

  /**
   * Retrieve index information for a table.
   * @param table the table.
   * @return list of indices.
   * @throws SQLException if thrown by the jdbc driver.
   */
  private List getIndices(RelationalTable table) throws SQLException {
    List indices = new ArrayList<>();

    // maps index name to column names
    Multimap idxColumns = ArrayListMultimap.create();

    // one row per index-column pair
    ResultSet rs = m_metadataFactory.getMetadata().getIndexInfo(table.getCatalogSchema().getCatalog(),
                                                                table.getCatalogSchema().getSchema(),
                                                                table.getName(), false, false);

    while (rs.next()) {
      String idxName = rs.getString(6);
      Collection columns = idxColumns.get(idxName);
      if (columns.isEmpty()) {
        // build a new index
        RelationalIndex index = new RelationalIndex(table.getCatalogSchema(), rs.getString(6));
        indices.add(index);
      }

      columns.add(rs.getString(9));
    }

    for (RelationalIndex index : indices) {
      List columns = new ArrayList<>(idxColumns.size());
      for (String idxColumnName : idxColumns.get(index.getName())) {
        // Some db preserved names are double-quoted
        String columnName = idxColumnName.replaceAll("^\"|\"$", "");
        Column column = table.getColumnByName(columnName);

        if (column == null) {
          throw new InconsistentSchemaException("cannot find column " + columnName + " referenced by index " + index.getName() + " in table " + table.getName());
        }

        columns.add(column);
      }

      index.setColumns(columns);
    }

    return indices;
  }

  /**
   * Retrieve primary key information for a table.
   * @param table the table.
   * @return ordered list of primary key column names.
   * @throws SQLException if thrown by the jdbc driver.
   */
  private List getPrimaryKeyColumns(RelationalTable table) throws SQLException {
    Map primaryKeys = new TreeMap<>();
    ResultSet rs = m_metadataFactory.getMetadata().getPrimaryKeys(table.getCatalogSchema().getCatalog(), table.getCatalogSchema().getSchema(), table.getName());
    while (rs.next()) {
      primaryKeys.put(rs.getShort(5), rs.getString(4));
    }
    return Lists.newArrayList(primaryKeys.values());
  }

  @Override
  public RelationalDatabase createRelationalDatabase(CatalogSchema catalogSchema) {
    //Grab all the tables
    List tables;
    try {
      tables = getTables(catalogSchema);
    } catch (SQLException e) {
      throw new RelationalDatabaseReadException("could not read table information", e);
    }

    // build columns, foreign and primary keys in parallel
    runInParallel(Collections2.transform(tables, new Function>() {
      @Override
      public Callable apply(final RelationalTable table) {
        return new Callable() {
          @Override
          public Void call() throws Exception {
            table.setColumns(getColumns(table));
            table.setFks(new HashSet<>(getForeignKeys(table)));
            table.setPkColumns(getPrimaryKeyColumns(table));
            table.setIndices(getIndices(table));
            return null;
          }
        };
      }
    }));

    return new RelationalDatabase(tables);
  }
}