def testInsertLocation(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'location')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
submission_exec = connection.execute(submission_insert(
submitter='test_submitter', submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
answer_exec = connection.execute(answer_insert(
answer={'lon': 90, 'lat': 0},
answer_metadata={},
question_id=question_id,
submission_id=submission_id,
survey_id=survey_id,
type_constraint_name=tcn,
is_type_exception=False,
sequence_number=seq,
allow_multiple=mul))
answer_id = answer_exec.inserted_primary_key[0]
self.assertIsNotNone(answer_id)
condition = answer_table.c.answer_id == answer_id
answer = connection.execute(
answer_table.select().where(condition)).first()
location = get_geo_json(connection, answer)['coordinates']
self.assertEqual(location, [90, 0])
submission_2_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_2_id = submission_2_exec.inserted_primary_key[0]
answer_2_exec = connection.execute(answer_insert(
answer=None, question_id=question_id,
answer_metadata={},
submission_id=submission_2_id,
survey_id=survey_id,
type_constraint_name=tcn,
is_type_exception=False,
sequence_number=seq,
allow_multiple=mul))
answer_2_id = answer_2_exec.inserted_primary_key[0]
condition_2 = answer_table.c.answer_id == answer_2_id
answer_2 = connection.execute(
answer_table.select().where(condition_2)).first()
location_2 = get_geo_json(connection, answer_2)
self.assertEqual(location_2,
{'coordinates': [], 'type': 'MultiPoint'})
def _copy_submission_entries(connection: Connection,
existing_survey_id: str,
new_survey_id: str,
email: str) -> tuple:
"""
Copy submissions from an existing survey to its updated copy.
:param connection: the SQLAlchemy connection used for the transaction
:param existing_survey_id: the UUID of the existing survey
:param new_survey_id: the UUID of the survey's updated copy
:param email: the user's e-mail address
:return: a tuple containing the old and new submission IDs
"""
submissions = get_submissions_by_email(
connection, email,
survey_id=existing_survey_id
)
for sub in submissions:
values = {'submitter': sub.submitter,
'submitter_email': sub.submitter_email,
'submission_time': sub.submission_time,
'save_time': sub.save_time,
'survey_id': new_survey_id}
result = connection.execute(submission_insert(**values))
yield sub.submission_id, result.inserted_primary_key[0]
def _create_submission(connection: Connection,
survey_id: str,
required_ids: set,
submission_data: dict) -> str:
"""
Create a submission to the specified survey with the given submission
data and return the submission id.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:param required_ids: a set of UUIDs for questions which are marked
"required"
:param submission_data: the dict containing the submission information
:return: the id of the submission
:raise RequiredQuestionSkippedError: if a "required" question has no answer
"""
unanswered_required = required_ids.copy()
submitter = submission_data['submitter']
submitter_email = submission_data['submitter_email']
submission_time = submission_data.get('submission_time', None)
save_time = submission_data.get('save_time', None)
all_answers = submission_data['answers']
answers = filter(_answer_not_none, all_answers)
submission_values = {
'survey_id': survey_id,
'submitter': submitter,
'submitter_email': submitter_email,
'submission_time': submission_time,
'save_time': save_time
}
executable = submission_insert(**submission_values)
exceptions = [
('submission_survey_id_fkey',
SurveyDoesNotExistError(survey_id))
]
result = execute_with_exceptions(
connection, executable, exceptions)
submission_id = result.inserted_primary_key[0]
for answer in answers:
executable = _insert_answer(
connection, answer, submission_id, survey_id)
exceptions = [
('only_one_answer_allowed',
CannotAnswerMultipleTimesError(answer['question_id'])),
('answer_question_id_fkey',
IncorrectQuestionIdError(answer['question_id']))
]
execute_with_exceptions(connection, executable, exceptions)
unanswered_required.discard(answer['question_id'])
if unanswered_required:
raise RequiredQuestionSkippedError(unanswered_required)
return submission_id
def testAnswerInsertNoMetadata(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'integer')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
submission_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
answer_exec = connection.execute(answer_insert(
answer=1, question_id=question_id,
answer_metadata=None,
submission_id=submission_id,
survey_id=survey_id,
type_constraint_name=tcn,
is_type_exception=False,
sequence_number=seq,
allow_multiple=mul))
answer_id = answer_exec.inserted_primary_key[0]
self.assertIsNotNone(answer_id)
def testGetSubmissionsByEmail(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
for i in range(2):
connection.execute(submission_insert(
submitter='test_submitter{}'.format(i),
submitter_email='*****@*****.**', survey_id=survey_id))
submissions = get_submissions_by_email(
connection, 'test_email', survey_id=survey_id
)
self.assertEqual(submissions.rowcount, 2)
submissions = get_submissions_by_email(
connection,
'test_email',
survey_id=survey_id,
submitters=['test_submitter1']
)
self.assertEqual(submissions.rowcount, 1)
submissions = get_submissions_by_email(
connection,
'test_email',
survey_id=survey_id,
order_by='submitter', direction='desc'
)
self.assertEqual(
submissions.first()['submitter'],
'test_submitter1'
)
def testGetAnswerChoicesForChoiceId(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'multiple_choice')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
submission_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
choices = get_choices(connection, question_id)
the_choice = choices.first()
connection.execute(answer_choice_insert(
question_choice_id=the_choice.question_choice_id,
answer_choice_metadata={},
question_id=question_id,
submission_id=submission_id,
survey_id=survey_id, type_constraint_name=tcn, sequence_number=seq,
allow_multiple=mul))
gacfci = get_answer_choices_for_choice_id
actual_choices = gacfci(connection, the_choice.question_choice_id)
self.assertEqual(actual_choices.rowcount, 1)
def testAnswerChoiceInsertNoMetadata(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'multiple_choice')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
submission_exec = connection.execute(submission_insert(
submitter='test_submitter', submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
choices = get_choices(connection, question_id)
the_choice = choices.first()
exec_stmt = connection.execute(answer_choice_insert(
question_choice_id=the_choice.question_choice_id,
answer_choice_metadata=None,
question_id=question_id,
submission_id=submission_id,
survey_id=survey_id, type_constraint_name=tcn, sequence_number=seq,
allow_multiple=mul))
answer_id = exec_stmt.inserted_primary_key[0]
self.assertIsNotNone(answer_id)
def testGetAnswersForQuestion(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'integer')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
submission_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
connection.execute(answer_insert(answer=1, question_id=question_id,
answer_metadata={},
submission_id=submission_id,
survey_id=survey_id,
type_constraint_name=tcn,
is_type_exception=False,
sequence_number=seq,
allow_multiple=mul))
self.assertEqual(
get_answers_for_question(connection, question_id).rowcount, 1)
def testInsertFacility(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'facility')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
submission_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
answer_exec = connection.execute(answer_insert(
answer={'id': 'revisit ID', 'lon': 90, 'lat': 0},
answer_metadata={'facility_name': 'cool facility',
'facility_sector': 'health'},
question_id=question_id,
submission_id=submission_id,
survey_id=survey_id,
type_constraint_name=tcn,
is_type_exception=False,
sequence_number=seq,
allow_multiple=mul))
answer_id = answer_exec.inserted_primary_key[0]
self.assertIsNotNone(answer_id)
condition = answer_table.c.answer_id == answer_id
answer = connection.execute(
answer_table.select().where(condition)).first()
location = get_geo_json(connection, answer)['coordinates']
self.assertEqual(location, [90, 0])
facility_id = answer.answer_text
self.assertEqual(facility_id, 'revisit ID')
def testGetNumberOfSubmissions(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
submission_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
connection.execute(submission_table.select().where(
submission_table.c.submission_id ==
submission_id))
self.assertEqual(get_number_of_submissions(connection, survey_id), 1)
def testSubmissionInsert(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
submission_exec = connection.execute(submission_insert(
submitter='test_submitter', submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
sub_exec = connection.execute(submission_table.select().where(
submission_table.c.submission_id ==
submission_id))
submission = sub_exec.first()
self.assertEqual(submission_id, submission.submission_id)
def testGetSubmissionsWithFilter(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
q_where = question_table.select().where(
question_table.c.type_constraint_name == 'integer')
question = connection.execute(q_where).first()
question_id = question.question_id
tcn = question.type_constraint_name
seq = question.sequence_number
mul = question.allow_multiple
for i in range(2):
submission_exec = connection.execute(submission_insert(
submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
connection.execute(answer_insert(
answer=i,
question_id=question_id,
submission_id=submission_id,
answer_metadata={},
survey_id=survey_id,
type_constraint_name=tcn,
is_type_exception=False,
sequence_number=seq,
allow_multiple=mul))
self.assertEqual(
len(get_submissions_by_email(
connection,
'test_email',
survey_id=survey_id
).fetchall()),
2)
f_result = get_submissions_by_email(
connection,
'test_email',
survey_id=survey_id,
filters=[
{
'question_id': question_id,
'answer_integer': 1
}
]
).fetchall()
self.assertEqual(len(f_result), 1)
def testSubmissionSelect(self):
survey_id = connection.execute(survey_table.select().where(
survey_table.c.survey_title == 'test_title')).first().survey_id
submission_exec = connection.execute(
submission_insert(submitter='test_submitter',
submitter_email='*****@*****.**',
survey_id=survey_id))
submission_id = submission_exec.inserted_primary_key[0]
submission = submission_select(connection, submission_id,
email='test_email')
self.assertEqual(submission_id, submission.submission_id)
user_id = connection.execute(auth_user_table.select().where(
auth_user_table.c.email == 'test_email')).first().auth_user_id
submission2 = submission_select(connection, submission_id,
auth_user_id=user_id)
self.assertEqual(submission_id,
submission2.submission_id)
self.assertRaises(TypeError, submission_select, connection,
submission_id,
auth_user_id='', email='')
self.assertRaises(TypeError, submission_select, connection,
submission_id)
