class ScopeItem(Base):
__tablename__ = "scope_item"
id = sa.Column(sa.Integer, primary_key=True)
target = sa.Column(sa.String(length=128))
addr_family = sa.Column(sa.Integer)
start_addr = sa.Column(sa.VARBINARY(length=16))
stop_addr = sa.Column(sa.VARBINARY(length=16))
def parse_network_range(self, network: str):
net = IPNetwork(network)
self.addr_family = net.version
size = 4 if net.version == 4 else 16
self.start_addr = net.first.to_bytes(size, byteorder="big")
self.stop_addr = net.last.to_bytes(size, byteorder="big")
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'messages',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=False),
sa.Column('update_at', sa.TIMESTAMP(), nullable=False),
sa.Column('sender_id', sa.INTEGER(), nullable=False),
sa.Column('recipient_id', sa.INTEGER(), nullable=False),
sa.Column('message', sa.VARCHAR(), nullable=True),
sa.Column('is_delete', sa.BOOLEAN(), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'))
op.create_table(
'users',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=False),
sa.Column('update_at', sa.TIMESTAMP(), nullable=False),
sa.Column('login', sa.VARCHAR(length=20), nullable=False),
sa.Column('password', sa.VARBINARY(), nullable=False),
sa.Column('first_name', sa.VARCHAR(length=50), nullable=True),
sa.Column('last_name', sa.VARCHAR(length=50), nullable=True),
sa.Column('is_delete', sa.BOOLEAN(), nullable=False),
sa.Column('position', sa.VARCHAR(length=50), nullable=True),
sa.Column('department', sa.VARCHAR(length=50), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'),
sa.UniqueConstraint('login'))
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('users',
'username',
existing_type=mysql.VARCHAR(length=32),
nullable=True)
op.alter_column('users',
'status',
existing_type=mysql.INTEGER(display_width=11),
nullable=True)
op.alter_column('users',
'password_hash',
existing_type=sa.VARBINARY(length=255),
nullable=True)
op.alter_column('users',
'level',
existing_type=mysql.INTEGER(display_width=11),
nullable=True)
op.alter_column('users',
'created_time',
existing_type=mysql.DATETIME(),
nullable=True)
op.alter_column('games',
'name',
existing_type=mysql.VARCHAR(length=32),
nullable=True)
op.alter_column('companies',
'name',
existing_type=mysql.VARCHAR(length=32),
nullable=True)
class CodecBlockStorage(BaseModel):
__tablename__ = 'codec_block_storage'
block_hash = sa.Column(HashBinary(32),
primary_key=True,
index=True,
nullable=False)
storage_key = sa.Column(sa.VARBINARY(128), primary_key=True, index=True)
block_number = sa.Column(sa.Integer(), nullable=False, index=True)
scale_type = sa.Column(sa.String(255))
storage_module = sa.Column(sa.String(255), nullable=True, index=True)
storage_name = sa.Column(sa.String(255), nullable=True, index=True)
data = sa.Column(sa.JSON())
complete = sa.Column(sa.Boolean(),
nullable=False,
default=False,
index=True)
def __repr__(self):
return "<{}(storage_key={}, block_hash={})>".format(
self.__class__.__name__, self.storage_key.hex(),
self.block_hash.hex())
class Mapping(Base):
__tablename__ = 'mappings'
__table_args__ = (sa.UniqueConstraint('platform',
'digest',
name='platform_identity'), )
class Platform(Enum):
AMAZON = 'Amazon'
EBAY = 'eBay'
CDISCOUNT = 'CDiscount'
id = sa.Column(sa.BigInteger, primary_key=True)
_platform = sa.Column('platform', sa.Enum(Platform), nullable=False)
_dict = sa.Column('identity', sa.JSON, nullable=False)
_digest = sa.Column('digest', sa.VARBINARY(16), nullable=False)
@classmethod
def get_platform(cls, identity: Identity) -> Platform:
if isinstance(identity, AmazonId):
return Mapping.Platform.AMAZON
elif isinstance(identity, CDiscountId):
return Mapping.Platform.CDISCOUNT
elif isinstance(identity, EbayId):
return Mapping.Platform.EBAY
raise NotImplementedError(identity)
@classmethod
def digest(cls, identity: Identity) -> bytes:
identity_json = json.dumps(
identity.asdict(),
sort_keys=True,
).encode("utf-8")
return hashlib.md5(identity_json).digest()
@hybrid_property
def identity(self) -> Identity:
if self._platform == Mapping.Platform.AMAZON:
return AmazonId(**self._dict)
elif self._platform == Mapping.Platform.CDISCOUNT:
return CDiscountId(**self._dict)
elif self._platform == Mapping.Platform.EBAY:
return EbayId(**self._dict)
else:
raise NotImplementedError(self._platform)
@identity.setter
def identity(self, value: Identity) -> None:
self._platform = self.get_platform(value)
self._dict = value.asdict()
self._digest = self.digest(value)
@identity.comparator
def identity(self) -> Mapping.IdentityComparator:
return Mapping.IdentityComparator(self._digest)
class IdentityComparator(Comparator):
def __eq__(self, other: Identity) -> bool:
other_digest = Mapping.digest(other)
return self.__clause_element__() == other_digest
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('user',
'password',
existing_type=sa.VARBINARY(length=1137),
nullable=True)
op.alter_column('user',
'name',
existing_type=mysql.VARCHAR(collation='utf8mb4_general_ci',
length=200),
nullable=True)
op.alter_column('user',
'mail',
existing_type=mysql.VARCHAR(collation='utf8mb4_general_ci',
length=200),
nullable=True)
op.alter_column('report',
'user_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=True)
op.alter_column('report',
'event_id',
existing_type=mysql.INTEGER(display_width=11),
nullable=True)
op.add_column(
'event',
sa.Column('reports',
mysql.INTEGER(display_width=11),
autoincrement=False,
nullable=True))
op.alter_column('event',
'name',
existing_type=mysql.VARCHAR(collation='utf8mb4_general_ci',
length=200),
nullable=True)
op.alter_column('event',
'location',
existing_type=mysql.VARCHAR(collation='utf8mb4_general_ci',
length=200),
nullable=True)
op.alter_column('event',
'funding_start_date',
existing_type=mysql.DATETIME(),
nullable=True)
op.alter_column('event',
'funding_end_date',
existing_type=mysql.DATETIME(),
nullable=True)
op.alter_column('event',
'event_start_date',
existing_type=mysql.DATETIME(),
nullable=True)
op.alter_column('event',
'creation_date',
existing_type=mysql.DATETIME(),
nullable=True)
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'user',
sa.Column('token',
mysql.VARCHAR(collation='utf8mb4_unicode_ci', length=1024),
nullable=True))
op.add_column(
'user', sa.Column('password', sa.VARBINARY(length=1137),
nullable=True))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table("scope_item", schema=None) as batch_op:
batch_op.add_column(
sa.Column("addr_family", sa.Integer(), nullable=True))
batch_op.add_column(
sa.Column("start_addr", sa.VARBINARY(length=16), nullable=True))
batch_op.add_column(
sa.Column("stop_addr", sa.VARBINARY(length=16), nullable=True))
bind = op.get_bind()
session = orm.Session(bind=bind)
for item in session.query(ScopeItem):
net = IPNetwork(item.target)
size = 4 if net.version == 4 else 16
item.addr_family = net.version
item.start_addr = net.first.to_bytes(size, byteorder="big")
item.stop_addr = net.last.to_bytes(size, byteorder="big")
session.commit()
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('users',
'password',
existing_type=sa.VARBINARY(length=1137),
nullable=True)
op.alter_column('users',
'email',
existing_type=mysql.VARCHAR(length=255),
nullable=True)
op.drop_column('users', 'destination')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('SubmissionTable',
sa.Column('ID', mysql.VARCHAR(length=36), nullable=False),
sa.Column('SubmissionDate', mysql.DATETIME(), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('User', mysql.VARCHAR(length=100), nullable=True),
sa.Column('AnalysisType', mysql.VARCHAR(length=25), nullable=True),
sa.Column('IPaddress', sa.VARBINARY(length=16), nullable=True),
sa.PrimaryKeyConstraint('ID'),
mysql_default_charset='latin1',
mysql_engine='InnoDB'
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'users',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=False),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=False),
sa.Column('login', sa.VARCHAR(length=50), nullable=False),
sa.Column('password', sa.VARBINARY(), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'),
sa.UniqueConstraint('login'))
op.add_column('employees', sa.Column('user_id',
sa.INTEGER(),
nullable=True))
class VnfLcmFilters(model_base.BASE):
"""Contains all info about vnf LCM filters."""
__tablename__ = 'vnf_lcm_filters'
__maxsize__ = 65536
id = sa.Column(sa.Integer, nullable=True, primary_key=True)
subscription_uuid = sa.Column(sa.String(36),
sa.ForeignKey('vnf_lcm_subscriptions.id'),
nullable=False)
filter = sa.Column(sa.JSON, nullable=False)
notification_types = sa.Column(sa.VARBINARY(255), nullable=True)
notification_types_len = sa.Column(sa.Integer, nullable=True)
operation_types = sa.Column(sa.LargeBinary(length=__maxsize__),
nullable=True)
operation_types_len = sa.Column(sa.Integer, nullable=True)
def upgrade():
if op.get_bind().engine.name == 'postgresql':
op.create_table(
'nuage_sfc_vlan_subnet_mapping',
sa.Column('subnet_id', sa.String(36), nullable=False),
sa.Column('vlan_bit_map',
sa.dialects.postgresql.BYTEA(),
nullable=False), sa.PrimaryKeyConstraint('subnet_id'),
sa.ForeignKeyConstraint(['subnet_id'], ['subnets.id'],
ondelete='CASCADE'))
else:
op.create_table(
'nuage_sfc_vlan_subnet_mapping',
sa.Column('subnet_id', sa.String(36), nullable=False),
sa.Column('vlan_bit_map', sa.VARBINARY(512), nullable=False),
sa.PrimaryKeyConstraint('subnet_id'),
sa.ForeignKeyConstraint(['subnet_id'], ['subnets.id'],
ondelete='CASCADE'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'badge_template',
sa.Column('created_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('updated_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.UnicodeText(), nullable=False),
sa.Column('description', sa.UnicodeText(), nullable=True),
sa.Column('extends_id', sa.BigInteger(), nullable=True),
sa.Column('min_age', sa.Integer(), nullable=True),
sa.Column('max_age', sa.Integer(), nullable=True),
sa.Column('image', sa.UnicodeText(), nullable=True),
sa.ForeignKeyConstraint(['extends_id'], ['badge_template.id'],
onupdate='CASCADE',
ondelete='RESTRICT'),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_badge_template_created_at'),
'badge_template', ['created_at'],
unique=False)
op.create_index(op.f('ix_badge_template_updated_at'),
'badge_template', ['updated_at'],
unique=False)
op.create_table('flag', sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.Unicode(length=32), nullable=False),
sa.Column('description', sa.UnicodeText(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'user',
sa.Column('created_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('updated_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('username', sa.Unicode(length=128), nullable=False),
sa.Column('password', sa.VARBINARY(length=1024), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_user_created_at'),
'user', ['created_at'],
unique=False)
op.create_index(op.f('ix_user_updated_at'),
'user', ['updated_at'],
unique=False)
op.create_table(
'badge',
sa.Column('created_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('updated_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('foreign_id', sa.String(length=64), nullable=True),
sa.Column('name', sa.UnicodeText(), nullable=False),
sa.Column('real_age', sa.Integer(), nullable=True),
sa.Column('under_18', sa.Boolean(), nullable=False),
sa.Column('under_13', sa.Boolean(), nullable=False),
sa.Column('print_queued', sa.Boolean(), nullable=False),
sa.Column('print_queued_by_id', sa.BigInteger(), nullable=True),
sa.ForeignKeyConstraint(['print_queued_by_id'], ['user.id'],
onupdate='CASCADE',
ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('foreign_id'))
op.create_index(op.f('ix_badge_created_at'),
'badge', ['created_at'],
unique=False)
op.create_index(op.f('ix_badge_print_queued'),
'badge', ['print_queued'],
unique=False)
op.create_index(op.f('ix_badge_updated_at'),
'badge', ['updated_at'],
unique=False)
op.create_table(
'badge_template_to_flag',
sa.Column('badge_template_id', sa.BigInteger(), nullable=False),
sa.Column('flag_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['badge_template_id'], ['badge_template.id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['flag_id'], ['flag.id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('badge_template_id', 'flag_id'))
op.create_table(
'badge_print',
sa.Column('created_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('updated_at',
sqlalchemy_utils.types.arrow.ArrowType(),
nullable=True),
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('badge_id', sa.BigInteger(), nullable=False),
sa.Column('queued_by_id', sa.BigInteger(), nullable=True),
sa.Column('printed_by_id', sa.BigInteger(), nullable=True),
sa.ForeignKeyConstraint(['badge_id'], ['badge.id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['printed_by_id'], ['user.id'],
onupdate='CASCADE',
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['queued_by_id'], ['user.id'],
onupdate='CASCADE',
ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_badge_print_created_at'),
'badge_print', ['created_at'],
unique=False)
op.create_index(op.f('ix_badge_print_updated_at'),
'badge_print', ['updated_at'],
unique=False)
op.create_table(
'badge_to_flag', sa.Column('badge_id', sa.BigInteger(),
nullable=False),
sa.Column('flag_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['badge_id'], ['badge.id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['flag_id'], ['flag.id'],
onupdate='CASCADE',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('badge_id', 'flag_id'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'roles', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name',
sa.String(length=50),
server_default='',
nullable=False),
sa.Column('label',
sa.Unicode(length=255),
server_default='',
nullable=True), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'))
op.create_table(
'users', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.Unicode(length=30), nullable=False),
sa.Column('email', sa.Unicode(length=255), nullable=False),
sa.Column('confirmed_at', sa.DateTime(), nullable=True),
sa.Column('password',
sa.VARBINARY(length=1137),
server_default='',
nullable=False),
sa.Column('reset_password_token',
sa.String(length=100),
nullable=False),
sa.Column('is_active',
sa.Boolean(),
server_default='0',
nullable=False),
sa.Column('first_name',
sa.Unicode(length=50),
server_default='',
nullable=False),
sa.Column('last_name',
sa.Unicode(length=50),
server_default='',
nullable=False),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('last_updated', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'),
sa.UniqueConstraint('username'))
op.create_table(
'networks', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=255), nullable=False),
sa.Column('nickname', sa.String(length=20), nullable=False),
sa.Column('hostname', sa.String(length=100), nullable=False),
sa.Column('port', sa.Integer(), nullable=False),
sa.Column('realname', sa.String(length=100), nullable=False),
sa.Column('username', sa.String(length=50), nullable=False),
sa.Column('password', sa.String(length=100), nullable=False),
sa.Column('usermode', sa.String(length=1), nullable=False),
sa.Column('access_token', sa.String(length=100), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('current_nickname', sa.String(length=20), nullable=True),
sa.Column('status', sa.String(length=255), nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('last_updated', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('access_token'),
sa.UniqueConstraint('user_id', 'name'))
op.create_table(
'users_roles', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['role_id'], ['roles.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'channels', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=50), nullable=False),
sa.Column('password', sa.String(length=100), nullable=False),
sa.Column('network_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('status', sa.String(length=255), nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('last_updated', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['network_id'], ['networks.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('network_id', 'name'))
op.create_table(
'clients', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('socket', sa.String(length=100), nullable=False),
sa.Column('network_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('last_updated', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['network_id'], ['networks.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('socket', 'network_id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('graph',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('source', sa.Enum('tags', 'responses'), nullable=True),
sa.Column('startTime', sa.DateTime(), nullable=True),
sa.Column('finishTime', sa.DateTime(), nullable=True),
sa.Column('minUserOnNode', sa.Integer(), nullable=True),
sa.Column('minUserOnEdge', sa.Integer(), nullable=True),
sa.Column('isDirected', sa.Boolean(), nullable=True),
sa.Column('minEdgeWeight', sa.Float(), nullable=True),
sa.Column('name', sa.Unicode(length=50), nullable=True),
sa.Column('moreInfo', sa.Unicode(length=150), nullable=True),
sa.Column('creationTime', sa.DateTime(), nullable=True),
sa.Column('isActive', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('phrase',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('content', sa.Unicode(length=200), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('content')
)
op.create_table('questionnaire',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('subject', sa.Unicode(length=100), nullable=True),
sa.Column('picture', sa.VARBINARY(), nullable=True),
sa.Column('moreInfo', sa.Unicode(length=1000), nullable=True),
sa.Column('isActive', sa.Boolean(), nullable=True),
sa.Column('isPictorial', sa.Boolean(), nullable=True),
sa.Column('creationTime', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('subject')
)
op.create_table('user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.Unicode(length=100), nullable=True),
sa.Column('password', sa.Unicode(length=60), nullable=True),
sa.Column('role', sa.Enum('admin', 'user'), nullable=True),
sa.Column('firstname', sa.Unicode(length=100), nullable=True),
sa.Column('lastname', sa.Unicode(length=100), nullable=True),
sa.Column('email', sa.Unicode(length=200), nullable=True),
sa.Column('moreinfo', sa.Unicode(length=1000), nullable=True),
sa.Column('bdate', sa.Date(), nullable=True),
sa.Column('gender', sa.Enum('زن', 'مرد'), nullable=True),
sa.Column('degree', sa.Enum('کارشناسی ارشد و دکتری', 'کارشناسی', 'فوق دیپلم', 'دیپلم', 'زیر دیپلم'),
nullable=True),
sa.Column('nativeLanguage', sa.Enum('فارسی', 'سایر زبان\u200cها'), nullable=True),
sa.Column('major', sa.Unicode(length=100), nullable=True),
sa.Column('registerationTime', sa.DateTime(), nullable=True),
sa.Column('isSeeGraphPage', sa.Boolean(), nullable=True),
sa.Column('isSeePackPage', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('username')
)
op.create_table('_edge_in_graph',
sa.Column('weight', sa.Float(), nullable=True),
sa.Column('phrase1_id', sa.Integer(), nullable=False),
sa.Column('phrase2_id', sa.Integer(), nullable=False),
sa.Column('graph_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['graph_id'], ['graph.id'], ),
sa.ForeignKeyConstraint(['phrase1_id'], ['phrase.id'], ),
sa.ForeignKeyConstraint(['phrase2_id'], ['phrase.id'], ),
sa.PrimaryKeyConstraint('phrase1_id', 'phrase2_id', 'graph_id')
)
op.create_table('_node_in_graph',
sa.Column('weight', sa.Float(), nullable=True),
sa.Column('phrase_id', sa.Integer(), nullable=False),
sa.Column('graph_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['graph_id'], ['graph.id'], ),
sa.ForeignKeyConstraint(['phrase_id'], ['phrase.id'], ),
sa.PrimaryKeyConstraint('phrase_id', 'graph_id')
)
op.create_table('_phrase_controller',
sa.Column('phrase_id', sa.Integer(), nullable=False),
sa.Column('credit', sa.Integer(), nullable=True),
sa.Column('type', sa.Enum('white', 'black'), nullable=True),
sa.ForeignKeyConstraint(['phrase_id'], ['phrase.id'], ),
sa.PrimaryKeyConstraint('phrase_id')
)
op.create_table('_phrase_in_questionnaire',
sa.Column('questionnaire_id', sa.Integer(), nullable=False),
sa.Column('phrase_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['phrase_id'], ['phrase.id'], ),
sa.ForeignKeyConstraint(['questionnaire_id'], ['questionnaire.id'], ),
sa.PrimaryKeyConstraint('questionnaire_id', 'phrase_id')
)
op.create_table('_picture_for_phrase',
sa.Column('phrase_id', sa.Integer(), nullable=False),
sa.Column('questionnaire_id', sa.Integer(), nullable=False),
sa.Column('picture', sa.VARBINARY(), nullable=False),
sa.ForeignKeyConstraint(['phrase_id'], ['phrase.id'], ),
sa.ForeignKeyConstraint(['questionnaire_id'], ['questionnaire.id'], ),
sa.PrimaryKeyConstraint('phrase_id', 'questionnaire_id')
)
op.create_table('_searched_phrase',
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('phrase_id', sa.Integer(), nullable=False),
sa.Column('time', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['phrase_id'], ['phrase.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('user_id', 'phrase_id')
)
op.create_table('pack',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('startTime', sa.DateTime(), nullable=False),
sa.Column('finishTime', sa.DateTime(), nullable=True),
sa.Column('isPictorial', sa.Boolean(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('questionnaire_id', sa.Integer(), nullable=False),
sa.Column('isChecked', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(['questionnaire_id'], ['questionnaire.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('post',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('code', sa.Unicode(length=50), nullable=True),
sa.Column('caption', sa.Unicode(length=2500), nullable=False),
sa.Column('publishTime', sa.DateTime(), nullable=True),
sa.Column('storeTime', sa.DateTime(), nullable=True),
sa.Column('uid', sa.Integer(), nullable=True),
sa.Column('phrase_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['phrase_id'], ['phrase.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('code')
)
op.create_table('_response_in_pack',
sa.Column('duration', sa.Float(), nullable=True),
sa.Column('number', sa.Integer(), nullable=False),
sa.Column('phrase1_id', sa.Integer(), nullable=False),
sa.Column('phrase2_id', sa.Integer(), nullable=True),
sa.Column('pack_id', sa.Integer(), nullable=False),
sa.Column('creationTime', sa.DateTime(), nullable=True),
sa.Column('status', sa.Enum('accepted', 'rejected'), nullable=True),
sa.ForeignKeyConstraint(['pack_id'], ['pack.id'], ),
sa.ForeignKeyConstraint(['phrase1_id'], ['phrase.id'], ),
sa.ForeignKeyConstraint(['phrase2_id'], ['phrase.id'], ),
sa.PrimaryKeyConstraint('number', 'phrase1_id', 'pack_id')
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'hosts',
sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
sa.Column('hostname', sa.String(length=45), nullable=False),
sa.Column('created', sa.TIMESTAMP(), server_default=sa.func.now(),
nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('hostname'),
sa.UniqueConstraint('id')
)
op.create_table(
'files',
sa.Column('id', sa.BIGINT(), autoincrement=True, nullable=False),
sa.Column('path', sa.String(length=1023), nullable=False),
sa.Column('filename', sa.String(length=255), nullable=False),
sa.Column('owner', sa.String(length=48), nullable=True),
sa.Column('grp', sa.String(length=48), nullable=True),
sa.Column('uid', sa.INTEGER(), nullable=False),
sa.Column('gid', sa.INTEGER(), nullable=False),
sa.Column('mode', sa.INTEGER(), nullable=False),
sa.Column('size', sa.BIGINT(), nullable=False),
sa.Column('ctime', sa.TIMESTAMP(), nullable=True),
sa.Column('mtime', sa.TIMESTAMP(), nullable=True),
sa.Column('type', sa.Enum(u'c', u'd', u'f', u'l', u's'),
nullable=False),
sa.Column('links', sa.INTEGER(), server_default=sa.text(u'1'),
nullable=False),
sa.Column('sparseness', sa.Float(), server_default=sa.text(u'1'),
nullable=False),
sa.Column('shasum', sa.VARBINARY(length=64), nullable=True),
sa.Column('first_backup', sa.TIMESTAMP(), server_default=sa.func.now(),
nullable=False),
sa.Column('last_backup', sa.TIMESTAMP(), nullable=True),
sa.Column('host_id', sa.INTEGER(), nullable=False),
sa.ForeignKeyConstraint(['host_id'], [u'hosts.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('id')
)
op.create_index('index3', 'files', [
'filename', 'path', 'host_id', 'mode', 'size', 'mtime', 'uid', 'gid'],
unique=True)
op.create_index(op.f('ix_files_host_id'), 'files', ['host_id'],
unique=False)
op.create_table(
'savesets',
sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
sa.Column('saveset', sa.String(length=45), nullable=False),
sa.Column('location', sa.String(length=32), nullable=True),
sa.Column('created', sa.TIMESTAMP(), server_default=sa.func.now(),
nullable=False),
sa.Column('finished', sa.TIMESTAMP(), nullable=True),
sa.Column('host_id', sa.INTEGER(), nullable=False),
sa.Column('backup_host_id', sa.INTEGER(), nullable=False),
sa.ForeignKeyConstraint(['backup_host_id'], [u'hosts.id'], ),
sa.ForeignKeyConstraint(['host_id'], [u'hosts.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('id'),
sa.UniqueConstraint('saveset')
)
op.create_index(op.f('ix_savesets_backup_host_id'), 'savesets',
['backup_host_id'], unique=False)
op.create_index(op.f('ix_savesets_finished'), 'savesets', ['finished'],
unique=False)
op.create_index(op.f('ix_savesets_host_id'), 'savesets', ['host_id'],
unique=False)
op.create_table(
'volumes',
sa.Column('id', sa.INTEGER(), autoincrement=True, nullable=False),
sa.Column('volume', sa.String(length=45), nullable=False),
sa.Column('path', sa.String(length=255), nullable=False),
sa.Column('size', sa.BIGINT(), nullable=True),
sa.Column('created', sa.TIMESTAMP(), server_default=sa.func.now(),
nullable=False),
sa.Column('removable', sa.BOOLEAN(), server_default=sa.text(u'0'),
nullable=False),
sa.Column('mounted', sa.BOOLEAN(), server_default=sa.text(u'1'),
nullable=False),
sa.Column('host_id', sa.INTEGER(), server_default=sa.text(u'0'),
nullable=False),
sa.ForeignKeyConstraint(['host_id'], [u'hosts.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('id'),
sa.UniqueConstraint('volume')
)
op.create_index(op.f('ix_volumes_host_id'), 'volumes', ['host_id'],
unique=False)
op.create_table(
'backups',
sa.Column('id', sa.BIGINT(), autoincrement=True, nullable=False),
sa.Column('saveset_id', sa.INTEGER(), nullable=False),
sa.Column('volume_id', sa.INTEGER(), nullable=False),
sa.Column('file_id', sa.BIGINT(), nullable=False),
sa.ForeignKeyConstraint(['file_id'], [u'files.id'], ),
sa.ForeignKeyConstraint(['saveset_id'], [u'savesets.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['volume_id'], [u'volumes.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('id')
)
op.create_index(op.f('ix_backups_file_id'), 'backups', ['file_id'],
unique=False)
op.create_index(op.f('ix_backups_saveset_id'), 'backups', ['saveset_id'],
unique=False)
op.create_index(op.f('ix_backups_volume_id'), 'backups', ['volume_id'],
unique=False)
class NuageSfcVlanSubnetMapping(model_base.BASEV2):
__tablename__ = 'nuage_sfc_vlan_subnet_mapping'
subnet_id = sa.Column(sa.String(36),
sa.ForeignKey('subnets.id', ondelete='CASCADE'),
primary_key=True)
vlan_bit_map = sa.Column('vlan_bit_map', sa.VARBINARY(512), nullable=False)
"DATETIME",
dtrepr,
),
(sqlalchemy.DATE, datetime.date(2021, 2, 4), "DATE", dtrepr),
(sqlalchemy.TIME, datetime.time(4, 5, 7, 123456), "TIME", dtrepr),
(sqlalchemy.TIME, datetime.time(4, 5, 7), "TIME", dtrepr),
(sqlalchemy.TEXT, "myTEXT", "STRING", repr),
(sqlalchemy.VARCHAR, "myVARCHAR", "STRING", repr),
(sqlalchemy.NVARCHAR, "myNVARCHAR", "STRING", repr),
(sqlalchemy.VARCHAR(42), "myVARCHAR", "STRING(42)", repr),
(sqlalchemy.NVARCHAR(42), "myNVARCHAR", "STRING(42)", repr),
(sqlalchemy.CHAR, "myCHAR", "STRING", repr),
(sqlalchemy.NCHAR, "myNCHAR", "STRING", repr),
(sqlalchemy.BINARY, b"myBINARY", "BYTES", repr),
(sqlalchemy.VARBINARY, b"myVARBINARY", "BYTES", repr),
(sqlalchemy.VARBINARY(42), b"myVARBINARY", "BYTES(42)", repr),
(sqlalchemy.BOOLEAN, False, "BOOL", "false"),
(sqlalchemy.ARRAY(sqlalchemy.Integer), [1, 2, 3], "ARRAY<INT64>", repr),
(
sqlalchemy.ARRAY(sqlalchemy.DATETIME),
[
datetime.datetime(2021, 2, 3, 4, 5, 6),
datetime.datetime(2021, 2, 3, 4, 5, 7, 123456),
datetime.datetime(2021, 2, 3, 4, 5, 8, 123456),
],
"ARRAY<DATETIME>",
lambda a: "[" + ", ".join(dtrepr(v) for v in a) + "]",
),
],
)
def test_typed_parameters(faux_conn, type_, val, btype, vrep):
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('ua_session_base',
sa.Column('ua_sb_key',
sa.String(length=256),
nullable=False),
sa.Column('ua_sb_ip', sa.String(length=128),
nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('ua_sb_exceed', sa.SmallInteger(),
nullable=True),
sa.Column('ua_sb_lastheart',
sa.BigInteger(),
nullable=True),
sa.Column('ua_sb_creattime',
sa.BigInteger(),
nullable=True),
sa.PrimaryKeyConstraint('ua_sb_key'),
mysql_ENGINE='MEMORY')
op.create_table('ua_users',
sa.Column('ua_user_id', sa.Integer(), nullable=False),
sa.Column('ua_user_email',
sa.String(length=128),
nullable=True),
sa.Column('ua_user_nick',
sa.String(length=64),
nullable=True),
sa.Column('ua_pwd_hash',
sa.String(length=128),
nullable=True),
sa.Column('ua_email_confirmed',
sa.SmallInteger(),
nullable=True),
sa.Column('ua_creattime', sa.DateTime(), nullable=True),
sa.Column('avatar_hash',
sa.String(length=32),
nullable=True),
sa.PrimaryKeyConstraint('ua_user_id'),
mysql_ENGINE='MyISAM')
op.create_index(op.f('ix_ua_users_ua_user_email'),
'ua_users', ['ua_user_email'],
unique=True)
op.create_index(op.f('ix_ua_users_ua_user_nick'),
'ua_users', ['ua_user_nick'],
unique=True)
op.create_table('res_posts',
sa.Column('post_id', sa.Integer(), nullable=False),
sa.Column('author_id', sa.Integer(), nullable=True),
sa.Column('post_name', sa.String(length=64),
nullable=True),
sa.Column('post_body', sa.Text(), nullable=True),
sa.Column('post_body_html', sa.Text(), nullable=True),
sa.Column('post_updatetime', sa.DateTime(), nullable=True),
sa.Column('post_creattime', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['author_id'],
['ua_users.ua_user_id'],
),
sa.PrimaryKeyConstraint('post_id'),
mysql_ENGINE='MyISAM')
op.create_table('ua_session_data',
sa.Column('ua_sb_key',
sa.String(length=128),
nullable=False),
sa.Column('ua_sd_key',
sa.String(length=64),
nullable=False),
sa.Column('ua_sd_value',
sa.VARBINARY(length=512),
nullable=True),
sa.Column('ua_sd_type', sa.String(length=8),
nullable=True),
sa.ForeignKeyConstraint(
['ua_sb_key'],
['ua_session_base.ua_sb_key'],
),
sa.PrimaryKeyConstraint('ua_sb_key', 'ua_sd_key'),
mysql_ENGINE='MEMORY')
op.create_table('res_files',
sa.Column('file_id', sa.Integer(), nullable=False),
sa.Column('post_id', sa.Integer(), nullable=True),
sa.Column('file_name',
sa.String(length=128),
nullable=True),
sa.Column('file_type', sa.String(length=64),
nullable=True),
sa.Column('file_data', sa.LONGBLOB(), nullable=True),
sa.Column('file_creattime', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['post_id'],
['res_posts.post_id'],
),
sa.PrimaryKeyConstraint('file_id'),
mysql_ENGINE='MyISAM')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'maddr',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('partition_tag',
sa.Integer(),
server_default='0',
nullable=True),
sa.Column('email', sa.VARBINARY(length=191), nullable=False),
sa.Column('domain', sa.String(length=191), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_maddr_created_at'),
'maddr', ['created_at'],
unique=False)
op.create_index(op.f('ix_maddr_domain'), 'maddr', ['domain'], unique=False)
op.create_index(op.f('ix_maddr_email'), 'maddr', ['email'], unique=False)
op.create_index(op.f('ix_maddr_partition_tag'),
'maddr', ['partition_tag'],
unique=False)
op.create_index(op.f('ix_maddr_updated_at'),
'maddr', ['updated_at'],
unique=False)
op.create_table(
'mailaddr',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('priority', sa.Integer(), server_default='7',
nullable=False),
sa.Column('email', sa.VARBINARY(length=191), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_mailaddr_created_at'),
'mailaddr', ['created_at'],
unique=False)
op.create_index(op.f('ix_mailaddr_email'),
'mailaddr', ['email'],
unique=False)
op.create_index(op.f('ix_mailaddr_updated_at'),
'mailaddr', ['updated_at'],
unique=False)
op.create_table(
'msgrcpt',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('partition_tag',
sa.Integer(),
server_default='0',
nullable=False),
sa.Column('mail_id', sa.VARBINARY(length=16), nullable=False),
sa.Column('rseqnum', sa.Integer(), server_default='0', nullable=False),
sa.Column('rid', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('is_local',
sa.CHAR(length=1),
server_default='',
nullable=False),
sa.Column('content',
sa.CHAR(length=1),
server_default='',
nullable=False),
sa.Column('ds', sa.CHAR(length=1), nullable=False),
sa.Column('rs', sa.CHAR(length=1), nullable=False),
sa.Column('wl', sa.CHAR(length=1), server_default='', nullable=True),
sa.Column('bl', sa.CHAR(length=1), server_default='', nullable=True),
sa.Column('bspam_level', sa.FLOAT(), nullable=True),
sa.Column('smtp_resp',
sa.String(length=191),
server_default='',
nullable=True),
sa.PrimaryKeyConstraint('partition_tag', 'mail_id', 'rseqnum', 'rid'))
op.create_index(op.f('ix_msgrcpt_created_at'),
'msgrcpt', ['created_at'],
unique=False)
op.create_index(op.f('ix_msgrcpt_mail_id'),
'msgrcpt', ['mail_id'],
unique=False)
op.create_index(op.f('ix_msgrcpt_updated_at'),
'msgrcpt', ['updated_at'],
unique=False)
op.create_table(
'msgs',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('partition_tag',
sa.Integer(),
server_default='0',
nullable=False),
sa.Column('mail_id', sa.VARBINARY(length=16), nullable=False),
sa.Column('secret_id',
sa.VARBINARY(length=16),
server_default='',
nullable=True),
sa.Column('am_id', sa.String(length=20), nullable=False),
sa.Column('time_num', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('time_iso', sa.CHAR(length=16), nullable=False),
sa.Column('sid', mysql.BIGINT(unsigned=True), nullable=False),
sa.Column('policy',
sa.String(length=191),
server_default='',
nullable=True),
sa.Column('client_addr',
sa.String(length=191),
server_default='',
nullable=True),
sa.Column('size', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('originating',
sa.CHAR(length=1),
server_default='',
nullable=False),
sa.Column('content', sa.CHAR(length=1), nullable=True),
sa.Column('quar_type', sa.CHAR(length=1), nullable=True),
sa.Column('quar_loc',
sa.VARBINARY(length=191),
server_default='',
nullable=True),
sa.Column('dsn_sent',
sa.CHAR(length=1),
server_default='',
nullable=True),
sa.Column('spam_level', sa.FLOAT(), nullable=True),
sa.Column('message_id',
sa.String(length=191),
server_default='',
nullable=True),
sa.Column('from_addr',
sa.String(length=191),
server_default='',
nullable=True),
sa.Column('subject',
sa.String(length=191),
server_default='',
nullable=True),
sa.Column('host', sa.String(length=191), nullable=False),
sa.PrimaryKeyConstraint('partition_tag', 'mail_id'))
op.create_index(op.f('ix_msgs_created_at'),
'msgs', ['created_at'],
unique=False)
op.create_index(op.f('ix_msgs_mail_id'), 'msgs', ['mail_id'], unique=False)
op.create_index(op.f('ix_msgs_updated_at'),
'msgs', ['updated_at'],
unique=False)
op.create_table(
'outbound_wblist',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('rid', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('sid', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('wb', sa.String(length=10), nullable=False),
sa.PrimaryKeyConstraint('rid', 'sid'))
op.create_index(op.f('ix_outbound_wblist_created_at'),
'outbound_wblist', ['created_at'],
unique=False)
op.create_index(op.f('ix_outbound_wblist_updated_at'),
'outbound_wblist', ['updated_at'],
unique=False)
op.create_table(
'quarantine',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('partition_tag',
sa.Integer(),
server_default='0',
nullable=False),
sa.Column('chunk_ind', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('mail_id', sa.VARBINARY(length=16), nullable=False),
sa.Column('mail_text', sa.BLOB(), nullable=False),
sa.PrimaryKeyConstraint('partition_tag', 'chunk_ind', 'mail_id'))
op.create_index(op.f('ix_quarantine_created_at'),
'quarantine', ['created_at'],
unique=False)
op.create_index(op.f('ix_quarantine_updated_at'),
'quarantine', ['updated_at'],
unique=False)
op.create_table(
'wblist',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('rid', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('sid', mysql.INTEGER(unsigned=True), nullable=False),
sa.Column('wb', sa.String(length=10), nullable=False),
sa.PrimaryKeyConstraint('rid', 'sid'))
op.create_index(op.f('ix_wblist_created_at'),
'wblist', ['created_at'],
unique=False)
op.create_index(op.f('ix_wblist_updated_at'),
'wblist', ['updated_at'],
unique=False)
class Tables(object):
metadata = sqlalchemy.MetaData()
# API tables
sqlalchemy.Table(
"api_keys",
metadata,
sqlalchemy.Column("user_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("users.user_id")),
sqlalchemy.Column("key",
sqlalchemy.String(length=36),
primary_key=True),
sqlalchemy.Column("league_id", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("league_info.league_id")),
sqlalchemy.Column("access_level", sqlalchemy.Integer),
sqlalchemy.Column("active", sqlalchemy.Boolean),
)
sqlalchemy.Table(
"api_paths",
metadata,
sqlalchemy.Column("path_id",
sqlalchemy.Integer,
primary_key=True,
autoincrement=True),
sqlalchemy.Column("path", sqlalchemy.String(length=64)),
)
sqlalchemy.Table(
"api_permissions",
metadata,
sqlalchemy.Column("league_id", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("league_info.league_id")),
sqlalchemy.Column("path_id", sqlalchemy.Integer,
sqlalchemy.ForeignKey("api_paths.path_id")),
sqlalchemy.Column("access_level", sqlalchemy.Integer),
)
sqlalchemy.Table(
"elo_settings",
metadata,
sqlalchemy.Column("elo_id",
sqlalchemy.Integer,
primary_key=True,
autoincrement=True),
sqlalchemy.Column("kill", sqlalchemy.Float),
sqlalchemy.Column("death", sqlalchemy.Float),
sqlalchemy.Column("round_won", sqlalchemy.Float),
sqlalchemy.Column("round_lost", sqlalchemy.Float),
sqlalchemy.Column("match_won", sqlalchemy.Float),
sqlalchemy.Column("match_lost", sqlalchemy.Float),
sqlalchemy.Column("assist", sqlalchemy.Float),
sqlalchemy.Column("mate_blined", sqlalchemy.Float),
sqlalchemy.Column("mate_killed", sqlalchemy.Float),
)
# Basic league info
# knife_round
# 0 - Disabled
# 1 - Enabled
# pause
# If no time given in seconds its disabled.
# warmup_commands_only
# Commands can only be used during warmup.
# captain_choice_time
# Max amount of time for a captain choice.
# surrender
# If teams are allowed to surrender.
sqlalchemy.Table(
"league_info",
metadata,
sqlalchemy.Column("league_id",
sqlalchemy.String(length=4),
primary_key=True),
sqlalchemy.Column("league_name", sqlalchemy.String(length=32)),
sqlalchemy.Column("league_website", sqlalchemy.String(length=255)),
sqlalchemy.Column("websocket_endpoint", sqlalchemy.String(length=255)),
sqlalchemy.Column("queue_limit", sqlalchemy.Integer),
sqlalchemy.Column("discord_prefix", sqlalchemy.String(length=3)),
sqlalchemy.Column("sm_message_prefix", sqlalchemy.String(length=24)),
sqlalchemy.Column("knife_round", sqlalchemy.Integer),
sqlalchemy.Column("pause", sqlalchemy.Integer),
sqlalchemy.Column("surrender", sqlalchemy.Integer),
sqlalchemy.Column("warmup_commands_only", sqlalchemy.Integer),
sqlalchemy.Column("captain_choice_time", sqlalchemy.Integer),
sqlalchemy.Column("elo_id", sqlalchemy.Integer,
sqlalchemy.ForeignKey("elo_settings.elo_id")),
)
# League Discords
sqlalchemy.Table(
"league_discords",
metadata,
sqlalchemy.Column("guild_id", sqlalchemy.BigInteger, primary_key=True),
sqlalchemy.Column("league_id", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("league_info.league_id")),
)
# Queue Channels
# queue_type
# 0 - bot interface
# 1 - website interface
sqlalchemy.Table(
"league_queues",
metadata,
sqlalchemy.Column("channel_id",
sqlalchemy.BigInteger,
primary_key=True),
sqlalchemy.Column("guild_id", sqlalchemy.BigInteger,
sqlalchemy.ForeignKey("league_discords.guild_id")),
sqlalchemy.Column("queue_size", sqlalchemy.Integer),
sqlalchemy.Column("queue_type", sqlalchemy.Integer),
)
# Admins & Owners
# Access Levels
# 0 = Owner
# 1 = Admin
# 2 = Moderator
# 3 = Anti cheat reviewer
# 4 = Demo reviewer
# 5 = Sepecator
sqlalchemy.Table(
"league_admins",
metadata,
sqlalchemy.Column("league_id", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("league_info.league_id")),
sqlalchemy.Column("user_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("users.user_id")),
sqlalchemy.Column("access_level", sqlalchemy.Integer),
)
# User account details
# Region here is the users most recent region.
sqlalchemy.Table(
"users",
metadata,
sqlalchemy.Column("user_id",
sqlalchemy.String(length=36),
primary_key=True),
sqlalchemy.Column("steam_id",
sqlalchemy.String(length=64),
primary_key=True,
nullable=True),
sqlalchemy.Column("discord_id",
sqlalchemy.BigInteger,
primary_key=True,
nullable=True),
sqlalchemy.Column("region", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("region.region")),
sqlalchemy.Column("name", sqlalchemy.String(length=36)),
sqlalchemy.Column("pfp", sqlalchemy.String(length=128)),
sqlalchemy.Column(
"joined",
sqlalchemy.types.TIMESTAMP,
server_default=sqlalchemy.text("CURRENT_TIMESTAMP()")),
sqlalchemy.Column("ip_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("ip_details.ip_id")),
)
# Region table
sqlalchemy.Table(
"region",
metadata,
sqlalchemy.Column("region",
sqlalchemy.String(length=4),
primary_key=True),
)
# IP details / Proxy caching
sqlalchemy.Table(
"ip_details",
metadata,
sqlalchemy.Column("ip_id",
sqlalchemy.String(length=36),
primary_key=True),
sqlalchemy.Column("ip", sqlalchemy.VARBINARY(length=16)),
sqlalchemy.Column("region", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("region.region")),
sqlalchemy.Column("proxy", sqlalchemy.Boolean),
sqlalchemy.Column("provider", sqlalchemy.String(length=124)),
sqlalchemy.Column("city", sqlalchemy.String(length=64)),
sqlalchemy.Column("country", sqlalchemy.String(length=64)),
)
# Selection Order
sqlalchemy.Table(
"selection_order",
metadata,
sqlalchemy.Column("id",
sqlalchemy.Integer,
primary_key=True,
autoincrement=True),
sqlalchemy.Column("type", sqlalchemy.String(length=8)),
)
# Scoreboard
# Status codes
# 0 - Finished
# 1 - Live
# 2 - Map selection
# 3 - Player selection
sqlalchemy.Table(
"scoreboard_total",
metadata,
sqlalchemy.Column("match_id",
sqlalchemy.String(length=36),
primary_key=True),
sqlalchemy.Column("server_id", sqlalchemy.String(length=36)),
sqlalchemy.Column("map_order",
sqlalchemy.Integer,
sqlalchemy.ForeignKey("selection_order.id"),
nullable=True),
sqlalchemy.Column("player_order",
sqlalchemy.Integer,
sqlalchemy.ForeignKey("selection_order.id"),
nullable=True),
sqlalchemy.Column(
"timestamp",
sqlalchemy.types.TIMESTAMP,
server_default=sqlalchemy.text("CURRENT_TIMESTAMP()")),
sqlalchemy.Column("status", sqlalchemy.Integer),
sqlalchemy.Column("map",
sqlalchemy.String(length=24),
server_default="NULL"),
sqlalchemy.Column("region", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("region.region")),
sqlalchemy.Column("league_id", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("league_info.league_id")),
sqlalchemy.Column("team_1_name", sqlalchemy.String(length=64)),
sqlalchemy.Column("team_2_name", sqlalchemy.String(length=64)),
sqlalchemy.Column("team_1_score",
sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("team_2_score",
sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("team_1_side",
sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("team_2_side",
sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("record_statistics",
sqlalchemy.Boolean,
server_default="1"),
)
# Team Codes
# 0 Teamless
# 1 = CT
# 2 = T
sqlalchemy.Table(
"scoreboard",
metadata,
sqlalchemy.Column("match_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("scoreboard_total.match_id")),
sqlalchemy.Column("user_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("users.user_id")),
sqlalchemy.Column("captain", sqlalchemy.Integer),
sqlalchemy.Column("team", sqlalchemy.Integer),
sqlalchemy.Column("alive", sqlalchemy.Integer, server_default="1"),
sqlalchemy.Column("ping", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("kills", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("headshots", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("assists", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("deaths", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("shots_fired",
sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("shots_hit", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("mvps", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("score", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("disconnected",
sqlalchemy.Boolean,
server_default="0"),
)
# Match Map Pool
# Data is delete from here once match has ended.
sqlalchemy.Table(
"map_pool",
metadata,
sqlalchemy.Column("match_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("scoreboard_total.match_id")),
sqlalchemy.Column("map", sqlalchemy.String(length=24)),
)
# Statistics
# user_id isn't unique here,
# different regions have different stats.
sqlalchemy.Table(
"statistics",
metadata,
sqlalchemy.Column("user_id", sqlalchemy.String(length=36),
sqlalchemy.ForeignKey("users.user_id")),
sqlalchemy.Column("league_id", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("league_info.league_id")),
sqlalchemy.Column("region", sqlalchemy.String(length=4),
sqlalchemy.ForeignKey("region.region")),
sqlalchemy.Column("last_connected",
sqlalchemy.types.TIMESTAMP,
server_default=sqlalchemy.text("""CURRENT_TIMESTAMP()
ON UPDATE CURRENT_TIMESTAMP()""")
),
sqlalchemy.Column("total_time", sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("elo", sqlalchemy.Float, server_default="0"),
sqlalchemy.Column("kills", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("deaths", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("assists", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("shots", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("hits", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("damage", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("headshots", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("roundswon", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("roundslost", sqlalchemy.Integer,
server_default="0"),
sqlalchemy.Column("wins", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("ties", sqlalchemy.Integer, server_default="0"),
sqlalchemy.Column("losses", sqlalchemy.Integer, server_default="0"),
)
def __init__(self, obj):
""" Ensures all the tables have been built correctly. """
engine = sqlalchemy.create_engine(
str(obj.database_url.replace(driver="pymysql")) +
"?charset=utf8mb4")
self.metadata.create_all(engine)
