diff options
| author | Mike Bayer <mike_mp@zzzcomputing.com> | 2019-12-01 17:24:27 -0500 |
|---|---|---|
| committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2020-05-24 11:54:08 -0400 |
| commit | dce8c7a125cb99fad62c76cd145752d5afefae36 (patch) | |
| tree | 352dfa2c38005207ca64f45170bbba2c0f8c927e /test/orm | |
| parent | 1502b5b3e4e4b93021eb927a6623f288ef006ba6 (diff) | |
| download | sqlalchemy-dce8c7a125cb99fad62c76cd145752d5afefae36.tar.gz | |
Unify Query and select() , move all processing to compile phase
Convert Query to do virtually all compile state computation
in the _compile_context() phase, and organize it all
such that a plain select() construct may also be used as the
source of information in order to generate ORM query state.
This makes it such that Query is not needed except for
its additional methods like from_self() which are all to
be deprecated.
The construction of ORM state will occur beyond the
caching boundary when the new execution model is integrated.
future select() gains a working join() and filter_by() method.
as we continue to rebase and merge each commit in the steps,
callcounts continue to bump around. will have to look at
the final result when it's all in.
References: #5159
References: #4705
References: #4639
References: #4871
References: #5010
Change-Id: I19e05b3424b07114cce6c439b05198ac47f7ac10
Diffstat (limited to 'test/orm')
26 files changed, 2690 insertions, 714 deletions
diff --git a/test/orm/_fixtures.py b/test/orm/_fixtures.py index 2b4fca148..864174d96 100644 --- a/test/orm/_fixtures.py +++ b/test/orm/_fixtures.py @@ -153,7 +153,6 @@ class FixtureTest(fixtures.MappedTest): }, ) mapper(Keyword, keywords) - mapper( Node, nodes, diff --git a/test/orm/inheritance/test_assorted_poly.py b/test/orm/inheritance/test_assorted_poly.py index 029573c5f..62f2097d3 100644 --- a/test/orm/inheritance/test_assorted_poly.py +++ b/test/orm/inheritance/test_assorted_poly.py @@ -2162,13 +2162,13 @@ class CorrelateExceptWPolyAdaptTest( __tablename__ = "c" id = Column(Integer, primary_key=True) - if use_correlate_except: - num_superclass = column_property( - select([func.count(Superclass.id)]) - .where(Superclass.common_id == id) - .correlate_except(Superclass) - .scalar_subquery() - ) + if use_correlate_except: + Common.num_superclass = column_property( + select([func.count(Superclass.id)]) + .where(Superclass.common_id == Common.id) + .correlate_except(Superclass) + .scalar_subquery() + ) if not use_correlate_except: Common.num_superclass = column_property( @@ -2222,13 +2222,12 @@ class CorrelateExceptWPolyAdaptTest( .filter(Common.id == 1) ) - # c.id, subquery are reversed. self.assert_compile( q, - "SELECT (SELECT count(s1.id) AS count_1 " + "SELECT c.id AS c_id, (SELECT count(s1.id) AS count_1 " "FROM s1 LEFT OUTER JOIN s2 ON s1.id = s2.id " "WHERE s1.common_id = c.id) AS anon_1, " - "c.id AS c_id, s1.id AS s1_id, " + "s1.id AS s1_id, " "s1.common_id AS s1_common_id, " "s1.discriminator_field AS s1_discriminator_field, " "s2.id AS s2_id FROM s1 " diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py index db36e36b0..9ee5ce2ab 100644 --- a/test/orm/inheritance/test_polymorphic_rel.py +++ b/test/orm/inheritance/test_polymorphic_rel.py @@ -255,7 +255,7 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Person) - .join("paperwork", aliased=False) + .join("paperwork") .filter(Paperwork.description.like("%review%")) .all(), [b1, m1], @@ -266,7 +266,7 @@ class _PolymorphicTestBase(object): eq_( sess.query(Person) .order_by(Person.person_id) - .join("paperwork", aliased=False) + .join("paperwork") .filter(Paperwork.description.like("%#2%")) .all(), [e1, m1], @@ -277,7 +277,7 @@ class _PolymorphicTestBase(object): eq_( sess.query(Engineer) .order_by(Person.person_id) - .join("paperwork", aliased=False) + .join("paperwork") .filter(Paperwork.description.like("%#2%")) .all(), [e1], @@ -288,14 +288,14 @@ class _PolymorphicTestBase(object): eq_( sess.query(Person) .order_by(Person.person_id) - .join("paperwork", aliased=False) + .join("paperwork") .filter(Person.name.like("%dog%")) .filter(Paperwork.description.like("%#2%")) .all(), [m1], ) - def test_join_from_polymorphic_aliased_one(self): + def test_join_from_polymorphic_flag_aliased_one(self): sess = create_session() eq_( sess.query(Person) @@ -306,7 +306,19 @@ class _PolymorphicTestBase(object): [b1, m1], ) - def test_join_from_polymorphic_aliased_two(self): + def test_join_from_polymorphic_explicit_aliased_one(self): + sess = create_session() + pa = aliased(Paperwork) + eq_( + sess.query(Person) + .order_by(Person.person_id) + .join(pa, "paperwork") + .filter(pa.description.like("%review%")) + .all(), + [b1, m1], + ) + + def test_join_from_polymorphic_flag_aliased_two(self): sess = create_session() eq_( sess.query(Person) @@ -317,7 +329,19 @@ class _PolymorphicTestBase(object): [e1, m1], ) - def test_join_from_polymorphic_aliased_three(self): + def test_join_from_polymorphic_explicit_aliased_two(self): + sess = create_session() + pa = aliased(Paperwork) + eq_( + sess.query(Person) + .order_by(Person.person_id) + .join(pa, "paperwork") + .filter(pa.description.like("%#2%")) + .all(), + [e1, m1], + ) + + def test_join_from_polymorphic_flag_aliased_three(self): sess = create_session() eq_( sess.query(Engineer) @@ -328,14 +352,27 @@ class _PolymorphicTestBase(object): [e1], ) + def test_join_from_polymorphic_explicit_aliased_three(self): + sess = create_session() + pa = aliased(Paperwork) + eq_( + sess.query(Engineer) + .order_by(Person.person_id) + .join(pa, "paperwork") + .filter(pa.description.like("%#2%")) + .all(), + [e1], + ) + def test_join_from_polymorphic_aliased_four(self): sess = create_session() + pa = aliased(Paperwork) eq_( sess.query(Person) .order_by(Person.person_id) - .join("paperwork", aliased=True) + .join(pa, "paperwork") .filter(Person.name.like("%dog%")) - .filter(Paperwork.description.like("%#2%")) + .filter(pa.description.like("%#2%")) .all(), [m1], ) @@ -377,7 +414,7 @@ class _PolymorphicTestBase(object): [m1], ) - def test_join_from_with_polymorphic_aliased_one(self): + def test_join_from_with_polymorphic_flag_aliased_one(self): sess = create_session() eq_( sess.query(Person) @@ -388,7 +425,19 @@ class _PolymorphicTestBase(object): [b1, m1], ) - def test_join_from_with_polymorphic_aliased_two(self): + def test_join_from_with_polymorphic_explicit_aliased_one(self): + sess = create_session() + pa = aliased(Paperwork) + eq_( + sess.query(Person) + .with_polymorphic(Manager) + .join(pa, "paperwork") + .filter(pa.description.like("%review%")) + .all(), + [b1, m1], + ) + + def test_join_from_with_polymorphic_flag_aliased_two(self): sess = create_session() eq_( sess.query(Person) @@ -400,15 +449,30 @@ class _PolymorphicTestBase(object): [e1, m1], ) + def test_join_from_with_polymorphic_explicit_aliased_two(self): + sess = create_session() + pa = aliased(Paperwork) + eq_( + sess.query(Person) + .with_polymorphic([Manager, Engineer]) + .order_by(Person.person_id) + .join(pa, "paperwork") + .filter(pa.description.like("%#2%")) + .all(), + [e1, m1], + ) + def test_join_from_with_polymorphic_aliased_three(self): sess = create_session() + pa = aliased(Paperwork) + eq_( sess.query(Person) .with_polymorphic([Manager, Engineer]) .order_by(Person.person_id) - .join("paperwork", aliased=True) + .join(pa, "paperwork") .filter(Person.name.like("%dog%")) - .filter(Paperwork.description.like("%#2%")) + .filter(pa.description.like("%#2%")) .all(), [m1], ) @@ -423,7 +487,7 @@ class _PolymorphicTestBase(object): c2, ) - def test_join_to_polymorphic_aliased(self): + def test_join_to_polymorphic_flag_aliased(self): sess = create_session() eq_( sess.query(Company) @@ -433,33 +497,60 @@ class _PolymorphicTestBase(object): c2, ) + def test_join_to_polymorphic_explicit_aliased(self): + sess = create_session() + ea = aliased(Person) + eq_( + sess.query(Company) + .join(ea, "employees") + .filter(ea.name == "vlad") + .one(), + c2, + ) + def test_polymorphic_any_one(self): sess = create_session() any_ = Company.employees.any(Person.name == "vlad") eq_(sess.query(Company).filter(any_).all(), [c2]) - def test_polymorphic_any_two(self): + def test_polymorphic_any_flag_alias_two(self): sess = create_session() # test that the aliasing on "Person" does not bleed into the # EXISTS clause generated by any() any_ = Company.employees.any(Person.name == "wally") eq_( sess.query(Company) - .join(Company.employees, aliased=True) + .join("employees", aliased=True) .filter(Person.name == "dilbert") .filter(any_) .all(), [c1], ) + def test_polymorphic_any_explicit_alias_two(self): + sess = create_session() + # test that the aliasing on "Person" does not bleed into the + # EXISTS clause generated by any() + any_ = Company.employees.any(Person.name == "wally") + ea = aliased(Person) + eq_( + sess.query(Company) + .join(ea, Company.employees) + .filter(ea.name == "dilbert") + .filter(any_) + .all(), + [c1], + ) + def test_polymorphic_any_three(self): sess = create_session() any_ = Company.employees.any(Person.name == "vlad") + ea = aliased(Person) eq_( sess.query(Company) - .join(Company.employees, aliased=True) - .filter(Person.name == "dilbert") + .join(ea, Company.employees) + .filter(ea.name == "dilbert") .filter(any_) .all(), [], @@ -932,6 +1023,11 @@ class _PolymorphicTestBase(object): def test_join_to_subclass(self): sess = create_session() + # TODO: these should all be deprecated (?) - these joins are on the + # core tables and should not be getting adapted, not sure why + # adaptation is happening? (is it?) emit a warning when the adaptation + # occurs? + eq_( sess.query(Company) .join(people.join(engineers), "employees") @@ -1087,7 +1183,8 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Company) - .join("employees", "paperwork", aliased=False) + .join(Company.employees) + .join(Person.paperwork) .filter(Paperwork.description.like("%#2%")) .all(), [c1], @@ -1097,7 +1194,8 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Company) - .join("employees", "paperwork", aliased=False) + .join(Company.employees) + .join(Person.paperwork) .filter(Paperwork.description.like("%#%")) .all(), [c1, c2], @@ -1107,7 +1205,8 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Company) - .join("employees", "paperwork", aliased=False) + .join(Company.employees) + .join(Person.paperwork) .filter(Person.name.in_(["dilbert", "vlad"])) .filter(Paperwork.description.like("%#2%")) .all(), @@ -1118,7 +1217,8 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Company) - .join("employees", "paperwork", aliased=False) + .join(Company.employees) + .join(Person.paperwork) .filter(Person.name.in_(["dilbert", "vlad"])) .filter(Paperwork.description.like("%#%")) .all(), @@ -1129,9 +1229,9 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Company) - .join("employees", aliased=aliased) + .join("employees") .filter(Person.name.in_(["dilbert", "vlad"])) - .join("paperwork", from_joinpoint=True, aliased=False) + .join(Person.paperwork) .filter(Paperwork.description.like("%#2%")) .all(), [c1], @@ -1141,9 +1241,9 @@ class _PolymorphicTestBase(object): sess = create_session() eq_( sess.query(Company) - .join("employees", aliased=aliased) + .join("employees") .filter(Person.name.in_(["dilbert", "vlad"])) - .join("paperwork", from_joinpoint=True, aliased=False) + .join(Person.paperwork) .filter(Paperwork.description.like("%#%")) .all(), [c1, c2], @@ -1151,66 +1251,82 @@ class _PolymorphicTestBase(object): def test_join_through_polymorphic_aliased_one(self): sess = create_session() + ea = aliased(Person) + pa = aliased(Paperwork) eq_( sess.query(Company) - .join("employees", "paperwork", aliased=True) - .filter(Paperwork.description.like("%#2%")) + .join(ea, Company.employees) + .join(pa, ea.paperwork) + .filter(pa.description.like("%#2%")) .all(), [c1], ) def test_join_through_polymorphic_aliased_two(self): sess = create_session() + ea = aliased(Person) + pa = aliased(Paperwork) eq_( sess.query(Company) - .join("employees", "paperwork", aliased=True) - .filter(Paperwork.description.like("%#%")) + .join(ea, Company.employees) + .join(pa, ea.paperwork) + .filter(pa.description.like("%#%")) .all(), [c1, c2], ) def test_join_through_polymorphic_aliased_three(self): sess = create_session() + ea = aliased(Person) + pa = aliased(Paperwork) eq_( sess.query(Company) - .join("employees", "paperwork", aliased=True) - .filter(Person.name.in_(["dilbert", "vlad"])) - .filter(Paperwork.description.like("%#2%")) + .join(ea, Company.employees) + .join(pa, ea.paperwork) + .filter(ea.name.in_(["dilbert", "vlad"])) + .filter(pa.description.like("%#2%")) .all(), [c1], ) def test_join_through_polymorphic_aliased_four(self): sess = create_session() + ea = aliased(Person) + pa = aliased(Paperwork) eq_( sess.query(Company) - .join("employees", "paperwork", aliased=True) - .filter(Person.name.in_(["dilbert", "vlad"])) - .filter(Paperwork.description.like("%#%")) + .join(ea, Company.employees) + .join(pa, ea.paperwork) # we can't use "paperwork" here? + .filter(ea.name.in_(["dilbert", "vlad"])) + .filter(pa.description.like("%#%")) .all(), [c1, c2], ) def test_join_through_polymorphic_aliased_five(self): sess = create_session() + ea = aliased(Person) + pa = aliased(Paperwork) eq_( sess.query(Company) - .join("employees", aliased=aliased) - .filter(Person.name.in_(["dilbert", "vlad"])) - .join("paperwork", from_joinpoint=True, aliased=True) - .filter(Paperwork.description.like("%#2%")) + .join(ea, "employees") + .filter(ea.name.in_(["dilbert", "vlad"])) + .join(pa, ea.paperwork) + .filter(pa.description.like("%#2%")) .all(), [c1], ) def test_join_through_polymorphic_aliased_six(self): sess = create_session() + pa = aliased(Paperwork) + ea = aliased(Person) eq_( sess.query(Company) - .join("employees", aliased=aliased) - .filter(Person.name.in_(["dilbert", "vlad"])) - .join("paperwork", from_joinpoint=True, aliased=True) - .filter(Paperwork.description.like("%#%")) + .join(ea, Company.employees) + .filter(ea.name.in_(["dilbert", "vlad"])) + .join(pa, ea.paperwork) + .filter(pa.description.like("%#%")) .all(), [c1, c2], ) diff --git a/test/orm/inheritance/test_relationship.py b/test/orm/inheritance/test_relationship.py index a4dde3f02..ea5b9f96b 100644 --- a/test/orm/inheritance/test_relationship.py +++ b/test/orm/inheritance/test_relationship.py @@ -151,10 +151,11 @@ class SelfReferentialTestJoinedToBase(fixtures.MappedTest): sess.add(e1) sess.flush() sess.expunge_all() + pa = aliased(Person) eq_( sess.query(Engineer) - .join("reports_to", aliased=True) - .filter(Person.name == "dogbert") + .join(pa, "reports_to") + .filter(pa.name == "dogbert") .first(), Engineer(name="dilbert"), ) @@ -261,10 +262,12 @@ class SelfReferentialJ2JTest(fixtures.MappedTest): sess.flush() sess.expunge_all() + ma = aliased(Manager) + eq_( sess.query(Engineer) - .join("reports_to", aliased=True) - .filter(Manager.name == "dogbert") + .join(ma, "reports_to") + .filter(ma.name == "dogbert") .first(), Engineer(name="dilbert"), ) @@ -438,22 +441,24 @@ class SelfReferentialJ2JSelfTest(fixtures.MappedTest): [Engineer(name="e1")], ) - def test_join_aliased_flag_one(self): + def test_join_aliased_one(self): sess = self._two_obj_fixture() + ea = aliased(Engineer) eq_( sess.query(Engineer) - .join("reports_to", aliased=True) - .filter(Engineer.name == "wally") + .join(ea, "reports_to") + .filter(ea.name == "wally") .first(), Engineer(name="dilbert"), ) - def test_join_aliased_flag_two(self): + def test_join_aliased_two(self): sess = self._five_obj_fixture() + ea = aliased(Engineer) eq_( sess.query(Engineer) - .join(Engineer.engineers, aliased=True) - .filter(Engineer.name == "e4") + .join(ea, Engineer.engineers) + .filter(ea.name == "e4") .all(), [Engineer(name="e2")], ) @@ -463,26 +468,27 @@ class SelfReferentialJ2JSelfTest(fixtures.MappedTest): e1 = sess.query(Engineer).filter_by(name="e1").one() e2 = sess.query(Engineer).filter_by(name="e2").one() + ea = aliased(Engineer) eq_( sess.query(Engineer) - .join(Engineer.engineers, aliased=True) - .filter(Engineer.reports_to == None) + .join(ea, Engineer.engineers) + .filter(ea.reports_to == None) .all(), # noqa [], ) eq_( sess.query(Engineer) - .join(Engineer.engineers, aliased=True) - .filter(Engineer.reports_to == e1) + .join(ea, Engineer.engineers) + .filter(ea.reports_to == e1) .all(), [e1], ) eq_( sess.query(Engineer) - .join(Engineer.engineers, aliased=True) - .filter(Engineer.reports_to != None) + .join(ea, Engineer.engineers) + .filter(ea.reports_to != None) .all(), # noqa [e1, e2], ) @@ -2496,9 +2502,9 @@ class MultipleAdaptUsesEntityOverTableTest( def test_two_joins_adaption(self): a, c, d = self.tables.a, self.tables.c, self.tables.d - q = self._two_join_fixture() + q = self._two_join_fixture()._compile_state() - btoc = q._from_obj[0].left + btoc = q.from_clauses[0].left ac_adapted = btoc.right.element.left c_adapted = btoc.right.element.right @@ -2506,7 +2512,7 @@ class MultipleAdaptUsesEntityOverTableTest( is_(ac_adapted.element, a) is_(c_adapted.element, c) - ctod = q._from_obj[0].right + ctod = q.from_clauses[0].right ad_adapted = ctod.element.left d_adapted = ctod.element.right is_(ad_adapted.element, a) @@ -2514,9 +2520,10 @@ class MultipleAdaptUsesEntityOverTableTest( bname, cname, dname = q._entities - b_name_adapted = q._adapt_clause(bname.column, False, True) - c_name_adapted = q._adapt_clause(cname.column, False, True) - d_name_adapted = q._adapt_clause(dname.column, False, True) + adapter = q._get_current_adapter() + b_name_adapted = adapter(bname.column, False) + c_name_adapted = adapter(cname.column, False) + d_name_adapted = adapter(dname.column, False) assert bool(b_name_adapted == a.c.name) assert bool(c_name_adapted == ac_adapted.c.name) diff --git a/test/orm/inheritance/test_selects.py b/test/orm/inheritance/test_selects.py index 335b55e2b..7ac953fd0 100644 --- a/test/orm/inheritance/test_selects.py +++ b/test/orm/inheritance/test_selects.py @@ -71,9 +71,12 @@ class InheritingSelectablesTest(fixtures.MappedTest): s = Session() - assert [Baz(), Baz(), Bar(), Bar()] == s.query(Foo).order_by( - Foo.b.desc() - ).all() + # assert [Baz(), Baz(), Bar(), Bar()] == s.query(Foo).order_by( + # Foo.b.desc() + # ).all() + + # import pdb + # pdb.set_trace() assert [Bar(), Bar()] == s.query(Bar).all() diff --git a/test/orm/inheritance/test_single.py b/test/orm/inheritance/test_single.py index b32a8af3d..101e815fe 100644 --- a/test/orm/inheritance/test_single.py +++ b/test/orm/inheritance/test_single.py @@ -624,10 +624,10 @@ class RelationshipFromSingleTest( ) subq = context.attributes[ ( - "subquery", + "subqueryload_data", (class_mapper(Manager), class_mapper(Manager).attrs.stuff), ) - ] + ]["query"] self.assert_compile( subq, diff --git a/test/orm/test_cache_key.py b/test/orm/test_cache_key.py index 72a1f4c8e..53cb45171 100644 --- a/test/orm/test_cache_key.py +++ b/test/orm/test_cache_key.py @@ -1,15 +1,25 @@ from sqlalchemy import inspect +from sqlalchemy.future import select as future_select from sqlalchemy.orm import aliased from sqlalchemy.orm import defaultload from sqlalchemy.orm import defer from sqlalchemy.orm import joinedload from sqlalchemy.orm import Load +from sqlalchemy.orm import Session from sqlalchemy.orm import subqueryload +from sqlalchemy.orm import with_polymorphic +from sqlalchemy.sql.base import CacheableOptions +from sqlalchemy.sql.visitors import InternalTraversal from sqlalchemy.testing import eq_ from test.orm import _fixtures +from .inheritance import _poly_fixtures from ..sql.test_compare import CacheKeyFixture +def stmt_20(*elements): + return tuple(elem._statement_20() for elem in elements) + + class CacheKeyTest(CacheKeyFixture, _fixtures.FixtureTest): run_setup_mappers = "once" run_inserts = None @@ -53,6 +63,7 @@ class CacheKeyTest(CacheKeyFixture, _fixtures.FixtureTest): self._run_cache_key_fixture( lambda: ( joinedload(User.addresses), + joinedload(User.addresses.of_type(aliased(Address))), joinedload("addresses"), joinedload(User.orders).selectinload("items"), joinedload(User.orders).selectinload(Order.items), @@ -82,6 +93,9 @@ class CacheKeyTest(CacheKeyFixture, _fixtures.FixtureTest): self._run_cache_key_fixture( lambda: ( Load(User).joinedload(User.addresses), + Load(User).joinedload( + User.addresses.of_type(aliased(Address)) + ), Load(User).joinedload(User.orders), Load(User).defer(User.id), Load(User).subqueryload("addresses"), @@ -122,3 +136,218 @@ class CacheKeyTest(CacheKeyFixture, _fixtures.FixtureTest): ), ]: eq_(left._generate_cache_key(), right._generate_cache_key()) + + def test_future_selects_w_orm_joins(self): + + User, Address, Keyword, Order, Item = self.classes( + "User", "Address", "Keyword", "Order", "Item" + ) + + a1 = aliased(Address) + + self._run_cache_key_fixture( + lambda: ( + future_select(User).join(User.addresses), + future_select(User).join(User.orders), + future_select(User).join(User.addresses).join(User.orders), + future_select(User).join(Address, User.addresses), + future_select(User).join(a1, User.addresses), + future_select(User).join(User.addresses.of_type(a1)), + future_select(User) + .join(Address, User.addresses) + .join_from(User, Order), + future_select(User) + .join(Address, User.addresses) + .join_from(User, User.orders), + ), + compare_values=True, + ) + + def test_orm_query_basic(self): + + User, Address, Keyword, Order, Item = self.classes( + "User", "Address", "Keyword", "Order", "Item" + ) + + a1 = aliased(Address) + + self._run_cache_key_fixture( + lambda: stmt_20( + Session().query(User), + Session().query(User).prefix_with("foo"), + Session().query(User).filter_by(name="ed"), + Session().query(User).filter_by(name="ed").order_by(User.id), + Session().query(User).filter_by(name="ed").order_by(User.name), + Session().query(User).filter_by(name="ed").group_by(User.id), + Session() + .query(User) + .join(User.addresses) + .filter(User.name == "ed"), + Session().query(User).join(User.orders), + Session() + .query(User) + .join(User.orders) + .filter(Order.description == "adsf"), + Session().query(User).join(User.addresses).join(User.orders), + Session().query(User).join(Address, User.addresses), + Session().query(User).join(a1, User.addresses), + Session().query(User).join(User.addresses.of_type(a1)), + Session().query(Address).join(Address.user), + Session().query(User, Address).filter_by(name="ed"), + Session().query(User, a1).filter_by(name="ed"), + ), + compare_values=True, + ) + + def test_options(self): + class MyOpt(CacheableOptions): + _cache_key_traversal = [ + ("x", InternalTraversal.dp_plain_obj), + ("y", InternalTraversal.dp_plain_obj), + ] + x = 5 + y = () + + self._run_cache_key_fixture( + lambda: ( + MyOpt, + MyOpt + {"x": 10}, + MyOpt + {"x": 15, "y": ("foo",)}, + MyOpt + {"x": 15, "y": ("foo",)} + {"y": ("foo", "bar")}, + ), + compare_values=True, + ) + + +class PolyCacheKeyTest(CacheKeyFixture, _poly_fixtures._Polymorphic): + run_setup_mappers = "once" + run_inserts = None + run_deletes = None + + def test_wp_objects(self): + Person, Manager, Engineer, Boss = self.classes( + "Person", "Manager", "Engineer", "Boss" + ) + + self._run_cache_key_fixture( + lambda: ( + inspect(with_polymorphic(Person, [Manager, Engineer])), + inspect(with_polymorphic(Person, [Manager])), + inspect(with_polymorphic(Person, [Manager, Engineer, Boss])), + inspect( + with_polymorphic(Person, [Manager, Engineer], flat=True) + ), + inspect( + with_polymorphic( + Person, + [Manager, Engineer], + future_select(Person) + .outerjoin(Manager) + .outerjoin(Engineer) + .subquery(), + ) + ), + ), + compare_values=True, + ) + + def test_wp_queries(self): + Person, Manager, Engineer, Boss = self.classes( + "Person", "Manager", "Engineer", "Boss" + ) + + def one(): + return ( + Session().query(Person).with_polymorphic([Manager, Engineer]) + ) + + def two(): + wp = with_polymorphic(Person, [Manager, Engineer]) + + return Session().query(wp) + + def three(): + wp = with_polymorphic(Person, [Manager, Engineer]) + + return Session().query(wp).filter(wp.name == "asdfo") + + def three_a(): + wp = with_polymorphic(Person, [Manager, Engineer], flat=True) + + return Session().query(wp).filter(wp.name == "asdfo") + + def four(): + return ( + Session() + .query(Person) + .with_polymorphic([Manager, Engineer]) + .filter(Person.name == "asdf") + ) + + def five(): + subq = ( + future_select(Person) + .outerjoin(Manager) + .outerjoin(Engineer) + .subquery() + ) + wp = with_polymorphic(Person, [Manager, Engineer], subq) + + return Session().query(wp).filter(wp.name == "asdfo") + + def six(): + subq = ( + future_select(Person) + .outerjoin(Manager) + .outerjoin(Engineer) + .subquery() + ) + + return ( + Session() + .query(Person) + .with_polymorphic([Manager, Engineer], subq) + .filter(Person.name == "asdfo") + ) + + self._run_cache_key_fixture( + lambda: stmt_20( + one(), two(), three(), three_a(), four(), five(), six() + ), + compare_values=True, + ) + + def test_wp_joins(self): + Company, Person, Manager, Engineer, Boss = self.classes( + "Company", "Person", "Manager", "Engineer", "Boss" + ) + + def one(): + return ( + Session() + .query(Company) + .join(Company.employees) + .filter(Person.name == "asdf") + ) + + def two(): + wp = with_polymorphic(Person, [Manager, Engineer]) + return ( + Session() + .query(Company) + .join(Company.employees.of_type(wp)) + .filter(wp.name == "asdf") + ) + + def three(): + wp = with_polymorphic(Person, [Manager, Engineer]) + return ( + Session() + .query(Company) + .join(Company.employees.of_type(wp)) + .filter(wp.Engineer.name == "asdf") + ) + + self._run_cache_key_fixture( + lambda: stmt_20(one(), two(), three()), compare_values=True, + ) diff --git a/test/orm/test_core_compilation.py b/test/orm/test_core_compilation.py new file mode 100644 index 000000000..86edf53af --- /dev/null +++ b/test/orm/test_core_compilation.py @@ -0,0 +1,1082 @@ +from sqlalchemy import exc +from sqlalchemy import func +from sqlalchemy import insert +from sqlalchemy import literal_column +from sqlalchemy import testing +from sqlalchemy.future import select +from sqlalchemy.orm import aliased +from sqlalchemy.orm import column_property +from sqlalchemy.orm import join as orm_join +from sqlalchemy.orm import mapper +from sqlalchemy.orm import Session +from sqlalchemy.orm import with_polymorphic +from sqlalchemy.sql.selectable import Join as core_join +from sqlalchemy.testing import assert_raises_message +from sqlalchemy.testing import AssertsCompiledSQL +from .inheritance import _poly_fixtures +from .test_query import QueryTest + + +# TODO: +# composites / unions, etc. + + +class BuilderTest(QueryTest, AssertsCompiledSQL): + __dialect__ = "default" + + def test_filter_by(self): + User, Address = self.classes("User", "Address") + + stmt = select(User).filter_by(name="ed") + + self.assert_compile( + stmt, + "SELECT users.id, users.name FROM users " + "WHERE users.name = :name_1", + ) + + +class JoinTest(QueryTest, AssertsCompiledSQL): + __dialect__ = "default" + + def test_join_from_no_onclause(self): + User, Address = self.classes("User", "Address") + + stmt = select(literal_column("1")).join_from(User, Address) + self.assert_compile( + stmt, + "SELECT 1 FROM users JOIN addresses " + "ON users.id = addresses.user_id", + ) + + def test_join_from_w_relationship(self): + User, Address = self.classes("User", "Address") + + stmt = select(literal_column("1")).join_from( + User, Address, User.addresses + ) + self.assert_compile( + stmt, + "SELECT 1 FROM users JOIN addresses " + "ON users.id = addresses.user_id", + ) + + def test_join_from_alised_w_relationship(self): + User, Address = self.classes("User", "Address") + + u1 = aliased(User) + + stmt = select(literal_column("1")).join_from(u1, Address, u1.addresses) + self.assert_compile( + stmt, + "SELECT 1 FROM users AS users_1 JOIN addresses " + "ON users_1.id = addresses.user_id", + ) + + def test_join_conflicting_right_side(self): + User, Address = self.classes("User", "Address") + + stmt = select(User).join(Address, User.orders) + assert_raises_message( + exc.InvalidRequestError, + "Selectable 'addresses' is not derived from 'orders'", + stmt.compile, + ) + + def test_join_from_conflicting_left_side_plain(self): + User, Address, Order = self.classes("User", "Address", "Order") + + stmt = select(User).join_from(User, Address, Order.address) + assert_raises_message( + exc.InvalidRequestError, + r"explicit from clause .*User.* does not match .* Order.address", + stmt.compile, + ) + + def test_join_from_conflicting_left_side_mapper_vs_aliased(self): + User, Address = self.classes("User", "Address") + + u1 = aliased(User) + + stmt = select(User).join_from(User, Address, u1.addresses) + assert_raises_message( + exc.InvalidRequestError, + # the display of the attribute here is not consistent vs. + # the straight aliased class, should improve this. + r"explicit from clause .*User.* does not match left side .*" + r"of relationship attribute AliasedClass_User.addresses", + stmt.compile, + ) + + def test_join_from_conflicting_left_side_aliased_vs_mapper(self): + User, Address = self.classes("User", "Address") + + u1 = aliased(User) + + stmt = select(u1).join_from(u1, Address, User.addresses) + assert_raises_message( + exc.InvalidRequestError, + r"explicit from clause aliased\(User\) does not match left " + "side of relationship attribute User.addresses", + stmt.compile, + ) + + def test_join_from_we_can_explicitly_tree_joins(self): + User, Address, Order, Item, Keyword = self.classes( + "User", "Address", "Order", "Item", "Keyword" + ) + + stmt = ( + select(User) + .join(User.addresses) + .join_from(User, Order, User.orders) + .join(Order.items) + ) + self.assert_compile( + stmt, + "SELECT users.id, users.name FROM users JOIN addresses " + "ON users.id = addresses.user_id JOIN orders " + "ON users.id = orders.user_id JOIN order_items AS order_items_1 " + "ON orders.id = order_items_1.order_id JOIN items " + "ON items.id = order_items_1.item_id", + ) + + def test_join_from_w_filter_by(self): + User, Address, Order, Item, Keyword = self.classes( + "User", "Address", "Order", "Item", "Keyword" + ) + + stmt = ( + select(User) + .filter_by(name="n1") + .join(User.addresses) + .filter_by(email_address="a1") + .join_from(User, Order, User.orders) + .filter_by(description="d1") + .join(Order.items) + .filter_by(description="d2") + ) + self.assert_compile( + stmt, + "SELECT users.id, users.name FROM users " + "JOIN addresses ON users.id = addresses.user_id " + "JOIN orders ON users.id = orders.user_id " + "JOIN order_items AS order_items_1 " + "ON orders.id = order_items_1.order_id " + "JOIN items ON items.id = order_items_1.item_id " + "WHERE users.name = :name_1 " + "AND addresses.email_address = :email_address_1 " + "AND orders.description = :description_1 " + "AND items.description = :description_2", + checkparams={ + "name_1": "n1", + "email_address_1": "a1", + "description_1": "d1", + "description_2": "d2", + }, + ) + + +class RelationshipNaturalCompileTest(QueryTest, AssertsCompiledSQL): + """test using core join() with relationship attributes. + + as __clause_element__() produces a workable SQL expression, this should + be generally possible. + + However, it can't work for many-to-many relationships, as these + require two joins. Only the ORM can look at the entities and decide + that there's a separate "secondary" table to be rendered as a separate + join. + + """ + + __dialect__ = "default" + + @testing.fails("need to have of_type() expressions render directly") + def test_of_type_implicit_join(self): + User, Address = self.classes("User", "Address") + + u1 = aliased(User) + a1 = aliased(Address) + + stmt1 = select(u1).where(u1.addresses.of_type(a1)) + stmt2 = Session().query(u1).filter(u1.addresses.of_type(a1)) + + expected = ( + "SELECT users_1.id, users_1.name FROM users AS users_1, " + "addresses AS addresses_1 WHERE users_1.id = addresses_1.user_id" + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_of_type_explicit_join(self): + User, Address = self.classes("User", "Address") + + u1 = aliased(User) + a1 = aliased(Address) + + stmt = select(u1).join(u1.addresses.of_type(a1)) + + self.assert_compile( + stmt, + "SELECT users_1.id, users_1.name FROM users AS users_1 " + "JOIN addresses AS addresses_1 " + "ON users_1.id = addresses_1.user_id", + ) + + def test_many_to_many_explicit_join(self): + Item, Keyword = self.classes("Item", "Keyword") + + stmt = select(Item).join(Keyword, Item.keywords) + + self.assert_compile( + stmt, + "SELECT items.id, items.description FROM items " + "JOIN item_keywords AS item_keywords_1 " + "ON items.id = item_keywords_1.item_id " + "JOIN keywords ON keywords.id = item_keywords_1.keyword_id", + ) + + def test_many_to_many_implicit_join(self): + Item, Keyword = self.classes("Item", "Keyword") + + stmt = select(Item).where(Item.keywords) + + # this was the intent of the primary + secondary clauseelement. + # it can do enough of the right thing in an implicit join + # context. + self.assert_compile( + stmt, + "SELECT items.id, items.description FROM items, " + "item_keywords AS item_keywords_1, keywords " + "WHERE items.id = item_keywords_1.item_id " + "AND keywords.id = item_keywords_1.keyword_id", + ) + + +class InheritedTest(_poly_fixtures._Polymorphic): + run_setup_mappers = "once" + + +class ImplicitWithPolymorphicTest( + _poly_fixtures._PolymorphicUnions, AssertsCompiledSQL +): + """Test a series of mappers with a very awkward with_polymorphic setting, + that tables and columns are rendered using the selectable in the correct + contexts. PolymorphicUnions represent the most awkward and verbose + polymorphic fixtures you can have. expressions need to be maximally + accurate in terms of the mapped selectable in order to produce correct + queries, which also will be really wrong if that mapped selectable is not + in use. + + """ + + __dialect__ = "default" + + def test_select_columns_where_baseclass(self): + Person = self.classes.Person + + stmt = ( + select(Person.person_id, Person.name) + .where(Person.name == "some name") + .order_by(Person.person_id) + ) + + sess = Session() + q = ( + sess.query(Person.person_id, Person.name) + .filter(Person.name == "some name") + .order_by(Person.person_id) + ) + + expected = ( + "SELECT pjoin.person_id, pjoin.name FROM " + "(SELECT engineers.person_id AS person_id, people.company_id AS " + "company_id, people.name AS name, people.type AS type, " + "engineers.status AS status, engineers.engineer_name AS " + "engineer_name, engineers.primary_language AS primary_language, " + "CAST(NULL AS VARCHAR(50)) AS manager_name FROM people " + "JOIN engineers ON people.person_id = engineers.person_id " + "UNION ALL SELECT managers.person_id AS person_id, " + "people.company_id AS company_id, people.name AS name, " + "people.type AS type, managers.status AS status, " + "CAST(NULL AS VARCHAR(50)) AS engineer_name, " + "CAST(NULL AS VARCHAR(50)) AS primary_language, " + "managers.manager_name AS manager_name FROM people " + "JOIN managers ON people.person_id = managers.person_id) AS " + "pjoin WHERE pjoin.name = :name_1 ORDER BY pjoin.person_id" + ) + self.assert_compile(stmt, expected) + + self.assert_compile(q.statement, expected) + + def test_select_where_baseclass(self): + Person = self.classes.Person + + stmt = ( + select(Person) + .where(Person.name == "some name") + .order_by(Person.person_id) + ) + + sess = Session() + q = ( + sess.query(Person) + .filter(Person.name == "some name") + .order_by(Person.person_id) + ) + + expected = ( + "SELECT pjoin.person_id, pjoin.company_id, pjoin.name, " + "pjoin.type, pjoin.status, pjoin.engineer_name, " + "pjoin.primary_language, pjoin.manager_name FROM " + "(SELECT engineers.person_id AS person_id, people.company_id " + "AS company_id, people.name AS name, people.type AS type, " + "engineers.status AS status, engineers.engineer_name AS " + "engineer_name, engineers.primary_language AS primary_language, " + "CAST(NULL AS VARCHAR(50)) AS manager_name FROM people " + "JOIN engineers ON people.person_id = engineers.person_id " + "UNION ALL SELECT managers.person_id AS person_id, " + "people.company_id AS company_id, people.name AS name, " + "people.type AS type, managers.status AS status, " + "CAST(NULL AS VARCHAR(50)) AS engineer_name, " + "CAST(NULL AS VARCHAR(50)) AS primary_language, " + "managers.manager_name AS manager_name FROM people " + "JOIN managers ON people.person_id = managers.person_id) AS " + "pjoin WHERE pjoin.name = :name_1 ORDER BY pjoin.person_id" + ) + self.assert_compile(stmt, expected) + + self.assert_compile(q.statement, expected) + + def test_select_where_subclass(self): + + Engineer = self.classes.Engineer + + # what will *not* work with Core, that the ORM does for now, + # is that if you do where/orderby Person.column, it will de-adapt + # the Person columns from the polymorphic union + + stmt = ( + select(Engineer) + .where(Engineer.name == "some name") + .order_by(Engineer.person_id) + ) + + sess = Session() + q = ( + sess.query(Engineer) + .filter(Engineer.name == "some name") + .order_by(Engineer.person_id) + ) + + # the ORM has a different column selection than what a purely core + # select does, in terms of engineers.person_id vs. people.person_id + + expected = ( + "SELECT engineers.person_id, people.person_id, people.company_id, " + "people.name, " + "people.type, engineers.status, " + "engineers.engineer_name, engineers.primary_language " + "FROM people JOIN engineers " + "ON people.person_id = engineers.person_id " + "WHERE people.name = :name_1 ORDER BY engineers.person_id" + ) + + self.assert_compile(stmt, expected) + self.assert_compile(q.statement, expected) + + def test_select_where_columns_subclass(self): + + Engineer = self.classes.Engineer + + # what will *not* work with Core, that the ORM does for now, + # is that if you do where/orderby Person.column, it will de-adapt + # the Person columns from the polymorphic union + + # After many attempts to get the JOIN to render, by annotating + # the columns with the "join" that they come from and trying to + # get Select() to render out that join, there's no approach + # that really works without stepping on other assumptions, so + # add select_from(Engineer) explicitly. It's still puzzling why the + # ORM seems to know how to make this decision more effectively + # when the select() has the same amount of information. + stmt = ( + select(Engineer.person_id, Engineer.name) + .where(Engineer.name == "some name") + .select_from(Engineer) + .order_by(Engineer.person_id) + ) + + sess = Session() + q = ( + sess.query(Engineer.person_id, Engineer.name) + .filter(Engineer.name == "some name") + .order_by(Engineer.person_id) + ) + + expected = ( + "SELECT engineers.person_id, people.name " + "FROM people JOIN engineers " + "ON people.person_id = engineers.person_id " + "WHERE people.name = :name_1 ORDER BY engineers.person_id" + ) + + self.assert_compile(stmt, expected) + self.assert_compile(q.statement, expected) + + +class RelationshipNaturalInheritedTest(InheritedTest, AssertsCompiledSQL): + __dialect__ = "default" + + straight_company_to_person_expected = ( + "SELECT companies.company_id, companies.name FROM companies " + "JOIN people ON companies.company_id = people.company_id" + ) + + default_pjoin = ( + "(people LEFT OUTER " + "JOIN engineers ON people.person_id = engineers.person_id " + "LEFT OUTER JOIN managers " + "ON people.person_id = managers.person_id " + "LEFT OUTER JOIN boss ON managers.person_id = boss.boss_id) " + "ON companies.company_id = people.company_id" + ) + + flat_aliased_pjoin = ( + "(people AS people_1 LEFT OUTER JOIN engineers AS " + "engineers_1 ON people_1.person_id = engineers_1.person_id " + "LEFT OUTER JOIN managers AS managers_1 " + "ON people_1.person_id = managers_1.person_id " + "LEFT OUTER JOIN boss AS boss_1 ON " + "managers_1.person_id = boss_1.boss_id) " + "ON companies.company_id = people_1.company_id" + ) + + aliased_pjoin = ( + "(SELECT people.person_id AS people_person_id, people.company_id " + "AS people_company_id, people.name AS people_name, people.type " + "AS people_type, engineers.person_id AS engineers_person_id, " + "engineers.status AS engineers_status, engineers.engineer_name " + "AS engineers_engineer_name, engineers.primary_language " + "AS engineers_primary_language, managers.person_id " + "AS managers_person_id, managers.status AS managers_status, " + "managers.manager_name AS managers_manager_name, " + "boss.boss_id AS boss_boss_id, boss.golf_swing AS boss_golf_swing " + "FROM people LEFT OUTER JOIN engineers ON people.person_id = " + "engineers.person_id LEFT OUTER JOIN managers ON " + "people.person_id = managers.person_id LEFT OUTER JOIN boss " + "ON managers.person_id = boss.boss_id) AS anon_1 " + "ON companies.company_id = anon_1.people_company_id" + ) + + person_paperwork_expected = ( + "SELECT companies.company_id, companies.name FROM companies " + "JOIN people ON companies.company_id = people.company_id " + "JOIN paperwork ON people.person_id = paperwork.person_id" + ) + + c_to_p_whereclause = ( + "SELECT companies.company_id, companies.name FROM companies " + "JOIN people ON companies.company_id = people.company_id " + "WHERE people.name = :name_1" + ) + + poly_columns = "SELECT people.person_id FROM people" + + def test_straight(self): + Company, Person, Manager, Engineer = self.classes( + "Company", "Person", "Manager", "Engineer" + ) + + stmt1 = select(Company).select_from( + orm_join(Company, Person, Company.employees) + ) + stmt2 = select(Company).join(Company.employees) + stmt3 = Session().query(Company).join(Company.employees).statement + + # TODO: can't get aliasing to not happen for .join() verion + self.assert_compile( + stmt1, + self.straight_company_to_person_expected.replace( + "pjoin_1", "pjoin" + ), + ) + self.assert_compile(stmt2, self.straight_company_to_person_expected) + self.assert_compile(stmt3, self.straight_company_to_person_expected) + + def test_columns(self): + Company, Person, Manager, Engineer = self.classes( + "Company", "Person", "Manager", "Engineer" + ) + + stmt = select(Person.person_id) + + self.assert_compile(stmt, self.poly_columns) + + def test_straight_whereclause(self): + Company, Person, Manager, Engineer = self.classes( + "Company", "Person", "Manager", "Engineer" + ) + + # TODO: fails + # stmt1 = ( + # select(Company) + # .select_from(orm_join(Company, Person, Company.employees)) + # .where(Person.name == "ed") + # ) + + stmt2 = ( + select(Company).join(Company.employees).where(Person.name == "ed") + ) + stmt3 = ( + Session() + .query(Company) + .join(Company.employees) + .filter(Person.name == "ed") + .statement + ) + + # TODO: more inheriance woes, the first statement doesn't know that + # it loads polymorphically with Person. should we have mappers and + # ORM attributes return their polymorphic entity for + # __clause_element__() ? or should we know to look inside the + # orm_join and find all the entities that are important? it is + # looking like having ORM expressions use their polymoprhic selectable + # will solve a lot but not all of these problems. + + # self.assert_compile(stmt1, self.c_to_p_whereclause) + + # self.assert_compile(stmt1, self.c_to_p_whereclause) + self.assert_compile(stmt2, self.c_to_p_whereclause) + self.assert_compile(stmt3, self.c_to_p_whereclause) + + def test_two_level(self): + Company, Person, Paperwork = self.classes( + "Company", "Person", "Paperwork" + ) + + stmt1 = select(Company).select_from( + orm_join(Company, Person, Company.employees).join( + Paperwork, Person.paperwork + ) + ) + + stmt2 = select(Company).join(Company.employees).join(Person.paperwork) + stmt3 = ( + Session() + .query(Company) + .join(Company.employees) + .join(Person.paperwork) + .statement + ) + + self.assert_compile(stmt1, self.person_paperwork_expected) + self.assert_compile( + stmt2, self.person_paperwork_expected.replace("pjoin", "pjoin_1") + ) + self.assert_compile( + stmt3, self.person_paperwork_expected.replace("pjoin", "pjoin_1") + ) + + def test_wpoly_of_type(self): + Company, Person, Manager, Engineer = self.classes( + "Company", "Person", "Manager", "Engineer" + ) + + p1 = with_polymorphic(Person, "*") + + stmt1 = select(Company).select_from( + orm_join(Company, p1, Company.employees.of_type(p1)) + ) + + stmt2 = select(Company).join(Company.employees.of_type(p1)) + stmt3 = ( + Session() + .query(Company) + .join(Company.employees.of_type(p1)) + .statement + ) + expected = ( + "SELECT companies.company_id, companies.name " + "FROM companies JOIN %s" % self.default_pjoin + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + self.assert_compile(stmt3, expected) + + def test_wpoly_aliased_of_type(self): + Company, Person, Manager, Engineer = self.classes( + "Company", "Person", "Manager", "Engineer" + ) + s = Session() + + p1 = with_polymorphic(Person, "*", aliased=True) + + stmt1 = select(Company).select_from( + orm_join(Company, p1, Company.employees.of_type(p1)) + ) + + stmt2 = select(Company).join(p1, Company.employees.of_type(p1)) + + stmt3 = s.query(Company).join(Company.employees.of_type(p1)).statement + + expected = ( + "SELECT companies.company_id, companies.name FROM companies " + "JOIN %s" % self.aliased_pjoin + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + self.assert_compile(stmt3, expected) + + def test_wpoly_aliased_flat_of_type(self): + Company, Person, Manager, Engineer = self.classes( + "Company", "Person", "Manager", "Engineer" + ) + + p1 = with_polymorphic(Person, "*", aliased=True, flat=True) + + stmt1 = select(Company).select_from( + orm_join(Company, p1, Company.employees.of_type(p1)) + ) + + stmt2 = select(Company).join(p1, Company.employees.of_type(p1)) + + stmt3 = ( + Session() + .query(Company) + .join(Company.employees.of_type(p1)) + .statement + ) + + expected = ( + "SELECT companies.company_id, companies.name FROM companies " + "JOIN %s" % self.flat_aliased_pjoin + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + self.assert_compile(stmt3, expected) + + +class RelNaturalAliasedJoinsTest( + _poly_fixtures._PolymorphicAliasedJoins, RelationshipNaturalInheritedTest +): + straight_company_to_person_expected = ( + # TODO: would rather not have the aliasing here but can't fix + # that right now + "SELECT companies.company_id, companies.name FROM companies " + "JOIN (SELECT people.person_id AS people_person_id, people.company_id " + "AS people_company_id, people.name AS people_name, people.type " + "AS people_type, engineers.person_id AS engineers_person_id, " + "engineers.status AS engineers_status, engineers.engineer_name " + "AS engineers_engineer_name, engineers.primary_language AS " + "engineers_primary_language, managers.person_id AS " + "managers_person_id, managers.status AS managers_status, " + "managers.manager_name AS managers_manager_name FROM people " + "LEFT OUTER JOIN engineers ON people.person_id = " + "engineers.person_id LEFT OUTER JOIN managers ON people.person_id = " + "managers.person_id) AS pjoin_1 ON companies.company_id = " + "pjoin_1.people_company_id" + ) + + person_paperwork_expected = ( + "SELECT companies.company_id, companies.name FROM companies JOIN " + "(SELECT people.person_id AS people_person_id, people.company_id " + "AS people_company_id, people.name AS people_name, people.type " + "AS people_type, engineers.person_id AS engineers_person_id, " + "engineers.status AS engineers_status, engineers.engineer_name " + "AS engineers_engineer_name, engineers.primary_language AS " + "engineers_primary_language, managers.person_id AS " + "managers_person_id, managers.status AS managers_status, " + "managers.manager_name AS managers_manager_name FROM people " + "LEFT OUTER JOIN engineers ON people.person_id = engineers.person_id " + "LEFT OUTER JOIN managers ON people.person_id = managers.person_id) " + "AS pjoin ON companies.company_id = pjoin.people_company_id " + "JOIN paperwork ON pjoin.people_person_id = paperwork.person_id" + ) + + default_pjoin = ( + "(SELECT people.person_id AS people_person_id, " + "people.company_id AS people_company_id, people.name AS people_name, " + "people.type AS people_type, engineers.person_id AS " + "engineers_person_id, engineers.status AS engineers_status, " + "engineers.engineer_name AS engineers_engineer_name, " + "engineers.primary_language AS engineers_primary_language, " + "managers.person_id AS managers_person_id, managers.status " + "AS managers_status, managers.manager_name AS managers_manager_name " + "FROM people LEFT OUTER JOIN engineers ON people.person_id = " + "engineers.person_id LEFT OUTER JOIN managers " + "ON people.person_id = managers.person_id) AS pjoin " + "ON companies.company_id = pjoin.people_company_id" + ) + flat_aliased_pjoin = ( + "(SELECT people.person_id AS people_person_id, " + "people.company_id AS people_company_id, people.name AS people_name, " + "people.type AS people_type, engineers.person_id " + "AS engineers_person_id, engineers.status AS engineers_status, " + "engineers.engineer_name AS engineers_engineer_name, " + "engineers.primary_language AS engineers_primary_language, " + "managers.person_id AS managers_person_id, " + "managers.status AS managers_status, managers.manager_name " + "AS managers_manager_name FROM people " + "LEFT OUTER JOIN engineers ON people.person_id = engineers.person_id " + "LEFT OUTER JOIN managers ON people.person_id = managers.person_id) " + "AS pjoin_1 ON companies.company_id = pjoin_1.people_company_id" + ) + + aliased_pjoin = ( + "(SELECT people.person_id AS people_person_id, people.company_id " + "AS people_company_id, people.name AS people_name, " + "people.type AS people_type, engineers.person_id AS " + "engineers_person_id, engineers.status AS engineers_status, " + "engineers.engineer_name AS engineers_engineer_name, " + "engineers.primary_language AS engineers_primary_language, " + "managers.person_id AS managers_person_id, managers.status " + "AS managers_status, managers.manager_name AS managers_manager_name " + "FROM people LEFT OUTER JOIN engineers ON people.person_id = " + "engineers.person_id LEFT OUTER JOIN managers " + "ON people.person_id = managers.person_id) AS pjoin_1 " + "ON companies.company_id = pjoin_1.people_company_id" + ) + + c_to_p_whereclause = ( + "SELECT companies.company_id, companies.name FROM companies " + "JOIN (SELECT people.person_id AS people_person_id, " + "people.company_id AS people_company_id, people.name AS people_name, " + "people.type AS people_type, engineers.person_id AS " + "engineers_person_id, engineers.status AS engineers_status, " + "engineers.engineer_name AS engineers_engineer_name, " + "engineers.primary_language AS engineers_primary_language, " + "managers.person_id AS managers_person_id, managers.status " + "AS managers_status, managers.manager_name AS managers_manager_name " + "FROM people LEFT OUTER JOIN engineers " + "ON people.person_id = engineers.person_id " + "LEFT OUTER JOIN managers ON people.person_id = managers.person_id) " + "AS pjoin_1 ON companies.company_id = pjoin_1.people_company_id " + "WHERE pjoin_1.people_name = :name_1" + ) + + poly_columns = ( + "SELECT pjoin.people_person_id FROM (SELECT people.person_id AS " + "people_person_id, people.company_id AS people_company_id, " + "people.name AS people_name, people.type AS people_type, " + "engineers.person_id AS engineers_person_id, engineers.status " + "AS engineers_status, engineers.engineer_name AS " + "engineers_engineer_name, engineers.primary_language AS " + "engineers_primary_language, managers.person_id AS " + "managers_person_id, managers.status AS managers_status, " + "managers.manager_name AS managers_manager_name FROM people " + "LEFT OUTER JOIN engineers ON people.person_id = engineers.person_id " + "LEFT OUTER JOIN managers ON people.person_id = managers.person_id) " + "AS pjoin" + ) + + +class RawSelectTest(QueryTest, AssertsCompiledSQL): + """older tests from test_query. Here, they are converted to use + future selects with ORM compilation. + + """ + + __dialect__ = "default" + + def test_select_from_entity(self): + User = self.classes.User + + self.assert_compile( + select(literal_column("*")).select_from(User), + "SELECT * FROM users", + ) + + def test_where_relationship(self): + User = self.classes.User + + stmt1 = select(User).where(User.addresses) + stmt2 = Session().query(User).filter(User.addresses).statement + + expected = ( + "SELECT users.id, users.name FROM users, addresses " + "WHERE users.id = addresses.user_id" + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_where_m2m_relationship(self): + Item = self.classes.Item + + expected = ( + "SELECT items.id, items.description FROM items, " + "item_keywords AS item_keywords_1, keywords " + "WHERE items.id = item_keywords_1.item_id " + "AND keywords.id = item_keywords_1.keyword_id" + ) + + stmt1 = select(Item).where(Item.keywords) + stmt2 = Session().query(Item).filter(Item.keywords).statement + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_inline_select_from_entity(self): + User = self.classes.User + + expected = "SELECT * FROM users" + stmt1 = select(literal_column("*")).select_from(User) + stmt2 = ( + Session().query(literal_column("*")).select_from(User).statement + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_select_from_aliased_entity(self): + User = self.classes.User + ua = aliased(User, name="ua") + + stmt1 = select(literal_column("*")).select_from(ua) + stmt2 = Session().query(literal_column("*")).select_from(ua) + + expected = "SELECT * FROM users AS ua" + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_correlate_entity(self): + User = self.classes.User + Address = self.classes.Address + + expected = ( + "SELECT users.name, addresses.id, " + "(SELECT count(addresses.id) AS count_1 " + "FROM addresses WHERE users.id = addresses.user_id) AS anon_1 " + "FROM users, addresses" + ) + + stmt1 = select( + User.name, + Address.id, + select(func.count(Address.id)) + .where(User.id == Address.user_id) + .correlate(User) + .scalar_subquery(), + ) + stmt2 = ( + Session() + .query( + User.name, + Address.id, + select(func.count(Address.id)) + .where(User.id == Address.user_id) + .correlate(User) + .scalar_subquery(), + ) + .statement + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_correlate_aliased_entity(self): + User = self.classes.User + Address = self.classes.Address + uu = aliased(User, name="uu") + + stmt1 = select( + uu.name, + Address.id, + select(func.count(Address.id)) + .where(uu.id == Address.user_id) + .correlate(uu) + .scalar_subquery(), + ) + + stmt2 = ( + Session() + .query( + uu.name, + Address.id, + select(func.count(Address.id)) + .where(uu.id == Address.user_id) + .correlate(uu) + .scalar_subquery(), + ) + .statement + ) + + expected = ( + "SELECT uu.name, addresses.id, " + "(SELECT count(addresses.id) AS count_1 " + "FROM addresses WHERE uu.id = addresses.user_id) AS anon_1 " + "FROM users AS uu, addresses" + ) + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_columns_clause_entity(self): + User = self.classes.User + + expected = "SELECT users.id, users.name FROM users" + + stmt1 = select(User) + stmt2 = Session().query(User).statement + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_columns_clause_columns(self): + User = self.classes.User + + expected = "SELECT users.id, users.name FROM users" + + stmt1 = select(User.id, User.name) + stmt2 = Session().query(User.id, User.name).statement + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_columns_clause_aliased_columns(self): + User = self.classes.User + ua = aliased(User, name="ua") + + stmt1 = select(ua.id, ua.name) + stmt2 = Session().query(ua.id, ua.name).statement + expected = "SELECT ua.id, ua.name FROM users AS ua" + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_columns_clause_aliased_entity(self): + User = self.classes.User + ua = aliased(User, name="ua") + + stmt1 = select(ua) + stmt2 = Session().query(ua).statement + expected = "SELECT ua.id, ua.name FROM users AS ua" + + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) + + def test_core_join_in_select_from_no_onclause(self): + User = self.classes.User + Address = self.classes.Address + + self.assert_compile( + select(User).select_from(core_join(User, Address)), + "SELECT users.id, users.name FROM users " + "JOIN addresses ON users.id = addresses.user_id", + ) + + def test_join_to_entity_no_onclause(self): + User = self.classes.User + Address = self.classes.Address + + self.assert_compile( + select(User).join(Address), + "SELECT users.id, users.name FROM users " + "JOIN addresses ON users.id = addresses.user_id", + ) + + def test_insert_from_query(self): + User = self.classes.User + Address = self.classes.Address + + s = Session() + q = s.query(User.id, User.name).filter_by(name="ed") + self.assert_compile( + insert(Address).from_select(("id", "email_address"), q), + "INSERT INTO addresses (id, email_address) " + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users WHERE users.name = :name_1", + ) + + def test_insert_from_query_col_attr(self): + User = self.classes.User + Address = self.classes.Address + + s = Session() + q = s.query(User.id, User.name).filter_by(name="ed") + self.assert_compile( + insert(Address).from_select( + (Address.id, Address.email_address), q + ), + "INSERT INTO addresses (id, email_address) " + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users WHERE users.name = :name_1", + ) + + def test_update_from_entity(self): + from sqlalchemy.sql import update + + User = self.classes.User + self.assert_compile( + update(User), "UPDATE users SET id=:id, name=:name" + ) + + self.assert_compile( + update(User).values(name="ed").where(User.id == 5), + "UPDATE users SET name=:name WHERE users.id = :id_1", + checkparams={"id_1": 5, "name": "ed"}, + ) + + def test_delete_from_entity(self): + from sqlalchemy.sql import delete + + User = self.classes.User + self.assert_compile(delete(User), "DELETE FROM users") + + self.assert_compile( + delete(User).where(User.id == 5), + "DELETE FROM users WHERE users.id = :id_1", + checkparams={"id_1": 5}, + ) + + def test_insert_from_entity(self): + from sqlalchemy.sql import insert + + User = self.classes.User + self.assert_compile( + insert(User), "INSERT INTO users (id, name) VALUES (:id, :name)" + ) + + self.assert_compile( + insert(User).values(name="ed"), + "INSERT INTO users (name) VALUES (:name)", + checkparams={"name": "ed"}, + ) + + def test_col_prop_builtin_function(self): + class Foo(object): + pass + + mapper( + Foo, + self.tables.users, + properties={ + "foob": column_property( + func.coalesce(self.tables.users.c.name) + ) + }, + ) + + stmt1 = select(Foo).where(Foo.foob == "somename").order_by(Foo.foob) + stmt2 = ( + Session() + .query(Foo) + .filter(Foo.foob == "somename") + .order_by(Foo.foob) + .statement + ) + + expected = ( + "SELECT coalesce(users.name) AS coalesce_1, " + "users.id, users.name FROM users " + "WHERE coalesce(users.name) = :param_1 " + "ORDER BY coalesce_1" + ) + self.assert_compile(stmt1, expected) + self.assert_compile(stmt2, expected) diff --git a/test/orm/test_default_strategies.py b/test/orm/test_default_strategies.py index 53309b282..6de59d2a2 100644 --- a/test/orm/test_default_strategies.py +++ b/test/orm/test_default_strategies.py @@ -259,8 +259,7 @@ class DefaultStrategyOptionsTest(_fixtures.FixtureTest): assert_raises_message( sa.exc.ArgumentError, "Wildcard token cannot be followed by another entity", - sess.query(User).options, - opt, + sess.query(User).options(opt)._compile_context, ) def test_global_star_ignored_no_entities_unbound(self): diff --git a/test/orm/test_deferred.py b/test/orm/test_deferred.py index b9198033b..e0eba3d11 100644 --- a/test/orm/test_deferred.py +++ b/test/orm/test_deferred.py @@ -1634,8 +1634,7 @@ class InheritanceTest(_Polymorphic): 'Mapped attribute "Manager.status" does not apply to any of the ' "root entities in this query, e.g. " r"with_polymorphic\(Person, \[Manager\]\).", - s.query(wp).options, - load_only(Manager.status), + s.query(wp).options(load_only(Manager.status))._compile_context, ) q = s.query(wp).options(load_only(wp.Manager.status)) @@ -1661,18 +1660,24 @@ class InheritanceTest(_Polymorphic): sa.exc.ArgumentError, r'Can\'t find property named "status" on ' r"with_polymorphic\(Person, \[Manager\]\) in this Query.", - s.query(Company).options, - joinedload(Company.employees.of_type(wp)).load_only("status"), + s.query(Company) + .options( + joinedload(Company.employees.of_type(wp)).load_only("status") + ) + ._compile_context, ) assert_raises_message( sa.exc.ArgumentError, 'Attribute "Manager.status" does not link from element ' r'"with_polymorphic\(Person, \[Manager\]\)"', - s.query(Company).options, - joinedload(Company.employees.of_type(wp)).load_only( - Manager.status - ), + s.query(Company) + .options( + joinedload(Company.employees.of_type(wp)).load_only( + Manager.status + ) + ) + ._compile_context, ) self.assert_compile( diff --git a/test/orm/test_deprecations.py b/test/orm/test_deprecations.py index d1c7a08a9..299aba809 100644 --- a/test/orm/test_deprecations.py +++ b/test/orm/test_deprecations.py @@ -1,9 +1,12 @@ import sqlalchemy as sa from sqlalchemy import and_ +from sqlalchemy import cast from sqlalchemy import desc from sqlalchemy import event from sqlalchemy import func from sqlalchemy import Integer +from sqlalchemy import literal_column +from sqlalchemy import or_ from sqlalchemy import select from sqlalchemy import String from sqlalchemy import testing @@ -747,8 +750,10 @@ class DeprecatedOptionAllTest(OptionsPathTest, _fixtures.FixtureTest): assert_raises_message( sa.exc.ArgumentError, message, - create_session().query(*entity_list).options, - *options + create_session() + .query(*entity_list) + .options(*options) + ._compile_context, ) def test_defer_addtl_attrs(self): @@ -1296,6 +1301,10 @@ class NonPrimaryMapperTest(_fixtures.FixtureTest, AssertsCompiledSQL): class InstancesTest(QueryTest, AssertsCompiledSQL): + @testing.fails( + "ORM refactor not allowing this yet, " + "we may just abandon this use case" + ) def test_from_alias_one(self): User, addresses, users = ( self.classes.User, @@ -1332,6 +1341,41 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): self.assert_sql_count(testing.db, go, 1) + def test_from_alias_two_old_way(self): + User, addresses, users = ( + self.classes.User, + self.tables.addresses, + self.tables.users, + ) + + query = ( + users.select(users.c.id == 7) + .union(users.select(users.c.id > 7)) + .alias("ulist") + .outerjoin(addresses) + .select( + use_labels=True, order_by=[text("ulist.id"), addresses.c.id] + ) + ) + sess = create_session() + q = sess.query(User) + + def go(): + with testing.expect_deprecated( + "The AliasOption is not necessary for entities to be " + "matched up to a query" + ): + result = ( + q.options( + contains_alias("ulist"), contains_eager("addresses") + ) + .from_statement(query) + .all() + ) + assert self.static.user_address_result == result + + self.assert_sql_count(testing.db, go, 1) + def test_contains_eager(self): users, addresses, User = ( self.tables.users, @@ -1672,3 +1716,238 @@ class SessionEventsTest(_RemoveListeners, _fixtures.FixtureTest): canary.after_bulk_delete_legacy.mock_calls, [call(sess, upd.query, upd.context, upd.result)], ) + + +class ImmediateTest(_fixtures.FixtureTest): + run_inserts = "once" + run_deletes = None + + @classmethod + def setup_mappers(cls): + Address, addresses, users, User = ( + cls.classes.Address, + cls.tables.addresses, + cls.tables.users, + cls.classes.User, + ) + + mapper(Address, addresses) + + mapper(User, users, properties=dict(addresses=relationship(Address))) + + def test_value(self): + User = self.classes.User + + sess = create_session() + + with testing.expect_deprecated(r"Query.value\(\) is deprecated"): + eq_(sess.query(User).filter_by(id=7).value(User.id), 7) + with testing.expect_deprecated(r"Query.value\(\) is deprecated"): + eq_( + sess.query(User.id, User.name).filter_by(id=7).value(User.id), + 7, + ) + with testing.expect_deprecated(r"Query.value\(\) is deprecated"): + eq_(sess.query(User).filter_by(id=0).value(User.id), None) + + sess.bind = testing.db + with testing.expect_deprecated(r"Query.value\(\) is deprecated"): + eq_(sess.query().value(sa.literal_column("1").label("x")), 1) + + def test_value_cancels_loader_opts(self): + User = self.classes.User + + sess = create_session() + + q = ( + sess.query(User) + .filter(User.name == "ed") + .options(joinedload(User.addresses)) + ) + + with testing.expect_deprecated(r"Query.value\(\) is deprecated"): + q = q.value(func.count(literal_column("*"))) + + +class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): + __dialect__ = "default" + + def test_values(self): + Address, users, User = ( + self.classes.Address, + self.tables.users, + self.classes.User, + ) + + sess = create_session() + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + assert list(sess.query(User).values()) == list() + + sel = users.select(User.id.in_([7, 8])).alias() + q = sess.query(User) + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = q.select_entity_from(sel).values(User.name) + eq_(list(q2), [("jack",), ("ed",)]) + + q = sess.query(User) + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = q.order_by(User.id).values( + User.name, User.name + " " + cast(User.id, String(50)) + ) + eq_( + list(q2), + [ + ("jack", "jack 7"), + ("ed", "ed 8"), + ("fred", "fred 9"), + ("chuck", "chuck 10"), + ], + ) + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.join("addresses") + .filter(User.name.like("%e%")) + .order_by(User.id, Address.id) + .values(User.name, Address.email_address) + ) + eq_( + list(q2), + [ + ("ed", "ed@wood.com"), + ("ed", "ed@bettyboop.com"), + ("ed", "ed@lala.com"), + ("fred", "fred@fred.com"), + ], + ) + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.join("addresses") + .filter(User.name.like("%e%")) + .order_by(desc(Address.email_address)) + .slice(1, 3) + .values(User.name, Address.email_address) + ) + eq_(list(q2), [("ed", "ed@wood.com"), ("ed", "ed@lala.com")]) + + adalias = aliased(Address) + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.join(adalias, "addresses") + .filter(User.name.like("%e%")) + .order_by(adalias.email_address) + .values(User.name, adalias.email_address) + ) + eq_( + list(q2), + [ + ("ed", "ed@bettyboop.com"), + ("ed", "ed@lala.com"), + ("ed", "ed@wood.com"), + ("fred", "fred@fred.com"), + ], + ) + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = q.values(func.count(User.name)) + assert next(q2) == (4,) + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.select_entity_from(sel) + .filter(User.id == 8) + .values(User.name, sel.c.name, User.name) + ) + eq_(list(q2), [("ed", "ed", "ed")]) + + # using User.xxx is alised against "sel", so this query returns nothing + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.select_entity_from(sel) + .filter(User.id == 8) + .filter(User.id > sel.c.id) + .values(User.name, sel.c.name, User.name) + ) + eq_(list(q2), []) + + # whereas this uses users.c.xxx, is not aliased and creates a new join + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.select_entity_from(sel) + .filter(users.c.id == 8) + .filter(users.c.id > sel.c.id) + .values(users.c.name, sel.c.name, User.name) + ) + eq_(list(q2), [("ed", "jack", "jack")]) + + @testing.fails_on("mssql", "FIXME: unknown") + def test_values_specific_order_by(self): + users, User = self.tables.users, self.classes.User + + sess = create_session() + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + assert list(sess.query(User).values()) == list() + + sel = users.select(User.id.in_([7, 8])).alias() + q = sess.query(User) + u2 = aliased(User) + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.select_entity_from(sel) + .filter(u2.id > 1) + .filter(or_(u2.id == User.id, u2.id != User.id)) + .order_by(User.id, sel.c.id, u2.id) + .values(User.name, sel.c.name, u2.name) + ) + eq_( + list(q2), + [ + ("jack", "jack", "jack"), + ("jack", "jack", "ed"), + ("jack", "jack", "fred"), + ("jack", "jack", "chuck"), + ("ed", "ed", "jack"), + ("ed", "ed", "ed"), + ("ed", "ed", "fred"), + ("ed", "ed", "chuck"), + ], + ) + + @testing.fails_on("mssql", "FIXME: unknown") + @testing.fails_on( + "oracle", "Oracle doesn't support boolean expressions as " "columns" + ) + @testing.fails_on( + "postgresql+pg8000", + "pg8000 parses the SQL itself before passing on " + "to PG, doesn't parse this", + ) + @testing.fails_on("firebird", "unknown") + def test_values_with_boolean_selects(self): + """Tests a values clause that works with select boolean + evaluations""" + + User = self.classes.User + + sess = create_session() + + q = sess.query(User) + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = ( + q.group_by(User.name.like("%j%")) + .order_by(desc(User.name.like("%j%"))) + .values( + User.name.like("%j%"), func.count(User.name.like("%j%")) + ) + ) + eq_(list(q2), [(True, 1), (False, 3)]) + + with testing.expect_deprecated(r"Query.values?\(\) is deprecated"): + q2 = q.order_by(desc(User.name.like("%j%"))).values( + User.name.like("%j%") + ) + eq_(list(q2), [(True,), (False,), (False,), (False,)]) diff --git a/test/orm/test_dynamic.py b/test/orm/test_dynamic.py index 1ca1bec03..d15a9d7cc 100644 --- a/test/orm/test_dynamic.py +++ b/test/orm/test_dynamic.py @@ -85,6 +85,16 @@ class DynamicTest(_DynamicFixture, _fixtures.FixtureTest, AssertsCompiledSQL): ) eq_(self.static.user_address_result, q.all()) + eq_( + [ + User( + id=7, + addresses=[Address(id=1, email_address="jack@bean.com")], + ) + ], + q.filter_by(id=7).all(), + ) + def test_statement(self): """test that the .statement accessor returns the actual statement that would render, without any _clones called.""" diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py index 3a6810d80..abd48d7c0 100644 --- a/test/orm/test_eager_relations.py +++ b/test/orm/test_eager_relations.py @@ -631,13 +631,10 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): self.assert_sql_count(testing.db, go, 1) def go(): + ka = aliased(Keyword) eq_( self.static.item_keyword_result[0:2], - ( - q.join("keywords", aliased=True).filter( - Keyword.name == "red" - ) - ).all(), + (q.join(ka, "keywords").filter(ka.name == "red")).all(), ) self.assert_sql_count(testing.db, go, 1) @@ -5489,7 +5486,7 @@ class EntityViaMultiplePathTestTwo(fixtures.DeclarativeMappedTest): a = relationship(A, primaryjoin=a_id == A.id) ld = relationship(LD, primaryjoin=ld_id == LD.id) - def test_multi_path_load(self): + def test_multi_path_load_legacy_join_style(self): User, LD, A, LDA = self.classes("User", "LD", "A", "LDA") s = Session() diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py index 08b59ce67..4b20dfca6 100644 --- a/test/orm/test_froms.py +++ b/test/orm/test_froms.py @@ -1,7 +1,6 @@ import sqlalchemy as sa from sqlalchemy import and_ from sqlalchemy import asc -from sqlalchemy import cast from sqlalchemy import desc from sqlalchemy import exc as sa_exc from sqlalchemy import exists @@ -9,21 +8,21 @@ from sqlalchemy import ForeignKey from sqlalchemy import func from sqlalchemy import Integer from sqlalchemy import literal_column -from sqlalchemy import or_ from sqlalchemy import select from sqlalchemy import String from sqlalchemy import Table from sqlalchemy import testing from sqlalchemy import text from sqlalchemy import true +from sqlalchemy import union from sqlalchemy import util from sqlalchemy.engine import default +from sqlalchemy.future import select as future_select from sqlalchemy.orm import aliased from sqlalchemy.orm import backref from sqlalchemy.orm import clear_mappers from sqlalchemy.orm import column_property from sqlalchemy.orm import configure_mappers -from sqlalchemy.orm import contains_alias from sqlalchemy.orm import contains_eager from sqlalchemy.orm import create_session from sqlalchemy.orm import joinedload @@ -275,6 +274,29 @@ class QueryCorrelatesLikeSelect(QueryTest, AssertsCompiledSQL): "FROM users) AS anon_1", ) + def test_correlate_to_union_newstyle(self): + User = self.classes.User + + q = future_select(User).apply_labels() + + q = future_select(User).union(q).apply_labels().subquery() + + u_alias = aliased(User) + + raw_subq = exists().where(u_alias.id > q.c[0]) + + self.assert_compile( + future_select(q, raw_subq).apply_labels(), + "SELECT anon_1.users_id AS anon_1_users_id, " + "anon_1.users_name AS anon_1_users_name, " + "EXISTS (SELECT * FROM users AS users_1 " + "WHERE users_1.id > anon_1.users_id) AS anon_2 " + "FROM (" + "SELECT users.id AS users_id, users.name AS users_name FROM users " + "UNION SELECT users.id AS users_id, users.name AS users_name " + "FROM users) AS anon_1", + ) + class RawSelectTest(QueryTest, AssertsCompiledSQL): """compare a bunch of select() tests with the equivalent Query using @@ -536,7 +558,8 @@ class FromSelfTest(QueryTest, AssertsCompiledSQL): ), ) - def test_multiple_with_column_entities(self): + def test_multiple_with_column_entities_oldstyle(self): + # relies upon _orm_only_from_obj_alias setting User = self.classes.User sess = create_session() @@ -552,6 +575,26 @@ class FromSelfTest(QueryTest, AssertsCompiledSQL): [(7, 1), (8, 1), (9, 1), (10, 1)], ) + def test_multiple_with_column_entities_newstyle(self): + User = self.classes.User + + sess = create_session() + + q1 = sess.query(User.id) + + subq1 = aliased(User, q1.subquery()) + + q2 = sess.query(subq1.id).add_columns(func.count().label("foo")) + q2 = q2.group_by(subq1.id).order_by(subq1.id).subquery() + + q3 = sess.query(q2) + eq_( + q3.all(), [(7, 1), (8, 1), (9, 1), (10, 1)], + ) + + q3 = future_select(q2) + eq_(sess.execute(q3).fetchall(), [(7, 1), (8, 1), (9, 1), (10, 1)]) + class ColumnAccessTest(QueryTest, AssertsCompiledSQL): """test access of columns after _from_selectable has been applied""" @@ -607,8 +650,7 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): sa.exc.ArgumentError, r"A selectable \(FromClause\) instance is " "expected when the base alias is being set", - sess.query(User).select_entity_from, - User, + sess.query(User).select_entity_from(User)._compile_context, ) def test_select_from_no_aliasing(self): @@ -624,7 +666,8 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): "users) AS anon_1 WHERE users.name = :name_1", ) - def test_anonymous_expression(self): + def test_anonymous_expression_oldstyle(self): + # relies upon _orm_only_from_obj_alias setting from sqlalchemy.sql import column sess = create_session() @@ -640,39 +683,25 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.c1", ) - def test_anonymous_expression_from_self_twice(self): + def test_anonymous_expression_newstyle(self): from sqlalchemy.sql import column - sess = create_session() c1, c2 = column("c1"), column("c2") - q1 = sess.query(c1, c2).filter(c1 == "dog") - q1 = q1.from_self().from_self() - self.assert_compile( - q1.order_by(c1), - "SELECT anon_1.anon_2_c1 AS anon_1_anon_2_c1, anon_1.anon_2_c2 AS " - "anon_1_anon_2_c2 FROM (SELECT anon_2.c1 AS anon_2_c1, anon_2.c2 " - "AS anon_2_c2 " - "FROM (SELECT c1, c2 WHERE c1 = :c1_1) AS " - "anon_2) AS anon_1 ORDER BY anon_1.anon_2_c1", - ) - - def test_anonymous_expression_union(self): - from sqlalchemy.sql import column + q1 = future_select(c1, c2).where(c1 == "dog") + q2 = future_select(c1, c2).where(c1 == "cat") + subq = q1.union(q2).subquery() + q3 = future_select(subq).apply_labels() - sess = create_session() - c1, c2 = column("c1"), column("c2") - q1 = sess.query(c1, c2).filter(c1 == "dog") - q2 = sess.query(c1, c2).filter(c1 == "cat") - q3 = q1.union(q2) self.assert_compile( - q3.order_by(c1), + q3.order_by(subq.c.c1), "SELECT anon_1.c1 AS anon_1_c1, anon_1.c2 " "AS anon_1_c2 FROM (SELECT c1, c2 WHERE " "c1 = :c1_1 UNION SELECT c1, c2 " "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.c1", ) - def test_table_anonymous_expression_from_self_twice(self): + def test_table_anonymous_expression_from_self_twice_oldstyle(self): + # relies upon _orm_only_from_obj_alias setting from sqlalchemy.sql import column sess = create_session() @@ -690,7 +719,99 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): "ORDER BY anon_1.anon_2_t1_c1", ) - def test_anonymous_labeled_expression(self): + def test_table_anonymous_expression_from_self_twice_newstyle(self): + from sqlalchemy.sql import column + + t1 = table("t1", column("c1"), column("c2")) + stmt = ( + future_select(t1.c.c1, t1.c.c2) + .where(t1.c.c1 == "dog") + .apply_labels() + ) + + subq1 = stmt.subquery("anon_2").select().apply_labels() + + subq2 = subq1.subquery("anon_1") + + q1 = future_select(subq2).apply_labels() + + self.assert_compile( + # as in test_anonymous_expression_from_self_twice_newstyle_wlabels, + # apply_labels() means the subquery cols have long names. however, + # here we illustrate if they did use apply_labels(), but they also + # named the subqueries explicitly as one would certainly do if they + # were using apply_labels(), we can get at that column based on how + # it is aliased, no different than plain SQL. + q1.order_by(subq2.c.anon_2_t1_c1), + "SELECT anon_1.anon_2_t1_c1 " + "AS anon_1_anon_2_t1_c1, anon_1.anon_2_t1_c2 " + "AS anon_1_anon_2_t1_c2 " + "FROM (SELECT anon_2.t1_c1 AS anon_2_t1_c1, " + "anon_2.t1_c2 AS anon_2_t1_c2 FROM (SELECT t1.c1 AS t1_c1, t1.c2 " + "AS t1_c2 FROM t1 WHERE t1.c1 = :c1_1) AS anon_2) AS anon_1 " + "ORDER BY anon_1.anon_2_t1_c1", + ) + + def test_anonymous_expression_from_self_twice_oldstyle(self): + # relies upon _orm_only_from_obj_alias setting + from sqlalchemy.sql import column + + sess = create_session() + c1, c2 = column("c1"), column("c2") + q1 = sess.query(c1, c2).filter(c1 == "dog") + q1 = q1.from_self().from_self() + self.assert_compile( + q1.order_by(c1), + "SELECT anon_1.anon_2_c1 AS anon_1_anon_2_c1, anon_1.anon_2_c2 AS " + "anon_1_anon_2_c2 FROM (SELECT anon_2.c1 AS anon_2_c1, anon_2.c2 " + "AS anon_2_c2 " + "FROM (SELECT c1, c2 WHERE c1 = :c1_1) AS " + "anon_2) AS anon_1 ORDER BY anon_1.anon_2_c1", + ) + + def test_anonymous_expression_from_self_twice_newstyle_wlabels(self): + from sqlalchemy.sql import column + + c1, c2 = column("c1"), column("c2") + subq = future_select(c1, c2).where(c1 == "dog").subquery() + + subq2 = future_select(subq).apply_labels().subquery() + + stmt = future_select(subq2).apply_labels() + + self.assert_compile( + # because of the apply labels we don't have simple keys on + # subq2.c + stmt.order_by(subq2.c.corresponding_column(c1)), + "SELECT anon_1.anon_2_c1 AS anon_1_anon_2_c1, anon_1.anon_2_c2 AS " + "anon_1_anon_2_c2 FROM (SELECT anon_2.c1 AS anon_2_c1, anon_2.c2 " + "AS anon_2_c2 " + "FROM (SELECT c1, c2 WHERE c1 = :c1_1) AS " + "anon_2) AS anon_1 ORDER BY anon_1.anon_2_c1", + ) + + def test_anonymous_expression_from_self_twice_newstyle_wolabels(self): + from sqlalchemy.sql import column + + c1, c2 = column("c1"), column("c2") + subq = future_select(c1, c2).where(c1 == "dog").subquery() + + subq2 = future_select(subq).subquery() + + stmt = future_select(subq2) + + self.assert_compile( + # without labels we can access .c1 but the statement will not + # have the same labeling applied (which does not matter) + stmt.order_by(subq2.c.c1), + "SELECT anon_1.c1, anon_1.c2 FROM " + "(SELECT anon_2.c1 AS c1, anon_2.c2 AS c2 " + "FROM (SELECT c1, c2 WHERE c1 = :c1_1) AS " + "anon_2) AS anon_1 ORDER BY anon_1.c1", + ) + + def test_anonymous_labeled_expression_oldstyle(self): + # relies upon _orm_only_from_obj_alias setting sess = create_session() c1, c2 = column("c1"), column("c2") q1 = sess.query(c1.label("foo"), c2.label("bar")).filter(c1 == "dog") @@ -704,7 +825,21 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.foo", ) - def test_anonymous_expression_plus_aliased_join(self): + def test_anonymous_labeled_expression_newstyle(self): + c1, c2 = column("c1"), column("c2") + q1 = future_select(c1.label("foo"), c2.label("bar")).where(c1 == "dog") + q2 = future_select(c1.label("foo"), c2.label("bar")).where(c1 == "cat") + subq = union(q1, q2).subquery() + q3 = future_select(subq).apply_labels() + self.assert_compile( + q3.order_by(subq.c.foo), + "SELECT anon_1.foo AS anon_1_foo, anon_1.bar AS anon_1_bar FROM " + "(SELECT c1 AS foo, c2 AS bar WHERE c1 = :c1_1 UNION SELECT " + "c1 AS foo, c2 AS bar " + "WHERE c1 = :c1_2) AS anon_1 ORDER BY anon_1.foo", + ) + + def test_anonymous_expression_plus_flag_aliased_join(self): """test that the 'dont alias non-ORM' rule remains for other kinds of aliasing when _from_selectable() is used.""" @@ -715,9 +850,36 @@ class ColumnAccessTest(QueryTest, AssertsCompiledSQL): sess = create_session() q1 = sess.query(User.id).filter(User.id > 5) q1 = q1.from_self() + q1 = q1.join(User.addresses, aliased=True).order_by( User.id, Address.id, addresses.c.id ) + + self.assert_compile( + q1, + "SELECT anon_1.users_id AS anon_1_users_id " + "FROM (SELECT users.id AS users_id FROM users " + "WHERE users.id > :id_1) AS anon_1 JOIN addresses AS addresses_1 " + "ON anon_1.users_id = addresses_1.user_id " + "ORDER BY anon_1.users_id, addresses_1.id, addresses.id", + ) + + def test_anonymous_expression_plus_explicit_aliased_join(self): + """test that the 'dont alias non-ORM' rule remains for other + kinds of aliasing when _from_selectable() is used.""" + + User = self.classes.User + Address = self.classes.Address + addresses = self.tables.addresses + + sess = create_session() + q1 = sess.query(User.id).filter(User.id > 5) + q1 = q1.from_self() + + aa = aliased(Address) + q1 = q1.join(aa, User.addresses).order_by( + User.id, aa.id, addresses.c.id + ) self.assert_compile( q1, "SELECT anon_1.users_id AS anon_1_users_id " @@ -855,7 +1017,7 @@ class AddEntityEquivalenceTest(fixtures.MappedTest, AssertsCompiledSQL): class InstancesTest(QueryTest, AssertsCompiledSQL): - def test_from_alias_two(self): + def test_from_alias_two_needs_nothing(self): User, addresses, users = ( self.classes.User, self.tables.addresses, @@ -876,7 +1038,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): def go(): result = ( - q.options(contains_alias("ulist"), contains_eager("addresses")) + q.options(contains_eager("addresses")) .from_statement(query) .all() ) @@ -884,6 +1046,36 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): self.assert_sql_count(testing.db, go, 1) + def test_from_alias_two(self): + User, addresses, users = ( + self.classes.User, + self.tables.addresses, + self.tables.users, + ) + + query = ( + users.select(users.c.id == 7) + .union(users.select(users.c.id > 7)) + .alias("ulist") + .outerjoin(addresses) + .select( + use_labels=True, order_by=[text("ulist.id"), addresses.c.id] + ) + ) + sess = create_session() + q = sess.query(User) + + def go(): + ulist_alias = aliased(User, alias=query.alias("ulist")) + result = ( + q.options(contains_eager("addresses")) + .select_entity_from(ulist_alias) + .all() + ) + assert self.static.user_address_result == result + + self.assert_sql_count(testing.db, go, 1) + def test_from_alias_three(self): User, addresses, users = ( self.classes.User, @@ -1357,106 +1549,6 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): __dialect__ = "default" - def test_values(self): - Address, users, User = ( - self.classes.Address, - self.tables.users, - self.classes.User, - ) - - sess = create_session() - - assert list(sess.query(User).values()) == list() - - sel = users.select(User.id.in_([7, 8])).alias() - q = sess.query(User) - q2 = q.select_entity_from(sel).values(User.name) - eq_(list(q2), [("jack",), ("ed",)]) - - q = sess.query(User) - q2 = q.order_by(User.id).values( - User.name, User.name + " " + cast(User.id, String(50)) - ) - eq_( - list(q2), - [ - ("jack", "jack 7"), - ("ed", "ed 8"), - ("fred", "fred 9"), - ("chuck", "chuck 10"), - ], - ) - - q2 = ( - q.join("addresses") - .filter(User.name.like("%e%")) - .order_by(User.id, Address.id) - .values(User.name, Address.email_address) - ) - eq_( - list(q2), - [ - ("ed", "ed@wood.com"), - ("ed", "ed@bettyboop.com"), - ("ed", "ed@lala.com"), - ("fred", "fred@fred.com"), - ], - ) - - q2 = ( - q.join("addresses") - .filter(User.name.like("%e%")) - .order_by(desc(Address.email_address)) - .slice(1, 3) - .values(User.name, Address.email_address) - ) - eq_(list(q2), [("ed", "ed@wood.com"), ("ed", "ed@lala.com")]) - - adalias = aliased(Address) - q2 = ( - q.join(adalias, "addresses") - .filter(User.name.like("%e%")) - .order_by(adalias.email_address) - .values(User.name, adalias.email_address) - ) - eq_( - list(q2), - [ - ("ed", "ed@bettyboop.com"), - ("ed", "ed@lala.com"), - ("ed", "ed@wood.com"), - ("fred", "fred@fred.com"), - ], - ) - - q2 = q.values(func.count(User.name)) - assert next(q2) == (4,) - - q2 = ( - q.select_entity_from(sel) - .filter(User.id == 8) - .values(User.name, sel.c.name, User.name) - ) - eq_(list(q2), [("ed", "ed", "ed")]) - - # using User.xxx is alised against "sel", so this query returns nothing - q2 = ( - q.select_entity_from(sel) - .filter(User.id == 8) - .filter(User.id > sel.c.id) - .values(User.name, sel.c.name, User.name) - ) - eq_(list(q2), []) - - # whereas this uses users.c.xxx, is not aliased and creates a new join - q2 = ( - q.select_entity_from(sel) - .filter(users.c.id == 8) - .filter(users.c.id > sel.c.id) - .values(users.c.name, sel.c.name, User.name) - ) - eq_(list(q2), [("ed", "jack", "jack")]) - def test_alias_naming(self): User = self.classes.User @@ -1470,69 +1562,6 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): "foobar.name AS foobar_name FROM users AS foobar", ) - @testing.fails_on("mssql", "FIXME: unknown") - def test_values_specific_order_by(self): - users, User = self.tables.users, self.classes.User - - sess = create_session() - - assert list(sess.query(User).values()) == list() - - sel = users.select(User.id.in_([7, 8])).alias() - q = sess.query(User) - u2 = aliased(User) - q2 = ( - q.select_entity_from(sel) - .filter(u2.id > 1) - .filter(or_(u2.id == User.id, u2.id != User.id)) - .order_by(User.id, sel.c.id, u2.id) - .values(User.name, sel.c.name, u2.name) - ) - eq_( - list(q2), - [ - ("jack", "jack", "jack"), - ("jack", "jack", "ed"), - ("jack", "jack", "fred"), - ("jack", "jack", "chuck"), - ("ed", "ed", "jack"), - ("ed", "ed", "ed"), - ("ed", "ed", "fred"), - ("ed", "ed", "chuck"), - ], - ) - - @testing.fails_on("mssql", "FIXME: unknown") - @testing.fails_on( - "oracle", "Oracle doesn't support boolean expressions as " "columns" - ) - @testing.fails_on( - "postgresql+pg8000", - "pg8000 parses the SQL itself before passing on " - "to PG, doesn't parse this", - ) - @testing.fails_on("firebird", "unknown") - def test_values_with_boolean_selects(self): - """Tests a values clause that works with select boolean - evaluations""" - - User = self.classes.User - - sess = create_session() - - q = sess.query(User) - q2 = ( - q.group_by(User.name.like("%j%")) - .order_by(desc(User.name.like("%j%"))) - .values(User.name.like("%j%"), func.count(User.name.like("%j%"))) - ) - eq_(list(q2), [(True, 1), (False, 3)]) - - q2 = q.order_by(desc(User.name.like("%j%"))).values( - User.name.like("%j%") - ) - eq_(list(q2), [(True,), (False,), (False,), (False,)]) - def test_correlated_subquery(self): """test that a subquery constructed from ORM attributes doesn't leak out those entities to the outermost query.""" @@ -1580,12 +1609,8 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) - def test_column_queries(self): - Address, users, User = ( - self.classes.Address, - self.tables.users, - self.classes.User, - ) + def test_column_queries_one(self): + User = self.classes.User sess = create_session() @@ -1594,11 +1619,25 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): [("jack",), ("ed",), ("fred",), ("chuck",)], ) + def test_column_queries_two(self): + users, User = ( + self.tables.users, + self.classes.User, + ) + + sess = create_session() sel = users.select(User.id.in_([7, 8])).alias() q = sess.query(User.name) q2 = q.select_entity_from(sel).all() eq_(list(q2), [("jack",), ("ed",)]) + def test_column_queries_three(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() eq_( sess.query(User.name, Address.email_address) .filter(User.id == Address.user_id) @@ -1612,6 +1651,13 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_column_queries_four(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() eq_( sess.query(User.name, func.count(Address.email_address)) .outerjoin(User.addresses) @@ -1621,6 +1667,13 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): [("jack", 1), ("ed", 3), ("fred", 1), ("chuck", 0)], ) + def test_column_queries_five(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() eq_( sess.query(User, func.count(Address.email_address)) .outerjoin(User.addresses) @@ -1635,6 +1688,13 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_column_queries_six(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() eq_( sess.query(func.count(Address.email_address), User) .outerjoin(User.addresses) @@ -1649,6 +1709,13 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_column_queries_seven(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() adalias = aliased(Address) eq_( sess.query(User, func.count(adalias.email_address)) @@ -1664,6 +1731,14 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_column_queries_eight(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() + adalias = aliased(Address) eq_( sess.query(func.count(adalias.email_address), User) .outerjoin(adalias, User.addresses) @@ -1678,6 +1753,15 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_column_queries_nine(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() + + adalias = aliased(Address) # select from aliasing + explicit aliasing eq_( sess.query(User, adalias.email_address, adalias.id) @@ -1695,16 +1779,34 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_column_queries_ten(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() + # anon + select from aliasing + aa = aliased(Address) eq_( sess.query(User) - .join(User.addresses, aliased=True) - .filter(Address.email_address.like("%ed%")) + .join(aa, User.addresses) + .filter(aa.email_address.like("%ed%")) .from_self() .all(), [User(name="ed", id=8), User(name="fred", id=9)], ) + def test_column_queries_eleven(self): + Address, User = ( + self.classes.Address, + self.classes.User, + ) + + sess = create_session() + + adalias = aliased(Address) # test eager aliasing, with/without select_entity_from aliasing for q in [ sess.query(User, adalias.email_address) @@ -1945,6 +2047,12 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ], ) + def test_from_self_internal_literals_oldstyle(self): + # relies upon _orm_only_from_obj_alias setting + Order = self.classes.Order + + sess = create_session() + # ensure column expressions are taken from inside the subquery, not # restated at the top q = ( @@ -1957,8 +2065,8 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): self.assert_compile( q, "SELECT anon_1.orders_id AS " - "anon_1_orders_id, anon_1.orders_descriptio" - "n AS anon_1_orders_description, " + "anon_1_orders_id, " + "anon_1.orders_description AS anon_1_orders_description, " "anon_1.foo AS anon_1_foo FROM (SELECT " "orders.id AS orders_id, " "orders.description AS orders_description, " @@ -1968,6 +2076,29 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ) eq_(q.all(), [(3, "order 3", "q")]) + def test_from_self_internal_literals_newstyle(self): + Order = self.classes.Order + + stmt = future_select( + Order.id, Order.description, literal_column("'q'").label("foo") + ).where(Order.description == "order 3") + + subq = aliased(Order, stmt.apply_labels().subquery()) + + stmt = future_select(subq).apply_labels() + self.assert_compile( + stmt, + "SELECT anon_1.orders_id AS " + "anon_1_orders_id, " + "anon_1.orders_description AS anon_1_orders_description " + "FROM (SELECT " + "orders.id AS orders_id, " + "orders.description AS orders_description, " + "'q' AS foo FROM orders WHERE " + "orders.description = :description_1) AS " + "anon_1", + ) + def test_multi_mappers(self): Address, addresses, users, User = ( self.classes.Address, @@ -2302,8 +2433,8 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ), ]: q = s.query(crit) - mzero = q._entity_zero() - is_(mzero, q._query_entity_zero().entity_zero) + mzero = q._compile_state()._entity_zero() + is_(mzero, q._compile_state()._entities[0].entity_zero) q = q.join(j) self.assert_compile(q, exp) @@ -2332,8 +2463,8 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): ), ]: q = s.query(crit) - mzero = q._entity_zero() - is_(mzero, q._query_entity_zero().entity_zero) + mzero = q._compile_state()._entity_zero() + is_(mzero, q._compile_state()._entities[0].entity_zero) q = q.join(j) self.assert_compile(q, exp) @@ -2817,16 +2948,7 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): eq_( sess.query(User) .select_entity_from(sel.subquery()) - .join("orders", "items", "keywords") - .filter(Keyword.name.in_(["red", "big", "round"])) - .all(), - [User(name="jack", id=7)], - ) - - eq_( - sess.query(User) - .select_entity_from(sel.subquery()) - .join("orders", "items", "keywords", aliased=True) + .join(User.orders, Order.items, Item.keywords) .filter(Keyword.name.in_(["red", "big", "round"])) .all(), [User(name="jack", id=7)], @@ -2895,7 +3017,7 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): .joinedload("items") .joinedload("keywords") ) - .join("orders", "items", "keywords", aliased=True) + .join(User.orders, Order.items, Item.keywords) .filter(Keyword.name.in_(["red", "big", "round"])) .all(), [ @@ -2968,19 +3090,8 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): eq_( sess.query(Order) .select_entity_from(sel2.subquery()) - .join("items", "keywords") - .filter(Keyword.name == "red") - .order_by(Order.id) - .all(), - [ - Order(description="order 1", id=1), - Order(description="order 2", id=2), - ], - ) - eq_( - sess.query(Order) - .select_entity_from(sel2.subquery()) - .join("items", "keywords", aliased=True) + .join(Order.items) + .join(Item.keywords) .filter(Keyword.name == "red") .order_by(Order.id) .all(), @@ -3074,7 +3185,7 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): class CustomJoinTest(QueryTest): run_setup_mappers = None - def test_double_same_mappers(self): + def test_double_same_mappers_flag_alias(self): """test aliasing of joins with a custom join condition""" ( @@ -3147,6 +3258,91 @@ class CustomJoinTest(QueryTest): [User(id=7)], ) + def test_double_same_mappers_explicit_alias(self): + """test aliasing of joins with a custom join condition""" + + ( + addresses, + items, + order_items, + orders, + Item, + User, + Address, + Order, + users, + ) = ( + self.tables.addresses, + self.tables.items, + self.tables.order_items, + self.tables.orders, + self.classes.Item, + self.classes.User, + self.classes.Address, + self.classes.Order, + self.tables.users, + ) + + mapper(Address, addresses) + mapper( + Order, + orders, + properties={ + "items": relationship( + Item, + secondary=order_items, + lazy="select", + order_by=items.c.id, + ) + }, + ) + mapper(Item, items) + mapper( + User, + users, + properties=dict( + addresses=relationship(Address, lazy="select"), + open_orders=relationship( + Order, + primaryjoin=and_( + orders.c.isopen == 1, users.c.id == orders.c.user_id + ), + lazy="select", + viewonly=True, + ), + closed_orders=relationship( + Order, + primaryjoin=and_( + orders.c.isopen == 0, users.c.id == orders.c.user_id + ), + lazy="select", + viewonly=True, + ), + ), + ) + q = create_session().query(User) + + oo = aliased(Order) + co = aliased(Order) + oi = aliased(Item) + ci = aliased(Item) + + # converted from aliased=True. This is kind of the worst case + # kind of query when we don't have aliased=True. two different + # styles are illustrated here, but the important point is that + # the filter() is not doing any trickery, you need to pass it the + # aliased entity explicitly. + eq_( + q.join(oo, User.open_orders) + .join(oi, oo.items) + .filter(oi.id == 4) + .join(User.closed_orders.of_type(co)) + .join(co.items.of_type(ci)) + .filter(ci.id == 3) + .all(), + [User(id=7)], + ) + class ExternalColumnsTest(QueryTest): """test mappers with SQL-expressions added as column properties.""" @@ -3239,10 +3435,11 @@ class ExternalColumnsTest(QueryTest): [(address, address.user) for address in address_result], ) + ualias2 = aliased(User) eq_( sess.query(Address, ualias.count) .join(ualias, "user") - .join("user", aliased=True) + .join(ualias2, "user") .order_by(Address.id) .all(), [ @@ -3257,7 +3454,7 @@ class ExternalColumnsTest(QueryTest): eq_( sess.query(Address, ualias.concat, ualias.count) .join(ualias, "user") - .join("user", aliased=True) + .join(ualias2, "user") .order_by(Address.id) .all(), [ @@ -3289,7 +3486,7 @@ class ExternalColumnsTest(QueryTest): list( sess.query(Address) .join("user") - .values(Address.id, User.id, User.concat, User.count) + .with_entities(Address.id, User.id, User.concat, User.count) ), [ (1, 7, 14, 1), @@ -3304,7 +3501,7 @@ class ExternalColumnsTest(QueryTest): list( sess.query(Address, ua) .select_entity_from(join(Address, ua, "user")) - .values(Address.id, ua.id, ua.concat, ua.count) + .with_entities(Address.id, ua.id, ua.concat, ua.count) ), [ (1, 7, 14, 1), @@ -3476,30 +3673,12 @@ class TestOverlyEagerEquivalentCols(fixtures.MappedTest): s11 = Sub1(data="s11") s12 = Sub1(data="s12") - s2 = Sub2(data="s2") b1 = Base(data="b1", sub1=[s11], sub2=[]) b2 = Base(data="b1", sub1=[s12], sub2=[]) sess.add(b1) sess.add(b2) sess.flush() - # there's an overlapping ForeignKey here, so not much option except - # to artificially control the flush order - b2.sub2 = [s2] - sess.flush() - - q = sess.query(Base).outerjoin("sub2", aliased=True) - assert sub1.c.id not in q._filter_aliases[0].equivalents - - eq_( - sess.query(Base) - .join("sub1") - .outerjoin("sub2", aliased=True) - .filter(Sub1.id == 1) - .one(), - b1, - ) - class LabelCollideTest(fixtures.MappedTest): """Test handling for a label collision. This collision diff --git a/test/orm/test_generative.py b/test/orm/test_generative.py index 97106cafc..c5cf4a2c0 100644 --- a/test/orm/test_generative.py +++ b/test/orm/test_generative.py @@ -86,17 +86,12 @@ class GenerativeQueryTest(fixtures.MappedTest): assert sess.query(func.max(foo.c.bar)).filter( foo.c.bar < 30 ).one() == (29,) - assert ( - next(query.filter(foo.c.bar < 30).values(sa.func.max(foo.c.bar)))[ - 0 - ] - == 29 - ) - assert ( - next(query.filter(foo.c.bar < 30).values(sa.func.max(foo.c.bar)))[ - 0 - ] - == 29 + + eq_( + query.filter(foo.c.bar < 30) + .with_entities(sa.func.max(foo.c.bar)) + .scalar(), + 29, ) @testing.fails_if( @@ -131,15 +126,19 @@ class GenerativeQueryTest(fixtures.MappedTest): query = create_session().query(Foo) - avg_f = next( - query.filter(foo.c.bar < 30).values(sa.func.avg(foo.c.bar)) - )[0] - assert float(round(avg_f, 1)) == 14.5 + avg_f = ( + query.filter(foo.c.bar < 30) + .with_entities(sa.func.avg(foo.c.bar)) + .scalar() + ) + eq_(float(round(avg_f, 1)), 14.5) - avg_o = next( - query.filter(foo.c.bar < 30).values(sa.func.avg(foo.c.bar)) - )[0] - assert float(round(avg_o, 1)) == 14.5 + avg_o = ( + query.filter(foo.c.bar < 30) + .with_entities(sa.func.avg(foo.c.bar)) + .scalar() + ) + eq_(float(round(avg_o, 1)), 14.5) def test_filter(self): Foo = self.classes.Foo diff --git a/test/orm/test_joins.py b/test/orm/test_joins.py index 1895a41e8..300670a70 100644 --- a/test/orm/test_joins.py +++ b/test/orm/test_joins.py @@ -586,6 +586,24 @@ class JoinOnSynonymTest(_fixtures.FixtureTest, AssertsCompiledSQL): class JoinTest(QueryTest, AssertsCompiledSQL): __dialect__ = "default" + def test_foo(self): + User = self.classes.User + + sess = create_session() + + # test overlapping paths. User->orders is used by both joins, but + # rendered once. + self.assert_compile( + sess.query(User).join("orders", "items").join("orders", "address"), + "SELECT users.id AS users_id, users.name AS users_name FROM users " + "JOIN orders " + "ON users.id = orders.user_id " + "JOIN order_items AS order_items_1 " + "ON orders.id = order_items_1.order_id " + "JOIN items ON items.id = order_items_1.item_id JOIN addresses " + "ON addresses.id = orders.address_id", + ) + def test_single_name(self): User = self.classes.User @@ -598,7 +616,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): ) assert_raises( - sa_exc.InvalidRequestError, sess.query(User).join, "user" + sa_exc.InvalidRequestError, + sess.query(User).join("user")._compile_context, ) self.assert_compile( @@ -623,6 +642,23 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "ON addresses.id = orders.address_id", ) + def test_filter_by_from_full_join(self): + User, Address = self.classes("User", "Address") + + sess = create_session() + + q = ( + sess.query(User) + .join(Address, User.addresses) + .filter_by(email_address="foo") + ) + self.assert_compile( + q, + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN addresses ON users.id = addresses.user_id " + "WHERE addresses.email_address = :email_address_1", + ) + def test_invalid_kwarg_join(self): User = self.classes.User sess = create_session() @@ -670,8 +706,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): sa_exc.InvalidRequestError, r"No entities to join from; please use select_from\(\) to " r"establish the left entity/selectable of this join", - sess.query().join, - Address, + sess.query().join(Address)._compile_context, ) def test_isouter_flag(self): @@ -692,51 +727,138 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "FROM users FULL OUTER JOIN orders ON users.id = orders.user_id", ) - def test_multi_tuple_form(self): + def test_multi_tuple_form_legacy_one(self): """test the 'tuple' form of join, now superseded by the two-element join() form. - Not deprecating this style as of yet. """ - Item, Order, User = ( - self.classes.Item, + Order, User = ( self.classes.Order, self.classes.User, ) sess = create_session() - # assert_raises( - # sa.exc.SADeprecationWarning, - # sess.query(User).join, (Order, User.id==Order.user_id) - # ) - + q = ( + sess.query(User) + .join((Order, User.id == Order.user_id)) + .filter_by(description="foo") + ) self.assert_compile( - sess.query(User).join((Order, User.id == Order.user_id)), + q, "SELECT users.id AS users_id, users.name AS users_name " - "FROM users JOIN orders ON users.id = orders.user_id", + "FROM users JOIN orders ON users.id = orders.user_id " + "WHERE orders.description = :description_1", + ) + + def test_multi_tuple_form_legacy_two(self): + """test the 'tuple' form of join, now superseded + by the two-element join() form. + + + """ + + Item, Order, User = ( + self.classes.Item, + self.classes.Order, + self.classes.User, ) + sess = create_session() + + q = ( + sess.query(User) + .join((Order, User.id == Order.user_id), (Item, Order.items)) + .filter_by(description="foo") + ) self.assert_compile( - sess.query(User).join( - (Order, User.id == Order.user_id), (Item, Order.items) - ), + q, "SELECT users.id AS users_id, users.name AS users_name " "FROM users JOIN orders ON users.id = orders.user_id " "JOIN order_items AS order_items_1 ON orders.id = " "order_items_1.order_id JOIN items ON items.id = " - "order_items_1.item_id", + "order_items_1.item_id WHERE items.description = :description_1", + ) + + def test_multi_tuple_form_legacy_three(self): + """test the 'tuple' form of join, now superseded + by the two-element join() form. + + + """ + + Order, User = ( + self.classes.Order, + self.classes.User, ) + sess = create_session() + # the old "backwards" form + q = ( + sess.query(User) + .join(("orders", Order)) + .filter_by(description="foo") + ) self.assert_compile( - sess.query(User).join(("orders", Order)), + q, "SELECT users.id AS users_id, users.name AS users_name " - "FROM users JOIN orders ON users.id = orders.user_id", + "FROM users JOIN orders ON users.id = orders.user_id " + "WHERE orders.description = :description_1", ) + def test_multi_tuple_form_legacy_three_point_five(self): + """test the 'tuple' form of join, now superseded + by the two-element join() form. + + + """ + + Order, User = ( + self.classes.Order, + self.classes.User, + ) + + sess = create_session() + + q = sess.query(User).join(Order, "orders").filter_by(description="foo") + self.assert_compile( + q, + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN orders ON users.id = orders.user_id " + "WHERE orders.description = :description_1", + ) + + def test_multi_tuple_form_legacy_four(self): + User, Order, Item, Keyword = self.classes( + "User", "Order", "Item", "Keyword" + ) + + sess = create_session() + + # ensure when the tokens are broken up that from_joinpoint + # is set between them + + expected = ( + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN orders ON users.id = orders.user_id " + "JOIN order_items AS order_items_1 ON orders.id = " + "order_items_1.order_id JOIN items ON items.id = " + "order_items_1.item_id JOIN item_keywords AS item_keywords_1 " + "ON items.id = item_keywords_1.item_id " + "JOIN keywords ON keywords.id = item_keywords_1.keyword_id" + ) + + q = sess.query(User).join( + (Order, "orders"), (Item, "items"), (Keyword, "keywords") + ) + self.assert_compile(q, expected) + + q = sess.query(User).join("orders", "items", "keywords") + self.assert_compile(q, expected) + def test_single_prop_1(self): User = self.classes.User @@ -995,19 +1117,6 @@ class JoinTest(QueryTest, AssertsCompiledSQL): ) assert [User(id=7, name="jack")] == result - def test_raises_on_dupe_target_rel(self): - User = self.classes.User - - assert_raises_message( - sa.exc.SAWarning, - "Pathed join target Order.items has already been joined to; " - "skipping", - lambda: create_session() - .query(User) - .outerjoin("orders", "items") - .outerjoin("orders", "items"), - ) - def test_from_joinpoint(self): Item, User, Order = ( self.classes.Item, @@ -1108,18 +1217,14 @@ class JoinTest(QueryTest, AssertsCompiledSQL): # before the error raise was added, this would silently work..... assert_raises( sa_exc.InvalidRequestError, - sess.query(User).join, - Address, - Address.user, + sess.query(User).join(Address, Address.user)._compile_context, ) # but this one would silently fail adalias = aliased(Address) assert_raises( sa_exc.InvalidRequestError, - sess.query(User).join, - adalias, - Address.user, + sess.query(User).join(adalias, Address.user)._compile_context, ) def test_multiple_with_aliases(self): @@ -1341,8 +1446,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "Don't know how to join to .*Item.*. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - q.join, - Item, + q.join(Item)._compile_context, ) def test_invalid_join_entity_from_no_from_clause(self): @@ -1356,8 +1460,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "Don't know how to join to .*Item.*. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - q.join, - Item, + q.join(Item)._compile_context, ) def test_invalid_join_entity_from_multiple_from_clause(self): @@ -1378,8 +1481,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "Don't know how to join to .*Item.*. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - q.join, - Item, + q.join(Item)._compile_context, ) def test_join_explicit_left_multiple_from_clause(self): @@ -1438,8 +1540,10 @@ class JoinTest(QueryTest, AssertsCompiledSQL): sa_exc.InvalidRequestError, "Can't identify which entity in which to assign the " "left side of this join.", - sess.query(u1, u2).select_from(u1, u2).join, - User.addresses, + sess.query(u1, u2) + .select_from(u1, u2) + .join(User.addresses) + ._compile_context, ) # more specific ON clause @@ -1475,8 +1579,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "multiple FROMS which can join to this entity. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - q.join, - a1, + q.join(a1)._compile_context, ) # to resolve, add an ON clause @@ -1532,8 +1635,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "multiple FROMS which can join to this entity. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - q.join, - a1, + q.join(a1)._compile_context, ) # to resolve, add an ON clause @@ -1584,8 +1686,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "multiple FROMS which can join to this entity. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - q.outerjoin, - a1, + q.outerjoin(a1)._compile_context, ) # the condition which occurs here is: Query._from_obj contains both @@ -2374,8 +2475,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "Don't know how to join to .*User.*. " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - sess.query(users.c.id).join, - User, + sess.query(users.c.id).join(User)._compile_context, ) assert_raises_message( @@ -2383,8 +2483,10 @@ class JoinTest(QueryTest, AssertsCompiledSQL): "Don't know how to join to .*User.* " r"Please use the .select_from\(\) " "method to establish an explicit left side, as well as", - sess.query(users.c.id).select_from(users).join, - User, + sess.query(users.c.id) + .select_from(users) + .join(User) + ._compile_context, ) def test_on_clause_no_right_side(self): @@ -2395,8 +2497,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): assert_raises_message( sa_exc.ArgumentError, "Expected mapped entity or selectable/table as join target", - sess.query(User).join, - User.id == Address.user_id, + sess.query(User).join(User.id == Address.user_id)._compile_context, ) def test_select_from(self): @@ -2686,10 +2787,9 @@ class JoinFromSelectableTest(fixtures.MappedTest, AssertsCompiledSQL): r"The aliased=True parameter on query.join\(\) only works with " "an ORM entity, not a plain selectable, as the target.", # this doesn't work, so have it raise an error - sess.query(T1.id).join, - subq, - subq.c.t1_id == T1.id, - aliased=True, + sess.query(T1.id) + .join(subq, subq.c.t1_id == T1.id, aliased=True) + ._compile_context, ) @@ -3193,6 +3293,56 @@ class SelfReferentialTest(fixtures.MappedTest, AssertsCompiledSQL): node = q.first() eq_(node.data, "n122") + def test_join_4_explicit_join(self): + Node = self.classes.Node + sess = create_session() + + na = aliased(Node) + na2 = aliased(Node) + + # this one is a great example of how to show how the API changes; + # while it requires the explicitness of aliased(Node), the whole + # guesswork of joinpoint / aliased goes away and the whole thing + # is simpler + # + # .join("parent", aliased=True) + # .filter(Node.data == "n12") + # .join("parent", aliased=True, from_joinpoint=True) + # .filter(Node.data == "n1") + # + # becomes: + # + # na = aliased(Node) + # na2 = aliased(Node) + # + # ... + # .join(na, Node.parent) + # .filter(na.data == "n12") + # .join(na2, na.parent) + # .filter(na2.data == "n1") + # + q = ( + sess.query(Node) + .filter(Node.data == "n122") + .join(na, Node.parent) + .filter(na.data == "n12") + .join(na2, na.parent) + .filter(na2.data == "n1") + ) + + self.assert_compile( + q, + "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, " + "nodes.data AS nodes_data FROM nodes JOIN nodes AS nodes_1 " + "ON nodes_1.id = nodes.parent_id JOIN nodes AS nodes_2 " + "ON nodes_2.id = nodes_1.parent_id WHERE nodes.data = :data_1 " + "AND nodes_1.data = :data_2 AND nodes_2.data = :data_3", + checkparams={"data_1": "n122", "data_2": "n12", "data_3": "n1"}, + ) + + node = q.first() + eq_(node.data, "n122") + def test_join_4_filter(self): Node = self.classes.Node sess = create_session() @@ -3390,8 +3540,7 @@ class SelfReferentialTest(fixtures.MappedTest, AssertsCompiledSQL): sa.exc.InvalidRequestError, "Can't construct a join from mapped class Node->nodes to mapped " "class Node->nodes, they are the same entity", - s.query(Node).join, - Node.children, + s.query(Node).join(Node.children)._compile_context, ) def test_explicit_join_1(self): @@ -3539,7 +3688,7 @@ class SelfReferentialTest(fixtures.MappedTest, AssertsCompiledSQL): Node.data == "n122", n1.data == "n12", n2.data == "n1" ) ) - .values(Node.data, n1.data, n2.data) + .with_entities(Node.data, n1.data, n2.data) ), [("n122", "n12", "n1")], ) diff --git a/test/orm/test_lazy_relations.py b/test/orm/test_lazy_relations.py index be8301bae..65158dbd4 100644 --- a/test/orm/test_lazy_relations.py +++ b/test/orm/test_lazy_relations.py @@ -439,7 +439,9 @@ class LazyTest(_fixtures.FixtureTest): def process_query_conditionally(self, query): """process query during a lazyload""" canary() - query._params = query._params.union(dict(name=self.crit)) + params = dict(query.load_options._params) + query.load_options += {"_params": params} + query.load_options._params.update(dict(name=self.crit)) s = Session() ed = s.query(User).options(MyOption("ed")).filter_by(name="ed").one() diff --git a/test/orm/test_loading.py b/test/orm/test_loading.py index 270610c9f..968c8229b 100644 --- a/test/orm/test_loading.py +++ b/test/orm/test_loading.py @@ -75,7 +75,7 @@ class InstancesTest(_fixtures.FixtureTest): ctx = q._compile_context() cursor = mock.Mock() - q._entities = [ + ctx.compile_state._entities = [ mock.Mock(row_processor=mock.Mock(side_effect=Exception("boom"))) ] assert_raises(Exception, loading.instances, q, cursor, ctx) diff --git a/test/orm/test_lockmode.py b/test/orm/test_lockmode.py index bc90c25d8..a3dd42fc2 100644 --- a/test/orm/test_lockmode.py +++ b/test/orm/test_lockmode.py @@ -31,7 +31,7 @@ class ForUpdateTest(_fixtures.FixtureTest): q = s.query(User).with_for_update( read=read, nowait=nowait, of=of, key_share=key_share ) - sel = q._compile_context().statement + sel = q._compile_state().statement assert q._for_update_arg.read is read assert sel._for_update_arg.read is read diff --git a/test/orm/test_mapper.py b/test/orm/test_mapper.py index 545bf8e01..a090d0044 100644 --- a/test/orm/test_mapper.py +++ b/test/orm/test_mapper.py @@ -2568,8 +2568,9 @@ class DeepOptionsTest(_fixtures.FixtureTest): "root entities in this query, e.g. mapped class User->users. " "Please specify the full path from one of the root entities " "to the target attribute.", - sess.query(User).options, - sa.orm.joinedload(Order.items), + sess.query(User) + .options(sa.orm.joinedload(Order.items)) + ._compile_context, ) # joinedload "keywords" on items. it will lazy load "orders", then diff --git a/test/orm/test_options.py b/test/orm/test_options.py index 34fee8470..034e940d9 100644 --- a/test/orm/test_options.py +++ b/test/orm/test_options.py @@ -77,20 +77,23 @@ class PathTest(object): return orm_util.PathRegistry.coerce(self._make_path(path)) def _assert_path_result(self, opt, q, paths): - q._attributes = dict(q._attributes) attr = {} if isinstance(opt, strategy_options._UnboundLoad): for val in opt._to_bind: val._bind_loader( - [ent.entity_zero for ent in q._mapper_entities], - q._current_path, + [ + ent.entity_zero + for ent in q._compile_state()._mapper_entities + ], + q.compile_options._current_path, attr, False, ) else: - opt._process(q, True) - attr = q._attributes + compile_state = q._compile_state() + compile_state.attributes = attr = {} + opt._process(compile_state, True) assert_paths = [k[1] for k in attr] eq_( @@ -1216,16 +1219,23 @@ class OptionsNoPropTest(_fixtures.FixtureTest): def _assert_option(self, entity_list, option): Item = self.classes.Item - q = create_session().query(*entity_list).options(joinedload(option)) + context = ( + create_session() + .query(*entity_list) + .options(joinedload(option)) + ._compile_state() + ) key = ("loader", (inspect(Item), inspect(Item).attrs.keywords)) - assert key in q._attributes + assert key in context.attributes def _assert_loader_strategy_exception(self, entity_list, options, message): assert_raises_message( orm_exc.LoaderStrategyException, message, - create_session().query(*entity_list).options, - *options + create_session() + .query(*entity_list) + .options(*options) + ._compile_state, ) def _assert_eager_with_entity_exception( @@ -1234,8 +1244,10 @@ class OptionsNoPropTest(_fixtures.FixtureTest): assert_raises_message( sa.exc.ArgumentError, message, - create_session().query(*entity_list).options, - *options + create_session() + .query(*entity_list) + .options(*options) + ._compile_state, ) def _assert_eager_with_just_column_exception( @@ -1244,8 +1256,10 @@ class OptionsNoPropTest(_fixtures.FixtureTest): assert_raises_message( sa.exc.ArgumentError, message, - create_session().query(column).options, - joinedload(eager_option), + create_session() + .query(column) + .options(joinedload(eager_option)) + ._compile_state, ) @@ -1260,8 +1274,7 @@ class OptionsNoPropTestInh(_Polymorphic): r'Mapped attribute "Manager.status" does not apply to any of ' r"the root entities in this query, e.g. " r"with_polymorphic\(Person, \[Manager\]\).", - s.query(wp).options, - load_only(Manager.status), + s.query(wp).options(load_only(Manager.status))._compile_state, ) def test_missing_attr_of_type_subclass(self): @@ -1271,10 +1284,13 @@ class OptionsNoPropTestInh(_Polymorphic): sa.exc.ArgumentError, r'Attribute "Manager.manager_name" does not link from element ' r'"with_polymorphic\(Person, \[Engineer\]\)".$', - s.query(Company).options, - joinedload(Company.employees.of_type(Engineer)).load_only( - Manager.manager_name - ), + s.query(Company) + .options( + joinedload(Company.employees.of_type(Engineer)).load_only( + Manager.manager_name + ) + ) + ._compile_state, ) def test_missing_attr_of_type_subclass_name_matches(self): @@ -1286,10 +1302,13 @@ class OptionsNoPropTestInh(_Polymorphic): sa.exc.ArgumentError, r'Attribute "Manager.status" does not link from element ' r'"with_polymorphic\(Person, \[Engineer\]\)".$', - s.query(Company).options, - joinedload(Company.employees.of_type(Engineer)).load_only( - Manager.status - ), + s.query(Company) + .options( + joinedload(Company.employees.of_type(Engineer)).load_only( + Manager.status + ) + ) + ._compile_state, ) def test_missing_str_attr_of_type_subclass(self): @@ -1299,10 +1318,13 @@ class OptionsNoPropTestInh(_Polymorphic): sa.exc.ArgumentError, r'Can\'t find property named "manager_name" on ' r"mapped class Engineer->engineers in this Query.$", - s.query(Company).options, - joinedload(Company.employees.of_type(Engineer)).load_only( - "manager_name" - ), + s.query(Company) + .options( + joinedload(Company.employees.of_type(Engineer)).load_only( + "manager_name" + ) + ) + ._compile_state, ) def test_missing_attr_of_type_wpoly_subclass(self): @@ -1314,10 +1336,13 @@ class OptionsNoPropTestInh(_Polymorphic): sa.exc.ArgumentError, r'Attribute "Manager.manager_name" does not link from ' r'element "with_polymorphic\(Person, \[Manager\]\)".$', - s.query(Company).options, - joinedload(Company.employees.of_type(wp)).load_only( - Manager.manager_name - ), + s.query(Company) + .options( + joinedload(Company.employees.of_type(wp)).load_only( + Manager.manager_name + ) + ) + ._compile_state, ) def test_missing_attr_is_missing_of_type_for_alias(self): @@ -1330,8 +1355,9 @@ class OptionsNoPropTestInh(_Polymorphic): r'Attribute "AliasedClass_Person.name" does not link from ' r'element "mapped class Person->people". Did you mean to use ' r"Company.employees.of_type\(AliasedClass_Person\)\?", - s.query(Company).options, - joinedload(Company.employees).load_only(pa.name), + s.query(Company) + .options(joinedload(Company.employees).load_only(pa.name)) + ._compile_state, ) q = s.query(Company).options( @@ -1341,7 +1367,7 @@ class OptionsNoPropTestInh(_Polymorphic): Company.employees.property ][inspect(pa)][pa.name.property] key = ("loader", orig_path.natural_path) - loader = q._attributes[key] + loader = q._compile_state().attributes[key] eq_(loader.path, orig_path) @@ -1403,8 +1429,11 @@ class PickleTest(PathTest, QueryTest): query = create_session().query(User) attr = {} load = opt._bind_loader( - [ent.entity_zero for ent in query._mapper_entities], - query._current_path, + [ + ent.entity_zero + for ent in query._compile_state()._mapper_entities + ], + query.compile_options._current_path, attr, False, ) @@ -1437,8 +1466,11 @@ class PickleTest(PathTest, QueryTest): query = create_session().query(User) attr = {} load = opt._bind_loader( - [ent.entity_zero for ent in query._mapper_entities], - query._current_path, + [ + ent.entity_zero + for ent in query._compile_state()._mapper_entities + ], + query.compile_options._current_path, attr, False, ) @@ -1479,10 +1511,11 @@ class LocalOptsTest(PathTest, QueryTest): for opt in opts: if isinstance(opt, strategy_options._UnboundLoad): + ctx = query._compile_state() for tb in opt._to_bind: tb._bind_loader( - [ent.entity_zero for ent in query._mapper_entities], - query._current_path, + [ent.entity_zero for ent in ctx._mapper_entities], + query.compile_options._current_path, attr, False, ) @@ -1568,27 +1601,29 @@ class SubOptionsTest(PathTest, QueryTest): run_deletes = None def _assert_opts(self, q, sub_opt, non_sub_opts): - existing_attributes = q._attributes - q._attributes = dict(q._attributes) attr_a = {} for val in sub_opt._to_bind: val._bind_loader( - [ent.entity_zero for ent in q._mapper_entities], - q._current_path, + [ + ent.entity_zero + for ent in q._compile_state()._mapper_entities + ], + q.compile_options._current_path, attr_a, False, ) - q._attributes = dict(existing_attributes) - attr_b = {} for opt in non_sub_opts: for val in opt._to_bind: val._bind_loader( - [ent.entity_zero for ent in q._mapper_entities], - q._current_path, + [ + ent.entity_zero + for ent in q._compile_state()._mapper_entities + ], + q.compile_options._current_path, attr_b, False, ) diff --git a/test/orm/test_pickled.py b/test/orm/test_pickled.py index 5c4d1e22d..a5a983740 100644 --- a/test/orm/test_pickled.py +++ b/test/orm/test_pickled.py @@ -495,9 +495,11 @@ class PickleTest(fixtures.MappedTest): sa.orm.defer(User.name), sa.orm.joinedload("addresses").joinedload(Address.dingaling), ]: - q = sess.query(User).options(opt) + context = sess.query(User).options(opt)._compile_context() opt = [ - v for v in q._attributes.values() if isinstance(v, sa.orm.Load) + v + for v in context.attributes.values() + if isinstance(v, sa.orm.Load) ][0] opt2 = pickle.loads(pickle.dumps(opt)) @@ -767,7 +769,7 @@ class TupleLabelTest(_fixtures.FixtureTest): eq_(row.name, row[0]) eq_(row.foobar, row[1]) - for row in sess.query(User).values( + for row in sess.query(User).with_entities( User.name, User.id.label("foobar") ): if pickled is not False: diff --git a/test/orm/test_query.py b/test/orm/test_query.py index 8943bfc1f..030e6c870 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -200,10 +200,21 @@ class RowTupleTest(QueryTest): cte = sess.query(User.id).cte() ex = sess.query(User).exists() - is_(sess.query(subq1)._deep_entity_zero(), inspect(User)) - is_(sess.query(subq2)._deep_entity_zero(), inspect(User)) - is_(sess.query(cte)._deep_entity_zero(), inspect(User)) - is_(sess.query(ex)._deep_entity_zero(), inspect(User)) + is_( + sess.query(subq1)._compile_state()._deep_entity_zero(), + inspect(User), + ) + is_( + sess.query(subq2)._compile_state()._deep_entity_zero(), + inspect(User), + ) + is_( + sess.query(cte)._compile_state()._deep_entity_zero(), + inspect(User), + ) + is_( + sess.query(ex)._compile_state()._deep_entity_zero(), inspect(User), + ) @testing.combinations( lambda sess, User: ( @@ -942,6 +953,7 @@ class GetTest(QueryTest): assert a in u2.addresses s.query(User).populate_existing().get(7) + assert u2 not in s.dirty assert u2.name == "jack" assert a not in u2.addresses @@ -1138,37 +1150,37 @@ class InvalidGenerationsTest(QueryTest, AssertsCompiledSQL): q = s.query(User, Address) assert_raises(sa_exc.InvalidRequestError, q.get, 5) - def test_entity_or_mapper_zero(self): + def test_entity_or_mapper_zero_from_context(self): User, Address = self.classes.User, self.classes.Address s = create_session() - q = s.query(User, Address) + q = s.query(User, Address)._compile_state() is_(q._mapper_zero(), inspect(User)) is_(q._entity_zero(), inspect(User)) u1 = aliased(User) - q = s.query(u1, Address) + q = s.query(u1, Address)._compile_state() is_(q._mapper_zero(), inspect(User)) is_(q._entity_zero(), inspect(u1)) - q = s.query(User).select_from(Address) + q = s.query(User).select_from(Address)._compile_state() is_(q._mapper_zero(), inspect(User)) is_(q._entity_zero(), inspect(Address)) - q = s.query(User.name, Address) + q = s.query(User.name, Address)._compile_state() is_(q._mapper_zero(), inspect(User)) is_(q._entity_zero(), inspect(User)) - q = s.query(u1.name, Address) + q = s.query(u1.name, Address)._compile_state() is_(q._mapper_zero(), inspect(User)) is_(q._entity_zero(), inspect(u1)) q1 = s.query(User).exists() - q = s.query(q1) + q = s.query(q1)._compile_state() is_(q._mapper_zero(), None) is_(q._entity_zero(), None) - q1 = s.query(Bundle("b1", User.id, User.name)) + q1 = s.query(Bundle("b1", User.id, User.name))._compile_state() is_(q1._mapper_zero(), inspect(User)) is_(q1._entity_zero(), inspect(User)) @@ -1266,8 +1278,10 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): sess = Session() lead = sess.query(entity) context = lead._compile_context() - context.statement._label_style = LABEL_STYLE_TABLENAME_PLUS_COL - lead = context.statement.compile(dialect=dialect) + context.compile_state.statement._label_style = ( + LABEL_STYLE_TABLENAME_PLUS_COL + ) + lead = context.compile_state.statement.compile(dialect=dialect) expected = (str(lead) + " WHERE " + expected).replace("\n", "") clause = sess.query(entity).filter(clause) self.assert_compile(clause, expected, checkparams=checkparams) @@ -1312,6 +1326,7 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): lhs = testing.resolve_lambda(lhs, User=User) rhs = testing.resolve_lambda(rhs, User=User) + create_session().query(User) self._test(py_op(lhs, rhs), res % sql_op) @@ -1393,60 +1408,6 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): ~(None == Address.user), "addresses.user_id IS NOT NULL" # noqa ) - def test_o2m_compare_to_null_orm_adapt(self): - User, Address = self.classes.User, self.classes.Address - self._test_filter_aliases( - User.id == None, # noqa - "users_1.id IS NULL", - Address, - Address.user, - ), - self._test_filter_aliases( - User.id != None, # noqa - "users_1.id IS NOT NULL", - Address, - Address.user, - ), - self._test_filter_aliases( - ~(User.id == None), # noqa - "users_1.id IS NOT NULL", - Address, - Address.user, - ), - self._test_filter_aliases( - ~(User.id != None), # noqa - "users_1.id IS NULL", - Address, - Address.user, - ), - - def test_m2o_compare_to_null_orm_adapt(self): - User, Address = self.classes.User, self.classes.Address - self._test_filter_aliases( - Address.user == None, # noqa - "addresses_1.user_id IS NULL", - User, - User.addresses, - ), - self._test_filter_aliases( - Address.user != None, # noqa - "addresses_1.user_id IS NOT NULL", - User, - User.addresses, - ), - self._test_filter_aliases( - ~(Address.user == None), # noqa - "addresses_1.user_id IS NOT NULL", - User, - User.addresses, - ), - self._test_filter_aliases( - ~(Address.user != None), # noqa - "addresses_1.user_id IS NULL", - User, - User.addresses, - ), - def test_o2m_compare_to_null_aliased(self): User = self.classes.User u1 = aliased(User) @@ -1496,16 +1457,6 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): entity=u1, ) - def test_o2m_any_orm_adapt(self): - User, Address = self.classes.User, self.classes.Address - self._test_filter_aliases( - User.addresses.any(Address.id == 17), - "EXISTS (SELECT 1 FROM addresses " - "WHERE users_1.id = addresses.user_id AND addresses.id = :id_1)", - Address, - Address.user, - ) - def test_m2o_compare_instance(self): User, Address = self.classes.User, self.classes.Address u7 = User(id=5) @@ -1526,93 +1477,19 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): checkparams={"user_id_1": 7}, ) - def test_m2o_compare_instance_orm_adapt(self): - User, Address = self.classes.User, self.classes.Address - u7 = User(id=5) - attributes.instance_state(u7)._commit_all(attributes.instance_dict(u7)) - u7.id = 7 - - self._test_filter_aliases( - Address.user == u7, - ":param_1 = addresses_1.user_id", - User, - User.addresses, - checkparams={"param_1": 7}, - ) - def test_m2o_compare_instance_negated_warn_on_none(self): User, Address = self.classes.User, self.classes.Address u7_transient = User(id=None) with expect_warnings("Got None for value of column users.id; "): - self._test_filter_aliases( + self._test( Address.user != u7_transient, - "addresses_1.user_id != :user_id_1 " - "OR addresses_1.user_id IS NULL", - User, - User.addresses, + "addresses.user_id != :user_id_1 " + "OR addresses.user_id IS NULL", checkparams={"user_id_1": None}, ) - def test_m2o_compare_instance_negated_orm_adapt(self): - User, Address = self.classes.User, self.classes.Address - u7 = User(id=5) - attributes.instance_state(u7)._commit_all(attributes.instance_dict(u7)) - u7.id = 7 - - u7_transient = User(id=7) - - self._test_filter_aliases( - Address.user != u7, - "addresses_1.user_id != :user_id_1 OR addresses_1.user_id IS NULL", - User, - User.addresses, - checkparams={"user_id_1": 7}, - ) - - self._test_filter_aliases( - ~(Address.user == u7), - ":param_1 != addresses_1.user_id", - User, - User.addresses, - checkparams={"param_1": 7}, - ) - - self._test_filter_aliases( - ~(Address.user != u7), - "NOT (addresses_1.user_id != :user_id_1 " - "OR addresses_1.user_id IS NULL)", - User, - User.addresses, - checkparams={"user_id_1": 7}, - ) - - self._test_filter_aliases( - Address.user != u7_transient, - "addresses_1.user_id != :user_id_1 OR addresses_1.user_id IS NULL", - User, - User.addresses, - checkparams={"user_id_1": 7}, - ) - - self._test_filter_aliases( - ~(Address.user == u7_transient), - ":param_1 != addresses_1.user_id", - User, - User.addresses, - checkparams={"param_1": 7}, - ) - - self._test_filter_aliases( - ~(Address.user != u7_transient), - "NOT (addresses_1.user_id != :user_id_1 " - "OR addresses_1.user_id IS NULL)", - User, - User.addresses, - checkparams={"user_id_1": 7}, - ) - def test_m2o_compare_instance_aliased(self): User, Address = self.classes.User, self.classes.Address u7 = User(id=5) @@ -1953,7 +1830,7 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): q = session.query(Address).filter(Address.user_id == q) - assert isinstance(q._criterion.right, expression.ColumnElement) + assert isinstance(q.whereclause.right, expression.ColumnElement) self.assert_compile( q, "SELECT addresses.id AS addresses_id, addresses.user_id " @@ -2976,10 +2853,11 @@ class FilterTest(QueryTest, AssertsCompiledSQL): ).all() # test that the contents are not adapted by the aliased join + ua = aliased(Address) assert ( [User(id=7), User(id=8)] == sess.query(User) - .join("addresses", aliased=True) + .join(ua, "addresses") .filter( ~User.addresses.any(Address.email_address == "fred@fred.com") ) @@ -2987,7 +2865,7 @@ class FilterTest(QueryTest, AssertsCompiledSQL): ) assert [User(id=10)] == sess.query(User).outerjoin( - "addresses", aliased=True + ua, "addresses" ).filter(~User.addresses.any()).all() def test_any_doesnt_overcorrelate(self): @@ -3049,10 +2927,11 @@ class FilterTest(QueryTest, AssertsCompiledSQL): ) # test has() doesn't get subquery contents adapted by aliased join + ua = aliased(User) assert ( [Address(id=2), Address(id=3), Address(id=4)] == sess.query(Address) - .join("user", aliased=True) + .join(ua, "user") .filter(Address.user.has(User.name.like("%ed%"), id=8)) .order_by(Address.id) .all() @@ -3236,7 +3115,7 @@ class FilterTest(QueryTest, AssertsCompiledSQL): sess = create_session() assert_raises_message( sa.exc.InvalidRequestError, - "Entity 'addresses' has no property 'name'", + 'Entity namespace for "addresses" has no property "name"', sess.query(addresses).filter_by, name="ed", ) @@ -3350,9 +3229,7 @@ class FilterTest(QueryTest, AssertsCompiledSQL): e = sa.func.count(123) assert_raises_message( sa_exc.InvalidRequestError, - r"Can't use filter_by when the first entity 'count\(:count_1\)' of" - " a query is not a mapped class. Please use the filter method " - "instead, or change the order of the entities in the query", + r'Entity namespace for "count\(\:count_1\)" has no property "col"', s.query(e).filter_by, col=42, ) @@ -3474,8 +3351,9 @@ class HasAnyTest(fixtures.DeclarativeMappedTest, AssertsCompiledSQL): B, C = self.classes("B", "C") s = Session() + ca = aliased(C) self.assert_compile( - s.query(B).join(B.c, aliased=True).filter(B.c.has(C.id == 1)), + s.query(B).join(ca, B.c).filter(B.c.has(C.id == 1)), "SELECT b.id AS b_id, b.c_id AS b_c_id " "FROM b JOIN c AS c_1 ON c_1.id = b.c_id " "WHERE EXISTS " @@ -3486,8 +3364,9 @@ class HasAnyTest(fixtures.DeclarativeMappedTest, AssertsCompiledSQL): B, D = self.classes("B", "D") s = Session() + da = aliased(D) self.assert_compile( - s.query(B).join(B.d, aliased=True).filter(B.d.any(D.id == 1)), + s.query(B).join(da, B.d).filter(B.d.any(D.id == 1)), "SELECT b.id AS b_id, b.c_id AS b_c_id " "FROM b JOIN b_d AS b_d_1 ON b.id = b_d_1.bid " "JOIN d AS d_1 ON d_1.id = b_d_1.did " @@ -3504,7 +3383,7 @@ class HasMapperEntitiesTest(QueryTest): q = s.query(User) - assert q._has_mapper_entities + assert q._compile_state()._has_mapper_entities def test_cols(self): User = self.classes.User @@ -3512,7 +3391,7 @@ class HasMapperEntitiesTest(QueryTest): q = s.query(User.id) - assert not q._has_mapper_entities + assert not q._compile_state()._has_mapper_entities def test_cols_set_entities(self): User = self.classes.User @@ -3521,7 +3400,7 @@ class HasMapperEntitiesTest(QueryTest): q = s.query(User.id) q._set_entities(User) - assert q._has_mapper_entities + assert q._compile_state()._has_mapper_entities def test_entity_set_entities(self): User = self.classes.User @@ -3530,7 +3409,7 @@ class HasMapperEntitiesTest(QueryTest): q = s.query(User) q._set_entities(User.id) - assert not q._has_mapper_entities + assert not q._compile_state()._has_mapper_entities class SetOpsTest(QueryTest, AssertsCompiledSQL): @@ -3764,10 +3643,11 @@ class AggregateTest(QueryTest): sess = create_session() orders = sess.query(Order).filter(Order.id.in_([2, 3, 4])) eq_( - next(orders.values(func.sum(Order.user_id * Order.address_id))), - (79,), + orders.with_entities( + func.sum(Order.user_id * Order.address_id) + ).scalar(), + 79, ) - eq_(orders.value(func.sum(Order.user_id * Order.address_id)), 79) def test_apply(self): Order = self.classes.Order @@ -4264,7 +4144,7 @@ class DistinctTest(QueryTest, AssertsCompiledSQL): ) -class PrefixWithTest(QueryTest, AssertsCompiledSQL): +class PrefixSuffixWithTest(QueryTest, AssertsCompiledSQL): def test_one_prefix(self): User = self.classes.User sess = create_session() @@ -4272,6 +4152,14 @@ class PrefixWithTest(QueryTest, AssertsCompiledSQL): expected = "SELECT PREFIX_1 " "users.name AS users_name FROM users" self.assert_compile(query, expected, dialect=default.DefaultDialect()) + def test_one_suffix(self): + User = self.classes.User + sess = create_session() + query = sess.query(User.name).suffix_with("SUFFIX_1") + # trailing space for some reason + expected = "SELECT users.name AS users_name FROM users SUFFIX_1 " + self.assert_compile(query, expected, dialect=default.DefaultDialect()) + def test_many_prefixes(self): User = self.classes.User sess = create_session() @@ -4350,7 +4238,7 @@ class YieldTest(_fixtures.FixtureTest): sess = create_session() q = sess.query(User).yield_per(15) q = q.execution_options(foo="bar") - assert q._yield_per + assert q.load_options._yield_per eq_( q._execution_options, {"stream_results": True, "foo": "bar", "max_row_buffer": 15}, @@ -5642,18 +5530,19 @@ class SynonymTest(QueryTest, AssertsCompiledSQL): User, Order = self.classes.User, self.classes.Order for j in ( - ["orders", "items"], - ["orders_syn", "items"], + [User.orders, Order.items], + [User.orders_syn, Order.items], [User.orders_syn, Order.items], - ["orders_syn_2", "items"], - [User.orders_syn_2, "items"], - ["orders", "items_syn"], - ["orders_syn", "items_syn"], - ["orders_syn_2", "items_syn"], + [User.orders_syn_2, Order.items], + [User.orders, Order.items_syn], + [User.orders_syn, Order.items_syn], + [User.orders_syn_2, Order.items_syn], ): - result = ( - create_session().query(User).join(*j).filter_by(id=3).all() - ) + q = create_session().query(User) + for path in j: + q = q.join(path) + q = q.filter_by(id=3) + result = q.all() assert [User(id=7, name="jack"), User(id=9, name="fred")] == result def test_with_parent(self): @@ -5895,18 +5784,6 @@ class ImmediateTest(_fixtures.FixtureTest): sess.query(User.id, User.name).scalar, ) - def test_value(self): - User = self.classes.User - - sess = create_session() - - eq_(sess.query(User).filter_by(id=7).value(User.id), 7) - eq_(sess.query(User.id, User.name).filter_by(id=7).value(User.id), 7) - eq_(sess.query(User).filter_by(id=0).value(User.id), None) - - sess.bind = testing.db - eq_(sess.query().value(sa.literal_column("1").label("x")), 1) - class ExecutionOptionsTest(QueryTest): def test_option_building(self): diff --git a/test/orm/test_selectin_relations.py b/test/orm/test_selectin_relations.py index 8bac71c1b..8bb5ee93d 100644 --- a/test/orm/test_selectin_relations.py +++ b/test/orm/test_selectin_relations.py @@ -348,13 +348,10 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): q = create_session().query(Item).order_by(Item.id) def go(): + ka = aliased(Keyword) eq_( self.static.item_keyword_result[0:2], - ( - q.join("keywords", aliased=True).filter( - Keyword.name == "red" - ) - ).all(), + (q.join(ka, "keywords").filter(ka.name == "red")).all(), ) self.assert_sql_count(testing.db, go, 2) diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index c2afe6f99..39e4f89ab 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -346,13 +346,10 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): q = create_session().query(Item).order_by(Item.id) def go(): + ka = aliased(Keyword) eq_( self.static.item_keyword_result[0:2], - ( - q.join("keywords", aliased=True).filter( - Keyword.name == "red" - ) - ).all(), + (q.join(ka, "keywords").filter(ka.name == "red")).all(), ) self.assert_sql_count(testing.db, go, 2) @@ -2700,8 +2697,11 @@ class CyclicalInheritingEagerTestTwo( ctx = s.query(Director).options(subqueryload("*"))._compile_context() q = ctx.attributes[ - ("subquery", (inspect(Director), inspect(Director).attrs.movies)) - ] + ( + "subqueryload_data", + (inspect(Director), inspect(Director).attrs.movies), + ) + ]["query"] self.assert_compile( q, "SELECT movie.id AS movie_id, " @@ -2830,8 +2830,11 @@ class SubqueryloadDistinctTest( ctx = q._compile_context() q2 = ctx.attributes[ - ("subquery", (inspect(Movie), inspect(Movie).attrs.director)) - ] + ( + "subqueryload_data", + (inspect(Movie), inspect(Movie).attrs.director), + ) + ]["query"] self.assert_compile( q2, "SELECT director.id AS director_id, " @@ -2853,8 +2856,11 @@ class SubqueryloadDistinctTest( eq_(rows, [(1, "Woody Allen", 1), (1, "Woody Allen", 1)]) q3 = ctx2.attributes[ - ("subquery", (inspect(Director), inspect(Director).attrs.photos)) - ] + ( + "subqueryload_data", + (inspect(Director), inspect(Director).attrs.photos), + ) + ]["query"] self.assert_compile( q3, @@ -2914,12 +2920,12 @@ class SubqueryloadDistinctTest( ctx = q._compile_context() q2 = ctx.attributes[ - ("subquery", (inspect(Credit), Credit.movie.property)) - ] + ("subqueryload_data", (inspect(Credit), Credit.movie.property)) + ]["query"] ctx2 = q2._compile_context() q3 = ctx2.attributes[ - ("subquery", (inspect(Movie), Movie.director.property)) - ] + ("subqueryload_data", (inspect(Movie), Movie.director.property)) + ]["query"] result = s.execute(q3) eq_(result.fetchall(), [(1, "Woody Allen", 1), (1, "Woody Allen", 1)]) diff --git a/test/orm/test_utils.py b/test/orm/test_utils.py index 23372fa2a..5e3f51606 100644 --- a/test/orm/test_utils.py +++ b/test/orm/test_utils.py @@ -216,17 +216,21 @@ class AliasedClassTest(fixtures.TestBase, AssertsCompiledSQL): eq_( Point.x_alone._annotations, { + "entity_namespace": point_mapper, "parententity": point_mapper, "parentmapper": point_mapper, "orm_key": "x_alone", + "compile_state_plugin": "orm", }, ) eq_( Point.x._annotations, { + "entity_namespace": point_mapper, "parententity": point_mapper, "parentmapper": point_mapper, "orm_key": "x", + "compile_state_plugin": "orm", }, ) |