Python ForeignKeyConstraint.create方法代码示例

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    port_table = Table('port', meta, autoload=True)
    subnet_table = Table('subnet', meta, autoload=True)

    subnet_id = Column('subnet_id', Integer)
    port_table.create_column(subnet_id)

    ports = port_table.select().execute()
    subnets = subnet_table.select().execute()
    subnets = dict((netaddr.IPNetwork('%s/%s' % (net.ip, net.mask), version=4),
                   net.id) for net in subnets)

    for port in ports:
        match = [v for k, v in subnets.items()
                 if netaddr.IPAddress(port.ip) in k]
        if len(match) != 1:
            raise RuntimeError('More than one subnet matches %s' % port.ip)
        port_table.update().where(port_table.c.id == port.id).\
            values(subnet_id=match[0]).execute()

    port_table.c.subnet_id.alter(nullable=False)

    fkey = ForeignKeyConstraint(columns=[port_table.c.subnet_id],
                                refcolumns=[subnet_table.c.id])
    fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    display_migration_details()
    # Load existing tables
    metadata.reflect()
    try:
        User_table = Table( "galaxy_user", metadata, autoload=True )
    except NoSuchTableError:
        User_table = None
        log.debug( "Failed loading table galaxy_user" )
    if User_table is not None:
        try:
            col = Column( "form_values_id", Integer, index=True )
            col.create( User_table, index_name='ix_user_form_values_id')
            assert col is User_table.c.form_values_id
        except Exception, e:
            log.debug( "Adding column 'form_values_id' to galaxy_user table failed: %s" % ( str( e ) ) )
        try:
            FormValues_table = Table( "form_values", metadata, autoload=True )
        except NoSuchTableError:
            FormValues_table = None
            log.debug( "Failed loading table form_values" )
        if migrate_engine.name != 'sqlite':
            # Add 1 foreign key constraint to the form_values table
            if User_table is not None and FormValues_table is not None:
                try:
                    cons = ForeignKeyConstraint( [User_table.c.form_values_id],
                                                 [FormValues_table.c.id],
                                                 name='user_form_values_id_fk' )
                    # Create the constraint
                    cons.create()
                except Exception, e:
                    log.debug( "Adding foreign key constraint 'user_form_values_id_fk' to table 'galaxy_user' failed: %s" % ( str( e ) ) )

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def downgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine

    compute_nodes = Table('compute_nodes', meta, autoload=True)
    shadow_compute_nodes = Table('shadow_compute_nodes', meta, autoload=True)
    services = Table('services', meta, autoload=True)

    _correct_sqlite_unique_constraints(migrate_engine, compute_nodes)

    # Make the service_id field not nullable
    # NOTE(sbauza): Beyond the point of this commit, service_id will not be
    # updated, but previous commits still do. We can tho safely go back to
    # a state where all the compute nodes are providing this field.
    compute_nodes.c.service_id.alter(nullable=False)
    shadow_compute_nodes.c.service_id.alter(nullable=False)

    # Adding only FK if not existing yet
    fkeys = {fk.parent.name: fk.column
             for fk in compute_nodes.foreign_keys}
    if 'service_id' in fkeys and fkeys['service_id'] == services.c.id:
        return

    # NOTE(sbauza): See 216_havana.py for the whole logic
    if migrate_engine.name == 'postgresql':
        # PostgreSQL names things like it wants (correct and compatible!)
        fkey = ForeignKeyConstraint(columns=[compute_nodes.c.service_id],
                                    refcolumns=[services.c.id])
        fkey.create()
    else:
        # For MySQL we name our fkeys explicitly so they match Havana
        fkey = ForeignKeyConstraint(columns=[compute_nodes.c.service_id],
                                    refcolumns=[services.c.id],
                                    name='fk_compute_nodes_service_id')
        fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    display_migration_details()
    # Load existing tables
    metadata.reflect()
    # Create the folder_id column
    try:
        Request_table = Table( "request", metadata, autoload=True )
    except NoSuchTableError:
        Request_table = None
        log.debug( "Failed loading table request" )
    if Request_table is not None:
        try:
            col = Column( "folder_id", Integer, index=True )
            col.create( Request_table, index_name='ix_request_folder_id')
            assert col is Request_table.c.folder_id
        except Exception, e:
            log.debug( "Adding column 'folder_id' to request table failed: %s" % ( str( e ) ) )
        try:
            LibraryFolder_table = Table( "library_folder", metadata, autoload=True )
        except NoSuchTableError:
            LibraryFolder_table = None
            log.debug( "Failed loading table library_folder" )
        # Add 1 foreign key constraint to the library_folder table
        if migrate_engine.name != 'sqlite' and Request_table is not None and LibraryFolder_table is not None:
            try:
                cons = ForeignKeyConstraint( [Request_table.c.folder_id],
                                             [LibraryFolder_table.c.id],
                                             name='request_folder_id_fk' )
                # Create the constraint
                cons.create()
            except Exception, e:
                log.debug( "Adding foreign key constraint 'request_folder_id_fk' to table 'library_folder' failed: %s" % ( str( e ) ) )

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def cascade_fkeys(metadata, restore=False):
    """ Sets all fkeys to cascade on update """
    for table_name, table in metadata.tables.items():
        for fkey in list(table.foreign_keys):
            if restore:
                if fkey.constraint.name in cascade_fkeys.fkey_onupdate_restore:
                    onupdate = cascade_fkeys.fkey_onupdate_restore[
                        fkey.constraint.name]
                else:
                    continue
            else:
                cascade_fkeys.fkey_onupdate_restore[fkey.constraint.name] = \
                    fkey.constraint.onupdate
                onupdate = "CASCADE"

            params = {
                'columns': fkey.constraint.columns,
                'refcolumns': [fkey.column],
                'name': fkey.constraint.name,
                'onupdate': fkey.constraint.onupdate,
                'ondelete': fkey.constraint.ondelete,
                'deferrable': fkey.constraint.deferrable,
                'initially': fkey.constraint.initially,
                'table': table
            }

            fkey_constraint = ForeignKeyConstraint(**params)
            fkey_constraint.drop()

            params['onupdate'] = onupdate
            fkey_constraint = ForeignKeyConstraint(**params)
            fkey_constraint.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    if migrate_engine.name == 'sqlite':
        return
    meta = MetaData(bind=migrate_engine)
    for table_name, ref, child in TABLES:
        table = Table(table_name, meta, autoload=True)

        column_name, ref_table_name, ref_column_name = ref
        column = table.c[column_name]

        ref_table = Table(ref_table_name, meta, autoload=True)
        ref_column = ref_table.c[ref_column_name]

        subq = select([ref_column]).where(ref_column != None)

        if child:
            # Dump and cleanup rows in child table first
            child_table_name, child_column_name, child_ref_column_name = child

            child_table = Table(child_table_name, meta, autoload=True)
            child_column = child_table.c[child_column_name]

            child_ref_column = table.c[child_ref_column_name]

            child_subq = select([child_ref_column]).where(~ column.in_(subq))
            dump_cleanup_rows(migrate_engine, meta, child_table,
                              child_column.in_(child_subq))

        dump_cleanup_rows(migrate_engine, meta, table, ~ column.in_(subq))

        params = {'columns': [column], 'refcolumns': [ref_column]}
        if migrate_engine.name == 'mysql':
            params['name'] = "_".join(('fk', table_name, column_name))
        fkey = ForeignKeyConstraint(**params)
        fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def downgrade(migrate_engine):
    if migrate_engine.name == 'sqlite':
        return
    meta = MetaData(bind=migrate_engine)
    load_tables = dict((table_name, Table(table_name, meta, autoload=True))
                       for table_name in TABLES)
    for table_name, indexes in INDEXES.items():
        table = load_tables[table_name]
        # Save data that conflicted with FK.
        columns = [column.copy() for column in table.columns]
        table_dump = Table('dump027_' + table_name, meta, *columns)
        table_dump.create()
        for column, ref_table_name, ref_column_name in indexes:
            ref_table = load_tables[ref_table_name]
            subq = select([getattr(ref_table.c, ref_column_name)])
            sql = utils.InsertFromSelect(table_dump, table.select().where(
                ~ getattr(table.c, column).in_(subq)))
            sql_del = table.delete().where(
                ~ getattr(table.c, column).in_(subq))
            migrate_engine.execute(sql)
            migrate_engine.execute(sql_del)

            params = {'columns': [table.c[column]],
                      'refcolumns': [ref_table.c[ref_column_name]]}
            if migrate_engine.name == 'mysql':
                params['name'] = "_".join(('fk', table_name, column))
            fkey = ForeignKeyConstraint(**params)
            fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    meta = MetaData(migrate_engine)
    trait_type = Table(
        'trait_type', meta,
        Column('id', Integer, primary_key=True),
        Column('desc', String(255)),
        Column('data_type', Integer),
        UniqueConstraint('desc', 'data_type', name="tt_unique")
    )
    trait = Table('trait', meta, autoload=True)
    unique_name = Table('unique_name', meta, autoload=True)
    trait_type.create(migrate_engine)
    # Trait type extracts data from Trait and Unique name.
    # We take all trait names from Unique Name, and data types
    # from Trait. We then remove dtype and name from trait, and
    # remove the name field.

    conn = migrate_engine.connect()
    sql = ("INSERT INTO trait_type "
           "SELECT unique_name.id, unique_name.key, trait.t_type FROM trait "
           "INNER JOIN unique_name "
           "ON trait.name_id = unique_name.id "
           "GROUP BY unique_name.id, unique_name.key, trait.t_type")
    conn.execute(sql)
    conn.close()

    # Now we need to drop the foreign key constraint, rename
    # the trait.name column, and re-add a new foreign
    # key constraint
    params = {'columns': [trait.c.name_id],
              'refcolumns': [unique_name.c.id]}
    if migrate_engine.name == 'mysql':
        params['name'] = "trait_ibfk_1"  # foreign key to the unique name table
    fkey = ForeignKeyConstraint(**params)
    fkey.drop()

    Column('trait_type_id', Integer).create(trait)

    # Move data from name_id column into trait_type_id column
    query = select([trait.c.id, trait.c.name_id])
    for key, value in migration.paged(query):
        trait.update().where(trait.c.id == key)\
            .values({"trait_type_id": value}).execute()

    trait.c.name_id.drop()

    params = {'columns': [trait.c.trait_type_id],
              'refcolumns': [trait_type.c.id]}
    if migrate_engine.name == 'mysql':
        params['name'] = "_".join(('fk', 'trait_type', 'id'))

    fkey = ForeignKeyConstraint(**params)
    fkey.create()

    # Drop the t_type column to data_type.
    trait.c.t_type.drop()

    # Finally, drop the unique_name table - we don't need it
    # anymore.
    unique_name.drop()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    event_type = Table(
        'event_type', meta,
        Column('id', Integer, primary_key=True),
        Column('desc', String(255), unique=True),
        mysql_engine='InnoDB',
        mysql_charset='utf8',
    )
    event_type.create()
    event = Table('event', meta, autoload=True)
    unique_name = Table('unique_name', meta, autoload=True)

    # Event type is a specialization of Unique name, so
    # we insert into the event_type table all the distinct
    # unique names from the event.unique_name field along
    # with the key from the unique_name table, and
    # then rename the event.unique_name field to event.event_type
    conn = migrate_engine.connect()
    sql = ("INSERT INTO event_type "
           "SELECT unique_name.id, unique_name.key FROM event "
           "INNER JOIN unique_name "
           "ON event.unique_name_id = unique_name.id "
           "GROUP BY unique_name.id")
    conn.execute(sql)
    conn.close()
    # Now we need to drop the foreign key constraint, rename
    # the event.unique_name column, and re-add a new foreign
    # key constraint
    params = {'columns': [event.c.unique_name_id],
              'refcolumns': [unique_name.c.id]}
    if migrate_engine.name == 'mysql':
        params['name'] = "event_ibfk_1"
    fkey = ForeignKeyConstraint(**params)
    fkey.drop()

    Column('event_type_id', Integer).create(event)

    # Move data from unique_name_id column into event_type_id column
    # and delete the entry from the unique_name table
    query = select([event.c.id, event.c.unique_name_id])
    for key, value in migration.paged(query):
        event.update().where(event.c.id == key)\
            .values({"event_type_id": value}).execute()
        unique_name.delete()\
            .where(unique_name.c.id == key).execute()

    params = {'columns': [event.c.event_type_id],
              'refcolumns': [event_type.c.id]}
    if migrate_engine.name == 'mysql':
        params['name'] = "_".join(('fk', 'event_type', 'id'))
    fkey = ForeignKeyConstraint(**params)
    fkey.create()

    event.c.unique_name_id.drop()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def downgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine
    dns_domains_old = Table('dns_domains', meta, autoload=True)
    dns_domains_old.rename(name='dns_domains_old')

    # NOTE(dprince): manually remove pkey/fkey for postgres
    if migrate_engine.name == "postgresql":
        sql = """ALTER TABLE ONLY dns_domains_old DROP CONSTRAINT
              dns_domains_pkey;
              ALTER TABLE ONLY dns_domains_old DROP CONSTRAINT
              dns_domains_project_id_fkey;"""
        migrate_engine.execute(sql)

    #Bind new metadata to avoid issues after the rename
    meta = MetaData()
    meta.bind = migrate_engine

    dns_domains_new = Table('dns_domains', meta,
        Column('created_at', DateTime),
        Column('updated_at', DateTime),
        Column('deleted_at', DateTime),
        Column('deleted', Boolean),
        Column('domain', String(length=512), primary_key=True, nullable=False),
        Column('scope', String(length=255)),
        Column('availability_zone', String(length=255)),
        Column('project_id', String(length=255)),
        mysql_engine='InnoDB',
        mysql_charset='latin1',
    )
    dns_domains_new.create()

    dns_domains_old = Table('dns_domains_old', meta, autoload=True)
    record_list = list(dns_domains_old.select().execute())
    for rec in record_list:
        row = dns_domains_new.insert()
        row.execute({'created_at': rec['created_at'],
                    'updated_at': rec['updated_at'],
                    'deleted_at': rec['deleted_at'],
                    'deleted': rec['deleted'],
                    'domain': rec['domain'],
                    'scope': rec['scope'],
                    'availability_zone': rec['availability_zone'],
                    'project_id': rec['project_id'],
                    })

    dns_domains_old.drop()

    # NOTE(dprince): We can't easily add the MySQL Fkey on the downgrade
    # because projects is 'utf8' where dns_domains is 'latin1'.
    if migrate_engine.name != "mysql":
        projects = Table('projects', meta, autoload=True)
        fkey = ForeignKeyConstraint(columns=[dns_domains_new.c.project_id],
                               refcolumns=[projects.c.id])
        fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    if migrate_engine.name == "sqlite":
        return
    meta = MetaData(bind=migrate_engine)
    storage_pools = Table("storage_pools", meta, autoload=True)
    storage_groups = Table("storage_groups", meta, autoload=True)
    params = {"columns": [storage_pools.c.primary_storage_group_id], "refcolumns": [storage_groups.c.id]}
    if migrate_engine.name == "mysql":
        params["name"] = "_".join(("storage_pool", "primary_storage_group_id", "fkey"))
    fkey = ForeignKeyConstraint(**params)
    fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def downgrade(migrate_engine):
    """Convert volume_type from UUID back to int."""
    meta = MetaData()
    meta.bind = migrate_engine

    volumes = Table("volumes", meta, autoload=True)
    volume_types = Table("volume_types", meta, autoload=True)
    extra_specs = Table("volume_type_extra_specs", meta, autoload=True)

    fkey_remove_list = [volumes.c.volume_type_id, volume_types.c.id, extra_specs.c.volume_type_id]

    for column in fkey_remove_list:
        fkeys = list(column.foreign_keys)
        if fkeys:
            fkey_name = fkeys[0].constraint.name
            fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name)

            try:
                fkey.drop()
            except Exception:
                if migrate_engine.url.get_dialect().name.startswith("sqlite"):
                    pass
                else:
                    raise

    vtype_list = list(volume_types.select().execute())
    new_id = 1

    for t in vtype_list:
        volumes.update().where(volumes.c.volume_type_id == t["id"]).values(volume_type_id=new_id).execute()

        extra_specs.update().where(extra_specs.c.volume_type_id == t["id"]).values(volume_type_id=new_id).execute()

        volume_types.update().where(volume_types.c.id == t["id"]).values(id=new_id).execute()

        new_id += 1

    volumes.c.volume_type_id.alter(Integer)
    volume_types.c.id.alter(Integer)
    extra_specs.c.volume_type_id.alter(Integer)

    for column in fkey_remove_list:
        fkeys = list(column.foreign_keys)
        if fkeys:
            fkey_name = fkeys[0].constraint.name
            fkey = ForeignKeyConstraint(columns=[column], refcolumns=[volume_types.c.id], name=fkey_name)
            try:
                fkey.create()
                LOG.info("Created foreign key %s" % fkey_name)
            except Exception:
                if migrate_engine.url.get_dialect().name.startswith("sqlite"):
                    pass
                else:
                    raise

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    if migrate_engine.name == 'sqlite':
        return
    meta = MetaData(bind=migrate_engine)
    storage_pools = Table('storage_pools', meta, autoload=True)
    storage_groups = Table('storage_groups', meta, autoload=True)
    params = {'columns': [storage_pools.c.primary_storage_group_id],
              'refcolumns': [storage_groups.c.id]}
    if migrate_engine.name == 'mysql':
        params['name'] = "_".join(('storage_pools', 'primary_storage_group_ids', 'fkey'))
    fkey = ForeignKeyConstraint(**params)
    fkey.create()

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    display_migration_details()
    # Load existing tables
    metadata.reflect()
    # Create the job_to_output_library_dataset table
    try:
        JobToOutputLibraryDatasetAssociation_table.create()
    except Exception as e:
        print("Creating job_to_output_library_dataset table failed: %s" % str( e ))
        log.debug( "Creating job_to_output_library_dataset table failed: %s" % str( e ) )
    # Create the library_folder_id column
    try:
        Job_table = Table( "job", metadata, autoload=True )
    except NoSuchTableError:
        Job_table = None
        log.debug( "Failed loading table job" )
    if Job_table is not None:
        try:
            col = Column( "library_folder_id", Integer, index=True )
            col.create( Job_table, index_name='ix_job_library_folder_id')
            assert col is Job_table.c.library_folder_id
        except Exception as e:
            log.debug( "Adding column 'library_folder_id' to job table failed: %s" % ( str( e ) ) )
        try:
            LibraryFolder_table = Table( "library_folder", metadata, autoload=True )
        except NoSuchTableError:
            LibraryFolder_table = None
            log.debug( "Failed loading table library_folder" )
        # Add 1 foreign key constraint to the job table
        if migrate_engine.name != 'sqlite':
            # Sqlite can't alter-table-add-foreign-key
            if Job_table is not None and LibraryFolder_table is not None:
                try:
                    cons = ForeignKeyConstraint( [Job_table.c.library_folder_id],
                                                 [LibraryFolder_table.c.id],
                                                 name='job_library_folder_id_fk' )
                    # Create the constraint
                    cons.create()
                except Exception as e:
                    log.debug( "Adding foreign key constraint 'job_library_folder_id_fk' to table 'library_folder' failed: %s" % ( str( e ) ) )
    # Create the ix_dataset_state index
    try:
        Dataset_table = Table( "dataset", metadata, autoload=True )
    except NoSuchTableError:
        Dataset_table = None
        log.debug( "Failed loading table dataset" )
    i = Index( "ix_dataset_state", Dataset_table.c.state )
    try:
        i.create()
    except Exception as e:
        print(str(e))
        log.debug( "Adding index 'ix_dataset_state' to dataset table failed: %s" % str( e ) )

