def recent_queries():
"""
Page listing recent queries.
"""
# Initialise task database
task_db = db_interface.TaskDB(dbname='sqlite:///tasks.db',
tblname='sql_queries')
tasks = task_db.get_all()
# Count the number of queries, and how many are running
nqueries = len(tasks)
nrunning = 0
queries = []
for t in tasks:
# Track no. of running queries
if t.status == statuses['running'][0]: nrunning += 1
# Construct table
s_id, s_col, s_tag = statuses[status_map[t.status]]
qry = t.query[:80] + (t.query[80:] and '...') # truncate query
queries.append((qry, 'anon', t.created, s_col, s_tag, t.uuid))
return render_template('recent_queries.html',
query_info=queries,
num_queries=nqueries,
num_queries_running=nrunning)
def query_remove(query_id):
"""
Remove a query from the task DB (and remove cached results).
"""
# Fetch query by ID
query = query_db.AsyncResult(query_id)
# Delete from cache
query.forget()
# Delete from task DB
# Initialise task database
task_db = db_interface.TaskDB(dbname='sqlite:///tasks.db',
tblname='sql_queries')
# Remove task
task_db.remove_task(uuid=query.id)
return redirect(url_for('recent_queries'))
def dispatch_sql_query():
"""
Take a submitted SQL query and dispatch to a worker to complete in the
background.
"""
sql_query = request.form['sql']
# Initialise task DB
task_db = db_interface.TaskDB(dbname='sqlite:///tasks.db',
tblname='sql_queries')
# Run query asynchronously
query = query_db.delay(dbname='sqlite:///galacticus.db',
sql_query=sql_query)
# Save task details
task_db.add_task(uuid=query.id,
query=sql_query,
status=statuses['queued'][0])
# Redirect to results page
return redirect(url_for('query_status', query_id=query.id))
def query_db(self, dbname, sql_query):
"""
Perform SQL query on database.
"""
# Open database
galdb = db_interface.GalaxyDB(dbname=dbname)
# Update task status
metadata = { 'db_name': dbname, 'sql_query': sql_query }
self.update_state(state='PROGRESS', meta=metadata)
task_db = db_interface.TaskDB(dbname='sqlite:///tasks.db',
tblname='sql_queries')
task_db.update_task(uuid=self.request.id, status='running')
# Run query and fetch results
err = None; results = None; cols = None
try:
res = galdb.query(sql_query)
cols = res.keys()
results = res.fetchall()
# Unpack into regular Python lists (which are serialisable)
tbl = [[val for val in row] for row in results]
# Add result to metadata list
metadata['result'] = (cols, tbl, err)
# Mark task as completed
task_db.update_task(uuid=self.request.id, status='finished')
except Exception as err:
print("ERROR:", err)
metadata['result'] = ([], [], str(err))
task_db.update_task(uuid=self.request.id, status='failed')
# Return results
return metadata
def query_status(query_id):
"""
Display the status or results of a query.
"""
# Get query
query = query_db.AsyncResult(query_id)
# Check status of query
if query.ready():
# Query finished; render results
print("*** FINISHED")
sql_query = query.get()['sql_query']
cols, tbl, err = query.get()['result']
status = 'error' if err else 'finished'
return render_template('show_query.html',
sql_query=sql_query,
sql_res=tbl,
sql_cols=cols,
sql_err=err,
sql_max_rows=200,
query_status=status,
query_id=query_id)
elif query.status == 'PROGRESS':
# Query still in progress
print("*** PROGRESS")
sql_query = query.get()['sql_query']
return render_template('show_query.html',
sql_query=sql_query,
sql_res=[],
sql_cols=[
'In progress',
],
sql_err=None,
sql_max_rows=200,
query_status='inprogress',
query_id=query_id)
elif query.status == 'PENDING':
# Query hasn't been registered yet or does not exist
sql_query = "PENDING"
sql_cols = [
'Pending',
]
query_status = 'pending'
# Load task database and check if expired
task_db = db_interface.TaskDB(dbname='sqlite:///tasks.db',
tblname='sql_queries')
tasks = task_db.get_task_by_uuid(query_id)
if len(tasks) > 0:
if status_map[tasks[0].status] == 'finished':
# Task previously finished, but has now expired
sql_query = tasks[0].query
sql_cols = [
'Query expired',
]
query_status = 'expired'
return render_template('show_query.html',
sql_query=sql_query,
sql_res=[],
sql_cols=sql_cols,
sql_err=None,
sql_max_rows=200,
query_status=query_status,
query_id=query_id,
expiry_time="%d" % (EXPIRY_TIME /
(24. * 3600.)))
else:
# An unknown error occurred
sql_query = "ERROR"
print("*** ERROR")
return render_template('show_query.html',
sql_query=sql_query,
sql_res=[],
sql_cols=[],
sql_err="Query returned an invalid status.",
sql_max_rows=200,
query_status='error',
query_id=query_id)