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)