From 7771279ceccdc07d1406373f943768895887682c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 17 Jul 2005 03:10:21 +0000 Subject: [PATCH] --- lib/sqlalchemy/engine.py | 1 - lib/sqlalchemy/mapper.py | 55 ++++++++---- lib/sqlalchemy/sql.py | 8 +- test/mapper.py | 188 ++++++++++++++++++++++++--------------- 4 files changed, 159 insertions(+), 93 deletions(-) diff --git a/lib/sqlalchemy/engine.py b/lib/sqlalchemy/engine.py index 9f50c9c71d..6ea026159e 100644 --- a/lib/sqlalchemy/engine.py +++ b/lib/sqlalchemy/engine.py @@ -148,7 +148,6 @@ class ResultProxy: def fetchone(self): row = self.cursor.fetchone() if row is not None: - print repr(row) return RowProxy(self, row) else: return None diff --git a/lib/sqlalchemy/mapper.py b/lib/sqlalchemy/mapper.py index c5462e78c1..6e2f62d3ab 100644 --- a/lib/sqlalchemy/mapper.py +++ b/lib/sqlalchemy/mapper.py @@ -63,7 +63,8 @@ class Mapper(object): def instances(self, cursor): result = [] cursor = engine.ResultProxy(cursor) - localmap = IdentityMap() + + localmap = {} while True: row = cursor.fetchone() if row is None: @@ -122,26 +123,41 @@ class Mapper(object): return self.identitymap.get_key(row, self.class_, self.table, self.selectable) def _instance(self, row, localmap, result): + """pulls an object instance from the given row and appends it to the given result list. + if the instance already exists in the given identity map, its not added. in either + case, executes all the property loaders on the instance to also process extra information + in the row.""" + + # create the instance if its not in the identity map, + # else retrieve it identitykey = self._identity_key(row) - if not localmap.has_key(identitykey): - instance = self._create(row, identitykey, localmap) - if instance is not None: - result.append(instance) + exists = self.identitymap.has_key(identitykey) + if not exists: + instance = self.class_() + for column in self.selectable.primary_keys: + if row[column.label] is None: + return None + self.identitymap[identitykey] = instance else: - instance = localmap[identitykey] - for key, prop in self.props.iteritems(): - prop.execute(instance, key, row, identitykey, localmap, True) - - def _create(self, row, identitykey, localmap): - instance = self.class_() - for column in self.selectable.primary_keys: - if row[column.label] is None: - return None + instance = self.identitymap[identitykey] + + # call further mapper properties on the row, to pull further + # instances from the row and possibly populate this item. for key, prop in self.props.iteritems(): - prop.execute(instance, key, row, identitykey, localmap, False) - self.identitymap[identitykey] = instance - localmap[identitykey] = instance - return instance + prop.execute(instance, key, row, identitykey, localmap, exists) + + # now add to the result list, but we only want to add + # to the result list uniquely, so get another identity map + # that is associated with that list + try: + imap = localmap[id(result)] + except: + imap = localmap.setdefault(id(result), IdentityMap()) + if not imap.has_key(identitykey): + imap[identitykey] = instance + result.append(instance) + + class MapperProperty: @@ -164,6 +180,7 @@ class EagerLoader(MapperProperty): self.whereclause = whereclause def setup(self, key, primarytable, statement): + """add a left outer join to the statement thats being constructed""" targettable = self.mapper.selectable if hasattr(statement, '_outerjoin'): statement._outerjoin = sql.outerjoin(statement._outerjoin, targettable, self.whereclause) @@ -175,6 +192,8 @@ class EagerLoader(MapperProperty): value.setup(key, self.mapper.selectable, statement) def execute(self, instance, key, row, identitykey, localmap, isduplicate): + """a row. tell our mapper to look for a new object instance in the row, and attach + it to a list on the parent instance.""" try: list = getattr(instance, key) except AttributeError: diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 450d105679..92fd761239 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -299,7 +299,13 @@ class Join(Selectable): return self.left._engine() or self.right._engine() def _get_from_objects(self): - result = [self] + [FromClause(from_key = c.id) for c in self.left._get_from_objects() + self.right._get_from_objects()] + m = {} + for x in self.onclause._get_from_objects(): + m[x.id] = x + result = [self] + [FromClause(from_key = c.id) for c in self.left._get_from_objects() + self.right._get_from_objects()] + for x in result: + m[x.id] = x + result = m.values() return result class Alias(Selectable): diff --git a/test/mapper.py b/test/mapper.py index ff71da8c0b..48b4c5109f 100644 --- a/test/mapper.py +++ b/test/mapper.py @@ -1,8 +1,10 @@ from testbase import PersistTest -import unittest, sys +import unittest, sys, os import sqlalchemy.databases.sqlite as sqllite +if os.access('querytest.db', os.F_OK): + os.remove('querytest.db') db = sqllite.engine('querytest.db', echo = True) from sqlalchemy.sql import * @@ -10,17 +12,96 @@ from sqlalchemy.schema import * import sqlalchemy.mapper as mapper +users = Table('users', db, + Column('user_id', INT, primary_key = True), + Column('user_name', VARCHAR(20)), +) + +addresses = Table('email_addresses', db, + Column('address_id', INT, primary_key = True), + Column('user_id', INT), + Column('email_address', VARCHAR(20)), +) + +orders = Table('orders', db, + Column('order_id', INT, primary_key = True), + Column('user_id', INT), + Column('description', VARCHAR(50)), + Column('isopen', INT) +) + +orderitems = Table('items', db, + Column('item_id', INT, primary_key = True), + Column('order_id', INT), + Column('item_name', VARCHAR(50)) +) + +keywords = Table('keywords', db, + Column('keyword_id', INT, primary_key = True), + Column('name', VARCHAR(50)) +) + +itemkeywords = Table('itemkeywords', db, + Column('item_id', INT), + Column('keyword_id', INT) +) + +users.build() +users.insert().execute(user_id = 7, user_name = 'jack') +users.insert().execute(user_id = 8, user_name = 'ed') +users.insert().execute(user_id = 9, user_name = 'fred') + +addresses.build() +addresses.insert().execute(address_id = 1, user_id = 7, email_address = "jack@bean.com") +addresses.insert().execute(address_id = 2, user_id = 8, email_address = "ed@wood.com") +addresses.insert().execute(address_id = 3, user_id = 8, email_address = "ed@lala.com") + +orders.build() +orders.insert().execute(order_id = 1, user_id = 7, description = 'order 1', isopen=0) +orders.insert().execute(order_id = 2, user_id = 9, description = 'order 2', isopen=0) +orders.insert().execute(order_id = 3, user_id = 7, description = 'order 3', isopen=1) +orders.insert().execute(order_id = 4, user_id = 9, description = 'order 4', isopen=1) +orders.insert().execute(order_id = 5, user_id = 7, description = 'order 5', isopen=0) + +orderitems.build() +orderitems.insert().execute(item_id=1, order_id=2, item_name='item 1') +orderitems.insert().execute(item_id=3, order_id=3, item_name='item 3') +orderitems.insert().execute(item_id=2, order_id=2, item_name='item 2') +orderitems.insert().execute(item_id=5, order_id=3, item_name='item 5') +orderitems.insert().execute(item_id=4, order_id=3, item_name='item 4') + +keywords.build() +keywords.insert().execute(keyword_id=1, name='blue') +keywords.insert().execute(keyword_id=2, name='red') +keywords.insert().execute(keyword_id=3, name='green') +keywords.insert().execute(keyword_id=4, name='big') +keywords.insert().execute(keyword_id=5, name='small') +keywords.insert().execute(keyword_id=6, name='round') +keywords.insert().execute(keyword_id=7, name='square') + +itemkeywords.build() +itemkeywords.insert().execute(keyword_id=2, item_id=1) +itemkeywords.insert().execute(keyword_id=2, item_id=2) +itemkeywords.insert().execute(keyword_id=4, item_id=1) +itemkeywords.insert().execute(keyword_id=6, item_id=1) +itemkeywords.insert().execute(keyword_id=7, item_id=2) +itemkeywords.insert().execute(keyword_id=6, item_id=3) +itemkeywords.insert().execute(keyword_id=3, item_id=3) +itemkeywords.insert().execute(keyword_id=5, item_id=2) +itemkeywords.insert().execute(keyword_id=4, item_id=3) + class User: def __repr__(self): return ( """ User ID: %s +User Name: %s Addresses: %s Orders: %s Open Orders %s Closed Orders %s ------------------ -""" % tuple([self.user_id] + [repr(getattr(self, attr, None)) for attr in ('addresses', 'orders', 'orders_open', 'orders_closed')]) +""" % tuple([self.user_id, repr(self.user_name)] + [repr(getattr(self, attr, None)) for attr in ('addresses', 'orders', 'orders_open', 'orders_closed')]) ) @@ -34,109 +115,70 @@ class Order: class Item: def __repr__(self): - return "Item: " + repr(self.item_name) + return "Item: " + repr(self.item_name) + " " +repr(getattr(self, 'keywords', None)) + +class Keyword: + def __repr__(self): + return "Keyword: " + repr(self.name) class MapperTest(PersistTest): def setUp(self): mapper.clear_identity() - - self.users = Table('users', db, - Column('user_id', INT, primary_key = True), - Column('user_name', VARCHAR(20)), - ) - - self.addresses = Table('email_addresses', db, - Column('address_id', INT, primary_key = True), - Column('user_id', INT), - Column('email_address', VARCHAR(20)), - ) - - self.orders = Table('orders', db, - Column('order_id', INT, primary_key = True), - Column('user_id', INT), - Column('description', VARCHAR(50)), - Column('isopen', INT) - ) - - self.orderitems = Table('items', db, - Column('item_id', INT, primary_key = True), - Column('order_id', INT), - Column('item_name', VARCHAR(50)) - ) - - self.users.build() - self.users.insert().execute(user_id = 7, user_name = 'jack') - self.users.insert().execute(user_id = 8, user_name = 'ed') - self.users.insert().execute(user_id = 9, user_name = 'fred') - - self.addresses.build() - self.addresses.insert().execute(address_id = 1, user_id = 7, email_address = "jack@bean.com") - self.addresses.insert().execute(address_id = 2, user_id = 8, email_address = "ed@wood.com") - self.addresses.insert().execute(address_id = 3, user_id = 8, email_address = "ed@lala.com") - - self.orders.build() - self.orders.insert().execute(order_id = 1, user_id = 7, description = 'order 1', isopen=0) - self.orders.insert().execute(order_id = 2, user_id = 9, description = 'order 2', isopen=0) - self.orders.insert().execute(order_id = 3, user_id = 7, description = 'order 3', isopen=1) - self.orders.insert().execute(order_id = 4, user_id = 9, description = 'order 4', isopen=1) - self.orders.insert().execute(order_id = 5, user_id = 7, description = 'order 5', isopen=0) - - self.orderitems.build() - self.orderitems.insert().execute(item_id=1, order_id=2, item_name='item 1') - self.orderitems.insert().execute(item_id=3, order_id=3, item_name='item 3') - self.orderitems.insert().execute(item_id=2, order_id=2, item_name='item 2') - self.orderitems.insert().execute(item_id=5, order_id=3, item_name='item 5') - self.orderitems.insert().execute(item_id=4, order_id=3, item_name='item 4') + def testmapper(self): - m = mapper.Mapper(User, self.users) + m = mapper.Mapper(User, users) l = m.select() print repr(l) def testeager(self): - m = mapper.Mapper(User, self.users, properties = dict( - addresses = mapper.EagerLoader(mapper.Mapper(Address, self.addresses), self.users.c.user_id==self.addresses.c.user_id) + m = mapper.Mapper(User, users, properties = dict( + addresses = mapper.EagerLoader(mapper.Mapper(Address, addresses), users.c.user_id==addresses.c.user_id) )) l = m.select() print repr(l) def testmultieager(self): - m = mapper.Mapper(User, self.users, properties = dict( - addresses = mapper.EagerLoader(mapper.Mapper(Address, self.addresses), self.users.c.user_id==self.addresses.c.user_id), - orders = mapper.EagerLoader(mapper.Mapper(Order, self.orders), self.users.c.user_id==self.orders.c.user_id), + m = mapper.Mapper(User, users, properties = dict( + addresses = mapper.EagerLoader(mapper.Mapper(Address, addresses), users.c.user_id==addresses.c.user_id), + orders = mapper.EagerLoader(mapper.Mapper(Order, orders), users.c.user_id==orders.c.user_id), ), identitymap = mapper.IdentityMap()) l = m.select() print repr(l) def testdoubleeager(self): - openorders = alias(self.orders, 'openorders') - closedorders = alias(self.orders, 'closedorders') - m = mapper.Mapper(User, self.users, properties = dict( - orders_open = mapper.EagerLoader(mapper.Mapper(Order, openorders), and_(openorders.c.isopen == 1, self.users.c.user_id==openorders.c.user_id)), - orders_closed = mapper.EagerLoader(mapper.Mapper(Order, closedorders), and_(closedorders.c.isopen == 0, self.users.c.user_id==closedorders.c.user_id)) + openorders = alias(orders, 'openorders') + closedorders = alias(orders, 'closedorders') + m = mapper.Mapper(User, users, properties = dict( + orders_open = mapper.EagerLoader(mapper.Mapper(Order, openorders), and_(openorders.c.isopen == 1, users.c.user_id==openorders.c.user_id)), + orders_closed = mapper.EagerLoader(mapper.Mapper(Order, closedorders), and_(closedorders.c.isopen == 0, users.c.user_id==closedorders.c.user_id)) ), identitymap = mapper.IdentityMap()) l = m.select() print repr(l) def testnestedeager(self): - ordermapper = mapper.Mapper(Order, self.orders, properties = dict( - items = mapper.EagerLoader(mapper.Mapper(Item, self.orderitems), self.orders.c.order_id == self.orderitems.c.order_id) + ordermapper = mapper.Mapper(Order, orders, properties = dict( + items = mapper.EagerLoader(mapper.Mapper(Item, orderitems), orders.c.order_id == orderitems.c.order_id) )) - m = mapper.Mapper(User, self.users, properties = dict( - addresses = mapper.EagerLoader(mapper.Mapper(Address, self.addresses), self.users.c.user_id==self.addresses.c.user_id), - orders = mapper.EagerLoader(ordermapper, self.users.c.user_id==self.orders.c.user_id), + m = mapper.Mapper(User, users, properties = dict( + addresses = mapper.EagerLoader(mapper.Mapper(Address, addresses), users.c.user_id==addresses.c.user_id), + orders = mapper.EagerLoader(ordermapper, users.c.user_id==orders.c.user_id), )) l = m.select() print repr(l) + + def testmanytomanyeager(self): + items = orderitems - def tearDown(self): - self.users.drop() - self.addresses.drop() - self.orders.drop() - self.orderitems.drop() - pass + m = mapper.Mapper(Item, items, properties = dict( + keywords = mapper.EagerLoader(mapper.Mapper(Keyword, keywords), + and_(items.c.item_id == itemkeywords.c.item_id, keywords.c.keyword_id == itemkeywords.c.keyword_id)) + )) + l = m.select() + print repr(l) + if __name__ == "__main__": unittest.main() -- 2.47.2