DatabaseAccessObject.java

// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you 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.cloud.upgrade.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;

public class DatabaseAccessObject {

    protected Logger logger = LogManager.getLogger(DatabaseAccessObject.class);

    public void addForeignKey(Connection conn, String tableName, String tableColumn, String foreignTableName, String foreignColumnName) {
        String addForeignKeyStmt = String.format("ALTER TABLE `cloud`.`%s` ADD CONSTRAINT `fk_%s__%s` FOREIGN KEY `fk_%s__%s`(`%s`) REFERENCES `%s`(`%s`)", tableName, tableName, tableColumn, tableName, tableColumn, tableColumn, foreignTableName, foreignColumnName);
        try(PreparedStatement pstmt = conn.prepareStatement(addForeignKeyStmt);)
        {
            pstmt.executeUpdate();
            logger.debug(String.format("Foreign key is added successfully from the table %s", tableName));
        } catch (SQLException e) {
            logger.error("Ignored SQL Exception when trying to add foreign key on table "  + tableName + " exception: " + e.getMessage());
        }
    }

    public void dropKey(Connection conn, String tableName, String key, boolean isForeignKey)
    {
        String alter_sql_str;
        if (isForeignKey) {
            alter_sql_str = "ALTER TABLE " + tableName + " DROP FOREIGN KEY " + key;
        } else {
            alter_sql_str = "ALTER TABLE " + tableName + " DROP KEY " + key;
        }
        try(PreparedStatement pstmt = conn.prepareStatement(alter_sql_str);)
        {
            pstmt.executeUpdate();
            logger.debug("Key " + key + " is dropped successfully from the table " + tableName);
        } catch (SQLException e) {
            logger.debug("Ignored SQL Exception when trying to drop " + (isForeignKey ? "foreign " : "") + "key " + key + " on table "  + tableName + " exception: " + e.getMessage());

        }
    }

    public void dropPrimaryKey(Connection conn, String tableName) {
        try(PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE " + tableName + " DROP PRIMARY KEY ");) {
            pstmt.executeUpdate();
            logger.debug("Primary key is dropped successfully from the table " + tableName);
        } catch (SQLException e) {
            logger.debug("Ignored SQL Exception when trying to drop primary key on table " + tableName + " exception: " + e.getMessage());
        }
    }

    public void dropColumn(Connection conn, String tableName, String columnName) {
        try (PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE " + tableName + " DROP COLUMN " + columnName);){
            pstmt.executeUpdate();
            logger.debug("Column " + columnName + " is dropped successfully from the table " + tableName);
        } catch (SQLException e) {
            logger.warn("Unable to drop column " + columnName + " due to exception", e);
        }
    }

    public boolean columnExists(Connection conn, String tableName, String columnName) {
        boolean columnExists = false;
        try (PreparedStatement pstmt = conn.prepareStatement("SELECT " + columnName + " FROM " + tableName);){
            pstmt.executeQuery();
            columnExists = true;
        } catch (SQLException e) {
            logger.debug("Field " + columnName + " doesn't exist in " + tableName + " ignoring exception: " + e.getMessage());
        }
        return columnExists;
    }

    public String getColumnType(Connection conn, String tableName, String columnName) {
        try (PreparedStatement pstmt = conn.prepareStatement(String.format("DESCRIBE %s %s", tableName, columnName));){
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("Type");
            }
        } catch (SQLException e) {
            logger.warn("Type for column {} can not be retrieved in {} ignoring exception: {}", columnName, tableName, e.getMessage());
        }
        return null;
    }

    public void addColumn(Connection conn, String tableName, String columnName, String columnDefinition) {
        try (PreparedStatement pstmt = conn.prepareStatement(String.format("ALTER TABLE %s ADD COLUMN %s %s", tableName, columnName, columnDefinition));){
            pstmt.executeUpdate();
            logger.debug("Column {} is added successfully from the table {}", columnName, tableName);
        } catch (SQLException e) {
            logger.warn("Unable to add column {} to table {} due to exception", columnName, tableName, e);
        }
    }

    public void changeColumn(Connection conn, String tableName, String oldColumnName, String newColumnName, String columnDefinition) {
        try (PreparedStatement pstmt = conn.prepareStatement(String.format("ALTER TABLE %s CHANGE COLUMN %s %s %s", tableName, oldColumnName, newColumnName, columnDefinition));){
            pstmt.executeUpdate();
            logger.debug("Column {} is changed successfully to {} from the table {}", oldColumnName, newColumnName, tableName);
        } catch (SQLException e) {
            logger.warn("Unable to add column {} to {} from the table {} due to exception", oldColumnName, newColumnName, tableName, e);
        }
    }

    public String generateIndexName(String tableName, String... columnName) {
        return String.format("i_%s__%s", tableName, StringUtils.join(columnName, "__"));
    }

    public boolean indexExists(Connection conn, String tableName, String indexName) {
        try (PreparedStatement pstmt = conn.prepareStatement(String.format("SHOW INDEXES FROM %s where Key_name = \"%s\"", tableName, indexName))) {
            ResultSet result = pstmt.executeQuery();
            if (result.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.debug(String.format("Index %s doesn't exist, ignoring exception:", indexName, e.getMessage()));
        }
        return false;
    }

    public void createIndex(Connection conn, String tableName, String indexName, String... columnNames) {
        String stmt = String.format("CREATE INDEX %s ON %s (%s)", indexName, tableName, StringUtils.join(columnNames, ", "));
        logger.debug("Statement: " + stmt);
        try (PreparedStatement pstmt = conn.prepareStatement(stmt)) {
            pstmt.execute();
            logger.debug(String.format("Created index %s", indexName));
        } catch (SQLException e) {
            logger.warn(String.format("Unable to create index %s", indexName), e);
        }
    }

    public void renameIndex(Connection conn, String tableName, String oldName, String newName) {
        String stmt = String.format("ALTER TABLE %s RENAME INDEX %s TO %s", tableName, oldName, newName);
        logger.debug("Statement: {}", stmt);
        try (PreparedStatement pstmt = conn.prepareStatement(stmt)) {
            pstmt.execute();
            logger.debug("Renamed index {} to {}", oldName, newName);
        } catch (SQLException e) {
            logger.warn("Unable to rename index {} to {}", oldName, newName, e);
        }
    }

    protected void closePreparedStatement(PreparedStatement pstmt, String errorMessage) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            logger.warn(errorMessage, e);
        }
    }
}