/*
 * 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.db;

import java.sql.*;
import java.util.*;
import com.silverwrist.util.*;
import com.silverwrist.dynamo.except.*;
import com.silverwrist.dynamo.iface.*;
import com.silverwrist.dynamo.util.*;

public class GlobalBlocksOps_mysql extends GlobalBlocksOps
{
  /*--------------------------------------------------------------------------------
   * Attributes
   *--------------------------------------------------------------------------------
   */

  private DBUtilities m_utils;             // reference to utilities object

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

  public GlobalBlocksOps_mysql(DBConnectionPool pool)
  {
    super(pool);
    m_utils = (DBUtilities)(pool.queryService(DBUtilities.class));

  } // end constructor

  /*--------------------------------------------------------------------------------
   * Abstract implementations from class GlobalBlocksOps
   *--------------------------------------------------------------------------------
   */

  String getBlock(PropertyKey key) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // look up the block
      stmt = conn.prepareStatement("SELECT block FROM globalblock WHERE nsid = ? AND block_name = ?;");
      stmt.setInt(1,key.getNamespaceID());
      stmt.setString(2,key.getName());
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;  // block not found
      return rs.getString(1);

    } // 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 getBlock

  String putBlock(PropertyKey key, String data) throws DatabaseException
  {
    String old_value = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the table
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES globalblock WRITE;");

      // look to see if the block value is already there
      stmt = conn.prepareStatement("SELECT block FROM globalblock WHERE nsid = ? AND block_name = ?;");
      stmt.setInt(1,key.getNamespaceID());
      stmt.setString(2,key.getName());
      rs = stmt.executeQuery();
      if (rs.next())
	old_value = rs.getString(1);
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);

      if (old_value!=null)
      { // prepare the statement to update the existing record
	stmt = conn.prepareStatement("UPDATE globalblock SET block = ? WHERE nsid = ? AND block_name = ?;");
	stmt.setString(1,data);
	stmt.setInt(2,key.getNamespaceID());
	stmt.setString(3,key.getName());

      } // end if
      else
      { // prepare the statement to insert a new record
	stmt = conn.prepareStatement("INSERT INTO globalblock (nsid, block_name, block) VALUES (?, ?, ?);");
	stmt.setInt(1,key.getNamespaceID());
	stmt.setString(2,key.getName());
	stmt.setString(3,data);

      } // end else

      stmt.executeUpdate();  // execute it!

      return old_value;  // return previous value

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

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

    } // end finally

  } // end putBlock

  String removeBlock(PropertyKey key) throws DatabaseException
  {
    String old_value = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the table
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES globalblock WRITE;");

      // look to see if the property value is already there
      stmt = conn.prepareStatement("SELECT block FROM globalblock WHERE nsid = ? AND block_name = ?;");
      stmt.setInt(1,key.getNamespaceID());
      stmt.setString(2,key.getName());
      rs = stmt.executeQuery();
      if (rs.next())
	old_value = rs.getString(1);
      else
	return null;  // no need to remove anything
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);

      // delete the database row
      stmt = conn.prepareStatement("DELETE FROM globalblock WHERE nsid = ? AND block_name = ?;");
      stmt.setInt(1,key.getNamespaceID());
      stmt.setString(2,key.getName());
      stmt.executeUpdate();

      return old_value;

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

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

    } // end finally

  } // end removeBlock

  int[] getBlockNamespaceIDs() throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // execute the query!
      stmt = conn.prepareStatement("SELECT DISTINCT nsid FROM globalblock;");
      rs = stmt.executeQuery();

      // read out a list of the namespace IDs
      ArrayList tmp = new ArrayList();
      while (rs.next())
	tmp.add(new Integer(rs.getInt(1)));

      // create and return the array
      int[] rc = new int[tmp.size()];
      for (int i=0; i<tmp.size(); i++)
	rc[i] = ((Integer)(tmp.get(i))).intValue();
      tmp.clear();
      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 getBlockNamespaceIDs

  Collection getAllBlockNames(int nsid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // execute the query!
      stmt = conn.prepareStatement("SELECT block_name FROM globalblock WHERE nsid = ?;");
      stmt.setInt(1,nsid);
      rs = stmt.executeQuery();

      // prepare the return value
      ArrayList rc = new ArrayList();
      while (rs.next())
	rc.add(rs.getString(1));
      rc.trimToSize();
      return Collections.unmodifiableList(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 getAllBlockNames

} // end class GlobalBlocksOps_mysql
