def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta = sql.MetaData() meta.bind = migrate_engine role_user_table = sql.Table('role_user_fiware', meta, autoload=True) role_fiware = sql.Table('role_fiware', meta, autoload=True) if 'mysql' in str(meta): ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_ibfk_1', ondelete='CASCADE').drop() ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_ibfk_1').create() if 'postgres' in str(meta): ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_role_id_fkey', ondelete='CASCADE').drop() ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_role_id_fkey').create()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta = sql.MetaData() meta.bind = migrate_engine authorization_code_table = sql.Table('authorization_code_oauth2', meta, autoload=True) consumer_oauth2 = sql.Table('consumer_oauth2', meta, autoload=True) if 'mysql' in str(meta): ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_ibfk_1').drop() ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_ibfk_1', ondelete='CASCADE').create() elif 'postgres' in str(meta): ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_consumer_id_fkey').drop() ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_consumer_id_fkey', ondelete='CASCADE').create()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta = sql.MetaData() meta.bind = migrate_engine role_user_table = sql.Table('role_user_fiware', meta, autoload=True) role_fiware = sql.Table('role_fiware', meta, autoload=True) if 'mysql' in str(meta): ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_ibfk_1').drop() ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_ibfk_1', ondelete='CASCADE').create() if 'postgres' in str(meta): ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_role_id_fkey').drop() ForeignKeyConstraint(columns=[role_user_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_user_fiware_role_id_fkey', ondelete='CASCADE').create()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine flavors = Table('flavors', meta, Column('created_at', DateTime), Column('updated_at', DateTime), Column('name', String(length=255), nullable=False), Column('id', Integer, primary_key=True, nullable=False), Column('memory_mb', Integer, nullable=False), Column('vcpus', Integer, nullable=False), Column('swap', Integer, nullable=False), Column('vcpu_weight', Integer), Column('flavorid', String(length=255), nullable=False), Column('rxtx_factor', Float), Column('root_gb', Integer), Column('ephemeral_gb', Integer), Column('disabled', Boolean), Column('is_public', Boolean), UniqueConstraint("flavorid", name="uniq_flavors0flavorid"), UniqueConstraint("name", name="uniq_flavors0name"), mysql_engine='InnoDB', mysql_charset='utf8') flavors.create(checkfirst=True) flavor_extra_specs = Table( 'flavor_extra_specs', meta, Column('created_at', DateTime), Column('updated_at', DateTime), Column('id', Integer, primary_key=True, nullable=False), Column('flavor_id', Integer, nullable=False), Column('key', String(length=255), nullable=False), Column('value', String(length=255)), UniqueConstraint('flavor_id', 'key', name='uniq_flavor_extra_specs0flavor_id0key'), Index('flavor_extra_specs_flavor_id_key_idx', 'flavor_id', 'key'), ForeignKeyConstraint(columns=['flavor_id'], refcolumns=[flavors.c.id]), mysql_engine='InnoDB', mysql_charset='utf8') flavor_extra_specs.create(checkfirst=True) flavor_projects = Table( 'flavor_projects', meta, Column('created_at', DateTime), Column('updated_at', DateTime), Column('id', Integer, primary_key=True, nullable=False), Column('flavor_id', Integer, nullable=False), Column('project_id', String(length=255), nullable=False), UniqueConstraint('flavor_id', 'project_id', name='uniq_flavor_projects0flavor_id0project_id'), ForeignKeyConstraint(columns=['flavor_id'], refcolumns=[flavors.c.id]), mysql_engine='InnoDB', mysql_charset='utf8') flavor_projects.create(checkfirst=True)
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine builders = sautils.Table('builders', metadata, autoload=True) masters = sautils.Table('masters', metadata, autoload=True) workers = sautils.Table('workers', metadata, autoload=True) builder_masters = sautils.Table('builder_masters', metadata, autoload=True) configured_workers = sautils.Table('configured_workers', metadata, autoload=True) for fk in ( ForeignKeyConstraint([builder_masters.c.builderid], [builders.c.id], ondelete='CASCADE'), ForeignKeyConstraint([builder_masters.c.masterid], [masters.c.id], ondelete='CASCADE'), ForeignKeyConstraint([configured_workers.c.buildermasterid], [builder_masters.c.id], ondelete='CASCADE'), ForeignKeyConstraint([configured_workers.c.workerid], [workers.c.id], ondelete='CASCADE'), ): fk.drop() fk.create()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta = sql.MetaData() meta.bind = migrate_engine authorization_code_table = sql.Table('authorization_code_oauth2', meta, autoload=True) consumer_oauth2 = sql.Table('consumer_oauth2', meta, autoload=True) if 'mysql' in str(meta): ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_ibfk_1', ondelete='CASCADE').drop() ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_ibfk_1').create() elif 'postgres' in str(meta): ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_consumer_id_fkey', ondelete='CASCADE').drop() ForeignKeyConstraint( columns=[authorization_code_table.c.consumer_id], refcolumns=[consumer_oauth2.c.id], name='authorization_code_oauth2_consumer_id_fkey').create()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta = sql.MetaData() meta.bind = migrate_engine if 'mysql' in str(meta): role_user_table = sql.Table('role_user_fiware', meta, autoload=True) role_fiware_table = sql.Table('role_fiware', meta, autoload=True) role_organization_table = sql.Table('role_organization_fiware', meta, autoload=True) consumer_oauth2 = sql.Table('consumer_oauth2', meta, autoload=True) project = sql.Table('project', meta, autoload=True) ForeignKeyConstraint(columns=[role_user_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='role_user_fiware_ibfk_4').drop() ForeignKeyConstraint(columns=[role_user_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='role_user_fiware_ibfk_4', ondelete='CASCADE').create() ForeignKeyConstraint(columns=[role_user_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='role_user_fiware_ibfk_3').drop() ForeignKeyConstraint(columns=[role_user_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='role_user_fiware_ibfk_3', ondelete='CASCADE').create() ForeignKeyConstraint(columns=[role_fiware_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='role_fiware_ibfk_1').drop() ForeignKeyConstraint(columns=[role_fiware_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='role_fiware_ibfk_1', ondelete='CASCADE').create() ForeignKeyConstraint( columns=[role_organization_table.c.organization_id], refcolumns=[project.c.id], name='role_organization_fiware_ibfk_2').drop() ForeignKeyConstraint( columns=[role_organization_table.c.organization_id], refcolumns=[project.c.id], name='role_organization_fiware_ibfk_2', ondelete='CASCADE').create() ForeignKeyConstraint( columns=[role_organization_table.c.organization_id], refcolumns=[project.c.id], name='role_organization_fiware_ibfk_3').drop() ForeignKeyConstraint( columns=[role_organization_table.c.organization_id], refcolumns=[project.c.id], name='role_organization_fiware_ibfk_3', ondelete='CASCADE').create()
def upgrade(migrate_engine): meta.bind = migrate_engine roles_users = Table("roles_users", meta, autoload=True) role = Table("role", meta, autoload=True) user = Table("user", meta, autoload=True) fk_role = ForeignKeyConstraint([roles_users.c.role_id], [role.c.id]) fk_role.create() fk_user = ForeignKeyConstraint([roles_users.c.user_id], [user.c.id]) fk_user.create()
def migration_004_up(meta): permission_table = sql.Table('permission_fiware', meta, autoload=True) consumer_oauth2 = sql.Table('consumer_oauth2', meta, autoload=True) ForeignKeyConstraint(columns=[permission_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='permission_fiware_application_id_fkey').drop() ForeignKeyConstraint(columns=[permission_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='permission_fiware_application_id_fkey', ondelete='CASCADE').create()
def downgrade(migrate_engine): meta.bind = migrate_engine studies_owners = Table("studies_owners", meta, autoload=True) study = Table("study", meta, autoload=True) user = Table("user", meta, autoload=True) fk_study = ForeignKeyConstraint([studies_owners.c.study_id], [study.c.id]) fk_study.drop() fk_user = ForeignKeyConstraint([studies_owners.c.user_id], [user.c.id]) fk_user.drop()
def upgrade(migrate_engine): meta.bind = migrate_engine upload = Table("upload", meta, autoload=True) study = Table("study", meta, autoload=True) user = Table("user", meta, autoload=True) fk_study = ForeignKeyConstraint([upload.c.study_id], [study.c.id]) fk_study.create() fk_user = ForeignKeyConstraint([upload.c.uploader_id], [user.c.id]) fk_user.create()
def migration_006_down(meta): role_user_table = sql.Table('role_user_fiware', meta, autoload=True) user = sql.Table('user', meta, autoload=True) ForeignKeyConstraint(columns=[role_user_table.c.user_id], refcolumns=[user.c.id], name='role_user_fiware_user_id_fkey', ondelete='CASCADE').drop() ForeignKeyConstraint(columns=[role_user_table.c.user_id], refcolumns=[user.c.id], name='role_user_fiware_user_id_fkey').create()
def upgrade(migrate_engine): meta.bind = migrate_engine studies_collaborators = Table("studies_collaborators", meta, autoload=True) study = Table("study", meta, autoload=True) user = Table("user", meta, autoload=True) fk_study = ForeignKeyConstraint([studies_collaborators.c.study_id], [study.c.id]) fk_study.create() fk_user = ForeignKeyConstraint([studies_collaborators.c.user_id], [user.c.id]) fk_user.create()
def migration_005_down(meta): role_permission_table = sql.Table('role_permission_fiware', meta, autoload=True) role_fiware = sql.Table('role_fiware', meta, autoload=True) ForeignKeyConstraint(columns=[role_permission_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_permission_fiware_role_id_fkey', ondelete='CASCADE').drop() ForeignKeyConstraint(columns=[role_permission_table.c.role_id], refcolumns=[role_fiware.c.id], name='role_permission_fiware_role_id_fkey').create()
def drop_constraints_and_alter_types(primary_table_name, foreign_tables, revision_table_name): # 1 drop all foreign key constraints dropped_fk_constraints = [] primary_table = Table(primary_table_name, metadata, autoload=True) for table_name in foreign_tables: table = Table(table_name, metadata, autoload=True) for constraint in table.constraints.copy(): if isinstance(constraint, sqlalchemy.schema.ForeignKeyConstraint): foreign_key_cols = [key.column for key in constraint.elements] fk_col = foreign_key_cols[0] if fk_col.table == primary_table: orig_fk = ForeignKeyConstraint(constraint.columns, foreign_key_cols, name=constraint.name, table=table) orig_fk.drop() dropped_fk_constraints.append((constraint.columns, foreign_key_cols, constraint.name, table.name)) #print 'CON', dropped_fk_constraints[-1] # 2 alter type of primary table id and foreign keys id_col = constraint.table.columns[constraint.columns[0]] id_col.alter(type=UnicodeText) primary_table = Table(primary_table_name, metadata, autoload=True) id_col = primary_table.c['id'] id_col.alter(type=UnicodeText) if revision_table_name: # Revision table id column type changed as well revision_table = Table(revision_table_name, metadata, autoload=True) id_col = revision_table.c['id'] id_col.alter(type=UnicodeText) return dropped_fk_constraints
def drop_constraints_and_alter_types(): # 1 drop all foreign key constraints dropped_fk_constraints = [] revision_table = Table('revision', metadata, autoload=True) foreign_tables = ['package', 'package_tag', 'package_revision', 'package_tag_revision', 'package_extra', 'package_extra_revision', ] for table_name in foreign_tables: table = Table(table_name, metadata, autoload=True) for constraint in table.constraints.copy(): if isinstance(constraint, sqlalchemy.schema.ForeignKeyConstraint): foreign_key_cols = [key.column for key in constraint.elements] fk_col = foreign_key_cols[0] if fk_col.table == revision_table: orig_fk = ForeignKeyConstraint(constraint.columns, foreign_key_cols, name=constraint.name, table=table) orig_fk.drop() dropped_fk_constraints.append((constraint.columns, foreign_key_cols, constraint.name, table.name)) # 2 alter type of revision id and foreign keys id_col = constraint.table.columns[constraint.columns[0]] id_col.alter(type=UnicodeText) revision_table = Table('revision', metadata, autoload=True) id_col = revision_table.c['id'] id_col.alter(type=UnicodeText, ) return dropped_fk_constraints
def upgrade(migrate_engine): """Add workers table.""" meta = MetaData() meta.bind = migrate_engine workers = Table( 'workers', meta, # Inherited fields from CinderBase Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(), default=False), # Workers table specific fields Column('id', Integer, primary_key=True), Column('resource_type', String(40), nullable=False), Column('resource_id', String(36), nullable=False), Column('status', String(255), nullable=False), Column('service_id', Integer, nullable=True), UniqueConstraint('resource_type', 'resource_id'), mysql_engine='InnoDB', mysql_charset='utf8', ) workers.create() services = Table('services', meta, autoload=True) ForeignKeyConstraint(columns=[workers.c.service_id], refcolumns=[services.c.id]).create()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine build_requests = Table('build_requests', meta, autoload=True) request_specs = Table('request_specs', meta, autoload=True) for fkey in build_requests.foreign_keys: if fkey.target_fullname == 'request_specs.id': ForeignKeyConstraint(columns=['request_spec_id'], refcolumns=[request_specs.c.id], table=build_requests, name=fkey.name).drop() break # These are being made nullable because they are no longer used after the # addition of the instance column. However they need a deprecation period # before they can be dropped. columns_to_nullify = [ 'request_spec_id', 'user_id', 'security_groups', 'config_drive' ] for column in columns_to_nullify: getattr(build_requests.c, column).alter(nullable=True) inspector = reflection.Inspector.from_engine(migrate_engine) constrs = inspector.get_unique_constraints('build_requests') constr_names = [constr['name'] for constr in constrs] if 'uniq_build_requests0request_spec_id' in constr_names: UniqueConstraint('request_spec_id', table=build_requests, name='uniq_build_requests0request_spec_id').drop()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine builders = sautils.Table('builders', metadata, autoload=True) masters = sautils.Table('masters', metadata, autoload=True) workers = sautils.Table('workers', metadata, autoload=True) builder_masters = sautils.Table('builder_masters', metadata, autoload=True) configured_workers = sautils.Table('configured_workers', metadata, autoload=True) fks_to_change = [] # we need to parse the reflected model in order to find the automatic fk name that was put # mysql and pgsql have different naming convention so this is not very easy to have generic code working. for table, keys in [(builder_masters, (builders.c.id, masters.c.id)), (configured_workers, (builder_masters.c.id, workers.c.id))]: for fk in table.constraints: if not isinstance(fk, sa.ForeignKeyConstraint): continue for c in fk.elements: if c.column in keys: # migrate.xx.ForeignKeyConstraint is changing the model so initializing here # would break the iteration (Set changed size during iteration) fks_to_change.append(( table, (fk.columns, [c.column]), dict(name=fk.name, ondelete='CASCADE'))) for table, args, kwargs in fks_to_change: fk = ForeignKeyConstraint(*args, **kwargs) table.append_constraint(fk) try: fk.drop() except NotSupportedError: pass # some versions of sqlite do not support drop, but will still update the fk fk.create()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine cell_mappings = Table('cell_mappings', meta, autoload=True) host_mappings = Table('host_mappings', meta, Column('created_at', DateTime), Column('updated_at', DateTime), Column('id', Integer, primary_key=True, nullable=False), Column('cell_id', Integer, nullable=False), Column('host', String(length=255), nullable=False), UniqueConstraint('host', name='uniq_host_mappings0host'), Index('host_idx', 'host'), ForeignKeyConstraint(columns=['cell_id'], refcolumns=[cell_mappings.c.id ]), mysql_engine='InnoDB', mysql_charset='utf8') host_mappings.create(checkfirst=True)
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine cell_mappings = Table('cell_mappings', meta, autoload=True) instance_mappings = Table( 'instance_mappings', meta, Column('created_at', DateTime), Column('updated_at', DateTime), Column('id', Integer, primary_key=True, nullable=False), Column('instance_uuid', String(length=36), nullable=False), Column('cell_id', Integer, nullable=False), Column('project_id', String(length=255), nullable=False), UniqueConstraint('instance_uuid', name='uniq_instance_mappings0instance_uuid'), Index('project_id_idx', 'project_id'), ForeignKeyConstraint(columns=['cell_id'], refcolumns=[cell_mappings.c.id]), mysql_engine='InnoDB', mysql_charset='utf8') # NOTE(mriedem): DB2 creates an index when a unique constraint is created # so trying to add a second index on the instance_uuid column will fail # with error SQL0605W, so omit the index in the case of DB2. if migrate_engine.name != 'ibm_db_sa': Index('instance_uuid_idx', instance_mappings.c.instance_uuid) instance_mappings.create(checkfirst=True)
def downgrade(migrate_engine): meta.bind = migrate_engine domains_table = Table('domains', meta, autoload=True) domainmetadata_table = Table('domainmetadata', meta, autoload=True) records_table = Table('records', meta, autoload=True) records_fk = ForeignKeyConstraint([records_table.c.domain_id], [domains_table.c.id], ondelete="CASCADE") records_fk.drop() domainmetadata_fk = ForeignKeyConstraint( [domainmetadata_table.c.domain_id], [domains_table.c.id], ondelete="CASCADE") domainmetadata_fk.drop()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta.bind = migrate_engine shelter = Table('shelter', meta, autoload=True) puppy = Table('puppy', meta, autoload=True) shelter_puppies = Table('shelter_puppies', meta, autoload=True) cons1 = ForeignKeyConstraint([shelter_puppies.c.shelter_id], [shelter.c.id]) cons1.drop() cons2 = ForeignKeyConstraint([shelter_puppies.c.puppy_id], [puppy.c.id]) cons2.drop() shelter_puppies.drop() pass
def downgrade(migrate_engine): meta.bind = migrate_engine user = Table("user", meta, autoload=True) site = Table("site", meta, autoload=True) fk_site = ForeignKeyConstraint([user.c.site_id], [site.c.id]) fk_site.drop()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta.bind = migrate_engine puppy = Table('puppy', meta, autoload=True) adoptors = Table('adoptors', meta, autoload=True) puppy_adoptors = Table('puppy_adoptors', meta, autoload=True) cons1 = ForeignKeyConstraint([puppy_adoptors.c.puppy_id], [puppy.c.id]) cons1.drop() cons2 = ForeignKeyConstraint([puppy_adoptors.c.adoptor_id], [adoptors.c.id]) cons2.drop() puppy_adoptors.drop() pass
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta = sql.MetaData() meta.bind = migrate_engine if 'mysql' in str(meta): role_user_table = sql.Table('role_user_fiware', meta, autoload=True) user = sql.Table('user', meta, autoload=True) ForeignKeyConstraint(columns=[role_user_table.c.user_id], refcolumns=[user.c.id], name='role_user_fiware_ibfk_2', ondelete='CASCADE').drop() ForeignKeyConstraint(columns=[role_user_table.c.user_id], refcolumns=[user.c.id], name='role_user_fiware_ibfk_2').create()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta = sql.MetaData() meta.bind = migrate_engine if 'mysql' in str(meta): permission_table = sql.Table('permission_fiware', meta, autoload=True) consumer_oauth2 = sql.Table('consumer_oauth2', meta, autoload=True) ForeignKeyConstraint( columns=[permission_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='permission_fiware_ibfk_1', ondelete='CASCADE').drop() ForeignKeyConstraint( columns=[permission_table.c.application_id], refcolumns=[consumer_oauth2.c.id], name='permission_fiware_ibfk_1').create()
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine snapshots = Table('snapshots', meta, autoload=True) volumes = Table('volumes', meta, autoload=True) ForeignKeyConstraint(columns=[snapshots.c.volume_id], refcolumns=[volumes.c.id]).drop()
def downgrade(migrate_engine): meta = MetaData(bind=migrate_engine) pci_devices = Table('pci_devices', meta, autoload=True) compute_nodes = Table('compute_nodes', meta, autoload=True) fkey = ForeignKeyConstraint(columns=[pci_devices.c.compute_node_id], refcolumns=[compute_nodes.c.id]) fkey.drop()
def upgrade(migrate_engine): meta.bind = migrate_engine field.create() field_type = Table("field_type", meta, autoload=True) study = Table("study", meta, autoload=True) fk_field_type = ForeignKeyConstraint([field.c.field_type_id], [field_type.c.id]) fk_field_type.create() fk_study = ForeignKeyConstraint([field.c.study_id], [study.c.id]) fk_study.create() uk_study_id_order = UniqueConstraint(field.c.study_id, field.c.order, table=field) uk_study_id_order.create()