Example #1
0
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
Example #2
0
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
    """
Example #3
0
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)
Example #4
0
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
Example #5
0
 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
Example #6
0
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
Example #7
0
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