/*
 * Copyright (C) 1999-2004 <a href="mailto:mandarax@jbdietrich.com">Jens Dietrich</a>
 *
 * 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 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.mandarax.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.WeakHashMap;
import javax.sql.DataSource;
import org.mandarax.util.logging.LogCategories;
import org.mandarax.kernel.Session;

/**
 * Implementation of a function based on a SQL query.
 * The query contains variable parameters represented by the VARIABLE_PROXY
 * constants, just like the query strings used in JDBC prepared statements
 * (the value of this variable is "?").
 * The return value is the one row in the result set converted to an object.
 * If the result set has no rows or more than one row,
 * an IllegalArgumentException is thrown.
 * Therefore, queries will typically retrieve one column and have a where
 * clause with a primary key condition.
 * <p>
 * <strong>Example:</strong>The query <code>SELECT NAME FROM CUSTOMER WHERE ID=?</code>
 * will define a function that takes one value (the id) as input and returns
 * one value (the name of the customer).
 * <br>
 * The (java) return type and the parameter types must be declared.
 * Defaults (Object/Object[]) will be used otherwise.
 * <br>
 * Internally, we are using PreparedStatements. Since these statements can be reused,
 * they are cached by connection using weak references (so that the function
 * does not keep the connection alive if nobody else does it).
 * @see java.sql.PreparedStatement
 * @see org.mandarax.sql.SQLObjectRelationalMapping
 * @author <A HREF="mailto:mandarax@jbdietrich.com">Jens Dietrich</A>
 * @version 3.3.1 <9 August 2004>
 * @since 1.6
 */
public class SQLFunction  implements org.mandarax.kernel.Function, LogCategories {

    public static final String        VARIABLE_PROXY          = "?";
    private String                     name                    = null;
    private DataSource                 dataSource              = null;
    private String                     query                   = null;
    private SQLObjectRelationalMapping objectRelationalMapping = null;
    private Class[] structure       = { Object.class };
    private transient Map     statementCache  = new WeakHashMap ();
    private boolean cacheStatements = true;
    private boolean closeConnection = true;

    /**
     * Constructor.
     */
    public SQLFunction() {
        super ();
    }

    /**
     * Set the data source.
     * @return a data source.
     */
    public DataSource getDataSource() {
        return dataSource;
    }

    /**
     * Get the name of the function.
     * @return the name of the predicate
     */
    public String getName() {
        return name;
    }

    /**
     * Get the return type.
     * @return the return type of the function
     */
    public Class getReturnType() {
        return objectRelationalMapping.getTargetType ();
    }

    /**
     * Get the query string.
     * @return a query
     */
    public String getQuery() {
        return query;
    }

    /**
     * Get the type structure of the object, e.g. the types of terms
     * that can be used with this constructor.
     * <strong>Warning:</strong>Number and types must be consistent with
     * the query and the column types in the table(s).
     * @return an array of classes
     */
    public java.lang.Class[] getStructure() {
        return structure;
    }

    /**
     * Set the type structure of the object, e.g. the types of terms
     * that can be used with this constructor.
     * <strong>Warning:</strong>Number and types must be consistent with
     * the query and the column types in the table(s).
     * @return struct an array of classes
     */
    public void setStructure(Class[] struct) {
        structure = struct;
    }

    /**
     * Set the object relational mapping used.
     * @param map a mapping
     */
    public void setObjectRelationalMapping(SQLObjectRelationalMapping map) {
        objectRelationalMapping = map;
    }

    /**
     * Get the object relational mapping.
     * @return a mapping
     */
    public SQLObjectRelationalMapping getObjectRelationalMapping() {
        return objectRelationalMapping;
    }

    /**
     * Get a prepared statement for a string.
     * @return a prepared statement
     * @param con a database connection
     */
    private PreparedStatement getStatement(Connection con) throws SQLException {
        if(con == null) {
            return null;
        }
        PreparedStatement stmt = null;
        if(cacheStatements) {
			if (statementCache==null) statementCache  = new WeakHashMap ();
            stmt = (PreparedStatement) statementCache.get (con);
            if(stmt == null) {
                stmt = con.prepareStatement (query);
				statementCache.put (con, stmt);
            } 
            else {
                LOG_SQL.debug ("Using cached SQL for SQLFunction");
            }
        } 
        else {
            stmt = con.prepareStatement (query);
        }
        return stmt;
    }

