def column_upgrade(table_name, column_name, enum_name):
    if op.get_context().bind.dialect.name != 'postgresql':
        if op.get_context().bind.dialect.name == 'mssql':
            op.drop_constraint('ck_ps_endpoints_identify_by_pjsip_identify_by_values',
                               table_name)
        op.alter_column(table_name, column_name, type_=sa.String(80))
        return

    # Postgres requires a few more steps
    op.execute('ALTER TABLE ' + table_name + ' ALTER COLUMN ' + column_name +
               ' TYPE varchar(80) USING identify_by::text::' + enum_name)

    op.execute('DROP TYPE ' + enum_name)
def downgrade():
    # Convert to UUID for PostreSQL or to CHAR(32) for others
    if op.get_context().dialect.name == 'sqlite':
        pass # No difference between varchar and char in SQLite
    elif op.get_context().dialect.name == 'postgresql':
        drop_user_id_fkeys()
        for table, col in ( ("user", "id"),
                            ("sender", "user_id"),
                            ("vote", "user_id") ):
            op.alter_column(table, col, type_=sa.Unicode(255),
                            existing_type=types.UUID,
                            existing_nullable=False)
        # Need cascade for data conversion below, it will be removed by the
        # last operation (or the loop on FKEYS_CASCADE if offline).
        create_user_id_fkeys("CASCADE")
    else:
        # Untested on other engines
        for table, col in ( ("user", "id"),
                            ("sender", "user_id"),
                            ("vote", "user_id") ):
            op.alter_column(table, col, type_=sa.Unicode(255),
                            existing_type=types.UUID,
                            existing_nullable=False)
    if not context.is_offline_mode():
        connection = op.get_bind()
        # Create a new MetaData instance here because the data is UUIDs and we
        # want to convert to simple strings
        metadata = sa.MetaData()
        metadata.bind = connection
        User = Base.metadata.tables["user"].tometadata(metadata)
        User = sa.Table("user", metadata,
            sa.Column("id", sa.Unicode(255), primary_key=True),
            extend_existing=True)
        transaction = connection.begin()
        for user in User.select().execute():
            try:
                new_user_id = UUID(user.id).int
            except ValueError:
                continue # Already converted
            User.update().where(
                User.c.id == user.id
            ).values(id=new_user_id).execute()
        transaction.commit()
        if connection.dialect.name != "sqlite":
            drop_user_id_fkeys()
            create_user_id_fkeys(None)
    # Now remove onupdate=CASCADE from some foreign keys
    rebuild_fkeys(None)
def upgrade():
    # Convert existing data into UUID strings
    if not context.is_offline_mode():
        connection = op.get_bind()
        # Create a new MetaData instance here because the data is not proper
        # UUIDs yet so it'll error out.
        metadata = sa.MetaData()
        metadata.bind = connection
        User = Base.metadata.tables["user"].tometadata(metadata)
        User = sa.Table("user", metadata,
            sa.Column("id", sa.Unicode(255), primary_key=True),
            extend_existing=True)
        if connection.dialect.name != "sqlite":
            drop_user_id_fkeys()
            create_user_id_fkeys("CASCADE")
        transaction = connection.begin()
        for user in User.select().execute():
            try:
                new_user_id = unicode(UUID(int=int(user.id)))
            except ValueError:
                continue # Already converted
            User.update().where(
                User.c.id == user.id
            ).values(id=new_user_id).execute()
        transaction.commit()
    # Convert to UUID for PostreSQL or to CHAR(32) for others
    if op.get_context().dialect.name == 'sqlite':
        pass # No difference between varchar and char in SQLite
    elif op.get_context().dialect.name == 'postgresql':
        drop_user_id_fkeys()
        for table, col in ( ("user", "id"),
                            ("sender", "user_id"),
                            ("vote", "user_id") ):
            op.execute('''
                ALTER TABLE "{table}"
                    ALTER COLUMN {col} TYPE UUID USING {col}::uuid
                '''.format(table=table, col=col))
        create_user_id_fkeys("CASCADE")
    else:
        # Untested on other engines
        for table, col in ( ("user", "id"),
                            ("sender", "user_id"),
                            ("vote", "user_id") ):
            op.alter_column(table, col, type_=types.UUID,
                            existing_type=sa.Unicode(255),
                            existing_nullable=False)
    # Now add onupdate=CASCADE to some foreign keys.
    rebuild_fkeys("CASCADE")
def downgrade():
    """Downgrade database."""
    ctx = op.get_context()
    insp = Inspector.from_engine(ctx.connection.engine)

    for fk in insp.get_foreign_keys('b2share_block_schema'):
        if fk['referred_table'] == 'b2share_community':
            op.drop_constraint(
                op.f(fk['name']),
                'b2share_block_schema',
                type_='foreignkey'
            )

    op.drop_table('b2share_block_schema_version')

    for fk in insp.get_foreign_keys('b2share_community_schema_version'):
        if fk['referred_table'] == 'b2share_community':
            op.drop_constraint(
                op.f(fk['name']),
                'b2share_community_schema_version',
                type_='foreignkey'
            )

    op.drop_table('b2share_community_schema_version')
    op.drop_table('b2share_block_schema')
    op.drop_table('b2share_root_schema_version')
def downgrade():
    # Was unable to find a way to use op.alter_column() to remove the
    # unique index property.
    if op.get_context().bind.dialect.name == 'mssql':
        op.drop_constraint('uq_queue_members_uniqueid', 'queue_members')
    op.drop_column('queue_members', 'uniqueid')
    op.add_column('queue_members', sa.Column(name='uniqueid', type_=sa.String(80), nullable=False))
