/**
 * class relConnection - methods related to java.sql.Connection
 **/
package com.CellFusion.Relational;

import java.sql.*;
import java.util.Vector;

public class RelConnection extends Object{
	static public DatabaseMetaData	gDefaultMetaData = null;
	static public RelConnectionInfo	gDefaultConnectionInfo = null;
	static public boolean			gDefaultConnectionIsOracle = false;

    public static RelConnection createConnection(){
    	if (gDefaultConnectionInfo == null) {
    		String dbUser = Util.getCFProperty("dbUser");
    		if ("*ws*".equals( dbUser )) {
    			dbUser = Util.getCFProperty("workstation");
    		}
    		gDefaultConnectionInfo = new RelConnectionInfo(
    			Util.getCFProperty("dbName"),
    			Util.getCFProperty("dbDriver"),
    			Util.getCFProperty("dbURL"),
    			dbUser,
    			Util.getCFProperty("dbP") 
    		);
    	}
    	return RelConnection.createConnection( gDefaultConnectionInfo );
	}
		
    public static RelConnection createConnection( RelConnectionInfo connectionInfo ){
    	if (connectionInfo == null) {
    		Util.reportErr("RelConnection.startConnection: connectionInfo null!");
    		return null;
    	}
    	String connectionName =	connectionInfo.getConnectionName();
	    String driver =  		connectionInfo.getDriverName();
	    String url =			connectionInfo.getURL();
	    String user =			connectionInfo.getUser();
	    String pass =			connectionInfo.getPassword();
	    
	    if (driver == null || url == null || user == null || pass == null) {
	    	Util.reportErr("Info in cellfusion.properties missing" );
	    	return null;
	    }
	    if (driver.length() <= 0 || url.length() <= 0 || user.length() <= 0 || pass.length() < 2) {
	    	Util.reportErr("Info in cellfusion.properties missing" );
	    	return null;
	    }
	    StringBuffer passBuffer = new StringBuffer( pass.substring(1) );
	    for (int i = 0; i < passBuffer.length(); i ++ ) {
	    	char ch = passBuffer.charAt(i);
	    	if (ch == 'a') {
	    		ch = 'z';
	    	} else if (ch == 'A') {
	    		ch = 'Z';
	    	} else if (ch == '0') {
	    		ch = '9';
	    	} else {
	    		ch = (char) (((int) ch) - 1);
	    	}
	    	passBuffer.setCharAt( i, ch );
	    }
	    pass = passBuffer.toString();
	   //System.out.println("Hello: " + pass);
	    try { 
    	Util.log( "===== New Connection to " + connectionName + " with Driver '" + driver + "'...");
	    	Class.forName( driver );
	    	//Util.log("...driver loaded.");
	    }
	    catch( Exception e ) {
	    	Util.reportErr("startConnection loading Driver: " + e );
	    	return null;
	    }

	    //Util.log("Opening connection...");
	    Connection connection = null;
	    try {
	    	connection = DriverManager.getConnection( url, user, pass  );
	    	if (connection != null) {
	    		connection.setAutoCommit( false );
	    	} else {
	    		Util.reportErr("startConnection: getConnection returned null! ");
	    	}	
		}
		catch (Exception e) {
	    	Util.reportErr("startConnection getConnection: " + e );
			connection = null;
		}
		if (connection == null) {
			Util.reportErr("Not able to open connection!");
			return null;
		}else{
			// Util.log("...connection opened.");
		}
		
		boolean isOracle = false;
		boolean done = false;
		
		if (connectionInfo == gDefaultConnectionInfo && gDefaultMetaData != null) {
			isOracle = gDefaultConnectionIsOracle;
			Util.log("...additional connection opened.");
		} else {
			try {
				DatabaseMetaData meta = connection.getMetaData();
				
				String dbName = meta.getDatabaseProductName();
				isOracle = "Oracle".equals( dbName );
				Util.log("");
				Util.log("Database Product Name:          " + dbName );

				if (connectionInfo == gDefaultConnectionInfo) {
					gDefaultConnectionIsOracle = isOracle;
					gDefaultMetaData = meta; // set this global only after gDefaultIsOracle is already valid
				} 
				
				// output the multiline version string in a formatted way
				String dbVersion = meta.getDatabaseProductVersion();
				int newLineIx = dbVersion.indexOf('\n');
				while (newLineIx >= 0) {
					Util.log("Database Product Version:       " + dbVersion.substring( 0, newLineIx ) );
					dbVersion = dbVersion.substring( newLineIx + 1 );
					newLineIx = dbVersion.indexOf('\n');
				}
				Util.log("Database Product Version:       " + dbVersion );
				
				Util.log("Driver Name:                    " + meta.getDriverName() );
				Util.log("Driver Version:                 " + meta.getDriverVersion() );
				Util.log("Default Transaction Isolation:  " + meta.getDefaultTransactionIsolation() );
				Util.log("Maximum Connections:            " + meta.getMaxConnections() );
				Util.log("");
			}
			catch (Exception e) {
				 Util.log("Exception accesing meta data: " + e );
			}
		}
		RelConnection relConnection = new RelConnection();
    		relConnection.mConnectionInfo = connectionInfo;
    		relConnection.mIsOracle = isOracle;
    		relConnection.mConnection = connection;
	    	relConnection.mTAOpen = false;
	    	relConnection.mTAUpdated = false;
	    	relConnection.mTALocked = false;
		return relConnection;
    }
    
