def test_init_db(self):
db_logger = MTurkDataHandler('test1', file_name=self.DB_NAME)
conn = db_logger._get_connection()
c = conn.cursor()
c.execute('SELECT COUNT(*) FROM runs;')
self.assertEqual(c.fetchone()[0], 0)
c.execute('SELECT COUNT(*) FROM hits;')
self.assertEqual(c.fetchone()[0], 0)
c.execute('SELECT COUNT(*) FROM assignments;')
self.assertEqual(c.fetchone()[0], 0)
c.execute('SELECT COUNT(*) FROM workers;')
self.assertEqual(c.fetchone()[0], 0)
c.execute('SELECT COUNT(*) FROM pairings;')
self.assertEqual(c.fetchone()[0], 0)
def test_worker_workflows(self):
run_id = 'Test_run_3'
hits_created = 10
db_logger = MTurkDataHandler(run_id, file_name=self.DB_NAME)
db_logger.log_new_run(hits_created, run_id)
HIT1 = self.create_hit()
HIT2 = self.create_hit()
HIT3 = self.create_hit()
db_logger.log_hit_status(HIT1)
db_logger.log_hit_status(HIT2)
db_logger.log_hit_status(HIT3)
worker_id_1 = 'TEST_WORKER_ID_1'
worker_id_2 = 'TEST_WORKER_ID_2'
assignment_id_1 = 'TEST_ASSIGNMENT_ID_1'
assignment_id_2 = 'TEST_ASSIGNMENT_ID_2'
assignment_id_3 = 'TEST_ASSIGNMENT_ID_3'
# Create two workers and assign the 3 assignments to them
db_logger.log_worker_accept_assignment(worker_id_1, assignment_id_1,
HIT1['HIT']['HITId'])
db_logger.log_worker_accept_assignment(worker_id_2, assignment_id_2,
HIT2['HIT']['HITId'])
db_logger.log_worker_accept_assignment(worker_id_2, assignment_id_3,
HIT3['HIT']['HITId'])
# Ensure two workers have been created
conn = db_logger._get_connection()
c = conn.cursor()
c.execute('SELECT COUNT(*) FROM workers;')
self.assertEqual(c.fetchone()[0], 2)
# Ensure non-existent worker is None
self.assertIsNone(db_logger.get_worker_data('fake_id'))
# Ensure the two workers have the correct expected values
worker_1_data = db_logger.get_worker_data(worker_id_1)
worker_2_data = db_logger.get_worker_data(worker_id_2)
self.assertEqual(worker_1_data['worker_id'], worker_id_1)
self.assertEqual(worker_1_data['accepted'], 1)
self.assertEqual(worker_1_data['disconnected'], 0)
self.assertEqual(worker_1_data['completed'], 0)
self.assertEqual(worker_1_data['approved'], 0)
self.assertEqual(worker_1_data['rejected'], 0)
self.assertEqual(worker_2_data['worker_id'], worker_id_2)
self.assertEqual(worker_2_data['accepted'], 2)
self.assertEqual(worker_2_data['disconnected'], 0)
self.assertEqual(worker_2_data['completed'], 0)
self.assertEqual(worker_2_data['approved'], 0)
self.assertEqual(worker_2_data['rejected'], 0)
# Ensure all the assignments are marked as accepted
c.execute('SELECT COUNT(*) FROM assignments WHERE status = ?;',
('Accepted', ))
self.assertEqual(c.fetchone()[0], 3)
# Ensure non-existing assign is None
self.assertIsNone(db_logger.get_assignment_data('fake_id'))
# Check each of the assignments
assignment_1_data = db_logger.get_assignment_data(assignment_id_1)
assignment_2_data = db_logger.get_assignment_data(assignment_id_2)
assignment_3_data = db_logger.get_assignment_data(assignment_id_3)
self.assertEqual(assignment_1_data['assignment_id'], assignment_id_1)
self.assertEqual(assignment_1_data['status'], 'Accepted')
self.assertEqual(assignment_1_data['approve_time'], None)
self.assertEqual(assignment_1_data['worker_id'], worker_id_1)
self.assertEqual(assignment_1_data['hit_id'], HIT1['HIT']['HITId'])
self.assertEqual(assignment_2_data['assignment_id'], assignment_id_2)
self.assertEqual(assignment_2_data['status'], 'Accepted')
self.assertEqual(assignment_2_data['approve_time'], None)
self.assertEqual(assignment_2_data['worker_id'], worker_id_2)
self.assertEqual(assignment_2_data['hit_id'], HIT2['HIT']['HITId'])
self.assertEqual(assignment_3_data['assignment_id'], assignment_id_3)
self.assertEqual(assignment_3_data['status'], 'Accepted')
self.assertEqual(assignment_3_data['approve_time'], None)
self.assertEqual(assignment_3_data['worker_id'], worker_id_2)
self.assertEqual(assignment_3_data['hit_id'], HIT3['HIT']['HITId'])
# Ensure three pairings have been created, one for each assignment
c.execute('SELECT COUNT(*) FROM pairings')
self.assertEqual(c.fetchone()[0], 3)
# Ensure pairings are accurate
self.assertIsNone(
db_logger.get_worker_assignment_pairing(worker_id_1,
assignment_id_3))
pair_1 = db_logger.get_worker_assignment_pairing(
worker_id_1, assignment_id_1)
pair_2 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_2)
pair_3 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_3)
for f in [
'onboarding_start', 'onboarding_end', 'task_start', 'task_end',
'conversation_id'
]:
for pair in [pair_1, pair_2, pair_3]:
self.assertIsNone(pair[f])
self.assertEqual(pair_1['status'], AssignState.STATUS_NONE)
self.assertEqual(pair_2['status'], AssignState.STATUS_NONE)
self.assertEqual(pair_3['status'], AssignState.STATUS_NONE)
self.assertEqual(pair_1['worker_id'], worker_id_1)
self.assertEqual(pair_2['worker_id'], worker_id_2)
self.assertEqual(pair_3['worker_id'], worker_id_2)
self.assertEqual(pair_1['assignment_id'], assignment_id_1)
self.assertEqual(pair_2['assignment_id'], assignment_id_2)
self.assertEqual(pair_3['assignment_id'], assignment_id_3)
self.assertEqual(pair_1['run_id'], run_id)
self.assertEqual(pair_2['run_id'], run_id)
self.assertEqual(pair_3['run_id'], run_id)
self.assertEqual(pair_1['bonus_amount'], 0)
self.assertEqual(pair_2['bonus_amount'], 0)
self.assertEqual(pair_3['bonus_amount'], 0)
self.assertEqual(pair_1['bonus_text'], '')
self.assertEqual(pair_2['bonus_text'], '')
self.assertEqual(pair_3['bonus_text'], '')
self.assertFalse(pair_1['bonus_paid'])
self.assertFalse(pair_2['bonus_paid'])
self.assertFalse(pair_3['bonus_paid'])
# Ensure get_pairings_for_assignment works
pair_4 = db_logger.get_pairings_for_assignment(assignment_id_2)[0]
self.assertEqual(pair_2, pair_4)
self.assertListEqual([],
db_logger.get_pairings_for_assignment('fake_id'))
# Ensure get_all_<thing>_for_worker works
self.assertListEqual(
[], db_logger.get_all_assignments_for_worker('fake_id'))
self.assertListEqual([],
db_logger.get_all_pairings_for_worker('fake_id'))
self.assertEqual(
db_logger.get_all_assignments_for_worker(worker_id_1)[0],
assignment_1_data)
self.assertEqual(
len(db_logger.get_all_assignments_for_worker(worker_id_2)), 2)
self.assertEqual(
db_logger.get_all_pairings_for_worker(worker_id_1)[0], pair_1)
self.assertEqual(
len(db_logger.get_all_pairings_for_worker(worker_id_2)), 2)
# test task_restricted gets
self.assertEqual(
db_logger.get_all_task_assignments_for_worker(worker_id_1)[0],
assignment_1_data)
self.assertEqual(
len(db_logger.get_all_task_assignments_for_worker(worker_id_2)), 2)
self.assertEqual(
len(
db_logger.get_all_task_assignments_for_worker(
worker_id_1, 'fake_id')), 0)
self.assertEqual(
db_logger.get_all_task_pairings_for_worker(worker_id_1)[0], pair_1)
self.assertEqual(
len(db_logger.get_all_task_pairings_for_worker(worker_id_2)), 2)
self.assertEqual(
len(
db_logger.get_all_task_pairings_for_worker(
worker_id_1, 'fake_id')), 0)
conversation_id_1 = "CONV_ID_1"
conversation_id_2 = "CONV_ID_2"
db_logger.log_start_onboard(worker_id_1, assignment_id_1)
db_logger.log_start_onboard(worker_id_2, assignment_id_2)
db_logger.log_start_onboard(worker_id_2, assignment_id_3)
db_logger.log_finish_onboard(worker_id_1, assignment_id_1)
db_logger.log_finish_onboard(worker_id_2, assignment_id_2)
db_logger.log_finish_onboard(worker_id_2, assignment_id_3)
db_logger.log_start_task(worker_id_1, assignment_id_1,
conversation_id_1)
db_logger.log_start_task(worker_id_2, assignment_id_2,
conversation_id_1)
db_logger.log_start_task(worker_id_2, assignment_id_3,
conversation_id_2)
# Check to see retrieval by conversation
pairs_1 = db_logger.get_pairings_for_conversation(conversation_id_1)
pairs_2 = db_logger.get_pairings_for_conversation(conversation_id_2)
pairs_3 = db_logger.get_pairings_for_conversation('fake_id')
pair_1 = db_logger.get_worker_assignment_pairing(
worker_id_1, assignment_id_1)
pair_2 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_2)
pair_3 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_3)
self.assertEqual(pairs_1[0], pair_1)
self.assertEqual(pairs_1[1], pair_2)
self.assertEqual(pairs_2[0], pair_3)
self.assertEqual(len(pairs_3), 0)
# Do some final processing on assignments
db_logger.log_complete_assignment(
worker_id_1, assignment_id_1, time.time(),
AssignState.STATUS_PARTNER_DISCONNECT)
db_logger.log_disconnect_assignment(worker_id_2, assignment_id_2,
time.time(),
AssignState.STATUS_DISCONNECT)
db_logger.log_complete_assignment(worker_id_2, assignment_id_3,
time.time(), AssignState.STATUS_DONE)
# Assignment state consistent
assignment_1_data = db_logger.get_assignment_data(assignment_id_1)
assignment_2_data = db_logger.get_assignment_data(assignment_id_2)
assignment_3_data = db_logger.get_assignment_data(assignment_id_3)
self.assertEqual(assignment_1_data['assignment_id'], assignment_id_1)
self.assertEqual(assignment_1_data['status'], 'Completed')
self.assertIsNotNone(assignment_1_data['approve_time'])
self.assertEqual(assignment_1_data['worker_id'], worker_id_1)
self.assertEqual(assignment_1_data['hit_id'], HIT1['HIT']['HITId'])
self.assertEqual(assignment_2_data['assignment_id'], assignment_id_2)
self.assertEqual(assignment_2_data['status'], 'Completed')
self.assertIsNotNone(assignment_2_data['approve_time'])
self.assertEqual(assignment_2_data['worker_id'], worker_id_2)
self.assertEqual(assignment_2_data['hit_id'], HIT2['HIT']['HITId'])
self.assertEqual(assignment_3_data['assignment_id'], assignment_id_3)
self.assertEqual(assignment_3_data['status'], 'Completed')
self.assertIsNotNone(assignment_3_data['approve_time'])
self.assertEqual(assignment_3_data['worker_id'], worker_id_2)
self.assertEqual(assignment_3_data['hit_id'], HIT3['HIT']['HITId'])
# Worker state consistent
worker_1_data = db_logger.get_worker_data(worker_id_1)
worker_2_data = db_logger.get_worker_data(worker_id_2)
self.assertEqual(worker_1_data['worker_id'], worker_id_1)
self.assertEqual(worker_1_data['accepted'], 1)
self.assertEqual(worker_1_data['disconnected'], 0)
self.assertEqual(worker_1_data['completed'], 1)
self.assertEqual(worker_1_data['approved'], 0)
self.assertEqual(worker_1_data['rejected'], 0)
self.assertEqual(worker_2_data['worker_id'], worker_id_2)
self.assertEqual(worker_2_data['accepted'], 2)
self.assertEqual(worker_2_data['disconnected'], 1)
self.assertEqual(worker_2_data['completed'], 1)
self.assertEqual(worker_2_data['approved'], 0)
self.assertEqual(worker_2_data['rejected'], 0)
# Ensure Pairing state is consistent
pair_1 = db_logger.get_worker_assignment_pairing(
worker_id_1, assignment_id_1)
pair_2 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_2)
pair_3 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_3)
self.assertEqual(pair_1['status'],
AssignState.STATUS_PARTNER_DISCONNECT)
self.assertEqual(pair_2['status'], AssignState.STATUS_DISCONNECT)
self.assertEqual(pair_3['status'], AssignState.STATUS_DONE)
self.assertEqual(pair_1['worker_id'], worker_id_1)
self.assertEqual(pair_2['worker_id'], worker_id_2)
self.assertEqual(pair_3['worker_id'], worker_id_2)
self.assertEqual(pair_1['assignment_id'], assignment_id_1)
self.assertEqual(pair_2['assignment_id'], assignment_id_2)
self.assertEqual(pair_3['assignment_id'], assignment_id_3)
self.assertEqual(pair_1['conversation_id'], conversation_id_1)
self.assertEqual(pair_2['conversation_id'], conversation_id_1)
self.assertEqual(pair_3['conversation_id'], conversation_id_2)
self.assertGreaterEqual(pair_1['onboarding_end'],
pair_1['onboarding_start'])
self.assertGreaterEqual(pair_2['onboarding_end'],
pair_2['onboarding_start'])
self.assertGreaterEqual(pair_3['onboarding_end'],
pair_3['onboarding_start'])
self.assertGreaterEqual(pair_1['task_start'], pair_1['onboarding_end'])
self.assertGreaterEqual(pair_2['task_start'], pair_2['onboarding_end'])
self.assertGreaterEqual(pair_3['task_start'], pair_3['onboarding_end'])
self.assertGreaterEqual(pair_1['task_end'], pair_1['onboarding_start'])
self.assertGreaterEqual(pair_2['task_end'], pair_2['onboarding_start'])
self.assertGreaterEqual(pair_3['task_end'], pair_3['onboarding_start'])
self.assertEqual(pair_1['run_id'], run_id)
self.assertEqual(pair_2['run_id'], run_id)
self.assertEqual(pair_3['run_id'], run_id)
self.assertEqual(pair_1['bonus_amount'], 0)
self.assertEqual(pair_2['bonus_amount'], 0)
self.assertEqual(pair_3['bonus_amount'], 0)
self.assertEqual(pair_1['bonus_text'], '')
self.assertEqual(pair_2['bonus_text'], '')
self.assertEqual(pair_3['bonus_text'], '')
self.assertFalse(pair_1['bonus_paid'])
self.assertFalse(pair_2['bonus_paid'])
self.assertFalse(pair_3['bonus_paid'])
# Ensure run state is consistent
run_data = db_logger.get_run_data(run_id)
self.assertEqual(run_data['run_id'], run_id)
self.assertEqual(run_data['created'], 3)
self.assertEqual(run_data['completed'], 2)
self.assertEqual(run_data['maximum'], hits_created)
self.assertEqual(run_data['failed'], 1)
# Test "submitting" and abandoning hits
db_logger.log_submit_assignment(worker_id_1, assignment_id_1)
db_logger.log_abandon_assignment(worker_id_2, assignment_id_2)
db_logger.log_submit_assignment(worker_id_2, assignment_id_3)
assignment_1_data = db_logger.get_assignment_data(assignment_id_1)
assignment_2_data = db_logger.get_assignment_data(assignment_id_2)
assignment_3_data = db_logger.get_assignment_data(assignment_id_3)
self.assertEqual(assignment_1_data['assignment_id'], assignment_id_1)
self.assertEqual(assignment_1_data['status'], 'Reviewable')
self.assertIsNotNone(assignment_1_data['approve_time'])
self.assertEqual(assignment_1_data['worker_id'], worker_id_1)
self.assertEqual(assignment_1_data['hit_id'], HIT1['HIT']['HITId'])
self.assertEqual(assignment_2_data['assignment_id'], assignment_id_2)
self.assertEqual(assignment_2_data['status'], 'Abandoned')
self.assertIsNotNone(assignment_2_data['approve_time'])
self.assertEqual(assignment_2_data['worker_id'], worker_id_2)
self.assertEqual(assignment_2_data['hit_id'], HIT2['HIT']['HITId'])
self.assertEqual(assignment_3_data['assignment_id'], assignment_id_3)
self.assertEqual(assignment_3_data['status'], 'Reviewable')
self.assertIsNotNone(assignment_3_data['approve_time'])
self.assertEqual(assignment_3_data['worker_id'], worker_id_2)
self.assertEqual(assignment_3_data['hit_id'], HIT3['HIT']['HITId'])
# Test approving and rejecting
amount_use = 3
reason_use = 'Just because'
db_logger.log_award_amount(worker_id_1, assignment_id_1, amount_use,
reason_use)
db_logger.log_award_amount(worker_id_2, assignment_id_2, amount_use,
reason_use)
db_logger.log_bonus_paid(worker_id_1, assignment_id_1)
db_logger.log_approve_assignment(worker_id_1, assignment_id_1)
db_logger.log_approve_assignment(worker_id_2, assignment_id_2)
db_logger.log_reject_assignment(worker_id_2, assignment_id_3)
# Ensure state is valid again
assignment_1_data = db_logger.get_assignment_data(assignment_id_1)
assignment_2_data = db_logger.get_assignment_data(assignment_id_2)
assignment_3_data = db_logger.get_assignment_data(assignment_id_3)
self.assertEqual(assignment_1_data['assignment_id'], assignment_id_1)
self.assertEqual(assignment_1_data['status'], 'Approved')
self.assertIsNotNone(assignment_1_data['approve_time'])
self.assertEqual(assignment_1_data['worker_id'], worker_id_1)
self.assertEqual(assignment_1_data['hit_id'], HIT1['HIT']['HITId'])
self.assertEqual(assignment_2_data['assignment_id'], assignment_id_2)
self.assertEqual(assignment_2_data['status'], 'Approved')
self.assertIsNotNone(assignment_2_data['approve_time'])
self.assertEqual(assignment_2_data['worker_id'], worker_id_2)
self.assertEqual(assignment_2_data['hit_id'], HIT2['HIT']['HITId'])
self.assertEqual(assignment_3_data['assignment_id'], assignment_id_3)
self.assertEqual(assignment_3_data['status'], 'Rejected')
self.assertIsNotNone(assignment_3_data['approve_time'])
self.assertEqual(assignment_3_data['worker_id'], worker_id_2)
self.assertEqual(assignment_3_data['hit_id'], HIT3['HIT']['HITId'])
worker_1_data = db_logger.get_worker_data(worker_id_1)
worker_2_data = db_logger.get_worker_data(worker_id_2)
self.assertEqual(worker_1_data['worker_id'], worker_id_1)
self.assertEqual(worker_1_data['accepted'], 1)
self.assertEqual(worker_1_data['disconnected'], 0)
self.assertEqual(worker_1_data['completed'], 1)
self.assertEqual(worker_1_data['approved'], 1)
self.assertEqual(worker_1_data['rejected'], 0)
self.assertEqual(worker_2_data['worker_id'], worker_id_2)
self.assertEqual(worker_2_data['accepted'], 2)
self.assertEqual(worker_2_data['disconnected'], 1)
self.assertEqual(worker_2_data['completed'], 1)
self.assertEqual(worker_2_data['approved'], 1)
self.assertEqual(worker_2_data['rejected'], 1)
pair_1 = db_logger.get_worker_assignment_pairing(
worker_id_1, assignment_id_1)
pair_2 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_2)
pair_3 = db_logger.get_worker_assignment_pairing(
worker_id_2, assignment_id_3)
self.assertEqual(pair_1['status'],
AssignState.STATUS_PARTNER_DISCONNECT)
self.assertEqual(pair_2['status'], AssignState.STATUS_DISCONNECT)
self.assertEqual(pair_3['status'], AssignState.STATUS_DONE)
self.assertEqual(pair_1['worker_id'], worker_id_1)
self.assertEqual(pair_2['worker_id'], worker_id_2)
self.assertEqual(pair_3['worker_id'], worker_id_2)
self.assertEqual(pair_1['assignment_id'], assignment_id_1)
self.assertEqual(pair_2['assignment_id'], assignment_id_2)
self.assertEqual(pair_3['assignment_id'], assignment_id_3)
self.assertEqual(pair_1['conversation_id'], conversation_id_1)
self.assertEqual(pair_2['conversation_id'], conversation_id_1)
self.assertEqual(pair_3['conversation_id'], conversation_id_2)
self.assertGreaterEqual(pair_1['onboarding_end'],
pair_1['onboarding_start'])
self.assertGreaterEqual(pair_2['onboarding_end'],
pair_2['onboarding_start'])
self.assertGreaterEqual(pair_3['onboarding_end'],
pair_3['onboarding_start'])
self.assertGreaterEqual(pair_1['task_start'], pair_1['onboarding_end'])
self.assertGreaterEqual(pair_2['task_start'], pair_2['onboarding_end'])
self.assertGreaterEqual(pair_3['task_start'], pair_3['onboarding_end'])
self.assertGreaterEqual(pair_1['task_end'], pair_1['onboarding_start'])
self.assertGreaterEqual(pair_2['task_end'], pair_2['onboarding_start'])
self.assertGreaterEqual(pair_3['task_end'], pair_3['onboarding_start'])
self.assertEqual(pair_1['run_id'], run_id)
self.assertEqual(pair_2['run_id'], run_id)
self.assertEqual(pair_3['run_id'], run_id)
self.assertEqual(pair_1['bonus_amount'], amount_use)
self.assertEqual(pair_2['bonus_amount'], amount_use)
self.assertEqual(pair_3['bonus_amount'], 0)
self.assertEqual(pair_1['bonus_text'], reason_use)
self.assertEqual(pair_2['bonus_text'], reason_use)
self.assertEqual(pair_3['bonus_text'], '')
self.assertTrue(pair_1['bonus_paid'])
self.assertFalse(pair_2['bonus_paid'])
self.assertFalse(pair_3['bonus_paid'])
