Python sqlalchemy 模块，Enum() 实例源码

def _get_field_value(column):
    """Get field value for resource creating

    returning None indicates that not setting this field in resource dict
    """
    if column.nullable:
        # just skip nullable column
        return None
    if isinstance(column.type, sql.Text):
        return 'fake_text'
    elif isinstance(column.type, sql.Enum):
        return column.type.enums[0]
    elif isinstance(column.type, sql.String):
        return 'fake_str'
    elif isinstance(column.type, sql.Integer):
        return 1
    elif isinstance(column.type, sql.Float):
        return 1.0
    elif isinstance(column.type, sql.Boolean):
        return True
    elif isinstance(column.type, sql.DateTime):
        return datetime.datetime.utcnow()
    else:
        return None

def task_table(base):
    class Task(base):
        __tablename__ = 'Task'

        id_ = Column(Integer, Sequence('task_id_seq'), primary_key=True)
        run_id = Column(String, default='')
        step_id = Column(String,)
        sequence = Column(Integer,)
        recovery = Column(Integer, nullable=False)
        pid = Column(Integer, nullable=True)
        status = Column(SQLEnum(TaskStatus), ) 
        result = Column(PickleType() , nullable=True,)
        created = Column(DateTime(), default=datetime.utcnow) 
        updated = Column(DateTime(), nullable=True, ) 

        __table_args__ = (
                UniqueConstraint('run_id', 'step_id', 'sequence', 'recovery'),
                )

        def __repr__(self):
            return "<Task(id='%s', run_id='%s', step_id='%s', sequence='%s', recovery='%s', pid='%s', status='%s', created='%s', updated='%s')>" % (
                self.id_, self.run_id, self.step_id, self.sequence, self.recovery, self.pid, self.status, self.created, self.updated)

    return Task

def test_fancy_coltypes(self):
        Table(
            'simple_items', self.metadata,
            Column('enum', ENUM('A', 'B', name='blah')),
            Column('bool', BOOLEAN),
            Column('number', NUMERIC(10, asdecimal=False)),
        )

        assert self.generate_code() == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, Enum, MetaData, Numeric, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('enum', Enum('A', 'B', name='blah')),
    Column('bool', Boolean),
    Column('number', Numeric(10, asdecimal=False))
)
"""

def test_enum_detection(self):
        Table(
            'simple_items', self.metadata,
            Column('enum', VARCHAR(255)),
            CheckConstraint(r"simple_items.enum IN ('A', '\'B', 'C')")
        )

        assert self.generate_code() == """\