def upgrade():
    # NOTE(sheeprine): Hack to let the migrations pass for postgresql
    dialect = op.get_context().dialect.name
    if dialect == 'postgresql':
        constraints = ['uniq_field_threshold', 'uniq_service_threshold']
    else:
        constraints = ['uniq_field_mapping', 'uniq_service_mapping']
    op.create_table('hashmap_thresholds',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('threshold_id', sa.String(length=36), nullable=False),
    sa.Column('level', sa.Numeric(precision=20, scale=8), nullable=True),
    sa.Column('cost', sa.Numeric(precision=20, scale=8), nullable=False),
    sa.Column('map_type', sa.Enum('flat', 'rate', name='enum_map_type'),
              nullable=False),
    sa.Column('service_id', sa.Integer(), nullable=True),
    sa.Column('field_id', sa.Integer(), nullable=True),
    sa.Column('group_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['field_id'], ['hashmap_fields.id'],
                            ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['group_id'], ['hashmap_groups.id'],
                            ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['service_id'], ['hashmap_services.id'],
                            ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('threshold_id'),
    sa.UniqueConstraint('level', 'field_id', name=constraints[0]),
    sa.UniqueConstraint('level', 'service_id', name=constraints[1]),
    mysql_charset='utf8',
    mysql_engine='InnoDB')
def upgrade(active_plugins=None, options=None):
    if not migration.should_run(active_plugins, migration_for_plugins):
        return

    # Workaround for Alemic bug #89
    # https://bitbucket.org/zzzeek/alembic/issue/89
    context = op.get_context()
    if context.bind.dialect.name == "postgresql":
        op.execute(
            "CREATE TYPE ipv6_ra_modes AS ENUM ('%s', '%s', '%s')" % ("slaac", "dhcpv6-stateful", "dhcpv6-stateless")
        )
        op.execute(
            "CREATE TYPE ipv6_address_modes AS ENUM ('%s', '%s', '%s')"
            % ("slaac", "dhcpv6-stateful", "dhcpv6-stateless")
        )
    op.add_column(
        "subnets",
        sa.Column(
            "ipv6_ra_mode", sa.Enum("slaac", "dhcpv6-stateful", "dhcpv6-stateless", name="ipv6_ra_modes"), nullable=True
        ),
    )
    op.add_column(
        "subnets",
        sa.Column(
            "ipv6_address_mode",
            sa.Enum("slaac", "dhcpv6-stateful", "dhcpv6-stateless", name="ipv6_address_modes"),
            nullable=True,
        ),
    )
def downgrade():
    current_context = op.get_context()
    meta = current_context.opts['target_metadata']
    user = sa.Table('users', meta, autoload=True)

    # remove all records to undo the preseed.
    op.execute(user.delete())
def upgrade():
    ctx = op.get_context()
    con = op.get_bind()
    table_exists = ctx.dialect.has_table(con.engine, 'project_quotas')
    if not table_exists:
        op.create_table(
            'project_quotas',
            sa.Column('id', sa.String(length=36), nullable=False),
            sa.Column('created_at', sa.DateTime(), nullable=False),
            sa.Column('updated_at', sa.DateTime(), nullable=False),
            sa.Column('deleted_at', sa.DateTime(), nullable=True),
            sa.Column('deleted', sa.Boolean(), nullable=False),
            sa.Column('status', sa.String(length=20), nullable=False),
            sa.Column('project_id', sa.String(length=36), nullable=False),
            sa.Column('secrets', sa.Integer(), nullable=True),
            sa.Column('orders', sa.Integer(), nullable=True),
            sa.Column('containers', sa.Integer(), nullable=True),
            sa.Column('transport_keys', sa.Integer(), nullable=True),
            sa.Column('consumers', sa.Integer(), nullable=True),
            sa.ForeignKeyConstraint(['project_id'],
                                    ['projects.id'],
                                    name='project_quotas_fk'),
            sa.PrimaryKeyConstraint('id'),
            mysql_engine='InnoDB')
        op.create_index(
            op.f('ix_project_quotas_project_id'),
            'project_quotas',
            ['project_id'],
            unique=False)
def upgrade():
    # sqlite doesn't have ALTER command
    cx = op.get_context()
    if 'sqlite' in cx.connection.engine.name:
        with op.batch_alter_table("records") as batch_op:
            batch_op.add_column(sa.Column('fingerprints', sa.Text))
            
            batch_op.drop_column('meta_data')
            batch_op.drop_column('orcid_claims')
            batch_op.drop_column('nonbib_data')
            batch_op.drop_column('fulltext')
            
            batch_op.drop_column('meta_data_updated')
            batch_op.drop_column('orcid_claims_updated')
            batch_op.drop_column('nonbib_data_updated')
            batch_op.drop_column('fulltext_updated')
            
    else:
        
        op.add_column('records', sa.Column('fingerprints', sa.Text))
        
        op.drop_column('records', 'meta_data')
        op.drop_column('records', 'orcid_claims')
        op.drop_column('records', 'nonbib_data')
        op.drop_column('records', 'fulltext')
        
        op.drop_column('records', 'meta_data_updated')
        op.drop_column('records', 'orcid_claims_updated')
        op.drop_column('records', 'nonbib_data_updated')
        op.drop_column('records', 'fulltext_updated')
def downgrade():
    cx = op.get_context()
    if 'sqlite' in cx.connection.engine.name:
        with op.batch_alter_table("records") as batch_op:
            batch_op.drop_column('fingerprints')
            
            batch_op.add_column(sa.Column('meta_data', sa.Text))
            batch_op.add_column(sa.Column('orcid_claims', sa.Text))
            batch_op.add_column(sa.Column('nonbib_data', sa.Text))
            batch_op.add_column(sa.Column('fulltext', sa.Text))
            
            batch_op.add_column(sa.Column('meta_data_updated', sa.TIMESTAMP))
            batch_op.add_column(sa.Column('orcid_claims_updated', sa.TIMESTAMP))
            batch_op.add_column(sa.Column('nonbib_data_updated', sa.TIMESTAMP))
            batch_op.add_column(sa.Column('fulltext_updated', sa.TIMESTAMP))
    else:
        op.drop_column('records', 'fingerprints')
        
        op.add_column('records', sa.Column('meta_data', sa.Text))
        op.add_column('records', sa.Column('orcid_claims', sa.Text))
        op.add_column('records', sa.Column('nonbib_data', sa.Text))
        op.add_column('records', sa.Column('fulltext', sa.Text))
        
        op.add_column('records', sa.Column('meta_data_updated', sa.TIMESTAMP))
        op.add_column('records', sa.Column('orcid_claims_updated', sa.TIMESTAMP))
        op.add_column('records', sa.Column('nonbib_data_updated', sa.TIMESTAMP))
        op.add_column('records', sa.Column('fulltext_updated', sa.TIMESTAMP))
def downgrade():

    context = op.get_context()
    dialect = context.bind.dialect.name

    op.drop_constraint(
        name=PK_NAME,
        table_name=TABLE_NAME,
        type_='primary'
    )

    op.add_column(
        TABLE_NAME,
        sa.Column('id', sa.String(32))
    )

    if dialect == 'ibm_db_sa':
        # DB2 doesn't support nullable column in primary key
        op.alter_column(
            table_name=TABLE_NAME,
            column_name='id',
            nullable=False
        )

    with migration.remove_fks_from_table(TABLE_NAME):
        op.create_primary_key(
            name=PK_NAME,
            table_name=TABLE_NAME,
            cols=['id']
        )
示例#13
0
def upgrade():
    #Note: alter type can't be run in a transaction, this needs transaction_per_migration=True in the context, too.
    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level='AUTOCOMMIT')
    op.execute(DDL("ALTER TYPE candidate_source ADD VALUE 'manual'"))
    op.execute(DDL("ALTER TYPE candidate_source ADD VALUE 'ale'"))
