def notificationLogging(request): db = DBConnection() form = util.FieldStorage(request) event_type = form['Event.1.EventType'].value event_time = parseISO(form['Event.1.EventTime'].value) hit_type_id = form['Event.1.HITTypeId'].value hit_id = form['Event.1.HITId'].value assignment_id = None if form.has_key('Event.1.AssignmentId'): assignment_id = form['Event.1.AssignmentId'].value #TODO: check to make sure we never get "event.2.eventtype" etc. logging.info("Event notification from MTurk: " + str(event_type) + " " + str(event_time) + " " + str(hit_type_id) + " " + str(hit_id) + " " + str(assignment_id)) try: db.query_and_return_array( "INSERT INTO notifications (eventtype, timestamp, hittypeid, hitid, assignmentid) VALUES (%s, %s, %s, %s, %s)" "", (event_type, event_time, hit_type_id, hit_id, assignment_id)) if event_type == "AssignmentReturned": db.query_and_return_array( """UPDATE `assignments` SET `return` = %s WHERE `assignmentid` = %s;""", (event_time, assignment_id)) except Exception: logging.exception("Notification exception")
def disableVideo(request): db = DBConnection() form = util.FieldStorage(request) videoid = form['videoid'].value db.query_and_return_array("""DELETE FROM pictures WHERE videoid = %s""", (videoid, )) db.query_and_return_array("""UPDATE videos SET enabled = FALSE WHERE pk = %s""", (videoid, ))
def getWorkerOpenHITs(worker_id): """Returns a list of all HITs where the last word was that the worker had accepted it""" open_hits = [] # this gives us the last notification we got for any HIT that this worker touched db = DBConnection() results = db.query_and_return_array( """SELECT `assignment_duration`, `accept`, `return`, `submit`, `assignmentid` FROM hits, assignments WHERE assignments.hitid = hits.hitid AND assignments.workerid = %s """, (worker_id, )) for row in results: is_done = row['submit'] is not None is_returned = row['return'] is not None assignment_elapses = datetime.fromtimestamp( row['accept']) + timedelta(seconds=row['assignment_duration']) is_expired = (datetime.now() > assignment_elapses) if not is_done and not is_returned and not is_expired: notifications = db.query_and_return_array( """SELECT `eventtype` FROM notifications WHERE assignmentid = %s AND `eventtype` = "AssignmentReturned" OR `eventtype` = "AssignmentAbandoned" """, (row['assignmentid'], )) if len(notifications) == 0: open_hits.append(row['assignmentid']) return open_hits
def updateAssignment(assignmentid, videoid): """ Updates the database to map this video onto the assignment """ db = DBConnection() try: db.query_and_return_array("""UPDATE assignments SET videoid = %s WHERE assignmentid = %s""", (videoid, assignmentid) ) except: logging.exception("Error updating assignments table to set videoid")
def log(request): db = DBConnection() form = util.FieldStorage(request) event = form['event'].value detail = form['detail'].value # e.g., word number assignment = form['assignmentid'].value worker = form['workerid'].value hit = form['hitid'].value ip = request.connection.remote_ip useragent = request.headers_in['User-Agent'] time = parseISO(form['time'].value) servertime = unixtime(datetime.now()) #logging.debug("Logging assignment %s event %s" % (assignment, event)) try: db.query_and_return_array( """INSERT INTO logging (event, detail, assignmentid, time, servertime, ip, useragent) VALUES (%s, %s, %s, %s, %s, %s, %s)""", (event, detail, assignment, time, servertime, ip, useragent)) # if it's an accept event, we start logging information about it in our assignments table if event == "accept": db.query_and_return_array( """INSERT INTO assignments (assignmentid, workerid, hitid, accept) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE workerid = %s, `accept` = %s, `show` = NULL, `go` = NULL, `first` = NULL, `submit` = NULL, `videoid` = NULL""", (assignment, worker, hit, time, worker, time)) except: logging.exception("Logging exception:")
def enableVideo(request): # request.content_type = "application/json" db = DBConnection() form = util.FieldStorage(request) videoid = form['videoid'].value db.query_and_return_array("""UPDATE videos SET enabled = TRUE WHERE pk = %s""", (videoid, ))
def updateAssignment(assignmentid): """ Updates the database to say whether this assignment had real work associated with it """ db = DBConnection() try: db.query_and_return_array( """UPDATE assignments SET was_real_work=1 WHERE assignmentid = %s""", (assignmentid, )) except: logging.exception("Error updating assignments table to set videoid")
def log_submission_in_db(request): """ Mapping of db columns (left) to form keys (right): assignmentid = assignmentId workerid = w vote = v accept = a show = sh go = g submit = su """ form = util.FieldStorage(request) # Get all the items out of the form assignmentid = get_value_or_none(form, "assignmentId") workerid = get_value_or_none(form, "w") vote = get_value_or_none(form, "v") # Parse the times accept = get_time_or_none(form, "a") show = get_time_or_none(form, "sh") go = get_time_or_none(form, "g") submit = get_time_or_none(form, "su") sql = """UPDATE `assignments` SET `show` = %s, `go` = %s, `submit` = %s WHERE assignmentid = %s; """ try: db = DBConnection() db.query_and_return_array(sql, (show, go, submit, assignmentid)) voteid = getVoteForAssignment(assignmentid, db) phase = getMostRecentPhase(voteid, db) sql = """INSERT INTO responses (voteid, phaseid, assignmentid, response) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE response=%s""" db.query_and_return_array( sql, (voteid, phase['pk'], assignmentid, vote, vote)) result = db.query_and_return_array( """SELECT COUNT(*) FROM responses WHERE phaseid = %s""", (phase['pk'], )) if result[0]['COUNT(*)'] >= MIN_VOTES_TO_DECIDE and phase['end'] == 0: closePhase(phase['pk'], unixtime(datetime.now()), db) decideVote(phase['phase_list'], db) except: logging.exception("couldn't insert into the database")
def setAgreement(request): form = util.FieldStorage(request) worker_id = form['workerid'].value db=DBConnection() result = db.query_and_return_array("""UPDATE workers SET read_instructions = TRUE WHERE workerid = %s""", (worker_id, ))
def getRandomVideo(request): request.content_type = "application/json" form = util.FieldStorage(request) assignmentid = form['assignmentid'].value is_slow = False if form.has_key('slow') and form['slow'] == "1": is_slow = True videoid = None db = DBConnection() if form.has_key('videoid'): videoid = int(form['videoid'].value) else: if not is_slow: # are there any videos with incomplete phases that we can join? # grab the most recently touched one sql = """ SELECT videoid FROM phases, phase_lists WHERE end IS NULL AND is_abandoned = 0 AND start >= %s AND phases.phase_list = phase_lists.pk ORDER BY start DESC LIMIT 1 """ max_age = unixtime(datetime.now() - timedelta( seconds=location_ping.PHASE_MAX_AGE_IN_SECONDS)) unfinished_phases = db.query_and_return_array(sql, (max_age, )) if len(unfinished_phases) > 0: videoid = unfinished_phases[0]['videoid'] if is_slow or videoid is None: # TODO: this will not scale once we have over ~10,000 rows logging.debug("Getting random video") #videoid = db.query_and_return_array("""SELECT pk FROM videos LEFT JOIN study_videos ON videos.pk = study_videos.videoid WHERE study_videos.videoid IS NULL ORDER BY RAND() LIMIT 1""")[0]['pk'] videoid = db.query_and_return_array( """SELECT pictures.videoid FROM `phase_lists`, pictures WHERE pictures.phase_list = phase_lists.pk AND is_historical = 0 ORDER BY RAND() LIMIT 1""" )[0]['videoid'] video_json = ready.getAndAssignVideo(assignmentid, videoid, db) logging.debug(video_json) request.write(json.dumps(video_json, cls=location_ping.DecimalEncoder))
def log_submission_in_db(request): """ Mapping of db columns (left) to form keys (right): assignmentid = assignmentId workerid = w phases = p accept = a show = sh go = g validation_pictures = v submit = su """ """ TODO: add calculation of total focus/blur time to the submission process we'll parse this out of the log table and add it to the submission page """ form = util.FieldStorage(request) # Get all the items out of the form assignmentid = get_value_or_none(form, "assignmentId") workerid = get_value_or_none(form, "w") phases = get_value_or_none(form, "p") validation = get_value_or_none(form, "v") # Parse the times accept = get_time_or_none(form, "a") show = get_time_or_none(form, "sh") go = get_time_or_none(form, "g") submit = get_time_or_none(form, "su") sql = """UPDATE `assignments` SET `phases` = %s, `validation` = %s, `show` = %s, `go` = %s, `submit` = %s WHERE assignmentid = %s; """ try: db = DBConnection() db.query_and_return_array( sql, (phases, validation, show, go, submit, assignmentid)) except: logging.exception("couldn't insert into the database")
def replayLog(request): request.content_type = "application/json" form = util.FieldStorage(request) videoid = form['videoid'].value db = DBConnection() phases = db.query_and_return_array( """SELECT * FROM phases, (SELECT phase_lists.pk AS phase_list FROM phase_lists, pictures WHERE phase_lists.videoid = %s AND phase_lists.is_historical = FALSE AND phase_lists.pk = pictures.phase_list ORDER BY phase_lists.pk DESC LIMIT 1) AS pl WHERE phases.phase_list = pl.phase_list ORDER BY phase""", (videoid, )) for phase in phases: locations = db.query_and_return_array( """SELECT location, servertime, assignmentid, phase FROM locations WHERE phase = %s ORDER BY servertime""", (phase['phase'], )) phase['locations'] = locations request.write(json.dumps(phases, cls=location_ping.DecimalEncoder))
def log_submission_in_db(request): """ Mapping of db columns (left) to form keys (right): assignmentid = assignmentId workerid = w vote = v accept = a show = sh go = g submit = su """ form = util.FieldStorage(request) # Get all the items out of the form assignmentid = get_value_or_none(form, "assignmentId") workerid = get_value_or_none(form, "w") vote = get_value_or_none(form, "v") # Parse the times accept = get_time_or_none(form, "a") show = get_time_or_none(form, "sh") go = get_time_or_none(form, "g") submit = get_time_or_none(form, "su") sql = """UPDATE `assignments` SET `show` = %s, `go` = %s, `submit` = %s WHERE assignmentid = %s; """ try: db = DBConnection() db.query_and_return_array(sql, (show, go, submit, assignmentid)) sql = """INSERT INTO slow_votes (assignmentid, vote) VALUES (%s, %s) ON DUPLICATE KEY UPDATE vote=%s""" db.query_and_return_array(sql, (assignmentid, vote, vote)) except: logging.exception("couldn't insert into the database")
def numRetainerWorkers(request): request.content_type = "application/json" db = DBConnection() ping_floor = unixtime(datetime.now() - timedelta(seconds=10)) sql = """SELECT logging.assignmentid, logging.servertime FROM logging, (SELECT MAX(servertime) AS pingtime, assignmentid FROM logging WHERE servertime > %s AND event LIKE 'ping%%' GROUP BY assignmentid) as mostRecent WHERE logging.servertime = mostRecent.pingTime AND logging.assignmentid=mostRecent.assignmentid AND event = 'ping-waiting' GROUP BY assignmentid""" result = db.query_and_return_array(sql, (ping_floor, )) output = {'num_waiting': len(result)} request.write(json.dumps(output))
def getAgreementForWorker(worker_id): db=DBConnection() result = db.query_and_return_array("""SELECT read_instructions FROM workers WHERE workerid = %s""", (worker_id, )) if len(result) == 0: # they haven't been initialized; initialize and try again # TODO: This is a hack...there should be a more principled way to # initialize the worker ID. Maybe we put that code at the top of the # word_clicker.mpy? new_condition = condition.setRandomCondition(worker_id) return getAgreementForWorker(worker_id) else: agreed = bool(result[0]['read_instructions']) return agreed
def is_ready(request): request.content_type = "application/json" db = DBConnection() form = util.FieldStorage(request) assignmentid = form['assignmentid'].value workerid = form['workerid'].value w = db.query_and_return_array("SELECT * from work_available") if len(w) > 0: updateAssignment(assignmentid) request.write(json.dumps({'is_ready': True})) else: request.write(json.dumps({'is_ready': False}))
def slowSubmit(request): """ Handles submissions from the "slow" server """ form = util.FieldStorage(request) # Get relevant items out of the form assignmentid = submit.get_value_or_none(form, "assignmentId") workerid = submit.get_value_or_none(form, "w") snapshots_raw = submit.get_value_or_none(form, "sn") snapshots = simplejson.loads(snapshots_raw) logging.debug("SNAPSHOTS: %s" % snapshots) db = DBConnection() for snapshot in snapshots: db.query_and_return_array("""INSERT INTO slow_snapshots (assignmentid, location) VALUES (%s, %s)""", (assignmentid, snapshot) ) if workerid == PHOTOGRAPHER_ID: # photographer doesn't submit a HIT :) submit.log_submission_in_db(request) request.write("OK, got it! %s for %s" % (snapshots, assignmentid)) else: submit.record_and_redirect(request)
def log_submission_in_db(request): """ Mapping of db columns (left) to form keys (right): assignmentid = assignmentId workerid = w accept = a show = sh go = g submit = su points = p """ form = util.FieldStorage(request) # Get all the items out of the form assignmentid = get_value_or_none(form, "assignmentId") workerid = get_value_or_none(form, "w") points = get_value_or_none(form, "p") # Parse the times accept = get_time_or_none(form, "a") show = get_time_or_none(form, "sh") go = get_time_or_none(form, "g") submit = get_time_or_none(form, "su") sql = """UPDATE `assignments` SET `points` = %s, `show` = %s, `go` = %s, `submit` = %s WHERE assignmentid = %s; """ try: db=DBConnection() db.query_and_return_array(sql, (points, show, go, submit, assignmentid)) except: logging.exception("couldn't insert into the database")
def getCurrentPositions(request): request.content_type = "application/json" form = util.FieldStorage(request) videoid = form['videoid'].value db = DBConnection() cur_phase = db.query_and_return_array( """SELECT * FROM phases, phase_lists WHERE phase_lists.videoid = %s AND phase_lists.pk = phases.phase_list ORDER BY phases.phase DESC LIMIT 1""", (videoid, ))[0] locations = db.query_and_return_array( """ SELECT locations.assignmentid, locations.location FROM ( SELECT assignmentid, MAX(servertime) as maxtime FROM locations WHERE phase = %s GROUP BY assignmentid ) AS current, locations WHERE current.maxtime = locations.servertime AND current.assignmentid = locations.assignmentid AND locations.phase = %s """, (cur_phase['phase'], cur_phase['phase'])) cur_phase['locations'] = locations request.write(json.dumps(cur_phase, cls=location_ping.DecimalEncoder))
def getRandomVote(request): request.content_type = "application/json" form = util.FieldStorage(request) db = DBConnection() assignmentid = form['assignmentid'] if form.has_key('voteid'): voteid = int(form['voteid'].value) else: logging.debug("Getting random vote") voteid = db.query_and_return_array( """SELECT * FROM votes ORDER BY RAND() LIMIT 1""")[0] result = ready.getAndAssignVote(voteid, assignmentid, db) request.write(json.dumps(result, cls=DecimalEncoder))
def getRandomPhotos(request): request.content_type = "application/json" form = util.FieldStorage(request) db = DBConnection() assignmentid = form['assignmentid'] if form.has_key('videoid'): videoid = int(form['videoid'].value) else: logging.debug("Getting random photos") videoid = db.query_and_return_array( """SELECT COUNT(*), videos.pk FROM videos, slow_snapshots, assignments WHERE videos.pk NOT IN (SELECT videoid FROM study_videos) AND slow_snapshots.assignmentid = assignments.assignmentid AND assignments.videoid = videos.pk AND videos.pk <> 1 GROUP BY videos.pk HAVING COUNT(*) > 1""" )[0]['pk'] #videoid = db.query_and_return_array("""SELECT videoid, COUNT(*) FROM slow_votes, assignments WHERE slow_votes.assignmentid = assignments.assignmentid GROUP BY videoid HAVING COUNT(*) > %s ORDER BY RAND() LIMIT 1""", (MIN_VOTES, ))[0]['videoid'] result = ready.getAndAssignPhotos(videoid, assignmentid, db) request.write(json.dumps(result, cls=DecimalEncoder))
def getVideos(request): request.content_type = "application/json" db = DBConnection() indexNewVideos(db) # get all videos and whether they are currently enabled and have no previous photos videos_in_db = db.query_and_return_array("""SELECT videos.pk, (location IS NOT NULL) AS hasphotos FROM videos LEFT JOIN pictures ON pictures.videoid = videos.pk""") videos = [] for video in videos_in_db: has_photos = bool(video['hasphotos']) curVideo = getVideo(video['pk'], create_phase=(not has_photos), restart_if_converged=True) curVideo['hasphotos'] = has_photos videos.append(curVideo) request.write(json.dumps(videos, cls = DecimalEncoder))
def getVideo(videoid, restart_if_converged=False, create_phase=True): db = DBConnection() result = db.query_and_return_array( """SELECT pk, width, height, filename, creationtime, enabled FROM videos WHERE pk = %s""", (videoid, ))[0] json_out = dict(is_ready=True, width=result['width'], height=result['height'], filename=result['filename'], videoid=result['pk'], creationtime=result['creationtime'], enabled=result['enabled']) # get or create a video labeling phase if create_phase: phase = location_ping.getMostRecentPhase( videoid, db, restart_if_converged=restart_if_converged) json_out['phase'] = phase return json_out