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 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) (limited to 'wqflask/base') 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: -- cgit v1.2.3 From ee84f921331e7e68df0d57a1bc285b5f91780135 Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 13:09:38 +0300 Subject: replace multiple tissue queries with a single one --- wqflask/base/data_set.py | 28 ++++++++++++++++------------ 1 file changed, 16 insertions(+), 12 deletions(-) (limited to 'wqflask/base') diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index 40c38fe8..f5bd5cb8 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -29,6 +29,7 @@ import json import gzip import cPickle as pickle import itertools +from operator import itemgetter from redis import Redis Redis = Redis() @@ -335,18 +336,21 @@ class DatasetGroup(object): 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)) + results = g.db.execute(''' + SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name + FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue + WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and + ProbeFreeze.TissueId = Tissue.Id and + ProbeSetFreeze.public > %s and + ProbeFreeze.InbredSetId = InbredSet.Id and + InbredSet.Name like %s + ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId + ''', (webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) + + for tissue_name, dataset in itertools.groupby(results.fetchall(), itemgetter(0)): + dataset_sub_menu = [item[1:] for item in dataset] + 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) -- cgit v1.2.3 From 73be62c9e5ef5c4b3a87d3fcdc46f338679bf5e1 Mon Sep 17 00:00:00 2001 From: Artem Tarasov Date: Wed, 13 May 2015 14:16:14 +0300 Subject: combine three similar queries into one --- wqflask/base/data_set.py | 59 ++++++++++++++++++++++++------------------------ 1 file changed, 30 insertions(+), 29 deletions(-) (limited to 'wqflask/base') diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index f5bd5cb8..706b193d 100755 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -319,38 +319,39 @@ class DatasetGroup(object): 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. results = g.db.execute(''' - SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name - FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue - WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and - ProbeFreeze.TissueId = Tissue.Id and - ProbeSetFreeze.public > %s and - ProbeFreeze.InbredSetId = InbredSet.Id and - InbredSet.Name like %s - ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId - ''', (webqtlConfig.PUBLICTHRESH, "%" + self.name + "%")) + (SELECT '#PublishFreeze',PublishFreeze.FullName,PublishFreeze.Name + FROM PublishFreeze,InbredSet + WHERE PublishFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name = %s + and PublishFreeze.public > %s) + UNION + (SELECT '#GenoFreeze',GenoFreeze.FullName,GenoFreeze.Name + FROM GenoFreeze, InbredSet + WHERE GenoFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name = %s + and GenoFreeze.public > %s) + UNION + (SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name + FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue + WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id + and ProbeFreeze.TissueId = Tissue.Id + and ProbeFreeze.InbredSetId = InbredSet.Id + and InbredSet.Name like %s + and ProbeSetFreeze.public > %s + ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId) + ''', (self.name, webqtlConfig.PUBLICTHRESH, + self.name, webqtlConfig.PUBLICTHRESH, + "%" + self.name + "%", webqtlConfig.PUBLICTHRESH)) for tissue_name, dataset in itertools.groupby(results.fetchall(), itemgetter(0)): - dataset_sub_menu = [item[1:] for item in dataset] - dataset_menu.append(dict(tissue=tissue_name, - datasets=dataset_sub_menu)) + if tissue_name in ['#PublishFreeze', '#GenoFreeze']: + for item in dataset: + dataset_menu.append(dict(tissue=None, datasets=[item[1:]])) + else: + dataset_sub_menu = [item[1:] for item in dataset] + 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) -- cgit v1.2.3