def update(connection: Connection, data: dict):
"""
Update a survey (title, questions). You can also add or modify questions
here. Note that this creates a new survey (with new submissions, etc),
copying everything from the old survey. The old survey's title will be
changed to end with "(new version created on <time>)".
:param connection: a SQLAlchemy Connection
:param data: JSON containing the UUID of the survey and fields to update.
"""
survey_id = data['survey_id']
email = data['email']
existing_survey = survey_select(connection, survey_id, email=email)
if 'survey_metadata' not in data:
data['survey_metadata'] = existing_survey.survey_metadata
update_time = datetime.datetime.now()
with connection.begin():
new_title = '{} (new version created on {})'.format(
existing_survey.survey_title, update_time.isoformat())
executable = update_record(survey_table, 'survey_id', survey_id,
survey_title=new_title)
exc = [('survey_title_survey_owner_key',
SurveyAlreadyExistsError(new_title))]
execute_with_exceptions(connection, executable, exc)
new_survey_id = _create_survey(connection, data)
return get_one(connection, new_survey_id, email=email)
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 _create_branches(connection: Connection,
questions_json: list,
question_dicts: list,
survey_id: str):
"""
Create the branches in a survey.
:param connection: the SQLAlchemy Connection object for the transaction
:param questions_json: a list of dictionaries coming from the JSON input
:param question_dicts: a list of dictionaries resulting from inserting
the questions
:param survey_id: the UUID of the survey
"""
for index, question_dict in enumerate(questions_json):
from_dict = question_dicts[index]
from_q_id = from_dict['question_id']
branches = question_dict['branches']
if branches is None:
continue
for branch in branches:
choice_index = branch['choice_number']
question_choice_id = from_dict['choice_ids'][choice_index]
from_tcn = question_dict['type_constraint_name']
from_mul = from_dict['allow_multiple']
to_question_index = branch['to_question_number'] - 1
to_question_id = question_dicts[to_question_index]['question_id']
to_tcn = question_dicts[to_question_index]['type_constraint_name']
to_seq = question_dicts[to_question_index]['sequence_number']
to_mul = question_dicts[to_question_index]['allow_multiple']
branch_dict = {'question_choice_id': question_choice_id,
'from_question_id': from_q_id,
'from_type_constraint': from_tcn,
'from_sequence_number': index + 1,
'from_allow_multiple': from_mul,
'from_survey_id': survey_id,
'to_question_id': to_question_id,
'to_type_constraint': to_tcn,
'to_sequence_number': to_seq,
'to_allow_multiple': to_mul,
'to_survey_id': survey_id}
executable = question_branch_insert(**branch_dict)
exc = [('question_branch_from_question_id_question_choice_id_key',
MultipleBranchError(question_choice_id))]
execute_with_exceptions(connection, executable, exc)
def _create_choices(connection: Connection,
values: dict,
question_id: str,
submission_map: dict,
existing_question_id: str=None) -> Iterator:
"""
Create the choices of a survey question. 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 values: the dictionary of values associated with the question
:param question_id: the UUID of the question
:param submission_map: a dictionary mapping old submission_id to new
:param existing_question_id: the UUID of the existing question (if this is
an update)
:return: an iterable of the resultant choice fields
"""
choices = values['choices']
new_choices, updates = _determine_choices(connection, existing_question_id,
choices)
for number, choice in enumerate(new_choices):
choice_dict = {
'question_id': question_id,
'survey_id': values['survey_id'],
'choice': choice,
'choice_number': number,
'type_constraint_name': values['type_constraint_name'],
'question_sequence_number': values['sequence_number'],
'allow_multiple': values['allow_multiple']}
executable = question_choice_insert(**choice_dict)
exc = [('unique_choice_names', RepeatedChoiceError(choice))]
result = execute_with_exceptions(connection, executable, exc)
result_ipk = result.inserted_primary_key
question_choice_id = result_ipk[0]
if choice in updates:
question_fields = {'question_id': question_id,
'type_constraint_name': result_ipk[2],
'sequence_number': result_ipk[3],
'allow_multiple': result_ipk[4],
'survey_id': values['survey_id']}
for answer in get_answer_choices_for_choice_id(connection,
updates[choice]):
answer_values = question_fields.copy()
new_submission_id = submission_map[answer.submission_id]
answer_values['question_choice_id'] = question_choice_id
answer_values['submission_id'] = new_submission_id
answer_metadata = answer.answer_choice_metadata
answer_values['answer_choice_metadata'] = answer_metadata
connection.execute(answer_choice_insert(**answer_values))
yield question_choice_id
def _create_survey(connection: Connection, data: dict) -> str:
"""
Use the given connection to create a survey within a transaction. If
this is an update to an existing survey, it will also copy over existing
submissions.
:param connection: the SQLAlchemy connection used for the transaction
:param data: a JSON representation of the survey
:return: the UUID of the survey in the database
"""
is_update = 'survey_id' in data
email = data['email']
user_id = get_auth_user_by_email(connection, email).auth_user_id
title = data['survey_title']
data_q = data['questions']
# First, create an entry in the survey table
safe_title = get_free_title(connection, title, user_id)
survey_values = {
'auth_user_id': user_id,
'survey_metadata': data['survey_metadata'],
'survey_title': safe_title}
executable = survey_insert(**survey_values)
exc = [('survey_title_survey_owner_key',
SurveyAlreadyExistsError(safe_title))]
result = execute_with_exceptions(connection, executable, exc)
survey_id = result.inserted_primary_key[0]
# a map of old submission_id to new submission_id
submission_map = None
if is_update:
submission_map = {
entry[0]: entry[1] for entry in
_copy_submission_entries(
connection,
data['survey_id'],
survey_id,
data['email'])
}
# Now insert questions. Inserting branches has to come afterward so
# that the question_id values actually exist in the tables.
questions = list(_create_questions(connection, data_q, survey_id,
submission_map=submission_map))
if -1 not in set(q['question_to_sequence_number'] for q in questions):
raise SurveyDoesNotEndError()
_create_branches(connection, data_q, questions, survey_id)
return survey_id
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}
