def upgrade(): op.add_column("publication", sa.Column("publication_uuid", BINARY(16), nullable=False)) op.execute( "UPDATE publication SET publication_uuid = UNHEX(REPLACE(UUID(), '-', ''))" ) op.create_index("uq_publication_uuid", "publication", ["publication_uuid"], unique=True) op.add_column("publication_mapping", sa.Column("publication_uuid", BINARY(16), nullable=False)) op.execute("""UPDATE publication_mapping JOIN publication ON publication_mapping.publication_id = publication.publication_id SET publication_mapping.publication_uuid = publication.publication_uuid""" ) op.create_index( "uq_publication_mapping_uuid", "publication_mapping", ["publication_uuid", "version"], unique=True, ) op.alter_column( "publication", "publication_id", autoincrement=False, existing_type=sa.Integer, nullable=False, ) op.execute( "ALTER TABLE publication DROP PRIMARY KEY, ADD PRIMARY KEY (publication_uuid)" ) op.alter_column( "publication", "publication_id", new_column_name="publication_int_id", existing_type=sa.Integer, ) op.alter_column( "publication", "publication_uuid", new_column_name="publication_id", existing_type=BINARY(16), nullable=False, ) op.alter_column( "publication_mapping", "publication_id", new_column_name="publication_int_id", existing_type=sa.Integer, ) op.alter_column( "publication_mapping", "publication_uuid", new_column_name="publication_id", existing_type=BINARY(16), nullable=False, )
class Review(Base): __tablename__ = 'reviews' value = Column('rev_value', BINARY(10)) article = Column('rev_article', BINARY(255), primary_key=True) timestamp = Column('rev_timestamp', BINARY(20)) def __repr__(self): return "<Review(value=%r, article=%r, timestamp=%r)>" % ( self.value, self.article, self.timestamp)
def downgrade(): op.alter_column( "publication", "producer_id", new_column_name="producer_uuid", existing_type=BINARY(16), ) op.alter_column( "publication", "producer_int_id", new_column_name="producer_id", existing_type=sa.Integer, ) op.alter_column( "producer_mapping", "producer_id", new_column_name="producer_uuid", existing_type=BINARY(16), ) op.alter_column( "producer_mapping", "producer_int_id", new_column_name="producer_id", existing_type=sa.Integer, ) op.alter_column( "producer", "producer_id", new_column_name="producer_uuid", existing_type=BINARY(16), nullable=False, ) op.alter_column( "producer", "producer_int_id", new_column_name="producer_id", existing_type=sa.Integer, ) op.execute( "ALTER TABLE producer DROP PRIMARY KEY, ADD PRIMARY KEY (producer_id)") op.alter_column("producer", "producer_id", autoincrement=True, existing_type=sa.Integer) op.drop_column("publication", "producer_uuid") op.drop_index("uq_producer_mapping_uuid", table_name="producer_mapping") op.drop_column("producer_mapping", "producer_uuid") op.drop_index("uq_producer_uuid", table_name="producer") op.drop_column("producer", "producer_uuid")
class SB_Membership(Base): """Data model object representing a Membership""" __tablename__ = 'Membership' __uuid_list__ = {'group_id', 'user_id'} __wo_fields__ = set() __primary_key__ = 'user_id' group_id = Column(BINARY(16), ForeignKey('Group.group_id'), primary_key=True) user_id = Column(BINARY(16), ForeignKey('User.user_id'), primary_key=True) is_owner = Column(Boolean) is_admin = Column(Boolean) user = relationship('User', back_populates='memberships') group = relationship('Group', back_populates='memberships')
class BinaryUUID(TypeDecorator): """Optimize UUID keys. Store as 16 bit binary, retrieve as uuid.""" # Required and identifies the TypeEngine class. impl = BINARY(16) def process_bind_param(self, value, dialect): # pylint: disable=no-self-use, unused-argument """On the way in. :param value: UUID :param dialect: :return: """ try: return value.bytes except AttributeError: try: return uuid.UUID(value).bytes except TypeError: return value def process_result_value(self, value, dialect): # pylint: disable=no-self-use, unused-argument """On the way out. :param value: UUID in bytes :param dialect: :return: """ return uuid.UUID(bytes=value)
class SB_User(Base): """Data model object representing a User""" __tablename__ = 'User' __uuid_list__ = {'user_id'} __wo_fields__ = {'password'} __primary_key__ = 'user_id' user_id = Column(BINARY(16), primary_key=True) username = Column(String(32)) email = Column(String(80)) first_name = Column(String(80)) full_name = Column(String(120)) last_name = Column(String(80)) phone = Column(String(20)) source = Column(String(80)) profiles = Column(JSON) create_users = Column(Boolean) create_groups = Column(Boolean) grant_privs = Column(Boolean) memberships = relationship('Membership', back_populates='user') def __init__(self, **kwargs): """Initializes the ID for newly constructed objects""" super(SB_User, self).__init__(**kwargs) self.user_id = uuid.uuid4().bytes
class Reputation_Data(Base): __tablename__ = 'reputation_data' rep_ip_dst = Column('rep_ip_dst', VARBINARY(16), primary_key=False) rep_rel_dst = Column('rep_rel_dst', TINYINT, primary_key=False) event_id = Column('event_id', BINARY(16), ForeignKey('acid_event.id'), primary_key=True) rep_rel_src = Column('rep_rel_src', TINYINT, primary_key=False) rep_prio_dst = Column('rep_prio_dst', TINYINT, primary_key=False) rep_act_dst = Column('rep_act_dst', VARCHAR(64), primary_key=False) rep_ip_src = Column('rep_ip_src', VARBINARY(16), primary_key=False) rep_act_src = Column('rep_act_src', VARCHAR(64), primary_key=False) rep_prio_src = Column('rep_prio_src', TINYINT, primary_key=False) # # Relations: # @property def serialize(self): return { 'rep_ip_dst': get_ip_str_from_bytes(self.rep_ip_dst), 'rep_rel_dst': self.rep_rel_dst, 'event_id': get_uuid_string_from_bytes(self.event_id), 'rep_rel_src': self.rep_rel_src, 'rep_prio_dst': self.rep_prio_dst, 'rep_act_dst': self.rep_act_dst, 'rep_ip_src': get_ip_str_from_bytes(self.rep_ip_src), 'rep_act_src': self.rep_act_src, 'rep_prio_src': self.rep_prio_src, }
class Monitor_Data(Base): __tablename__ = 'monitor_data' monitor_id = Column('monitor_id', INTEGER, primary_key=True) timestamp = Column('timestamp', TIMESTAMP, primary_key=True) component_id = Column('component_id', BINARY(16), primary_key=True) data = Column('data', TEXT, primary_key=False) component_type = Column('component_type', VARCHAR(55), primary_key=False) # # Relations: # @property def serialize(self): return { 'monitor_id': self.monitor_id, 'timestamp': self.timestamp, 'component_id': get_uuid_string_from_bytes(self.component_id) if self.component_id else '', 'data': self.data, 'component_type': self.component_type }
class Base(object): @declared_attr def __tablename__(cls): return cls.__name__ @declared_attr def __primary_key__(cls): return to_snakecase(cls.__name__) + '_id' __uuid_list__ = {'_creator_user_id'} __wo_fields__ = set() __table_args__ = {'mysql_charset':'utf8'} record_created = Column(DATETIME, server_default=text('CURRENT_TIMESTAMP')) record_updated = Column(DATETIME, server_default=text('CURRENT_TIMESTAMP'), onupdate=datetime.datetime.now) _creator_user_id = Column(BINARY(16)) def is_uuid(self, field_name): return field_name in self.__uuid_list__ def assign_id(self): """Initializes the object""" LOGGER.info('assign_id for {}'.format(type(self).__name__)) setattr(self, self.__primary_key__, uuid.uuid4().bytes) def apply_update(self, fields): """Applies a dict to the fields in this object""" mapper = inspect(type(self)) for column in mapper.attrs: # Skip columns that are relationships if type(column.class_attribute.property).__name__ == 'RelationshipProperty': continue # If the column exists in the field dictionary, update its value if column.key in fields: if self.is_uuid(column.key): setattr(self, column.key, uuid.UUID(fields[column.key]).bytes) elif type(column.columns[0].type) == DATETIME: setattr(self, column.key, datetime.datetime.fromisoformat(fields[column.key].replace("Z","+00:00"))) else: setattr(self, column.key, fields[column.key]) def get_uuid(self): """Returns the text version of the UUID, the binary version is stored in the database""" return str(uuid.UUID(bytes=getattr(self, self.__primary_key__))) def dump(self, deep=False): """Returns dictionary of fields and values""" ret = {} mapper = inspect(type(self)) for key, value in vars(self).items(): column = mapper.attrs.get(key) if column and type(column).__name__ == 'RelationshipProperty': continue if key in self.__uuid_list__ and value: ret[key] = str(uuid.UUID(bytes=value)) elif column and value and type(column.columns[0].type) == DATETIME: # Hacky way of delivering date in iso format in UTC that aligns with Javascript ret[key] = value.isoformat()[:-3] + "Z" elif not key.startswith('_') and not key in self.__wo_fields__: ret[key] = value return ret
class User(hs_base, HomestackDatabase): """ Class that represents our User table """ __tablename__ = "Users" __bind_key__ = "homestack" # int: The id of the user user_id = Column(INTEGER(unsigned=True), primary_key=True) id = synonym("user_id") # datetime: The time the user record was originally created time = Column(DATETIME, default=datetime.utcnow, nullable=False, index=True) # datetime: The time the user last logged in timestamp = Column(DATETIME, default=datetime.utcnow, nullable=False, index=True) # str: The username for this user username = Column(VARCHAR(128), unique=True, nullable=False) # bin: The binary representation of a sha256 hash, generated by pbkdf2 hasking password_salt = Column(BINARY(32), nullable=False, index=True, default=lambda: os.urandom(32)) # list: Map the users groups user_groups = relationship("UserGroup", secondary=UserToUserGroup) # Flask-Login related attributes and methods is_authenticated = True is_anonymous = False is_active = True # Determine whether or not this user has a given role def has_role(self, name): for group in self.user_groups: for role in group.roles: if name == role.name: return True return False # Determine whether or not this user is in the specified group def in_group(self, name): return name in [v.name for v in self.user_groups] # Per Miguel Grinberg's suggestion, return Flask-Login friendly unique ID in Unicode def get_id(self): try: return unicode(self.user_id) except NameError: return str(self.user_id)
def upgrade(): op.create_table( "parser_stats", sa.Column("parser_name", sa.String(255), nullable=False), sa.Column("processed_date", sa.Date, nullable=False), sa.Column("producer_id", BINARY(16), nullable=False), sa.Column("processed_count", sa.Integer, nullable=False), sa.PrimaryKeyConstraint("parser_name", "processed_date", "producer_id"), ) op.create_table( "publication_stats", sa.Column("published_date", sa.Date, nullable=False), sa.Column("producer_id", BINARY(16), nullable=False), sa.Column("published_count", sa.Integer, nullable=False), sa.PrimaryKeyConstraint("published_date", "producer_id"), )
class UserPermissions(Base): __tablename__ = "user_perms" component_id = Column('component_id', BINARY(16), primary_key=True) login = Column('login', VARCHAR(64), primary_key=False) @property def serialize(self): return {'component_id': get_uuid_string_from_bytes(self.component_id), 'login': self.login}
class Log(Base): __tablename__ = 'logging' id = Column('log_id', INTEGER(10, unsigned=True), primary_key=True) namespace = Column('log_namespace', BIGINT(11)) type = Column('log_type', BINARY(32)) title = Column('log_title', BINARY(255)) timestamp = Column('log_timestamp', BINARY(14)) params = Column('log_params', BLOB) def __repr__(self): return "<Log(namespace=%r, title=%r, timestamp=%r, type=%r)>" % ( self.namespace, self.title, self.timestamp, self.type) @property def timestamp_dt(self): return datetime.strptime(self.quality_timestamp.decode('utf-8'), '%Y%m%dT%H%M%S')
def load_dialect_impl(self, dialect): if dialect.name == 'postgresql': return dialect.type_descriptor(UUID()) elif dialect.name == 'oracle': return dialect.type_descriptor(RAW(16)) elif dialect.name == 'mysql': return dialect.type_descriptor(BINARY(16)) else: return dialect.type_descriptor(CHAR(32))
class Status_Message(Base): __tablename__ = 'status_message' id = Column('id', BINARY(16), primary_key=True) level = Column('level', TINYINT, primary_key=False) title = Column('title', TEXT, primary_key=False) description = Column('description', TEXT, primary_key=False) type = Column('type', VARCHAR(20), primary_key=False) expire = Column('expire', DATETIME, primary_key=False) actions = Column('actions', TEXT, primary_key=False) alternative_actions = Column('alternative_actions', TEXT, primary_key=False) message_role = Column('message_role', TEXT, primary_key=False) action_role = Column('action_role', TEXT, primary_key=False) source = Column('source', VARCHAR(32), primary_key=False) MESSAGE_LEVEL_MAP = { 1: "info", 2: "warning", 3: "error" } @staticmethod def get_message_level_str(level): """Returns a string representing the current level""" if level in Status_Message.MESSAGE_LEVEL_MAP: return Status_Message.MESSAGE_LEVEL_MAP[level] return "" @staticmethod def get_level_integer_from_string(level_str): """Returns the level integer value from a string value""" level_str = level_str.lower() if level_str not in Status_Message.MESSAGE_LEVEL_MAP.values(): return 0 for key, value in Status_Message.MESSAGE_LEVEL_MAP.iteritems(): if value == level_str: return key return 0 @property def serialize(self): """Returns a serialized object from Status_Message object""" return { 'id': get_uuid_string_from_bytes(self.id) if self.id else '', 'level': Status_Message.get_message_level_str(self.level), 'title': self.title, 'description': self.description, 'type': self.type, 'expire': self.expire, 'actions': self.actions, 'alternative_actions': self.alternative_actions, 'message_role': self.message_role, 'action_role': self.action_role, 'source': self.source }
class Current_Status(Base): __tablename__ = 'current_status' message_id = Column('message_id', SMALLINT, ForeignKey('status_message.id'), primary_key=True) component_id = Column('component_id', BINARY(16), primary_key=True) component_type = Column('component_type', ENUM('net', 'host', 'user', 'sensor', 'server', 'system'), primary_key=False) creation_time = Column('creation_time', TIMESTAMP, primary_key=False) viewed = Column('viewed', ENUM('true', 'false'), primary_key=False) supressed = Column('supressed', TINYINT, primary_key=False) supressed_time = Column('supressed_time', TIMESTAMP, primary_key=False) additional_info = Column('additional_info', TEXT, primary_key=False) message = relationship(Status_Message, lazy=False) # Sonar warning @staticmethod def _get_value_string(s): return s if s is not None else '' # @staticmethod def _get_value_uuid(s): return str(uuid.UUID(bytes=s)) if s is not None else '' @property def serialize(self): component_dict = self.host or self.net or self.sensor or self.user or self.system if len(component_dict.keys()) > 0: (component_name, component_ip) = component_dict.keys()[0] else: (component_name, component_ip) = (None, None) # TODO: Fix relationship in BBDD. Added this to avoid crashing in # apimethods.status #component_name = "" #component_ip = "" message_level = self.message.level if self.message is not None else "" message_description = self.message.desc if self.message is not None else "" return { 'message_id': self.message_id, 'component_id': self._get_value_uuid(self.component_id), 'component_type': self.component_type, 'component_name': self._get_value_string(component_name), 'component_ip': self._get_value_string(component_ip), 'supressed_time': self.supressed_time, 'creation_time': self.creation_time, 'supressed': self.supressed, 'viewed': True if self.viewed != 'false' else False, 'level': message_level, 'description': message_description }
def downgrade(): op.alter_column( "publication_mapping", "publication_id", nullable=True, existing_type=BINARY(16) ) op.alter_column( "publication_mapping", "version", nullable=True, existing_type=sa.Integer ) op.alter_column("publication_mapping", "info", nullable=True, existing_type=sa.JSON) op.alter_column( "producer_mapping", "site_id", autoincrement=True, existing_type=sa.Integer )
class AsecDb_Suggestion_pattern(Base): __tablename__ = 'suggestion_pattern' __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'} id = Column(MEDIUMINT, primary_key=True) suggestion_group_id = Column(BINARY(16), nullable=False) pattern_json = Column(TEXT, nullable=False) def __repr__(self): return "<SuggestionPattern('%s','%s','%s')>" % ( self.id, str( UUID(bytes=self.suggestion_group_id)), self.pattern_json)
class Ac_Acid_Event(Base): __tablename__ = 'ac_acid_event' cnt = Column('cnt', INTEGER, primary_key=False) ctx = Column('ctx', BINARY(16), primary_key=True, index=True) src_net = Column('src_net', BINARY(16), primary_key=True, index=True) day = Column('day', DATETIME, primary_key=True, index=True) dst_host = Column('dst_host', BINARY(16), primary_key=True, index=True) dst_net = Column('dst_net', BINARY(16), primary_key=True, index=True) plugin_id = Column('plugin_id', INTEGER, primary_key=True, autoincrement=False, index=True) device_id = Column('device_id', INTEGER, primary_key=True, autoincrement=False, index=True) plugin_sid = Column('plugin_sid', INTEGER, primary_key=True, autoincrement=False, index=True) src_host = Column('src_host', BINARY(16), primary_key=True, index=True) # # Relations: # @property def serialize(self): return { 'cnt': self.cnt, 'ctx': get_uuid_string_from_bytes(self.ctx), 'src_net': get_uuid_string_from_bytes(self.src_net), 'day': self.day, 'dst_host': get_uuid_string_from_bytes(self.dst_host), 'dst_net': get_uuid_string_from_bytes(self.dst_net), 'plugin_id': self.plugin_id, 'device_id': self.device_id, 'plugin_sid': self.plugin_sid, 'src_host': get_uuid_string_from_bytes(self.src_host), }
class AsecDb_AlarmCoincidence(Base): __tablename__ = 'alarm_coincidence' __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'} id = Column(MEDIUMINT, primary_key=True) sensor_id = Column(BINARY(16), nullable=False) sample_log = Column(TEXT) data = Column(TEXT) datetime = Column(DATETIME, default=datetime.now()) def __repr__(self): return "<AlarmCoincidence('%s', '%s','%s')>" % (self.id, self.log_id, data)
class AsecDb_Suggestion(Base): __tablename__ = 'suggestions' __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'} id = Column(MEDIUMINT, primary_key=True) suggestion_group_id = Column(BINARY(16), nullable=False) filename = Column(VARCHAR(255), nullable=False) location = Column(VARCHAR(255), nullable=False) datetime = Column(DATETIME, default=datetime.now) def __repr__(self): return "<Suggestions('%s','%s','%s','%s')>" % ( self.id, str(UUID(bytes=self.suggestion_group_id)), self.filename, self.location)
class AsecDb_Notification(Base): __tablename__ = 'notification' __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'} id = Column(MEDIUMINT, primary_key=True) sensor_id = Column(BINARY(16), nullable=False) plugin_id = Column(MEDIUMINT, nullable=False) rule_name = Column(VARCHAR(45), nullable=False) log_file = Column(VARCHAR(45), nullable=False) datetime = Column(DATETIME, default=datetime.now()) def __repr__(self): return "<Notifications('%s','%s','%s','%s','%s')>" % ( self.id, str( UUID(bytes=self.sensor_id)), self.plugin_id, self.rule_name)
class Password(hs_base, HomestackDatabase): """ Class that represents our Passwords table """ __tablename__ = "Passwords" __bind_key__ = "homestack" # int: The ID of the password row password_id = Column(INTEGER(unsigned=True), primary_key=True) id = synonym('password_id') # bin: the binary representation of a sha256 encrypted password hashed_password = Column(BINARY(128), nullable=False, index=True)
class BinaryUUID(TypeDecorator): impl = BINARY(16) def process_bind_param(self, value, dialect): try: return value.bytes except AttributeError: try: return uuid.UUID(value).bytes except TypeError: return value def process_result_value(self, value, dialect): return uuid.UUID(bytes=value)
class Celery_Job (Base): __tablename__ = 'celery_job' info = Column('info', BLOB, primary_key=False) last_modified = Column('last_modified', TIMESTAMP, primary_key=False) id = Column('id', BINARY(16), primary_key=True) # # Relations: # @property def serialize(self): return {'info': self.info, 'last_modified': self.last_modified, 'id': str(uuid.UUID(bytes=self.id)) if self.id else ''}
class Team(db.Model): """A team of players working together. Can own assets and money.""" __tablename__ = "team" id = Column(UnsignedInt, primary_key=True) name = Column(String(32), nullable=False, unique=True) picture = Column(BINARY(128), nullable=False) entity_id = Column(UnsignedInt, ForeignKey("entity.id"), nullable=False, unique=True) entity = relationship("Entity") def __repr__(self): return f"<Team '{self.name}'>"
def upgrade(): op.alter_column( "producer_mapping", "site_id", autoincrement=False, existing_type=sa.Integer ) op.alter_column( "publication_mapping", "info", nullable=False, existing_type=sa.JSON ) op.alter_column( "publication_mapping", "version", nullable=False, existing_type=sa.Integer ) op.execute("DELETE FROM publication_mapping WHERE publication_id IS NULL") op.alter_column( "publication_mapping", "publication_id", nullable=False, existing_type=BINARY(16), )
class Account(db.Model): """An account is used to sign in to Doctrine, and contains all user information and credentials.""" __tablename__ = "account" id = Column(UnsignedInt, primary_key=True) email_address_id = Column(UnsignedInt, ForeignKey("email_address.id"), nullable=False, unique=True) email_address = relationship("EmailAddress") password_hash = Column(BINARY(60), nullable=False) is_developer = Column(Boolean, nullable=False, default=False) creation_time = Column(DateTime, nullable=False, default=datetime.utcnow) def __init__(self, email_address, password): # Generate password hash password_hash = bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()) super().__init__(email_address=email_address, password_hash=password_hash) def _asdict(self): account_info = { "id": self.id, "email_address": str(self.email_address), "creation_time": self.creation_time.replace(tzinfo=timezone.utc).isoformat(), "is_developer": self.is_developer, "profiles": [ profile._asdict(embed_account=False) for profile in Profile.query.filter_by(account=self).all() ], } return account_info def __repr__(self): return f"<Account '{self.email_address}'>"
class EmailAddress(db.Model): """Email addresses are normalised in order for uniqueness to be enforced.""" __tablename__ = "email_address" id = Column(UnsignedInt, primary_key=True) hash = Column(BINARY(16), nullable=False, unique=True) email_address = Column(String(255), nullable=False) def __init__(self, email_address): # Generate hash from the email address automatically email_hash = mmh3.hash128(email_address) email_hash = email_hash.to_bytes(16, byteorder="big") super().__init__(hash=email_hash, email_address=email_address) def __repr__(self): return f"<EmailAddress '{self.email_address}'>" def __str__(self): return self.email_address
class Sig_Reference(Base): __tablename__ = 'sig_reference' plugin_id = Column('plugin_id', INTEGER(11), primary_key=True) ctx = Column('ctx', BINARY(16), primary_key=True) plugin_sid = Column('plugin_sid', INTEGER(11), primary_key=True) ref_id = Column('ref_id', INTEGER(10), ForeignKey('reference.ref_id'), primary_key=True) # # Relations: # @property def serialize(self): return { 'plugin_id': self.plugin_id, 'ctx': get_uuid_string_from_bytes(self.ctx), 'plugin_sid': self.plugin_sid, 'ref_id': self.ref_id, }