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()

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()

def downgrade(migrate_engine):
    metadata.bind = migrate_engine
    # Load existing tables
    metadata.reflect()
    # NOTE: all new data added in the upgrade method is eliminated here via table drops
    # Drop 1 foreign key constraint from the metadata_file table
    try:
        MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
    except NoSuchTableError:
        MetadataFile_table = None
        log.debug( "Failed loading table metadata_file" )
    try:
        LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
    except NoSuchTableError:
        LibraryDatasetDatasetAssociation_table = None
        log.debug( "Failed loading table library_dataset_dataset_association" )
    if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None:
        try:
            cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id],
                                         [LibraryDatasetDatasetAssociation_table.c.id],
                                         name='metadata_file_lda_id_fkey' )
            # Drop the constraint
            cons.drop()
        except Exception, e:
            log.debug( "Dropping foreign key constraint 'metadata_file_lda_id_fkey' from table 'metadata_file' failed: %s" % ( str( e ) ) )

def downgrade(migrate_engine):
    if migrate_engine.name == 'sqlite':
        return
    meta = MetaData(bind=migrate_engine)
    for table_name, ref, child in TABLES:
        table = Table(table_name, meta, autoload=True)

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

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

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

        with migrate_engine.begin():
            restore_rows(migrate_engine, meta, table_name)

        # compute_node_stats has a missing foreign key and is a child of
        # of compute_nodes. Don't bother processing it as a child since
        # only want to restore the dump once
        if child and table_name != 'compute_nodes':
            child_table_name, child_column_name, child_ref_column_name = child

            with migrate_engine.begin():
                restore_rows(migrate_engine, meta, child_table_name)

def downgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine

    fk_name = None

    if migrate_engine.name == 'mysql':
        fk_name = 'reservations_ibfk_1'
    elif migrate_engine.name == 'postgresql':
        fk_name = 'reservations_usage_id_fkey'

    # NOTE: MySQL and PostgreSQL Cannot drop the quota_usages table
    # until the foreign key is removed.  We remove the foreign key first,
    # and then we drop the table.
    table = Table('reservations', meta, autoload=True)
    ref_table = Table('reservations', meta, autoload=True)
    params = {'columns': [table.c['usage_id']],
              'refcolumns': [ref_table.c['id']],
              'name': fk_name}

    if fk_name:
        fkey = ForeignKeyConstraint(**params)
        fkey.drop()

    quota_classes = Table('quota_classes', meta, autoload=True)
    quota_classes.drop()

    quota_usages = Table('quota_usages', meta, autoload=True)
    quota_usages.drop()

    reservations = Table('reservations', meta, autoload=True)
    reservations.drop()

def downgrade(migrate_engine):
    """Remove volume_type_rate_limit table."""
    meta = MetaData()
    meta.bind = migrate_engine

    qos_specs = Table('quality_of_service_specs', meta, autoload=True)

    if migrate_engine.name == 'mysql':
        # NOTE(alanmeadows): MySQL Cannot drop column qos_specs_id
        # until the foreign key volumes_types_ibfk_1 is removed.  We
        # remove the foreign key first, and then we drop the column.
        table = Table('volume_types', meta, autoload=True)
        ref_table = Table('volume_types', meta, autoload=True)
        params = {'columns': [table.c['qos_specs_id']],
                  'refcolumns': [ref_table.c['id']],
                  'name': 'volume_types_ibfk_1'}

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

    volume_types = Table('volume_types', meta, autoload=True)
    qos_specs_id = Column('qos_specs_id', String(36))

    volume_types.drop_column(qos_specs_id)
    qos_specs.drop()

def upgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine

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

    _correct_sqlite_unique_constraints(migrate_engine, compute_nodes)

    # Make the service_id column nullable
    compute_nodes.c.service_id.alter(nullable=True)
    shadow_compute_nodes.c.service_id.alter(nullable=True)

    for fk in compute_nodes.foreign_keys:
        if fk.column == services.c.id:
            # Delete the FK
            fkey = ForeignKeyConstraint(columns=[compute_nodes.c.service_id],
                                        refcolumns=[services.c.id],
                                        name=fk.name)
            fkey.drop()
            break
    for index in compute_nodes.indexes:
        if 'service_id' in index.columns:
            # Delete the nested index which was created by the FK
            index.drop()
            break

