def upgrade():
op.create_table(
'draft',
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('table', sa.UnicodeText(), nullable=False),
sa.Column('table_row_id', UUIDType(binary=False), nullable=False),
sa.Column('body', JsonDict(), server_default='{}', nullable=False),
sa.Column('revision', UUIDType(binary=False), nullable=True),
sa.PrimaryKeyConstraint('table', 'table_row_id'),
)
def upgrade():
op.create_table('audit', sa.Column('id', UUIDType(), nullable=False),
sa.Column('user_id', UUIDType(), nullable=False),
sa.Column('code', sa.Text(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_audit_created_at'),
'audit', ['created_at'],
unique=False)
class NotificationTrackingRecord(Base):
__tablename__ = 'notification_tracking'
# Application ID.
application_id = Column(UUIDType(), primary_key=True)
# Upstream application ID.
upstream_application_id = Column(UUIDType(), primary_key=True)
# Partition ID.
pipeline_id = Column(Integer(), primary_key=True)
# Notification ID.
notification_id = Column(BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
def upgrade():
op.create_table(
'questionnaire_types',
sa.Column('id', UUIDType(binary=False), nullable=False),
sa.Column('type_name', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('pk_questionnaire_types')))
with op.batch_alter_table('questionnaire') as batch_op:
batch_op.add_column(
sa.Column('questionnaire_type_id',
UUIDType(binary=False),
nullable=True))
batch_op.create_foreign_key(
op.f('fk_questionnaire_questionnaire_type_questionnaire_types'),
'questionnaire_types', ['questionnaire_type_id'], ['id'])
class StoredEventRecord(Base):
__tablename__ = 'stored_events'
# Record ID.
id = Column(BigInteger().with_variant(Integer, "sqlite"),
primary_key=True,
index=True,
unique=True)
# Originator ID (e.g. an entity or aggregate ID).
originator_id = Column(UUIDType(), nullable=False)
# Originator version of item in sequence.
originator_version = Column(BigInteger().with_variant(Integer, "sqlite"),
nullable=False)
# Type of the event (class name).
event_type = Column(Text(), nullable=False)
# State of the item (serialized dict, possibly encrypted).
state = Column(Text())
__table_args__ = (Index('stored_events_sequence_id_position_index',
'originator_id',
'originator_version',
unique=True), )
class IntegerSequencedItem(db.Model):
__tablename__ = 'integer_sequenced_items'
sequence_id = db.Column(UUIDType(), primary_key=True)
position = db.Column(db.BigInteger(), primary_key=True)
topic = db.Column(db.String(255))
data = db.Column(db.Text())
__table_args__ = db.Index('index', 'sequence_id', 'position'),
class Cell(Base, VariableMixin):
__tablename__ = 'cells'
__table_args__ = (UniqueConstraint("region_id",
"name",
name="uq_cell0regionid0name"), )
id = Column(Integer, primary_key=True)
region_id = Column(Integer,
ForeignKey('regions.id'),
index=True,
nullable=False)
cloud_id = Column(Integer,
ForeignKey('clouds.id'),
index=True,
nullable=False)
project_id = Column(UUIDType(binary=False),
ForeignKey('projects.id'),
index=True,
nullable=False)
name = Column(String(255))
note = Column(Text)
_repr_columns = [id, name]
project = relationship('Project', back_populates='cells')
cloud = relationship('Cloud', back_populates='cells')
region = relationship('Region', back_populates='cells')
devices = relationship('Device', back_populates='cell')
networks = relationship('Network', back_populates='cell')
@property
def resolution_order(self):
return list(
itertools.chain([self], [self.region], [self.cloud],
[self.project]))
class SqlTimestampSequencedItem(Base):
# Explicit table name.
__tablename__ = 'timestamp_sequenced_items'
# Unique constraint.
__table_args__ = UniqueConstraint('sequence_id',
'position',
name='time_sequenced_items_uc'),
# Primary key.
id = Column(Integer,
Sequence('integer_sequened_item_id_seq'),
primary_key=True)
# Sequence ID (e.g. an entity or aggregate ID).
sequence_id = Column(UUIDType(), index=True)
# Position (timestamp) of item in sequence.
position = Column(Float(), index=True)
# Topic of the item (e.g. path to domain event class).
topic = Column(String(255))
# State of the item (serialized dict, possibly encrypted).
data = Column(Text())
class SqlIntegerSequencedItem(Base):
__tablename__ = 'integer_sequenced_items'
id = Column(Integer,
Sequence('integer_sequened_item_id_seq'),
primary_key=True)
# Sequence ID (e.g. an entity or aggregate ID).
sequence_id = Column(UUIDType(), index=True)
# Position (index) of item in sequence.
position = Column(BigInteger(), index=True)
# Topic of the item (e.g. path to domain event class).
topic = Column(String(255))
# State of the item (serialized dict, possibly encrypted).
data = Column(Text())
# Unique constraint includes 'entity_id' which is a good value
# to partition on, because all events for an entity will be in the same
# partition, which may help performance.
__table_args__ = UniqueConstraint('sequence_id',
'position',
name='integer_sequenced_item_uc'),
class IntegerSequencedWithIDRecord(Base):
__tablename__ = 'integer_sequenced_items'
# Record ID.
id = Column(BigInteger().with_variant(Integer, "sqlite"),
primary_key=True,
index=True,
unique=True)
# Sequence ID (e.g. an entity or aggregate ID).
sequence_id = Column(UUIDType(), nullable=False)
# Position (index) of item in sequence.
position = Column(BigInteger().with_variant(Integer, "sqlite"),
nullable=False)
# Topic of the item (e.g. path to domain event class).
topic = Column(Text(), nullable=False)
# State of the item (serialized dict, possibly encrypted).
data = Column(Text())
__table_args__ = (Index(
'integer_sequenced_items_sequence_id_position_index',
'sequence_id',
'position',
unique=True), )
class ThingyNote(db.Model):
user_id = Column(UUIDType(), ForeignKey(User.id), primary_key=True)
thingy_id = Column(Integer, ForeignKey(Thingy.id), primary_key=True)
value = Column(String(5000), nullable=False)
added = Column(DateTime(timezone=True), server_default=func.now())
thingy = relationship(Thingy, backref="note", uselist=False)
user = relationship(User)
class SameyBaseDBTable:
@declared_attr
def __tablename__(self):
# Generate __tablename__ automatically by turning CamelCase class name to snake_case
snake = re.sub("(.)([A-Z][a-z]+)", r"\1_\2", self.__name__)
return re.sub("([a-z0-9])([A-Z])", r"\1_\2", snake).lower()
@declared_attr
def __table_args__(self):
return {"mysql_engine": "InnoDB"}
@hybrid_property
def id(self):
try:
return shortuuid.encode(self.uuid)
except AttributeError:
return
pk = Column(Integer(), primary_key=True)
uuid = Column(UUIDType(), nullable=False, default=uuid4)
name = Column(String(255), nullable=False)
created = Column(DateTime(), default=datetime.utcnow)
changed = Column(DateTime())
@id.setter
def id(self, set_id):
if set_id is str:
uuid = shortuuid.decode(set_id)
if uuid is UUID4:
self.uuid = shortuuid.decode(set_id)
def __str__(self):
return self.name
class Token(Base):
__tablename__ = "Token"
id = db.Column(UUIDType(binary=False, cache_ok=False),
default=uuid4,
primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('User.id'))
task_id = db.Column(db.Integer, db.ForeignKey('Task.id'))
room_name = db.Column(db.String(100),
db.ForeignKey('Room.name'),
nullable=False)
permissions_id = db.Column(db.Integer,
db.ForeignKey("Permissions.id"),
nullable=False)
source = db.Column(db.String(100))
valid = db.Column(db.Boolean, default=True, nullable=False)
def __repr__(self):
return str(self.id)
def as_dict(self):
return dict(
{
'user': self.user_id,
'task': self.task_id,
'room': self.room_name,
'permissions': self.permissions.as_dict(),
'source': self.source,
'valid': self.valid,
},
**super(Token, self).as_dict())
class KeyInfo(Base):
__tablename__ = "keys"
id = Column(Integer, primary_key=True, autoincrement=True)
created = Column(DateTime, default=datetime.utcnow())
expires_at = Column(DateTime)
key_hash = Column(String(256), unique=True)
identifier = Column(UUIDType(binary=False), nullable=False)
class ExtendedIntegerSequencedRecord(Base):
__tablename__ = 'extended_integer_sequenced_items'
id = Column(BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
# Sequence ID (e.g. an entity or aggregate ID).
sequence_id = Column(UUIDType(), nullable=False)
# Position (index) of item in sequence.
position = Column(BigInteger(), nullable=False)
# Topic of the item (e.g. path to domain event class).
topic = Column(String(255), nullable=False)
# State of the item (serialized dict, possibly encrypted).
state = Column(Text())
# Timestamp of the event.
timestamp = Column(DECIMAL(24, 6, 6), nullable=False)
# timestamp = Column(DECIMAL(27, 9, 9), nullable=False)
# Type of the event (class name).
event_type = Column(String(255))
__table_args__ = (Index('integer_sequenced_items_index',
'sequence_id',
'position',
unique=True), )
def upgrade():
"""Upgrade database."""
op.create_table(
"workflows_record_sources",
sa.Column("created", sa.DateTime(), nullable=True),
sa.Column("updated", sa.DateTime(), nullable=True),
sa.Column("record_uuid", UUIDType(), nullable=False),
sa.Column(
"source",
postgresql.ENUM("arxiv",
"submitter",
"publisher",
name="source_enum"),
nullable=False,
),
sa.Column("json",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.ForeignKeyConstraint(
["record_uuid"],
["records_metadata.id"],
name=op.f(
"fk_workflows_record_sources_record_uuid_records_metadata"),
ondelete="CASCADE",
),
sa.PrimaryKeyConstraint("record_uuid",
"source",
name=op.f("pk_workflows_record_sources")),
)
class Task(db.Model):
__tablename__ = 'task'
id = db.Column(UUIDType(binary=False), primary_key=True, default=uuid4)
name = db.Column(db.String(512))
def to_dict(self):
return {'id': self.id.hex, 'name': self.name}
class NetInterface(Base):
__tablename__ = 'net_interfaces'
__table_args__ = (UniqueConstraint("device_id",
"name",
name="uq_netinter0deviceid0name"), )
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=True)
interface_type = Column(String(255), nullable=True)
vlan_id = Column(Integer, nullable=True)
port = Column(Integer, nullable=True)
vlan = Column(String(255), nullable=True)
duplex = Column(String(255), nullable=True)
speed = Column(String(255), nullable=True)
link = Column(String(255), nullable=True)
cdp = Column(String(255), nullable=True)
security = Column(String(255), nullable=True)
project_id = Column(UUIDType(binary=False),
ForeignKey('projects.id'),
index=True,
nullable=False)
device_id = Column(Integer, ForeignKey('devices.id'))
network_id = Column(Integer, ForeignKey('networks.id'), nullable=True)
network = relationship('Network',
back_populates="devices",
cascade='all',
lazy='joined')
device = relationship('Device',
back_populates="interfaces",
cascade='all',
lazy='joined')
class TableRow(db.Model):
csv_file_id = db.Column(UUIDType(binary=False),
db.ForeignKey('csv_file.id'),
primary_key=True)
row_index = db.Column(db.Integer, primary_key=True)
row_type = db.Column(db.String, nullable=False)
subtype = db.Column(db.String, nullable=True)
meta = db.Column(JSONType, nullable=True)
csv_file = db.relationship(CSVFile,
backref=db.backref(
'rows',
lazy=True,
order_by='TableRow.row_index'))
@property
def row_name(self):
return self.csv_file.keys[self.row_index]
@property
def data_row_index(self):
return _data_row_index(self)
@property
def missing_percent(self):
if self.row_type == TABLE_ROW_TYPES.DATA:
return self.csv_file._stats['rows']['missing'][self.data_row_index]
@property
def data_variance(self):
if self.row_type == TABLE_ROW_TYPES.DATA:
return self.csv_file._stats['rows']['variance'][
self.data_row_index]
class User(db.Model):
id = Column(UUIDType(), default=uuid.uuid4().bytes, primary_key=True)
pw_hash = Column(String(256), nullable=False)
pw_expired = Column(DateTime, nullable=True)
pw_updated = Column(DateTime, default=datetime.utcnow)
email = Column(String(255), unique=True, nullable=False, index=True)
email_updated = Column(DateTime(timezone=True), server_default=func.now())
email_verified = Column(DateTime(timezone=True), nullable=True)
phone_number = Column(String(255), nullable=True)
phone_number_updated = Column(DateTime, nullable=True)
phone_number_verified = Column(Boolean, default=False)
created = Column(DateTime(timezone=True), server_default=func.now())
updated = Column(DateTime(timezone=True), onupdate=func.now())
@classmethod
def from_email(cls, email):
return cls.query.filter_by(email=email)
def update_pw_hash(self, pw_hash):
self.pw_hash = pw_hash
self.pw_updated = datetime.utcnow()
self.pw_expired = None
db.session.commit()
def verify_email(self):
self.email_updated = self.email_verified = datetime.utcnow()
db.session.commit()
def __repr__(self):
return f"User(id={self.id}, email='{self.email}, created='{self.created}')"
class SqlStoredEvent(Base):
# Explicit table name.
__tablename__ = 'stored_events'
# Unique constraint.
__table_args__ = UniqueConstraint('aggregate_id',
'aggregate_version',
name='stored_events_uc'),
# Primary key.
id = Column(Integer, Sequence('stored_event_id_seq'), primary_key=True)
# Sequence ID (e.g. an entity or aggregate ID).
aggregate_id = Column(UUIDType(), index=True)
# Position (timestamp) of item in sequence.
aggregate_version = Column(BigInteger(), index=True)
# Type of the event (class name).
event_type = Column(String(100))
# Timestamp of the event.
timestamp = Column(Float())
# State of the item (serialized dict, possibly encrypted).
state = Column(Text())
class Thingy(db.Model):
id = Column(Integer, primary_key=True)
type_id = Column(Integer, ForeignKey(ThingyType.id), primary_key=True)
user_id = Column(UUIDType(), ForeignKey(User.id), primary_key=True)
added = Column(DateTime, default=datetime.utcnow)
type = relationship(ThingyType, backref="thingy", uselist=False)
user = relationship(User)
class TimestampSequencedWithIDRecord(Base):
__tablename__ = "timestamp_sequenced_items"
# Record ID.
id = Column(
BigInteger().with_variant(Integer, "sqlite"),
primary_key=True,
index=True,
unique=True,
autoincrement=True,
)
# Sequence ID (e.g. an entity or aggregate ID).
sequence_id = Column(UUIDType(), nullable=False)
# Position (timestamp) of item in sequence.
position = Column(DECIMAL(24, 6, 6), nullable=False, unique=False)
# Topic of the item (e.g. path to domain event class).
topic = Column(Text(), nullable=False)
# State of the item (serialized dict, possibly encrypted).
state = Column(LargeBinary())
__table_args__ = (
Index(
"timestamp_sequenced_items_sequence_id_position_index",
"sequence_id",
"position",
unique=True,
),
Index("timestamp_sequenced_items_position_index", "position", unique=False),
)
class Network(Base, VariableMixin):
__tablename__ = 'networks'
__table_args__ = (UniqueConstraint("name",
"project_id",
"region_id",
name="uq_name0projectid0regionid"), )
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=True)
cidr = Column(String(255), nullable=True)
gateway = Column(String(255), nullable=True)
netmask = Column(String(255), nullable=True)
ip_block_type = Column(String(255), nullable=True)
nss = Column(String(255), nullable=True)
region_id = Column(Integer,
ForeignKey('regions.id'),
index=True,
nullable=False)
cell_id = Column(Integer,
ForeignKey('cells.id'),
index=True,
nullable=True)
project_id = Column(UUIDType(binary=False),
ForeignKey('projects.id'),
index=True,
nullable=False)
devices = relationship('NetInterface', back_populates='network')
region = relationship('Region', back_populates='networks')
cell = relationship('Cell', back_populates='networks')
project = relationship('Project', back_populates='networks')
class IntegerSequencedItem(db.Model):
__tablename__ = 'integer_sequenced_items'
id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"), primary_key=True)
sequence_id = db.Column(UUIDType(), nullable=False)
position = db.Column(db.BigInteger(), nullable=False)
topic = db.Column(db.String(255), nullable=False)
data = db.Column(db.Text(), nullable=False)
__table_args__ = db.Index('index', 'sequence_id', 'position', unique=True),
class GroupLevel(db.Model):
csv_file_id = db.Column(UUIDType(binary=False),
db.ForeignKey('csv_file.id'),
primary_key=True)
name = db.Column(db.String, primary_key=True)
color = db.Column(db.String, nullable=False)
label = db.Column(db.String)
description = db.Column(db.String)
def __init__(self, *args, **kwargs):
uuid_kwargs = {}
for kwarg in ('binary', 'native'):
if kwarg in kwargs:
uuid_kwargs[kwarg] = kwargs.pop(kwarg)
self.sqlalchemy_type = UUIDType(**uuid_kwargs)
super(UUID, self).__init__(*args, **kwargs)
class IntegerSequencedItem(db.Model):
__tablename__ = "integer_sequenced_items"
id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"), primary_key=True)
sequence_id = db.Column(UUIDType(), nullable=False)
position = db.Column(db.BigInteger(), nullable=False)
topic = db.Column(db.String(255), nullable=False)
state = db.Column(db.Text(), nullable=False)
__table_args__ = (db.Index("index", "sequence_id", "position", unique=True),)
class TableTransform(db.Model):
__table_args__ = (UniqueConstraint('id', 'priority'), )
id = db.Column(UUIDType(binary=False), primary_key=True, default=uuid4)
csv_file_id = db.Column(UUIDType(binary=False),
db.ForeignKey('csv_file.id'),
nullable=False)
created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
transform_type = db.Column(db.String, nullable=False)
args = db.Column(JSONType, nullable=False)
priority = db.Column(db.Float, nullable=False)
csv_file = db.relationship(CSVFile)
def apply(self, table):
kwargs = dict(table=table,
transform_type=self.transform_type,
**self.args)
return transform.dispatch(**kwargs)
class SnapshotRecord(Base):
__tablename__ = 'snapshots'
# Sequence ID (e.g. an entity or aggregate ID).
sequence_id = Column(UUIDType(), primary_key=True)
# Position (index) of item in sequence.
position = Column(BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
# Topic of the item (e.g. path to domain entity class).
topic = Column(Text(), nullable=False)
# State of the item (serialized dict, possibly encrypted).
data = Column(Text())