def upgrade():
    metadata = _get_database_metadata()

    # Get relevant tables
    secrets = metadata.tables['secrets']
    project_secret = metadata.tables['project_secret']

    # Add project_id to the secrets
    op.execute(secrets.update().
               values({'project_id': project_secret.c.project_id}).
               where(secrets.c.id == project_secret.c.secret_id).
               where(secrets.c.project_id == None)
               )

    # Need to drop foreign key constraint before mysql will allow changes
    ctx = op.get_context()
    _drop_constraint(ctx, 'secrets_project_fk', 'secrets')

    # make project_id no longer nullable
    op.alter_column('secrets', 'project_id',
                    type_=sa.String(36), nullable=False)

    # Create foreign key constraint again
    _create_constraint(ctx, 'secrets_project_fk', 'secrets', 'projects',
                       ['project_id'], ['id'])
def upgrade():
    cx = op.get_context()
    
    with op.batch_alter_table("records") as batch_op:
        batch_op.add_column(sa.Column('direct_data', sa.Text))
        batch_op.add_column(sa.Column('direct_updated', sa.TIMESTAMP))
        batch_op.add_column(sa.Column('direct_processed', sa.TIMESTAMP))
def upgrade():
    # Workaround for Alemic bug #89
    # https://bitbucket.org/zzzeek/alembic/issue/89
    context = op.get_context()
    if context.bind.dialect.name == "postgresql":
        op.execute(
            "CREATE TYPE ipv6_ra_modes AS ENUM ('%s', '%s', '%s')" % ("slaac", "dhcpv6-stateful", "dhcpv6-stateless")
        )
        op.execute(
            "CREATE TYPE ipv6_address_modes AS ENUM ('%s', '%s', '%s')"
            % ("slaac", "dhcpv6-stateful", "dhcpv6-stateless")
        )
    op.add_column(
        "subnets",
        sa.Column(
            "ipv6_ra_mode", sa.Enum("slaac", "dhcpv6-stateful", "dhcpv6-stateless", name="ipv6_ra_modes"), nullable=True
        ),
    )
    op.add_column(
        "subnets",
        sa.Column(
            "ipv6_address_mode",
            sa.Enum("slaac", "dhcpv6-stateful", "dhcpv6-stateless", name="ipv6_address_modes"),
            nullable=True,
        ),
    )
def upgrade():
    # In order to sanitize the data during migration,
    # the current records in the table need to be verified
    # and all the duplicate records which violate the PK
    # constraint need to be removed.
    context = op.get_context()
    if context.bind.dialect.name == 'postgresql':
        op.execute('DELETE FROM %(table)s WHERE id in ('
                   'SELECT %(table)s.id FROM %(table)s LEFT OUTER JOIN '
                   '(SELECT MIN(id) as id, router_id, l3_agent_id '
                   ' FROM %(table)s GROUP BY router_id, l3_agent_id) AS temp '
                   'ON %(table)s.id = temp.id WHERE temp.id is NULL);'
                   % {'table': TABLE_NAME})
    else:
        op.execute('DELETE %(table)s FROM %(table)s LEFT OUTER JOIN '
                   '(SELECT MIN(id) as id, router_id, l3_agent_id '
                   ' FROM %(table)s GROUP BY router_id, l3_agent_id) AS temp '
                   'ON %(table)s.id = temp.id WHERE temp.id is NULL;'
                   % {'table': TABLE_NAME})

    op.drop_column(TABLE_NAME, 'id')

    op.create_primary_key(
        name=PK_NAME,
        table_name=TABLE_NAME,
        cols=['router_id', 'l3_agent_id']
    )
