/*
 Wotonomy: OpenStep design patterns for pure Java applications.
 Copyright (C) 2001 Michael Powers

 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, see http://www.gnu.org
 */
package net.wotonomy.jdbcadaptor;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import net.wotonomy.access.EOAdaptorChannel;
import net.wotonomy.access.EOAttribute;
import net.wotonomy.access.EOEntity;
import net.wotonomy.access.EOGeneralAdaptorException;
import net.wotonomy.access.EOSQLExpression;
import net.wotonomy.access.EOStoredProcedure;
import net.wotonomy.control.EOFetchSpecification;
import net.wotonomy.control.EOQualifier;
import net.wotonomy.foundation.NSArray;
import net.wotonomy.foundation.NSData;
import net.wotonomy.foundation.NSDictionary;
import net.wotonomy.foundation.NSKeyValueCoding;
import net.wotonomy.foundation.NSMutableDictionary;
import net.wotonomy.foundation.NSTimestamp;

/**
* Concrete implementation of EOAdaptorChannel for use with JDBC.
*
* @author ezamudio@nasoft.com
* @author $Author: cgruber $
* @version $Revision: 903 $
*/
public class JDBCChannel extends EOAdaptorChannel {

	protected boolean _fetchInProgress;
	protected ResultSet _resultSet;
	protected Statement _statement;
	protected NSArray _attsToFetch;
	protected NSArray _resultAttributes;
	protected boolean _transactionWasOpen;
	protected NSDictionary _spReturnValues;
	protected int _resultCount;

	/**
	 * Creates a new JDBCChannel.
	 * @param context The JDBCContext this channel belongs to.
	 */
	public JDBCChannel(JDBCContext context) {
		super(context);
	}

	protected JDBCContext _context() {
		return (JDBCContext)adaptorContext();
	}

	/* Sets the attributes to be fetched from the database.
	 * @see net.wotonomy.access.EOAdaptorChannel#setAttributesToFetch(net.wotonomy.foundation.NSArray)
	 */
	public void setAttributesToFetch(NSArray atts) {
		_attsToFetch = atts;
	}

	/* Returns an array with the attributes that will be fetched.
	 * @see net.wotonomy.access.EOAdaptorChannel#attributesToFetch()
	 */
	public NSArray attributesToFetch() {
		return _attsToFetch;
	}

	/* Cancels the fetch, rolling back the transaction.
	 * @see net.wotonomy.access.EOAdaptorChannel#cancelFetch()
	 */
	public void cancelFetch() {
		if (_statement == null || _resultSet == null)
			return;
		try {
			_resultSet.close();
			_statement.cancel();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("Cannot cancel fetch in database.", ex);
		}
	}

	/* Closes the jdbc channel.
	 * @see net.wotonomy.access.EOAdaptorChannel#closeChannel()
	 */
	public void closeChannel() {
		if (_statement == null)
			return;
		try {
			_statement.close();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to close the channel.", ex);
		}
	}

	/* If the fetch was done with an array of EOAttributes, returns
	 * that same array; otherwise it creates an array of EOAttributes
	 * based on the column names that will be fetched.
	 * @see net.wotonomy.access.EOAdaptorChannel#describeResults()
	 */
	public NSArray describeResults() {
		if (_resultSet == null || !_fetchInProgress)
			throw new EOGeneralAdaptorException("Cannot describe results without a result set.");
		if (_resultAttributes == null) {
			try {
				ResultSetMetaData _rsmeta = _resultSet.getMetaData();
				EOAttribute[] attarr = new EOAttribute[_rsmeta.getColumnCount()];
				for (int i = 1; i <= attarr.length; i++) {
					EOAttribute a = new EOAttribute();
					a.setName("Attribute " + (i));
					a.setColumnName(_rsmeta.getColumnName(i));
					a.setClassName(_rsmeta.getColumnClassName(i));
					a.setExternalType(_rsmeta.getColumnTypeName(i));
					a.setPrecision(_rsmeta.getPrecision(i));
					a.setScale(_rsmeta.getScale(i));
					a.setAllowsNull(_rsmeta.isNullable(i) == ResultSetMetaData.columnNullable);
					a.setWidth(_rsmeta.getColumnDisplaySize(i));
					a.setReadOnly(_rsmeta.isReadOnly(i));
					attarr[i-1] = a;
				}
				_resultAttributes = new NSArray(attarr);
			} catch (SQLException ex) {
				throw new JDBCAdaptorException("While trying to get the result set metadata.", ex);
			}
		}
		return _resultAttributes;
	}

