From 74cc8490e36a261009905b9ba604e83848fc1c20 Mon Sep 17 00:00:00 2001 From: Natasha Sherstneva Date: Tue, 19 Nov 2013 22:34:59 +0000 Subject: [PATCH] PROT-4 updated structure ProteinData (change primary key, add column ExecTime), JpredArchive (add column FinalStatus), CassandraRemover almost finished --- .../cassandra/CassandraNativeConnector.java | 7 +- datadb/compbio/cassandra/CassandraReader.java | 4 +- datadb/compbio/cassandra/CassandraRemover.java | 142 +++++++++++++++++--- datadb/compbio/cassandra/CassandraWriter.java | 8 +- server/compbio/listeners/ServletDeleteRecord.java | 19 ++- website/Update.jsp | 4 +- 6 files changed, 152 insertions(+), 32 deletions(-) diff --git a/datadb/compbio/cassandra/CassandraNativeConnector.java b/datadb/compbio/cassandra/CassandraNativeConnector.java index 9d214ec..edae10d 100644 --- a/datadb/compbio/cassandra/CassandraNativeConnector.java +++ b/datadb/compbio/cassandra/CassandraNativeConnector.java @@ -63,17 +63,18 @@ public class CassandraNativeConnector { + "ExecutionStatus ascii, Protein ascii, PRIMARY KEY(JobID));"); session.execute("CREATE TABLE IF NOT EXISTS ProteinData " - + "(jobtime bigint, JobID ascii, Protein ascii, PRIMARY KEY(JobID));"); + + "(jobtime bigint, JobID ascii, ExecTime int, Protein ascii, PRIMARY KEY(jobtime, JobID));"); session.execute("CREATE TABLE IF NOT EXISTS JpredArchive " - + "(JobID ascii, Protein varchar, IP ascii, StartTime bigint, ExecTime int, alignment map, " + + "(JobID ascii, Protein varchar, IP ascii, StartTime bigint, ExecTime int, FinalStatus 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));"); session.execute("CREATE INDEX IF NOT EXISTS ProteinSeq ON ProteinRow (protein);"); - session.execute("CREATE INDEX IF NOT EXISTS JobDateStamp ON ProteinData (jobtime);"); + 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() { diff --git a/datadb/compbio/cassandra/CassandraReader.java b/datadb/compbio/cassandra/CassandraReader.java index af697a0..941ad1c 100644 --- a/datadb/compbio/cassandra/CassandraReader.java +++ b/datadb/compbio/cassandra/CassandraReader.java @@ -127,7 +127,7 @@ public class CassandraReader { */ public Map ReadProteinSequenceByCounter() { final long startTime = System.currentTimeMillis(); - String com = "SELECT Protein FROM ProteinRow;"; + String com = "SELECT Protein, JobID FROM ProteinRow;"; System.out.println("Command: " + com); ResultSet results = session.execute(com); if (results.isExhausted()) @@ -140,6 +140,8 @@ public class CassandraReader { int c = 0; for (Row r : rows) { String protein = r.getString("Protein"); + String id = r.getString("JobID"); + System.out.println(id + ", " + protein); if (res.containsKey(protein)) res.put(protein, res.get(protein) + 1); else diff --git a/datadb/compbio/cassandra/CassandraRemover.java b/datadb/compbio/cassandra/CassandraRemover.java index d066d82..8404663 100644 --- a/datadb/compbio/cassandra/CassandraRemover.java +++ b/datadb/compbio/cassandra/CassandraRemover.java @@ -1,5 +1,11 @@ package compbio.cassandra; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; +import java.util.List; + import org.apache.log4j.Logger; import com.datastax.driver.core.ResultSet; @@ -8,6 +14,7 @@ import com.datastax.driver.core.Session; public class CassandraRemover { private Session session; + static SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy/MM/dd"); private static Logger log = Logger.getLogger(CassandraNativeConnector.class); public CassandraRemover() { @@ -21,31 +28,130 @@ public class CassandraRemover { } /* - * getting a record from CF for current jobId + * delete a record from CF for current jobId */ - public StructureJobLog ReadJobLog(String jobid) { - final long startTime = System.currentTimeMillis(); - String com = "SELECT Protein, StartTime FROM ProteinLog WHERE JobID = '" + jobid + "';"; + private void RemoveJob(String jobid, long date) { + String com0 = "DELETE FROM ProteinLog WHERE JobID = '" + jobid + "';"; + System.out.println("Command: " + com0); + session.execute(com0); + String com1 = "DELETE FROM ProteinRow WHERE JobID = '" + jobid + "';"; + System.out.println("Command: " + com1); + session.execute(com1); + String com2 = "DELETE FROM ProteinData WHERE JobID = '" + jobid + "' AND jobtime = " + date + ";"; + System.out.println("Command: " + com2); + session.execute(com2); + String com3 = "UPDATE jpredarchive SET finalstatus = 'DELETED' WHERE JobID = '" + jobid + "' ;"; + System.out.println("Command: " + com3); + session.execute(com3); + String com = "SELECT total FROM JobDateInfo WHERE jobday = " + date + ";"; + System.out.println("Command: " + com); + ResultSet results = session.execute(com); + long njobs = results.one().getLong("total"); + System.out.println("njobs: " + njobs); + String com4 = "INSERT INTO JobDateInfo " + "(jobday, Total)" + " VALUES (" + date + "," + (njobs -1) + ");"; + System.out.println("Command: " + com4); + session.execute(com4); + System.out.println("Remove jobs: " + jobid); + } + + public void RemoveJobById (String jobid) { + if (jobid == null) + return; + Long date = FindDate(jobid); + if (date == null) + return; + RemoveJob(jobid, date); + } + + public void RemoveJobByDate (String date1, String date2) { + System.out.println("Start " + date1 + ", " + date2); + if (date1 == null || date2 == null) + return; + Long dateBegin = convertDate(date1); + Long dateEnd = convertDate(date2); + System.out.println("Date to long done!: "); + if (dateBegin == null || dateEnd == null) + return; + Calendar start = Calendar.getInstance(); + start.setTime(new Date(dateBegin)); + Calendar end = Calendar.getInstance(); + end.setTime(new Date(dateEnd)); + System.out.println("Date to cal done!: "); + for (Date date = start.getTime(); !start.after(end); start.add(Calendar.DATE, 1), date = start.getTime()) { + String com = "SELECT JobID FROM ProteinData WHERE jobtime = " + date.getTime() + ";"; + System.out.println("Command: " + com); + ResultSet results = session.execute(com); + if (!results.isExhausted()) { + List rows = results.all(); + for (Row r : rows) { + String jobid = r.getString("JobID"); + if (jobid != null) + RemoveJob(jobid, date.getTime()); + } + } + } + } + + + public void RemoveJobByIp (String ip) { + if (ip == null) + return; + String com = "SELECT databegin, JobID FROM ProteinLog WHERE ip = '" + ip + "';"; + System.out.println("Command: " + com); + ResultSet results = session.execute(com); + if (!results.isExhausted()) { + List rows = results.all(); + for (Row r : rows) { + Long date = convertDate(r.getString("databegin")); + String jobid = r.getString("JobID"); + if (date == null || jobid == null) + continue; + RemoveJob(jobid, date); + } + } + } + + public void RemoveJobBySequence (String seq) { + if (seq == null) + return; + String com = "SELECT JobID FROM ProteinRow WHERE Protein = '" + seq + "';"; + System.out.println("Command: " + com); + ResultSet results = session.execute(com); + if (!results.isExhausted()) { + List rows = results.all(); + for (Row r : rows) { + String jobid = r.getString("JobID"); + if (jobid == null) + continue; + Long date = FindDate(jobid); + if (date == null) + continue; + RemoveJob(jobid, date); + } + } + } + + private Long FindDate(String jobid) { + String com = "SELECT databegin FROM ProteinLog WHERE JobID = '" + jobid + "';"; System.out.println("Command: " + com); ResultSet results = session.execute(com); if (results.isExhausted()) return null; - final long queryTime = System.currentTimeMillis(); - Row row = results.one(); - String com1 = "SELECT * FROM ProteinRow WHERE JobID = '" + jobid + "' ALLOW FILTERING;"; - System.out.println("Command: " + com1); - ResultSet results1 = session.execute(com1); - if (results1.isExhausted()) - return null; - Row row1 = results1.one(); - StructureJobLog res = new StructureJobLog(row.getString("Protein"), row.getString("JobID"), row.getString("DataBegin"), - row.getString("DataEnd"), row.getString("ip"), row1.getMap("Predictions", String.class, String.class)); - System.out.println("Query time is " + (queryTime - startTime) + " msec"); - final long endTime = System.currentTimeMillis(); - System.out.println(" rows analysed, execution time is " + (endTime - startTime) + " msec"); - return res; + Long date = convertDate(results.one().getString("databegin")); + return date; } + protected long convertDate (String d) { + try { + if (null != d) { + Date startdate = dateformatter.parse(d); + return startdate.getTime(); + } + } catch (ParseException e) { + e.printStackTrace(); + } + return 0L; + } } diff --git a/datadb/compbio/cassandra/CassandraWriter.java b/datadb/compbio/cassandra/CassandraWriter.java index 48bbda7..592016e 100644 --- a/datadb/compbio/cassandra/CassandraWriter.java +++ b/datadb/compbio/cassandra/CassandraWriter.java @@ -58,8 +58,8 @@ public class CassandraWriter { + "','" + execstatus + "','" + protein + "');"; session.execute(com1); - String com2 = "INSERT INTO ProteinData " + "(jobtime, JobID, Protein)" + " VALUES (" + job.getStartingDate() + ",'" + id - + "','" + protein + "');"; + String com2 = "INSERT INTO ProteinData " + "(jobtime, JobID, ExecTime, Protein)" + " VALUES (" + job.getStartingDate() + ",'" + id + + "'," + job.getExecutionTime() + ",'" + protein + "');"; session.execute(com2); String allpredictions = ""; @@ -120,8 +120,8 @@ 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,LOG, ArchiveLink) VALUES ('" + id + "','" - + job.getProtein() + "','" + job.getIP() + "'," + job.getStartingTime() + "," + job.getExecutionTime() + ",'" + log + 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 + "');"; session.execute(com); diff --git a/server/compbio/listeners/ServletDeleteRecord.java b/server/compbio/listeners/ServletDeleteRecord.java index 8ec6a1f..49107bc 100644 --- a/server/compbio/listeners/ServletDeleteRecord.java +++ b/server/compbio/listeners/ServletDeleteRecord.java @@ -8,6 +8,7 @@ import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; +import compbio.cassandra.CassandraRemover; import compbio.statistic.CassandraRequester; /** @@ -20,14 +21,24 @@ public class ServletDeleteRecord extends HttpServlet { * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { - CassandraRequester cr = new CassandraRequester(); + CassandraRemover cr = new CassandraRemover(); String flagId = request.getParameter("byId"); String jobId = request.getParameter("id"); + String flagDate = request.getParameter("byDate"); String date1 = request.getParameter("date1"); String date2 = request.getParameter("date2"); - if (flagId.equals("on")) - request.setAttribute("result", cr.countJobs(date1, date2)); - System.out.println(flagId); + String flagIp = request.getParameter("byIp"); + String ip = request.getParameter("ip"); + String flagSeq = request.getParameter("bySequence"); + String seq = request.getParameter("seq"); + if (flagId != null) + cr.RemoveJobById(jobId); + if (flagDate != null) + cr.RemoveJobByDate(date1, date2); + if (flagIp != null) + cr.RemoveJobByIp(ip); + if (flagSeq != null) + cr.RemoveJobBySequence(seq); // request.setAttribute("IdJob", id); // RequestDispatcher rd = request.getRequestDispatcher("/ReportLogInfo.jsp"); // rd.forward(request, response); diff --git a/website/Update.jsp b/website/Update.jsp index 844079b..c3f0227 100644 --- a/website/Update.jsp +++ b/website/Update.jsp @@ -24,8 +24,8 @@ function show(el, id) { records for the period of dates records with ip