def upgrade():
    op.create_index(op.f('ix_certificate_authority_metadata_ca_id'), 'certificate_authority_metadata', ['ca_id'], unique=False)
    op.create_index(op.f('ix_certificate_authority_metadata_key'), 'certificate_authority_metadata', ['key'], unique=False)
    op.create_index(op.f('ix_container_consumer_metadata_container_id'), 'container_consumer_metadata', ['container_id'], unique=False)
    op.create_index(op.f('ix_container_secret_container_id'), 'container_secret', ['container_id'], unique=False)
    op.create_index(op.f('ix_container_secret_secret_id'), 'container_secret', ['secret_id'], unique=False)
    op.create_index(op.f('ix_containers_project_id'), 'containers', ['project_id'], unique=False)
    op.create_index(op.f('ix_encrypted_data_kek_id'), 'encrypted_data', ['kek_id'], unique=False)
    op.create_index(op.f('ix_encrypted_data_secret_id'), 'encrypted_data', ['secret_id'], unique=False)
    op.create_index(op.f('ix_kek_data_project_id'), 'kek_data', ['project_id'], unique=False)
    op.create_index(op.f('ix_order_barbican_metadata_order_id'), 'order_barbican_metadata', ['order_id'], unique=False)
    op.create_index(op.f('ix_order_plugin_metadata_order_id'), 'order_plugin_metadata', ['order_id'], unique=False)
    op.create_index(op.f('ix_order_retry_tasks_order_id'), 'order_retry_tasks', ['order_id'], unique=False)
    op.create_index(op.f('ix_orders_container_id'), 'orders', ['container_id'], unique=False)
    op.create_index(op.f('ix_orders_project_id'), 'orders', ['project_id'], unique=False)
    op.create_index(op.f('ix_orders_secret_id'), 'orders', ['secret_id'], unique=False)

    ctx = op.get_context()
    _drop_constraint(ctx, 'preferred_certificate_authorities_ibfk_1', 'preferred_certificate_authorities')

    op.alter_column('preferred_certificate_authorities', 'ca_id',
               existing_type=sa.VARCHAR(length=36),
               nullable=False)

    op.create_foreign_key('preferred_certificate_authorities_fk', 'preferred_certificate_authorities',
                          'certificate_authorities', ['ca_id'], ['id'])

    op.create_index(op.f('ix_preferred_certificate_authorities_ca_id'), 'preferred_certificate_authorities', ['ca_id'], unique=False)
    op.create_index(op.f('ix_preferred_certificate_authorities_project_id'), 'preferred_certificate_authorities', ['project_id'], unique=True)
    op.create_index(op.f('ix_project_certificate_authorities_ca_id'), 'project_certificate_authorities', ['ca_id'], unique=False)
    op.create_index(op.f('ix_project_certificate_authorities_project_id'), 'project_certificate_authorities', ['project_id'], unique=False)
    op.create_index(op.f('ix_project_secret_project_id'), 'project_secret', ['project_id'], unique=False)
    op.create_index(op.f('ix_project_secret_secret_id'), 'project_secret', ['secret_id'], unique=False)
    op.create_index(op.f('ix_secret_store_metadata_secret_id'), 'secret_store_metadata', ['secret_id'], unique=False)
def upgrade():
    """Add 'time_stamp' column to 'node_feature' table and update all
    dependencies effected by this change.
      1) Drop the dependent view
      2) Add the new column
      3) Update all dependent stored procedures and recreate the view
       to include the new column
    """

    db_dialect = op.get_context().dialect
    if 'postgresql' in db_dialect.name:
        op.execute(textwrap.dedent("DROP VIEW IF EXISTS node_features_view;"))
    else:
        print("View 'node_features_view' was not drop in prepare for adding "
              "new 'time_stamp' column to 'node_feature' table.  "
              "'%s' is not a supported database dialect." % db_dialect.name)
        return
    op.add_column('node_feature', sa.Column('time_stamp',
                                            sa.DateTime(),
                                            nullable=False,
                                            server_default=sa.func.now()))
    if 'postgresql' in db_dialect.name:
        _postgresql_upgrade_ddl()
    else:
        print("Functions were not updated.  "
              "'%s' is not a supported database dialect." % db_dialect.name)
        return
def downgrade():
    cx = op.get_context()
    if 'sqlite' in cx.connection.engine.name:
        with op.batch_alter_table("records") as batch_op:
            batch_op.alter_column('fingerprint', new_column_name='fingerprints')
    else:
        op.alter_column('records', 'fingerprint', new_column_name='fingerprints')    
def downgrade():
    cx = op.get_context()
    if 'sqlite' in cx.connection.engine.name:
        with op.batch_alter_table("records") as batch_op:
            batch_op.drop_column('direct_created')
    else:
        op.drop_column('records', 'direct_created')
def upgrade():
    # Workaround for Alembic bug #89
    # https://bitbucket.org/zzzeek/alembic/issue/89
    context = op.get_context()
    if context.bind.dialect.name == 'postgresql':
        op.execute("CREATE TYPE ipv6_ra_modes AS ENUM ('%s', '%s', '%s')"
                   % ('slaac', 'dhcpv6-stateful', 'dhcpv6-stateless'))
        op.execute("CREATE TYPE ipv6_address_modes AS ENUM ('%s', '%s', '%s')"
                   % ('slaac', 'dhcpv6-stateful', 'dhcpv6-stateless'))
    op.add_column('subnets',
                  sa.Column('ipv6_ra_mode',
                            sa.Enum('slaac',
                                    'dhcpv6-stateful',
                                    'dhcpv6-stateless',
                                    name='ipv6_ra_modes'),
                            nullable=True)
                  )
    op.add_column('subnets',
                  sa.Column('ipv6_address_mode',
                            sa.Enum('slaac',
                                    'dhcpv6-stateful',
                                    'dhcpv6-stateless',
                                    name='ipv6_address_modes'),
                            nullable=True)
                  )
