1 package uk.ac.ebi.intenz.mapper;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 import java.sql.Types;
9 import java.util.Collection;
10 import java.util.Collections;
11 import java.util.HashMap;
12 import java.util.HashSet;
13 import java.util.Hashtable;
14 import java.util.Map;
15 import java.util.Set;
16
17 import org.apache.log4j.Logger;
18
19 import uk.ac.ebi.biobabel.util.collections.OperatorSet;
20 import uk.ac.ebi.intenz.domain.constants.EnzymeSourceConstant;
21 import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
22 import uk.ac.ebi.intenz.domain.constants.Status;
23 import uk.ac.ebi.intenz.domain.enzyme.Cofactor;
24 import uk.ac.ebi.rhea.domain.Compound;
25 import uk.ac.ebi.rhea.domain.XRef.Availability;
26 import uk.ac.ebi.rhea.mapper.MapperException;
27
28
29
30
31
32
33
34
35
36
37 public class EnzymeCofactorMapper {
38
39 private static final Logger LOGGER =
40 Logger.getLogger(EnzymeCofactorMapper.class.getName());
41
42 public EnzymeCofactorMapper(){
43 }
44
45 public void close(){
46 }
47
48 @Override
49 protected void finalize() throws Throwable {
50 close();
51 }
52
53 private static final String COLUMNS =
54 "enzyme_id, cofactor_text, order_in, source, status, web_view, compound_id, operator, op_grp";
55
56 private static final String FIND_STM =
57 "SELECT " + COLUMNS + " FROM cofactors WHERE enzyme_id = ? AND compound_id IS NOT NULL" +
58 " ORDER BY order_in, op_grp ASC";
59
60 private static final String FIND_BY_CHEBI_ID =
61 "SELECT cd.compound_id, cd.name FROM compound_data cd"
62 + " WHERE cd.accession = ?";
63
64 private static final String FIND_SIB_COFACTORS_STM =
65 "SELECT " + COLUMNS + " FROM cofactors" +
66 " WHERE enzyme_id = ? AND compound_id IS NOT NULL AND (web_view = 'INTENZ' OR web_view LIKE '%SIB%')" +
67 " ORDER BY order_in, op_grp ASC";
68
69 private static final String FIND_ALL =
70 "SELECT cf.compound_id, cf.enzyme_id, f_quad2string(e.ec1, e.ec2, e.ec3, e.ec4) ec" +
71 " FROM cofactors cf, enzymes e WHERE cf.compound_id IS NOT NULL and cf.enzyme_id = e.enzyme_id" +
72 " ORDER BY compound_id";
73
74 private static final String LOAD_COMPOUND_STM =
75 "SELECT accession, name, formula, charge, published"
76 + " FROM compound_data WHERE compound_id = ?";
77
78 private static final String INSERT_STM =
79 "INSERT INTO cofactors (" + COLUMNS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
80
81 private static final String INSERT_COMPOUND_STM =
82 "INSERT INTO compound_data"
83 + " (compound_id, name, source, accession) VALUES"
84 + " (s_compound_id.nextval, ?, 'CHEBI', ?)";
85
86 private static final String DELETE_COMPOUND_STM =
87 "DELETE FROM compound_data WHERE compound_id = ?";
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.debug("No cofactor information found for the enzyme with ID "
128 + enzymeId);
129 }
130 return result;
131 }
132
133
134
135
136
137
138
139
140
141 public Compound findByChebiId(String chebiId, Connection con)
142 throws SQLException{
143 Compound compound = null;
144 PreparedStatement stm = null;
145 ResultSet rs = null;
146 try {
147 stm = con.prepareStatement(FIND_BY_CHEBI_ID);
148 stm.setString(1, chebiId);
149 rs = stm.executeQuery();
150 if (rs.next()){
151 compound = new Compound(rs.getLong("compound_id"),
152 rs.getString("name"), "CHEBI", chebiId);
153 }
154 } finally {
155 if (rs != null) rs.close();
156 if (stm != null) stm.close();
157 }
158 return compound;
159 }
160
161
162
163
164
165
166
167
168
169
170
171
172
173 public Set<Object> exportSibCofactors(Long enzymeId, Connection con)
174 throws SQLException, MapperException {
175 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
176 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
177
178 PreparedStatement findStatement = null;
179 ResultSet rs = null;
180 Set<Object> result = new HashSet<Object>();
181
182 try {
183
184 findStatement = con.prepareStatement(FIND_SIB_COFACTORS_STM);
185 findStatement.setLong(1, enzymeId.longValue());
186 rs = findStatement.executeQuery();
187 result = getCofactors(rs);
188 } finally {
189 if (rs != null) rs.close();
190 if (findStatement != null) findStatement.close();
191 }
192
193 return result;
194 }
195
196
197
198
199
200
201
202
203
204
205 public Map<Compound, Map<Long, String>> findAll(Connection con)
206 throws SQLException, MapperException {
207 Map<Compound, Map<Long, String>> result = new Hashtable<Compound, Map<Long, String>>();
208 Statement stm = null;
209 ResultSet rs = null;
210 try {
211 stm = con.createStatement();
212 rs = stm.executeQuery(FIND_ALL);
213 Long lastCompoundId = null;
214 Map<Long, String> lastMap = null;
215 while (rs.next()){
216 Long compoundId = rs.getLong("compound_id");
217 Long enzymeId = rs.getLong("enzyme_id");
218 String ec = rs.getString("ec");
219 if (!compoundId.equals(lastCompoundId)){
220
221 Compound compound = getCompound(con, compoundId);
222 lastMap = new HashMap<Long, String>();
223 result.put(compound, lastMap);
224 }
225 lastMap.put(enzymeId, ec);
226 lastCompoundId = compoundId;
227 }
228 } finally {
229 if (rs != null) rs.close();
230 if (stm != null) stm.close();
231 }
232 return result;
233 }
234
235
236
237
238
239
240
241
242
243
244 public void insert(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
245 throws SQLException {
246 if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
247 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
248 if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
249 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
250
251 PreparedStatement insertStatement = null;
252
253 try {
254 insertStatement = con.prepareStatement(INSERT_STM);
255 int iii = 1;
256 for (Object o : cofactors) {
257 if (o instanceof Cofactor){
258 Cofactor cofactor = (Cofactor) o;
259 doInsert(cofactor, enzymeId, (iii), status, null, null, insertStatement);
260 insertStatement.execute();
261 } else if (o instanceof OperatorSet){
262 OperatorSet os = (OperatorSet) o;
263 doInsert(os, enzymeId, (iii), status, "0", 0, insertStatement);
264 }
265 iii++;
266 }
267 } finally {
268 insertStatement.close();
269 }
270 }
271
272
273
274
275
276
277
278
279
280 public Long insertCompound(Compound compound, Connection con)
281 throws SQLException {
282 Long compoundId = null;
283 PreparedStatement stm = null;
284 ResultSet rs = null;
285 try {
286 stm = con.prepareStatement(INSERT_COMPOUND_STM,
287 new String[]{ "compound_id" });
288 stm.setString(1, compound.getXmlName());
289 stm.setString(2, compound.getAccession());
290 int result = stm.executeUpdate();
291 if (result > 0){
292 rs = stm.getGeneratedKeys();
293 if (rs != null && rs.next()){
294 compoundId = rs.getLong(1);
295 compound.setId(compoundId);
296 }
297 }
298 LOGGER.info("Inserted new cofactor: " + compound.getXmlName()
299 + " [" + compound.getAccession() + "]");
300 return compoundId;
301 } finally {
302 if (rs != null) rs.close();
303 if (stm != null) stm.close();
304 }
305 }
306
307
308
309
310
311
312
313
314 public void deleteCompound(Long compoundId, Connection con)
315 throws SQLException{
316 PreparedStatement stm = null;
317 try {
318 stm = con.prepareStatement(DELETE_COMPOUND_STM);
319 stm.setLong(1, compoundId);
320 stm.execute();
321 LOGGER.info("Deleted compound with internal ID " + compoundId);
322 } finally {
323 if (stm != null) stm.close();
324 }
325 }
326
327
328
329
330
331
332
333
334
335
336 public void reload(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
337 throws SQLException {
338 if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
339 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
340 if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
341 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
342
343 deleteAll(enzymeId, con);
344 insert(cofactors, enzymeId, status, con);
345 }
346
347
348
349
350
351
352
353
354 public void update(Compound oldCf, Compound newCf, Connection con)
355 throws SQLException{
356 PreparedStatement stm = null;
357 try {
358 stm = con.prepareStatement(UPDATE_STM);
359 stm.setLong(1, newCf.getId());
360 stm.setString(2, newCf.getName());
361 stm.setLong(3, oldCf.getId());
362 stm.execute();
363 } finally {
364 if (stm != null) stm.close();
365 }
366 }
367
368
369
370
371
372
373
374
375 public void deleteAll(Long enzymeId, Connection con) throws SQLException {
376 if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
377 if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
378
379 PreparedStatement deleteAllStatement = null;
380
381 try {
382 deleteAllStatement = con.prepareStatement(DELETE_ALL_STM);
383 deleteAllStatement.setLong(1, enzymeId.longValue());
384 deleteAllStatement.execute();
385 } finally {
386 deleteAllStatement.close();
387 }
388 }
389
390
391
392
393
394
395
396
397
398
399
400 private Cofactor doLoad(ResultSet rs) throws SQLException {
401 assert rs != null;
402
403 String cofactorString = "";
404 String source = "";
405 String view = "";
406 Long compoundId;
407
408 if (rs.getString("cofactor_text") != null) cofactorString = rs.getString("cofactor_text");
409 if (rs.getString("source") != null) source = rs.getString("source");
410 if (rs.getString("web_view") != null) view = rs.getString("web_view");
411 compoundId = rs.getLong("compound_id");
412
413 Compound compound = null;
414 if (compoundId == Compound.NO_ID_ASSIGNED){
415 compound = Compound.valueOf(compoundId, null, cofactorString, null,
416 null, null, Availability.N);
417 } else {
418 Connection connection = rs.getStatement().getConnection();
419 compound = getCompound(connection, compoundId);
420 }
421 return Cofactor.valueOf(compound, EnzymeSourceConstant.valueOf(source), EnzymeViewConstant.valueOf(view));
422 }
423
424
425
426
427
428
429
430
431 public Compound getCompound(Connection connection, Long compoundId)
432 throws SQLException {
433 Compound compound = null;
434 PreparedStatement stm = null;
435 ResultSet cofactorRs = null;
436 try {
437 stm = connection.prepareStatement(LOAD_COMPOUND_STM);
438 stm.setLong(1, compoundId);
439 cofactorRs = stm.executeQuery();
440 if (cofactorRs.next()){
441 String accession = cofactorRs.getString("accession");
442 String name = cofactorRs.getString("name");
443 String formula = cofactorRs.getString("formula");
444 String charge = String.valueOf(cofactorRs.getInt("charge"));
445 String avail = cofactorRs.getString("published");
446 compound = Compound.valueOf(compoundId,
447 Long.valueOf(accession.replace("CHEBI:", "")),
448 name, formula, charge, accession,
449 avail == null? null : Availability.valueOf(avail));
450 }
451 } finally {
452 if (cofactorRs != null) cofactorRs.close();
453 if (stm != null) stm.close();
454 }
455 return compound;
456 }
457
458 @SuppressWarnings("unchecked")
459 private Set<Object> getCofactors(ResultSet rs)
460 throws SQLException, MapperException {
461 Set result = null;
462 int currentOrderIn = 0;
463 OperatorSet.Builder osBuilder = null;
464 while (rs.next()) {
465 if (result == null) result = Collections.synchronizedSet(new HashSet());
466 int orderIn = rs.getInt("order_in");
467 if (orderIn > currentOrderIn){
468 if (osBuilder != null && !osBuilder.isEmpty()){
469 result.add(osBuilder.getOperatorSet());
470 osBuilder.clear();
471 }
472 currentOrderIn = orderIn;
473 }
474 Cofactor cofactor = doLoad(rs);
475 if (rs.getString("op_grp") == null){
476 result.add(cofactor);
477 } else {
478 if (osBuilder == null) osBuilder = new OperatorSet.Builder();
479 osBuilder.add(rs.getString("op_grp"), rs.getString("operator"), cofactor);
480 }
481 }
482 if (osBuilder != null && !osBuilder.isEmpty()) result.add(osBuilder.getOperatorSet());
483 return result;
484 }
485
486 private int doInsert(OperatorSet cofactors, Long enzymeId, int orderIn, Status status,
487 String operatorGroup, int currentGroup, PreparedStatement insertStatement)
488 throws SQLException{
489 for (Object o : cofactors) {
490 if (o instanceof Cofactor){
491 Cofactor cofactor = (Cofactor) o;
492 doInsert(cofactor, enzymeId, orderIn, status, cofactors.getOperator(),
493 operatorGroup, insertStatement);
494 insertStatement.execute();
495 } else {
496 currentGroup++;
497 OperatorSet os = (OperatorSet) o;
498 currentGroup = doInsert(os, enzymeId, orderIn, status,
499 operatorGroup+String.valueOf(currentGroup), currentGroup, insertStatement);
500 }
501 }
502 return currentGroup;
503 }
504
505
506
507
508
509
510
511
512
513
514 private void doInsert(Cofactor cofactor, Long enzymeId, int orderIn, Status status,
515 String operator, String operatorGroup, PreparedStatement insertStatement)
516 throws SQLException {
517 assert cofactor != null : "Parameter 'cofactor' must not be null.";
518 assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
519 assert orderIn > 0 : "Parameter 'orderIn' must be > 0.";
520 assert status != null : "Parameter 'status' must not be null.";
521 assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
522 assert !(operator == null ^ operatorGroup == null) : "No operator w/o operator group and viceversa";
523
524 insertStatement.setLong(1, enzymeId.longValue());
525 insertStatement.setString(2, cofactor.getCompound().getXmlName());
526 insertStatement.setInt(3, orderIn);
527 insertStatement.setString(4, cofactor.getSource().toString());
528 insertStatement.setString(5, status.getCode());
529 insertStatement.setString(6, cofactor.getView().toString());
530 if (Compound.NO_ID_ASSIGNED.equals(cofactor.getCompound().getId())){
531 insertStatement.setLong(7, insertCompound(
532 cofactor.getCompound(), insertStatement.getConnection()));
533 } else {
534 insertStatement.setLong(7, cofactor.getCompound().getId());
535 }
536 if (operatorGroup == null){
537 insertStatement.setNull(8, Types.VARCHAR);
538 insertStatement.setNull(9, Types.VARCHAR);
539 } else {
540 insertStatement.setString(8, operator);
541 insertStatement.setString(9, operatorGroup);
542 }
543 }
544 }