Python sqlalchemy.schema.CheckConstraint() Examples

def generic_constraint(self, name, table_name, type_, schema=None, **kw):
        t = self.table(table_name, schema=schema)
        types = {
            'foreignkey': lambda name: sa_schema.ForeignKeyConstraint(
                [], [], name=name),
            'primary': sa_schema.PrimaryKeyConstraint,
            'unique': sa_schema.UniqueConstraint,
            'check': lambda name: sa_schema.CheckConstraint("", name=name),
            None: sa_schema.Constraint
        }
        try:
            const = types[type_]
        except KeyError:
            raise TypeError("'type' can be one of %s" %
                            ", ".join(sorted(repr(x) for x in types)))
        else:
            const = const(name=name)
            t.append_constraint(const)
            return const 

def test_enum_constraint_type_doesnt_double(self):
        m1 = MetaData()

        t1 = Table(
            "x",
            m1,
            Column("flag", Enum("a", "b", "c", create_constraint=True)),
        )
        eq_(
            len([c for c in t1.constraints if isinstance(c, CheckConstraint)]),
            1,
        )
        m2 = MetaData()
        t2 = t1.to_metadata(m2)

        eq_(
            len([c for c in t2.constraints if isinstance(c, CheckConstraint)]),
            1,
        ) 

def test_change_name_change_metadata(self):
        meta = MetaData()
        meta2 = MetaData()

        table = Table(
            "mytable",
            meta,
            Column("myid", Integer, primary_key=True),
            Column("name", String(40), nullable=True),
            Column(
                "description", String(30), CheckConstraint("description='hi'")
            ),
            UniqueConstraint("name"),
            schema="myschema",
        )

        table2 = table.to_metadata(meta2, name="newtable")

        assert table.metadata is not table2.metadata
        eq_((table.name, table2.name), ("mytable", "newtable"))
        eq_((table.key, table2.key), ("myschema.mytable", "myschema.newtable")) 

def test_to_metadata_ok(self):
        m = MetaData()

        t = Table("tbl", m, Column("a", Integer), Column("b", Integer))

        t2 = Table("t2", m, Column("a", Integer), Column("b", Integer))

        UniqueConstraint(t.c.a)
        CheckConstraint(t.c.a > 5)
        ForeignKeyConstraint([t.c.a], [t2.c.a])
        PrimaryKeyConstraint(t.c.a)

        m2 = MetaData()

        t3 = t.to_metadata(m2)

        eq_(len(t3.constraints), 4)

        for c in t3.constraints:
            assert c.table is t3 

def test_auto_append_ck_on_col_attach_three(self):
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        c = Column("c", Integer)
        ck = CheckConstraint(a > b + c)

        t = Table("tbl", m, a)
        assert ck not in t.constraints

        t.append_column(b)
        assert ck not in t.constraints

        t2 = Table("t2", m)
        t2.append_column(c)

        # two different tables, so CheckConstraint does nothing.
        assert ck not in t.constraints 

def test_schematype_ck_name_boolean(self):
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )

        u1 = Table(
            "user",
            m1,
            Column("x", Boolean(name="foo", create_constraint=True)),
        )
        # constraint is not hit
        eq_(
            [c for c in u1.constraints if isinstance(c, CheckConstraint)][
                0
            ].name,
            "foo",
        )
        # but is hit at compile time
        self.assert_compile(
            schema.CreateTable(u1),
            'CREATE TABLE "user" ('
            "x BOOLEAN, "
            "CONSTRAINT ck_user_foo CHECK (x IN (0, 1))"
            ")",
        ) 

def test_schematype_ck_name_boolean_not_on_name(self):
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
        )

        u1 = Table("user", m1, Column("x", Boolean(create_constraint=True)))
        # constraint is not hit
        is_(
            [c for c in u1.constraints if isinstance(c, CheckConstraint)][
                0
            ].name,
            _NONE_NAME,
        )
        # but is hit at compile time
        self.assert_compile(
            schema.CreateTable(u1),
            'CREATE TABLE "user" ('
            "x BOOLEAN, "
            "CONSTRAINT ck_user_x CHECK (x IN (0, 1))"
            ")",
        ) 

