def downgrade(): downvert_data(op) op.drop_table('user_preferences') op.drop_table('query_policy') # Create a temporary type "_resourcetypeenum" type temporary_type = ENUM(*OLD_RESOURCE_TYPES, name='resourcetypeenum_temp') new_type = ENUM(*NEW_RESOURCE_TYPES, name='resourcetypeenum') temporary_type.create(op.get_bind(), checkfirst=True) # Convert the name column from old_type to temporary_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column( 'name', existing_type=new_type, type_=temporary_type, nullable=False, postgresql_using='name::text::resourcetypeenum_temp') # Drop the old type, create a new enum of type 'resourcetypeenum' new_type.drop(op.get_bind(), checkfirst=True) old_type = ENUM(*OLD_RESOURCE_TYPES, name='resourcetypeenum') old_type.create(op.get_bind(), checkfirst=True) # Convert the name column from temporary_type to new_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column('name', existing_type=temporary_type, type_=old_type, nullable=False, postgresql_using='name::text::resourcetypeenum') # Drop the temporary type temporary_type.drop(op.get_bind(), checkfirst=True)
def downgrade(): context = op.get_context() op.drop_column('ps_endpoints', 'dtls_fingerprint') if context.bind.dialect.name == 'postgresql': enum = ENUM(*SHA_HASH_VALUES, name=SHA_HASH_NAME) enum.drop(op.get_bind(), checkfirst=False)
def downgrade(): context = op.get_context() op.drop_column('ps_endpoints', 'dtls_fingerprint') if context.bind.dialect.name == 'postgresql': enum = ENUM(*SHA_HASH_VALUES, name=SHA_HASH_NAME) enum.drop(op.get_bind(), checkfirst=False)
def downgrade(): if op.get_context().bind.dialect.name == 'mssql': op.drop_constraint('ck_ps_globals_taskprocessor_overload_trigger_pjsip_taskprocessor_overload_trigger_values', 'ps_globals') op.drop_column('ps_globals', 'taskprocessor_overload_trigger') if context.bind.dialect.name == 'postgresql': enum = ENUM(*PJSIP_TASKPROCESSOR_OVERLOAD_TRIGGER_VALUES, name=PJSIP_TASKPROCESSOR_OVERLOAD_TRIGGER_NAME) enum.drop(op.get_bind(), checkfirst=False)
def upgrade(): # Create a temporary type "_resourcetypeenum" type temporary_type = ENUM(*NEW_RESOURCE_TYPES, name='resourcetypeenum_temp') old_type = ENUM(*OLD_RESOURCE_TYPES, name='resourcetypeenum') temporary_type.create(op.get_bind(), checkfirst=True) # Convert the name column from old_type to temporary_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column( 'name', existing_type=old_type, type_=temporary_type, nullable=False, postgresql_using='name::text::resourcetypeenum_temp') # Drop the old type, create a new enum of type 'resourcetypeenum' old_type.drop(op.get_bind(), checkfirst=True) new_type = ENUM(*NEW_RESOURCE_TYPES, name='resourcetypeenum') new_type.create(op.get_bind(), checkfirst=True) # Convert the name column from temporary_type to new_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column('name', existing_type=temporary_type, type_=new_type, nullable=False, postgresql_using='name::text::resourcetypeenum') # Drop the temporary type temporary_type.drop(op.get_bind(), checkfirst=True) op.create_table( 'query_policy', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=False), sa.Column('description', sa.Text(), nullable=True), sa.Column('policy_filters', postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column('resource_id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['resource_id'], ['resource.id'], name='valid_query_definition_resource', ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'), sa.UniqueConstraint('resource_id')) op.create_table( 'user_preferences', sa.Column('id', sa.Integer(), nullable=False), sa.Column('user_id', sa.Integer(), nullable=False), sa.Column('preferences', postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.ForeignKeyConstraint(['user_id'], ['user.id'], name='valid_user', ondelete='CASCADE'), sa.PrimaryKeyConstraint('id')) upvert_data(op)
def downgrade(): context = op.get_context() if op.get_context().bind.dialect.name == 'mssql': op.drop_constraint('ck_ps_endpoints_dtls_fingerprint_sha_hash_values', 'ps_endpoints') op.drop_column('ps_endpoints', 'dtls_fingerprint') if context.bind.dialect.name == 'postgresql': enum = ENUM(*SHA_HASH_VALUES, name=SHA_HASH_NAME) enum.drop(op.get_bind(), checkfirst=False)
def downgrade(): if op.get_context().bind.dialect.name == 'mssql': op.drop_constraint( 'ck_ps_globals_taskprocessor_overload_trigger_pjsip_taskprocessor_overload_trigger_values', 'ps_globals') op.drop_column('ps_globals', 'taskprocessor_overload_trigger') if context.bind.dialect.name == 'postgresql': enum = ENUM(*PJSIP_TASKPROCESSOR_OVERLOAD_TRIGGER_VALUES, name=PJSIP_TASKPROCESSOR_OVERLOAD_TRIGGER_NAME) enum.drop(op.get_bind(), checkfirst=False)
def destroy_db(): metadata = MetaData() metadata.bind = db.engine metadata.reflect() tables = list(metadata.sorted_tables) while len(tables): for table in tables: try: table.drop(checkfirst=True) tables.remove(table) except InternalError: pass for enum in inspect(db.engine).get_enums(): enum = ENUM(name=enum["name"]) enum.drop(bind=db.engine, checkfirst=True)
def evilshit(): """EVIL: Delete all data and recreate the database.""" delete_index() db.drop_all() from sqlalchemy import MetaData, inspect from sqlalchemy.dialects.postgresql import ENUM metadata = MetaData() metadata.bind = db.engine metadata.reflect() for table in metadata.sorted_tables: table.drop(checkfirst=True) for enum in inspect(db.engine).get_enums(): enum = ENUM(name=enum['name']) enum.drop(bind=db.engine, checkfirst=True) init()
def destroy_db(): metadata = MetaData() metadata.bind = db.engine metadata.reflect() tables = list(metadata.sorted_tables) while len(tables): for table in tables: try: table.drop(checkfirst=True) tables.remove(table) except InternalError: pass for enum in inspect(db.engine).get_enums(): enum = ENUM(name=enum['name']) enum.drop(bind=db.engine, checkfirst=True)
def upgrade(): # Create a temporary type "_resourcetypeenum" type temporary_type = ENUM(*NEW_RESOURCE_TYPES, name='resourcetypeenum_temp') old_type = ENUM(*OLD_RESOURCE_TYPES, name='resourcetypeenum') temporary_type.create(op.get_bind(), checkfirst=True) # Convert the name column from old_type to temporary_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column( 'name', existing_type=old_type, type_=temporary_type, nullable=False, postgresql_using='name::text::resourcetypeenum_temp') # Drop the old type, create a new enum of type 'resourcetypeenum' old_type.drop(op.get_bind(), checkfirst=True) new_type = ENUM(*NEW_RESOURCE_TYPES, name='resource_type_enum') new_type.create(op.get_bind(), checkfirst=True) # Convert the name column from temporary_type to new_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column( 'name', existing_type=temporary_type, type_=new_type, nullable=False, postgresql_using='name::text::resource_type_enum') # Drop the temporary type temporary_type.drop(op.get_bind(), checkfirst=True) with op.batch_alter_table('alert_definitions', schema=None) as batch_op: batch_op.add_column( sa.Column('authorization_resource_id', sa.Integer(), nullable=True)) batch_op.create_unique_constraint('unique_alert_resource', [ 'authorization_resource_id']) batch_op.create_foreign_key('valid_alert_resource', 'resource', [ 'authorization_resource_id'], ['id'], ondelete='CASCADE') upvert_data(op) with op.batch_alter_table('alert_definitions', schema=None) as batch_op: batch_op.alter_column( sa.Column('authorization_resource_id', sa.Integer(), nullable=False))
def evilshit(): """EVIL: Delete all data and recreate the database.""" delete_index() from sqlalchemy import MetaData, inspect from sqlalchemy.exc import InternalError from sqlalchemy.dialects.postgresql import ENUM metadata = MetaData() metadata.bind = db.engine metadata.reflect() tables = list(metadata.sorted_tables) while len(tables): for table in tables: try: table.drop(checkfirst=True) tables.remove(table) except InternalError: pass for enum in inspect(db.engine).get_enums(): enum = ENUM(name=enum['name']) enum.drop(bind=db.engine, checkfirst=True) upgrade()
def downgrade(): with op.batch_alter_table('alert_definitions', schema=None) as batch_op: batch_op.drop_constraint('valid_alert_resource', type_='foreignkey') batch_op.drop_constraint('unique_alert_resource', type_='unique') batch_op.drop_column('authorization_resource_id') downvert_data(op) # Create a temporary type "_resourcetypeenum" type temporary_type = ENUM(*OLD_RESOURCE_TYPES, name='resourcetypeenum_temp') new_type = ENUM(*NEW_RESOURCE_TYPES, name='resource_type_enum') temporary_type.create(op.get_bind(), checkfirst=True) # Convert the name column from old_type to temporary_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column( 'name', existing_type=new_type, type_=temporary_type, nullable=False, postgresql_using='name::text::resourcetypeenum_temp') # Drop the old type, create a new enum of type 'resourcetypeenum' new_type.drop(op.get_bind(), checkfirst=True) old_type = ENUM(*OLD_RESOURCE_TYPES, name='resourcetypeenum') old_type.create(op.get_bind(), checkfirst=True) # Convert the name column from temporary_type to new_type with op.batch_alter_table('resource_type', schema=None) as batch_op: batch_op.alter_column( 'name', existing_type=temporary_type, type_=old_type, nullable=False, postgresql_using='name::text::resourcetypeenum') # Drop the temporary type temporary_type.drop(op.get_bind(), checkfirst=True)
class DeclEnumType(SchemaType, TypeDecorator): """DeclEnum augmented so that it can persist to the database.""" def __init__(self, enum): self.enum = enum self.impl = ENUM(*enum.names(), name=self._sql_type_name(), create_type=True, schema="horse") # self.impl = Enum(*enum.names(), name=self._sql_type_name()) def _sql_type_name(self): return "ck%s" % re.sub('([A-Z])', lambda m: '_' + m.group(1).lower(), self.enum.__name__) def drop(self, bind=None, checkfirst=False): # print("drop "*10) z = self.impl.drop(bind, checkfirst) # print("drop "*10) return z return "DROP TYPE IF EXISTS {}".format(self._sql_type_name()) # def create(self, schema): # return "CREATE TYPE {} AS ENUM ({})".format( # schema, # ','.join([ "'{}'".format(n) for n in self.enum.names()])) def _set_table(self, table, column): self.impl._set_table(table, column) def copy(self): return DeclEnumType(self.enum) def process_bind_param(self, value, dialect): if isinstance(value, EnumSymbol): value = value.name return value def process_result_value(self, value, dialect): if value is not None: return getattr(self.enum, value.strip())