/**
 * Copyright (c) 1996-2006 Cafesoft, LLC. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * Cafesoft, LLC. ("Confidential Information"). You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Cafesoft.
 *
 * CAFESOFT MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF THE
 * SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
 * PURPOSE, OR NON-INFRINGEMENT. CAFESOFT SHALL NOT BE LIABLE FOR ANY DAMAGES
 * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING
 * THIS SOFTWARE OR ITS DERIVATIVES.
 */
package examples.service;

import com.cafesoft.core.service.AbstractLifecycleService;
import com.cafesoft.core.service.ServiceConfig;
import com.cafesoft.core.service.ServiceException;

import com.cafesoft.core.lifecycle.LifecycleEvent;
import com.cafesoft.core.lifecycle.LifecycleException;

import com.cafesoft.core.jdbc.SqlCommandIterator;

import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
import java.util.Properties;

import java.io.File;
import java.io.FileReader;
import java.io.FileNotFoundException;
import java.io.PrintStream;
import java.io.IOException;
import java.io.LineNumberReader;

import java.sql.Driver;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * <code>StandardRdbmsService</code> starts and stops an embedded
 * Relational Database Management System for use by Cams example components.
 * This service can create/initialize
 * any number of databases by use of configuration parameters and SQL
 * command files. The following example shows how this service may be
 * configured for a database named "userinfodb" within the "examples"
 * security domain file: security-domain.xml:
 * <PRE>
 * 	<!-- Register an RdbmsService -->
 *	<service id="rdbms-service" enabled="true" debug="true">
 *		<service-type>examples.service.RdbmsService</service-type>
 *		<service-class>examples.service.StandardRdbmsService</service-class>
 *		<param-list>
 *			<param name="database.userinfo" value=""/>
 *			<param name="userinfo.driver" value="org.hsqldb.jdbcDriver"/>
 *			<param name="userinfo.url" value="jdbc:hsqldb:${cams.security-domain.home}/userinfodb/userinfodb"/>
 *			<param name="userinfo.user" value="sa"/>
 *			<param name="userinfo.password" value=""/>
 *			<param name="userinfo.startScript" value="${cams.security-domain.home}/userinfodb/create.sql"/>
 *			<param name="userinfo.stopScript" value="${cams.security-domain.home}/userinfodb/drop.sql"/>
 *		</param-list>
 *	</service>
 * </PRE>
 * The parameter: database.userinfo declares a database named "userinfo".
 * For an arbitrary database of name "dbName", the following parameters must
 * also be specified:
 * <UL>
 * <LI>dbName.driver - the JDBC driver used to access the database
 * <LI>dbName.url - the JDBC URL used to connect with the database
 * <LI>dbName.user - the username used to login to the database
 * <LI>dbName.password - the password used to login to the database
 * <LI>dbName.startScript - the path to a file containing SQL commands executed
 * 	when the service is started. This script can be used to create tables,
 *  populate them, etc.
 * <LI>dbName.stopScript - the path to a file containing SQL commands executed
 * 	when the service is stopped. This script can be used to drop tables, etc.
 * </UL>
 *
 * @version $Revision: 1.5 $ $Date: 2006/08/30 18:31:01 $
 * @author Norbert K. Kuhnert
 */
