EnzymeReactionMapper

package uk.ac.ebi.intenz.mapper;

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

import org.apache.log4j.Logger;

import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
import uk.ac.ebi.intenz.domain.enzyme.EnzymaticReactions;
import uk.ac.ebi.rhea.domain.Database;
import uk.ac.ebi.rhea.domain.Reaction;
import uk.ac.ebi.rhea.domain.Status;
import uk.ac.ebi.rhea.mapper.MapperException;
import uk.ac.ebi.rhea.mapper.db.RheaCompoundDbReader;
import uk.ac.ebi.rhea.mapper.db.RheaDbReader;

/**
 * Maps reaction information to the corresponding database table.
 * <br>
 * <b>IMPORTANT</b>: after using instances of this class, call the
 * {@link #close()} method, otherwise the underlying Rhea mapper objects will
 * keep their statements open.
 * @author Michael Darsow,
 * 		Rafael AlcÔøΩntara
 * @version $Revision: 1.6 $ $Date: 2009/05/26 14:59:09 $
 */
public class EnzymeReactionMapper {

	private static final Logger LOGGER =
		Logger.getLogger(EnzymeReactionMapper.class.getName());

	protected RheaDbReader rheaReader;

  public EnzymeReactionMapper(){
	  try {
		rheaReader = new RheaDbReader(new RheaCompoundDbReader(null));
	} catch (IOException e) {
		throw new RuntimeException(e);
	}
  }

  private static final String SEL_COLS_FROM_TBLS =
	  "SELECT rm.reaction_id, rm.web_view, rm.order_in, rm.iubmb," +
	  " ir.equation, ir.source, ir.qualifiers, ir.status" +
	  " FROM reactions_map rm, intenz_reactions ir";

  private static final String FIND_STM =
	  SEL_COLS_FROM_TBLS +
	  " WHERE rm.enzyme_id = ? AND rm.reaction_id = ir.reaction_id" +
	  " ORDER BY rm.order_in";

  private static final String FIND_SIB_STM =
	  SEL_COLS_FROM_TBLS +
	" WHERE rm.enzyme_id = ? AND rm.reaction_id = ir.reaction_id" +
	" AND (rm.web_view  LIKE '%SIB%' OR rm.web_view = 'INTENZ')" +
	" ORDER BY rm.order_in";

  private static final String INSERT_STM =
	  "INSERT INTO reactions_map (reaction_id, enzyme_id, web_view, iubmb," +
	  " order_in) VALUES (?, ?, ?, ?, ?)";

  private static final String DELETE_ALL_STM =
	  "DELETE reactions_map WHERE enzyme_id = ?";

  /* Abstract reactions in old REACTIONS table */

	private static final String SEL_ABSTRACT_COLS_FROM_TBLS =
		"SELECT equation, web_view, order_in, source, NULL reaction_id," +
		" 'N' iubmb, NULL qualifiers, status FROM reactions";

	private static final String FIND_ABSTRACT_STM =
		SEL_ABSTRACT_COLS_FROM_TBLS +
		" WHERE enzyme_id = ? ORDER BY order_in";

	private static final String FIND_ABSTRACT_SIB_STM =
		SEL_ABSTRACT_COLS_FROM_TBLS + " WHERE enzyme_id = ? AND" +
		" (web_view LIKE '%SIB%' OR web_view = 'INTENZ') ORDER BY order_in";

	private static final String INSERT_ABSTRACT_STM =
		"INSERT INTO reactions (enzyme_id, equation, order_in, status, source," +
		" web_view) VALUES (?, ?, ?, ?, ?, ?)";

  private static final String DELETE_ALL_ABSTRACT_STM =
	  "DELETE reactions WHERE enzyme_id = ?";

