Python alembic.op.create_check_constraint() Examples

def upgrade():
    op.add_column('templates', sa.Column('is_letter_contact_blank', sa.Boolean(), nullable=True))
    op.add_column('templates_history', sa.Column('is_letter_contact_blank', sa.Boolean(), nullable=True))
    op.execute("update templates set is_letter_contact_blank = false")
    op.execute("update templates_history set is_letter_contact_blank = false")
    op.alter_column("templates", "is_letter_contact_blank", nullable=False)
    op.alter_column("templates_history", "is_letter_contact_blank", nullable=False)

    op.create_check_constraint(
        "ck_templates_contact_block_is_blank",
        "templates",
        "Not(is_letter_contact_blank = True and service_letter_contact_id is not Null)"
    )
    op.create_check_constraint(
        "ck_templates_history_contact_block_is_blank",
        "templates_history",
        "Not(is_letter_contact_blank = True and service_letter_contact_id is not Null)"
    ) 

def batch_create_check_constraint(
            cls, operations, constraint_name, condition, **kw):
        """Issue a "create check 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_check_constraint`

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

           * name -> constraint_name

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

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('sticker_usage', 'usage_count',
                    existing_type=sa.INTEGER(),
                    nullable=False)
    op.add_column('chat', sa.Column('expecting_sticker_set', sa.BOOLEAN(), autoincrement=False, nullable=False))
    op.drop_constraint("only_one_action_check", "chat")
    op.create_check_constraint("only_one_action_check", "chat", """
        (expecting_sticker_set IS TRUE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (tagging_random_sticker IS TRUE AND expecting_sticker_set IS FALSE AND fix_single_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (fix_single_sticker IS TRUE AND tagging_random_sticker IS FALSE AND expecting_sticker_set IS FALSE AND full_sticker_set IS FALSE) OR \
        (full_sticker_set IS TRUE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE AND expecting_sticker_set IS FALSE) OR \
        (full_sticker_set IS FALSE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE AND expecting_sticker_set IS FALSE)
    """)

    # ### end Alembic commands ### 

def test_add_check_constraint_name_is_none(self):
        context = op_fixture(naming_convention={"ck": "ck_%(table_name)s_foo"})
        op.create_check_constraint(
            None, "user_table", func.len(column("name")) > 5
        )
        context.assert_(
            "ALTER TABLE user_table ADD CONSTRAINT ck_user_table_foo "
            "CHECK (len(name) > 5)"
        ) 

def test_add_check_constraint(self):
        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        op.create_check_constraint(
            "foo", "user_table", func.len(column("name")) > 5
        )
        context.assert_(
            "ALTER TABLE user_table ADD CONSTRAINT ck_user_table_foo "
            "CHECK (len(name) > 5)"
        ) 

def test_add_explicit_constraint(self):
        op_fixture("sqlite")
        assert_raises_message(
            NotImplementedError,
            "No support for ALTER of constraints in SQLite dialect",
            op.create_check_constraint,
            "foo",
            "sometable",
            column("name") > 5,
        ) 

def batch_create_check_constraint(
            cls, operations, constraint_name, condition, **kw):
        """Issue a "create check 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_check_constraint`

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

           * name -> constraint_name

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

def upgrade():
    ''' Add the column remote_git to the table pull_requests and make the
    project_id_from field nullable.
    '''
    op.add_column(
        'pull_requests',
        sa.Column('remote_git', sa.Text, nullable=True)
    )
    op.alter_column(
        'pull_requests',
        column_name='project_id_from',
        nullable=True,
        existing_nullable=False)
    op.create_check_constraint(
        "ck_lcl_or_remo_pr",
        "pull_requests",
        'NOT(project_id_from IS NULL AND remote_git IS NULL)'
    ) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint("only_one_action_check", "chat")
    op.create_check_constraint("only_one_action_check", "chat", """
        (tagging_random_sticker IS TRUE AND fix_single_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (fix_single_sticker IS TRUE AND tagging_random_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (full_sticker_set IS TRUE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE) OR \
        (full_sticker_set IS FALSE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE)
    """)
    op.drop_column('chat', 'expecting_sticker_set')
    op.alter_column('sticker_usage', 'usage_count',
                    existing_type=sa.INTEGER(),
                    nullable=True)

    # ### end Alembic commands ### 

def downgrade():
    """ Bring back the pull_request_check constraint. """
    op.create_check_constraint(
        "pull_requests_check",
        "pull_requests",
        'NOT(project_id_from IS NULL AND remote_git IS NULL)'
    ) 

def downgrade():
    op.create_check_constraint('check_namespace', 'redirect', 'rd_namespace >= 0') 

def downgrade():
    op.drop_constraint('property_check', 'property')
    op.create_check_constraint('property_check', 'property', 'NOT(prop_resource IS NULL AND prop_string IS NULL AND prop_datetime IS NULL AND prop_integer IS NULL AND prop_url IS NULL AND prop_json IS NULL)'),
    op.drop_column('property', 'prop_float') 

def upgrade():
    op.add_column('property', sa.Column('prop_float', sa.Float(), nullable=True))
    op.drop_constraint('property_check', 'property')
    op.create_check_constraint('property_check', 'property', 'NOT(prop_resource IS NULL AND prop_string IS NULL AND prop_datetime IS NULL AND prop_integer IS NULL AND prop_float IS NULL AND prop_url IS NULL AND prop_json IS NULL)'), 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('property_check', 'property')
    op.create_check_constraint('property_check', 'property', 'NOT(prop_resource IS NULL AND prop_string IS NULL AND prop_datetime IS NULL AND prop_integer IS NULL AND prop_url IS NULL)'),
    op.drop_column('property', 'prop_json')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('property', sa.Column('prop_json', sa.JSON(), nullable=True))
    op.drop_constraint('property_check', 'property')
    op.create_check_constraint('property_check', 'property', 'NOT(prop_resource IS NULL AND prop_string IS NULL AND prop_datetime IS NULL AND prop_integer IS NULL AND prop_url IS NULL AND prop_json IS NULL)'),
    # ### end Alembic commands ### 

def downgrade():
    op.add_column('chat', sa.Column('current_sticker_set_name', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.create_foreign_key('chat_current_sticker_set_name_fkey', 'chat', 'sticker_set', ['current_sticker_set_name'], ['name'], onupdate='CASCADE', ondelete='SET NULL')
    op.create_index('ix_chat_current_sticker_set_name', 'chat', ['current_sticker_set_name'], unique=False)
    op.drop_column('chat', 'tag_mode')

    op.create_check_constraint("only_one_action_check", "chat", """
        (tagging_random_sticker IS TRUE AND fix_single_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (fix_single_sticker IS TRUE AND tagging_random_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (full_sticker_set IS TRUE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE) OR \
        (full_sticker_set IS FALSE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE)
    """) 

def upgrade():
	conn = op.get_bind()
	sync_trigger(conn, 'package', 'search_vector', ["name", "title", "short_desc", "desc"])
	op.create_check_constraint("name_valid", "package", "name ~* '^[a-z0-9_]+$'") 

def upgrade():
    op.execute("""
            update
                services
            set
                postage = 'second'
        """)

    op.create_check_constraint(
        'ck_services_postage',
        'services',
        "postage in ('second', 'first')"
    )
    op.alter_column('services', 'postage', nullable=False) 

def upgrade():
    op.alter_column('users', 'mobile_number', nullable=True)

    op.create_check_constraint(
        'ck_users_mobile_or_email_auth',
        'users',
        "auth_type = 'email_auth' or mobile_number is not null"
    ) 

def upgrade():
    op.create_check_constraint("ck_started_before_ended",
                               "resource",
                               "started_at <= ended_at")
    op.create_check_constraint("ck_started_before_ended",
                               "resource_history",
                               "started_at <= ended_at") 

def test_add_check_constraint_schema(self):
        context = op_fixture()
        op.create_check_constraint(
            "ck_user_name_len",
            "user_table",
            func.len(column("name")) > 5,
            schema="foo",
        )
        context.assert_(
            "ALTER TABLE foo.user_table ADD CONSTRAINT ck_user_name_len "
            "CHECK (len(name) > 5)"
        ) 

def test_add_check_constraint(self):
        context = op_fixture()
        op.create_check_constraint(
            "ck_user_name_len", "user_table", func.len(column("name")) > 5
        )
        context.assert_(
            "ALTER TABLE user_table ADD CONSTRAINT ck_user_name_len "
            "CHECK (len(name) > 5)"
        ) 

def upgrade():
	conn = op.get_bind()
	op.create_check_constraint("CK_approval_valid", "package_release", "not approved OR (task_id IS NULL AND NOT url = '')") 

def batch_create_check_constraint(
        cls, operations, constraint_name, condition, **kw
    ):
        """Issue a "create check 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_check_constraint`

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

           * name -> constraint_name

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

def upgrade():
    """General cleanup and constraint stuff."""
    session = Session(bind=op.get_bind())
    # Set all changes to reviewed, where an task exists
    session.query(InlineQueryRequest) \
        .filter(or_(
            InlineQueryRequest.duration.is_(None),
            InlineQueryRequest.offset.is_(None),
            InlineQueryRequest.next_offset.is_(None),
        )) \
        .delete()

    session.query(InlineQueryRequest) \
        .filter(or_(
            InlineQueryRequest.duration.is_(None),
            InlineQueryRequest.offset.is_(None),
            InlineQueryRequest.next_offset.is_(None),
        )) \
        .delete()

    op.alter_column('inline_query', 'query',
                    existing_type=sa.VARCHAR(),
                    nullable=False)
    op.drop_column('inline_query', 'bot')
    op.alter_column('inline_query_request', 'duration',
                    existing_type=postgresql.INTERVAL(),
                    nullable=False)
    op.alter_column('inline_query_request', 'next_offset',
                    existing_type=sa.VARCHAR(),
                    nullable=False)
    op.alter_column('inline_query_request', 'offset',
                    existing_type=sa.VARCHAR(),
                    nullable=False)
    op.alter_column('sticker', 'original_emojis',
                    existing_type=sa.VARCHAR(),
                    nullable=False)
    op.drop_column('vote_ban', 'old_tags')

    # Chat check constraint
    op.create_check_constraint("only_one_action_check", "chat", """
        (expecting_sticker_set IS TRUE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (tagging_random_sticker IS TRUE AND expecting_sticker_set IS FALSE AND fix_single_sticker IS FALSE AND full_sticker_set IS FALSE) OR \
        (fix_single_sticker IS TRUE AND tagging_random_sticker IS FALSE AND expecting_sticker_set IS FALSE AND full_sticker_set IS FALSE) OR \
        (full_sticker_set IS TRUE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE AND expecting_sticker_set IS FALSE) OR \
        (full_sticker_set IS FALSE AND tagging_random_sticker IS FALSE AND fix_single_sticker IS FALSE AND expecting_sticker_set IS FALSE)
    """)

    # Sticker set check constraints
    op.create_check_constraint("reviewed_and_complete_check", "sticker_set", "NOT (reviewed AND NOT complete)")

    # Task check constraints
    op.create_check_constraint("check_user_tags_check", "task", "(type = 'check_user_tags' AND is_default_language IS NOT NULL AND \
                                user_id IS NOT NULL) OR type != 'check_user_tags'")
    op.create_check_constraint("vote_ban_check", "task", "(type = 'vote_ban' AND user_id IS NOT NULL) OR type != 'vote_ban'")
    op.create_check_constraint("scan_set_check", "task", "(type = 'scan_set' AND sticker_set_name IS NOT NULL and chat_id IS NOT NULL) OR type != 'vote_ban'") 

def create_check_constraint(
            cls, operations,
            constraint_name, table_name, condition,
            schema=None, **kw):
        """Issue a "create check constraint" instruction using the
        current migration context.

        e.g.::

            from alembic import op
            from sqlalchemy.sql import column, func

            op.create_check_constraint(
                "ck_user_name_len",
                "user",
                func.len(column('name')) > 5
            )

        CHECK constraints are usually against a SQL expression, so ad-hoc
        table metadata is usually needed.   The function will convert the given
        arguments into a :class:`sqlalchemy.schema.CheckConstraint` bound
        to an anonymous table in order to emit the CREATE statement.

        :param name: Name of the check constraint.  The name is necessary
         so that an ALTER statement can be emitted.  For setups that
         use an automated naming scheme such as that described at
         :ref:`sqla:constraint_naming_conventions`,
         ``name`` here can be ``None``, as the event listener will
         apply the name to the constraint object when it is associated
         with the table.
        :param table_name: String name of the source table.
        :param condition: SQL expression that's the condition of the
         constraint. Can be a string or SQLAlchemy expression language
         structure.
        :param deferrable: optional bool. If set, emit DEFERRABLE or
         NOT DEFERRABLE when issuing DDL for this constraint.
        :param initially: optional string. If set, emit INITIALLY <value>
         when issuing DDL for this constraint.
        :param schema: Optional schema name to operate within.  To control
         quoting of the schema outside of the default behavior, use
         the SQLAlchemy construct
         :class:`~sqlalchemy.sql.elements.quoted_name`.

         .. versionadded:: 0.7.0 'schema' can now accept a
            :class:`~sqlalchemy.sql.elements.quoted_name` construct.

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

           * name -> constraint_name
           * source -> table_name

        """
        op = cls(constraint_name, table_name, condition, schema=schema, **kw)
        return operations.invoke(op) 

def create_check_constraint(
            cls, operations,
            constraint_name, table_name, condition,
            schema=None, **kw):
        """Issue a "create check constraint" instruction using the
        current migration context.

        e.g.::

            from alembic import op
            from sqlalchemy.sql import column, func

            op.create_check_constraint(
                "ck_user_name_len",
                "user",
                func.len(column('name')) > 5
            )

        CHECK constraints are usually against a SQL expression, so ad-hoc
        table metadata is usually needed.   The function will convert the given
        arguments into a :class:`sqlalchemy.schema.CheckConstraint` bound
        to an anonymous table in order to emit the CREATE statement.

        :param name: Name of the check constraint.  The name is necessary
         so that an ALTER statement can be emitted.  For setups that
         use an automated naming scheme such as that described at
         :ref:`sqla:constraint_naming_conventions`,
         ``name`` here can be ``None``, as the event listener will
         apply the name to the constraint object when it is associated
         with the table.
        :param table_name: String name of the source table.
        :param condition: SQL expression that's the condition of the
         constraint. Can be a string or SQLAlchemy expression language
         structure.
        :param deferrable: optional bool. If set, emit DEFERRABLE or
         NOT DEFERRABLE when issuing DDL for this constraint.
        :param initially: optional string. If set, emit INITIALLY <value>
         when issuing DDL for this constraint.
        :param schema: Optional schema name to operate within.  To control
         quoting of the schema outside of the default behavior, use
         the SQLAlchemy construct
         :class:`~sqlalchemy.sql.elements.quoted_name`.

         .. versionadded:: 0.7.0 'schema' can now accept a
            :class:`~sqlalchemy.sql.elements.quoted_name` construct.

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

           * name -> constraint_name
           * source -> table_name

        """
        op = cls(constraint_name, table_name, condition, schema=schema, **kw)
        return operations.invoke(op) 

def create_check_constraint(
        cls,
        operations,
        constraint_name,
        table_name,
        condition,
        schema=None,
        **kw
    ):
        """Issue a "create check constraint" instruction using the
        current migration context.

        e.g.::

            from alembic import op
            from sqlalchemy.sql import column, func

            op.create_check_constraint(
                "ck_user_name_len",
                "user",
                func.len(column('name')) > 5
            )

        CHECK constraints are usually against a SQL expression, so ad-hoc
        table metadata is usually needed.   The function will convert the given
        arguments into a :class:`sqlalchemy.schema.CheckConstraint` bound
        to an anonymous table in order to emit the CREATE statement.

        :param name: Name of the check constraint.  The name is necessary
         so that an ALTER statement can be emitted.  For setups that
         use an automated naming scheme such as that described at
         :ref:`sqla:constraint_naming_conventions`,
         ``name`` here can be ``None``, as the event listener will
         apply the name to the constraint object when it is associated
         with the table.
        :param table_name: String name of the source table.
        :param condition: SQL expression that's the condition of the
         constraint. Can be a string or SQLAlchemy expression language
         structure.
        :param deferrable: optional bool. If set, emit DEFERRABLE or
         NOT DEFERRABLE when issuing DDL for this constraint.
        :param initially: optional string. If set, emit INITIALLY <value>
         when issuing DDL for this constraint.
        :param schema: Optional schema name to operate within.  To control
         quoting of the schema outside of the default behavior, use
         the SQLAlchemy construct
         :class:`~sqlalchemy.sql.elements.quoted_name`.

         .. versionadded:: 0.7.0 'schema' can now accept a
            :class:`~sqlalchemy.sql.elements.quoted_name` construct.

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

           * name -> constraint_name
           * source -> table_name

        """
        op = cls(constraint_name, table_name, condition, schema=schema, **kw)
        return operations.invoke(op)