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