  /**
   * Tries to find reaction information about an enzyme.
   *
   * @param enzymeId Enzyme ID of entry.
   * @param con      The logical connection.
   * @return a <code>EnzymaticReactions</code> object containing
   * 	<code>Reaction</code>instances or <code>null</code> if nothing has been found.
   * @throws SQLException in case of a generic database problem.
   * @throws MapperException in case of problem retrieving Rhea reactions.
   */
  public EnzymaticReactions find(Long enzymeId, Connection con)
  throws SQLException, MapperException {
        if (enzymeId == null) {
            throw new NullPointerException("Parameter 'enzymeId' must not be null.");
        }
        if (con == null) {
            throw new NullPointerException("Parameter 'con' must not be null.");
        }

        EnzymaticReactions result = find(enzymeId, con, FIND_STM);
    	EnzymaticReactions abstractReactions =
    		find(enzymeId, con, FIND_ABSTRACT_STM);
        if (result == null) { // no Rhea reactions
            result = abstractReactions;
        } else {
        	if (abstractReactions != null) result.add(abstractReactions);
        }
        return result;
    }

  /**
   * Exports all reactions which are displayed in the ENZYME view.
   *
   * Affected table rows will be locked.
   *
   * @param enzymeId The enzyme ID used to retreive the related reactions.
   * @param con The database connection.
   * @return an <code>EnzymaticReactions</code> containing reactions
   * 	or <code>null</code> if no reaction could be found.
   * @throws SQLException if a generic database error occured.
   * @throws MapperException in case of problem retrieving Rhea reactions.
   * @throws NullPointerException if either of the parameters is <code>null</code>.
   */
  public EnzymaticReactions exportSibReactions(Long enzymeId, Connection con)
  throws SQLException, MapperException {
    if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
    if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");

    EnzymaticReactions result = find(enzymeId, con, FIND_SIB_STM);
    if (result == null)
    	result = find(enzymeId, con, FIND_ABSTRACT_SIB_STM);
    else
        result.add(find(enzymeId, con, FIND_ABSTRACT_SIB_STM));
    return result;
  }

  private EnzymaticReactions find(Long enzymeId, Connection con,
		  String findQuery)
  throws SQLException, MapperException {
	  EnzymaticReactions result = null;
	  PreparedStatement findStatement = null;
	  ResultSet rs = null;
	  try {
		  findStatement = con.prepareStatement(findQuery);
		  findStatement.setLong(1, enzymeId.longValue());
		  rs = findStatement.executeQuery();
		  while (rs.next()) {
			  if (result == null) result = new EnzymaticReactions();
              result.add(loadReaction(rs), rs.getString("web_view"),
            		  rs.getString("iubmb").equalsIgnoreCase("Y"));
		  }
	  } finally {
		  if (rs != null) rs.close();
		  if (findStatement != null) findStatement.close();
	  }
	  return result;
  }

  /**
   * Stores the given list of reactions into the database.
   *
   * @param reactions The vector of reactions.
   * @param enzymeId  The enzyme ID.
   * @param con       ...
   * @throws SQLException
   */
  public void insert(EnzymaticReactions reactions, Long enzymeId, Connection con)
  throws SQLException {
    if (reactions == null) throw new NullPointerException("Parameter 'reactions' must not be null.");
    if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
    if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");

    for (int i = 0; i < reactions.size(); i++) {
		Reaction reaction = reactions.getReaction(i);
		EnzymeViewConstant view = reactions.getReactionView(i);
		boolean iubmb = reactions.getReactionIubmbFlag(i);
    	if (reaction.getId().equals(Reaction.NO_ID_ASSIGNED)){
    		insertReaction(enzymeId, reaction, view, iubmb, i+1, con);
    	} else {
    		insertMapping(enzymeId, reaction.getId(), view, iubmb, i+1, con);
    	}
    }
  }

  /**
   * Inserts abstract reactions into the old REACTIONS table.
   * @param enzymeId
   * @param reaction
   * @param view
   * @param iubmb
   * @param orderIn
   * @param con
   * @throws SQLException
   */
  private void insertReaction(Long enzymeId, Reaction reaction,
		  EnzymeViewConstant view, boolean iubmb, int orderIn, Connection con)
  throws SQLException {
	  PreparedStatement stm = null;
	  try {
		  stm = con.prepareStatement(INSERT_ABSTRACT_STM);
		  stm.setLong(1, enzymeId);
		  stm.setString(2, reaction.getTextualRepresentation());
		  stm.setInt(3, orderIn);
		  stm.setString(4, reaction.getStatus().toString()); // NO status
		  stm.setString(5, reaction.getSource().toString());
		  stm.setString(6, view.toString());
		  stm.execute();
	  } finally {
		  if (stm != null) stm.close();
	  }
  }

