def clean_up_old_hits(conn, verbose=True, dry_run=False):
counts = {'cleaned':0, 'remaining':0, 'errors':0}
hits = get_all_hits(conn)
now = timeutils.unixtime(datetime.now())
for h in hits:
try:
num_to_be_reviewed = len(conn.get_assignments(h.HITId, status="Submitted")) # yet to be reviewed
if verbose:
print "------------------"
print "HIT ID: " + h.HITId
expiration = timeutils.parseISO(h.Expiration)
duration = Decimal(h.AssignmentDurationInSeconds)
print "Expiration: " + str(expiration)
print "isExpired: " + str(h.expired)
print "AssignmentDuration: " + str(duration)
print "Seconds until last assignment can be turned in: " + str((float(expiration)+float(duration))-float(now))
print "HITStatus: " + h.HITStatus
print "Yet-to-be-reviewed assignments: " + str(num_to_be_reviewed)
print "Approved assignments: " + str(len(conn.get_assignments(h.HITId, status="Approved")))
print "Rejected assignments: " + str(len(conn.get_assignments(h.HITId, status="Rejected")))
if (h.HITStatus == 'Reviewable' or h.HITStatus == 'Reviewing') and num_to_be_reviewed == 0:
if not dry_run:
conn.dispose_hit(h.HITId)
print "DISPOSED OF HIT"
counts['cleaned'] += 1
if dry_run:
print "(dry run -- HIT not actually disposed)"
else:
counts['remaining'] += 1
except Exception, e:
print "exception processing HIT:\n" + str(e)
counts['errors'] += 1
def postVideo(db, version):
if version == "slow":
column = "slow_available"
date_column = "slow_available_time"
else:
column = "fast_available"
date_column = "fast_available_time"
to_post = db.query_and_return_array(
"""SELECT videos.pk, videos.filename FROM videos, study_videos WHERE videos.pk = study_videos.videoid AND study_videos."""
+ column + """ = FALSE ORDER BY RAND() LIMIT 1""")
if len(to_post) > 0:
print("Video being posted: %s" % to_post)
db.query_and_return_array(
"""UPDATE study_videos SET """ + column + """ = TRUE, """ +
date_column + """ = %s WHERE videoid = %s""",
(unixtime(datetime.now()), to_post[0]['pk']))
return True
else:
print("Nothing to post")
return False
"""
def uploadVideo(name, width, height):
db = DBConnection()
try:
sql = """INSERT INTO videos (filename, width, height, creationtime, enabled) VALUES (%s, %s, %s, %s, FALSE)"""
db.query_and_return_array(sql, (name, width, height, unixtime(datetime.now())))
except Exception, e:
print("Error writing video to database:")
print(e)
def getPingStatus(db, event_type):
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 = %s GROUP BY assignmentid"""
result = db.query_and_return_array(sql, (ping_floor, event_type))
return result
def post(self, mt_conn, db_conn):
hit = ExternalHit.post(self, mt_conn)
sql = """INSERT INTO hits
(hitid, hittypeid, creation_time, title, description, keywords, reward, assignment_duration, lifetime, max_assignments, auto_approval_delay, waitbucket)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
db_conn.query_and_return_array(sql, (hit.HITId, hit.HITTypeId,
unixtime(datetime.now()), self.title, self.description, self.keywords,
self.reward_as_usd_float, self.assignment_duration, self.lifetime, self.max_assignments, self.auto_approval_delay, self.waitbucket))
return hit
def postVotes(db):
to_post = db.query_and_return_array(
"""SELECT videos.pk, videos.filename FROM videos, study_videos WHERE videos.pk = study_videos.videoid AND study_videos.slow_voting_available = FALSE ORDER BY RAND() LIMIT 1"""
)
if len(to_post) > 0:
print("Vote being posted: %s" % to_post)
db.query_and_return_array(
"""UPDATE study_videos SET slow_voting_available = TRUE, slow_voting_available_time = %s WHERE videoid = %s""",
(unixtime(datetime.now()), to_post[0]['pk']))
return True
else:
print("Nothing to post")
return False
def unlabeledVideos(db, is_slow):
""" Returns videos that need to be Turked """
# we need videos that have no pictures yet
if is_slow:
inner_query = "SELECT COUNT(*) AS numPictures, videoid FROM slow_snapshots, assignments WHERE slow_snapshots.assignmentid = assignments.assignmentid AND workerid <> '" + PHOTOGRAPHER_ID + "' GROUP BY videoid"
num_pics_condition = 'OR pictureCount.numPictures < 3'
else:
inner_query = "SELECT COUNT(*) AS numPictures, videoid FROM pictures GROUP BY videoid"
num_pics_condition = ''
ping_floor = unixtime(datetime.now() - timedelta(seconds=10))
result = db.query_and_return_array(
"""
SELECT pk FROM videos
LEFT JOIN (""" + inner_query + """)
AS pictureCount
ON pictureCount.videoid = videos.pk
LEFT JOIN (SELECT COUNT(DISTINCT assignments.assignmentid) AS numWorkers, videoid
FROM assignments, (SELECT MAX(servertime) AS pingtime, assignmentid FROM logging WHERE
servertime > %s AND event LIKE 'ping%%' GROUP BY assignmentid) AS recentPing
WHERE recentPing.assignmentid = assignments.assignmentid GROUP BY videoid)
AS workerCount
ON workerCount.videoid = videos.pk
WHERE (pictureCount.numPictures IS NULL """ + num_pics_condition +
""")
AND videos.enabled = TRUE
AND (workerCount.numWorkers <= """ + str(MAX_WORKERS_PER_VIDEO) +
""" OR workerCount.numWorkers IS NULL)
ORDER BY videos.pk DESC """, (ping_floor, ))
return result