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
+ String dbpath = PropertyHelperManager.getLocalPath();\r
+ log.info("Looking for JABAWS access statistics database at: "\r
+ + dbpath);\r
+ System.setProperty("derby.system.home", dbpath);\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
+ }\r
return conn;\r
}\r
-\r
public StatDB() throws SQLException {\r
this.conn = getDBConnection();\r
}\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.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 ";\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
Services wservice, Boolean clusterOnly) throws SQLException {\r
\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
git://source.jalview.org / jabaws.git / blobdiff