/* ======================================================================
   Parts Copyright 2006 University of Leeds, Oxford University, University of the Highlands and Islands.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

====================================================================== */

package org.bodington.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.bodington.pool.LimitedObjectPool;
import org.bodington.pool.ObjectPoolException;

/**
 * This is a wrapper around the old Bodington connection pooling.
 * You should probably use the {@link CommonsConnectionPool} instead.
 * <table border="1">
 * <tr>
 *  <th>Property</th><th>Description</th><th>Default</th><th>Required</th>
 * </tr>
 * <tr>
 *  <td>sqldatabase.url</td>
 *  <td>The JDBC URL to attempt to connect to.</td> 
 *  <td></td>
 *  <td>Yes</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.driver</td>
 *  <td>The class to use for the database driver.</td>
 *  <td></td>
 *  <td>Yes</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.user_name</td>
 *  <td>The username to connect to the database with.</td>
 *  <td></td>
 *  <td>No</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.password</td>
 *  <td>The password to connect to the database with.</td>
 *  <td></td>
 *  <td>No</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.poolsize</td>
 *  <td>The size of the database connection pool.</td>
 *  <td>10</td>
 *  <td>No</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.poolusermax</td>
 *  <td>The maximum number of connections a user can hold at once.</td>
 *  <td>2</td>
 *  <td>No</td>
 * </tr>
 * <tr>
 *  <td>sqldatabase.poolusergreedymax</td>
 *  <td>The maximum number of outstanding requests for connections a user can hold.</td>
 *  <td>10</td>
 *  <td>No</td>
 * </tr>
 * </table>
 * @author buckett
 */
public class BodingtonConnectionPool extends LimitedObjectPool implements ConnectionPool
	{
    private static Logger log = Logger.getLogger(BodingtonConnectionPool.class);
	private String url;
	private String user_name;
	private String password;
	
	/**
	 * Create a new Bodington connection pool, getting the values from the 
	 * bodington properties.
	 * @param properties The properties object containing the configuration information.
	 */
	public BodingtonConnectionPool (Properties properties) throws ConnectionPoolException
	{

		this.url = properties.getProperty( "sqldatabase.url" );
		this.user_name = properties.getProperty( "sqldatabase.user_name" );
		this.password = properties.getProperty( "sqldatabase.password" );
		
		String jdbc_driver = properties.getProperty( "sqldatabase.driver" );

	    int pool_size = 10;
	    int pool_user_max = 2;
	    int pool_user_greedy_max = 10;
	    
		try
		{
		    pool_size = Integer.parseInt( properties.getProperty( "sqldatabase.poolsize", "10" ) );
		}
		catch ( NumberFormatException nfex ){}		
		try
		{
		    pool_user_max = Integer.parseInt( properties.getProperty( "sqldatabase.poolusermax", "2" ) );
		}
		catch ( NumberFormatException nfex ){}
		try
		{
		    pool_user_greedy_max = Integer.parseInt( properties.getProperty( "sqldatabase.poolusergreedymax", "10" ) );
		}
		catch ( NumberFormatException nfex ){}

		if (jdbc_driver == null || url == null)
		    throw new ConnectionPoolException ("JDBC Driver and URL cannot be empty");
	    //make sure database driver registers itself
		try
		{
		    Class.forName( jdbc_driver );
		}
		catch (ClassNotFoundException cnfe)
		{
		    throw new ConnectionPoolException("Could not find database driver: "+ jdbc_driver);
		}
	    
		try
		{
			init( pool_size, pool_user_max, 30000, pool_user_greedy_max, 30000);
		}
		catch ( ObjectPoolException ope )
		{
		    throw new ConnectionPoolException("Failed to setup pool", ope);
		}
	}
      
	protected Object createObject()
		throws Exception
		{
		return DriverManager.getConnection( url, user_name, password );
		}

		
	protected Object initialiseObject( Object o )
		throws Exception
		{
		Connection connection = (Connection)o;
		Statement statement = null;
		ResultSet results = null;
		
		try
			{
			connection.setAutoCommit( true );
			// force exception on connections that are dead
			statement = connection.createStatement();
			results = statement.executeQuery( "SELECT * FROM objects WHERE id = 0" );
			results.close();
			
			//didn't throw exception so it's OK to use this connection.
			return connection;
			}
		catch ( SQLException ex )
			{
			log.fatal("ConnectionPool.initialiseObject() SQLException trying to init connection - got rid of it.", ex );
			}
		finally
			{
			try
				{
				if ( results != null )
					results.close();
				if ( statement != null )
					statement.close();
				}
			catch ( SQLException sqlex )
				{
				}
			}
						
		// erase this dead connection
		try
			{
			if ( !connection.isClosed() )
				connection.close();
			}
		catch ( SQLException sqlex )
			{
			}
						
		// and make a fresh one
		connection = DriverManager.getConnection( url, user_name, password );
		connection.setAutoCommit( true );
		return connection;
		}
		
	protected void poolObject( Object o )
		throws Exception
		{
		}
		
		
	public Connection getConnection( Object user )
		throws ConnectionPoolException
		{
		Connection connection;
	    if (log.isDebugEnabled())
	    {
	        log.debug("Getting connection for: "+ user);
	    }
		try
		{
		    connection = (Connection)getObject( user );
		    if (connection == null)
		    		throw new ConnectionPoolException( "Unable to get connection" );
		    	return connection;
		}
		catch (ObjectPoolException ope)
		{
		    throw new ConnectionPoolException( ope.getMessage());
		}
		}

		
	public void freeConnection( Connection free_con )
		throws ConnectionPoolException
		{
	    if (log.isDebugEnabled())
	    {
	        log.debug("Freeing connection: "+ free_con);
	    }
	    try
	    {
	        freeObject( free_con );
		}
	    catch (ObjectPoolException ope)
	    {
	        throw new ConnectionPoolException (ope.getMessage());
	    }
	}
}