From e2c4d1cc58163319088d5bcf1c1661ae5d959947 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 8 Dec 2005 03:02:33 +0000 Subject: [PATCH] more grueling unit tests involving limit, offset and distinct (and unions) --- test/mapper.py | 161 ++++++++++++++++++++++++++++++++++--------------- test/tables.py | 3 +- 2 files changed, 116 insertions(+), 48 deletions(-) diff --git a/test/mapper.py b/test/mapper.py index 57ede7dbda..a63070ee45 100644 --- a/test/mapper.py +++ b/test/mapper.py @@ -7,6 +7,56 @@ from sqlalchemy import * from tables import * import tables +user_result = [{'user_id' : 7}, {'user_id' : 8}, {'user_id' : 9}] +user_address_result = [ +{'user_id' : 7, 'addresses' : (Address, [{'address_id' : 1}])}, +{'user_id' : 8, 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3}, {'address_id' : 4}])}, +{'user_id' : 9, 'addresses' : (Address, [])} +] +user_address_orders_result = [{'user_id' : 7, + 'addresses' : (Address, [{'address_id' : 1}]), + 'orders' : (Order, [{'order_id' : 1}, {'order_id' : 3},{'order_id' : 5},]) +}, + +{'user_id' : 8, + 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3}, {'address_id' : 4}]), + 'orders' : (Order, []) +}, +{'user_id' : 9, + 'addresses' : (Address, []), + 'orders' : (Order, [{'order_id' : 2},{'order_id' : 4}]) +}] + +user_all_result = [ +{'user_id' : 7, + 'addresses' : (Address, [{'address_id' : 1}]), + 'orders' : (Order, [ + {'order_id' : 1, 'items': (Item, [])}, + {'order_id' : 3, 'items': (Item, [{'item_id':3, 'item_name':'item 3'}, {'item_id':4, 'item_name':'item 4'}, {'item_id':5, 'item_name':'item 5'}])}, + {'order_id' : 5, 'items': (Item, [])}, + ]) +}, +{'user_id' : 8, + 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3}, {'address_id' : 4}]), + 'orders' : (Order, []) +}, +{'user_id' : 9, + 'addresses' : (Address, []), + 'orders' : (Order, [ + {'order_id' : 2, 'items': (Item, [{'item_id':1, 'item_name':'item 1'}, {'item_id':2, 'item_name':'item 2'}])}, + {'order_id' : 4, 'items': (Item, [])} + ]) +}] + +item_keyword_result = [ +{'item_id' : 1, 'keywords' : (Keyword, [{'keyword_id' : 2}, {'keyword_id' : 4}, {'keyword_id' : 6}])}, +{'item_id' : 2, 'keywords' : (Keyword, [{'keyword_id' : 2, 'name':'red'}, {'keyword_id' : 5, 'name':'small'}, {'keyword_id' : 7, 'name':'square'}])}, +{'item_id' : 3, 'keywords' : (Keyword, [{'keyword_id' : 3,'name':'green'}, {'keyword_id' : 4,'name':'big'}, {'keyword_id' : 6,'name':'round'}])}, +{'item_id' : 4, 'keywords' : (Keyword, [])}, +{'item_id' : 5, 'keywords' : (Keyword, [])} +] + + class MapperSuperTest(AssertMixin): def setUpAll(self): db.echo = False @@ -37,15 +87,15 @@ class MapperTest(MapperSuperTest): """tests loading rows with a mapper and producing object instances""" m = mapper(User, users) l = m.select() - self.assert_result(l, User, {'user_id' : 7}, {'user_id' : 8}, {'user_id' : 9}) + self.assert_result(l, User, *user_result) l = m.select(users.c.user_name.endswith('ed')) - self.assert_result(l, User, {'user_id' : 8}, {'user_id' : 9}) + self.assert_result(l, User, *user_result[1:3]) def testmultitable(self): usersaddresses = sql.join(users, addresses, users.c.user_id == addresses.c.user_id) m = mapper(User, usersaddresses, primarytable = users, primary_key=[users.c.user_id]) l = m.select() - self.assert_result(l, User, {'user_id' : 7}, {'user_id' : 8}) + self.assert_result(l, User, *user_result[0:2]) def testeageroptions(self): """tests that a lazy relation can be upgraded to an eager relation via the options method""" @@ -55,11 +105,7 @@ class MapperTest(MapperSuperTest): # l = m.select() l = m.options(eagerload('addresses')).select() - self.assert_result(l, User, - {'user_id' : 7, 'addresses' : (Address, [{'address_id' : 1}])}, - {'user_id' : 8, 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3}, {'address_id' : 4}])}, - {'user_id' : 9, 'addresses' : (Address, [])} - ) + self.assert_result(l, User, *user_address_result) def testlazyoptions(self): """tests that an eager relation can be upgraded to a lazy relation via the options method""" @@ -67,11 +113,7 @@ class MapperTest(MapperSuperTest): addresses = relation(Address, addresses, lazy = False) )) l = m.options(lazyload('addresses')).select() - self.assert_result(l, User, - {'user_id' : 7, 'addresses' : (Address, [{'address_id' : 1}])}, - {'user_id' : 8, 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3}, {'address_id' : 4}])}, - {'user_id' : 9, 'addresses' : (Address, [])} - ) + self.assert_result(l, User, *user_address_result) class PropertyTest(MapperSuperTest): def testbasic(self): @@ -148,6 +190,31 @@ class LazyTest(MapperSuperTest): {'user_id' : 9, 'addresses' : (Address, [])}, ) + def testlimit(self): + ordermapper = mapper(Order, orders, properties = dict( + items = relation(Item, orderitems, lazy = True) + )) + + m = mapper(User, users, properties = dict( + addresses = relation(Address, addresses, lazy = True), + orders = relation(ordermapper, primaryjoin = users.c.user_id==orders.c.user_id, lazy = True), + )) + l = m.select(limit=2, offset=1) + self.assert_result(l, User, *user_all_result[1:3]) + + # use a union all to get a lot of rows to join against + u2 = users.alias('u2') + s = union_all(u2.select(use_labels=True), u2.select(use_labels=True), u2.select(use_labels=True)).alias('u') + l = m.select(s.c.u2_user_id==User.c.user_id, distinct=True) + self.assert_result(l, User, *user_all_result) + + objectstore.clear() + m = mapper(Item, orderitems, properties = dict( + keywords = relation(Keyword, keywords, itemkeywords, lazy = True), + )) + l = m.select((Item.c.item_name=='item 2') | (Item.c.item_name=='item 5') | (Item.c.item_name=='item 3'), order_by=[Item.c.item_id], limit=2) + self.assert_result(l, Item, *[item_keyword_result[1], item_keyword_result[2]]) + def testonetoone(self): m = mapper(User, users, properties = dict( address = relation(Address, addresses, lazy = True, uselist = False) @@ -226,12 +293,8 @@ class EagerTest(MapperSuperTest): addresses = relation(m, lazy = False), )) l = m.select() - self.assert_result(l, User, - {'user_id' : 7, 'addresses' : (Address, [{'address_id' : 1}])}, - {'user_id' : 8, 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3},{'address_id' : 4}])}, - {'user_id' : 9, 'addresses' : (Address, [])} - ) - + self.assert_result(l, User, *user_address_result) + def testorderby(self): m = mapper(Address, addresses) @@ -258,6 +321,35 @@ class EagerTest(MapperSuperTest): {'user_id' : 8, 'addresses' : (Address, [{'email_address':'ed@wood.com'},{'email_address':'ed@lala.com'}, {'email_address':'ed@bettyboop.com'}, ])}, {'user_id' : 9, 'addresses' : (Address, [])}, ) + + def testlimit(self): + ordermapper = mapper(Order, orders, properties = dict( + items = relation(Item, orderitems, lazy = False) + )) + + m = mapper(User, users, properties = dict( + addresses = relation(Address, addresses, lazy = False), + orders = relation(ordermapper, primaryjoin = users.c.user_id==orders.c.user_id, lazy = False), + )) + l = m.select(limit=2, offset=1) + self.assert_result(l, User, *user_all_result[1:3]) + + # this is an involved 3x union of the users table to get a lot of rows. + # then see if the "distinct" works its way out. you actually get the same + # result with or without the distinct, just via less or more rows. + u2 = users.alias('u2') + s = union_all(u2.select(use_labels=True), u2.select(use_labels=True), u2.select(use_labels=True)).alias('u') + l = m.select(s.c.u2_user_id==User.c.user_id, distinct=True) + self.assert_result(l, User, *user_all_result) + + objectstore.clear() + m = mapper(Item, orderitems, properties = dict( + keywords = relation(Keyword, keywords, itemkeywords, lazy = False), + )) + l = m.select((Item.c.item_name=='item 2') | (Item.c.item_name=='item 5') | (Item.c.item_name=='item 3'), order_by=[Item.c.item_id], limit=2) + self.assert_result(l, Item, *[item_keyword_result[1], item_keyword_result[2]]) + + def testonetoone(self): m = mapper(User, users, properties = dict( @@ -367,27 +459,7 @@ class EagerTest(MapperSuperTest): orders = relation(ordermapper, primaryjoin = users.c.user_id==orders.c.user_id, lazy = False), )) l = m.select() - self.assert_result(l, User, - {'user_id' : 7, - 'addresses' : (Address, [{'address_id' : 1}]), - 'orders' : (Order, [ - {'order_id' : 1, 'items': (Item, [])}, - {'order_id' : 3, 'items': (Item, [{'item_id':3, 'item_name':'item 3'}, {'item_id':4, 'item_name':'item 4'}, {'item_id':5, 'item_name':'item 5'}])}, - {'order_id' : 5, 'items': (Item, [])}, - ]) - }, - {'user_id' : 8, - 'addresses' : (Address, [{'address_id' : 2}, {'address_id' : 3}, {'address_id' : 4}]), - 'orders' : (Order, []) - }, - {'user_id' : 9, - 'addresses' : (Address, []), - 'orders' : (Order, [ - {'order_id' : 2, 'items': (Item, [{'item_id':1, 'item_name':'item 1'}, {'item_id':2, 'item_name':'item 2'}])}, - {'order_id' : 4, 'items': (Item, [])} - ]) - } - ) + self.assert_result(l, User, *user_all_result) def testmanytomany(self): items = orderitems @@ -396,14 +468,9 @@ class EagerTest(MapperSuperTest): keywords = relation(Keyword, keywords, itemkeywords, lazy = False), )) l = m.select() - self.assert_result(l, Item, - {'item_id' : 1, 'keywords' : (Keyword, [{'keyword_id' : 2}, {'keyword_id' : 4}, {'keyword_id' : 6}])}, - {'item_id' : 2, 'keywords' : (Keyword, [{'keyword_id' : 2, 'name':'red'}, {'keyword_id' : 5, 'name':'small'}, {'keyword_id' : 7, 'name':'square'}])}, - {'item_id' : 3, 'keywords' : (Keyword, [{'keyword_id' : 3,'name':'green'}, {'keyword_id' : 4,'name':'big'}, {'keyword_id' : 6,'name':'round'}])}, - {'item_id' : 4, 'keywords' : (Keyword, [])}, - {'item_id' : 5, 'keywords' : (Keyword, [])} - ) + self.assert_result(l, Item, *item_keyword_result) +# l = m.select() l = m.select(and_(keywords.c.name == 'red', keywords.c.keyword_id == itemkeywords.c.keyword_id, items.c.item_id==itemkeywords.c.item_id)) self.assert_result(l, Item, {'item_id' : 1, 'keywords' : (Keyword, [{'keyword_id' : 2}, {'keyword_id' : 4}, {'keyword_id' : 6}])}, diff --git a/test/tables.py b/test/tables.py index 8bb0587e57..d0d0692488 100644 --- a/test/tables.py +++ b/test/tables.py @@ -160,7 +160,8 @@ class Order(object): class Item(object): def __repr__(self): - return "Item: " + repr(self.item_name) + " " +repr(getattr(self, 'keywords', None)) +# return repr(self.__dict__) + return "Item: " + repr(self.item_name) + " " + repr(getattr(self, 'keywords', None)) class Keyword(object): def __repr__(self): -- 2.47.2