  /**
   * Inserts a mapping reaction-enzyme into the database.
   * @param enzymeId
   * @param reactionId
   * @param view
   * @param iubmb
   * @param orderIn
   * @param con
 * @throws SQLException
   */
  public void insertMapping(Long enzymeId, Long reactionId,
		  EnzymeViewConstant view, boolean iubmb,
		  int orderIn, Connection con) throws SQLException{
	  PreparedStatement stm = null;
	  try {
		  stm = con.prepareStatement(INSERT_STM);
		  stm.setLong(1, reactionId);
		  stm.setLong(2, enzymeId);
		  stm.setString(3, view.toString());
		  stm.setString(4, iubmb? "Y" : "N");
		  stm.setInt(5, orderIn);
		  stm.execute();
		  LOGGER.info("[MAPPED] enzyme_id=" + enzymeId + " to reaction_id=" + reactionId);
	  } finally {
		  if (stm != null) stm.close();
	  }
  }

  /**
   * Replace the reactions in the database related to one enzyme
   * with those passed as parameter.
   *
   * @param enzymeId  The enzyme ID.
   * @param reactions The vector of reactions.
   * @param con
   * @throws SQLException
   */
  public void update(Long enzymeId, EnzymaticReactions reactions, Connection con)
  throws SQLException {
    deleteAll(enzymeId, con);
    insert(reactions, enzymeId, con);
  }

  /**
   * Deletes all reactions mappings and abstract reactions related to one enzyme instance.
   *
   * @param enzymeId Enzyme ID of the enzyme instance.
   * @param con      ...
   * @throws SQLException
   */
  void deleteAll(Long enzymeId, Connection con) throws SQLException {
    if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
    if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");

    PreparedStatement stm = null;
    try {
      stm = con.prepareStatement(DELETE_ALL_STM);
      stm.setLong(1, enzymeId.longValue());
      stm.execute();
      stm.close();
      stm = con.prepareStatement(DELETE_ALL_ABSTRACT_STM);
      stm.setLong(1, enzymeId.longValue());
      stm.execute();
    } finally {
      if (stm != null) stm.close();
    }
  }

  /**
   * Creates an empty Reaction object, with just an equation, source and status.
   * The reaction ID will be <code>null</code> for old plain text reactions, not
   * <code>null</code> in case of error while reading data from Rhea.
   * @param reactionId
   * @param equation
   * @param source A database code
   * @param status
   * @return
   * @throws java.sql.SQLException
   */
    private Reaction loadEmtpyReaction(Long reactionId, String equation,
            String source, String status)
    throws SQLException {
        Reaction reaction = new Reaction(reactionId, equation, Database.fromCode(source));
        reaction.setStatus(Status.valueOf(status));
        return reaction;
    }

  /**
   * Creates the <code>Reaction</code> object from the given result set.
   *
   * @param rs The result set object.
   * @return a {@link Reaction}
   * @throws SQLException in case of a generic database problem.
   * @throws MapperException in case of problem retrieving the Rhea reaction.
   */
  private Reaction loadReaction(ResultSet rs) throws SQLException, MapperException {
    assert rs != null : "Parameter 'rs' must not be null.";

    String equation = rs.getString("equation");
    String source = rs.getString("source");
    Long reactionId = rs.getLong("reaction_id");
    String status = rs.getString("status");
	// Convert enzyme status codes to Rhea-ction status codes:
	if (status.equals("SU") || status.equals("PR")) status = "NO";
    Reaction reaction;
    if (reactionId.equals(0L)){
        reaction = loadEmtpyReaction(reactionId, equation, source, status);
    } else { // get the whole reaction
        try {
            rheaReader.setConnection(rs.getStatement().getConnection());
            reaction = rheaReader.findByReactionId(reactionId);
        } catch (Exception ex) {
            LOGGER.error("Unable to retrieve reaction from Rhea", ex);
            reaction = loadEmtpyReaction(reactionId, equation, source, status);
        } finally {
        	rheaReader.close();
        }
    }
    return reaction;
  }

	public void close() throws MapperException {
		rheaReader.close();
	}

	@Override
	protected void finalize() throws Throwable {
		close();
	}

}