SQLHelper.java

/*
 *
 * The DbUnit Database Testing Framework
 * Copyright (C)2005, DbUnit.org
 *
 * 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 org.dbunit.util;

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;

import org.dbunit.DatabaseUnitRuntimeException;
import org.dbunit.database.IMetadataHandler;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.datatype.DataType;
import org.dbunit.dataset.datatype.DataTypeException;
import org.dbunit.dataset.datatype.IDataTypeFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Helper for SQL-related stuff.
 * <br>
 * TODO: testcases, also think about refactoring so that methods are not static anymore (for better extensibility)
 * @author Felipe Leme (dbunit@felipeal.net)
 * @version $Revision$
 * @since Nov 5, 2005
 * 
 */
public class SQLHelper {

    /**
     * The database product name reported by Sybase JDBC drivers.
     */
    public static final String DB_PRODUCT_SYBASE = "Sybase";

    /**
     * Logger for this class
     */
    private static final Logger logger = LoggerFactory.getLogger(SQLHelper.class);

    // class is "static"
    private SQLHelper() {}

    /**
     * Gets the primary column for a table.
     * @param conn connection with the database
     * @param table table name
     * @return name of primary column for a table (assuming it's just 1 column).
     * @throws SQLException raised while getting the meta data
     */
    public static String getPrimaryKeyColumn( Connection conn, String table ) throws SQLException {
        logger.debug("getPrimaryKeyColumn(conn={}, table={}) - start", conn, table);

        DatabaseMetaData metadata = conn.getMetaData();
        ResultSet rs = metadata.getPrimaryKeys( null, null, table );
        rs.next();
        String pkColumn = rs.getString(4);
        return pkColumn;
    }

    /**
     * Close a result set and a prepared statement, checking for null references.
     * @param rs result set to be closed
     * @param stmt prepared statement to be closed
     * @throws SQLException exception raised in either close() method
     */
    public static void close(ResultSet rs, Statement stmt) throws SQLException {
        logger.debug("close(rs={}, stmt={}) - start", rs, stmt);

        try {
            SQLHelper.close(rs);
        } finally {
            SQLHelper.close( stmt );
        }
    }

    /**
     * Close a SQL statement, checking for null references.
     * @param stmt statement to be closed
     * @throws SQLException exception raised while closing the statement
     */
    public static void close(Statement stmt) throws SQLException {
        logger.debug("close(stmt={}) - start", stmt);

        if ( stmt != null ) {
            stmt.close();
        }
    }

    /**
     * Closes the given result set in a null-safe way
     * @param resultSet
     * @throws SQLException
     */
    public static void close(ResultSet resultSet) throws SQLException {
        logger.debug("close(resultSet={}) - start", resultSet);

        if(resultSet != null) {
            resultSet.close();
        }
    }

    /**
     * Returns <code>true</code> if the given schema exists for the given connection.
     * @param connection The connection to a database
     * @param schema The schema to be searched
     * @return Returns <code>true</code> if the given schema exists for the given connection.
     * @throws SQLException
     * @since 2.3.0
     */
    public static boolean schemaExists(Connection connection, String schema)
            throws SQLException
            {
        logger.trace("schemaExists(connection={}, schema={}) - start", connection, schema);

        if(schema == null)
        {
            throw new NullPointerException("The parameter 'schema' must not be null");
        }

        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getSchemas(); //null, schemaPattern);
        try
        {
            while(rs.next())
            {
                String foundSchema = rs.getString("TABLE_SCHEM");
                if(foundSchema.equals(schema))
                {
                    return true;
                }
            }

            // Especially for MySQL check the catalog
            if(catalogExists(connection, schema))
            {
                logger.debug("Found catalog with name {}. Returning true because DB is probably on MySQL", schema);
                return true;
            }

            return false;
        }
        finally
        {
            rs.close();
        }
            }

    /**
     * Checks via {@link DatabaseMetaData#getCatalogs()} whether or not the given catalog exists.
     * @param connection
     * @param catalog
     * @return
     * @throws SQLException
     * @since 2.4.4
     */
    private static boolean catalogExists(Connection connection, String catalog) throws SQLException
    {
        logger.trace("catalogExists(connection={}, catalog={}) - start", connection, catalog);

        if(catalog == null)
        {
            throw new NullPointerException("The parameter 'catalog' must not be null");
        }

        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getCatalogs();
        try
        {
            while(rs.next())
            {
                String foundCatalog = rs.getString("TABLE_CAT");
                if(foundCatalog.equals(catalog))
                {
                    return true;
                }
            }
            return false;
        }
        finally
        {
            rs.close();
        }

    }

