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