def _alter_sourceassoc(meta, t_name, ix_name, post_action=False): if meta.bind.engine.name == 'sqlite': return sourceassoc = sa.Table('sourceassoc', meta, autoload=True) table = sa.Table(t_name, meta, autoload=True) user = sa.Table('user', meta, autoload=True) c_name = '%s_id' % t_name col = getattr(sourceassoc.c, c_name) uniq_name = 'uniq_sourceassoc0%s0user_id' % c_name uniq_cols = (c_name, 'user_id') param = {'columns': [col], 'refcolumns': [table.c.id]} user_param = { 'columns': [sourceassoc.c.user_id], 'refcolumns': [user.c.id] } if meta.bind.engine.name == 'mysql': param['name'] = 'fk_sourceassoc_%s' % c_name user_param['name'] = 'fk_sourceassoc_user_id' actions = [ migrate.ForeignKeyConstraint(**user_param), migrate.ForeignKeyConstraint(**param), sa.Index(ix_name, sourceassoc.c.source_id, col), migrate.UniqueConstraint(*uniq_cols, table=sourceassoc, name=uniq_name) ] for action in actions: action.create() if post_action else action.drop()
def _get_foreign_keys(t_images, t_image_members, t_image_properties): """Retrieve and return foreign keys for members/properties tables.""" try: image_members_fk_name = list(t_image_members.foreign_keys)[0].name image_properties_fk_name = list( t_image_properties.foreign_keys)[0].name except IndexError: image_members_fk_name = "ix_image_members_image_id_name" image_properties_fk_name = "ix_image_properties_key" foreign_keys = [] if t_image_members.foreign_keys: img_members_fk_name = list(t_image_members.foreign_keys)[0].name fk1 = migrate.ForeignKeyConstraint([t_image_members.c.image_id], [t_images.c.id], name=img_members_fk_name) foreign_keys.append(fk1) if t_image_properties.foreign_keys: img_properties_fk_name = list(t_image_properties.foreign_keys)[0].name fk2 = migrate.ForeignKeyConstraint([t_image_properties.c.image_id], [t_images.c.id], name=img_properties_fk_name) foreign_keys.append(fk2) return foreign_keys
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine role = sql.Table(ROLE_TABLE, meta, autoload=True) implied_role = sql.Table(IMPLIED_ROLE_TABLE, meta, autoload=True) fkeys = [ {'columns': [implied_role.c.prior_role_id], 'references': [role.c.id]}, {'columns': [implied_role.c.implied_role_id], 'references': [role.c.id]}, ] # NOTE(stevemar): We need to divide these into two separate loops otherwise # they may clobber each other and only end up with one foreign key. for fkey in fkeys: migrate.ForeignKeyConstraint(columns=fkey['columns'], refcolumns=fkey['references'], name=fkey.get('name')).drop() for fkey in fkeys: migrate.ForeignKeyConstraint(columns=fkey['columns'], refcolumns=fkey['references'], name=fkey.get('name'), ondelete="CASCADE").create()
def _get_foreign_keys(t_images, t_image_members, t_image_properties, dialect): """Retrieve and return foreign keys for members/properties tables.""" foreign_keys = [] if t_image_members.foreign_keys: img_members_fk_name = list(t_image_members.foreign_keys)[0].name if dialect == 'mysql': fk1 = migrate.ForeignKeyConstraint([t_image_members.c.image_id], [t_images.c.id], name=img_members_fk_name) else: fk1 = migrate.ForeignKeyConstraint([t_image_members.c.image_id], [t_images.c.id]) foreign_keys.append(fk1) if t_image_properties.foreign_keys: img_properties_fk_name = list(t_image_properties.foreign_keys)[0].name if dialect == 'mysql': fk2 = migrate.ForeignKeyConstraint([t_image_properties.c.image_id], [t_images.c.id], name=img_properties_fk_name) else: fk2 = migrate.ForeignKeyConstraint([t_image_properties.c.image_id], [t_images.c.id]) foreign_keys.append(fk2) return foreign_keys
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine user = sql.Table('user', meta, autoload=True) project = sql.Table('project', meta, autoload=True) fk_name = [ c for c in user.constraints if isinstance(c, sql.ForeignKeyConstraint) and c.column_keys == ['domain_id'] ][0].name fk_constraint = migrate.ForeignKeyConstraint(columns=[user.c.domain_id], refcolumns=[project.c.id]) fk_constraint.name = fk_name fk_constraint.drop() identity_provider = sql.Table('identity_provider', meta, autoload=True) fk_name = [ c for c in identity_provider.constraints if isinstance(c, sql.ForeignKeyConstraint) and c.column_keys == ['domain_id'] ][0].name fk_constraint = migrate.ForeignKeyConstraint( columns=[identity_provider.c.domain_id], refcolumns=[project.c.id]) fk_constraint.name = fk_name fk_constraint.drop()
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine cascaded_pods = sql.Table( 'cascaded_pods', meta, sql.Column('pod_id', sql.String(length=36), primary_key=True), sql.Column('pod_name', sql.String(length=255), unique=True, nullable=False), sql.Column('pod_az_name', sql.String(length=255), nullable=True), sql.Column('dc_name', sql.String(length=255), nullable=True), sql.Column('az_name', sql.String(length=255), nullable=False), mysql_engine='InnoDB', mysql_charset='utf8') cascaded_pod_service_configuration = sql.Table( 'cascaded_pod_service_configuration', meta, sql.Column('service_id', sql.String(length=64), primary_key=True), sql.Column('pod_id', sql.String(length=64), nullable=False), sql.Column('service_type', sql.String(length=64), nullable=False), sql.Column('service_url', sql.String(length=512), nullable=False), mysql_engine='InnoDB', mysql_charset='utf8') pod_binding = sql.Table( 'pod_binding', meta, sql.Column('id', sql.String(36), primary_key=True), sql.Column('tenant_id', sql.String(length=255), nullable=False), sql.Column('pod_id', sql.String(length=255), nullable=False), sql.Column('is_binding', sql.Boolean, nullable=False), sql.Column('created_at', sql.DateTime), sql.Column('updated_at', sql.DateTime), migrate.UniqueConstraint( 'tenant_id', 'pod_id', name='pod_binding0tenant_id0pod_id'), mysql_engine='InnoDB', mysql_charset='utf8') tables = [cascaded_pods, cascaded_pod_service_configuration, pod_binding] for table in tables: table.create() fkey = {'columns': [cascaded_pod_service_configuration.c.pod_id], 'references': [cascaded_pods.c.pod_id]} migrate.ForeignKeyConstraint(columns=fkey['columns'], refcolumns=fkey['references'], name=fkey.get('name')).create() fkey = {'columns': [pod_binding.c.pod_id], 'references': [cascaded_pods.c.pod_id]} migrate.ForeignKeyConstraint(columns=fkey['columns'], refcolumns=fkey['references'], name=fkey.get('name')).create()
def _downgrade_sqlite_namespace_id_name_constraint(metadef, metadef_namespaces, constraint_name, fk_name): migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name, name=constraint_name).drop() migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name).create() migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id], name=fk_name).drop() migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id]).create()
def _get_foreign_keys(t_images, t_image_members, t_image_properties): """Retrieve and return foreign keys for members/properties tables.""" image_members_fk_name = list(t_image_members.foreign_keys)[0].name image_properties_fk_name = list(t_image_properties.foreign_keys)[0].name fk1 = migrate.ForeignKeyConstraint([t_image_members.c.image_id], [t_images.c.id], name=image_members_fk_name) fk2 = migrate.ForeignKeyConstraint([t_image_properties.c.image_id], [t_images.c.id], name=image_properties_fk_name) return fk1, fk2
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine federated_table = sql.Table('federated_user', meta, autoload=True) protocol_table = sql.Table('federation_protocol', meta, autoload=True) migrate.ForeignKeyConstraint( columns=[federated_table.c.protocol_id, federated_table.c.idp_id], refcolumns=[protocol_table.c.id, protocol_table.c.idp_id]).drop() migrate.ForeignKeyConstraint( columns=[federated_table.c.protocol_id, federated_table.c.idp_id], refcolumns=[protocol_table.c.id, protocol_table.c.idp_id], ondelete='CASCADE').create()
def _handle_meter_indices(meta, downgrade=False): if meta.bind.engine.name == 'sqlite': return resource = sa.Table('resource', meta, autoload=True) project = sa.Table('project', meta, autoload=True) user = sa.Table('user', meta, autoload=True) meter = sa.Table('meter', meta, autoload=True) indices = [(sa.Index('ix_meter_timestamp', meter.c.timestamp), sa.Index('ix_sample_timestamp', meter.c.timestamp)), (sa.Index('ix_meter_user_id', meter.c.user_id), sa.Index('ix_sample_user_id', meter.c.user_id)), (sa.Index('ix_meter_project_id', meter.c.project_id), sa.Index('ix_sample_project_id', meter.c.project_id)), (sa.Index('idx_meter_rid_cname', meter.c.resource_id, meter.c.counter_name), sa.Index('idx_sample_rid_cname', meter.c.resource_id, meter.c.counter_name))] fk_params = [({ 'columns': [meter.c.resource_id], 'refcolumns': [resource.c.id] }, 'fk_meter_resource_id', 'fk_sample_resource_id'), ({ 'columns': [meter.c.project_id], 'refcolumns': [project.c.id] }, 'fk_meter_project_id', 'fk_sample_project_id'), ({ 'columns': [meter.c.user_id], 'refcolumns': [user.c.id] }, 'fk_meter_user_id', 'fk_sample_user_id')] for fk in fk_params: params = fk[0] if meta.bind.engine.name == 'mysql': params['name'] = fk[2] if downgrade else fk[1] migrate.ForeignKeyConstraint(**params).drop() for meter_ix, sample_ix in indices: meter_ix.create() if downgrade else meter_ix.drop() sample_ix.drop() if downgrade else sample_ix.create() for fk in fk_params: params = fk[0] if meta.bind.engine.name == 'mysql': params['name'] = fk[1] if downgrade else fk[2] migrate.ForeignKeyConstraint(**params).create()
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine implied_role = sql.Table('implied_role', meta, sql.Column('prior_role_id', sql.String(length=64), primary_key=True), sql.Column('implied_role_id', sql.String(length=64), primary_key=True), mysql_engine='InnoDB', mysql_charset='utf8') implied_role.create() role = sql.Table(ROLE_TABLE, meta, autoload=True) fkeys = [ { 'columns': [implied_role.c.prior_role_id], 'references': [role.c.id] }, { 'columns': [implied_role.c.implied_role_id], 'references': [role.c.id] }, ] for fkey in fkeys: migrate.ForeignKeyConstraint(columns=fkey['columns'], refcolumns=fkey['references'], name=fkey.get('name')).create()
def upgrade(migrate_engine): if migrate_engine.name == 'sqlite': upgrade_sqlite(migrate_engine) return meta = sqlalchemy.MetaData() meta.bind = migrate_engine tmpl_table = sqlalchemy.Table('raw_template', meta, autoload=True) environment = sqlalchemy.Column('environment', heat_db_types.Json) environment.create(tmpl_table) predecessor = sqlalchemy.Column('predecessor', sqlalchemy.Integer) predecessor.create(tmpl_table) fkey = migrate.ForeignKeyConstraint(columns=[tmpl_table.c.predecessor], refcolumns=[tmpl_table.c.id], name='predecessor_fkey_ref') fkey.create() stack_table = sqlalchemy.Table('stack', meta, autoload=True) update_query = tmpl_table.update().values( environment=sqlalchemy.select([stack_table.c.parameters]).where( sqlalchemy.and_(stack_table.c.raw_template_id == tmpl_table.c.id, stack_table.c.deleted_at.is_(None))).as_scalar()) migrate_engine.execute(update_query) stack_table.c.parameters.drop()
def add_constraints(constraints): for constraint_def in constraints: migrate.ForeignKeyConstraint(columns=[ getattr(constraint_def['table'].c, constraint_def['fk_column']) ], refcolumns=[constraint_def['ref_column'] ]).create()
def _downgrade_constraint_with_fk(metadef, metadef_namespaces, constraint_name, fk_curr_name, fk_next_name): fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id], name=fk_curr_name) fkc.drop() migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name, name=constraint_name).drop() fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id], name=fk_next_name) fkc.create()
def downgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) sample = sa.Table('sample', meta, autoload=True) sample.c.volume.alter(name='counter_volume') sa.Column('counter_name', sa.String(255)).create(sample) sa.Column('counter_type', sa.String(255)).create(sample) sa.Column('counter_unit', sa.String(255)).create(sample) meter = sa.Table('meter', meta, autoload=True) for row in sa.select([meter]).execute(): sample.update()\ .where(sample.c.meter_id == row['id'])\ .values({sample.c.counter_name: row['name'], sample.c.counter_type: row['type'], sample.c.counter_unit: row['unit']}).execute() params = {'columns': [sample.c.meter_id], 'refcolumns': [meter.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'fk_sample_meter_id' if migrate_engine.name != 'sqlite': migrate.ForeignKeyConstraint(**params).drop() handle_rid_index(meta, True) sample.c.meter_id.drop() meter.drop()
def _migrate_to_region_id(migrate_engine, region_table, endpoint_table): endpoints = list(endpoint_table.select().execute()) for endpoint in endpoints: if endpoint.region is None: continue region = list( region_table.select( whereclause=region_table.c.id == endpoint.region).execute()) if len(region) == 1: region_id = region[0].id else: region_id = endpoint.region region = {'id': region_id, 'description': '', 'extra': '{}'} session = sessionmaker(bind=migrate_engine)() region_table.insert(region).execute() session.commit() new_values = {'region_id': region_id} f = endpoint_table.c.id == endpoint.id update = endpoint_table.update().where(f).values(new_values) migrate_engine.execute(update) migrate.ForeignKeyConstraint(columns=[endpoint_table.c.region_id], refcolumns=[region_table.c.id], name='fk_endpoint_region_id').create()
def downgrade_resource_data_post(migrate_engine): meta = sqlalchemy.MetaData(bind=migrate_engine) res_table = sqlalchemy.Table('resource', meta, autoload=True) rd_table = sqlalchemy.Table('resource_data', meta, autoload=True) # set: resource_data.resource_id = resource.id if migrate_engine.name == 'sqlite': res_list = res_table.select().order_by( sqlalchemy.sql.expression.asc( res_table.c.created_at)).execute().fetchall() for res in res_list: values = {'resource_id': res.id} update = rd_table.update().where( rd_table.c.tmp_res_id == res.id).values(values) migrate_engine.execute(update) # set foreignkey on resource_id if migrate_engine.name == 'mysql': sqlalchemy.Index('resource_data_resource_id_fkey', rd_table.c.resource_id).drop() cons = migrate.ForeignKeyConstraint(columns=[rd_table.c.resource_id], refcolumns=[res_table.c.id]) cons.create() rd_table.c.resource_id.alter(nullable=False) rd_table.c.tmp_res_id.drop()
def _drop_index_with_fk_constraint(metadef, metadef_namespaces, index_name, fk_old_name, fk_new_name): fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id], name=fk_old_name) fkc.drop() if index_name: Index(index_name, metadef.c.namespace_id).drop() # Rename the fk for consistency across all db's fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id], name=fk_new_name) fkc.create()
def remove_constraints(constraints): for constraint_def in constraints: migrate.ForeignKeyConstraint(columns=[ getattr(constraint_def['table'].c, constraint_def['fk_column']) ], refcolumns=[constraint_def['ref_column']], name=(get_fkey_constraint_name( constraint_def['table'], constraint_def['fk_column']))).drop()
def change_uniq(meta, downgrade=False): uniq_name = 'uniq_sourceassoc0meter_id0user_id' columns = ('meter_id', 'user_id') if meta.bind.engine.name == 'sqlite': return sourceassoc = sa.Table('sourceassoc', meta, autoload=True) meter = sa.Table('meter', meta, autoload=True) user = sa.Table('user', meta, autoload=True) if meta.bind.engine.name == 'mysql': # For mysql dialect all dependent FK should be removed # before renaming of constraint. params = { 'columns': [sourceassoc.c.meter_id], 'refcolumns': [meter.c.id], 'name': 'fk_sourceassoc_meter_id' } migrate.ForeignKeyConstraint(**params).drop() params = { 'columns': [sourceassoc.c.user_id], 'refcolumns': [user.c.id], 'name': 'fk_sourceassoc_user_id' } migrate.ForeignKeyConstraint(**params).drop() if downgrade: migrate.UniqueConstraint(*columns, table=sourceassoc, name=uniq_name).drop() else: migrate.UniqueConstraint(*columns, table=sourceassoc, name=uniq_name).create() if meta.bind.engine.name == 'mysql': params = { 'columns': [sourceassoc.c.meter_id], 'refcolumns': [meter.c.id], 'name': 'fk_sourceassoc_meter_id' } migrate.ForeignKeyConstraint(**params).create() params = { 'columns': [sourceassoc.c.user_id], 'refcolumns': [user.c.id], 'name': 'fk_sourceassoc_user_id' } migrate.ForeignKeyConstraint(**params).create()
def _update_sqlite_namespace_id_name_constraint(metadef, metadef_namespaces, new_constraint_name, new_fk_name): migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name).drop() migrate.UniqueConstraint(metadef.c.namespace_id, metadef.c.name, name=new_constraint_name).create() migrate.ForeignKeyConstraint([metadef.c.namespace_id], [metadef_namespaces.c.id], name=new_fk_name).create()
def remove_constraints(constraints): for constraint_def in constraints: constraint_names = get_constraints_names(constraint_def['table'], constraint_def['fk_column']) for constraint_name in constraint_names: migrate.ForeignKeyConstraint( columns=[getattr(constraint_def['table'].c, constraint_def['fk_column'])], refcolumns=[constraint_def['ref_column']], name=constraint_name).drop()
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine service_table = sql.Table('service', meta, autoload=True) region_table = sql.Table('region', meta, autoload=True) project_table = sql.Table('project', meta, autoload=True) registered_limit_table = sql.Table( 'registered_limit', meta, sql.Column('id', sql.String(length=64), primary_key=True), sql.Column('service_id', sql.String(255), sql.ForeignKey(service_table.c.id)), sql.Column('region_id', sql.String(64), sql.ForeignKey(region_table.c.id), nullable=True), sql.Column('resource_name', sql.String(255)), sql.Column('default_limit', sql.Integer, nullable=False), sql.UniqueConstraint('service_id', 'region_id', 'resource_name'), mysql_engine='InnoDB', mysql_charset='utf8') registered_limit_table.create(migrate_engine, checkfirst=True) limit_table = sql.Table('limit', meta, sql.Column('id', sql.String(length=64), primary_key=True), sql.Column('project_id', sql.String(64), sql.ForeignKey(project_table.c.id)), sql.Column('service_id', sql.String(255)), sql.Column('region_id', sql.String(64), nullable=True), sql.Column('resource_name', sql.String(255)), sql.Column('resource_limit', sql.Integer, nullable=False), sql.UniqueConstraint('project_id', 'service_id', 'region_id', 'resource_name'), mysql_engine='InnoDB', mysql_charset='utf8') limit_table.create(migrate_engine, checkfirst=True) migrate.ForeignKeyConstraint(columns=[ limit_table.c.service_id, limit_table.c.region_id, limit_table.c.resource_name ], refcolumns=[ registered_limit_table.c.service_id, registered_limit_table.c.region_id, registered_limit_table.c.resource_name ]).create()
def upgrade(migrate_engine): meta = MetaData(bind=migrate_engine) shadow_agents = Table('shadow_agents', meta, autoload=True) pods = Table('pods', meta, autoload=True) col_pod_id_fkey = shadow_agents.c.pod_id col_pod_id_pkey = pods.c.pod_id # In the migration script 003_shadow_agent.py, the pod_id string length in # shadow_agents table is 64, but pod_id string length in pods table # is 36. The string length in foreign key and primary key isn't the same if col_pod_id_fkey.type.length != col_pod_id_pkey.type.length: # Delete the old foreign key constraint. If it exists, we can't # modify the pod_id length. migrate.ForeignKeyConstraint(columns=[shadow_agents.c.pod_id], refcolumns=[pods.c.pod_id]).drop() col_pod_id_fkey.alter(type=String(col_pod_id_pkey.type.length)) # Create the foreign key constraint migrate.ForeignKeyConstraint(columns=[shadow_agents.c.pod_id], refcolumns=[pods.c.pod_id]).create()
def handle_rid_index(meta, downgrade=False): if meta.bind.engine.name == 'sqlite': return resource = sa.Table('resource', meta, autoload=True) sample = sa.Table('sample', meta, autoload=True) params = { 'columns': [sample.c.resource_id], 'refcolumns': [resource.c.id], 'name': 'fk_sample_resource_id' } if meta.bind.engine.name == 'mysql': # For mysql dialect all dependent FK should be removed # before index create/delete migrate.ForeignKeyConstraint(**params).drop() index = sa.Index('idx_sample_rid_cname', sample.c.resource_id, sample.c.counter_name) index.create() if downgrade else index.drop() if meta.bind.engine.name == 'mysql': migrate.ForeignKeyConstraint(**params).create()
def upgrade(migrate_engine): meta = MetaData(bind=migrate_engine) resource_routings = Table('resource_routings', meta, autoload=True) pods = Table('pods', meta, autoload=True) col_pod_id_fkey = getattr(resource_routings.c, 'pod_id') col_pod_id_pkey = getattr(pods.c, 'pod_id') # In the migration script 002_resource.py, the pod_id string length in # resource_routings table is 64, but pod_id string length in pods table # is 36. The string length in foreign key and primary key isn't the same if col_pod_id_fkey.type.length != col_pod_id_pkey.type.length: # Delete the old constraint. If it exists, we can't modify the # pod_id length. migrate.ForeignKeyConstraint(columns=[resource_routings.c.pod_id], refcolumns=[pods.c.pod_id]).drop() col_pod_id_fkey.alter(type=String(col_pod_id_pkey.type.length)) # Create the foreign key constraint migrate.ForeignKeyConstraint(columns=[resource_routings.c.pod_id], refcolumns=[pods.c.pod_id]).create()
def _migrate_to_region(migrate_engine, region_table, endpoint_table): endpoints = list(endpoint_table.select().execute()) for endpoint in endpoints: new_values = {'region': endpoint.region_id} f = endpoint_table.c.id == endpoint.id update = endpoint_table.update().where(f).values(new_values) migrate_engine.execute(update) if 'sqlite' != migrate_engine.name: migrate.ForeignKeyConstraint(columns=[endpoint_table.c.region_id], refcolumns=[region_table.c.id], name='fk_endpoint_region_id').drop() endpoint_table.c.region_id.drop()
def upgrade(migrate_engine): meta = sa.MetaData(bind=migrate_engine) meter = sa.Table('meter', meta, sa.Column('id', sa.Integer, primary_key=True), sa.Column('name', sa.String(255), nullable=False), sa.Column('type', sa.String(255)), sa.Column('unit', sa.String(255)), sa.UniqueConstraint('name', 'type', 'unit', name='def_unique'), mysql_engine='InnoDB', mysql_charset='utf8') meter.create() sample = sa.Table('sample', meta, autoload=True) query = sa.select( [sample.c.counter_name, sample.c.counter_type, sample.c.counter_unit]).distinct() for row in query.execute(): meter.insert().values(name=row['counter_name'], type=row['counter_type'], unit=row['counter_unit']).execute() meter_id = sa.Column('meter_id', sa.Integer) meter_id.create(sample) params = {'columns': [sample.c.meter_id], 'refcolumns': [meter.c.id]} if migrate_engine.name == 'mysql': params['name'] = 'fk_sample_meter_id' if migrate_engine.name != 'sqlite': migrate.ForeignKeyConstraint(**params).create() index = sa.Index('ix_meter_name', meter.c.name) index.create(bind=migrate_engine) for row in sa.select([meter]).execute(): (sample.update().where( sa.and_(sample.c.counter_name == row['name'], sample.c.counter_type == row['type'], sample.c.counter_unit == row['unit'])).values({ sample.c.meter_id: row['id'] }).execute()) handle_rid_index(meta) sample.c.counter_name.drop() sample.c.counter_type.drop() sample.c.counter_unit.drop() sample.c.counter_volume.alter(name='volume')
def upgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine cascaded_sites = sql.Table('cascaded_sites', meta, sql.Column('site_id', sql.String(length=64), primary_key=True), sql.Column('site_name', sql.String(length=64), unique=True, nullable=False), sql.Column('az_id', sql.String(length=64), nullable=False), mysql_engine='InnoDB', mysql_charset='utf8') cascaded_site_service_configuration = sql.Table( 'cascaded_site_service_configuration', meta, sql.Column('service_id', sql.String(length=64), primary_key=True), sql.Column('site_id', sql.String(length=64), nullable=False), sql.Column('service_type', sql.String(length=64), nullable=False), sql.Column('service_url', sql.String(length=512), nullable=False), mysql_engine='InnoDB', mysql_charset='utf8') cascaded_site_services = sql.Table('cascaded_site_services', meta, sql.Column('site_id', sql.String(length=64), primary_key=True), mysql_engine='InnoDB', mysql_charset='utf8') tables = [ cascaded_sites, cascaded_site_service_configuration, cascaded_site_services ] for table in tables: table.create() fkey = { 'columns': [cascaded_site_service_configuration.c.site_id], 'references': [cascaded_sites.c.site_id] } migrate.ForeignKeyConstraint(columns=fkey['columns'], refcolumns=fkey['references'], name=fkey.get('name')).create()
def upgrade(migrate_engine): if migrate_engine.name == 'sqlite': upgrade_sqlite(migrate_engine) return meta = sqlalchemy.MetaData() meta.bind = migrate_engine tmpl_table = sqlalchemy.Table('raw_template', meta, autoload=True) # drop constraint fkey = migrate.ForeignKeyConstraint(columns=[tmpl_table.c.predecessor], refcolumns=[tmpl_table.c.id], name='predecessor_fkey_ref') fkey.drop() tmpl_table.c.predecessor.drop()