class PersonIdentifierPrincipal(Principal):
"""
Security principal based on an external ID representing
a person/user entity.
"""
__tablename__ = 'person_identifier_principal'
__versioned__ = {}
id = schema.Column(UuidType,
schema.ForeignKey('principal.id'),
default=uuid.uuid4,
primary_key=True)
id_type = schema.Column(types.UnicodeText, nullable=False)
id_value = schema.Column(types.UnicodeText, nullable=False)
@validates('id_type')
def validate_id_type(self, key, id_type):
return PersonIdType(id_type).value
__table_args__ = (schema.UniqueConstraint('id_type', 'id_value'), )
__mapper_args__ = {
'polymorphic_identity': 'person-identifier-principal',
'inherit_condition': id == Principal.id,
}
class Microregion(datasets.Entity):
"""Entity class for microregions."""
_name = 'microregion'
__table__ = schema.Table(
'microregions', datasets.Entity.metadata,
schema.Column('id', types.Integer, nullable=False, primary_key=True),
schema.Column('mesoregion_id',
types.SmallInteger,
schema.ForeignKey('mesoregions.id', use_alter=True),
nullable=False,
index=True),
schema.Column('state_id',
types.SmallInteger,
schema.ForeignKey('states.id', use_alter=True),
nullable=False,
index=True),
schema.Column('name', types.String(64), nullable=False, index=True))
# Relationships
state = orm.relationship('State', back_populates='microregions')
mesoregion = orm.relationship('Mesoregion', back_populates='microregions')
municipalities =\
orm.relationship('Municipality', back_populates='microregion')
districts = orm.relationship('District', back_populates='microregion')
subdistricts =\
orm.relationship('Subdistrict', back_populates='microregion')
class Vote(ModelBase):
"""
The Vote object represents the *current* ballot for a given voter.
"""
__versioned__ = {}
__tablename__ = 'vote'
# TODO: Find out what other constraints we'd need? One vote per election?
voter_id = schema.Column(
schema.ForeignKey('pollbook_voters.id'),
primary_key=True,
)
ballot_id = schema.Column(
schema.ForeignKey('vote_log.ballot_id'),
index=True,
nullable=False,
)
voter = relationship(
"Voter",
back_populates="votes",
)
record = relationship(
"VoteRecord",
back_populates="vote",
)
class Envelope(ModelBase):
__versioned__ = {}
__tablename__ = 'ballots'
# a unique vote id - this is the ballot_id in the evalg.models.votes
# models.
id = schema.Column(
evalg.database.types.UuidType,
default=uuid.uuid4,
doc='a unique uuid for the ballot',
primary_key=True,
)
# Describe how ballots are serialized.
# E.g. base64-plaintext, base64-pkcs1
envelope_type = schema.Column(
sqltypes.UnicodeText,
doc='a reference to the serializer/encryption used for this ballot',
nullable=False,
)
# Ballot contents
ballot_data = schema.Column(
sqltypes.LargeBinary,
doc='the ballot content',
)
def temp_create_table2(self, database):
' create a table using sqlalchmey '
host, port = get_clickhouse_host_port()
engine = get_clickhouse_engine(host, port, database)
meta = sa.sql.schema.MetaData()
temp = sa_schema.Table('temp5', meta)
temp.append_column(sa_schema.Column('id', ch_types.Int64))
temp.append_column(
sa_schema.Column('grp_code', ch_types.Nullable(ch_types.Int64)))
temp.append_column(engines.MergeTree(order_by=('id', )))
temp.create(engine)
class VoteRecord(ModelBase):
"""
The VoteRecord represents *all* votes left by a given voter.
"""
__versioned__ = {}
__tablename__ = 'vote_log'
# ballot_id is a reference to a ballot.
# the ballot *may* not reside in this database -- how would be set up this?
ballot_id = schema.Column(
evalg.database.types.UuidType,
doc='reference to the ballot',
primary_key=True,
)
voter_id = schema.Column(
schema.ForeignKey('pollbook_voters.id'),
doc='reference to the voter who cast the ballot',
index=True,
nullable=False,
)
logged_at = schema.Column(
evalg.database.types.UtcDateTime,
default=utcnow,
doc='timestamp of the change',
index=True,
nullable=False,
)
ip_addr = schema.Column(
evalg.database.types.IpAddressType,
doc='client ip that stored this vote',
nullable=True,
)
user = schema.Column(
sqltypes.UnicodeText,
doc='authenticated user that stored this vote',
nullable=True,
)
vote = relationship(
'Vote',
doc='link to the vote if this record is a current vote',
back_populates="record",
)
def get_clickhouse_sa_columns(metadata):
columns = metadata['columns']
ch_columns = []
for idx, col in enumerate(columns):
name = col['name']
col_type = col['type'],
ch_type = get_clickhouse_type(col['type'])
nullable = col['nullable']
# make the first column non-nullable (needed for Clickhouse)
if idx == 0:
tbl_col = sa_schema.Column(name, ch_type)
else:
tbl_col = sa_schema.Column(name,
ch_types.Nullable(ch_type))
ch_columns.append(tbl_col)
return ch_columns
class ReleasedData(Base, audit.AuditColumnsMixin, ReleasedDataMixin):
__tablename__ = "released_data"
__table_args__ = (schema.PrimaryKeyConstraint(
"program_name",
"project_code",
"data_type",
name="released_data_pk",
), )
def __repr__(self):
return "<ReleasedData(project_id='{}', data_type='{}', is_controlled={}, is_open={})>".format(
self.project_id,
self.data_type,
self.is_controlled,
self.is_open,
)
is_controlled = schema.Column(sqltypes.Boolean, nullable=False)
def to_json(self):
return {
"program_name": self.program_name,
"project_code": self.project_code,
"data_type": self.data_type,
"is_controlled": self.is_controlled,
"is_open": self.is_open,
}
class ReleasedDataLog(Base, audit.AuditColumnsMixin, ReleasedDataMixin):
__tablename__ = "released_data_log"
__table_args__ = (
schema.Index(
"released_data_log_program_name_project_code_idx",
"program_name",
"project_code",
),
schema.PrimaryKeyConstraint("id", name="released_data_log_pk"),
)
def __repr__(self):
return "<ReleasedDataLog(project_id='{}', release_number={}, data_type='{}', is_open={}, action='{}')>".format(
self.project_id,
self.release_number,
self.data_type,
self.is_open,
self.action,
)
release_data_log_id_seq = schema.Sequence(name="release_data_log_id_seq",
metadata=Base.metadata)
id = schema.Column(
sqltypes.Integer,
nullable=False,
server_default=release_data_log_id_seq.next_value(),
)
release_number = schema.Column(sqltypes.Text, nullable=False)
action = schema.Column(sqltypes.Text, nullable=False)
@validates("action")
def validate_action(self, key, action):
if action not in RELEASED_DATA_LOG_ACTION_VALUES:
raise ValueError(
""""{action}" is not a valid value for {key}""".format(
action=action, key=key))
return action
def to_json(self):
return {
"program_name": self.program_name,
"project_code": self.project_code,
"release_number": self.release_number,
"data_type": self.data_type,
"is_open": self.is_open,
"action": self.action,
}
class ReleasedDataMixin:
program_name = schema.Column(sqltypes.Text, nullable=False)
project_code = schema.Column(sqltypes.Text, nullable=False)
is_open = schema.Column(sqltypes.Boolean, nullable=False)
data_type = schema.Column(sqltypes.Text, nullable=False)
@property
def project_id(self):
return "{}-{}".format(self.program_name, self.project_code)
@validates("data_type")
def validate_data_type(self, key, data_type):
if data_type not in RELEASED_DATA_DATA_TYPE_VALUES:
raise ValueError(
""""{data_type}" is not a valid value for {key}""".format(
data_type=data_type, key=key))
return data_type
class StudyRuleProgramProject(Base, audit.AuditColumnsMixin):
"""A relationship between study rules, programs, and projects.
A study rule can contain one or more programs. For each program, one or more projects
can be associated.
This relationship is used when the study rule includes a subset of projects from a
program.
Attributes:
study_rule_id: The id of the associated study rule.
program_name: The name of the program to associate with the study rule.
project_code: The code of the project to associate with the study rule.
created_datetime: The date and time when the record is created.
updated_datetime: The date and time when the record is updated.
"""
__tablename__ = "study_rule_program_project"
study_rule_id = schema.Column(sqltypes.Integer, nullable=False)
program_name = schema.Column(sqltypes.Text, nullable=False)
project_code = schema.Column(sqltypes.Text, nullable=False)
__table_args__ = (
schema.PrimaryKeyConstraint("study_rule_id", "program_name", "project_code", name="study_rule_program_project_pk"),
schema.ForeignKeyConstraint(("study_rule_id",), ("study_rule.id",), name="study_rule_program_project_study_rule_id_fk"),
)
def __repr__(self):
return "<StudyRuleProgramProject(study_rule_id={study_rule_id}, program_name='{program_name}', project_code='{project_code}', created_datetime={created_datetime}, updated_datetime={updated_datetime})>".format(
study_rule_id=self.study_rule_id,
program_name=self.program_name,
project_code=self.project_code,
created_datetime=self.created_datetime.isoformat() if self.created_datetime else None,
updated_datetime=self.updated_datetime.isoformat() if self.updated_datetime else None,
)
def to_json(self):
return {
"study_rule_id": self.study_rule_id,
"program_name": self.program_name,
"project_code": self.project_code,
"created_datetime": self.created_datetime.isoformat() if self.created_datetime else None,
"updated_datetime": self.updated_datetime.isoformat() if self.updated_datetime else None,
}
class State(datasets.Entity):
"""Entity class for states."""
_name = 'state'
__table__ = schema.Table(
'states', datasets.Entity.metadata,
schema.Column('id',
types.SmallInteger,
nullable=False,
primary_key=True),
schema.Column('name', types.String(32), nullable=False, index=True))
# Relationships
mesoregions = orm.relationship('Mesoregion', back_populates='state')
microregions = orm.relationship('Microregion', back_populates='state')
municipalities = orm.relationship('Municipality', back_populates='state')
districts = orm.relationship('District', back_populates='state')
subdistricts = orm.relationship('Subdistrict', back_populates='state')
class GroupPrincipal(Principal):
""" Security principal based on membership in a group. """
__tablename__ = 'group_principal'
__versioned__ = {}
id = schema.Column(UuidType,
schema.ForeignKey('principal.id'),
default=uuid.uuid4,
primary_key=True)
group_id = schema.Column(UuidType,
schema.ForeignKey('group.id'),
nullable=False)
group = relationship('Group', back_populates='principals')
__mapper_args__ = {
'polymorphic_identity': 'group-principal',
'inherit_condition': id == Principal.id,
}
class PersonPrincipal(Principal):
""" Security principal based on a person/user entity. """
__tablename__ = 'person_principal'
__versioned__ = {}
id = schema.Column(UuidType,
schema.ForeignKey('principal.id'),
default=uuid.uuid4,
primary_key=True)
person_id = schema.Column(UuidType,
schema.ForeignKey('person.id'),
nullable=False,
unique=True)
person = relationship('Person', back_populates='principal')
__mapper_args__ = {
'polymorphic_identity': 'person-principal',
'inherit_condition': id == Principal.id,
}
class Role(ModelBase):
""" Roles granted to a principal. """
__tablename__ = 'role'
__versioned__ = {}
grant_id = schema.Column(UuidType, default=uuid.uuid4, primary_key=True)
name = schema.Column(types.String, nullable=False)
target_type = schema.Column(types.String(50), nullable=False)
principal_id = schema.Column(UuidType,
schema.ForeignKey('principal.id'),
nullable=False)
principal = relationship('Principal', back_populates='roles')
__mapper_args__ = {
'polymorphic_identity': 'role',
'polymorphic_on': target_type
}
class StudyRule(Base, audit.AuditColumnsMixin):
"""A study rule for use with single-study controlled-access.
Attributes:
id: A unique identifier for the study rule.
name: A unique, informational name for the study rule.
created_datetime: The date and time when the record is created.
updated_datetime: The date and time when the record is updated.
"""
__tablename__ = "study_rule"
id_seq = schema.Sequence(name="study_rule_id_seq", metadata=Base.metadata)
id = schema.Column(sqltypes.Integer, nullable=False, server_default=id_seq.next_value())
name = schema.Column(sqltypes.Text, nullable=False)
__table_args__ = (
schema.PrimaryKeyConstraint("id", name="study_rule_pk"),
schema.Index("study_rule_name_idx", "name", unique=True),
)
whole_programs = orm.relationship("StudyRuleProgram", lazy="joined")
partial_programs = orm.relationship("StudyRuleProgramProject", lazy="joined")
def __repr__(self):
return "<StudyRule(id={id}, name='{name}', created_datetime={created_datetime}, updated_datetime={updated_datetime})>".format(
id=self.id,
name=self.name,
created_datetime=self.created_datetime.isoformat() if self.created_datetime else None,
updated_datetime=self.updated_datetime.isoformat() if self.updated_datetime else None,
)
def to_json(self):
return {
"id": self.id,
"name": self.name,
"created_datetime": self.created_datetime.isoformat() if self.created_datetime else None,
"updated_datetime": self.updated_datetime.isoformat() if self.updated_datetime else None,
}
class ElectionGroupRole(Role):
""" Roles granted on election. """
__tablename__ = 'election_group_role'
__versioned__ = {}
grant_id = schema.Column(UuidType,
schema.ForeignKey('role.grant_id'),
default=uuid.uuid4,
primary_key=True)
group_id = schema.Column(UuidType, schema.ForeignKey('election_group.id'))
group = relationship('ElectionGroup', backref='roles', lazy='joined')
global_role = schema.Column(types.Boolean)
__table_args__ = (schema.CheckConstraint(or_(
and_(global_role == true(), group_id == null()),
or_(global_role == null(), global_role == false())),
name='no_eg_when_global'), )
# principal_id = schema.Column(
# UuidType,
# schema.ForeignKey('principal.id'),
# nullable=False)
# principal = relationship(
# 'Principal',
# back_populates='roles')
# __table_args__ = (
# schema.UniqueConstraint('role', 'election_id', 'principal_id'),
# )
__mapper_args__ = {
'polymorphic_identity': 'election-group-role',
}
class Principal(ModelBase):
"""
Security principal.
A security principal is an abstract representation of:
- an individual user,
- a group of users,
- an identifier able to be mapped to a logged in user
"""
__tablename__ = 'principal'
__versioned__ = {}
id = schema.Column(UuidType, default=uuid.uuid4, primary_key=True)
principal_type = schema.Column(types.String, nullable=False)
roles = relationship('Role', back_populates='principal')
__mapper_args__ = {
'polymorphic_identity': 'principal',
'polymorphic_on': principal_type,
}
def temp_create_table_from_metadata(self, metadata_file, database):
' create a table '
meta_path = pathlib.Path(metadata_file)
if not meta_path.exists() or not meta_path.is_file():
sys.exit('{} is not a valid file'.format(meta_path))
metadata = yaml.load(meta_path.open(), Loader=yaml.SafeLoader)
def get_clickhouse_type(sa_type):
clickhouse_types = {
'BOOLEAN': ch_types.UInt8,
'TINYINT': ch_types.Int8,
'SMALLINT': ch_types.Int16,
'INTEGER': ch_types.Int32,
'BIGINT': ch_types.Int64,
'FLOAT': ch_types.Float64,
'VARCHAR': ch_types.String
}
return clickhouse_types.get(sa_type, None)
def get_clickhouse_sa_columns(metadata):
columns = metadata['columns']
ch_columns = []
for idx, col in enumerate(columns):
name = col['name']
col_type = col['type'],
ch_type = get_clickhouse_type(col['type'])
nullable = col['nullable']
# make the first column non-nullable (needed for Clickhouse)
if idx == 0:
tbl_col = sa_schema.Column(name, ch_type)
else:
tbl_col = sa_schema.Column(name,
ch_types.Nullable(ch_type))
ch_columns.append(tbl_col)
return ch_columns
host, port = get_clickhouse_host_port()
engine = get_clickhouse_engine(host, port, database)
ch_columns = get_clickhouse_sa_columns(metadata)
first_col_name = ch_columns[0].name
meta = sa.sql.schema.MetaData()
# temp = sa_schema.Table(metadata['table'], meta)
new_table = sa_schema.Table(metadata['table'], meta)
for idx, col in enumerate(ch_columns):
new_table.append_column(col)
new_table.append_column(engines.MergeTree(order_by=(first_col_name, )))
new_table.create(engine)
return
temp.append_column(sa_schema.Column('id', ch_types.Int64))
temp.append_column(
sa_schema.Column('grp_code', ch_types.Nullable(ch_types.Int64)))
temp.append_column(engines.MergeTree(order_by=('id', )))
temp.create(engine)
return
sql = '''
create table temp4
(
`id` Int64,
`grp_code` Int64
)
ENGINE = MergeTree()
ORDER BY id
'''
host, port = get_clickhouse_host_port()
database = 'default'
execute_clickhouse_sql(host, port, database, sql)
