def validationAgreed(chosen_image):
db = DBConnection()
good = getImages(db, True)
print(good)
print(chosen_image)
return chosen_image in good
def generateAndVote():
db = DBConnection()
generate = db.query_and_return_array("""SELECT * FROM assignments, slow_snapshots, study_videos
WHERE
assignments.assignmentid = slow_snapshots.assignmentid
AND assignments.videoid = study_videos.videoid
AND workerid NOT LIKE 'photographer'
ORDER BY assignments.videoid, assignments.submit""")
generate_by_video = groupByKey(generate, lambda x: x['videoid'])
lags = []
for video in generate_by_video.keys():
print(video)
generates = list(generate_by_video[video])
# get third dude
third = generates[2]
generate_lag = third['submit'] - third['slow_available_time']
#print generate_lag
votes = db.query_and_return_array("""SELECT * FROM assignments,
slow_votes, study_videos WHERE assignments.videoid = %s AND assignments.assignmentid = slow_votes.assignmentid AND assignments.videoid = study_videos.videoid ORDER BY assignments.submit""", (video, ))
fifth = votes[4]
vote_lag = fifth['submit'] - fifth['slow_voting_available_time']
#print vote_lag
lags.append(float(generate_lag + vote_lag))
print(lags[-1])
print lags
print("\n\n\nRESULTS")
print("Median: %s" % numpy.median(lags))
print("Average: %s" % numpy.mean(lags))
print("Std. Dev: %s" % numpy.std(lags))
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 test_delete_planet_not_found(self):
planet_id = 'xxxxxxxxxxxx'
table = self.create_table()
db = DBConnection()
response = db.delete_planet(planet_id)
assert bool(response) == False
def reset():
results = []
error = None
dbconnection = DBConnection()
conn = dbconnection.mongodb_conn()
if conn is not None:
dbconnection.delete_database()
return render_template('index.html', results=results, error=error)
def post(self):
planet = json.loads(self.request.body)
if 'planet_name' and 'climate' and 'terrain' not in planet:
raise Exception('Missing body arguments')
db = DBConnection()
db.insert_planet(planet)
# returnar uma mensagem de sucesso
self.write({'planet': planet})
def get(self):
try:
db = DBConnection()
planets = db.retrieve_all_planets()
for planet in planets:
planet['films'] = fetch_films(planet['planet_name'])
self.write({'planets': planets})
except Exception as e:
logger.exception("Planets::get")
raise Exception('Error to get all planets')
def test_retrieve_planet_not_found(self):
planet_id = 'yyyyyyyyyyy'
planet = {
"planet_id": planet_id,
"planet_name": "Marcela",
"terrain": "Arenoso",
"climate": "Deserto"
}
self.create_table()
db = DBConnection()
response = db.retrieve_planet('planet_id', planet_id)
self.assertEquals(response, {})
def portPhotographer():
db = DBConnection()
snapshots = db.query_and_return_array(
"""SELECT slow_snapshots.pk, slow_snapshots.location, assignments.videoid
FROM slow_snapshots, assignments, study_videos WHERE slow_snapshots.assignmentid = assignments.assignmentid AND workerid LIKE 'photographer' AND study_videos.videoid = assignments.videoid ORDER BY assignments.videoid"""
)
for snapshot in snapshots:
print(snapshot)
db.query_and_return_array(
"""INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)""",
(PHOTOGRAPHER, snapshot['videoid'], snapshot['location'],
json.dumps(dict(snapshot_pk=snapshot['pk']))))
print("Done!")
def portGenerateAndVote():
"""ports information into the algorithms table for the generate and vote condition"""
db = DBConnection()
sql = """SELECT vote_assignments.pk, study_videos.videoid, vote FROM study_videos
LEFT JOIN (SELECT slow_votes.pk, slow_votes.assignmentid, videoid, vote FROM slow_votes, assignments WHERE slow_votes.assignmentid = assignments.assignmentid) AS vote_assignments
ON vote_assignments.videoid = study_videos.videoid
WHERE slow_voting_available = TRUE ORDER BY vote_assignments.pk"""
votes = db.query_and_return_array(sql)
# we need to take just the first five votes for each video
grouped_votes = groupAssignmentsByKey(votes, lambda x: x['videoid'])
videoids = sorted(grouped_votes.keys())
votes = []
for videoid in videoids:
video_votes = sorted(list(grouped_votes[videoid]),
key=lambda x: x['pk'])
num_votes = 0
winner = None
while winner is None:
if len(video_votes) < num_votes:
print("Not enough votes to decide this case: %s", video_votes)
return
num_votes += 1
(winner, counts) = checkForWinner(video_votes[:num_votes])
vote = dict()
vote['videoid'] = videoid
vote['slow_vote_pks'] = [
vote['pk'] for vote in video_votes[:num_votes]
]
vote['count'] = counts
vote['winner'] = (Decimal(str(winner)) / 100) - Decimal(
'.01'
) # off by one frame error from jpeg filename to slider position
votes.append(vote)
print vote
# num_voters = sorted([len(vote['slow_vote_pks']) for vote in votes])
for vote in votes:
db.query_and_return_array(
"""INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)""",
(GENERATE_AND_VOTE, vote['videoid'], vote['winner'],
json.dumps(vote, cls=StudyDecimalEncoder)))
print("Done")
def test_retrieve_planet(self):
planet_id = 'xxxxxxxxxxxx'
planet = {
"planet_id": planet_id,
"planet_name": "Renata",
"terrain": "Arenoso",
"climate": "Deserto"
}
table = self.create_table()
table.put_item(Item=planet)
db = DBConnection()
response = db.retrieve_planet('planet_id', planet_id)
self.assertEquals(response, planet)
def test_delete_planet(self):
planet_id = 'yyyyyyyyyyy'
planet = {
"planet_id": planet_id,
"planet_name": "Marcela",
"terrain": "Arenoso",
"climate": "Deserto"
}
table = self.create_table()
table.put_item(Item=planet)
db = DBConnection()
response = db.delete_planet(planet_id)
assert bool(response) == True
def getPhotos(filename):
db = DBConnection()
locations = db.query_and_return_array(
"""SELECT DISTINCT location FROM pictures, videos WHERE pictures.videoid = videos.pk AND videos.filename = %s""",
(filename, ))
# don't forget off-by-one: slider starts at 0, but first photo is photo1.jpg
filenumbers = [
'%03d' % min(int(row['location'] * 100 + 1), 100) for row in locations
]
return [
VIDEO_HOST_DIRECTORY + filename + filenumber + '.jpg'
for filenumber in filenumbers
]
def delete(self, planet_id):
try:
db = DBConnection()
result = db.delete_planet(planet_id)
if not result:
self.set_status(404)
self.finish("Planet not found")
return
self.write(
{'message': 'Planet with id={} was deleted'.format(planet_id)})
except Exception as e:
logger.exception("DeletePlanet::delete")
raise Exception('Error to delete planet')
def getAssignments(experiments):
""" Queries the database for all the assignments completed in this experiment, and populates the array with all relevant timestamps """
db = DBConnection()
experimentString = ', '.join(
[str(experiment) for experiment in experiments])
results = db.query_and_return_array(
"""SELECT * from assignments a, workers w, hits h WHERE experiment IN ("""
+ experimentString +
""") AND a.workerid = w.workerid AND a.hitid = h.hitid """)
assignments = []
""" For each assignment, get its completion information """
for row in results:
assignment = Assignment()
assignment.workerid = row['workerid']
assignment.assignmentid = row['assignmentid']
assignment.wait_bucket = row['waitbucket']
assignment.precision = row['precision']
assignment.recall = row['recall']
assignment.condition = condition.getConditionName(
bool(row['is_alert']), bool(row['is_reward']),
bool(row['is_tetris']))
if row['first'] is not None:
assignment.answer_time = datetime.fromtimestamp(row['first'])
# when did the worker accept that task?
if row['accept'] is not None:
assignment.accept_time = datetime.fromtimestamp(row['accept'])
# when did the task or 'go' button appear to the user?
if row['show'] is not None:
assignment.show_time = datetime.fromtimestamp(row['show'])
# when did the worker click the "go" button?
if row['go'] is not None:
assignment.go_time = datetime.fromtimestamp(row['go'])
if row['submit'] is not None:
assignment.submit_time = datetime.fromtimestamp(row['submit'])
assignments.append(assignment)
print("Total number of assignments: %s" % (len(assignments)))
return assignments
def printCurrentlyActiveCount():
ping_floor = datetime.now() - timedelta(seconds=15)
ping_types = ["ping-waiting", "ping-showing", "ping-working"]
db = DBConnection()
results = dict()
for ping_type in ping_types:
row = db.query_and_return_array(
"""SELECT COUNT(DISTINCT assignmentid) FROM logging WHERE event='%s' AND servertime >= %s"""
% (ping_type, unixtime(ping_floor)))[0]
results[ping_type] = row['COUNT(DISTINCT assignmentid)']
print(ping_type + ": unique assignmentIds pings in last 15 seconds: " +
str(results[ping_type]))
return results
def portTakeFirst():
db = DBConnection()
sql = """SELECT slow_snapshots.pk, slow_snapshots.location, assignments.videoid FROM slow_snapshots, assignments WHERE slow_snapshots.pk IN
(SELECT MIN(slow_snapshots.pk) FROM slow_snapshots, assignments, study_videos WHERE assignments.assignmentid = slow_snapshots.assignmentid AND assignments.videoid = study_videos.videoid AND workerid NOT LIKE 'photographer' GROUP BY study_videos.videoid)
AND assignments.assignmentid = slow_snapshots.assignmentid ORDER BY assignments.videoid ASC
"""
snapshots = db.query_and_return_array(sql)
for snapshot in snapshots:
print(snapshot)
db.query_and_return_array(
"""INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)""",
(GENERATE_FIRST, snapshot['videoid'], snapshot['location'],
json.dumps(dict(snapshot_pk=snapshot['pk']))))
print("Done!")
def getSlowPhotos(videoid, limit):
db = DBConnection()
locations = db.query_and_return_array(
"""SELECT filename, location FROM slow_snapshots, assignments, videos WHERE slow_snapshots.assignmentid = assignments.assignmentid AND videos.pk = assignments.videoid AND assignments.videoid = %s ORDER BY slow_snapshots.pk ASC LIMIT %s""",
(videoid, limit))
filename = locations[0]['filename']
# don't forget off-by-one: slider starts at 0, but first photo is photo1.jpg
filenumbers = set([
'%03d' % min(int(row['location'] * 100 + 1), 100) for row in locations
]) # use set() so we don't get copies
return [
VIDEO_HOST_DIRECTORY + filename + filenumber + '.jpg'
for filenumber in filenumbers
]
def parseVideos(study_videos_only=True):
"""
Grabs each video from the DB and tells us information
about its lag
"""
db = DBConnection()
if study_videos_only:
all_videos = db.query_and_return_array(
"""SELECT pk, filename FROM videos, study_videos WHERE videos.pk = study_videos.videoid"""
)
else:
all_videos = db.query_and_return_array(
"""SELECT pk, filename FROM videos""")
printPhaseTimes(db, study_videos_only, all_videos)
print('\n')
printVideoTimes(db, study_videos_only)
def setRandomCondition(worker_id):
""" Chooses a random group to assign the worker to, and sets it in the database """
db = DBConnection()
count = db.query_and_return_array(
"""SELECT COUNT(*) FROM workers""")[0]['COUNT(*)']
index = count % len(CONDITIONS)
random_condition = CONDITIONS[index]
db.query_and_return_array(
"""INSERT INTO workers (workerid, is_alert, is_reward, is_tetris, read_instructions) VALUES (%s, %s, %s, %s, FALSE) ON DUPLICATE KEY UPDATE is_alert=%s, is_reward=%s, is_tetris=%s, read_instructions=FALSE""",
(worker_id, random_condition['is_alert'],
random_condition['is_reward'], random_condition['is_tetris'],
random_condition['is_alert'], random_condition['is_reward'],
random_condition['is_tetris']))
return random_condition
def getCondition(worker_id):
""" Sees if the worker has already been assigned a condition, and if not, assigns them """
db = DBConnection()
rows = db.query_and_return_array(
"""SELECT is_alert, is_reward, is_tetris FROM workers WHERE workerid = %s """,
(worker_id, ))
if len(rows) == 0: # not in database yet
result = setRandomCondition(worker_id)
else:
result = rows[0]
is_alert = bool(result['is_alert'])
is_reward = bool(result['is_reward'])
is_tetris = bool(result['is_tetris'])
return {'isAlert': is_alert, 'isReward': is_reward, 'isTetris': is_tetris}
def randomizeConditions(do_you_mean_it="NO"):
if do_you_mean_it != "YES_I_MEAN_IT":
print """
This will assign all workerids to a new random condition. Do not do this lightly!\n
Call with randomizeConditions('YES_I_MEAN_IT')
"""
return 0
else:
print("Assigning all workers to a new random condition")
db = DBConnection()
# get all workers, randomize them one by one
result = db.query_and_return_array("""SELECT workerid FROM workers""")
workers = [row['workerid'] for row in result]
for worker in workers:
setRandomCondition(worker)
def test_insert_planet(self):
planet = {
"planet_name": "Marino",
"terrain": "blah",
"climate": "Seco"
}
table = self.create_table()
db = DBConnection()
db.insert_planet(planet)
response = table.scan(FilterExpression=Attr('planet_name').eq('Marino'))
if 'Items' in response:
item = response['Items'][0]
self.assertTrue('planet_id' in item)
self.assertEquals(item['planet_name'], 'Marino')
self.assertEquals(item['terrain'], 'blah')
self.assertEquals(item['climate'], 'Seco')
def index():
results = []
error = None
if request.method == "POST":
dbconnection = DBConnection()
conn = dbconnection.mongodb_conn()
if request.form['text-box'] == '':
return '', 204
URL = request.form['text-box']
URL = URL.replace(" ", "")
dataSplited = list()
if (URL.endswith('.txt')):
# parseo los datos y los subo
scrapper = Scrapper(URL)
scrapper.get_data()
scrapper.parse_data()
print scrapper.dictOfWords
if conn is not None:
for key in scrapper.dictOfWords.keys():
dbconnection.save_in_database(key,
scrapper.dictOfWords[key])
print "posting data"
words = list()
# recojo los datos y los muestro
if conn is not None:
words = dbconnection.get_all_data_from_database()
for word in words:
results.append(word)
else:
error = "Database is down."
print error
return render_template('index.html', results=results, error=error)
def main():
building_db = DBConnection('building_label_db')
building_db.establish_connection()
# Tabelle anlegen
cursor = building_db.create_cursor("create")
create_building_table = '''CREATE TABLE buildings
( ID char(36) PRIMARY KEY NOT NULL,
picture_path varchar(256),
zip numeric,
city varchar(30),
street varchar(30),
number integer,
google_id char(56),
timestamp timestamp); '''
cursor.execute(create_building_table)
building_db.connection.commit()
building_db.close_cursor("create")
def answer_reviewer(answer):
result = None
approve_response = (True, APPROVE_REASON)
reject_response = (False, REJECT_REASON)
db = DBConnection()
try:
whacked = [int(s) for s in answer['m'].split('|')]
assignmentid = answer['assignmentId']
actualPosition = int(db.query_and_return_array("""SELECT moleposition FROM assignments, moles WHERE assignmentid = %s AND moles.pk = assignments.moleid""", (assignmentid, ))[0]['moleposition'])
print("Whack! Position %s, goal position %s" % (whacked, actualPosition))
if (actualPosition in whacked):
result = approve_response
else:
result = reject_response
except:
logging.exception("error reviewing answer: " + str(answer))
return result
def get(self):
try:
search_by = self.get_argument("search_by")
value = self.get_argument("value")
db = DBConnection()
planet = db.retrieve_planet(search_by, value)
if not planet:
self.set_status(404)
self.finish("Planet not found")
return
planet['films'] = fetch_films(planet['planet_name'])
self.write({'planet': planet})
except tornado.web.MissingArgumentError as argError:
logger.exception("SearchPlanet::get")
raise argError
except Exception as e:
logger.exception("SearchPlanet::get")
raise Exception('Error to search planet')
def portContinuousRefinement():
"""ports information into the algorithhms table for the continuous refinement condition"""
db = DBConnection()
sql = """SELECT * FROM pictures, study_videos, phase_lists WHERE pictures.videoid = study_videos.videoid AND pictures.phase_list = phase_lists.pk AND NOT is_historical GROUP BY pictures.videoid"""
pictures = db.query_and_return_array(sql)
if len(pictures) != NUM_VIDEOS:
print(
str(len(pictures)) + " videos instead of " + str(NUM_VIDEOS) +
". Stopping.")
print([picture['videoid'] for picture in pictures])
return
for picture in pictures:
sql = """INSERT INTO algorithms (algorithm, videoid, location, detail) VALUES (%s, %s, %s, %s)"""
detail = json.dumps(dict(phase_list=picture['phase_list']))
db.query_and_return_array(sql,
(CONTINUOUS_REFINEMENT, picture['videoid'],
picture['location'], detail))
print("done")
def test_retrieve_all_planets(self):
planet1 = {
"planet_id": 'yyyyyyyyyyy',
"planet_name": "Marcela",
"terrain": "Arenoso",
"climate": "Deserto"
}
planet2 = {
"planet_id": 'xxxxxxxxxxxx',
"planet_name": "Renata",
"terrain": "Arenoso",
"climate": "Deserto"
}
table = self.create_table()
table.put_item(Item=planet1)
table.put_item(Item=planet2)
db = DBConnection()
response = db.retrieve_all_planets()
print(response)
self.assertEquals(response[0], planet1)
self.assertEquals(response[1], planet2)
def agreedWithPhases(phases, locations, min_phases):
count_agreed = 0
db = DBConnection()
print locations
for i in range(len(phases) - 1):
# get the location we were at when the phase ended
try:
last_location = Decimal(str(locations[i]))
except IndexError:
# we don't have enough data points, maybe they came in midway through the decision
# we'll just use one they do have
last_location = Decimal(str(locations[0]))
# get the bounds that ended up being decided on (e.g., the next phase)
bounds = db.query_and_return_array(
"""SELECT min, max FROM phases WHERE phase = %s""",
(phases[i + 1], ))[0]
if bounds['min'] <= last_location and last_location <= bounds['max']:
count_agreed += 1
return count_agreed >= min_phases
