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
31
32
33
34
35
36
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
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
97
98
99
100
101
102
103
104
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
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
134
135
136
137
138
139
140
141
142
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
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
169
170
171
172
173
174
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
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
210
211
212
213
214
215
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
247
248
249
250
251
252
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
267
268
269
270
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
288
289
290
291
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
310
311
312
313
314
315
316
317
318
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
396
397
398
399
400
401
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 }