def downgrade(migrate_engine):
    metadata.bind = migrate_engine
    metadata.reflect()
    # Drop 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 = Job_table.c.library_folder_id
            col.drop()
        except Exception as e:
            log.debug( "Dropping column 'library_folder_id' from job table failed: %s" % ( str( e ) ) )
    # Drop the job_to_output_library_dataset table
    try:
        JobToOutputLibraryDatasetAssociation_table.drop()
    except Exception as e:
        print(str(e))
        log.debug( "Dropping job_to_output_library_dataset table failed: %s" % str( e ) )
    # Drop 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.drop()
    except Exception as e:
        print(str(e))
        log.debug( "Dropping index 'ix_dataset_state' from dataset table failed: %s" % str( e ) )

def upgrade(migrate_engine):
    meta.bind = migrate_engine

    records_table = Table("records", meta, autoload=True)

    index = Index("designate_recordset_id", records_table.c.designate_recordset_id)
    index.create()

def downgrade(migrate_engine):
    meta.bind = migrate_engine

    records_table = Table('records', meta, autoload=True)
    index = Index('designate_recordset_id',
                  records_table.c.designate_recordset_id)
    index.drop()

def upgrade(migrate_engine):
    print(__doc__)
    metadata.bind = migrate_engine
    HistoryDatasetAssociation_table = Table("history_dataset_association", metadata, autoload=True)
    # Load existing tables
    metadata.reflect()
    # Add 2 indexes to the galaxy_user table
    i = Index('ix_hda_extension', HistoryDatasetAssociation_table.c.extension)
    try:
        i.create()
    except Exception:
        log.exception("Adding index 'ix_hda_extension' to history_dataset_association table failed.")

    # Set the default data in the galaxy_user table, but only for null values
    cmd = "UPDATE history_dataset_association SET extension = 'qual454' WHERE extension = 'qual' and peek like \'>%%\'"
    try:
        migrate_engine.execute(cmd)
    except Exception:
        log.exception("Resetting extension qual to qual454 in history_dataset_association failed.")
    cmd = "UPDATE history_dataset_association SET extension = 'qualsolexa' WHERE extension = 'qual' and peek not like \'>%%\'"
    try:
        migrate_engine.execute(cmd)
    except Exception:
        log.exception("Resetting extension qual to qualsolexa in history_dataset_association failed.")
    # Add 1 index to the history_dataset_association table
    try:
        i.drop()
    except Exception:
        log.exception("Dropping index 'ix_hda_extension' to history_dataset_association table failed.")

def loadTable(datapath, datatable, delimiter, dtype, engine, indexCols=[], skipLines=1, chunkSize=100000, **kwargs):
    cnt = 0
    with open(datapath) as fh:
        while cnt < skipLines:
            fh.readline()
            cnt += 1
        cnt = 0
        tmpstr = ''
        for l in fh:
            tmpstr += l
            cnt += 1
            if cnt%chunkSize == 0:
                print "Loading chunk #%i"%(int(cnt/chunkSize))
                dataArr = numpy.genfromtxt(StringIO(tmpstr), dtype=dtype, delimiter=delimiter, **kwargs)
                engine.execute(datatable.insert(), [dict((name, numpy.asscalar(l[name])) for name in l.dtype.names) for l in dataArr])
                tmpstr = ''
        #Clean up the last chunk
        if len(tmpstr) > 0:
            dataArr = numpy.genfromtxt(StringIO(tmpstr), dtype=dtype, delimiter=delimiter, **kwargs)
            try:
                engine.execute(datatable.insert(), [dict((name, numpy.asscalar(l[name])) for name in l.dtype.names) for l in dataArr])
            # If the file only has one line, the result of genfromtxt is a 0-d array, so cannot be iterated
            except TypeError:
                engine.execute(datatable.insert(), [dict((name, numpy.asscalar(dataArr[name])) for name in dataArr.dtype.names),])

    for col in indexCols:
        if hasattr(col, "__iter__"):
            print "Creating index on %s"%(",".join(col))
            colArr = (datatable.c[c] for c in col)
            i = Index('%sidx'%''.join(col), *colArr)
        else:
            print "Creating index on %s"%(col)
            i = Index('%sidx'%col, datatable.c[col])

        i.create(engine)

