PostgresDbManagement.java

/**
 * Copyright (C) 2009 - present by OpenGamma Inc. and the OpenGamma group of companies
 *
 * Please see distribution for license.
 */
package com.opengamma.util.db.management;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.PostgreSQLDialect;

import com.google.common.base.Objects;
import com.opengamma.OpenGammaRuntimeException;

/**
 * Database management for Postgres databases.
 */
public final class PostgresDbManagement extends AbstractDbManagement {

  /**
   * SQL to retrieve all the columns.
   */
  private static final String GET_ALL_COLUMNS_SQL =
    "SELECT column_name AS name,data_type AS datatype,is_nullable AS allowsnull,column_default AS defaultvalue FROM information_schema.columns WHERE table_catalog='";
  /**
   * The Postgres default schema.
   */
  private static final String POSTGRES_DEFAULT_SCHEMA = "public";
  /**
   * Singleton instance.
   */
  private static final PostgresDbManagement INSTANCE = new PostgresDbManagement();

  /**
   * The underlying Hibernate dialect.
   */
  private PostgreSQLDialect _hibernateDialect;

  /**
   * Restricted constructor.
   */
  private PostgresDbManagement() {
  }

  /**
   * Gets the singleton instance.
   * 
   * @return the instance, not null
   */
  public static PostgresDbManagement getInstance() {
    return INSTANCE;
  }

  //-------------------------------------------------------------------------
  @Override
  public synchronized Dialect getHibernateDialect() {
    if (_hibernateDialect == null) {
      // constructed lazily so we don't get log message about 'using dialect' if we're not actually using it
      _hibernateDialect = new PostgreSQLDialect();
    }
    return _hibernateDialect;
  }

  @Override
  public Class<?> getJDBCDriverClass() {
    return org.postgresql.Driver.class;
  }

  @Override
  public String getDatabaseName() {
    return "postgres";
  }

  //-------------------------------------------------------------------------
  @Override
  public String getAllSchemasSQL(String catalog) {
    return "SELECT nspname AS name from pg_namespace";
  }

  @Override
  public String getAllForeignKeyConstraintsSQL(String catalog, String schema) {
    String effScheme = Objects.firstNonNull(schema, POSTGRES_DEFAULT_SCHEMA);
    String sql = "SELECT constraint_name AS name, table_name FROM information_schema.table_constraints WHERE " +
      "constraint_catalog = '" + catalog + "' AND constraint_schema = '" + effScheme + "'" + " AND constraint_type = 'FOREIGN KEY'";
    return sql;
  }

  @Override
  public String getAllSequencesSQL(String catalog, String schema) {
    String effScheme = Objects.firstNonNull(schema, POSTGRES_DEFAULT_SCHEMA);
    String sql = "SELECT sequence_name AS name FROM information_schema.sequences WHERE " +
      "sequence_catalog = '" + catalog + "'" + " AND sequence_schema = '" + effScheme + "'";
    return sql;
  }

  @Override
  public String getAllTablesSQL(String catalog, String schema) {
    String effScheme = Objects.firstNonNull(schema, POSTGRES_DEFAULT_SCHEMA);
    String sql = "SELECT table_name AS name FROM information_schema.tables WHERE " +
      "table_catalog = '" + catalog + "'" + " AND table_schema = '" + effScheme + "' AND table_type = 'BASE TABLE'";
    return sql;
  }

  @Override
  public String getAllViewsSQL(String catalog, String schema) {
    String effScheme = Objects.firstNonNull(schema, POSTGRES_DEFAULT_SCHEMA);
    String sql = "SELECT table_name AS name FROM information_schema.tables WHERE " +
      "table_catalog = '" + catalog + "'" + " AND table_schema = '" + effScheme + "' AND table_type = 'VIEW'";
    return sql;
  }

  @Override
  public String getAllColumnsSQL(String catalog, String schema, String table) {
    String effScheme = Objects.firstNonNull(schema, POSTGRES_DEFAULT_SCHEMA);
    StringBuilder sql = new StringBuilder(GET_ALL_COLUMNS_SQL);
    sql.append(catalog).append("' AND table_schema='").append(effScheme).append("' AND table_name='");
    sql.append(table).append("'");
    return sql.toString();
  }

  @Override
  public String getCreateSchemaSQL(String catalog, String schema) {
    return "CREATE SCHEMA " + schema;
  }
  
  @Override
  public String getSchemaVersionTable(String schemaGroupName) {
    return (schemaGroupName + SCHEMA_VERSION_TABLE_SUFFIX).toLowerCase();
  }

  @Override
  public String getSchemaVersionSQL(String catalog, String schemaGroupName) {
    return "SELECT version_value FROM " + getSchemaVersionTable(schemaGroupName) + " WHERE version_key = 'schema_patch'";
  }

  @Override
  public CatalogCreationStrategy getCatalogCreationStrategy() {
    return new SQLCatalogCreationStrategy(
        this, 
        getUser(), 
        getPassword(), 
        "SELECT datname AS name FROM pg_database",
        "template1");
  }

  @Override
  public void dropSchema(String catalog, String schema) {
    if (schema != null) {
      super.dropSchema(catalog, schema);
    } else {
      try {
        if (!getCatalogCreationStrategy().catalogExists(catalog)) {
          return;
        }
        try (Connection conn = connect(catalog)) {
          try (Statement statement = conn.createStatement()) {
            //TODO default schema
            statement.executeUpdate("DROP SCHEMA IF EXISTS public CASCADE;CREATE SCHEMA public;");
          }
        }
      } catch (SQLException se) {
        throw new OpenGammaRuntimeException("Failed to drop the default schema", se);
      }
    }
  }
}