+/* Copyright (c) 2011 Peter Troshin\r
+ * \r
+ * JAva Bioinformatics Analysis Web Services (JABAWS) @version: 2.0 \r
+ * \r
+ * This library is free software; you can redistribute it and/or modify it under the terms of the\r
+ * Apache License version 2 as published by the Apache Software Foundation\r
+ * \r
+ * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without\r
+ * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the Apache \r
+ * License for more details.\r
+ * \r
+ * A copy of the license is in apache_license.txt. It is also available here:\r
+ * @see: http://www.apache.org/licenses/LICENSE-2.0.txt\r
+ * \r
+ * Any republication or derived work distributed in source code form\r
+ * must include this copyright and license notice.\r
+ */\r
package compbio.stat.collector;\r
\r
import java.sql.Connection;\r
import java.sql.Statement;\r
import java.sql.Timestamp;\r
import java.util.ArrayList;\r
+import java.util.Date;\r
import java.util.List;\r
import java.util.Set;\r
\r
\r
private static final Logger log = Logger.getLogger(StatDB.class);\r
\r
- Connection conn;\r
- private static Connection getDBConnection() throws SQLException {\r
- String dbpath = PropertyHelperManager.getLocalPath();\r
- log.info("Looking for JABAWS access statistics database at: " + dbpath);\r
- System.setProperty("derby.system.home", dbpath);\r
- Connection conn = DriverManager.getConnection(protocol + statDBName\r
- + ";create=false");\r
+ static Connection conn;\r
\r
- conn.setAutoCommit(true);\r
+ private synchronized static Connection getDBConnection()\r
+ throws SQLException {\r
+\r
+ if (conn != null && !conn.isClosed()) {\r
+ return conn;\r
+ } else {\r
+ try {\r
+ String dbpath = PropertyHelperManager.getLocalPath();\r
+ log.info("Looking for JABAWS access statistics database at: "\r
+ + dbpath);\r
+ System.setProperty("derby.system.home", dbpath);\r
+ // Apparently under Tomcat webapp you cannot rely on Java\r
+ // auto discovery and have to register the driver explicitly\r
+ Class.forName(driver);\r
+ conn = DriverManager.getConnection(protocol + statDBName\r
+ + ";create=false");\r
+\r
+ conn.setAutoCommit(true);\r
+ /*\r
+ * Runtime.getRuntime().addShutdownHook(new Thread() {\r
+ * \r
+ * @Override public void run() { shutdownDBServer(); } });\r
+ */\r
+ } catch (ClassNotFoundException e) {\r
+ log.error(e.getMessage(), e);\r
+ }\r
+ }\r
return conn;\r
}\r
-\r
public StatDB() throws SQLException {\r
this.conn = getDBConnection();\r
}\r
Connection conn = DriverManager.getConnection(protocol + statDBName\r
+ ";create=false");\r
conn.setAutoCommit(true);\r
+ log.debug("Connecting to the TEST database!");\r
return conn;\r
}\r
\r
+ "isCollected SMALLINT NOT NULL, "\r
+ "isClusterJob SMALLINT NOT NULL)";\r
// We create a table...\r
- System.out.println(create);\r
+ log.debug(create);\r
s.execute(create);\r
s.close();\r
conn.close();\r
}\r
\r
+ static void clearStatTable() throws SQLException {\r
+ Connection conn = getDBConnection();\r
+ String query = "delete from exec_stat";\r
+ Statement st = conn.createStatement();\r
+ st.executeUpdate(query);\r
+ st.close();\r
+ conn.commit();\r
+ conn.close();\r
+ }\r
+\r
void insertData(Set<JobStat> jobstatus) throws SQLException {\r
- System.out.println("Inserting " + jobstatus.size());\r
+ log.info("Inserting " + jobstatus.size()\r
+ + " new records into the statistics database");\r
\r
conn.setAutoCommit(false);\r
String insert = "insert into exec_stat (service_name, cluster_job_id, job_id, start, finish, "\r
pstm.executeUpdate();\r
}\r
conn.commit();\r
+ conn.setAutoCommit(true);\r
+ pstm.close();\r
+ }\r
+\r
+ public Date getEarliestRecord() throws SQLException {\r
+ String query = "select min(start) from exec_stat";\r
+ Statement st = conn.createStatement();\r
+ ResultSet res = st.executeQuery(query);\r
+ boolean exist = res.next();\r
+ Date date = new Date();\r
+ if (exist) {\r
+ date = res.getDate(1);\r
+ }\r
+\r
+ res.close();\r
+ st.close();\r
+ return date;\r
+ }\r
+\r
+ public int getTotalJobsCount(Timestamp from, Timestamp to)\r
+ throws SQLException {\r
+ String allQuery = "select count(*) from exec_stat where start BETWEEN ? and ? ";\r
+ return getIntResult(from, to, allQuery);\r
+ }\r
+\r
+ public int getCancelledCount(Timestamp from, Timestamp to)\r
+ throws SQLException {\r
+ // js.isCancelled\r
+ String cancelledQuery = "select count(*) from exec_stat where start BETWEEN ? and ? and isCancelled=1 ";\r
+ return getIntResult(from, to, cancelledQuery);\r
+ }\r
+\r
+ public int getAbandonedCount(Timestamp from, Timestamp to)\r
+ throws SQLException {\r
+ // !js.isCollected && !js.isCancelled && js.hasResult()\r
+ String abandonedQuery = "select count(*) from exec_stat where start BETWEEN ? and ? and isCollected=0 and isCancelled=0 and resultsize>0 ";\r
+ return getIntResult(from, to, abandonedQuery);\r
+ }\r
+\r
+ public int getIncompleteCount(Timestamp from, Timestamp to)\r
+ throws SQLException {\r
+ // !js.hasResult()\r
+ String incompleteQuery = "select count(*) from exec_stat where start BETWEEN ? and ? and resultsize<=0 and isCancelled=0";\r
+ return getIntResult(from, to, incompleteQuery);\r
+ }\r
+\r
+ private int getIntResult(Timestamp from, Timestamp to, String query)\r
+ throws SQLException {\r
+\r
+ log.debug("getIntRes: QUERY: " + query);\r
+ log.debug("getIntRes: FROM: " + from);\r
+ log.debug("getIntRes: TO: " + to);\r
+\r
+ PreparedStatement pstm = conn.prepareStatement(query);\r
+ pstm.setTimestamp(1, from);\r
+ pstm.setTimestamp(2, to);\r
+ pstm.execute();\r
+ ResultSet res = pstm.getResultSet();\r
+ boolean exist = res.next();\r
+ int count = 0;\r
+ if (exist) {\r
+ count = res.getInt(1);\r
+ }\r
+ log.debug("getIntRes: RES: " + count);\r
+ res.close();\r
pstm.close();\r
+ return count;\r
}\r
\r
public List<JobStat> readData(Timestamp from, Timestamp to,\r
}\r
}\r
\r
+ log.debug("QUERY: " + query);\r
+ log.debug("FROM: " + from);\r
+ log.debug("TO: " + to);\r
+ log.debug("WS: " + wservice);\r
+\r
PreparedStatement pstm = conn.prepareStatement(query);\r
pstm.setTimestamp(1, from);\r
pstm.setTimestamp(2, to);\r
pstm.execute();\r
List<JobStat> stats = new ArrayList<JobStat>();\r
ResultSet rs = pstm.getResultSet();\r
+ int rcount = 0;\r
+\r
while (rs.next()) {\r
+ rcount++;\r
stats.add(JobStat.newInstance(Services.getService(rs.getString(1)),\r
rs.getString(2), rs.getString(3), rs.getTimestamp(4),\r
rs.getTimestamp(5), rs.getLong(6), rs.getLong(7),\r
rs.getBoolean(8), rs.getBoolean(9)));\r
}\r
+\r
+ log.debug("QUERY result len: " + rcount);\r
rs.close();\r
pstm.close();\r
\r
return stats;\r
}\r
+\r
+ /**\r
+ * Removes the job if\r
+ * \r
+ * 1) It has already been recorded\r
+ * \r
+ * 2) It has not completed and did not timeout - this is to prevent\r
+ * recording the information on the incomplete jobs.\r
+ * \r
+ * @param fsJobs\r
+ * @throws SQLException\r
+ */\r
public void removeRecordedJobs(Set<JobStat> fsJobs) throws SQLException {\r
\r
String query = "select job_id from exec_stat";\r
}\r
}\r
result.close();\r
+ st.close();\r
}\r
\r
- public void shutdownDBServer() {\r
+ public static synchronized final void shutdownDBServer() {\r
// ## DATABASE SHUTDOWN SECTION ##\r
/***\r
* In embedded mode, an application should shut down Derby. Shutdown\r
conn.close();\r
}\r
} catch (SQLException e) {\r
- System.err.println("Database commit failed with "\r
- + e.getLocalizedMessage());\r
+ log.warn("Database commit failed with " + e.getLocalizedMessage());\r
}\r
boolean gotSQLExc = false;\r
try {\r
}\r
}\r
if (!gotSQLExc) {\r
- System.err.println("Database did not shut down normally");\r
+ log.warn("Database did not shut down normally");\r
} else {\r
- System.out.println("Database shut down normally");\r
+ log.info("Database shut down normally");\r
}\r
}\r
- public static void main(String[] args) throws SQLException {\r
+ public static void main(String[] args) {\r
+ // This is called from Ant cleanStatTable task\r
+ try {\r
+ clearStatTable();\r
+ shutdownDBServer();\r
+ } catch (SQLException e) {\r
+ System.err.println("Fails to clean up JABAWS stat database!");\r
+ e.printStackTrace();\r
+ }\r
// new StatDB().createStatTable();\r
// insertData(null);\r
/*\r