def upgrade(migrate_engine):
    display_migration_details()
    metadata.bind = migrate_engine
    db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
    HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
    # Load existing tables
    metadata.reflect()
    # Add 2 indexes to the galaxy_user table
    i = Index( 'ix_hda_extension', HistoryDatasetAssociation_table.c.extension )
    try:
        i.create()
    except Exception as e:
        log.debug( "Adding index 'ix_hda_extension' to history_dataset_association table failed: %s" % ( str( e ) ) )

    # Set the default data in the galaxy_user table, but only for null values
    cmd = "UPDATE history_dataset_association SET extension = 'qual454' WHERE extension = 'qual' and peek like \'>%%\'"
    try:
        db_session.execute( cmd )
    except Exception as e:
        log.debug( "Resetting extension qual to qual454 in history_dataset_association failed: %s" % ( str( e ) ) )
    cmd = "UPDATE history_dataset_association SET extension = 'qualsolexa' WHERE extension = 'qual' and peek not like \'>%%\'"
    try:
        db_session.execute( cmd )
    except Exception as e:
        log.debug( "Resetting extension qual to qualsolexa in history_dataset_association failed: %s" % ( str( e ) ) )
    # Add 1 index to the history_dataset_association table
    try:
        i.drop()
    except Exception as e:
        log.debug( "Dropping index 'ix_hda_extension' to history_dataset_association table failed: %s" % ( str( e ) ) )

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    print(__doc__)
    metadata.reflect()
    try:
        if migrate_engine.name == 'mysql':
            # Strip slug index prior to creation so we can do it manually.
            slug_index = None
            for ix in Page_table.indexes:
                if ix.name == 'ix_page_slug':
                    slug_index = ix
            Page_table.indexes.remove(slug_index)
        Page_table.create()
        if migrate_engine.name == 'mysql':
            # Create slug index manually afterward.
            i = Index("ix_page_slug", Page_table.c.slug, mysql_length=200)
            i.create()
    except Exception:
        log.exception("Could not create page table")
    try:
        PageRevision_table.create()
    except Exception:
        log.exception("Could not create page_revision table")

    # Add 1 column to the user table
    User_table = Table("galaxy_user", metadata, autoload=True)
    col = Column('username', String(255), index=True, unique=True, default=False)
    col.create(User_table, index_name='ix_user_username', unique_name='username')
    assert col is User_table.c.username

def downgrade(migrate_engine):
    meta.bind = migrate_engine

    records_table = Table("records", meta, autoload=True)

    name_idx = Index("rec_name_index", records_table.c.name)
    name_idx.create()

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

    t = Table("aggregate_metadata", meta, autoload=True)
    i = Index("aggregate_metadata_key_idx", t.c.key)
    i.drop(migrate_engine)

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

    build_requests = Table('build_requests', meta, autoload=True)

    columns_to_add = [
            ('instance_uuid',
                Column('instance_uuid', String(length=36))),
            ('instance',
                Column('instance', Text())),
    ]
    for (col_name, column) in columns_to_add:
        if not hasattr(build_requests.c, col_name):
            build_requests.create_column(column)

    for index in build_requests.indexes:
        if [c.name for c in index.columns] == ['instance_uuid']:
            break
    else:
        index = Index('build_requests_instance_uuid_idx',
                build_requests.c.instance_uuid)
        index.create()

    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_requests0instance_uuid' not in constr_names:
        UniqueConstraint('instance_uuid', table=build_requests,
                name='uniq_build_requests0instance_uuid').create()

def _mk_index_on(engine, ts_name):
    fc_table = introspect_table(engine, "{}_FieldChangeV2".format(ts_name))

    fast_fc_lookup = Index('idx_fast_fieldchange_lookup', fc_table.c.StartOrderID)
    try:
        fast_fc_lookup.create(engine)
    except (sqlalchemy.exc.OperationalError, sqlalchemy.exc.ProgrammingError) as e:
        logger.warning("Skipping index creation on {}, because of {}".format(fc_table.name, e.message))

def upgrade(migrate_engine):
    """Add an index to make the scheduler lookups of compute_nodes and joined
    compute_node_stats more efficient.
    """
    meta = MetaData(bind=migrate_engine)
    cn_stats = Table(TABLE_NAME, meta, autoload=True)
    idx = Index(IDX_NAME, cn_stats.c.compute_node_id, cn_stats.c.deleted)
    idx.create(migrate_engine)

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

    t = Table('instance_type_extra_specs', meta, autoload=True)
    i = Index('instance_type_extra_specs_instance_type_id_key_idx',
              t.c.instance_type_id, t.c.key)
    i.drop(migrate_engine)

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

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

    index = Index('reservations_uuid_idx', reservations.c.uuid)
    index.drop(migrate_engine)

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

    images = Table('images', meta, autoload=True)

    index = Index(INDEX_NAME, images.c.checksum)
    index.create(migrate_engine)

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

    t = Table('migrations', meta, autoload=True)
    i = Index('migrations_instance_uuid_and_status_idx', t.c.deleted,
            t.c.instance_uuid, t.c.status)
    i.drop(migrate_engine)

 def _create_indexes(self):
     # create an index on timeseries values if it doesn't exist
     try:
         i = Index('ix_timeseries_values_id',
                   model.DataValue.__table__.c.timeseries_id)
         i.create(self.engine)
     except OperationalError:
         pass

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

    t = Table('agent_builds', meta, autoload=True)
    i = Index('agent_builds_hypervisor_os_arch_idx',
              t.c.hypervisor, t.c.os, t.c.architecture)
    i.drop(migrate_engine)

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

    t = Table('bw_usage_cache', meta, autoload=True)
    i = Index('bw_usage_cache_uuid_start_period_idx',
              t.c.uuid, t.c.start_period)
    i.drop(migrate_engine)

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

    t = Table('dns_domains', meta, autoload=True)
    i = Index('dns_domains_domain_deleted_idx',
              t.c.domain, t.c.deleted)
    i.drop(migrate_engine)