def get_tree():
"""
get:
summary: Get RockMaker tree nodes
description: Obtain the RockMaker tree, optionally limited to a top-level folder, or by a parentID to
allow for lazy-loading of a tree element
parameters:
- name: source_id
description: primary key in POLO database for RockMaker instance to search. Default: 1
type: integer
required: false
- name: folder
description: name of top-level folder used to limit search
type: string
required: false
- name: parentId
description: name of top-level folder used to limit search, default: 3
type: string
required: false
responses:
200:
description: A JSON object containing tree information below specified or default node
"""
source_id = request.args.get('source_id')
if source_id is None or source_id == 'undefined':
source_id = 1
else:
source_id = int(source_id)
rm_connection = rm_engines[source_id].connect()
# default
start_node = 3
# if folder is set, determine root node from that...
folder = request.args.get('folder') or request.args.get('isid')
if folder is not None:
try:
resultproxy = rm_connection.execute(sql_tree_user_node, folder)
start_node = get_result(resultproxy)[0]['id']
except:
start_node = 3
# but if parentId is specified, use that instead. If neither specified, use node_id = 3
# node_id = 3 is the "Projects" level in RockMaker
parent_id = request.args.get('parentId') or request.args.get('parent_id') or request.args.get('ParentID')
if parent_id is not None:
start_node = parent_id
resultproxy = rm_connection.execute(sql_tree, start_node)
nodes = get_result(resultproxy)
rm_connection.close()
# OBFUSCATION FOR DEMO PURPOSES
if 'demo' in request.args or app.config['DEMO']:
for node in nodes:
if node['node_type'] != 'ExperimentPlate':
node['name'] = 'XXXXX'
return jsonify(nodes)
def toggle_favorite():
"""
Under construction
"""
image_score_id = request.form.get('image_score_id')
favorited_by = request.form.get('favorited_by')
if image_score_id and favorited_by:
connection = polo_engine.connect()
sql = 'select plate_id, well_num, drop_num from image_scores where id = %s'
resultproxy = connection.execute(sql, (int(image_score_id)))
result = get_result(resultproxy)[0]
plate_id = result['plate_id']
well_num = result['well_num']
drop_num = result['drop_num']
else:
connection.close()
return abort(400, "Need to POST key/value pairs for image_score_id and favorited_by")
if plate_id and well_num and drop_num:
sql = 'select count(id) as count from favorites where plate_id = %s and well_num = %s and drop_num = %s and favorited_by = %s'
resultproxy = connection.execute(sql, (plate_id, well_num, drop_num, favorited_by))
result = get_result(resultproxy)[0]
connection.close()
return jsonify(result)
def get_sources():
"""
get:
summary: Get list of configured RockMaker instances
description: Return list of RockMaker source_id values and their associated information,
including name and url_prefix for constructing URLs to images and date of
last update
parameters:
responses:
200:
description: JSON object with information for all configured RockMaker instances
"""
polo_connection = polo_engine.connect()
sql = "select * from sources"
resultproxy = polo_connection.execute(sql)
sources = get_result(resultproxy)
# add last score date from each source to the source name.
# This *should* be done in a single query, but it's been too slow.
sql = """
select DATE_FORMAT(i.date_scored, '%%d-%%b-%%Y %%H:%%i') d
from image_scores i
where i.source_id = %s
order by date_scored DESC
LIMIT 1
"""
for source in sources:
resultproxy = polo_connection.execute(sql, source['id'])
source['name'] += " ({})".format(get_result(resultproxy)[0]['d'])
sources[0]['selected'] = True
polo_connection.close()
return jsonify(sources)
def get_timecourse():
"""
get:
summary: Get visible image data for a plate/well/drop
description: Obtain the list of all visible images for a source/plate/well/drop combination scored
using a specified algorithm
parameters:
- name: source_id
description: primary key in POLO database for RockMaker instance to search. Default: 1
type: integer
required: false
- name: algo_name
description: Show scores from this algorithm. Default: first one detected
type: string
required: false
- name: plate_id
description: Plate ID, same as RockMaker Plate.ID
type: integer
required: true
- name: well_num
description: Well number, same as RockMaker Well.WellNumber
type: integer
required: true
- name: drop_num
description: Drop number, same as RockMaker WellDrop.DropNumber
type: integer
required: true
responses:
200:
description: JSON list of image metadata
"""
source_id = request.args.get('source_id')
if source_id is None:
source_id = 1
else:
source_id = int(source_id)
plate_id = request.args.get('plate_id')
well_num = request.args.get('well_num')
drop_num = request.args.get('drop_num')
algo_name = request.args.get('algo_name')
if algo_name is None:
sql = "select distinct scored_by from image_scores"
resultproxy = polo_connection.execute(sql)
algo_name = get_result(resultproxy)[0]['scored_by']
polo_connection = polo_engine.connect()
resultproxy = polo_connection.execute(sql_timecourse, source_id, algo_name,
int(plate_id), int(well_num),
int(drop_num))
scores = get_result(resultproxy)
polo_connection.close()
return jsonify(scores)
def get_other():
"""
get:
summary: Get all image data for a plate/well/drop
description: Obtain the list of all images (including visible, UV, etc.) for a source/plate/well/drop combination
parameters:
- name: source_id
description: primary key in POLO database for RockMaker instance to search. Default: 1
type: integer
required: false
- name: plate_id
description: Plate ID, same as RockMaker Plate.ID
type: integer
required: true
- name: well_num
description: Well number, same as RockMaker Well.WellNumber
type: integer
required: true
- name: drop_num
description: Drop number, same as RockMaker WellDrop.DropNumber
type: integer
required: true
responses:
200:
description: JSON list of image metadata
"""
source_id = request.args.get('source_id')
if source_id is None:
source_id = 1
else:
source_id = int(source_id)
plate_id = request.args.get('plate_id')
well_num = request.args.get('well_num')
drop_num = request.args.get('drop_num')
polo_connection = polo_engine.connect()
resultproxy = polo_connection.execute(sql_image_prefix, source_id)
prefix = get_result(resultproxy)[0]['prefix']
rm_connection = rm_engines[source_id].connect()
resultproxy = rm_connection.execute(sql_other_images, prefix,
int(plate_id), int(well_num),
int(drop_num))
scores = get_result(resultproxy)
rm_connection.close()
return jsonify(scores)
def get_annotations():
"""
get:
summary: Return all manually-annotated image metadata
description: Return all manually-annotated image metadata
parameters:
responses:
200:
description: JSON-formatted information
"""
# TODO add filters for CPXOD, limit by plate_id(s)
# allow download in different formats, including JSON and CSV
results = {}
errors = []
output_format = request.args.get('format')
if output_format is None:
output_format = 'JSON'
else:
output_format = output_format.upper()
if output_format not in ['JSON', 'CSV']:
errors.append("Format must be either JSON (default) or CSV")
return jsonify({'results': results, 'errors': errors})
try:
connection = polo_engine.connect()
sql = '''select plate_id, well_num, drop_num, scored_by, classification, disputed_by, manual_call,
CONCAT(url_prefix, relative_path) as url
from disputes d, image_scores i, sources s
where d.image_score_id = i.id
and i.source_id = s.id
'''
resultproxy = connection.execute(sql)
results = get_result(resultproxy)
connection.close()
except:
errors.append("Problem connecting to POLO database")
finally:
if 'connection' in locals():
connection.close()
if output_format == 'JSON':
return jsonify({'results': results, 'errors': errors})
elif output_format == 'CSV':
si = io.StringIO()
writer = csv.DictWriter(si,
dialect='excel',
fieldnames=['plate_id', 'well_num', 'drop_num', 'classification', 'manual_call',
'scored_by', 'disputed_by', 'url']
)
writer.writeheader()
for result in results:
writer.writerow(result)
output = make_response(si.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=POLO_annotations.csv"
output.headers["Content-type"] = "text/csv"
return output
def status():
"""
get:
summary: Return JSON with information about all of the connected databases to show
that this web application not only works, but can also connect to the
dependent databases
description: response indicates application is running and that database connections are valid
parameters:
responses:
200:
description: JSON with information about number of database records, containing an errors
key with error information
"""
results = {}
errors = []
# POLO database
try:
connection = polo_engine.connect()
sql = 'select count(*) as c from image_scores'
resultproxy = connection.execute(sql)
results['polo_score_count'] = get_result(resultproxy)[0]['c']
connection.close()
except:
errors.append("Problem connecting to POLO database")
finally:
if 'connection' in locals():
connection.close()
# RockMaker databases
for rm_engine_key in rm_engines.keys():
try:
connection = rm_engines[rm_engine_key].connect()
sql = 'select count(*) as c from Plate'
resultproxy = connection.execute(sql)
results[f'rm_{rm_engine_key}_plate_count'] = get_result(
resultproxy)[0]['c']
connection.close()
except:
errors.append(
f"Problem connecting to RockMaker database {rm_engine_key}")
finally:
if 'connection' in locals():
connection.close()
return jsonify({'results': results, 'errors': errors})
def get_image_info():
"""
get:
summary: Get information for a specific image
description: Get image type and pixel size
parameters:
- name: source_id
description: primary key in POLO database for RockMaker instance to search. Default: 1
type: integer
required: false
- name: region_id
description:
type: integer
required: true
- name: batch_id
description:
type: integer
required: true
- name: profile_id
description:
type: integer
required: true
responses:
200:
description: JSON hash of image metadata, including pixel_size (microns per pixel)
"""
source_id = request.args.get('source_id')
if source_id is None:
source_id = 1
else:
source_id = int(source_id)
try:
region_id = request.args.get('region_id')
profile_id = request.args.get('profile_id')
batch_id = request.args.get('batch_id')
rm_connection = rm_engines[source_id].connect()
resultproxy = rm_connection.execute(sql_image_info, int(region_id), int(batch_id), int(profile_id))
info = get_result(resultproxy)[0]
info['region_id'] = region_id
info['profile_id'] = profile_id
info['batch_id'] = batch_id
return jsonify(info)
except TypeError:
return abort(400, "Must specify region_id, profile_id, and batch_id")
except KeyError:
return abort(400, "Must specify an existing source_id")
except IndexError:
return abort(400, "Possible mismatch between batch_id and region_id. Please double-check.")
finally:
rm_connection.close()
def dispute():
"""
post:
summary: Save a manual annotation for a single scored image
description: response indicates application is running and that database connections are valid
parameters:
- name: image_score_id
description: ID for the image_score to dispute
type: integer
required: true
- name: disputed_by
description: the username of the person manually annotating the score
type: string
required: true
- name: manual_call
description: a string representing the manual call, typically one character of 'CPXOD'
type: string
required: true
responses:
200:
description: A JSON object with the details of the inserted row, including ID
"""
image_score_id = request.form.get('image_score_id')
disputed_by = request.form.get('disputed_by')
manual_call = request.form.get('manual_call')
if image_score_id and disputed_by and manual_call:
connection = polo_engine.connect()
sql = 'insert into disputes (image_score_id, disputed_by, manual_call) values (%s, %s, %s)'
resultproxy = connection.execute(
sql, (int(image_score_id), disputed_by, manual_call))
# get last insert (including automatically-generated ID and DATE
sql = 'select * from disputes where id = LAST_INSERT_ID()'
resultproxy = connection.execute(sql)
entry = get_result(resultproxy)
connection.close()
return jsonify(entry)
else:
return abort(
400,
"Need to POST key/value pairs for image_score_id, disputed_by, and manual_call"
)
def get_algorithms():
"""
get:
summary: Return list of unique algorithms used for scoring images
description: Return list of unique algorithms used for scoring images
parameters:
responses:
200:
description: JSON object with information for all unique algorithms
"""
polo_connection = polo_engine.connect()
sql = "select scored_by as id, scored_by as name, count(scored_by) as count from image_scores group by scored_by"
resultproxy = polo_connection.execute(sql)
algos = get_result(resultproxy)
algos[0]['selected'] = True
for algo in algos:
algo['name'] += " ({:,})".format(algo['count'])
polo_connection.close()
return jsonify(algos)
def get_scores():
"""
get:
summary: Get scores for one or more plates, optionally filtered/sorted/paginated.
description: Obtain the list of all scores and other data for one or more plates. Sorting, filtering,
and pagination is supported.
- name: source_id
description: primary key in POLO database for RockMaker instance to search. Default: 1
type: integer
required: false
- name: algo_name
description: Show scores from this algorithm. Default: first one detected
type: string
required: false
- name: plate_ids
description: comma-delimited plate ids, expressed as RockMaker Plate.ID
type: string
required: true
- name: min_score
description: only return scores greater than this one (on "crystal" column)
type: float
required: false
- name: method
description: which score to use for a source/plate/well/drop: either "date_imaged" (for most recent) or
"crystal" (for highest crystal score). Default: date_imaged
type: string
required: false
- name: drop_num
description: comma-delimited image drop number(s) to filter on
type: string
required: false
- name: temperature
description: comma-delimited temperature values to filter on
type: string
required: false
- name: limit
description: how many scores to return. Default: 10
type: int
required: false
- name: page
description: which page to return. Default: 1
type: int
required: false
- name: sortBy
description: which column to sort on. Default: crystal
type: string
required: false
- name: direction
description: which direction to sort. Default: DESC
type: string
required: false
- name: timecourse
description: If "true" then include info for sparklines. Default: true
type: string
required: false
responses:
200:
description: JSON list of scores and associated metadata
"""
method = request.args.get('method')
if method is None:
method = "date_imaged"
source_id = request.args.get('source_id')
if source_id is None:
source_id = 1
else:
source_id = int(source_id)
algo_name = request.args.get('algo_name') # handle case when None below
min_score = request.args.get('min_score')
if min_score is None:
min_score = 0.0
else:
min_score = float(min_score)
timecourse = request.args.get('timecourse')
if timecourse is None or timecourse == "true":
timecourse = True
else:
timecourse = False
plate_ids = request.args.get('plate_ids').split(',')
if plate_ids == ['']:
return jsonify([])
# DATABASE FILTERS
try:
drop_num = request.args.get('drop_num')
if drop_num is not None and len(drop_num) > 0:
# avoid injection attack by converting to integers, and back to comma-separated
drops = ','.join([str(int(d)) for d in re.split(r'\s*,\s*|\s+', drop_num)])
drop_filter = "AND drop_num IN ({})".format(drops) # allow to select multiple through use of commas
else:
drop_filter = ''
except:
drop_filter = ''
try:
temperature = request.args.get('temperature')
if temperature is not None and len(temperature) > 0:
# avoid injection attack by converting to integers, and back to comma-separated
temperatures = ','.join([str(int(d)) for d in re.split(r'\s*,\s*|\s+', temperature)])
temperature_filter = "AND temperature IN ({})".format(
temperatures) # allow to select multiple through use of commas
else:
temperature_filter = ''
except:
temperature_filter = ''
# POST-DATABASE FILTER
# conditions = request.args.get('conditions')
# if conditions is not None:
# condition_filter = "AND conditions LIKE '%{}%".format(conditions)
plate_ids = [int(plate_id) for plate_id in plate_ids]
limit = request.args.get('limit', 10)
page = request.args.get('page', 1)
sort_by = request.args.get('sortBy', 'crystal')
direction = request.args.get('direction', 'DESC')
if sort_by == 'well_name':
sort_by = 'well_num'
if sort_by == 'plate_name':
sort_by = 'plate_id'
offset = (int(page) - 1) * int(limit)
sort_dir = f'{sort_by} {direction}'
scores = request.args.getlist('scores[]')
if scores:
score_filter = 'AND id IN ({})'.format(",".join(scores))
else:
score_filter = ''
# MAKE DATABASE CONNECTIONS
polo_connection = polo_engine.connect()
rm_connection = rm_engines[source_id].connect()
if algo_name is None:
sql = "select distinct scored_by from image_scores"
resultproxy = polo_connection.execute(sql)
algo_name = get_result(resultproxy)[0]['scored_by']
# GET TOTAL IMAGE SCORES
query_placeholders = ','.join(['%s'] * len(plate_ids))
params = [source_id, algo_name]
params.extend(plate_ids)
resultproxy = polo_connection.execute(
sql_all.format(temperature_filter=temperature_filter, drop_filter=drop_filter, score_filter=score_filter,
rank_method=method, min_score=min_score, query_placeholders=query_placeholders), params)
total = len(get_result(resultproxy))
# SET SORT ORDER BASED AND GET PAGINATED REQUEST
polo_connection.execute(sql_set_sort_order, sort_dir)
polo_connection.execute(sql_set_pwd_rank)
params = [source_id, algo_name]
params.extend(plate_ids)
params.append(int(limit))
params.append(int(offset))
if timecourse:
sql = sql_paginated_with_timecourse
else:
sql = sql_paginated_without_timecourse
resultproxy = polo_connection.execute(
sql.format(temperature_filter=temperature_filter, drop_filter=drop_filter, score_filter=score_filter,
rank_method=method, min_score=min_score, query_placeholders=query_placeholders), params
)
scores = get_result(resultproxy)
try:
for plate_id in plate_ids:
resultproxy = rm_connection.execute(sql_conditions_protein, plate_id)
conditions = get_result(resultproxy)
for condition in conditions:
for score in scores:
if score['plate_id'] == plate_id and \
score['well_num'] == condition['well_num'] and \
score['drop_num'] == condition['drop_num']:
score['conditions'] = condition['conditions']
score['protein'] = condition['protein']
score['protein_notes'] = condition['protein_notes']
except IndexError:
print("IndexError getting conditions and/or protein for plate", plate_id)
polo_connection.close()
rm_connection.close()
# OBFUSCATION FOR DEMO PURPOSES
if 'demo' in request.args or app.config['DEMO']:
for score in scores:
score['protein'] = 'XXXXX'
return jsonify({'total': total, 'records': scores})
def search():
"""
get:
summary: Search a RockMaker instance for matching plate, barcode, or experiment name
description: Search a RockMaker instance for matching plate, barcode, or experiment name
parameters:
- name: source_id
description: primary key in POLO database for RockMaker instance to search. Default: 1
type: integer
required: false
- name: folder
description: name of top-level folder used to limit search
type: string
required: false
- name: q
description: query string, which can be split by spaces unless enclosed by quotation to find multiple plates
type: string
required: true
responses:
200:
description: A JSON object containing tree information for matching plates, barcodes, and experiments
"""
source_id = request.args.get('source_id')
if source_id is None:
source_id = 1
else:
source_id = int(source_id)
folder = request.args.get('folder') or request.args.get('isid')
query_string = request.args.get('q')
if query_string is None or query_string == '':
return jsonify([])
rm_connection = rm_engines[source_id].connect()
# split by space unless enclosed by quotation - use shlex.split (via https://stackoverflow.com/questions/79968/split-a-string-by-spaces-preserving-quoted-substrings-in-python)
parsed_query = shlex.split(query_string)
# only accept queries which are 3+ characters
limited_parsed_query = [p for p in parsed_query if len(p) > 2]
if limited_parsed_query == []:
return jsonify([])
matching_plates = []
for p in limited_parsed_query:
# MATCHING TREENODES VIA PLATE ID
resultproxy = rm_connection.execute(sql_nodes_from_matching_plateid, f'%{p}%')
m = get_result(resultproxy)
node_ids = [x['id'] for x in m]
if len(node_ids) > 0:
# get paths for matching plate nodes
query_placeholders = ','.join(['?'] * len(node_ids))
resultproxy = rm_connection.execute(
sql_path_to_experiments_via_nodeids.format(query_placeholders=query_placeholders), node_ids)
paths = get_result(resultproxy)
merged = merge_dicts(m, paths, 'id')
if folder:
merged = [x for x in merged if x['path'].startswith(folder)]
for x in merged:
x['node_type'] = 'ExperimentPlate'
if 'folder_name' in x:
# OBFUSCATION FOR DEMO PURPOSES
if 'demo' in request.args or app.config['DEMO']:
x['path'] = 'XXXXX'
x['folder_name'] = 'XXXXX'
if len(x['folder_name']) > 22:
x.update({'name': x['folder_name'][:20] + '... > ' + x['name']})
else:
x.update({'name': x['folder_name'] + ' > ' + x['name']})
matching_plates.extend(merged)
matching_barcodes = []
for p in limited_parsed_query:
# MATCHING TREENODES VIA PLATE BARCODE
resultproxy = rm_connection.execute(sql_nodes_from_matching_barcode, f'%{p}%')
m = get_result(resultproxy)
node_ids = [x['id'] for x in m]
if len(node_ids) > 0:
# get paths for matching plate nodes
query_placeholders = ','.join(['?'] * len(node_ids))
resultproxy = rm_connection.execute(
sql_path_to_experiments_via_nodeids.format(query_placeholders=query_placeholders), node_ids)
paths = get_result(resultproxy)
merged = merge_dicts(m, paths, 'id')
if folder:
merged = [x for x in merged if x['path'].startswith(folder)]
for x in merged:
x['node_type'] = 'ExperimentPlate'
if 'folder_name' in x:
# OBFUSCATION FOR DEMO PURPOSES
if 'demo' in request.args or app.config['DEMO']:
x['path'] = 'XXXXX'
x['folder_name'] = 'XXXXX'
if len(x['folder_name']) > 22:
x.update({'name': x['folder_name'][:20] + '... > ' + x['name']})
else:
x.update({'name': x['folder_name'] + ' > ' + x['name']})
matching_barcodes.extend(merged)
matching_experiments = []
for p in limited_parsed_query:
# MATCHING EXPERIMENTS VIA TEXT
resultproxy = rm_connection.execute(sql_path_to_experiments_like_text, f'%{p}%')
m = get_result(resultproxy)
app.logger.debug("Matching experiments {} {}".format(len(m), m))
node_ids = [x['id'] for x in m]
if len(node_ids) > 0:
# get plates for matching experiment nodes
query_placeholders = ','.join(['?'] * len(node_ids))
sql = sql_plates_from_experiment_nodes.format(query_placeholders=query_placeholders)
resultproxy = rm_connection.execute(sql, node_ids)
plates = get_result(resultproxy)
app.logger.debug("Plates {} {}".format(len(plates), plates))
merged = []
for plate in plates:
for match in m:
if plate['parent_id'] == match['id']:
plate['folder_name'] = match['folder_name']
plate['path'] = match['path']
merged.append(plate)
app.logger.debug("Merged {} {}".format(len(merged), merged))
if folder:
merged = [x for x in merged if x['path'].startswith(folder)]
for x in merged:
x['node_type'] = 'ExperimentPlate'
if 'folder_name' in x:
if 'name' not in x:
x['name'] = '?'
# OBFUSCATION FOR DEMO PURPOSES
if 'demo' in request.args or app.config['DEMO']:
x['path'] = 'XXXXX'
x['folder_name'] = 'XXXXX'
if len(x['folder_name']) > 22:
x.update({'name': x['folder_name'][:20] + '... > ' + x['name']})
else:
x.update({'name': x['folder_name'] + ' > ' + x['name']})
matching_experiments.extend(merged)
# MATCHING EXPERIMENTS VIA PROTEIN FORMULATION
matching_proteins = []
for p in limited_parsed_query:
# MATCHING PLATES VIA PROTEIN
resultproxy = rm_connection.execute(sql_plates_from_matching_protein, f'%{p}%')
m = get_result(resultproxy)
if folder:
m = [x for x in m if x['path'].startswith(folder)]
if len(m) > 0:
for x in m:
x['node_type'] = 'ExperimentPlate'
if 'folder_name' in x:
# OBFUSCATION FOR DEMO PURPOSES
if 'demo' in request.args or app.config['DEMO']:
x['path'] = 'XXXXX'
x['folder_name'] = 'XXXXX'
if len(x['folder_name']) > 22:
x.update({'name': x['folder_name'][:20] + '... > ' + x['name']})
else:
x.update({'name': x['folder_name'] + ' > ' + x['name']})
matching_proteins.extend(m)
matches = []
if len(matching_plates) > 0:
matches.append({'name': 'Matching plate IDs',
'id': '0',
'node_type': 'Folder',
'has_children': True,
'children': matching_plates})
if len(matching_barcodes) > 0:
matches.append({'name': 'Matching barcodes',
'id': '00',
'node_type': 'Folder',
'has_children': True,
'children': matching_barcodes})
if len(matching_experiments) > 0:
matches.append({'name': 'Matching experiments',
'id': '000',
'node_type': 'Folder',
'has_children': True,
'children': matching_experiments})
if len(matching_proteins) > 0:
matches.append({'name': 'Matching proteins',
'id': '0000',
'node_type': 'Folder',
'has_children': True,
'children': matching_proteins})
return jsonify(matches)
