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 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 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 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): # 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 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): 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): # 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 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 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 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 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): """Add missing foreign key constraint on pci_devices.compute_node_id.""" 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.create()
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 downgrade(migrate_engine): meta.bind = migrate_engine account = Table('account', meta, autoload=True) cons = ForeignKeyConstraint([account.c.rooms], [room.c.id]) account = Table('account', meta, autoload=True) cons.drop() account.c.rooms.drop() room.drop()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine jobs = Table('jobs', meta, autoload=True) job_metadata = Table('job_metadata', meta, autoload=True) if not job_metadata.foreign_keys: cons = ForeignKeyConstraint([job_metadata.c.job_id], [jobs.c.id]) cons.create()
def drop_foreign_key_constraints(constraint_names, columns, ref_columns): """Drop the foreign key constraints that match the given criteria. :param constraint_names: List of foreign key constraint names :param columns: List of the foreign key columns. :param ref_columns: List of the referenced columns. """ for constraint_name in constraint_names: fkey_constraint = ForeignKeyConstraint(columns=columns, refcolumns=ref_columns, name=constraint_name) fkey_constraint.drop()
def upgrade(migrate_engine): """Add missing foreign key constraint on pci_devices.compute_node_id.""" 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.create()
def upgrade(migrate_engine): class AuthUserLog(Base): """ event: L - Login R - Register P - Password F - Forgot """ __tablename__ = 'auth_user_log' __table_args__ = {"sqlite_autoincrement": True} id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey("auth_users.id", onupdate='CASCADE', ondelete='CASCADE'), index=True) time = Column(DateTime(), default=func.now()) ip_addr = Column(Unicode(39), nullable=False) internal_user = Column(Boolean, nullable=False, default=False) external_user = Column(Boolean, nullable=False, default=False) event = Column(Enum(u'L',u'R',u'P',u'F', name=u"event"), default=u'L') recreate_constraints = [AuthUserLog] # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata debug = True session.configure(bind=migrate_engine) migrate_engine.echo=debug metadata.bind = migrate_engine metadata.reflect(only=['auth_users']) r_meta = s.MetaData(migrate_engine, True) def commit(): session.commit() r_meta.bind.execute ('COMMIT;') metadata.bind.execute('COMMIT;') # create constraints fks = [] for md in recreate_constraints: t = md.__table__ rt = r_meta.tables[t.name] rt_constraints = [a for a in rt.foreign_keys] for cs in deepcopy(t.foreign_keys): if cs.__class__.__name__ == 'ForeignKey': table, column = cs.target_fullname.split('.') target = [r_meta.tables[table].c[column]] parent = [r_meta.tables[cs.parent.table.name].c[cs.parent.name]] fk = ForeignKeyConstraint(columns=parent,refcolumns=target) fk.use_alter = cs.use_alter fk.ondelete = 'CASCADE' fk.onupdate = 'CASCADE' fk.name = cs.name fks.append(fk) if (cs.name in [a.name for a in rt_constraints] or (cs.target_fullname in [a.target_fullname for a in rt_constraints])): fk.drop(migrate_engine) commit() for fk in fks: fk.create(migrate_engine) commit()
def upgrade(migrate_engine): meta.bind = migrate_engine job_queue = Table('spider_execution_queue', meta, autoload=True) job_queue_slot = Column('slot', Integer, default=1) job_queue_slot.create(job_queue) spider_settings.create() spiders = Table('spiders', meta, autoload=True) spider_settings_spider_id_fk = ForeignKeyConstraint( [spider_settings.c.spider_id], [spiders.c.id]) spider_settings_spider_id_fk.create()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine schedules = Table('schedules', meta, autoload=True) schedule_metadata = Table('schedule_metadata', meta, autoload=True) if not schedule_metadata.foreign_keys: cons = ForeignKeyConstraint([schedule_metadata.c.schedule_id], [schedules.c.id]) cons.create()
def upgrade(migrate_engine): meta.bind = migrate_engine account = Table('account', meta, autoload=True) rooms = Column('rooms', Integer) room.create() rooms.create(account) cons = ForeignKeyConstraint([account.c.rooms], [room.c.id]) cons.create()
def create_foreign_key_constraints(constraint_names, columns, ref_columns): """Create the foreign key constraints that match the given criteria. :param constraint_names: List of foreign key constraint names :param columns: List of the foreign key columns. :param ref_columns: List of the referenced columns. """ for constraint_name in constraint_names: fkey_constraint = ForeignKeyConstraint(columns=columns, refcolumns=ref_columns, name=constraint_name) fkey_constraint.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 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 upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine table_names = set(TABLES_FKEYS_SET_NULL.keys()) table_names.update(TABLES_COLUMNS_NOT_NULL.keys()) tables = {} for t in table_names: tables[t] = sautils.Table(t, 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 postgres have different naming convention so this is not very # easy to have generic code working. for t, keys in TABLES_FKEYS_SET_NULL.items(): table = tables[t] for fk in table.constraints: if not isinstance(fk, sa.ForeignKeyConstraint): continue for c in fk.elements: if str(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='SET NULL'))) for table, args, kwargs in fks_to_change: fk = ForeignKeyConstraint(*args, **kwargs) table.append_constraint(fk) try: fk.drop() except NotSupportedError: # some versions of sqlite do not support drop, # but will still update the fk pass fk.create() for t, cols in TABLES_COLUMNS_NOT_NULL.items(): table = tables[t] if table.dialect_options.get('mysql', {}).get('engine') == 'InnoDB': migrate_engine.execute('SET FOREIGN_KEY_CHECKS = 0;') try: for c in table.columns: if c.name in cols: c.alter(nullable=False) finally: if table.dialect_options.get('mysql', {}).get('engine') == 'InnoDB': migrate_engine.execute('SET FOREIGN_KEY_CHECKS = 1;')
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 downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta = MetaData(bind=migrate_engine) tt = Table('meas_IsotopeTable', meta, autoload=True) t = Table('proc_FitTable', meta, autoload=True) cons = ForeignKeyConstraint([t.c.isotope_id], [tt.c.id]) cons.drop() try: t.c.isotope_id.drop() except: pass
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 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 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): 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 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.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 upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta = MetaData(bind=migrate_engine) t = Table('proc_FitTable', meta, autoload=True) try: t.c.isotope.drop() except: pass c = Column('isotope_id', Integer) try: c.create(t) except: pass tt = Table('meas_IsotopeTable', meta, autoload=True) cons = ForeignKeyConstraint([t.c.isotope_id], [tt.c.id]) cons.create()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta.bind = migrate_engine tt = Table('gen_ProjectTable', meta, autoload=True) cons = ForeignKeyConstraint([t.c.project_id], [tt.c.id]) cons.drop() tt2 = Table('meas_AnalysisTable', meta, autoload=True) cons = ForeignKeyConstraint([t2.c.analysis_id], [tt2.c.id]) cons.drop() cons = ForeignKeyConstraint([t2.c.figure_id], [tt2.c.id]) cons.drop() t.drop() t2.drop()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta = MetaData(bind=migrate_engine) mt = Table('meas_MeasurementTable', meta, autoload=True) et = Table('meas_ExtractionTable', meta, autoload=True) st = Table('meas_ScriptTable', meta, autoload=True) fk = ForeignKeyConstraint([mt.c.script_id], [st.c.id]) fk.drop() fk = ForeignKeyConstraint([et.c.script_id], [st.c.id]) fk.drop() st.drop() c = Column('script_blob', BLOB) c.create(mt) c = Column('hash', String(32)) c.create(mt) c = Column('script_name', String(80)) c.create(mt) # # c = Column('script_blob', BLOB) c.create(et) c = Column('hash', String(32)) c.create(et) c = Column('script_name', BLOB) c.create(et) mt.c.script_id.drop() et.c.script_id.drop()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta = MetaData(bind=migrate_engine) mt = Table('meas_MeasurementTable', meta, autoload=True) et = Table('meas_ExtractionTable', meta, autoload=True) mt.c.script_blob.drop() mt.c.hash.drop() mt.c.script_name.drop() et.c.script_blob.drop() et.c.hash.drop() et.c.script_name.drop() c = Column('script_id', Integer) c.create(mt) c = Column('script_id', Integer) c.create(et) st = Table('meas_ScriptTable', meta, Column('id', Integer, primary_key=True), Column('name', String(80)), Column('hash', String(32)), Column('blob', BLOB) ) st.create() fk = ForeignKeyConstraint([mt.c.script_id], [st.c.id]) fk.create() fk = ForeignKeyConstraint([et.c.script_id], [st.c.id]) fk.create()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta.bind = migrate_engine t.create() t2.create() tt = Table('gen_ProjectTable', meta, autoload=True) cons = ForeignKeyConstraint([t.c.project_id], [tt.c.id]) cons.create() tt2 = Table('meas_AnalysisTable', meta, autoload=True) cons = ForeignKeyConstraint([t2.c.analysis_id], [tt2.c.id]) cons.create() cons = ForeignKeyConstraint([t2.c.figure_id], [tt2.c.id]) cons.create()
def downgrade(migrate_engine): meta.bind = migrate_engine pool_attributes_table = Table('pool_attributes', meta, autoload=True) # pools = Table('pools', meta, autoload=True) constraint = UniqueConstraint('pool_id', 'key', 'value', name='unique_pool_attribute', table=pool_attributes_table) fk_constraint = ForeignKeyConstraint(columns=['pool_id'], refcolumns=['pools.id'], ondelete='CASCADE', table=pool_attributes_table) # First have to drop the ForeignKeyConstraint fk_constraint.drop() # Then drop the UniqueConstraint constraint.drop() # Then recreate the ForeignKeyConstraint fk_constraint.create()
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.bind.echo = True setup_all() try: # constraints = ExtraData.table.constraints # for c in constraints: # if 'partnerLink_id' in c.columns: fkc = ForeignKeyConstraint([ExtraData.table.c.partnerLink_id], [PartnerLink.table.c.id]) fkc.drop() indexes = ExtraData.table.indexes for l in indexes: if l.name == "ix_extradata_partnerLink_id": l.drop() except: print traceback.format_exc() try: col = ExtraData.table.columns.get('partnerLink_id') col.drop(ExtraData.table) except Exception, e: print str(e) print traceback.format_exc()
def upgrade(migrate_engine): metadata = sa.MetaData() metadata.bind = migrate_engine tables = {} for t in TABLES_FKEYS: tables[t] = sautils.Table(t, 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 postgres have different naming convention so this is not very # easy to have generic code working. for t, keys in iteritems(TABLES_FKEYS): table = tables[t] for fk in table.constraints: if not isinstance(fk, sa.ForeignKeyConstraint): continue for c in fk.elements: if str(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: # some versions of sqlite do not support drop, # but will still update the fk pass fk.create()
def recreate_table_fkeys(table, session,): """Recreate all foreign keys in the table or declarative object""" if not isinstance(table, Table): table = table.__table__ migrate_engine = session.bind metadata = table.metadata r_meta = s.MetaData(migrate_engine, True) def commit(): session.commit() r_meta.bind.execute ('COMMIT;') metadata.bind.execute('COMMIT;') fks = [] commit() t = table rt = r_meta.tables[t.name] rt_constraints = [a for a in rt.foreign_keys] for cs in deepcopy(t.foreign_keys): if cs.__class__.__name__ == 'ForeignKey': table, column = cs.target_fullname.split('.') target = [r_meta.tables[table].c[column]] parent_table = r_meta.tables[cs.parent.table.name] parent = [parent_table.c[cs.parent.name]] fk = ForeignKeyConstraint(columns=parent,refcolumns=target) fk.use_alter = cs.use_alter fk.ondelete = 'CASCADE' fk.onupdate = 'CASCADE' fk.name = cs.name fks.append(fk) if (cs.name in [a.name for a in rt_constraints] or (cs.target_fullname in [a.target_fullname for a in rt_constraints])): try: fk.drop(migrate_engine) commit() except:pass for fk in fks: fk.create(migrate_engine) commit()
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): # 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 = MetaData(bind=migrate_engine) mt = Table('meas_MeasurementTable', meta, autoload=True) sp = Table('meas_SpectrometerParametersTable', meta, autoload=True) c = Column('measurement_id', Integer) c.create(sp) fk = ForeignKeyConstraint([sp.c.measurement_id], [mt.c.id]) fk.create() fk = ForeignKeyConstraint([mt.c.spectrometer_parameters_id], [sp.c.id]) fk.drop() mt.c.spectrometer_parameters_id.drop() sp.c.hash.drop()
def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. meta.bind = migrate_engine mst = Table('gen_MassSpectrometerTable', meta, autoload=True) tt = Table('meas_ExtractionTable', meta, autoload=True) cons = ForeignKeyConstraint([t.c.mass_spectrometer_id], [mst.c.id]) cons.drop() cons = ForeignKeyConstraint([tt.c.sensitivity_id], [t.c.id]) cons.drop() tt.c.sensitivity_multiplier.drop() tt.c.sensitivity_id.drop() t.drop()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta = MetaData(bind=migrate_engine) mt = Table('meas_MeasurementTable', meta, autoload=True) c = Column('spectrometer_parameters_id', Integer) c.create(mt) sp = Table('meas_SpectrometerParametersTable', meta, autoload=True) fk = ForeignKeyConstraint([mt.c.spectrometer_parameters_id], [sp.c.id]) fk.create() fk = ForeignKeyConstraint([sp.c.measurement_id], [mt.c.id]) fk.drop() sp.c.measurement_id.drop() c = Column('hash', String(32)) c.create(sp)
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 upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta.bind = migrate_engine t.create() # tt = Table('meas_ExtractionTable', meta, autoload=True) c = Column('sensitivity_multiplier', Float) c.create(tt, populate_default=True) # c = Column('sensitivity_id', Integer) c.create(tt) # cons = ForeignKeyConstraint([tt.c.sensitivity_id], [t.c.id]) cons.create() # mst = Table('gen_MassSpectrometerTable', meta, autoload=True) cons = ForeignKeyConstraint([t.c.mass_spectrometer_id], [mst.c.id]) cons.create()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata meta.bind = migrate_engine shelter_puppies = Table('shelter_puppies', meta, Column('id', INTEGER, primary_key=True, nullable=False), Column('shelter_id', INTEGER), Column('puppy_id', INTEGER) ) shelter_puppies.create() 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.create() cons2 = ForeignKeyConstraint([shelter_puppies.c.puppy_id], [puppy.c.id]) cons2.create() pass
def drop_foreign_key(fk_def): table = fk_def[0]._get_table() col = fk_def[0] ref_col = fk_def[1] # Use .copy() to avoid the set changing during the for operation for fk in table.foreign_keys.copy(): # Check if the fk is the one we want if fk.column == col and fk.parent == ref_col: fkc = ForeignKeyConstraint([fk.column], [fk.parent], name=fk.constraint.name) fkc.drop() # Check if the fk is the one we want (sometimes it seems the parent # / col is switched if fk.parent == col and fk.column == ref_col: fkc = ForeignKeyConstraint([fk.parent], [fk.column], name=fk.constraint.name) fkc.drop()
def upgrade(migrate_engine): metadata = MetaData() metadata.bind = migrate_engine group_table = Table('group', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('name', UnicodeText, unique=True, nullable=False), Column('title', UnicodeText), Column('description', UnicodeText), Column('created', DateTime, default=datetime.now), ) group_revision_table = Table('group_revision', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('name', UnicodeText, nullable=False), Column('title', UnicodeText), Column('description', UnicodeText), Column('created', DateTime, default=datetime.now), Column('state', UnicodeText), Column('revision_id', UnicodeText, ForeignKey('revision.id'), primary_key=True), Column('continuity_id', UnicodeText, ForeignKey('group.id')) ) package_group_table = Table('package_group', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('package_id', UnicodeText, ForeignKey('package.id')), Column('group_id', UnicodeText, ForeignKey('group.id')), ) package_group_revision_table = Table('package_group_revision', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('package_id', UnicodeText, ForeignKey('package.id')), Column('group_id', UnicodeText, ForeignKey('group.id')), Column('state', UnicodeText), Column('revision_id', UnicodeText, ForeignKey('revision.id'), primary_key=True), Column('continuity_id', UnicodeText, ForeignKey('package_group.id')) ) group_extra_table = Table('group_extra', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('group_id', UnicodeText, ForeignKey('group.id')), Column('key', UnicodeText), Column('value', JsonType), ) group_extra_revision_table = Table('group_extra_revision', metadata, Column('id', UnicodeText, primary_key=True, default=make_uuid), Column('group_id', UnicodeText, ForeignKey('group.id')), Column('key', UnicodeText), Column('value', JsonType), Column('state', UnicodeText), Column('revision_id', UnicodeText, ForeignKey('revision.id'), primary_key=True), Column('continuity_id', UnicodeText, ForeignKey('group_extra.id')) ) revision_table = Table('revision', metadata, autoload=True) package_table = Table('package', metadata, autoload=True) rev_id = make_uuid() q = revision_table.insert(values={'id': rev_id, 'author': u'system', 'message': u"Add versioning to groups, group_extras and package_groups", 'timestamp': datetime.utcnow(), 'state': u'active'}) r = migrate_engine.execute(q) # handle groups: # BUG in sqlalchemy-migrate 0.4/0.5.4: "group" isn't escaped properly when sent to # postgres. # cf http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=32 #state = Column('state', UnicodeText) #revision_id = Column('revision_id', UnicodeText) #state.create(group_table) #revision_id.create(group_table) migrate_engine.execute('ALTER TABLE "group" ADD COLUMN state TEXT') migrate_engine.execute('ALTER TABLE "group" ADD COLUMN revision_id TEXT') #q = group_table.update(values={'state': 'active', # 'revision_id': rev_id}) #migrate_engine.execute(q) migrate_engine.execute('UPDATE "group" SET revision_id = \'%s\', state=\'active\'' % rev_id) #fk = ForeignKeyConstraint(['revision_id'], [revision_table.c.id], table=group_table) #fk.create(migrate_engine) migrate_engine.execute('ALTER TABLE "group" ADD CONSTRAINT "group_revision_id_fkey" ' + \ 'FOREIGN KEY (revision_id) REFERENCES revision(id)') group_revision_table.create() for row in migrate_engine.execute(group_table.select()): group_rev = dict(row.items()) group_rev['continuity_id'] = group_rev['id'] # otherwise, this doesn't get mapped due to the bug above: group_rev['state'] = u'active' group_rev['revision_id'] = rev_id q = group_revision_table.insert(values=group_rev) migrate_engine.execute(q) state = Column('state', UnicodeText) revision_id = Column('revision_id', UnicodeText) state.create(package_group_table) revision_id.create(package_group_table) q = package_group_table.update(values={'state': u'active', 'revision_id': rev_id}) migrate_engine.execute(q) fk = ForeignKeyConstraint(['revision_id'], [revision_table.c.id], table=package_group_table, name = 'package_group_revision_id_fkey') fk.create(migrate_engine) package_group_revision_table.create() for row in migrate_engine.execute(package_group_table.select()): pkg_group_rev = dict(row.items()) pkg_group_rev['continuity_id'] = pkg_group_rev['id'] q = package_group_revision_table.insert(values=pkg_group_rev) migrate_engine.execute(q) state = Column('state', UnicodeText) revision_id = Column('revision_id', UnicodeText) state.create(group_extra_table) revision_id.create(group_extra_table) q = group_extra_table.update(values={'state': u'active', 'revision_id': rev_id}) migrate_engine.execute(q) fk = ForeignKeyConstraint(['revision_id'], [revision_table.c.id], table=group_extra_table, name='group_extra_revision_id_fkey') fk.create(migrate_engine) group_extra_revision_table.create() for row in migrate_engine.execute(group_extra_table.select()): group_extra_rev = dict(row.items()) group_extra_rev['continuity_id'] = group_rev['id'] q = group_extra_revision_table.insert(values=group_extra_rev) migrate_engine.execute(q)