Python sqlalchemy.schema.UniqueConstraint() Examples

def test_auto_append_constraint(self):
        m = MetaData()

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

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

        for c in (
            UniqueConstraint(t.c.a),
            CheckConstraint(t.c.a > 5),
            ForeignKeyConstraint([t.c.a], [t2.c.a]),
            PrimaryKeyConstraint(t.c.a),
        ):
            assert c in t.constraints
            t.append_constraint(c)
            assert c in t.constraints

        c = Index("foo", t.c.a)
        assert c in t.indexes 

def test_auto_append_uq_on_col_attach_five(self):
        """Test that a uniqueconstraint that names Column and string names
        *will* autoattach if the table has all those names up front.

        """
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        c = Column("c", Integer)

        t = Table("tbl", m, a, c, b)

        uq = UniqueConstraint(a, "b", "c")

        assert uq in t.constraints

        t.append_constraint(uq)

        assert uq in t.constraints

        eq_(
            [cn for cn in t.constraints if isinstance(cn, UniqueConstraint)],
            [uq],
        ) 

def _fixture(self):
        from sqlalchemy.engine.default import DefaultDialect

        class CopyDialectOptionsTestDialect(DefaultDialect):
            construct_arguments = [
                (Table, {"some_table_arg": None}),
                (Column, {"some_column_arg": None}),
                (Index, {"some_index_arg": None}),
                (PrimaryKeyConstraint, {"some_pk_arg": None}),
                (UniqueConstraint, {"some_uq_arg": None}),
            ]

        def load(dialect_name):
            if dialect_name == "copydialectoptionstest":
                return CopyDialectOptionsTestDialect
            else:
                raise exc.NoSuchModuleError("no dialect %r" % dialect_name)

        with mock.patch("sqlalchemy.dialects.registry.load", load):
            yield 

def check_dialect_options_(cls, t):
        eq_(
            t.dialect_kwargs["copydialectoptionstest_some_table_arg"], "a1",
        )
        eq_(
            t.c.foo.dialect_kwargs["copydialectoptionstest_some_column_arg"],
            "a2",
        )
        eq_(
            t.primary_key.dialect_kwargs["copydialectoptionstest_some_pk_arg"],
            "a3",
        )
        eq_(
            list(t.indexes)[0].dialect_kwargs[
                "copydialectoptionstest_some_index_arg"
            ],
            "a4",
        )
        eq_(
            list(c for c in t.constraints if isinstance(c, UniqueConstraint))[
                0
            ].dialect_kwargs["copydialectoptionstest_some_uq_arg"],
            "a5",
        ) 

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_auto_append_uq_on_col_attach_three(self):
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        c = Column("c", Integer)
        uq = UniqueConstraint(a, b, c)

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

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

        t2 = Table("t2", m)

        # two different tables, so UniqueConstraint raises
        assert_raises_message(
            exc.ArgumentError,
            r"Column\(s\) 't2\.c' are not part of table 'tbl'\.",
            t2.append_column,
            c,
        ) 

def test_unique_true_flag(self):
        meta = MetaData()

        table = Table("mytable", meta, Column("x", Integer, unique=True))

        m2 = MetaData()

        t2 = table.to_metadata(m2)

        eq_(
            len(
                [
                    const
                    for const in t2.constraints
                    if isinstance(const, UniqueConstraint)
                ]
            ),
            1,
        ) 

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_extend_existing_dupes_constraints(self):
        meta2 = self._notexisting_fixture()
        users = Table(
            "users",
            meta2,
            Column("id", Integer),
            Column("name", Unicode),
            UniqueConstraint("name"),
            extend_existing=True,
        )
        assert "name" in users.c
        assert "id" in users.c
        eq_(len(users.constraints), 2)

        u2 = Table(
            "users",
            meta2,
            Column("id", Integer),
            Column("name", Unicode),
            UniqueConstraint("name"),
            extend_existing=True,
        )
        # constraint got duped
        eq_(len(u2.constraints), 3) 

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 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 _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 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 _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_uq_allcols_underscore_name(self):
        u1 = self._fixture(
            naming_convention={"uq": "uq_%(table_name)s_%(column_0_N_name)s"}
        )
        uq = UniqueConstraint(u1.c.data, u1.c.data2, u1.c.data3)
        eq_(uq.name, "uq_user_data_Data2_Data3") 