    public static boolean checkConnection(){
    	if (gConnectionPool.size() > 0) {
    		return true;
    	}
    	RelConnection connection = RelConnection.requestConnection();
    	if (connection == null) {
    		return false;
    	}
    	returnConnection( connection );
    	return true;
    }

    public void closeConnection(){
    	if (mConnection == null) {
    		return; // already closed or not opened
    	}
    	if (mTAOpen) {
    		Util.log("closeConnection: Transaction is still open!");
    		endTransaction( false );
    	}
	try {
			mConnection.close();
	}
	catch( Exception e ) {
    	Util.reportErr("RelConnection.closeConnection close: " + e );
	}
	mConnection = null;
    }
    
	public final static RelConnectionInfo gConnectionInfoOracleDemo = new RelConnectionInfo("Oracle Server","oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@38.169.27.12:1521:peach","scott","tiger");
	public final static RelConnectionInfo gConnectionInfoOracle = new RelConnectionInfo("Oracle Server","oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@38.169.27.12:1521:peach","cf","cf17925");		
	public final static RelConnectionInfo gConnectionInfoSQLAnywhere = new RelConnectionInfo("Sybase SQL Anywhere","sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:omsdb","dba","sql");
	public final static RelConnectionInfo gConnectionInfo = gConnectionInfoOracle;
		
    public boolean beginTransaction( RelTransaction transaction )
    {
    	if (mConnection == null) {
    		Util.reportErr("beginTransaction: mConnection is null!");
    		return false;
    	}
    	if (mTAOpen) {
    		Util.reportErr("beginTransaction: Another Transaction is still open!");
    		endTransaction( false );
    	}
    	mTA = transaction;
    	mTAOpen = true;
    	mTAUpdated = false;
    	mTALocked = false;
    	return mTAOpen;
    }
    
    public boolean endTransaction( boolean commit )
    {
    	if (!mTAOpen) {
    		Util.reportErr("endTransaction: The Transaction isn't open!");
    		return false;
    	}
    	if (mTA == null) {
    		Util.reportErr("endTransaction: mTA is null!");
    		return false;
    	}
    	if (mConnection == null) {
    		Util.reportErr("endTransaction: mConnection is null!");
    		mTAOpen = false;
    		mTAUpdated = false;
    		mTALocked = false;
    		return false;
    	}
    	boolean res = false;
    	//if (mTAUpdated) {  // change to always either commit or rollback
	    	try {
		    	if (commit && mTAUpdated) {
		    		mConnection.commit();
		    		Util.log("=== commit ===");
		    	} else {
		    		mConnection.rollback();
		    		Util.log("=== rollback ===");
		    	}
		    	res = true;
		    }
		    catch (Throwable e) {
		    	Util.reportErr("endTransaction (" 
		    				+ (commit ? "commit" : "rollback") 
		    				+ ") : " + e );
		    }
    	//} else {
    	//	res = true; // nothing has been changed
    	//}
    	mTAOpen = false;
    	mTAUpdated = false;
    	mTALocked = false;
    	mTA = null;
    	return res;
    }

