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 delete_status(event, context):
"""
deletes a clean or dirty flag for a given target's spectrum
:param event:
:param context:
:return:
"""
if 'tgt_id' not in event['pathParameters']:
return invalid_request("Missing path parameters 'tgt_id'")
tgt_id = urllib.parse.unquote(event['pathParameters']['tgt_id'])
if 'queryStringParameters' not in event or 'identifiedBy' not in event[
'queryStringParameters']:
return invalid_request("Missing query string parameter 'identifiedBy'")
identified_by = event['queryStringParameters']['identifiedBy']
try:
result = database.query(
'DELETE FROM compound_spectrum_quality '
'WHERE target_id = %s AND identified_by = %s', conn,
[tgt_id, identified_by])
response = {"statusCode": 204, "headers": headers.__HTTP_HEADERS__}
except Exception as ex:
logger.error(str(ex))
response = {
"statusCode": 500,
"headers": headers.__HTTP_HEADERS__,
"body": json.dumps({"error": str(ex)}, use_decimal=True)
}
return response
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 delete_metas(event, context):
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
library = urllib.parse.unquote(event['pathParameters']['library'])
splash = urllib.parse.unquote(event['pathParameters']['splash'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT id '
'FROM compound '
'WHERE NOT hidden '
'AND splash=%(splash)s '
'AND method=%(library)s '
'AND version=%(version)s ', conn, {
'splash': splash,
'library': library,
'version': version
})
if result is not None:
# compound with metadata exists
database.query(
'DELETE FROM compound_meta '
'WHERE target_id = %(tgt_id)s', conn, {'tgt_id': result[0][0]})
return no_content()
else:
return not_found()
def promote_adduct(event, context):
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'name' not in event['pathParameters']:
return invalid_request('missing "adduct" path parameter')
library = urllib.parse.unquote(event['pathParameters']['library'])
splash = urllib.parse.unquote(event['pathParameters']['splash'])
adduct = urllib.parse.unquote(event['pathParameters']['name'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT exists (SELECT 1 FROM compound c '
'WHERE NOT c.hidden '
'AND c.method = %(library)s '
'AND c.splash=%(splash)s '
'AND c.version=%(version)s)', conn, {
'library': library,
'splash': splash,
'version': version
})
if result[0][0] == 0:
return not_found()
else:
try:
response = database.query(
'UPDATE compound SET adduct = %(adduct)s '
'WHERE method=%(library)s '
'AND splash=%(splash)s '
'AND version=%(version)s', conn, {
'adduct': adduct,
'library': library,
'splash': splash,
'version': version
})
# create a response
return {
'statusCode':
200,
'headers':
headers.__HTTP_HEADERS__,
'body':
json.dumps(
{
'library': library,
'splash': splash,
'version': version,
'adduct': adduct
},
use_decimal=True)
}
except Exception as ex:
traceback.print_exc()
return server_error(ex)
def promote_name(event, context):
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
if 'name' not in event['pathParameters']:
return invalid_request('missing "name" path parameter')
method_name = urllib.parse.unquote(event['pathParameters']['library'])
splash = urllib.parse.unquote(event['pathParameters']['splash'])
name = urllib.parse.unquote(event['pathParameters']['name'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT exists (SELECT 1 FROM compound c '
'WHERE NOT c.hidden '
'AND method = %(method)s '
'AND splash = %(splash)s '
'AND version = %(version)s)', conn, {
'method': method_name,
'splash': splash,
'version': version
})
if result[0][0] == 0:
return not_found()
else:
try:
database.query(
'UPDATE compound c SET name = %(name)s '
'WHERE method = %(method)s '
'AND splash = %(splash)s '
'AND version = %(version)s', conn, {
'name': name,
'method': method_name,
'splash': splash,
'version': version
})
# create a response
return {
'statusCode':
200,
'headers':
headers.__HTTP_HEADERS__,
'body':
json.dumps(
{
'library': method_name,
'splash': splash,
'version': version,
'name': name
},
use_decimal=True)
}
except Exception as ex:
return server_error(ex)
def delete_adduct(event, context):
"""
:param event:
:param context:
:return:
"""
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'name' not in event['pathParameters']:
return invalid_request('missing "name" path parameter')
if 'identifiedBy' not in event['pathParameters']:
return invalid_request('missing "identifiedBy" path parameter')
splash = urllib.parse.unquote(event['pathParameters']['splash'])
library = urllib.parse.unquote(event['pathParameters']['library'])
identified_by = urllib.parse.unquote(
event['pathParameters']['identifiedBy'])
name = urllib.parse.unquote(event['pathParameters']['name'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT DISTINCT ca.id, c.id, c.name '
'FROM compound c, compound_adduct ca '
'WHERE NOT c.hidden '
'AND c.id = ca.target_id '
'AND splash = %(splash)s '
'AND method = %(method)s '
'AND version = %(version)s '
'AND ca.name = %(name)s '
'AND identified_by = %(identified_by)s', conn, {
'splash': splash,
'method': library,
'name': name,
'identified_by': identified_by,
'version': version
})
if result is not None:
adduct_id = result[0][0]
target_id = result[0][1]
target_name = result[0][2]
if name == target_name:
database.query(
"UPDATE compound SET adduct = NULL WHERE id = %(target_id)s",
conn, {'target_id': target_id})
database.query("DELETE FROM compound_adduct WHERE id = %(adduct_id)s",
conn, {'adduct_id': adduct_id})
return no_content()
else:
return not_found()
def delete_meta(event, context):
'''
deletes a single metadata item from a specific compound, matched by name and value.
:param event:
:param context:
:return:
'''
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')
if 'name' not in event['pathParameters']:
return compounds.invalid_request('missing "name" path parameter')
if 'value' not in event['pathParameters']:
return compounds.invalid_request('missing "value" path parameter')
if 'identifiedBy' not in event['pathParameters']:
return compounds.invalid_request(
'missing "identifiedBy" path parameter')
library = 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'])
value = urllib.parse.unquote(event['pathParameters']['value'])
identifiedBy = urllib.parse.unquote(
event['pathParameters']['identifiedBy'])
result = database.query(
f'SELECT id, splash, method, version '
f'FROM compound '
f'WHERE NOT hidden '
f'AND method = %(method)s '
f'AND splash = %(splash)s '
f'AND version = %(version)s', conn, {
'splash': splash,
'method': library,
'version': version
})
if result is not None:
try:
database.query(
'DELETE FROM compound_meta '
'WHERE target_id = %(tgt_id)s '
'AND name = %(name)s '
'AND value = %(value)s '
'AND identified_by = %(identifiedBy)s ', conn, {
'tgt_id': result[0][0],
'name': name,
'value': value,
'identifiedBy': identifiedBy
})
except Exception as ex:
return server_error(ex)
return no_content()
def process_event(events, query_str):
if 'method' not in events['pathParameters']:
return invalid_request('missing or invalid method path parameter')
method = urllib.parse.unquote(events['pathParameters']['method'])
version = urllib.parse.unquote(events['pathParameters'].get('version'))
# check if they request a compound or a sample
if 'object_type' not in events['pathParameters'] or \
events['pathParameters']['object_type'] not in ['target', 'sample']:
return invalid_request(
'missing or invalid object type, it should be <target|sample>')
else:
object_type = urllib.parse.unquote(
events['pathParameters']['object_type'])
# get the splash or sample name for query depending on 'object_type'
if 'value' not in events['pathParameters']:
if object_type == 'target':
return invalid_request('missing splash as value path parameter')
else:
return invalid_request(
'missing sample name as value path parameter')
else:
value = urllib.parse.unquote(events['pathParameters']['value'])
filters = {'value': value, 'version': version, 'method': method}
if object_type == 'target':
query = query_str.replace('@column_name@', 'splash')
else:
query = query_str.replace('@column_name@', 'sample')
try:
caller = inspect.stack()[1][3]
result = database.query(query, conn, filters)
code = http.HTTPStatus.NOT_FOUND if result is None else http.HTTPStatus.OK
# create a response
return {
'statusCode':
code,
'headers':
headers.__HTTP_HEADERS__,
'body':
json.dumps(
{
caller: result,
'object_type': object_type,
'filters': filters
},
use_decimal=True)
}
except Exception as ex:
return server_error(ex)
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 list_adducts(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']).strip()
if mode.lower() == 'positive':
return {
'statusCode': http.HTTPStatus.OK,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps(ADDUCTS['positive'])
}
elif mode.lower() == 'negative':
return {
'statusCode': http.HTTPStatus.OK,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps(ADDUCTS['negative'])
}
else:
return {
'statusCode': http.HTTPStatus.BAD_REQUEST,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps({'error': 'Invalid mode'})
}
def delete_all_status(event, context):
"""
deletes all clean or dirty flag for a given target's spectrum
:param event:
:param context:
:return:
"""
if 'tgt_id' not in event['pathParameters']:
return invalid_request("Missing path parameters 'tgt_id'")
tgt_id = event['pathParameters']['tgt_id']
# splash = urllib.parse.unquote(events['pathParameters']['splash'])
# method = urllib.parse.unquote(events['pathParameters']['method'])
# version = urllib.parse.unquote(events['pathParameters']['version'])
try:
database.query(
'DELETE FROM compound_spectrum_quality '
'WHERE target_id = %(tgt_id)s',
# 'AND target_splash = %(splash)s '
# 'AND target_method = %(method)s '
# 'AND target_version = %(version)s',
conn,
{'tgt_id': tgt_id
}) # , 'splash': splash, 'method': method, 'version': version})
return no_content()
except Exception as ex:
return server_error(ex)
def delete_comment(event, context):
"""
:param event:
:param context:
:return:
"""
# 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')
if 'identifiedBy' not in event['pathParameters']:
return compounds.invalid_request('missing "identifiedBy" path parameter')
# library = urllib.parse.unquote(event['pathParameters']['library'])
# splash = urllib.parse.unquote(event['pathParameters']['splash'])
# version = urllib.parse.unquote(event['pathParameters'].get('version', 'fixed'))
identifiedBy = urllib.parse.unquote(event['pathParameters']['identifiedBy'])
comment_id = urllib.parse.unquote(event['pathParameters']['comment_id'])
try:
database.query(
"DELETE FROM compound_comment WHERE id = %(comment_id)s AND identified_by = %(identifiedBy)s",
conn, {'comt_id': comment_id, 'identifiedBy': identifiedBy})
except Exception as ex:
return server_error(ex)
return no_content()
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 duplicates_of_compound(event, context):
"""
Lists all duplicate splashes for a given compound
:param event:
:param context:
:return:
"""
if 'library' not in event['pathParameters']:
return invalid_request('Missing library path parameter')
if 'splash' not in event['pathParameters']:
return invalid_request('Missing splash path parameter')
library = urllib.parse.unquote(event['pathParameters'].get('library', ''))
splash = urllib.parse.unquote(event['pathParameters'].get('splash', ''))
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT id, method, splash, version FROM compound '
'WHERE duplicate_of = ('
' SELECT id FROM compound '
' WHERE method = %(library)s '
' AND splash = %(splash)s '
' AND version = %(version)s '
')',
conn,
params={
'library': library,
'splash': splash,
'version': version
})
if result is None:
return not_found()
result = [{
'id': x[0],
'library': x[1],
'splash': x[2],
'version': x[3]
} for x in result]
return {
'statusCode': http.HTTPStatus.OK,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps(result)
}
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 delete_names(event, context):
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
splash = urllib.parse.unquote(event['pathParameters']['splash'])
library = urllib.parse.unquote(event['pathParameters']['library'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT id, pre_cursors_mass, retention_index '
'FROM compound '
'WHERE NOT hidden '
'AND splash = %(splash)s '
'AND method = %(method)s '
'AND version = %(version)s', conn, {
'splash': splash,
'method': library,
'version': version
})
if result is not None:
target_id = result[0][0]
target_mz = result[0][1]
target_ri = result[0][2]
unk_name = f'unknown_{target_mz:.4f}_{target_ri:.4f}'
# compound with adducts exists
database.query(
'DELETE FROM compound_name WHERE target_id = %(tgt_id)s', conn,
{'tgt_id': target_id})
database.query(
'UPDATE compound SET name = %(unk_name)s WHERE id = %(tgt_id)s',
conn, {
'unk_name': unk_name,
'tgt_id': target_id
})
return no_content()
else:
return not_found()
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 delete_comments(event, context):
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
library = urllib.parse.unquote(event['pathParameters']['library'])
splash = urllib.parse.unquote(event['pathParameters']['splash'])
version = urllib.parse.unquote(event['pathParameters'].get('version', 'fixed'))
try:
database.query(
'DELETE FROM compound_comment '
'WHERE target_splash = %(splash)s '
'AND target_method = %(method)s '
'AND target_version = %(version)s',
conn, {'splash': splash, 'method': library, 'version': version})
except Exception as ex:
return server_error(ex)
return no_content()
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 delete(event, context):
""" hide istd """
if 'id' not in event['pathParameters']:
return compounds.invalid_request('Missing "id" pathParameter')
istd_id = int(urllib.parse.unquote(event['pathParameters'].get('id', '0')))
try:
database.query('UPDATE compound SET hidden = true WHERE id = %(id)s',
conn, {'id': istd_id})
return {
'statusCode': http.HTTPStatus.NO_CONTENT,
'headers': headers.__HTTP_HEADERS__
}
except Exception as ex:
return compounds.server_error(ex)
def save(event, context):
"""create / update"""
obj = json.loads(event['body'])
obj['created'] = event['requestContext']['requestTime']
try:
if event['httpMethod'] == 'POST':
# create / post
new_data = database.query(
"INSERT INTO compound (id, method, splash, version, accurate_mass, adduct, hidden, inchi_key, "
"ion_mode, msms, name, pre_cursors_mass, retention_index, sample, target_type, created, updated) "
"VALUES (nextval('compound_id'), %(method)s, %(splash)s, %(version)s, %(accurate_mass)s, %(adduct)s, "
"%(hidden)s, %(inchi_key)s, %(ion_mode)s, %(msms)s, %(name)s, %(pre_cursors_mass)s, "
"%(retention_index)s, %(sample)s, 'ISTD', %(created)s, %(created)s) "
'RETURNING id, method, splash, version, accurate_mass, name, adduct, hidden, inchi_key, ion_mode, '
'msms, pre_cursors_mass, retention_index, target_type, sample',
conn, obj)
new_istd = list(map(istd_transform, new_data))[0]
return {
'statusCode': http.HTTPStatus.CREATED,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps(new_istd, use_decimal=True)
}
elif event['httpMethod'] == 'PUT':
# update / put
database.query(
'UPDATE compound SET method = %(method)s, splash = %(splash)s, version = %(version)s, '
'accurate_mass = %(accurate_mass)s, adduct = %(adduct)s, hidden = %(hidden)s, '
'inchi_key = %(inchi_key)s, ion_mode = %(ion_mode)s, msms = %(msms)s, name = %(name)s, '
'pre_cursors_mass = %(pre_cursors_mass)s, retention_index = %(retention_index)s '
'WHERE id = %(id)s', conn, obj)
return {
'statusCode': http.HTTPStatus.OK,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps(obj, use_decimal=True)
}
else:
return compounds.invalid_request('Invalid request method')
except Exception as ex:
print(str(ex))
return compounds.server_error(ex)
def compounds_with_duplicates(event, context):
"""
Lists splash keys that have duplicated compounds.
Use compound.get on each splash to get all compound info
:param event:
:param context:
:return:
"""
if 'library' not in event['pathParameters']:
return invalid_request('Missing library path parameter')
library = urllib.parse.unquote(event['pathParameters'].get('library', ''))
result = database.query(
'SELECT id, method, splash, version FROM compound '
'WHERE method = %(library)s '
'AND id in ('
'SELECT duplicate_of FROM compound WHERE duplicate_of IS NOT NULL'
')',
conn,
params={'library': library})
if result is None:
return not_found()
result = [{
'id': x[0],
'library': x[1],
'splash': x[2],
'version': x[3]
} for x in result]
return {
'statusCode': http.HTTPStatus.OK,
'headers': headers.__HTTP_HEADERS__,
'body': json.dumps(result)
}
def register_adduct(event, context):
"""
registers a new adduct for a given target
:param event:
:param context:
:return:
"""
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
if 'name' not in event['pathParameters']:
return invalid_request('missing "name" path parameter')
if 'identifiedBy' not in event['pathParameters']:
return invalid_request('missing "identifiedBy" path parameter')
library = urllib.parse.unquote(event['pathParameters']['library'])
splash = urllib.parse.unquote(event['pathParameters']['splash'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
identified_by = urllib.parse.unquote(
event['pathParameters']['identifiedBy'])
name = urllib.parse.unquote(event['pathParameters']['name'])
comment = ''
body = event.get('body', '')
if body:
tmp = json.loads(body)
comment = tmp.get('comment', '').strip()
# load compound to get the correct id
result = database.query(
'SELECT id, splash, method, version '
'FROM compound '
'WHERE NOT hidden '
'AND splash = %(splash)s '
'AND method = %(method)s '
'AND version = %(version)s', conn, {
'splash': splash,
'method': library,
'version': version
})
if result is None:
return not_found()
else:
tgt_id = result[0][0]
# check if adduct exists
a_result = database.query(
'SELECT id '
'FROM compound_adduct ca '
'WHERE target_id = %(tgt_id)s '
'AND name = %(name)s '
'AND identified_by = %(identified_by)s', conn, {
'tgt_id': tgt_id,
'splash': splash,
'method': library,
'name': name,
'identified_by': identified_by,
'version': version
})
if a_result is None:
# now register the given name and associated information
# 1. get new sequence number
result = database.query("select nextval('adducts_seq')", conn)
new_adduct_id = result[0][0]
database.query(
'INSERT INTO compound_adduct(id, name, identified_by, comment, '
'target_id, target_method, target_splash, target_version) '
'VALUES(%(new_id)s,%(name)s,%(identified_by)s, %(comment)s, '
'%(tgt_id)s,%(tgt_method)s,%(tgt_splash)s,%(tgt_version)s)',
conn, {
'new_id': new_adduct_id,
'name': name,
'identified_by': identified_by,
'comment': comment,
'tgt_id': tgt_id,
'tgt_method': library,
'tgt_splash': splash,
'tgt_version': version
})
# create a response
return no_content()
def register_status(event, context):
"""
registers a new clean or dirty flag for a given target's spectrum
: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:
return invalid_request('Missing parameters')
if 'clean' not in event['queryStringParameters']:
return invalid_request('Missing parameters "clean"')
if event['queryStringParameters']['clean'] not in statusDic.keys():
return invalid_request(
'Invalid query parameter "clean", must be "true" or "false"')
if 'identifiedBy' not in event['queryStringParameters']:
return invalid_request('Missing parameter "identifiedBy"')
clean = statusDic[event['queryStringParameters']['clean']]
identified_by = urllib.parse.unquote(
event['queryStringParameters']['identifiedBy'])
try:
# check if the user already marked this spectrum, possibly updating the 'clean' value
query = 'SELECT * FROM compound_spectrum_quality ' \
'WHERE target_id = %(tgt_id)s ' \
'AND identified_by = %(identified_by)s'
exists = database.query(query, conn, {
'tgt_id': tgt_id,
'identified_by': identified_by
})
# 0. get compound info
cmp = database.query(
'SELECT method, splash, version FROM compound WHERE id = %(tgt_id)s',
conn, {'tgt_id': tgt_id})
# now add the flag for the specified target
if exists is None or len(exists) < 1:
# 1. get new sequence number
result = database.query("SELECT nextval('specquality_seq')", conn)
new_status_id = result[0][0]
# 2a. add record if user didn't mark this target
query = 'INSERT INTO compound_spectrum_quality(id, clean, target_id, identified_by, ' \
'target_method, target_splash, target_version) ' \
'VALUES (%(id)s, %(clean)s, %(tgt_id)s, %(identifiedBy)s, %(method)s, %(splash)s, %(version)s)'
else:
# 2b. update record if user marked this target
new_status_id = exists[0][0]
query = 'UPDATE compound_spectrum_quality SET clean = %(clean)s ' \
'WHERE id = %(id)s AND target_id = %(tgt_id)s AND identified_by = %(identifiedBy)s'
database.query(
query, conn, {
'clean': clean,
'id': new_status_id,
'tgt_id': tgt_id,
'identifiedBy': identified_by,
'method': cmp[0][0],
'splash': cmp[0][1],
'version': cmp[0][2]
})
# create a response
return {
'statusCode':
200,
'headers':
headers.__HTTP_HEADERS__,
'body':
json.dumps(
{
'id': new_status_id,
'clean': clean,
'target_id': tgt_id,
'identifiedBy': identified_by
},
use_decimal=True)
}
except Exception as ex:
return server_error(ex)
def register_comment(event, context):
"""
registers a new comment for a given target.
:param event:
:param context:
:return:
"""
if 'splash' not in event['pathParameters']:
return compounds.invalid_request('missing "splash" path parameter')
if 'library' not in event['pathParameters']:
return compounds.invalid_request('missing "library" path parameter')
if 'identifiedBy' not in event['pathParameters']:
return compounds.invalid_request('missing "identifiedBy" path parameter')
if event['body'] is None or event['body'].strip() is '':
return compounds.invalid_request('request body is empty, it should contain the comment')
splash = urllib.parse.unquote(event['pathParameters']['splash'])
library = urllib.parse.unquote(event['pathParameters']['library'])
identified_by = urllib.parse.unquote(event['pathParameters']['identifiedBy'])
version = urllib.parse.unquote(event['pathParameters'].get('version', 'fixed'))
comment = ''
body = event.get('body', '')
if body:
tmp = json.loads(body)
comment = tmp.get('comment', '').strip()
# load compound AND comments to get the correct id
result = database.query(
'SELECT id, splash, method, version '
'FROM compound '
'WHERE NOT hidden '
'AND splash = %(splash)s '
'AND method = %(method)s '
'AND version = %(version)s',
conn, {'splash': splash, 'method': library, 'version': version})
if result is None:
return not_found()
else:
tgt_id = result[0][0]
# now register the given name and associated information
c_result = database.query("select nextval('comments_seq')", conn)
new_comment_id = c_result[0][0]
try:
database.query(
'INSERT INTO compound_comment(id, identified_by, comment, target_id, '
'target_method, target_splash, target_version) '
'VALUES(%(new_id)s, %(identified_by)s, %(comment)s, '
'%(tgt_id)s, %(tgt_method)s, %(tgt_splash)s, %(tgt_version)s)',
conn, {'new_id': new_comment_id, 'identified_by': identified_by, 'comment': comment,
'tgt_id': tgt_id, 'tgt_method': library, 'tgt_splash': splash, 'tgt_version': version})
except Exception as ex:
return server_error(ex)
# create a response
return no_content()
def register_meta(event, context):
"""
registers a new adduct for a given target
:param event:
:param context:
:return:
"""
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')
if 'name' not in event['pathParameters']:
return compounds.invalid_request('missing "name" path parameter')
if 'value' not in event['pathParameters']:
return compounds.invalid_request('missing "value" path parameter')
if 'identifiedBy' not in event['pathParameters']:
return compounds.invalid_request(
'missing "identifiedBy" path parameter')
library = 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'])
value = urllib.parse.unquote(event['pathParameters']['value'])
identifiedBy = urllib.parse.unquote(
event['pathParameters']['identifiedBy'])
comment = ''
body = event.get('body', None)
if body is not None and len(body.strip()):
comment = urllib.parse.unquote(body.strip())
# load compound to get the correct id
result = database.query(
'SELECT id, splash, method, version '
'FROM compound '
'WHERE splash = %(splash)s '
'AND method = %(method)s '
'AND version = %(version)s', conn, {
'splash': splash,
'method': library,
'version': version
})
if result is None:
return not_found()
else:
tgt_id = result[0][0]
# check if metadata exists with same name and value
m_result = database.query(
'SELECT id '
'FROM compound_meta '
'WHERE NOT rejected '
'AND target_id = %(tgt_id)s '
'AND lower(name) = lower(%(name)s) '
'AND lower(value) = lower(%(value)s) '
'AND identified_by = %(identifiedBy)s ', conn, {
'tgt_id': tgt_id,
'name': name,
'value': value,
'identifiedBy': identifiedBy
})
if m_result is None:
# now register the given metadata and associated information
new_result = database.query("select nextval('metas_seq')", conn)
new_meta_id = new_result[0][0]
result = database.query(
'INSERT INTO compound_meta(id, name, value, identified_by, comment, '
'target_id, target_method, target_splash, target_version) '
'VALUES(%(new_id)s, %(name)s, %(value)s, %(identified_by)s, %(comment)s, '
'%(tgt_id)s, %(tgt_method)s, %(tgt_splash)s, %(tgt_version)s)',
conn, {
'new_id': new_meta_id,
'name': name,
'value': value,
'identified_by': identifiedBy,
'comment': comment,
'tgt_id': tgt_id,
'tgt_method': library,
'tgt_splash': splash,
'tgt_version': version
})
else:
# skip duplicating metadata with same name and value
logger.warning(
f'Compound {splash} already contains metadata {name} with value {value}, skipping.'
)
return no_content()
def delete_name(event, context):
"""
:param event:
:param context:
:return:
"""
if 'splash' not in event['pathParameters']:
return invalid_request('missing "splash" path parameter')
if 'library' not in event['pathParameters']:
return invalid_request('missing "library" path parameter')
if 'name' not in event['pathParameters']:
return invalid_request('missing "name" path parameter')
if 'identifiedBy' not in event['pathParameters']:
return invalid_request('missing "identifiedBy" path parameter')
splash = urllib.parse.unquote(event['pathParameters']['splash'])
library = urllib.parse.unquote(event['pathParameters']['library'])
identified_by = urllib.parse.unquote(
event['pathParameters']['identifiedBy'])
name = urllib.parse.unquote(event['pathParameters']['name'])
version = urllib.parse.unquote(event['pathParameters'].get(
'version', 'fixed'))
result = database.query(
'SELECT DISTINCT cn.id, c.id, c.name, c.pre_cursors_mass, c.retention_index '
'FROM compound c, compound_name cn '
'WHERE NOT c.hidden '
'AND c.id = cn.target_id '
'AND splash = %(splash)s '
'AND method = %(method)s '
'AND version = %(version)s '
'AND cn.name = %(name)s '
'AND identified_by = %(identified_by)s', conn, {
'splash': splash,
'method': library,
'name': name,
'identified_by': identified_by,
'version': version
})
if result is not None:
name_id = result[0][0]
target_id = result[0][1]
target_name = result[0][2]
target_mz = result[0][3]
target_ri = result[0][4]
unk_name = f'unknown_{target_mz:.4f}_{target_ri:.4f}'
if name == target_name:
database.query(
"UPDATE compound "
"SET name = %(unk_name)s "
"WHERE id = %(tgt_id)s", conn, {
'unk_name': unk_name,
'target_id': target_id
})
database.query('DELETE FROM compound_name WHERE id = %(name_id)s',
conn, {'name_id': name_id})
return no_content()
else:
return not_found()
