1.. module:: sqlalchemy.orm
2
3.. _mapper_composite:
4
5Composite Column Types
6======================
7
8Sets of columns can be associated with a single user-defined datatype. The ORM
9provides a single attribute which represents the group of columns using the
10class you provide.
11
12A simple example represents pairs of columns as a ``Point`` object.
13``Point`` represents such a pair as ``.x`` and ``.y``::
14
15    class Point(object):
16        def __init__(self, x, y):
17            self.x = x
18            self.y = y
19
20        def __composite_values__(self):
21            return self.x, self.y
22
23        def __repr__(self):
24            return "Point(x=%r, y=%r)" % (self.x, self.y)
25
26        def __eq__(self, other):
27            return isinstance(other, Point) and \
28                other.x == self.x and \
29                other.y == self.y
30
31        def __ne__(self, other):
32            return not self.__eq__(other)
33
34The requirements for the custom datatype class are that it have a constructor
35which accepts positional arguments corresponding to its column format, and
36also provides a method ``__composite_values__()`` which returns the state of
37the object as a list or tuple, in order of its column-based attributes. It
38also should supply adequate ``__eq__()`` and ``__ne__()`` methods which test
39the equality of two instances.
40
41We will create a mapping to a table ``vertices``, which represents two points
42as ``x1/y1`` and ``x2/y2``. These are created normally as :class:`.Column`
43objects. Then, the :func:`.composite` function is used to assign new
44attributes that will represent sets of columns via the ``Point`` class::
45
46    from sqlalchemy import Column, Integer
47    from sqlalchemy.orm import composite
48    from sqlalchemy.ext.declarative import declarative_base
49
50    Base = declarative_base()
51
52    class Vertex(Base):
53        __tablename__ = 'vertices'
54
55        id = Column(Integer, primary_key=True)
56        x1 = Column(Integer)
57        y1 = Column(Integer)
58        x2 = Column(Integer)
59        y2 = Column(Integer)
60
61        start = composite(Point, x1, y1)
62        end = composite(Point, x2, y2)
63
64A classical mapping above would define each :func:`.composite`
65against the existing table::
66
67    mapper(Vertex, vertices_table, properties={
68        'start':composite(Point, vertices_table.c.x1, vertices_table.c.y1),
69        'end':composite(Point, vertices_table.c.x2, vertices_table.c.y2),
70    })
71
72We can now persist and use ``Vertex`` instances, as well as query for them,
73using the ``.start`` and ``.end`` attributes against ad-hoc ``Point`` instances:
74
75.. sourcecode:: python+sql
76
77    >>> v = Vertex(start=Point(3, 4), end=Point(5, 6))
78    >>> session.add(v)
79    >>> q = session.query(Vertex).filter(Vertex.start == Point(3, 4))
80    {sql}>>> print(q.first().start)
81    BEGIN (implicit)
82    INSERT INTO vertices (x1, y1, x2, y2) VALUES (?, ?, ?, ?)
83    (3, 4, 5, 6)
84    SELECT vertices.id AS vertices_id,
85            vertices.x1 AS vertices_x1,
86            vertices.y1 AS vertices_y1,
87            vertices.x2 AS vertices_x2,
88            vertices.y2 AS vertices_y2
89    FROM vertices
90    WHERE vertices.x1 = ? AND vertices.y1 = ?
91     LIMIT ? OFFSET ?
92    (3, 4, 1, 0)
93    {stop}Point(x=3, y=4)
94
95.. autofunction:: composite
96
97
98Tracking In-Place Mutations on Composites
99-----------------------------------------
100
101In-place changes to an existing composite value are
102not tracked automatically.  Instead, the composite class needs to provide
103events to its parent object explicitly.   This task is largely automated
104via the usage of the :class:`.MutableComposite` mixin, which uses events
105to associate each user-defined composite object with all parent associations.
106Please see the example in :ref:`mutable_composites`.
107
108.. _composite_operations:
109
110Redefining Comparison Operations for Composites
111-----------------------------------------------
112
113The "equals" comparison operation by default produces an AND of all
114corresponding columns equated to one another. This can be changed using
115the ``comparator_factory`` argument to :func:`.composite`, where we
116specify a custom :class:`.CompositeProperty.Comparator` class
117to define existing or new operations.
118Below we illustrate the "greater than" operator, implementing
119the same expression that the base "greater than" does::
120
121    from sqlalchemy.orm.properties import CompositeProperty
122    from sqlalchemy import sql
123
124    class PointComparator(CompositeProperty.Comparator):
125        def __gt__(self, other):
126            """redefine the 'greater than' operation"""
127
128            return sql.and_(*[a>b for a, b in
129                              zip(self.__clause_element__().clauses,
130                                  other.__composite_values__())])
131
132    class Vertex(Base):
133        ___tablename__ = 'vertices'
134
135        id = Column(Integer, primary_key=True)
136        x1 = Column(Integer)
137        y1 = Column(Integer)
138        x2 = Column(Integer)
139        y2 = Column(Integer)
140
141        start = composite(Point, x1, y1,
142                            comparator_factory=PointComparator)
143        end = composite(Point, x2, y2,
144                            comparator_factory=PointComparator)
145
146Nesting Composites
147-------------------
148
149Composite objects can be defined to work in simple nested schemes, by
150redefining behaviors within the composite class to work as desired, then
151mapping the composite class to the full length of individual columns normally.
152Typically, it is convenient to define separate constructors for user-defined
153use and generate-from-row use. Below we reorganize the ``Vertex`` class to
154itself be a composite object, which is then mapped to a class ``HasVertex``::
155
156    from sqlalchemy.orm import composite
157
158    class Point(object):
159        def __init__(self, x, y):
160            self.x = x
161            self.y = y
162
163        def __composite_values__(self):
164            return self.x, self.y
165
166        def __repr__(self):
167            return "Point(x=%r, y=%r)" % (self.x, self.y)
168
169        def __eq__(self, other):
170            return isinstance(other, Point) and \
171                other.x == self.x and \
172                other.y == self.y
173
174        def __ne__(self, other):
175            return not self.__eq__(other)
176
177    class Vertex(object):
178        def __init__(self, start, end):
179            self.start = start
180            self.end = end
181
182        @classmethod
183        def _generate(self, x1, y1, x2, y2):
184            """generate a Vertex from a row"""
185            return Vertex(
186                Point(x1, y1),
187                Point(x2, y2)
188            )
189
190        def __composite_values__(self):
191            return \
192                self.start.__composite_values__() + \
193                self.end.__composite_values__()
194
195    class HasVertex(Base):
196        __tablename__ = 'has_vertex'
197        id = Column(Integer, primary_key=True)
198        x1 = Column(Integer)
199        y1 = Column(Integer)
200        x2 = Column(Integer)
201        y2 = Column(Integer)
202
203        vertex = composite(Vertex._generate, x1, y1, x2, y2)
204
205We can then use the above mapping as::
206
207    hv = HasVertex(vertex=Vertex(Point(1, 2), Point(3, 4)))
208
209    s.add(hv)
210    s.commit()
211
212    hv = s.query(HasVertex).filter(
213        HasVertex.vertex == Vertex(Point(1, 2), Point(3, 4))).first()
214    print(hv.vertex.start)
215    print(hv.vertex.end)
216