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)