/*
    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.
 *
 */