    /**
     * Perform the function or predicate using an array of terms as parameters.
     * @return the result of the perform operation
     * @param parameter an array of terms
     * @param session a session object
     * @throws java.lang.UnsupportedOperationException
     * @throws java.lang.IllegalArgumentException thrown if any problem occurs, including connection
     * problems, or the result set having one or more than one rows
     */
    public Object perform(org.mandarax.kernel.Term[] parameter,Session session) throws UnsupportedOperationException, IllegalArgumentException {
        Connection con = null;
        try {

            // get connection and statement
            // @todo database login
            con  = dataSource.getConnection ();
            PreparedStatement stmt = getStatement (con);

            LOG_SQL.debug ("Preparing SQL " + stmt);

            // set the parameters
            for(int i = 0; i < parameter.length; i++) {
                stmt.setObject (i + 1, parameter[i].resolve (session));
            }

            // perform query
            LOG_SQL.debug ("Excecuting SQL " + stmt);

            ResultSet resultSet = stmt.executeQuery ();
            Object    result    = null;

            if(resultSet.next ()) {
                result = objectRelationalMapping.buildObject (resultSet);
                LOG_SQL.debug ("Performing SQL function yields value " + result);
            } else {
                LOG_SQL.warn ("Performing SQL function yields no value (the result set is empty)");
                release(con);
                throw new IllegalArgumentException ("Query yields no results for these values");
            }
            if(resultSet.next ()) {
                LOG_SQL.warn ("Query yields more than one results for these values");
                release(con);
                throw new IllegalArgumentException ("Query yields more than one results for these values");
            }
			release(con);
            return result;
        } catch(SQLException x) {
            LOG_SQL.error ("Exception executing SQL", x);
            release(con);
            throw new IllegalArgumentException ("Problem performing SQL function");
        }
    }
    /**
     * Release the connection used to invoke the function.
     * @param con the connection
	 */
    private void release(Connection con) {
    	try {
    		if (con!=null && closeConnection) {
    			con.close();
    			if (LOG_SQL.isDebugEnabled()) LOG_SQL.debug("JDBC connection released by SQLFunction " + this);	
    		}
    	}
    	catch (SQLException x) {
    		LOG_SQL.error("Exception closing jdbc connection in SQLFunction " + this,x);
    	}
    }	
    /**
     * Set the data source.
     * @param ds a data source.
     */
    public void setDataSource(DataSource ds) {
        dataSource = ds;
    }

    /**
     * Set the query.
     * @param q a query string
     */
    public void setQuery(String q) {
        query = q;
    }

    /**
     * Set the name.
     * @param n a name
     */
    public void setName(String n) {
        name = n;
    }

    /**
     * Indicates whether the object (usually a term or a clause set) can be performed
     * using the java semantics.
     * @return true
     */
    public boolean isExecutable() {
        return true;
    }

    /**
     * Convert the object to a string.
     * @return a string
     */
    public String toString() {
        return(name == null)
              ? super.toString ()
              : name;
    }

    /**
     * Compare objects.
     * @param obj another object
     * @return a boolean
     */
    public boolean equals(Object obj) {
        if((obj != null) && (obj instanceof SQLFunction)) {
            SQLFunction f      = (SQLFunction) obj;
            boolean     result = true;

            // compare query, name and data source
            // bugfix in 1.8 - thanks to chenjb@gsta.com
            result = (name == null)?(f.name == null):name.equals (f.name);
            result = result && ((dataSource == null)?(f.dataSource == null):dataSource.equals (f.dataSource));
            result = result && ((query == null)?(f.query == null): query.equals (f.query));
            result = result && ((objectRelationalMapping == null)?(f.objectRelationalMapping == null):objectRelationalMapping.equals(f.objectRelationalMapping));

            Class[] c1 = f.structure;
            Class[] c2 = structure;

            if(c1 == null) {
                result = c2 == null;
            } else {
                result = result && (c1.length == c2.length);

                for(int i = 0; i < c1.length; i++) {
                    result = result && (c1[i] == c2[i]);
                }
            }

            return result;
        } else {
            return false;
        }
    }

    /**
     * Get the hashcode of the object.
     * @return the hash code of the object
     */
    public int hashCode() {
        return((name == null)
               ? 0
               : name.hashCode ()) ^ ((query == null)
                                      ? 0
                                      : query.hashCode ()) ^ ((dataSource
                                      == null)
                                                              ? 0
                                                              : dataSource
                                                              .hashCode ());
    }
	/**
	 * Indicates whether to close the connection at the end.
	 * @return a boolean
	 */
	public boolean isCloseConnection() {
		return closeConnection;
	}

	/**
	 * Sets whether to close the connection at the end.
	 * @param closeConnection a boolean
	 */
	public void setCloseConnection(boolean closeConnection) {
		this.closeConnection = closeConnection;
	}

}