    /**
     * Checks if the given table exists.
     * @param metaData The database meta data
     * @param schema The schema in which the table should be searched. If <code>null</code>
     * the schema is not used to narrow the table name.
     * @param tableName The table name to be searched
     * @return Returns <code>true</code> if the given table exists in the given schema.
     * Else returns <code>false</code>.
     * @throws SQLException
     * @since 2.3.0
     * @deprecated since 2.4.5 - use {@link IMetadataHandler#tableExists(DatabaseMetaData, String, String)}
     */
    public static boolean tableExists(DatabaseMetaData metaData, String schema,
            String tableName)
                    throws SQLException
                    {
        ResultSet tableRs = metaData.getTables(null, schema, tableName, null);
        try
        {
            return tableRs.next();
        }
        finally
        {
            SQLHelper.close(tableRs);
        }
                    }

    /**
     * Utility method for debugging to print all tables of the given metadata on the given stream
     * @param metaData
     * @param outputStream
     * @throws SQLException
     */
    public static void printAllTables(DatabaseMetaData metaData, PrintStream outputStream) throws SQLException
    {
        ResultSet rs = metaData.getTables(null, null, null, null);
        try
        {
            while (rs.next())
            {
                String catalog = rs.getString("TABLE_CAT");
                String schema = rs.getString("TABLE_SCHEM");
                String table = rs.getString("TABLE_NAME");
                final StringBuilder tableInfo = new StringBuilder();
                if(catalog!=null) tableInfo.append(catalog).append(".");
                if(schema!=null) tableInfo.append(schema).append(".");
                tableInfo.append(table);
                // Print the info
                outputStream.println(tableInfo);
            }
            outputStream.flush();
        }
        finally
        {
            SQLHelper.close(rs);
        }

    }

