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)
