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