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 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 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 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 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_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 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_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 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()