示例#23
0
def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "durations",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("duration", sa.Integer(), nullable=False),
        sa.Column("inactive", sa.Boolean(), nullable=True),
        sa.PrimaryKeyConstraint("id"),
    )
    op.add_column("talks", sa.Column("duration_id", sa.Integer(), nullable=True))
    op.create_foreign_key(None, "talks", "durations", ["duration_id"], ["id"])
    ### end Alembic commands ###

    op.bulk_insert(
        durations_table,
        [
            {"name": "30 minutes", "duration": 30, "inactive": False},
            {"name": "45 minutes", "duration": 45, "inactive": False},
            {"name": "60 minutes", "duration": 60, "inactive": False},
            {"name": "1/2 day", "duration": 180, "inactive": False},
            {"name": "full day", "duration": 360, "inactive": False},
        ],
    )

    op.execute("UPDATE talks AS t SET duration_id = d.id FROM durations AS d WHERE t.duration::text = d.name")

    op.alter_column("talks", "duration_id", existing_type=sa.INTEGER(), nullable=False)
    op.drop_column("talks", "duration")
    context = op.get_context()
    if context.bind.dialect.name == "postgresql":
        sql = "DROP TYPE duration"
        op.execute(sql)
def upgrade():

    if op.get_context().dialect.name == 'postgresql':
        # INFO - G.M - 2018-11-27 - TO modify type in postgresq, we should
        # create a new one set column type to this new one and remove old one
        op.execute("ALTER TYPE authtype RENAME TO authtype_old;")
        op.execute("ALTER TABLE users alter auth_type drop default;")
        enum = sa.Enum(*new_auth_type_list, name='authtype')
        enum.create(op.get_bind(), checkfirst=False)
        with op.batch_alter_table('users') as batch_op:
            batch_op.alter_column(
                'auth_type',
                type_=enum,
                postgresql_using="auth_type::text::authtype",
                server_default='INTERNAL'
            )
        op.execute("DROP TYPE authtype_old;")
    else:
        # INFO - G.M - 2018-11-27 - MYSQL case
        enum = sa.Enum(*new_auth_type_list, name='authtype')
        enum.create(op.get_bind(), checkfirst=False)
        with op.batch_alter_table('users') as batch_op:
            batch_op.alter_column(
                'auth_type',
                type_=enum,
            )
def upgrade():
    # sqlite doesn't have ALTER command
    cx = op.get_context()
    if 'sqlite' in cx.connection.engine.name:
        with op.batch_alter_table("records") as batch_op:
            batch_op.add_column(sa.Column('direct_created', sa.TIMESTAMP))
    else:
        op.add_column('records', sa.Column('direct_created', sa.TIMESTAMP))
def drop_data_samples_view():
    db_dialect = op.get_context().dialect
    if 'postgresql' in db_dialect.name:
        postgres_drop_data_samples_view()
        return True
    else:
        print("Views were not dropped. '%s' is not a supported database dialect." % db_dialect.name)
        return False
def downgrade():
    db_dialect = op.get_context().dialect
    if 'postgresql' in db_dialect.name:
        _postgresql_downgrade_ddl()
    else:
        print("Function generate_partition_triggers() was not downgraded.  "
              "'%s' is not a supported database dialect." % db_dialect.name)
        return
def _drop_view(view):
    dialect = op.get_context().dialect.name
    if dialect in sql_drop_view_clauses and view in sql_drop_view_clauses[dialect]:
        op.execute(sql_drop_view_clauses[dialect][view])
        return True
    else:
        print("Unable to drop view '{str_view}'. '{str_dialect}' is not a supported database dialect." . format(str_view=view, str_dialect=dialect))
        return False
def recreate_views():
    db_dialect = op.get_context().dialect
    if 'postgresql' in db_dialect.name:
        postgres_recreate_views()
        return True
    else:
        print("Views were not re-created. '%s' is not a supported database dialect." % db_dialect.name)
        return False
def upgrade():
    dialect = op.get_context().dialect
    if dialect.name == 'sqlite':
        upgrade_sqlite()
    elif dialect.name == 'mysql':
        upgrade_mysql()
    elif dialect.name == 'postgresql':
        upgrade_postgresql()
示例#31
0
def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    connection = None
    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level='AUTOCOMMIT')

    op.execute("ALTER TYPE providers ADD value 'google' after 'facebook'")

    if connection is not None:
        connection.execution_options(isolation_level='READ_COMMITTED')
示例#32
0
def upgrade():
    conn = op.get_bind()
    meta = sa.MetaData(bind=conn)
    tv_episodes = sa.Table('tv_episodes', meta, autoload=True)

    with op.get_context().begin_transaction():
        for row in conn.execute(tv_episodes.select()):
            conn.execute(
                f'UPDATE tv_episodes SET subtitles_lastsearch = "" WHERE tv_episodes.indexer_id = {row.indexer_id}'
            )

    with op.batch_alter_table("tv_episodes") as batch_op:
        batch_op.alter_column('subtitles_lastsearch',
                              type_=sa.DateTime(timezone=True))

    with op.get_context().begin_transaction():
        for row in conn.execute(tv_episodes.select()):
            conn.execute(
                f'UPDATE tv_episodes SET subtitles_lastsearch = {sa.func.current_timestamp()} WHERE tv_episodes.indexer_id = {row.indexer_id}'
            )
