webservices/compbio/ws/execstat/StatDB.java

   1 package compbio.ws.execstat;\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 compbio.ws.client.Services;\r
  16 import compbio.ws.execstat.StatProcessor.JobStat;\r
  17 \r
  18 public class StatDB {\r
  19 \r
  20         /* the default framework is embedded */\r
  21         // private final String framework = "embedded";\r
  22         private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";\r
  23         private static final String protocol = "jdbc:derby:";\r
  24         private static final String statDBName = "ExecutionStatistic";\r
  25 \r
  26         private static Connection getDBConnection() throws SQLException {\r
  27                 // TODO\r
  28                 System.setProperty("derby.system.home", ".");\r
  29                 Connection conn = DriverManager.getConnection(protocol + statDBName\r
  30                                 + ";create=true");\r
  31 \r
  32                 // We want to control transactions manually. Autocommit is on by\r
  33                 // default in JDBC.\r
  34                 // conn.setAutoCommit(false);\r
  35                 return conn;\r
  36         }\r
  37 \r
  38         // ServiceName,jobname,start,finish,inputSize,resultSize,isCancelled,isCollected\r
  39         /**\r
  40          * \r
  41          * rs.getBoolean(i) will return true for any non-zero value and false for 0\r
  42          * on SMALLINT data column.\r
  43          * \r
  44          * @throws SQLException\r
  45          */\r
  46         private static void createStatTable() throws SQLException {\r
  47                 Connection conn = getDBConnection();\r
  48                 /*\r
  49                  * Creating a statement object that we can use for running various SQL\r
  50                  * statements commands against the database.\r
  51                  */\r
  52                 Statement s = conn.createStatement();\r
  53                 String create = "create table exec_stat("\r
  54                                 + "number INT GENERATED ALWAYS AS IDENTITY,"\r
  55                                 + "service_name VARCHAR(15) NOT NULL, "\r
  56                                 + "cluster_job_id VARCHAR(30), "\r
  57                                 + "job_id VARCHAR(35) NOT NULL PRIMARY KEY, "\r
  58                                 + "start TIMESTAMP," + "finish TIMESTAMP,"\r
  59                                 + "inputsize BIGINT," + "resultsize BIGINT,"\r
  60                                 + "isCancelled SMALLINT NOT NULL,"\r
  61                                 + "isCollected SMALLINT NOT NULL, "\r
  62                                 + "isClusterJob SMALLINT NOT NULL)";\r
  63                 // We create a table...\r
  64                 System.out.println(create);\r
  65                 s.execute(create);\r
  66                 s.close();\r
  67                 conn.close();\r
  68         }\r
  69 \r
  70         static void insertData(Set<JobStat> jobstatus) throws SQLException {\r
  71                 System.out.println("Inserting " + jobstatus.size());\r
  72                 Connection conn = getDBConnection();\r
  73                 conn.setAutoCommit(false);\r
  74                 String insert = "insert into exec_stat (service_name, cluster_job_id, job_id, start, finish, "\r
  75                                 + "inputsize, resultsize, isCancelled, isCollected, isClusterJob) "\r
  76                                 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";\r
  77                 PreparedStatement pstm = conn.prepareStatement(insert);\r
  78                 for (JobStat js : jobstatus) {\r
  79                         pstm.setString(1, js.webService.toString());\r
  80                         pstm.setString(2, js.clusterJobId);\r
  81                         pstm.setString(3, js.jobname);\r
  82                         pstm.setTimestamp(4, new Timestamp(js.start));\r
  83                         pstm.setTimestamp(5, new Timestamp(js.finish));\r
  84                         pstm.setLong(6, js.inputSize);\r
  85                         pstm.setLong(7, js.resultSize);\r
  86                         pstm.setBoolean(8, js.isCancelled);\r
  87                         pstm.setBoolean(9, js.isCollected);\r
  88                         pstm.setBoolean(10, js.isClusterJob());\r
  89                         pstm.executeUpdate();\r
  90                 }\r
  91                 conn.commit();\r
  92                 pstm.close();\r
  93                 conn.close();\r
  94         }\r
  95 \r
  96         static List<JobStat> readData(Timestamp from, Timestamp to,\r
  97                         Services wservice, Boolean clusterOnly) throws SQLException {\r
  98                 Connection conn = getDBConnection();\r
  99                 String query = "select service_name, cluster_job_id, job_id, start, finish, inputsize, "\r
 100                                 + "resultsize, isCancelled, isCollected from exec_stat where start BETWEEN ? and ? ";\r
 101 \r
 102                 if (wservice != null) {\r
 103                         query += " and service_name=? ";\r
 104                 }\r
 105 \r
 106                 if (clusterOnly != null) {\r
 107                         if (clusterOnly) {\r
 108                                 query += " and isClusterJob!=0 ";\r
 109                         } else {\r
 110                                 query += " and isClusterJob=0 ";\r
 111                         }\r
 112                 }\r
 113 \r
 114                 PreparedStatement pstm = conn.prepareStatement(query);\r
 115                 pstm.setTimestamp(1, from);\r
 116                 pstm.setTimestamp(2, to);\r
 117                 if (wservice != null) {\r
 118                         pstm.setString(3, wservice.toString());\r
 119                 }\r
 120                 pstm.execute();\r
 121                 List<JobStat> stats = new ArrayList<StatProcessor.JobStat>();\r
 122                 ResultSet rs = pstm.getResultSet();\r
 123                 while (rs.next()) {\r
 124                         stats.add(new JobStat(Services.getService(rs.getString(1)), rs\r
 125                                         .getString(2), rs.getString(3), rs.getTimestamp(4)\r
 126                                         .getTime(), rs.getTimestamp(5).getTime(), rs.getLong(6), rs\r
 127                                         .getLong(7), rs.getBoolean(8), rs.getBoolean(9)));\r
 128                 }\r
 129                 rs.close();\r
 130                 pstm.close();\r
 131                 conn.close();\r
 132                 return stats;\r
 133         }\r
 134 \r
 135         static void removeRecordedJobs(Set<String> fsJobs) throws SQLException {\r
 136                 Connection conn = getDBConnection();\r
 137                 String query = "select job_id from exec_stat";\r
 138 \r
 139                 Statement st = conn.createStatement();\r
 140                 ResultSet result = st.executeQuery(query);\r
 141 \r
 142                 while (result.next()) {\r
 143                         String recordedJob = result.getString(1);\r
 144                         if (fsJobs.contains(recordedJob)) {\r
 145                                 fsJobs.remove(recordedJob);\r
 146                         }\r
 147                 }\r
 148                 result.close();\r
 149                 conn.close();\r
 150         }\r
 151 \r
 152         void shutdownDBServer() {\r
 153                 // ## DATABASE SHUTDOWN SECTION ##\r
 154                 /***\r
 155                  * In embedded mode, an application should shut down Derby. Shutdown\r
 156                  * throws the XJ015 exception to confirm success.\r
 157                  ***/\r
 158                 boolean gotSQLExc = false;\r
 159                 try {\r
 160                         DriverManager.getConnection("jdbc:derby:;shutdown=true");\r
 161                 } catch (SQLException se) {\r
 162                         if (se.getSQLState().equals("XJ015")) {\r
 163                                 gotSQLExc = true;\r
 164                         }\r
 165                 }\r
 166                 if (!gotSQLExc) {\r
 167                         System.out.println("Database did not shut down normally");\r
 168                 } else {\r
 169                         System.out.println("Database shut down normally");\r
 170                 }\r
 171         }\r
 172         public static void main(String[] args) throws SQLException {\r
 173                 // createStatTable();\r
 174                 // insertData(null);\r
 175 \r
 176                 Date from = new Date();\r
 177                 from.setMonth(1);\r
 178                 System.out.println(new StatProcessor(readData(\r
 179                                 new Timestamp(from.getTime()),\r
 180                                 new Timestamp(new Date().getTime()), null, null)).reportStat());\r
 181 \r
 182         }\r
 183 }\r