def upgrade(migrate_engine):

    metadata.bind = migrate_engine

    # dummy definitions to satisfy foreign keys
    Table('instance', metadata, autoload=True)
    Table('group', metadata, autoload=True)

    # add the column for the polymorphic identity
    # we have to use 'nullable=True' cause the values are
    # null when the column is created
    type_col = Column('type', String(40), nullable=True)
    type_col.create(badge_table)

    # fill column with the right values
    select = badge_table.select().with_only_columns(
        ['id', 'title', 'badge_delegateable', 'badge_delegateable_category'])
    badges_query_result = migrate_engine.execute(select)
    for values in badges_query_result:
        (id_, title, delegateable, category) = values
        if category:
            type_ = CATEGORY_BADGE
        elif delegateable:
            type_ = DELEGATEABLE_BADGE
        else:
            type_ = USER_BADGE
        update = badge_table.update().values(type=type_).where(
            badge_table.c.id == id_)
        migrate_engine.execute(update)

    # drop the old columns
    badge_table.c.badge_delegateable.drop()
    badge_table.c.badge_delegateable_category.drop()

    type_col.alter(nullable=False)

def upgrade(migrate_engine):
    meta.bind = migrate_engine

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

    # Create the new inherit_ttl column
    inherit_ttl = Column('inherit_ttl', Boolean(), default=True)
    inherit_ttl.create(records_table)

    # Semi-Populate the new inherit_ttl column. We'll need to do a cross-db
    # join from powerdns.records -> powerdns.domains -> designate.domains, so
    # we can't perform the second half here.
    query = records_table.update().values(inherit_ttl=False)
    query = query.where(records_table.c.ttl != None)
    query.execute()

    # If there are records without an explicity configured TTL, we'll need
    # a manual post-migration step.
    query = records_table.select()
    query = query.where(records_table.c.ttl == None)
    c = query.count()

    if c > 0:
        pmq = ('UPDATE powerdns.records JOIN powerdns.domains ON powerdns.reco'
               'rds.domain_id = powerdns.domains.id JOIN designate.domains ON '
               'powerdns.domains.designate_id = designate.domains.id SET power'
               'dns.records.ttl = designate.domains.ttl WHERE powerdns.records'
               '.inherit_ttl = 1;')

        LOG.warning(_LW('**** A manual post-migration step is required ****'))
        LOG.warning(_LW('Please issue this query: %s' % pmq))

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    print(__doc__)
    metadata.reflect()
    try:
        LibraryDataset_table = Table("library_dataset", metadata, autoload=True)
        c = Column("purged", Boolean, index=True, default=False)
        c.create(LibraryDataset_table, index_name='ix_library_dataset_purged')
        assert c is LibraryDataset_table.c.purged
    except Exception:
        log.exception("Adding purged column to library_dataset table failed.")
    # Update the purged flag to the default False
    cmd = "UPDATE library_dataset SET purged = %s;" % engine_false(migrate_engine)
    try:
        migrate_engine.execute(cmd)
    except Exception:
        log.exception("Setting default data for library_dataset.purged column failed.")

    # Update the purged flag for those LibaryDatasets whose purged flag should be True.  This happens
    # when the LibraryDataset has no active LibraryDatasetDatasetAssociations.
    cmd = "SELECT * FROM library_dataset WHERE deleted = %s;" % engine_true(migrate_engine)
    deleted_lds = migrate_engine.execute(cmd).fetchall()
    for row in deleted_lds:
        cmd = "SELECT * FROM library_dataset_dataset_association WHERE library_dataset_id = %d AND library_dataset_dataset_association.deleted = %s;" % (int(row.id), engine_false(migrate_engine))
        active_lddas = migrate_engine.execute(cmd).fetchall()
        if not active_lddas:
            print("Updating purged column to True for LibraryDataset id : ", int(row.id))
            cmd = "UPDATE library_dataset SET purged = %s WHERE id = %d;" % (engine_true(migrate_engine), int(row.id))
            migrate_engine.execute(cmd)

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    instance_table = Table(
        "instance",
        metadata,
        Column("id", Integer, primary_key=True),
        Column("key", Unicode(20), nullable=False, unique=True),
        Column("label", Unicode(255), nullable=False),
        Column("description", UnicodeText(), nullable=True),
        Column("required_majority", Float, nullable=False),
        Column("activation_delay", Integer, nullable=False),
        Column("create_time", DateTime, default=func.now()),
        Column("access_time", DateTime, default=func.now(), onupdate=func.now()),
        Column("delete_time", DateTime, nullable=True),
        Column("creator_id", Integer, ForeignKey("user.id"), nullable=False),
        Column("default_group_id", Integer, ForeignKey("group.id"), nullable=True),
        Column("allow_adopt", Boolean, default=True),
        Column("allow_delegate", Boolean, default=True),
        Column("allow_propose", Boolean, default=True),
        Column("allow_index", Boolean, default=True),
        Column("hidden", Boolean, default=False),
        Column("locale", Unicode(7), nullable=True),
        Column("css", UnicodeText(), nullable=True),
        Column("frozen", Boolean, default=False),
        Column("milestones", Boolean, default=False),
        Column("use_norms", Boolean, nullable=True, default=True),
        Column("require_selection", Boolean, nullable=True, default=False),
        Column("is_authenticated", Boolean, nullable=True, default=False),
    )

    hide_categories = Column("hide_global_categories", Boolean, nullable=True, default=False)
    hide_categories.create(instance_table)
    u = instance_table.update(values={"hide_global_categories": False})
    migrate_engine.execute(u)

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

    instances = Table('instances', meta, autoload=True)
    volumes = Table('volumes', meta, autoload=True)
    instance_id_column = Column('instance_id', Integer)

    instance_id_column.create(volumes)
    try:
        volumes.update().values(
            instance_id=select(
                [instances.c.id],
                instances.c.uuid == volumes.c.instance_uuid)
                ).execute()
    except Exception:
        instance_id_column.drop()

    fkeys = list(volumes.c.instance_id.foreign_keys)
    if fkeys:
        try:
            fk_name = fkeys[0].constraint.name
            ForeignKeyConstraint(
                columns=[volumes.c.instance_id],
                refcolumns=[instances.c.id],
                name=fk_name).create()

        except Exception:
            LOG.error(_("foreign key could not be created"))
            raise

    volumes.c.instance_uuid.drop()

