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