From 9fe745e278ddb45f713ec29dbaa0b881e1420f48 Mon Sep 17 00:00:00 2001 From: Sasha Sherstnev Date: Mon, 3 Feb 2014 14:05:26 +0000 Subject: [PATCH] Simplify the jobdateinfo table (remove Total, Program, Version) --- .../cassandra/CassandraNativeConnector.java | 2 +- .../compbio/cassandra/CassandraNewTableWriter.java | 22 -------------------- datadb/compbio/cassandra/CassandraRemover.java | 14 ++++++------- datadb/compbio/cassandra/CassandraWriter.java | 5 ++--- .../cassandra/readers/DailyStatisticsReader.java | 9 +++++--- 5 files changed, 15 insertions(+), 37 deletions(-) diff --git a/datadb/compbio/cassandra/CassandraNativeConnector.java b/datadb/compbio/cassandra/CassandraNativeConnector.java index f02000c..6deed99 100644 --- a/datadb/compbio/cassandra/CassandraNativeConnector.java +++ b/datadb/compbio/cassandra/CassandraNativeConnector.java @@ -86,7 +86,7 @@ public class CassandraNativeConnector { + "predictions map, ArchiveLink varchar, LOG varchar, ProgramName ascii, ProgramVersion ascii, PRIMARY KEY(JobID));"); session.execute("CREATE TABLE IF NOT EXISTS JobDateInfo " - + "(jobday bigint, Total bigint, TotalOK bigint, TotalStopped bigint, TotalError bigint, TotalTimeOut bigint, Program varchar, Version varchar, PRIMARY KEY(jobday));"); + + "(jobday bigint, TotalOK bigint, TotalStopped bigint, TotalError bigint, TotalTimeOut bigint, PRIMARY KEY(jobday));"); session.execute("CREATE TABLE IF NOT EXISTS Programs " + "(Program varchar, Version varchar, Description varchar, weblink varchar, PRIMARY KEY(Program,Version));"); diff --git a/datadb/compbio/cassandra/CassandraNewTableWriter.java b/datadb/compbio/cassandra/CassandraNewTableWriter.java index 3b6c741..db0e70c 100644 --- a/datadb/compbio/cassandra/CassandraNewTableWriter.java +++ b/datadb/compbio/cassandra/CassandraNewTableWriter.java @@ -78,28 +78,6 @@ public class CassandraNewTableWriter { } /* - * fill new table - */ - public void FillNewTable() { - final long StartTime = System.currentTimeMillis(); - long erldate = CassandraNativeConnector.getEarliestDateInDB(); - Calendar runnicCal = Calendar.getInstance(); - runnicCal.setTime(new Date(erldate)); - Calendar endcal = Calendar.getInstance(); - long endTime = endcal.getTime().getTime(); - for (Date date = runnicCal.getTime(); date.getTime() < endTime; runnicCal.add(Calendar.DATE, 1), date = runnicCal.getTime()) { - try { - ResultSet results = session.execute("SELECT * FROM ProteinData WHERE jobtime = " + date.getTime() + ";"); - session.execute("INSERT INTO JobDateInfo " + "(jobday, Total)" + " VALUES (" + date.getTime() + "," + results.all().size() - + ");"); - } catch (QueryExecutionException e) { - e.printStackTrace(); - } - } - System.out.println("Table JobDateInfo filled: total time is " + (System.currentTimeMillis() - StartTime) + " msec"); - } - - /* * fill a table with the database global parameters */ public void FillMainDBParameters() { diff --git a/datadb/compbio/cassandra/CassandraRemover.java b/datadb/compbio/cassandra/CassandraRemover.java index d7aeafb..dbad018 100644 --- a/datadb/compbio/cassandra/CassandraRemover.java +++ b/datadb/compbio/cassandra/CassandraRemover.java @@ -60,7 +60,6 @@ public class CassandraRemover extends CassandraReader { return 0; } Row row = results.one(); - long njobs = row.getLong("Total") - 1; if (status.equals("OK")) { long njobsOK = row.getLong("TotalOK") - 1; String com4 = "DELETE FROM ProteinRow WHERE JobID = '" + jobid + "';"; @@ -70,20 +69,20 @@ public class CassandraRemover extends CassandraReader { String com5 = "DELETE FROM ProteinData WHERE JobID = '" + jobid + "' AND jobtime = " + date + ";"; System.out.println("Command: " + com5); CassandraQuery(com5); - UpdateJobDateInfo(date, "TotalOK", njobsOK, njobs); + UpdateJobDateInfo(date, "TotalOK", njobsOK); } else { String com6 = "DELETE FROM FailLog WHERE JobID = '" + jobid + "' AND jobtime = " + date + ";"; System.out.println("Command: " + com6); CassandraQuery(com6); if (status.equals("STOPPED")) { long njobsStopped = row.getLong("TotalStopped") - 1; - UpdateJobDateInfo(date, "TotalStopped", njobsStopped, njobs); + UpdateJobDateInfo(date, "TotalStopped", njobsStopped); } else if (status.equals("ERROR")) { long njobsError = row.getLong("TotalError") - 1; - UpdateJobDateInfo(date, "TotalError", njobsError, njobs); + UpdateJobDateInfo(date, "TotalError", njobsError); } else if (status.equals("TIMEDOUT")) { long njobsTimeOut = row.getLong("TotalTimeOut") - 1; - UpdateJobDateInfo(date, "TotalTimeOut", njobsTimeOut, njobs); + UpdateJobDateInfo(date, "TotalTimeOut", njobsTimeOut); } } System.out.println("Job " + jobid + " removed..."); @@ -99,9 +98,8 @@ public class CassandraRemover extends CassandraReader { * @return nothing * */ - private void UpdateJobDateInfo(long date, String ColumnName, long totalcol, long total) { - String com = "UPDATE JobDateInfo SET " + ColumnName + " = " + totalcol + ", Total = " + total + " WHERE jobday = " + date + ";"; - System.out.println("Command: " + com); + private void UpdateJobDateInfo(long date, String ColumnName, long totalcol) { + String com = "UPDATE JobDateInfo SET " + ColumnName + " = " + totalcol + " WHERE jobday = " + date + ";"; CassandraQuery(com); } diff --git a/datadb/compbio/cassandra/CassandraWriter.java b/datadb/compbio/cassandra/CassandraWriter.java index b12f59e..8c1bc9d 100644 --- a/datadb/compbio/cassandra/CassandraWriter.java +++ b/datadb/compbio/cassandra/CassandraWriter.java @@ -181,9 +181,8 @@ public class CassandraWriter { njobsStop += r.getLong("TotalStopped"); njobsTimeOut += r.getLong("TotalTimeOut"); } - String com = "INSERT INTO JobDateInfo " + "(jobday, Total, TotalOK, TotalStopped, TotalError, TotalTimeOut)" + " VALUES (" - + job.getStartingDate() + "," + njobsTotal + "," + njobsOk + "," + njobsStop + "," + njobsError + "," + njobsTimeOut - + ");"; + String com = "INSERT INTO JobDateInfo " + "(jobday, TotalOK, TotalStopped, TotalError, TotalTimeOut)" + " VALUES (" + + job.getStartingDate() + "," + njobsOk + "," + njobsStop + "," + njobsError + "," + njobsTimeOut + ");"; if (null == execute(com)) { System.out.println("CassandraWriter.FormQueryTables: couldn't insert into JobDateInfo"); // return 0; diff --git a/datadb/compbio/cassandra/readers/DailyStatisticsReader.java b/datadb/compbio/cassandra/readers/DailyStatisticsReader.java index 2a41621..123af43 100644 --- a/datadb/compbio/cassandra/readers/DailyStatisticsReader.java +++ b/datadb/compbio/cassandra/readers/DailyStatisticsReader.java @@ -43,9 +43,12 @@ public class DailyStatisticsReader extends CassandraReader { return null; if (results.isExhausted()) continue; - Row therow = results.one(); - Total res = new Total(therow.getLong("Total"), therow.getLong("TotalOK"), therow.getLong("TotalStopped"), - therow.getLong("TotalError"), therow.getLong("TotalTimeOut")); + Row row = results.one(); + long nOK = row.getLong("TotalOK"); + long nStopped = row.getLong("TotalStopped"); + long nError = row.getLong("TotalError"); + long nTimeOut = row.getLong("TotalTimeOut"); + Total res = new Total(nOK + nStopped + nError + nTimeOut, nOK, nStopped, nError, nTimeOut); if (!results.isExhausted()) { Date dat = new Date(date.getTime()); log.warn("CassandraReader.ReadDateTable: date row for " + dat.toString() + " (" + date.getTime() + ") duplicated "); -- 1.7.10.2