def upgrade(migrate_engine):
    meta.bind = migrate_engine

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

    recordset_id = Column('designate_recordset_id', UUID())
    recordset_id.create(records_table)

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

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

    types = {}
    for instance in migrate_engine.execute(instances.select()):
        if instance.instance_type_id is None:
            types[instance.id] = None
            continue
        try:
            types[instance.id] = int(instance.instance_type_id)
        except ValueError:
            LOG.warn("Instance %s did not have instance_type_id "
                         "converted to an integer because its value is %s" %
                          (instance.id, instance.instance_type_id))
            types[instance.id] = None

    integer_column = Column('instance_type_id_int', Integer(), nullable=True)
    string_column = instances.c.instance_type_id

    integer_column.create(instances)
    for instance_id, instance_type_id in types.iteritems():
        update = instances.update().\
                where(instances.c.id == instance_id).\
                values(instance_type_id_int=instance_type_id)
        migrate_engine.execute(update)

    string_column.alter(name='instance_type_id_str')
    integer_column.alter(name='instance_type_id')
    string_column.drop()

 def check_create_fk(self, from_table_id, to_table_id, ignoreexisting=False):
     from_type = setobject_type_registry.lookup_by_table(from_table_id)        
     to_type = setobject_type_registry.lookup_by_table(to_table_id)        
     pk = to_type.get_primary_key_attr_name()
     # Now add foreign key if not existant yet
     if not field_exists(from_table_id, self.foreignkeycol):
         col = Column(
             self.foreignkeycol,
             getattr(to_type.get_table_class().c, pk).type,
             ForeignKey(to_table_id + "." + pk),
         )
         col.create(from_type.get_table_class())
        
         # The foreign key column has been newly created
         Session().flush()
         # deferred import
         from p2.datashackle.core.models.mapping import map_tables
         map_tables(exclude_sys_tables=True)
     else:
         # it exists, check whether it is what we want or something else
         fkset = getattr(from_type.get_table_class().c, self.foreignkeycol).foreign_keys
         if len(fkset) > 0:
             for fk in fkset:
                 if str(fk.column) == to_table_id + "." + pk \
                         and ignoreexisting == True:
                     return # this is what we want! fine.
             raise UserException("A relation with a similar Data Field Name but targetting the table '" + \
                 str(fk.column).split('.',1)[0] + "' already exists. Please use another Data Field Name.")
         raise UserException("The column '" + self.foreignkeycol + "' in the table '" + to_table_id + \
             "' does already exist. Please choose a unique Data Field Name that doesn't collide with existing data columns.")

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):
    print(__doc__)
    metadata.bind = migrate_engine
    metadata.reflect()
    # Initialize.
    if migrate_engine.name == 'mysql' or migrate_engine.name == 'sqlite':
        default_false = "0"
    elif migrate_engine.name in ['postgresql', 'postgres']:
        default_false = "false"

    try:
        RepositoryMetadata_table = Table("repository_metadata", metadata, autoload=True)
    except NoSuchTableError:
        RepositoryMetadata_table = None
        log.debug("Failed loading table repository_metadata.")

    if RepositoryMetadata_table is not None:
        # Create the test_install_error column.
        c = Column("test_install_error", Boolean, default=False, index=True)
        try:
            c.create(RepositoryMetadata_table, index_name="ix_repository_metadata_ttie")
            assert c is RepositoryMetadata_table.c.test_install_error
            migrate_engine.execute("UPDATE repository_metadata SET test_install_error=%s" % default_false)
        except Exception:
            log.exception("Adding test_install_error column to the repository_metadata table failed.")

    # Create skip_tool_test table.
    try:
        SkipToolTest_table.create()
    except Exception:
        log.exception("Creating the skip_tool_test table failed.")

