From d9f617afba9c3881f273902447aef94755a6deee Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 11:13:05 +0300 Subject: cache datasets for each group --- wqflask/base/data_set.py | 47 ++++++++++++++++++++++++++++++++ wqflask/wqflask/database.py | 7 ++++- wqflask/wqflask/show_trait/show_trait.py | 35 +----------------------- 3 files changed, 54 insertions(+), 35 deletions(-) diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index 1cd57b4b..40c38fe8 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -292,6 +292,7 @@ class DatasetGroup(object): self.incparentsf1 = False self.allsamples = None + self._datasets = None def get_specified_markers(self, markers = []): self.markers = HumanMarkers(self.name, markers) @@ -305,6 +306,52 @@ class DatasetGroup(object): self.markers = marker_class(self.name) + def datasets(self): + key = "group_dataset_menu:v1:" + self.name + print("key is:", key) + with Bench("Loading cache"): + result = Redis.get(key) + if result: + self._datasets = pickle.loads(result) + return self._datasets + + dataset_menu = [] + print("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH) + print("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH)) + results = g.db.execute("""SELECT PublishFreeze.FullName,PublishFreeze.Name FROM + PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name = %s and PublishFreeze.public > %s""", + (self.name, webqtlConfig.PUBLICTHRESH)) + for item in results.fetchall(): + dataset_menu.append(dict(tissue=None, + datasets=[item])) + + results = g.db.execute("""SELECT GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze, + InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id and InbredSet.Name = + %s and GenoFreeze.public > %s""", + (self.name, webqtlConfig.PUBLICTHRESH)) + for item in results.fetchall(): + dataset_menu.append(dict(tissue=None, + datasets=[item])) + + #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob. + tissues = g.db.execute("SELECT Id, Name FROM Tissue order by Name") + for item in tissues.fetchall(): + tissue_id, tissue_name = item + data_sets = g.db.execute('''SELECT ProbeSetFreeze.FullName,ProbeSetFreeze.Name FROM ProbeSetFreeze, ProbeFreeze, + InbredSet WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and ProbeFreeze.TissueId = %s and + ProbeSetFreeze.public > %s and ProbeFreeze.InbredSetId = InbredSet.Id and InbredSet.Name like %s + order by ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId ''', + (tissue_id, webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) + dataset_sub_menu = [item for item in data_sets.fetchall() if item] + if dataset_sub_menu: + dataset_menu.append(dict(tissue=tissue_name, + datasets=dataset_sub_menu)) + + Redis.set(key, pickle.dumps(dataset_menu, pickle.HIGHEST_PROTOCOL)) + Redis.expire(key, 60*5) + self._datasets = dataset_menu + return self._datasets def get_f1_parent_strains(self): try: diff --git a/wqflask/wqflask/database.py b/wqflask/wqflask/database.py index e55f06a7..16013a0c 100755 --- a/wqflask/wqflask/database.py +++ b/wqflask/wqflask/database.py @@ -13,6 +13,11 @@ db_session = scoped_session(sessionmaker(autocommit=False, Base = declarative_base() Base.query = db_session.query_property() +import logging + +logging.basicConfig() +logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) + def init_db(): # import all modules here that might define models so that # they will be registered properly on the metadata. Otherwise @@ -23,4 +28,4 @@ def init_db(): Base.metadata.create_all(bind=engine) print("Done creating all...") -init_db() \ No newline at end of file +init_db() diff --git a/wqflask/wqflask/show_trait/show_trait.py b/wqflask/wqflask/show_trait/show_trait.py index b28c9b76..9bce69d9 100755 --- a/wqflask/wqflask/show_trait/show_trait.py +++ b/wqflask/wqflask/show_trait/show_trait.py @@ -888,40 +888,7 @@ class ShowTrait(object): this_group = 'BXD' if this_group: - - dataset_menu = [] - print("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH) - print("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH)) - results = g.db.execute("""SELECT PublishFreeze.FullName,PublishFreeze.Name FROM - PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id - and InbredSet.Name = %s and PublishFreeze.public > %s""", - (this_group, webqtlConfig.PUBLICTHRESH)) - for item in results.fetchall(): - dataset_menu.append(dict(tissue=None, - datasets=[item])) - - results = g.db.execute("""SELECT GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze, - InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id and InbredSet.Name = - %s and GenoFreeze.public > %s""", - (this_group, webqtlConfig.PUBLICTHRESH)) - for item in results.fetchall(): - dataset_menu.append(dict(tissue=None, - datasets=[item])) - - #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob. - tissues = g.db.execute("SELECT Id, Name FROM Tissue order by Name") - for item in tissues.fetchall(): - tissue_id, tissue_name = item - data_sets = g.db.execute('''SELECT ProbeSetFreeze.FullName,ProbeSetFreeze.Name FROM ProbeSetFreeze, ProbeFreeze, - InbredSet WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and ProbeFreeze.TissueId = %s and - ProbeSetFreeze.public > %s and ProbeFreeze.InbredSetId = InbredSet.Id and InbredSet.Name like %s - order by ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId ''', - (tissue_id, webqtlConfig.PUBLICTHRESH, "%" + this_group + "%")) - dataset_sub_menu = [item for item in data_sets.fetchall() if item] - if dataset_sub_menu: - dataset_menu.append(dict(tissue=tissue_name, - datasets=dataset_sub_menu)) - + dataset_menu = self.dataset.group.datasets() dataset_menu_selected = None if len(dataset_menu): if this_trait and this_trait.dataset: -- cgit v1.2.3