def downgrade(): bind = op.get_bind() insp = engine.reflection.Inspector.from_engine(bind) # Remove the missing uniqueness constraint from the sql_metrics table. with op.batch_alter_table("sql_metrics", naming_convention=conv) as batch_op: batch_op.drop_constraint( generic_find_uq_constraint_name( "sql_metrics", {"metric_name", "table_id"}, insp ) or "uq_sql_metrics_table_id", type_="unique", ) # Restore the size of the sql_metrics.metric_name column and forego that it # be non-nullable. with op.batch_alter_table("sql_metrics") as batch_op: batch_op.alter_column( "metric_name", existing_type=String(255), nullable=True, type_=String(512) ) # Forego that the sql_metrics.expression column be non-nullable. with op.batch_alter_table("sql_metrics") as batch_op: batch_op.alter_column("expression", existing_type=Text, nullable=True) # Forego that the metrics.metric_name column be non-nullable. with op.batch_alter_table("metrics") as batch_op: batch_op.alter_column("metric_name", existing_type=String(255), nullable=True) # Forego that the metrics.json column be non-nullable. with op.batch_alter_table("metrics") as batch_op: batch_op.alter_column("json", existing_type=Text, nullable=True)
def downgrade(): bind = op.get_bind() insp = sa.engine.reflection.Inspector.from_engine(bind) # Restore the size of the metric_name column. with op.batch_alter_table('metrics', naming_convention=conv) as batch_op: batch_op.alter_column( 'metric_name', existing_type=sa.String(length=255), type_=sa.String(length=512), existing_nullable=True, ) # Remove the previous missing uniqueness constraints. for table, column in names.items(): with op.batch_alter_table(table, naming_convention=conv) as batch_op: batch_op.drop_constraint( generic_find_uq_constraint_name( table, {column, 'datasource_id'}, insp, ) or 'uq_{}_{}'.format(table, column), type_='unique', )
def downgrade(): bind = op.get_bind() insp = engine.reflection.Inspector.from_engine(bind) # Remove the missing uniqueness constraint from the table_columns table. with op.batch_alter_table('table_columns', naming_convention=conv) as batch_op: batch_op.drop_constraint( generic_find_uq_constraint_name( 'table_columns', {'column_name', 'table_id'}, insp, ) or 'uq_table_columns_column_name', type_='unique', ) # Restore the size of the table_columns.column_name column and forego that # it be non-nullable. with op.batch_alter_table('table_columns') as batch_op: batch_op.alter_column( 'column_name', existing_type=String(255), nullable=True, type_=String(256), ) # Forego that the columns.column_name be non-nullable. with op.batch_alter_table('columns') as batch_op: batch_op.alter_column( 'column_name', existing_type=String(255), nullable=True, )
def upgrade(): bind = op.get_bind() # Uniqueness constraint if present only exists in MySQL. if isinstance(bind.dialect, MySQLDialect): constraint_name = generic_find_uq_constraint_name( "tables", {"table_name"}, Inspector.from_engine(bind)) if constraint_name: op.drop_constraint(constraint_name, "tables", type_="unique")
def upgrade(): bind = op.get_bind() insp = engine.reflection.Inspector.from_engine(bind) # Drop the uniqueness constraint if it exists. constraint = generic_find_uq_constraint_name("tables", {"table_name"}, insp) if constraint: with op.batch_alter_table("tables", naming_convention=conv) as batch_op: batch_op.drop_constraint(constraint, type_="unique")
def downgrade(): bind = op.get_bind() insp = engine.reflection.Inspector.from_engine(bind) # Remove the missing uniqueness constraint from the sql_metrics table. with op.batch_alter_table('sql_metrics', naming_convention=conv) as batch_op: batch_op.drop_constraint( generic_find_uq_constraint_name( 'sql_metrics', {'metric_name', 'table_id'}, insp, ) or 'uq_sql_metrics_table_id', type_='unique', ) # Restore the size of the sql_metrics.metric_name column and forego that it # be non-nullable. with op.batch_alter_table('sql_metrics') as batch_op: batch_op.alter_column( 'metric_name', existing_type=String(255), nullable=True, type_=String(512), ) # Forego that the sql_metrics.expression column be non-nullable. with op.batch_alter_table('sql_metrics') as batch_op: batch_op.alter_column( 'expression', existing_type=Text, nullable=True, ) # Forego that the metrics.metric_name column be non-nullable. with op.batch_alter_table('metrics') as batch_op: batch_op.alter_column( 'metric_name', existing_type=String(255), nullable=True, ) # Forego that the metrics.json column be non-nullable. with op.batch_alter_table('metrics') as batch_op: batch_op.alter_column( 'json', existing_type=Text, nullable=True, )
def downgrade(): bind = op.get_bind() insp = sa.engine.reflection.Inspector.from_engine(bind) # Add cluster_name column with op.batch_alter_table("datasources") as batch_op: batch_op.add_column(sa.Column("cluster_name", sa.String(250))) # Update cluster_name values metadata = sa.MetaData(bind=bind) datasources = sa.Table("datasources", metadata, autoload=True) clusters = sa.Table("clusters", metadata, autoload=True) statement = datasources.update().values( cluster_name=sa.select([clusters.c.cluster_name]).where( datasources.c.cluster_id == clusters.c.id).as_scalar()) bind.execute(statement) with op.batch_alter_table("datasources") as batch_op: # Drop cluster_id column fk_constraint_name = generic_find_fk_constraint_name( "datasources", {"id"}, "clusters", insp) uq_constraint_name = generic_find_uq_constraint_name( "datasources", {"cluster_id", "datasource_name"}, insp) batch_op.drop_constraint(fk_constraint_name, type_="foreignkey") batch_op.drop_constraint(uq_constraint_name, type_="unique") batch_op.drop_column("cluster_id") # Add constraints to cluster_name column batch_op.alter_column("cluster_name", existing_type=sa.String(250), nullable=False) batch_op.create_unique_constraint("uq_datasources_cluster_name", ["cluster_name", "datasource_name"]) batch_op.create_foreign_key( "fk_datasources_cluster_name_clusters", "clusters", ["cluster_name"], ["cluster_name"], )
def upgrade(): bind = op.get_bind() insp = sa.engine.reflection.Inspector.from_engine(bind) # Add the new less restrictive uniqueness constraint. with op.batch_alter_table("datasources", naming_convention=conv) as batch_op: batch_op.create_unique_constraint("uq_datasources_cluster_name", ["cluster_name", "datasource_name"]) # Augment the tables which have a foreign key constraint related to the # datasources.datasource_name column. for foreign in ["columns", "metrics"]: with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Add the datasource_id column with the relevant constraints. batch_op.add_column(sa.Column("datasource_id", sa.Integer)) batch_op.create_foreign_key( "fk_{}_datasource_id_datasources".format(foreign), "datasources", ["datasource_id"], ["id"], ) # Helper table for database migration using minimal schema. table = sa.Table( foreign, sa.MetaData(), sa.Column("id", sa.Integer, primary_key=True), sa.Column("datasource_name", sa.String(255)), sa.Column("datasource_id", sa.Integer), ) # Migrate the existing data. for datasource in bind.execute(datasources.select()): bind.execute(table.update().where( table.c.datasource_name == datasource.datasource_name).values( datasource_id=datasource.id)) with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Drop the datasource_name column and associated constraints. Note # due to prior revisions (1226819ee0e3, 3b626e2a6783) there may # incorectly be multiple duplicate constraints. names = generic_find_fk_constraint_names(foreign, {"datasource_name"}, "datasources", insp) for name in names: batch_op.drop_constraint( name or "fk_{}_datasource_name_datasources".format(foreign), type_="foreignkey", ) batch_op.drop_column("datasource_name") try: # Drop the old more restrictive uniqueness constraint. with op.batch_alter_table("datasources", naming_convention=conv) as batch_op: batch_op.drop_constraint( generic_find_uq_constraint_name("datasources", {"datasource_name"}, insp) or "uq_datasources_datasource_name", type_="unique", ) except Exception as ex: logging.warning( "Constraint drop failed, you may want to do this " "manually on your database. For context, this is a known " "issue around undeterministic contraint names on Postgres " "and perhaps more databases through SQLAlchemy.") logging.exception(ex)
def downgrade(): bind = op.get_bind() insp = sa.engine.reflection.Inspector.from_engine(bind) # Add the new more restrictive uniqueness constraint which is required by # the foreign key constraints. Note this operation will fail if the # datasources.datasource_name column is no longer unique. with op.batch_alter_table("datasources", naming_convention=conv) as batch_op: batch_op.create_unique_constraint("uq_datasources_datasource_name", ["datasource_name"]) # Augment the tables which have a foreign key constraint related to the # datasources.datasource_id column. for foreign in ["columns", "metrics"]: with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Add the datasource_name column with the relevant constraints. batch_op.add_column(sa.Column("datasource_name", sa.String(255))) batch_op.create_foreign_key( "fk_{}_datasource_name_datasources".format(foreign), "datasources", ["datasource_name"], ["datasource_name"], ) # Helper table for database migration using minimal schema. table = sa.Table( foreign, sa.MetaData(), sa.Column("id", sa.Integer, primary_key=True), sa.Column("datasource_name", sa.String(255)), sa.Column("datasource_id", sa.Integer), ) # Migrate the existing data. for datasource in bind.execute(datasources.select()): bind.execute(table.update().where( table.c.datasource_id == datasource.id).values( datasource_name=datasource.datasource_name)) with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Drop the datasource_id column and associated constraint. batch_op.drop_constraint( "fk_{}_datasource_id_datasources".format(foreign), type_="foreignkey") batch_op.drop_column("datasource_id") with op.batch_alter_table("datasources", naming_convention=conv) as batch_op: # Prior to dropping the uniqueness constraint, the foreign key # associated with the cluster_name column needs to be dropped. batch_op.drop_constraint( generic_find_fk_constraint_name("datasources", {"cluster_name"}, "clusters", insp) or "fk_datasources_cluster_name_clusters", type_="foreignkey", ) # Drop the old less restrictive uniqueness constraint. batch_op.drop_constraint( generic_find_uq_constraint_name( "datasources", {"cluster_name", "datasource_name"}, insp) or "uq_datasources_cluster_name", type_="unique", ) # Re-create the foreign key associated with the cluster_name column. batch_op.create_foreign_key( "fk_{}_datasource_id_datasources".format(foreign), "clusters", ["cluster_name"], ["cluster_name"], )
def upgrade(): bind = op.get_bind() insp = sa.engine.reflection.Inspector.from_engine(bind) # Add the new less restrictive uniqueness constraint. with op.batch_alter_table('datasources', naming_convention=conv) as batch_op: batch_op.create_unique_constraint( 'uq_datasources_cluster_name', ['cluster_name', 'datasource_name'], ) # Augment the tables which have a foreign key constraint related to the # datasources.datasource_name column. for foreign in ['columns', 'metrics']: with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Add the datasource_id column with the relevant constraints. batch_op.add_column(sa.Column('datasource_id', sa.Integer)) batch_op.create_foreign_key( 'fk_{}_datasource_id_datasources'.format(foreign), 'datasources', ['datasource_id'], ['id'], ) # Helper table for database migration using minimal schema. table = sa.Table( foreign, sa.MetaData(), sa.Column('id', sa.Integer, primary_key=True), sa.Column('datasource_name', sa.String(255)), sa.Column('datasource_id', sa.Integer), ) # Migrate the existing data. for datasource in bind.execute(datasources.select()): bind.execute( table.update().where( table.c.datasource_name == datasource.datasource_name, ).values( datasource_id=datasource.id, ), ) with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Drop the datasource_name column and associated constraints. Note # due to prior revisions (1226819ee0e3, 3b626e2a6783) there may # incorectly be multiple duplicate constraints. names = generic_find_fk_constraint_names( foreign, {'datasource_name'}, 'datasources', insp, ) for name in names: batch_op.drop_constraint( name or 'fk_{}_datasource_name_datasources'.format(foreign), type_='foreignkey', ) batch_op.drop_column('datasource_name') try: # Drop the old more restrictive uniqueness constraint. with op.batch_alter_table('datasources', naming_convention=conv) as batch_op: batch_op.drop_constraint( generic_find_uq_constraint_name( 'datasources', {'datasource_name'}, insp, ) or 'uq_datasources_datasource_name', type_='unique', ) except Exception as e: logging.warning( 'Constraint drop failed, you may want to do this ' 'manually on your database. For context, this is a known ' 'issue around undeterministic contraint names on Postgres ' 'and perhaps more databases through SQLAlchemy.') logging.exception(e)
def downgrade(): bind = op.get_bind() insp = sa.engine.reflection.Inspector.from_engine(bind) # Add the new more restrictive uniqueness constraint which is required by # the foreign key constraints. Note this operation will fail if the # datasources.datasource_name column is no longer unique. with op.batch_alter_table('datasources', naming_convention=conv) as batch_op: batch_op.create_unique_constraint( 'uq_datasources_datasource_name', ['datasource_name'], ) # Augment the tables which have a foreign key constraint related to the # datasources.datasource_id column. for foreign in ['columns', 'metrics']: with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Add the datasource_name column with the relevant constraints. batch_op.add_column(sa.Column('datasource_name', sa.String(255))) batch_op.create_foreign_key( 'fk_{}_datasource_name_datasources'.format(foreign), 'datasources', ['datasource_name'], ['datasource_name'], ) # Helper table for database migration using minimal schema. table = sa.Table( foreign, sa.MetaData(), sa.Column('id', sa.Integer, primary_key=True), sa.Column('datasource_name', sa.String(255)), sa.Column('datasource_id', sa.Integer), ) # Migrate the existing data. for datasource in bind.execute(datasources.select()): bind.execute( table.update().where( table.c.datasource_id == datasource.id, ).values( datasource_name=datasource.datasource_name, ), ) with op.batch_alter_table(foreign, naming_convention=conv) as batch_op: # Drop the datasource_id column and associated constraint. batch_op.drop_constraint( 'fk_{}_datasource_id_datasources'.format(foreign), type_='foreignkey', ) batch_op.drop_column('datasource_id') with op.batch_alter_table('datasources', naming_convention=conv) as batch_op: # Prior to dropping the uniqueness constraint, the foreign key # associated with the cluster_name column needs to be dropped. batch_op.drop_constraint( generic_find_fk_constraint_name( 'datasources', {'cluster_name'}, 'clusters', insp, ) or 'fk_datasources_cluster_name_clusters', type_='foreignkey', ) # Drop the old less restrictive uniqueness constraint. batch_op.drop_constraint( generic_find_uq_constraint_name( 'datasources', {'cluster_name', 'datasource_name'}, insp, ) or 'uq_datasources_cluster_name', type_='unique', ) # Re-create the foreign key associated with the cluster_name column. batch_op.create_foreign_key( 'fk_{}_datasource_id_datasources'.format(foreign), 'clusters', ['cluster_name'], ['cluster_name'], )