1 package uk.ac.ebi.intenz.tools.release;
2
3 import uk.ac.ebi.biobabel.util.db.DatabaseInstance;
4 import uk.ac.ebi.biobabel.util.db.OracleDatabaseInstance;
5 import uk.ac.ebi.intenz.domain.enzyme.EnzymeCommissionNumber;
6 import uk.ac.ebi.rhea.domain.Status;
7
8 import java.io.IOException;
9 import java.sql.*;
10
11 import org.apache.log4j.Logger;
12
13
14
15
16
17
18
19
20
21 public class ID2EC {
22
23 private static final Logger LOGGER = Logger.getLogger(ID2EC.class);
24
25 private static String findAllStatement() {
26 return new StringBuilder("SELECT enzyme_id, ec1, ec2, ec3, ec4, status, source")
27 .append(" FROM enzyme.enzymes WHERE status IN ('OK','PR','PM')")
28 .append(" AND enzyme_id NOT IN")
29 .append(" (SELECT before_id FROM enzyme.history_events")
30 .append(" WHERE event_class = 'MOD')")
31 .append(" ORDER BY ec1, ec2, ec3, ec4")
32 .toString();
33 }
34
35 private static String DELETE_ALL = "DELETE FROM id2ec";
36
37 private static String insertIDAndECStatement() {
38 return "INSERT INTO id2ec (enzyme_id, ec, status, source) VALUES ( ?, ?, ?, ? )";
39 }
40
41
42
43
44
45
46
47 public static void main(String[] args) {
48
49 if (args.length == 0){
50 LOGGER.error("ID2EC needs one parameter (DB instance name)");
51 System.exit(1);
52 }
53
54 String instanceName = args[0];
55 DatabaseInstance instance = null;
56 try {
57 instance = OracleDatabaseInstance.getInstance(instanceName);
58 } catch (IOException e) {
59 LOGGER.error("Missing database configuration for " + instanceName, e);
60 System.exit(2);
61 }
62
63 if (instance == null){
64 LOGGER.error("Missing database parameter(s)");
65 System.exit(3);
66 }
67
68 Connection con = instance.getConnection();
69 if (con == null){
70 LOGGER.error("Could not open connection to " + instance.getName());
71 System.exit(4);
72 }
73
74 Statement deleteAllStatement = null;
75 try {
76 LOGGER.info("Deleting ID2EC table...");
77 deleteAllStatement = con.createStatement();
78 deleteAllStatement.execute(DELETE_ALL);
79 LOGGER.info("... Deleted!");
80 } catch (SQLException e) {
81 LOGGER.error("Could not clear table ID2EC on " + instanceName, e);
82 try {
83 if (deleteAllStatement != null) deleteAllStatement.close();
84 if (con != null) con.close();
85 } catch (SQLException e2) {
86 e2.printStackTrace();
87 }
88 System.exit(5);
89 }
90
91 PreparedStatement getAllPublicEnzymes = null, insertIntoID2EC = null;
92 ResultSet rs = null;
93
94 LOGGER.info("Starting ID2EC index...");
95 try {
96 insertIntoID2EC = con.prepareStatement(insertIDAndECStatement());
97
98
99 getAllPublicEnzymes = con.prepareStatement(findAllStatement());
100 rs = getAllPublicEnzymes.executeQuery();
101
102 while (rs.next()) {
103 final String id = rs.getString(1);
104 final String status = rs.getString("status");
105 final String ec = new StringBuilder(rs.getString("ec1"))
106 .append('.').append(rs.getString("ec2"))
107 .append('.').append(rs.getString("ec3"))
108 .append('.').append(status.equals("PM")? "n" : "")
109 .append(rs.getString("ec4"))
110 .toString();
111 final String source = rs.getString("source");
112 insertIntoID2EC.setString(1, id);
113 insertIntoID2EC.setString(2, ec);
114 insertIntoID2EC.setString(3, status);
115 insertIntoID2EC.setString(4, source);
116 insertIntoID2EC.executeUpdate();
117 insertIntoID2EC.clearParameters();
118 }
119
120 con.commit();
121 } catch (SQLException e) {
122 try {
123 con.rollback();
124 } catch (SQLException e1) {
125 e1.printStackTrace();
126 }
127 e.printStackTrace();
128 } finally {
129 try {
130 if (getAllPublicEnzymes != null) getAllPublicEnzymes.close();
131 if (insertIntoID2EC != null) insertIntoID2EC.close();
132 con.close();
133 } catch (SQLException e) {
134 e.printStackTrace();
135 }
136 }
137
138 LOGGER.info("Indexing finished!");
139 }
140
141 }