def _pre_upgrade_273(self, engine): if engine.name != 'sqlite': return # Drop a variety of unique constraints to ensure that the script # properly readds them back for table_name, constraint_name in [ ('compute_nodes', 'uniq_compute_nodes0' 'host0hypervisor_hostname'), ('fixed_ips', 'uniq_fixed_ips0address0deleted'), ('instance_info_caches', 'uniq_instance_info_caches0' 'instance_uuid'), ('instance_type_projects', 'uniq_instance_type_projects0' 'instance_type_id0project_id0' 'deleted'), ('pci_devices', 'uniq_pci_devices0compute_node_id0' 'address0deleted'), ('virtual_interfaces', 'uniq_virtual_interfaces0' 'address0deleted') ]: table = oslodbutils.get_table(engine, table_name) constraints = [ c for c in table.constraints if c.name == constraint_name ] for cons in constraints: # Need to use sqlalchemy-migrate UniqueConstraint cons = UniqueConstraint(*[c.name for c in cons.columns], name=cons.name, table=table) cons.drop()
def _pre_upgrade_273(self, engine): if engine.name != 'sqlite': return # Drop a variety of unique constraints to ensure that the script # properly readds them back for table_name, constraint_name in [ ('compute_nodes', 'uniq_compute_nodes0' 'host0hypervisor_hostname'), ('fixed_ips', 'uniq_fixed_ips0address0deleted'), ('instance_info_caches', 'uniq_instance_info_caches0' 'instance_uuid'), ('instance_type_projects', 'uniq_instance_type_projects0' 'instance_type_id0project_id0' 'deleted'), ('pci_devices', 'uniq_pci_devices0compute_node_id0' 'address0deleted'), ('virtual_interfaces', 'uniq_virtual_interfaces0' 'address0deleted')]: table = oslodbutils.get_table(engine, table_name) constraints = [c for c in table.constraints if c.name == constraint_name] for cons in constraints: # Need to use sqlalchemy-migrate UniqueConstraint cons = UniqueConstraint(*[c.name for c in cons.columns], name=cons.name, table=table) cons.drop()
def upgrade(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine hosts_table = Table('hosts', meta, autoload=True) failover_segments = Table('failover_segments', meta, autoload=True) # NOTE(Dinesh_Bhor) We need to drop foreign keys first because unique # constraints that we want to delete depend on them. So drop the fk and # recreate it again after unique constraint deletion. cons_fk = ForeignKeyConstraint([hosts_table.c.failover_segment_id], [failover_segments.c.uuid], name="fk_failover_segments_uuid") cons_fk.drop(engine=migrate_engine) cons_unique = UniqueConstraint('failover_segment_id', 'name', 'deleted', name='uniq_host0name0deleted', table=hosts_table) cons_unique.drop(engine=migrate_engine) # Create an updated unique constraint updated_cons_unique = UniqueConstraint('name', 'deleted', name='uniq_host0name0deleted', table=hosts_table) cons_fk.create() updated_cons_unique.create()
def upgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True)) for table_name in TABLES) if migrate_engine.name != 'sqlite': for table_name, indexes in INDEXES.items(): table = load_tables[table_name] for column, ref_table_name, ref_column_name in indexes: ref_table = load_tables[ref_table_name] params = { 'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]] } if (migrate_engine.name == "mysql" and table_name != 'alarm_history'): params['name'] = "_".join(('fk', table_name, column)) elif (migrate_engine.name == "postgresql" and table_name == "sample"): # The fk contains the old table name params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.drop() sourceassoc = load_tables['sourceassoc'] if migrate_engine.name != 'sqlite': idx = sa.Index('idx_su', sourceassoc.c.source_id, sourceassoc.c.user_id) idx.drop(bind=migrate_engine) idx = sa.Index('idx_sp', sourceassoc.c.source_id, sourceassoc.c.project_id) idx.drop(bind=migrate_engine) params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id'} uc = UniqueConstraint('sample_id', table=sourceassoc, **params) uc.create() params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id0user_id'} uc = UniqueConstraint('sample_id', 'user_id', table=sourceassoc, **params) uc.drop() sourceassoc.c.user_id.drop() sourceassoc.c.project_id.drop() for table_name in TABLES_DROP: sa.Table(table_name, meta, autoload=True).drop()
def upgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True)) for table_name in TABLES) if migrate_engine.name != 'sqlite': for table_name, indexes in INDEXES.items(): table = load_tables[table_name] for column, ref_table_name, ref_column_name in indexes: ref_table = load_tables[ref_table_name] params = {'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]]} if migrate_engine.name == "mysql" and \ table_name != 'alarm_history': params['name'] = "_".join(('fk', table_name, column)) elif migrate_engine.name == "postgresql" and \ table_name == "sample": # The fk contains the old table name params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.drop() sourceassoc = load_tables['sourceassoc'] if migrate_engine.name != 'sqlite': idx = sa.Index('idx_su', sourceassoc.c.source_id, sourceassoc.c.user_id) idx.drop(bind=migrate_engine) idx = sa.Index('idx_sp', sourceassoc.c.source_id, sourceassoc.c.project_id) idx.drop(bind=migrate_engine) params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id'} uc = UniqueConstraint('sample_id', table=sourceassoc, **params) uc.create() params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id0user_id'} uc = UniqueConstraint('sample_id', 'user_id', table=sourceassoc, **params) uc.drop() sourceassoc.c.user_id.drop() sourceassoc.c.project_id.drop() for table_name in TABLES_DROP: sa.Table(table_name, meta, autoload=True).drop()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine compute_nodes = Table('compute_nodes', meta, autoload=True) ukey = UniqueConstraint( 'host', 'hypervisor_hostname', 'deleted', table=compute_nodes, name="uniq_compute_nodes0host0hypervisor_hostname0deleted") ukey.drop() ukey = UniqueConstraint('host', 'hypervisor_hostname', table=compute_nodes, name="uniq_compute_nodes0host0hypervisor_hostname") ukey.create()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # Remove the new column compute_nodes = Table("compute_nodes", meta, autoload=True) shadow_compute_nodes = Table("shadow_compute_nodes", meta, autoload=True) ukey = UniqueConstraint( "host", "hypervisor_hostname", table=compute_nodes, name="uniq_compute_nodes0host0hypervisor_hostname" ) ukey.drop() compute_nodes.drop_column("host") shadow_compute_nodes.drop_column("host")
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # Remove the new column compute_nodes = Table('compute_nodes', meta, autoload=True) shadow_compute_nodes = Table('shadow_compute_nodes', meta, autoload=True) ukey = UniqueConstraint('host', 'hypervisor_hostname', table=compute_nodes, name="uniq_compute_nodes0host0hypervisor_hostname") ukey.drop() compute_nodes.drop_column('host') shadow_compute_nodes.drop_column('host')
def downgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) user = sa.Table( 'user', meta, sa.Column('id', sa.String(255), primary_key=True), mysql_engine='InnoDB', mysql_charset='utf8', ) project = sa.Table( 'project', meta, sa.Column('id', sa.String(255), primary_key=True), mysql_engine='InnoDB', mysql_charset='utf8', ) tables = [project, user] for i in sorted(tables): i.create() load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True)) for table_name in TABLES) # Restore the sourceassoc columns and constraints sourceassoc = load_tables['sourceassoc'] user_id = sa.Column('user_id', sa.String(255)) project_id = sa.Column('project_id', sa.String(255)) sourceassoc.create_column(user_id) sourceassoc.create_column(project_id) if migrate_engine.name != 'sqlite': params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id0user_id'} uc = UniqueConstraint('sample_id', 'user_id', table=sourceassoc, **params) uc.create() params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id'} uc = UniqueConstraint('sample_id', table=sourceassoc, **params) uc.drop() idx = sa.Index('idx_su', sourceassoc.c.source_id, sourceassoc.c.user_id) idx.create(bind=migrate_engine) idx = sa.Index('idx_sp', sourceassoc.c.source_id, sourceassoc.c.project_id) idx.create(bind=migrate_engine) # Restore the user/project columns and constraints in all tables for table_name, indexes in INDEXES.items(): table = load_tables[table_name] for column, ref_table_name, ref_column_name in indexes: ref_table = load_tables[ref_table_name] c = getattr(Alias(table).c, column) except_q = exists([getattr(ref_table.c, ref_column_name)]) q = select([c]).where(and_(c != sa.null(), not_(except_q))) q = q.distinct() # NOTE(sileht): workaround for # https://bitbucket.org/zzzeek/sqlalchemy/ # issue/3044/insert-from-select-union_all q.select = lambda: q sql_ins = ref_table.insert().from_select( [getattr(ref_table.c, ref_column_name)], q) try: migrate_engine.execute(sql_ins) except TypeError: # from select is empty pass if migrate_engine.name != 'sqlite': params = {'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]]} if migrate_engine.name == "mysql" and \ table_name != 'alarm_history': params['name'] = "_".join(('fk', table_name, column)) elif migrate_engine.name == "postgresql" and \ table_name == "sample": # The fk contains the old table name params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.create()
def downgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) user = sa.Table( 'user', meta, sa.Column('id', sa.String(255), primary_key=True), mysql_engine='InnoDB', mysql_charset='utf8', ) project = sa.Table( 'project', meta, sa.Column('id', sa.String(255), primary_key=True), mysql_engine='InnoDB', mysql_charset='utf8', ) tables = [project, user] for i in sorted(tables): i.create() load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True)) for table_name in TABLES) # Restore the sourceassoc columns and constraints sourceassoc = load_tables['sourceassoc'] user_id = sa.Column('user_id', sa.String(255)) project_id = sa.Column('project_id', sa.String(255)) sourceassoc.create_column(user_id) sourceassoc.create_column(project_id) if migrate_engine.name != 'sqlite': params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id0user_id'} uc = UniqueConstraint('sample_id', 'user_id', table=sourceassoc, **params) uc.create() params = {} if migrate_engine.name == "mysql": params = {'name': 'uniq_sourceassoc0sample_id'} uc = UniqueConstraint('sample_id', table=sourceassoc, **params) uc.drop() idx = sa.Index('idx_su', sourceassoc.c.source_id, sourceassoc.c.user_id) idx.create(bind=migrate_engine) idx = sa.Index('idx_sp', sourceassoc.c.source_id, sourceassoc.c.project_id) idx.create(bind=migrate_engine) # Restore the user/project columns and constraints in all tables for table_name, indexes in INDEXES.items(): table = load_tables[table_name] for column, ref_table_name, ref_column_name in indexes: ref_table = load_tables[ref_table_name] c = getattr(Alias(table).c, column) except_q = exists([getattr(ref_table.c, ref_column_name)]) q = select([c]).where(and_(c != sa.null(), not_(except_q))) q = q.distinct() # NOTE(sileht): workaround for # https://bitbucket.org/zzzeek/sqlalchemy/ # issue/3044/insert-from-select-union_all q.select = lambda: q sql_ins = ref_table.insert().from_select( [getattr(ref_table.c, ref_column_name)], q) try: migrate_engine.execute(sql_ins) except TypeError: # from select is empty pass if migrate_engine.name != 'sqlite': params = { 'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]] } if migrate_engine.name == "mysql" and \ table_name != 'alarm_history': params['name'] = "_".join(('fk', table_name, column)) elif migrate_engine.name == "postgresql" and \ table_name == "sample": # The fk contains the old table name params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.create()