def is_ready(request):
request.content_type = "application/json"
db = DBConnection(autocommit=False)
form = util.FieldStorage(request)
assignmentid = form['assignmentid'].value
workerid = form['workerid'].value
if form.has_key('videoid'):
videoid = int(form['videoid'].value)
else:
is_slow = form.has_key('slow') and form['slow'] == "1"
result = unlabeledVideos(db, is_slow)
logging.debug(result)
if is_slow:
# have I already labeled this video?
result = filter(lambda x: not haveCompleted(x['pk'], workerid, db),
result)
if len(result) == 0:
request.write(json.dumps({'is_ready': False}))
return
else:
# grab the most recent video upload
logging.debug("Videos needing labels: " + str(result))
videoid = result[0]['pk']
video = getAndAssignVideo(assignmentid, videoid)
db.commit()
request.write(json.dumps(video, cls=location_ping.DecimalEncoder))
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 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 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 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 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 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 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 getRandomWork(request):
request.content_type = "application/json"
form = util.FieldStorage(request)
db = DBConnection()
assignmentid = form['assignmentid']
result = {"puppet": "human", "is_ready": True}
request.write(json.dumps(result))
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 getValidationImages(request):
db = DBConnection()
good = getImages(db, True)
bad = getImages(db, False)
videos = []
videos.append(random.choice(good))
videos.append(random.choice(bad))
videos.append(random.choice(bad))
random.shuffle(videos)
request.write(json.dumps(videos))
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
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 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 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 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 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 is_ready(request):
request.content_type = "application/json"
db = DBConnection()
form = util.FieldStorage(request)
assignmentid = form['assignmentid'].value
workerid = form['workerid'].value
result = getVideosNeedingVotes(db, workerid)
if len(result) == 0:
request.write(json.dumps( { 'is_ready' : False } ))
else:
# grab the most recent video upload
videoid = result[0]['videoid']
result = getAndAssignPhotos(videoid, assignmentid, db)
request.write(json.dumps(result, cls = location_ping.DecimalEncoder))
def is_ready(request):
request.content_type = "application/json"
db = DBConnection()
form = util.FieldStorage(request)
assignmentid = form['assignmentid'].value
workerid = form['workerid'].value
result = getMolesNeedingWhacks(db, workerid)
if len(result) == 0:
request.write(json.dumps( { 'is_ready' : False } ))
else:
# grab the most recent molewhack
random.shuffle(result)
mole = result[0]
result = getAndAssignMole(mole, assignmentid, db)
request.write(json.dumps(result, cls=DecimalEncoder))
def locationPing(request):
request.content_type = "application/json"
db = DBConnection(autocommit = False)
start = datetime.now()
try:
(phase, assignment, location, video_id) = getArgs(request)
servertime = unixtime(datetime.now())
# logging.debug("Location %s for video %s on phase %s" % (location, video_id, phase))
cur_phase = getMostRecentPhase(video_id, db)
# Ensure that there isn't already a newer phase that
# we should be returning to the client
if cur_phase['phase'] != phase:
request.write(json.dumps(cur_phase, cls=DecimalEncoder))
else:
# Record where we are
pushLocation(location, phase, assignment, video_id, servertime, db)
# Has the phase already converged? (i.e., too small to be divisible)
(is_new_phase, new_min, new_max) = compareLocations(cur_phase, servertime, db)
if is_new_phase:
closePhase(cur_phase['phase'], servertime, False, db)
# Agreement! Create a new phase.
new_phase = createPhase(video_id, new_min, new_max,
servertime, cur_phase['phase_list'], db)
logging.debug("Creating new phase: %s" % new_phase)
if not phaseIsDivisible(new_phase):
logging.debug("Picture has converged!")
closePhase(new_phase['phase'], servertime, False, db)
takePicture(new_phase, video_id, db)
request.write(json.dumps(new_phase, cls=DecimalEncoder))
else:
request.write(json.dumps(cur_phase, cls=DecimalEncoder))
db.commit()
except Exception, e:
db.rollback()
logging.exception(e)
raise
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")
