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)