    /**
     * Returns the database and JDBC driver information as pretty formatted string
     * @param metaData The JDBC database metadata needed to retrieve database information
     * @return The database information as formatted string
     */
    public static String getDatabaseInfo(DatabaseMetaData metaData)
    {
        final StringBuilder sb = new StringBuilder();
        sb.append("\n");

        String dbInfo = null;

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return metaData.getDatabaseProductName();
            }
        }.executeWrappedCall(metaData);
        sb.append("\tdatabase product name=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return metaData.getDatabaseProductVersion();
            }
        }.executeWrappedCall(metaData);
        sb.append("\tdatabase version=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return String.valueOf(metaData.getDatabaseMajorVersion());
            }
        }.executeWrappedCall(metaData);
        sb.append("\tdatabase major version=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return String.valueOf(metaData.getDatabaseMinorVersion());
            }
        }.executeWrappedCall(metaData);
        sb.append("\tdatabase minor version=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return metaData.getDriverName();
            }
        }.executeWrappedCall(metaData);
        sb.append("\tjdbc driver name=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return metaData.getDriverVersion();
            }
        }.executeWrappedCall(metaData);
        sb.append("\tjdbc driver version=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return String.valueOf(metaData.getDriverMajorVersion());
            }
        }.executeWrappedCall(metaData);
        sb.append("\tjdbc driver major version=").append(dbInfo).append("\n");

        dbInfo = new ExceptionWrapper(){
            public String wrappedCall(DatabaseMetaData metaData) throws Exception {
                return String.valueOf(metaData.getDriverMinorVersion());
            }
        }.executeWrappedCall(metaData);
        sb.append("\tjdbc driver minor version=").append(dbInfo).append("\n");

        return sb.toString();
    }

    /**
     * Prints the database and JDBC driver information to the given output stream
     * @param metaData The JDBC database metadata needed to retrieve database information
     * @param outputStream The stream to which the information is printed
     * @throws SQLException
     */
    public static void printDatabaseInfo(DatabaseMetaData metaData, PrintStream outputStream) throws SQLException
    {
        String dbInfo = getDatabaseInfo(metaData);
        try {
            outputStream.println(dbInfo);
        }
        finally {
            outputStream.flush();
        }
    }

    /**
     * Detects whether or not the given metadata describes the connection to a Sybase database
     * or not.
     * @param metaData The metadata to be checked whether it is a Sybase connection
     * @return <code>true</code> if and only if the given metadata belongs to a Sybase database.
     * @throws SQLException
     */
    public static boolean isSybaseDb(DatabaseMetaData metaData) throws SQLException
    {
        String dbProductName = metaData.getDatabaseProductName();
        boolean isSybase = (dbProductName != null && dbProductName.equals(DB_PRODUCT_SYBASE));
        return isSybase;
    }


    /**
     * Utility method to create a {@link Column} object from a SQL {@link ResultSet} object.
     * 
     * @param resultSet A result set produced via {@link DatabaseMetaData#getColumns(String, String, String, String)}
     * @param dataTypeFactory The factory used to lookup the {@link DataType} for this column
     * @param datatypeWarning Whether or not a warning should be printed if the column could not
     * be created because of an unknown datatype.
     * @return The {@link Column} or <code>null</code> if the column could not be initialized because of an
     * unknown datatype.
     * @throws SQLException
     * @throws DataTypeException
     * @since 2.4.0
     */
    public static final Column createColumn(ResultSet resultSet,
            IDataTypeFactory dataTypeFactory, boolean datatypeWarning)
                    throws SQLException, DataTypeException
                    {
        String tableName = resultSet.getString(3);
        String columnName = resultSet.getString(4);
        int sqlType = resultSet.getInt(5);
        //If Types.DISTINCT like SQL DOMAIN, then get Source Date Type of SQL-DOMAIN
        if(sqlType == java.sql.Types.DISTINCT)
        {
            sqlType = resultSet.getInt("SOURCE_DATA_TYPE");
        }

        String sqlTypeName = resultSet.getString(6);
        //        int columnSize = resultSet.getInt(7);
        int nullable = resultSet.getInt(11);
        String remarks = resultSet.getString(12);
        String columnDefaultValue = resultSet.getString(13);
        String isAutoIncrement = resultSet.getString(23);
        // some JDBC drivers do not have this column even though they claim to be compliant with JDBC 4.1 or later
        String isGenerated = resultSet.getMetaData().getColumnCount() >= 24 ? resultSet.getString(24) : null;

        // Convert SQL type to DataType
        DataType dataType =
                dataTypeFactory.createDataType(sqlType, sqlTypeName, tableName, columnName);
        if (dataType != DataType.UNKNOWN)
        {
            Column column = new Column(columnName, dataType,
                    sqlTypeName, Column.nullableValue(nullable), columnDefaultValue, remarks,
                    Column.AutoIncrement.autoIncrementValue(isAutoIncrement),
                    Column.convertMetaDataBoolean(isGenerated));
            return column;
        }
        else
        {
            if (datatypeWarning)
                logger.warn(
                        tableName + "." + columnName +
                        " data type (" + sqlType + ", '" + sqlTypeName +
                        "') not recognized and will be ignored. See FAQ for more information.");

            // datatype unknown - column not created
            return null;
        }
                    }

    /**
     * Checks if the given <code>resultSet</code> matches the given schema and table name.
     * The comparison is <b>case sensitive</b>.
     * @param resultSet A result set produced via {@link DatabaseMetaData#getColumns(String, String, String, String)}
     * @param schema The name of the schema to check. If <code>null</code> it is ignored in the comparison
     * @param table The name of the table to check. If <code>null</code> it is ignored in the comparison
     * @param caseSensitive Whether or not the comparison should be case sensitive or not
     * @return <code>true</code> if the column metadata of the given <code>resultSet</code> matches
     * the given schema and table parameters.
     * @throws SQLException
     * @since 2.4.0
     * @deprecated since 2.4.4 - use {@link IMetadataHandler#matches(ResultSet, String, String, String, String, boolean)}
     */
    public static boolean matches(ResultSet resultSet,
            String schema, String table, boolean caseSensitive)
                    throws SQLException
                    {
        return matches(resultSet, null, schema, table, null, caseSensitive);
                    }


    /**
     * Checks if the given <code>resultSet</code> matches the given schema and table name.
     * The comparison is <b>case sensitive</b>.
     * @param resultSet A result set produced via {@link DatabaseMetaData#getColumns(String, String, String, String)}
     * @param catalog The name of the catalog to check. If <code>null</code> it is ignored in the comparison
     * @param schema The name of the schema to check. If <code>null</code> it is ignored in the comparison
     * @param table The name of the table to check. If <code>null</code> it is ignored in the comparison
     * @param column The name of the column to check. If <code>null</code> it is ignored in the comparison
     * @param caseSensitive Whether or not the comparison should be case sensitive or not
     * @return <code>true</code> if the column metadata of the given <code>resultSet</code> matches
     * the given schema and table parameters.
     * @throws SQLException
     * @since 2.4.0
     * @deprecated since 2.4.4 - use {@link IMetadataHandler#matches(ResultSet, String, String, String, String, boolean)}
     */
    public static boolean matches(ResultSet resultSet,
            String catalog, String schema,
            String table, String column, boolean caseSensitive)
                    throws SQLException
                    {
        String catalogName = resultSet.getString(1);
        String schemaName = resultSet.getString(2);
        String tableName = resultSet.getString(3);
        String columnName = resultSet.getString(4);

        // MYSQL provides only a catalog but no schema
        if(schema != null && schemaName == null && catalog==null && catalogName != null){
            logger.debug("Switching catalog/schema because the are mutually null");
            schemaName = catalogName;
            catalogName = null;
        }

        boolean areEqual =
                areEqualIgnoreNull(catalog, catalogName, caseSensitive) &&
                areEqualIgnoreNull(schema, schemaName, caseSensitive) &&
                areEqualIgnoreNull(table, tableName, caseSensitive) &&
                areEqualIgnoreNull(column, columnName, caseSensitive);
        return areEqual;
                    }

    /**
     * Compares the given values and returns true if they are equal.
     * If the first value is <code>null</code> or empty String it always
     * returns <code>true</code> which is the way of ignoring <code>null</code>s
     * for this specific case.
     * @param value1 The first value to compare. Is ignored if null or empty String
     * @param value2 The second value to be compared
     * @return <code>true</code> if both values are equal or if the first value
     * is <code>null</code> or empty string.
     * @since 2.4.4
     */
    public static final boolean areEqualIgnoreNull(String value1, String value2, boolean caseSensitive)
    {
        if(value1==null || value1.equals(""))
        {
            return true;
        }
        else
        {
            if(caseSensitive && value1.equals(value2))
            {
                return true;
            }
            else if(!caseSensitive && value1.equalsIgnoreCase(value2))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }

    /**
     * Corrects the case of the given String according to the way in which the database stores metadata.
     * @param databaseIdentifier A database identifier such as a table name or a schema name for
     * which the case should be corrected.
     * @param connection The connection used to lookup the database metadata. This is needed to determine
     * the way in which the database stores its metadata.
     * @return The database identifier in the correct case for the RDBMS
     * @since 2.4.4
     */
    public static final String correctCase(final String databaseIdentifier, Connection connection)
    {
        logger.trace("correctCase(tableName={}, connection={}) - start", databaseIdentifier, connection);

        try
        {
            return correctCase(databaseIdentifier, connection.getMetaData());
        }
        catch (SQLException e)
        {
            throw new DatabaseUnitRuntimeException("Exception while trying to access database metadata", e);
        }
    }

    /**
     * Corrects the case of the given String according to the way in which the database stores metadata.
     * @param databaseIdentifier A database identifier such as a table name or a schema name for
     * which the case should be corrected.
     * @param databaseMetaData The database metadata needed to determine the way in which the database stores
     * its metadata.
     * @return The database identifier in the correct case for the RDBMS
     * @since 2.4.4
     */
    public static final String correctCase(final String databaseIdentifier, DatabaseMetaData databaseMetaData)
    {
        logger.trace("correctCase(tableName={}, databaseMetaData={}) - start", databaseIdentifier, databaseMetaData);

        if (databaseIdentifier == null) {
            throw new NullPointerException(
                    "The parameter 'databaseIdentifier' must not be null");
        }
        if (databaseMetaData == null) {
            throw new NullPointerException(
                    "The parameter 'databaseMetaData' must not be null");
        }

        try {
            String resultTableName = databaseIdentifier;
            String dbIdentifierQuoteString = databaseMetaData.getIdentifierQuoteString();
            if(!isEscaped(databaseIdentifier, dbIdentifierQuoteString)){
                if(databaseMetaData.storesLowerCaseIdentifiers())
                {
                    resultTableName = databaseIdentifier.toLowerCase(Locale.ENGLISH);
                }
                else if(databaseMetaData.storesUpperCaseIdentifiers())
                {
                    resultTableName = databaseIdentifier.toUpperCase(Locale.ENGLISH);
                }
                else
                {
                    logger.debug("Database does not store upperCase or lowerCase identifiers. " +
                            "Will not correct case of the table names.");
                }
            }
            else
            {
                if(logger.isDebugEnabled())
                    logger.debug("The tableName '{}' is escaped. Will not correct case.", databaseIdentifier);
                }
            return resultTableName;
        }
        catch (SQLException e)
        {
            throw new DatabaseUnitRuntimeException("Exception while trying to access database metadata", e);
        }
    }

    /**
     * Checks whether two given values are unequal and if so print a log message (level DEBUG)
     * @param oldValue The old value of a property
     * @param newValue The new value of a property
     * @param message The message to be logged
     * @param source The class which invokes this method - used for enriching the log message
     * @since 2.4.4
     */
    public static final void logInfoIfValueChanged(String oldValue, String newValue, String message, Class source)
    {
        if(logger.isInfoEnabled())
        {
            if(oldValue != null && !oldValue.equals(newValue))
                logger.debug("{}. {} oldValue={} newValue={}", new Object[] {source, message, oldValue, newValue});
            }
        }

    /**
     * Checks whether two given values are unequal and if so print a log message (level DEBUG)
     * @param oldValue The old value of a property
     * @param newValue The new value of a property
     * @param message The message to be logged
     * @param source The class which invokes this method - used for enriching the log message
     * @since 2.4.8
     */
    public static final void logDebugIfValueChanged(String oldValue, String newValue, String message, Class source)
    {
        if (logger.isDebugEnabled())
        {
            if (oldValue != null && !oldValue.equals(newValue))
                logger.debug("{}. {} oldValue={} newValue={}", new Object[] {source, message, oldValue, newValue});
            }
        }

    /**
     * @param tableName
     * @param dbIdentifierQuoteString
     * @return
     * @since 2.4.4
     */
    private static final boolean isEscaped(String tableName, String dbIdentifierQuoteString)
    {
        logger.trace("isEscaped(tableName={}, dbIdentifierQuoteString={}) - start", tableName, dbIdentifierQuoteString);

        if (dbIdentifierQuoteString == null) {
            throw new NullPointerException(
                    "The parameter 'dbIdentifierQuoteString' must not be null");
        }
        boolean isEscaped = tableName!=null && (tableName.startsWith(dbIdentifierQuoteString));
        if(logger.isDebugEnabled())
            logger.debug("isEscaped returns '{}' for tableName={} (dbIdentifierQuoteString={})",
                    new Object[]{Boolean.valueOf(isEscaped), tableName, dbIdentifierQuoteString} );
        return isEscaped;
    }

    /**
     * Performs a method invocation and catches all exceptions that occur during the invocation.
     * Utility which works similar to a closure, just a bit less elegant.
     * @author gommma (gommma AT users.sourceforge.net)
     * @author Last changed by: $Author$
     * @version $Revision$ $Date$
     * @since 2.4.6
     */
    static abstract class ExceptionWrapper{

        public static final String NOT_AVAILABLE_TEXT = "<not available>";

        /**
         * Default constructor
         */
        public ExceptionWrapper()
        {
        }

        /**
         * Executes the call and catches all exception that might occur.
         * @param metaData
         * @return The result of the call
         */
        public final String executeWrappedCall(DatabaseMetaData metaData) {
            try{
                String result = wrappedCall(metaData);
                return result;
            }
            catch(Exception e){
                logger.trace("Problem retrieving DB information via DatabaseMetaData", e);
                return NOT_AVAILABLE_TEXT;
            }
        }
        /**
         * Calls the method that might throw an exception to be handled
         * @param metaData
         * @return The result of the call as human readable string
         * @throws Exception Any exception that might occur during the method invocation
         */
        public abstract String wrappedCall(DatabaseMetaData metaData) throws Exception;
    }

}