1from sqlalchemy import MetaData, Integer, String, ForeignKey
2from sqlalchemy import util
3from sqlalchemy.testing.schema import Table
4from sqlalchemy.testing.schema import Column
5from sqlalchemy.orm import attributes, mapper, relationship, \
6    backref, configure_mappers
7from sqlalchemy.testing import fixtures
8
9__all__ = ()
10
11
12class FixtureTest(fixtures.MappedTest):
13    """A MappedTest pre-configured with a common set of fixtures.
14
15    """
16
17    run_define_tables = 'once'
18    run_setup_classes = 'once'
19    run_setup_mappers = 'each'
20    run_inserts = 'each'
21    run_deletes = 'each'
22
23    @classmethod
24    def setup_classes(cls):
25        class Base(cls.Comparable):
26            pass
27
28        class User(Base):
29            pass
30
31        class Order(Base):
32            pass
33
34        class Item(Base):
35            pass
36
37        class Keyword(Base):
38            pass
39
40        class Address(Base):
41            pass
42
43        class Dingaling(Base):
44            pass
45
46        class Node(Base):
47            pass
48
49        class CompositePk(Base):
50            pass
51
52    @classmethod
53    def _setup_stock_mapping(cls):
54        Node, composite_pk_table, users, Keyword, items, Dingaling, \
55            order_items, item_keywords, Item, User, dingalings, \
56            Address, keywords, CompositePk, nodes, Order, orders, \
57            addresses = cls.classes.Node, \
58            cls.tables.composite_pk_table, cls.tables.users, \
59            cls.classes.Keyword, cls.tables.items, \
60            cls.classes.Dingaling, cls.tables.order_items, \
61            cls.tables.item_keywords, cls.classes.Item, \
62            cls.classes.User, cls.tables.dingalings, \
63            cls.classes.Address, cls.tables.keywords, \
64            cls.classes.CompositePk, cls.tables.nodes, \
65            cls.classes.Order, cls.tables.orders, cls.tables.addresses
66
67        # use OrderedDict on this one to support some tests that
68        # assert the order of attributes (e.g. orm/test_inspect)
69        mapper(User, users, properties=util.OrderedDict(
70            [('addresses', relationship(Address, backref='user',
71                                        order_by=addresses.c.id)),
72             ('orders', relationship(Order, backref='user',
73                                     order_by=orders.c.id)),  # o2m, m2o
74             ]
75        ))
76        mapper(Address, addresses, properties={
77            # o2o
78            'dingaling': relationship(Dingaling, uselist=False,
79                                      backref="address")
80        })
81        mapper(Dingaling, dingalings)
82        mapper(Order, orders, properties={
83            # m2m
84            'items': relationship(Item, secondary=order_items,
85                                  order_by=items.c.id),
86            'address': relationship(Address),  # m2o
87        })
88        mapper(Item, items, properties={
89            'keywords': relationship(Keyword, secondary=item_keywords)  # m2m
90        })
91        mapper(Keyword, keywords)
92
93        mapper(Node, nodes, properties={
94            'children': relationship(Node,
95                                     backref=backref('parent',
96                                                     remote_side=[nodes.c.id]))
97        })
98
99        mapper(CompositePk, composite_pk_table)
100
101        configure_mappers()
102
103    @classmethod
104    def define_tables(cls, metadata):
105        Table('users', metadata,
106              Column('id', Integer, primary_key=True,
107                     test_needs_autoincrement=True),
108              Column('name', String(30), nullable=False),
109              test_needs_acid=True,
110              test_needs_fk=True)
111
112        Table('addresses', metadata,
113              Column('id', Integer, primary_key=True,
114                     test_needs_autoincrement=True),
115              Column('user_id', None, ForeignKey('users.id')),
116              Column('email_address', String(50), nullable=False),
117              test_needs_acid=True,
118              test_needs_fk=True)
119
120        Table('email_bounces', metadata,
121              Column('id', Integer, ForeignKey('addresses.id')),
122              Column('bounces', Integer))
123
124        Table('orders', metadata,
125              Column('id', Integer, primary_key=True,
126                     test_needs_autoincrement=True),
127              Column('user_id', None, ForeignKey('users.id')),
128              Column('address_id', None, ForeignKey('addresses.id')),
129              Column('description', String(30)),
130              Column('isopen', Integer),
131              test_needs_acid=True,
132              test_needs_fk=True)
133
134        Table("dingalings", metadata,
135              Column('id', Integer, primary_key=True,
136                     test_needs_autoincrement=True),
137              Column('address_id', None, ForeignKey('addresses.id')),
138              Column('data', String(30)),
139              test_needs_acid=True,
140              test_needs_fk=True)
141
142        Table('items', metadata,
143              Column('id', Integer, primary_key=True,
144                     test_needs_autoincrement=True),
145              Column('description', String(30), nullable=False),
146              test_needs_acid=True,
147              test_needs_fk=True)
148
149        Table('order_items', metadata,
150              Column('item_id', None, ForeignKey('items.id')),
151              Column('order_id', None, ForeignKey('orders.id')),
152              test_needs_acid=True,
153              test_needs_fk=True)
154
155        Table('keywords', metadata,
156              Column('id', Integer, primary_key=True,
157                     test_needs_autoincrement=True),
158              Column('name', String(30), nullable=False),
159              test_needs_acid=True,
160              test_needs_fk=True)
161
162        Table('item_keywords', metadata,
163              Column('item_id', None, ForeignKey('items.id')),
164              Column('keyword_id', None, ForeignKey('keywords.id')),
165              test_needs_acid=True,
166              test_needs_fk=True)
167
168        Table('nodes', metadata,
169              Column('id', Integer, primary_key=True,
170                     test_needs_autoincrement=True),
171              Column('parent_id', Integer, ForeignKey('nodes.id')),
172              Column('data', String(30)),
173              test_needs_acid=True,
174              test_needs_fk=True)
175
176        Table('composite_pk_table', metadata,
177              Column('i', Integer, primary_key=True),
178              Column('j', Integer, primary_key=True),
179              Column('k', Integer, nullable=False))
180
181    @classmethod
182    def setup_mappers(cls):
183        pass
184
185    @classmethod
186    def fixtures(cls):
187        return dict(
188            users=(
189                ('id', 'name'),
190                (7, 'jack'),
191                (8, 'ed'),
192                (9, 'fred'),
193                (10, 'chuck')
194            ),
195
196            addresses=(
197                ('id', 'user_id', 'email_address'),
198                (1, 7, "jack@bean.com"),
199                (2, 8, "ed@wood.com"),
200                (3, 8, "ed@bettyboop.com"),
201                (4, 8, "ed@lala.com"),
202                (5, 9, "fred@fred.com")
203            ),
204
205            email_bounces=(
206                ('id', 'bounces'),
207                (1, 1),
208                (2, 0),
209                (3, 5),
210                (4, 0),
211                (5, 0)
212            ),
213
214            orders=(
215                ('id', 'user_id', 'description', 'isopen', 'address_id'),
216                (1, 7, 'order 1', 0, 1),
217                (2, 9, 'order 2', 0, 4),
218                (3, 7, 'order 3', 1, 1),
219                (4, 9, 'order 4', 1, 4),
220                (5, 7, 'order 5', 0, None)
221            ),
222
223            dingalings=(
224                ('id', 'address_id', 'data'),
225                (1, 2, 'ding 1/2'),
226                (2, 5, 'ding 2/5')
227            ),
228
229            items=(
230                ('id', 'description'),
231                (1, 'item 1'),
232                (2, 'item 2'),
233                (3, 'item 3'),
234                (4, 'item 4'),
235                (5, 'item 5')
236            ),
237
238            order_items=(
239                ('item_id', 'order_id'),
240                (1, 1),
241                (2, 1),
242                (3, 1),
243
244                (1, 2),
245                (2, 2),
246                (3, 2),
247
248                (3, 3),
249                (4, 3),
250                (5, 3),
251
252                (1, 4),
253                (5, 4),
254
255                (5, 5)
256            ),
257
258            keywords=(
259                ('id', 'name'),
260                (1, 'blue'),
261                (2, 'red'),
262                (3, 'green'),
263                (4, 'big'),
264                (5, 'small'),
265                (6, 'round'),
266                (7, 'square')
267            ),
268
269            item_keywords=(
270                ('keyword_id', 'item_id'),
271                (2, 1),
272                (2, 2),
273                (4, 1),
274                (6, 1),
275                (5, 2),
276                (3, 3),
277                (4, 3),
278                (7, 2),
279                (6, 3)
280            ),
281
282            nodes=(
283                ('id', 'parent_id', 'data'),
284            ),
285
286            composite_pk_table=(
287                ('i', 'j', 'k'),
288                (1, 2, 3),
289                (2, 1, 4),
290                (1, 1, 5),
291                (2, 2, 6)
292            )
293        )
294
295    @util.memoized_property
296    def static(self):
297        return CannedResults(self)
298
299
300class CannedResults(object):
301    """Built on demand, instances use mappers in effect at time of call."""
302
303    def __init__(self, test):
304        self.test = test
305
306    @property
307    def user_result(self):
308        User = self.test.classes.User
309
310        return [
311            User(id=7),
312            User(id=8),
313            User(id=9),
314            User(id=10)]
315
316    @property
317    def user_address_result(self):
318        User, Address = self.test.classes.User, self.test.classes.Address
319
320        return [
321            User(id=7, addresses=[
322                Address(id=1)
323            ]),
324            User(id=8, addresses=[
325                Address(id=2, email_address='ed@wood.com'),
326                Address(id=3, email_address='ed@bettyboop.com'),
327                Address(id=4, email_address='ed@lala.com'),
328            ]),
329            User(id=9, addresses=[
330                Address(id=5)
331            ]),
332            User(id=10, addresses=[])]
333
334    @property
335    def address_user_result(self):
336        User, Address = self.test.classes.User, self.test.classes.Address
337        u7 = User(id=7)
338        u8 = User(id=8)
339        u9 = User(id=9)
340        return [
341            Address(id=1, email_address='jack@bean.com', user=u7),
342            Address(id=2, email_address='ed@wood.com', user=u8),
343            Address(id=3, email_address='ed@bettyboop.com', user=u8),
344            Address(id=4, email_address='ed@lala.com', user=u8),
345            Address(id=5, user=u9)
346        ]
347
348    @property
349    def user_all_result(self):
350        User, Address, Order, Item = self.test.classes.User, \
351            self.test.classes.Address, self.test.classes.Order, \
352            self.test.classes.Item
353
354        return [
355            User(id=7,
356                 addresses=[Address(id=1)],
357                 orders=[
358                     Order(description='order 1',
359                           items=[
360                               Item(description='item 1'),
361                               Item(description='item 2'),
362                               Item(description='item 3')]),
363                     Order(description='order 3'),
364                     Order(description='order 5')]),
365            User(id=8,
366                 addresses=[Address(id=2), Address(id=3), Address(id=4)]),
367            User(id=9,
368                 addresses=[
369                     Address(id=5)],
370                 orders=[
371                     Order(description='order 2',
372                           items=[
373                               Item(description='item 1'),
374                               Item(description='item 2'),
375                               Item(description='item 3')]),
376                     Order(description='order 4',
377                           items=[
378                               Item(description='item 1'),
379                               Item(description='item 5')])]),
380            User(id=10, addresses=[])]
381
382    @property
383    def user_order_result(self):
384        User, Order, Item = self.test.classes.User, \
385            self.test.classes.Order, self.test.classes.Item
386        return [
387            User(id=7,
388                 orders=[
389                     Order(id=1,
390                           items=[Item(id=1), Item(id=2), Item(id=3)]),
391                     Order(id=3,
392                           items=[Item(id=3), Item(id=4), Item(id=5)]),
393                     Order(id=5,
394                           items=[Item(id=5)])]),
395            User(id=8,
396                 orders=[]),
397            User(id=9,
398                 orders=[
399                     Order(id=2,
400                           items=[Item(id=1), Item(id=2), Item(id=3)]),
401                     Order(id=4,
402                           items=[Item(id=1), Item(id=5)])]),
403            User(id=10)]
404
405    @property
406    def item_keyword_result(self):
407        Item, Keyword = self.test.classes.Item, self.test.classes.Keyword
408        return [
409            Item(id=1,
410                 keywords=[
411                     Keyword(name='red'),
412                     Keyword(name='big'),
413                     Keyword(name='round')]),
414            Item(id=2,
415                 keywords=[
416                     Keyword(name='red'),
417                     Keyword(name='small'),
418                     Keyword(name='square')]),
419            Item(id=3,
420                 keywords=[
421                     Keyword(name='green'),
422                     Keyword(name='big'),
423                     Keyword(name='round')]),
424            Item(id=4,
425                 keywords=[]),
426            Item(id=5,
427                 keywords=[])]
428
429    @property
430    def user_item_keyword_result(self):
431        Item, Keyword = self.test.classes.Item, self.test.classes.Keyword
432        User, Order = self.test.classes.User, self.test.classes.Order
433
434        item1, item2, item3, item4, item5 = \
435            Item(id=1,
436                 keywords=[
437                     Keyword(name='red'),
438                     Keyword(name='big'),
439                     Keyword(name='round')]),\
440            Item(id=2,
441                 keywords=[
442                     Keyword(name='red'),
443                     Keyword(name='small'),
444                     Keyword(name='square')]),\
445            Item(id=3,
446                 keywords=[
447                     Keyword(name='green'),
448                     Keyword(name='big'),
449                     Keyword(name='round')]),\
450            Item(id=4, keywords=[]),\
451            Item(id=5, keywords=[])
452
453        user_result = [
454            User(id=7,
455                 orders=[
456                     Order(id=1,
457                           items=[item1, item2, item3]),
458                     Order(id=3,
459                           items=[item3, item4, item5]),
460                     Order(id=5,
461                           items=[item5])]),
462            User(id=8, orders=[]),
463            User(id=9,
464                 orders=[
465                     Order(id=2,
466                           items=[item1, item2, item3]),
467                     Order(id=4,
468                           items=[item1, item5])]),
469            User(id=10, orders=[])]
470        return user_result
471