def 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()

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 ) ) )

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 ) ) )

def downgrade(migrate_engine):
    if migrate_engine.name == "sqlite":
        return
    meta = MetaData(bind=migrate_engine)
    storage_pools = Table("storage_pools", meta, autoload=True)
    storage_groups = Table("storage_groups", meta, autolaod=True)
    params = {"columns": [storage_pools.c.primary_storage_group_id], "refcolumns": [storage_groups.c.id]}
    fkey = ForeignKeyConstraint(**params)
    fkey.drop()

def 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()

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()

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()

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 ) )

def downgrade(migrate_engine):
    # Operations to reverse the above upgrade go here.
    meta.bind = migrate_engine

    tt = Table('proc_SelectedHistoriesTable', meta, autoload=True)

    con = ForeignKeyConstraint([tt.c.selected_arar_id], [t.c.id])
    con.drop()

    t.drop()
    th.drop()
    tt.c.selected_arar_id.drop()

    def _remove_foreign_key_constraints(engine, meta, table_name):
        inspector = reflection.Inspector.from_engine(engine)

        for fk in inspector.get_foreign_keys(table_name):
            source_table = Table(table_name, meta, autoload=True)
            target_table = Table(fk['referred_table'], meta, autoload=True)

            fkey = ForeignKeyConstraint(
                columns=_get_columns(source_table, fk['constrained_columns']),
                refcolumns=_get_columns(target_table, fk['referred_columns']),
                name=fk['name'])
            fkey.drop()

def upgrade(migrate_engine):
    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.")

def upgrade(migrate_engine):
    meta = sa.MetaData(bind=migrate_engine)
    load_tables = dict((table_name, sa.Table(table_name, meta,
                                             autoload=True))
                       for table_name in TABLES)

    if migrate_engine.name != 'sqlite':
        for table_name, indexes in INDEXES.items():
            table = load_tables[table_name]
            for column, ref_table_name, ref_column_name in indexes:
                ref_table = load_tables[ref_table_name]
                params = {'columns': [table.c[column]],
                          'refcolumns': [ref_table.c[ref_column_name]]}

                if migrate_engine.name == "mysql" and \
                        table_name != 'alarm_history':
                    params['name'] = "_".join(('fk', table_name, column))
                elif migrate_engine.name == "postgresql" and \
                        table_name == "sample":
                    # The fk contains the old table name
                    params['name'] = "_".join(('meter', column, 'fkey'))

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

    sourceassoc = load_tables['sourceassoc']
    if migrate_engine.name != 'sqlite':
        idx = sa.Index('idx_su', sourceassoc.c.source_id,
                       sourceassoc.c.user_id)
        idx.drop(bind=migrate_engine)
        idx = sa.Index('idx_sp', sourceassoc.c.source_id,
                       sourceassoc.c.project_id)
        idx.drop(bind=migrate_engine)

        params = {}
        if migrate_engine.name == "mysql":
            params = {'name': 'uniq_sourceassoc0sample_id'}
        uc = UniqueConstraint('sample_id', table=sourceassoc, **params)
        uc.create()

        params = {}
        if migrate_engine.name == "mysql":
            params = {'name': 'uniq_sourceassoc0sample_id0user_id'}
        uc = UniqueConstraint('sample_id', 'user_id',
                              table=sourceassoc, **params)
        uc.drop()
    sourceassoc.c.user_id.drop()
    sourceassoc.c.project_id.drop()

    for table_name in TABLES_DROP:
        sa.Table(table_name, meta, autoload=True).drop()

def downgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    load_tables = dict((table_name, Table(table_name, meta, autoload=True))
                       for table_name in TABLES)
    for table_name, indexes in INDEXES.items():
        table = load_tables[table_name]
        for column, ref_table_name, ref_column_name in indexes:
            ref_table = load_tables[ref_table_name]
            params = {'columns': [table.c[column]],
                      'refcolumns': [ref_table.c[ref_column_name]]}
            if migrate_engine.name == 'mysql':
                params['name'] = "_".join(('fk', table_name, column))
            fkey = ForeignKeyConstraint(**params)
            fkey.drop()