def upgrade():
op.add_column(
'item',
sa.Column('transferable_until',
sa.TIMESTAMP(timezone=True),
nullable=True))
class User(Base):
__tablename__ = "user"
@declared_attr
def __table_args__(cls): # noqa: N805 pylint:disable=no-self-argument
return (
# (email, authority) must be unique
sa.UniqueConstraint("email", "authority"),
# (normalised username, authority) must be unique. This index is
# also critical for making user lookups fast.
sa.Index(
"ix__user__userid",
_normalise_username(cls.username),
cls.authority,
unique=True,
),
# Optimize lookup of shadowbanned users.
sa.Index("ix__user__nipsa",
cls.nipsa,
postgresql_where=cls.nipsa.is_(True)),
)
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
#: Username as chosen by the user on registration
_username = sa.Column("username", sa.UnicodeText(), nullable=False)
#: The "authority" for this user. This represents the "namespace" in which
#: this user lives. By default, all users are created in the namespace
#: corresponding to `request.domain`, but this can be overridden with the
#: `h.authority` setting.
authority = sa.Column("authority", sa.UnicodeText(), nullable=False)
#: The display name which will be used when rendering an annotation.
display_name = sa.Column(sa.UnicodeText())
#: A short user description/bio
description = sa.Column(sa.UnicodeText())
#: A free-form column to allow the user to say where they are
location = sa.Column(sa.UnicodeText())
#: The user's URI/link on the web
uri = sa.Column(sa.UnicodeText())
#: The user's ORCID ID
orcid = sa.Column(sa.UnicodeText())
#: Is this user an admin member?
admin = sa.Column(
sa.Boolean,
default=False,
nullable=False,
server_default=sa.sql.expression.false(),
)
#: Is this user a staff member?
staff = sa.Column(
sa.Boolean,
nullable=False,
default=False,
server_default=sa.sql.expression.false(),
)
#: Is this user flagged as "Not (Suitable) In Public Site Areas" (AKA
#: NIPSA). This flag is used to shadow-ban a user so their annotations
#: don't appear to anyone but themselves.
nipsa = sa.Column(
sa.Boolean,
nullable=False,
default=False,
server_default=sa.sql.expression.false(),
)
sidebar_tutorial_dismissed = sa.Column(
sa.Boolean, default=False, server_default=(sa.sql.expression.false()))
#: A timestamp representing the last time the user accepted the privacy policy.
#: A NULL value in this column indicates the user has never accepted a privacy policy.
privacy_accepted = sa.Column(sa.DateTime, nullable=True)
# Has the user opted-in for news etc.
comms_opt_in = sa.Column(sa.Boolean, nullable=True)
identities = sa.orm.relationship("UserIdentity",
backref="user",
cascade="all, delete-orphan")
@hybrid_property
def username(self):
return self._username
@username.setter
def username(self, value):
self._username = value
@username.comparator
def username(cls): # noqa: N805 pylint:disable=no-self-argument
return UsernameComparator(cls._username)
@hybrid_property
def userid(self):
return "acct:{username}@{authority}".format(username=self.username,
authority=self.authority)
@userid.comparator
def userid(cls): # noqa: N805 pylint: disable=no-self-argument
return UserIDComparator(cls.username, cls.authority)
email = sa.Column(sa.UnicodeText())
last_login_date = sa.Column(sa.TIMESTAMP(timezone=False), nullable=True)
registered_date = sa.Column(
sa.TIMESTAMP(timezone=False),
default=datetime.datetime.utcnow,
server_default=sa.func.now(),
nullable=False,
)
activation_date = sa.Column(sa.TIMESTAMP(timezone=False), nullable=True)
# Activation foreign key
activation_id = sa.Column(sa.Integer, sa.ForeignKey("activation.id"))
activation = sa.orm.relationship("Activation", backref="user")
@property
def is_activated(self):
if self.activation_id is None:
return True
return False
def activate(self):
"""Activate the user by deleting any activation they have."""
session = sa.orm.object_session(self)
self.activation_date = datetime.datetime.utcnow()
session.delete(self.activation)
#: Hashed password
password = sa.Column(sa.UnicodeText(), nullable=True)
#: Last password update
password_updated = sa.Column(sa.DateTime(), nullable=True)
#: Password salt
#:
#: N.B. This field is DEPRECATED. The password context we use already
#: manages the generation of a random salt when hashing a password and we
#: don't need a separate salt column. This remains for "legacy" passwords
#: which were, sadly, double-salted. As users log in, we are slowly
#: upgrading their passwords and setting this column to None.
salt = sa.Column(sa.UnicodeText(), nullable=True)
@sa.orm.validates("email")
def validate_email(self, _key, email):
if email is None:
return email
if len(email) > EMAIL_MAX_LENGTH:
raise ValueError("email must be less than {max} characters "
"long".format(max=EMAIL_MAX_LENGTH))
return email
@sa.orm.validates("_username")
def validate_username(self, _key, username):
if not USERNAME_MIN_LENGTH <= len(username) <= USERNAME_MAX_LENGTH:
raise ValueError("username must be between {min} and {max} "
"characters long".format(min=USERNAME_MIN_LENGTH,
max=USERNAME_MAX_LENGTH))
if not re.match(USERNAME_PATTERN, username):
raise ValueError("username must have only letters, numbers, "
"periods, and underscores.")
return username
@classmethod
def get_by_email(cls, session, email, authority):
"""Fetch a user by email address."""
if email is None:
return None
return (session.query(cls).filter(
sa.func.lower(cls.email) == email.lower(),
cls.authority == authority).first())
@classmethod
def get_by_activation(cls, session, activation):
"""Fetch a user by activation instance."""
user = session.query(cls).filter(
cls.activation_id == activation.id).first()
return user
@classmethod
def get_by_username(cls, session, username, authority):
"""Fetch a user by username."""
return (session.query(cls).filter(cls.username == username,
cls.authority == authority).first())
def __acl__(self):
terms = []
# auth_clients that have the same authority as the user
# may update the user
user_update_principal = "client_authority:{}".format(self.authority)
terms.append((security.Allow, user_update_principal, "update"))
terms.append(security.DENY_ALL)
return terms
def __repr__(self):
return "<User: %s>" % self.username
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('an-weekly-module',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('categories', sa.String(length=150), nullable=False),
sa.Column('components', sa.String(length=150), nullable=False),
sa.Column('module_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('an-weekly-steps',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('step_id', sa.String(length=150), nullable=False),
sa.Column('description', sa.String(length=550), nullable=False),
sa.Column('skip', sa.Boolean(), nullable=False),
sa.Column('bugs', sa.PickleType(), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('an-weekly-tests',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('module_id', sa.String(length=150), nullable=False),
sa.Column('skip', sa.Boolean(), nullable=False),
sa.Column('bugs', sa.PickleType(), nullable=False),
sa.Column('steps', sa.PickleType(), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('bugs',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('status', sa.String(length=50), nullable=False),
sa.Column('description', sa.String(length=250), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('minor-module',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('categories', sa.String(length=150), nullable=False),
sa.Column('components', sa.String(length=150), nullable=False),
sa.Column('module_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('minor-steps',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('step_id', sa.String(length=150), nullable=False),
sa.Column('description', sa.String(length=550), nullable=False),
sa.Column('skip', sa.Boolean(), nullable=False),
sa.Column('bugs', sa.PickleType(), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('minor-tests',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('module_id', sa.String(length=150), nullable=False),
sa.Column('skip', sa.Boolean(), nullable=False),
sa.Column('bugs', sa.PickleType(), nullable=False),
sa.Column('steps', sa.PickleType(), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('module',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('categories', sa.String(length=150), nullable=False),
sa.Column('components', sa.String(length=150), nullable=False),
sa.Column('module_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('steps',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('step_id', sa.String(length=150), nullable=False),
sa.Column('description', sa.String(length=550), nullable=False),
sa.Column('skip', sa.Boolean(), nullable=False),
sa.Column('bugs', sa.PickleType(), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('tests',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.String(length=150), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('module_id', sa.String(length=150), nullable=False),
sa.Column('skip', sa.Boolean(), nullable=False),
sa.Column('bugs', sa.PickleType(), nullable=False),
sa.Column('steps', sa.PickleType(), nullable=False),
sa.Column('creation_date', sa.TIMESTAMP(), server_default=sa.text(u'CURRENT_TIMESTAMP'), nullable=False),
sa.PrimaryKeyConstraint('id')
)
def _(itype, **kwargs):
return sa.TIMESTAMP(bool(itype.timezone))
def upgrade():
# (1) Remove indices
op.drop_index("feed_updates_last_successful_idx", table_name="feed_update")
op.drop_index("ix_feed_update_last_action_time", table_name="feed_update")
# (2) Remove existing columns
op.drop_column("feed_update", "failure_message")
op.drop_column("feed_update", "execution_duration")
op.drop_column("feed_update", "feed_time")
op.drop_column("feed_update", "explanation")
op.drop_column("feed_update", "raw_data_hash")
# (3) Rename last_action_time to completed_at
op.alter_column(
"feed_update",
"last_action_time",
new_column_name="completed_at",
server_default=None,
)
# (4) Add new columns
op.add_column(
"feed_update",
sa.Column("content_created_at",
sa.TIMESTAMP(timezone=True),
nullable=True),
)
op.add_column("feed_update",
sa.Column("content_hash", sa.String(), nullable=True))
op.add_column("feed_update",
sa.Column("download_duration", sa.Float(), nullable=True))
op.add_column(
"feed_update",
sa.Column(
"result",
sa.Enum(
"UPDATED",
"NOT_NEEDED",
"PARSE_ERROR",
"DOWNLOAD_ERROR",
"INVALID_PARSER",
"EMPTY_FEED",
"SYNC_ERROR",
"UNEXPECTED_ERROR",
name="result",
native_enum=False,
create_constraint=True,
),
nullable=True,
),
)
op.add_column(
"feed_update",
sa.Column("num_parsed_entities", sa.Integer(), nullable=True))
op.add_column("feed_update",
sa.Column("num_added_entities", sa.Integer(), nullable=True))
op.add_column(
"feed_update",
sa.Column("num_updated_entities", sa.Integer(), nullable=True))
op.add_column(
"feed_update",
sa.Column("num_deleted_entities", sa.Integer(), nullable=True))
op.add_column("feed_update",
sa.Column("result_message", sa.String(), nullable=True))
op.add_column(
"feed_update",
sa.Column("scheduled_at", sa.TIMESTAMP(timezone=True), nullable=True),
)
op.add_column("feed_update",
sa.Column("total_duration", sa.Float(), nullable=True))
# (5) Add multi-column indices
op.create_index(
"feed_update_success_pk_completed_at_idx",
"feed_update",
["feed_pk", "completed_at"],
unique=False,
postgresql_where=sa.text("status = 'SUCCESS'"),
)
op.create_index(
"feed_update_feed_pk_feed_update_pk_idx",
"feed_update",
["feed_pk", "pk"],
unique=False,
)
database = databases.Database(
os.environ["DATABASE_URL"], force_rollback="TESTING" in os.environ
)
metadata = sqlalchemy.MetaData()
twigs = sqlalchemy.Table(
"twigs",
metadata,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column(
"project", sqlalchemy.String(length=64), nullable=True, index=True
),
sqlalchemy.Column(
"added_on",
sqlalchemy.TIMESTAMP(timezone=True),
nullable=False,
index=True,
),
sqlalchemy.Column("data", JSONB, nullable=False),
sqlalchemy.Index("idx_data", "data", postgresql_using="gin"),
)
async def find_twigs(
filters: List[sqlalchemy.sql.elements.ColumnElement] = tuple(),
):
query = twigs.select()
for exp in filters:
query = query.where(exp)
query = query.order_by(twigs.c.added_on.desc())
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('user',
'active',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.drop_column('scrap_material', 'nextCheckDate')
op.drop_column('scrap_material', 'lastCheckDate')
op.drop_column('scrap_material', 'effectiveDate')
op.drop_column('repair_material', 'nextCheckDate')
op.drop_column('repair_material', 'lastCheckDate')
op.drop_column('repair_material', 'effectiveDate')
op.drop_column('put_out_store_material', 'effectiveDate')
op.drop_column('loan_material', 'effectiveDate')
op.alter_column('file_resource_version',
'addTime',
existing_type=sa.TIMESTAMP(timezone=True),
type_=mysql.TIMESTAMP(),
existing_nullable=True,
autoincrement=False)
op.alter_column('file_resource',
'addTime',
existing_type=sa.TIMESTAMP(timezone=True),
server_default=sa.text(
u'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
type_=mysql.TIMESTAMP(),
existing_nullable=False)
op.alter_column('examine_repair_record_version',
'Soluted',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True,
autoincrement=False)
op.alter_column('examine_repair_record',
'Soluted',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.drop_column('disassemble_material', 'effectiveDate')
op.drop_column('borrowing_in_return_material', 'nextCheckDate')
op.drop_column('borrowing_in_return_material', 'effectiveDate')
op.alter_column('basic_action',
'view',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'upload_meeting_file',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'upload_contract_file',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'submit_review',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'submit',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'stored',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'sent',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'send',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'second_approved',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'second_approve_refuse',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'review_refuse',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'review_approve',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'review_again',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'review',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'reserve_again',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'receive',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'put_in_store',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'purchase_application',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'out_store_part',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'out_store_finish',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'in_store_part',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'in_store_finish',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'finish',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'edit_bound_status',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'edit',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'delete',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_st',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_scrap',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_rw',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_rp_rt',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_rf',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_out',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_mr',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_lr',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_in',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_er',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_eo',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_br',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create_as',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'create',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'check_complete',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'cancel',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'borrowing_in_return',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'borrow_application',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'approved',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'approve_refuse',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('basic_action',
'approve',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.drop_column('assemble_application_list', 'effectiveDate')
op.alter_column('airmaterial_category_version',
'isOrNotHavePeriodCheck',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True,
autoincrement=False)
op.alter_column('airmaterial_category_version',
'isOrNotHaveEffectiveDate',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True,
autoincrement=False)
op.alter_column('airmaterial_category',
'isOrNotHavePeriodCheck',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
op.alter_column('airmaterial_category',
'isOrNotHaveEffectiveDate',
existing_type=sa.Boolean(),
type_=mysql.TINYINT(display_width=1),
existing_nullable=True)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('users', sa.Column('updatetimestamp', sa.TIMESTAMP(timezone=True), nullable=False))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('github_user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('login', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('product',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=255), nullable=False),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('stability_status',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('job_id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.Text(), nullable=False),
sa.Column('status',
sa.Enum('PASS',
'FAIL',
'OK',
'TIMEOUT',
'ERROR',
'NOTRUN',
'CRASH',
name='teststatus'),
nullable=False),
sa.Column('count', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('test',
sa.Column('id', sa.Text(), nullable=False),
sa.Column('parent_id', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['parent_id'],
['test.id'],
),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('commit',
sa.Column('sha', sa.String(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['github_user.id'],
),
sa.PrimaryKeyConstraint('sha'),
extend_existing=True)
op.create_table('repository',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('owner_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['owner_id'],
['github_user.id'],
),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('pull_request',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('number', sa.Integer(), nullable=False),
sa.Column('title', sa.String(), nullable=False),
sa.Column('state',
sa.Enum('OPEN', 'CLOSED', name='prstatus'),
nullable=False),
sa.Column('head_sha', sa.String(), nullable=False),
sa.Column('base_sha', sa.String(), nullable=False),
sa.Column('head_repo_id', sa.Integer(), nullable=False),
sa.Column('base_repo_id', sa.Integer(), nullable=False),
sa.Column('head_branch', sa.String(), nullable=False),
sa.Column('base_branch', sa.String(), nullable=False),
sa.Column('created_by', sa.Integer(), nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=False),
sa.Column('merged', sa.Boolean(), nullable=False),
sa.Column('merged_by', sa.Integer(), nullable=True),
sa.Column('merged_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=False),
sa.Column('closed_at', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['base_repo_id'],
['repository.id'],
),
sa.ForeignKeyConstraint(
['base_sha'],
['commit.sha'],
),
sa.ForeignKeyConstraint(
['created_by'],
['github_user.id'],
),
sa.ForeignKeyConstraint(
['head_repo_id'],
['repository.id'],
),
sa.ForeignKeyConstraint(
['head_sha'],
['commit.sha'],
),
sa.ForeignKeyConstraint(
['merged_by'],
['github_user.id'],
),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('build',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('number', sa.Integer(), nullable=False),
sa.Column('pull_request_id', sa.Integer(), nullable=True),
sa.Column('head_sha', sa.String(), nullable=True),
sa.Column('base_sha', sa.String(), nullable=True),
sa.Column('status',
sa.Enum('PENDING',
'PASSED',
'FIXED',
'BROKEN',
'FAILED',
'STILL_FAILING',
'CANCELLED',
'ERRORED',
name='buildstatus'),
nullable=False),
sa.Column('started_at', sa.TIMESTAMP(), nullable=True),
sa.Column('finished_at', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['base_sha'],
['commit.sha'],
),
sa.ForeignKeyConstraint(
['head_sha'],
['commit.sha'],
),
sa.ForeignKeyConstraint(
['pull_request_id'],
['pull_request.id'],
),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('test_mirror',
sa.Column('pull_id',
sa.Integer(),
autoincrement=False,
nullable=False),
sa.Column('url', sa.String(), nullable=True),
sa.ForeignKeyConstraint(
['pull_id'],
['pull_request.id'],
),
sa.PrimaryKeyConstraint('pull_id'),
extend_existing=True)
op.create_table('user_pr',
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('pull_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['pull_id'],
['pull_request.id'],
),
sa.ForeignKeyConstraint(
['user_id'],
['github_user.id'],
),
extend_existing=True)
op.create_table('job',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('number', sa.Float(), nullable=True),
sa.Column('build_id', sa.Integer(), nullable=False),
sa.Column('product_id', sa.Integer(), nullable=False),
sa.Column('state',
sa.Enum('CREATED',
'QUEUED',
'STARTED',
'PASSED',
'FAILED',
'ERRORED',
'FINISHED',
name='jobstatus'),
nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('allow_failure', sa.Boolean(), nullable=False),
sa.Column('started_at', sa.TIMESTAMP(), nullable=True),
sa.Column('finished_at', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['build_id'],
['build.id'],
),
sa.ForeignKeyConstraint(
['product_id'],
['product.id'],
),
sa.PrimaryKeyConstraint('id'),
extend_existing=True)
op.create_table('job_result',
sa.Column('job_id', sa.Integer(), nullable=False),
sa.Column('test_id', sa.Text(), nullable=False),
sa.Column('iterations', sa.Integer(), nullable=False),
sa.Column('messages', sa.Text(), nullable=True),
sa.Column('consistent', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(
['job_id'],
['job.id'],
),
sa.ForeignKeyConstraint(
['test_id'],
['test.id'],
),
sa.PrimaryKeyConstraint('job_id', 'test_id'),
extend_existing=True)
import warnings
from typing import Optional, Iterator, Iterable
from abc import ABC, abstractmethod
from confluent_kafka import KafkaException, Consumer # type: ignore
import sqlalchemy as sa # type: ignore
from sqlalchemy.sql import func, select, bindparam, and_ # type: ignore
# SQLAlchemy doesn't properly understand when you use columns with a "key"
# property with PostgreSQL's on_conflict_do_update statement, so it prints a
# pointless warning that we can just ignore.
warnings.filterwarnings("ignore",
message="Additional column names not matching.*")
UTC = timezone.utc
TIMESTAMP_TZ = lambda: sa.TIMESTAMP(timezone=True)
# pylint: disable=invalid-name
meta = sa.MetaData()
TABLE: str = os.environ["TABLE"]
if TABLE not in ["flights", "positions"]:
raise ValueError(
f"Invalid TABLE env variable: {TABLE} - must be 'flights' or 'positions'"
)
if TABLE == "flights":
table = sa.Table(
"flights",
meta,
sa.Column("id", sa.String, primary_key=True),
sa.Column("added",
def sa_timestamp(): # noqa: D103
return sa.TIMESTAMP(timezone=True)
def upgrade():
if op.get_bind().dialect.name == 'mysql':
# ### tResourceAttrt
try:
op.alter_column('tResourceAttr',
'resource_attr_id',
new_column_name='id',
existing_type=sa.Integer(),
primary_key=True,
autoincrement=True,
nullable=False)
except Exception as e:
log.exception(e)
else: ## sqlite
# ## tResourceAttr
try:
op.drop_table('tResourceAttr_new')
except:
log.info("tResourceAttr_new isn't there")
try:
# ## tResourceAttr
op.create_table(
'tResourceAttr_new',
sa.Column('id', sa.Integer(), primary_key=True,
nullable=False),
sa.Column('attr_id',
sa.Integer(),
sa.ForeignKey('tAttr.id'),
nullable=False),
sa.Column('ref_key', sa.String(60), nullable=False,
index=True),
sa.Column(
'network_id',
sa.Integer(),
sa.ForeignKey('tNetwork.id'),
index=True,
nullable=True,
),
sa.Column(
'project_id',
sa.Integer(),
sa.ForeignKey('tProject.id'),
index=True,
nullable=True,
),
sa.Column('node_id',
sa.Integer(),
sa.ForeignKey('tNode.id'),
index=True,
nullable=True),
sa.Column('link_id',
sa.Integer(),
sa.ForeignKey('tLink.id'),
index=True,
nullable=True),
sa.Column('group_id',
sa.Integer(),
sa.ForeignKey('tResourceGroup.id'),
index=True,
nullable=True),
sa.Column('attr_is_var',
sa.String(1),
nullable=False,
server_default=sa.text(u"'N'")),
sa.Column('cr_date',
sa.TIMESTAMP(),
nullable=False,
server_default=sa.text(u'CURRENT_TIMESTAMP')),
sa.UniqueConstraint('network_id', 'attr_id',
name='net_attr_1'),
sa.UniqueConstraint('project_id',
'attr_id',
name='proj_attr_1'),
sa.UniqueConstraint('node_id', 'attr_id', name='node_attr_1'),
sa.UniqueConstraint('link_id', 'attr_id', name='link_attr_1'),
sa.UniqueConstraint('group_id', 'attr_id',
name='group_attr_1'),
)
op.execute(
"insert into tResourceAttr_new (id, attr_id, ref_key, network_id, project_id, node_id, link_id, group_id, attr_is_var, cr_date) select resource_attr_id, attr_id, ref_key, network_id, project_id, node_id, link_id, group_id, attr_is_var, cr_date from tResourceAttr"
)
op.rename_table('tResourceAttr', 'tResourceAttr_old')
op.rename_table('tResourceAttr_new', 'tResourceAttr')
op.drop_table('tResourceAttr_old')
except Exception as e:
log.exception(e)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('country', sa.Column('created_at', sa.TIMESTAMP(), nullable=False))
op.add_column('country', sa.Column('updated_at', sa.TIMESTAMP(), nullable=True))
class Project(Base):
"""
Models an upstream project and maps it to a database table.
Attributes:
id (sa.Integer): The database primary key.
name (sa.String): The upstream project's name.
homepage (sa.String): The URL for the project's home page.
backend (sa.String): The name of the backend to use when fetching updates;
this is a foreign key to a :class:`Backend`.
ecosystem_name (sa.String): The name of the ecosystem this project is a part
of. If the project isn't part of an ecosystem (e.g. PyPI), use the homepage
URL.
version_url (sa.String): The url to use when polling for new versions. This
may be ignored if this project is part of an ecosystem with a fixed
URL (e.g. Cargo projects are on https://crates.io).
regex (sa.String): A Python ``re`` style regular expression that is applied
to the HTML from ``version_url`` to find versions.
insecure (sa.Boolean): Whether or not to validate the x509 certificate
offered by the server at ``version_url``. Defaults to ``False``.
latest_version (sa.Boolean): The latest version for the project, as determined
by the version sorting algorithm.
logs (sa.Text): The result of the last update.
check_successful (sa.Boolean): Flag that contains result of last check.
``None`` - not checked yet, ``True`` - checked successfully, ``False``
- error occured during check
updated_on (sa.DateTime): When the project was last updated.
created_on (sa.DateTime): When the project was created in Anitya.
packages (list): List of :class:`Package` objects which represent the
downstream packages for this project.
version_scheme (sa.String): The version scheme to use for this project.
If this is null, a default will be used. See the :mod:`anitya.lib.versions`
documentation for more information.
"""
__tablename__ = 'projects'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(200), nullable=False, index=True)
homepage = sa.Column(sa.String(200), nullable=False)
backend = sa.Column(sa.String(200), default='custom')
ecosystem_name = sa.Column(sa.String(200), nullable=False, index=True)
version_url = sa.Column(sa.String(200), nullable=True)
regex = sa.Column(sa.String(200), nullable=True)
version_prefix = sa.Column(sa.String(200), nullable=True)
insecure = sa.Column(sa.Boolean, nullable=False, default=False)
version_scheme = sa.Column(sa.String(50), nullable=True)
latest_version = sa.Column(sa.String(50))
logs = sa.Column(sa.Text)
check_successful = sa.Column(sa.Boolean, default=None, index=True)
last_check = sa.Column(sa.TIMESTAMP(timezone=True),
default=lambda: arrow.utcnow().datetime,
index=True)
next_check = sa.Column(sa.TIMESTAMP(timezone=True),
default=lambda: arrow.utcnow().datetime,
index=True)
updated_on = sa.Column(sa.DateTime,
server_default=sa.func.now(),
onupdate=sa.func.current_timestamp())
created_on = sa.Column(sa.DateTime, default=datetime.datetime.utcnow)
packages = sa.orm.relationship('Packages', cascade='all, delete-orphan')
__table_args__ = (
sa.UniqueConstraint('name', 'homepage'),
sa.UniqueConstraint('name',
'ecosystem_name',
name="UNIQ_PROJECT_NAME_PER_ECOSYSTEM"),
)
@validates('backend')
def validate_backend(self, key, value):
if value not in BACKEND_PLUGINS.get_plugin_names():
raise ValueError('Backend "{}" is not supported.'.format(value))
return value
@property
def versions(self):
''' Return list of all versions stored, sorted from newest to oldest.
Returns:
:obj:`list` of :obj:`str`: List of versions
'''
sorted_versions = self.get_sorted_version_objects()
return [str(v) for v in sorted_versions]
def create_version_objects(self, versions):
"""
Creates sorted list of version objects defined by `self.version_class` from versions list.
Args:
versions (list(str)): List of versions that are not associated with the project.
Returns:
list(`anitya.lib.versions.Base`): List of version objects defined by
`self.version_class`.
"""
version_class = self.get_version_class()
versions = sorted([
version_class(version=version,
prefix=self.version_prefix,
created_on=datetime.datetime.utcnow())
for version in versions
])
return versions
def get_version_url(self):
''' Returns full version url, which is used by backend.
Returns:
str: Version url or empty string if backend is not specified
'''
if not self.backend:
return ""
backend = BACKEND_PLUGINS.get_plugin(self.backend)
return backend.get_version_url(self)
def get_sorted_version_objects(self):
''' Return list of all version objects stored, sorted from newest to oldest.
Returns:
:obj:`list` of :obj:`anitya.db.models.ProjectVersion`: List of version objects
'''
version_class = self.get_version_class()
versions = [
version_class(version=v_obj.version,
prefix=self.version_prefix,
created_on=v_obj.created_on)
for v_obj in self.versions_obj
]
sorted_versions = list(reversed(sorted(versions)))
return sorted_versions
def get_version_class(self):
"""
Get the class for the version scheme used by this project.
This will take into account the defaults set in the ecosystem, backend,
and globally. The version scheme locations are checked in the following
order and the first non-null result is returned:
1. On the project itself in the ``version_scheme`` column.
2. The project's ecosystem default, if the project is part of one.
3. The project's backend default, if the backend defines one.
4. The global default defined in :data:`anitya.lib.versions.GLOBAL_DEFAULT`
Returns:
anitya.lib.versions.Version: A ``Version`` sub-class.
"""
version_scheme = self.version_scheme
if not version_scheme and self.ecosystem_name:
ecosystem = ECOSYSTEM_PLUGINS.get_plugin(self.ecosystem_name)
if ecosystem is None:
# This project uses its URL as an ecosystem
version_scheme = DEFAULT_VERSION_SCHEME
else:
version_scheme = ecosystem.default_version_scheme
if not version_scheme and self.backend:
backend = BACKEND_PLUGINS.get_plugin(self.backend)
version_scheme = backend.default_version_scheme
if not version_scheme:
version_scheme = DEFAULT_VERSION_SCHEME
return VERSION_PLUGINS.get_plugin(version_scheme)
def __repr__(self):
return '<Project(%s, %s)>' % (self.name, self.homepage)
def __json__(self, detailed=False):
output = dict(
id=self.id,
name=self.name,
homepage=self.homepage,
regex=self.regex,
backend=self.backend,
version_url=self.version_url,
version=self.latest_version,
versions=self.versions,
created_on=time.mktime(self.created_on.timetuple())
if self.created_on else None,
updated_on=time.mktime(self.updated_on.timetuple())
if self.updated_on else None,
ecosystem=self.ecosystem_name,
)
if detailed:
output['packages'] = [pkg.__json__() for pkg in self.packages]
return output
@classmethod
def get_or_create(cls, session, name, homepage, backend='custom'):
project = cls.by_name_and_homepage(session, name, homepage)
if not project:
project = cls(name=name, homepage=homepage, backend=backend)
session.add(project)
session.flush()
return project
@classmethod
def by_name(cls, session, name):
return session.query(cls).filter_by(name=name).all()
@classmethod
def by_id(cls, session, project_id):
return session.query(cls).filter_by(id=project_id).first()
get = by_id
@classmethod
def by_homepage(cls, session, homepage):
return session.query(cls).filter_by(homepage=homepage).all()
@classmethod
def by_name_and_homepage(cls, session, name, homepage):
query = session.query(cls).filter(cls.name == name).filter(
cls.homepage == homepage)
return query.first()
@classmethod
def by_name_and_ecosystem(cls, session, name, ecosystem):
try:
query = session.query(cls)
query = query.filter(cls.name == name,
cls.ecosystem_name == ecosystem)
return query.one()
except NoResultFound:
return None
@classmethod
def all(cls, session, page=None, count=False):
query = session.query(Project).order_by(sa.func.lower(Project.name))
query = _paginate_query(query, page)
if count:
return query.count()
else:
return query.all()
@classmethod
def by_distro(cls, session, distro, page=None, count=False):
query = session.query(Project).filter(
Project.id == Packages.project_id).filter(
sa.func.lower(Packages.distro) == sa.func.lower(
distro)).order_by(sa.func.lower(Project.name))
query = _paginate_query(query, page)
if count:
return query.count()
else:
return query.all()
@classmethod
def updated(cls,
session,
status='updated',
name=None,
log=None,
page=None,
count=False):
''' Method used to retrieve projects according to their logs and
how they performed at the last cron job.
:kwarg status: used to filter the projects based on how they
performed at the last cron run
:kwarg name: if present, will return the entries having the matching
name
:kwarg log: if present, will return the entries having the matching
log
:kwarg page: The page number of returned, pages contain 50 entries
:kwarg count: A boolean used to return either the list of entries
matching the criterias or just the COUNT of entries
'''
query = session.query(Project).order_by(sa.func.lower(Project.name))
if status == 'updated':
query = query.filter(
Project.logs.isnot(None),
Project.logs == 'Version retrieved correctly',
)
elif status == 'failed':
query = query.filter(
Project.logs.isnot(None),
Project.logs != 'Version retrieved correctly',
~Project.logs.ilike('Something strange occured%'),
)
elif status == 'odd':
query = query.filter(
Project.logs.isnot(None),
Project.logs != 'Version retrieved correctly',
Project.logs.ilike('Something strange occured%'),
)
elif status == 'new':
query = query.filter(Project.logs.is_(None), )
elif status == 'never_updated':
query = query.filter(
Project.logs.isnot(None),
Project.logs != 'Version retrieved correctly',
Project.latest_version.is_(None),
)
if name:
if '*' in name:
name = name.replace('*', '%')
else:
name = '%' + name + '%'
query = query.filter(Project.name.ilike(name), )
if log:
if '*' in log:
log = log.replace('*', '%')
else:
log = '%' + log + '%'
query = query.filter(Project.logs.ilike(log), )
query = _paginate_query(query, page)
if count:
return query.count()
else:
return query.all()
@classmethod
def search(cls, session, pattern, distro=None, page=None, count=False):
''' Search the projects by their name or package name '''
query1 = session.query(cls)
if pattern:
pattern = pattern.replace('_', r'\_')
if '*' in pattern:
pattern = pattern.replace('*', '%')
if '%' in pattern:
query1 = query1.filter(Project.name.ilike(pattern))
else:
query1 = query1.filter(Project.name == pattern)
query2 = session.query(cls).filter(Project.id == Packages.project_id)
if pattern:
if '%' in pattern:
query2 = query2.filter(Packages.package_name.ilike(pattern))
else:
query2 = query2.filter(Packages.package_name == pattern)
if distro is not None:
query1 = query1.filter(Project.id == Packages.project_id).filter(
sa.func.lower(Packages.distro) == sa.func.lower(distro))
query = query1.distinct().union(query2.distinct()).order_by(cls.name)
query = _paginate_query(query, page)
if count:
return query.count()
else:
return query.all()
class User(Base, BaseModel):
__tablename__ = 'users'
__mapper_args__ = {'extension': UserMapperExtension()}
id = sa.Column(sa.Integer(), primary_key=True)
username = sa.Column(sa.Unicode(30), unique=True)
password = sa.Column(sa.String(40))
email = sa.Column(sa.Unicode(100), nullable=False, unique=True)
status = sa.Column(sa.SmallInteger(), nullable=False)
firstname = sa.Column(sa.Unicode(25))
lastname = sa.Column(sa.Unicode(25))
company_name = sa.Column(sa.Unicode(255), default=u'')
last_login_date = sa.Column(sa.TIMESTAMP(timezone=True),
default=sa.sql.func.now(),
server_default=sa.func.now())
def __repr__(self):
return '<User: %s>' % self.username
groups_dynamic = sa.orm.relationship('Group',
secondary='users_groups',
lazy='dynamic',
passive_deletes=True,
passive_updates=True)
user_permissions = sa.orm.relationship('UserPermission',
cascade="all, delete-orphan",
passive_deletes=True,
passive_updates=True)
@property
def permissions(self):
q = Session.query(GroupPermission.perm_name.label('perm_name'))
q = q.filter(GroupPermission.group_name == UserGroup.group_name)
q = q.filter(User.username == UserGroup.username)
q = q.filter(User.username == self.username)
q2 = Session.query(UserPermission.perm_name.label('perm_name'))
q2 = q2.filter(User.username == self.username)
q = q.union(q2)
return [row.perm_name for row in q]
def gravatar_url(self, default='mm'):
# construct the url
gravatar_url = "http://www.gravatar.com/avatar/" \
+ hashlib.md5(self.email.lower()).hexdigest() + "?"
gravatar_url += urllib.urlencode({'d': default})
return gravatar_url
@classmethod
def pass_crypt(cls, text):
crypt = hashlib.sha1(text)
return crypt.hexdigest()
@classmethod
def by_username(cls,
username,
cache=FromCache("default_term", "by_id"),
invalidate=False):
q = Session.query(cls).filter(cls.username == username)
if cache:
q = q.options(sa.orm.eagerload(User.groups))
q = q.options(cache)
if invalidate:
q.invalidate()
return True
return q.first()
@classmethod
def by_usernames(cls,
usernames,
cache=FromCache("default_term", "by_id"),
invalidate=False):
q = Session.query(cls).filter(cls.username.in_(usernames))
# if cache:
# q = q.options(sa.orm.eagerload(User.groups))
# q = q.options(cache)
# if invalidate:
# q.invalidate()
# return True
return q
@classmethod
def by_email(cls,
email,
cache=FromCache("default_term", "by_id"),
invalidate=False):
q = Session.query(cls).filter(cls.email == email)
if cache:
q = q.options(sa.orm.eagerload(User.groups))
q = q.options(cache)
if invalidate:
q.invalidate()
return True
return q.first()
def upgrade():
""" Initial Taurus table schema generated by `alembic revision --autogenerate`
"""
# Schema: instance_status_history
op.create_table(
'instance_status_history',
sa.Column('server',
sa.VARCHAR(length=100),
server_default='',
nullable=False),
sa.Column('timestamp',
sa.TIMESTAMP(),
server_default='0000-00-00 00:00:00',
nullable=False),
sa.Column('status',
sa.VARCHAR(length=32),
server_default='',
nullable=False),
sa.PrimaryKeyConstraint('server', 'timestamp'))
# Schema: lock
op.create_table('lock',
sa.Column('name', sa.VARCHAR(length=40), nullable=False),
sa.PrimaryKeyConstraint('name'))
op.execute("INSERT INTO `lock` (`name`) VALUES('metrics')")
# Schema: metric
op.create_table(
'metric', sa.Column('uid', sa.VARCHAR(length=40), nullable=False),
sa.Column('datasource', sa.VARCHAR(length=100), nullable=True),
sa.Column('name', sa.VARCHAR(length=255), nullable=True),
sa.Column('description', sa.VARCHAR(length=200), nullable=True),
sa.Column('server', sa.VARCHAR(length=100), nullable=True),
sa.Column('location', sa.VARCHAR(length=200), nullable=True),
sa.Column('parameters', sa.TEXT(), nullable=True),
sa.Column('status',
sa.INTEGER(),
server_default='0',
autoincrement=False,
nullable=True), sa.Column('message',
sa.TEXT(),
nullable=True),
sa.Column('collector_error', sa.TEXT(), nullable=True),
sa.Column('last_timestamp', sa.TIMESTAMP(), nullable=True),
sa.Column('poll_interval',
sa.INTEGER(),
server_default='60',
autoincrement=False,
nullable=True),
sa.Column('tag_name', sa.VARCHAR(length=200), nullable=True),
sa.Column('model_params', sa.TEXT(), nullable=True),
sa.Column('last_rowid',
sa.INTEGER(),
autoincrement=False,
nullable=True), sa.PrimaryKeyConstraint('uid'))
op.create_index('datasource_idx', 'metric', ['datasource'], unique=False)
op.create_index('location_idx', 'metric', ['location'], unique=False)
op.create_index('server_idx', 'metric', ['server'], unique=False)
# Schema: metric_data
op.create_table(
'metric_data',
sa.Column('uid',
sa.VARCHAR(length=40),
server_default='',
nullable=False),
sa.Column('rowid',
sa.INTEGER(),
server_default='0',
autoincrement=False,
nullable=False),
sa.Column('timestamp',
sa.TIMESTAMP(),
server_default=sa.func.current_timestamp(),
nullable=False),
sa.Column('metric_value', mysql.DOUBLE(), nullable=False),
sa.Column('raw_anomaly_score', mysql.DOUBLE(), nullable=True),
sa.Column('anomaly_score', mysql.DOUBLE(), nullable=True),
sa.Column('display_value',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column('multi_step_best_predictions', mysql.DOUBLE(),
nullable=True),
sa.ForeignKeyConstraint(['uid'], [u'metric.uid'],
name='metric_data_to_metric_fk',
onupdate='CASCADE',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('uid', 'rowid'))
op.create_index('anomaly_score_idx',
'metric_data', ['anomaly_score'],
unique=False)
op.create_index('timestamp_idx',
'metric_data', ['timestamp'],
unique=False)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('airmaterial_category',
'isOrNotHaveEffectiveDate',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('airmaterial_category',
'isOrNotHavePeriodCheck',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('airmaterial_category_version',
'isOrNotHaveEffectiveDate',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True,
autoincrement=False)
op.alter_column('airmaterial_category_version',
'isOrNotHavePeriodCheck',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True,
autoincrement=False)
op.alter_column('basic_action',
'airmaterial_record',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'approve',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'approve_refuse',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'approved',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'borrow_application',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'borrowing_in_return',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'cancel',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'check_complete',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_as',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_br',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_eo',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_er',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_in',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_lr',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_mr',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_out',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_rf',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_rp_rt',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_rw',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_scrap',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'create_st',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'delete',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'edit',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'edit_bound_status',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'export_pdf',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'finish',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'in_store_finish',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'in_store_part',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'out_store_finish',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'out_store_part',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'purchase_application',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'put_in_store',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'receive',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'remove_bound_status',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'reserve_again',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'review',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'review_again',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'review_approve',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'review_refuse',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'second_approve_refuse',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'second_approved',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'send',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'sent',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'stored',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'submit',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'submit_review',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'upload_contract_file',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'upload_meeting_file',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('basic_action',
'view',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('examine_repair_record',
'Soluted',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('examine_repair_record_version',
'Soluted',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True,
autoincrement=False)
op.alter_column('file_resource',
'addTime',
existing_type=mysql.TIMESTAMP(),
server_default=None,
type_=sa.TIMESTAMP(timezone=True),
existing_nullable=False)
op.alter_column('file_resource_version',
'addTime',
existing_type=mysql.TIMESTAMP(),
type_=sa.TIMESTAMP(timezone=True),
existing_nullable=True,
autoincrement=False)
op.add_column(
'flight_log',
sa.Column('EngineTimeExtra', sa.String(length=255), nullable=True))
op.add_column(
'flight_log_version',
sa.Column('EngineTimeExtra',
sa.String(length=255),
autoincrement=False,
nullable=True))
op.alter_column('repair_material',
'totalUseTime',
existing_type=mysql.FLOAT(),
type_=sa.String(length=255),
existing_nullable=True)
op.alter_column('user',
'active',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('user', sa.Column('up_time', sa.TIMESTAMP(), nullable=True))
op.drop_column('user', 'upda_time')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'accounts',
sa.Column('activated_at', sa.TIMESTAMP(timezone=True), nullable=True))
def sa_timestamp():
return sa.TIMESTAMP(timezone=True)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('affects',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('vendor', sa.String(length=255), nullable=True),
sa.Column('product', sa.String(length=255), nullable=True),
sa.Column('version', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_affects_deleted_at'),
'affects', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_affects_nvd_json_id'),
'affects', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_table('cve_details',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('cve_id', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_cve_details_cve_id'),
'cve_details', ['cve_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cve_details_deleted_at'),
'cve_details', ['deleted_at'],
unique=False,
schema='cve')
op.create_table('cvss2',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('nvd_xml_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('jvn_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('vector_string',
sa.String(length=255),
nullable=True),
sa.Column('access_vector',
sa.String(length=255),
nullable=True),
sa.Column('access_complexity',
sa.String(length=255),
nullable=True),
sa.Column('authentication',
sa.String(length=255),
nullable=True),
sa.Column('confidentiality_impact',
sa.String(length=255),
nullable=True),
sa.Column('integrity_impact',
sa.String(length=255),
nullable=True),
sa.Column('availability_impact',
sa.String(length=255),
nullable=True),
sa.Column('base_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('severity', sa.String(length=255),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_cvss2_deleted_at'),
'cvss2', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cvss2_jvn_id'),
'cvss2', ['jvn_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cvss2_nvd_xml_id'),
'cvss2', ['nvd_xml_id'],
unique=False,
schema='cve')
op.create_table('cvss2_extras',
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('nvd_xml_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('jvn_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('vector_string',
sa.String(length=255),
nullable=True),
sa.Column('access_vector',
sa.String(length=255),
nullable=True),
sa.Column('access_complexity',
sa.String(length=255),
nullable=True),
sa.Column('authentication',
sa.String(length=255),
nullable=True),
sa.Column('confidentiality_impact',
sa.String(length=255),
nullable=True),
sa.Column('integrity_impact',
sa.String(length=255),
nullable=True),
sa.Column('availability_impact',
sa.String(length=255),
nullable=True),
sa.Column('base_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('severity', sa.String(length=255),
nullable=True),
sa.Column('exploitability_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('impact_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('obtain_all_privilege',
mysql.TINYINT(display_width=1),
nullable=True),
sa.Column('obtain_user_privilege',
mysql.TINYINT(display_width=1),
nullable=True),
sa.Column('obtain_other_privilege',
mysql.TINYINT(display_width=1),
nullable=True),
sa.Column('user_interaction_required',
mysql.TINYINT(display_width=1),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_cvss2_extras_deleted_at'),
'cvss2_extras', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cvss2_extras_nvd_json_id'),
'cvss2_extras', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_table('env_cpes',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('cpe_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('uri', sa.String(length=255), nullable=True),
sa.Column('formatted_string',
sa.String(length=255),
nullable=True),
sa.Column('well_formed_name', sa.Text(), nullable=True),
sa.Column('part', sa.String(length=255), nullable=True),
sa.Column('vendor', sa.String(length=255), nullable=True),
sa.Column('product', sa.String(length=255), nullable=True),
sa.Column('version', sa.String(length=255), nullable=True),
sa.Column('update', sa.String(length=255), nullable=True),
sa.Column('edition', sa.String(length=255), nullable=True),
sa.Column('language', sa.String(length=255),
nullable=True),
sa.Column('software_edition',
sa.String(length=255),
nullable=True),
sa.Column('target_sw',
sa.String(length=255),
nullable=True),
sa.Column('target_hw',
sa.String(length=255),
nullable=True),
sa.Column('other', sa.String(length=255), nullable=True),
sa.Column('version_start_excluding',
sa.String(length=255),
nullable=True),
sa.Column('version_start_including',
sa.String(length=255),
nullable=True),
sa.Column('version_end_excluding',
sa.String(length=255),
nullable=True),
sa.Column('version_end_including',
sa.String(length=255),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_env_cpes_cpe_id'),
'env_cpes', ['cpe_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_env_cpes_deleted_at'),
'env_cpes', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_env_cpes_formatted_string'),
'env_cpes', ['formatted_string'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_env_cpes_uri'),
'env_cpes', ['uri'],
unique=False,
schema='cve')
op.create_table('feed_meta',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('url', sa.String(length=255), nullable=True),
sa.Column('hash', sa.String(length=255), nullable=True),
sa.Column('last_modified_date',
sa.String(length=255),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_feed_meta_deleted_at'),
'feed_meta', ['deleted_at'],
unique=False,
schema='cve')
op.create_table('jvns',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('cve_detail_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('cve_id', sa.String(length=255), nullable=True),
sa.Column('title', sa.String(length=255), nullable=True),
sa.Column('summary', sa.Text(), nullable=True),
sa.Column('jvn_link', sa.String(length=255),
nullable=True),
sa.Column('jvn_id', sa.String(length=255), nullable=True),
sa.Column('published_date', sa.TIMESTAMP(), nullable=True),
sa.Column('last_modified_date',
sa.TIMESTAMP(),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_jvns_cve_detail_id'),
'jvns', ['cve_detail_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_jvns_cve_id'),
'jvns', ['cve_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_jvns_deleted_at'),
'jvns', ['deleted_at'],
unique=False,
schema='cve')
op.create_table('nvd_jsons',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('cve_detail_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('cve_id', sa.String(length=255), nullable=True),
sa.Column('published_date', sa.TIMESTAMP(), nullable=True),
sa.Column('last_modified_date',
sa.TIMESTAMP(),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_nvd_jsons_cve_detail_id'),
'nvd_jsons', ['cve_detail_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_nvd_jsons_cve_id'),
'nvd_jsons', ['cve_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_nvd_jsons_deleted_at'),
'nvd_jsons', ['deleted_at'],
unique=False,
schema='cve')
op.create_table('nvd_xmls',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('cve_detail_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('cve_id', sa.String(length=255), nullable=True),
sa.Column('summary', sa.Text(), nullable=True),
sa.Column('published_date', sa.TIMESTAMP(), nullable=True),
sa.Column('last_modified_date',
sa.TIMESTAMP(),
nullable=True),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_nvd_xmls_cve_detail_id'),
'nvd_xmls', ['cve_detail_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_nvd_xmls_cve_id'),
'nvd_xmls', ['cve_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_nvd_xmls_deleted_at'),
'nvd_xmls', ['deleted_at'],
unique=False,
schema='cve')
op.create_table('cwe_data',
sa.Column('cwe_id', sa.String(length=255), nullable=False),
sa.Column('cwe_name', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('cwe_id'),
schema='cwe')
op.create_index(op.f('ix_cwe_cwe_data_cwe_id'),
'cwe_data', ['cwe_id'],
unique=False,
schema='cwe')
op.create_table('user',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('email', sa.String(length=256), nullable=True),
sa.Column('full_name',
sa.String(length=256),
nullable=True),
sa.Column('profile_picture',
sa.String(length=256),
nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email'),
schema='main')
op.create_table('cpes',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('jvn_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('nvd_xml_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('uri', sa.String(length=255), nullable=True),
sa.Column('formatted_string',
sa.String(length=255),
nullable=True),
sa.Column('well_formed_name', sa.Text(), nullable=True),
sa.Column('part', sa.String(length=255), nullable=True),
sa.Column('vendor', sa.String(length=255), nullable=True),
sa.Column('product', sa.String(length=255), nullable=True),
sa.Column('version', sa.String(length=255), nullable=True),
sa.Column('update', sa.String(length=255), nullable=True),
sa.Column('edition', sa.String(length=255), nullable=True),
sa.Column('language', sa.String(length=255),
nullable=True),
sa.Column('software_edition',
sa.String(length=255),
nullable=True),
sa.Column('target_sw',
sa.String(length=255),
nullable=True),
sa.Column('target_hw',
sa.String(length=255),
nullable=True),
sa.Column('other', sa.String(length=255), nullable=True),
sa.Column('version_start_excluding',
sa.String(length=255),
nullable=True),
sa.Column('version_start_including',
sa.String(length=255),
nullable=True),
sa.Column('version_end_excluding',
sa.String(length=255),
nullable=True),
sa.Column('version_end_including',
sa.String(length=255),
nullable=True),
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.ForeignKeyConstraint(
['nvd_json_id'],
['cve.nvd_jsons.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_cpes_deleted_at'),
'cpes', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_formatted_string'),
'cpes', ['formatted_string'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_jvn_id'),
'cpes', ['jvn_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_nvd_json_id'),
'cpes', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_nvd_xml_id'),
'cpes', ['nvd_xml_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_part'),
'cpes', ['part'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_product'),
'cpes', ['product'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_uri'),
'cpes', ['uri'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cpes_vendor'),
'cpes', ['vendor'],
unique=False,
schema='cve')
op.create_table('cvss3',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('jvn_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('vector_string',
sa.String(length=255),
nullable=True),
sa.Column('attack_vector',
sa.String(length=255),
nullable=True),
sa.Column('attack_complexity',
sa.String(length=255),
nullable=True),
sa.Column('privileges_required',
sa.String(length=255),
nullable=True),
sa.Column('user_interaction',
sa.String(length=255),
nullable=True),
sa.Column('scope', sa.String(length=255), nullable=True),
sa.Column('confidentiality_impact',
sa.String(length=255),
nullable=True),
sa.Column('integrity_impact',
sa.String(length=255),
nullable=True),
sa.Column('availability_impact',
sa.String(length=255),
nullable=True),
sa.Column('base_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('base_severity',
sa.String(length=255),
nullable=True),
sa.Column('exploitability_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('impact_score',
sa.Float(asdecimal=True),
nullable=True),
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.ForeignKeyConstraint(
['nvd_json_id'],
['cve.nvd_jsons.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_cvss3_deleted_at'),
'cvss3', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cvss3_jvn_id'),
'cvss3', ['jvn_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cvss3_nvd_json_id'),
'cvss3', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_table('cwes',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('nvd_xml_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('jvn_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('cwe_id', sa.String(length=255), nullable=True),
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.ForeignKeyConstraint(
['nvd_json_id'],
['cve.nvd_jsons.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_cwes_deleted_at'),
'cwes', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cwes_jvn_id'),
'cwes', ['jvn_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cwes_nvd_json_id'),
'cwes', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_cwes_nvd_xml_id'),
'cwes', ['nvd_xml_id'],
unique=False,
schema='cve')
op.create_table('descriptions',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('lang', sa.String(length=255), nullable=True),
sa.Column('value', sa.Text(), nullable=True),
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.ForeignKeyConstraint(
['nvd_json_id'],
['cve.nvd_jsons.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_descriptions_deleted_at'),
'descriptions', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_descriptions_nvd_json_id'),
'descriptions', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_table('references',
sa.Column('id',
mysql.INTEGER(display_width=10),
nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('updated_at', sa.TIMESTAMP(), nullable=True),
sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
sa.Column('nvd_xml_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('jvn_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.Column('source', sa.String(length=255), nullable=True),
sa.Column('link', sa.Text(), nullable=True),
sa.Column('nvd_json_id',
mysql.INTEGER(display_width=10),
nullable=True),
sa.ForeignKeyConstraint(
['nvd_json_id'],
['cve.nvd_jsons.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='cve')
op.create_index(op.f('ix_cve_references_deleted_at'),
'references', ['deleted_at'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_references_jvn_id'),
'references', ['jvn_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_references_nvd_json_id'),
'references', ['nvd_json_id'],
unique=False,
schema='cve')
op.create_index(op.f('ix_cve_references_nvd_xml_id'),
'references', ['nvd_xml_id'],
unique=False,
schema='cve')
op.create_table('vulnerability',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('comment', sa.Text(), nullable=False),
sa.Column('exploit_exists', sa.Boolean(), nullable=True),
sa.Column('cve_id', sa.String(length=255), nullable=True),
sa.Column('creator_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['creator_id'],
[u'main.user.id'],
),
sa.ForeignKeyConstraint(
['cve_id'],
['cve.nvd_jsons.cve_id'],
),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('cve_id'),
schema='main')
op.create_table('vulnerability_git_commits',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('commit_hash',
sa.String(length=1000),
nullable=False),
sa.Column('commit_link',
sa.String(length=1000),
nullable=False),
sa.Column('repo_name',
sa.String(length=1000),
nullable=False),
sa.Column('repo_owner',
sa.String(length=1000),
nullable=True),
sa.Column('repo_url',
sa.String(length=1000),
nullable=True),
sa.Column('vulnerability_details_id',
sa.Integer(),
nullable=True),
sa.Column('tree_cache', mysql.LONGTEXT(), nullable=True),
sa.ForeignKeyConstraint(
['vulnerability_details_id'],
['main.vulnerability.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='main')
op.create_index(op.f('ix_main_vulnerability_git_commits_commit_hash'),
'vulnerability_git_commits', ['commit_hash'],
unique=False,
schema='main')
op.create_table('vulnerability_resources',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('link', sa.String(length=1000), nullable=False),
sa.Column('vulnerability_details_id',
sa.Integer(),
nullable=True),
sa.ForeignKeyConstraint(
['vulnerability_details_id'],
['main.vulnerability.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='main')
op.create_table('repository_files',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('file_name',
sa.String(length=1000),
nullable=False),
sa.Column('file_path',
sa.String(length=1000),
nullable=False),
sa.Column('file_hash',
sa.String(length=1000),
nullable=False),
sa.Column('file_patch', sa.Text(), nullable=False),
sa.Column('commit_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['commit_id'],
['main.vulnerability_git_commits.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='main')
op.create_table('repository_file_comments',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('revision', sa.Integer(), nullable=False),
sa.Column('active', sa.Boolean(), nullable=False),
sa.Column('archived_at', sa.DateTime(), nullable=True),
sa.Column('row_from', sa.Integer(), nullable=True),
sa.Column('row_to', sa.Integer(), nullable=True),
sa.Column('text', sa.Text(), nullable=False),
sa.Column('sort_pos', sa.Integer(), nullable=True),
sa.Column('repository_file_id',
sa.Integer(),
nullable=True),
sa.Column('creator_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['creator_id'],
[u'main.user.id'],
),
sa.ForeignKeyConstraint(
['repository_file_id'],
['main.repository_files.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='main')
op.create_table('repository_file_markers',
sa.Column('id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('revision', sa.Integer(), nullable=False),
sa.Column('active', sa.Boolean(), nullable=False),
sa.Column('archived_at', sa.DateTime(), nullable=True),
sa.Column('row_from', sa.Integer(), nullable=True),
sa.Column('row_to', sa.Integer(), nullable=True),
sa.Column('column_from', sa.Integer(), nullable=True),
sa.Column('column_to', sa.Integer(), nullable=True),
sa.Column('marker_class',
sa.String(length=100),
nullable=False),
sa.Column('repository_file_id',
sa.Integer(),
nullable=True),
sa.Column('creator_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['creator_id'],
[u'main.user.id'],
),
sa.ForeignKeyConstraint(
['repository_file_id'],
['main.repository_files.id'],
),
sa.PrimaryKeyConstraint('id'),
schema='main')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('metrics_data_delay',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('value', sa.Integer(), nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_metrics_data_delay_created_at'),
'metrics_data_delay', ['created_at'],
unique=False)
op.create_index(op.f('ix_metrics_data_delay_table_id'),
'metrics_data_delay', ['table_id'],
unique=False)
op.create_table('metrics_data_values',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('column_name', sa.String(), nullable=True),
sa.Column('column_value', sa.String(), nullable=True),
sa.Column('check_name', sa.String(), nullable=True),
sa.Column('check_value', sa.Float(), nullable=True),
sa.Column('time_interval', sa.String(), nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_metrics_data_values_created_at'),
'metrics_data_values', ['created_at'],
unique=False)
op.create_index(op.f('ix_metrics_data_values_table_id'),
'metrics_data_values', ['table_id'],
unique=False)
op.create_table('metrics_data_volume',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('time_interval', sa.String(), nullable=True),
sa.Column('count', sa.BigInteger(), nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_metrics_data_volume_created_at'),
'metrics_data_volume', ['created_at'],
unique=False)
op.create_index(op.f('ix_metrics_data_volume_table_id'),
'metrics_data_volume', ['table_id'],
unique=False)
op.create_table('metrics_data_volume_diff',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('date', sa.Date(), nullable=True),
sa.Column('count', sa.BigInteger(), nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_metrics_data_volume_diff_created_at'),
'metrics_data_volume_diff', ['created_at'],
unique=False)
op.create_index(op.f('ix_metrics_data_volume_diff_table_id'),
'metrics_data_volume_diff', ['table_id'],
unique=False)
op.create_table('metrics_table_schema_changes',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('table_id', sa.Integer(), nullable=True),
sa.Column('column_name', sa.String(), nullable=True),
sa.Column('column_type', sa.String(), nullable=True),
sa.Column('column_count', sa.Integer(), nullable=True),
sa.Column('operation', sa.String(), nullable=True),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_metrics_table_schema_changes_created_at'),
'metrics_table_schema_changes', ['created_at'],
unique=False)
op.create_index(op.f('ix_metrics_table_schema_changes_table_id'),
'metrics_table_schema_changes', ['table_id'],
unique=False)
op.create_table(
'monitored_table', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.TIMESTAMP(), nullable=True),
sa.Column('source_db', sa.String(), nullable=True),
sa.Column('active', sa.Boolean(), nullable=True),
sa.Column('table_name', sa.String(), nullable=True),
sa.Column('time_column', sa.String(), nullable=True),
sa.Column('time_column_type', sa.String(), nullable=True),
sa.Column('schema',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True), sa.PrimaryKeyConstraint('id'))
def upgrade():
op.create_table(
"vehicle",
sa.Column("pk", sa.Integer(), nullable=False),
sa.Column("id", sa.String(), nullable=True),
sa.Column("source_pk", sa.Integer(), nullable=False),
sa.Column("system_pk", sa.Integer(), nullable=False),
sa.Column("trip_pk", sa.Integer(), nullable=True),
sa.Column("label", sa.String(), nullable=True),
sa.Column("license_plate", sa.String(), nullable=True),
sa.Column(
"current_status",
sa.Enum(
"INCOMING_AT",
"STOPPED_AT",
"IN_TRANSIT_TO",
name="status",
native_enum=False,
),
nullable=False,
),
sa.Column("latitude", sa.Float(), nullable=True),
sa.Column("longitude", sa.Float(), nullable=True),
sa.Column("bearing", sa.Float(), nullable=True),
sa.Column("odometer", sa.Float(), nullable=True),
sa.Column("speed", sa.Float(), nullable=True),
sa.Column(
"congestion_level",
sa.Enum(
"UNKNOWN_CONGESTION_LEVEL",
"RUNNING_SMOOTHLY",
"STOP_AND_GO",
"CONGESTION",
"SEVERE_CONGESTION",
name="congestionlevel",
native_enum=False,
),
nullable=False,
),
sa.Column("updated_at", sa.TIMESTAMP(timezone=True), nullable=True),
sa.ForeignKeyConstraint(
["source_pk"],
["feed_update.pk"],
),
sa.ForeignKeyConstraint(
["system_pk"],
["system.pk"],
),
sa.PrimaryKeyConstraint("pk"),
sa.UniqueConstraint("system_pk", "id"),
)
op.create_index(op.f("ix_vehicle_source_pk"),
"vehicle", ["source_pk"],
unique=False)
op.create_index(op.f("ix_vehicle_system_pk"),
"vehicle", ["system_pk"],
unique=False)
op.create_index(op.f("ix_vehicle_trip_pk"),
"vehicle", ["trip_pk"],
unique=True)
op.create_foreign_key(None, "vehicle", "trip", ["trip_pk"], ["pk"])
op.alter_column("alert",
"cause",
existing_type=sa.VARCHAR(),
nullable=False)
op.alter_column("alert",
"effect",
existing_type=sa.VARCHAR(),
nullable=False)
op.add_column("trip", sa.Column("delay", sa.Integer(), nullable=True))
op.add_column(
"trip",
sa.Column("started_at", sa.TIMESTAMP(timezone=True), nullable=True))
op.add_column(
"trip",
sa.Column("updated_at", sa.TIMESTAMP(timezone=True), nullable=True))
op.drop_column("trip", "current_stop_sequence")
op.drop_column("trip", "vehicle_id")
op.drop_column("trip", "last_update_time")
op.drop_column("trip", "start_time")
op.drop_column("trip", "current_status")
op.add_column("vehicle",
sa.Column("current_stop_pk", sa.Integer(), nullable=True))
op.add_column(
"vehicle",
sa.Column("current_stop_sequence", sa.Integer(), nullable=True))
op.add_column(
"vehicle",
sa.Column(
"occupancy_status",
sa.Enum(
"EMPTY",
"MANY_SEATS_AVAILABLE",
"FEW_SEATS_AVAILABLE",
"STANDING_ROOM_ONLY",
"CRUSHED_STANDING_ROOM_ONLY",
"FULL",
"NOT_ACCEPTING_PASSENGERS",
"UNKNOWN",
name="occupancystatus",
native_enum=False,
),
nullable=False,
),
)
op.create_foreign_key(None, "vehicle", "stop", ["current_stop_pk"], ["pk"])
def upgrade():
conn = op.get_bind()
if conn.dialect.name == "mysql":
conn.execute("SET time_zone = '+00:00'")
cur = conn.execute("SELECT @@explicit_defaults_for_timestamp")
res = cur.fetchall()
if res[0][0] == 0:
raise Exception(
"Global variable explicit_defaults_for_timestamp needs to be on (1) for mysql"
)
op.alter_column(
table_name="chart",
column_name="last_modified",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="dag",
column_name="last_scheduler_run",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="dag", column_name="last_pickled", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="dag", column_name="last_expired", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="dag_pickle",
column_name="created_dttm",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="dag_run",
column_name="execution_date",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="dag_run", column_name="start_date", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="dag_run", column_name="end_date", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="import_error",
column_name="timestamp",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="job", column_name="start_date", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="job", column_name="end_date", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="job",
column_name="latest_heartbeat",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="log", column_name="dttm", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="log", column_name="execution_date", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="sla_miss",
column_name="execution_date",
type_=mysql.TIMESTAMP(fsp=6),
nullable=False,
)
op.alter_column(
table_name="sla_miss", column_name="timestamp", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="task_fail",
column_name="execution_date",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="task_fail",
column_name="start_date",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="task_fail", column_name="end_date", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="task_instance",
column_name="execution_date",
type_=mysql.TIMESTAMP(fsp=6),
nullable=False,
)
op.alter_column(
table_name="task_instance",
column_name="start_date",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="task_instance",
column_name="end_date",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="task_instance",
column_name="queued_dttm",
type_=mysql.TIMESTAMP(fsp=6),
)
op.alter_column(
table_name="xcom", column_name="timestamp", type_=mysql.TIMESTAMP(fsp=6)
)
op.alter_column(
table_name="xcom",
column_name="execution_date",
type_=mysql.TIMESTAMP(fsp=6),
)
else:
# sqlite and mssql datetime are fine as is. Therefore, not converting
if conn.dialect.name in ("sqlite", "mssql"):
return
# we try to be database agnostic, but not every db (e.g. sqlserver)
# supports per session time zones
if conn.dialect.name == "postgresql":
conn.execute("set timezone=UTC")
op.alter_column(
table_name="chart",
column_name="last_modified",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag",
column_name="last_scheduler_run",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag",
column_name="last_pickled",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag",
column_name="last_expired",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag_pickle",
column_name="created_dttm",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag_run",
column_name="execution_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag_run",
column_name="start_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="dag_run",
column_name="end_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="import_error",
column_name="timestamp",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="job",
column_name="start_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="job", column_name="end_date", type_=sa.TIMESTAMP(timezone=True)
)
op.alter_column(
table_name="job",
column_name="latest_heartbeat",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="log", column_name="dttm", type_=sa.TIMESTAMP(timezone=True)
)
op.alter_column(
table_name="log",
column_name="execution_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="sla_miss",
column_name="execution_date",
type_=sa.TIMESTAMP(timezone=True),
nullable=False,
)
op.alter_column(
table_name="sla_miss",
column_name="timestamp",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="task_fail",
column_name="execution_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="task_fail",
column_name="start_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="task_fail",
column_name="end_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="task_instance",
column_name="execution_date",
type_=sa.TIMESTAMP(timezone=True),
nullable=False,
)
op.alter_column(
table_name="task_instance",
column_name="start_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="task_instance",
column_name="end_date",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="task_instance",
column_name="queued_dttm",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="xcom",
column_name="timestamp",
type_=sa.TIMESTAMP(timezone=True),
)
op.alter_column(
table_name="xcom",
column_name="execution_date",
type_=sa.TIMESTAMP(timezone=True),
)
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, JSONB, insert
CHUNKSIZE = 800 * 1024
meta = sa.MetaData()
history = sa.Table(
'fs_history', meta,
sa.Column('id',
UUID,
primary_key=True,
server_default=sa.text('gen_random_uuid()')),
sa.Column('path', sa.String, nullable=False),
sa.Column('rev', sa.Integer, default=0, nullable=False),
sa.Column('created',
sa.TIMESTAMP(timezone=True),
default=sa.func.now(),
index=True), sa.Column('content_type', sa.String),
sa.Column('owner', sa.String), sa.Column('meta', JSONB),
sa.Column('data', sa.Binary), sa.UniqueConstraint('path', 'rev'),
sa.Index('meta_idx', 'meta', postgresql_using="gin"))
active = sa.Table(
'fs_files',
meta,
sa.Column('path', sa.String, primary_key=True, nullable=False),
sa.Column('created', sa.TIMESTAMP(timezone=True), nullable=False),
sa.Column('modified', sa.TIMESTAMP(timezone=True), nullable=False),
sa.Column('version', sa.ForeignKey(history.c.id), nullable=False),
sa.Column('length', sa.Integer, nullable=False, default=0),
)
def upgrade():
op.execute('CREATE SCHEMA "Secret"')
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('SecretType',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('Name', sa.String(length=100), nullable=False),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.PrimaryKeyConstraint('Id'),
sa.UniqueConstraint('Name'),
schema='Secret')
op.create_table('AuthenticationType',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('SecretTypeId', sa.Integer(), nullable=True),
sa.Column('Name', sa.String(length=100), nullable=False),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['SecretTypeId'],
['Secret.SecretType.Id'],
),
sa.PrimaryKeyConstraint('Id'),
sa.UniqueConstraint('Name'),
schema='Secret')
op.create_table('Secret',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('SecretTypeId', sa.Integer(), nullable=True),
sa.Column('Name', sa.String(length=100), nullable=False),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['SecretTypeId'],
['Secret.SecretType.Id'],
),
sa.PrimaryKeyConstraint('Id'),
schema='Secret')
op.create_index(op.f('ix_Secret_Secret_Name'),
'Secret', ['Name'],
unique=False,
schema='Secret')
op.create_table('ConnectionFile',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('ConnectionId', sa.Integer(), nullable=True),
sa.Column('ConnectorTypeId', sa.Integer(), nullable=True),
sa.Column('Folder', sa.String(length=100), nullable=True),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['ConnectionId'],
['Connection.Connection.Id'],
),
sa.ForeignKeyConstraint(
['ConnectorTypeId'],
['Connection.ConnectorType.Id'],
),
sa.PrimaryKeyConstraint('Id'),
schema='Connection')
op.create_table('ConnectionSecret',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('ConnectionId', sa.Integer(), nullable=True),
sa.Column('SecretId', sa.Integer(), nullable=True),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['ConnectionId'],
['Connection.Connection.Id'],
),
sa.ForeignKeyConstraint(
['SecretId'],
['Secret.Secret.Id'],
),
sa.PrimaryKeyConstraint('Id'),
schema='Connection')
op.create_table('SecretSource',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('SecretId', sa.Integer(), nullable=True),
sa.Column('AuthenticationTypeId',
sa.Integer(),
nullable=True),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['AuthenticationTypeId'],
['Secret.AuthenticationType.Id'],
),
sa.ForeignKeyConstraint(
['SecretId'],
['Secret.Secret.Id'],
),
sa.PrimaryKeyConstraint('Id'),
schema='Secret')
op.create_table('DataIntegrationConnectionFile',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('DataIntegrationConnectionId',
sa.Integer(),
nullable=True),
sa.Column('FileName',
sa.String(length=1000),
nullable=False),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['DataIntegrationConnectionId'],
['Integration.DataIntegrationConnection.Id'],
),
sa.PrimaryKeyConstraint('Id'),
schema='Integration')
op.create_table('SecretSourceBasicAuthentication',
sa.Column('Id', sa.Integer(), nullable=False),
sa.Column('SecretSourceId', sa.Integer(), nullable=True),
sa.Column('User', sa.String(length=100), nullable=False),
sa.Column('Password',
sa.String(length=100),
nullable=False),
sa.Column('CreatedByUserId', sa.Integer(), nullable=False),
sa.Column('CreationDate', sa.DateTime(), nullable=False),
sa.Column('LastUpdatedUserId', sa.Integer(),
nullable=True),
sa.Column('LastUpdatedDate', sa.DateTime(), nullable=True),
sa.Column('IsDeleted', sa.Integer(), nullable=False),
sa.Column('Comments',
sa.String(length=1000),
nullable=True),
sa.Column('RowVersion', sa.TIMESTAMP(), nullable=True),
sa.ForeignKeyConstraint(
['SecretSourceId'],
['Secret.SecretSource.Id'],
),
sa.PrimaryKeyConstraint('Id'),
schema='Secret')
insert_secret_and_authentication_types()
insert_connection_database_to_secrets()
op.drop_column('ConnectionDatabase', 'User', schema='Connection')
op.drop_column('ConnectionDatabase', 'Password', schema='Connection')
def upgrade():
op.create_table(
"itemsequence",
sa.Column("parent_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("value", sa.Integer(), server_default="0", nullable=False),
sa.PrimaryKeyConstraint("parent_id", "value"),
)
op.execute(NEXT_ITEM_VALUE_FUNCTION)
op.create_table(
"api_token",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("access_token", sa.String(length=64), nullable=False),
sa.Column("refresh_token", sa.String(length=64), nullable=False),
sa.Column("expires_at", sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("access_token"),
sa.UniqueConstraint("refresh_token"),
)
op.create_table(
"itemoption",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("item_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("name", sa.String(length=64), nullable=False),
sa.Column("value", sa.Text(), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("item_id", "name", name="unq_itemoption_name"),
)
op.create_table(
"itemstat",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("item_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("name", sa.String(length=64), nullable=False),
sa.Column("value", sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("item_id", "name", name="unq_itemstat_name"),
)
op.create_table(
"repository",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("name", sa.String(length=200), nullable=False),
sa.Column("url", sa.String(length=200), nullable=False),
sa.Column("backend", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("status", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("data", zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("last_update", sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column("last_update_attempt", sa.TIMESTAMP(timezone=True), nullable=True),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("name"),
sa.UniqueConstraint("url"),
)
op.create_table(
"user",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("email", sa.String(length=128), nullable=False),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("email"),
)
op.create_table(
"api_token_repository_access",
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("api_token_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["api_token_id"], ["api_token.id"]),
sa.ForeignKeyConstraint(["repository_id"], ["repository.id"]),
sa.PrimaryKeyConstraint("repository_id", "api_token_id"),
)
op.create_table(
"author",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("name", sa.String(length=128), nullable=False),
sa.Column("email", sa.String(length=128), nullable=True),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("repository_id", "email", name="unq_author_email"),
)
op.create_index(
op.f("ix_author_repository_id"), "author", ["repository_id"], unique=False
)
op.create_table(
"hook",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("token", sa.LargeBinary(length=64), nullable=False),
sa.Column("provider", sa.String(length=64), nullable=False),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("token"),
)
op.create_index(
op.f("ix_hook_repository_id"), "hook", ["repository_id"], unique=False
)
op.create_table(
"identity",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("user_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("external_id", sa.String(length=64), nullable=False),
sa.Column("provider", sa.String(length=32), nullable=False),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("config", zeus.db.types.json.JSONEncodedDict(), nullable=False),
sa.ForeignKeyConstraint(["user_id"], ["user.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("external_id"),
sa.UniqueConstraint("user_id", "provider", name="unq_identity_user"),
)
op.create_index(op.f("ix_identity_user_id"), "identity", ["user_id"], unique=False)
op.create_table(
"repository_access",
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("user_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["repository_id"], ["repository.id"]),
sa.ForeignKeyConstraint(["user_id"], ["user.id"]),
sa.PrimaryKeyConstraint("repository_id", "user_id"),
)
op.create_table(
"revision",
sa.Column("sha", sa.String(length=40), nullable=False),
sa.Column("author_id", zeus.db.types.guid.GUID(), nullable=True),
sa.Column("committer_id", zeus.db.types.guid.GUID(), nullable=True),
sa.Column("message", sa.Text(), nullable=True),
sa.Column("parents", postgresql.ARRAY(sa.String(length=40)), nullable=True),
sa.Column("branches", postgresql.ARRAY(sa.String(length=128)), nullable=True),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column(
"date_committed",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["author_id"], ["author.id"]),
sa.ForeignKeyConstraint(["committer_id"], ["author.id"]),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("sha"),
sa.UniqueConstraint("repository_id", "sha", name="unq_revision"),
)
op.create_index(
op.f("ix_revision_author_id"), "revision", ["author_id"], unique=False
)
op.create_index(
op.f("ix_revision_committer_id"), "revision", ["committer_id"], unique=False
)
op.create_index(
op.f("ix_revision_repository_id"), "revision", ["repository_id"], unique=False
)
op.create_table(
"patch",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("parent_revision_sha", sa.String(length=40), nullable=False),
sa.Column("diff", sa.Text(), nullable=False),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(
["repository_id", "parent_revision_sha"],
["revision.repository_id", "revision.sha"],
),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(
"idx_repo_sha", "patch", ["repository_id", "parent_revision_sha"], unique=False
)
op.create_index(
op.f("ix_patch_repository_id"), "patch", ["repository_id"], unique=False
)
op.create_table(
"source",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("patch_id", zeus.db.types.guid.GUID(), nullable=True),
sa.Column("revision_sha", sa.String(length=40), nullable=False),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("data", zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["patch_id"], ["patch.id"]),
sa.ForeignKeyConstraint(
["repository_id", "revision_sha"],
["revision.repository_id", "revision.sha"],
),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("patch_id"),
sa.UniqueConstraint(
"repository_id", "revision_sha", "patch_id", name="unq_source_revision"
),
)
op.create_index(
"idx_source_repo_sha", "source", ["repository_id", "revision_sha"], unique=False
)
op.create_index(
op.f("ix_source_repository_id"), "source", ["repository_id"], unique=False
)
op.create_table(
"build",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("source_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("author_id", zeus.db.types.guid.GUID(), nullable=True),
sa.Column("number", sa.Integer(), nullable=False),
sa.Column("status", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("result", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("date_started", sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column("date_finished", sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("data", zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column("provider", sa.String(), nullable=True),
sa.Column("external_id", sa.String(length=64), nullable=True),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["author_id"], ["author.id"]),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.ForeignKeyConstraint(["source_id"], ["source.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("repository_id", "number", name="unq_build_number"),
sa.UniqueConstraint(
"repository_id", "provider", "external_id", name="unq_build_provider"
),
)
op.create_index(op.f("ix_build_author_id"), "build", ["author_id"], unique=False)
op.create_index(
op.f("ix_build_repository_id"), "build", ["repository_id"], unique=False
)
op.create_index(op.f("ix_build_source_id"), "build", ["source_id"], unique=False)
op.create_table(
"job",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("build_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("number", sa.Integer(), nullable=False),
sa.Column("status", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("result", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("date_started", sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column("date_finished", sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("data", zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column("provider", sa.String(), nullable=True),
sa.Column("external_id", sa.String(length=64), nullable=True),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["build_id"], ["build.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("build_id", "number", name="unq_job_number"),
sa.UniqueConstraint(
"build_id", "provider", "external_id", name="unq_job_provider"
),
)
op.create_index(op.f("ix_job_build_id"), "job", ["build_id"], unique=False)
op.create_index(
op.f("ix_job_repository_id"), "job", ["repository_id"], unique=False
)
op.create_table(
"filecoverage",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("job_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("filename", sa.String(length=256), nullable=False),
sa.Column("data", sa.Text(), nullable=False),
sa.Column("lines_covered", sa.Integer(), nullable=False),
sa.Column("lines_uncovered", sa.Integer(), nullable=False),
sa.Column("diff_lines_covered", sa.Integer(), nullable=False),
sa.Column("diff_lines_uncovered", sa.Integer(), nullable=False),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["job_id"], ["job.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id", "filename"),
sa.UniqueConstraint("job_id", "filename", name="unq_job_filname"),
)
op.create_index(
op.f("ix_filecoverage_repository_id"),
"filecoverage",
["repository_id"],
unique=False,
)
op.create_table(
"testcase",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("job_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("hash", sa.String(length=40), nullable=False),
sa.Column("name", sa.Text(), nullable=False),
sa.Column("result", zeus.db.types.enum.Enum(), nullable=False),
sa.Column("duration", sa.Integer(), nullable=True),
sa.Column("message", sa.Text(), nullable=True),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["job_id"], ["job.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("job_id", "hash", name="unq_testcase_hash"),
)
op.create_index(
op.f("ix_testcase_repository_id"), "testcase", ["repository_id"], unique=False
)
op.create_table(
"artifact",
sa.Column("id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("job_id", zeus.db.types.guid.GUID(), nullable=False),
sa.Column("testcase_id", zeus.db.types.guid.GUID(), nullable=True),
sa.Column("name", sa.String(length=256), nullable=False),
sa.Column(
"type", zeus.db.types.enum.Enum(), server_default="0", nullable=False
),
sa.Column("file", zeus.db.types.file.File(), nullable=False),
sa.Column(
"date_created",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("now()"),
nullable=False,
),
sa.Column("repository_id", zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(["job_id"], ["job.id"], ondelete="CASCADE"),
sa.ForeignKeyConstraint(
["repository_id"], ["repository.id"], ondelete="CASCADE"
),
sa.ForeignKeyConstraint(["testcase_id"], ["testcase.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(
op.f("ix_artifact_repository_id"), "artifact", ["repository_id"], unique=False
)
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
# Normalised user identifier
uid = sa.Column(sa.UnicodeText(), nullable=False, unique=True)
# Username as chosen by the user on registration
_username = sa.Column('username',
sa.UnicodeText(),
nullable=False,
unique=True)
admin = sa.Column(sa.Boolean,
default=False,
nullable=False,
server_default=sa.sql.expression.false())
# Is this user a staff member?
staff = sa.Column(sa.Boolean,
nullable=False,
default=False,
server_default=sa.sql.expression.false())
sidebar_tutorial_dismissed = sa.Column(sa.Boolean,
default=False,
server_default=(
sa.sql.expression.false()))
def _get_username(self):
return self._username
def _set_username(self, value):
if not USERNAME_MIN_LENGTH <= len(value) <= USERNAME_MAX_LENGTH:
raise ValueError('username must be between {min} and {max} '
'characters long'.format(
min=USERNAME_MIN_LENGTH,
max=USERNAME_MAX_LENGTH))
self._username = value
self.uid = _username_to_uid(value)
@declared_attr
def username(self):
return sa.orm.synonym('_username',
descriptor=property(self._get_username,
self._set_username))
email = sa.Column(sa.UnicodeText(), nullable=False, unique=True)
last_login_date = sa.Column(sa.TIMESTAMP(timezone=False),
default=datetime.datetime.utcnow,
server_default=sa.func.now(),
nullable=False)
registered_date = sa.Column(sa.TIMESTAMP(timezone=False),
default=datetime.datetime.utcnow,
server_default=sa.func.now(),
nullable=False)
# Activation foreign key
activation_id = sa.Column(sa.Integer, sa.ForeignKey(Activation.id))
activation = sa.orm.relationship('Activation', backref='user')
@sa.orm.validates('email')
def validate_email(self, key, email):
if len(email) > EMAIL_MAX_LENGTH:
raise ValueError('email must be less than {max} characters '
'long'.format(max=EMAIL_MAX_LENGTH))
return email
@property
def is_activated(self):
if self.activation_id is None:
return True
return False
def activate(self):
"""Activate the user by deleting any activation they have."""
session = sa.orm.object_session(self)
session.delete(self.activation)
# Hashed password
_password = sa.Column('password', sa.UnicodeText(), nullable=False)
# Password salt
salt = sa.Column(sa.UnicodeText(), nullable=False)
# Last password update
password_updated = sa.Column(sa.DateTime(),
default=datetime.datetime.utcnow,
server_default=sa.func.now(),
nullable=False)
@hybrid_property
def password(self):
return self._password
@password.setter
def password(self, value):
self._set_password(value)
def _get_password(self):
return self._password
def _set_password(self, raw_password):
if len(raw_password) < PASSWORD_MIN_LENGTH:
raise ValueError('password must be more than {min} characters '
'long'.format(min=PASSWORD_MIN_LENGTH))
self._password = self._hash_password(raw_password)
self.password_updated = datetime.datetime.utcnow()
def _hash_password(self, password):
if not self.salt:
self.salt = _generate_random_string(24)
return text_type(CRYPT.encode(password + self.salt))
@classmethod
def get_by_email(cls, session, email):
"""Fetch a user by email address."""
return session.query(cls).filter(
sa.func.lower(cls.email) == email.lower()
).first()
@classmethod
def get_by_activation(cls, session, activation):
"""Fetch a user by activation instance."""
user = session.query(cls).filter(
cls.activation_id == activation.id
).first()
return user
@classmethod
def validate_user(cls, user, password):
"""Validate the passed password for the specified user."""
if not user:
return None
if user.password is None:
valid = False
else:
valid = CRYPT.check(user.password, password + user.salt)
return valid
@classmethod
def get_by_username(cls, session, username):
"""Fetch a user by username."""
uid = _username_to_uid(username)
return session.query(cls).filter(cls.uid == uid).first()
def __repr__(self):
return '<User: %s>' % self.username
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'audit_generated_games',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('randomizer', sa.String(length=45), nullable=True),
sa.Column('hash_id', sa.String(length=50), nullable=True),
sa.Column('permalink', sa.String(length=2000), nullable=True),
sa.Column('settings', sa.JSON(), nullable=True),
sa.Column('gentype', sa.String(length=45), nullable=True),
sa.Column('genoption', sa.String(length=45), nullable=True),
sa.Column('timestamp',
sa.DateTime(),
server_default=sa.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('customizer', mysql.INTEGER(display_width=11),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_audit_generated_games_hash_id'),
'audit_generated_games', ['hash_id'],
unique=False)
op.create_table(
'audit_messages',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=True),
sa.Column('message_id', mysql.BIGINT(display_width=20), nullable=True),
sa.Column('user_id', mysql.BIGINT(display_width=20), nullable=True),
sa.Column('channel_id', mysql.BIGINT(display_width=20), nullable=True),
sa.Column('message_date', sa.DateTime(), nullable=True),
sa.Column('content',
sa.String(length=4000, collation='utf8mb4_bin'),
nullable=True),
sa.Column('attachment',
sa.String(length=2000, collation='utf8mb4_bin'),
nullable=True),
sa.Column('deleted',
mysql.INTEGER(display_width=11),
server_default=sa.text("'0'"),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_audit_messages_message_id'),
'audit_messages', ['message_id'],
unique=False)
op.create_table(
'config',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('parameter',
sa.String(length=45, collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('value',
sa.String(length=45, collation='utf8mb4_unicode_ci'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'daily',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('hash',
sa.String(length=45, collation='utf8_bin'),
nullable=False), sa.PrimaryKeyConstraint('id'))
op.create_table(
'gtbk_games',
sa.Column('game_id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('channel', sa.String(length=200), nullable=True),
sa.Column('status', sa.String(length=45), nullable=True),
sa.Column('timestamp',
sa.DateTime(),
server_default=sa.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
nullable=True), sa.PrimaryKeyConstraint('game_id'))
op.create_table(
'gtbk_guesses',
sa.Column('guess_id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('game_id', mysql.INTEGER(display_width=11), nullable=True),
sa.Column('twitch_user', sa.String(length=200), nullable=True),
sa.Column('guess', mysql.INTEGER(display_width=11), nullable=True),
sa.Column('score',
mysql.INTEGER(display_width=11),
server_default=sa.text("'0'"),
nullable=True),
sa.Column('timestamp',
sa.TIMESTAMP(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=False), sa.PrimaryKeyConstraint('guess_id'))
op.create_index('guess_UNIQUE',
'gtbk_guesses', ['game_id', 'twitch_user'],
unique=True)
op.create_table(
'gtbk_whitelist',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('channel',
sa.String(length=200, collation='utf8_bin'),
nullable=True),
sa.Column('twitch_user',
sa.String(length=200, collation='utf8_bin'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'mention_counters',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('role_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('counter',
mysql.INTEGER(display_width=11),
server_default=sa.text("'1'"),
nullable=False),
sa.Column('last_used',
sa.DateTime(),
server_default=sa.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
nullable=False), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('role_id'))
op.create_table(
'permissions',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('role_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('permission',
sa.String(length=45, collation='utf8mb4_unicode_ci'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'reaction_group',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('channel_id', mysql.BIGINT(display_width=20),
nullable=False),
sa.Column('message_id', mysql.BIGINT(display_width=20),
nullable=False),
sa.Column('name',
sa.String(length=400, collation='utf8mb4_unicode_ci'),
nullable=True),
sa.Column('description', mysql.VARCHAR(length=1000), nullable=True),
sa.Column('bot_managed',
mysql.INTEGER(display_width=11),
nullable=True), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('message_id'))
op.create_table(
'reaction_role',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('reaction_group_id',
mysql.BIGINT(display_width=20),
nullable=True),
sa.Column('role_id', mysql.BIGINT(display_width=20), nullable=True),
sa.Column('name',
sa.String(length=45, collation='utf8mb4_unicode_ci'),
nullable=False),
sa.Column('emoji', mysql.VARCHAR(length=200), nullable=False),
sa.Column('description',
sa.String(length=400, collation='utf8mb4_unicode_ci'),
nullable=True),
sa.Column('protect_mentions',
mysql.TINYINT(display_width=1),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'seed_presets',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('name', mysql.VARCHAR(length=45), nullable=False),
sa.Column('randomizer', mysql.VARCHAR(length=45), nullable=True),
sa.Column('customizer', mysql.TINYINT(display_width=4), nullable=True),
sa.Column('settings', sa.JSON(), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'spoiler_races',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('srl_id',
sa.String(length=45, collation='utf8_bin'),
nullable=False),
sa.Column('spoiler_url',
sa.String(length=255, collation='utf8_bin'),
nullable=False),
sa.Column('studytime', mysql.INTEGER(display_width=11), nullable=True),
sa.Column('date',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('srl_id'))
op.create_table(
'srl_nick_verification',
sa.Column('srl_nick',
sa.String(length=45, collation='utf8_bin'),
nullable=False),
sa.Column('key', mysql.BIGINT(display_width=20), nullable=True),
sa.Column('discord_user_id',
mysql.BIGINT(display_width=20),
nullable=True),
sa.Column('timestamp',
sa.DateTime(),
server_default=sa.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
nullable=True), sa.PrimaryKeyConstraint('srl_nick'),
sa.UniqueConstraint('key'), sa.UniqueConstraint('srl_nick'))
op.create_table(
'srl_races',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('srl_id', sa.String(length=45), nullable=False),
sa.Column('goal', sa.String(length=200), nullable=False),
sa.Column('timestamp',
sa.DateTime(),
server_default=sa.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('message', sa.String(length=200), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('srl_id'))
op.create_table(
'srlnick',
sa.Column('discord_user_id',
mysql.BIGINT(display_width=20),
nullable=False),
sa.Column('srl_nick', sa.String(length=200), nullable=True),
sa.Column('twitch_name', sa.String(length=200), nullable=True),
sa.Column('srl_verified', mysql.BIT(length=1), nullable=True),
sa.PrimaryKeyConstraint('discord_user_id'))
op.create_index(op.f('ix_srlnick_srl_nick'),
'srlnick', ['srl_nick'],
unique=False)
op.create_index(op.f('ix_srlnick_twitch_name'),
'srlnick', ['twitch_name'],
unique=False)
op.create_table(
'tournament_results',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('srl_id',
sa.String(length=45, collation='utf8_bin'),
nullable=True),
sa.Column('episode_id',
sa.String(length=45, collation='utf8_bin'),
nullable=True),
sa.Column('permalink',
sa.String(length=200, collation='utf8_bin'),
nullable=True),
sa.Column('spoiler',
sa.String(length=200, collation='utf8_bin'),
nullable=True),
sa.Column('event',
sa.String(length=45, collation='utf8_bin'),
nullable=True),
sa.Column('status',
sa.String(length=45, collation='utf8_bin'),
nullable=True),
sa.Column('results_json', sa.JSON(), nullable=True),
sa.Column('week',
sa.String(length=45, collation='utf8_bin'),
nullable=True),
sa.Column('written_to_gsheet',
mysql.TINYINT(display_width=4),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'twitch_channels',
sa.Column('channel', sa.String(length=200), nullable=False),
sa.Column('group', sa.String(length=45), nullable=False),
sa.PrimaryKeyConstraint('channel'))
op.create_table(
'twitch_command_text',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('channel',
sa.String(length=200, collation='utf8_bin'),
nullable=True),
sa.Column('command',
sa.String(length=45, collation='utf8_bin'),
nullable=True),
sa.Column('content',
sa.String(length=200, collation='utf8_bin'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_index('idx_twitch_command_text_channel_command',
'twitch_command_text', ['channel', 'command'],
unique=True)
op.create_table(
'voice_role',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('guild_id', mysql.BIGINT(display_width=20), nullable=False),
sa.Column('voice_channel_id',
mysql.BIGINT(display_width=20),
nullable=False),
sa.Column('role_id', mysql.BIGINT(display_width=20), nullable=False),
sa.PrimaryKeyConstraint('id'))
def registered_date(self):
""" Date of user's registration """
return sa.Column(sa.TIMESTAMP(timezone=False),
default=sa.sql.func.now(),
server_default=sa.func.now(),
nullable=False)