def upgrade(migrate_engine):
    print __doc__
    metadata.bind = migrate_engine
    metadata.reflect()
    # Initialize.
    if migrate_engine.name == 'mysql' or migrate_engine.name == 'sqlite':
        default_false = "0"
    elif migrate_engine.name in ['postgresql', 'postgres']:
        default_false = "false"

    try:
        RepositoryMetadata_table = Table( "repository_metadata", metadata, autoload=True )
    except NoSuchTableError:
        RepositoryMetadata_table = None
        log.debug( "Failed loading table repository_metadata." )

    if RepositoryMetadata_table is not None:
        # Drop the tool_test_errors column from the repository_metadata table as it is poorly named.  It will be replaced with the new
        # tool_test_results column.
        try:
            col = RepositoryMetadata_table.c.tool_test_errors
            col.drop()
        except Exception, e:
            log.debug( "Dropping column 'tool_test_errors' from repository_metadata table failed: %s" % ( str( e ) ) )

        # Create the tool_test_results column to replace the ill-named tool_test_errors column just dropped above.
        c = Column( "tool_test_results", JSONType, nullable=True )
        try:
            c.create( RepositoryMetadata_table )
            assert c is RepositoryMetadata_table.c.tool_test_results
        except Exception, e:
            print "Adding tool_test_results column to the repository_metadata table failed: %s" % str( e )

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

    table = Table('user', meta, autoload=True)
    col = Column('optional_attributes',
                 MutationDict.as_mutable(JSONEncodedDict))
    col.create(table)

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    print(__doc__)
    metadata.reflect()
    try:
        SampleDataset_table = Table( "sample_dataset", metadata, autoload=True )
    except NoSuchTableError:
        SampleDataset_table = None
        log.debug( "Failed loading table 'sample_dataset'" )

    if SampleDataset_table is not None:
        cmd = "SELECT id, file_path FROM sample_dataset"
        result = migrate_engine.execute( cmd )
        filepath_dict = {}
        for r in result:
            id = int(r[0])
            filepath_dict[id] = r[1]
        # remove the 'file_path' column
        try:
            SampleDataset_table.c.file_path.drop()
        except Exception:
            log.exception("Deleting column 'file_path' from the 'sample_dataset' table failed.")
        # create the column again
        try:
            col = Column( "file_path", TEXT )
            col.create( SampleDataset_table )
            assert col is SampleDataset_table.c.file_path
        except Exception:
            log.exception("Creating column 'file_path' in the 'sample_dataset' table failed.")

        for id, file_path in filepath_dict.items():
            cmd = "update sample_dataset set file_path='%s' where id=%i" % (file_path, id)
            migrate_engine.execute( cmd )

def upgrade(migrate_engine):
    meta.bind = migrate_engine

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

    disabled = Column('disabled', TINYINT(1), server_default='0')
    disabled.create(records_table)

