1from __future__ import unicode_literals
2
3import datetime
4
5from sqlalchemy import and_
6from sqlalchemy import Column
7from sqlalchemy import Date
8from sqlalchemy import DateTime
9from sqlalchemy import Float
10from sqlalchemy import ForeignKey
11from sqlalchemy import func
12from sqlalchemy import Integer
13from sqlalchemy import join
14from sqlalchemy import outerjoin
15from sqlalchemy import select
16from sqlalchemy import Sequence
17from sqlalchemy import Table
18from sqlalchemy import Time
19from sqlalchemy import Unicode
20from sqlalchemy.testing import replay_fixture
21
22
23"""Benchmark for SQLAlchemy.
24
25An adaptation of Robert Brewers' ZooMark speed tests. """
26
27
28ITERATIONS = 1
29
30
31class ZooMarkTest(replay_fixture.ReplayFixtureTest):
32
33    """Runs the ZooMark and squawks if method counts vary from the norm."""
34
35    __requires__ = ("cpython",)
36    __only_on__ = "postgresql+psycopg2"
37
38    def _run_steps(self, ctx):
39        with ctx():
40            self._baseline_1a_populate()
41        with ctx():
42            self._baseline_2_insert()
43        with ctx():
44            self._baseline_3_properties()
45        with ctx():
46            self._baseline_4_expressions()
47        with ctx():
48            self._baseline_5_aggregates()
49        with ctx():
50            self._baseline_6_editing()
51        with ctx():
52            self._baseline_7_multiview()
53
54    def setup_engine(self):
55        self._baseline_1_create_tables()
56
57    def teardown_engine(self):
58        self._baseline_8_drop()
59
60    def _baseline_1_create_tables(self):
61        Table(
62            "Zoo",
63            self.metadata,
64            Column(
65                "ID",
66                Integer,
67                Sequence("zoo_id_seq"),
68                primary_key=True,
69                index=True,
70            ),
71            Column("Name", Unicode(255)),
72            Column("Founded", Date),
73            Column("Opens", Time),
74            Column("LastEscape", DateTime),
75            Column("Admission", Float),
76        )
77        Table(
78            "Animal",
79            self.metadata,
80            Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True),
81            Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True),
82            Column("Name", Unicode(100)),
83            Column("Species", Unicode(100)),
84            Column("Legs", Integer, default=4),
85            Column("LastEscape", DateTime),
86            Column("Lifespan", Float(4)),
87            Column("MotherID", Integer, ForeignKey("Animal.ID")),
88            Column("PreferredFoodID", Integer),
89            Column("AlternateFoodID", Integer),
90        )
91        self.metadata.create_all()
92
93    def _baseline_1a_populate(self):
94        Zoo = self.metadata.tables["Zoo"]
95        Animal = self.metadata.tables["Animal"]
96        engine = self.metadata.bind
97        wap = engine.execute(
98            Zoo.insert(),
99            Name="Wild Animal Park",
100            Founded=datetime.date(2000, 1, 1),
101            Opens=datetime.time(8, 15, 59),
102            LastEscape=datetime.datetime(2004, 7, 29, 5, 6, 7),
103            Admission=4.95,
104        ).inserted_primary_key[0]
105        sdz = engine.execute(
106            Zoo.insert(),
107            Name="San Diego Zoo",
108            Founded=datetime.date(1935, 9, 13),
109            Opens=datetime.time(9, 0, 0),
110            Admission=0,
111        ).inserted_primary_key[0]
112        engine.execute(
113            Zoo.insert(inline=True),
114            Name="Montr\xe9al Biod\xf4me",
115            Founded=datetime.date(1992, 6, 19),
116            Opens=datetime.time(9, 0, 0),
117            Admission=11.75,
118        )
119        seaworld = engine.execute(
120            Zoo.insert(), Name="Sea_World", Admission=60
121        ).inserted_primary_key[0]
122
123        # Let's add a crazy futuristic Zoo to test large date values.
124
125        engine.execute(
126            Zoo.insert(),
127            Name="Luna Park",
128            Founded=datetime.date(2072, 7, 17),
129            Opens=datetime.time(0, 0, 0),
130            Admission=134.95,
131        ).inserted_primary_key[0]
132
133        # Animals
134
135        leopardid = engine.execute(
136            Animal.insert(), Species="Leopard", Lifespan=73.5
137        ).inserted_primary_key[0]
138        engine.execute(
139            Animal.update(Animal.c.ID == leopardid),
140            ZooID=wap,
141            LastEscape=datetime.datetime(2004, 12, 21, 8, 15, 0, 999907),
142        )
143        engine.execute(
144            Animal.insert(), Species="Lion", ZooID=wap
145        ).inserted_primary_key[0]
146
147        engine.execute(Animal.insert(), Species="Slug", Legs=1, Lifespan=0.75)
148        engine.execute(
149            Animal.insert(), Species="Tiger", ZooID=sdz
150        ).inserted_primary_key[0]
151
152        # Override Legs.default with itself just to make sure it works.
153
154        engine.execute(Animal.insert(inline=True), Species="Bear", Legs=4)
155        engine.execute(
156            Animal.insert(inline=True),
157            Species="Ostrich",
158            Legs=2,
159            Lifespan=103.2,
160        )
161        engine.execute(
162            Animal.insert(inline=True), Species="Centipede", Legs=100
163        )
164        engine.execute(
165            Animal.insert(), Species="Emperor Penguin", Legs=2, ZooID=seaworld
166        ).inserted_primary_key[0]
167        engine.execute(
168            Animal.insert(), Species="Adelie Penguin", Legs=2, ZooID=seaworld
169        ).inserted_primary_key[0]
170        engine.execute(
171            Animal.insert(inline=True),
172            Species="Millipede",
173            Legs=1000000,
174            ZooID=sdz,
175        )
176
177        # Add a mother and child to test relationships
178
179        bai_yun = engine.execute(
180            Animal.insert(), Species="Ape", Name="Bai Yun", Legs=2
181        ).inserted_primary_key[0]
182        engine.execute(
183            Animal.insert(inline=True),
184            Species="Ape",
185            Name="Hua Mei",
186            Legs=2,
187            MotherID=bai_yun,
188        )
189
190    def _baseline_2_insert(self):
191        Animal = self.metadata.tables["Animal"]
192        i = Animal.insert(inline=True)
193        for x in range(ITERATIONS):
194            i.execute(Species="Tick", Name="Tick %d" % x, Legs=8)
195
196    def _baseline_3_properties(self):
197        Zoo = self.metadata.tables["Zoo"]
198        Animal = self.metadata.tables["Animal"]
199        engine = self.metadata.bind
200
201        def fullobject(select):
202            """Iterate over the full result row."""
203
204            return list(engine.execute(select).first())
205
206        for x in range(ITERATIONS):
207
208            # Zoos
209
210            fullobject(Zoo.select(Zoo.c.Name == "Wild Animal Park"))
211            fullobject(Zoo.select(Zoo.c.Founded == datetime.date(1935, 9, 13)))
212            fullobject(Zoo.select(Zoo.c.Name == "Montr\xe9al Biod\xf4me"))
213            fullobject(Zoo.select(Zoo.c.Admission == float(60)))
214
215            # Animals
216
217            fullobject(Animal.select(Animal.c.Species == "Leopard"))
218            fullobject(Animal.select(Animal.c.Species == "Ostrich"))
219            fullobject(Animal.select(Animal.c.Legs == 1000000))
220            fullobject(Animal.select(Animal.c.Species == "Tick"))
221
222    def _baseline_4_expressions(self):
223        Zoo = self.metadata.tables["Zoo"]
224        Animal = self.metadata.tables["Animal"]
225        engine = self.metadata.bind
226
227        def fulltable(select):
228            """Iterate over the full result table."""
229
230            return [list(row) for row in engine.execute(select).fetchall()]
231
232        for x in range(ITERATIONS):
233            assert len(fulltable(Zoo.select())) == 5
234            assert len(fulltable(Animal.select())) == ITERATIONS + 12
235            assert len(fulltable(Animal.select(Animal.c.Legs == 4))) == 4
236            assert len(fulltable(Animal.select(Animal.c.Legs == 2))) == 5
237            assert (
238                len(
239                    fulltable(
240                        Animal.select(
241                            and_(Animal.c.Legs >= 2, Animal.c.Legs < 20)
242                        )
243                    )
244                )
245                == ITERATIONS + 9
246            )
247            assert len(fulltable(Animal.select(Animal.c.Legs > 10))) == 2
248            assert len(fulltable(Animal.select(Animal.c.Lifespan > 70))) == 2
249            assert (
250                len(fulltable(Animal.select(Animal.c.Species.startswith("L"))))
251                == 2
252            )
253            assert (
254                len(
255                    fulltable(Animal.select(Animal.c.Species.endswith("pede")))
256                )
257                == 2
258            )
259            assert (
260                len(fulltable(Animal.select(Animal.c.LastEscape != None))) == 1
261            )  # noqa
262            assert (
263                len(fulltable(Animal.select(None == Animal.c.LastEscape)))
264                == ITERATIONS + 11
265            )  # noqa
266
267            # In operator (containedby)
268
269            assert (
270                len(fulltable(Animal.select(Animal.c.Species.like("%pede%"))))
271                == 2
272            )
273            assert (
274                len(
275                    fulltable(
276                        Animal.select(
277                            Animal.c.Species.in_(["Lion", "Tiger", "Bear"])
278                        )
279                    )
280                )
281                == 3
282            )
283
284            # Try In with cell references
285            class thing(object):
286                pass
287
288            pet, pet2 = thing(), thing()
289            pet.Name, pet2.Name = "Slug", "Ostrich"
290            assert (
291                len(
292                    fulltable(
293                        Animal.select(
294                            Animal.c.Species.in_([pet.Name, pet2.Name])
295                        )
296                    )
297                )
298                == 2
299            )
300
301            # logic and other functions
302
303            assert (
304                len(fulltable(Animal.select(Animal.c.Species.like("Slug"))))
305                == 1
306            )
307            assert (
308                len(fulltable(Animal.select(Animal.c.Species.like("%pede%"))))
309                == 2
310            )
311            name = "Lion"
312            assert (
313                len(
314                    fulltable(
315                        Animal.select(
316                            func.length(Animal.c.Species) == len(name)
317                        )
318                    )
319                )
320                == ITERATIONS + 3
321            )
322            assert (
323                len(fulltable(Animal.select(Animal.c.Species.like("%i%"))))
324                == ITERATIONS + 7
325            )
326
327            # Test now(), today(), year(), month(), day()
328
329            assert (
330                len(
331                    fulltable(
332                        Zoo.select(
333                            and_(
334                                Zoo.c.Founded != None,  # noqa
335                                Zoo.c.Founded
336                                < func.current_timestamp(_type=Date),
337                            )
338                        )
339                    )
340                )
341                == 3
342            )
343            assert (
344                len(
345                    fulltable(
346                        Animal.select(
347                            Animal.c.LastEscape
348                            == func.current_timestamp(_type=Date)
349                        )
350                    )
351                )
352                == 0
353            )
354            assert (
355                len(
356                    fulltable(
357                        Animal.select(
358                            func.date_part("year", Animal.c.LastEscape) == 2004
359                        )
360                    )
361                )
362                == 1
363            )
364            assert (
365                len(
366                    fulltable(
367                        Animal.select(
368                            func.date_part("month", Animal.c.LastEscape) == 12
369                        )
370                    )
371                )
372                == 1
373            )
374            assert (
375                len(
376                    fulltable(
377                        Animal.select(
378                            func.date_part("day", Animal.c.LastEscape) == 21
379                        )
380                    )
381                )
382                == 1
383            )
384
385    def _baseline_5_aggregates(self):
386        Animal = self.metadata.tables["Animal"]
387        Zoo = self.metadata.tables["Zoo"]
388        engine = self.metadata.bind
389
390        for x in range(ITERATIONS):
391
392            # views
393
394            view = engine.execute(select([Animal.c.Legs])).fetchall()
395            legs = sorted([x[0] for x in view])
396            expected = {
397                "Leopard": 73.5,
398                "Slug": 0.75,
399                "Tiger": None,
400                "Lion": None,
401                "Bear": None,
402                "Ostrich": 103.2,
403                "Centipede": None,
404                "Emperor Penguin": None,
405                "Adelie Penguin": None,
406                "Millipede": None,
407                "Ape": None,
408                "Tick": None,
409            }
410            for species, lifespan in engine.execute(
411                select([Animal.c.Species, Animal.c.Lifespan])
412            ).fetchall():
413                assert lifespan == expected[species]
414            expected = ["Montr\xe9al Biod\xf4me", "Wild Animal Park"]
415            e = select(
416                [Zoo.c.Name],
417                and_(
418                    Zoo.c.Founded != None,  # noqa
419                    Zoo.c.Founded <= func.current_timestamp(),
420                    Zoo.c.Founded >= datetime.date(1990, 1, 1),
421                ),
422            )
423            values = [val[0] for val in engine.execute(e).fetchall()]
424            assert set(values) == set(expected)
425
426            # distinct
427
428            legs = [
429                x[0]
430                for x in engine.execute(
431                    select([Animal.c.Legs], distinct=True)
432                ).fetchall()
433            ]
434            legs.sort()
435
436    def _baseline_6_editing(self):
437        Zoo = self.metadata.tables["Zoo"]
438        engine = self.metadata.bind
439        for x in range(ITERATIONS):
440
441            # Edit
442
443            SDZ = engine.execute(
444                Zoo.select(Zoo.c.Name == "San Diego Zoo")
445            ).first()
446            engine.execute(
447                Zoo.update(Zoo.c.ID == SDZ["ID"]),
448                Name="The San Diego Zoo",
449                Founded=datetime.date(1900, 1, 1),
450                Opens=datetime.time(7, 30, 0),
451                Admission="35.00",
452            )
453
454            # Test edits
455
456            SDZ = engine.execute(
457                Zoo.select(Zoo.c.Name == "The San Diego Zoo")
458            ).first()
459            assert SDZ["Founded"] == datetime.date(1900, 1, 1), SDZ["Founded"]
460
461            # Change it back
462
463            engine.execute(
464                Zoo.update(Zoo.c.ID == SDZ["ID"]),
465                Name="San Diego Zoo",
466                Founded=datetime.date(1935, 9, 13),
467                Opens=datetime.time(9, 0, 0),
468                Admission="0",
469            )
470
471            # Test re-edits
472
473            SDZ = engine.execute(
474                Zoo.select(Zoo.c.Name == "San Diego Zoo")
475            ).first()
476            assert SDZ["Founded"] == datetime.date(1935, 9, 13)
477
478    def _baseline_7_multiview(self):
479        Zoo = self.metadata.tables["Zoo"]
480        Animal = self.metadata.tables["Animal"]
481        engine = self.metadata.bind
482
483        def fulltable(select):
484            """Iterate over the full result table."""
485
486            return [list(row) for row in engine.execute(select).fetchall()]
487
488        for x in range(ITERATIONS):
489            fulltable(
490                select(
491                    [Zoo.c.ID] + list(Animal.c),
492                    Zoo.c.Name == "San Diego Zoo",
493                    from_obj=[join(Zoo, Animal)],
494                )
495            )
496            Zoo.select(Zoo.c.Name == "San Diego Zoo")
497            fulltable(
498                select(
499                    [Zoo.c.ID, Animal.c.ID],
500                    and_(
501                        Zoo.c.Name == "San Diego Zoo",
502                        Animal.c.Species == "Leopard",
503                    ),
504                    from_obj=[join(Zoo, Animal)],
505                )
506            )
507
508            # Now try the same query with INNER, LEFT, and RIGHT JOINs.
509
510            fulltable(
511                select(
512                    [Zoo.c.Name, Animal.c.Species],
513                    from_obj=[join(Zoo, Animal)],
514                )
515            )
516            fulltable(
517                select(
518                    [Zoo.c.Name, Animal.c.Species],
519                    from_obj=[outerjoin(Zoo, Animal)],
520                )
521            )
522            fulltable(
523                select(
524                    [Zoo.c.Name, Animal.c.Species],
525                    from_obj=[outerjoin(Animal, Zoo)],
526                )
527            )
528
529    def _baseline_8_drop(self):
530        self.metadata.drop_all()
531