"""Benchmark for SQLAlchemy. Note that test 7 is omitted because the corresponding Storm test was not completed by Robert Brewer. """ import datetime import os import sys import time from sqlalchemy import * from sqlalchemy.orm import * class ZooMark(object): def setup(self, uri, iterations): global db, metadata, ITERATIONS, session db = create_engine(uri) metadata = MetaData(db) ITERATIONS = iterations session = create_session(transactional=True, autoflush=True, bind=db.connect()) def teardown(self): session.close() metadata.drop_all() db.dispose() def step_1_create_tables(self): global zoo, animal zoo = Table('Zoo', metadata, Column('ID', Integer, Sequence('zoo_id_seq'), primary_key=True, index=True), Column('Name', Unicode(255)), Column('Founded', Date), Column('Opens', Time), Column('LastEscape', DateTime), Column('Admission', Float), ) zoo.create() animal = Table('Animal', metadata, Column('ID', Integer, Sequence('animal_id_seq'), primary_key=True), Column('ZooID', Integer, ForeignKey('Zoo.ID'), index=True), Column('Name', Unicode(100)), Column('Species', Unicode(100)), Column('Legs', Integer, default=4), Column('LastEscape', DateTime), Column('Lifespan', Float(4)), Column('MotherID', Integer, ForeignKey('Animal.ID')), Column('PreferredFoodID', Integer), Column('AlternateFoodID', Integer), ) animal.create() global Zoo, Animal class Zoo(object): def __init__(self, **kwargs): for k, v in kwargs.iteritems(): setattr(self, k, v) class Animal(object): def __init__(self, **kwargs): for k, v in kwargs.iteritems(): setattr(self, k, v) mapper(Zoo, zoo) mapper(Animal, animal) def step_1a_populate(self): wap = Zoo(Name=u'Wild Animal Park', Founded=datetime.date(2000, 1, 1), # 59 can give rounding errors with divmod, which # AdapterFromADO needs to correct. Opens=datetime.time(8, 15, 59), LastEscape=datetime.datetime(2004, 7, 29, 5, 6, 7), Admission=4.95, ) session.save(wap) sdz = Zoo(Name =u'San Diego Zoo', # This early date should play havoc with a number # of implementations. Founded = datetime.date(1835, 9, 13), Opens = datetime.time(9, 0, 0), Admission = 0, ) session.save(sdz) bio = Zoo( Name = u'Montr\xe9al Biod\xf4me', Founded = datetime.date(1992, 6, 19), Opens = datetime.time(9, 0, 0), Admission = 11.75, ) session.save(bio) seaworld = Zoo( Name =u'Sea_World', Admission = 60) session.save(seaworld) # Let's add a crazy futuristic Zoo to test large date values. lp = Zoo(Name =u'Luna Park', Founded = datetime.date(2072, 7, 17), Opens = datetime.time(0, 0, 0), Admission = 134.95, ) session.save(lp) session.flush() # Animals leopard = Animal(Species=u'Leopard', Lifespan=73.5,) session.save(leopard) leopard.ZooID = wap.ID leopard.LastEscape = datetime.datetime(2004, 12, 21, 8, 15, 0, 999907) session.save(Animal(Species=u'Lion', ZooID=wap.ID)) session.save(Animal(Species=u'Slug', Legs=1, Lifespan=.75)) session.save(Animal(Species=u'Tiger', ZooID=sdz.ID)) # Override Legs.default with itself just to make sure it works. session.save(Animal(Species=u'Bear', Legs=4)) session.save(Animal(Species=u'Ostrich', Legs=2, Lifespan=103.2)) session.save(Animal(Species=u'Centipede', Legs=100)) session.save(Animal(Species=u'Emperor Penguin', Legs=2, ZooID=seaworld.ID)) session.save(Animal(Species=u'Adelie Penguin', Legs=2, ZooID=seaworld.ID)) session.save(Animal(Species=u'Millipede', Legs=1000000, ZooID=sdz.ID)) # Add a mother and child to test relationships bai_yun = Animal(Species=u'Ape', Nameu=u'Bai Yun', Legs=2) session.save(bai_yun) session.save(Animal(Species=u'Ape', Name=u'Hua Mei', Legs=2, MotherID=bai_yun.ID)) session.flush() session.commit() def step_2_insert(self): for x in xrange(ITERATIONS): session.save(Animal(Species=u'Tick', Name=u'Tick %d' % x, Legs=8)) session.flush() def step_3_Properties(self): for x in xrange(ITERATIONS): # Zoos WAP = list(session.query(Zoo).filter(Zoo.Name==u'Wild Animal Park')) SDZ = list(session.query(Zoo).filter(Zoo.Founded==datetime.date(1835, 9, 13))) Biodome = list(session.query(Zoo).filter(Zoo.Name==u'Montr\xe9al Biod\xf4me')) seaworld = list(session.query(Zoo).filter(Zoo.Admission == float(60))) # Animals leopard = list(session.query(Animal).filter(Animal.Species == u'Leopard')) ostrich = list(session.query(Animal).filter(Animal.Species==u'Ostrich')) millipede = list(session.query(Animal).filter(Animal.Legs==1000000)) ticks = list(session.query(Animal).filter(Animal.Species==u'Tick')) def step_4_Expressions(self): for x in xrange(ITERATIONS): assert len(list(session.query(Zoo))) == 5 assert len(list(session.query(Animal))) == ITERATIONS + 12 assert len(list(session.query(Animal).filter(Animal.Legs==4))) == 4 assert len(list(session.query(Animal).filter(Animal.Legs == 2))) == 5 assert len(list(session.query(Animal).filter(and_(Animal.Legs >= 2, Animal.Legs < 20)))) == ITERATIONS + 9 assert len(list(session.query(Animal).filter(Animal.Legs > 10))) == 2 assert len(list(session.query(Animal).filter(Animal.Lifespan > 70))) == 2 assert len(list(session.query(Animal).filter(Animal.Species.like(u'L%')))) == 2 assert len(list(session.query(Animal).filter(Animal.Species.like(u'%pede')))) == 2 assert len(list(session.query(Animal).filter(Animal.LastEscape != None))) == 1 assert len(list(session.query(Animal).filter(Animal.LastEscape == None))) == ITERATIONS + 11 # In operator (containedby) assert len(list(session.query(Animal).filter(Animal.Species.like(u'%pede%')))) == 2 assert len(list(session.query(Animal).filter( Animal.Species.in_((u'Lion', u'Tiger', u'Bear'))))) == 3 # Try In with cell references class thing(object): pass pet, pet2 = thing(), thing() pet.Name, pet2.Name = u'Slug', u'Ostrich' assert len(list(session.query(Animal).filter(Animal.Species.in_((pet.Name, pet2.Name))))) == 2 # logic and other functions name =u'Lion' assert len(list(session.query(Animal).filter(func.length(Animal.Species) == len(name)))) == ITERATIONS + 3 assert len(list(session.query(Animal).filter(Animal.Species.like(u'%i%')))) == ITERATIONS + 7 # Test now(), today(), year(), month(), day() assert len(list(session.query(Zoo).filter(and_(Zoo.Founded != None, Zoo.Founded < func.now())))) == 3 assert len(list(session.query(Animal).filter(Animal.LastEscape == func.now()))) == 0 assert len(list(session.query(Animal).filter(func.date_part('year', Animal.LastEscape) == 2004))) == 1 assert len(list(session.query(Animal).filter(func.date_part('month', Animal.LastEscape) == 12))) == 1 assert len(list(session.query(Animal).filter(func.date_part('day', Animal.LastEscape) == 21))) == 1 def step_5_Aggregates(self): def getall(*args, **kwargs): return session.connection().execute(select(*args, **kwargs)).fetchall() for x in xrange(ITERATIONS): # views legs = getall([animal.c.Legs]) legs.sort() expected = {u'Leopard': 73.5, u'Slug': .75, u'Tiger': None, u'Lion': None, u'Bear': None, u'Ostrich': 103.2, u'Centipede': None, u'Emperor Penguin': None, u'Adelie Penguin': None, u'Millipede': None, u'Ape': None, u'Tick': None, } for species, lifespan in getall([animal.c.Species, animal.c.Lifespan]): assert lifespan == expected[species] expected = [u'Montr\xe9al Biod\xf4me', u'Wild Animal Park'] e = getall([Zoo.Name], and_(zoo.c.Founded != None, zoo.c.Founded <= datetime.datetime.now(), zoo.c.Founded >= datetime.date(1990, 1, 1))) values = [val[0] for val in e] assert set(values) == set(expected) # distinct legs = [x[0] for x in getall([animal.c.Legs], distinct=True)] legs.sort() def step_6_Editing(self): for x in xrange(ITERATIONS): # Edit SDZ = session.query(Zoo).filter(Zoo.Name==u'San Diego Zoo').one() SDZ.Name = u'The San Diego Zoo' SDZ.Founded = datetime.date(1900, 1, 1) SDZ.Opens = datetime.time(7, 30, 0) SDZ.Admission = 35.00 # Test edits SDZ = session.query(Zoo).filter(Zoo.Name==u'The San Diego Zoo').one() assert SDZ.Founded == datetime.date(1900, 1, 1), SDZ.Founded # Change it back SDZ.Name = u'San Diego Zoo' SDZ.Founded = datetime.date(1835, 9, 13) SDZ.Opens = datetime.time(9, 0, 0) SDZ.Admission = 0 # Test re-edits SDZ = session.query(Zoo).filter(Zoo.Name==u'San Diego Zoo').one() assert SDZ.Founded == datetime.date(1835, 9, 13), SDZ.Founded