diff options
| -rw-r--r-- | doc/build/changelog/changelog_10.rst | 13 | ||||
| -rw-r--r-- | doc/build/changelog/migration_10.rst | 18 | ||||
| -rw-r--r-- | doc/build/core/selectable.rst | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 21 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/query.py | 30 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 17 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 131 | ||||
| -rw-r--r-- | test/dialect/test_oracle.py | 45 | ||||
| -rw-r--r-- | test/sql/test_cte.py | 30 |
9 files changed, 247 insertions, 61 deletions
diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index 7126d0930..32fe4daab 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -23,6 +23,19 @@ on compatibility concerns, see :doc:`/changelog/migration_10`. .. change:: + :tags: feature, oracle + :tickets: 3220 + + Added support for CTEs under Oracle. This includes some tweaks + to the aliasing syntax, as well as a new CTE feature + :meth:`.CTE.suffix_with`, which is useful for adding in special + Oracle-specific directives to the CTE. + + .. seealso:: + + :ref:`change_3220` + + .. change:: :tags: feature, mysql :tickets: 3121 diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst index 929a5fe3d..9fbbb889d 100644 --- a/doc/build/changelog/migration_10.rst +++ b/doc/build/changelog/migration_10.rst @@ -1616,6 +1616,24 @@ reflection from temp tables as well, which is :ticket:`3203`. :ticket:`3204` +.. _change_3220: + +Improved support for CTEs in Oracle +----------------------------------- + +CTE support has been fixed up for Oracle, and there is also a new feature +:meth:`.CTE.with_suffixes` that can assist with Oracle's special directives:: + + included_parts = select([ + part.c.sub_part, part.c.part, part.c.quantity + ]).where(part.c.part == "p1").\ + cte(name="included_parts", recursive=True).\ + suffix_with( + "search depth first by part set ord1", + "cycle part set y_cycle to 1 default 0", dialect='oracle') + +:ticket:`3220` + .. _change_2984: Drizzle Dialect is now an External Dialect diff --git a/doc/build/core/selectable.rst b/doc/build/core/selectable.rst index 52acb28e5..03ebeb4ab 100644 --- a/doc/build/core/selectable.rst +++ b/doc/build/core/selectable.rst @@ -60,6 +60,9 @@ elements are themselves :class:`.ColumnElement` subclasses). .. autoclass:: HasPrefixes :members: +.. autoclass:: HasSuffixes + :members: + .. autoclass:: Join :members: :inherited-members: diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 6df38e57e..524ba8115 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -549,6 +549,9 @@ class OracleCompiler(compiler.SQLCompiler): def visit_false(self, expr, **kw): return '0' + def get_cte_preamble(self, recursive): + return "WITH" + def get_select_hint_text(self, byfroms): return " ".join( "/*+ %s */" % text for table, text in byfroms.items() @@ -619,22 +622,10 @@ class OracleCompiler(compiler.SQLCompiler): return (self.dialect.identifier_preparer.format_sequence(seq) + ".nextval") - def visit_alias(self, alias, asfrom=False, ashint=False, **kwargs): - """Oracle doesn't like ``FROM table AS alias``. Is the AS standard - SQL?? - """ - - if asfrom or ashint: - alias_name = isinstance(alias.name, expression._truncated_label) and \ - self._truncated_identifier("alias", alias.name) or alias.name + def get_render_as_alias_suffix(self, alias_name_text): + """Oracle doesn't like ``FROM table AS alias``""" - if ashint: - return alias_name - elif asfrom: - return self.process(alias.original, asfrom=asfrom, **kwargs) + \ - " " + self.preparer.format_alias(alias, alias_name) - else: - return self.process(alias.original, **kwargs) + return " " + alias_name_text def returning_clause(self, stmt, returning_cols): columns = [] diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 790686288..9b7747e15 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -75,6 +75,7 @@ class Query(object): _having = None _distinct = False _prefixes = None + _suffixes = None _offset = None _limit = None _for_update_arg = None @@ -1003,7 +1004,7 @@ class Query(object): '_limit', '_offset', '_joinpath', '_joinpoint', '_distinct', '_having', - '_prefixes', + '_prefixes', '_suffixes' ): self.__dict__.pop(attr, None) self._set_select_from([fromclause], True) @@ -2359,12 +2360,38 @@ class Query(object): .. versionadded:: 0.7.7 + .. seealso:: + + :meth:`.HasPrefixes.prefix_with` + """ if self._prefixes: self._prefixes += prefixes else: self._prefixes = prefixes + @_generative() + def suffix_with(self, *suffixes): + """Apply the suffix to the query and return the newly resulting + ``Query``. + + :param \*suffixes: optional suffixes, typically strings, + not using any commas. + + .. versionadded:: 1.0.0 + + .. seealso:: + + :meth:`.Query.prefix_with` + + :meth:`.HasSuffixes.suffix_with` + + """ + if self._suffixes: + self._suffixes += suffixes + else: + self._suffixes = suffixes + def all(self): """Return the results represented by this ``Query`` as a list. @@ -2601,6 +2628,7 @@ class Query(object): 'offset': self._offset, 'distinct': self._distinct, 'prefixes': self._prefixes, + 'suffixes': self._suffixes, 'group_by': self._group_by or None, 'having': self._having } diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 29a7401a1..9304bba9f 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1193,12 +1193,16 @@ class SQLCompiler(Compiled): self, asfrom=True, **kwargs ) + if cte._suffixes: + text += " " + self._generate_prefixes( + cte, cte._suffixes, **kwargs) + self.ctes[cte] = text if asfrom: if cte_alias_name: text = self.preparer.format_alias(cte, cte_alias_name) - text += " AS " + cte_name + text += self.get_render_as_alias_suffix(cte_name) else: return self.preparer.format_alias(cte, cte_name) return text @@ -1217,8 +1221,8 @@ class SQLCompiler(Compiled): elif asfrom: ret = alias.original._compiler_dispatch(self, asfrom=True, **kwargs) + \ - " AS " + \ - self.preparer.format_alias(alias, alias_name) + self.get_render_as_alias_suffix( + self.preparer.format_alias(alias, alias_name)) if fromhints and alias in fromhints: ret = self.format_from_hint_text(ret, alias, @@ -1228,6 +1232,9 @@ class SQLCompiler(Compiled): else: return alias.original._compiler_dispatch(self, **kwargs) + def get_render_as_alias_suffix(self, alias_name_text): + return " AS " + alias_name_text + def _add_to_result_map(self, keyname, name, objects, type_): if not self.dialect.case_sensitive: keyname = keyname.lower() @@ -1554,6 +1561,10 @@ class SQLCompiler(Compiled): compound_index == 0 and toplevel: text = self._render_cte_clause() + text + if select._suffixes: + text += " " + self._generate_prefixes( + select, select._suffixes, **kwargs) + self.stack.pop(-1) if asfrom and parens: diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 8198a6733..87029ec2b 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -171,6 +171,79 @@ class Selectable(ClauseElement): return self +class HasPrefixes(object): + _prefixes = () + + @_generative + def prefix_with(self, *expr, **kw): + """Add one or more expressions following the statement keyword, i.e. + SELECT, INSERT, UPDATE, or DELETE. Generative. + + This is used to support backend-specific prefix keywords such as those + provided by MySQL. + + E.g.:: + + stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql") + + Multiple prefixes can be specified by multiple calls + to :meth:`.prefix_with`. + + :param \*expr: textual or :class:`.ClauseElement` construct which + will be rendered following the INSERT, UPDATE, or DELETE + keyword. + :param \**kw: A single keyword 'dialect' is accepted. This is an + optional string dialect name which will + limit rendering of this prefix to only that dialect. + + """ + dialect = kw.pop('dialect', None) + if kw: + raise exc.ArgumentError("Unsupported argument(s): %s" % + ",".join(kw)) + self._setup_prefixes(expr, dialect) + + def _setup_prefixes(self, prefixes, dialect=None): + self._prefixes = self._prefixes + tuple( + [(_literal_as_text(p, warn=False), dialect) for p in prefixes]) + + +class HasSuffixes(object): + _suffixes = () + + @_generative + def suffix_with(self, *expr, **kw): + """Add one or more expressions following the statement as a whole. + + This is used to support backend-specific suffix keywords on + certain constructs. + + E.g.:: + + stmt = select([col1, col2]).cte().suffix_with( + "cycle empno set y_cycle to 1 default 0", dialect="oracle") + + Multiple prefixes can be specified by multiple calls + to :meth:`.suffix_with`. + + :param \*expr: textual or :class:`.ClauseElement` construct which + will be rendered following the target clause. + :param \**kw: A single keyword 'dialect' is accepted. This is an + optional string dialect name which will + limit rendering of this suffix to only that dialect. + + """ + dialect = kw.pop('dialect', None) + if kw: + raise exc.ArgumentError("Unsupported argument(s): %s" % + ",".join(kw)) + self._setup_suffixes(expr, dialect) + + def _setup_suffixes(self, suffixes, dialect=None): + self._suffixes = self._suffixes + tuple( + [(_literal_as_text(p, warn=False), dialect) for p in suffixes]) + + class FromClause(Selectable): """Represent an element that can be used within the ``FROM`` clause of a ``SELECT`` statement. @@ -1088,7 +1161,7 @@ class Alias(FromClause): return self.element.bind -class CTE(Alias): +class CTE(Generative, HasSuffixes, Alias): """Represent a Common Table Expression. The :class:`.CTE` object is obtained using the @@ -1104,10 +1177,13 @@ class CTE(Alias): name=None, recursive=False, _cte_alias=None, - _restates=frozenset()): + _restates=frozenset(), + _suffixes=None): self.recursive = recursive self._cte_alias = _cte_alias self._restates = _restates + if _suffixes: + self._suffixes = _suffixes super(CTE, self).__init__(selectable, name=name) def alias(self, name=None, flat=False): @@ -1116,6 +1192,7 @@ class CTE(Alias): name=name, recursive=self.recursive, _cte_alias=self, + _suffixes=self._suffixes ) def union(self, other): @@ -1123,7 +1200,8 @@ class CTE(Alias): self.original.union(other), name=self.name, recursive=self.recursive, - _restates=self._restates.union([self]) + _restates=self._restates.union([self]), + _suffixes=self._suffixes ) def union_all(self, other): @@ -1131,7 +1209,8 @@ class CTE(Alias): self.original.union_all(other), name=self.name, recursive=self.recursive, - _restates=self._restates.union([self]) + _restates=self._restates.union([self]), + _suffixes=self._suffixes ) @@ -2118,44 +2197,7 @@ class CompoundSelect(GenerativeSelect): bind = property(bind, _set_bind) -class HasPrefixes(object): - _prefixes = () - - @_generative - def prefix_with(self, *expr, **kw): - """Add one or more expressions following the statement keyword, i.e. - SELECT, INSERT, UPDATE, or DELETE. Generative. - - This is used to support backend-specific prefix keywords such as those - provided by MySQL. - - E.g.:: - - stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql") - - Multiple prefixes can be specified by multiple calls - to :meth:`.prefix_with`. - - :param \*expr: textual or :class:`.ClauseElement` construct which - will be rendered following the INSERT, UPDATE, or DELETE - keyword. - :param \**kw: A single keyword 'dialect' is accepted. This is an - optional string dialect name which will - limit rendering of this prefix to only that dialect. - - """ - dialect = kw.pop('dialect', None) - if kw: - raise exc.ArgumentError("Unsupported argument(s): %s" % - ",".join(kw)) - self._setup_prefixes(expr, dialect) - - def _setup_prefixes(self, prefixes, dialect=None): - self._prefixes = self._prefixes + tuple( - [(_literal_as_text(p, warn=False), dialect) for p in prefixes]) - - -class Select(HasPrefixes, GenerativeSelect): +class Select(HasPrefixes, HasSuffixes, GenerativeSelect): """Represents a ``SELECT`` statement. """ @@ -2163,6 +2205,7 @@ class Select(HasPrefixes, GenerativeSelect): __visit_name__ = 'select' _prefixes = () + _suffixes = () _hints = util.immutabledict() _statement_hints = () _distinct = False @@ -2180,6 +2223,7 @@ class Select(HasPrefixes, GenerativeSelect): having=None, correlate=True, prefixes=None, + suffixes=None, **kwargs): """Construct a new :class:`.Select`. @@ -2425,6 +2469,9 @@ class Select(HasPrefixes, GenerativeSelect): if prefixes: self._setup_prefixes(prefixes) + if suffixes: + self._setup_suffixes(suffixes) + GenerativeSelect.__init__(self, **kwargs) @property diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index a771c5d80..b2a490e71 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -180,6 +180,51 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t.update().values(plain=5), 'UPDATE s SET "plain"=:"plain"' ) + def test_cte(self): + part = table( + 'part', + column('part'), + column('sub_part'), + column('quantity') + ) + + included_parts = select([ + part.c.sub_part, part.c.part, part.c.quantity + ]).where(part.c.part == "p1").\ + cte(name="included_parts", recursive=True).\ + suffix_with( + "search depth first by part set ord1", + "cycle part set y_cycle to 1 default 0", dialect='oracle') + + incl_alias = included_parts.alias("pr1") + parts_alias = part.alias("p") + included_parts = included_parts.union_all( + select([ + parts_alias.c.sub_part, + parts_alias.c.part, parts_alias.c.quantity + ]).where(parts_alias.c.part == incl_alias.c.sub_part) + ) + + q = select([ + included_parts.c.sub_part, + func.sum(included_parts.c.quantity).label('total_quantity')]).\ + group_by(included_parts.c.sub_part) + + self.assert_compile( + q, + "WITH included_parts(sub_part, part, quantity) AS " + "(SELECT part.sub_part AS sub_part, part.part AS part, " + "part.quantity AS quantity FROM part WHERE part.part = :part_1 " + "UNION ALL SELECT p.sub_part AS sub_part, p.part AS part, " + "p.quantity AS quantity FROM part p, included_parts pr1 " + "WHERE p.part = pr1.sub_part) " + "search depth first by part set ord1 cycle part set " + "y_cycle to 1 default 0 " + "SELECT included_parts.sub_part, sum(included_parts.quantity) " + "AS total_quantity FROM included_parts " + "GROUP BY included_parts.sub_part" + ) + def test_limit(self): t = table('sometable', column('col1'), column('col2')) s = select([t]) diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index b907fe649..c7906dcb7 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -462,3 +462,33 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): 'FROM "order" JOIN regional_sales AS anon_1 ' 'ON anon_1."order" = "order"."order"' ) + + def test_suffixes(self): + orders = table('order', column('order')) + s = select([orders.c.order]).cte("regional_sales") + s = s.suffix_with("pg suffix", dialect='postgresql') + s = s.suffix_with('oracle suffix', dialect='oracle') + stmt = select([orders]).where(orders.c.order > s.c.order) + + self.assert_compile( + stmt, + 'WITH regional_sales AS (SELECT "order"."order" AS "order" ' + 'FROM "order") SELECT "order"."order" FROM "order", ' + 'regional_sales WHERE "order"."order" > regional_sales."order"' + ) + + self.assert_compile( + stmt, + 'WITH regional_sales AS (SELECT "order"."order" AS "order" ' + 'FROM "order") oracle suffix SELECT "order"."order" FROM "order", ' + 'regional_sales WHERE "order"."order" > regional_sales."order"', + dialect='oracle' + ) + + self.assert_compile( + stmt, + 'WITH regional_sales AS (SELECT "order"."order" AS "order" ' + 'FROM "order") pg suffix SELECT "order"."order" FROM "order", ' + 'regional_sales WHERE "order"."order" > regional_sales."order"', + dialect='postgresql' + )
\ No newline at end of file |