From ae38ab4dc7b6e22731c1d57765605304d36ccfa2 Mon Sep 17 00:00:00 2001 From: Sasha Sherstnev Date: Mon, 11 Nov 2013 16:58:32 +0000 Subject: [PATCH] Rebuild tables for queries --- conf/Proteocache.properties | 4 ++- .../compbio/cassandra/CassandraNewTableWriter.java | 23 +++++++++++------ datadb/compbio/cassandra/CassandraReader.java | 27 +++++++++----------- server/compbio/listeners/ContextListener.java | 19 +++++++++++--- server/compbio/statistic/CassandraRequester.java | 16 +++++------- 5 files changed, 52 insertions(+), 37 deletions(-) diff --git a/conf/Proteocache.properties b/conf/Proteocache.properties index 35cfe56..ffd1a6c 100644 --- a/conf/Proteocache.properties +++ b/conf/Proteocache.properties @@ -3,10 +3,12 @@ # test server is 10.0.115.190 cassandra.host=localhost +cassandra.newtables.update=false; + ################################################################################# # Jpred sources # real Jpred web-server -cassandra.jpred.web.update=true +cassandra.jpred.web.update=false cassandra.jpred.web.inidelay=0 cassandra.jpred.web.updaterate=200 diff --git a/datadb/compbio/cassandra/CassandraNewTableWriter.java b/datadb/compbio/cassandra/CassandraNewTableWriter.java index 1832750..88a4ca2 100644 --- a/datadb/compbio/cassandra/CassandraNewTableWriter.java +++ b/datadb/compbio/cassandra/CassandraNewTableWriter.java @@ -2,7 +2,6 @@ package compbio.cassandra; import java.util.Calendar; import java.util.Date; -import java.util.List; import org.apache.log4j.Logger; @@ -11,10 +10,10 @@ import com.datastax.driver.core.Session; import com.datastax.driver.core.ResultSet; import com.datastax.driver.core.PreparedStatement; import com.datastax.driver.core.BoundStatement; +import com.datastax.driver.core.exceptions.QueryExecutionException; import compbio.engine.ProteoCachePropertyHelperManager; import compbio.cassandra.CassandraNativeConnector; -import compbio.util.PropertyHelper; public class CassandraNewTableWriter { private Session session; @@ -56,18 +55,26 @@ public class CassandraNewTableWriter { Calendar endcal = Calendar.getInstance(); Date end = endcal.getTime(); for (Date date = start.getTime(); !start.after(end); start.add(Calendar.DATE, 1), date = start.getTime()) { + final long startTime = System.currentTimeMillis(); String query1 = "SELECT * FROM ProteinData WHERE jobtime = " + date.getTime() + ";"; System.out.println("Query db: " + query1); - ResultSet results = session.execute(query1); - String query2 = "INSERT INTO JobDateInfo " + "(jobday, Total)" + " VALUES (" + date.getTime() + "," + results.all().size()+ ");"; - System.out.println("Insert DB: " + query2); - session.execute(query2); + try { + ResultSet results = session.execute(query1); + final long queryTime = System.currentTimeMillis(); + System.out.println("Query time for " + date.toString() + " is " + (queryTime - startTime) + " msec"); + String query2 = "INSERT INTO JobDateInfo " + "(jobday, Total)" + " VALUES (" + date.getTime() + "," + results.all().size() + + ");"; + System.out.println("Insert DB: " + query2); + session.execute(query2); + } catch (QueryExecutionException e) { + e.printStackTrace(); + } } System.out.println("Table JobDateInfo filled"); } - + /* - * fill new table + * fill a table with the database global parameters */ public void FillParameters() { Date bubu = new Date(CassandraNativeConnector.getEarliestDateInDB()); diff --git a/datadb/compbio/cassandra/CassandraReader.java b/datadb/compbio/cassandra/CassandraReader.java index c71243b..c7d08bf 100644 --- a/datadb/compbio/cassandra/CassandraReader.java +++ b/datadb/compbio/cassandra/CassandraReader.java @@ -1,6 +1,6 @@ package compbio.cassandra; -import java.util.Calendar; +import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.ArrayList; @@ -54,23 +54,20 @@ public class CassandraReader { } /* - * getting data from the db ProteinData + * getting data from the db JobDateInfo */ - public Integer ReadDateTable(long queryDate) { - final long startTime = System.currentTimeMillis(); - String com = "SELECT jobtime, JobID FROM ProteinData WHERE jobtime = " + queryDate + ";"; - System.out.println("Command: " + com); - ResultSet results = session.execute(com); - final long queryTime = System.currentTimeMillis(); - System.out.println("Query time is " + (queryTime - startTime) + " msec"); + public long ReadDateTable(long queryDate) { + ResultSet results = session.execute("SELECT Total FROM JobDateInfo WHERE jobday = " + queryDate + ";"); if (results.isExhausted()) return 0; - List rows = results.all(); - final long endTime = System.currentTimeMillis(); - System.out.println("Processing time is " + (endTime - queryTime) + " msec"); - return rows.size(); + Row therow = results.one(); + long res = therow.getLong("Total"); + if (!results.isExhausted()) { + Date date = new Date (queryDate); + log.warn("CassandraReader.ReadDateTable: date row for " + date.toString () + " ("+ queryDate + ") duplicated "); + } + return res; } - /* * getting whole protein sequence from the db ProteinRow */ @@ -131,7 +128,7 @@ public class CassandraReader { /* * getting protein sequences by counter */ - public Map ReadProteinDataByCounter() { + public Map ReadProteinSequenceByCounter() { final long startTime = System.currentTimeMillis(); String com = "SELECT Protein FROM ProteinRow;"; System.out.println("Command: " + com); diff --git a/server/compbio/listeners/ContextListener.java b/server/compbio/listeners/ContextListener.java index 5b05a8b..a1ff9cb 100644 --- a/server/compbio/listeners/ContextListener.java +++ b/server/compbio/listeners/ContextListener.java @@ -29,11 +29,13 @@ import compbio.util.Util; public class ContextListener implements ServletContextListener { private ScheduledExecutorService webjob_scheduler; private ScheduledExecutorService localjob_scheduler; + private ScheduledExecutorService update_scheduler; CassandraNativeConnector db = new CassandraNativeConnector(); static PropertyHelper ph = ProteoCachePropertyHelperManager.getPropertyHelper(); private static Logger log = Logger.getLogger(ContextListener.class); public static boolean READ_WEB_JPRED = false; public static boolean READ_LOCALFILE_JPRED = false; + public static boolean FILL_NEWTABLE = false; private static boolean initBooleanValue(String key) { assert key != null; @@ -52,13 +54,21 @@ public class ContextListener implements ServletContextListener { System.out.println("ProteoCache session start......"); // connect to the db and create table if needed db.Connect(); - CassandraNewTableWriter updater = new CassandraNewTableWriter(); - - // updater.FillParameters(); - // updater.FillNewTable(); + final CassandraNewTableWriter updater = new CassandraNewTableWriter(); READ_WEB_JPRED = initBooleanValue("cassandra.jpred.web.update"); READ_LOCALFILE_JPRED = initBooleanValue("cassandra.jpred.local.update"); + FILL_NEWTABLE = initBooleanValue("cassandra.newtables.update"); + + if (FILL_NEWTABLE) { + update_scheduler = Executors.newSingleThreadScheduledExecutor(); + update_scheduler.schedule(new Runnable() { + @Override + public void run() { + updater.FillNewTable(); + } + }, 10, TimeUnit.SECONDS); + } if (READ_WEB_JPRED) { // get data from real Jpred production server @@ -161,6 +171,7 @@ public class ContextListener implements ServletContextListener { if (READ_LOCALFILE_JPRED) { localjob_scheduler.shutdownNow(); } + update_scheduler.shutdownNow(); } } diff --git a/server/compbio/statistic/CassandraRequester.java b/server/compbio/statistic/CassandraRequester.java index 4bb1966..2da38e7 100755 --- a/server/compbio/statistic/CassandraRequester.java +++ b/server/compbio/statistic/CassandraRequester.java @@ -141,11 +141,9 @@ 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()) { - Integer res = db.ReadDateTable(date.getTime()); - if (res == null) - continue; + long res = db.ReadDateTable(date.getTime()); DataBase db = new DataBase(); - db.setTotal(res); + db.setTotal((int)res); db.setDate(DateFormat(date.getTime())); query.add(db); } @@ -180,13 +178,13 @@ public class CassandraRequester { } /* - * query protein sequences with number of jobs - * */ - public List readProteinByCounter(int counter) { + * query protein sequences with number of jobs + */ + public List readProteinByCounter(int minimalcounter) { query = new ArrayList(); - Map map = db.ReadProteinDataByCounter(); + Map map = db.ReadProteinSequenceByCounter(); for (Map.Entry entry : map.entrySet()) { - if (entry.getValue() > counter) { + if (entry.getValue() > minimalcounter) { DataBase db = new DataBase(); db.setTotalId(entry.getValue()); db.setProt(entry.getKey()); -- 1.7.10.2