public class StandardRdbmsService extends AbstractLifecycleService
	implements RdbmsService
{
	/** A list of in-memory database names. */
	private List dbNameList = new ArrayList();

	/**
	 * Create a new StandardRdbmsService instance.
	 */
	public StandardRdbmsService()
	{
	}

	/**
	 * Override the initialize method to check for required config parameters.
	 *
	 * @param serviceConfig the object through which configuration parameters and
	 *	other resources are available.
	 * @exception ServiceException if one or more configuration parameters
	 *	are missing.
	 */
	public void initialize(ServiceConfig serviceConfig) throws ServiceException
	{
		// Initialize state in the abstract base class
		super.initialize(serviceConfig);

		// Save the name of each In-memory databases to be created.
		Iterator iter = serviceConfig.getInitParameterNames();
		while (iter.hasNext())
		{
			String paramName = (String)iter.next();
			if (paramName.startsWith("database."))
				dbNameList.add(paramName.substring(paramName.indexOf(".") + 1));
		}

		// For each database, make sure required parameters are specified.
		if (dbNameList.size() > 0)
		{
			for (int i = 0; i < dbNameList.size(); i++)
			{
				String dbName = (String)dbNameList.get(i);

				String paramName = dbName + ".driver";
				String paramValue = serviceConfig.getInitParameter(paramName);
				if (paramValue == null)
					throw new ServiceException("Missing parameter: " + paramName);

				// Make sure the database driver loads
				try
				{
					Driver driver = (Driver)Class.forName(paramValue).newInstance();
				}
				catch (Exception e)
				{
					throw new ServiceException("Unable to load JDBC driver: " +
						paramValue, e);
				}

				paramName = dbName + ".url";
				if (serviceConfig.getInitParameter(paramName) == null)
					throw new ServiceException("Missing parameter: " + paramName);

				paramName = dbName + ".user";
				if (serviceConfig.getInitParameter(paramName) == null)
					throw new ServiceException("Missing parameter: " + paramName);

				paramName = dbName + ".password";
				if (serviceConfig.getInitParameter(paramName) == null)
					throw new ServiceException("Missing parameter: " + paramName);

				paramName = dbName + ".startScript";
				paramValue = serviceConfig.getInitParameter(paramName);
				if (paramValue == null)
					throw new ServiceException("Missing parameter: " + paramName);
				if (new File(paramValue).exists() == false)
					throw new ServiceException("CREATE script file: " + paramValue +
						" does not exist");

				paramName = dbName + ".stopScript";
				paramValue = serviceConfig.getInitParameter(paramName);
				if (serviceConfig.getInitParameter(paramName) == null)
					throw new ServiceException("Missing parameter: " + paramName);
				if (new File(paramValue).exists() == false)
					throw new ServiceException("stop() script file: " + paramValue +
						" does not exist");
			}
		}
		else
		{
			if (debug)
				logger.debug(this, "No databases configured");
		}
	}

	/**
	 * Prepare for the beginning of active use of the public methods of this
	 * component.  This method should be called before any of the public
	 * methods of this component are utilized.  It should also send a
	 * LifecycleEvent of type START_EVENT to any registered listeners.
	 *
	 * @exception IllegalStateException if this component has already been
	 *  started
	 * @exception LifecycleException if this component detects a fatal error
	 *  that prevents this component from being used
	 */
	public void start() throws LifecycleException
	{
		if (debug)
			logger.debug(this, "Starting StandardRdbmsService");

		if (started)
			throw new LifecycleException(
				"StandardRdbmsService already started");

		started = true;

		// Run the start() script for each configured database
		for (int i = 0; i < dbNameList.size(); i++)
		{
			String dbName = (String)dbNameList.get(i);
			String scriptPath = serviceConfig.getInitParameter(dbName + ".startScript");

			if (debug)
				logger.debug(this, "Creating example database=" + dbName +
					", script=" + scriptPath);

			executeScript(dbName, scriptPath);
		}

		if (debug)
			logger.debug(this, "Started StandardRdbmsService");

		// Fire an START_EVENT to registered listeners
		fireEvent(new LifecycleEvent(this, LifecycleEvent.START_EVENT));
	}


	/**
	 * Gracefully terminate the active use of the public methods of this
	 * component.  This method should be the last one called on a given
	 * instance of this component.  It should also send a LifecycleEvent
	 * of type STOP_EVENT to any registered listeners.
	 *
	 * @exception IllegalStateException if this component has not been started
	 * @exception LifecycleException if this component detects a fatal error
	 *  that needs to be reported
	 */
	public void stop() throws LifecycleException
	{
		if (debug)
			logger.debug(this, "Stopping StandardRdbmsService");

		if (started == false)
			throw new LifecycleException(
				"StandardRdbmsService has not been started");

		started = false;

		// Run the stop() script for each configured database
		for (int i = 0; i < dbNameList.size(); i++)
		{
			String dbName = (String)dbNameList.get(i);
			String scriptPath = serviceConfig.getInitParameter(dbName + ".stopScript");

			if (debug)
				logger.debug(this, "Dropping example database=" + dbName +
					", script=" + scriptPath);

			executeScript(dbName, scriptPath);
		}

		if (debug)
			logger.debug(this, "StandardRdbmsService stopped.");

		fireEvent(new LifecycleEvent(this, LifecycleEvent.STOP_EVENT));
	}

	/**
	 * Destroy the service.
	 */
	public void destroy()
	{
		dbNameList.clear();
		dbNameList = null;

		// Invoke the super class destroy method.
		super.destroy();
	}

	/**
	 * Execute a database script.
	 *
	 * @param dbName the name of the database.
	 * @param scriptPath the file containing SQL commands to be executed.
	 * @exception LifecycleException if an error executing the script.
	 */
	private void executeScript(String dbName, String scriptPath)
			throws LifecycleException
	{
		Driver driver = null;
		Connection c = null;
		Statement s = null;
		ResultSet r = null;

		String paramName = dbName + ".driver";
		String driverName = serviceConfig.getInitParameter(paramName);

		try
		{
			// Load the database driver
			driver = (Driver)Class.forName(driverName).newInstance();
		}
		catch (Exception e)
		{
			throw new LifecycleException("Unable to load JDBC driver: " + driverName, e);
		}

		try
		{
			// Connect to the database
			String url = serviceConfig.getInitParameter(dbName + ".url");
			Properties p = new Properties();
			p.setProperty("user", serviceConfig.getInitParameter(dbName + ".user"));
			p.setProperty("password", serviceConfig.getInitParameter(dbName + ".password"));
			c = driver.connect(url, p);

			// Iterate over SQL commands found in a file.
			SqlCommandIterator sqlIter = new SqlCommandIterator(scriptPath, logger);
			while (sqlIter.hasNext())
			{
				// Get the next SQL command
				String sql = sqlIter.next();

				if (debug)
					logger.debug(this, "Line=" + sqlIter.getLineNumber() +
						", Executing SQL='" + sql + "'");

				// Execute the SQL statement
				s = c.createStatement();
				boolean hasResultSet = s.execute(sql);
				if (hasResultSet)
				{
					// If a ResultSet was returned, just close it
					r = s.getResultSet();
					r.close();
					r = null;
				}

				s.close();
				s = null;
			}

			c.close();
			c = null;
		}
		catch (SQLException e)
		{
			throw new LifecycleException(
				"Error executing script: " + scriptPath, e);
		}
		catch (FileNotFoundException e)
		{
			throw new LifecycleException(
				"Could not find script: " + scriptPath, e);
		}
		catch (IOException e)
		{
			throw new LifecycleException(
				"Error reading script: " + scriptPath, e);
		}
		finally
		{
			try
			{
				if (r != null)
					r.close();

				if (s != null)
					s.close();

				if (c != null)
					c.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

} // End of class: StandardRdbmsService