def test_schematype_ck_name_enum(self):
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )

        u1 = Table(
            "user",
            m1,
            Column("x", Enum("a", "b", name="foo", create_constraint=True)),
        )
        eq_(
            [c for c in u1.constraints if isinstance(c, CheckConstraint)][
                0
            ].name,
            "foo",
        )
        # but is hit at compile time
        self.assert_compile(
            schema.CreateTable(u1),
            'CREATE TABLE "user" ('
            "x VARCHAR(1), "
            "CONSTRAINT ck_user_foo CHECK (x IN ('a', 'b'))"
            ")",
        ) 

def generic_constraint(self, name, table_name, type_, schema=None, **kw):
        t = self.table(table_name, schema=schema)
        types = {
            "foreignkey": lambda name: sa_schema.ForeignKeyConstraint(
                [], [], name=name
            ),
            "primary": sa_schema.PrimaryKeyConstraint,
            "unique": sa_schema.UniqueConstraint,
            "check": lambda name: sa_schema.CheckConstraint("", name=name),
            None: sa_schema.Constraint,
        }
        try:
            const = types[type_]
        except KeyError:
            raise TypeError(
                "'type' can be one of %s"
                % ", ".join(sorted(repr(x) for x in types))
            )
        else:
            const = const(name=name)
            t.append_constraint(const)
            return const 

