def samples_delete_cmd(task, creator, editor, specimen, block, slide, label,
newer, older, yes):
"""Delete training samples for TASK"""
delete_samples(task, creator, editor, specimen, block, slide, label, newer,
older, yes)
get_db().commit()
def users_list_command():
"""List users"""
db = get_db()
# Create a Pandas data frame
df = pandas.read_sql_query(
'SELECT id,username,site_admin FROM user ORDER BY id', get_db())
# Dump the database entries
with pandas.option_context('display.max_rows', None):
print(df)
def list_projects_command():
# Parse existing projects read from JSON files
df = pandas.read_sql_query("SELECT id,disp_name,desc FROM project",
get_db())
with pandas.option_context('display.max_rows', None,
'display.max_colwidth', 20):
print(df)
def add_project(name, json_file, update_existing=False):
# Read the JSON
data = json.load(json_file)
# Validate the JSON
validate(instance=data, schema=project_schema)
# Update the database
db = get_db()
if update_existing:
rc = db.execute(
'UPDATE project SET disp_name=?, desc=?, base_url=?, json=? WHERE id=?',
(data['disp_name'], data['desc'], data['base_url'],
json.dumps(data), name))
if rc.rowcount == 1:
db.commit()
print('Updated project %s' % (rc.lastrowid, ))
else:
rc = db.execute(
'INSERT INTO project (id, disp_name, desc, base_url, json) VALUES (?,?,?,?,?)',
(name, data['disp_name'], data['desc'], data['base_url'],
json.dumps(data)))
if rc.rowcount == 1:
db.commit()
print('Created project %s' % (rc.lastrowid, ))
def update_labelset_from_json(id, json_file):
db = get_db()
# Validate the JSON against the schem
data = json.load(json_file)
validate(instance=data, schema=label_schema)
# Go through the labels
for row in data:
# Check if a label with this name already exists
rc = db.execute(
"SELECT * FROM label "
"WHERE labelset=? and name COLLATE NOCASE = ?",
(id, row['name'])).fetchone()
# Get the description, which is optional
desc = row['description'] if 'description' in row else None
# If so, we will be updating this label
if rc is not None:
db.execute(
"UPDATE label SET color=?, description=? "
"WHERE labelset=? and name COLLATE NOCASE = ?",
(row['color'], desc, id, row['name']))
else:
db.execute(
"INSERT INTO label (name, color, description, labelset) VALUES (?,?,?,?)",
(row['name'], row['color'], desc, id))
def api_get_slide_random_patch(task_id, slide_id, width):
db = get_db()
# Find out which machine the slide is currently being served from
# Get the tiff image from which to sample the region of interest
sr = get_slide_ref(slide_id)
# Get the identifiers for the slide
# TODO: what to do with project here?
(project, specimen, block, slide_name, slide_ext) = sr.get_id_tuple()
# Are we de this slide to a different node?
del_url = find_delegate_for_slide(slide_id)
# If local, call the method directly
rawbytes = None
if del_url is None:
rawbytes = get_random_patch(project, specimen, block, 'raw',
slide_name, slide_ext, 0, width,
'png').data
else:
url = '%s/dzi/random_patch/%s/%s/%s/raw/%s.%s/%d/%d.png' % (
del_url, project, specimen, block, slide_name, slide_ext, 0, width)
pr = sr.get_project_ref()
post_data = urllib.urlencode(
{'project_data': json.dumps(pr.get_dict())})
rawbytes = urllib2.urlopen(url, post_data).read()
# Send the patch
resp = make_response(rawbytes)
resp.mimetype = 'image/png'
return resp
def project_update_label(project, label_id):
db = get_db()
db.execute("UPDATE label SET name=?, description=?, color=? WHERE id=?",
(request.form['name'], request.form['desc'],
request.form['color'], label_id))
db.commit()
return json.dumps({"status": "ok"})
def samples_generate_csv(task,
fout,
list_metadata=False,
list_ids=False,
list_block=False,
header=True):
db = get_db()
# Create the full view
make_dbview_full('v_full')
# Select keys to export
keys = ('slide_name', 'label_name', 'x', 'y', 'w', 'h')
if list_metadata:
keys = keys + ('t_create', 'creator', 't_edit', 'editor')
if list_block:
keys = keys + ('specimen_name', 'block_name', 'stain')
if list_ids:
keys = ('id', ) + keys
# Run query
rc = db.execute(
'SELECT *, x0 as x, y0 as y, x1-x0 as w, y1-y0 as h FROM v_full '
'WHERE task=? ORDER BY id', (task, ))
if header:
fout.write(','.join(keys) + '\n')
for row in rc.fetchall():
vals = map(lambda a: str(row[a]), keys)
fout.write(','.join(vals) + '\n')
def project_listing():
# Get the current user
user = session['user_id']
db = get_db()
# Result array
listing = []
# List all projects for the current user
if session.get('user_is_site_admin', False) is not True:
rc = db.execute('SELECT P.*, PA.admin FROM project P '
'LEFT JOIN project_access PA ON PA.project=P.id '
'WHERE PA.user = ?'
'ORDER BY P.disp_name', (user,))
else:
rc = db.execute('SELECT P.*, 1 as admin FROM project P ORDER BY P.disp_name ')
for row in rc.fetchall():
# Get the statistics for the project
stat = db.execute(
'SELECT COUNT (id) as nslides, '
' COUNT(DISTINCT block_id) as nblocks, '
' COUNT(DISTINCT specimen_name) as nspecimens '
'FROM slide_info WHERE project=?', (row['id'],)).fetchone()
# Create a dictionary
listing.append({'id':row['id'],'admin':row['admin'],'disp_name':row['disp_name'],'desc':row['desc'],
'nslides':stat['nslides'], 'nblocks':stat['nblocks'], 'nspecimens':stat['nspecimens']})
# Generate a bunch of json
return json.dumps([x for x in listing])
def update_sample():
data = json.loads(request.get_data())
rect = data['geometry']
sample_id = data['id']
db = get_db()
# Update the metadata
# Get existing properties
rc = db.execute('SELECT meta_id,task FROM training_sample WHERE id=?',
(sample_id, )).fetchone()
check_rect(rc['task'], rect)
update_edit_meta(rc['meta_id'])
# Update the main record
db.execute(
'UPDATE training_sample '
'SET x0=?, y0=?, x1=?, y1=?, label=?, have_patch=0 '
'WHERE id=?',
(rect[0], rect[1], rect[2], rect[3], data['label_id'], sample_id))
db.commit()
# Save an image patch around the sample
slide_id = db.execute('SELECT slide FROM training_sample WHERE id=?',
(sample_id, )).fetchone()['slide']
generate_sample_patch(slide_id, sample_id, rect)
return "success"
def login_with_api_key():
if request.method == 'POST':
api_key = request.form['api_key']
error = None
db = get_db()
user = db.execute(
"""
SELECT U.*, AK.t_expires
FROM user_api_key AK LEFT JOIN user U on AK.user = U.id
WHERE AK.api_key = ?""", (api_key, )).fetchone()
if user is None:
error = 'Invalid API key'
elif user['disabled'] > 0:
error = 'User account is disabled'
elif user['t_expires'] < time.time():
error = 'API key has expired'
if error is None:
session.clear()
session['user_id'] = user['id']
session['user_is_site_admin'] = user['site_admin']
session['user_api_key'] = True
return make_response({"status": "ok"}, 200)
return make_response({"status": "failed", "error": error}, 401)
def __init__(self, name, dict=None):
self.name = name
if dict is None:
# When JSON is not provided, we read the entry from the database
db = get_db()
rc = db.execute('SELECT * FROM project WHERE id=?', (name,)).fetchone()
if rc is None:
raise ValueError('Project %s is not in the database' % (name,))
# Read the database entries into local vars
self.disp_name = rc['disp_name']
self.desc = rc['desc']
self.url_base = rc['base_url']
self._dict = json.loads(rc['json'])
else:
# Use dict
self.disp_name = dict['disp_name']
self.desc = dict['desc']
self.url_base = dict['base_url']
self._dict = dict
# Initialize the URL handler
if self.url_base.startswith('gs://'):
self._url_handler = GCSHandler()
else:
self._url_handler = None
def send_user_resetlink(user_id, email=None, expiry=86400):
if email is None:
db = get_db()
rc = db.execute(
'SELECT * FROM user WHERE id=? AND email IS NOT NULL AND disabled=0',
(user_id, )).fetchone()
if rc is not None:
email = rc['email']
else:
return None
# Create a password reset link
url = create_password_reset_link(user_id)
# Create an email
mail = get_mail()
server_name = current_app.config['HISTOANNOT_PUBLIC_NAME']
msg = Message("Password Reset Link for %s" % (server_name, ))
msg.add_recipient(email)
msg.html = """
<p>You have requested a link to reset your password on %s.</p>
<p>Please follow this link to reset the password for user <b>%s</b>:</p>
<a href="%s">%s</a>
""" % (server_name, rc['username'], url, url)
mail.send(msg)
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['passwd']
db = get_db()
error = None
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username or password.'
elif user['disabled'] > 0:
error = 'User account is disabled.'
elif user['password'] is None:
error = 'You have not created a password yet. Please reset your password.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect username or password.'
if error is None:
session.clear()
session['user_id'] = user['id']
session['user_is_site_admin'] = user['site_admin']
session['user_api_key'] = False
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def task_listing(project):
db = get_db()
# List the available tasks (TODO: check user access to task)
rc = db.execute('SELECT * FROM task_info WHERE project=?', (project, ))
listing = []
for row in rc.fetchall():
# Parse the json
task_id = row['id']
# Get the subset of stains to which the task applies
stat = db.execute(
"""SELECT COUNT(S.id) as nslides, COUNT(DISTINCT block_id) as nblocks, COUNT(DISTINCT specimen_name) as nspecimens
FROM task_slide_index TSI
LEFT JOIN slide_info S on S.id == TSI.slide
WHERE task_id=?""", (task_id, )).fetchone()
# Create a dict
task = json.loads(row['json'])
d = {'id': row['id'], 'name': task['name'], 'desc': task['desc']}
for key in ('nspecimens', 'nblocks', 'nslides'):
d[key] = stat[key]
listing.append(d)
return json.dumps([x for x in listing])
def slides_list_cmd(task, specimen, block, section, slide, stain, min_paths,
min_markers, csv):
"""List slides in a task"""
db = get_db()
# Create a DB view of slide details
make_slide_dbview(task, 'v_full')
# Build up a where clause
w = filter(lambda (a, b): b is not None and b is not False,
[('specimen_name LIKE ?', specimen),
('block_name LIKE ?', block), ('section = ?', section),
('slide = ?', slide), ('stain = ?', stain),
('n_paths >= ?', min_paths), ('n_markers >= ?', min_markers)])
if len(w) > 0:
w_sql, w_prm = zip(*w)
w_clause = 'WHERE %s' % ' AND '.join(w_sql)
else:
w_clause = ''
w_prm = ()
# Create a Pandas data frame
df = pandas.read_sql_query("SELECT * FROM v_full %s" % w_clause,
db,
params=w_prm)
# Dump the database entries
if csv is not None:
df.to_csv(csv, index=False)
else:
with pandas.option_context('display.max_rows', None):
print(df)
def samples_fix_patches_cmd(task):
"""Generate missing patches for the samples in the database"""
# Get a list of all patches relevant to us, sorted by slide so we don't have to
# sample slides out of order
make_dbview_full('v_full')
db = get_db()
rc = db.execute('SELECT * FROM v_full '
'WHERE task=? ORDER BY slide_name', (task, )).fetchall()
# Get the required patch dimensions
(project, t_data) = get_task_data(task)
patch_dim = t_data['dltrain'].get('display-patch-size', 512)
# For each patch check if it is there
for row in rc:
id = row['id']
fn = get_sample_patch_filename(id)
if os.path.exists(fn):
w, h = Image.open(fn).size
if w == patch_dim and h == patch_dim:
continue
print('Missing or corrupt patch for sample %d, %s' % (id, fn))
rect = (float(row['x0']), float(row['y0']), float(row['x1']),
float(row['y1']))
# Generate the patch
generate_sample_patch(row['slide'],
id,
rect,
dims=(patch_dim, patch_dim))
def project_get_json_command(project):
db = get_db()
row = db.execute('SELECT * FROM project WHERE id=?',
(project, )).fetchone()
if row is not None:
print(json.dumps(row['json'], indent=2))
else:
print('Project %s not found' % (project, ))
def get_slide_by_name(self, slide_name):
"""Search for a slide with a given name in the project. All slides imported
into a project must have unique names"""
db=get_db()
rc = db.execute('SELECT * FROM slide_info WHERE slide_name=? AND project=?',
(slide_name, self.name)).fetchone()
return rc['id'] if rc is not None else None
def print_tasks_command(task):
"""Print the JSON for a task"""
db = get_db()
rc = db.execute('SELECT json FROM task WHERE id=?', (task, )).fetchone()
if rc is not None:
print(json.dumps(json.loads(rc['json']), indent=4))
else:
print('Task %d does not exist' % (int(task), ))
def get_samples(task_id, slide_id):
db = get_db()
ll = db.execute(
'SELECT S.*, M.creator, M.editor, M.t_create, M.t_edit, L.color '
'FROM training_sample S LEFT JOIN label L on S.label = L.id '
' LEFT JOIN edit_meta M on S.meta_id = M.id '
'WHERE S.slide=? and S.task=? ORDER BY M.t_edit DESC ',
(slide_id, task_id))
return json.dumps([dict(row) for row in ll.fetchall()])
def rebuild_project_slice_indices(project, specific_task_id=None):
db = get_db()
rc = db.execute(
'SELECT id, name FROM task_info WHERE project=? ORDER BY id',
(project, ))
for row in rc.fetchall():
if specific_task_id is None or specific_task_id == row['id']:
n = rebuild_task_slide_index(row['id'])
print('Index for task %d rebuilt with %d slides' % (row['id'], n))
def ping():
print("GETTING PINGED")
db = get_db()
if 'url' in request.form and 'cpu_percent' in request.form:
db.execute(
'INSERT OR REPLACE INTO dzi_node(url,t_ping,cpu_percent) VALUES(?,?,?)',
(request.form['url'], time.time(), request.form['cpu_percent']))
db.commit()
return "ok"
def api_get_annot_timestamp(task_id, slide_id):
db = get_db()
rc = db.execute('SELECT M.t_edit FROM annot A '
'LEFT JOIN edit_meta M on A.meta_id = M.id '
'WHERE A.task_id = ? AND A.slide_id = ?',
(task_id, slide_id)).fetchone()
if rc is not None:
return json.dumps({'timestamp' : rc['t_edit']}), 200, {'ContentType':'application/json'}
else:
return json.dumps({'timestamp' : None}), 200, {'ContentType':'application/json'}
def api_get_annot_timestamp_by_slidename(task_id, slide_name):
db = get_db()
rc = db.execute('SELECT S.id FROM annot A '
'LEFT JOIN slide S on A.slide_id = S.id '
'WHERE A.task_id = ? and S.slide_name = ?',
(task_id, slide_name)).fetchone()
if rc is not None:
return api_get_annot_timestamp(task_id, rc['id'])
else:
return json.dumps({'timestamp': None}), 200, {'ContentType': 'application/json'}
def api_get_annot_svg_by_slidename(task_id, slide_name):
db = get_db()
rc = db.execute('SELECT S.id FROM annot A '
'LEFT JOIN slide S on A.slide_id = S.id '
'WHERE A.task_id = ? and S.slide_name = ?',
(task_id, slide_name)).fetchone()
if rc is not None:
return api_get_annot_svg(task_id, rc['id'])
else:
abort(404)
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute(
'SELECT * FROM user WHERE id = ? AND disabled=0',
(user_id, )).fetchone()
g.login_via_api_key = session.get('user_api_key', False)
def list_tasks_command(project):
"""List available tasks in a project"""
print('%08s %s' % ('Task ID', 'Task Name'))
db = get_db()
rc = db.execute(
'SELECT id, name FROM task_info WHERE project=? ORDER BY id',
(project, ))
for row in rc.fetchall():
print('%08s %s' % (row['id'], row['name']))
def get_label_id_in_task(task_id, label_name):
db = get_db()
project, task = get_task_data(task_id)
ls_id = get_labelset_id(project, task)
# Look up the label
label_id = db.execute('SELECT id FROM label WHERE name=? AND labelset=?',
(label_name, ls_id)).fetchone()['id']
return label_id
def delegate_dzi_list_command():
"""List all active DZI delegates"""
# Arbitrary cutoff of 2 minutes
t_test = time.time() - 120
db = get_db()
rc = db.execute('SELECT * FROM dzi_node WHERE t_ping > ?',
(t_test, )).fetchall()
for row in rc:
print('%s %d %f' %
(row['url'], time.time() - row['t_ping'], row['cpu_percent']))