/**
 * The contents of this file are subject to the license and copyright
 * detailed in the LICENSE and NOTICE files at the root of the source
 * tree and available online at
 *
 * http://www.dspace.org/license/
 */
package org.dspace.storage.rdbms;

import java.io.*;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.log4j.Level;
import org.dspace.core.ConfigurationManager;
import org.dspace.core.Context;

/**
 * Executes SQL queries.
 * 
 * @author Peter Breton
 * @author Jim Downing
 * @version $Revision$
 */
public class DatabaseManager
{
    /** log4j category */
    private static Logger log = Logger.getLogger(DatabaseManager.class);

    /** True if initialization has been done */
    private static boolean initialized = false;

    private static Map<String, String> insertSQL = new HashMap<String, String>();

    private static boolean isOracle = false;
    private static boolean isPostgres = false;

    static
    {
        if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
        {
            isOracle = true;
            isPostgres = false;
        }
        else
        {
            isOracle = false;
            isPostgres = true;
        }
    }

    /** DataSource (retrieved from jndi */
    private static DataSource dataSource = null;
    private static String sqlOnBorrow = null;

    /** Name to use for the pool */
    private static String poolName = "dspacepool";
    
    /** 
     * This regular expression is used to perform sanity checks 
     * on database names (i.e. tables and columns). 
     * 
     * FIXME: Regular expressions can be slow to solve this in the future we should
     * probably create a system where we don't pass in column and table names to these low
     * level database methods. This approach is highly exploitable for injection 
     * type attacks because we are unable to determine where the input came from. Instead
     * we could pass in static integer constants which are then mapped to their sql name. 
     */
    private static final Pattern DB_SAFE_NAME = Pattern.compile("^[a-zA-Z_1-9.]+$");

    /**
     * A map of database column information. The key is the table name, a
     * String; the value is an array of ColumnInfo objects.
     */
    private static Map<String, Map<String, ColumnInfo>> info = new HashMap<String, Map<String, ColumnInfo>>();

    /**
     * Protected Constructor to prevent instantiation except by derived classes.
     */
    protected DatabaseManager()
    {
    }

    public static boolean isOracle()
    {
        return isOracle;
    }
    