def test_schema_boolean(metadata):
    Table(
        "simple_items", metadata, Column("bool1", INTEGER), CheckConstraint("testschema.simple_items.bool1 IN (0, 1)"), schema="testschema"
    )

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

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('bool1', Boolean),
    schema='testschema'
)
"""
    ) 

def test_noindexes_table(metadata):
    simple_items = Table("simple_items", metadata, Column("number", INTEGER), CheckConstraint("number > 2"))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))

    assert (
        generate_code(metadata, noindexes=True)
        == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('number', Integer),
    CheckConstraint('number > 2')
)
"""
    ) 

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

    assert (
        generate_code(metadata)
        == """\
# 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 generic_constraint(self, name, table_name, type_, schema=None, **kw):
        t = self.table(table_name, schema=schema)
        types = {
            'foreignkey': lambda name: sa_schema.ForeignKeyConstraint(
                [], [], name=name),
            'primary': sa_schema.PrimaryKeyConstraint,
            'unique': sa_schema.UniqueConstraint,
            'check': lambda name: sa_schema.CheckConstraint("", name=name),
            None: sa_schema.Constraint
        }
        try:
            const = types[type_]
        except KeyError:
            raise TypeError("'type' can be one of %s" %
                            ", ".join(sorted(repr(x) for x in types)))
        else:
            const = const(name=name)
            t.append_constraint(const)
            return const 

def __table_args__(cls):  # pylint: disable=no-self-argument
        # exception for CERN Oracle identifier length limitations
        # pylint: disable=maybe-no-member
        if cls.__tablename__.upper() == 'UPDATED_ACCOUNT_COUNTERS':
            return cls._table_args + (CheckConstraint('CREATED_AT IS NOT NULL', 'UPDATED_ACCNT_CNTRS_CREATED_NN'),
                                      CheckConstraint('UPDATED_AT IS NOT NULL', 'UPDATED_ACCNT_CNTRS_UPDATED_NN'),
                                      {'mysql_engine': 'InnoDB'})
        # pylint: disable=maybe-no-member
        elif cls.__tablename__.upper() == 'UPDATED_RSE_COUNTERS':
            return cls._table_args + (CheckConstraint('CREATED_AT IS NOT NULL', 'UPDATED_RSE_CNTRS_CREATED_NN'),
                                      CheckConstraint('UPDATED_AT IS NOT NULL', 'UPDATED_RSE_CNTRS_UPDATED_NN'),
                                      {'mysql_engine': 'InnoDB'})
        # pylint: disable=maybe-no-member
        elif cls.__tablename__.upper() == 'DIDS_FOLLOWED_EVENTS':
            return cls._table_args + (CheckConstraint('CREATED_AT IS NOT NULL', 'DIDS_FOLLOWED_EVENTS_CRE_NN'),
                                      CheckConstraint('UPDATED_AT IS NOT NULL', 'DIDS_FOLLOWED_EVENTS_UPD_NN'),
                                      {'mysql_engine': 'InnoDB'})

        # otherwise, proceed normally
        # pylint: disable=maybe-no-member
        return cls._table_args + (CheckConstraint('CREATED_AT IS NOT NULL', name=cls.__tablename__.upper() + '_CREATED_NN'),
                                  CheckConstraint('UPDATED_AT IS NOT NULL', name=cls.__tablename__.upper() + '_UPDATED_NN'),
                                  {'mysql_engine': 'InnoDB'}) 

def _mysql_drop_constraint(element, compiler, **kw):
    """Redefine SQLAlchemy's drop constraint to
    raise errors for invalid constraint type."""

    constraint = element.element
    if isinstance(constraint, (schema.ForeignKeyConstraint,
                               schema.PrimaryKeyConstraint,
                               schema.UniqueConstraint)
                  ):
        return compiler.visit_drop_constraint(element, **kw)
    elif isinstance(constraint, schema.CheckConstraint):
        raise NotImplementedError(
            "MySQL does not support CHECK constraints.")
    else:
        raise NotImplementedError(
            "No generic 'DROP CONSTRAINT' in MySQL - "
            "please specify constraint type") 

def _mysql_drop_constraint(element, compiler, **kw):
    """Redefine SQLAlchemy's drop constraint to
    raise errors for invalid constraint type."""

    constraint = element.element
    if isinstance(constraint, (schema.ForeignKeyConstraint,
                               schema.PrimaryKeyConstraint,
                               schema.UniqueConstraint)
                  ):
        return compiler.visit_drop_constraint(element, **kw)
    elif isinstance(constraint, schema.CheckConstraint):
        # note that SQLAlchemy as of 1.2 does not yet support
        # DROP CONSTRAINT for MySQL/MariaDB, so we implement fully
        # here.
        return "ALTER TABLE %s DROP CONSTRAINT %s" % \
            (compiler.preparer.format_table(constraint.table),
             compiler.preparer.format_constraint(constraint))
    else:
        raise NotImplementedError(
            "No generic 'DROP CONSTRAINT' in MySQL - "
            "please specify constraint type") 

def test_ck_name_required(self):
        u1 = self._fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        ck = CheckConstraint(u1.c.data == "x", name="mycheck")
        eq_(ck.name, "ck_user_mycheck")

        assert_raises_message(
            exc.InvalidRequestError,
            r"Naming convention including %\(constraint_name\)s token "
            "requires that constraint is explicitly named.",
            CheckConstraint,
            u1.c.data == "x",
        ) 

def test_boolean_constraint_type_doesnt_double(self):
        m1 = MetaData()

        t1 = Table("x", m1, Column("flag", Boolean(create_constraint=True)))
        eq_(
            len([c for c in t1.constraints if isinstance(c, CheckConstraint)]),
            1,
        )
        m2 = MetaData()
        t2 = t1.to_metadata(m2)

        eq_(
            len([c for c in t2.constraints if isinstance(c, CheckConstraint)]),
            1,
        ) 

def _columns_for_constraint(constraint):
    if isinstance(constraint, ForeignKeyConstraint):
        return [fk.parent for fk in constraint.elements]
    elif isinstance(constraint, CheckConstraint):
        return _find_columns(constraint.sqltext)
    else:
        return list(constraint.columns) 

def check_constraint(self, name, source, condition, schema=None, **kw):
        t = sa_schema.Table(source, self.metadata(),
                            sa_schema.Column('x', Integer), schema=schema)
        ck = sa_schema.CheckConstraint(condition, name=name, **kw)
        t.append_constraint(ck)
        return ck 

def test_auto_append_lowercase_table(self):
        from sqlalchemy import table, column

        t = table("t", column("a"))
        t2 = table("t2", column("a"))
        for c in (
            UniqueConstraint(t.c.a),
            CheckConstraint(t.c.a > 5),
            ForeignKeyConstraint([t.c.a], [t2.c.a]),
            PrimaryKeyConstraint(t.c.a),
            Index("foo", t.c.a),
        ):
            assert True 

def test_check_constraint_copy(self):
        m = MetaData()
        t = Table("tbl", m, Column("a", Integer), Column("b", Integer))
        ck = CheckConstraint(t.c.a > 5)
        ck2 = ck.copy()
        assert ck in t.constraints
        assert ck2 not in t.constraints 

def test_ambig_check_constraint_auto_append(self):
        m = MetaData()

        t = Table("tbl", m, Column("a", Integer), Column("b", Integer))

        t2 = Table("t2", m, Column("a", Integer), Column("b", Integer))
        c = CheckConstraint(t.c.a > t2.c.b)
        assert c not in t.constraints
        assert c not in t2.constraints 

def test_auto_append_ck_on_col_attach_one(self):
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        ck = CheckConstraint(a > b)

        t = Table("tbl", m, a, b)
        assert ck in t.constraints 

def test_pickle_ck_binary_annotated_col(self, no_pickle_annotated):
        t, q_col = no_pickle_annotated

        ck = CheckConstraint(q_col > 5)
        t.append_constraint(ck)

        m2 = pickle.loads(pickle.dumps(t.metadata))
        const = [
            c
            for c in m2.tables["t"].constraints
            if isinstance(c, CheckConstraint)
        ][0]
        is_true(const.sqltext.compare(ck.sqltext)) 

def test_schematype_ck_name_boolean_no_name(self):
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )

        u1 = Table("user", m1, Column("x", Boolean(create_constraint=True)))
        # constraint gets special _defer_none_name
        is_(
            [c for c in u1.constraints if isinstance(c, CheckConstraint)][
                0
            ].name,
            _NONE_NAME,
        )
        # no issue with native boolean
        self.assert_compile(
            schema.CreateTable(u1),
            'CREATE TABLE "user" (' "x BOOLEAN" ")",
            dialect="postgresql",
        )

        assert_raises_message(
            exc.InvalidRequestError,
            r"Naming convention including \%\(constraint_name\)s token "
            r"requires that constraint is explicitly named.",
            schema.CreateTable(u1).compile,
            dialect=default.DefaultDialect(),
        ) 

def test_ck_name_deferred_required(self):
        u1 = self._fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        ck = CheckConstraint(u1.c.data == "x", name=naming._defer_name(None))

        assert_raises_message(
            exc.InvalidRequestError,
            r"Naming convention including %\(constraint_name\)s token "
            "requires that constraint is explicitly named.",
            schema.AddConstraint(ck).compile,
        ) 

def test_column_attached_ck_name(self):
        m = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        ck = CheckConstraint("x > 5", name="x1")
        Table("t", m, Column("x", ck))
        eq_(ck.name, "ck_t_x1") 

def drop_constraint(self, const):
        if isinstance(const, schema.CheckConstraint) and _is_type_bound(const):
            return

        super(MySQLImpl, self).drop_constraint(const) 

def check_constraint(self, name, source, condition, schema=None, **kw):
        t = sa_schema.Table(source, self.metadata(),
                            sa_schema.Column('x', Integer), schema=schema)
        ck = sa_schema.CheckConstraint(condition, name=name, **kw)
        t.append_constraint(ck)
        return ck