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