Python alembic.op.create_unique_constraint() Examples

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('app_user',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(length=255), nullable=True),
    sa.Column('password', sa.String(length=255), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('is_admin', sa.Boolean(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email')
    )
    op.create_table('password_reset',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('code', sa.String(length=255), nullable=True),
    sa.Column('date', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('code')
    )
    ### end Alembic commands ###

    op.create_unique_constraint('unique_user_code', 'password_reset', ['user_id', 'code']) 

def upgrade():
    op.create_table('sfc_port_pair_group_params',
                    sa.Column('keyword', sa.String(length=255),
                              nullable=False),
                    sa.Column('value', sa.String(length=255),
                              nullable=True),
                    sa.Column('pair_group_id', sa.String(length=36),
                              nullable=False),
                    sa.ForeignKeyConstraint(['pair_group_id'],
                                            ['sfc_port_pair_groups.id'],
                                            ondelete='CASCADE'),
                    sa.PrimaryKeyConstraint('keyword', 'pair_group_id'),
                    mysql_engine='InnoDB'
                    )
    op.add_column('sfc_port_chains', sa.Column('chain_id',
                  sa.Integer(), nullable=False))
    op.create_unique_constraint(None, 'sfc_port_chains', ['chain_id'])
    op.add_column('sfc_port_pair_groups', sa.Column('group_id',
                  sa.Integer(), nullable=False))
    op.create_unique_constraint(None, 'sfc_port_pair_groups', ['group_id']) 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('rss_parser_funcs', 'func',
               existing_type=sa.TEXT(),
               nullable=False)
    op.add_column('rss_parser_feed_name_lut_version', sa.Column('feed_name', sa.TEXT(), autoincrement=False, nullable=True))
    op.create_index('ix_rss_parser_feed_name_lut_version_feed_name', 'rss_parser_feed_name_lut_version', ['feed_name'], unique=False)
    op.drop_index(op.f('ix_rss_parser_feed_name_lut_version_feed_id'), table_name='rss_parser_feed_name_lut_version')
    op.drop_column('rss_parser_feed_name_lut_version', 'feed_id')
    op.add_column('rss_parser_feed_name_lut', sa.Column('feed_name', sa.TEXT(), autoincrement=False, nullable=False))
    op.drop_constraint(None, 'rss_parser_feed_name_lut', type_='foreignkey')
    op.create_foreign_key('rss_parser_feed_name_lut_feed_name_fkey', 'rss_parser_feed_name_lut', 'rss_parser_funcs', ['feed_name'], ['feed_name'])
    op.drop_constraint(None, 'rss_parser_feed_name_lut', type_='unique')
    op.create_unique_constraint('rss_parser_feed_name_lut_feed_netloc_feed_name_key', 'rss_parser_feed_name_lut', ['feed_netloc', 'feed_name'])
    op.create_index('ix_rss_parser_feed_name_lut_feed_name', 'rss_parser_feed_name_lut', ['feed_name'], unique=False)
    op.drop_index(op.f('ix_rss_parser_feed_name_lut_feed_id'), table_name='rss_parser_feed_name_lut')
    op.drop_column('rss_parser_feed_name_lut', 'feed_id')
    ### end Alembic commands ### 

def upgrade():
    op.add_column('compute_node',
                  sa.Column('rp_uuid', sa.String(length=36), nullable=True))
    op.create_unique_constraint('uniq_compute_node0rp_uuid',
                                'compute_node', ['rp_uuid'])

    # perform data migration between tables
    session = sa.orm.Session(bind=op.get_bind())
    with session.begin(subtransactions=True):
        for row in session.query(COMPUTE_NODE_TABLE):
            session.execute(
                COMPUTE_NODE_TABLE.update().values(
                    rp_uuid=row.uuid).where(
                        COMPUTE_NODE_TABLE.c.uuid == row.uuid)
            )
    # this commit is necessary to allow further operations
    session.commit()

    op.alter_column('compute_node', 'rp_uuid',
                    nullable=False,
                    existing_type=sa.String(length=36),
                    existing_nullable=True,
                    existing_server_default=False) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('task_result', sa.Column('traceback', sa.TEXT(), autoincrement=False, nullable=True))
    op.add_column('task_result', sa.Column('task_id', sa.VARCHAR(length=155), autoincrement=False, nullable=True))
    op.add_column('task_result', sa.Column('date_done', postgresql.TIMESTAMP(), autoincrement=False, nullable=True))
    op.add_column('task_result', sa.Column('status', sa.VARCHAR(length=50), autoincrement=False, nullable=True))
    op.add_column('task_result', sa.Column('result', postgresql.BYTEA(), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'task_result', type_='foreignkey')
    op.create_unique_constraint('task_result_task_id_key', 'task_result', ['task_id'])
    op.drop_index(op.f('ix_task_result_celery_taskmeta_id'), table_name='task_result')
    op.drop_column('task_result', 'celery_taskmeta_id')
    op.drop_table('celery_tasksetmeta')
    op.drop_table('celery_taskmeta')
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('output_file', sa.Column('uploaded_movie_url', sa.VARCHAR(length=600), autoincrement=False, nullable=True))
    op.add_column('output_file', sa.Column('uploaded_movie_name', sa.VARCHAR(length=150), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'output_file', type_='foreignkey')
    op.drop_constraint('output_file_uc', 'output_file', type_='unique')
    op.create_unique_constraint('output_file_uc', 'output_file', ['name', 'entity_id', 'output_type_id', 'task_type_id', 'representation', 'revision'])
    op.drop_column('output_file', 'temporal_entity_id')
    op.drop_constraint(None, 'asset_instance', type_='foreignkey')
    op.create_index('ix_asset_instance_entity_type_id', 'asset_instance', ['entity_type_id'], unique=False)
    op.create_index('ix_asset_instance_entity_id', 'asset_instance', ['entity_id'], unique=False)
    op.drop_constraint('asset_instance_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_uc', 'asset_instance', ['asset_id', 'entity_id', 'number'])
    op.drop_constraint('asset_instance_name_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_name_uc', 'asset_instance', ['entity_id', 'name'])
    op.drop_index(op.f('ix_asset_instance_scene_id'), table_name='asset_instance')
    op.alter_column('asset_instance', 'entity_type_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.alter_column('asset_instance', 'entity_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.drop_column('asset_instance', 'scene_id')
    op.drop_table('asset_instance_link')
    # ### end Alembic commands ### 

def batch_create_unique_constraint(
            cls, operations, constraint_name, columns, **kw):
        """Issue a "create unique constraint" instruction using the
        current batch migration context.

        The batch form of this call omits the ``source`` and ``schema``
        arguments from the call.

        .. seealso::

            :meth:`.Operations.create_unique_constraint`

        .. versionchanged:: 0.8.0 The following positional argument names
           have been changed:

           * name -> constraint_name

        """
        kw['schema'] = operations.impl.schema
        op = cls(
            constraint_name, operations.impl.table_name, columns,
            **kw
        )
        return operations.invoke(op) 

def batch_create_unique_constraint(
        cls, operations, constraint_name, columns, **kw
    ):
        """Issue a "create unique constraint" instruction using the
        current batch migration context.

        The batch form of this call omits the ``source`` and ``schema``
        arguments from the call.

        .. seealso::

            :meth:`.Operations.create_unique_constraint`

        .. versionchanged:: 0.8.0 The following positional argument names
           have been changed:

           * name -> constraint_name

        """
        kw["schema"] = operations.impl.schema
        op = cls(constraint_name, operations.impl.table_name, columns, **kw)
        return operations.invoke(op) 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_notification_statistics_day'), table_name='notification_statistics')
    op.drop_constraint('uix_service_to_day', 'notification_statistics')

    op.alter_column('notification_statistics', 'day', new_column_name='day_date')
    op.add_column('notification_statistics', sa.Column('day', sa.String(), nullable=True))

    op.get_bind()
    op.execute("UPDATE notification_statistics ns1 SET day = (SELECT to_char(day_date, 'YYYY-MM-DD') FROM notification_statistics ns2 WHERE ns1.id = ns2.id)")

    op.alter_column('notification_statistics', 'day', nullable=False)
    op.drop_column('notification_statistics', 'day_date')
    op.create_unique_constraint('uix_service_to_day', 'notification_statistics', columns=['service_id', 'day'])

    ### end Alembic commands ### 

def downgrade():
    ''' Remove column access_id from user_projects and projects_groups '''

    # this removes the current constraints as well.
    op.drop_column('user_projects', 'access')
    op.drop_column('projects_groups', 'access')

    # recreate the previous constraints
    op.create_unique_constraint(
            None,
            'user_projects',
            ['project_id', 'user_id'],
    )
    op.create_primary_key(
            None,
            'projects_groups',
            ['project_id', 'group_id'],
    )
    op.drop_table('access_levels') 

def upgrade():
    op.create_unique_constraint(
        op.f('uq_txn_reconciles_ofx_account_id'),
        'txn_reconciles',
        ['ofx_account_id', 'ofx_fitid']
    )
    op.create_unique_constraint(
        op.f('uq_txn_reconciles_txn_id'),
        'txn_reconciles',
        ['txn_id']
    ) 

def upgrade():

    op.execute("""
        update
            email_branding
        set
            domain = null
        where
            domain = ''
    """)
    op.create_unique_constraint('uq_email_branding_domain', 'email_branding', ['domain']) 

def upgrade():
    generate_records_for_existing()
    for table, model in TABLE_MODELS:
        op.alter_column(table, 'standard_attr_id', nullable=False,
                        existing_type=sa.BigInteger(), existing_nullable=True,
                        existing_server_default=False)
        op.create_foreign_key(
            constraint_name=None, source_table=table,
            referent_table='standardattributes',
            local_cols=['standard_attr_id'], remote_cols=['id'],
            ondelete='CASCADE')
        op.create_unique_constraint(
            constraint_name='uniq_%s0standard_attr_id' % table,
            table_name=table, columns=['standard_attr_id']) 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_unique_constraint('myuser_aga_id_key', 'myuser', ['aga_id'])
    op.drop_index('aga_id__claimed', table_name='myuser')
    op.drop_column('myuser', 'claimed')
    ### end Alembic commands ### 

def upgrade():
    op.create_unique_constraint('ix_id_service_id', 'template_folder', ['id', 'service_id'])
    op.create_table('user_folder_permissions',
        sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('template_folder_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.ForeignKeyConstraint(['template_folder_id', 'service_id'], ['template_folder.id', 'template_folder.service_id'], ),
        sa.ForeignKeyConstraint(['user_id', 'service_id'], ['user_to_service.user_id', 'user_to_service.service_id'], ),
        sa.ForeignKeyConstraint(['template_folder_id'], ['template_folder.id'], ),
        sa.PrimaryKeyConstraint('user_id', 'template_folder_id', 'service_id'),
    ) 

def upgrade():
    op.alter_column('email_branding', 'name',
                    existing_type=sa.VARCHAR(length=255),
                    nullable=False)
    op.create_unique_constraint('uq_email_branding_name', 'email_branding', ['name']) 

def downgrade():
    op.execute("DELETE FROM notifications WHERE template_id = '{}'".format(template_id))
    op.execute("DELETE FROM notification_history WHERE template_id = '{}'".format(template_id))
    op.execute("DELETE FROM template_redacted WHERE template_id = '{}'".format(template_id))
    op.execute("DELETE FROM templates_history WHERE id = '{}'".format(template_id))
    op.execute("DELETE FROM templates WHERE id = '{}'".format(template_id))
    op.create_unique_constraint('organisation_to_service_service_id_organisation_id_key', 'organisation_to_service', ['service_id', 'organisation_id']) 

def downgrade():
    op.drop_index('uix_service_to_key_name', table_name='api_keys')
    op.create_unique_constraint('uix_service_to_key_name', 'api_keys', ['service_id', 'name']) 

def test_add_unique_constraint(self):
        context = op_fixture()
        op.create_unique_constraint("uk_test", "t1", ["foo", "bar"])
        context.assert_(
            "ALTER TABLE t1 ADD CONSTRAINT uk_test UNIQUE (foo, bar)"
        ) 

def downgrade():
    op.drop_constraint('caix', 'clientapplication', type_='unique')
    op.create_unique_constraint(u'caix', 'clientapplication', ['ip', 'clienttype'])
    # This will probably raise errors about violated UNIQUE constraints
    op.drop_column('clientapplication', 'node') 

def upgrade():
    node = get_privacyidea_node()
    try:
        # The ``node`` field is not nullable. Hence, We set the server_default to the current node to ensure that
        # the ``node`` of all existing rows is set to the current node.
        op.add_column('clientapplication', sa.Column('node', sa.Unicode(length=255),
                                                     nullable=False, server_default=node))
        op.drop_constraint(u'caix', 'clientapplication', type_='unique')
        op.create_unique_constraint('caix', 'clientapplication', ['ip', 'clienttype', 'node'])
    except Exception as exx:
        print("Failed to add 'node' column to 'clientapplication' table")
        print(exx) 

def upgrade():
    try:
        # Adapt schema definition to work with oracle
        op.create_unique_constraint(None, 'caconnector', ['name'])
        op.drop_index('ix_clientapplication_id', table_name='clientapplication')
        op.create_unique_constraint(None, 'machineresolver', ['name'])
        op.drop_index('ix_pidea_audit_id', table_name='pidea_audit')
        op.create_unique_constraint(None, 'policy', ['name'])
        op.create_unique_constraint(None, 'radiusserver', ['identifier'])
        op.create_unique_constraint(None, 'realm', ['name'])
        op.create_unique_constraint(None, 'resolver', ['name'])
        op.create_unique_constraint(None, 'smsgateway', ['identifier'])
        op.create_index(op.f('ix_subscription_application'), 'subscription', ['application'], unique=False)
        op.alter_column('token', 'active',
                   existing_type=sa.BOOLEAN(),
                   nullable=False)
        op.create_index(op.f('ix_token_resolver'), 'token', ['resolver'], unique=False)
        op.create_index(op.f('ix_token_serial'), 'token', ['serial'], unique=True)
        op.create_index(op.f('ix_token_tokentype'), 'token', ['tokentype'], unique=False)
        op.create_index(op.f('ix_token_user_id'), 'token', ['user_id'], unique=False)
        op.alter_column('tokenrealm', 'id',
                   existing_type=sa.INTEGER(),
                   nullable=True)
    except Exception as exx:
        print ("## Schema seems already to be oracle compatible.")
        print (exx)

    try:
        # Remove unused columns in the Table usercache.
        op.drop_column('usercache', 'realm')
        op.drop_column('usercache', 'expiration')
        op.drop_index('ix_usercache_expiration', table_name='usercache')
    except Exception as exx:
        print ("## Unnecessary columns in table usercache obviously do not "
               "exist anymore.")
        print (exx) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_unique_constraint('user_sshkey_key', 'user', ['sshkey'])
    op.create_index('ix_user_comment', 'user', ['comment'], unique=False)
    op.drop_index(op.f('ix_user_sshkeyhash'), table_name='user')
    op.alter_column('user', 'sshkeyhash',
               existing_type=sa.String(length=64),
               type_=sa.VARCHAR(length=5000),
               existing_nullable=True)
    op.create_index('ix_passentry_salt', 'passentry', ['salt'], unique=False)
    op.create_index('ix_passentry_password', 'passentry', ['password'], unique=False)
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'asset_instance', type_='foreignkey')
    op.create_unique_constraint('asset_instance_name_uc', 'asset_instance', ['scene_id', 'name'])
    op.drop_constraint('asset_instance_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_uc', 'asset_instance', ['asset_id', 'scene_id', 'number'])
    op.drop_index(op.f('ix_asset_instance_target_asset_id'), table_name='asset_instance')
    op.drop_column('asset_instance', 'target_asset_id')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('asset_instance', sa.Column('target_asset_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True))
    op.create_index(op.f('ix_asset_instance_target_asset_id'), 'asset_instance', ['target_asset_id'], unique=False)
    op.drop_constraint('asset_instance_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_uc', 'asset_instance', ['asset_id', 'target_asset_id', 'scene_id', 'number'])
    op.drop_constraint('asset_instance_name_uc', 'asset_instance', type_='unique')
    op.create_foreign_key(None, 'asset_instance', 'entity', ['target_asset_id'], ['id'])
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('asset_instance_link',
    sa.Column('entity_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=False),
    sa.Column('asset_instance_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=False),
    sa.ForeignKeyConstraint(['asset_instance_id'], ['asset_instance.id'], ),
    sa.ForeignKeyConstraint(['entity_id'], ['entity.id'], ),
    sa.PrimaryKeyConstraint('entity_id', 'asset_instance_id')
    )
    op.add_column('asset_instance', sa.Column('scene_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True))
    op.alter_column('asset_instance', 'entity_id',
               existing_type=postgresql.UUID(),
               nullable=True)
    op.alter_column('asset_instance', 'entity_type_id',
               existing_type=postgresql.UUID(),
               nullable=True)
    op.create_index(op.f('ix_asset_instance_scene_id'), 'asset_instance', ['scene_id'], unique=False)
    op.drop_constraint('asset_instance_name_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_name_uc', 'asset_instance', ['scene_id', 'name'])
    op.drop_constraint('asset_instance_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_uc', 'asset_instance', ['asset_id', 'scene_id', 'number'])
    op.drop_index('ix_asset_instance_entity_id', table_name='asset_instance')
    op.drop_index('ix_asset_instance_entity_type_id', table_name='asset_instance')
    op.create_foreign_key(None, 'asset_instance', 'entity', ['scene_id'], ['id'])
    op.add_column('output_file', sa.Column('temporal_entity_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True))
    op.drop_constraint('output_file_uc', 'output_file', type_='unique')
    op.create_unique_constraint('output_file_uc', 'output_file', ['name', 'entity_id', 'asset_instance_id', 'output_type_id', 'task_type_id', 'temporal_entity_id', 'representation', 'revision'])
    op.create_foreign_key(None, 'output_file', 'entity', ['temporal_entity_id'], ['id'])
    op.drop_column('output_file', 'uploaded_movie_name')
    op.drop_column('output_file', 'uploaded_movie_url')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('notification_uc', 'notification', type_='unique')
    op.create_unique_constraint('notification_uc', 'notification', ['person_id', 'author_id', 'comment_id', 'type'])
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('task_type_uc', 'task_type', type_='unique')
    op.create_unique_constraint('task_type_uc', 'task_type', ['name', 'department_id'])
    op.drop_column('task_type', 'for_entity')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('task_type', sa.Column('for_entity', sa.String(length=10), nullable=True))
    op.drop_constraint('task_type_uc', 'task_type', type_='unique')
    op.create_unique_constraint('task_type_uc', 'task_type', ['name', 'for_entity', 'department_id'])
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('playlist_uc', 'playlist', type_='unique')
    op.create_unique_constraint('playlist_uc', 'playlist', ['name', 'project_id'])
    # ### end Alembic commands ###