[Sqlalchemy-commits] sqlalchemy: - polymorphic_union() gets a "cast_nulls" option,
Brought to you by:
zzzeek
From: <co...@sq...> - 2011-05-12 16:47:06
|
details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/42fa28ee1237 changeset: 7608:42fa28ee1237 user: zzzeek date: Thu May 12 12:47:09 2011 -0400 description: - polymorphic_union() gets a "cast_nulls" option, disables the usage of CAST when it renders the labeled NULL columns. [ticket:1502] - polymorphic_union() renders the columns in their original table order, as according to the first table/selectable in the list of polymorphic unions in which they appear. (which is itself an unordered mapping unless you pass an OrderedDict). diffstat: CHANGES | 10 ++++++ lib/sqlalchemy/orm/util.py | 21 +++++++++++- test/orm/inheritance/test_basic.py | 58 +++++++++++++++++++++++++++++++++++++- 3 files changed, 85 insertions(+), 4 deletions(-) diffs (137 lines): diff -r 2c75ce95255a -r 42fa28ee1237 CHANGES --- a/CHANGES Wed May 11 18:58:11 2011 -0700 +++ b/CHANGES Thu May 12 12:47:09 2011 -0400 @@ -77,6 +77,16 @@ reference the correct mapper. [ticket:2163]. Also in 0.6.8. + - polymorphic_union() gets a "cast_nulls" option, + disables the usage of CAST when it renders + the labeled NULL columns. [ticket:1502] + + - polymorphic_union() renders the columns in their + original table order, as according to the first + table/selectable in the list of polymorphic + unions in which they appear. (which is itself + an unordered mapping unless you pass an OrderedDict). + - sql - Changed the handling in determination of join conditions such that foreign key errors are diff -r 2c75ce95255a -r 42fa28ee1237 lib/sqlalchemy/orm/util.py --- a/lib/sqlalchemy/orm/util.py Wed May 11 18:58:11 2011 -0700 +++ b/lib/sqlalchemy/orm/util.py Thu May 12 12:47:09 2011 -0400 @@ -65,14 +65,26 @@ event.listen(desc, 'append', append, raw=True, retval=True) event.listen(desc, 'set', set_, raw=True, retval=True) -def polymorphic_union(table_map, typecolname, aliasname='p_union'): +def polymorphic_union(table_map, typecolname, aliasname='p_union', cast_nulls=True): """Create a ``UNION`` statement used by a polymorphic mapper. See :ref:`concrete_inheritance` for an example of how this is used. + + :param table_map: mapping of polymorphic identities to + :class:`.Table` objects. + :param typecolname: string name of a "discriminator" column, which will be + derived from the query, producing the polymorphic identity for each row. If + ``None``, no polymorphic discriminator is generated. + :param aliasname: name of the :func:`~sqlalchemy.sql.expression.alias()` + construct generated. + :param cast_nulls: if True, non-existent columns, which are represented as labeled + NULLs, will be passed into CAST. This is a legacy behavior that is problematic + on some backends such as Oracle - in which case it can be set to False. + """ - colnames = set() + colnames = util.OrderedSet() colnamemaps = {} types = {} for key in table_map.keys(): @@ -95,7 +107,10 @@ try: return colnamemaps[table][name] except KeyError: - return sql.cast(sql.null(), types[name]).label(name) + if cast_nulls: + return sql.cast(sql.null(), types[name]).label(name) + else: + return sql.type_coerce(sql.null(), types[name]).label(name) result = [] for type, table in table_map.iteritems(): diff -r 2c75ce95255a -r 42fa28ee1237 test/orm/inheritance/test_basic.py --- a/test/orm/inheritance/test_basic.py Wed May 11 18:58:11 2011 -0700 +++ b/test/orm/inheritance/test_basic.py Thu May 12 12:47:09 2011 -0400 @@ -5,7 +5,7 @@ from sqlalchemy.orm import * from sqlalchemy.orm import exc as orm_exc, attributes from test.lib.assertsql import AllOf, CompiledSQL - +from sqlalchemy.sql import table, column from test.lib import testing, engines from test.lib import fixtures from test.orm import _fixtures @@ -1816,4 +1816,60 @@ sess.add(s1) assert_raises(sa_exc.DBAPIError, sess.flush) +class PolymorphicUnionTest(fixtures.TestBase, testing.AssertsCompiledSQL): + __dialect__ = 'default' + def _fixture(self): + t1 = table('t1', column('c1', Integer), + column('c2', Integer), + column('c3', Integer)) + t2 = table('t2', column('c1', Integer), column('c2', Integer), + column('c3', Integer), + column('c4', Integer)) + t3 = table('t3', column('c1', Integer), + column('c3', Integer), + column('c5', Integer)) + return t1, t2, t3 + + def test_type_col_present(self): + t1, t2, t3 = self._fixture() + self.assert_compile( + polymorphic_union( + util.OrderedDict([("a", t1), ("b", t2), ("c", t3)]), + 'q1' + ), + "SELECT t1.c1, t1.c2, t1.c3, CAST(NULL AS INTEGER) AS c4, " + "CAST(NULL AS INTEGER) AS c5, 'a' AS q1 FROM t1 UNION ALL " + "SELECT t2.c1, t2.c2, t2.c3, t2.c4, CAST(NULL AS INTEGER) AS c5, " + "'b' AS q1 FROM t2 UNION ALL SELECT t3.c1, " + "CAST(NULL AS INTEGER) AS c2, t3.c3, CAST(NULL AS INTEGER) AS c4, " + "t3.c5, 'c' AS q1 FROM t3" + ) + + def test_type_col_non_present(self): + t1, t2, t3 = self._fixture() + self.assert_compile( + polymorphic_union( + util.OrderedDict([("a", t1), ("b", t2), ("c", t3)]), + None + ), + "SELECT t1.c1, t1.c2, t1.c3, CAST(NULL AS INTEGER) AS c4, " + "CAST(NULL AS INTEGER) AS c5 FROM t1 UNION ALL SELECT t2.c1, " + "t2.c2, t2.c3, t2.c4, CAST(NULL AS INTEGER) AS c5 FROM t2 " + "UNION ALL SELECT t3.c1, CAST(NULL AS INTEGER) AS c2, t3.c3, " + "CAST(NULL AS INTEGER) AS c4, t3.c5 FROM t3" + ) + + def test_no_cast_null(self): + t1, t2, t3 = self._fixture() + self.assert_compile( + polymorphic_union( + util.OrderedDict([("a", t1), ("b", t2), ("c", t3)]), + 'q1', cast_nulls=False + ), + "SELECT t1.c1, t1.c2, t1.c3, NULL AS c4, NULL AS c5, 'a' AS q1 " + "FROM t1 UNION ALL SELECT t2.c1, t2.c2, t2.c3, t2.c4, NULL AS c5, " + "'b' AS q1 FROM t2 UNION ALL SELECT t3.c1, NULL AS c2, t3.c3, " + "NULL AS c4, t3.c5, 'c' AS q1 FROM t3" + ) + |