	public int executeUpdate( String sql )
	{
		return executeUpdate( sql, false, false, false );
	}
	
	public int executeLockStatement( String sql )
	{
		return executeUpdate( sql, false, false, true );
	}
	
	public int executeDDL( String sql )
	{
		return executeUpdate( sql, true, false, false );
	}
	
	public int executeDropDDL( String sql )
	{
		return executeUpdate( sql, true, true, false );
	}
	

	public int executeUpdate( String sql, boolean ddl, boolean drop, boolean locking )
	{
		if (mTAOpen) {
			//if (!ddl) {
			//	mTAUpdated =true;
			//}
		} else {
			Util.reportErr("executeUpdate: update without open Transaction! " );
			return 0;
		}
		if (mTA == null) {
			Util.reportErr("executeUpdate: update with Transaction == null! " );
			return 0;
		}
		if (getDebugMode()) {
			Util.log("update SQL (DEBUG): " + sql );
			return 0;
		}
		if (!checkConnection()) { // not really necessary now there is a transaction used here
			mTA.incErrorCount( false );
			return 0;
		}
		boolean trace = (mTA == null) || mTA.getTraceSQL();
		int updates = 0;
		Statement stmt = requestStatement();
		if (stmt == null) {
			mTA.incErrorCount( false );
			return 0;
		}
		try {
			if (trace) {
				Util.log("update SQL: " + sql );
			}
			updates = stmt.executeUpdate( sql );
			if (locking) {
				mTALocked = true;
			}else{
				if (updates > 0) {
					mTAUpdated = true;
				}
			}
			if (!ddl) {
				if (trace) {
					Util.log("...number of updated records: " + updates );
				}
			}
		}
		catch (Throwable e) {
			mTA.incErrorCount( false );
			if (!trace) {		// do it now in case of error if trace was turned off
				Util.log("update SQL: " + sql );
			}
			Util.reportErr("RelConnection executeUpdate: " + e );
			updates = 0;
		}
		finally {
			returnStatement( stmt );
		}
		return (updates);
	}
	
	public Statement requestStatement()
	{
		if (!checkConnection()) {
			return null;
		}
		Statement stmt = null;
		try {
			stmt = mConnection.createStatement();
			if (stmt == null) {
				Util.reportErr("requestStatement: createStatement returns null! " );
			}
		}
		catch (Exception e) {
			Util.reportErr("requestStatement: Exception in createStatement: " + e );
			stmt = null;
		}
		return (stmt);
	}

	public static void returnStatement( Statement statement )
	{
		if (statement == null) {
			Util.reportErr("returnStatement: statement is null" );
		}
		try {
			statement.close();
		}
		catch( Exception e) {
			Util.reportErr("returnStatement: " + e );
		}
	}

	public static DatabaseMetaData getMetaDataOfDefaultConnection()
	{
		RelConnection connection = requestConnection();
		if (connection == null) {
			return null;
		}
		DatabaseMetaData dbmd = connection.getMetaData();
		returnConnection( connection );
		return dbmd;
	}

	public DatabaseMetaData getMetaData()
	{
		DatabaseMetaData dbmd = null;
		try {
			dbmd = mConnection.getMetaData();
			if (dbmd == null) {
				Util.log("getMetaData: Connection.getMetaData returns null! " );
			}
		}
		catch (Exception e) {
			Util.log("getMetaData: Exception: " + e.getMessage() );
			dbmd = null;
		}
		return (dbmd);
	}
	
	public boolean getDebugMode() { return mDebug; }
	
	public boolean setDebugMode( boolean debug )
	{
		boolean oldValue = mDebug;
		mDebug = debug;
		return oldValue;
	}
		
