def downgrade(migrate_engine): """Remove volume_type_rate_limit table.""" meta = MetaData() meta.bind = migrate_engine qos_specs = Table('quality_of_service_specs', meta, autoload=True) if migrate_engine.name == 'mysql': # NOTE(alanmeadows): MySQL Cannot drop column qos_specs_id # until the foreign key volumes_types_ibfk_1 is removed. We # remove the foreign key first, and then we drop the column. table = Table('volume_types', meta, autoload=True) ref_table = Table('volume_types', meta, autoload=True) params = {'columns': [table.c['qos_specs_id']], 'refcolumns': [ref_table.c['id']], 'name': 'volume_types_ibfk_1'} fkey = ForeignKeyConstraint(**params) fkey.drop() volume_types = Table('volume_types', meta, autoload=True) qos_specs_id = Column('qos_specs_id', String(36)) volume_types.drop_column(qos_specs_id) qos_specs.drop()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine fk_name = None if migrate_engine.name == 'mysql': fk_name = 'reservations_ibfk_1' elif migrate_engine.name == 'postgresql': fk_name = 'reservations_usage_id_fkey' # NOTE: MySQL and PostgreSQL Cannot drop the quota_usages table # until the foreign key is removed. We remove the foreign key first, # and then we drop the table. table = Table('reservations', meta, autoload=True) ref_table = Table('reservations', meta, autoload=True) params = {'columns': [table.c['usage_id']], 'refcolumns': [ref_table.c['id']], 'name': fk_name} if fk_name: fkey = ForeignKeyConstraint(**params) fkey.drop() quota_classes = Table('quota_classes', meta, autoload=True) quota_classes.drop() quota_usages = Table('quota_usages', meta, autoload=True) quota_usages.drop() reservations = Table('reservations', meta, autoload=True) reservations.drop()
def upgrade(migrate_engine): meta = MetaData(migrate_engine) trait_type = Table( 'trait_type', meta, Column('id', Integer, primary_key=True), Column('desc', String(255)), Column('data_type', Integer), UniqueConstraint('desc', 'data_type', name="tt_unique") ) trait = Table('trait', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) trait_type.create(migrate_engine) # Trait type extracts data from Trait and Unique name. # We take all trait names from Unique Name, and data types # from Trait. We then remove dtype and name from trait, and # remove the name field. conn = migrate_engine.connect() sql = ("INSERT INTO trait_type " "SELECT unique_name.id, unique_name.key, trait.t_type FROM trait " "INNER JOIN unique_name " "ON trait.name_id = unique_name.id " "GROUP BY unique_name.id, unique_name.key, trait.t_type") conn.execute(sql) conn.close() # Now we need to drop the foreign key constraint, rename # the trait.name column, and re-add a new foreign # key constraint params = {'columns': [trait.c.name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = "trait_ibfk_1" # foreign key to the unique name table fkey = ForeignKeyConstraint(**params) fkey.drop() Column('trait_type_id', Integer).create(trait) # Move data from name_id column into trait_type_id column query = select([trait.c.id, trait.c.name_id]) for key, value in migration.paged(query): trait.update().where(trait.c.id == key)\ .values({"trait_type_id": value}).execute() trait.c.name_id.drop() params = {'columns': [trait.c.trait_type_id], 'refcolumns': [trait_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'trait_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.create() # Drop the t_type column to data_type. trait.c.t_type.drop() # Finally, drop the unique_name table - we don't need it # anymore. unique_name.drop()
def downgrade(migrate_engine): metadata.bind = migrate_engine # Load existing tables metadata.reflect() # NOTE: all new data added in the upgrade method is eliminated here via table drops # Drop 1 foreign key constraint from the metadata_file table try: MetadataFile_table = Table( "metadata_file", metadata, autoload=True ) except NoSuchTableError: MetadataFile_table = None log.debug( "Failed loading table metadata_file" ) try: LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True ) except NoSuchTableError: LibraryDatasetDatasetAssociation_table = None log.debug( "Failed loading table library_dataset_dataset_association" ) if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None: try: cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id], [LibraryDatasetDatasetAssociation_table.c.id], name='metadata_file_lda_id_fkey' ) # Drop the constraint cons.drop() except Exception, e: log.debug( "Dropping foreign key constraint 'metadata_file_lda_id_fkey' from table 'metadata_file' failed: %s" % ( str( e ) ) )
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine compute_nodes = Table('compute_nodes', meta, autoload=True) shadow_compute_nodes = Table('shadow_compute_nodes', meta, autoload=True) services = Table('services', meta, autoload=True) _correct_sqlite_unique_constraints(migrate_engine, compute_nodes) # Make the service_id column nullable compute_nodes.c.service_id.alter(nullable=True) shadow_compute_nodes.c.service_id.alter(nullable=True) for fk in compute_nodes.foreign_keys: if fk.column == services.c.id: # Delete the FK fkey = ForeignKeyConstraint(columns=[compute_nodes.c.service_id], refcolumns=[services.c.id], name=fk.name) fkey.drop() break for index in compute_nodes.indexes: if 'service_id' in index.columns: # Delete the nested index which was created by the FK index.drop() break
def cascade_fkeys(metadata, restore=False): """ Sets all fkeys to cascade on update """ for table_name, table in metadata.tables.items(): for fkey in list(table.foreign_keys): if restore: if fkey.constraint.name in cascade_fkeys.fkey_onupdate_restore: onupdate = cascade_fkeys.fkey_onupdate_restore[ fkey.constraint.name] else: continue else: cascade_fkeys.fkey_onupdate_restore[fkey.constraint.name] = \ fkey.constraint.onupdate onupdate = "CASCADE" params = { 'columns': fkey.constraint.columns, 'refcolumns': [fkey.column], 'name': fkey.constraint.name, 'onupdate': fkey.constraint.onupdate, 'ondelete': fkey.constraint.ondelete, 'deferrable': fkey.constraint.deferrable, 'initially': fkey.constraint.initially, 'table': table } fkey_constraint = ForeignKeyConstraint(**params) fkey_constraint.drop() params['onupdate'] = onupdate fkey_constraint = ForeignKeyConstraint(**params) fkey_constraint.create()
def downgrade(migrate_engine): if migrate_engine.name == 'sqlite': return meta = MetaData(bind=migrate_engine) for table_name, ref, child in TABLES: table = Table(table_name, meta, autoload=True) column_name, ref_table_name, ref_column_name = ref column = table.c[column_name] ref_table = Table(ref_table_name, meta, autoload=True) ref_column = ref_table.c[ref_column_name] params = {'columns': [column], 'refcolumns': [ref_column]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', table_name, column_name)) with migrate_engine.begin(): fkey = ForeignKeyConstraint(**params) fkey.drop() with migrate_engine.begin(): restore_rows(migrate_engine, meta, table_name) # compute_node_stats has a missing foreign key and is a child of # of compute_nodes. Don't bother processing it as a child since # only want to restore the dump once if child and table_name != 'compute_nodes': child_table_name, child_column_name, child_ref_column_name = child with migrate_engine.begin(): restore_rows(migrate_engine, meta, child_table_name)
def downgrade(migrate_engine): if migrate_engine.name == "sqlite": return meta = MetaData(bind=migrate_engine) storage_pools = Table("storage_pools", meta, autoload=True) storage_groups = Table("storage_groups", meta, autolaod=True) params = {"columns": [storage_pools.c.primary_storage_group_id], "refcolumns": [storage_groups.c.id]} fkey = ForeignKeyConstraint(**params) fkey.drop()
def upgrade(migrate_engine): meta = MetaData(bind=migrate_engine) event_type = Table( 'event_type', meta, Column('id', Integer, primary_key=True), Column('desc', String(255), unique=True), mysql_engine='InnoDB', mysql_charset='utf8', ) event_type.create() event = Table('event', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) # Event type is a specialization of Unique name, so # we insert into the event_type table all the distinct # unique names from the event.unique_name field along # with the key from the unique_name table, and # then rename the event.unique_name field to event.event_type conn = migrate_engine.connect() sql = ("INSERT INTO event_type " "SELECT unique_name.id, unique_name.key FROM event " "INNER JOIN unique_name " "ON event.unique_name_id = unique_name.id " "GROUP BY unique_name.id") conn.execute(sql) conn.close() # Now we need to drop the foreign key constraint, rename # the event.unique_name column, and re-add a new foreign # key constraint params = {'columns': [event.c.unique_name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = "event_ibfk_1" fkey = ForeignKeyConstraint(**params) fkey.drop() Column('event_type_id', Integer).create(event) # Move data from unique_name_id column into event_type_id column # and delete the entry from the unique_name table query = select([event.c.id, event.c.unique_name_id]) for key, value in migration.paged(query): event.update().where(event.c.id == key)\ .values({"event_type_id": value}).execute() unique_name.delete()\ .where(unique_name.c.id == key).execute() params = {'columns': [event.c.event_type_id], 'refcolumns': [event_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'event_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.create() event.c.unique_name_id.drop()
def downgrade(migrate_engine): """Convert volume_type from UUID back to int.""" meta = MetaData() meta.bind = migrate_engine volumes = Table("volumes", meta, autoload=True) volume_types = Table("volume_types", meta, autoload=True) extra_specs = Table("volume_type_extra_specs", meta, autoload=True) fkey_remove_list = [volumes.c.volume_type_id, volume_types.c.id, extra_specs.c.volume_type_id] for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.drop() except Exception: if migrate_engine.url.get_dialect().name.startswith("sqlite"): pass else: raise vtype_list = list(volume_types.select().execute()) new_id = 1 for t in vtype_list: volumes.update().where(volumes.c.volume_type_id == t["id"]).values(volume_type_id=new_id).execute() extra_specs.update().where(extra_specs.c.volume_type_id == t["id"]).values(volume_type_id=new_id).execute() volume_types.update().where(volume_types.c.id == t["id"]).values(id=new_id).execute() new_id += 1 volumes.c.volume_type_id.alter(Integer) volume_types.c.id.alter(Integer) extra_specs.c.volume_type_id.alter(Integer) for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.create() LOG.info("Created foreign key %s" % fkey_name) except Exception: if migrate_engine.url.get_dialect().name.startswith("sqlite"): pass else: raise
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta.bind = migrate_engine tt = Table('proc_SelectedHistoriesTable', meta, autoload=True) con = ForeignKeyConstraint([tt.c.selected_arar_id], [t.c.id]) con.drop() t.drop() th.drop() tt.c.selected_arar_id.drop()
def _remove_foreign_key_constraints(engine, meta, table_name): inspector = reflection.Inspector.from_engine(engine) for fk in inspector.get_foreign_keys(table_name): source_table = Table(table_name, meta, autoload=True) target_table = Table(fk['referred_table'], meta, autoload=True) fkey = ForeignKeyConstraint( columns=_get_columns(source_table, fk['constrained_columns']), refcolumns=_get_columns(target_table, fk['referred_columns']), name=fk['name']) fkey.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 upgrade(migrate_engine): if migrate_engine.name == "sqlite": return meta = MetaData(bind=migrate_engine) load_tables = dict((table_name, Table(table_name, meta, autoload=True)) for table_name in 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] params = {"columns": [table.c[column]], "refcolumns": [ref_table.c[ref_column_name]]} if migrate_engine.name == "mysql": params["name"] = "_".join(("fk", table_name, column)) fkey = ForeignKeyConstraint(**params) fkey.drop()
def downgrade(migrate_engine): meta = MetaData(bind=migrate_engine) load_tables = dict((table_name, Table(table_name, meta, autoload=True)) for table_name in 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] params = {'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', table_name, column)) fkey = ForeignKeyConstraint(**params) fkey.drop()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine fk_name = None if migrate_engine.name == 'mysql': fk_name = 'reservations_ibfk_1' elif migrate_engine.name == 'postgresql': fk_name = 'reservations_usage_id_fkey' # NOTE: MySQL and PostgreSQL Cannot drop the quota_usages table # until the foreign key is removed. We remove the foreign key first, # and then we drop the table. table = Table('reservations', meta, autoload=True) ref_table = Table('reservations', meta, autoload=True) params = {'columns': [table.c['usage_id']], 'refcolumns': [ref_table.c['id']], 'name': fk_name} if fk_name: try: fkey = ForeignKeyConstraint(**params) fkey.drop() except Exception: msg = _LE("Dropping foreign key %s failed.") LOG.error(msg, fk_name) quota_classes = Table('quota_classes', meta, autoload=True) try: quota_classes.drop() except Exception: LOG.error(_LE("quota_classes table not dropped")) raise quota_usages = Table('quota_usages', meta, autoload=True) try: quota_usages.drop() except Exception: LOG.error(_LE("quota_usages table not dropped")) raise reservations = Table('reservations', meta, autoload=True) try: reservations.drop() except Exception: LOG.error(_LE("reservations table not dropped")) raise
def downgrade(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) stack = sqlalchemy.Table('stack', meta, autoload=True) if migrate_engine.name == 'sqlite': _downgrade_sqlite(migrate_engine) else: raw_template = sqlalchemy.Table('raw_template', meta, autoload=True) fkey = ForeignKeyConstraint(columns=[stack.c.prev_raw_template_id], refcolumns=[raw_template.c.id], name='prev_raw_template_ref') fkey.drop() stack.c.prev_raw_template_id.drop() stack.c.current_traversal.drop() stack.c.current_deps.drop()
def downgrade(migrate_engine): meta = MetaData(bind=migrate_engine) meta.bind = migrate_engine port_table = Table('port', meta, autoload=True) subnet_table = Table('subnet', meta, autoload=True) for fk in port_table.foreign_keys: if fk.column == subnet_table.c.id: # Delete the FK fkey = ForeignKeyConstraint(columns=[port_table.c.subnet_id], refcolumns=[subnet_table.c.id], name=fk.name) fkey.drop() break port_table.c.subnet_id.drop()
def downgrade(migrate_engine): print("037 downgrade") meta = MetaData() meta.bind = migrate_engine versions = Table('versions', meta, autoload=True) targets = Table('targets', meta, autoload=True) params = {'columns': [versions.c['target_id']], 'refcolumns': [targets.c['id']], 'name': 'versions_ibfk_1'} foreign = ForeignKeyConstraint(**params) foreign.drop() versions.drop_column(target_id) tables = [define_target_status_table(meta), define_targets_table(meta), define_host_versions_table(meta)] drop_tables(tables)
def upgrade(migrate_engine): """Add UUID primary key column to encryption.""" meta = MetaData() meta.bind = migrate_engine encryptions = Table('encryption', meta, autoload=True) encryption_id_column_kwargs = {} if migrate_engine.name == 'ibm_db_sa': # NOTE(junxiebj): DB2 10.5 doesn't support primary key # constraints over nullable columns, so we have to # make the column non-nullable in the DB2 case. encryption_id_column_kwargs['nullable'] = False encryption_id = Column('encryption_id', String(36), **encryption_id_column_kwargs) encryptions.create_column(encryption_id) encryption_items = list(encryptions.select().execute()) for item in encryption_items: encryptions.update().\ where(encryptions.c.volume_type_id == item['volume_type_id']).\ values(encryption_id=str(uuid.uuid4())).execute() # NOTE (e0ne): need to drop FK first for MySQL if migrate_engine.name == 'mysql': ref_table = Table('volume_types', meta, autoload=True) params = {'columns': [encryptions.c['volume_type_id']], 'refcolumns': [ref_table.c['id']], 'name': 'encryption_ibfk_1'} volume_type_fk = ForeignKeyConstraint(**params) volume_type_fk.drop() try: volume_type_pk = PrimaryKeyConstraint('volume_type_id', table=encryptions) volume_type_pk.drop() except Exception: # NOTE (e0ne): SQLite doesn't support 'drop constraint' statament if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise pkey = PrimaryKeyConstraint(encryptions.columns.encryption_id) pkey.create()
def downgrade(migrate_engine): if migrate_engine.name == 'mysql': # NOTE(jhesketh): MySQL Cannot drop index # 'uniq_aggregate_metadata0aggregate_id0key0deleted': needed in a # foreign key constraint. So we'll remove the fkey constraint on the # aggregate_metadata table and add it back after the index is # downgraded. meta = MetaData(bind=migrate_engine) table = Table('aggregate_metadata', meta, autoload=True) ref_table = Table('aggregates', meta, autoload=True) params = {'columns': [table.c['aggregate_id']], 'refcolumns': [ref_table.c['id']], 'name': 'aggregate_metadata_ibfk_1'} fkey = ForeignKeyConstraint(**params) fkey.drop() utils.drop_unique_constraint(migrate_engine, TABLE_NAME, UC_NAME, *COLUMNS) if migrate_engine.name == 'mysql': fkey.create()
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine registered_limit_table = sql.Table('registered_limit', meta, autoload=True) service_table = sql.Table('service', meta, autoload=True) region_table = sql.Table('region', meta, autoload=True) inspector = sql.engine.reflection.Inspector.from_engine(migrate_engine) for fk in inspector.get_foreign_keys('registered_limit'): if fk['referred_table'] == 'service': fkey = ForeignKeyConstraint([registered_limit_table.c.service_id], [service_table.c.id], name=fk['name']) fkey.drop() else: fkey = ForeignKeyConstraint([registered_limit_table.c.region_id], [region_table.c.id], name=fk['name']) fkey.drop()
def downgrade(migrate_engine): if migrate_engine.name == 'mysql': # NOTE(jhesketh): MySQL Cannot drop index # migrations_instance_uuid_and_status_idx needed in a foreign # key constraint. So we'll remove the fkey constraint on the # aggregate_metadata table and add it back after the indexes are # downgraded. meta = MetaData(bind=migrate_engine) table = Table('migrations', meta, autoload=True) ref_table = Table('instances', meta, autoload=True) params = {'columns': [table.c['instance_uuid']], 'refcolumns': [ref_table.c['uuid']]} if migrate_engine.name == 'mysql': params['name'] = 'fk_migrations_instance_uuid' fkey = ForeignKeyConstraint(**params) fkey.drop() utils.modify_indexes(migrate_engine, data, upgrade=False) if migrate_engine.name == 'mysql': fkey.create()
def upgrade(migrate_engine): meta = sqlalchemy.MetaData() meta.bind = migrate_engine resource_data = sqlalchemy.Table('resource_data', meta, autoload=True) resource = sqlalchemy.Table('resource', meta, autoload=True) for fk in resource_data.foreign_keys: if fk.column == resource.c.id: # delete the existing fk # and create with ondelete cascade and a proper name existing_fkey = ForeignKeyConstraint( columns=[resource_data.c.resource_id], refcolumns=[resource.c.id], name=fk.name) existing_fkey.drop() fkey = ForeignKeyConstraint( columns=[resource_data.c.resource_id], refcolumns=[resource.c.id], name="fk_resource_id", ondelete='CASCADE') fkey.create() break
def downgrade(migrate_engine): meta = MetaData(bind=migrate_engine) event_type = Table('event_type', meta, autoload=True) event = Table('event', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) # Re-insert the event type table records into the old # unique_name table. conn = migrate_engine.connect() sql = ("INSERT INTO unique_name " "SELECT event_type.id, event_type.desc FROM event_type") conn.execute(sql) conn.close() # Drop the foreign key constraint to event_type, drop the # event_type table, rename the event.event_type column to # event.unique_name, and re-add the old foreign # key constraint params = {'columns': [event.c.event_type_id], 'refcolumns': [event_type.c.id]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'event_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.drop() event_type.drop() Column('unique_name_id', Integer).create(event) # Move data from event_type_id column to unique_name_id column query = select([event.c.id, event.c.event_type_id]) for key, value in migration.paged(query): event.update().where(event.c.id == key)\ .values({"unique_name_id": value}).execute() event.c.event_type_id.drop() params = {'columns': [event.c.unique_name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'event_ibfk_1' fkey = ForeignKeyConstraint(**params) fkey.create()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine if migrate_engine.name == 'mysql': # NOTE(jsbryant): MySQL Cannot drop the quota_usages table # until the foreign key reservations_ibfk_1 is removed. We # remove the foreign key first, and then we drop the table. table = Table('reservations', meta, autoload=True) ref_table = Table('reservations', meta, autoload=True) params = {'columns': [table.c['usage_id']], 'refcolumns': [ref_table.c['id']], 'name': 'reservations_ibfk_1'} try: fkey = ForeignKeyConstraint(**params) fkey.drop() except Exception: LOG.error(_("Dropping foreign key reservations_ibfk_1 failed.")) quota_classes = Table('quota_classes', meta, autoload=True) try: quota_classes.drop() except Exception: LOG.error(_("quota_classes table not dropped")) raise quota_usages = Table('quota_usages', meta, autoload=True) try: quota_usages.drop() except Exception: LOG.error(_("quota_usages table not dropped")) raise reservations = Table('reservations', meta, autoload=True) try: reservations.drop() except Exception: LOG.error(_("reservations table not dropped")) raise
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) # drop foreign keys 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]]} fk_table_name = table_name if migrate_engine.name == "mysql": params['name'] = "_".join(('fk', fk_table_name, column)) elif (migrate_engine.name == "postgresql" and table_name == 'sample'): # fk was not renamed in script 030 params['name'] = "_".join(('meter', column, 'fkey')) fkey = ForeignKeyConstraint(**params) fkey.drop() # create source field in sample sample = load_tables['sample'] sample.create_column(sa.Column('source_id', sa.String(255))) # move source values to samples sourceassoc = load_tables['sourceassoc'] query = (sa.select([sourceassoc.c.sample_id, sourceassoc.c.source_id]). where(sourceassoc.c.sample_id.isnot(None))) for sample_id, source_id in migration.paged(query): (sample.update().where(sample_id == sample.c.id). values({'source_id': source_id}).execute()) # drop tables for table_name in DROP_TABLES: sa.Table(table_name, meta, autoload=True).drop()
def downgrade(migrate_engine): metadata.bind = migrate_engine NewWorkflowStepConnection_table = Table("workflow_step_connection", metadata, autoload=True) for fkc in NewWorkflowStepConnection_table.foreign_key_constraints: mfkc = MigrateForeignKeyConstraint([_.parent for _ in fkc.elements], [_.column for _ in fkc.elements], name=fkc.name) try: mfkc.drop() except Exception: log.exception("Dropping foreign key constraint '%s' from table '%s' failed", mfkc.name, NewWorkflowStepConnection_table) for index in NewWorkflowStepConnection_table.indexes: drop_index(index, NewWorkflowStepConnection_table) NewWorkflowStepConnection_table.rename("workflow_step_connection_predowngrade145") # Try to deregister that table to work around some caching problems it seems. NewWorkflowStepConnection_table.deregister() metadata._remove_table("workflow_step_connection", metadata.schema) metadata.reflect() OldWorkflowStepConnection_table = Table( "workflow_step_connection", metadata, Column("id", Integer, primary_key=True), Column("output_step_id", Integer, ForeignKey("workflow_step.id"), index=True), Column("input_step_id", Integer, ForeignKey("workflow_step.id"), index=True), Column("output_name", TEXT), Column("input_name", TEXT), Column("input_subworkflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True), ) create_table(OldWorkflowStepConnection_table) insert_step_connections_cmd = \ "INSERT INTO workflow_step_connection (output_step_id, input_step_id, output_name, input_name, input_subworkflow_step_id) " + \ "SELECT wsc.output_step_id, wsi.workflow_step_id, wsc.output_name, wsi.name, wsc.input_subworkflow_step_id " + \ "FROM workflow_step_connection_predowngrade145 AS wsc JOIN workflow_step_input AS wsi ON wsc.input_step_input_id = wsi.id ORDER BY wsc.id" migrate_engine.execute(insert_step_connections_cmd) for table in (NewWorkflowStepConnection_table, WorkflowStepInput_table): drop_table(table)
def downgrade(migrate_engine): """Remove volume_type_rate_limit table.""" meta = MetaData() meta.bind = migrate_engine qos_specs = Table('quality_of_service_specs', meta, autoload=True) if migrate_engine.name == 'mysql': # NOTE(alanmeadows): MySQL Cannot drop column qos_specs_id # until the foreign key volumes_types_ibfk_1 is removed. We # remove the foreign key first, and then we drop the column. table = Table('volume_types', meta, autoload=True) ref_table = Table('volume_types', meta, autoload=True) params = {'columns': [table.c['qos_specs_id']], 'refcolumns': [ref_table.c['id']], 'name': 'volume_types_ibfk_1'} try: fkey = ForeignKeyConstraint(**params) fkey.drop() except Exception: LOG.error(_LE("Dropping foreign key volume_types_ibfk_1 failed")) volume_types = Table('volume_types', meta, autoload=True) qos_specs_id = Column('qos_specs_id', String(36)) try: volume_types.drop_column(qos_specs_id) except Exception: LOG.error(_LE("Dropping qos_specs_id column failed.")) raise try: qos_specs.drop() except Exception: LOG.error(_LE("Dropping quality_of_service_specs table failed.")) raise
def downgrade(migrate_engine): if migrate_engine.name == 'mysql': # NOTE(jhesketh): MySQL Cannot drop index # migrations_instance_uuid_and_status_idx needed in a foreign # key constraint. So we'll remove the fkey constraint on the # aggregate_metadata table and add it back after the indexes are # downgraded. meta = MetaData(bind=migrate_engine) table = Table('migrations', meta, autoload=True) ref_table = Table('instances', meta, autoload=True) params = { 'columns': [table.c['instance_uuid']], 'refcolumns': [ref_table.c['uuid']] } if migrate_engine.name == 'mysql': params['name'] = 'fk_migrations_instance_uuid' fkey = ForeignKeyConstraint(**params) fkey.drop() utils.modify_indexes(migrate_engine, data, upgrade=False) if migrate_engine.name == 'mysql': fkey.create()
def downgrade(migrate_engine): if migrate_engine.name == 'sqlite': return meta = MetaData(bind=migrate_engine) load_tables = dict((table_name, Table(table_name, meta, autoload=True)) for table_name in 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] params = {'columns': [table.c[column]], 'refcolumns': [ref_table.c[ref_column_name]]} if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', table_name, column)) with migrate_engine.begin(): fkey = ForeignKeyConstraint(**params) fkey.drop() with migrate_engine.begin(): # Restore data that had been dropped. table_dump_name = 'dump_' + table_name table_dump = Table(table_dump_name, meta, autoload=True) sql = utils.InsertFromSelect(table, table_dump.select()) migrate_engine.execute(sql) table_dump.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 downgrade(migrate_engine): """Convert volume_type from UUID back to int.""" meta = MetaData() meta.bind = migrate_engine volumes = Table('volumes', meta, autoload=True) volume_types = Table('volume_types', meta, autoload=True) extra_specs = Table('volume_type_extra_specs', meta, autoload=True) fkey_remove_list = [volumes.c.volume_type_id, volume_types.c.id, extra_specs.c.volume_type_id] for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.drop() except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise vtype_list = list(volume_types.select().execute()) new_id = 1 for t in vtype_list: volumes.update().\ where(volumes.c.volume_type_id == t['id']).\ values(volume_type_id=new_id).execute() extra_specs.update().\ where(extra_specs.c.volume_type_id == t['id']).\ values(volume_type_id=new_id).execute() volume_types.update().\ where(volume_types.c.id == t['id']).\ values(id=new_id).execute() new_id += 1 if migrate_engine.name == 'postgresql': # NOTE(e0ne): PostgreSQL can't cast string to int automatically table_column_pairs = [('volumes', 'volume_type_id'), ('volume_types', 'id'), ('volume_type_extra_specs', 'volume_type_id')] sql = 'ALTER TABLE {0} ALTER COLUMN {1} ' + \ 'TYPE INTEGER USING {1}::numeric' for table, column in table_column_pairs: migrate_engine.execute(sql.format(table, column)) else: volumes.c.volume_type_id.alter(Integer) volume_types.c.id.alter(Integer) extra_specs.c.volume_type_id.alter(Integer) for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.create() except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise
def upgrade(migrate_engine): """Convert volume_type_id to UUID.""" meta = MetaData() meta.bind = migrate_engine volumes = Table('volumes', meta, autoload=True) volume_types = Table('volume_types', meta, autoload=True) extra_specs = Table('volume_type_extra_specs', meta, autoload=True) fkey_remove_list = [volumes.c.volume_type_id, volume_types.c.id, extra_specs.c.volume_type_id] for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.drop() except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise volumes.c.volume_type_id.alter(String(36)) volume_types.c.id.alter(String(36)) extra_specs.c.volume_type_id.alter(String(36)) vtype_list = list(volume_types.select().execute()) for t in vtype_list: new_id = str(uuid.uuid4()) volumes.update().\ where(volumes.c.volume_type_id == t['id']).\ values(volume_type_id=new_id).execute() extra_specs.update().\ where(extra_specs.c.volume_type_id == t['id']).\ values(volume_type_id=new_id).execute() volume_types.update().\ where(volume_types.c.id == t['id']).\ values(id=new_id).execute() for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.create() except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise
def upgrade(migrate_engine): meta = MetaData(migrate_engine) trait_type = Table( 'trait_type', meta, Column('id', Integer, primary_key=True), Column('desc', String(255)), Column('data_type', Integer), UniqueConstraint('desc', 'data_type', name="tt_unique"), mysql_engine='InnoDB', mysql_charset='utf8', ) trait = Table('trait', meta, autoload=True) unique_name = Table('unique_name', meta, autoload=True) trait_type.create(migrate_engine) # Trait type extracts data from Trait and Unique name. # We take all trait names from Unique Name, and data types # from Trait. We then remove dtype and name from trait, and # remove the name field. conn = migrate_engine.connect() sql = ("INSERT INTO trait_type " "SELECT unique_name.id, unique_name.key, trait.t_type FROM trait " "INNER JOIN unique_name " "ON trait.name_id = unique_name.id " "GROUP BY unique_name.id, unique_name.key, trait.t_type") conn.execute(sql) conn.close() # Now we need to drop the foreign key constraint, rename # the trait.name column, and re-add a new foreign # key constraint params = {'columns': [trait.c.name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = "trait_ibfk_1" # foreign key to the unique name table fkey = ForeignKeyConstraint(**params) fkey.drop() Column('trait_type_id', Integer).create(trait) # Move data from name_id column into trait_type_id column query = select([trait.c.id, trait.c.name_id]) for key, value in migration.paged(query): (trait.update().where(trait.c.id == key).values({ "trait_type_id": value }).execute()) trait.c.name_id.drop() params = { 'columns': [trait.c.trait_type_id], 'refcolumns': [trait_type.c.id] } if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'trait_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.create() # Drop the t_type column to data_type. trait.c.t_type.drop() # Finally, drop the unique_name table - we don't need it # anymore. unique_name.drop()
def downgrade(migrate_engine): meta = MetaData(migrate_engine) unique_name = Table( 'unique_name', meta, Column('id', Integer, primary_key=True), Column('key', String(255), unique=True), mysql_engine='InnoDB', mysql_charset='utf8', ) trait_type = Table('trait_type', meta, autoload=True) trait = Table('trait', meta, autoload=True) # Create the UniqueName table, drop the foreign key constraint # to trait_type, drop the trait_type table, rename the # trait.trait_type column to traitname, re-add the dtype to # the trait table, and re-add the old foreign key constraint unique_name.create(migrate_engine) conn = migrate_engine.connect() sql = ("INSERT INTO unique_name " "SELECT trait_type.id, trait_type.desc " "FROM trait_type") conn.execute(sql) conn.close() params = { 'columns': [trait.c.trait_type_id], 'refcolumns': [trait_type.c.id] } if migrate_engine.name == 'mysql': params['name'] = "_".join(('fk', 'trait_type', 'id')) fkey = ForeignKeyConstraint(**params) fkey.drop() # Re-create the old columns in trait Column("name_id", Integer).create(trait) Column("t_type", Integer).create(trait) # copy data from trait_type.data_type into trait.t_type query = select([trait_type.c.id, trait_type.c.data_type]) for key, value in migration.paged(query): trait.update().where(trait.c.trait_type_id == key)\ .values({"t_type": value}).execute() # Move data from name_id column into trait_type_id column query = select([trait.c.id, trait.c.trait_type_id]) for key, value in migration.paged(query): trait.update().where(trait.c.id == key)\ .values({"name_id": value}).execute() # Add a foreign key to the unique_name table params = {'columns': [trait.c.name_id], 'refcolumns': [unique_name.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'trait_ibfk_1' fkey = ForeignKeyConstraint(**params) fkey.create() trait.c.trait_type_id.drop() # Drop the trait_type table. It isn't needed anymore trait_type.drop()
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() # NOTE: all new data added in the upgrade method is eliminated here via table drops # Drop 1 foreign key constraint from the metadata_file table MetadataFile_table = Table("metadata_file", metadata, autoload=True) LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True) try: cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id], [LibraryDatasetDatasetAssociation_table.c.id], name='metadata_file_lda_id_fkey') # Drop the constraint cons.drop() except Exception: log.exception( "Dropping foreign key constraint 'metadata_file_lda_id_fkey' from table 'metadata_file' failed." ) # Drop 1 foreign key constraint from the history_dataset_association table HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True) LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True) try: cons = ForeignKeyConstraint( [ HistoryDatasetAssociation_table.c. copied_from_library_dataset_dataset_association_id ], [LibraryDatasetDatasetAssociation_table.c.id], name= 'history_dataset_association_copied_from_library_dataset_da_fkey') # Drop the constraint cons.drop() except Exception: log.exception( "Dropping foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' from table 'history_dataset_association' failed." ) # Drop all of the new tables above TABLES = [ UserGroupAssociation_table, UserRoleAssociation_table, GroupRoleAssociation_table, Group_table, DatasetPermissions_table, LibraryPermissions_table, LibraryFolderPermissions_table, LibraryDatasetPermissions_table, LibraryDatasetDatasetAssociationPermissions_table, LibraryItemInfoPermissions_table, LibraryItemInfoTemplatePermissions_table, DefaultUserPermissions_table, DefaultHistoryPermissions_table, Role_table, LibraryDatasetDatasetInfoAssociation_table, LibraryDataset_table, LibraryDatasetDatasetAssociation_table, LibraryDatasetDatasetInfoTemplateAssociation_table, JobExternalOutputMetadata_table, Library_table, LibraryFolder_table, LibraryItemInfoTemplateElement_table, LibraryInfoTemplateAssociation_table, LibraryFolderInfoTemplateAssociation_table, LibraryDatasetInfoTemplateAssociation_table, LibraryInfoAssociation_table, LibraryFolderInfoAssociation_table, LibraryDatasetInfoAssociation_table, LibraryItemInfoElement_table, LibraryItemInfo_table, LibraryItemInfoTemplate_table, ] for table in TABLES: drop_table(table) # Drop the index on the Job.state column - changeset 2192 drop_index('ix_job_state', 'job', 'state', metadata) # Drop 1 column from the stored_workflow table - changeset 2328 drop_column('importable', 'stored_workflow', metadata) # Drop 1 column from the metadata_file table drop_column('lda_id', 'metadata_file', metadata) # Drop 1 column from the history_dataset_association table drop_column('copied_from_library_dataset_dataset_association_id', HistoryDatasetAssociation_table) # Drop 2 columns from the galaxy_user table User_table = Table("galaxy_user", metadata, autoload=True) drop_column('deleted', User_table) drop_column('purged', User_table)
def upgrade(migrate_engine): """Convert volume and snapshot id columns from int to varchar.""" meta = MetaData() meta.bind = migrate_engine volumes = Table('volumes', meta, autoload=True) snapshots = Table('snapshots', meta, autoload=True) iscsi_targets = Table('iscsi_targets', meta, autoload=True) volume_metadata = Table('volume_metadata', meta, autoload=True) block_device_mapping = Table('block_device_mapping', meta, autoload=True) sm_volumes = Table('sm_volume', meta, autoload=True) volume_mappings = Table('volume_id_mappings', meta, autoload=True) snapshot_mappings = Table('snapshot_id_mappings', meta, autoload=True) fkey_columns = [ iscsi_targets.c.volume_id, volume_metadata.c.volume_id, sm_volumes.c.id, ] for column in fkey_columns: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name LOG.info('Dropping foreign key %s' % fkey_name) fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volumes.c.id], name=fkey_name) try: fkey.drop() except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise volume_list = list(volumes.select().execute()) for v in volume_list: new_id = select( [volume_mappings.c.uuid], volume_mappings.c.id == v['id']).execute().fetchone()[0] volumes.update().\ where(volumes.c.id == v['id']).\ values(id=new_id).execute() sm_volumes.update().\ where(sm_volumes.c.id == v['id']).\ values(id=new_id).execute() snapshots.update().\ where(snapshots.c.volume_id == v['id']).\ values(volume_id=new_id).execute() iscsi_targets.update().\ where(iscsi_targets.c.volume_id == v['id']).\ values(volume_id=new_id).execute() volume_metadata.update().\ where(volume_metadata.c.volume_id == v['id']).\ values(volume_id=new_id).execute() block_device_mapping.update().\ where(block_device_mapping.c.volume_id == v['id']).\ values(volume_id=new_id).execute() snapshot_list = list(snapshots.select().execute()) for s in snapshot_list: new_id = select( [snapshot_mappings.c.uuid], snapshot_mappings.c.id == s['id']).execute().fetchone()[0] volumes.update().\ where(volumes.c.snapshot_id == s['id']).\ values(snapshot_id=new_id).execute() snapshots.update().\ where(snapshots.c.id == s['id']).\ values(id=new_id).execute() block_device_mapping.update().\ where(block_device_mapping.c.snapshot_id == s['id']).\ values(snapshot_id=new_id).execute() for column in fkey_columns: fkeys = list(column.foreign_keys) if fkeys: fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volumes.c.id]) try: fkey.create() LOG.info('Created foreign key %s' % fkey_name) except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise
HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True ) except NoSuchTableError: HistoryDatasetAssociation_table = None log.debug( "Failed loading table history_dataset_association" ) try: LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True ) except NoSuchTableError: LibraryDatasetDatasetAssociation_table = None log.debug( "Failed loading table library_dataset_dataset_association" ) if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None: try: cons = ForeignKeyConstraint( [HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id], [LibraryDatasetDatasetAssociation_table.c.id], name='history_dataset_association_copied_from_library_dataset_da_fkey' ) # Drop the constraint cons.drop() except Exception, e: log.debug( "Dropping foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' from table 'history_dataset_association' failed: %s" % ( str( e ) ) ) # Drop all of the new tables above try: UserGroupAssociation_table.drop() except Exception, e: log.debug( "Dropping user_group_association table failed: %s" % str( e ) ) try: UserRoleAssociation_table.drop() except Exception, e: log.debug( "Dropping user_role_association table failed: %s" % str( e ) ) try: GroupRoleAssociation_table.drop() except Exception, e: log.debug( "Dropping group_role_association table failed: %s" % str( e ) )
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() # NOTE: all new data added in the upgrade method is eliminated here via table drops # Drop 1 foreign key constraint from the metadata_file table try: MetadataFile_table = Table("metadata_file", metadata, autoload=True) except NoSuchTableError: MetadataFile_table = None log.debug("Failed loading table metadata_file") try: LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True) except NoSuchTableError: LibraryDatasetDatasetAssociation_table = None log.debug("Failed loading table library_dataset_dataset_association") if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None: try: cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id], [LibraryDatasetDatasetAssociation_table.c.id], name='metadata_file_lda_id_fkey') # Drop the constraint cons.drop() except Exception: log.exception( "Dropping foreign key constraint 'metadata_file_lda_id_fkey' from table 'metadata_file' failed." ) # Drop 1 foreign key constraint from the history_dataset_association table try: HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True) except NoSuchTableError: HistoryDatasetAssociation_table = None log.debug("Failed loading table history_dataset_association") try: LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True) except NoSuchTableError: LibraryDatasetDatasetAssociation_table = None log.debug("Failed loading table library_dataset_dataset_association") if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None: try: cons = ForeignKeyConstraint( [ HistoryDatasetAssociation_table.c. copied_from_library_dataset_dataset_association_id ], [LibraryDatasetDatasetAssociation_table.c.id], name= 'history_dataset_association_copied_from_library_dataset_da_fkey' ) # Drop the constraint cons.drop() except Exception: log.exception( "Dropping foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' from table 'history_dataset_association' failed." ) # Drop all of the new tables above try: UserGroupAssociation_table.drop() except Exception: log.exception("Dropping user_group_association table failed.") try: UserRoleAssociation_table.drop() except Exception: log.exception("Dropping user_role_association table failed.") try: GroupRoleAssociation_table.drop() except Exception: log.exception("Dropping group_role_association table failed.") try: Group_table.drop() except Exception: log.exception("Dropping galaxy_group table failed.") try: DatasetPermissions_table.drop() except Exception: log.exception("Dropping dataset_permissions table failed.") try: LibraryPermissions_table.drop() except Exception: log.exception("Dropping library_permissions table failed.") try: LibraryFolderPermissions_table.drop() except Exception: log.exception("Dropping library_folder_permissions table failed.") try: LibraryDatasetPermissions_table.drop() except Exception: log.exception("Dropping library_dataset_permissions table failed.") try: LibraryDatasetDatasetAssociationPermissions_table.drop() except Exception: log.exception( "Dropping library_dataset_dataset_association_permissions table failed." ) try: LibraryItemInfoPermissions_table.drop() except Exception: log.exception("Dropping library_item_info_permissions table failed.") try: LibraryItemInfoTemplatePermissions_table.drop() except Exception: log.exception( "Dropping library_item_info_template_permissions table failed.") try: DefaultUserPermissions_table.drop() except Exception: log.exception("Dropping default_user_permissions table failed.") try: DefaultHistoryPermissions_table.drop() except Exception: log.exception("Dropping default_history_permissions table failed.") try: Role_table.drop() except Exception: log.exception("Dropping role table failed.") try: LibraryDatasetDatasetInfoAssociation_table.drop() except Exception: log.exception( "Dropping library_dataset_dataset_info_association table failed.") try: LibraryDataset_table.drop() except Exception: log.exception("Dropping library_dataset table failed.") try: LibraryDatasetDatasetAssociation_table.drop() except Exception: log.exception( "Dropping library_dataset_dataset_association table failed.") try: LibraryDatasetDatasetInfoTemplateAssociation_table.drop() except Exception: log.exception( "Dropping library_dataset_dataset_info_template_association table failed." ) try: JobExternalOutputMetadata_table.drop() except Exception: log.exception("Dropping job_external_output_metadata table failed.") try: Library_table.drop() except Exception: log.exception("Dropping library table failed.") try: LibraryFolder_table.drop() except Exception: log.exception("Dropping library_folder table failed.") try: LibraryItemInfoTemplateElement_table.drop() except Exception: log.exception( "Dropping library_item_info_template_element table failed.") try: LibraryInfoTemplateAssociation_table.drop() except Exception: log.exception( "Dropping library_info_template_association table failed.") try: LibraryFolderInfoTemplateAssociation_table.drop() except Exception: log.exception( "Dropping library_folder_info_template_association table failed.") try: LibraryDatasetInfoTemplateAssociation_table.drop() except Exception: log.exception( "Dropping library_dataset_info_template_association table failed.") try: LibraryInfoAssociation_table.drop() except Exception: log.exception("Dropping library_info_association table failed.") try: LibraryFolderInfoAssociation_table.drop() except Exception: log.exception("Dropping library_folder_info_association table failed.") try: LibraryDatasetInfoAssociation_table.drop() except Exception: log.exception( "Dropping library_dataset_info_association table failed.") try: LibraryItemInfoElement_table.drop() except Exception: log.exception("Dropping library_item_info_element table failed.") try: LibraryItemInfo_table.drop() except Exception: log.exception("Dropping library_item_info table failed.") try: LibraryItemInfoTemplate_table.drop() except Exception: log.exception("Dropping library_item_info_template table failed.") # Drop the index on the Job.state column - changeset 2192 drop_index('ix_job_state', 'job', 'state', metadata) # Drop 1 column from the stored_workflow table - changeset 2328 try: StoredWorkflow_table = Table("stored_workflow", metadata, autoload=True) except NoSuchTableError: StoredWorkflow_table = None log.debug("Failed loading table stored_workflow") if StoredWorkflow_table is not None: try: col = StoredWorkflow_table.c.importable col.drop() except Exception: log.exception( "Dropping column 'importable' from stored_workflow table failed." ) # Drop 1 column from the metadata_file table try: MetadataFile_table = Table("metadata_file", metadata, autoload=True) except NoSuchTableError: MetadataFile_table = None log.debug("Failed loading table metadata_file") if MetadataFile_table is not None: try: col = MetadataFile_table.c.lda_id col.drop() except Exception: log.exception( "Dropping column 'lda_id' from metadata_file table failed.") # Drop 1 column from the history_dataset_association table try: HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True) except NoSuchTableError: HistoryDatasetAssociation_table = None log.debug("Failed loading table history_dataset_association") if HistoryDatasetAssociation_table is not None: try: col = HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id col.drop() except Exception: log.exception( "Dropping column 'copied_from_library_dataset_dataset_association_id' from history_dataset_association table failed." ) # Drop 2 columns from the galaxy_user table try: User_table = Table("galaxy_user", metadata, autoload=True) except NoSuchTableError: User_table = None log.debug("Failed loading table galaxy_user") if User_table is not None: try: col = User_table.c.deleted col.drop() except Exception: log.exception( "Dropping column 'deleted' from galaxy_user table failed.") try: col = User_table.c.purged col.drop() except Exception: log.exception( "Dropping column 'purged' from galaxy_user table failed.")
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # Drop column from snapshots table if migrate_engine.name == 'mysql': # MySQL cannot drop column cgsnapshot_id until the foreign key # constraint is removed. So remove the foreign key first, and # then drop the column. table = Table('snapshots', meta, autoload=True) ref_table = Table('snapshots', meta, autoload=True) params = { 'columns': [table.c['cgsnapshot_id']], 'refcolumns': [ref_table.c['id']], 'name': 'snapshots_ibfk_1' } try: fkey = ForeignKeyConstraint(**params) fkey.drop() except Exception: LOG.error( _("Dropping foreign key 'cgsnapshot_id' in " "the 'snapshots' table failed.")) snapshots = Table('snapshots', meta, autoload=True) cgsnapshot_id = snapshots.columns.cgsnapshot_id snapshots.drop_column(cgsnapshot_id) # Drop column from volumes table if migrate_engine.name == 'mysql': # MySQL cannot drop column consistencygroup_id until the foreign # key constraint is removed. So remove the foreign key first, # and then drop the column. table = Table('volumes', meta, autoload=True) ref_table = Table('volumes', meta, autoload=True) params = { 'columns': [table.c['consistencygroup_id']], 'refcolumns': [ref_table.c['id']], 'name': 'volumes_ibfk_1' } try: fkey = ForeignKeyConstraint(**params) fkey.drop() except Exception: LOG.error( _("Dropping foreign key 'consistencygroup_id' in " "the 'volumes' table failed.")) volumes = Table('volumes', meta, autoload=True) consistencygroup_id = volumes.columns.consistencygroup_id volumes.drop_column(consistencygroup_id) # Drop table cgsnapshots = Table('cgsnapshots', meta, autoload=True) try: cgsnapshots.drop() except Exception: LOG.error(_("cgsnapshots table not dropped")) raise # Drop table consistencygroups = Table('consistencygroups', meta, autoload=True) try: consistencygroups.drop() except Exception: LOG.error(_("consistencygroups table not dropped")) raise
def downgrade(migrate_engine): """Convert volume_type from UUID back to int.""" meta = MetaData() meta.bind = migrate_engine volumes = Table('volumes', meta, autoload=True) volume_types = Table('volume_types', meta, autoload=True) extra_specs = Table('volume_type_extra_specs', meta, autoload=True) fkey_remove_list = [ volumes.c.volume_type_id, volume_types.c.id, extra_specs.c.volume_type_id ] for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.drop() except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise vtype_list = list(volume_types.select().execute()) new_id = 1 for t in vtype_list: volumes.update().\ where(volumes.c.volume_type_id == t['id']).\ values(volume_type_id=new_id).execute() extra_specs.update().\ where(extra_specs.c.volume_type_id == t['id']).\ values(volume_type_id=new_id).execute() volume_types.update().\ where(volume_types.c.id == t['id']).\ values(id=new_id).execute() new_id += 1 volumes.c.volume_type_id.alter(Integer) volume_types.c.id.alter(Integer) extra_specs.c.volume_type_id.alter(Integer) for column in fkey_remove_list: fkeys = list(column.foreign_keys) if fkeys: fkey_name = fkeys[0].constraint.name fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name) try: fkey.create() LOG.info('Created foreign key %s' % fkey_name) except Exception: if migrate_engine.url.get_dialect().name.startswith('sqlite'): pass else: raise