    /**
     * Set the constraint check to deferred (commit time)
     * 
     * @param context
     *            The context object
     * @param constraintName
     *            the constraint name to deferred
     * @throws SQLException
     */
    public static void setConstraintDeferred(Context context,
            String constraintName) throws SQLException
    {
        Statement statement = null;
        try
        {
            statement = context.getDBConnection().createStatement();
            statement.execute("SET CONSTRAINTS " + constraintName + " DEFERRED");
            statement.close();
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }

    /**
     * Set the constraint check to immediate (every query)
     * 
     * @param context
     *            The context object
     * @param constraintName
     *            the constraint name to check immediately after every query
     * @throws SQLException
     */
    public static void setConstraintImmediate(Context context,
            String constraintName) throws SQLException
    {
        Statement statement = null;
        try
        {
            statement = context.getDBConnection().createStatement();
            statement.execute("SET CONSTRAINTS " + constraintName + " IMMEDIATE");
            statement.close();
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }
    
    /**
     * Return an iterator with the results of the query. The table parameter
     * indicates the type of result. If table is null, the column names are read
     * from the ResultSetMetaData.
     * 
     * @param context
     *            The context object
     * @param table
     *            The name of the table which results
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference  
     * 			  within the query.
     * @return A TableRowIterator with the results of the query
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRowIterator queryTable(Context context, String table, String query, Object... parameters ) throws SQLException
    {
        if (log.isDebugEnabled())
        {
            StringBuilder sb = new StringBuilder("Running query \"").append(query).append("\"  with parameters: ");
            for (int i = 0; i < parameters.length; i++)
            {
                if (i > 0)
               {
                       sb.append(",");
               }
                sb.append(parameters[i].toString());
            }
            log.debug(sb.toString());
        }
        
        PreparedStatement statement = context.getDBConnection().prepareStatement(query);
        try
        {
            loadParameters(statement, parameters);

            TableRowIterator retTRI = new TableRowIterator(statement.executeQuery(), canonicalize(table));

            retTRI.setStatement(statement);
            return retTRI;
        }
        catch (SQLException sqle)
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException s)
                {
                }
            }

            throw sqle;
        }
    }
    
    /**
     * Return an iterator with the results of the query.
     * 
     * @param context
     *            The context object
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.
     * @return A TableRowIterator with the results of the query
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRowIterator query(Context context, String query,
            Object... parameters) throws SQLException    
    {
        if (log.isDebugEnabled())
        {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < parameters.length; i++)
            {
                if (i > 0)
               {
                       sb.append(",");
               }
                sb.append(parameters[i].toString());
            }
            log.debug("Running query \"" + query + "\"  with parameters: " + sb.toString());
        }

        PreparedStatement statement = context.getDBConnection().prepareStatement(query);
        try
        {
            loadParameters(statement,parameters);

            TableRowIterator retTRI = new TableRowIterator(statement.executeQuery());

            retTRI.setStatement(statement);
            return retTRI;
        }
        catch (SQLException sqle)
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException s)
                {
                }
            }

            throw sqle;
        }
    }

    /**
     * Return the single row result to this query, or null if no result. If more
     * than one row results, only the first is returned.
     * 
     * @param context
     *            Current DSpace context
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.

     * @return A TableRow object, or null if no result
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRow querySingle(Context context, String query,
            Object... parameters) throws SQLException
    {
        TableRow retRow = null;
        TableRowIterator iterator = null;
        try
        {
            iterator = query(context, query, parameters);
            retRow = (!iterator.hasNext()) ? null : iterator.next();
        }
        finally
        {
            if (iterator != null)
            {
                iterator.close();
            }
        }

        return (retRow);
    }
    
    /**
     * Return the single row result to this query, or null if no result. If more
     * than one row results, only the first is returned.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The name of the table which results
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.
     * @return A TableRow object, or null if no result
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRow querySingleTable(Context context, String table,
            String query, Object... parameters) throws SQLException
    {
        TableRow retRow = null;
        TableRowIterator iterator = queryTable(context, canonicalize(table), query, parameters);

        try
        {
            retRow = (!iterator.hasNext()) ? null : iterator.next();
        }
        finally
        {
            if (iterator != null)
            {
                iterator.close();
            }
        }
        return (retRow);
    }
    
    /**
     * Execute an update, insert or delete query. Returns the number of rows
     * affected by the query.
     * 
     * @param context
     *            Current DSpace context
     * @param query
     *            The SQL query to execute
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.
     * @return The number of rows affected by the query.
     * @exception SQLException
     *                If a database error occurs
     */
    public static int updateQuery(Context context, String query, Object... parameters) throws SQLException
    {
        PreparedStatement statement = null;

        if (log.isDebugEnabled())
        {
            StringBuilder sb = new StringBuilder("Running query \"").append(query).append("\"  with parameters: ");
            for (int i = 0; i < parameters.length; i++)
            {
                if (i > 0)
               {
                       sb.append(",");
               }
                sb.append(parameters[i].toString());
            }
            log.debug(sb.toString());
        }

        try
        {        	
        	statement = context.getDBConnection().prepareStatement(query);
        	loadParameters(statement, parameters);
        	
        	return statement.executeUpdate();
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }

    /**
     * Create a new row in the given table, and assigns a unique id.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The RDBMS table in which to create the new row
     * @return The newly created row
     */
    public static TableRow create(Context context, String table)
            throws SQLException
    {
        TableRow row = new TableRow(canonicalize(table), getColumnNames(table));
        insert(context, row);

        return row;
    }

    /**
     * Find a table row by its primary key. Returns the row, or null if no row
     * with that primary key value exists.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table in which to find the row
     * @param id
     *            The primary key value
     * @return The row resulting from the query, or null if no row with that
     *         primary key value exists.
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRow find(Context context, String table, int id)
            throws SQLException
    {
        String ctable = canonicalize(table);

        return findByUnique(context, ctable, getPrimaryKeyColumn(ctable),
                Integer.valueOf(id));
    }

    /**
     * Find a table row by a unique value. Returns the row, or null if no row
     * with that primary key value exists. If multiple rows with the value
     * exist, one is returned.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table to use to find the object
     * @param column
     *            The name of the unique column
     * @param value
     *            The value of the unique column
     * @return The row resulting from the query, or null if no row with that
     *         value exists.
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRow findByUnique(Context context, String table,
            String column, Object value) throws SQLException
    {
        String ctable = canonicalize(table);

        if ( ! DB_SAFE_NAME.matcher(ctable).matches())
        {
            throw new SQLException("Unable to execute select query because table name (" + ctable + ") contains non alphanumeric characters.");
        }

        if ( ! DB_SAFE_NAME.matcher(column).matches())
        {
            throw new SQLException("Unable to execute select query because column name (" + column + ") contains non alphanumeric characters.");
        }

        StringBuilder sql = new StringBuilder("select * from ").append(ctable).append(" where ").append(column).append(" = ? ");
        return querySingleTable(context, ctable, sql.toString(), value);
    }

    /**
     * Delete a table row via its primary key. Returns the number of rows
     * deleted.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table to delete from
     * @param id
     *            The primary key value
     * @return The number of rows deleted
     * @exception SQLException
     *                If a database error occurs
     */
    public static int delete(Context context, String table, int id)
            throws SQLException
    {
        String ctable = canonicalize(table);

        return deleteByValue(context, ctable, getPrimaryKeyColumn(ctable),
                Integer.valueOf(id));
    }

    /**
     * Delete all table rows with the given value. Returns the number of rows
     * deleted.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table to delete from
     * @param column
     *            The name of the column
     * @param value
     *            The value of the column
     * @return The number of rows deleted
     * @exception SQLException
     *                If a database error occurs
     */
    public static int deleteByValue(Context context, String table,
            String column, Object value) throws SQLException
    {
        String ctable = canonicalize(table);

        if ( ! DB_SAFE_NAME.matcher(ctable).matches())
        {
            throw new SQLException("Unable to execute delete query because table name (" + ctable + ") contains non alphanumeric characters.");
        }

        if ( ! DB_SAFE_NAME.matcher(column).matches())
        {
            throw new SQLException("Unable to execute delete query because column name (" + column + ") contains non alphanumeric characters.");
        }

        StringBuilder sql = new StringBuilder("delete from ").append(ctable).append(" where ").append(column).append(" = ? ");
        return updateQuery(context, sql.toString(), value);
    }

    /**
     * Obtain an RDBMS connection.
     * 
     * @return A new database connection.
     * @exception SQLException
     *                If a database error occurs, or a connection cannot be
     *                obtained.
     */
    public static Connection getConnection() throws SQLException
    {
        initialize();

        if (dataSource != null) {
        	Connection conn = dataSource.getConnection();
        	
        	//Cambio para GV: Para que ordenene correctamente los numeros
	        if (isOracle) {		
	
	          	//SET SESSIONS NLS_SORT FOR CORRECT SQL ORDER BY SENTENCES IN ORACLE
	          	String nls_sort = ConfigurationManager.getProperty("NLS.SORT");
			        
		        	if(nls_sort==null || nls_sort.equals("")) nls_sort="BINARY";
	
		        	PreparedStatement pstmt2 = conn.prepareStatement("ALTER SESSION SET NLS_SORT='" + nls_sort+ "'");
		        	try {
		        		pstmt2.execute();
		        		log.debug("ALTER SESSION SET NLS_SORT='" + nls_sort + "'; EXECUTED");
		        	}
		        	finally {
		        		if (pstmt2 != null){
	                  		pstmt2.close();
	              		}
		    			}
        	}
        	
        	
        	if (!StringUtils.isEmpty(sqlOnBorrow))
        	{
	        	PreparedStatement pstmt = conn.prepareStatement(sqlOnBorrow);
	        	try
	        	{
	        		pstmt.execute();
	        	}
	        	finally
	        	{
	        		if (pstmt != null)
                    {
                        pstmt.close();
                    }
	        	}
        	}

        	return conn;
        }

        return null;
    }

    public static DataSource getDataSource()
    {
        try
        {
            initialize();
        }
        catch (SQLException e)
        {
            throw new IllegalStateException(e.getMessage(), e);
        }

        return dataSource;
    }

    /**
     * Release resources associated with this connection.
     * 
     * @param c
     *            The connection to release
     */
    public static void freeConnection(Connection c)
    {
        try
        {
            if (c != null)
            {
                c.close();
            }
        }
        catch (SQLException e)
        {
            log.warn(e.getMessage(), e);
        }
    }

    /**
     * Create a table row object that can be passed into the insert method, not
     * commonly used unless the table has a referential integrity constraint.
     * 
     * @param table
     *            The RDBMS table in which to create the new row
     * @return The newly created row
     * @throws SQLException
     */
    public static TableRow row(String table) throws SQLException
    {
        return new TableRow(canonicalize(table), getColumnNames(table));
    }

    /**
     * Insert a table row into the RDBMS.
     * 
     * @param context
     *            Current DSpace context
     * @param row
     *            The row to insert
     * @exception SQLException
     *                If a database error occurs
     */
    public static void insert(Context context, TableRow row) throws SQLException
    {
        int newID;
        if (isPostgres)
        {
            newID = doInsertPostgres(context, row);
        }
        else
        {
            newID = doInsertGeneric(context, row);
        }

        row.setColumn(getPrimaryKeyColumn(row), newID);
    }

    /**
     * Update changes to the RDBMS. Note that if the update fails, the values in
     * the row will NOT be reverted.
     * 
     * @param context
     *            Current DSpace context
     * @param row
     *            The row to update
     * @return The number of rows affected (1 or 0)
     * @exception SQLException
     *                If a database error occurs
     */
    public static int update(Context context, TableRow row) throws SQLException
    {
        String table = row.getTable();

        StringBuilder sql = new StringBuilder().append("update ").append(table)
                .append(" set ");

        List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
        ColumnInfo pk = getPrimaryKeyColumnInfo(table);
        Collection<ColumnInfo> info = getColumnInfo(table);

        String separator = "";
        for (ColumnInfo col : info)
        {
            // Only update this column if it has changed
            if (!col.isPrimaryKey())
            {
                if (row.hasColumnChanged(col.getName()))
                {
                    sql.append(separator).append(col.getName()).append(" = ?");
                    columns.add(col);
                    separator = ", ";
                }
            }
        }

        // Only execute the update if there is anything to update
        if (columns.size() > 0)
        {
            sql.append(" where ").append(pk.getName()).append(" = ?");
            columns.add(pk);

            return executeUpdate(context.getDBConnection(), sql.toString(), columns, row);
        }

        return 1;
    }

    /**
     * Delete row from the RDBMS.
     * 
     * @param context
     *            Current DSpace context
     * @param row
     *            The row to delete
     * @return The number of rows affected (1 or 0)
     * @exception SQLException
     *                If a database error occurs
     */
    public static int delete(Context context, TableRow row) throws SQLException
    {
        if (null == row.getTable())
        {
            throw new IllegalArgumentException("Row not associated with a table");
        }

        String pk = getPrimaryKeyColumn(row);

        if (row.isColumnNull(pk))
        {
            throw new IllegalArgumentException("Primary key value is null");
        }

        return delete(context, row.getTable(), row.getIntColumn(pk));
    }

    /**
     * Return metadata about a table.
     * 
     * @param table
     *            The name of the table
     * @return An array of ColumnInfo objects
     * @exception SQLException
     *                If a database error occurs
     */
    static Collection<ColumnInfo> getColumnInfo(String table) throws SQLException
    {
        Map<String, ColumnInfo> cinfo = getColumnInfoInternal(table);

        return (cinfo == null) ? null : cinfo.values();
    }

    /**
     * Return info about column in table.
     * 
     * @param table
     *            The name of the table
     * @param column
     *            The name of the column
     * @return Information about the column
     * @exception SQLException
     *                If a database error occurs
     */
    static ColumnInfo getColumnInfo(String table, String column)
            throws SQLException
    {
        Map<String, ColumnInfo> info = getColumnInfoInternal(table);

        return (info == null) ? null : info.get(column);
    }

    /**
     * Return the names of all the columns of the given table.
     * 
     * @param table
     *            The name of the table
     * @return The names of all the columns of the given table, as a List. Each
     *         element of the list is a String.
     * @exception SQLException
     *                If a database error occurs
     */
    static List<String> getColumnNames(String table) throws SQLException
    {
        List<String> results = new ArrayList<String>();
        Collection<ColumnInfo> info = getColumnInfo(table);

        for (ColumnInfo col : info)
        {
            results.add(col.getName());
        }

        return results;
    }

    /**
     * Return the names of all the columns of the ResultSet.
     * 
     * @param meta
     *            The ResultSetMetaData
     * @return The names of all the columns of the given table, as a List. Each
     *         element of the list is a String.
     * @exception SQLException
     *                If a database error occurs
     */
    static List<String> getColumnNames(ResultSetMetaData meta) throws SQLException
    {
        List<String> results = new ArrayList<String>();
        int columns = meta.getColumnCount();

        for (int i = 0; i < columns; i++)
        {
            results.add(meta.getColumnLabel(i + 1));
        }

        return results;
    }

    /**
     * Return the canonical name for a table.
     * 
     * @param table
     *            The name of the table.
     * @return The canonical name of the table.
     */
    static String canonicalize(String table)
    {
        // Oracle expects upper-case table names
        if (isOracle)
        {
            return (table == null) ? null : table.toUpperCase();
        }

        // default database postgres wants lower-case table names
        return (table == null) ? null : table.toLowerCase();
    }

    ////////////////////////////////////////
    // SQL loading methods
    ////////////////////////////////////////

    /**
     * Load SQL into the RDBMS.
     * 
     * @param sql
     *            The SQL to load.
     * throws SQLException
     *            If a database error occurs
     */
    public static void loadSql(String sql) throws SQLException
    {
        try
        {
            loadSql(new StringReader(sql));
        }
        catch (IOException ioe)
        {
        }
    }

    /**
     * Load SQL from a reader into the RDBMS.
     * 
     * @param r
     *            The Reader from which to read the SQL.
     * @throws SQLException
     *            If a database error occurs
     * @throws IOException
     *            If an error occurs obtaining data from the reader
     */
    public static void loadSql(Reader r) throws SQLException, IOException
    {
        BufferedReader reader = new BufferedReader(r);
        StringBuilder sqlBuilder = new StringBuilder();
        String sql = null;

        String line = null;

        Connection connection = null;
        Statement statement = null;

        try
        {
            connection = getConnection();
            connection.setAutoCommit(true);
            statement = connection.createStatement();

            boolean inquote = false;

            while ((line = reader.readLine()) != null)
            {
                // Look for comments
                int commentStart = line.indexOf("--");

                String input = (commentStart != -1) ? line.substring(0, commentStart) : line;

                // Empty line, skip
                if (input.trim().equals(""))
                {
                    continue;
                }

                // Put it on the SQL buffer
                sqlBuilder.append(input.replace(';', ' ')); // remove all semicolons
                                                     // from sql file!

                // Add a space
                sqlBuilder.append(" ");

                // More to come?
                // Look for quotes
                int index = 0;
                int count = 0;
                int inputlen = input.length();

                while ((index = input.indexOf('\'', count)) != -1)
                {
                    // Flip the value of inquote
                    inquote = !inquote;

                    // Move the index
                    count = index + 1;

                    // Make sure we do not exceed the string length
                    if (count >= inputlen)
                    {
                        break;
                    }
                }

                // If we are in a quote, keep going
                // Note that this is STILL a simple heuristic that is not
                // guaranteed to be correct
                if (inquote)
                {
                    continue;
                }

                int endMarker = input.indexOf(';', index);

                if (endMarker == -1)
                {
                    continue;
                }

                sql = sqlBuilder.toString();
                if (log.isDebugEnabled())
                {
                    log.debug("Running database query \"" + sql + "\"");
                }

                try
                {
                    // Use execute, not executeQuery (which expects results) or
                    // executeUpdate
                    statement.execute(sql);
                }
                catch (SQLWarning sqlw)
                {
                    if (log.isDebugEnabled())
                    {
                        log.debug("Got SQL Warning: " + sqlw, sqlw);
                    }
                }
                catch (SQLException sqle)
                {
                    String msg = "Got SQL Exception: " + sqle;
                    String sqlmessage = sqle.getMessage();

                    // These are Postgres-isms:
                    // There's no easy way to check if a table exists before
                    // creating it, so we always drop tables, then create them
                    boolean isDrop = ((sql != null) && (sqlmessage != null)
                            && (sql.toUpperCase().startsWith("DROP"))
                            && (sqlmessage.indexOf("does not exist") != -1));

                    // Creating a view causes a bogus warning
                    boolean isNoResults = ((sql != null)
                            && (sqlmessage != null)
                            && (sql.toUpperCase().startsWith("CREATE VIEW")
                                    || sql.toUpperCase().startsWith("CREATE FUNCTION"))
                            && (sqlmessage.indexOf("No results were returned") != -1));

                    // If the messages are bogus, give them a low priority
                    if (isDrop || isNoResults)
                    {
                        if (log.isDebugEnabled())
                        {
                            log.debug(msg, sqle);
                        }
                    }
                    // Otherwise, we need to know!
                    else
                    {
                        if (log.isEnabledFor(Level.WARN))
                        {
                            log.warn(msg, sqle);
                        }
                    }
                }

                // Reset SQL buffer
                sqlBuilder = new StringBuilder();
                sql = null;
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.close();
            }

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

    ////////////////////////////////////////
    // Helper methods
    ////////////////////////////////////////

    /**
     * Convert the current row in a ResultSet into a TableRow object.
     * 
     * @param results
     *            A ResultSet to process
     * @param table
     *            The name of the table
     * @return A TableRow object with the data from the ResultSet
     * @exception SQLException
     *                If a database error occurs
     */
    static TableRow process(ResultSet results, String table) throws SQLException
    {
        return process(results, table, null);
    }

    /**
     * Convert the current row in a ResultSet into a TableRow object.
     *
     * @param results
     *            A ResultSet to process
     * @param table
     *            The name of the table
     * @param pColumnNames
     *            The name of the columns in this resultset
     * @return A TableRow object with the data from the ResultSet
     * @exception SQLException
     *                If a database error occurs
     */
    static TableRow process(ResultSet results, String table, List<String> pColumnNames) throws SQLException
    {
        ResultSetMetaData meta = results.getMetaData();
        int columns = meta.getColumnCount() + 1;

        // If we haven't been passed the column names try to generate them from the metadata / table
        List<String> columnNames = pColumnNames != null ? pColumnNames :
                                        ((table == null) ? getColumnNames(meta) : getColumnNames(table));

        TableRow row = new TableRow(canonicalize(table), columnNames);

        // Process the columns in order
        // (This ensures maximum backwards compatibility with
        // old JDBC drivers)
        for (int i = 1; i < columns; i++)
        {
            String name = meta.getColumnName(i);
            int jdbctype = meta.getColumnType(i);

            switch (jdbctype)
            {
                case Types.BIT:
                    row.setColumn(name, results.getBoolean(i));
                    break;

                case Types.INTEGER:
                case Types.NUMERIC:
                    if (isOracle)
                    {
                        long longValue = results.getLong(i);
                        if (longValue <= (long)Integer.MAX_VALUE)
                        {
                            row.setColumn(name, (int) longValue);
                        }
                        else
                        {
                            row.setColumn(name, longValue);
                        }
                    }
                    else
                    {
                        row.setColumn(name, results.getInt(i));
                    }
                    break;

                case Types.DECIMAL:
                case Types.BIGINT:
                    row.setColumn(name, results.getLong(i));
                    break;

                case Types.DOUBLE:
                    row.setColumn(name, results.getDouble(i));
                    break;

                case Types.CLOB:
                    if (isOracle)
                    {
                        row.setColumn(name, results.getString(i));
                    }
                    else
                    {
                        throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype);
                    }
                    break;

                case Types.VARCHAR:
                    try
                    {
                        byte[] bytes = results.getBytes(i);

                        if (bytes != null)
                        {
                            String mystring = new String(results.getBytes(i), "UTF-8");
                            row.setColumn(name, mystring);
                        }
                        else
                        {
                            row.setColumn(name, results.getString(i));
                        }
                    }
                    catch (UnsupportedEncodingException e)
                    {
                        log.error("Unable to parse text from database", e);
                    }
                    break;

                case Types.DATE:
                    row.setColumn(name, results.getDate(i));
                    break;

                case Types.TIME:
                    row.setColumn(name, results.getTime(i));
                    break;

                case Types.TIMESTAMP:
                    row.setColumn(name, results.getTimestamp(i));
                    break;

                default:
                    throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype);
            }

            // Determines if the last column was null, and sets the tablerow accordingly
            if (results.wasNull())
            {
                row.setColumnNull(name);
            }
        }

        // Now that we've prepped the TableRow, reset the flags so that we can detect which columns have changed
        row.resetChanged();
        return row;
    }

    /**
     * Return the name of the primary key column. We assume there's only one
     * primary key per table; if there are more, only the first one will be
     * returned.
     * 
     * @param row
     *            The TableRow to return the primary key for.
     * @return The name of the primary key column, or null if the row has no
     *         primary key.
     * @exception SQLException
     *                If a database error occurs
     */
    public static String getPrimaryKeyColumn(TableRow row) throws SQLException
    {
        return getPrimaryKeyColumn(row.getTable());
    }

    /**
     * Return the name of the primary key column in the given table. We assume
     * there's only one primary key per table; if there are more, only the first
     * one will be returned.
     * 
     * @param table
     *            The name of the RDBMS table
     * @return The name of the primary key column, or null if the table has no
     *         primary key.
     * @exception SQLException
     *                If a database error occurs
     */
    protected static String getPrimaryKeyColumn(String table)
            throws SQLException
    {
        ColumnInfo info = getPrimaryKeyColumnInfo(table);

        return (info == null) ? null : info.getName();
    }

    /**
     * Return column information for the primary key column, or null if the
     * table has no primary key. We assume there's only one primary key per
     * table; if there are more, only the first one will be returned.
     * 
     * @param table
     *            The name of the RDBMS table
     * @return A ColumnInfo object, or null if the table has no primary key.
     * @exception SQLException
     *                If a database error occurs
     */
    static ColumnInfo getPrimaryKeyColumnInfo(String table) throws SQLException
    {
        Collection<ColumnInfo> cinfo = getColumnInfo(canonicalize(table));

        for (ColumnInfo info : cinfo)
        {
            if (info.isPrimaryKey())
            {
                return info;
            }
        }

        return null;
    }

    /**
     * Execute SQL as a PreparedStatement on Connection. Bind parameters in
     * columns to the values in the table row before executing.
     * 
     * @param connection
     *            The SQL connection
     * @param sql
     *            The query to execute
     * @param columns
     *            The columns to bind
     * @param row
     *            The row
     * @return The number of rows affected by the query.
     * @exception SQLException
     *                If a database error occurs
     */
    private static void execute(Connection connection, String sql, Collection<ColumnInfo> columns, TableRow row) throws SQLException
    {
        PreparedStatement statement = null;

        if (log.isDebugEnabled())
        {
            log.debug("Running query \"" + sql + "\"");
        }

        try
        {
            statement = connection.prepareStatement(sql);
        	loadParameters(statement, columns, row);
            statement.execute();
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }

    private static int executeUpdate(Connection connection, String sql, Collection<ColumnInfo> columns, TableRow row) throws SQLException
    {
        PreparedStatement statement = null;

        if (log.isDebugEnabled())
        {
            log.debug("Running query \"" + sql + "\"");
        }

        try
        {
            statement = connection.prepareStatement(sql);
        	loadParameters(statement, columns, row);
            return statement.executeUpdate();
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }

    /**
     * Return metadata about a table.
     * 
     * @param table
     *            The name of the table
     * @return An map of info.
     * @exception SQLException
     *                If a database error occurs
     */
    private static Map<String, ColumnInfo> getColumnInfoInternal(String table) throws SQLException
    {
        String ctable = canonicalize(table);
        Map<String, ColumnInfo> results = info.get(ctable);

        if (results != null)
        {
            return results;
        }

        results = retrieveColumnInfo(ctable);
        info.put(ctable, results);

        return results;
    }

    /**
     * Read metadata about a table from the database.
     * 
     * @param table
     *            The RDBMS table.
     * @return A map of information about the columns. The key is the name of
     *         the column, a String; the value is a ColumnInfo object.
     * @exception SQLException
     *                If there is a problem retrieving information from the
     *                RDBMS.
     */
    private static Map<String, ColumnInfo> retrieveColumnInfo(String table) throws SQLException
    {
        Connection connection = null;
        ResultSet pkcolumns = null;
        ResultSet columns = null;
        
        try
        {
            String schema = ConfigurationManager.getProperty("db.schema");
            if(StringUtils.isBlank(schema)){
                schema = null;
            }
            String catalog = null;
            
            int dotIndex = table.indexOf('.'); 
            if (dotIndex > 0)
            {
                catalog = table.substring(0, dotIndex);
                table = table.substring(dotIndex + 1, table.length());
                log.warn("catalog: " + catalog);
                log.warn("table: " + table);
            }
            
            connection = getConnection();

            DatabaseMetaData metadata = connection.getMetaData();
            Map<String, ColumnInfo> results = new HashMap<String, ColumnInfo>();

            int max = metadata.getMaxTableNameLength();
            String tname = ((max > 0) && (table.length() >= max)) ? table
                    .substring(0, max - 1) : table;
            
            pkcolumns = metadata.getPrimaryKeys(catalog, schema, tname);
            
            Set<String> pks = new HashSet<String>();

            while (pkcolumns.next())
            {
                pks.add(pkcolumns.getString(4));
            }

            columns = metadata.getColumns(catalog, schema, tname, null);

            while (columns.next())
            {
                String column = columns.getString(4);
                ColumnInfo cinfo = new ColumnInfo();
                cinfo.setName(column);
                cinfo.setType((int) columns.getShort(5));

                if (pks.contains(column))
                {
                    cinfo.setIsPrimaryKey(true);
                }

                results.put(column, cinfo);
            }

            return Collections.unmodifiableMap(results);
        }
        finally
        {
            if (pkcolumns != null)
            {
                try { pkcolumns.close(); } catch (SQLException sqle) { }
            }

            if (columns != null)
            {
                try { columns.close(); } catch (SQLException sqle) { }
            }

            if (connection != null)
            {
                try { connection.close(); } catch (SQLException sqle) { }
            }
        }
    }

    /**
     * Provide a means for a (web) application to cleanly terminate the connection pool.
     * @throws SQLException
     */
    public static synchronized void shutdown() throws SQLException
    {
        if (initialized)
        {
            dataSource = null;
            initialized = false;
        }
    }

    /**
     * Initialize the DatabaseManager.
     */
    private static synchronized void initialize() throws SQLException
    {
        if (initialized)
        {
            return;
        }

        try
        {
            String jndiName = ConfigurationManager.getProperty("db.jndi");
            if (!StringUtils.isEmpty(jndiName))
            {
                try
                {
                    javax.naming.Context ctx = new InitialContext();
                    javax.naming.Context env = ctx == null ? null : (javax.naming.Context)ctx.lookup("java:/comp/env");
                    dataSource = (DataSource)(env == null ? null : env.lookup(jndiName));
                }
                catch (Exception e)
                {
                    log.error("Error retrieving JNDI context: " + jndiName, e);
                }

                if (dataSource != null)
                {
                    if (isOracle)
                    {
                        sqlOnBorrow = "ALTER SESSION SET current_schema=" + ConfigurationManager.getProperty("db.username").trim().toUpperCase();
                    }

                    log.debug("Using JNDI dataSource: " + jndiName);
                }
                else
                {
                    log.info("Unable to locate JNDI dataSource: " + jndiName);
                }
            }

            if (isOracle)
            {
                if (!StringUtils.isEmpty(ConfigurationManager.getProperty("db.postgres.schema")))
                {
                    sqlOnBorrow = "SET SEARCH_PATH TO " + ConfigurationManager.getProperty("db.postgres.schema").trim();
                }
            }

            if (dataSource == null)
            {
                if (!StringUtils.isEmpty(jndiName))
                {
                    log.info("Falling back to creating own Database pool");
                }

                dataSource = DataSourceInit.getDatasource();
            }

            initialized = true;
        }
        catch (SQLException se)
        {
            // Simply throw up SQLExceptions
            throw se;
        }
        catch (Exception e)
        {
            // Need to be able to catch other exceptions. Pretend they are
            // SQLExceptions, but do log
            log.warn("Exception initializing DB pool", e);
            throw new SQLException(e.toString(), e);
        }
    }

	/**
	 * Iterate over the given parameters and add them to the given prepared statement. 
	 * Only a select number of datatypes are supported by the JDBC driver.
	 *
	 * @param statement
	 * 			The unparameterized statement.
	 * @param parameters
	 * 			The parameters to be set on the statement.
	 */
	protected static void loadParameters(PreparedStatement statement, Object[] parameters) throws SQLException
    {
		statement.clearParameters();

        int idx = 1;
        for (Object parameter : parameters)
	    {
	    	if (parameter instanceof String)
	    	{
	    		statement.setString(idx,(String) parameter);
	    	}
            else if (parameter instanceof Long)
            {
                statement.setLong(idx,((Long) parameter).longValue());
            }
	    	else if (parameter instanceof Integer)
	    	{
	    		statement.setInt(idx,((Integer) parameter).intValue());
	    	}
            else if (parameter instanceof Short)
            {
                statement.setShort(idx,((Short) parameter).shortValue());
            }
            else if (parameter instanceof Date)
            {
                statement.setDate(idx,(Date) parameter);
            }
            else if (parameter instanceof Time)
            {
                statement.setTime(idx,(Time) parameter);
            }
            else if (parameter instanceof Timestamp)
            {
                statement.setTimestamp(idx,(Timestamp) parameter);
            }
	    	else if (parameter instanceof Double)
	    	{
	    		statement.setDouble(idx,((Double) parameter).doubleValue());
	    	}
	    	else if (parameter instanceof Float)
	    	{
	    		statement.setFloat(idx,((Float) parameter).floatValue());
	    	}
            else if (parameter == null)
            {
                throw new SQLException("Attempting to insert null value into SQL query.");
            }
	    	else
	    	{
	    		throw new SQLException("Attempting to insert unknown datatype ("+parameter.getClass().getName()+") into SQL statement.");
	    	}

            idx++;
	    }
	}

    private static void loadParameters(PreparedStatement statement, Collection<ColumnInfo> columns, TableRow row) throws SQLException
    {
        int count = 0;
        for (ColumnInfo info : columns)
        {
            count++;
            String column = info.getCanonicalizedName();
            int jdbctype = info.getType();

            if (row.isColumnNull(column))
            {
                statement.setNull(count, jdbctype);
            }
            else
            {
                switch (jdbctype)
                {
                    case Types.BIT:
                        statement.setBoolean(count, row.getBooleanColumn(column));
                        break;

                    case Types.INTEGER:
                        if (isOracle)
                        {
                            statement.setLong(count, row.getLongColumn(column));
                        }
                        else
                        {
                            statement.setInt(count, row.getIntColumn(column));
                        }
                        break;

                    case Types.NUMERIC:
                    case Types.DECIMAL:
                        statement.setLong(count, row.getLongColumn(column));
                        // FIXME should be BigDecimal if TableRow supported that
                        break;

                    case Types.BIGINT:
                        statement.setLong(count, row.getLongColumn(column));
                        break;

                    case Types.CLOB:
                        if (isOracle)
                        {
                            // Support CLOBs in place of TEXT columns in Oracle
                            statement.setString(count, row.getStringColumn(column));
                        }
                        else
                        {
                            throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype);
                        }
                        break;

                    case Types.VARCHAR:
                        statement.setString(count, row.getStringColumn(column));
                        break;

                    case Types.DATE:
                        statement.setDate(count, new java.sql.Date(row.getDateColumn(column).getTime()));
                        break;

                    case Types.TIME:
                        statement.setTime(count, new Time(row.getDateColumn(column).getTime()));
                        break;

                    case Types.TIMESTAMP:
                        statement.setTimestamp(count, new Timestamp(row.getDateColumn(column).getTime()));
                        break;

                    default:
                        throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype);
                }
            }
        }
    }

    /**
     * Postgres-specific row insert, combining getnextid() and insert into single statement for efficiency
     * @param context
     * @param row
     * @return
     * @throws SQLException
     */
    private static int doInsertPostgres(Context context, TableRow row) throws SQLException
    {
        String table = row.getTable();

        Collection<ColumnInfo> info = getColumnInfo(table);
        Collection<ColumnInfo> params = new ArrayList<ColumnInfo>();

        String primaryKey = getPrimaryKeyColumn(table);
        String sql = insertSQL.get(table);

        boolean firstColumn = true;
        boolean foundPrimaryKey = false;
        if (sql == null)
        {
            // Generate SQL and filter parameter columns
            StringBuilder insertBuilder = new StringBuilder("INSERT INTO ").append(table).append(" ( ");
            StringBuilder valuesBuilder = new StringBuilder(") VALUES ( ");
            for (ColumnInfo col : info)
            {
                if (firstColumn)
                {
                    firstColumn = false;
                }
                else
                {
                    insertBuilder.append(",");
                    valuesBuilder.append(",");
                }

                insertBuilder.append(col.getName());

                if (!foundPrimaryKey && col.isPrimaryKey())
                {
                    valuesBuilder.append("getnextid('").append(table).append("')");
                    foundPrimaryKey = true;
                }
                else
                {
                    valuesBuilder.append('?');
                    params.add(col);
                }
            }

            sql = insertBuilder.append(valuesBuilder.toString()).append(") RETURNING ").append(getPrimaryKeyColumn(table)).toString();
            insertSQL.put(table, sql);
        }
        else
        {
            // Already have SQL, just filter parameter columns
            for (ColumnInfo col : info)
            {
                if (!foundPrimaryKey && col.isPrimaryKey())
                {
                    foundPrimaryKey = true;
                }
                else
                {
                    params.add(col);
                }
            }            
        }

        PreparedStatement statement = null;

        if (log.isDebugEnabled())
        {
            log.debug("Running query \"" + sql + "\"");
        }

        ResultSet rs = null;
        try
        {
            statement = context.getDBConnection().prepareStatement(sql);
        	loadParameters(statement, params, row);
            rs = statement.executeQuery();
            rs.next();
            return rs.getInt(1);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException sqle)
                {
                }
            }

            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }

    /**
     * Generic version of row insertion with separate id get / insert
     * @param context
     * @param row
     * @return
     * @throws SQLException
     */
    private static int doInsertGeneric(Context context, TableRow row) throws SQLException
    {
        int newID = -1;
        String table = row.getTable();
        PreparedStatement statement = null;
        ResultSet rs = null;

        try
        {
            // Get an ID (primary key) for this row by using the "getnextid"
            // SQL function in Postgres, or directly with sequences in Oracle
            if (isOracle)
            {
                statement = context.getDBConnection().prepareStatement("SELECT " + table + "_seq" + ".nextval FROM dual");
            }
            else
            {
                statement = context.getDBConnection().prepareStatement("SELECT getnextid(?) AS result");
                loadParameters(statement, new Object[] { table });
            }
            rs = statement.executeQuery();
            rs.next();
            newID = rs.getInt(1);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException sqle) { }
            }

            if (statement != null)
            {
                try { statement.close(); } catch (SQLException sqle) { }
            }
        }

        if (newID < 0)
        {
            throw new SQLException("Unable to retrieve sequence ID");
        }

        // Set the ID in the table row object
        row.setColumn(getPrimaryKeyColumn(table), newID);
        Collection<ColumnInfo> info = getColumnInfo(table);

        String sql = insertSQL.get(table);
        if (sql == null)
        {
            StringBuilder sqlBuilder = new StringBuilder().append("INSERT INTO ").append(table).append(" ( ");

            boolean firstColumn = true;
            for (ColumnInfo col : info)
            {
                if (firstColumn)
                {
                    sqlBuilder.append(col.getName());
                    firstColumn = false;
                }
                else
                {
                    sqlBuilder.append(",").append(col.getName());
                }
            }

            sqlBuilder.append(") VALUES ( ");

            // Values to insert
            firstColumn = true;
            for (int i = 0; i < info.size(); i++)
            {
                if (firstColumn)
                {
                    sqlBuilder.append("?");
                    firstColumn = false;
                }
                else
                {
                    sqlBuilder.append(",").append("?");
                }
            }

            // Watch the syntax
            sqlBuilder.append(")");
            sql = sqlBuilder.toString();
            insertSQL.put(table, sql);
        }

        execute(context.getDBConnection(), sql, info, row);
        return newID;
    }

