View Javadoc
1   package uk.ac.ebi.intenz.mapper;
2   
3   import java.io.IOException;
4   import java.sql.Connection;
5   import java.sql.PreparedStatement;
6   import java.sql.ResultSet;
7   import java.sql.SQLException;
8   import java.sql.Statement;
9   import java.sql.Types;
10  import java.util.Collection;
11  import java.util.Collections;
12  import java.util.HashMap;
13  import java.util.HashSet;
14  import java.util.Hashtable;
15  import java.util.Map;
16  import java.util.Set;
17  
18  import org.apache.log4j.Logger;
19  
20  import uk.ac.ebi.biobabel.util.collections.OperatorSet;
21  import uk.ac.ebi.intenz.domain.constants.EnzymeSourceConstant;
22  import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
23  import uk.ac.ebi.intenz.domain.constants.Status;
24  import uk.ac.ebi.intenz.domain.enzyme.Cofactor;
25  import uk.ac.ebi.rhea.domain.Compound;
26  import uk.ac.ebi.rhea.mapper.MapperException;
27  import uk.ac.ebi.rhea.mapper.db.RheaCompoundDbReader;
28  
29  /**
30   * Maps reaction information to the corresponding database tables.
31   * <br>
32   * <b>IMPORTANT</b>: after using instances of this class, call the
33   * {@link #close()} method, otherwise the underlying Rhea mapper objects will
34   * keep their statements open.
35   * @author Michael Darsow
36   * @version $Revision: 1.10 $ $Date: 2009/05/26 14:59:09 $
37   */
38  public class EnzymeCofactorMapper {
39  
40    private static final Logger LOGGER =
41  	  Logger.getLogger(EnzymeCofactorMapper.class.getName());
42    
43    protected RheaCompoundDbReader rheaCompoundReader;
44  
45    public EnzymeCofactorMapper(){
46  	  try {
47  		rheaCompoundReader = new RheaCompoundDbReader(null);
48  	} catch (IOException e) {
49  		throw new RuntimeException(e);
50  	}
51    }
52    
53    /**
54     * Closes the underlying {@link RheaCompoundDbReader}.
55     */
56    public void close(){
57  	  try {
58  		rheaCompoundReader.close();
59  	} catch (MapperException e) {
60  		LOGGER.error("Closing rheaCompoundReader", e);
61  	}
62    }
63  
64    	@Override
65  	protected void finalize() throws Throwable {
66  		close();
67  	}
68  
69    private static final String COLUMNS =
70  	  "enzyme_id, cofactor_text, order_in, source, status, web_view, compound_id, operator, op_grp";
71  
72    private static final String FIND_STM =
73  	  "SELECT " + COLUMNS + " FROM cofactors WHERE enzyme_id = ? AND compound_id IS NOT NULL" +
74  	  " ORDER BY order_in, op_grp ASC";
75  
76    private static final String FIND_SIB_COFACTORS_STM =
77  	  "SELECT " + COLUMNS + " FROM cofactors" +
78        " WHERE enzyme_id = ? AND compound_id IS NOT NULL AND (web_view = 'INTENZ' OR web_view LIKE '%SIB%')" +
79        " ORDER BY order_in, op_grp ASC";
80    
81    private static final String FIND_ALL =
82  	  "SELECT cf.compound_id, cf.enzyme_id, f_quad2string(e.ec1, e.ec2, e.ec3, e.ec4) ec" +
83  	  " FROM cofactors cf, enzymes e WHERE cf.compound_id IS NOT NULL and cf.enzyme_id = e.enzyme_id" +
84  	  " ORDER BY compound_id";
85  
86    private static final String INSERT_STM =
87  	  "INSERT INTO cofactors (" + COLUMNS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
88  
89    private static final String DELETE_ALL_STM =
90  	  "DELETE cofactors WHERE enzyme_id = ?";
91    
92    private static final String UPDATE_STM =
93  	  "UPDATE cofactors SET compound_id = ?, cofactor_text = ? WHERE compound_id = ?";
94  
95    /**
96     * Tries to find cofactor information about an enzyme.
97     *
98     * @param enzymeId Enzyme ID of entry.
99     * @param con      The logical connection.
100    * @return a <code>Set</code> containing <code>Cofactor</code> instances,
101    * 	or <code>OperatorSet</code> instances filled with <code>Cofactor</code> objects,
102    * 	or <code>null</code> if nothing has been found.
103    * @throws SQLException in case of a generic database problem.
104  * @throws MapperException in case of problem retrieving the cofactors.
105    */
106   public Set<Object> find(Long enzymeId, Connection con)
107   throws SQLException, MapperException {
108     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
109     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
110 
111     PreparedStatement findStatement = null;
112     ResultSet rs = null;
113     Set<Object> result = null;
114 
115     try {
116       // Core information.
117       findStatement = con.prepareStatement(FIND_STM);
118       findStatement.setLong(1, enzymeId.longValue());
119       rs = findStatement.executeQuery();
120       result = getCofactors(rs);
121     } finally {
122     	if (rs != null) rs.close();
123       if (findStatement != null) findStatement.close();
124     }
125 
126     if (result == null) {
127       LOGGER.info("No cofactor information found for the enzyme with ID " + enzymeId);
128     }
129     return result;
130   }
131 
132   /**
133    * Exports all cofactors which are displayed in the ENZYME view.
134    *
135    * Affected table rows will be locked.
136    *
137    * @param enzymeId The enzyme ID used to retreive the related cofactors.
138    * @param con The database connection.
139    * @return an {@link java.util.ArrayList} of cofactors or <code>null</code> if no cofactor could be found.
140    * @throws SQLException in case of a generic database problem.
141    * @throws MapperException in case of problem retrieving the cofactors.
142    * @throws NullPointerException if either of the parameters is <code>null</code>.
143    */
144   public Set<Object> exportSibCofactors(Long enzymeId, Connection con)
145   throws SQLException, MapperException {
146     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
147     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
148 
149     PreparedStatement findStatement = null;
150     ResultSet rs = null;
151     Set<Object> result = new HashSet<Object>();
152 
153     try {
154       // Core information.
155       findStatement = con.prepareStatement(FIND_SIB_COFACTORS_STM);
156       findStatement.setLong(1, enzymeId.longValue());
157       rs = findStatement.executeQuery();
158       result = getCofactors(rs);
159     } finally {
160     	if (rs != null) rs.close();
161     	if (findStatement != null) findStatement.close();
162     }
163 
164     return result;
165   }
166   
167   /**
168    * Retrieves a list of compounds acting as cofactors in the database, along with
169    * the identifiers for the enzymes they appear in.
170    * @param con a database connection
171    * @return a map of compounds (cofactors) to pairs enzyme ID/EC number.
172    * @throws SQLException in case of a generic database problem.
173    * @throws MapperException in case of problem retrieving compounds.
174    * @throws MapperException
175    */
176   public Map<Compound, Map<Long, String>> findAll(Connection con)
177   throws SQLException, MapperException {
178 	  Map<Compound, Map<Long, String>> result = new Hashtable<Compound, Map<Long, String>>();
179 	  Statement stm = null;
180 	  ResultSet rs = null;
181 	  try {
182 		  rheaCompoundReader.setConnection(con);
183 		  stm = con.createStatement();
184 		  rs = stm.executeQuery(FIND_ALL);
185 		  Long lastCompoundId = null;
186 		  Map<Long, String> lastMap = null;
187 		  while (rs.next()){
188 			  Long compoundId = rs.getLong("compound_id");
189 			  Long enzymeId = rs.getLong("enzyme_id");
190 			  String ec = rs.getString("ec");
191 			  if (!compoundId.equals(lastCompoundId)){
192 				  // new in the result
193 				  Compound compound = rheaCompoundReader.find(compoundId);
194 				  lastMap = new HashMap<Long, String>();
195 				  result.put(compound, lastMap);
196 			  }
197 			  lastMap.put(enzymeId, ec);
198 			  lastCompoundId = compoundId;
199 		  }
200 	  } finally {
201 		  if (rs != null) rs.close();
202 		  if (stm != null) stm.close();
203 		  rheaCompoundReader.close();
204 	  }
205 	  return result;
206   }
207 
208   /**
209    * Stores the given list of cofactors into the database.
210    *
211    * @param cofactors The vector of cofactors.
212    * @param enzymeId  The enzyme ID.
213    * @param status
214    * @param con       ...
215    * @throws SQLException
216    */
217   public void insert(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
218   throws SQLException {
219 	  if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
220 	  if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
221 	  if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
222 	  if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
223 
224 	  PreparedStatement insertStatement = null;
225 
226 	  try {
227 		  insertStatement = con.prepareStatement(INSERT_STM);
228 		  int iii = 1;
229 		  for (Object o : cofactors) {
230 			  if (o instanceof Cofactor){
231 				  Cofactor cofactor = (Cofactor) o;
232 				  doInsert(cofactor, enzymeId, (iii), status, null, null, insertStatement);
233 				  insertStatement.execute();
234 			  } else if (o instanceof OperatorSet){
235 				  OperatorSet os = (OperatorSet) o;
236 				  doInsert(os, enzymeId, (iii), status, "0", 0, insertStatement);
237 			  }
238 			  iii++;
239 		  }
240 	  } finally {
241 		  insertStatement.close();
242 	  }
243   }
244 
245   /**
246    * Reloads the given list of cofactors into the database.
247    *
248    * @param cofactors The vector of cofactors.
249    * @param enzymeId  The enzyme ID.
250    * @param status
251    * @param con       ...
252    * @throws SQLException
253    */
254   public void reload(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
255   throws SQLException {
256     if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
257     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
258     if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
259     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
260 
261     deleteAll(enzymeId, con);
262     insert(cofactors, enzymeId, status, con);
263   }
264   
265   /**
266    * Updates the ChEBI mapping of a cofactor, and the name if needed too.
267    * @param oldCf the old cofactor
268    * @param newCf the new cofactor
269    * @param con
270    * @throws SQLException
271    */
272   public void update(Compound oldCf, Compound newCf, Connection con)
273   throws SQLException{
274 	  PreparedStatement stm = null;
275 	  try {
276 		  stm = con.prepareStatement(UPDATE_STM);
277 		  stm.setLong(1, newCf.getId());
278 		  stm.setString(2, newCf.getName());
279 		  stm.setLong(3, oldCf.getId());
280 		  stm.execute();
281 	  } finally {
282 		  if (stm != null) stm.close();
283 	  }
284   }
285 
286   /**
287    * Deletes all cofactors related to one enzyme instance.
288    *
289    * @param enzymeId Enzyme ID of the enzyme instance.
290    * @param con      ...
291    * @throws SQLException
292    */
293   public void deleteAll(Long enzymeId, Connection con) throws SQLException {
294     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
295     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
296 
297     PreparedStatement deleteAllStatement = null;
298 
299     try {
300       deleteAllStatement = con.prepareStatement(DELETE_ALL_STM);
301       deleteAllStatement.setLong(1, enzymeId.longValue());
302       deleteAllStatement.execute();
303     } finally {
304       deleteAllStatement.close();
305     }
306   }
307 
308 
309   // ------------------- PRIVATE METHODS ------------------------
310 
311   /**
312    * Creates the <code>Cofactor</code> object from the given result set.
313    *
314    * @param rs The result set object.
315    * @return a <code>Cofactor</code> instance.
316    * @throws SQLException if a generic database error occurs.
317    * @throws MapperException in case of error loading compounds.
318    * @throws MapperException
319    */
320   private Cofactor doLoad(ResultSet rs) throws SQLException, MapperException {
321     assert rs != null;
322 
323     String cofactorString = "";
324     String source = "";
325     String view = "";
326     Long compoundId;
327 
328     if (rs.getString("cofactor_text") != null) cofactorString = rs.getString("cofactor_text");
329     if (rs.getString("source") != null) source = rs.getString("source");
330     if (rs.getString("web_view") != null) view = rs.getString("web_view");
331     compoundId = rs.getLong("compound_id");
332 
333     Compound compound = null;
334 	if (compoundId == Compound.NO_ID_ASSIGNED){
335 		compound = Compound.valueOf(compoundId, cofactorString, null, 0, null, null, null, null);
336 	} else {
337         try {
338     	    rheaCompoundReader.setConnection(rs.getStatement().getConnection());
339     	    compound = rheaCompoundReader.find(compoundId);
340         } finally {
341         	rheaCompoundReader.close();
342         }
343 	}
344     return Cofactor.valueOf(compound, EnzymeSourceConstant.valueOf(source), EnzymeViewConstant.valueOf(view));
345   }
346 
347   @SuppressWarnings("unchecked")
348   private Set<Object> getCofactors(ResultSet rs)
349   throws SQLException, MapperException {
350 	  Set result = null;
351 	  int currentOrderIn = 0;
352 	  OperatorSet.Builder osBuilder = null;
353       while (rs.next()) {
354     	  if (result == null) result = Collections.synchronizedSet(new HashSet());
355     	  int orderIn = rs.getInt("order_in");
356     	  if (orderIn > currentOrderIn){
357     		  if (osBuilder != null && !osBuilder.isEmpty()){
358 	    		  result.add(osBuilder.getOperatorSet());
359 	    		  osBuilder.clear();
360     		  }
361     		  currentOrderIn = orderIn;
362     	  }
363     	  Cofactor cofactor = doLoad(rs);
364     	  if (rs.getString("op_grp") == null){
365     		  result.add(cofactor);
366     	  } else {
367     		  if (osBuilder == null) osBuilder = new OperatorSet.Builder();
368     		  osBuilder.add(rs.getString("op_grp"), rs.getString("operator"), cofactor);
369     	  }
370       }
371 	  if (osBuilder != null && !osBuilder.isEmpty()) result.add(osBuilder.getOperatorSet());
372 	  return result;
373   }
374 
375   private int doInsert(OperatorSet cofactors, Long enzymeId, int orderIn, Status status,
376   		String operatorGroup, int currentGroup, PreparedStatement insertStatement)
377   throws SQLException{
378   	for (Object o : cofactors) {
379   		if (o instanceof Cofactor){
380   			Cofactor cofactor = (Cofactor) o;
381   			doInsert(cofactor, enzymeId, orderIn, status, cofactors.getOperator(),
382   					operatorGroup, insertStatement);
383   			insertStatement.execute();
384   		} else {
385   			currentGroup++;
386   			OperatorSet os = (OperatorSet) o;
387   			currentGroup = doInsert(os, enzymeId, orderIn, status,
388   					operatorGroup+String.valueOf(currentGroup), currentGroup, insertStatement);
389   		}
390   	}
391   	return currentGroup;
392   }
393 
394   /**
395    * Sets the parameters of the insert statement.
396    *
397    * @param cofactor        ...
398    * @param enzymeId        ...
399    * @param orderIn         ...
400    * @param insertStatement ...
401    * @throws java.sql.SQLException
402    */
403   private void doInsert(Cofactor cofactor, Long enzymeId, int orderIn, Status status,
404                         String operator, String operatorGroup, PreparedStatement insertStatement)
405           throws SQLException {
406     assert cofactor != null : "Parameter 'cofactor' must not be null.";
407     assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
408     assert orderIn > 0 : "Parameter 'orderIn' must be > 0.";
409     assert status != null : "Parameter 'status' must not be null.";
410     assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
411     assert !(operator == null ^ operatorGroup == null) : "No operator w/o operator group and viceversa";
412 
413     insertStatement.setLong(1, enzymeId.longValue());
414     insertStatement.setString(2, cofactor.getCompound().getName());
415     insertStatement.setInt(3, orderIn);
416     insertStatement.setString(4, cofactor.getSource().toString());
417     insertStatement.setString(5, status.getCode());
418     insertStatement.setString(6, cofactor.getView().toString());
419     if (cofactor.getCompound().getId() == Compound.NO_ID_ASSIGNED){
420     	insertStatement.setNull(7, Types.NUMERIC);
421     } else {
422         insertStatement.setLong(7, cofactor.getCompound().getId());
423     }
424     if (operatorGroup == null){
425     	insertStatement.setNull(8, Types.VARCHAR);
426     	insertStatement.setNull(9, Types.VARCHAR);
427     } else {
428     	insertStatement.setString(8, operator);
429     	insertStatement.setString(9, operatorGroup);
430     }
431   }
432 }