class ArgumentVote(Base):
__tablename__ = 'argumentvotes'
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
member = relationship("User", backref=backref("member_argumentvotes", cascade="all, delete-orphan"))
relation_id: int = C(Integer, ForeignKey('argumentrelations.id'), primary_key=True)
relation = relationship("ArgumentRelation", backref=backref("relation_votes", cascade="all, delete-orphan"))
weight: int = C(Integer, nullable=False, comment='if extendedDiscussion: --(-2),-,0,+,++(+2) , otherwise -1 and +1')
class GroupMember(Base):
__tablename__ = 'groupmembers'
group_id: int = C(Integer, ForeignKey('groups.id'), primary_key=True)
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
group = relationship("Group", backref=backref("group_members", cascade="all, delete-orphan"))
member = relationship("User", backref=backref("member_groups", cascade="all, delete-orphan"))
class Page(Base):
__tablename__ = 'page'
name: str = C(Text, primary_key=True)
lang: str = C(Text, primary_key=True)
title: str = C(Text)
text: str = C(Text)
permissions = C(JSON)
class OAuthToken(Base):
__tablename__ = 'oauth_token'
id = C(Integer, FK('users.id'), primary_key=True)
user = rel("User", backref=bref("oauth_token", uselist=False))
token = C(JSON)
provider = C(Text)
created_at = C(DateTime, nullable=False, server_default=func.now())
class Changeset(Base):
__tablename__ = 'changeset'
id: int = integer_pk()
document_id: int = C(Integer, ForeignKey('document.id'), nullable=False)
proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), nullable=False)
document = relationship(Document, back_populates='changesets')
proposition = relationship(Proposition, back_populates='changesets')
section: str = C(Text, comment='Identifier for the section of the document that is changed.')
class VotingPhaseType(Base):
__tablename__ = 'voting_phase_types'
id: int = integer_pk()
name: str = C(Text, server_default='', comment='readable name')
abbreviation: str = C(Text, server_default='', comment='abbreviated name')
secret_voting_possible: bool = C(Boolean, nullable=False)
voting_type = C(Enum(VotingType), nullable=False) # online, urn, assembly, board
description: str = C(Text, server_default='')
class UserProfile(Base):
__tablename__ = 'userprofiles'
id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
user = relationship("User", back_populates="profile")
sub: str = C(Text, unique=True)
eligible: bool = C(Boolean)
verified: bool = C(Boolean)
profile: str = C(Text)
class PropositionType(Base): # Antragsart
__tablename__ = 'propositiontypes'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
name: str = C(Text, unique=True, nullable=False)
abbreviation: str = C(Text, unique=True, nullable=False)
description: str = C(Text, server_default='')
policy_id: int = C(Integer, ForeignKey('policies.id'), nullable=False)
policy: Policy = relationship("Policy", back_populates="proposition_types")
ballots = relationship("Ballot", back_populates="proposition_type")
class BallotOption(Base):
__tablename__ = 'ballot_option'
uuid = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True)
voting_uuid = C(UUIDType, FK('ballot_voting.uuid'), nullable=False)
title = C(String)
text = C(Text, nullable=False)
voting = rel('BallotVoting', back_populates='options')
votes = rel('Vote', back_populates='option')
class UrnSupporter(Base): # §5b.2
__tablename__ = 'urnsupporters'
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
member = relationship("User", backref=backref("member_urns", cascade="all, delete-orphan"))
urn_id: int = C(Integer, ForeignKey('urns.id'), primary_key=True)
urn = relationship("Urn", backref=backref("urn_members", cascade="all, delete-orphan"))
type: str = C(Text, nullable=False) # responsible, request, voter # §5b.2+4
voted: bool = C(Boolean, nullable=False, server_default='false') # §5b.6
class VoteToken(Base):
__tablename__ = 'vote_token'
token = C(UUIDType,
server_default=func.gen_random_uuid(),
primary_key=True)
auid = C(UUIDType, nullable=False)
vote_uuid = C(UUIDType, FK('vote.uuid'), nullable=False)
vote = rel('Vote', back_populates='token')
class SecretVoter(Base): # §3.7, §4.4
__tablename__ = 'secretvoters'
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
member = relationship("User", backref=backref("member_ballots", cascade="all, delete-orphan"))
ballot_id: int = C(Integer, ForeignKey('ballots.id'), primary_key=True)
ballot = relationship("Ballot", backref=backref("ballot_members", cascade="all, delete-orphan"))
status: str = C(Enum(SecretVoterStatus), nullable=False) # active,expired,retracted
last_change: datetime = C(DateTime, nullable=False) # time of requested/retracted
class Supporter(Base): # §3.5
__tablename__ = 'supporters'
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
member = relationship("User", backref=backref("member_propositions", cascade="all, delete-orphan"))
proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), primary_key=True)
proposition = relationship("Proposition", backref=backref("propositions_member", cascade="all, delete-orphan"))
submitter: bool = C(Boolean, nullable=False, server_default='false', comment='submitter or regular')
status: SupporterStatus = C(Enum(SupporterStatus), nullable=False, server_default='ACTIVE')
last_change: datetime = C(DateTime, nullable=False, server_default=func.now(), comment='last status change')
class AreaMember(Base):
__tablename__ = 'areamembers'
area_id: int = C(Integer, ForeignKey('subjectareas.id'), primary_key=True)
area = relationship("SubjectArea", backref=backref("area_members", cascade="all, delete-orphan"))
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
member = relationship("User", backref=backref("member_areas", cascade="all, delete-orphan"))
def __init__(self, area=None, member=None):
self.area = area
self.member = member
class Tag(Base):
__tablename__ = 'tags'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
name: str = C(Text, unique=True, nullable=False)
parent_id: int = C(Integer, ForeignKey('tags.id'))
children = relationship("Tag", backref=backref('parent', remote_side=[id]))
mut_exclusive: bool = C(
Boolean, nullable=False, server_default='false', comment='whether all children are mutually exclusive'
)
propositions = association_proxy('tag_propositions', 'proposition') # <-PropositionTag-> Proposition
class PropositionTag(Base):
__tablename__ = 'propositiontags'
proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), primary_key=True)
proposition = relationship("Proposition", backref=backref("proposition_tags", cascade="all, delete-orphan"))
tag_id: int = C(Integer, ForeignKey('tags.id'), primary_key=True)
tag = relationship("Tag", backref=backref("tag_propositions", cascade="all, delete-orphan"))
def __init__(self, tag=None, proposition=None):
self.tag = tag
self.proposition = proposition
class Department(Base):
__tablename__ = 'departments'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
name: str = C(Text, unique=True, nullable=False)
description: str = C(Text)
voting_phases = relationship('VotingPhase', back_populates='department', cascade='all, delete-orphan')
members = association_proxy('department_members', 'member') # <-DepartmentMember-> User
areas = relationship("SubjectArea", back_populates="department")
exporter_settings: dict = C(MutableDict.as_mutable(JSONB), server_default='{}')
voting_module_settings: dict = C(MutableDict.as_mutable(JSONB), server_default='{}')
"""
class DepartmentMember(Base):
__tablename__ = 'departmentmembers'
department_id: int = C(Integer, ForeignKey('departments.id'), primary_key=True)
department = relationship("Department", backref=backref("department_members", cascade="all, delete-orphan"))
member_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
member = relationship("User", backref=backref("member_departments", cascade="all, delete-orphan"))
is_admin: bool = C(Boolean, nullable=False, server_default='false')
def __init__(self, department=None, member=None, is_admin=False):
self.department = department
self.member = member
self.is_admin = is_admin
class SubjectArea(Base): # Themenbereich §2.3+4
__tablename__ = 'subjectareas'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
name: str = C(Text, nullable=False)
description: str = C(Text)
department_id: int = C(Integer, ForeignKey('departments.id'), nullable=False)
department = relationship("Department", back_populates="areas")
ballots = relationship("Ballot", back_populates="area")
# Themenbereichsteilnehmer
# can only be removed if not proposition in this area supported §2.3
members = association_proxy('area_members', 'member') # <-AreaMember-> User
documents = relationship('Document', back_populates='area')
class PropositionNote(Base):
__tablename__ = 'propositionnotes'
proposition_id: LID = C(LIDType, ForeignKey('propositions.id'), primary_key=True)
user_id: int = C(Integer, ForeignKey('users.id'), primary_key=True)
notes: str = C(Text)
vote: VoteByUser = C(Enum(VoteByUser))
def __init__(self, user, id, notes=None, vote=VoteByUser.UNSURE):
self.proposition_id = id
self.user_id = user
self.notes = notes
self.vote = vote
class User(Base):
__tablename__ = 'users'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
name: str = C(Text, unique=True, nullable=False)
email: str = C(EmailType, unique=True, comment='optional, for notifications, otherwise use user/mails/')
auth_type: str = C(
Text, nullable=False, server_default='system', comment='deleted,system,token,virtual,oauth(has UserProfile)'
)
joined: datetime = C(DateTime, nullable=False, server_default=func.now())
active: bool = C(Boolean, nullable=False, server_default='true')
last_active: datetime = C(
DateTime,
nullable=False,
server_default=func.now(),
comment='last relevant activity (to be considered active member §2.2)'
)
can_login_until: datetime = C(
DateTime, comment='optional expiration datetime after which login is no longer possible'
)
# actions: submit/support proposition, voting, or explicit, deactivate after 2 periods
profile = relationship("UserProfile", uselist=False, back_populates="user")
groups = association_proxy('member_groups', 'group', creator=lambda g: GroupMember(group=g)) # <-GroupMember-> Group
# from user/membership/ all_nested_groups
departments = association_proxy('member_departments', 'department') # <-DepartmentMember-> Department
areas = association_proxy('member_areas', 'area') # <-AreaMember-> SubjectArea
supports = association_proxy('member_propositions', 'proposition') # <-Supporter-> Proposition
arguments = relationship("Argument", back_populates="author")
secret_voters = association_proxy('member_secretvoters', 'secretvoter') # <-SecretVoter-> Ballot
urns = association_proxy('member_urns', 'urn') # <-UrnSupporter-> Urn
postal_votes = association_proxy('member_postal', 'voting') # <-PostalVote-> VotingPhase
@property
def managed_departments(self):
return [md.department for md in self.member_departments if md.is_admin]
class Ballot(Base): # conflicting qualified propositions
__tablename__ = 'ballots'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
name: str = C(Text)
# <- propositions Proposition[]
# XXX: not sure if we need a status here. Add missing states to PropositionStatus or the other way round?
# status: str = C(Text, nullable=False) # submitted?, qualified, locked, obsolete # §4.8 §5.2
election: int = C(Integer, nullable=False, server_default='0') # 0=no election, otherwise nr of positions, §5d.4+5
# §3.8, one proposition is for qualification of election itself
voting_type: VotingType = C(Enum(VotingType)) # online, urn, assembly, board
proposition_type_id: int = C(Integer, ForeignKey('propositiontypes.id'))
proposition_type: PropositionType = relationship("PropositionType", back_populates="ballots")
area_id: int = C(Integer, ForeignKey('subjectareas.id'))
area = relationship("SubjectArea", back_populates="ballots") # contains department
# optional, if assigned, set proposition to planned
voting_id: int = C(Integer, ForeignKey('votingphases.id'))
voting = relationship("VotingPhase", back_populates="ballots")
secret_voters = association_proxy('ballot_members', 'member') # <-SecretVoter-> User
propositions = relationship("Proposition", back_populates="ballot")
# <-result VotingResult # optional
result: dict = C(MutableDict.as_mutable(JSONB))
class ArgumentRelation(Base):
__tablename__ = 'argumentrelations'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
parent_id: int = C(Integer, ForeignKey('argumentrelations.id'), comment='only for inter-arguments')
children = relationship("ArgumentRelation", backref=backref('parent', remote_side=[id]))
argument_id: int = C(Integer, ForeignKey('arguments.id'))
argument = relationship("Argument", backref=backref("argument_relations", cascade="all, delete-orphan"))
proposition_id: LID = C(LIDType, ForeignKey('propositions.id'))
proposition = relationship("Proposition", backref=backref("proposition_arguments", cascade="all, delete-orphan"))
argument_type: ArgumentType = C(Enum(ArgumentType), nullable=False)
def user_vote(self, user):
return object_session(self).query(ArgumentVote).filter_by(relation=self, member=user).scalar()
@cached_property
def score(self):
return sum(rv.weight for rv in self.relation_votes)
class Argument(Base):
__tablename__ = 'arguments'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
title: str = C(Text, nullable=False)
abstract: str = C(Text, nullable=False)
details: str = C(Text)
author_id: int = C(Integer, ForeignKey('users.id'))
author = relationship("User", backref=backref("member_arguments", cascade="all, delete-orphan"))
created_at: datetime = C(DateTime, nullable=False, server_default=func.now())
class VotingPhase(Base): # Abstimmungsperiode
__tablename__ = 'votingphases'
__table_args__ = (
CheckConstraint(
"(status='PREPARING' AND target IS NULL) OR (status!='PREPARING' AND target IS NOT NULL)", 'state_valid'
),
)
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
status: VotingStatus = C(Enum(VotingStatus), nullable=False, server_default='PREPARING')
target: datetime = C(DateTime, comment='constrained by §4.1')
department_id: int = C(Integer, ForeignKey('departments.id'), nullable=False)
phase_type_id: int = C(Integer, ForeignKey('voting_phase_types.id'), nullable=False)
secret: bool = C(
Boolean,
nullable=False,
server_default='false',
comment='whether any secret votes will take place (decision deadline §4.2)'
)
name: str = C(Text, server_default='', comment='short, readable name which can be used for URLs')
title: str = C(Text, server_default='')
description: str = C(Text, server_default='')
voting_module_data: dict = C(MutableDict.as_mutable(JSONB), server_default='{}')
ballots = relationship("Ballot", back_populates="voting")
department = relationship('Department', back_populates='voting_phases')
phase_type = relationship('VotingPhaseType')
# <- urns Urn[]
urns = relationship("Urn", back_populates="voting")
postal_votes = association_proxy('voting_postal', 'member') # <-PostalVote-> User
# send announcement before deadline §4.1
# send voting invitation before deadline §4.3
# deadline for vote registration before voting starts $5.6
# shall not assign more than recommended votings per period §5.2
# ask submitters for veto for move to other phase §5.3
@property
def ballots_can_be_added(self):
return self.status in (VotingStatus.PREPARING, VotingStatus.SCHEDULED)
class Urn(Base):
__tablename__ = 'urns'
id: int = C(Integer, Sequence('id_seq', optional=True), primary_key=True)
voting_id: int = C(Integer, ForeignKey('votingphases.id'), nullable=False)
voting = relationship("VotingPhase", back_populates="urns")
accepted: bool = C(Boolean, nullable=False, server_default='false')
location: str = C(Text, nullable=False)
description: str = C(Text)
opening = C(Time) # §5b.5
supporters = association_proxy('urn_members', 'member') # <-UrnSupporter-> User
class Vote(Base):
__tablename__ = 'vote'
uuid = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True)
yes_no = C(Boolean)
points = C(Integer, nullable=False)
option_uuid = C(UUIDType, FK('ballot_option.uuid'), nullable=False)
created_at = C(DateTime, nullable=False, server_default=func.now())
confirmed = C(Boolean, nullable=False, server_default='false')
token = rel(VoteToken, back_populates='vote')
option = rel(BallotOption, back_populates='votes')
class Document(Base):
__tablename__ = 'document'
id: int = integer_pk()
name: str = C(Text)
lang: str = C(Text)
area_id: int = C(Integer, ForeignKey('subjectareas.id'))
area = relationship('SubjectArea', back_populates='documents') # contains department
text: str = C(Text)
description: str = C(Text)
proposition_type_id: int = C(Integer, ForeignKey('propositiontypes.id'))
proposition_type = relationship('PropositionType')
changesets = relationship('Changeset', back_populates='document')
__table_args__ = (UniqueConstraint(name, lang, area_id), )
class BallotVoting(Base):
__tablename__ = 'ballot_voting'
uuid = C(UUIDType, server_default=func.gen_random_uuid(), primary_key=True)
department = C(String, nullable=False)
title = C(String)
created_at = C(DateTime, nullable=False, server_default=func.now())
starts_at = C(DateTime, nullable=False)
ends_at = C(DateTime, nullable=False)
options = rel(BallotOption, back_populates='voting')
def votes_to_confirm(self, auid):
return (object_session(self).query(
VoteToken.token, Vote, BallotOption).filter(
VoteToken.auid == auid, Vote.confirmed == False,
VoteToken.vote_uuid == Vote.uuid,
BallotOption.uuid == Vote.option_uuid,
BallotOption.voting == self).order_by(Vote.yes_no.desc(),
Vote.points.desc()))
class UserLoginToken(Base):
__tablename__ = 'user_login_token'
token: str = C(Text, primary_key=True)
user_id: int = C(Integer, ForeignKey('users.id'))
user = relationship("User", backref=backref("login_token", uselist=False))
valid_until: datetime = C(DateTime)