def _insert_answer(connection: Connection, answer: dict, submission_id: str, survey_id: str) -> Insert: """ Insert an answer from a submission into either the answer or answer_choice table. Don't forget to use a transaction! :param connection: a SQLAlchemy Connection :param answer: a dictionary of the answer values :param submission_id: the UUID of the submission :param survey_id: the UUID of the survey :return: the Insert object. Execute this! """ # Add a few fields to the answer dict value_dict = answer.copy() value_dict['submission_id'] = submission_id value_dict['survey_id'] = survey_id question = question_select(connection, value_dict['question_id']) value_dict['type_constraint_name'] = question.type_constraint_name value_dict['sequence_number'] = question.sequence_number value_dict['allow_multiple'] = question.allow_multiple # determine whether this is a choice selection is_mc = question.type_constraint_name == 'multiple_choice' is_type_exception = value_dict.get('is_type_exception') if is_mc and not is_type_exception: value_dict['question_choice_id'] = value_dict.pop('answer') # Might want to change 'answer_choice_metadata' to 'answer_metadata'... answer_metadata = value_dict.pop('answer_metadata') value_dict['answer_choice_metadata'] = answer_metadata insert = answer_choice_insert else: insert = answer_insert return insert(**value_dict)
def testQuestionSelect(self): survey_id = connection.execute(survey_table.select().where( survey_table.c.survey_title == 'test_title')).first().survey_id question_id = get_questions_no_credentials( connection, survey_id).first().question_id question = question_select(connection, question_id) self.assertEqual(question.question_id, question_id)
def _jsonify(connection: Connection, answer: object, question_id: str) -> object: """ This function returns a "nice" representation of an answer which can be serialized as JSON. :param connection: a SQLAlchemy Connection :param answer: a submitted value :param type_constraint_name: the UUID of the question :return: the nice representation """ type_constraint_name = question_select(connection, question_id).type_constraint_name if type_constraint_name in {'location', 'facility'}: geo_json = connection.execute(func.ST_AsGeoJSON(answer)).scalar() return json_decode(geo_json)['coordinates'] elif type_constraint_name in {'date', 'time'}: return maybe_isoformat(answer) elif type_constraint_name == 'decimal': return float(answer) elif type_constraint_name == 'multiple_choice': question_choice = question_choice_select(connection, answer) return question_choice.choice else: return answer
def bar_graph(connection: Connection, question_id: str, auth_user_id: str=None, email: str=None, limit: [int, None]=None, count_order: bool=False) -> dict: """ Get a list of the number of times each submission value appears. You must provide either an auth_user_id or e-mail address. :param connection: a SQLAlchemy Connection :param question_id: the UUID of the question :param auth_user_id: the UUID of the user :param email: the e-mail address of the user. :param limit: a limit on the number of results :param count_order: whether to order from largest count to smallest :return: a JSON dict containing the result [[values], [counts]] """ user_id = _get_user_id(connection, auth_user_id, email) allowable_types = {'text', 'integer', 'decimal', 'multiple_choice', 'date', 'time', 'location', 'facility'} question = question_select(connection, question_id) tcn = _get_type_constraint_name(allowable_types, question) # Assume that you only want to consider the non-other answers original_table, column_name = _table_and_column(tcn) table = original_table.join( question_table, original_table.c.question_id == question_table.c.question_id ).join(survey_table) conds = [question_table.c.question_id == question_id, survey_table.c.auth_user_id == user_id] column = get_column(original_table, column_name) column_query = select( [column, sqlcount(column)] ).select_from(table).group_by(column) ordering = desc(sqlcount(column)) if count_order else column ordered_query = column_query.order_by(ordering) result = connection.execute( ordered_query.where(and_(*conds)).limit(limit) ) result = _return_sql(connection, result, question.survey_id, user_id, question_id) bar_graph_result = [[_jsonify(connection, r[0], question_id), r[1]] for r in result] response = json_response( _return_sql(connection, bar_graph_result, question.survey_id, user_id, question_id)) response['query'] = 'bar_graph' return response
def time_series(connection: Connection, question_id: str, auth_user_id: str=None, email: str=None) -> dict: """ Get a list of submissions to the specified question over time. You must provide either an auth_user_id or e-mail address. :param connection: a SQLAlchemy Connection :param question_id: the UUID of the question :param auth_user_id: the UUID of the user :param email: the e-mail address of the user. :return: a JSON dict containing the result [[times], [values]] """ user_id = _get_user_id(connection, auth_user_id, email) allowable_types = {'text', 'integer', 'decimal', 'multiple_choice', 'date', 'time', 'location'} question = question_select(connection, question_id) tcn = _get_type_constraint_name(allowable_types, question) # Assume that you only want to consider the non-other answers original_table, column_name = _table_and_column(tcn) table = original_table.join( survey_table, original_table.c.survey_id == survey_table.c.survey_id ).join( submission_table, original_table.c.submission_id == submission_table.c.submission_id ) column = get_column(original_table, column_name) where_stmt = select( [column, submission_table.c.submission_time] ).select_from(table).where( original_table.c.question_id == question_id ).where( survey_table.c.auth_user_id == user_id ) result = _return_sql( connection, connection.execute(where_stmt.order_by('submission_time asc')), question.survey_id, auth_user_id, question_id) tsr = [[r.submission_time.isoformat(), _jsonify(connection, r[column_name], question_id)] for r in result] time_series_result = tsr response = json_response( _return_sql(connection, time_series_result, question.survey_id, user_id, question_id)) response['query'] = 'time_series' return response
def _get_fields(connection: Connection, answer: RowProxy) -> dict: """ Extract the relevant fields for an answer (from the answer or answer_choice table). :param connection: a SQLAlchemy connection :param answer: A record in the answer or answer_choice table :return: A dictionary of the fields. """ tcn = answer.type_constraint_name question_id = answer.question_id question = question_select(connection, question_id) result_dict = {'question_id': question_id, 'question_title': question.question_title, 'sequence_number': question.sequence_number, 'type_constraint_name': tcn, 'is_type_exception': _get_is_type_exception(answer)} try: # Get the choice for a multiple choice question choice_id = answer.question_choice_id result_dict['answer'] = choice_id result_dict['answer_choice_metadata'] = answer.answer_choice_metadata result_dict['answer_id'] = answer.answer_choice_id choice = question_choice_select(connection, choice_id) result_dict['choice'] = choice.choice result_dict['choice_number'] = choice.choice_number except AttributeError: # The answer is not a choice question = question_select(connection, answer.question_id) if answer.is_type_exception: tcn = 'text' result_dict['answer'] = _jsonify(connection, answer, tcn) result_dict['answer_metadata'] = answer.answer_metadata result_dict['answer_id'] = answer.answer_id result_dict['choice'] = None result_dict['choice_number'] = None return result_dict
def _scalar(connection: Connection, question_id: str, sql_function: GenericFunction, *, auth_user_id: str=None, email: str=None, is_type_exception: bool=False, allowable_types: set={'integer', 'decimal'}) -> Real: """ Get a scalar SQL-y value (max, mean, etc) across all submissions to a question. You must provide either an auth_user_id or e-mail address. :param connection: a SQLAlchemy Connection :param question_id: the UUID of the question :param sql_function: the SQL function to execute :param auth_user_id: the UUID of the user :param email: the e-mail address of the user :param is_type_exception: whether to look at the "other"/"don't know"/etc responses :return: the result of the SQL function :raise InvalidTypeForAggregationError: if the type constraint name is bad """ user_id = _get_user_id(connection, auth_user_id, email) question = question_select(connection, question_id) conds = [question_table.c.question_id == question_id, survey_table.c.auth_user_id == user_id] if is_type_exception: original_table = answer_table column_name = 'answer_text' else: tcn = _get_type_constraint_name(allowable_types, question) original_table, column_name = _table_and_column(tcn) table = original_table.join( question_table, original_table.c.question_id == question_table.c.question_id ).join(survey_table) if is_type_exception: conds.append(original_table.c.is_type_exception) column = get_column(original_table, column_name) result = connection.execute(select([sql_function(column)]).select_from( table).where(and_(*conds))).scalar() return _return_sql(connection, result, question.survey_id, user_id, question_id)
def get(self, question_id: str): question = question_select(self.db, question_id) answers = get_answers_for_question(self.db, question_id) time_data, bar_data, map_data = None, None, None if question.type_constraint_name in {'integer', 'decimal'}: try: data = time_series(self.db, question_id, email=self.current_user) time_data = data['result'] except NoSubmissionsToQuestionError: pass if question.type_constraint_name in {'text', 'integer', 'decimal', 'date', 'time', 'location', 'multiple_choice'}: try: data = bar_graph(self.db, question_id, email=self.current_user) bar_data = data['result'] except NoSubmissionsToQuestionError: pass if question.type_constraint_name in {'location', 'facility'}: map_data = list(self._get_map_data(answers)) self.render('visualization.html', time_data=time_data, bar_data=bar_data, map_data=map_data)
def _create_questions(connection: Connection, questions: list, survey_id: str, submission_map: dict=None) -> Iterator: """ Create the questions of a survey. If this is an update to an existing survey, it will also copy over answers to the questions. :param connection: the SQLAlchemy Connection object for the transaction :param questions: a list of dictionaries, each containing the values associated with a question :param survey_id: the UUID of the survey :param submission_map: a dictionary mapping old submission_id to new :return: an iterable of the resultant question fields """ for number, question in enumerate(questions, start=1): values = question.copy() values['sequence_number'] = number values['survey_id'] = survey_id existing_q_id = values.pop('question_id', None) executable = question_insert(**values) tcn = values['type_constraint_name'] exceptions = [('question_type_constraint_name_fkey', TypeConstraintDoesNotExistError(tcn)), ('minimal_logic', MissingMinimalLogicError(values['logic']))] result = execute_with_exceptions(connection, executable, exceptions) result_ipk = result.inserted_primary_key q_id = result_ipk[0] choices = list(_create_choices(connection, values, q_id, submission_map=submission_map, existing_question_id=existing_q_id)) if existing_q_id is not None: question_fields = {'question_id': q_id, 'sequence_number': result_ipk[1], 'allow_multiple': result_ipk[2], 'type_constraint_name': result_ipk[3], 'survey_id': survey_id} for answer in get_answers_for_question(connection, existing_q_id): new_tcn = result_ipk[3] old_tcn = question_select(connection, existing_q_id).type_constraint_name if new_tcn != old_tcn: continue answer_values = question_fields.copy() answer_values['answer_metadata'] = answer.answer_metadata new_submission_id = submission_map[answer.submission_id] is_type_exception = _get_is_type_exception(answer) answer_values['is_type_exception'] = is_type_exception if is_type_exception: answer_values['answer'] = answer.answer_text else: answer_values['answer'] = answer['answer_' + new_tcn] allow_other = values['logic']['allow_other'] allow_dont_know = values['logic']['allow_dont_know'] with_type_exception = allow_other or allow_dont_know if new_tcn == 'multiple_choice' and not with_type_exception: continue answer_values['submission_id'] = new_submission_id connection.execute(answer_insert(**answer_values)) q_to_seq_number = values['question_to_sequence_number'] yield {'question_id': q_id, 'type_constraint_name': tcn, 'sequence_number': values['sequence_number'], 'allow_multiple': values['allow_multiple'], 'choice_ids': choices, 'question_to_sequence_number': q_to_seq_number}
def wrapper(self, question_id: str, *args): question = question_select(self.db, question_id) authorized_email = get_email_address(self.db, question.survey_id) if self.current_user != authorized_email: raise tornado.web.HTTPError(404) return method(self, question_id, *args)