def retrieve_daemon_more_than_week_running_tasks():
sql = """SELECT ct.* FROM CONAN_TASKS ct join CONAN_USERS cu on ct.USER_ID = cu.ID
where ct.STATE='RUNNING' and cu.LAST_NAME='Daemon'
AND START_DATE < TO_DATE('%s', 'YYYY-MM-DD"T"HH24:MI:SS')""" % \
(datetime.datetime.today() - datetime.timedelta(days=7)).isoformat().split('.')[0]
print sql
exit()
return execute_select(sql, db)
def retrieve_job_status(accession):
sql_stmt = """SELECT STATE FROM CONAN_TASKS where NAME = '%s' and STATE != 'ABORTED' order by ID desc """ % accession
result = execute_select(sql_stmt, db)
if result and result[0]:
return result[0].state
return None
def retrieve_pub(acc, pubmed):
sql = """SELECT * FROM PUBLICATION WHERE PUBMEDID ='{pubmed}' OR ACC='{acc}'""".format(
acc=str(acc), pubmed=str(pubmed))
# sql = "SELECT * FROM PUBLICATION WHERE PUBMEDID ='{pubmed}'".format(acc=str(acc), pubmed=str(pubmed))
# print db
# sql = "SELECT * FROM PUBLICATION WHERE PUBMEDID ='6696735'"
return execute_select(sql, db)
def retrieve_ena_accession(acc):
sql = """select TEXT from study, study_annotations, controlled_vocabulary where
study.id = study_annotations.study_id and
study_annotations.type_id = controlled_vocabulary.id and
study.acc = '{acc}' and NAME = 'Comment[SecondaryAccession]'""".format(
acc=acc)
# print sql
return execute_select(sql, db)
def retrieve_permission_for_accession(acc):
sql = """ select s.releasedate,o.sc_user_id
from study s, sc_label l
left join sc_owner o on l.id=o.sc_label_id
where s.acc='{acc}'
and l.name='{acc}'
order by sc_user_id""".format(acc=acc)
# print sql
return execute_select(sql, db)
def get_file_by_run(run_acc):
sql = """
SELECT DATA_FILE_PATH FROM DATA_FILE_META
WHERE DATA_FILE_OWNER = 'RUN'
AND DATA_FILE_OWNER_ID='%s'
AND DATA_FILE_FORMAT = 'FASTQ'
""" % run_acc
return execute_select(sql, db)
def retrieve_today_updated_experiments(update_date=None):
if not update_date:
update_date = datetime.datetime.today().date().isoformat()
sql = """select sa.TEXT , s.ACC, s.TITLE, s.RELEASEDATE, p.DOI, p.PUBMED, p.AUTHORLIST, p.TITLE as publication_title
from AE2.STUDY s join AE2.VIEW_PUBLICATIONS p on s.ACC = p.STUDYACC
left join (select * from AE2.STUDY_ANNOTATIONS where TYPE_ID='151752' ) sa on s.ID = sa.STUDY_ID
where s.LASTUPDATEDATE >= DATE'%s' and s.ACC not like 'E-GEOD-%%'
order by sa.TEXT asc""" % update_date
return execute_select(sql, db)
def retrieve_study_by_acc(acc):
sql = """select title, description from STUDY where ACC = '{acc}'""".format(acc=acc)
# print sql
res, con = execute_select(sql, db, keep_connection=True)
a = 'No Description Available!'
# print 'RESSSS: ', res
if res and res[0]:
a = res[0].description.read()
con.close()
res[0].description = a
return res
def insert_publication(article):
id = execute_select('select SEQ_PUBLICATION.nextval*50 as id from dual',
db)[0].id
sql = """INSERT INTO PUBLICATION
(
ID,
ACC,
DOI,
AUTHORLIST,
TITLE,
EDITOR,
ISSUE,
PAGES,
PUBLICATION,
PUBLISHER,
URI,
VOLUME,
PUBMEDID,
YEAR
)
VALUES(
'{id}',
'{PUBMED}',
'{DOI}',
'{AUTHORLIST}',
'{TITLE}',
'{EDITOR}',
'{ISSUE}',
'{PAGES}',
'{PUBLICATION}',
'{PUBLISHER}',
'{URI}',
'{VOLUME}',
'{PUBMED}',
'{YEAR}'
)""".format(
id=str(id),
PUBMED=article.get('id', ''),
DOI=article.get('doi', None),
AUTHORLIST=article.get('authorString', '').replace("'",
'').encode('utf8'),
TITLE=article.get('title', '').replace("'", '').encode('utf8'),
EDITOR=article.get('editor', '').replace("'", '').encode('utf8'),
ISSUE=article.get('issue', None),
PAGES=article.get('pageInfo', None),
PUBLICATION=article.get('journalTitle', '').encode('utf8'),
PUBLISHER=article.get('publisher', '').encode('utf8'),
URI=article.get('uri', '').encode('utf8'),
VOLUME=article.get('journalVolume', ''),
YEAR=article.get('pubYear', ''))
execute_insert(sql, db)
return str(id)
def retrieve_existing_publications_by_accession(acc):
sql = """select distinct pubid,
pubmed,
doi,
title,
authorlist,
publication,
publisher,
editor,
year,
volume,
issue,
pages,
uri,
studyid
from view_publications
where studyacc='{acc}'""".format(acc=acc)
return execute_select(sql, db)
def retrieve_runs_by_experiment(exp_acc):
sql = """SELECT * from ERA.WH_RUN where EXPERIMENT_ID = '%s' """ % exp_acc
return execute_select(sql, db)
def retrieve_user_by_email(email):
sql = """SELECT ID FROM CONAN_USERS WHERE EMAIL='%s' AND USER_NAME != 'conan-daemon'""" % email
return execute_select(sql, db)
def is_array_design_exists(accession):
sql = """SELECT * from PLAT_DESIGN WHERE ACC = '%s'""" % accession
res = execute_select(sql, db)
if res and len(res) > 0:
return True
return False
def retrieve_samples_by_submission_acc(submission_acc):
return execute_select("""Select * from SAMPLE where SUBMISSION_ID = '%s'""" % submission_acc, db)
def retrieve_xml_by_biosample_id_list(bio_samples_ids):
sql = '''SELECT BIOSAMPLE_ID, xmltype.getStringVal(SAMPLE_XML) as sample_xml from SAMPLE WHERE BIOSAMPLE_ID in (%s)''' % ','.join(
["'%s'" % i for i in bio_samples_ids])
return execute_select(sql, db)
def retrieve_files_by_run_owner(run_acc):
return execute_select("""SELECT * FROM WEBIN_FILE WHERE DATA_FILE_OWNER_ID = '%s' AND DATA_FILE_OWNER = 'RUN'""" % run_acc, db)
def retrieve_sample_by_acc(acc):
return execute_select("""SELECT * FROM SAMPLE WHERE SAMPLE_ID = '%s'""" % acc, db)
def retrieve_ena_nodes_relations(ena_acc):
sql = """SELECT * from ERA.WH_RUN where STUDY_ID = '%s' """ % ena_acc
return execute_select(sql, db)
def retrieve_runs_by_submission_acc(submission_acc):
sql = """SELECT * FROM RUN WHERE SUBMISSION_ID = '%s'""" % submission_acc
# print sql
# exit()
return execute_select(sql, db)
def retrieve_run_by_acc(acc):
sql = """SELECT * FROM RUN WHERE RUN_ID = '%s'""" % acc
return execute_select(sql, db)
def retrieve_study_id_by_run_id(run_id):
sql = """SELECT STUDY_ID FROM EXPERIMENT
WHERE EXPERIMENT_ID = (SELECT EXPERIMENT_ID FROM RUN WHERE RUN_ID = '%s')""" % run_id
return execute_select(sql, db)
def retrieve_files_by_study_id(study_id):
sql = """select * from WEBIN_FILE WHERE DATA_FILE_OWNER_ID in
(select RUN_ID from RUN where EXPERIMENT_ID in
(SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE STUDY_ID = '%s') ) AND DATA_FILE_OWNER = 'RUN'""" % study_id
return execute_select(sql, db)
def retrieve_samples_by_study_id(study_id):
sql = """select es.experiment_id, s.* from SAMPLE s,EXPERIMENT_SAMPLE es where es.EXPERIMENT_ID in
(SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE STUDY_ID = '%s') AND s.SAMPLE_ID = es.SAMPLE_ID""" % study_id
return execute_select(sql, db)
def retrieve_runs_by_study_id(study_id):
sql = """select * from RUN where EXPERIMENT_ID in
(SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE STUDY_ID = '%s')""" % study_id
return execute_select(sql, db)
def retrieve_sample_acc_by_exp_acc(exp_acc):
return execute_select(
"""SELECT SAMPLE_ID FROM EXPERIMENT_SAMPLE WHERE EXPERIMENT_ID ='%s'"""
% exp_acc, db)
def retrieve_studies_by_accession(acc):
sql = """select bp.acc, epv.category, TO_CHAR(epv.term_text) as term_text, ept.category as cat, ept.term_text as txt
from BIO_PRODUCT bp, PRODUCT_PV ppv, EXP_PROP_TYPE ept, EXP_PROP_VAL epv where
bp.acc = '%s' and bp.ID = ppv.OWNER_ID and
ppv.PV_ID = epv.ID and epv.TYPE_ID = ept.ID""" % acc
return execute_select(sql, db)
def retrieve_runs_by_experiment_acc(exp_acc):
sql = """SELECT * FROM RUN WHERE EXPERIMENT_ID = '%s'""" % exp_acc
# print sql
# exit()
return execute_select(sql, db)
def retrieve_alias_by_sample_id(ena_sample):
sql = """SELECT SAMPLE_ALIAS from ERA.SAMPLE where SAMPLE_ID = '%s'""" % ena_sample
return execute_select(sql, db)
def retrieve_contact_by_study_id(ae_id):
sql = """SELECT * FROM CONTACT WHERE STUDY_ID=%s""" % str(ae_id)
# print sql
return execute_select(sql, db)
def retrieve_daemon_failed_tasks():
sql = """SELECT ct.* FROM CONAN_TASKS ct join CONAN_USERS cu on ct.USER_ID = cu.ID
where ct.STATE='FAILED' and cu.LAST_NAME='Daemon'"""
return execute_select(sql, db)
