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.List;\r
  12 import java.util.Set;\r
  13 \r
  14 import org.apache.log4j.Logger;\r
  15 \r
  16 import compbio.engine.conf.PropertyHelperManager;\r
  17 import compbio.util.Util;\r
  18 import compbio.ws.client.Services;\r
  19 \r
  20 /**\r
  21  * The database must be stored in the application root directory and called\r
  22  * "ExecutionStatistic"\r
  23  * \r
  24  * @author pvtroshin\r
  25  * \r
  26  */\r
  27 public class StatDB {\r
  28 \r
  29         private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";\r
  30         private static final String protocol = "jdbc:derby:";\r
  31         private static final String statDBName = "ExecutionStatistic";\r
  32 \r
  33         private static final Logger log = Logger.getLogger(StatDB.class);\r
  34 \r
  35         Connection conn;\r
  36         private static Connection getDBConnection() throws SQLException {\r
  37                 String dbpath = PropertyHelperManager.getLocalPath();\r
  38                 log.info("Looking for JABAWS access statistics database at: " + dbpath);\r
  39                 System.setProperty("derby.system.home", dbpath);\r
  40                 Connection conn = DriverManager.getConnection(protocol + statDBName\r
  41                                 + ";create=false");\r
  42 \r
  43                 conn.setAutoCommit(true);\r
  44                 return conn;\r
  45         }\r
  46 \r
  47         public StatDB() throws SQLException {\r
  48                 this.conn = getDBConnection();\r
  49         }\r
  50 \r
  51         /**\r
  52          * Connect to test database\r
  53          * \r
  54          * @param ignored\r
  55          * @throws SQLException\r
  56          */\r
  57         StatDB(boolean ignored) throws SQLException {\r
  58                 this.conn = getTestDBConnection();\r
  59         }\r
  60 \r
  61         private static Connection getTestDBConnection() throws SQLException {\r
  62                 System.setProperty("derby.system.home", "testsrc/testdata");\r
  63                 Connection conn = DriverManager.getConnection(protocol + statDBName\r
  64                                 + ";create=false");\r
  65                 conn.setAutoCommit(true);\r
  66                 return conn;\r
  67         }\r
  68 \r
  69         // ServiceName,jobname,start,finish,inputSize,resultSize,isCancelled,isCollected\r
  70         /**\r
  71          * \r
  72          * rs.getBoolean(i) will return true for any non-zero value and false for 0\r
  73          * on SMALLINT data column.\r
  74          * \r
  75          * @throws SQLException\r
  76          */\r
  77         private void createStatTable() throws SQLException {\r
  78 \r
  79                 /*\r
  80                  * Creating a statement object that we can use for running various SQL\r
  81                  * statements commands against the database.\r
  82                  */\r
  83                 Statement s = conn.createStatement();\r
  84                 String create = "create table exec_stat("\r
  85                                 + "number INT GENERATED ALWAYS AS IDENTITY,"\r
  86                                 + "service_name VARCHAR(15) NOT NULL, "\r
  87                                 + "cluster_job_id VARCHAR(30), "\r
  88                                 + "job_id VARCHAR(35) NOT NULL PRIMARY KEY, "\r
  89                                 + "start TIMESTAMP," + "finish TIMESTAMP,"\r
  90                                 + "inputsize BIGINT," + "resultsize BIGINT,"\r
  91                                 + "isCancelled SMALLINT NOT NULL,"\r
  92                                 + "isCollected SMALLINT NOT NULL, "\r
  93                                 + "isClusterJob SMALLINT NOT NULL)";\r
  94                 // We create a table...\r
  95                 System.out.println(create);\r
  96                 s.execute(create);\r
  97                 s.close();\r
  98                 conn.close();\r
  99         }\r
 100 \r
 101         void insertData(Set<JobStat> jobstatus) throws SQLException {\r
 102                 System.out.println("Inserting " + jobstatus.size());\r
 103 \r
 104                 conn.setAutoCommit(false);\r
 105                 String insert = "insert into exec_stat (service_name, cluster_job_id, job_id, start, finish, "\r
 106                                 + "inputsize, resultsize, isCancelled, isCollected, isClusterJob) "\r
 107                                 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";\r
 108                 PreparedStatement pstm = conn.prepareStatement(insert);\r
 109                 for (JobStat js : jobstatus) {\r
 110                         // Has to present\r
 111                         pstm.setString(1, js.webService.toString());\r
 112 \r
 113                         if (!Util.isEmpty(js.clusterJobId)) {\r
 114                                 pstm.setString(2, js.clusterJobId);\r
 115                         } else {\r
 116                                 pstm.setString(2, null);\r
 117                         }\r
 118                         // Has to present\r
 119                         pstm.setString(3, js.jobname);\r
 120 \r
 121                         if (js.start != ExecutionStatCollector.UNDEFINED) {\r
 122                                 pstm.setTimestamp(4, new Timestamp(js.start));\r
 123                         } else {\r
 124                                 pstm.setTimestamp(4, null);\r
 125                         }\r
 126                         if (js.finish != ExecutionStatCollector.UNDEFINED) {\r
 127                                 pstm.setTimestamp(5, new Timestamp(js.finish));\r
 128                         } else {\r
 129                                 pstm.setTimestamp(5, null);\r
 130                         }\r
 131                         // -1 if UNDEFINED\r
 132                         pstm.setLong(6, js.inputSize);\r
 133                         // -1 if UNDEFINED\r
 134                         pstm.setLong(7, js.resultSize);\r
 135 \r
 136                         pstm.setBoolean(8, js.isCancelled);\r
 137                         pstm.setBoolean(9, js.isCollected);\r
 138                         pstm.setBoolean(10, js.isClusterJob());\r
 139                         pstm.executeUpdate();\r
 140                 }\r
 141                 conn.commit();\r
 142                 pstm.close();\r
 143         }\r
 144 \r
 145         public List<JobStat> readData(Timestamp from, Timestamp to,\r
 146                         Services wservice, Boolean clusterOnly) throws SQLException {\r
 147 \r
 148                 String query = "select service_name, cluster_job_id, job_id, start, finish, inputsize, "\r
 149                                 + "resultsize, isCancelled, isCollected from exec_stat where start BETWEEN ? and ? ";\r
 150 \r
 151                 if (wservice != null) {\r
 152                         query += " and service_name=? ";\r
 153                 }\r
 154 \r
 155                 if (clusterOnly != null) {\r
 156                         if (clusterOnly) {\r
 157                                 query += " and isClusterJob!=0 ";\r
 158                         } else {\r
 159                                 query += " and isClusterJob=0 ";\r
 160                         }\r
 161                 }\r
 162 \r
 163                 PreparedStatement pstm = conn.prepareStatement(query);\r
 164                 pstm.setTimestamp(1, from);\r
 165                 pstm.setTimestamp(2, to);\r
 166                 if (wservice != null) {\r
 167                         pstm.setString(3, wservice.toString());\r
 168                 }\r
 169                 pstm.execute();\r
 170                 List<JobStat> stats = new ArrayList<JobStat>();\r
 171                 ResultSet rs = pstm.getResultSet();\r
 172                 while (rs.next()) {\r
 173                         stats.add(JobStat.newInstance(Services.getService(rs.getString(1)),\r
 174                                         rs.getString(2), rs.getString(3), rs.getTimestamp(4),\r
 175                                         rs.getTimestamp(5), rs.getLong(6), rs.getLong(7),\r
 176                                         rs.getBoolean(8), rs.getBoolean(9)));\r
 177                 }\r
 178                 rs.close();\r
 179                 pstm.close();\r
 180 \r
 181                 return stats;\r
 182         }\r
 183         public void removeRecordedJobs(Set<JobStat> fsJobs) throws SQLException {\r
 184 \r
 185                 String query = "select job_id from exec_stat";\r
 186 \r
 187                 Statement st = conn.createStatement();\r
 188                 ResultSet result = st.executeQuery(query);\r
 189 \r
 190                 while (result.next()) {\r
 191                         String recordedJob = result.getString(1);\r
 192                         JobStat recStat = JobStat.newIncompleteStat(recordedJob);\r
 193                         if (fsJobs.contains(recStat)) {\r
 194                                 fsJobs.remove(recStat);\r
 195                         }\r
 196                 }\r
 197                 result.close();\r
 198         }\r
 199 \r
 200         public void shutdownDBServer() {\r
 201                 // ## DATABASE SHUTDOWN SECTION ##\r
 202                 /***\r
 203                  * In embedded mode, an application should shut down Derby. Shutdown\r
 204                  * throws the XJ015 exception to confirm success.\r
 205                  ***/\r
 206                 try {\r
 207                         if (conn != null) {\r
 208                                 conn.close();\r
 209                         }\r
 210                 } catch (SQLException e) {\r
 211                         System.err.println("Database commit failed with "\r
 212                                         + e.getLocalizedMessage());\r
 213                 }\r
 214                 boolean gotSQLExc = false;\r
 215                 try {\r
 216                         DriverManager.getConnection("jdbc:derby:;shutdown=true");\r
 217                 } catch (SQLException se) {\r
 218                         if (se.getSQLState().equals("XJ015")) {\r
 219                                 gotSQLExc = true;\r
 220                         }\r
 221                 }\r
 222                 if (!gotSQLExc) {\r
 223                         System.err.println("Database did not shut down normally");\r
 224                 } else {\r
 225                         System.out.println("Database shut down normally");\r
 226                 }\r
 227         }\r
 228         public static void main(String[] args) throws SQLException {\r
 229                 // new StatDB().createStatTable();\r
 230                 // insertData(null);\r
 231                 /*\r
 232                  * StatDB statdb = new StatDB(); Date from = new Date();\r
 233                  * from.setMonth(1); System.out.println(new\r
 234                  * StatProcessor(statdb.readData( new Timestamp(from.getTime()), new\r
 235                  * Timestamp(new Date().getTime()), null, null)).reportStat());\r
 236                  */\r
 237         }\r
 238 }\r