- 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.
fixes #3220

4 files changed, 138 insertions, 61 deletions

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