def enum_update(table_name, column_name, enum_name, enum_values):
    if op.get_context().bind.dialect.name != 'postgresql':
        if op.get_context().bind.dialect.name == 'mssql':
            op.drop_constraint('ck_musiconhold_mode_moh_mode_values', 'musiconhold')
        op.alter_column(table_name, column_name,
                        type_=sa.Enum(*enum_values, name=enum_name))
        return

    # Postgres requires a few more steps
    tmp = enum_name + '_tmp'

    op.execute('ALTER TYPE ' + enum_name + ' RENAME TO ' + tmp)

    updated = sa.Enum(*enum_values, name=enum_name)
    updated.create(op.get_bind(), checkfirst=False)

    op.execute('ALTER TABLE ' + table_name + ' ALTER COLUMN ' + column_name +
               ' TYPE ' + enum_name + ' USING mode::text::' + enum_name)

    op.execute('DROP TYPE ' + tmp)
def upgrade():
    # Add new LCO API values to the followup_apis ENUM type
    with op.get_context().autocommit_block():
        op.execute(
            "ALTER TYPE followup_apis ADD VALUE IF NOT EXISTS 'SINISTROAPI'")
        op.execute(
            "ALTER TYPE followup_apis ADD VALUE IF NOT EXISTS 'SPECTRALAPI'")
        op.execute(
            "ALTER TYPE followup_apis ADD VALUE IF NOT EXISTS 'FLOYDSAPI'")
        op.execute(
            "ALTER TYPE followup_apis ADD VALUE IF NOT EXISTS 'MUSCATAPI'")
def _drop_item(item):
    dialect = op.get_context().dialect.name
    if dialect in sql_drop_item_clauses and item in sql_drop_item_clauses[
            dialect]:
        op.execute(sql_drop_item_clauses[dialect][item])
        return True
    else:
        err_msg = ("Unable to drop item '{str_item}' "
                   "is not a supported database dialect")
        print(err_msg.format(str_item=item, str_dialect=dialect))
        return False
示例#36
0
def table_has_column(table, column):
    config = op.get_context().config
    engine = engine_from_config(config.get_section(config.config_ini_section),
                                prefix='sqlalchemy.')
    insp = reflection.Inspector.from_engine(engine)
    has_column = False
    for col in insp.get_columns(table):
        if column not in col['name']:
            continue
        has_column = True
    return has_column
示例#37
0
def downgrade():
    if op.get_context().dialect.name == 'postgresql':
        op.execute(
            "ALTER TYPE climatizationstate RENAME TO climatizationstate_old")
        op.execute(
            "CREATE TYPE climatizationstate AS ENUM('UNKNOWN', 'VENTILATION', 'COOLING', 'HEATING', 'OFF')"
        )
        op.execute((
            "ALTER TABLE transactions ALTER COLUMN climatizationstate TYPE climatizationstate USING "
            "climatizationstate::text::climatizationstate"))
        op.execute("DROP TYPE climatizationstate_old")
def upgrade():
    ctx = op.get_context()
    dialect = ctx.dialect.name

    if dialect == 'postgresql':
        op.execute("""
            SELECT SETVAL(
                'analysis_info_id_seq',
                (SELECT MAX(id) + 1 FROM analysis_info)
            )
        """)
示例#39
0
def _drop_view(view):
    dialect = op.get_context().dialect.name
    if dialect in sql_drop_view_clauses and view in sql_drop_view_clauses[
            dialect]:
        op.execute(sql_drop_view_clauses[dialect][view])
        return True
    else:
        print(
            "Unable to drop view '{str_view}'. '{str_dialect}' is not a supported database dialect."
            .format(str_view=view, str_dialect=dialect))
        return False
示例#40
0
def upgrade():
    conn = op.get_bind()
    meta = sa.MetaData(bind=conn)
    tv_shows = sa.Table('tv_shows', meta, autoload=True)

    with op.get_context().begin_transaction():
        for row in conn.execute(tv_shows.select()):
            if len(row.lang) == 2:
                lang = babelfish.Language.fromalpha2(row.lang)
                conn.execute(
                    f'UPDATE tv_shows SET lang = "{lang.alpha3}" WHERE tv_shows.series_id = {row.series_id}'
                )
def upgrade():
    context = op.get_context()

    op.add_column('ps_contacts', sa.Column('endpoint', sa.String(40)))

    if context.bind.dialect.name != 'postgresql':
        op.alter_column('ps_contacts', 'expiration_time', type_=sa.BigInteger)
    else:
        op.execute('ALTER TABLE ps_contacts ALTER COLUMN expiration_time TYPE BIGINT USING expiration_time::bigint')

    op.create_index('ps_contacts_qualifyfreq_exp', 'ps_contacts', ['qualify_frequency', 'expiration_time'])
    op.create_index('ps_aors_qualifyfreq_contact', 'ps_aors', ['qualify_frequency', 'contact'])
def upgrade():
    # Create the new enum
    ast_bool_values = ENUM(*AST_BOOL_VALUES, name=AST_BOOL_NAME, create_type=False)
    if op.get_context().bind.dialect.name == 'postgresql':
        ast_bool_values.create(op.get_bind(), checkfirst=False)

    # There is no direct way to convert from Integer to ENUM that is
    # not database specific so we transition through a string type.
    op.alter_column('ps_endpoints', 'mwi_subscribe_replaces_unsolicited',
                    type_=sa.String(5))
    op.alter_column('ps_endpoints', 'mwi_subscribe_replaces_unsolicited',
            type_=ast_bool_values, postgresql_using='mwi_subscribe_replaces_unsolicited::{0}'.format(AST_BOOL_NAME))
