/*
 * The contents of this file are subject to the Mozilla Public License Version 1.1
 * (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.mozilla.org/MPL/>.
 * 
 * Software distributed under the License is distributed on an "AS IS" basis, WITHOUT
 * WARRANTY OF ANY KIND, either express or implied. See the License for the specific
 * language governing rights and limitations under the License.
 * 
 * The Original Code is the Venice Web Communities System.
 * 
 * The Initial Developer of the Original Code is Eric J. Bowersox <erbo@silcom.com>,
 * for Silverwrist Design Studios.  Portions created by Eric J. Bowersox are
 * Copyright (C) 2002 Eric J. Bowersox/Silverwrist Design Studios.  All Rights Reserved.
 * 
 * Contributor(s): 
 */
package com.silverwrist.dynamo.security;

import java.sql.*;
import java.util.*;
import org.apache.commons.collections.*;
import com.silverwrist.util.*;
import com.silverwrist.dynamo.except.*;
import com.silverwrist.dynamo.iface.*;
import com.silverwrist.dynamo.util.*;

public class AuditReadOps_mysql extends AuditReadOps
{
  /*--------------------------------------------------------------------------------
   * Attributes
   *--------------------------------------------------------------------------------
   */

  private DBUtilities m_utils;                // reference to utilities object
  private PropertySerializer m_psz;           // property serializer object

  /*--------------------------------------------------------------------------------
   * Constructor
   *--------------------------------------------------------------------------------
   */

  public AuditReadOps_mysql(DBConnectionPool pool)
  {
    super(pool);
    m_utils = (DBUtilities)(pool.queryService(DBUtilities.class));
    m_psz = (PropertySerializer)(pool.queryService(PropertySerializer.class));

  } // end constructor

  /*--------------------------------------------------------------------------------
   * Internal operations
   *--------------------------------------------------------------------------------
   */

  private final List runListStatement(PreparedStatement stmt) throws SQLException
  {
    ResultSet rs = null;
    try
    { // get the list of record IDs (long integers)
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return Collections.EMPTY_LIST;  // no results
      ArrayList rc = new ArrayList();
      do
      { // add each entry to the list
	rc.add(new Long(rs.getLong(1)));

      } while (rs.next()); // end do

      rc.trimToSize();
      return Collections.unmodifiableList(rc);

    } // end try
    finally
    { // shut down the result set before we go
      SQLUtils.shutdown(rs);

    } // end finally

  } // end runListStatement

  /*--------------------------------------------------------------------------------
   * Abstract implementations from class AuditReadOps
   *--------------------------------------------------------------------------------
   */

  List getAuditIDs(java.util.Date start, java.util.Date end) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE on_date >= ? AND on_date <= ? "
				   + "ORDER BY on_date;");
      m_utils.setDateTime(stmt,1,start);
      m_utils.setDateTime(stmt,2,end);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditIDs

  List getAuditIDs(java.util.Date start, java.util.Date end, int subid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE on_date >= ? AND on_date <= ? "
				   + "AND subid = ? ORDER BY on_date;");
      m_utils.setDateTime(stmt,1,start);
      m_utils.setDateTime(stmt,2,end);
      stmt.setInt(3,subid);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditIDs

  List getAuditIDsBefore(java.util.Date date) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE on_date <= ? ORDER BY on_date;");
      m_utils.setDateTime(stmt,1,date);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditIDsBefore

  List getAuditIDsBefore(java.util.Date date, int subid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE on_date <= ? AND subid = ? "
				   + "ORDER BY on_date;");
      m_utils.setDateTime(stmt,1,date);
      stmt.setInt(2,subid);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditIDsBefore

  List getAuditIDsAfter(java.util.Date date) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE on_date >= ? ORDER BY on_date;");
      m_utils.setDateTime(stmt,1,date);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditIDsAfter

  List getAuditIDsAfter(java.util.Date date, int subid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE on_date >= ? AND subid = ? "
				   + "ORDER BY on_date;");
      m_utils.setDateTime(stmt,1,date);
      stmt.setInt(2,subid);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditIDsAfter

  List getAllAuditIDs() throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit ORDER BY on_date;");

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAllAuditIDs

  List getAllAuditIDs(int subid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    try
    { // get a connection
      conn = getConnection();

      // create our statement
      stmt = conn.prepareStatement("SELECT record FROM audit WHERE subid = ? ORDER BY on_date;");
      stmt.setInt(1,subid);

      // and execute it, retrieving the results
      return runListStatement(stmt);
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAllAuditIDs

  Map getAuditRecordData(long rec_id) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // get our statement and execute it
      stmt = conn.prepareStatement("SELECT ae.event_nsid, ae.event_name, a.on_date, a.uid, a.subid, a.ip, "
				   + "a.prop0, a.prop1, a.prop2, a.prop3, a.prop4, a.prop5, a.prop6, a.prop7 "
				   + "FROM audit a, auditevent ae WHERE ae.eventid = a.event "
				   + "AND a.record = ?;");
      stmt.setLong(1,rec_id);
      rs = stmt.executeQuery();
      if (!(rs.next()))
      { // shouldn't happen - throw exception
	DatabaseException de = new DatabaseException(AuditReadOps_mysql.class,"SecurityMessages",
						     "audit.noRecord");
	de.setParameter(0,String.valueOf(rec_id));
	throw de;

      } // end if

      HashMap rc = new HashMap();
      rc.put(HMKEY_NSID,new Integer(rs.getInt(1)));
      rc.put(HMKEY_NAME,rs.getString(2));
      rc.put(HMKEY_DATE,m_utils.getDateTime(rs,3));
      rc.put(HMKEY_UID,new Integer(rs.getInt(4)));
      rc.put(HMKEY_SUBID,new Integer(rs.getInt(5)));
      rc.put(HMKEY_IP,rs.getString(6));
      Object[] props = new Object[8];
      for (int i=0; i<props.length; i++)
      { // get the property string from the result set
	String s = rs.getString(7+i);
	if (rs.wasNull())
	  props[i] = null;  // null property
	else
	{ // deserialize the property
	  props[i] = m_psz.deserializeProperty(s);
	  if (props[i]==null)
	  { // deserialization failed, throw an exception
	    DatabaseException de = new DatabaseException(AuditReadOps_mysql.class,"SecurityMessages",
							 "audit.deserialize");
	    de.setParameter(0,String.valueOf(rec_id));
	    throw de;

	  } // end if

	} // end else

      } // end for

      rc.put(HMKEY_PROPERTIES,props);
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAuditRecordData

} // end class AuditReadOps_mysql