# 需要导入模块: from migrate import ForeignKeyConstraint [as 别名]
# 或者: from migrate.ForeignKeyConstraint import create [as 别名]
def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    print(__doc__)
    # Load existing tables
    metadata.reflect()
    # Create the folder_id column
    try:
        Request_table = Table("request", metadata, autoload=True)
    except NoSuchTableError:
        Request_table = None
        log.debug("Failed loading table request")
    if Request_table is not None:
        try:
            col = Column("folder_id", Integer, index=True)
            col.create(Request_table, index_name='ix_request_folder_id')
            assert col is Request_table.c.folder_id
        except Exception:
            log.exception("Adding column 'folder_id' to request table failed.")
        try:
            LibraryFolder_table = Table("library_folder", metadata, autoload=True)
        except NoSuchTableError:
            LibraryFolder_table = None
            log.debug("Failed loading table library_folder")
        # Add 1 foreign key constraint to the library_folder table
        if migrate_engine.name != 'sqlite' and Request_table is not None and LibraryFolder_table is not None:
            try:
                cons = ForeignKeyConstraint([Request_table.c.folder_id],
                                            [LibraryFolder_table.c.id],
                                            name='request_folder_id_fk')
                # Create the constraint
                cons.create()
            except Exception:
                log.exception("Adding foreign key constraint 'request_folder_id_fk' to table 'library_folder' failed.")
    # Create the type column in form_definition
    try:
        FormDefinition_table = Table("form_definition", metadata, autoload=True)
    except NoSuchTableError:
        FormDefinition_table = None
        log.debug("Failed loading table form_definition")
    if FormDefinition_table is not None:
        try:
            col = Column("type", TrimmedString(255), index=True)
            col.create(FormDefinition_table, index_name='ix_form_definition_type')
            assert col is FormDefinition_table.c.type
        except Exception:
            log.exception("Adding column 'type' to form_definition table failed.")
        try:
            col = Column("layout", JSONType())
            col.create(FormDefinition_table)
            assert col is FormDefinition_table.c.layout
        except Exception:
            log.exception("Adding column 'layout' to form_definition table failed.")