	public boolean getReserved() { return mReserved; }
	
	/** 
	 * reserve this connection (for a specific use)
	 * returns true if successful 
	 * ( meaning it can be called without knowing whether
	 *   it is already used.)
	 **/
	public synchronized boolean reserve()
	{
		synchronized(this) {
			boolean connError = false;
			try{
				if (mConnection == null || mConnection.isClosed()) {
					connError = true; // will this catch "connection reset by peer" ? 
				}
			}
			catch (Throwable e) {
				Util.log("RelConnection.reserve called mConnection.isClosed(): " + e );
				connError = true;
			}
			if (connError) {
				synchronized(gConnectionPool) {  // watch out for deadlocks
					gConnectionPool.removeElement( this );
				}
				return false;
			}
			
			if (mReserved) { 
				return false;
			}
			mReserved = true;
			return true;
		}
	}
	
	/** 
	 * release a reservation on this connection after use
	 **/
	public synchronized boolean release()
	{
		synchronized(this) {
			if (!mReserved) { 
				return false;
			}
			mReserved = false;
			return true;
		}
	}
	
	//--- static methods---
	
	public static synchronized RelConnection requestConnection() 
	{
		synchronized (gConnectionPool) {
			RelConnection connection = null;
			int sz = gConnectionPool.size();
			//Util.log("requestConnection: size of connection pool: " + sz );
			for (int i = 0; i < sz; i++ ) {
				connection = (RelConnection) gConnectionPool.elementAt( i );
				if (connection.reserve()) {
					Util.log("requestConnection: returning existing connection from pool");
					return connection;
				}
			}
			//Util.log("requestConnection: creating new connection");
			connection = RelConnection.createConnection();  // the connection pool always uses the default connectionInfo
			if (connection == null) {
				return null; // no additional error message
			}
			if (!connection.reserve()) {
				Util.reportErr("requestConnection: Couldn't reserve new connection!");
				return null;
			}
			gConnectionPool.addElement( connection );
			return connection;
		}
	}
	
	public static synchronized void returnConnection( RelConnection connection )
	{
		if (!connection.getReserved()) {
			Util.reportErr("RelConnection.returnConnection: returned connection not reserved!");
			return;
		}
    	if (connection.mTAOpen || connection.mTA != null) {
    		Util.reportErr("RelConnection.returnConnection: The Transaction wasn't ended!");
    	}
		connection.release();
		//Util.log("returnConnection: connection returned");
	}
		
	
	public static synchronized void closeAllConnections()
	{
		try {
			synchronized( gConnectionPool ) {  // just to make sure
				int sz = gConnectionPool.size();
				Util.log("---closing all connections (pool size " + sz + ")---");
				for ( int i = 0; i < sz; i++) {
					RelConnection connection = (RelConnection) gConnectionPool.elementAt( i );
					if (connection.getReserved()) {
						Util.reportErr("RelConnection.closeAllConnections: connection still reserved!");
					}
					connection.closeConnection();
				}
				gConnectionPool.removeAllElements();
			}
		}
		catch (Throwable e ) { 
			Util.reportErr("Exception in closeAllConnections: " + e );
		}
	}
		
	public boolean isOracle()
	{
		return mIsOracle;
	}
	
	public java.sql.Connection getSQLConnection() // only for testing
	{
		return mConnection;
	}

	public boolean getTAUpdated()
	{
		return mTAUpdated;
	}
	
	public boolean getTALocked()
	{
		return mTALocked;
	}
	
	//--- private variables ---

	public Connection mConnection = null;
	public Statement mCachedStatement = null;
	public RelConnectionInfo mConnectionInfo = null;
	
	public boolean mDebug = false;
	
	public boolean mTAOpen = false;
	public boolean mTAUpdated = false;
	public boolean mTALocked = false;
	public RelTransaction mTA = null;
	public boolean mReserved = false;
	public boolean mIsOracle = true;
	
	//--- static variables ---
	
	public static Vector gConnectionPool = new Vector();
}