webservices/compbio/stat/collector/StatDB.java

   1 package compbio.stat.collector;\r
   2 \r
   3 import java.sql.Connection;\r
   4 import java.sql.DriverManager;\r
   5 import java.sql.PreparedStatement;\r
   6 import java.sql.ResultSet;\r
   7 import java.sql.SQLException;\r
   8 import java.sql.Statement;\r
   9 import java.sql.Timestamp;\r
  10 import java.util.ArrayList;\r
  11 import java.util.Date;\r
  12 import java.util.List;\r
  13 import java.util.Set;\r
  14 \r
  15 import org.apache.log4j.Logger;\r
  16 \r
  17 import compbio.engine.conf.PropertyHelperManager;\r
  18 import compbio.util.Util;\r
  19 import compbio.ws.client.Services;\r
  20 \r
  21 /**\r
  22  * The database must be stored in the application root directory and called\r
  23  * "ExecutionStatistic"\r
  24  * \r
  25  * @author pvtroshin\r
  26  * \r
  27  */\r
  28 public class StatDB {\r
  29 \r
  30         private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";\r
  31         private static final String protocol = "jdbc:derby:";\r
  32         private static final String statDBName = "ExecutionStatistic";\r
  33 \r
  34         private static final Logger log = Logger.getLogger(StatDB.class);\r
  35 \r
  36         static Connection conn;\r
  37 \r
  38         private synchronized static Connection getDBConnection()\r
  39                         throws SQLException {\r
  40 \r
  41                 if (conn != null && !conn.isClosed()) {\r
  42                         return conn;\r
  43                 } else {\r
  44                         String dbpath = PropertyHelperManager.getLocalPath();\r
  45                         log.info("Looking for JABAWS access statistics database at: "\r
  46                                         + dbpath);\r
  47                         System.setProperty("derby.system.home", dbpath);\r
  48                         conn = DriverManager.getConnection(protocol + statDBName\r
  49                                         + ";create=false");\r
  50 \r
  51                         conn.setAutoCommit(true);\r
  52                         /*\r
  53                          * Runtime.getRuntime().addShutdownHook(new Thread() {\r
  54                          * \r
  55                          * @Override public void run() { shutdownDBServer(); } });\r
  56                          */\r
  57                 }\r
  58                 return conn;\r
  59         }\r
  60         public StatDB() throws SQLException {\r
  61                 this.conn = getDBConnection();\r
  62         }\r
  63 \r
  64         /**\r
  65          * Connect to test database\r
  66          * \r
  67          * @param ignored\r
  68          * @throws SQLException\r
  69          */\r
  70         StatDB(boolean ignored) throws SQLException {\r
  71                 this.conn = getTestDBConnection();\r
  72         }\r
  73 \r
  74         private static Connection getTestDBConnection() throws SQLException {\r
  75                 System.setProperty("derby.system.home", "testsrc/testdata");\r
  76                 Connection conn = DriverManager.getConnection(protocol + statDBName\r
  77                                 + ";create=false");\r
  78                 conn.setAutoCommit(true);\r
  79                 log.debug("Connecting to the TEST database!");\r
  80                 return conn;\r
  81         }\r
  82 \r
  83         // ServiceName,jobname,start,finish,inputSize,resultSize,isCancelled,isCollected\r
  84         /**\r
  85          * \r
  86          * rs.getBoolean(i) will return true for any non-zero value and false for 0\r
  87          * on SMALLINT data column.\r
  88          * \r
  89          * @throws SQLException\r
  90          */\r
  91         private void createStatTable() throws SQLException {\r
  92 \r
  93                 /*\r
  94                  * Creating a statement object that we can use for running various SQL\r
  95                  * statements commands against the database.\r
  96                  */\r
  97                 Statement s = conn.createStatement();\r
  98                 String create = "create table exec_stat("\r
  99                                 + "number INT GENERATED ALWAYS AS IDENTITY,"\r
 100                                 + "service_name VARCHAR(15) NOT NULL, "\r
 101                                 + "cluster_job_id VARCHAR(30), "\r
 102                                 + "job_id VARCHAR(35) NOT NULL PRIMARY KEY, "\r
 103                                 + "start TIMESTAMP," + "finish TIMESTAMP,"\r
 104                                 + "inputsize BIGINT," + "resultsize BIGINT,"\r
 105                                 + "isCancelled SMALLINT NOT NULL,"\r
 106                                 + "isCollected SMALLINT NOT NULL, "\r
 107                                 + "isClusterJob SMALLINT NOT NULL)";\r
 108                 // We create a table...\r
 109                 log.debug(create);\r
 110                 s.execute(create);\r
 111                 s.close();\r
 112                 conn.close();\r
 113         }\r
 114 \r
 115         static void clearStatTable() throws SQLException {\r
 116                 Connection conn = getDBConnection();\r
 117                 String query = "delete from exec_stat";\r
 118                 Statement st = conn.createStatement();\r
 119                 st.executeUpdate(query);\r
 120                 st.close();\r
 121                 conn.commit();\r
 122                 conn.close();\r
 123         }\r
 124 \r
 125         void insertData(Set<JobStat> jobstatus) throws SQLException {\r
 126                 log.info("Inserting " + jobstatus.size()\r
 127                                 + " new records into the statistics database");\r
 128 \r
 129                 conn.setAutoCommit(false);\r
 130                 String insert = "insert into exec_stat (service_name, cluster_job_id, job_id, start, finish, "\r
 131                                 + "inputsize, resultsize, isCancelled, isCollected, isClusterJob) "\r
 132                                 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";\r
 133                 PreparedStatement pstm = conn.prepareStatement(insert);\r
 134                 for (JobStat js : jobstatus) {\r
 135                         // Has to present\r
 136                         pstm.setString(1, js.webService.toString());\r
 137 \r
 138                         if (!Util.isEmpty(js.clusterJobId)) {\r
 139                                 pstm.setString(2, js.clusterJobId);\r
 140                         } else {\r
 141                                 pstm.setString(2, null);\r
 142                         }\r
 143                         // Has to present\r
 144                         pstm.setString(3, js.jobname);\r
 145 \r
 146                         if (js.start != ExecutionStatCollector.UNDEFINED) {\r
 147                                 pstm.setTimestamp(4, new Timestamp(js.start));\r
 148                         } else {\r
 149                                 pstm.setTimestamp(4, null);\r
 150                         }\r
 151                         if (js.finish != ExecutionStatCollector.UNDEFINED) {\r
 152                                 pstm.setTimestamp(5, new Timestamp(js.finish));\r
 153                         } else {\r
 154                                 pstm.setTimestamp(5, null);\r
 155                         }\r
 156                         // -1 if UNDEFINED\r
 157                         pstm.setLong(6, js.inputSize);\r
 158                         // -1 if UNDEFINED\r
 159                         pstm.setLong(7, js.resultSize);\r
 160 \r
 161                         pstm.setBoolean(8, js.isCancelled);\r
 162                         pstm.setBoolean(9, js.isCollected);\r
 163                         pstm.setBoolean(10, js.isClusterJob());\r
 164                         pstm.executeUpdate();\r
 165                 }\r
 166                 conn.commit();\r
 167                 conn.setAutoCommit(true);\r
 168                 pstm.close();\r
 169         }\r
 170 \r
 171         public Date getEarliestRecord() throws SQLException {\r
 172                 String query = "select min(start) from exec_stat";\r
 173                 Statement st = conn.createStatement();\r
 174                 ResultSet res = st.executeQuery(query);\r
 175                 boolean exist = res.next();\r
 176                 Date date = new Date();\r
 177                 if (exist) {\r
 178                         date = res.getDate(1);\r
 179                 }\r
 180 \r
 181                 res.close();\r
 182                 st.close();\r
 183                 return date;\r
 184         }\r
 185 \r
 186         public int getTotalJobsCount(Timestamp from, Timestamp to)\r
 187                         throws SQLException {\r
 188                 String allQuery = "select count(*) from exec_stat where start BETWEEN ? and ? ";\r
 189                 return getIntResult(from, to, allQuery);\r
 190         }\r
 191 \r
 192         public int getCancelledCount(Timestamp from, Timestamp to)\r
 193                         throws SQLException {\r
 194                 // js.isCancelled\r
 195                 String cancelledQuery = "select count(*) from exec_stat where start BETWEEN ? and ?  and  isCancelled=1 ";\r
 196                 return getIntResult(from, to, cancelledQuery);\r
 197         }\r
 198 \r
 199         public int getAbandonedCount(Timestamp from, Timestamp to)\r
 200                         throws SQLException {\r
 201                 // !js.isCollected && !js.isCancelled && js.hasResult()\r
 202                 String abandonedQuery = "select count(*) from exec_stat where start BETWEEN ? and ? and isCollected=0 and isCancelled=0 and resultsize>0 ";\r
 203                 return getIntResult(from, to, abandonedQuery);\r
 204         }\r
 205 \r
 206         public int getIncompleteCount(Timestamp from, Timestamp to)\r
 207                         throws SQLException {\r
 208                 // !js.hasResult()\r
 209                 String incompleteQuery = "select count(*) from exec_stat where start BETWEEN ? and ? and resultsize<=0 and isCancelled=0";\r
 210                 return getIntResult(from, to, incompleteQuery);\r
 211         }\r
 212 \r
 213         private int getIntResult(Timestamp from, Timestamp to, String query)\r
 214                         throws SQLException {\r
 215 \r
 216                 log.debug("getIntRes: QUERY: " + query);\r
 217                 log.debug("getIntRes: FROM: " + from);\r
 218                 log.debug("getIntRes: TO: " + to);\r
 219 \r
 220                 PreparedStatement pstm = conn.prepareStatement(query);\r
 221                 pstm.setTimestamp(1, from);\r
 222                 pstm.setTimestamp(2, to);\r
 223                 pstm.execute();\r
 224                 ResultSet res = pstm.getResultSet();\r
 225                 boolean exist = res.next();\r
 226                 int count = 0;\r
 227                 if (exist) {\r
 228                         count = res.getInt(1);\r
 229                 }\r
 230                 log.debug("getIntRes: RES: " + count);\r
 231                 res.close();\r
 232                 pstm.close();\r
 233                 return count;\r
 234         }\r
 235 \r
 236         public List<JobStat> readData(Timestamp from, Timestamp to,\r
 237                         Services wservice, Boolean clusterOnly) throws SQLException {\r
 238 \r
 239                 String query = "select service_name, cluster_job_id, job_id, start, finish, inputsize, "\r
 240                                 + "resultsize, isCancelled, isCollected from exec_stat where start BETWEEN ? and ? ";\r
 241 \r
 242                 if (wservice != null) {\r
 243                         query += " and service_name=? ";\r
 244                 }\r
 245 \r
 246                 if (clusterOnly != null) {\r
 247                         if (clusterOnly) {\r
 248                                 query += " and isClusterJob!=0 ";\r
 249                         } else {\r
 250                                 query += " and isClusterJob=0 ";\r
 251                         }\r
 252                 }\r
 253 \r
 254                 log.debug("QUERY: " + query);\r
 255                 log.debug("FROM: " + from);\r
 256                 log.debug("TO: " + to);\r
 257                 log.debug("WS: " + wservice);\r
 258 \r
 259                 PreparedStatement pstm = conn.prepareStatement(query);\r
 260                 pstm.setTimestamp(1, from);\r
 261                 pstm.setTimestamp(2, to);\r
 262                 if (wservice != null) {\r
 263                         pstm.setString(3, wservice.toString());\r
 264                 }\r
 265                 pstm.execute();\r
 266                 List<JobStat> stats = new ArrayList<JobStat>();\r
 267                 ResultSet rs = pstm.getResultSet();\r
 268                 int rcount = 0;\r
 269 \r
 270                 while (rs.next()) {\r
 271                         rcount++;\r
 272                         stats.add(JobStat.newInstance(Services.getService(rs.getString(1)),\r
 273                                         rs.getString(2), rs.getString(3), rs.getTimestamp(4),\r
 274                                         rs.getTimestamp(5), rs.getLong(6), rs.getLong(7),\r
 275                                         rs.getBoolean(8), rs.getBoolean(9)));\r
 276                 }\r
 277 \r
 278                 log.debug("QUERY result len: " + rcount);\r
 279                 rs.close();\r
 280                 pstm.close();\r
 281 \r
 282                 return stats;\r
 283         }\r
 284 \r
 285         /**\r
 286          * Removes the job if\r
 287          * \r
 288          * 1) It has already been recorded\r
 289          * \r
 290          * 2) It has not completed and did not timeout - this is to prevent\r
 291          * recording the information on the incomplete jobs.\r
 292          * \r
 293          * @param fsJobs\r
 294          * @throws SQLException\r
 295          */\r
 296         public void removeRecordedJobs(Set<JobStat> fsJobs) throws SQLException {\r
 297 \r
 298                 String query = "select job_id from exec_stat";\r
 299 \r
 300                 Statement st = conn.createStatement();\r
 301                 ResultSet result = st.executeQuery(query);\r
 302 \r
 303                 while (result.next()) {\r
 304                         String recordedJob = result.getString(1);\r
 305                         JobStat recStat = JobStat.newIncompleteStat(recordedJob);\r
 306                         if (fsJobs.contains(recStat)) {\r
 307                                 fsJobs.remove(recStat);\r
 308                         }\r
 309                 }\r
 310                 result.close();\r
 311                 st.close();\r
 312         }\r
 313 \r
 314         public static synchronized final void shutdownDBServer() {\r
 315                 // ## DATABASE SHUTDOWN SECTION ##\r
 316                 /***\r
 317                  * In embedded mode, an application should shut down Derby. Shutdown\r
 318                  * throws the XJ015 exception to confirm success.\r
 319                  ***/\r
 320                 try {\r
 321                         if (conn != null) {\r
 322                                 conn.close();\r
 323                         }\r
 324                 } catch (SQLException e) {\r
 325                         log.warn("Database commit failed with " + e.getLocalizedMessage());\r
 326                 }\r
 327                 boolean gotSQLExc = false;\r
 328                 try {\r
 329                         DriverManager.getConnection("jdbc:derby:;shutdown=true");\r
 330                 } catch (SQLException se) {\r
 331                         if (se.getSQLState().equals("XJ015")) {\r
 332                                 gotSQLExc = true;\r
 333                         }\r
 334                 }\r
 335                 if (!gotSQLExc) {\r
 336                         log.warn("Database did not shut down normally");\r
 337                 } else {\r
 338                         log.info("Database shut down normally");\r
 339                 }\r
 340         }\r
 341         public static void main(String[] args) {\r
 342                 // This is called from Ant cleanStatTable task\r
 343                 try {\r
 344                         clearStatTable();\r
 345                         shutdownDBServer();\r
 346                 } catch (SQLException e) {\r
 347                         System.err.println("Fails to clean up JABAWS stat database!");\r
 348                         e.printStackTrace();\r
 349                 }\r
 350                 // new StatDB().createStatTable();\r
 351                 // insertData(null);\r
 352                 /*\r
 353                  * StatDB statdb = new StatDB(); Date from = new Date();\r
 354                  * from.setMonth(1); System.out.println(new\r
 355                  * StatProcessor(statdb.readData( new Timestamp(from.getTime()), new\r
 356                  * Timestamp(new Date().getTime()), null, null)).reportStat());\r
 357                  */\r
 358         }\r
 359 }\r