class User(Base):
"""Models a user. A user has at least one e-mail address."""
__tablename__ = 'auth_user'
id = util.pk()
name = sa.Column(pg.TEXT, nullable=False)
emails = relationship(
'Email',
order_by='Email.address',
backref='user',
cascade='all, delete-orphan',
passive_deletes=True,
)
role = sa.Column(
sa.Enum(
'enumerator', 'administrator',
name='user_roles', inherit_schema=True
),
nullable=False,
)
preferences = util.json_column(
'preferences', default='{"default_language": "English"}'
)
last_update_time = util.last_update_time()
__mapper_args__ = {
'polymorphic_identity': 'enumerator',
'polymorphic_on': role,
}
__table_args__ = (
sa.CheckConstraint(
"((preferences->>'default_language')) IS NOT NULL",
name='must_specify_default_language'
),
)
def _asdict(self) -> OrderedDict:
return OrderedDict((
('id', self.id),
('deleted', self.deleted),
('name', self.name),
('emails', [email.address for email in self.emails]),
('role', self.role),
('preferences', self.preferences),
('allowed_surveys', [s.id for s in self.allowed_surveys]),
('last_update_time', self.last_update_time),
))
class Choice(Base):
"""A choice for a MultipleChoiceQuestion.
Models a choice for a dokomoforms.models.survey.MultipleChoiceQuestion.
"""
__tablename__ = 'choice'
id = util.pk()
choice_text = util.json_column('choice_text')
choice_number = sa.Column(sa.Integer, nullable=False)
question_id = sa.Column(pg.UUID, nullable=False)
question_languages = util.languages_column('question_languages')
last_update_time = util.last_update_time()
__table_args__ = (
sa.UniqueConstraint('question_id',
'choice_number',
name='unique_choice_number'),
sa.UniqueConstraint('question_id',
'choice_text',
name='unique_choice_text'),
sa.UniqueConstraint('id', 'question_id'),
util.languages_constraint('choice_text', 'question_languages'),
sa.ForeignKeyConstraint(['question_id', 'question_languages'], [
'question_multiple_choice.id',
'question_multiple_choice.node_languages'
],
onupdate='CASCADE',
ondelete='CASCADE'),
)
def _asdict(self) -> OrderedDict:
return OrderedDict((
('id', self.id),
('deleted', self.deleted),
('choice_text', OrderedDict(sorted(self.choice_text.items()))),
('choice_number', self.choice_number),
('question',
OrderedDict((
('question_id', self.question_id),
('question_title',
OrderedDict(sorted(self.question.title.items()))),
))),
('last_update_time', self.last_update_time),
))
class Email(Base):
"""Models an e-mail address."""
__tablename__ = 'email'
id = util.pk()
address = sa.Column(
pg.TEXT, sa.CheckConstraint("address ~ '.*@.*'"),
nullable=False, unique=True
)
user_id = sa.Column(pg.UUID, util.fk('auth_user.id'), nullable=False)
last_update_time = util.last_update_time()
def _asdict(self) -> OrderedDict:
return OrderedDict((
('id', self.id),
('address', self.address),
('user', self.user.name),
('last_update_time', self.last_update_time),
))
class Node(Base):
"""A node is a Note or Question independent of any Survey.
A node is its own entity. A node can be a dokomoforms.models.survey.Note or
a dokomoforms.models.survey.Question.
You can use this class for querying, e.g.
session.query(Node).filter_by(title='Some Title')
To create the specific kind of Node you want, use
dokomoforms.models.survey.node.construct_node.
"""
__tablename__ = 'node'
id = util.pk()
languages = util.languages_column('languages')
title = util.json_column('title')
hint = util.json_column('hint', default='{"English": ""}')
type_constraint = sa.Column(node_type_enum, nullable=False)
logic = util.json_column('logic', default='{}')
last_update_time = util.last_update_time()
__mapper_args__ = {'polymorphic_on': type_constraint}
__table_args__ = (
sa.CheckConstraint(
"(type_constraint::TEXT != 'facility') OR ("
" ((logic->>'nlat')) IS NOT NULL AND "
" ((logic->>'slat')) IS NOT NULL AND "
" ((logic->>'wlng')) IS NOT NULL AND "
" ((logic->>'elng')) IS NOT NULL"
")",
name='facility_questions_must_have_bounds'),
sa.UniqueConstraint('id', 'type_constraint'),
sa.UniqueConstraint('id', 'languages', 'type_constraint'),
util.languages_constraint('title', 'languages'),
util.languages_constraint('hint', 'languages'),
)
class SurveyNode(Base):
"""A SurveyNode contains a Node and adds survey-specific metadata."""
__tablename__ = 'survey_node'
id = util.pk()
node_number = sa.Column(sa.Integer, nullable=False)
survey_node_answerable = sa.Column(
sa.Enum('non_answerable',
'answerable',
name='answerable_enum',
inherit_schema=True),
nullable=False,
)
node_id = sa.Column(pg.UUID, nullable=False)
node_languages = sa.Column(pg.ARRAY(pg.TEXT, as_tuple=True),
nullable=False)
type_constraint = sa.Column(node_type_enum, nullable=False)
the_node = relationship('Node')
@property # pragma: no cover
@abc.abstractmethod
def node(self):
"""The Node instance."""
root_survey_id = sa.Column(pg.UUID)
containing_survey_id = sa.Column(pg.UUID, nullable=False)
root_survey_languages = sa.Column(pg.ARRAY(pg.TEXT, as_tuple=True),
nullable=False)
sub_survey_id = sa.Column(pg.UUID)
sub_survey_repeatable = sa.Column(sa.Boolean)
non_null_repeatable = sa.Column(sa.Boolean,
nullable=False,
server_default='FALSE')
logic = util.json_column('logic', default='{}')
last_update_time = util.last_update_time()
__mapper_args__ = {'polymorphic_on': survey_node_answerable}
__table_args__ = (
sa.UniqueConstraint('id', 'node_id', 'type_constraint'),
sa.UniqueConstraint('id', 'containing_survey_id',
'root_survey_languages', 'node_id',
'type_constraint', 'non_null_repeatable'),
sa.CheckConstraint(
'(sub_survey_repeatable IS NULL) != '
'(sub_survey_repeatable = non_null_repeatable)',
name='you_must_mark_survey_nodes_repeatable_explicitly'),
sa.CheckConstraint(
'(root_survey_id IS NULL) != (sub_survey_id IS NULL)'),
sa.CheckConstraint(
'root_survey_languages @> node_languages',
name='all_survey_languages_present_in_node_languages'),
sa.ForeignKeyConstraint([
'root_survey_id', 'containing_survey_id', 'root_survey_languages'
], ['survey.id', 'survey.containing_id', 'survey.languages'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint([
'sub_survey_id', 'root_survey_languages', 'containing_survey_id',
'sub_survey_repeatable'
], [
'sub_survey.id', 'sub_survey.root_survey_languages',
'sub_survey.containing_survey_id', 'sub_survey.repeatable'
]),
sa.ForeignKeyConstraint(
['node_id', 'node_languages', 'type_constraint'],
['node.id', 'node.languages', 'node.type_constraint']),
)
def _asdict(self) -> OrderedDict:
result = self.node._asdict()
if result['logic']:
result['logic'].update(self.logic)
result['node_id'] = result.pop('id')
result['id'] = self.id
result['deleted'] = self.deleted
result['last_update_time'] = self.last_update_time
return result
class Survey(Base):
"""A Survey has a list of SurveyNodes.
Use an EnumeratorOnlySurvey to restrict submissions to enumerators.
"""
__tablename__ = 'survey'
id = util.pk()
containing_id = sa.Column(pg.UUID,
unique=True,
server_default=sa.func.uuid_generate_v4())
languages = util.languages_column('languages')
title = util.json_column('title')
url_slug = sa.Column(
pg.TEXT,
sa.CheckConstraint("url_slug != '' AND "
"url_slug !~ '[%%#;/?:@&=+$,\s]' AND "
"url_slug !~ '{}'".format(util.UUID_REGEX),
name='url_safe_slug'),
unique=True,
)
default_language = sa.Column(
pg.TEXT,
sa.CheckConstraint("default_language != ''",
name='non_empty_default_language'),
nullable=False,
server_default='English',
)
survey_type = sa.Column(survey_type_enum, nullable=False)
administrators = relationship(
'Administrator',
secondary=_administrator_table,
backref='admin_surveys',
passive_deletes=True,
)
submissions = relationship(
'Submission',
order_by='Submission.save_time',
backref='survey',
cascade='all, delete-orphan',
passive_deletes=True,
)
# dokomoforms.models.column_properties
# num_submissions
# earliest_submission_time
# latest_submission_time
# TODO: expand upon this
version = sa.Column(sa.Integer, nullable=False, server_default='1')
# ODOT
creator_id = sa.Column(pg.UUID,
util.fk('administrator.id'),
nullable=False)
# This is survey_metadata rather than just metadata because all models
# have a metadata attribute which is important for SQLAlchemy.
survey_metadata = util.json_column('survey_metadata', default='{}')
created_on = sa.Column(
pg.TIMESTAMP(timezone=True),
nullable=False,
server_default=current_timestamp(),
)
nodes = relationship(
'SurveyNode',
order_by='SurveyNode.node_number',
collection_class=ordering_list('node_number'),
cascade='all, delete-orphan',
passive_deletes=True,
)
last_update_time = util.last_update_time()
__mapper_args__ = {
'polymorphic_on': survey_type,
'polymorphic_identity': 'public',
}
__table_args__ = (
sa.Index(
'unique_survey_title_in_default_language_per_user',
sa.column(quoted_name('(title->>default_language)', quote=False)),
'creator_id',
unique=True,
),
sa.UniqueConstraint('id', 'containing_id', 'survey_type'),
sa.UniqueConstraint('id', 'containing_id', 'languages'),
util.languages_constraint('title', 'languages'),
sa.CheckConstraint("languages @> ARRAY[default_language]",
name='default_language_in_languages_exists'),
sa.CheckConstraint("(title->>default_language) != ''",
name='title_in_default_langauge_non_empty'),
)
def _asdict(self) -> OrderedDict:
return OrderedDict((
('id', self.id),
('deleted', self.deleted),
('languages', self.languages),
('title', OrderedDict(sorted(self.title.items()))),
('url_slug', self.url_slug),
('default_language', self.default_language),
('survey_type', self.survey_type),
('version', self.version),
('creator_id', self.creator_id),
('creator_name', self.creator.name),
('metadata', self.survey_metadata),
('created_on', self.created_on),
('last_update_time', self.last_update_time),
('nodes', self.nodes),
))
def _sequentialize(self, *, include_non_answerable=True):
"""Generate a pre-order traversal of this survey's nodes.
https://en.wikipedia.org/wiki/Tree_traversal#Depth-first
"""
for node in self.nodes:
if isinstance(node, NonAnswerableSurveyNode):
if include_non_answerable:
yield node
else:
# See https://bitbucket.org/ned/coveragepy/issues/198/
continue # pragma: no cover
else:
yield node
for sub_survey in node.sub_surveys:
yield from Survey._sequentialize(
sub_survey,
include_non_answerable=include_non_answerable)
class Bucket(Base):
"""A Bucket determines how to arrive at a SubSurvey.
A Bucket can be a range or a Choice.
"""
__tablename__ = 'bucket'
id = util.pk()
sub_survey_id = sa.Column(pg.UUID, nullable=False)
sub_survey_parent_type_constraint = sa.Column(node_type_enum,
nullable=False)
sub_survey_parent_survey_node_id = sa.Column(pg.UUID, nullable=False)
sub_survey_parent_node_id = sa.Column(pg.UUID, nullable=False)
bucket_type = sa.Column(
sa.Enum(
'integer',
'decimal',
'date',
'time',
'timestamp',
'multiple_choice',
name='bucket_type_name',
inherit_schema=True,
),
nullable=False,
)
@property # pragma: no cover
@abc.abstractmethod
def bucket(self):
"""The bucket is a range or Choice.
Buckets for a given SubSurvey cannot overlap.
"""
last_update_time = util.last_update_time()
__mapper_args__ = {'polymorphic_on': bucket_type}
__table_args__ = (
sa.CheckConstraint(
'bucket_type::TEXT = sub_survey_parent_type_constraint::TEXT',
name='bucket_type_matches_question_type'),
sa.ForeignKeyConstraint([
'sub_survey_id',
'sub_survey_parent_type_constraint',
'sub_survey_parent_survey_node_id',
'sub_survey_parent_node_id',
], [
'sub_survey.id',
'sub_survey.parent_type_constraint',
'sub_survey.parent_survey_node_id',
'sub_survey.parent_node_id',
],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.UniqueConstraint('id', 'sub_survey_parent_survey_node_id'),
sa.UniqueConstraint('id', 'sub_survey_id',
'sub_survey_parent_survey_node_id',
'sub_survey_parent_node_id'),
)
def _asdict(self) -> OrderedDict:
return OrderedDict((
('id', self.id),
('bucket_type', self.bucket_type),
('bucket', self.bucket),
))
class Submission(Base):
"""A Submission references a Survey and has a list of Answers."""
__tablename__ = 'submission'
id = util.pk()
submission_type = sa.Column(
sa.Enum(
'public_submission', 'enumerator_only_submission',
name='submission_type_enum', inherit_schema=True
),
nullable=False,
)
survey_id = sa.Column(pg.UUID, nullable=False)
survey_containing_id = sa.Column(pg.UUID, nullable=False)
survey_type = sa.Column(survey_type_enum, nullable=False)
# dokomoforms.models.column_properties
# survey_title
# survey_default_language
start_time = sa.Column(pg.TIMESTAMP(timezone=True))
save_time = sa.Column(
pg.TIMESTAMP(timezone=True),
nullable=False,
server_default=current_timestamp(),
)
submission_time = sa.Column(
pg.TIMESTAMP(timezone=True),
nullable=False,
server_default=current_timestamp(),
)
submitter_name = sa.Column(pg.TEXT, nullable=False, server_default='')
submitter_email = sa.Column(
pg.TEXT, sa.CheckConstraint("submitter_email ~ '^$|.*@.*'"),
nullable=False, server_default=''
)
answers = relationship(
'Answer',
order_by='Answer.answer_number',
collection_class=ordering_list('answer_number'),
cascade='all, delete-orphan',
passive_deletes=True,
)
last_update_time = util.last_update_time()
__mapper_args__ = {
'polymorphic_on': submission_type,
}
__table_args__ = (
sa.ForeignKeyConstraint(
['survey_id', 'survey_containing_id', 'survey_type'],
['survey.id', 'survey.containing_id', 'survey.survey_type'],
onupdate='CASCADE', ondelete='CASCADE'
),
sa.UniqueConstraint('id', 'survey_type'),
sa.UniqueConstraint('id', 'survey_id'),
sa.UniqueConstraint(
'id', 'survey_containing_id', 'save_time', 'survey_id'
),
)
def _default_asdict(self) -> OrderedDict:
return OrderedDict((
('id', self.id),
('deleted', self.deleted),
('survey_id', self.survey_id),
('start_time', self.start_time),
('save_time', self.save_time),
('submission_time', self.submission_time),
('last_update_time', self.last_update_time),
('submitter_name', self.submitter_name),
('submitter_email', self.submitter_email),
('answers', [
OrderedDict(
answer.response, survey_node_id=answer.survey_node_id
) for answer in self.answers
]),
))
class Answer(Base):
"""An Answer is a response to a SurveyNode.
An Answer can be one of an answer, an "other" response or a "don't know"
response. Answer.response abstracts over these 3 possibilites.
"""
__tablename__ = 'answer'
id = util.pk()
answer_number = sa.Column(sa.Integer, nullable=False)
submission_id = sa.Column(pg.UUID, nullable=False)
# save_time is here so that AnswerableSurveyNode can have a list of
# answers to that node ordered by save time of the submission
save_time = sa.Column(pg.TIMESTAMP(timezone=True), nullable=False)
survey_id = sa.Column(pg.UUID, nullable=False)
survey_containing_id = sa.Column(pg.UUID, nullable=False)
survey_node_containing_survey_id = sa.Column(pg.UUID, nullable=False)
survey_node_id = sa.Column(pg.UUID, nullable=False)
survey_node = relationship('AnswerableSurveyNode')
allow_multiple = sa.Column(sa.Boolean, nullable=False)
repeatable = sa.Column(sa.Boolean, nullable=False)
allow_other = sa.Column(sa.Boolean, nullable=False)
allow_dont_know = sa.Column(sa.Boolean, nullable=False)
question_id = sa.Column(pg.UUID, nullable=False)
# dokomoforms.models.column_properties
# question_title
type_constraint = sa.Column(node_type_enum, nullable=False)
answer_type = sa.Column(
sa.Enum(
'text',
'photo',
'integer',
'decimal',
'date',
'time',
'timestamp',
'location',
'facility',
'multiple_choice',
name='answer_type_name',
inherit_schema=True,
metadata=Base.metadata,
),
nullable=False,
)
last_update_time = util.last_update_time()
answer_metadata = util.json_column('answer_metadata', default='{}')
@property # pragma: no cover
@abc.abstractmethod
def main_answer(self):
"""The representative part of a provided answer.
The main_answer is the only answer for simple types (integer, text,
etc.) and for other types is the part of the answer that is most
important. In practice, the main_answer is special only in that all
Answer models have it, which is necessary for certain constraints and
for the response property.
"""
@property # pragma: no cover
@abc.abstractmethod
def answer(self):
"""The answer. Could be the same as main_answer in simple cases.
This property is the most useful representation available of the
answer. In the simplest case it is just a synonym for main_answer.
It could otherwise be a dictionary or another model.
"""
@property # pragma: no cover
@abc.abstractmethod
def other(self):
"""A text field containing "other" responses."""
@property # pragma: no cover
@abc.abstractmethod
def dont_know(self):
"""A text field containing "don't know" responses."""
@hybrid_property
def response(self) -> OrderedDict:
"""A dictionary that abstracts over answer, other, and dont_know.
{
'type_constraint': <self.answer_type>,
'response_type': 'answer|other|dont_know',
'response': <one of self.answer, self.other, self.dont_know>
}
"""
possible_resps = [
('answer', self.main_answer),
('other', self.other),
('dont_know', self.dont_know),
]
response_type, response = next(filter(_is_response, possible_resps))
if response_type == 'answer':
if self.type_constraint == 'multiple_choice':
response = {
'id': self.choice.id,
'choice_number': self.choice.choice_number,
'choice_text': self.choice.choice_text,
}
elif self.type_constraint == 'location':
lng, lat = json_decode(self.geo_json)['coordinates']
response = {'lng': lng, 'lat': lat}
elif self.type_constraint == 'facility':
response = self.answer
geo_json = json_decode(response['facility_location'])
response['lng'], response['lat'] = geo_json['coordinates']
del response['facility_location']
elif self.type_constraint == 'photo':
response = self.actual_photo_id
else:
response = self.answer
return OrderedDict((
('type_constraint', self.answer_type),
('response_type', response_type),
('response', response),
))
@response.setter
def response(self, response_dict):
"""Set the appropriate field using the response dict."""
response_type = response_dict['response_type']
if response_type not in {'answer', 'other', 'dont_know'}:
raise NotAResponseTypeError(response_type)
setattr(self, response_type, response_dict['response'])
__mapper_args__ = {'polymorphic_on': answer_type}
__table_args__ = (
sa.UniqueConstraint('id', 'allow_other', 'allow_dont_know'),
sa.UniqueConstraint(
'id',
'allow_other',
'allow_dont_know',
'survey_node_id',
'question_id',
'submission_id',
),
sa.CheckConstraint(
'survey_containing_id = survey_node_containing_survey_id'),
sa.CheckConstraint('type_constraint::TEXT = answer_type::TEXT'),
sa.ForeignKeyConstraint(
[
'submission_id', 'survey_containing_id', 'save_time',
'survey_id'
],
[
'submission.id', 'submission.survey_containing_id',
'submission.save_time', 'submission.survey_id'
],
onupdate='CASCADE',
ondelete='CASCADE',
),
sa.ForeignKeyConstraint([
'survey_node_id',
'survey_node_containing_survey_id',
'question_id',
'type_constraint',
'allow_multiple',
'repeatable',
'allow_other',
'allow_dont_know',
], [
'survey_node_answerable.id',
'survey_node_answerable.the_containing_survey_id',
'survey_node_answerable.the_node_id',
'survey_node_answerable.the_type_constraint',
'survey_node_answerable.allow_multiple',
'survey_node_answerable.the_sub_survey_repeatable',
'survey_node_answerable.allow_other',
'survey_node_answerable.allow_dont_know',
]),
sa.Index(
'only_one_answer_allowed',
'survey_node_id',
'submission_id',
unique=True,
postgresql_where=sa.not_(sa.or_(allow_multiple, repeatable)),
),
)
def _asdict(self, mode='json') -> OrderedDict:
items = (
('id', self.id),
('deleted', self.deleted),
('answer_number', self.answer_number),
('submission_id', self.submission_id),
('save_time', self.save_time),
('survey_id', self.survey_id),
('survey_node_id', self.survey_node_id),
('question_id', self.question_id),
('type_constraint', self.type_constraint),
('last_update_time', self.last_update_time),
)
if mode == 'csv':
response = self.response['response']
if isinstance(response, dict):
response = json_encode(response)
items += (
('main_answer', self.main_answer),
('response', response),
('response_type', self.response['response_type']),
)
else:
items += (('response', self.response), )
items += (('metadata', self.answer_metadata), )
return OrderedDict(items)