datadb/compbio/cassandra/CassandraWriter.java

   1 package compbio.cassandra;
   2
   3 import java.util.List;
   4
   5 import org.apache.log4j.Logger;
   6
   7 import com.datastax.driver.core.Row;
   8 import com.datastax.driver.core.Session;
   9 import com.datastax.driver.core.ResultSet;
  10 import com.datastax.driver.core.exceptions.QueryExecutionException;
  11 import com.datastax.driver.core.exceptions.QueryValidationException;
  12
  13 import compbio.data.sequence.FastaSequence;
  14 import compbio.engine.JpredJob;
  15 import compbio.engine.ProteoCachePropertyHelperManager;
  16 import compbio.util.PropertyHelper;
  17
  18 public class CassandraWriter {
  19         private Session session;
  20         private static final PropertyHelper ph = ProteoCachePropertyHelperManager.getPropertyHelper();
  21         private static Logger log = Logger.getLogger(CassandraNativeConnector.class);
  22
  23         CassandraWriter() {
  24                 Session inis = CassandraNativeConnector.getSession();
  25                 setSession(inis);
  26         }
  27
  28         public void setSession(Session s) {
  29                 assert s != null;
  30                 session = s;
  31         }
  32
  33         private ResultSet execute(String command) {
  34                 try {
  35                         ResultSet results = session.execute(command);
  36                         return results;
  37                 } catch (QueryExecutionException e) {
  38                         String mess = "CassandraWriter: query execution exception...";
  39                         System.out.println(mess);
  40                         log.error(mess);
  41                         log.error(e.getLocalizedMessage(), e.getCause());
  42                         return null;
  43                 } catch (QueryValidationException e) {
  44                         String mess = "CassandraWriter: query validation exception... Command: " + command;
  45                         System.out.println(mess);
  46                         log.error(mess);
  47                         log.error(e.getLocalizedMessage(), e.getCause());
  48                         return null;
  49                 }
  50         }
  51
  52         public boolean JobisNotInsterted(String jobid) {
  53                 ResultSet results = execute("SELECT * FROM ProteinLog WHERE JobID = '" + jobid + "';");
  54                 if (null != results && results.isExhausted()) {
  55                         return true;
  56                 }
  57                 return false;
  58         }
  59
  60         public boolean JobisNotArchived(String jobid) {
  61                 ResultSet results = execute("SELECT * FROM JpredArchive WHERE JobID = '" + jobid + "';");
  62                 if (null != results && results.isExhausted()) {
  63                         return true;
  64                 }
  65                 return false;
  66         }
  67
  68         /*
  69          * inserting data into the tables for queries
  70          */
  71         public int FormQueryTables(JpredJob job) {
  72                 if (JobisNotInsterted(job.getJobID())) {
  73                         String id = job.getJobID();
  74                         String protein = job.getProtein();
  75                         String finalstatus = job.getFinalStatus();
  76                         String execstatus = job.getExecutionStatus();
  77                         String ProgramName = job.getProgramName();
  78                         String ProgramVersion = job.getProgramVersion();
  79
  80                         String com1 = "INSERT INTO ProteinLog (JobID, IP, DataBegin, DataEnd, FinalStatus, ExecutionStatus, Protein, ProgramName, ProgramVersion) VALUES ('"
  81                                         + id
  82                                         + "','"
  83                                         + job.getIP()
  84                                         + "','"
  85                                         + job.getStartingTimeStr()
  86                                         + "','"
  87                                         + job.getEndTimeStr()
  88                                         + "','"
  89                                         + finalstatus
  90                                         + "','" + execstatus + "','" + protein + "','" + ProgramName + "','" + ProgramVersion + "');";
  91                         ResultSet insert = execute(com1);
  92                         if (null == insert) {
  93                                 System.out.println("CassandraWriter.FormQueryTables: couldn't insert into ProteinLog");
  94                                 // return 0;
  95                         }
  96                         if (finalstatus.equals("OK")) {
  97                                 String com2 = "INSERT INTO ProteinData " + "(jobtime, JobID, ExecTime, Protein)" + " VALUES (" + job.getStartingDate()
  98                                                 + ",'" + id + "'," + job.getExecutionTime() + ",'" + protein + "');";
  99                                 if (null == execute(com2)) {
 100                                         System.out.println("CassandraWriter.FormQueryTables: couldn't insert into ProteinData");
 101                                         // return 0;
 102                                 }
 103
 104                                 String allpredictions = "";
 105                                 List<FastaSequence> pr = job.getPredictions();
 106                                 for (FastaSequence pred : pr) {
 107                                         String predictionname = pred.getId();
 108                                         String prediction = pred.getSequence().replaceAll("\n", "");
 109                                         allpredictions += "'" + predictionname + "':'" + prediction + "',";
 110                                 }
 111                                 String final_prediction = "";
 112                                 if (!allpredictions.equals("")) {
 113                                         final_prediction = allpredictions.substring(0, allpredictions.length() - 1);
 114                                 }
 115
 116                                 ResultSet results2 = execute("SELECT * FROM ProteinRow WHERE JobID = '" + job.getJobID() + "';");
 117                                 if (null != results2 && results2.isExhausted()) {
 118                                         String com3 = "INSERT INTO ProteinRow (Protein, JobID, Predictions) VALUES ('" + protein + "','" + id + "',{"
 119                                                         + final_prediction + "});";
 120                                         if (null == execute(com3)) {
 121                                                 System.out.println("CassandraWriter.FormQueryTables: couldn't insert into ProteinRow");
 122                                                 return 0;
 123                                         }
 124                                 }
 125                         } else {
 126                                 String com5 = "INSERT INTO FailLog (jobtime, JobID, ExecTime, ip, FinalStatus) VALUES (" + job.getStartingDate() + ",'"
 127                                                 + id + "'," + job.getExecutionTime() + ",'" + job.getIP() + "', '" + finalstatus + "');";
 128                                 if (null == execute(com5)) {
 129                                         System.out.println("CassandraWriter.FormQueryTables: couldn't insert into FailLog");
 130                                         return 0;
 131                                 }
 132                         }
 133
 134                         // update Main parameters if the job is the earliest job so far
 135                         ResultSet results3 = execute("SELECT * FROM MainParameters WHERE Name = 'EarliestJobDate';");
 136                         if (null == results3) {
 137                                 System.out.println("CassandraWriter.FormQueryTables: couldn't get results from MainParameters");
 138                                 // return 0;
 139                         }
 140                         boolean updateparameter = true;
 141                         if (!results3.isExhausted()) {
 142                                 Row r = results3.one();
 143                                 if (job.getStartingDate() >= Long.parseLong(r.getString("Value")))
 144                                         updateparameter = false;
 145                         }
 146                         if (updateparameter) {
 147                                 String com6 = "INSERT INTO MainParameters (Name, Value) VALUES ('EarliestJobDate','" + job.getStartingDate() + "');";
 148                                 if (null == execute(com6)) {
 149                                         System.out.println("CassandraWriter.FormQueryTables: couldn't insert into MainParameters");
 150                                         return 0;
 151                                 }
 152                         }
 153
 154                         // update internal job counts (used by the Daily Statistics
 155                         // requests)
 156                         // TODO I don't like the bit of code. There should not be so many
 157                         // counters...
 158                         int njobsTotal = 1;
 159                         int njobsOk = 0;
 160                         int njobsStop = 0;
 161                         int njobsError = 0;
 162                         int njobsTimeOut = 0;
 163                         if (finalstatus.equals("OK"))
 164                                 njobsOk = 1;
 165                         else if (finalstatus.equals("TIMEDOUT"))
 166                                 njobsTimeOut = 1;
 167                         else if (finalstatus.equals("JPREDERROR"))
 168                                 njobsError = 1;
 169                         else if (finalstatus.equals("STOPPED"))
 170                                 njobsStop = 1;
 171                         ResultSet results4 = execute("SELECT * FROM JobDateInfo WHERE jobday = " + job.getStartingDate() + ";");
 172                         if (null == results4) {
 173                                 System.out.println("CassandraWriter.FormQueryTables: couldn't get data from JobDateInfo");
 174                                 // return 0;
 175                         }
 176                         if (!results4.isExhausted()) {
 177                                 Row r = results4.one();
 178                                 njobsTotal += r.getLong("Total");
 179                                 njobsOk += r.getLong("TotalOK");
 180                                 njobsError += r.getLong("TotalError");
 181                                 njobsStop += r.getLong("TotalStopped");
 182                                 njobsTimeOut += r.getLong("TotalTimeOut");
 183                         }
 184                         String com = "INSERT INTO JobDateInfo " + "(jobday, Total, TotalOK, TotalStopped, TotalError, TotalTimeOut)" + " VALUES ("
 185                                         + job.getStartingDate() + "," + njobsTotal + "," + njobsOk + "," + njobsStop + "," + njobsError + "," + njobsTimeOut
 186                                         + ");";
 187                         if (null == execute(com)) {
 188                                 System.out.println("CassandraWriter.FormQueryTables: couldn't insert into JobDateInfo");
 189                                 // return 0;
 190                         }
 191                         return 1;
 192                 }
 193                 return 0;
 194         }
 195
 196         /*
 197          * insert data from a real Jpred job: timing+IP, Execution Status, Final
 198          * status, protein sequence, predictions, alignment, LOG and tar.gz files
 199          */
 200         public int ArchiveData(JpredJob job, String archivepath) {
 201                 if (JobisNotArchived(job.getJobID())) {
 202                         String id = job.getJobID();
 203                         String log = job.getLog().replaceAll("'", "");
 204                         String com = "INSERT INTO JpredArchive (JobID, Protein, IP, StartTime, ExecTime, FinalStatus, ExecutionStatus, LOG, ArchiveLink, ProgramName, ProgramVersion) VALUES ('"
 205                                         + id
 206                                         + "','"
 207                                         + job.getProtein()
 208                                         + "','"
 209                                         + job.getIP()
 210                                         + "',"
 211                                         + job.getStartingTime()
 212                                         + ","
 213                                         + job.getExecutionTime()
 214                                         + ",'"
 215                                         + job.getFinalStatus()
 216                                         + "','"
 217                                         + job.getExecutionStatus()
 218                                         + "','"
 219                                         + log
 220                                         + "','"
 221                                         + archivepath
 222                                         + "','"
 223                                         + job.getProgramName() + "','" + job.getProgramVersion() + "');";
 224                         if (null == execute(com)) {
 225                                 System.out.println("CassandraWriter.ArchiveData: couldn't insert into JpredArchive");
 226                         }
 227                         List<FastaSequence> predictions = job.getPredictions();
 228                         for (FastaSequence p : predictions) {
 229                                 if (null == execute("UPDATE JpredArchive SET predictions = predictions + {'" + p.getId() + "':'"
 230                                                 + p.getSequence().replaceAll("\n", "") + "'} WHERE JobID = '" + id + "';")) {
 231                                         System.out.println("CassandraWriter.ArchiveData: couldn't update data in JpredArchive");
 232                                 }
 233                         }
 234
 235                         List<FastaSequence> seqs = job.getAlignment();
 236
 237                         for (FastaSequence s : seqs) {
 238                                 String com2 = "UPDATE JpredArchive SET alignment = alignment + {'" + s.getId() + "':'"
 239                                                 + s.getSequence().replaceAll("\n", "") + "'} WHERE JobID = '" + id + "';";
 240                                 if (null == execute(com2)) {
 241                                         System.out.println("CassandraWriter.ArchiveData: couldn't update data in JpredArchive");
 242                                 }
 243                         }
 244                         return 1;
 245                 }
 246                 return 0;
 247         }
 248
 249 }