"""Benchmark for Geniusql.""" import datetime import os thisdir = os.path.dirname(__file__) import sys import time import geniusql from geniusql import logic, logicfuncs logicfuncs.init() class ZooMark(object): def setup(self, uri, iterations, **opts): global db, schema, ITERATIONS opts = {'connections.Connect': ("host=localhost dbname=test user=postgres " "password=tiger")} db = geniusql.db("psycopg", **opts) schema = db.schema() # the implicit trans here does not seem to have any effect (geniusql r248); # no COMMITs are issued throughout the test. db.connections.implicit_trans = True ITERATIONS = iterations def teardown(self): del schema['Animal'] del schema['Zoo'] db.connections.shutdown() def step_1_create_tables(self): Animal = schema.table('Animal') Animal['ID'] = schema.column(int, autoincrement=True, key=True) Animal['ZooID'] = schema.column(int) Animal.add_index('ZooID') Animal['Name'] = schema.column(hints={'bytes': 100}) Animal['Species'] = schema.column(hints={'bytes': 100}) Animal['Legs'] = schema.column(int, default=4) Animal['LastEscape'] = schema.column(datetime.datetime) Animal['Lifespan'] = schema.column(float, hints={'precision': 4}) Animal['MotherID'] = schema.column(int) Animal['PreferredFoodID'] = schema.column(int) Animal['AlternateFoodID'] = schema.column(int) Animal.references['Animal'] = ('ID', 'Animal', 'MotherID') schema['Animal'] = Animal Zoo = schema.table('Zoo') Zoo['ID'] = schema.column(int, autoincrement=True, key=True) Zoo.add_index('ID') Zoo['Name'] = schema.column(hints={'bytes': 255}) Zoo['Founded'] = schema.column(datetime.date) Zoo['Opens'] = schema.column(datetime.time) Zoo['LastEscape'] = schema.column(datetime.datetime) Zoo['Admission'] = schema.column(float) Zoo.references['Animal'] = ('ID', 'Animal', 'ZooID') schema['Zoo'] = Zoo def step_1a_populate(self): Zoo = schema['Zoo'] Animal = schema['Animal'] wap = Zoo.insert(Name='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, )['ID'] sdz = Zoo.insert(Name = '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, )['ID'] Zoo.insert(Name = u'Montr\xe9al Biod\xf4me', Founded = datetime.date(1992, 6, 19), Opens = datetime.time(9, 0, 0), Admission = 11.75, ) seaworld = Zoo.insert(Name = 'Sea_World', Admission = 60)['ID'] # Let's add a crazy futuristic Zoo to test large date values. lp = Zoo.insert(Name = 'Luna Park', Founded = datetime.date(2072, 7, 17), Opens = datetime.time(0, 0, 0), Admission = 134.95, )['ID'] # Animals leopardid = Animal.insert(Species='Leopard', Lifespan=73.5)['ID'] Animal.save(ID=leopardid, ZooID=wap, LastEscape=datetime.datetime(2004, 12, 21, 8, 15, 0, 999907)) lion = Animal.insert(Species='Lion', ZooID=wap)['ID'] Animal.insert(Species='Slug', Legs=1, Lifespan=.75) tiger = Animal.insert(Species='Tiger', ZooID=sdz)['ID'] # Override Legs.default with itself just to make sure it works. Animal.insert(Species='Bear', Legs=4) Animal.insert(Species='Ostrich', Legs=2, Lifespan=103.2) Animal.insert(Species='Centipede', Legs=100) emp = Animal.insert(Species='Emperor Penguin', Legs=2, ZooID=seaworld)['ID'] adelie = Animal.insert(Species='Adelie Penguin', Legs=2, ZooID=seaworld)['ID'] Animal.insert(Species='Millipede', Legs=1000000, ZooID=sdz) # Add a mother and child to test relationships bai_yun = Animal.insert(Species='Ape', Name='Bai Yun', Legs=2) Animal.insert(Species='Ape', Name='Hua Mei', Legs=2, MotherID=bai_yun['ID']) def step_2_insert(self): Animal = schema['Animal'] for x in xrange(ITERATIONS): tick = Animal.insert(Species='Tick', Name='Tick %d' % x, Legs=8) def step_3_Properties(self): Zoo = schema['Zoo'] Animal = schema['Animal'] for x in xrange(ITERATIONS): # Zoos WAP = Zoo.select(lambda z: z.Name == 'Wild Animal Park') SDZ = Zoo.select(lambda z: z.Founded == datetime.date(1835, 9, 13)) Biodome = Zoo.select(lambda z: z.Name == u'Montr\xe9al Biod\xf4me') seaworld = Zoo.select(lambda z: z.Admission == float(60)) # Animals leopard = Animal.select(lambda a: a.Species == 'Leopard') ostrich = Animal.select(lambda a: a.Species == 'Ostrich') millipede = Animal.select(lambda a: a.Legs == 1000000) ticks = Animal.select(lambda a: a.Species == 'Tick') def step_4_Expressions(self): Zoo = schema['Zoo'] Animal = schema['Animal'] for x in xrange(ITERATIONS): allzoos = Zoo.select_all() assert len(allzoos) == 5, allzoos assert len(Animal.select_all(lambda x: True)) == ITERATIONS + 12 assert len(Animal.select_all(lambda x: x.Legs == 4)) == 4 assert len(Animal.select_all(lambda x: x.Legs == 2)) == 5 assert len(Animal.select_all(lambda x: x.Legs >= 2 and x.Legs < 20)) == ITERATIONS + 9 assert len(Animal.select_all(lambda x: x.Legs > 10)) == 2 assert len(Animal.select_all(lambda x: x.Lifespan > 70)) == 2 assert len(Animal.select_all(lambda x: x.Species.startswith('L'))) == 2 assert len(Animal.select_all(lambda x: x.Species.endswith('pede'))) == 2 assert len(Animal.select_all(lambda x: x.LastEscape != None)) == 1 assert len(Animal.select_all(lambda x: None == x.LastEscape)) == ITERATIONS + 11 # In operator (containedby) # ASSERTION BROKEN: assert len(Animal.select_all(lambda x: 'pede' in x.Species)) == 2 len(Animal.select_all(lambda x: 'pede' in x.Species)) assert len(Animal.select_all(lambda x: x.Species in ('Lion', 'Tiger', 'Bear'))) == 3 # Try In with cell references class thing(object): pass pet, pet2 = thing(), thing() pet.Name, pet2.Name = 'Slug', 'Ostrich' # ASSERTION BROKEN: assert len(Animal.select_all(lambda x: x.Species in (pet.Name, pet2.Name))) == 2 len(Animal.select_all(lambda x: x.Species in (pet.Name, pet2.Name))) == 2 # logic and other functions assert len(Animal.select_all(lambda x: ieq(x.Species, 'slug'))) == 1 assert len(Animal.select_all(lambda x: icontains(x.Species, 'PEDE'))) == 2 name = 'Lion' assert len(Animal.select_all(lambda x: len(x.Species) == len(name))) == ITERATIONS + 3 # This broke sometime in 2004. Rev 32 seems to have fixed it. # ASSERTION BROKEN assert len(Animal.select_all(lambda x: 'i' in x.Species)) == ITERATIONS + 7 len(Animal.select_all(lambda x: 'i' in x.Species)) # Test now(), today(), year(), month(), day() assert len(Zoo.select_all(lambda x: x.Founded != None and x.Founded < today())) == 3 assert len(Animal.select_all(lambda x: x.LastEscape == now())) == 0 assert len(Animal.select_all(lambda x: year(x.LastEscape) == 2004)) == 1 assert len(Animal.select_all(lambda x: month(x.LastEscape) == 12)) == 1 assert len(Animal.select_all(lambda x: day(x.LastEscape) == 21)) == 1 def step_5_Aggregates(self): Animal = schema['Animal'] Zoo = schema['Zoo'] for x in xrange(ITERATIONS): # views view = db.select((Animal, ['Legs'])) legs = [x[0] for x in view] legs.sort() expected = {'Leopard': 73.5, 'Slug': .75, 'Tiger': None, 'Lion': None, 'Bear': None, 'Ostrich': 103.2, 'Centipede': None, 'Emperor Penguin': None, 'Adelie Penguin': None, 'Millipede': None, 'Ape': None, 'Tick': None, } for species, lifespan in db.select((Animal, ['Species', 'Lifespan'])): assert lifespan == expected[species] expected = [u'Montr\xe9al Biod\xf4me', 'Wild Animal Park'] # changed "today()" to be in-python function like the Storm test e = (lambda x: x.Founded != None and x.Founded <= datetime.date.today() and x.Founded >= datetime.date(1990, 1, 1)) values = [val[0] for val in db.select((Zoo, ['Name'], e))] assert set(values) == set(expected) # distinct legs = [x[0] for x in db.select((Animal, ['Legs']), distinct=True)] legs.sort() def step_6_Editing(self): Zoo = schema['Zoo'] for x in xrange(ITERATIONS): # Edit SDZ = Zoo.select(Name='San Diego Zoo') Zoo.save(ID=SDZ['ID'], Name='The San Diego Zoo', Founded = datetime.date(1900, 1, 1), Opens = datetime.time(7, 30, 0), Admission = "35.00") # Test edits SDZ = Zoo.select(Name='The San Diego Zoo') assert SDZ['Founded'] == datetime.date(1900, 1, 1) # Change it back Zoo.save(ID=SDZ['ID'], Name = 'San Diego Zoo', Founded = datetime.date(1835, 9, 13), Opens = datetime.time(9, 0, 0), Admission = "0") # Test re-edits SDZ = Zoo.select(Name='San Diego Zoo') assert SDZ['Founded'] == datetime.date(1835, 9, 13) def step_7_Multiview(self): Zoo = schema['Zoo'] Animal = schema['Animal'] for x in xrange(ITERATIONS): f = (lambda z, a: z.Name == 'San Diego Zoo') zooed_animals = list(db.select((Zoo & Animal, [('ID',), Animal.keys()], f))) SDZ = Zoo.select(Name='San Diego Zoo') sdexpr = logic.filter(Name='San Diego Zoo') leo = lambda z, a: a.Species == 'Leopard' zooed_animals = list(db.select((Zoo & Animal, [('ID',), ('ID', )], sdexpr + leo))) # Now try the same query with INNER, LEFT, and RIGHT JOINs. zooed_animals = list(db.select((Zoo & Animal, [('Name', ), ('Species', )]))) zooed_animals = list(db.select((Zoo >> Animal, [('Name', ), ('Species', )]))) zooed_animals = list(db.select((Zoo << Animal, [('Name', ), ('Species', )])))