From 82b90175f757ab287d882882355626a84dd8c185 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 22 Jan 2012 20:11:03 -0500 Subject: [PATCH] - [feature] Added new capability to relationship loader options to allow "default" loader strategies. Pass '*' to any of joinedload(), lazyload(), subqueryload(), or noload() and that becomes the loader strategy used for all relationships, except for those explicitly stated in the Query. Thanks to up-and-coming contributor Kent Bower for an exhaustive and well written test suite ! [ticket:2351] --- CHANGES | 26 +- doc/build/orm/loading.rst | 40 +++ lib/sqlalchemy/orm/__init__.py | 7 + lib/sqlalchemy/orm/interfaces.py | 15 +- lib/sqlalchemy/orm/properties.py | 2 + lib/sqlalchemy/orm/strategies.py | 7 + test/orm/test_default_strategies.py | 402 ++++++++++++++++++++++++++++ 7 files changed, 489 insertions(+), 10 deletions(-) create mode 100644 test/orm/test_default_strategies.py diff --git a/CHANGES b/CHANGES index 402ef7b5c7..b09790e64d 100644 --- a/CHANGES +++ b/CHANGES @@ -6,6 +6,24 @@ CHANGES 0.7.5 ===== - orm + - [feature] Added "class_registry" argument to + declarative_base(). Allows two or more declarative + bases to share the same registry of class names. + + - [feature] query.filter() accepts multiple + criteria which will join via AND, i.e. + query.filter(x==y, z>q, ...) + + - [feature] Added new capability to relationship + loader options to allow "default" loader strategies. + Pass '*' to any of joinedload(), lazyload(), + subqueryload(), or noload() and that becomes the + loader strategy used for all relationships, + except for those explicitly stated in the + Query. Thanks to up-and-coming contributor + Kent Bower for an exhaustive and well + written test suite ! [ticket:2351] + - [bug] Fixed bug whereby event.listen(SomeClass) forced an entirely unnecessary compile of the mapper, making events very hard to set up @@ -15,14 +33,6 @@ CHANGES - [bug] Fixed bug whereby hybrid_property didn't work as a kw arg in any(), has(). - - [feature] Added "class_registry" argument to - declarative_base(). Allows two or more declarative - bases to share the same registry of class names. - - - [feature] query.filter() accepts multiple - criteria which will join via AND, i.e. - query.filter(x==y, z>q, ...) - - Fixed regression from 0.6 whereby if "load_on_pending" relationship() flag were used where a non-"get()" lazy clause needed to be diff --git a/doc/build/orm/loading.rst b/doc/build/orm/loading.rst index 9ae60e8a84..f8daa371e8 100644 --- a/doc/build/orm/loading.rst +++ b/doc/build/orm/loading.rst @@ -156,6 +156,44 @@ or more succinctly just use :func:`~sqlalchemy.orm.joinedload_all` or There are two other loader strategies available, **dynamic loading** and **no loading**; these are described in :ref:`largecollections`. +Default Loading Strategies +-------------------------- + +.. note:: + + Default loader strategies are a new feature as of version 0.7.5. + +Each of :func:`.joinedload`, :func:`.subqueryload`, :func:`.lazyload`, +and :func:`.noload` can be used to set the default style of +:func:`.relationship` loading +for a particular query, affecting all :func:`.relationship` -mapped +attributes not otherwise +specified in the :class:`.Query`. This feature is available by passing +the string ``'*'`` as the argument to any of these options:: + + session.query(MyClass).options(lazyload('*')) + +Above, the ``lazyload('*')`` option will supercede the ``lazy`` setting +of all :func:`.relationship` constructs in use for that query, +except for those which use the ``'dynamic'`` style of loading. +If some relationships specify +``lazy='joined'`` or ``lazy='subquery'``, for example, +using ``default_strategy(lazy='select')`` will unilaterally +cause all those relationships to use ``'select'`` loading. + +The option does not supercede loader options stated in the +query, such as :func:`.eagerload`, +:func:`.subqueryload`, etc. The query below will still use joined loading +for the ``widget`` relationship:: + + session.query(MyClass).options( + lazyload('*'), + joinedload(MyClass.widget) + ) + +If multiple ``'*'`` options are passed, the last one overrides +those previously passed. + .. _zen_of_eager_loading: The Zen of Eager Loading @@ -458,6 +496,8 @@ Relation Loader API .. autofunction:: lazyload +.. autofunction:: noload + .. autofunction:: subqueryload .. autofunction:: subqueryload_all diff --git a/lib/sqlalchemy/orm/__init__.py b/lib/sqlalchemy/orm/__init__.py index 58286e12b2..9fd969e3bf 100644 --- a/lib/sqlalchemy/orm/__init__.py +++ b/lib/sqlalchemy/orm/__init__.py @@ -1289,6 +1289,9 @@ def joinedload(*keys, **kw): # to joined-load across both, use joinedload_all() query(Order).options(joinedload_all(Order.items, Item.keywords)) + # set the default strategy to be 'joined' + query(Order).options(joinedload('*')) + :func:`joinedload` also accepts a keyword argument `innerjoin=True` which indicates using an inner join instead of an outer:: @@ -1360,6 +1363,7 @@ def joinedload_all(*keys, **kw): else: return strategies.EagerLazyOption(keys, lazy='joined', chained=True) + def eagerload(*args, **kwargs): """A synonym for :func:`joinedload()`.""" return joinedload(*args, **kwargs) @@ -1388,6 +1392,9 @@ def subqueryload(*keys): # to subquery-load across both, use subqueryload_all() query(Order).options(subqueryload_all(Order.items, Item.keywords)) + # set the default strategy to be 'subquery' + query(Order).options(subqueryload('*')) + See also: :func:`joinedload`, :func:`lazyload` """ diff --git a/lib/sqlalchemy/orm/interfaces.py b/lib/sqlalchemy/orm/interfaces.py index f570c10c29..7f2875e5a1 100644 --- a/lib/sqlalchemy/orm/interfaces.py +++ b/lib/sqlalchemy/orm/interfaces.py @@ -306,16 +306,24 @@ class StrategizedProperty(MapperProperty): """ + strategy_wildcard_key = None + def _get_context_strategy(self, context, reduced_path): key = ('loaderstrategy', reduced_path) + cls = None if key in context.attributes: cls = context.attributes[key] + elif self.strategy_wildcard_key: + key = ('loaderstrategy', (self.strategy_wildcard_key,)) + if key in context.attributes: + cls = context.attributes[key] + + if cls: try: return self._strategies[cls] except KeyError: return self.__init_strategy(cls) - else: - return self.strategy + return self.strategy def _get_strategy(self, cls): try: @@ -494,6 +502,9 @@ class PropertyOption(MapperOption): while tokens: token = tokens.popleft() if isinstance(token, basestring): + # wildcard token + if token.endswith(':*'): + return [(token,)], [] sub_tokens = token.split(".", 1) token = sub_tokens[0] tokens.extendleft(sub_tokens[1:]) diff --git a/lib/sqlalchemy/orm/properties.py b/lib/sqlalchemy/orm/properties.py index cc06311f43..59c4cb3dc1 100644 --- a/lib/sqlalchemy/orm/properties.py +++ b/lib/sqlalchemy/orm/properties.py @@ -185,6 +185,8 @@ class RelationshipProperty(StrategizedProperty): """ + strategy_wildcard_key = 'relationship:*' + def __init__(self, argument, secondary=None, primaryjoin=None, secondaryjoin=None, diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py index 1bdd18cded..5f4b182d08 100644 --- a/lib/sqlalchemy/orm/strategies.py +++ b/lib/sqlalchemy/orm/strategies.py @@ -1319,6 +1319,13 @@ class EagerLazyOption(StrategizedOption): def __init__(self, key, lazy=True, chained=False, propagate_to_loaders=True ): + if isinstance(key[0], basestring) and key[0] == '*': + if len(key) != 1: + raise sa_exc.ArgumentError( + "Wildcard identifier '*' must " + "be specified alone.") + key = ("relationship:*",) + propagate_to_loaders = False super(EagerLazyOption, self).__init__(key) self.lazy = lazy self.chained = self.lazy in (False, 'joined', 'subquery') and chained diff --git a/test/orm/test_default_strategies.py b/test/orm/test_default_strategies.py new file mode 100644 index 0000000000..4e0f7c4790 --- /dev/null +++ b/test/orm/test_default_strategies.py @@ -0,0 +1,402 @@ +from test.orm import _fixtures +from test.lib import testing +from sqlalchemy.orm import mapper, relationship, create_session +from sqlalchemy import util +import sqlalchemy as sa +from test.lib.testing import eq_, assert_raises_message + +class DefaultStrategyOptionsTest(_fixtures.FixtureTest): + + def _assert_fully_loaded(self, users): + # verify everything loaded, with no additional sql needed + def go(): + # comparison with no additional sql + eq_(users, self.static.user_all_result) + # keywords are not part of self.static.user_all_result, so + # verify all the item keywords were loaded, with no more sql. + # 'any' verifies at least some items have keywords; we build + # a list for any([...]) instead of any(...) to prove we've + # iterated all the items with no sql. + f = util.flatten_iterator + assert any([i.keywords for i in + f([o.items for o in f([u.orders for u in users])])]) + self.assert_sql_count(testing.db, go, 0) + + def _assert_addresses_loaded(self, users): + # verify all the addresses were joined loaded with no more sql + def go(): + for u, static in zip(users, self.static.user_all_result): + eq_(u.addresses, static.addresses) + self.assert_sql_count(testing.db, go, 0) + + def _downgrade_fixture(self): + users, Keyword, items, order_items, orders, Item, User, \ + Address, keywords, item_keywords, Order, addresses = \ + self.tables.users, self.classes.Keyword, self.tables.items, \ + self.tables.order_items, self.tables.orders, \ + self.classes.Item, self.classes.User, self.classes.Address, \ + self.tables.keywords, self.tables.item_keywords, \ + self.classes.Order, self.tables.addresses + + mapper(Address, addresses) + + mapper(Keyword, keywords) + + mapper(Item, items, properties=dict( + keywords=relationship(Keyword, secondary=item_keywords, + lazy='subquery', + order_by=item_keywords.c.keyword_id))) + + mapper(Order, orders, properties=dict( + items=relationship(Item, secondary=order_items, lazy='subquery', + order_by=order_items.c.item_id))) + + mapper(User, users, properties=dict( + addresses=relationship(Address, lazy='joined', + order_by=addresses.c.id), + orders=relationship(Order, lazy='joined', + order_by=orders.c.id))) + + return create_session() + + def _upgrade_fixture(self): + users, Keyword, items, order_items, orders, Item, User, \ + Address, keywords, item_keywords, Order, addresses = \ + self.tables.users, self.classes.Keyword, self.tables.items, \ + self.tables.order_items, self.tables.orders, \ + self.classes.Item, self.classes.User, self.classes.Address, \ + self.tables.keywords, self.tables.item_keywords, \ + self.classes.Order, self.tables.addresses + + mapper(Address, addresses) + + mapper(Keyword, keywords) + + mapper(Item, items, properties=dict( + keywords=relationship(Keyword, secondary=item_keywords, + lazy='select', + order_by=item_keywords.c.keyword_id))) + + mapper(Order, orders, properties=dict( + items=relationship(Item, secondary=order_items, lazy=True, + order_by=order_items.c.item_id))) + + mapper(User, users, properties=dict( + addresses=relationship(Address, lazy=True, + order_by=addresses.c.id), + orders=relationship(Order, + order_by=orders.c.id))) + + return create_session() + + def test_downgrade_baseline(self): + """Mapper strategy defaults load as expected + (compare to rest of DefaultStrategyOptionsTest downgrade tests).""" + sess = self._downgrade_fixture() + users = [] + + # test _downgrade_fixture mapper defaults, 3 queries (2 subquery loads). + def go(): + users[:] = sess.query(self.classes.User)\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 3) + + # all loaded with no additional sql + self._assert_fully_loaded(users) + + def test_disable_eagerloads(self): + """Mapper eager load strategy defaults can be shut off + with enable_eagerloads(False).""" + + # While this isn't testing a mapper option, it is included + # as baseline reference for how XYZload('*') option + # should work, namely, it shouldn't affect later queries + # (see other test_select_s) + sess = self._downgrade_fixture() + users = [] + + # demonstrate that enable_eagerloads loads with only 1 sql + def go(): + users[:] = sess.query(self.classes.User)\ + .enable_eagerloads(False)\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 1) + + # demonstrate that users[0].orders must now be loaded with 3 sql + # (need to lazyload, and 2 subquery: 3 total) + def go(): + users[0].orders + self.assert_sql_count(testing.db, go, 3) + + def test_last_one_wins(self): + sess = self._downgrade_fixture() + users = [] + + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.subqueryload('*'))\ + .options(sa.orm.joinedload(self.classes.User.addresses))\ + .options(sa.orm.lazyload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 1) + + # verify all the addresses were joined loaded (no more sql) + self._assert_addresses_loaded(users) + + def test_star_must_be_alone(self): + sess = self._downgrade_fixture() + User = self.classes.User + assert_raises_message( + sa.exc.ArgumentError, + "Wildcard identifier '\*' must be specified alone.", + sa.orm.subqueryload, '*', User.addresses + ) + def test_select_with_joinedload(self): + """Mapper load strategy defaults can be downgraded with + lazyload('*') option, while explicit joinedload() option + is still honored""" + sess = self._downgrade_fixture() + users = [] + + # lazyload('*') shuts off 'orders' subquery: only 1 sql + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.lazyload('*'))\ + .options(sa.orm.joinedload(self.classes.User.addresses))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 1) + + # verify all the addresses were joined loaded (no more sql) + self._assert_addresses_loaded(users) + + # users[0] has orders, which need to lazy load, and 2 subquery: + # (same as with test_disable_eagerloads): 3 total sql + def go(): + users[0].orders + self.assert_sql_count(testing.db, go, 3) + + def test_select_with_subqueryload(self): + """Mapper load strategy defaults can be downgraded with + lazyload('*') option, while explicit subqueryload() option + is still honored""" + sess = self._downgrade_fixture() + users = [] + + # now test 'default_strategy' option combined with 'subquery' + # shuts off 'addresses' load AND orders.items load: 2 sql expected + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.lazyload('*'))\ + .options(sa.orm.subqueryload(self.classes.User.orders))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 2) + + # Verify orders have already been loaded: 0 sql + def go(): + for u, static in zip(users, self.static.user_all_result): + assert len(u.orders) == len(static.orders) + self.assert_sql_count(testing.db, go, 0) + + # Verify lazyload('*') prevented orders.items load + # users[0].orders[0] has 3 items, each with keywords: 2 sql + # ('items' and 'items.keywords' subquery) + def go(): + for i in users[0].orders[0].items: + i.keywords + self.assert_sql_count(testing.db, go, 2) + + # lastly, make sure they actually loaded properly + eq_(users, self.static.user_all_result) + + def test_noload_with_joinedload(self): + """Mapper load strategy defaults can be downgraded with + noload('*') option, while explicit joinedload() option + is still honored""" + sess = self._downgrade_fixture() + users = [] + + # test noload('*') shuts off 'orders' subquery, only 1 sql + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.noload('*'))\ + .options(sa.orm.joinedload(self.classes.User.addresses))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 1) + + # verify all the addresses were joined loaded (no more sql) + self._assert_addresses_loaded(users) + + # User.orders should have loaded "noload" (meaning []) + def go(): + for u in users: + assert u.orders == [] + self.assert_sql_count(testing.db, go, 0) + + def test_noload_with_subqueryload(self): + """Mapper load strategy defaults can be downgraded with + noload('*') option, while explicit subqueryload() option + is still honored""" + sess = self._downgrade_fixture() + users = [] + + # test noload('*') option combined with subqueryload() + # shuts off 'addresses' load AND orders.items load: 2 sql expected + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.noload('*'))\ + .options(sa.orm.subqueryload(self.classes.User.orders))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 2) + + def go(): + # Verify orders have already been loaded: 0 sql + for u, static in zip(users, self.static.user_all_result): + assert len(u.orders) == len(static.orders) + # Verify noload('*') prevented orders.items load + # and set 'items' to [] + for u in users: + for o in u.orders: + assert o.items == [] + self.assert_sql_count(testing.db, go, 0) + + def test_joined(self): + """Mapper load strategy defaults can be upgraded with + joinedload('*') option.""" + sess = self._upgrade_fixture() + users = [] + + # test upgrade all to joined: 1 sql + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.joinedload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 1) + + # verify everything loaded, with no additional sql needed + self._assert_fully_loaded(users) + + def test_joined_with_lazyload(self): + """Mapper load strategy defaults can be upgraded with + joinedload('*') option, while explicit lazyload() option + is still honored""" + sess = self._upgrade_fixture() + users = [] + + # test joined all but 'keywords': upgraded to 1 sql + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.lazyload('orders.items.keywords'))\ + .options(sa.orm.joinedload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 1) + + # everything (but keywords) loaded ok + # (note self.static.user_all_result contains no keywords) + def go(): + eq_(users, self.static.user_all_result) + self.assert_sql_count(testing.db, go, 0) + + # verify the items were loaded, while item.keywords were not + def go(): + # redundant with last test, but illustrative + users[0].orders[0].items[0] + self.assert_sql_count(testing.db, go, 0) + def go(): + users[0].orders[0].items[0].keywords + self.assert_sql_count(testing.db, go, 1) + + def test_joined_with_subqueryload(self): + """Mapper load strategy defaults can be upgraded with + joinedload('*') option, while explicit subqueryload() option + is still honored""" + sess = self._upgrade_fixture() + users = [] + + # test upgrade all but 'addresses', which is subquery loaded (2 sql) + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.subqueryload(self.classes.User.addresses))\ + .options(sa.orm.joinedload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 2) + + # verify everything loaded, with no additional sql needed + self._assert_fully_loaded(users) + + def test_subquery(self): + """Mapper load strategy defaults can be upgraded with + subqueryload('*') option.""" + sess = self._upgrade_fixture() + users = [] + + # test upgrade all to subquery: 1 sql + 4 relationships = 5 + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.subqueryload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 5) + + # verify everything loaded, with no additional sql needed + self._assert_fully_loaded(users) + + def test_subquery_with_lazyload(self): + """Mapper load strategy defaults can be upgraded with + subqueryload('*') option, while explicit lazyload() option + is still honored""" + sess = self._upgrade_fixture() + users = [] + + # test subquery all but 'keywords' (1 sql + 3 relationships = 4) + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.lazyload('orders.items.keywords'))\ + .options(sa.orm.subqueryload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 4) + + # no more sql + # (note self.static.user_all_result contains no keywords) + def go(): + eq_(users, self.static.user_all_result) + self.assert_sql_count(testing.db, go, 0) + + # verify the item.keywords were not loaded + def go(): + users[0].orders[0].items[0] + self.assert_sql_count(testing.db, go, 0) + def go(): + users[0].orders[0].items[0].keywords + self.assert_sql_count(testing.db, go, 1) + + def test_subquery_with_joinedload(self): + """Mapper load strategy defaults can be upgraded with + subqueryload('*') option, while multiple explicit + joinedload() options are still honored""" + sess = self._upgrade_fixture() + users = [] + + # test upgrade all but 'addresses' & 'orders', which are joinedloaded + # (1 sql + items + keywords = 3) + def go(): + users[:] = sess.query(self.classes.User)\ + .options(sa.orm.joinedload(self.classes.User.addresses))\ + .options(sa.orm.joinedload(self.classes.User.orders))\ + .options(sa.orm.subqueryload('*'))\ + .order_by(self.classes.User.id)\ + .all() + self.assert_sql_count(testing.db, go, 3) + + # verify everything loaded, with no additional sql needed + self._assert_fully_loaded(users) -- 2.47.2