def test_uq_name_already_conv(self):
        m = MetaData(
            naming_convention={
                "uq": "uq_%(constraint_name)s_%(column_0_name)s"
            }
        )

        t = Table("mytable", m)
        uq = UniqueConstraint(name=naming.conv("my_special_key"))

        t.append_constraint(uq)
        eq_(uq.name, "my_special_key") 

def listens_for(target, identifier, *args, **kw):
    """Decorate a function as a listener for the given target + identifier.

    e.g.::

        from sqlalchemy import event
        from sqlalchemy.schema import UniqueConstraint

        @event.listens_for(UniqueConstraint, "after_parent_attach")
        def unique_constraint_name(const, table):
            const.name = "uq_%s_%s" % (
                table.name,
                list(const.columns)[0].name
            )

    A given function can also be invoked for only the first invocation
    of the event using the ``once`` argument::

        @event.listens_for(Mapper, "before_configure", once=True)
        def on_config():
            do_config()


    .. versionadded:: 0.9.4 Added ``once=True`` to :func:`.event.listen`
       and :func:`.event.listens_for`.

    .. seealso::

        :func:`.listen` - general description of event listening

    """
    def decorate(fn):
        listen(target, identifier, fn, *args, **kw)
        return fn
    return decorate 

def API_OBJECT_NAME(self):
        return self.type_

    # Override the default `deleted_at` column with one that is NOT NULL --
    # Category.deleted_at is needed in a UniqueConstraint.
    # Set the default Category.deleted_at = EPOCH instead. 

def unique_constraint(self, name, source, local_cols, schema=None, **kw):
        t = sa_schema.Table(
            source, self.metadata(),
            *[sa_schema.Column(n, NULLTYPE) for n in local_cols],
            schema=schema)
        kw['name'] = name
        uq = sa_schema.UniqueConstraint(*[t.c[n] for n in local_cols], **kw)
        # TODO: need event tests to ensure the event
        # is fired off here
        t.append_constraint(uq)
        return uq 

def listen(target, identifier, fn, *args, **kw):
    """Register a listener function for the given target.

    e.g.::

        from sqlalchemy import event
        from sqlalchemy.schema import UniqueConstraint

        def unique_constraint_name(const, table):
            const.name = "uq_%s_%s" % (
                table.name,
                list(const.columns)[0].name
            )
        event.listen(
                UniqueConstraint,
                "after_parent_attach",
                unique_constraint_name)


    A given function can also be invoked for only the first invocation
    of the event using the ``once`` argument::

        def on_config():
            do_config()

        event.listen(Mapper, "before_configure", on_config, once=True)

    .. versionadded:: 0.9.3 Added ``once=True`` to :func:`.event.listen`
       and :func:`.event.listens_for`.

    """

    _event_key(target, identifier, fn).listen(*args, **kw) 

def listens_for(target, identifier, *args, **kw):
    """Decorate a function as a listener for the given target + identifier.

    e.g.::

        from sqlalchemy import event
        from sqlalchemy.schema import UniqueConstraint

        @event.listens_for(UniqueConstraint, "after_parent_attach")
        def unique_constraint_name(const, table):
            const.name = "uq_%s_%s" % (
                table.name,
                list(const.columns)[0].name
            )

    A given function can also be invoked for only the first invocation
    of the event using the ``once`` argument::

        @event.listens_for(Mapper, "before_configure", once=True)
        def on_config():
            do_config()


    .. versionadded:: 0.9.4 Added ``once=True`` to :func:`.event.listen`
       and :func:`.event.listens_for`.

    .. seealso::

        :func:`.listen` - general description of event listening

    """
    def decorate(fn):
        listen(target, identifier, fn, *args, **kw)
        return fn
    return decorate 

