From 4ec4e979c9020b6c5302659fb09a41fa35bf68fb Mon Sep 17 00:00:00 2001 From: Natasha Sherstneva Date: Wed, 20 Nov 2013 14:55:59 +0000 Subject: [PATCH] PROT-4 updated CF JobDateInfo (add new columns), JpredArchive (add column ExecutionStatus), add new CF FailLog --- .../cassandra/CassandraNativeConnector.java | 9 +- datadb/compbio/cassandra/CassandraReader.java | 13 ++- datadb/compbio/cassandra/CassandraWriter.java | 88 +++++++++++++------- datadb/compbio/cassandra/DataBase.java | 43 +++++++++- datadb/compbio/cassandra/JpredParserHTTP.java | 6 +- server/compbio/statistic/CassandraRequester.java | 24 +++--- website/ReportJobsByDate.jsp | 37 +++++--- 7 files changed, 154 insertions(+), 66 deletions(-) diff --git a/datadb/compbio/cassandra/CassandraNativeConnector.java b/datadb/compbio/cassandra/CassandraNativeConnector.java index edae10d..132611f 100644 --- a/datadb/compbio/cassandra/CassandraNativeConnector.java +++ b/datadb/compbio/cassandra/CassandraNativeConnector.java @@ -65,17 +65,20 @@ public class CassandraNativeConnector { session.execute("CREATE TABLE IF NOT EXISTS ProteinData " + "(jobtime bigint, JobID ascii, ExecTime int, Protein ascii, PRIMARY KEY(jobtime, JobID));"); + session.execute("CREATE TABLE IF NOT EXISTS FailLog " + + "(jobtime bigint, JobID ascii, ExecTime int, ip ascii, FinalStatus ascii, PRIMARY KEY(jobtime, JobID));"); + session.execute("CREATE TABLE IF NOT EXISTS JpredArchive " - + "(JobID ascii, Protein varchar, IP ascii, StartTime bigint, ExecTime int, FinalStatus ascii, alignment map, " + + "(JobID ascii, Protein varchar, IP ascii, StartTime bigint, ExecTime int, FinalStatus ascii, ExecutionStatus ascii, alignment map, " + "predictions map, ArchiveLink varchar, LOG varchar, PRIMARY KEY(JobID));"); session.execute("CREATE TABLE IF NOT EXISTS JobDateInfo " - + "(jobday bigint, Total bigint, Program varchar, Version varchar, PRIMARY KEY(jobday));"); + + "(jobday bigint, Total bigint, TotalOK bigint, TotalStopped bigint, TotalError bigint, TotalTimeOut bigint, Program varchar, Version varchar, PRIMARY KEY(jobday));"); session.execute("CREATE INDEX IF NOT EXISTS ProteinSeq ON ProteinRow (protein);"); session.execute("CREATE INDEX IF NOT EXISTS ProteinIp ON ProteinLog (ip);"); // session.execute("CREATE INDEX IF NOT EXISTS JobDateStamp ON ProteinData (jobtime);"); - } + } public void Closing() { session.shutdown(); diff --git a/datadb/compbio/cassandra/CassandraReader.java b/datadb/compbio/cassandra/CassandraReader.java index 941ad1c..f4ceee5 100644 --- a/datadb/compbio/cassandra/CassandraReader.java +++ b/datadb/compbio/cassandra/CassandraReader.java @@ -53,12 +53,17 @@ public class CassandraReader { /* * getting data from the db JobDateInfo */ - public long ReadDateTable(long queryDate) { - ResultSet results = session.execute("SELECT Total FROM JobDateInfo WHERE jobday = " + queryDate + ";"); + public List ReadDateTable(long queryDate) { + ResultSet results = session.execute("SELECT * FROM JobDateInfo WHERE jobday = " + queryDate + ";"); if (results.isExhausted()) - return 0; + return null; Row therow = results.one(); - long res = therow.getLong("Total"); + List res = new ArrayList(); + res.add(therow.getLong("Total")); + res.add(therow.getLong("TotalOK")); + res.add(therow.getLong("TotalStopped")); + res.add(therow.getLong("TotalError")); + res.add(therow.getLong("TotalTimeOut")); if (!results.isExhausted()) { Date date = new Date (queryDate); log.warn("CassandraReader.ReadDateTable: date row for " + date.toString () + " ("+ queryDate + ") duplicated "); diff --git a/datadb/compbio/cassandra/CassandraWriter.java b/datadb/compbio/cassandra/CassandraWriter.java index 592016e..521f98c 100644 --- a/datadb/compbio/cassandra/CassandraWriter.java +++ b/datadb/compbio/cassandra/CassandraWriter.java @@ -1,5 +1,8 @@ package compbio.cassandra; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Date; import java.util.List; import org.apache.log4j.Logger; @@ -48,6 +51,10 @@ public class CassandraWriter { */ public int FormQueryTables(JpredJob job) { if (JobisNotInsterted(job.getJobID())) { + int njobsOk = 0; + int njobsStop = 0; + int njobsError = 0; + int njobsTimeOut = 0; String id = job.getJobID(); String ip = job.getIP(); String protein = job.getProtein(); @@ -57,42 +64,55 @@ public class CassandraWriter { + " VALUES ('" + id + "','" + ip + "','" + job.getStartingTimeStr() + "','" + job.getEndTimeStr() + "','" + finalstatus + "','" + execstatus + "','" + protein + "');"; session.execute(com1); - - String com2 = "INSERT INTO ProteinData " + "(jobtime, JobID, ExecTime, Protein)" + " VALUES (" + job.getStartingDate() + ",'" + id - + "'," + job.getExecutionTime() + ",'" + protein + "');"; - session.execute(com2); - - String allpredictions = ""; - List pr = job.getPredictions(); - for (FastaSequence pred : pr) { - String predictionname = pred.getId(); - String prediction = pred.getSequence().replaceAll("\n", ""); - allpredictions += "'" + predictionname + "':'" + prediction + "',"; - } - String final_prediction = ""; - if (!allpredictions.equals("")) { - final_prediction = allpredictions.substring(0, allpredictions.length() - 1); - } - - String check2 = "SELECT * FROM ProteinRow WHERE JobID = '" + job.getJobID() + "';"; - ResultSet results2 = session.execute(check2); - if (results2.isExhausted()) { - String com3 = "INSERT INTO ProteinRow " + "(Protein, JobID, Predictions)" + " VALUES ('" + protein + "','" + id + "',{" - + final_prediction + "});"; - session.execute(com3); + if (execstatus.equals("OK")) { + String com2 = "INSERT INTO ProteinData " + "(jobtime, JobID, ExecTime, Protein)" + " VALUES (" + job.getStartingDate() + ",'" + id + + "'," + job.getExecutionTime() + ",'" + protein + "');"; + session.execute(com2); + + String allpredictions = ""; + List pr = job.getPredictions(); + for (FastaSequence pred : pr) { + String predictionname = pred.getId(); + String prediction = pred.getSequence().replaceAll("\n", ""); + allpredictions += "'" + predictionname + "':'" + prediction + "',"; + } + String final_prediction = ""; + if (!allpredictions.equals("")) { + final_prediction = allpredictions.substring(0, allpredictions.length() - 1); + } + + String check2 = "SELECT * FROM ProteinRow WHERE JobID = '" + job.getJobID() + "';"; + ResultSet results2 = session.execute(check2); + if (results2.isExhausted()) { + String com3 = "INSERT INTO ProteinRow " + "(Protein, JobID, Predictions)" + " VALUES ('" + protein + "','" + id + "',{" + + final_prediction + "});"; + session.execute(com3); + } + njobsOk = 1; + } else { + String com5 = "INSERT INTO FailLog " + "(jobtime, JobID, ExecTime, ip, FinalStatus)" + " VALUES (" + + job.getStartingDate() + ",'" + id + + "'," + job.getExecutionTime() + ",'" + ip + "', '" + finalstatus + "');"; + session.execute(com5); + System.out.println(com5); + if (finalstatus.equals("TIMEDOUT")) + njobsTimeOut = 1; + else if (finalstatus.equals("JPREDERROR")) + njobsError = 1; + else if (finalstatus.equals("STOPPED")) + njobsStop = 1; } - // update some internal query tables String check3 = "SELECT * FROM MainParameters WHERE Name = 'EarliestJobDate';"; ResultSet results3 = session.execute(check3); boolean updateparameter = true; if (!results3.isExhausted()) { - Row r = results3.one(); + Row r = results3.one(); if (job.getStartingDate() >= Long.parseLong(r.getString("Value"))) updateparameter = false; } if (updateparameter) { - String com = "INSERT INTO MainParameters " + "(Name, Value)" + " VALUES ('EarliestJobDate','" + job.getStartingDateStr() + String com = "INSERT INTO MainParameters " + "(Name, Value)" + " VALUES ('EarliestJobDate','" + job.getStartingDate() + "');"; session.execute(com); } @@ -103,10 +123,15 @@ public class CassandraWriter { if (!results4.isExhausted()) { Row r = results4.one(); njobs += r.getLong("Total"); + njobsOk += r.getLong("TotalOK"); + njobsError += r.getLong("TotalError"); + njobsStop += r.getLong("TotalStopped"); + njobsTimeOut += r.getLong("TotalTimeOut"); } - String com = "INSERT INTO JobDateInfo " + "(jobday, Total)" + " VALUES (" + job.getStartingDate() + "," + njobs + ");"; + String com = "INSERT INTO JobDateInfo " + "(jobday, Total, TotalOK, TotalStopped, TotalError, TotalTimeOut)" + " VALUES (" + + job.getStartingDate() + "," + njobs + "," + njobsOk + "," + njobsStop + "," + njobsError + "," + njobsTimeOut + ");"; + System.out.println(com); session.execute(com); - return 1; } return 0; @@ -120,11 +145,10 @@ public class CassandraWriter { if (JobisNotArchived(job.getJobID())) { String id = job.getJobID(); String log = job.getLog().replaceAll("'", ""); - String com = "INSERT INTO JpredArchive (JobID, Protein, IP, StartTime, ExecTime, FinalStatus, LOG, ArchiveLink) VALUES ('" + id + "','" - + job.getProtein() + "','" + job.getIP() + "'," + job.getStartingTime() + "," + job.getExecutionTime() + ",'" + job.getFinalStatus() + "','" + log - + "','" + archivepath + "');"; + String com = "INSERT INTO JpredArchive (JobID, Protein, IP, StartTime, ExecTime, FinalStatus, ExecutionStatus, LOG, ArchiveLink) VALUES ('" + id + "','" + + job.getProtein() + "','" + job.getIP() + "'," + job.getStartingTime() + "," + job.getExecutionTime() + ",'" + job.getFinalStatus() + "','" + + job.getExecutionStatus() + "','" + log + "','" + archivepath + "');"; session.execute(com); - List predictions = job.getPredictions(); for (FastaSequence p : predictions) { session.execute("UPDATE JpredArchive SET predictions = predictions + {'" + p.getId() + "':'" diff --git a/datadb/compbio/cassandra/DataBase.java b/datadb/compbio/cassandra/DataBase.java index 91eabf0..d0586e7 100644 --- a/datadb/compbio/cassandra/DataBase.java +++ b/datadb/compbio/cassandra/DataBase.java @@ -6,13 +6,17 @@ import java.util.Collections; public class DataBase { private String date; - private int total; + private int total; // total jobs + private int totalOK; // total jobs with execution status OK + private int totalStopped; // total jobs with execution status STOPPED + private int totalError; // total jobs with execution status Jpred ERROR + private int totalTimeOut; // total jobs with execution status TIMEOUT private int totalJobs; - private int totalId; + private int totalId; // total jobs for current protein sequence private String id; - private String prot; + private String prot; // protein sequence private String jpred; - private List subProt; + private List subProt; // protein sequence divided by several parts for highlighting the particular part private List timeRez; private List timeTotalExec; private StructureJobLog logInfo; @@ -41,6 +45,37 @@ public class DataBase { return total; } + public void setTotalOK(int tot) { + this.totalOK = tot; + } + + public int getTotalOK() { + return totalOK; + } + + public void setTotalStopped(int tot) { + this.totalStopped = tot; + } + + public int getTotalStopped() { + return totalStopped; + } + + public void setTotalError(int tot) { + this.totalError = tot; + } + + public int getTotalError() { + return totalError; + } + + public void setTotalTimeOut(int tot) { + this.totalTimeOut = tot; + } + + public int getTotalTimeOut() { + return totalTimeOut; + } public void setTotalJobs(int totalJobs) { this.totalJobs = totalJobs; diff --git a/datadb/compbio/cassandra/JpredParserHTTP.java b/datadb/compbio/cassandra/JpredParserHTTP.java index e53ddd8..bb2993b 100644 --- a/datadb/compbio/cassandra/JpredParserHTTP.java +++ b/datadb/compbio/cassandra/JpredParserHTTP.java @@ -54,8 +54,8 @@ public class JpredParserHTTP implements JpredParser { private String parsePredictions(final InputStream stream, String jobid) throws FileNotFoundException { final FastaReader fr = new FastaReader(stream); String protein = ""; - alignment = new ArrayList(); - predictions = new ArrayList(); +// alignment = new ArrayList(); +// predictions = new ArrayList(); while (fr.hasNext()) { final FastaSequence fs = fr.next(); String seqid = fs.getId(); @@ -84,6 +84,8 @@ public class JpredParserHTTP implements JpredParser { } private int analyseJob(String[] jobinfo) throws IOException { + alignment = new ArrayList(); + predictions = new ArrayList(); boolean running = true; boolean ConcisefileExists = false; boolean LogfileExists = false; diff --git a/server/compbio/statistic/CassandraRequester.java b/server/compbio/statistic/CassandraRequester.java index 2da38e7..20ffe0e 100755 --- a/server/compbio/statistic/CassandraRequester.java +++ b/server/compbio/statistic/CassandraRequester.java @@ -141,11 +141,15 @@ public class CassandraRequester { end.setTime(new Date(dateEnd)); query = new ArrayList(); for (Date date = start.getTime(); !start.after(end); start.add(Calendar.DATE, 1), date = start.getTime()) { - long res = db.ReadDateTable(date.getTime()); - DataBase db = new DataBase(); - db.setTotal((int)res); - db.setDate(DateFormat(date.getTime())); - query.add(db); + List res = db.ReadDateTable(date.getTime()); + DataBase bean = new DataBase(); + bean.setTotal((int)(long)res.get(0)); + bean.setTotalOK((int)(long)res.get(1)); + bean.setTotalStopped((int)(long)res.get(2)); + bean.setTotalError((int)(long)res.get(3)); + bean.setTotalTimeOut((int)(long)res.get(4)); + bean.setDate(DateFormat(date.getTime())); + query.add(bean); } System.out.println("StatisticsProt.readLength: total number of dates = " + query.size()); return query; @@ -184,11 +188,11 @@ public class CassandraRequester { query = new ArrayList(); Map map = db.ReadProteinSequenceByCounter(); for (Map.Entry entry : map.entrySet()) { - if (entry.getValue() > minimalcounter) { - DataBase db = new DataBase(); - db.setTotalId(entry.getValue()); - db.setProt(entry.getKey()); - query.add(db); + if (entry.getValue() > minimalcounter && entry.getKey().length() > 0) { + DataBase bean = new DataBase(); + bean.setTotalId(entry.getValue()); + bean.setProt(entry.getKey()); + query.add(bean); } } return query; diff --git a/website/ReportJobsByDate.jsp b/website/ReportJobsByDate.jsp index 261a11b..5192e37 100644 --- a/website/ReportJobsByDate.jsp +++ b/website/ReportJobsByDate.jsp @@ -19,9 +19,21 @@

Time execution: ${timeExecution} ms

+ + + + + + + + + + + + @@ -29,13 +41,14 @@ Date - Number of Proteins + Total jobs + Number of jobs - Total - Failed - Cancelled - Abandoned + Status "OK" + Status "Stopped" + Status "Error" + Status "Time out" @@ -43,18 +56,20 @@ Total: ${sum} - 0 - 0 - 0 + ${sumOK} + ${sumStopped} + ${sumError} + ${sumTimeOut} ${res.date} - 0 - 0 - 0 + ${res.totalOK} + ${res.totalStopped} + ${res.totalError} + ${res.totalTimeOut} -- 1.7.10.2