	/* Deletes from the database the rows described by the qualifier,
	 * in the specified entity.
	 * @see net.wotonomy.access.EOAdaptorChannel#deleteRowsDescribedByQualifier(net.wotonomy.control.EOQualifier, net.wotonomy.access.EOEntity)
	 */
	public int deleteRowsDescribedByQualifier(EOQualifier q, EOEntity entity) {
		EOSQLExpression exp = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		exp.prepareDeleteExpressionForQualifier(q);
		evaluateExpression(exp);
		return _resultCount;
	}

	/* Creates a java.sql.Statement object and executes it.
	 * If there is an open transaction, the statement is executed inside it;
	 * otherwise a transaction is started, the statement executed, and
	 * the transaction is committed.
	 * @see net.wotonomy.access.EOAdaptorChannel#evaluateExpression(net.wotonomy.access.EOSQLExpression)
	 */
	public void evaluateExpression(EOSQLExpression sql) {
		if (!isOpen())
			throw new EOGeneralAdaptorException("Attempt to evaluate expression without opening the channel first.");
		try {
			_statement = _context().connection().createStatement();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("Cannot create java.sql.Statement", ex);
		}
		_resultSet = null;
		boolean isQuery = false;
		String text = sql.statement();
		try {
			//run an executeUpdate with these prefixes
			if (text.startsWith("INSERT") || text.startsWith("DELETE") || text.startsWith("UPDATE")) {
				conditionalBeginTransaction();
				_resultCount = _statement.executeUpdate(text);
				conditionalCommitTransaction();
				return;
			} else if (text.startsWith("SELECT")) {
				//run an executeQuery with SELECT
				if (_resultCount > 0)
					_statement.setMaxRows(_resultCount);
				_resultSet = _statement.executeQuery(text);
				_fetchInProgress = true;
				return;
			} else { //just plain execute
				conditionalBeginTransaction();
				isQuery = _statement.execute(text);
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to execute expression '" + text + "'", ex);
		}
		try {
			if (isQuery) {
				if (_resultCount > 0)
					_statement.setMaxRows(_resultCount);
				_resultSet = _statement.getResultSet();
			} else {
				_resultCount = _statement.getUpdateCount();
				conditionalCommitTransaction();
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to get the result set.", ex);
		}
	}

	/* Executes a stored procedure with the specified parameters.
	 * Any results that the procedure returns should be obtained
	 * by calling returnValuesForLastStoredProcedureInvocation.
	 * @see net.wotonomy.access.EOAdaptorChannel#executeStoredProcedure(net.wotonomy.access.EOStoredProcedure, net.wotonomy.foundation.NSDictionary)
	 */
	public void executeStoredProcedure(
			EOStoredProcedure proc, NSDictionary values) {
		if (!isOpen())
			throw new EOGeneralAdaptorException("Attempt to execute a stored procedure on a closed channel.");
		conditionalBeginTransaction();
		try {
			//Assemble the procedure call
			StringBuffer buf = new StringBuffer("{ call ");
			buf.append(proc.externalName());
			NSArray args = proc.arguments();
			if (args != null && args.count() > 0) {
				buf.append("[");
				for (int i = 0; i < args.count(); i++) {
					EOAttribute a = (EOAttribute)args.objectAtIndex(i);
					if (a.parameterDirection() != EOAttribute.OutParameter) {
						buf.append('?');
						buf.append(", ");
					}
				}
				buf.delete(buf.length()-2, buf.length());
				buf.append("]");
			}
			buf.append(" }");
			//get the callable statement
			CallableStatement sp = _context().connection().prepareCall(buf.toString());
			if (args != null && args.count() > 0) {
				int pos = 1;
				//set the in and inOut parameters
				for (int i = 0; i < args.count(); i++) {
					EOAttribute a = (EOAttribute)args.objectAtIndex(i);
					if (a.parameterDirection() != EOAttribute.OutParameter) {
						Object val = values.objectForKey(a.name());
						if (val == NSKeyValueCoding.NullValue)
							sp.setNull(pos, 0); //TODO: check sql type
						if (val instanceof String)
							sp.setString(pos, (String)val);
						else if (val instanceof BigDecimal)
							sp.setBigDecimal(pos, (BigDecimal)val);
						else if (val instanceof NSTimestamp)
							sp.setTimestamp(pos, (NSTimestamp)val);
						else if (val instanceof NSData)
							sp.setBytes(pos, ((NSData)val).bytes());
						else if (val instanceof Integer)
							sp.setInt(pos, ((Integer)val).intValue());
						else if (val instanceof Long)
							sp.setLong(pos, ((Long)val).longValue());
						else
							sp.setObject(pos, val);
						pos++;
					}
				}
			}
			//run the procedure
			sp.execute();
			//get the return values
			if (args != null && args.count() > 0) {
				int pos = 1;
				NSMutableDictionary retvals = new NSMutableDictionary();
				for (int i = 0; i < args.count(); i++) {
					EOAttribute a = (EOAttribute)args.objectAtIndex(i);
					if (a.parameterDirection() != EOAttribute.InParameter) {
						Object val = sp.getObject(pos);
						if (val == null)
							retvals.setObjectForKey(NSKeyValueCoding.NullValue, a.name());
						else if (val instanceof Blob) {
							try {
								retvals.setObjectForKey(new NSData(((Blob)val).getBinaryStream(), 1024), a.name());
							} catch (java.io.IOException ex) {
								//what should I do here?
								retvals.setObjectForKey(NSData.EmptyData, a.name());
							}
						} else
							retvals.setObjectForKey(val, a.name());
						pos++;
					}
				}
				_spReturnValues = retvals;
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to execute stored procedure.", ex);
		}
		conditionalCommitTransaction();
	}

	/* Fetches one row from the database
	 * @see net.wotonomy.access.EOAdaptorChannel#fetchRow()
	 */
	public NSMutableDictionary fetchRow() {
		if (_resultSet == null) {
			return null;
		}
		if (attributesToFetch() == null)
			throw new EOGeneralAdaptorException("Attempt to fetchRow without setting attributes to fetch first.");
		try {
			//If the current result set ends, there may be another one
			if (!_resultSet.next()) {
				_resultSet.close();
				_resultAttributes = null;
				_fetchInProgress = _statement.getMoreResults();
				if (_fetchInProgress)
					_resultSet = _statement.getResultSet();
				return null;
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to fetch row.", ex);
		}

		//Assemble the dictionary
		NSMutableDictionary dict = new NSMutableDictionary(attributesToFetch().count());
		try {
			for (int i = 0; i < attributesToFetch().count(); i++) {
				EOAttribute a = (EOAttribute)attributesToFetch().objectAtIndex(i);
				Object o = _resultSet.getObject(i+1);
				if (o == null)
					o = NSKeyValueCoding.NullValue;
				dict.setObjectForKey(o, a.name());
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to create row.", ex);
		}
		return dict;
	}

	/* Inserts a row into a table in the database.
	 * @see net.wotonomy.access.EOAdaptorChannel#insertRow(net.wotonomy.foundation.NSDictionary, net.wotonomy.access.EOEntity)
	 */
	public void insertRow(NSDictionary row, EOEntity entity) {
		EOSQLExpression exp = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		exp.prepareInsertExpressionWithRow(row);
		evaluateExpression(exp);
	}

	/* Indicates if a fetch is in progress; that is, if a SELECT statement
	 * was executed and there are still rows to be fetched.
	 * @see net.wotonomy.access.EOAdaptorChannel#isFetchInProgress()
	 */
	public boolean isFetchInProgress() {
		return _fetchInProgress;
	}

	/* Indicates if the channel is open.
	 * @see net.wotonomy.access.EOAdaptorChannel#isOpen()
	 */
	public boolean isOpen() {
		boolean open = (_context().connection() != null);
		try {
			open = open || !_context().connection().isClosed();
		} catch (SQLException ex) {
			open = false;
		}
		return open;
	}

	/* Opens the channel. If the adaptor context has not yet made
	 * a connection to the database, this forces the context to
	 * connect.
	 * @see net.wotonomy.access.EOAdaptorChannel#openChannel()
	 */
	public void openChannel() {
		try {
			if (_context().connection() == null || _context().connection().isClosed())
				_context().connect();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("Cannot open connection to database.", ex);
		}
	}

	/* Returns the values obtained from the last stored procedure executed.
	 * @see net.wotonomy.access.EOAdaptorChannel#returnValuesForLastStoredProcedureInvocation()
	 */
	public NSDictionary returnValuesForLastStoredProcedureInvocation() {
		return _spReturnValues;
	}

	/* Creates a SELECT expression and executes it. If the attribute array is null,
	 * then the result's metadata is used to dynamically create an array
	 * of attributes.
	 * @see net.wotonomy.access.EOAdaptorChannel#selectAttributes(net.wotonomy.foundation.NSArray, net.wotonomy.control.EOFetchSpecification, boolean, net.wotonomy.access.EOEntity)
	 */
	public void selectAttributes(
			NSArray atts, EOFetchSpecification fspec,
			boolean lock, EOEntity entity) {
		_resultAttributes = atts;
		EOSQLExpression expr = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		_fetchInProgress = true;
		expr.prepareSelectExpressionWithAttributes(atts, lock, fspec);
		//for now we store the fetch limit here
		if (fspec != null)
			_resultCount = fspec.fetchLimit();
		evaluateExpression(expr);
	}

	/* Creates and executes an UPDATE statement.
	 * @see net.wotonomy.access.EOAdaptorChannel#updateValuesInRowsDescribedByQualifier(net.wotonomy.foundation.NSDictionary, net.wotonomy.control.EOQualifier, net.wotonomy.access.EOEntity)
	 */
	public int updateValuesInRowsDescribedByQualifier(
			NSDictionary row, EOQualifier q, EOEntity entity) {
		EOSQLExpression exp = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		exp.prepareUpdateExpressionWithRow(row, q);
		evaluateExpression(exp);
		return _resultCount;
	}

	protected void conditionalBeginTransaction() {
		_transactionWasOpen = adaptorContext().hasOpenTransaction();
		if (!_transactionWasOpen)
			adaptorContext().beginTransaction();
	}

	protected void conditionalCommitTransaction() {
		if (!_transactionWasOpen)
			adaptorContext().commitTransaction();
		_transactionWasOpen = false;
	}

}
/*
* $Log$
* Revision 1.2  2006/02/18 22:59:22  cgruber
* make it compile with maven dependencies and add a cvsignore.
*
* Revision 1.1  2006/02/16 13:22:23  cgruber
* Check in all sources in eclipse-friendly maven-enabled packages.
*
* Revision 1.3  2003/08/14 02:15:11  chochos
* added lots of comments
*
* Revision 1.2  2003/08/13 20:45:20  chochos
* small fixes in evaluateExpression, which has been successfully tested with a SELECT statement.
*
* Revision 1.1  2003/08/13 20:12:48  chochos
* a subclass of EOAdaptorChannel to be used with JDBC.
*
*/