def downgrade(migrate_engine):
    metadata.bind = migrate_engine
    metadata.reflect()
    # Drop missing_test_components and tool_test_results from the repository_metadata table and add tool_test_errors to the repository_metadata table.
    RepositoryMetadata_table = Table("repository_metadata", metadata, autoload=True)

    # Drop the missing_test_components column.
    try:
        RepositoryMetadata_table.c.missing_test_components.drop()
    except Exception:
        log.exception("Dropping column missing_test_components from the repository_metadata table failed.")

    # Drop the tool_test_results column.
    try:
        RepositoryMetadata_table.c.tool_test_results.drop()
    except Exception:
        log.exception("Dropping column tool_test_results from the repository_metadata table failed.")

    # Create the tool_test_errors column.
    c = Column("tool_test_errors", JSONType, nullable=True)
    try:
        c.create(RepositoryMetadata_table)
        assert c is RepositoryMetadata_table.c.tool_test_errors
    except Exception:
        log.exception("Adding tool_test_errors column to the repository_metadata table failed.")

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    print(__doc__)
    metadata.reflect()
    ToolShedRepository_table = Table("tool_shed_repository", metadata, autoload=True)
    col = Column("installed_changeset_revision", TrimmedString(255))
    try:
        col.create(ToolShedRepository_table)
        assert col is ToolShedRepository_table.c.installed_changeset_revision
    except Exception:
        log.exception("Adding installed_changeset_revision column to the tool_shed_repository table failed.")
    # Update each row by setting the value of installed_changeset_revison to be the value of changeset_revision.
    # This will be problematic if the value of changeset_revision was updated to something other than the value
    # that it was when the repository was installed (because the install path determined in real time will attempt to
    # find the repository using the updated changeset_revison instead of the required installed_changeset_revision),
    # but at the time this script was written, this scenario is extremely unlikely.
    cmd = "SELECT id AS id, " \
        + "installed_changeset_revision AS installed_changeset_revision, " \
        + "changeset_revision AS changeset_revision " \
        + "FROM tool_shed_repository;"
    tool_shed_repositories = migrate_engine.execute(cmd).fetchall()
    update_count = 0
    for row in tool_shed_repositories:
        cmd = "UPDATE tool_shed_repository " \
            + "SET installed_changeset_revision = '%s' " % row.changeset_revision \
            + "WHERE changeset_revision = '%s';" % row.changeset_revision
        migrate_engine.execute(cmd)
        update_count += 1
    print("Updated the installed_changeset_revision column for ", update_count, " rows in the tool_shed_repository table.  ")

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 upgrade(migrate_engine):
    metadata.bind = migrate_engine
    print(__doc__)
    metadata.reflect()
    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:
        # create the column again as JSONType
        try:
            col = Column("notification", JSONType())
            col.create(Request_table)
            assert col is Request_table.c.notification
        except Exception:
            log.exception("Creating column 'notification' in the 'request' table failed.")

        cmd = "SELECT id, user_id, notify FROM request"
        result = migrate_engine.execute(cmd)
        for r in result:
            id = int(r[0])
            notify_new = dict(email=[], sample_states=[], body='', subject='')
            cmd = "UPDATE request SET notification='%s' WHERE id=%i" % (dumps(notify_new), id)
            migrate_engine.execute(cmd)

        # remove the 'notify' column for non-sqlite databases.
        if migrate_engine.name != 'sqlite':
            try:
                Request_table.c.notify.drop()
            except Exception:
                log.exception("Deleting column 'notify' from the 'request' table failed.")

def upgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine
    dialect = migrate_engine.url.get_dialect().name
    instance_actions = Table('instance_actions', meta, autoload=True)
    instances = Table('instances', meta, autoload=True)
    uuid_column = Column('instance_uuid', String(36))
    uuid_column.create(instance_actions)

    try:
        instance_actions.update().values(
            instance_uuid=select(
                [instances.c.uuid],
                instances.c.id == instance_actions.c.instance_id)
        ).execute()
    except Exception:
        uuid_column.drop()
        raise

    if not dialect.startswith('sqlite'):
        fkeys = list(instance_actions.c.instance_id.foreign_keys)
        if fkeys:
            try:
                fkey_name = fkeys[0].constraint.name
                ForeignKeyConstraint(columns=[instance_actions.c.instance_id],
                                     refcolumns=[instances.c.id],
                                     name=fkey_name).drop()
            except Exception:
                LOG.error(_("foreign key constraint couldn't be removed"))
                raise

    instance_actions.c.instance_id.drop()

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    message_table.create()
    message_recipient_table.create()

    email_messages = Column('email_messages', Boolean, default=True)
    email_messages.create(user_table)