def test_dialect_options_are_copied(self):
        with self._fixture():
            t1 = Table(
                "t",
                MetaData(),
                Column(
                    "foo",
                    Integer,
                    copydialectoptionstest_some_column_arg="a2",
                ),
                Column("bar", Integer),
                PrimaryKeyConstraint(
                    "foo", copydialectoptionstest_some_pk_arg="a3"
                ),
                UniqueConstraint(
                    "bar", copydialectoptionstest_some_uq_arg="a5"
                ),
                copydialectoptionstest_some_table_arg="a1",
            )
            Index(
                "idx", t1.c.foo, copydialectoptionstest_some_index_arg="a4",
            )

            self.check_dialect_options_(t1)

            m2 = MetaData()
            t2 = t1.to_metadata(m2)  # make a copy
            self.check_dialect_options_(t2) 

def listens_for(target, identifier, *args, **kw):
    """Decorate a function as a listener for the given target + identifier.

    e.g.::

        from sqlalchemy import event
        from sqlalchemy.schema import UniqueConstraint

        @event.listens_for(UniqueConstraint, "after_parent_attach")
        def unique_constraint_name(const, table):
            const.name = "uq_%s_%s" % (
                table.name,
                list(const.columns)[0].name
            )

    A given function can also be invoked for only the first invocation
    of the event using the ``once`` argument::

        @event.listens_for(Mapper, "before_configure", once=True)
        def on_config():
            do_config()


    .. versionadded:: 0.9.3 Added ``once=True`` to :func:`.event.listen`
       and :func:`.event.listens_for`.

    """
    def decorate(fn):
        listen(target, identifier, fn, *args, **kw)
        return fn
    return decorate 

def test_uq_name(self):
        u1 = self._fixture(
            naming_convention={"uq": "uq_%(table_name)s_%(column_0_name)s"}
        )
        uq = UniqueConstraint(u1.c.data)
        eq_(uq.name, "uq_user_data") 

def test_colliding_col_label_from_unique_flag_no_conv(self):
        t1 = self._colliding_name_fixture({"ck": "foo"}, {"unique": True})

        const = [c for c in t1.constraints if isinstance(c, UniqueConstraint)]
        is_(const[0].name, None)

        self.assert_compile(
            AddConstraint(const[0]), "ALTER TABLE foo ADD UNIQUE (id)"
        ) 

def test_colliding_col_label_from_unique_obj(self):
        t1 = self._colliding_name_fixture({"uq": "uq_%(column_0_label)s"}, {})
        uq = UniqueConstraint(t1.c.id)
        const = [c for c in t1.constraints if isinstance(c, UniqueConstraint)]
        is_(const[0], uq)
        eq_(const[0].name, "uq_foo_id")
        self.assert_compile(
            AddConstraint(const[0]),
            "ALTER TABLE foo ADD CONSTRAINT uq_foo_id UNIQUE (id)",
        ) 

def test_participating_unknown_schema_item(self):
        with self._fixture():
            # the dialect doesn't include UniqueConstraint in
            # its registry at all.
            assert_raises_message(
                exc.ArgumentError,
                "Argument 'participating_q_p_x' is not accepted by dialect "
                "'participating' on behalf of "
                "<class 'sqlalchemy.sql.schema.UniqueConstraint'>",
                UniqueConstraint,
                "a",
                "b",
                participating_q_p_x=8,
            ) 

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

        t.append_constraint(UniqueConstraint(q_col))

        m2 = pickle.loads(pickle.dumps(t.metadata))

        const = [
            c
            for c in m2.tables["t"].constraints
            if isinstance(c, UniqueConstraint)
        ][0]

        is_true(const.columns[0].compare(t.c.q)) 

def test_auto_append_uq_on_col_attach_four(self):
        """Test that a uniqueconstraint that names Column and string names
        won't autoattach using deferred column attachment.

        """
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        c = Column("c", Integer)
        uq = UniqueConstraint(a, "b", "c")

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

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

        t.append_column(c)

        # we don't track events for previously unknown columns
        # named 'c' to be attached
        assert uq not in t.constraints

        t.append_constraint(uq)

        assert uq in t.constraints

        eq_(
            [cn for cn in t.constraints if isinstance(cn, UniqueConstraint)],
            [uq],
        ) 

def test_auto_append_uq_on_col_attach_one(self):
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        uq = UniqueConstraint(a, b)

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