def get_user_pref_task(project_id, user_id=None, user_ip=None, external_uid=None, limit=1, offset=0, orderby='priority_0', desc=True): """ Select a new task based on user preference set under user profile. For each incomplete task, check if the number of users working on the task is smaller than the number of answers still needed. In that case, acquire a lock on the task that matches user preference(if any) with users profile and return the task to the user. If offset is nonzero, skip that amount of available tasks before returning to the user. """ user_pref_list = cached_users.get_user_preferences(user_id) sql = ''' SELECT task.id, COUNT(task_run.task_id) AS taskcount, n_answers, (SELECT info->'timeout' FROM project WHERE id=:project_id) as timeout FROM task LEFT JOIN task_run ON (task.id = task_run.task_id) WHERE NOT EXISTS (SELECT 1 FROM task_run WHERE project_id=:project_id AND user_id=:user_id AND task_id=task.id) AND task.project_id=:project_id AND (task.user_pref IS NULL OR {0}) AND task.state !='completed' group by task.id ORDER BY priority_0 DESC, id ASC LIMIT :limit; '''.format(user_pref_list) return text(sql)
def locked_task_sql(project_id, user_id=None, limit=1, rand_within_priority=False, task_type='gold_last', filter_user_prefs=False, priority_sort=True): ''' `task_type` will affect the type of tasks return by the query and can be one one of the following values: gold -> only gold tasks will be returned no_gold -> only non-gold tasks will be returned gold_last -> non-gold tasks will be returned before gold tasks. (Default) gold_first -> gold tasks will be returned before non-gold tasks. ''' filters = [] if filter_user_prefs: filters.append('AND ({})'.format( cached_users.get_user_preferences(user_id))) filters.append( data_access.get_data_access_db_clause_for_task_assignment(user_id) or '') if task_type == 'gold': filters.append('AND task.calibration = 1') elif task_type == 'no_gold': filters.append('AND task.calibration != 1') order_by = [] if task_type == 'gold_last': order_by.append('task.calibration') elif task_type == 'gold_first': order_by.append('task.calibration DESC NULLS LAST') if priority_sort: order_by.append('priority_0 DESC') if rand_within_priority: order_by.append('random()') else: order_by.append('id ASC') sql = ''' SELECT task.id, COUNT(task_run.task_id) AS taskcount, n_answers, task.calibration, (SELECT info->'timeout' FROM project WHERE id=:project_id) as timeout FROM task LEFT JOIN task_run ON (task.id = task_run.task_id) WHERE NOT EXISTS (SELECT 1 FROM task_run WHERE project_id=:project_id AND user_id=:user_id AND task_id=task.id) AND task.project_id=:project_id AND ((task.expiration IS NULL) OR (task.expiration > (now() at time zone 'utc')::timestamp)) AND task.state !='completed' AND task.state !='enrich' {} group by task.id ORDER BY {} LIMIT :limit; '''.format(' '.join(filters), ','.join(order_by)) return text(sql)
def n_available_tasks_for_user(project, user_id=None, user_ip=None): """Return the number of tasks for a given project a user can contribute to. based on the completion of the project tasks, previous task_runs submitted by the user and user preference set under user profile. """ from pybossa.sched import Schedulers n_tasks = 0 if user_id is None or user_id <= 0: return n_tasks assign_user = json.dumps({'assign_user': [cached_users.get_user_email(user_id)]}) if user_id else None scheduler = project["info"].get('sched', 'default') if type(project) == dict else project.info.get('sched', 'default') project_id = project['id'] if type(project) == dict else project.id if scheduler not in [Schedulers.user_pref, Schedulers.task_queue]: sql = ''' SELECT COUNT(*) AS n_tasks FROM task WHERE project_id=:project_id AND state !='completed' AND state !='enrich' AND id NOT IN (SELECT task_id FROM task_run WHERE project_id=:project_id AND user_id=:user_id) ; ''' else: user_pref_list = cached_users.get_user_preferences(user_id) user_filter_list = cached_users.get_user_filters(user_id) sql = ''' SELECT task.id, worker_filter FROM task WHERE project_id=:project_id AND state !='completed' AND state !='enrich' AND id NOT IN (SELECT task_id FROM task_run WHERE project_id=:project_id AND user_id=:user_id) AND ({}) AND ({}) ;'''.format(user_pref_list, user_filter_list) sqltext = text(sql) try: result = session.execute(sqltext, dict(project_id=project_id, user_id=user_id, assign_user=assign_user)) if scheduler not in [Schedulers.user_pref, Schedulers.task_queue]: for row in result: n_tasks = row.n_tasks return n_tasks else: num_available_tasks = 0 user_profile = cached_users.get_user_profile_metadata(user_id) user_profile = json.loads(user_profile) if user_profile else {} for task_id, w_filter in result: w_filter = w_filter or {} num_available_tasks += int( cached_task_browse_helpers.user_meet_task_requirement(task_id, w_filter, user_profile) ) return num_available_tasks except Exception as e: current_app.logger.exception('Exception in get_user_pref_task {0}, sql: {1}'.format(str(e), str(sqltext))) return None
def n_available_tasks_for_user(project, user_id=None, user_ip=None): """Return the number of tasks for a given project a user can contribute to. based on the completion of the project tasks, previous task_runs submitted by the user and user preference set under user profile. """ from pybossa.sched import Schedulers allowed_task_levels_clause = get_data_access_db_clause_for_task_assignment( user_id) n_tasks = 0 if user_id is None or user_id <= 0: return n_tasks assign_user = json.dumps({ 'assign_user': [cached_users.get_user_email(user_id)] }) if user_id else None scheduler = project.get( 'sched', 'default') if type(project) == dict else project.info.get( 'sched', 'default') project_id = project['id'] if type(project) == dict else project.id if scheduler != Schedulers.user_pref: sql = ''' SELECT COUNT(*) AS n_tasks FROM task WHERE project_id=:project_id AND state !='completed' AND state !='enrich' AND id NOT IN (SELECT task_id FROM task_run WHERE project_id=:project_id AND user_id=:user_id) {} ; '''.format(allowed_task_levels_clause) else: user_pref_list = cached_users.get_user_preferences(user_id) sql = ''' SELECT COUNT(*) AS n_tasks FROM task WHERE project_id=:project_id AND state !='completed' AND state !='enrich' AND id NOT IN (SELECT task_id FROM task_run WHERE project_id=:project_id AND user_id=:user_id) AND ({}) {} ; '''.format(user_pref_list, allowed_task_levels_clause) sqltext = text(sql) try: result = session.execute( sqltext, dict(project_id=project_id, user_id=user_id, assign_user=assign_user)) except Exception as e: current_app.logger.exception( 'Exception in get_user_pref_task {0}, sql: {1}'.format( str(e), str(sqltext))) return None for row in result: n_tasks = row.n_tasks return n_tasks
def get_user_pref_task(project_id, user_id=None, user_ip=None, external_uid=None, limit=1, offset=0, orderby='priority_0', desc=True, rand_within_priority=False, present_gold_task=False, gold_only=False): """ Select a new task based on user preference set under user profile. For each incomplete task, check if the number of users working on the task is smaller than the number of answers still needed. In that case, acquire a lock on the task that matches user preference(if any) with users profile and return the task to the user. If offset is nonzero, skip that amount of available tasks before returning to the user. """ user_pref_list = cached_users.get_user_preferences(user_id) secondary_order = 'random()' if rand_within_priority else 'id ASC' allowed_task_levels_clause = data_access.get_data_access_db_clause_for_task_assignment( user_id) order_by_calib = 'DESC NULLS LAST' if present_gold_task else '' gold_only_clause = 'AND task.calibration = 1' if gold_only else '' sql = ''' SELECT task.id, COUNT(task_run.task_id) AS taskcount, n_answers, task.calibration, (SELECT info->'timeout' FROM project WHERE id=:project_id) as timeout FROM task LEFT JOIN task_run ON (task.id = task_run.task_id) WHERE NOT EXISTS (SELECT 1 FROM task_run WHERE project_id=:project_id AND user_id=:user_id AND task_id=task.id) AND task.project_id=:project_id AND ({}) AND ((task.expiration IS NULL) OR (task.expiration > (now() at time zone 'utc')::timestamp)) AND task.state !='completed' {} {} group by task.id ORDER BY task.calibration {}, priority_0 DESC, {} LIMIT :limit; '''.format(user_pref_list, allowed_task_levels_clause, gold_only_clause, order_by_calib, secondary_order) return text(sql)
def n_available_tasks_for_user(project, user_id=None, user_ip=None): """Return the number of tasks for a given project a user can contribute to. based on the completion of the project tasks, previous task_runs submitted by the user and user preference set under user profile. """ from pybossa.sched import Schedulers n_tasks = 0 if user_id is None or user_id <= 0: return n_tasks scheduler = project.info.get('sched', 'default') if scheduler != Schedulers.user_pref: sql = ''' SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS (SELECT task_id FROM task_run WHERE project_id=:project_id AND user_id=:user_id AND task_id=task.id) AND project_id=:project_id AND state !='completed'; ''' else: user_pref_list = cached_users.get_user_preferences(user_id) sql = ''' SELECT COUNT(id) AS n_tasks FROM task WHERE NOT EXISTS (SELECT task_id FROM task_run WHERE project_id=:project_id AND user_id=:user_id AND task_id=task.id) AND project_id=:project_id AND (user_pref IS NULL OR {0}) AND state !='completed' ; '''.format(user_pref_list) sqltext = text(sql) try: result = session.execute(sqltext, dict(project_id=project.id, user_id=user_id)) except Exception as e: current_app.logger.exception( 'Exception in get_user_pref_task {0}, sql: {1}'.format( str(e), str(sqltext))) return None for row in result: n_tasks = row.n_tasks return n_tasks