def update_selection(self, analysis_id, is_selected, variant_ids):
"""
Add or remove variant/trx from the selection of the analysis
"""
analysis = Analysis.from_id(analysis_id)
if not isinstance(
variant_ids,
list) or not analysis or not analysis.status == 'ready':
return False
query = ""
# Update variant selection in working table
for vid in variant_ids:
ids = vid.split("_")
if len(ids) == 1:
query += "UPDATE wt_{} SET is_selected={} WHERE variant_id={}; ".format(
analysis.id, is_selected, vid)
else:
query += "UPDATE wt_{} SET is_selected={} WHERE variant_id={} AND trx_pk_value='{}'; ".format(
analysis.id, is_selected, ids[0], ids[1])
execute(query)
# Upate global selection information in analysis table
result = []
for row in execute(
"SELECT variant_id, trx_pk_value FROM wt_{} WHERE is_selected".
format(analysis_id)):
result.append("{}_{}".format(row.variant_id, row.trx_pk_value))
execute("UPDATE analysis SET selection='{}' WHERE id={}".format(
json.dumps(result), analysis_id))
return True
def delete(self, job_id, author_id=None, definitely=False):
"""
Delete a Job.
When normal user delete a job, this one is put in the trash project
Then an admin can definitely delete the job (with the flag finally set to True)
"""
from core.core import core
job = Job.from_id(job_id, 1)
if not job:
raise RegovarException("Job not found (id={}).".format(job_id))
if definitely:
# Kill job and remove DB entries
self.__finalize_job(job.id)
# TODO
#sql = "DELETE FROM job WHERE id={0}; DELETE FROM job_file WHERE analysis_id={0};".format(analysis_id)
#sql+= "DELETE FROM analysis_sample WHERE analysis_id={0}; DELETE FROM attribute WHERE analysis_id={0}".format(analysis_id)
#sql+= "DELETE FROM analysis_indicator_value WHERE analysis_id={0};".format(analysis_id)
#core.events.log(author_id, "warning", {"analysis_id": analysis.id}, "Irreversible deletion of the analysis: {}".format(analysis.name))
## Deleting file in the filesystem
#shutil.rmtree(job.path, True)
else:
# move job to trash
sql = "UPDATE job SET project_id=0 WHERE id={0}; ".format(job_id)
core.events.log(author_id, "info", {"job_id": job_id}, "Pipeline analysis moved to trash: {}".format(job.name))
execute(sql)
return job
def delete(self, analysis_id, author_id=None, definitely=False):
"""
Delete the analysis
When normal user delete an analysis, this one is put in the trash project
Then an admin can definitely delete the analysis (with the flag finally set to True)
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis: raise RegovarException("Unable to find analysis with the provided id {}".format(analysis_id))
if definitely:
self.clear_temps_data(analysis_id)
# Delete related files
# TODO
sql = "DELETE FROM analysis WHERE id={0}; DELETE FROM filter WHERE analysis_id={0};".format(analysis_id)
sql+= "DELETE FROM analysis_sample WHERE analysis_id={0}; DELETE FROM attribute WHERE analysis_id={0}".format(analysis_id)
sql+= "DELETE FROM analysis_indicator_value WHERE analysis_id={0};".format(analysis_id)
core.events.log(author_id, "warning", {"analysis_id": analysis.id}, "Irreversible deletion of the analysis: {}".format(analysis.name))
else:
sql = "UPDATE analysis SET project_id=0 WHERE id={0}; ".format(analysis_id)
core.events.log(author_id, "info", {"analysis_id": analysis.id}, "Analysis moved to trash: {}".format(analysis.name))
result = analysis.to_json()
execute(sql)
return result
def _get_disease(self, disease_id):
"""
Internal method, called by get(token) to retrieve generic disease data from a provided hpo id
"""
sql = "SELECT hpo_id, label, genes, phenotypes, phenotypes_neg, meta FROM hpo_disease WHERE hpo_id='{}'".format(disease_id)
row = execute(sql).first()
result = {
"id": disease_id,
"type" : "disease",
"label": row.label,
"genes": row.genes,
"phenotypes": [],
"phenotypes_neg": [],
"subjects": [],
"meta": row.meta
}
# Related phenotypes
pheno = row.phenotypes if row.phenotypes is not None else []
npheno = row.phenotypes_neg if row.phenotypes_neg is not None else []
rel = ["'{}'".format(i) for i in pheno]
rel += ["'{}'".format(i) for i in npheno]
sql = "SELECT hpo_id, label, definition, meta FROM hpo_phenotype WHERE hpo_id IN ({}) ORDER BY label".format(",".join(rel))
if len(rel) > 0:
rel = []
for r in execute(sql):
p = {"id": r.hpo_id, "label": r.label, "definition": r.definition, "meta": {"qualifiers": { disease_id: []}}}
if disease_id in r.meta["qualifiers"]:
p["meta"]["qualifiers"][disease_id] = r.meta["qualifiers"][disease_id]
rel += p["meta"]["qualifiers"][disease_id]
if r.hpo_id in pheno:
result["phenotypes"].append(p)
else:
result["phenotypes_neg"].append(p)
# Qualifiers phenotypes
rel =remove_duplicates(rel)
rel.sort()
rel = ["'{}'".format(p) for p in rel]
if len(rel) > 0:
sql = "SELECT hpo_id, label, definition FROM hpo_phenotype WHERE hpo_id IN ({}) ORDER BY label".format(",".join(rel))
for r in execute(sql):
p = {"id": r.hpo_id, "label": r.label, "definition": r.definition}
for pdata in result["phenotypes"]:
if r.hpo_id in pdata["meta"]["qualifiers"][disease_id]:
pdata["meta"]["qualifiers"][disease_id].remove(r.hpo_id)
pdata["meta"]["qualifiers"][disease_id].append(p)
for pdata in result["phenotypes_neg"]:
if r.hpo_id in pdata["meta"]["qualifiers"][disease_id]:
pdata["meta"]["qualifiers"][disease_id].remove(r.hpo_id)
pdata["meta"]["qualifiers"][disease_id].append(p)
# Related subjects
sql = "SELECT s.id, s.identifier, s.firstname, s.lastname, s.sex, s.dateofbirth, s.comment FROM subject s INNER JOIN subject_phenotype p ON p.subject_id=s.id WHERE p.presence='present' AND p.hpo_id='{}'".format(disease_id)
for r in execute(sql):
result["subjects"].append({"id": r.id, "identifier": r.identifier, "firstname": r.firstname, "lastname": r.lastname, "sex": r.sex, "dateofbirth": r.dateofbirth.isoformat() if r.dateofbirth else None, "comment": r.comment})
return result
def _get_hp(self, hpo_id):
"""
Internal method, called by get(token) to retrieve phenotypics data from a provided hpo id
"""
sql = "SELECT hpo_id, label, definition, parents, childs, allsubs_diseases, allsubs_genes, category, meta FROM hpo_phenotype WHERE hpo_id='{}'".format(hpo_id)
row = execute(sql).first()
result = {
"id": hpo_id,
"type" : "phenotypic",
"label": row.label,
"definition": row.definition,
"parents": [],
"childs": [],
"diseases": [],
"genes": row.allsubs_genes,
"subjects": [],
"category": row.category,
"meta": row.meta
}
# Related phenotypes
parents = row.parents if row.parents is not None else []
childs = row.childs if row.childs is not None else []
rel = ["'{}'".format(i) for i in parents]
rel += ["'{}'".format(i) for i in childs]
sql = "SELECT hpo_id, label FROM hpo_phenotype WHERE hpo_id IN ({}) ORDER BY label".format(",".join(rel))
if len(rel) > 0:
for r in execute(sql):
if r.hpo_id in parents:
result["parents"].append({"id": r.hpo_id, "label": r.label})
else:
result["childs"].append({"id": r.hpo_id, "label": r.label})
# Related diseases
rel = ["'{}'".format(i) for i in row.allsubs_diseases] if row.allsubs_diseases else []
sql = "SELECT hpo_id, label FROM hpo_disease WHERE hpo_id IN ({}) ORDER BY label".format(",".join(rel))
if len(rel) > 0:
for r in execute(sql):
result["diseases"].append({"id": r.hpo_id, "label": r.label})
# Related subjects
sql = "SELECT s.id, s.identifier, s.firstname, s.lastname, s.sex, s.dateofbirth, s.comment FROM subject s INNER JOIN subject_phenotype p ON p.subject_id=s.id WHERE p.presence='present' AND p.hpo_id='{}'".format(hpo_id)
for r in execute(sql):
result["subjects"].append({"id": r.id, "identifier": r.identifier, "firstname": r.firstname, "lastname": r.lastname, "sex": r.sex, "dateofbirth": r.dateofbirth.isoformat() if r.dateofbirth else None, "comment": r.comment})
# Qualifiers phenotypes
rel = []
for did in row.meta["qualifiers"]:
rel += ["'{}'".format(i) for i in row.meta["qualifiers"][did]]
rel = remove_duplicates(rel)
sql = "SELECT hpo_id, label, definition FROM hpo_phenotype WHERE hpo_id IN ({})".format(",".join(rel))
if len(rel) > 0:
for r in execute(sql):
p = {"id": r.hpo_id, "label": r.label, "definition": r.definition}
for did in row.meta["qualifiers"]:
if r.hpo_id in row.meta["qualifiers"][did]:
row.meta["qualifiers"][did].remove(r.hpo_id)
row.meta["qualifiers"][did].append(p)
return result
def search(self, search):
"""
Return all phenotypes (minimal info) matching the search term
To be used for autocomplete search by example
"""
# TODO: escape search
if not isinstance(search, str) or search.strip() == "":
raise RegovarException("Invalid search query")
ids = []
# Search among diseases
diseases = []
query = "SELECT hpo_id, label, meta FROM hpo_disease WHERE label ILIKE '%{0}%' ORDER BY label LIMIT 100".format(search)
for row in execute(query):
if row.hpo_id not in ids:
diseases.append({"id": row.hpo_id, "label": row.label, "meta": row.meta})
ids.append(row.hpo_id)
# Search also among diseases synonyms if needed
if len(diseases) == 0:
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE search ILIKE '%{0}%' ORDER BY label LIMIT 100".format(search)
for row in execute(query):
if row.hpo_id not in ids:
diseases.append({"id": row.hpo_id, "label": row.label, "meta": row.meta})
ids.append(row.hpo_id)
# Search among phenotypes
phenotypes = []
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE label ILIKE '%{0}%' ORDER BY label LIMIT 100".format(search)
for row in execute(query):
if row.hpo_id not in ids:
phenotypes.append({"id": row.hpo_id, "label": row.label, "meta": row.meta})
ids.append(row.hpo_id)
# Search also among synonyms if needed
if len(phenotypes) == 0:
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE search ILIKE '%{0}%' ORDER BY label LIMIT 100".format(search)
for row in execute(query):
if row.hpo_id not in ids:
phenotypes.append({"id": row.hpo_id, "label": row.label, "meta": row.meta})
ids.append(row.hpo_id)
# Search also among description if needed
if len(phenotypes) == 0:
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE definition ILIKE '%{0}%' ORDER BY label LIMIT 100".format(search)
for row in execute(query):
if row.hpo_id not in ids:
phenotypes.append({"id": row.hpo_id, "label": row.label, "meta": row.meta})
ids.append(row.hpo_id)
return diseases + phenotypes
def update(self, analysis_id, data, author_id=None):
"""
Update analysis with provided data. Data that are not provided are not updated (ignored).
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis:
raise RegovarException(
"Unable to find analysis with the provided id {}".format(
analysis_id))
# Update analysis's simple properties
analysis.load(data)
# saved filters
if "filters" in data.keys():
# delete old filters
execute(
"DELETE FROM filter WHERE analysis_id={}".format(analysis_id))
# create new associations
query = "INSERT INTO filter (analysis_id, name, filter) VALUES "
subquery = "({0}, '{1}', '{2}'')"
query = query + ', '.join([
subquery.format(analysis_id, f['name'], f['filter'])
for f in data["filters"]
])
execute(query)
# Updating dynamicaly samples not supported. it's better for the user to recreate a new analysis
# attributes + values
if "attributes" in data.keys():
# create new attributes
pattern = "({0}, {1}, '{2}', '{3}', MD5(CONCAT('{2}', '{3}')))"
data['attributes'] = [
a for a in data['attributes'] if a['name'] != ""
]
query = ', '.join([
pattern.format(analysis_id, sid, sql_escape(att['name']),
sql_escape(att['samples_values'][sid]))
for att in data['attributes'] for sid in att['samples_values']
])
# check if query seems good then apply change
if query != "":
execute("DELETE FROM attribute WHERE analysis_id={}".format(
analysis_id))
execute(
"INSERT INTO attribute (analysis_id, sample_id, name, value, wt_col_id) VALUES "
+ query)
else:
# TODO: log error
pass
# return reloaded analysis
core.events.log(
author_id, "info", {"analysis_id": analysis.id},
"Analysis information updated: {}".format(analysis.name))
return Analysis.from_id(analysis_id, 1)
def list(self):
"""
List all pipelines with minimum of data
"""
sql = "SELECT id, name, type, status, description, version, image_file_id, starred, installation_date, manifest, documents FROM pipeline ORDER BY id"
result = []
for res in execute(sql):
result.append({
"id":
res.id,
"name":
res.name,
"description":
res.description,
"type":
res.type,
"status":
res.status,
"version":
res.version,
"image_file_id":
res.image_file_id,
"starred":
res.starred,
"installation_date":
res.installation_date.isoformat(),
"manifest":
res.manifest,
"documents":
res.documents
})
return result
def list(self):
"""
List all subjects with "minimal data"
"""
sql = "SELECT id, identifier, firstname, lastname, sex, family_number, dateofbirth, comment, create_date, update_date FROM subject"
result = []
for res in execute(sql):
result.append({
"id":
res.id,
"identifier":
res.identifier,
"firstname":
res.firstname,
"lastname":
res.lastname,
"sex":
res.sex,
"family_number":
res.family_number,
"dateofbirth":
res.dateofbirth.isoformat() if res.dateofbirth else None,
"comment":
res.comment,
"create_date":
res.create_date.isoformat(),
"update_date":
res.update_date.isoformat()
})
return result
def list(self):
"""
List all projects with "minimal data"
"""
sql = "SELECT p.id, p.name, p.comment, p.parent_id, p.is_folder, p.create_date, p.update_date, array_agg(DISTINCT a.id) as analyses, array_agg(DISTINCT j.id) as jobs FROM project p LEFT JOIN analysis a ON a.project_id=p.id LEFT JOIN job j ON j.project_id=p.id where not is_sandbox GROUP BY p.id, p.name, p.comment, p.parent_id, p.is_folder, p.create_date, p.update_date ORDER BY p.parent_id, p.name"
result = []
for res in execute(sql):
result.append({
"id":
res.id,
"name":
res.name,
"comment":
res.comment,
"parent_id":
res.parent_id,
"is_folder":
res.is_folder,
"analyses_ids":
res.analyses if res.analyses[0] else [],
"jobs_ids":
res.jobs if res.jobs[0] else [],
"create_date":
res.create_date.isoformat(),
"update_date":
res.update_date.isoformat()
})
return result
def list(self):
"""
Return all phenotypes entries
"""
sql = "WITH data AS (SELECT DISTINCT(hpo_id) AS id, hpo_label AS label FROM hpo_phenotype UNION SELECT DISTINCT(hpo_id) AS id, hpo_label AS label FROM hpo_disease) SELECT distinct(id), label FROM data;"
result = []
for res in execute(sql):
result.append({"id": res.id, "label": res.label})
return result
def update_analysis_async(analysis, filter_id, data):
from core.model import Filter
total_results = core.filters.update_wt(analysis, "filter_{}".format(filter_id), data["filter"])
filter = Filter.from_id(filter_id)
filter.total_variants = execute("SELECT COUNT(DISTINCT variant_id) FROM wt_{} WHERE filter_{}".format(analysis.id, filter_id)).first()[0]
filter.total_results = total_results
filter.progress = 1
filter.save()
core.notify_all(data={'action':'filter_update', 'data': filter.to_json()})
def update_selection(self, analysis_id, is_selected, variant_ids):
"""
Add or remove variant/trx from the selection of the analysis
"""
analysis = Analysis.from_id(analysis_id)
if not isinstance(
variant_ids,
list) or not analysis or not analysis.status == 'ready':
return False
query = ""
for vid in variant_ids:
ids = vid.split("_")
if len(ids) == 1:
query += "UPDATE wt_{} SET is_selected={} WHERE variant_id={}; ".format(
analysis.id, is_selected, vid)
else:
query += "UPDATE wt_{} SET is_selected={} WHERE variant_id={} AND trx_pk_value='{}'; ".format(
analysis.id, is_selected, ids[0], ids[1])
execute(query)
return True
def list(self):
"""
Return all phenotypes entries
"""
sql = "WITH data AS (SELECT DISTINCT(hpo_id) AS id, hpo_label AS label FROM hpo_phenotype UNION SELECT DISTINCT(hpo_id) AS id, hpo_label AS label FROM hpo_disease) SELECT distinct(id), label FROM data;"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"label": res.label
})
return result
def clear_temps_data(self, analysis_id, author_id=None):
"""
Clear temporary data of the analysis (to save disk space by example)
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis:
raise RegovarException(
"Unable to fin analysis with the provided id {}".format(
analysis_id))
try:
execute("DROP TABLE IF EXISTS wt_{} CASCADE;".format(analysis_id))
execute(
"DROP TABLE IF EXISTS wt_{}_var CASCADE".format(analysis_id))
execute(
"DROP TABLE IF EXISTS wt_{}_tmp CASCADE".format(analysis_id))
analysis.status = "close"
analysis.save()
core.events.log(author_id, "info", {"analysis_id": analysis.id},
"Analysis closed: {}".format(analysis.name))
except Exception as ex:
raise RegovarException(
"Error occure when trying to clear temporary data of the analysis {}."
.format(analysis_id),
exception=ex)
return True
def delete(self, analysis_id, author_id=None, definitely=False):
"""
Delete the analysis
When normal user delete an analysis, this one is put in the trash project
Then an admin can definitely delete the analysis (with the flag finally set to True)
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis:
raise RegovarException(
"Unable to find analysis with the provided id {}".format(
analysis_id))
if definitely:
self.clear_temps_data(analysis_id)
# Delete related files
# TODO
sql = "DELETE FROM analysis WHERE id={0}; DELETE FROM filter WHERE analysis_id={0};".format(
analysis_id)
sql += "DELETE FROM analysis_sample WHERE analysis_id={0}; DELETE FROM attribute WHERE analysis_id={0}".format(
analysis_id)
sql += "DELETE FROM analysis_indicator_value WHERE analysis_id={0};".format(
analysis_id)
core.events.log(
author_id, "warning", {"analysis_id": analysis.id},
"Irreversible deletion of the analysis: {}".format(
analysis.name))
else:
sql = "UPDATE analysis SET project_id=0 WHERE id={0}; ".format(
analysis_id)
core.events.log(
author_id, "info", {"analysis_id": analysis.id},
"Analysis moved to trash: {}".format(analysis.name))
result = analysis.to_json()
execute(sql)
return result
def update_selection(self, analysis_id, is_selected, variant_ids):
"""
Add or remove variant/trx from the selection of the analysis
"""
analysis = Analysis.from_id(analysis_id)
if not isinstance(variant_ids, list) or not analysis or not analysis.status == 'ready':
return False
query = ""
# Update variant selection in working table
for vid in variant_ids:
ids = vid.split("_")
if len(ids) == 1:
query += "UPDATE wt_{} SET is_selected={} WHERE variant_id={}; ".format(analysis.id, is_selected, vid)
else:
query += "UPDATE wt_{} SET is_selected={} WHERE variant_id={} AND trx_pk_value='{}'; ".format(analysis.id, is_selected, ids[0], ids[1])
execute(query)
# Upate global selection information in analysis table
result = []
for row in execute("SELECT variant_id, trx_pk_value FROM wt_{} WHERE is_selected".format(analysis_id)):
result.append("{}_{}".format(row.variant_id, row.trx_pk_value))
execute("UPDATE analysis SET selection='{}' WHERE id={}".format(json.dumps(result), analysis_id))
return True
def update_analysis_async(analysis, filter_id, data):
from core.model import Filter
total_results = core.filters.update_wt(
analysis, "filter_{}".format(filter_id), data["filter"])
filter = Filter.from_id(filter_id)
filter.total_variants = execute(
"SELECT COUNT(DISTINCT variant_id) FROM wt_{} WHERE filter_{}".
format(analysis.id, filter_id)).first()[0]
filter.total_results = total_results
filter.progress = 1
filter.save()
core.notify_all(data={
'action': 'filter_update',
'data': filter.to_json()
})
def list(self):
"""
List all panels with minimal data
"""
sql = "SELECT id, name, description, owner, create_date, update_date, shared FROM panel ORDER BY id"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"name": res.name,
"description": res.description,
"owner": res.owner,
"shared": res.shared,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat()
})
return result
def list(self):
"""
Return all analyses with "minimal data"
"""
sql = "SELECT id, project_id, name, comment, create_date, update_date, reference_id, status FROM analysis ORDER BY id"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"project_id": res.project_id,
"name": res.name,
"comment": res.comment,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat(),
"reference_id": res.reference_id,
"status": res.status
})
return result
def list(self):
"""
Return all analyses with "minimal data"
"""
sql = "SELECT id, project_id, name, comment, create_date, update_date, reference_id, status FROM analysis"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"project_id": res.project_id,
"name": res.name,
"comment": res.comment,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat(),
"reference_id": res.reference_id,
"status": res.status
})
return result
def update(self, analysis_id, data, author_id=None):
"""
Update analysis with provided data. Data that are not provided are not updated (ignored).
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis:
raise RegovarException("Unable to find analysis with the provided id {}".format(analysis_id))
# Update analysis's simple properties
analysis.load(data)
# saved filters
if "filters" in data.keys():
# delete old filters
execute("DELETE FROM filter WHERE analysis_id={}".format(analysis_id))
# create new associations
query = "INSERT INTO filter (analysis_id, name, filter) VALUES "
subquery = "({0}, '{1}', '{2}'')"
query = query + ', '.join([subquery.format(analysis_id, f['name'], f['filter']) for f in data["filters"]])
execute(query)
# Updating dynamicaly samples not supported. it's better for the user to recreate a new analysis
# attributes + values
if "attributes" in data.keys():
# create new attributes
pattern = "({0}, {1}, '{2}', '{3}', MD5(CONCAT('{2}', '{3}')))"
data['attributes'] = [a for a in data['attributes'] if a['name'] != ""]
query = ', '.join([pattern.format(analysis_id, sid, sql_escape(att['name']), sql_escape(att['samples_values'][sid])) for att in data['attributes'] for sid in att['samples_values']])
# check if query seems good then apply change
if query != "":
execute("DELETE FROM attribute WHERE analysis_id={}".format(analysis_id))
execute("INSERT INTO attribute (analysis_id, sample_id, name, value, wt_col_id) VALUES " + query)
else:
# TODO: log error
pass
# return reloaded analysis
core.events.log(author_id, "info", {"analysis_id": analysis.id}, "Analysis information updated: {}".format(analysis.name))
return Analysis.from_id(analysis_id, 1)
def list(self):
"""
List all jobs with minimal data
"""
sql = "SELECT id, pipeline_id, project_id, name, comment, create_date, update_date, status, progress_value, progress_label FROM job ORDER BY id"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"pipeline_id": res.pipeline_id,
"project_id": res.project_id,
"name": res.name,
"comment": res.comment,
"status": res.status,
"progress_value": res.progress_value,
"progress_label": res.progress_label,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat()
})
return result
def list(self):
"""
List all subjects with "minimal data"
"""
sql = "SELECT id, identifier, firstname, lastname, sex, family_number, dateofbirth, comment, create_date, update_date FROM subject"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"identifier": res.identifier,
"firstname": res.firstname,
"lastname": res.lastname,
"sex": res.sex,
"family_number": res.family_number,
"dateofbirth": res.dateofbirth.isoformat() if res.dateofbirth else None,
"comment": res.comment,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat()
})
return result
def get_selection(self, analysis_id):
"""
Return list of selected variant (with same columns as set for the current filter)
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis:
raise RegovarException(
"Unable to find analysis with the provided id: {}".format(
analysis_id))
fields = core.filters.parse_fields(analysis, analysis.fields, "")
query = "SELECT {} FROM wt_{} WHERE is_selected".format(
fields, analysis_id)
result = []
for row in execute(query):
result.append({fid: row[fid] for fid in fields.split(", ")})
return result
def list(self):
"""
List all pipelines with minimum of data
"""
sql = "SELECT id, name, type, status, description, version, image_file_id, starred, installation_date, manifest, documents FROM pipeline ORDER BY id"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"name": res.name,
"description": res.description,
"type": res.type,
"status": res.status,
"version": res.version,
"image_file_id": res.image_file_id,
"starred": res.starred,
"installation_date": res.installation_date.isoformat(),
"manifest": res.manifest,
"documents": res.documents
})
return result
def list(self):
"""
List all users with minimal data
"""
sql = "SELECT id, login, firstname, lastname, email, create_date, update_date, is_activated, is_admin, function, location FROM \"user\" ORDER BY lastname, firstname, id"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"login": res.login,
"firstname": res.firstname,
"lastname": res.lastname,
"email": res.email,
"is_activated": res.is_activated,
"is_admin": res.is_admin,
"function": res.function,
"location": res.location,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat()
})
return result
def list(self):
"""
List all users with minimal data
"""
sql = "SELECT id, login, firstname, lastname, email, create_date, update_date, is_activated, is_admin, function, location FROM \"user\" ORDER BY lastname, firstname, id"
result = []
for res in execute(sql):
result.append({
"id": res.id,
"login": res.login,
"firstname": res.firstname,
"lastname": res.lastname,
"email": res.email,
"is_activated": res.is_activated,
"is_admin": res.is_admin,
"function": res.function,
"location": res.location,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat()
})
return result
def list(self):
"""
List all panels with minimal data
"""
sql = "SELECT p.id, p.name, p.description, p.owner, p.shared, e.id as vid, e.version, e.comment, e.create_date, e.update_date, e.data FROM panel p INNER JOIN panel_entry e ON p.id=e.panel_id ORDER BY p.name ASC, e.create_date DESC"
result = []
current = None
headversion = False
for res in execute(sql):
if not current or current["id"] != res.id:
if current != None: result.append(current)
current = current = {
"id": res.id,
"name": res.name,
"description": res.description,
"owner": res.owner,
"shared": res.shared,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat(),
"versions": []
}
headversion = True
# Add version
v = {
"id": res.vid,
"name": res.version,
"comment": res.comment,
"create_date": res.create_date.isoformat(),
"update_date": res.update_date.isoformat()
}
# Add entries for each panel head version
if headversion:
v["entries"] = res.data
headversion = False
# Append version to the panel
current["versions"].append(v)
if current != None: result.append(current)
return result
def list(self, ref_id=0):
"""
List all samples by default, or samples for a provided reference_id
"""
sql_where = " WHERE reference_id={}".format(
ref_id) if ref_id > 0 else ""
sql = "SELECT id, subject_id, name, comment, is_mosaic, file_id, loading_progress, reference_id, status FROM sample{} ORDER BY id".format(
sql_where)
result = []
for res in execute(sql):
result.append({
"id": res.id,
"subject_id": res.subject_id,
"name": res.name,
"comment": res.comment,
"status": res.status,
"is_mosaic": res.is_mosaic,
"file_id": res.file_id,
"loading_progress": res.loading_progress,
"reference_id": res.reference_id
})
return result
def clear_temps_data(self, analysis_id, author_id=None):
"""
Clear temporary data of the analysis (to save disk space by example)
"""
from core.core import core
analysis = Analysis.from_id(analysis_id)
if not analysis:
raise RegovarException("Unable to fin analysis with the provided id {}".format(analysis_id))
try:
execute("DROP TABLE IF EXISTS wt_{} CASCADE;".format(analysis_id))
execute("DROP TABLE IF EXISTS wt_{}_var CASCADE".format(analysis_id))
execute("DROP TABLE IF EXISTS wt_{}_tmp CASCADE".format(analysis_id))
analysis.status = "close"
analysis.save()
core.events.log(author_id, "info", {"analysis_id": analysis.id}, "Analysis closed: {}".format(analysis.name))
except Exception as ex:
raise RegovarException("Error occure when trying to clear temporary data of the analysis {}.".format(analysis_id), exception=ex)
return True
def search(self, search):
"""
Return all phenotypes (minimal info) matching the search term
To be used for autocomplete search by example
"""
# TODO: escape search
if not isinstance(search, str) or search.strip() == "":
raise RegovarException("Invalid search query")
ids = []
# Search among diseases
diseases = []
query = "SELECT hpo_id, label, meta FROM hpo_disease WHERE label ILIKE '%{0}%' ORDER BY label LIMIT 100".format(
search)
for row in execute(query):
if row.hpo_id not in ids:
diseases.append({
"id": row.hpo_id,
"label": row.label,
"meta": row.meta
})
ids.append(row.hpo_id)
# Search also among diseases synonyms if needed
if len(diseases) == 0:
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE search ILIKE '%{0}%' ORDER BY label LIMIT 100".format(
search)
for row in execute(query):
if row.hpo_id not in ids:
diseases.append({
"id": row.hpo_id,
"label": row.label,
"meta": row.meta
})
ids.append(row.hpo_id)
# Search among phenotypes
phenotypes = []
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE label ILIKE '%{0}%' ORDER BY label LIMIT 100".format(
search)
for row in execute(query):
if row.hpo_id not in ids:
phenotypes.append({
"id": row.hpo_id,
"label": row.label,
"meta": row.meta
})
ids.append(row.hpo_id)
# Search also among synonyms if needed
if len(phenotypes) == 0:
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE search ILIKE '%{0}%' ORDER BY label LIMIT 100".format(
search)
for row in execute(query):
if row.hpo_id not in ids:
phenotypes.append({
"id": row.hpo_id,
"label": row.label,
"meta": row.meta
})
ids.append(row.hpo_id)
# Search also among description if needed
if len(phenotypes) == 0:
query = "SELECT hpo_id, label, meta FROM hpo_phenotype WHERE definition ILIKE '%{0}%' ORDER BY label LIMIT 100".format(
search)
for row in execute(query):
if row.hpo_id not in ids:
phenotypes.append({
"id": row.hpo_id,
"label": row.label,
"meta": row.meta
})
ids.append(row.hpo_id)
return diseases + phenotypes
def _get_disease(self, disease_id):
"""
Internal method, called by get(token) to retrieve generic disease data from a provided hpo id
"""
sql = "SELECT hpo_id, label, genes, phenotypes, phenotypes_neg, meta FROM hpo_disease WHERE hpo_id='{}'".format(
disease_id)
row = execute(sql).first()
result = {
"id": disease_id,
"type": "disease",
"label": row.label,
"genes": row.genes,
"phenotypes": [],
"phenotypes_neg": [],
"subjects": [],
"meta": row.meta
}
# Related phenotypes
pheno = row.phenotypes if row.phenotypes is not None else []
npheno = row.phenotypes_neg if row.phenotypes_neg is not None else []
rel = ["'{}'".format(i) for i in pheno]
rel += ["'{}'".format(i) for i in npheno]
sql = "SELECT hpo_id, label, definition, meta FROM hpo_phenotype WHERE hpo_id IN ({}) ORDER BY label".format(
",".join(rel))
if len(rel) > 0:
rel = []
for r in execute(sql):
p = {
"id": r.hpo_id,
"label": r.label,
"definition": r.definition,
"meta": {
"qualifiers": {
disease_id: []
}
}
}
if disease_id in r.meta["qualifiers"]:
p["meta"]["qualifiers"][disease_id] = r.meta["qualifiers"][
disease_id]
rel += p["meta"]["qualifiers"][disease_id]
if r.hpo_id in pheno:
result["phenotypes"].append(p)
else:
result["phenotypes_neg"].append(p)
# Qualifiers phenotypes
rel = remove_duplicates(rel)
rel.sort()
rel = ["'{}'".format(p) for p in rel]
if len(rel) > 0:
sql = "SELECT hpo_id, label, definition FROM hpo_phenotype WHERE hpo_id IN ({}) ORDER BY label".format(
",".join(rel))
for r in execute(sql):
p = {
"id": r.hpo_id,
"label": r.label,
"definition": r.definition
}
for pdata in result["phenotypes"]:
if r.hpo_id in pdata["meta"]["qualifiers"][disease_id]:
pdata["meta"]["qualifiers"][disease_id].remove(
r.hpo_id)
pdata["meta"]["qualifiers"][disease_id].append(p)
for pdata in result["phenotypes_neg"]:
if r.hpo_id in pdata["meta"]["qualifiers"][disease_id]:
pdata["meta"]["qualifiers"][disease_id].remove(
r.hpo_id)
pdata["meta"]["qualifiers"][disease_id].append(p)
# Related subjects
sql = "SELECT s.id, s.identifier, s.firstname, s.lastname, s.sex, s.dateofbirth, s.comment FROM subject s INNER JOIN subject_phenotype p ON p.subject_id=s.id WHERE p.presence='present' AND p.hpo_id='{}'".format(
disease_id)
for r in execute(sql):
result["subjects"].append({
"id":
r.id,
"identifier":
r.identifier,
"firstname":
r.firstname,
"lastname":
r.lastname,
"sex":
r.sex,
"dateofbirth":
r.dateofbirth.isoformat() if r.dateofbirth else None,
"comment":
r.comment
})
return result
def _get_hp(self, hpo_id):
"""
Internal method, called by get(token) to retrieve phenotypics data from a provided hpo id
"""
sql = "SELECT hpo_id, label, definition, parents, childs, allsubs_diseases, allsubs_genes, category, meta FROM hpo_phenotype WHERE hpo_id='{}'".format(
hpo_id)
row = execute(sql).first()
result = {
"id": hpo_id,
"type": "phenotypic",
"label": row.label,
"definition": row.definition,
"parents": [],
"childs": [],
"diseases": [],
"genes": row.allsubs_genes,
"subjects": [],
"category": row.category,
"meta": row.meta
}
# Related phenotypes
parents = row.parents if row.parents is not None else []
childs = row.childs if row.childs is not None else []
rel = ["'{}'".format(i) for i in parents]
rel += ["'{}'".format(i) for i in childs]
sql = "SELECT hpo_id, label FROM hpo_phenotype WHERE hpo_id IN ({}) ORDER BY label".format(
",".join(rel))
if len(rel) > 0:
for r in execute(sql):
if r.hpo_id in parents:
result["parents"].append({
"id": r.hpo_id,
"label": r.label
})
else:
result["childs"].append({"id": r.hpo_id, "label": r.label})
# Related diseases
rel = ["'{}'".format(i)
for i in row.allsubs_diseases] if row.allsubs_diseases else []
sql = "SELECT hpo_id, label FROM hpo_disease WHERE hpo_id IN ({}) ORDER BY label".format(
",".join(rel))
if len(rel) > 0:
for r in execute(sql):
result["diseases"].append({"id": r.hpo_id, "label": r.label})
# Related subjects
sql = "SELECT s.id, s.identifier, s.firstname, s.lastname, s.sex, s.dateofbirth, s.comment FROM subject s INNER JOIN subject_phenotype p ON p.subject_id=s.id WHERE p.presence='present' AND p.hpo_id='{}'".format(
hpo_id)
for r in execute(sql):
result["subjects"].append({
"id":
r.id,
"identifier":
r.identifier,
"firstname":
r.firstname,
"lastname":
r.lastname,
"sex":
r.sex,
"dateofbirth":
r.dateofbirth.isoformat() if r.dateofbirth else None,
"comment":
r.comment
})
# Qualifiers phenotypes
rel = []
for did in row.meta["qualifiers"]:
rel += ["'{}'".format(i) for i in row.meta["qualifiers"][did]]
rel = remove_duplicates(rel)
sql = "SELECT hpo_id, label, definition FROM hpo_phenotype WHERE hpo_id IN ({})".format(
",".join(rel))
if len(rel) > 0:
for r in execute(sql):
p = {
"id": r.hpo_id,
"label": r.label,
"definition": r.definition
}
for did in row.meta["qualifiers"]:
if r.hpo_id in row.meta["qualifiers"][did]:
row.meta["qualifiers"][did].remove(r.hpo_id)
row.meta["qualifiers"][did].append(p)
return result