示例#43
0
def downgrade():
    with op.get_context().autocommit_block():
        op.execute("""
            ALTER TABLE services
                DROP CONSTRAINT ck_services_status,
                ADD CONSTRAINT ck_services_status CHECK(status::text = ANY(ARRAY['disabled', 'enabled', 'published']));
        """)
        op.execute("""
            ALTER TABLE archived_services
                DROP CONSTRAINT ck_archived_services_status,
                ADD CONSTRAINT ck_archived_services_status CHECK(status::text = ANY(ARRAY['disabled', 'enabled', 'published']));
        """)
示例#44
0
def downgrade():
    connection = None

    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level='AUTOCOMMIT')

    op.execute("ALTER TYPE log_type DROP VALUE 'create_cert'")
    op.execute("ALTER TYPE log_type DROP VALUE 'update_cert'")

    if connection is not None:
        connection.execution_options(isolation_level='READ_COMMITTED')
示例#45
0
def upgrade():
    # ALTER TYPE doesn't work from inside a transaction, disable it
    connection = None
    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level="AUTOCOMMIT")

    op.execute("ALTER TYPE scrapertype ADD VALUE IF NOT EXISTS 'YOUTUBE'")

    # re-activate the transaction for any future migrations
    if connection is not None:
        connection.execution_options(isolation_level="READ_COMMITTED")
def upgrade():
    # manually entered

    if op.get_context().dialect.name == 'postgresql':
        # https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.migration.MigrationContext.autocommit_block
        with op.get_context().autocommit_block():
            op.execute("ALTER TYPE taskstatus  ADD VALUE 'created'")
    else:
        # sqlite uses varchar + constraint for enum types
        taskstatus_enum = sa.Enum(
            'created',
            'pending',
            'running',
            'success',
            'failed',
            'skipped',
            name='taskstatus',
        )

        with op.batch_alter_table("tasks") as batch_op:
            batch_op.alter_column("status", type_=taskstatus_enum)
示例#47
0
def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('rooms_slots')
    op.drop_table('presentations')
    op.drop_table('slots')
    op.drop_table('days')
    op.drop_table('rooms')
    ### end Alembic commands ###

    context = op.get_context()
    if context.bind.dialect.name == 'postgresql':
        op.execute('DROP TYPE slotkind')
示例#48
0
def upgrade():
    # proxy/table info is no longer in the database
    op.drop_table('proxies')
    op.drop_table('hubs')

    # drop some columns no longer in use
    try:
        op.drop_column('users', 'auth_state')
        # mysql cannot drop _server_id without also dropping
        # implicitly created foreign key
        if op.get_context().dialect.name == 'mysql':
            op.drop_constraint('users_ibfk_1', 'users',  type_='foreignkey')
        op.drop_column('users', '_server_id')
    except sa.exc.OperationalError:
        # this won't be a problem moving forward, but downgrade will fail
        if op.get_context().dialect.name == 'sqlite':
            logger.warning("sqlite cannot drop columns. Leaving unused old columns in place.")
        else:
            raise

    op.add_column('users', sa.Column('encrypted_auth_state', sa.types.LargeBinary))
def column_downgrade(table_name, column_name, enum_name, enum_values):
    if op.get_context().bind.dialect.name != 'postgresql':
        op.alter_column(table_name, column_name,
                        type_=sa.Enum(*enum_values, name=enum_name))
        return

    # Postgres requires a few more steps
    updated = sa.Enum(*enum_values, name=enum_name)
    updated.create(op.get_bind(), checkfirst=False)

    op.execute('ALTER TABLE ' + table_name + ' ALTER COLUMN ' + column_name +
               ' TYPE ' + enum_name + ' USING identify_by::text::' + enum_name)
def downgrade():
    bind = op.get_context().bind
    inspector = reflection.Inspector.from_engine(bind)
    has_tables = inspector.get_table_names()
    if 'event_logs' in has_tables:
        columns = [x.get('name') for x in inspector.get_columns('event_logs')]
        if 'asset_key' in columns:
            op.drop_column('event_logs', 'asset_key')
            op.drop_index('idx_asset_key', 'event_logs')

            # also drop the index that was missing from the step_key migration
            op.drop_index('idx_step_key', 'event_logs')
示例#51
0
def upgrade():
    schema = context.get_context().config.get_main_option('schema')
    staticschema = schema + '_static'
    parentschema = context.get_context().config.get_main_option('parentschema')

    engine = op.get_bind().engine
    if type(engine).__name__ != 'MockConnection' and \
            op.get_context().dialect.has_table(
                engine, 'user', schema=staticschema):  # pragma: no cover
        return

    op.create_table(
        'user',
        Column('type', String(10), nullable=False),
        Column('id', Integer, primary_key=True),
        Column('username', Unicode, unique=True, nullable=False),
        Column('password', Unicode, nullable=False),
        Column('email', Unicode, nullable=False),
        Column('is_password_changed', Boolean, default=False),
        Column('role_name', String),
        schema=staticschema,
    )
    parent_column = ''
    parent_select = ''
    parent_join = ''
    if parentschema is not None and parentschema is not '':  # pragma: no cover
        op.add_column('user',
                      Column('parent_role_name', String),
                      schema=staticschema)
        parent_column = ', parent_role_name'
        parent_select = ', pr.name'
        parent_join = (
            'LEFT OUTER JOIN %(parentschema)s.role AS pr ON (pr.id = u.parent_role_id)'
            % {
                'parentschema': parentschema,
            })

    op.execute(
        'INSERT INTO %(staticschema)s.user '
        '(type, username, password, email, is_password_changed, role_name%(parent_column)s) ('
        'SELECT u.type, u.username, u.password, u.email, '
        'u.is_password_changed, r.name%(parent_select)s '
        'FROM %(schema)s.user AS u '
        'LEFT OUTER JOIN %(schema)s.role AS r ON (r.id = u.role_id) %(parent_join)s'
        ')' % {
            'staticschema': staticschema,
            'schema': schema,
            'parent_select': parent_select,
            'parent_column': parent_column,
            'parent_join': parent_join,
        })

    op.drop_table('user', schema=schema)
