/**
 * 
 */
package org.biomoby.registry.rdfagent.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.biomoby.shared.MobyException;

/**
 * This class implements the interface CentralDataAccess using mysql
 * 
 * @author Eddie created Feb 1, 2006
 */
public class CentralDataAccessImpl implements CentralDataAccess {

	private DBConnector connector = null;

	/**
	 * Default Constructor
	 * 
	 * @throws MobyException
	 *             if the agent cannot load the JDBC driver
	 */
	public CentralDataAccessImpl() throws MobyException {
		connector = new DBConnector();
	}

	/*
	 *  (non-Javadoc)
	 * @see org.biomoby.registry.rdfagent.util.CentralDataAccess#updateErrorCountForURL(java.lang.String, int)
	 */
	public boolean incrementErrorCountForURL(String signatureURL, int errorCode)
			throws MobyException {

		Log.info("Incrementing the error count for " + signatureURL + ". ");
		Connection connection = connector.getConnection();

		int updatedRowCount = -1;

		final String sql = "UPDATE service_validation "
				+ "SET error_code = ?,counter=counter+1 "
				+ "WHERE signatureURL = ?";
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, errorCode);
			preparedStatement.setString(2, signatureURL);
			updatedRowCount = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			Log.exception(this.getClass().getName(),
					"incrementErrorCountForURL(String)", e);
			Log.severe(e.getLocalizedMessage());
			return (updatedRowCount == 1);
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				Log.exception(this.getClass().getName(),
						"incrementErrorCountForURL(String)", e);
				Log.severe(e.getLocalizedMessage());
				return (updatedRowCount == 1);
			}

		}
		Log.info("Finished incrementing the error count for " + signatureURL + ". "
				+ updatedRowCount + " rows modified.");
		// only one row should be updated
		return (updatedRowCount == 1);
	}
	
	/*
	 *  (non-Javadoc)
	 * @see org.biomoby.registry.rdfagent.util.CentralDataAccess#updateErrorCountForURL(java.lang.String, int)
	 */
	public boolean decrementErrorCountForURL(String signatureURL)
			throws MobyException {

		Log.info("Decrementing the error count for " + signatureURL + ". ");
		Connection connection = connector.getConnection();

		int updatedRowCount = -1;

		final String sql = "UPDATE service_validation "
				+ "SET error_code = ?,counter=counter-1 "
				+ "WHERE signatureURL = ?";
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, signatureURL);
			updatedRowCount = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			Log.exception(this.getClass().getName(),
					"decrementErrorCountForURL(String)", e);
			Log.severe(e.getLocalizedMessage());
			return (updatedRowCount == 1);
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				Log.exception(this.getClass().getName(),
						"decrementErrorCountForURL(String)", e);
				Log.severe(e.getLocalizedMessage());
				return (updatedRowCount == 1);
			}

		}
		Log.info("Finished decrementing the error count for " + signatureURL + ". "
				+ updatedRowCount + " rows modified.");
		// only one row should be updated
		return (updatedRowCount == 1);
	}

	/*
	 *  (non-Javadoc)
	 * @see org.biomoby.registry.rdfagent.util.CentralDataAccess#deleteErrorCountForURL(java.lang.String)
	 */
	public boolean deleteErrorCountForURL(String signatureURL)
			throws MobyException {
		Log.info("Deleting the error count for " + signatureURL + ".");
		Connection connection = connector.getConnection();
		int deletedRowCount = -1;
		final String sql = "DELETE from service_validation "
				+ "WHERE signatureURL = ?";
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, signatureURL);
			deletedRowCount = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			Log.exception(this.getClass().getName(),
					"deleteErrorCountForURL(String)", e);
			Log.severe(e.getLocalizedMessage());
			return (deletedRowCount == 1);
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				Log.exception(this.getClass().getName(),
						"deleteErrorCountForURL(String)", e);
				Log.severe(e.getLocalizedMessage());
				return (deletedRowCount == 1);
			}
		}
		Log.info("Finished deleting the error count for " + signatureURL + ". "
				+ deletedRowCount + " rows removed.");
		// only one row should be deleted
		return (deletedRowCount == 1);

	}

	/*
	 *  (non-Javadoc)
	 * @see org.biomoby.registry.rdfagent.util.CentralDataAccess#insertErrorCountForURL(java.lang.String, int)
	 */
	public boolean insertErrorCountForURL(String signatureURL, int errorCode)
			throws MobyException {

		int insertedRowCount = -1;
		Log.info("Inserting an error count for " + signatureURL + ".");
		final String sql = "INSERT into service_validation (signatureURL,error_code,counter) values (?,?,1)";
		PreparedStatement preparedStatement = null;
		Connection con = connector.getConnection();
		try {
			preparedStatement = con.prepareStatement(sql);

			preparedStatement.setString(1, signatureURL);
			preparedStatement.setInt(2, errorCode);
			insertedRowCount = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			Log.exception(this.getClass().getName(),
					"insertErrorCountForURL(String, int)", e);
			Log.severe(e.getLocalizedMessage());
			return (insertedRowCount == 1);
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				Log.exception(this.getClass().getName(),
						"insertErrorCountForURL(String, int)", e);
				Log.severe(e.getLocalizedMessage());
				return (insertedRowCount == 1);
			}

		}
		Log.info("Finished inserting an error count for " + signatureURL + ". "
				+ insertedRowCount + " rows inserted.");
		return (insertedRowCount == 1);
	}

	/*
	 *  (non-Javadoc)
	 * @see org.biomoby.registry.rdfagent.util.CentralDataAccess#getErrorCountForURL(java.lang.String)
	 */
	public int getErrorCountForURL(String signatureURL) throws MobyException {

		Log.info("Retrieving an error count for " + signatureURL + ".");
		int errorCount = 0;
		Connection connection = connector.getConnection();

		final String sql = "SELECT counter FROM service_validation WHERE signatureURL = ?";
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, signatureURL);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				// should only iterator at most one time
				int count = resultSet.getInt(1);
				errorCount = count;
			}
		} catch (SQLException e) {
			Log.exception(this.getClass().getName(),
					"getErrorCountForURL(String, int)", e);
			Log.severe(e.getLocalizedMessage());
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException e) {
				Log.exception(this.getClass().getName(),
						"getErrorCountForURL(String, int)", e);
				Log.severe(e.getLocalizedMessage());
			}
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				Log.exception(this.getClass().getName(),
						"getErrorCountForURL(String, int)", e);
				Log.severe(e.getLocalizedMessage());
			}

		}
		Log.info("Finished retrieving an error count for " + signatureURL
				+ ". The count was " + errorCount + ".");
		return errorCount;
	}
	
	/*
	 *  (non-Javadoc)
	 * @see org.biomoby.registry.rdfagent.util.CentralDataAccess#cleanup()
	 */
	public void cleanup(){
		this.connector.closeConnection();
	}

}