def reject_name(event, context):
if 'name_id' not in event['pathParameters']:
return invalid_request('Missing id of the name to reject')
if 'compound_id' not in event['pathParameters']:
return invalid_request('missing id of the compound to modify')
if 'rejected' not in event['pathParameters'] or \
event['pathParameters']['rejected'].lower() not in ['true', 'false']:
return invalid_request(
'Invalid rejection value. Please use true or false')
try:
name_id = urllib.parse.unquote(
str(event['pathParameters'].get('name_id', '')))
compound_id = urllib.parse.unquote(
str(event['pathParameters'].get('compound_id', '')))
rejected = urllib.parse.unquote(
str(event['pathParameters'].get('rejected', 'false')))
except Exception as ex:
logger.error(ex.args)
return server_error(ex)
result = database.query(
'SELECT c.id, pre_cursors_mass, retention_index, cn.id, c.name, cn.name, c.name = cn.name as promoted '
'FROM compound c LEFT JOIN compound_name cn on c.id=cn.target_id '
'WHERE cn.id = %(name_id)s', conn, {'name_id': name_id})
if result is not None:
promoted = result[0][6]
target_mz = result[0][1]
target_ri = result[0][2]
unk_name = f'unknown_{target_mz:.4f}_{target_ri:.4f}'
# compound with name exists
database.html_response_query(
'UPDATE compound_name '
'SET rejected = %(rejected)s '
'WHERE id = %(name_id)s', conn, {
'rejected': rejected,
'name_id': name_id,
'compound_id': compound_id
})
if promoted and rejected:
database.query(
'UPDATE compound '
'SET name = %(unk_name)s '
'WHERE id = %(tgt_id)s', conn, {
'unk_name': unk_name,
'tgt_id': compound_id
})
return no_content()
else:
return not_found()
def reject_meta(event, context):
if 'meta_id' not in event['pathParameters']:
return invalid_request('Missing id of the metadata to reject')
if 'compound_id' not in event['pathParameters']:
return invalid_request('Missing id of compound to modify')
if 'rejected' not in event['pathParameters'] or \
event['pathParameters']['rejected'].lower() not in ['true', 'false']:
return invalid_request(
'Invalid rejection value. Please use true or false')
meta_id = urllib.parse.unquote(event['pathParameters']['meta_id'])
compound_id = urllib.parse.unquote(event['pathParameters']['compound_id'])
rejected = urllib.parse.unquote(event['pathParameters']['rejected'])
result = database.html_response_query(
'UPDATE compound_meta '
'SET rejected = %(rejected)s '
'WHERE id = %(meta_id)s '
'AND target_id = %(compound_id)s', conn, {
'rejected': rejected,
'meta_id': meta_id,
'compound_id': compound_id
})
if result['statusCode'] == 500:
return result
return no_content()
def libraries(event, context):
transform = lambda x: x[0]
sql = 'SELECT DISTINCT "method" FROM compound'
return database.html_response_query(sql=sql,
connection=conn,
transform=transform)
def list_adducts_query(event, context):
"""
returns the different adducts for a compound in a library
:param event:
:param context:
:return:
"""
if 'mode' not in event['pathParameters']:
return invalid_request(
'Invalid ion mode, please use "positive", "negative"')
mode = urllib.parse.unquote(event['pathParameters']['mode'])
transform = lambda x: x[0]
sql = "SELECT DISTINCT ca.name FROM compound_adduct ca WHERE ca.name ~ %s"
if mode.strip() and mode.strip() not in ION_MODES:
return invalid_request(
'Invalid ion mode, please use "positive", "negative"')
try:
result = database.html_response_query(
sql=sql,
connection=conn,
transform=transform,
params=[ADDUCT_PATTERNS[mode]['re']])
if result is []:
result.append(ADDUCT_PATTERNS[mode]['default'])
return result
except Exception as ex:
return server_error(ex)
def compound_list_with_istd(request, conn):
result = request.config.cache.get('cis/compounds_with_istd', None)
compounds = None
if result is None:
res = database.html_response_query(
'SELECT id, method, splash, sample, version, pre_cursors_mass, retention_index, target_type, name '
'FROM compound_consensus LIMIT 5000',
conn,
transform=lambda x: {
'id': x[0],
'method': x[1],
'splash': x[2],
'sample': x[3],
'version': x[4],
'mass': x[5],
'ri': x[6],
'target_type': x[7],
'name': x[8]
})
if res['statusCode'] == 200:
compounds = json.loads(res['body'])
if compounds is None:
pytest.fail("Can't find compounds in database")
else:
request.config.cache.set('cis/compounds_with_istd', compounds)
else:
print('using cached data')
compounds = result
return compounds
def reject_adduct(event, context):
if 'adduct_id' not in event['pathParameters']:
return invalid_request('Missing id of the adduct to reject')
if 'compound_id' not in event['pathParameters']:
return invalid_request('Missing id of compound to modify')
if 'rejected' not in event['pathParameters'] or \
event['pathParameters']['rejected'].lower() not in ['true', 'false']:
return invalid_request(
'Invalid rejection value. Please use true or false')
adduct_id = urllib.parse.unquote(event['pathParameters']['adduct_id'])
compound_id = urllib.parse.unquote(event['pathParameters']['compound_id'])
rejected = urllib.parse.unquote(event['pathParameters']['rejected'])
result = database.query(
'SELECT c.id, ca.id, c.adduct, ca.name, c.adduct = ca.name as promoted '
'FROM compound c LEFT JOIN compound_adduct ca on c.id=ca.target_id '
'WHERE ca.id = %(adduct_id)s', conn, {'adduct_id': adduct_id})
if result is not None:
promoted = result[0][4]
try:
# compound with existing adduct
database.html_response_query(
'UPDATE compound_adduct '
'SET rejected = %(rejected)s '
'WHERE id = %(adduct_id)s '
'AND target_id = %(compound_id)s', conn, {
'rejected': rejected,
'adduct_id': adduct_id,
'compound_id': compound_id
})
if promoted:
database.query(
'UPDATE compound '
'SET adduct = null '
'WHERE id = %(tgt_id)s', conn, {'tgt_id': compound_id})
except Exception as ex:
return server_error(ex)
return no_content()
else:
return no_content()
def profiles(event, context):
"""
loads all profiles from the database to be used in the client
"""
transform = lambda x: (x[0], x[1], x[2])
sql = 'select method, profiles, version from compound c group by method, profiles, version'
return database.html_response_query(sql=sql,
connection=conn,
transform=transform)
def get_status(event, context):
"""
returns the status of a spectrum as clean: true or false
:param event:
:param context:
:return:
"""
if 'tgt_id' not in event['pathParameters']:
return invalid_request(
'Missing path parameters "tgt_id" path parameter')
tgt_id = urllib.parse.unquote(event['pathParameters'].get('tgt_id'))
if 'queryStringParameters' not in event or event[
'queryStringParameters'] is None:
event['queryStringParameters'] = {
'limit': '10',
'offset': '0',
'identified_by': ''
}
try:
limit = urllib.parse.unquote(event['queryStringParameters'].get(
'limit', '10'))
offset = urllib.parse.unquote(event['queryStringParameters'].get(
'offset', '0'))
identified_by = urllib.parse.unquote(
event['queryStringParameters'].get('identifiedBy', ''))
# check if the user already marked this spectrum, possibly updating the 'clean' value
query = 'SELECT id, clean, target_id, identified_by ' \
'FROM compound_spectrum_quality ' \
'WHERE target_id = %(tgt_id)s'
limit_query = ' LIMIT %(limit)s OFFSET %(offset)s'
params = {'tgt_id': tgt_id, 'limit': limit, 'offset': offset}
if identified_by:
query = query + ' AND identified_by = %(identified_by)s'
params['identified_by'] = identified_by
transform = lambda x: {
'status_id': x[0],
'clean': x[1],
'target_id': x[2],
'identifiedBy': x[3]
}
return database.html_response_query(query + limit_query, conn, params,
transform)
except Exception as ex:
return server_error(ex)
def get_all(event, context):
version = 'fixed'
if event['queryStringParameters'] is not None:
version = event['queryStringParameters'].get('version', 'fixed')
return database.html_response_query(
'SELECT id, method, splash, version, accurate_mass, name, adduct, hidden, inchi_key, '
'ion_mode, msms, pre_cursors_mass, retention_index, target_type, sample '
'FROM compound '
"WHERE target_type = 'ISTD' "
'AND version = %(version)s '
'ORDER BY method, id', conn, {'version': version}, istd_transform)
def get(event, context):
if 'id' not in event['pathParameters']:
return compounds.invalid_request('Missing "id" path parameter')
try:
tgt_id = int(event['pathParameters'].get('id', '0'))
return database.html_response_query(
'SELECT id, method, splash, version, accurate_mass, name, adduct, hidden, inchi_key, '
'ion_mode, msms, pre_cursors_mass, retention_index, target_type, sample '
'FROM compound '
'WHERE id = %(tgt_id)s', conn, {'tgt_id': tgt_id}, istd_transform)
except Exception as ex:
return compounds.server_error(ex)
def profile(events, context):
"""
queries the profiles for one specific library
"""
if 'library' in events['pathParameters']:
method_name = urllib.parse.unquote(events['pathParameters']['library'])
transform = lambda x: x[0]
result = database.html_response_query(
'SELECT profiles FROM compound c '
'WHERE method = %(method)s '
'GROUP BY method, profiles, version',
conn, {'method': method_name},
transform=transform)
return result
def get_metas(event, context):
"""
returns all metadata items for the given bin
:param event:
:param context:
:return:
"""
offset = 0
limit = 10
if 'library' not in event['pathParameters']:
return compounds.invalid_request('missing "library" path parameter')
if 'splash' not in event['pathParameters']:
return compounds.invalid_request('missing "splash" path parameter')
method_name = urllib.parse.unquote(event['pathParameters']['library'])
splash = urllib.parse.unquote(event['pathParameters']['splash'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
name = urllib.parse.unquote(event['pathParameters']['name'], None)
if name is not None:
named = 'AND c.version = %(version)s '
else:
named = ''
transform = lambda x: {'name': x[0], 'value': x[1], 'identified_by': x[2]}
sql = 'SELECT cm.name, cm.value, cm.identified_by ' \
'FROM compound c LEFT JOIN compound_meta cm ON c.id = cm.target_id ' \
'WHERE NOT c.hidden ' \
'AND c.method = %(method)s ' \
'AND c.splash = %(splash)s ' \
'AND c.version = %(version)s ' \
f'{named}' \
'LIMIT %(limit)s ' \
'OFFSET %(offset)s'
return database.html_response_query(sql=sql,
connection=conn,
transform=transform,
params={
'method': method_name,
'splash': splash,
'version': version,
'limit': limit,
'offset': offset
})
def all(events, context):
'''
Gets a list of splashes
:param events:
:param context:
:return:
'''
if 'library' not in events['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'offset' in events['pathParameters']:
offset = int(events['pathParameters']['offset'])
else:
offset = 0
if 'limit' in events['pathParameters']:
limit = int(events['pathParameters']['limit'])
else:
limit = 10
library = urllib.parse.unquote(events['pathParameters']['library'])
version = urllib.parse.unquote(events['pathParameters'].get(
'version', 'fixed'))
logger.info(
f'loading all compounds for: {library}, version: {version} limit {limit} and offset {offset}'
)
transform = lambda x: x[0]
if 'type' in events['pathParameters']:
target_type = events['pathParameters']['type']
sql = 'SELECT splash FROM compound ' \
'WHERE NOT hidden ' \
'AND method = %(library)s ' \
'AND version = %(version)s ' \
'AND target_type = %(target_type)s ' \
'LIMIT %(limit)s OFFSET %(ofset)s'
return database.html_response_query(sql=sql,
connection=conn,
transform=transform,
params={
'library': library,
'target_type': target_type,
'version': version,
'limit': limit,
'offset': offset
})
else:
sql = 'SELECT splash FROM compound ' \
'WHERE NOT hidden ' \
'AND method = %(library)s ' \
'AND version = %(version)s ' \
'LIMIT %(limit)s OFFSET %(offset)s'
return database.html_response_query(sql=sql,
connection=conn,
transform=transform,
params={
'library': library,
'version': version,
'limit': limit,
'offset': offset
})
def get(events, context):
'''
Gets all the information for a single splash
:param events:
:param context:
:return:
'''
if 'library' not in events['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'splash' not in events['pathParameters']:
return invalid_request('missing "splash" path parameter')
library = urllib.parse.unquote(events['pathParameters']['library'])
splash = urllib.parse.unquote(events['pathParameters']['splash'])
version = urllib.parse.unquote(events['pathParameters'].get(
'version', 'fixed'))
filters = {'library': library, 'splash': splash, 'version': version}
def generate_metas_list(tgt_id):
metas = database.query(
'SELECT id, name, value, identified_by, comment, rejected '
'FROM compound_meta '
'WHERE target_id = %(tgt_id)s', conn, {'tgt_id': tgt_id})
logger.info(f'received metadata: {metas}')
if metas is None:
return []
else:
return list(
map(
lambda y: {
'id': y[0],
'name': y[1],
'value': y[2],
'identifiedBy': y[3],
'comment': y[4],
'rejected': y[5]
}, metas))
def generate_comments_list(tgt_id):
comments = database.query(
'SELECT id, comment, identified_by, rejected '
'FROM compound_comment '
'WHERE target_id = %(tgt_id)s', conn, {'tgt_id': tgt_id})
logger.info(f'received comments: {comments}')
if comments is None:
return []
else:
return list(
map(
lambda y: {
'id': y[0],
'identifiedBy': y[2],
'comment': y[1],
'rejected': y[3]
}, comments))
def generate_adducts_list(tgt_id):
adducts = database.query(
'SELECT id, name, identified_by, comment, rejected '
'FROM compound_adduct '
'WHERE target_id = %(tgt_id)s', conn, {'tgt_id': tgt_id})
logger.info(f'received adducts: {adducts}')
if adducts is None:
return []
else:
return list(
map(
lambda y: {
'id': y[0],
'name': y[1],
'identifiedBy': y[2],
'comment': y[3],
'rejected': y[4]
}, adducts))
def generate_name_list(tgt_id):
names = database.query(
'SELECT id, name, identified_by, comment, rejected '
'FROM compound_name '
'WHERE target_id = %(tgt_id)s', conn, {'tgt_id': tgt_id})
logger.info(f'received names: {names}')
if names is None:
return []
else:
return list(
map(
lambda y: {
'id': y[0],
'name': y[1],
'identifiedBy': y[2],
'comment': y[3],
'rejected': y[4]
}, names))
def get_duplicates_list(tgt_id):
dupes = database.query(
'SELECT id, method, splash, version FROM compound WHERE duplicate_of = %(tgt_id)s',
conn,
params={'tgt_id': tgt_id})
if dupes is None:
return []
else:
return [{
'id': y[0],
'method': y[1],
'splash': y[2],
'version': y[3]
} for y in dupes]
def generate_status_list(tgt_id):
stats = database.query(
'SELECT id, clean, target_id, identified_by FROM compound_spectrum_quality '
'WHERE target_id = %(tgt_id)s '
'LIMIT 10 OFFSET 0',
conn,
params={'tgt_id': tgt_id})
if stats is None:
return []
else:
return [{
'status_id': y[0],
'clean': y[1],
'target_id': y[2],
'identifiedBy': y[3]
} for y in stats]
transform = lambda x: {
'id': x[0],
'accurate_mass': x[1],
'target_type': x[2],
'inchi_key': x[3],
'method': x[4],
'ms_level': x[5],
'required_for_correction': x[7],
'retention_index': x[8],
'spectrum': x[9],
'splash': x[10],
'preferred_name': x[11],
'unique_mass': x[12],
'precursor_mass': x[13],
'preferred_adduct': x[14],
'associated_names': generate_name_list(x[0]),
'associated_adducts': generate_adducts_list(x[0]),
'associated_comments': generate_comments_list(x[0]),
'associated_metas': generate_metas_list(x[0]),
'associated_statuses': generate_status_list(x[0]),
'duplicates': get_duplicates_list(x[0]),
'sample': x[15],
'version': x[16],
'hidden': x[17],
'pre_cursors_intensity': x[18],
'msms_raw': x[19],
'meta_data': x[20],
'pending': x[21],
'rt_mz_members': x[22],
'msms_members': x[23],
'retention_index_min': x[24],
'retention_index_max': x[25],
'accurate_mass_min': x[26],
'accurate_mass_max': x[27],
'msms_clean': x[28],
'msms_consensus': x[29],
}
result = database.html_response_query(
'SELECT id, accurate_mass, target_type, inchi_key, method, 0 as "ms_level", '
'\'\' as "raw_spectrum", False as "required_for_correction", retention_index, msms, splash, name, '
'0.0 as "unique_mass", pre_cursors_mass, adduct, sample, version, hidden, pre_cursors_intensity, msms_raw, '
'meta_data, pending, rt_mz_members, msms_members, retention_index_min, retention_index_max, '
'accurate_mass_min, accurate_mass_max, msms as msms_clean, msms as msms_consensus '
'FROM compound_consensus '
'WHERE splash = %(splash)s '
'AND method = %(library)s '
'AND version = %(version)s ',
conn,
filters,
transform=transform)
return result
def get_all(events, context):
"""
queries the database for a paged list of annotations associated with a target.
:param events: the Lambda "event" object with pathParameters and queryStrings to run the query.
:param context: unused
:return: an dictionary to be converted in a response by AWS APIGateway.
"""
logger.info(f'EVENTS: {events}')
limit = 50
offset = 0
if 'queryStringParameters' in events and events[
'queryStringParameters'] is not None:
if 'limit' in events['queryStringParameters']:
limit = int(events['queryStringParameters']['limit'])
if 'offset' in events['queryStringParameters']:
offset = int(events['queryStringParameters']['offset'])
if 'pathParameters' in events:
if 'splash' in events['pathParameters']:
splash = urllib.parse.unquote(events['pathParameters']['splash'])
# get the annotations count and the target_id for the given splash
response = database.query(
'SELECT a.target_id, c.name, c.version, c.method, c.splash '
'FROM result_annotations a, compound c '
'WHERE a.target_id = c.id '
'AND c.splash = %s '
'AND a.method = c.method '
'AND a.version = c.version '
'GROUP BY a.target_id, c.name, c.version, c.method, c.splash',
conn, [splash])
print(response)
count, tgtid = response
if count == 0:
return {"statusCode": 404, "body": {}}
else:
try:
transform = lambda x: {
"id": x[0],
"accurate_mass": x[1],
"ion_mode": x[2],
"ms_level": x[3],
"original_retention_time": x[4],
"precursor_mass": x[5],
"raw_spectrum": x[6],
"replaced": x[7],
"retention_index": x[8],
"spectrum": x[9],
"splash": x[10],
"sample_id": x[11],
"target_id": x[12]
}
response = database.html_response_query(
'SELECT * FROM result_annotations a WHERE target_id = (%s) '
'LIMIT %d OFFSET %d', conn, [tgtid, limit, offset],
transform)
if response['statusCode'] != 200:
return response
result = json.loads(response['body'])
# create a response
body_data = json.dumps(
{
"splash": splash,
"total_count": count,
"annotations": result
},
use_decimal=True)
return {
"statusCode": 200,
"headers": headers.__HTTP_HEADERS__,
"body": body_data
}
except Exception as e:
# traceback.print_exc()
return {
"statusCode":
500,
"headers":
headers.__HTTP_HEADERS__,
"body":
json.dumps({
"error": str(e),
"splash": splash
},
use_decimal=True)
}
else:
return {
"statusCode":
500,
"headers":
headers.__HTTP_HEADERS__,
"body":
json.dumps({"error": "you need to provide a target's splash"},
use_decimal=True)
}
else:
return {
"statusCode":
500,
"headers":
headers.__HTTP_HEADERS__,
"body":
json.dumps({"error": "missing path parameters"}, use_decimal=True)
}