示例#52
0
def upgrade():
    bind = op.get_context().bind
    inspector = reflection.Inspector.from_engine(bind)
    has_tables = inspector.get_table_names()
    if "event_logs" in has_tables:
        columns = [x.get("name") for x in inspector.get_columns("event_logs")]
        if "asset_key" not in columns:
            op.add_column("event_logs", sa.Column("asset_key", sa.String))
            op.create_index("idx_asset_key", "event_logs", ["asset_key"], unique=False)

            # also add index that was missing from the step_key migration
            op.create_index("idx_step_key", "event_logs", ["step_key"], unique=False)
示例#53
0
def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_workflow_instances_start_date'),
                  table_name='workflow_instances')
    op.drop_index(op.f('ix_workflow_instances_end_date'),
                  table_name='workflow_instances')
    op.drop_index(op.f('ix_workflow_instances_created_date'),
                  table_name='workflow_instances')

    op.drop_index(op.f('ix_stage_instances_start_date'),
                  table_name='stage_instances')
    op.drop_index(op.f('ix_stage_instances_end_date'),
                  table_name='stage_instances')
    op.drop_index(op.f('ix_stage_instances_created_date'),
                  table_name='stage_instances')

    op.drop_constraint(None, 'qa_tests', type_='foreignkey')
    op.drop_index(op.f('ix_qa_tests_qa_test_type_id'), table_name='qa_tests')
    op.drop_column('qa_tests', 'qa_test_type_id')

    op.drop_index(op.f('ix_qa_test_mappings_feature_id'),
                  table_name='qa_test_mappings')
    op.drop_index(op.f('ix_qa_test_mappings_behavior_id'),
                  table_name='qa_test_mappings')
    op.drop_index(op.f('ix_qa_test_mappings_area_id'),
                  table_name='qa_test_mappings')

    if 'sqlite' != op.get_context().dialect.name:
        op.alter_column('qa_test_mappings',
                        'feature_id',
                        existing_type=sa.INTEGER(),
                        nullable=False)

    op.drop_constraint(None, 'qa_features', type_='foreignkey')
    op.drop_index(op.f('ix_qa_features_product_id'), table_name='qa_features')
    op.drop_column('qa_features', 'product_id')

    op.drop_constraint(None, 'qa_behavior_points', type_='foreignkey')
    op.drop_index(op.f('ix_qa_behavior_points_product_id'),
                  table_name='qa_behavior_points')
    op.drop_column('qa_behavior_points', 'product_id')

    op.drop_constraint(None, 'qa_areas', type_='foreignkey')
    op.drop_index(op.f('ix_qa_areas_product_id'), table_name='qa_areas')
    op.drop_column('qa_areas', 'product_id')

    op.drop_index(op.f('ix_qa_products_vcs_id'), table_name='qa_products')
    op.drop_index(op.f('ix_qa_products_name'), table_name='qa_products')
    op.drop_index(op.f('ix_qa_products_id'), table_name='qa_products')
    op.drop_table('qa_products')

    op.drop_index(op.f('ix_qa_test_types_id'), table_name='qa_test_types')
    op.drop_table('qa_test_types')
示例#54
0
def downgrade(active_plugins=None, options=None):

    context = op.get_context()
    dialect = context.bind.dialect.name

    # Drop the existed foreign key constraints
    # In order to perform primary key changes
    op.drop_constraint(
        name=fk_names[dialect]['l3_agent_id'],
        table_name=TABLE_NAME,
        type_='foreignkey'
    )
    op.drop_constraint(
        name=fk_names[dialect]['router_id'],
        table_name=TABLE_NAME,
        type_='foreignkey'
    )

    op.drop_constraint(
        name=PK_NAME,
        table_name=TABLE_NAME,
        type_='primary'
    )

    op.add_column(
        TABLE_NAME,
        sa.Column('id', sa.String(32))
    )

    # Restore the foreign key constraints
    op.create_foreign_key(
        name=fk_names[dialect]['router_id'],
        source=TABLE_NAME,
        referent='routers',
        local_cols=['router_id'],
        remote_cols=['id'],
        ondelete='CASCADE'
    )

    op.create_foreign_key(
        name=fk_names[dialect]['l3_agent_id'],
        source=TABLE_NAME,
        referent='agents',
        local_cols=['l3_agent_id'],
        remote_cols=['id'],
        ondelete='CASCADE'
    )

    op.create_primary_key(
        name=PK_NAME,
        table_name=TABLE_NAME,
        cols=['id']
    )
示例#55
0
def downgrade():
    with op.get_context().autocommit_block():
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS ind_message_otherid ON message (otherid)"
        )
        op.execute(
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS ind_message_userid ON message (userid)"
        )
        op.execute(
            "DROP INDEX CONCURRENTLY IF EXISTS ind_message_otherid_noteid")
        op.execute(
            "DROP INDEX CONCURRENTLY IF EXISTS ind_message_userid_noteid")