    /**
     * Main method used to perform tests on the database
     *
     * @param args The command line arguments
     */
    public static void main(String[] args)
    {
        // Get something from dspace.cfg to get the log lines out the way
        String url = ConfigurationManager.getProperty("db.url");

        // Try to connect to the database
        System.out.println("\nAttempting to connect to database: ");
        System.out.println(" - URL: " + url);
        System.out.println(" - Driver: " + ConfigurationManager.getProperty("db.driver"));
        System.out.println(" - Username: " + ConfigurationManager.getProperty("db.username"));
        System.out.println(" - Password: " + ConfigurationManager.getProperty("db.password"));
        System.out.println(" - Schema: " + ConfigurationManager.getProperty("db.schema"));
        System.out.println("\nTesting connection...");
        try
        {
            Connection connection = DatabaseManager.getConnection();
            connection.close();
        }
        catch (SQLException sqle)
        {
            System.err.println("\nError: ");
            System.err.println(" - " + sqle);
            System.err.println("\nPlease see the DSpace documentation for assistance.\n");
            System.exit(1);
        }

        System.out.println("Connected successfully!\n");
    }

    public static void applyOffsetAndLimit(StringBuffer query, List<Serializable> params, int offset, int limit){
        if(!isOracle()){
            offsetAndLimitPostgresQuery(query,params,offset,limit);
        }else{
            offsetAndLimitOracleQuery(query,params,offset,limit);
        }
    }

    private static void offsetAndLimitPostgresQuery(StringBuffer query , List<Serializable> params, int offset, int limit){
        query.append(" OFFSET ? LIMIT ?");
        params.add(offset);
        params.add(limit);
    }

    private static void offsetAndLimitOracleQuery(StringBuffer query , List<Serializable> params, int offset, int limit)
    {
        // prepare the LIMIT clause
        if (limit > 0 || offset > 0)
        {
            query.insert(0, "SELECT /*+ FIRST_ROWS(n) */ rec.*, ROWNUM rnum  FROM (");
            query.append(") ");
        }

        if (limit > 0)
        {
            query.append("rec WHERE rownum<=? ");
            if (offset > 0)
            {
                params.add(Integer.valueOf(limit + offset));
            }
            else
            {
                params.add(Integer.valueOf(limit));
            }
        }

        if (offset > 0)
        {
            query.insert(0, "SELECT * FROM (");
            query.append(") WHERE rnum>?");
            params.add(Integer.valueOf(offset));
        }
    }

}
