def n_available_tasks(app_id, user_id=None, user_ip=None):
"""Returns the number of tasks for a given app a user can contribute to,
based on the completion of the app tasks, and previous task_runs submitted
by the user"""
try:
if user_id and not user_ip:
query = text('''SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS
(SELECT task_id FROM task_run WHERE
app_id=:app_id AND user_id=:user_id AND task_id=task.id)
AND app_id=:app_id AND state !='completed';''')
session = get_session(db, bind='slave')
result = session.execute(query, dict(app_id=app_id, user_id=user_id))
else:
if not user_ip:
user_ip = '127.0.0.1'
query = text('''SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS
(SELECT task_id FROM task_run WHERE
app_id=:app_id AND user_ip=:user_ip AND task_id=task.id)
AND app_id=:app_id AND state !='completed';''')
session = get_session(db, bind='slave')
result = session.execute(query, dict(app_id=app_id, user_ip=user_ip))
n_tasks = 0
for row in result:
n_tasks = row.n_tasks
return n_tasks
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_available_tasks(app_id, user_id=None, user_ip=None):
"""Returns the number of tasks for a given app a user can contribute to,
based on the completion of the app tasks, and previous task_runs submitted
by the user"""
try:
if user_id and not user_ip:
query = text(
'''SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS
(SELECT task_id FROM task_run WHERE
app_id=:app_id AND user_id=:user_id AND task_id=task.id)
AND app_id=:app_id AND state !='completed';''')
session = get_session(db, bind='slave')
result = session.execute(query, dict(app_id=app_id,
user_id=user_id))
else:
if not user_ip:
user_ip = '127.0.0.1'
query = text(
'''SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS
(SELECT task_id FROM task_run WHERE
app_id=:app_id AND user_ip=:user_ip AND task_id=task.id)
AND app_id=:app_id AND state !='completed';''')
session = get_session(db, bind='slave')
result = session.execute(query, dict(app_id=app_id,
user_ip=user_ip))
n_tasks = 0
for row in result:
n_tasks = row.n_tasks
return n_tasks
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_count(category):
"""Count the number of apps in a given category"""
if category == 'featured':
return _n_featured()
if category == 'draft':
return _n_draft()
try:
sql = text('''
WITH uniq AS (
SELECT COUNT(app.id) FROM task, app
LEFT OUTER JOIN category ON app.category_id=category.id
WHERE
category.short_name=:category
AND app.hidden=0
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id)
SELECT COUNT(*) FROM uniq
''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(category=category))
count = 0
for row in results:
count = row[0]
return count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_top5_users_24_hours():
try:
session = get_session(db, bind='slave')
# Top 5 Most active users in last 24 hours
sql = text('''SELECT "user".id, "user".fullname, "user".name,
COUNT(task_run.app_id) AS n_answers FROM "user", task_run
WHERE "user".id=task_run.user_id
AND DATE(task_run.finish_time) > NOW() - INTERVAL '24 hour'
AND DATE(task_run.finish_time) <= NOW()
GROUP BY "user".id
ORDER BY n_answers DESC LIMIT 5;''')
results = session.execute(sql, dict(limit=5))
top5_users_24_hours = []
for row in results:
user = dict(id=row.id, fullname=row.fullname,
name=row.name,
n_answers=row.n_answers)
top5_users_24_hours.append(user)
return top5_users_24_hours
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_locs(): # pragma: no cover
try:
session = get_session(db, bind='slave')
# All IP addresses from anonymous users to create a map
locs = []
if current_app.config['GEO']:
sql = '''SELECT DISTINCT(user_ip) from task_run WHERE user_ip IS NOT NULL;'''
results = session.execute(sql)
geolite = current_app.root_path + '/../dat/GeoLiteCity.dat'
gic = pygeoip.GeoIP(geolite)
for row in results:
loc = gic.record_by_addr(row.user_ip)
if loc is None:
loc = {}
if (len(loc.keys()) == 0):
loc['latitude'] = 0
loc['longitude'] = 0
locs.append(dict(loc=loc))
return locs
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_top5_apps_24_hours():
try:
session = get_session(db, bind='slave')
# Top 5 Most active apps in last 24 hours
sql = text('''SELECT app.id, app.name, app.short_name, app.info,
COUNT(task_run.app_id) AS n_answers FROM app, task_run
WHERE app.id=task_run.app_id
AND app.hidden=0
AND DATE(task_run.finish_time) > NOW() - INTERVAL '24 hour'
AND DATE(task_run.finish_time) <= NOW()
GROUP BY app.id
ORDER BY n_answers DESC LIMIT 5;''')
results = session.execute(sql, dict(limit=5))
top5_apps_24_hours = []
for row in results:
tmp = dict(id=row.id, name=row.name, short_name=row.short_name,
info=dict(json.loads(row.info)), n_answers=row.n_answers)
top5_apps_24_hours.append(tmp)
return top5_apps_24_hours
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_count(category):
"""Count the number of apps in a given category"""
try:
sql = text('''
WITH uniq AS (
SELECT COUNT(app.id) FROM task, app
LEFT OUTER JOIN category ON app.category_id=category.id
WHERE
category.short_name=:category
AND app.hidden=0
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id)
SELECT COUNT(*) FROM uniq
''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(category=category))
count = 0
for row in results:
count = row[0]
return count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_featured(category, page=1, per_page=5):
"""Return a list of featured apps with a pagination"""
try:
count = n_featured()
sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
app.description,
"user".fullname AS owner FROM app, featured, "user"
WHERE app.id=featured.app_id AND app.hidden=0
AND "user".id=app.owner_id GROUP BY app.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created, description=row.description,
overall_progress=overall_progress(row.id),
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
featured=row.id,
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def new_task(app_id, user_id=None, user_ip=None, offset=0):
'''Get a new task by calling the appropriate scheduler function.
'''
try:
session = get_session(db, bind='slave')
app = session.query(App).get(app_id)
if not app.allow_anonymous_contributors and user_id is None:
info = dict(
error="This project does not allow anonymous contributors")
error = Task(info=info)
return error
else:
sched_map = {
'default': get_depth_first_task,
'breadth_first': get_breadth_first_task,
'depth_first': get_depth_first_task,
'random': get_random_task,
'incremental': get_incremental_task,
'filter_by_users': get_filtered_by_user_task}
sched = sched_map.get(app.info.get('sched'), sched_map['default'])
return sched(app_id, user_id, user_ip, offset=offset)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def apps_contributed(user_id):
try:
sql = text('''
WITH apps_contributed as
(SELECT DISTINCT(app_id) FROM task_run
WHERE user_id=:user_id)
SELECT app.id, app.name, app.short_name, app.owner_id,
app.description, app.info FROM app, apps_contributed
WHERE app.id=apps_contributed.app_id ORDER BY app.name DESC;
''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(user_id=user_id))
apps_contributed = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
owner_id=row.owner_id,
description=row.description,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=json.loads(row.info))
apps_contributed.append(app)
return apps_contributed
except: # pragma: no cover
session.rollback()
finally:
session.close()
def create(taskrun=None):
authorized = False
try:
session = get_session(db, bind='slave')
if taskrun.user_ip:
sql = text('''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run
WHERE task_run.app_id=:app_id AND
task_run.task_id=:task_id AND
task_run.user_ip=:user_ip;''')
results = session.execute(sql, dict(app_id=taskrun.app_id,
task_id=taskrun.task_id,
user_ip=taskrun.user_ip))
elif taskrun.user_id:
sql = text('''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run
WHERE task_run.app_id=:app_id AND
task_run.task_id=:task_id AND
task_run.user_id=:user_id;''')
results = session.execute(sql, dict(app_id=taskrun.app_id,
task_id=taskrun.task_id,
user_id=taskrun.user_id))
else:
return False
n_task_runs = 0
for row in results:
n_task_runs = row.n_task_runs
authorized = (n_task_runs <= 0)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
if not authorized:
raise abort(403)
return authorized
def new_task(app_id, user_id=None, user_ip=None, offset=0):
'''Get a new task by calling the appropriate scheduler function.
'''
try:
session = get_session(db, bind='slave')
app = session.query(App).get(app_id)
if not app.allow_anonymous_contributors and user_id is None:
info = dict(
error="This project does not allow anonymous contributors")
error = Task(info=info)
return error
else:
sched_map = {
'default': get_depth_first_task,
'breadth_first': get_breadth_first_task,
'depth_first': get_depth_first_task,
'random': get_random_task,
'incremental': get_incremental_task
}
sched = sched_map.get(app.info.get('sched'), sched_map['default'])
return sched(app_id, user_id, user_ip, offset=offset)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def hidden_apps(user_id):
try:
sql = text('''
SELECT app.id, app.name, app.short_name, app.description,
app.owner_id,
app.info
FROM app, task
WHERE app.id=task.app_id AND app.owner_id=:user_id AND
app.hidden=1 AND app.info LIKE('%task_presenter%')
GROUP BY app.id, app.name, app.short_name,
app.description,
app.info;''')
apps_published = []
session = get_session(db, bind='slave')
results = session.execute(sql, dict(user_id=user_id))
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
owner_id=row.owner_id,
description=row.description,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=json.loads(row.info))
apps_published.append(app)
return apps_published
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_candidate_tasks(app_id, user_id=None, user_ip=None, n_answers=30, offset=0):
"""Gets all available tasks for a given project and user"""
try:
session = get_session(db, bind='slave')
rows = None
if user_id and not user_ip:
query = text('''
SELECT id FROM task WHERE NOT EXISTS
(SELECT task_id FROM task_run WHERE
app_id=:app_id AND user_id=:user_id AND task_id=task.id)
AND app_id=:app_id AND state !='completed'
ORDER BY priority_0 DESC, id ASC LIMIT 10''')
rows = session.execute(query, dict(app_id=app_id, user_id=user_id))
else:
if not user_ip:
user_ip = '127.0.0.1'
query = text('''
SELECT id FROM task WHERE NOT EXISTS
(SELECT task_id FROM task_run WHERE
app_id=:app_id AND user_ip=:user_ip AND task_id=task.id)
AND app_id=:app_id AND state !='completed'
ORDER BY priority_0 DESC, id ASC LIMIT 10''')
rows = session.execute(query, dict(app_id=app_id, user_ip=user_ip))
tasks = []
for t in rows:
tasks.append(session.query(Task).get(t.id))
return tasks
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_draft(category=None, page=1, per_page=5):
"""Return list of draft projects"""
try:
sql = text('''SELECT app.id, app.name, app.short_name, app.created,
app.description, app.info, "user".fullname as owner
FROM "user", app LEFT JOIN task ON app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0
AND app.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_draft(category, page=1, per_page=5):
"""Return list of draft projects"""
try:
count = n_draft()
sql = text('''SELECT app.id, app.name, app.short_name, app.created,
app.description, app.info, "user".fullname as owner
FROM "user", app LEFT JOIN task ON app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0
AND app.owner_id="user".id
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps, count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def applications(name):
"""
List user's project list.
Returns a Jinja2 template with the list of projects of the user.
"""
try:
session = get_session(db, bind='slave')
user = session.query(User).filter_by(name=name).first()
if not user:
return abort(404)
if current_user.name != name:
return abort(403)
user = db.session.query(model.user.User).get(current_user.id)
apps_published, apps_draft = _get_user_apps(user.id)
apps_published.extend(cached_users.hidden_apps(user.id))
return render_template('account/applications.html',
title=gettext("Projects"),
apps_published=apps_published,
apps_draft=apps_draft)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def overall_progress(app_id):
"""Returns the percentage of submitted Tasks Runs done when a task is
completed"""
try:
sql = text('''SELECT task.id, n_answers,
COUNT(task_run.task_id) AS n_task_runs
FROM task LEFT OUTER JOIN task_run ON task.id=task_run.task_id
WHERE task.app_id=:app_id GROUP BY task.id''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(app_id=app_id))
n_expected_task_runs = 0
n_task_runs = 0
for row in results:
tmp = row[2]
if row[2] > row[1]:
tmp = row[1]
n_expected_task_runs += row[1]
n_task_runs += tmp
pct = float(0)
if n_expected_task_runs != 0:
pct = float(n_task_runs) / float(n_expected_task_runs)
return (pct * 100)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_featured(category=None, page=1, per_page=5):
"""Return a list of featured apps with a pagination"""
try:
sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
app.description,
"user".fullname AS owner FROM app, "user"
WHERE app.featured=true AND app.hidden=0
AND "user".id=app.owner_id GROUP BY app.id, "user".id
OFFSET(:offset) LIMIT(:limit);
''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
created=row.created, description=row.description,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
owner=row.owner,
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def overall_progress(app_id):
"""Returns the percentage of submitted Tasks Runs done when a task is
completed"""
try:
sql = text('''SELECT task.id, n_answers,
COUNT(task_run.task_id) AS n_task_runs
FROM task LEFT OUTER JOIN task_run ON task.id=task_run.task_id
WHERE task.app_id=:app_id GROUP BY task.id''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(app_id=app_id))
n_expected_task_runs = 0
n_task_runs = 0
for row in results:
tmp = row[2]
if row[2] > row[1]:
tmp = row[1]
n_expected_task_runs += row[1]
n_task_runs += tmp
pct = float(0)
if n_expected_task_runs != 0:
pct = float(n_task_runs) / float(n_expected_task_runs)
return (pct * 100)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_incremental_task(app_id, user_id=None, user_ip=None, n_answers=30, offset=0):
"""
Get a new task for a given project with its last given answer.
It is an important strategy when dealing with large tasks, as
transcriptions.
"""
try:
session = get_session(db, bind='slave')
candidate_tasks = get_candidate_tasks(app_id, user_id, user_ip,
n_answers, offset=0)
total_remaining = len(candidate_tasks)
if total_remaining == 0:
return None
rand = random.randrange(0, total_remaining)
task = candidate_tasks[rand]
#Find last answer for the task
q = session.query(TaskRun)\
.filter(TaskRun.task_id == task.id)\
.order_by(TaskRun.finish_time.desc())
last_task_run = q.first()
if last_task_run:
task.info['last_answer'] = last_task_run.info
#TODO: As discussed in GitHub #53
# it is necessary to create a lock in the task!
return task
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def draft_apps(user_id):
try:
sql = text('''
SELECT app.id, app.name, app.short_name, app.description,
owner_id,
app.info
FROM app
WHERE app.owner_id=:user_id
AND app.info NOT LIKE('%task_presenter%')
GROUP BY app.id, app.name, app.short_name,
app.description,
app.info;''')
apps_draft = []
session = get_session(db, bind='slave')
results = session.execute(sql, dict(user_id=user_id))
for row in results:
app = dict(id=row.id, name=row.name, short_name=row.short_name,
owner_id=row.owner_id,
description=row.description,
info=json.loads(row.info))
apps_draft.append(app)
return apps_draft
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def applications(name):
"""
List user's project list.
Returns a Jinja2 template with the list of projects of the user.
"""
try:
session = get_session(db, bind='slave')
user = session.query(User).filter_by(name=name).first()
if not user:
return abort(404)
if current_user.name != name:
return abort(403)
user = db.session.query(model.user.User).get(current_user.id)
apps_published, apps_draft = _get_user_apps(user.id)
apps_published.extend(cached_users.hidden_apps(user.id))
return render_template('account/applications.html',
title=gettext("Projects"),
apps_published=apps_published,
apps_draft=apps_draft)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_top5_apps_24_hours():
try:
session = get_session(db, bind='slave')
# Top 5 Most active apps in last 24 hours
sql = text('''SELECT app.id, app.name, app.short_name, app.info,
COUNT(task_run.app_id) AS n_answers FROM app, task_run
WHERE app.id=task_run.app_id
AND app.hidden=0
AND DATE(task_run.finish_time) > NOW() - INTERVAL '24 hour'
AND DATE(task_run.finish_time) <= NOW()
GROUP BY app.id
ORDER BY n_answers DESC LIMIT 5;''')
results = session.execute(sql, dict(limit=5))
top5_apps_24_hours = []
for row in results:
tmp = dict(id=row.id,
name=row.name,
short_name=row.short_name,
info=dict(json.loads(row.info)),
n_answers=row.n_answers)
top5_apps_24_hours.append(tmp)
return top5_apps_24_hours
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_top5_users_24_hours():
try:
session = get_session(db, bind='slave')
# Top 5 Most active users in last 24 hours
sql = text('''SELECT "user".id, "user".fullname, "user".name,
COUNT(task_run.app_id) AS n_answers FROM "user", task_run
WHERE "user".id=task_run.user_id
AND DATE(task_run.finish_time) > NOW() - INTERVAL '24 hour'
AND DATE(task_run.finish_time) <= NOW()
GROUP BY "user".id
ORDER BY n_answers DESC LIMIT 5;''')
results = session.execute(sql, dict(limit=5))
top5_users_24_hours = []
for row in results:
user = dict(id=row.id,
fullname=row.fullname,
name=row.name,
n_answers=row.n_answers)
top5_users_24_hours.append(user)
return top5_users_24_hours
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_locs(): # pragma: no cover
try:
session = get_session(db, bind='slave')
# All IP addresses from anonymous users to create a map
locs = []
if current_app.config['GEO']:
sql = '''SELECT DISTINCT(user_ip) from task_run WHERE user_ip IS NOT NULL;'''
results = session.execute(sql)
geolite = current_app.root_path + '/../dat/GeoLiteCity.dat'
gic = pygeoip.GeoIP(geolite)
for row in results:
loc = gic.record_by_addr(row.user_ip)
if loc is None:
loc = {}
if (len(loc.keys()) == 0):
loc['latitude'] = 0
loc['longitude'] = 0
locs.append(dict(loc=loc))
return locs
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def stats_users(app_id):
"""Return users's stats for a given app_id"""
try:
session = get_session(db, bind='slave')
users = {}
auth_users = []
anon_users = []
# Get Authenticated Users
sql = text('''SELECT task_run.user_id AS user_id,
COUNT(task_run.id) as n_tasks FROM task_run
WHERE task_run.user_id IS NOT NULL AND
task_run.user_ip IS NULL AND
task_run.app_id=:app_id
GROUP BY task_run.user_id ORDER BY n_tasks DESC
LIMIT 5;''')
results = session.execute(sql, dict(app_id=app_id))
for row in results:
auth_users.append([row.user_id, row.n_tasks])
sql = text('''SELECT count(distinct(task_run.user_id)) AS user_id FROM task_run
WHERE task_run.user_id IS NOT NULL AND
task_run.user_ip IS NULL AND
task_run.app_id=:app_id;''')
results = session.execute(sql, dict(app_id=app_id))
for row in results:
users['n_auth'] = row[0]
# Get all Anonymous Users
sql = text('''SELECT task_run.user_ip AS user_ip,
COUNT(task_run.id) as n_tasks FROM task_run
WHERE task_run.user_ip IS NOT NULL AND
task_run.user_id IS NULL AND
task_run.app_id=:app_id
GROUP BY task_run.user_ip ORDER BY n_tasks DESC;''').execution_options(stream=True)
results = session.execute(sql, dict(app_id=app_id))
for row in results:
anon_users.append([row.user_ip, row.n_tasks])
sql = text('''SELECT COUNT(DISTINCT(task_run.user_ip)) AS user_ip FROM task_run
WHERE task_run.user_ip IS NOT NULL AND
task_run.user_id IS NULL AND
task_run.app_id=:app_id;''')
results = session.execute(sql, dict(app_id=app_id))
for row in results:
users['n_anon'] = row[0]
return users, anon_users, auth_users
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def stats_dates(app_id):
try:
dates = {}
dates_anon = {}
dates_auth = {}
dates_n_tasks = {}
session = get_session(db, bind='slave')
avg, total_n_tasks = get_avg_n_tasks(app_id)
# Get all answers per date
sql = text('''
WITH myquery AS (
SELECT TO_DATE(finish_time, 'YYYY-MM-DD\THH24:MI:SS.US') as d,
COUNT(id)
FROM task_run WHERE app_id=:app_id GROUP BY d)
SELECT to_char(d, 'YYYY-MM-DD') as d, count from myquery;
''').execution_options(stream=True)
results = session.execute(sql, dict(app_id=app_id))
for row in results:
dates[row.d] = row.count
dates_n_tasks[row.d] = total_n_tasks * avg
# Get all answers per date for auth
sql = text('''
WITH myquery AS (
SELECT TO_DATE(finish_time, 'YYYY-MM-DD\THH24:MI:SS.US') as d,
COUNT(id)
FROM task_run WHERE app_id=:app_id AND user_ip IS NULL GROUP BY d)
SELECT to_char(d, 'YYYY-MM-DD') as d, count from myquery;
''').execution_options(stream=True)
results = session.execute(sql, dict(app_id=app_id))
for row in results:
dates_auth[row.d] = row.count
# Get all answers per date for anon
sql = text('''
WITH myquery AS (
SELECT TO_DATE(finish_time, 'YYYY-MM-DD\THH24:MI:SS.US') as d,
COUNT(id)
FROM task_run WHERE app_id=:app_id AND user_id IS NULL GROUP BY d)
SELECT to_char(d, 'YYYY-MM-DD') as d, count from myquery;
''').execution_options(stream=True)
results = session.execute(sql, dict(app_id=app_id))
for row in results:
dates_anon[row.d] = row.count
return dates, dates_n_tasks, dates_anon, dates_auth
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_breadth_first_task(app_id, user_id=None, user_ip=None, n_answers=30, offset=0):
"""Gets a new task which have the least number of task runs (excluding the
current user).
Note that it **ignores** the number of answers limit for efficiency reasons
(this is not a big issue as all it means is that you may end up with some
tasks run more than is strictly needed!)
"""
try:
# Uncomment the next three lines to profile the sched function
#import timeit
#T = timeit.Timer(lambda: get_candidate_tasks(app_id, user_id,
# user_ip, n_answers))
#print "First algorithm: %s" % T.timeit(number=1)
session = get_session(db, bind='slave')
if user_id and not user_ip:
sql = text('''
SELECT task.id, COUNT(task_run.task_id) AS taskcount FROM task
LEFT JOIN task_run ON (task.id = task_run.task_id) WHERE NOT EXISTS
(SELECT 1 FROM task_run WHERE app_id=:app_id AND
user_id=:user_id AND task_id=task.id)
AND task.app_id=:app_id AND task.state !='completed'
group by task.id ORDER BY taskcount, id ASC LIMIT 10;
''')
tasks = session.execute(sql, dict(app_id=app_id, user_id=user_id))
else:
if not user_ip: # pragma: no cover
user_ip = '127.0.0.1'
sql = text('''
SELECT task.id, COUNT(task_run.task_id) AS taskcount FROM task
LEFT JOIN task_run ON (task.id = task_run.task_id) WHERE NOT EXISTS
(SELECT 1 FROM task_run WHERE app_id=:app_id AND
user_ip=:user_ip AND task_id=task.id)
AND task.app_id=:app_id AND task.state !='completed'
group by task.id ORDER BY taskcount, id ASC LIMIT 10;
''')
# results will be list of (taskid, count)
tasks = session.execute(sql, dict(app_id=app_id, user_ip=user_ip))
# ignore n_answers for the present - we will just keep going once we've
# done as many as we need
tasks = [x[0] for x in tasks]
if tasks:
if (offset == 0):
return session.query(Task).get(tasks[0])
else:
if (offset < len(tasks)):
return session.query(Task).get(tasks[offset])
else:
return None
else: # pragma: no cover
return None
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_app(short_name):
try:
session = get_session(db, bind='slave')
app = session.query(App).filter_by(short_name=short_name).first()
return app
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_app(short_name):
try:
session = get_session(db, bind='slave')
app = session.query(App).filter_by(short_name=short_name).first()
return app
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_all():
"""Return all categories"""
try:
session = get_session(db, bind='slave')
data = session.query(model.category.Category).all()
return data
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_all():
"""Return all categories"""
try:
session = get_session(db, bind='slave')
data = session.query(model.category.Category).all()
return data
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def gen_csv(out, writer, write_user):
try:
session = get_session(db, bind='slave')
add_headers(writer)
for user in session.query(model.user.User).yield_per(1):
write_user(writer, user)
yield out.getvalue()
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def gen_csv(out, writer, write_user):
try:
session = get_session(db, bind='slave')
add_headers(writer)
for user in session.query(model.user.User).yield_per(1):
write_user(writer, user)
yield out.getvalue()
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def user_progress(app_id=None, short_name=None):
"""API endpoint for user progress.
Return a JSON object with two fields regarding the tasks for the user:
{ 'done': 10,
'total: 100
}
This will mean that the user has done a 10% of the available tasks for
him
"""
if app_id or short_name:
if short_name:
app = _retrieve_app(short_name=short_name)
elif app_id:
app = _retrieve_app(app_id=app_id)
if app:
try:
session = get_session(db, bind='slave')
# get done tasks from DB
if current_user.is_anonymous():
sql = text(
'''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run
WHERE task_run.app_id=:app_id AND
task_run.user_ip=:user_ip;''')
user_ip = request.remote_addr
if (user_ip == None):
user_ip = '127.0.0.1' # set address to local host for internal tests (see AnonymousTaskRunFactory)!
results = session.execute(
sql, dict(app_id=app.id, user_ip=user_ip))
else:
sql = text(
'''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run
WHERE task_run.app_id=:app_id AND
task_run.user_id=:user_id;''')
results = session.execute(
sql, dict(app_id=app.id, user_id=current_user.id))
n_task_runs = 0
for row in results:
n_task_runs = row.n_task_runs
# get total tasks from DB
tmp = dict(done=n_task_runs, total=n_tasks(app.id))
return Response(json.dumps(tmp), mimetype="application/json")
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
else:
return abort(404)
else: # pragma: no cover
return abort(404)
def gen_json():
try:
session = get_session(db, bind='slave')
users = session.query(model.user.User).all()
json_users = []
for user in users:
json_users.append(dictize_with_exportable_attributes(user))
return json.dumps(json_users)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_total_tasks_site():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT SUM(n_answers) AS n_tasks FROM task''')
results = session.execute(sql)
for row in results:
total = row.n_tasks
return total or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_tasks_site():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT COUNT(task.id) AS n_tasks FROM task''')
results = session.execute(sql)
for row in results:
n_tasks = row.n_tasks
return n_tasks or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_task_runs_site():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT COUNT(task_run.id) AS n_task_runs FROM task_run''')
results = session.execute(sql)
for row in results:
n_task_runs = row.n_task_runs
return n_task_runs or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_auth_users():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT COUNT("user".id) AS n_auth FROM "user";''')
results = session.execute(sql)
for row in results:
n_auth = row.n_auth
return n_auth or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_task_runs(app_id):
"""Return all the Task Runs for a given app_id"""
try:
session = get_session(db, bind='slave')
task_runs = []
for tr in session.query(TaskRun).filter_by(app_id=app_id).yield_per(100):
task_runs.append(tr)
return task_runs
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def gen_json():
try:
session = get_session(db, bind='slave')
users = session.query(model.user.User).all()
json_users = []
for user in users:
json_users.append(dictize_with_exportable_attributes(user))
return json.dumps(json_users)
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_total_tasks_site():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT SUM(n_answers) AS n_tasks FROM task''')
results = session.execute(sql)
for row in results:
total = row.n_tasks
return total or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_auth_users():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT COUNT("user".id) AS n_auth FROM "user";''')
results = session.execute(sql)
for row in results:
n_auth = row.n_auth
return n_auth or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_featured():
"""Return number of featured apps"""
try:
sql = text('''select count(*) from featured;''')
session = get_session(db, bind='slave')
results = session.execute(sql)
for row in results:
count = row[0]
return count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def _has_no_tasks(app_id):
try:
query = text('''SELECT COUNT(id) AS n_tasks FROM task
WHERE app_id=:app_id;''')
session = get_session(db, bind='slave')
result = session.execute(query, dict(app_id=app_id))
for row in result:
n_tasks = row.n_tasks
return n_tasks == 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def _n_featured():
"""Return number of featured apps"""
try:
sql = text('''SELECT COUNT(*) FROM app WHERE featured=true;''')
session = get_session(db, bind='slave')
results = session.execute(sql)
for row in results:
count = row[0]
return count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def _has_no_tasks(app_id):
try:
query = text('''SELECT COUNT(id) AS n_tasks FROM task
WHERE app_id=:app_id;''')
session = get_session(db, bind='slave')
result = session.execute(query, dict(app_id=app_id))
for row in result:
n_tasks = row.n_tasks
return n_tasks == 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_tasks(app_id):
try:
sql = text('''SELECT COUNT(task.id) AS n_tasks FROM task
WHERE task.app_id=:app_id''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(app_id=app_id))
n_tasks = 0
for row in results:
n_tasks = row.n_tasks
return n_tasks
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_anon_users():
try:
session = get_session(db, bind='slave')
sql = text('''SELECT COUNT(DISTINCT(task_run.user_ip))
AS n_anon FROM task_run;''')
results = session.execute(sql)
for row in results:
n_anon = row.n_anon
return n_anon or 0
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def last_activity(app_id):
try:
sql = text('''SELECT finish_time FROM task_run WHERE app_id=:app_id
ORDER BY finish_time DESC LIMIT 1''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(app_id=app_id))
for row in results:
if row is not None:
return row[0]
else: # pragma: no cover
return None
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def n_registered_volunteers(app_id):
try:
sql = text('''SELECT COUNT(DISTINCT(task_run.user_id)) AS n_registered_volunteers FROM task_run
WHERE task_run.user_id IS NOT NULL AND
task_run.user_ip IS NULL AND
task_run.app_id=:app_id;''')
session = get_session(db, bind='slave')
results = session.execute(sql, dict(app_id=app_id))
n_registered_volunteers = 0
for row in results:
n_registered_volunteers = row.n_registered_volunteers
return n_registered_volunteers
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def _n_draft():
"""Return number of draft projects"""
try:
sql = text('''SELECT COUNT(app.id) FROM app
LEFT JOIN task on app.id=task.app_id
WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
AND app.hidden=0;''')
session = get_session(db, bind='slave')
results = session.execute(sql)
for row in results:
count = row[0]
return count
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get_random_task(app_id,
user_id=None,
user_ip=None,
n_answers=30,
offset=0):
"""Returns a random task for the user"""
try:
session = get_session(db, bind='slave')
app = session.query(App).get(app_id)
from random import choice
if len(app.tasks) > 0:
return choice(app.tasks)
else:
return None
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
def get(category, page=1, per_page=5):
"""Return a list of apps with at least one task and a task_presenter
with a pagination for a given category"""
try:
sql = text('''SELECT app.id, app.name, app.short_name, app.description,
app.info, app.created, app.category_id, app.featured, "user".fullname AS owner
FROM "user", task, app
LEFT OUTER JOIN category ON app.category_id=category.id
WHERE
category.short_name=:category
AND app.hidden=0
AND "user".id=app.owner_id
AND app.info LIKE('%task_presenter%')
AND task.app_id=app.id
GROUP BY app.id, "user".id ORDER BY app.name
OFFSET :offset
LIMIT :limit;''')
offset = (page - 1) * per_page
session = get_session(db, bind='slave')
results = session.execute(sql, dict(category=category, limit=per_page, offset=offset))
apps = []
for row in results:
app = dict(id=row.id,
name=row.name, short_name=row.short_name,
created=row.created,
description=row.description,
owner=row.owner,
featured=row.featured,
last_activity=pretty_date(last_activity(row.id)),
last_activity_raw=last_activity(row.id),
overall_progress=overall_progress(row.id),
n_tasks=n_tasks(row.id),
n_volunteers=n_volunteers(row.id),
info=dict(json.loads(row.info)))
apps.append(app)
return apps
except: # pragma: no cover
session.rollback()
raise
finally:
session.close()