# coding: utf-8
from sqlalchemy import Column, Enum, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('enum', Enum('A', "\\\\'B", 'C'))
)
"""

def upgrade():
    op.create_table('quark_locks',
                    sa.Column('created_at', sa.DateTime(), nullable=True),
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('type', sa.Enum('ip_address'), nullable=False),
                    sa.PrimaryKeyConstraint('id'),
                    mysql_engine='InnoDB')
    op.create_table('quark_lock_holders',
                    sa.Column('created_at', sa.DateTime(), nullable=True),
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('lock_id', sa.Integer(), nullable=False),
                    sa.Column('name', sa.String(length=255), nullable=True),
                    sa.ForeignKeyConstraint(['lock_id'], ['quark_locks.id'], ),
                    sa.PrimaryKeyConstraint('id'),
                    mysql_engine='InnoDB')
    op.add_column(u'quark_ip_addresses', sa.Column('lock_id', sa.Integer(),
                                                   nullable=True))

def setUp(self):
        super(Test4fc07b41d45c, self).setUp()
        self.previous_revision = "42a3c8c0db75"
        self.current_revision = "4fc07b41d45c"
        self.metadata = sa.MetaData(bind=self.engine)
        # NOTE(thomasem): Create a quark_ip_addresses table that has an
        # identical schema as the revision before it for the columns this data
        # migration is concerned with.
        self.ip_addresses_table = sa.Table(
            'quark_ip_addresses', self.metadata,
            sa.Column('id', sa.String(length=36), primary_key=True),
            sa.Column('_deallocated', sa.Boolean()),
            sa.Column('address_type', sa.Enum('fixed', 'shared', 'floating'))
        )
        self.metadata.create_all()
        alembic_command.stamp(self.config, self.previous_revision)

def sa_table():
    choices = ['a', 'b', 'c']
    meta = sa.MetaData()
    post = sa.Table(
        'test_post', meta,
        sa.Column('id', sa.Integer, nullable=False),
        sa.Column('title', sa.String(200), nullable=False),
        sa.Column('category', sa.String(200), nullable=True),
        sa.Column('body', sa.Text, nullable=False),
        sa.Column('views', sa.Integer, nullable=False),
        sa.Column('average_note', sa.Float, nullable=False),
        # sa.Column('pictures', postgresql.JSON, server_default='{}'),
        sa.Column('published_at', sa.DateTime, nullable=False),
        # sa.Column('tags', postgresql.ARRAY(sa.Integer), server_default='{}'),
        sa.Column('status',
                  sa.Enum(*choices, name="enum_name", native_enum=False),
                  server_default="a", nullable=False),
        sa.Column('visible', sa.Boolean, nullable=False),

        # Indexes #
        sa.PrimaryKeyConstraint('id', name='post_id_pkey'))
    return post

def document_schema():
    choices = ['a', 'b', 'c']
    schema = t.Dict({
        t.Key('_id'): MongoId,
        t.Key('title'): t.String(max_length=200),
        t.Key('category'): t.String(max_length=200),
        t.Key('body'): t.String,
        t.Key('views'): t.Int,
        t.Key('average_note'): t.Float,
        # t.Key('pictures'): t.Dict({}).allow_extra('*'),
        t.Key('published_at'): DateTime,
        # t.Key('tags'): t.List(t.Int),
        t.Key('status'): t.Enum(*choices),
        t.Key('visible'): t.StrBool,
    })
    return schema

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('surveys',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('questions',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('content', sa.String(), nullable=False),
    sa.Column('kind', sa.Enum('text', 'numeric', 'boolean', name='question_kind'), nullable=True),
    sa.Column('survey_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['survey_id'], ['surveys.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('answers',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('content', sa.String(), nullable=False),
    sa.Column('session_id', sa.String(), nullable=False),
    sa.Column('question_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['question_id'], ['questions.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def upgrade():
    op.create_table('solution',
        sa.Column('created', sa.DateTime(), nullable=False),
        sa.Column('updated', sa.DateTime(), nullable=False),
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('problem_id', sa.Integer(), nullable=False),
        sa.Column('programming_language_name', sa.String(length=20), nullable=False),
        sa.Column('testing_mode', sa.Enum('one', 'first_fail', 'full', name='testingmodes'), nullable=False),
        sa.Column('state', sa.Enum('new', 'reserved', 'received', 'tested', 'rejected', name='states'), nullable=False),
        sa.Column('status', sqlalchemy_utils.types.scalar_list.ScalarListType(), nullable=False),
        sa.Column('scored_points', sa.Numeric(precision=3), nullable=False),
        sa.Column('source_code_seaweed_id', sa.String(length=255), nullable=False),
        sa.Column('testing_report_seaweed_id', sa.String(length=255), nullable=False),
        sa.Column('creator_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(['creator_id'], ['user.id'], ),
        sa.ForeignKeyConstraint(['problem_id'], ['problem.id'], ),
        sa.ForeignKeyConstraint(['programming_language_name'], ['programming_language.name'], ),
        sa.PrimaryKeyConstraint('id')
    )

def upgrade(migrate_engine):
    """Function adds key_pairs type field."""
    meta = MetaData(bind=migrate_engine)
    key_pairs = Table('key_pairs', meta, autoload=True)
    shadow_key_pairs = Table('shadow_key_pairs', meta, autoload=True)

    enum = Enum('ssh', 'x509', metadata=meta, name='keypair_types')
    enum.create()

    keypair_type = Column('type', enum, nullable=False,
                          server_default=keypair.KEYPAIR_TYPE_SSH)

    if hasattr(key_pairs.c, 'type'):
        key_pairs.c.type.drop()

    if hasattr(shadow_key_pairs.c, 'type'):
        shadow_key_pairs.c.type.drop()

    key_pairs.create_column(keypair_type)
    shadow_key_pairs.create_column(keypair_type.copy())

def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    migrations = Table('migrations', meta, autoload=True)
    shadow_migrations = Table('shadow_migrations', meta, autoload=True)

    enum = Enum('migration', 'resize', 'live-migration', 'evacuation',
                metadata=meta, name='migration_type')
    enum.create()

    migration_type = Column('migration_type', enum, nullable=True)

    if not hasattr(migrations.c, 'migration_type'):
        migrations.create_column(migration_type)
    if not hasattr(shadow_migrations.c, 'migration_type'):
        shadow_migrations.create_column(migration_type.copy())

    hidden = Column('hidden', Boolean, default=False)
    if not hasattr(migrations.c, 'hidden'):
        migrations.create_column(hidden)
    if not hasattr(shadow_migrations.c, 'hidden'):
        shadow_migrations.create_column(hidden.copy())

def upgrade():
    op.add_column(
        'vulnerabilities',
        sa.Column('test_type', sa.Enum('request'), nullable=True)
    )

def upgrade():
    op.create_table('mastodon_app',
    sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
    sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
    sa.Column('instance', sa.String(), nullable=False),
    sa.Column('client_id', sa.String(), nullable=False),
    sa.Column('client_secret', sa.String(), nullable=False),
    sa.Column('protocol', sa.Enum('http', 'https', name='enum_protocol'), nullable=False),
    sa.PrimaryKeyConstraint('instance', name=op.f('pk_mastodon_app'))
    )

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('user_log',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('ipaddr', postgresql.INET(), nullable=False),
    sa.Column('timestamp', sa.DateTime(), nullable=False),
    sa.Column('uid', sa.String(length=32), nullable=False),
    sa.Column('method', sa.Enum('GET', 'HEAD', 'POST', 'PUT', 'DELETE', 'CONNECT', 'OPTIONS', 'TRACE', 'PATCH', name='http_enum'), nullable=True),
    sa.Column('blueprint', sa.String(length=32), nullable=False),
    sa.Column('path', sa.String(length=128), nullable=False),
    sa.Column('description', sa.String(length=128), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    semester = postgresql.ENUM('Fall', 'Spring', 'Neither', name='co_op_enum')
    semester.create(op.get_bind())
    op.add_column('current_coops', sa.Column('semester', sa.Enum('Fall', 'Spring', 'Neither', name='co_op_enum'), server_default='Neither', nullable=False))
    op.drop_column('current_coops', 'active')
    ### end Alembic commands ###

def upgrade():
    classification_type_enum = postgresql.ENUM('binary', 'multi-label', 'multi-class', name='classification_type_enum')
    classification_type_enum.create(op.get_bind())
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('problem', sa.Column('classification_type', sa.Enum('binary', 'multi-label', 'multi-class', name='classification_type_enum'), server_default='binary', nullable=True))
    op.execute('''
        UPDATE "problem" SET classification_type = 'multi-class' WHERE (SELECT COUNT(*) FROM "problem_label" WHERE problem_id = problem.id) >= 2
    ''')
    # ### end Alembic commands ###

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('rule',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('type', sa.String(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('action', sa.Enum('added', 'removed', 'both', name='rule_actions'), nullable=False),
    sa.Column('alerters', postgresql.ARRAY(sa.String()), nullable=False),
    sa.Column('config', postgresql.JSONB(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def upgrade():
    ip_addresses = table('quark_ip_addresses',
                         column('address_type', sa.Enum),
                         column('_deallocated', sa.Boolean))
    connection = op.get_bind()
    t = ip_addresses.update().values({'address_type': 'fixed'}).where(
        ip_addresses.c._deallocated == 0)
    connection.execute(t)

def downgrade():
    ip_addresses = table('quark_ip_addresses',
                         column('address_type', sa.Enum))
    connection = op.get_bind()
    t = ip_addresses.update().values({'address_type': None})
    connection.execute(t)

def upgrade():
    op.add_column('quark_ip_addresses',
                  sa.Column('address_type',
                            sa.Enum('shared', 'floating', 'fixed',
                                    name='quark_ip_address_types'),
                            nullable=True))

def upgrade():
    op.add_column("task", sa.Column("status", sa.Enum("pending", "processing", "finished", "deleted", name="task_status_type"), server_default="pending", nullable=False))

    op.execute("update task set status = 'pending' where finished = false and node_id is null")
    op.execute("update task set status = 'processing' where finished = false and node_id is not null")
    op.execute("update task set status = 'finished' where finished = true")

    op.drop_column("task", "finished")

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('directories',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('parent', sa.Integer(), nullable=True),
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.Column('date_uploaded', sa.DateTime(), nullable=False),
    sa.Column('author', sa.Text(), nullable=False),
    sa.Column('thumbnail_uuid', sa.Text(), nullable=False),
    sa.Column('blocked_groups', sa.Text(), nullable=False),
    sa.ForeignKeyConstraint(['parent'], ['directories.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('files',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('parent', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('caption', sa.Text(), nullable=False),
    sa.Column('date_uploaded', sa.DateTime(), nullable=False),
    sa.Column('author', sa.Text(), nullable=False),
    sa.Column('thumbnail_uuid', sa.Text(), nullable=False),
    sa.Column('filetype', sa.Enum('Photo', 'Video', 'Text', name='filetype_enum'), nullable=False),
    sa.Column('exif_data', sa.Text(), nullable=False),
    sa.ForeignKeyConstraint(['parent'], ['directories.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def upgrade():
    enum = sa.Enum("active", "delete", name="metric_status_enum")
    enum.create(op.get_bind(), checkfirst=False)
    op.add_column("metric",
                  sa.Column('status', enum,
                            nullable=False,
                            server_default="active"))
    op.create_index('ix_metric_status', 'metric', ['status'], unique=False)

    op.drop_constraint("fk_metric_resource_id_resource_id",
                       "metric", type_="foreignkey")
    op.create_foreign_key("fk_metric_resource_id_resource_id",
                          "metric", "resource",
                          ("resource_id",), ("id",),
                          ondelete="SET NULL")

def upgrade():
    states = ("active", "creating", "creation_error", "deleting",
              "deletion_error")
    enum = sa.Enum(*states, name="resource_type_state_enum")
    enum.create(op.get_bind(), checkfirst=False)
    op.add_column("resource_type",
                  sa.Column('state', enum, nullable=False,
                            server_default="creating"))
    rt = sa.sql.table('resource_type', sa.sql.column('state', enum))
    op.execute(rt.update().values(state="active"))

def downgrade():
    """Downgrade the database to an older revision."""
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()
    conn.execute("DELETE FROM ecosystems WHERE name = 'nuget'")

    # There's no 'ALTER TYPE enum REMOVE VALUE'
    op.alter_column('package_gh_usage', 'ecosystem_backend',
                    existing_type=postgresql.ENUM('none', 'npm', 'maven', 'pypi', 'rubygems',
                                                  'scm', 'crates', 'nuget',
                                                  name='ecosystem_backend_enum'),
                    type_=postgresql.ENUM('none', 'npm', 'maven', 'pypi', 'rubygems', 'scm',
                                          'crates', name='ecosystem_backend_enum'),
                    existing_nullable=True)
    op.alter_column('ecosystems', '_backend',
                    existing_type=sa.Enum('none', 'npm', 'maven', 'pypi', 'rubygems', 'scm',
                                          'crates', 'nuget', name='ecosystem_backend_enum'),
                    type_=sa.Enum('none', 'npm', 'maven', 'pypi', 'rubygems', 'scm', 'crates',
                                  name='ecosystem_backend_enum'),
                    existing_nullable=True)
    op.alter_column('component_gh_usage', 'ecosystem_backend',
                    existing_type=postgresql.ENUM('none', 'npm', 'maven', 'pypi', 'rubygems',
                                                  'scm', 'crates', 'nuget',
                                                  name='ecosystem_backend_enum'),
                    type_=postgresql.ENUM('none', 'npm', 'maven', 'pypi', 'rubygems', 'scm',
                                          'crates', name='ecosystem_backend_enum'),
                    existing_nullable=True)
    # ### end Alembic commands ###

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    consent_type_type = postgresql.ENUM('FORM', 'INFORMATION_SHEET', name='consent_type_type')
    consent_type_type.create(op.get_bind())

    op.add_column('consents', sa.Column('consent_type', sa.Enum('FORM', 'INFORMATION_SHEET', name="consent_type_type"), nullable=True))
    consent_type = sa.table('consents', sa.column('consent_type'))
    op.execute(consent_type.update().values(consent_type='FORM'))
    op.alter_column('consents', 'consent_type', nullable=False)


    op.add_column('consents', sa.Column('weight', sa.Integer(), nullable=True))

    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    op.drop_column('consents', 'weight')
    op.drop_column('consents', 'consent_type')
    enum_type = sa.Enum(name='consent_type_type')
    enum_type.drop(op.get_bind(), checkfirst=True)

    # ### end Alembic commands ###

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('nurture_samples_options',
    sa.Column('id', sa.Enum('ADULT_NS', 'ADULT_CKD', 'CHILDREN15_2ND', 'CHILDREN15_B', 'CHILDREN_LESS_15_2ND', 'CHILDREN_LESS_15_B', 'CHILDREN30_2ND', 'CHILDREN30_B', name='protocol_option_type'), nullable=False),
    sa.Column('label', sa.String(), nullable=False),
    sa.Column('epa', sa.Integer(), nullable=True),
    sa.Column('epb', sa.Integer(), nullable=True),
    sa.Column('lpa', sa.Integer(), nullable=True),
    sa.Column('lpb', sa.Integer(), nullable=True),
    sa.Column('uc', sa.Integer(), nullable=True),
    sa.Column('ub', sa.Integer(), nullable=True),
    sa.Column('ud', sa.Integer(), nullable=True),
    sa.Column('fub', sa.Integer(), nullable=True),
    sa.Column('sc', sa.Integer(), nullable=True),
    sa.Column('sa', sa.Integer(), nullable=True),
    sa.Column('sb', sa.Integer(), nullable=True),
    sa.Column('rna', sa.Integer(), nullable=True),
    sa.Column('wb', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    #sa.UniqueConstraint('protocol')
    )
    op.add_column(u'nurture_samples', sa.Column('protocol_id', sa.Enum('ADULT_NS', 'ADULT_CKD', 'CHILDREN15_2ND', 'CHILDREN15_B', 'CHILDREN_LESS_15_2ND', 'CHILDREN_LESS_15_B', 'CHILDREN30_2ND', 'CHILDREN30_B', name='protocol_option_type'), nullable=False))
    op.create_foreign_key('nurture_samples_protocol_id_fkey', 'nurture_samples', 'nurture_samples_options', ['protocol_id'], ['id'])
    op.drop_column(u'nurture_samples', 'protocol')
    bind = op.get_bind()
    session = Session(bind=bind)

    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('ADULT_NS'), label='Adult NS', epa=18, epb=3, lpa=19, lpb=2, uc=1, ub=24, ud=1, fub=10, sc=1, sa=46, sb=4, rna=1, wb=3))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('ADULT_CKD'), label='Adult CKD', epa=18, epb=3, lpa=19, lpb=2, uc=1, ub=24, ud=1, fub=10, sc=1, sa=46, sb=4, rna=1, wb=2))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('CHILDREN30_B'), label='Children >30kg NS Baseline, Disease F.Up', epa=11, epb=1, lpa=9, lpb=1, uc=1, ub=24, ud=1, fub=10, sc=1, sa=30))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('CHILDREN30_2ND'), label='Children >30kg NS 2nd visit', epa=10, epb=1, lpa=10, lpb=1, sa=12, rna=1, wb=3))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('CHILDREN15_B'), label='Children 15-30kg NS Baseline, Disease F.Up', epa=7, epb=1, uc=1, ub=24, ud=1, fub=10, sc=1, sa=13))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('CHILDREN15_2ND'), label='Children 15-30kg NS 2nd visit', sa=12, wb=2))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('CHILDREN_LESS_15_B'), label='Children <15kg NS Baseline, Disease F.Up', uc=1, ub=12, ud=1, fub=4, sc=1, sa=8))
    session.add(SampleOption(id=PROTOCOL_OPTION_TYPE('CHILDREN_LESS_15_2ND'), label='Children <15kg NS 2nd visit', wb=2))

    session.commit()
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(u'nurture_samples', sa.Column('protocol', sa.VARCHAR(), autoincrement=False, default='Adult'))
    op.drop_constraint('nurture_samples_protocol_id_fkey', 'nurture_samples', type_='foreignkey')
    op.drop_column(u'nurture_samples', 'protocol_id')
    op.drop_table('nurture_samples_options')
    enum_type = sa.Enum(name='protocol_option_type')
    enum_type.drop(op.get_bind(), checkfirst=True)
    # ### end Alembic commands ###

def upgrade():
    with op.batch_alter_table('upgrade_tasks') as batch_op:
        batch_op.add_column(
            sa.Column('status', sa.Enum(*constants.UPGRADE_STATUSES)))

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

    request_specs = Table('request_specs', meta, autoload=True)
    build_requests = Table('build_requests', meta,
        Column('created_at', DateTime),
        Column('updated_at', DateTime),
        Column('id', Integer, primary_key=True, nullable=False),
        Column('request_spec_id', Integer, nullable=False),
        Column('project_id', String(length=255), nullable=False),
        Column('user_id', String(length=255), nullable=False),
        Column('display_name', String(length=255)),
        Column('instance_metadata', Text),
        Column('progress', Integer),
        Column('vm_state', String(length=255)),
        Column('task_state', String(length=255)),
        Column('image_ref', String(length=255)),
        Column('access_ip_v4', InetSmall()),
        Column('access_ip_v6', InetSmall()),
        Column('info_cache', Text),
        Column('security_groups', Text, nullable=False),
        Column('config_drive', Boolean, default=False, nullable=False),
        Column('key_name', String(length=255)),
        Column('locked_by', Enum('owner', 'admin',
            name='build_requests0locked_by')),
        UniqueConstraint('request_spec_id',
            name='uniq_build_requests0request_spec_id'),
        Index('build_requests_project_id_idx', 'project_id'),
        ForeignKeyConstraint(columns=['request_spec_id'],
            refcolumns=[request_specs.c.id]),
        mysql_engine='InnoDB',
        mysql_charset='utf8'
    )

    build_requests.create(checkfirst=True)

def _create_shadow_tables(migrate_engine):
    meta = MetaData(migrate_engine)
    meta.reflect(migrate_engine)
    table_names = list(meta.tables.keys())

    meta.bind = migrate_engine

    for table_name in table_names:
        table = Table(table_name, meta, autoload=True)

        columns = []
        for column in table.columns:
            column_copy = None
            # NOTE(boris-42): BigInteger is not supported by sqlite, so
            #                 after copy it will have NullType, other
            #                 types that are used in Nova are supported by
            #                 sqlite.
            if isinstance(column.type, NullType):
                column_copy = Column(column.name, BigInteger(), default=0)
            if table_name == 'instances' and column.name == 'locked_by':
                enum = Enum('owner', 'admin',
                            name='shadow_instances0locked_by')
                column_copy = Column(column.name, enum)
            else:
                column_copy = column.copy()
            columns.append(column_copy)

        shadow_table_name = 'shadow_' + table_name
        shadow_table = Table(shadow_table_name, meta, *columns,
                             mysql_engine='InnoDB')
        try:
            shadow_table.create()
        except Exception:
            LOG.info(repr(shadow_table))
            LOG.exception(_LE('Exception while creating table.'))
            raise

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('games',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('winner', sa.Enum('white', 'black', name='color'), nullable=True),
    sa.Column('handicap', sa.SmallInteger(), nullable=True),
    sa.Column('komi', sa.SmallInteger(), nullable=True),
    sa.Column('season', sa.Integer(), nullable=True),
    sa.Column('episode', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('players',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('first_name', sa.String(length=30), nullable=True),
    sa.Column('last_name', sa.String(length=30), nullable=True),
    sa.Column('aga_id', sa.Integer(), nullable=True),
    sa.Column('aga_rank', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_players_aga_id'), 'players', ['aga_id'], unique=True)
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(length=80), nullable=False),
    sa.Column('email', sa.String(length=80), nullable=False),
    sa.Column('password', sa.Binary(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.Column('first_name', sa.String(length=30), nullable=True),
    sa.Column('last_name', sa.String(length=30), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('is_admin', sa.Boolean(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    sa.UniqueConstraint('username')
    )
    op.create_table('black_player_games',
    sa.Column('player_id', sa.Integer(), nullable=False),
    sa.Column('game_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['game_id'], ['games.id'], ),
    sa.ForeignKeyConstraint(['player_id'], ['players.id'], ),
    sa.PrimaryKeyConstraint('player_id', 'game_id')
    )
    op.create_table('roles',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=80), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )
    op.create_table('white_player_games',
    sa.Column('player_id', sa.Integer(), nullable=False),
    sa.Column('game_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['game_id'], ['games.id'], ),
    sa.ForeignKeyConstraint(['player_id'], ['players.id'], ),
    sa.PrimaryKeyConstraint('player_id', 'game_id')
    )
    # ### end Alembic commands ###

def upgrade():
    op.create_table('h3c_servicecontexts',
                    sa.Column('id', sa.String(36), primary_key=True),
                    sa.Column('tenant_id', sa.String(255)),
                    sa.Column('name', sa.String(255)),
                    sa.Column('description', sa.String(255)),
                    sa.Column('type', sa.Enum('router', 'network',
                                              'subnet', 'port'),
                              nullable=False),
                    sa.Column('in_chain', sa.Boolean)
                    )
    op.create_table('h3c_serviceinsertions',
                    sa.Column('id', sa.String(36), primary_key=True),
                    sa.Column('tenant_id', sa.String(255),
                              nullable=True),
                    sa.Column('name', sa.String(255)),
                    sa.Column('source_context_type', sa.String(255),
                              nullable=True),
                    sa.Column('source_context_id', sa.String(255),
                              nullable=True),
                    sa.Column('destination_context_type', sa.String(255),
                              nullable=True),
                    sa.Column('destination_context_id', sa.String(255),
                              nullable=True)
                    )
    op.create_table('h3c_servicenodes',
                    sa.Column('id', sa.String(36), primary_key=True),
                    sa.Column('tenant_id', sa.String(255)),
                    sa.Column('service_type', sa.String(255)),
                    sa.Column('service_instance_id', sa.String(255)),
                    sa.Column('insertion_id', sa.String(255),
                              sa.ForeignKey('h3c_serviceinsertions.id',
                                            ondelete='CASCADE')),
                    )
    op.create_table('h3c_loadbalancers',
                    sa.Column('id', sa.String(36), primary_key=True),
                    sa.Column('tenant_id', sa.String(255)),
                    sa.Column('pool_id', sa.String(255)),
                    sa.Column('name', sa.String(255)),
                    sa.Column('description', sa.String(255)),
                    sa.Column('mode', sa.Enum('GATEWAY', 'SERVICE_CHAIN',
                                              'VIP_ROUTE', 'CGSR'),
                              nullable=False)
                    )
    op.create_table('h3c_l3_vxlan_allocations',
                    sa.Column('vxlan_vni', sa.Integer, nullable=False,
                              primary_key=True, autoincrement=False),
                    sa.Column('router_id', sa.String(255)),
                    sa.Column('allocated', sa.Boolean, nullable=False,
                              default=False, server_default=sa.sql.false(),
                              index=True),
                    )

def upgrade():
    """Upgrade the database to a newer revision."""
    # ### commands auto generated by Alembic - please adjust! ###
    ecosystems = op.create_table('ecosystems',
                                 sa.Column('id', sa.Integer(), nullable=False),
                                 sa.Column('name', sa.String(length=255), nullable=True),
                                 sa.Column('_backend', sa.Enum('none', 'npm', 'maven', 'pypi',
                                                               'rubygems', 'scm', 'crates',
                                                               name='ecosystem_backend_enum'),
                                           nullable=True),
                                 sa.Column('url', sa.String(length=255), nullable=True),
                                 sa.Column('fetch_url', sa.String(length=255), nullable=True),
                                 sa.PrimaryKeyConstraint('id'))
    op.bulk_insert(ecosystems,
                   [
                       {'id': 1, 'name': 'rubygems', '_backend': 'rubygems',
                        'url': 'https://rubygems.org/',
                        'fetch_url': 'https://rubygems.org/api/v1'},
                       {'id': 2, 'name': 'npm', '_backend': 'npm',
                        'url': 'https://www.npmjs.com/',
                        'fetch_url': 'https://registry.npmjs.org/'},
                       {'id': 3, 'name': 'maven', '_backend': 'maven',
                        'url': 'https://repo1.maven.org/maven2/', 'fetch_url': None},
                       {'id': 4, 'name': 'pypi', '_backend': 'pypi',
                        'url': 'https://pypi.python.org/',
                        'fetch_url': 'https://pypi.python.org/pypi'},
                       {'id': 5, 'name': 'go', '_backend': 'scm', 'url': None, 'fetch_url': None},
                       {'id': 6, 'name': 'crates', '_backend': 'crates',
                        'url': 'https://crates.io/',
                        'fetch_url': None}, ])
    op.create_table('packages',
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('ecosystem_id', sa.Integer(), nullable=True),
                    sa.Column('name', sa.String(length=255), nullable=True),
                    sa.ForeignKeyConstraint(['ecosystem_id'], ['ecosystems.id'], ),
                    sa.PrimaryKeyConstraint('id'),
                    sa.UniqueConstraint('ecosystem_id', 'name', name='ep_unique'))
    op.create_table('versions',
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('package_id', sa.Integer(), nullable=True),
                    sa.Column('identifier', sa.String(length=255), nullable=True),
                    sa.ForeignKeyConstraint(['package_id'], ['packages.id'], ),
                    sa.PrimaryKeyConstraint('id'),
                    sa.UniqueConstraint('package_id', 'identifier', name='pv_unique'))
    op.add_column('analyses', sa.Column('version_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'analyses', 'versions', ['version_id'], ['id'])
    op.drop_column('analyses', 'package')
    op.drop_column('analyses', 'ecosystem')
    op.drop_column('analyses', 'version')
    op.add_column('analysis_requests', sa.Column('version_id', sa.Integer(), nullable=True))
    op.drop_index('epv_index', table_name='analysis_requests')
    op.create_index('epv_index', 'analysis_requests', ['version_id'], unique=True,
                    postgresql_where=sa.text('fulfilled_at IS NULL'))
    op.create_foreign_key(None, 'analysis_requests', 'versions', ['version_id'], ['id'])
    op.drop_column('analysis_requests', 'package')
    op.drop_column('analysis_requests', 'ecosystem')
    op.drop_column('analysis_requests', 'version')
    # ### end Alembic commands ###

def upgrade():
    op.create_table(
        'clusters',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('version', sa.Enum(*VERSIONS)),
        sa.Column('status', sa.Enum(*STATUSES)),
        sa.Column('name', sa.String(255)),
        sa.PrimaryKeyConstraint('id')
    )

    op.create_table(
        'hosts',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('hostname', sa.String(255)),
        sa.Column('cluster_id', sa.String(36), nullable=False),
        sa.ForeignKeyConstraint(['cluster_id'], ['clusters.id']),
        sa.PrimaryKeyConstraint('id')
    )

    op.create_table(
        'services',
        sa.Column('name', sa.String(255)),
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('host_id', sa.String(36), nullable=False),
        sa.Column('version', sa.Enum(*VERSIONS)),
        sa.ForeignKeyConstraint(['host_id'], ['hosts.id']),
        sa.PrimaryKeyConstraint('id')

    )

    op.create_table(
        'upgrade_tasks',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('upgrade_start_time', sa.DateTime),
        sa.Column('upgrade_end_time', sa.DateTime),
        sa.Column('cluster_id', sa.String(36), nullable=False),
        sa.Column('from_version', sa.Enum(*VERSIONS)),
        sa.Column('to_version', sa.Enum(*VERSIONS)),
        sa.ForeignKeyConstraint(['cluster_id'], ['clusters.id']),
        sa.PrimaryKeyConstraint('id')
    )

    op.create_table(
        'service_upgrade_records',
        sa.Column('id', sa.String(36), nullable=False),
        sa.Column('service_id', sa.String(36), nullable=False),
        sa.Column('upgrade_task_id', sa.String(36), nullable=False),
        sa.Column('status', sa.Enum(*STATUSES)),
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(['service_id'], ['services.id']),
        sa.ForeignKeyConstraint(['upgrade_task_id'], ['upgrade_tasks.id']),
    )