load the driver explicitly
[jabaws.git] / webservices / compbio / stat / collector / StatDB.java
index dd7e2b7..b469c44 100644 (file)
@@ -1,3 +1,20 @@
+/* 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
@@ -8,6 +25,7 @@ import java.sql.SQLException;
 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
@@ -32,18 +50,37 @@ public class StatDB {
 \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
@@ -63,6 +100,7 @@ public class StatDB {
                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
@@ -92,14 +130,25 @@ public class StatDB {
                                + "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
@@ -139,7 +188,73 @@ public class StatDB {
                        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
@@ -160,6 +275,11 @@ public class StatDB {
                        }\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
@@ -169,17 +289,34 @@ public class StatDB {
                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
@@ -195,9 +332,10 @@ public class StatDB {
                        }\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
@@ -208,8 +346,7 @@ public class StatDB {
                                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
@@ -220,12 +357,20 @@ public class StatDB {
                        }\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