/*
 * 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) 2003 Eric J. Bowersox/Silverwrist Design Studios.  All Rights Reserved.
 * 
 * Contributor(s): 
 */
package com.silverwrist.dynamo.db;

import java.io.*;
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 ImageStoreOps_mysql extends ImageStoreOps
{
  /*--------------------------------------------------------------------------------
   * Constructor
   *--------------------------------------------------------------------------------
   */

  public ImageStoreOps_mysql(DBConnectionPool pool)
  {
    super(pool);

  } // end constructor

  /*--------------------------------------------------------------------------------
   * Abstract implementations from class ImageStoreOps
   *--------------------------------------------------------------------------------
   */

  int imageTypeToIndex(PropertyKey pkey) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the table while we work
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES imagetype WRITE;");

      // first try to look the index up
      stmt = conn.prepareStatement("SELECT typecode FROM imagetype WHERE nsid = ? AND name = ?;");
      stmt.setInt(1,pkey.getNamespaceID());
      stmt.setString(2,pkey.getName());
      rs = stmt.executeQuery();
      if (rs.next())
	return rs.getInt(1);

      // then add it
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("INSERT INTO imagetype (nsid, name) VALUES (?, ?);");
      stmt.setInt(1,pkey.getNamespaceID());
      stmt.setString(2,pkey.getName());
      stmt.executeUpdate();
      return MySQLUtils.getLastInsertInt(conn);

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

  DataItem getImage(int id) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // Start by getting the MIME type and image length.
      stmt = conn.prepareStatement("SELECT mimetype, length FROM imagestore WHERE imageid = ?;");
      stmt.setInt(1,id);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;
      String mime_type = rs.getString(1);
      int length = rs.getInt(2);

      // Now get the actual data blob.
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT data FROM imagestore WHERE imageid = ?;");
      stmt.setInt(1,id);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;

      // We need to copy the data blob into a local buffer.  We also mesh the whole thing into a DataItem.
      return new StaticDataItem("image" + id,mime_type,length,IOUtils.load(rs.getBinaryStream(1)));

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

    } // end catch
    catch (IOException e)
    { // turn IOException into DatabaseException too
      DatabaseException de = new DatabaseException(ImageStoreOps_mysql.class,"DatabaseMessages",
						   "img.loadFail",e);
      de.setParameter(0,String.valueOf(id));
      throw de;

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

    } // end finally

  } // end getImage

  int saveNewImage(int typecode, int owner_id, int owner_flag, DataItem image) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the table while we work
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES imagestore WRITE;");

      // insert all the "base" information
      stmt = conn.prepareStatement("INSERT INTO imagestore (typecode, ownerid, ownerflag, mimetype, length) "
				   + "VALUES (?, ?, ?, ?, ?);");
      stmt.setInt(1,typecode);
      stmt.setInt(2,owner_id);
      stmt.setInt(3,owner_flag);
      stmt.setString(4,image.getMimeType());
      stmt.setInt(5,image.getSize());
      stmt.executeUpdate();
      int rc = MySQLUtils.getLastInsertInt(conn);

      // insert the file data itself
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("UPDATE imagestore SET data = ? WHERE imageid = ?;");
      stmt.setBlob(1,image.getBlob());
      stmt.setInt(2,rc);
      stmt.executeUpdate();

      return rc;  // all done!

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

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

    } // end finally

  } // end saveNewImage

  boolean canReplaceImage(int uid, int image_id) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // get the owner ID and flag from the image records
      stmt = conn.prepareStatement("SELECT ownerid, ownerflag FROM imagestore WHERE imageid = ?;");
      stmt.setInt(1,image_id);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return false;
      int owner_id = rs.getInt(1);
      if (rs.getInt(2)==0)  // a user owns it
	return (uid==owner_id);

      // Test group membership for ownership.
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT uid FROM groupmembers WHERE uid = ? AND gid = ?;");
      stmt.setInt(1,uid);
      stmt.setInt(2,owner_id);
      rs = stmt.executeQuery();
      return rs.next();

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

  void replaceImage(int image_id, DataItem image) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the table while we work
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES imagestore WRITE;");

      // poke in the basic data items first
      stmt = conn.prepareStatement("UPDATE imagestore SET mimetype = ?, length = ?, data = NULL "
				   + "WHERE imageid = ?;");
      stmt.setString(1,image.getMimeType());
      stmt.setInt(2,image.getSize());
      stmt.setInt(3,image_id);
      stmt.executeUpdate();
      
      // insert the new file data itself
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("UPDATE imagestore SET data = ? WHERE imageid = ?;");
      stmt.setBlob(1,image.getBlob());
      stmt.setInt(2,image_id);
      stmt.executeUpdate();

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

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

    } // end finally

  } // end replaceImage

  int findImageID(int typecode, int owner_id, int owner_flag) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // create statement and run it
      stmt = conn.prepareStatement("SELECT imageid FROM imagestore WHERE typecode = ? AND ownerid = ? "
				   + "AND ownerflag = ?;");
      stmt.setInt(1,typecode);
      stmt.setInt(2,owner_id);
      stmt.setInt(3,owner_flag);
      rs = stmt.executeQuery();
      if (rs.next())
	return rs.getInt(1);
      else
	return -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 findImageID

  void deleteImage(int image_id) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the table while we work
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES imagestore WRITE;");

      // delete it!
      stmt = conn.prepareStatement("DELETE FROM imagestore WHERE imageid = ?;");
      stmt.setInt(1,image_id);
      stmt.executeUpdate();
      
    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

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

    } // end finally

  } // end deleteImage

} // end class ImageStoreOps_mysql
