- The ``version_id_generator`` parameter of ``Mapper`` can now be specified

to rely upon server generated version identifiers, using triggers
or other database-provided versioning features, by passing the value
``False``.  The ORM will use RETURNING when available to immediately
load the new version identifier, else it will emit a second SELECT.
[ticket:2793]
- The ``eager_defaults`` flag of :class:`.Mapper` will now allow the
newly generated default values to be fetched using an inline
RETURNING clause, rather than a second SELECT statement, for backends
that support RETURNING.
- Added a new variant to :meth:`.ValuesBase.returning` called
:meth:`.ValuesBase.return_defaults`; this allows arbitrary columns
to be added to the RETURNING clause of the statement without interfering
with the compilers usual "implicit returning" feature, which is used to
efficiently fetch newly generated primary key values.  For supporting
backends, a dictionary of all fetched values is present at
:attr:`.ResultProxy.returned_defaults`.
- add a glossary entry for RETURNING
- add documentation for version id generation, [ticket:867]

3 files changed, 113 insertions, 151 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 6370b1227..5d05cbc29 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1761,11 +1761,12 @@ class SQLCompiler(Compiled):
                         '=' + c[1] for c in colparams
                         )
 
-        if update_stmt._returning:
-            self.returning = update_stmt._returning
+        if self.returning or update_stmt._returning:
+            if not self.returning:
+                self.returning = update_stmt._returning
             if self.returning_precedes_values:
                 text += " " + self.returning_clause(
-                                    update_stmt, update_stmt._returning)
+                                    update_stmt, self.returning)
 
         if extra_froms:
             extra_from_text = self.update_from_clause(
@@ -1785,7 +1786,7 @@ class SQLCompiler(Compiled):
 
         if self.returning and not self.returning_precedes_values:
             text += " " + self.returning_clause(
-                                    update_stmt, update_stmt._returning)
+                                    update_stmt, self.returning)
 
         self.stack.pop(-1)
 
@@ -1866,6 +1867,19 @@ class SQLCompiler(Compiled):
                                 self.dialect.implicit_returning and \
                                 stmt.table.implicit_returning
 
+        if self.isinsert:
+            implicit_return_defaults = implicit_returning and stmt._return_defaults
+        elif self.isupdate:
+            implicit_return_defaults = self.dialect.implicit_returning and \
+                                stmt.table.implicit_returning and \
+                                stmt._return_defaults
+
+        if implicit_return_defaults:
+            if stmt._return_defaults is True:
+                implicit_return_defaults = set(stmt.table.c)
+            else:
+                implicit_return_defaults = set(stmt._return_defaults)
+
         postfetch_lastrowid = need_pks and self.dialect.postfetch_lastrowid
 
         check_columns = {}
@@ -1928,6 +1942,10 @@ class SQLCompiler(Compiled):
                 elif c.primary_key and implicit_returning:
                     self.returning.append(c)
                     value = self.process(value.self_group())
+                elif implicit_return_defaults and \
+                    c in implicit_return_defaults:
+                    self.returning.append(c)
+                    value = self.process(value.self_group())
                 else:
                     self.postfetch.append(c)
                     value = self.process(value.self_group())
@@ -1984,14 +2002,20 @@ class SQLCompiler(Compiled):
                             not self.dialect.sequences_optional):
                             proc = self.process(c.default)
                             values.append((c, proc))
-                            if not c.primary_key:
+                            if implicit_return_defaults and \
+                                c in implicit_return_defaults:
+                                self.returning.append(c)
+                            elif not c.primary_key:
                                 self.postfetch.append(c)
                     elif c.default.is_clause_element:
                         values.append(
                             (c, self.process(c.default.arg.self_group()))
                         )
 
-                        if not c.primary_key:
+                        if implicit_return_defaults and \
+                            c in implicit_return_defaults:
+                            self.returning.append(c)
+                        elif not c.primary_key:
                             # dont add primary key column to postfetch
                             self.postfetch.append(c)
                     else:
@@ -2000,8 +2024,14 @@ class SQLCompiler(Compiled):
                         )
                         self.prefetch.append(c)
                 elif c.server_default is not None:
-                    if not c.primary_key:
+                    if implicit_return_defaults and \
+                        c in implicit_return_defaults:
+                        self.returning.append(c)
+                    elif not c.primary_key:
                         self.postfetch.append(c)
+                elif implicit_return_defaults and \
+                        c in implicit_return_defaults:
+                        self.returning.append(c)
 
             elif self.isupdate:
                 if c.onupdate is not None and not c.onupdate.is_sequence:
@@ -2009,14 +2039,25 @@ class SQLCompiler(Compiled):
                         values.append(
                             (c, self.process(c.onupdate.arg.self_group()))
                         )
-                        self.postfetch.append(c)
+                        if implicit_return_defaults and \
+                            c in implicit_return_defaults:
+                            self.returning.append(c)
+                        else:
+                            self.postfetch.append(c)
                     else:
                         values.append(
                             (c, self._create_crud_bind_param(c, None))
                         )
                         self.prefetch.append(c)
                 elif c.server_onupdate is not None:
-                    self.postfetch.append(c)
+                    if implicit_return_defaults and \
+                        c in implicit_return_defaults:
+                        self.returning.append(c)
+                    else:
+                        self.postfetch.append(c)
+                elif implicit_return_defaults and \
+                        c in implicit_return_defaults:
+                        self.returning.append(c)
 
         if parameters and stmt_parameters:
             check = set(parameters).intersection(
diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py
index cbebf7d55..abbd05efe 100644
--- a/lib/sqlalchemy/sql/dml.py
+++ b/lib/sqlalchemy/sql/dml.py
@@ -104,9 +104,14 @@ class UpdateBase(HasPrefixes, Executable, ClauseElement):
         read the documentation notes for the database in use in
         order to determine the availability of RETURNING.
 
+        .. seealso::
+
+          :meth:`.ValuesBase.return_defaults`
+
         """
         self._returning = cols
 
+
     @_generative
     def with_hint(self, text, selectable=None, dialect_name="*"):
         """Add a table hint for a single table to this
@@ -303,6 +308,58 @@ class ValuesBase(UpdateBase):
             else:
                 self.parameters.update(kwargs)
 
+    @_generative
+    def return_defaults(self, *cols):
+        """If available, make use of a RETURNING clause for the purpose
+        of fetching server-side expressions and defaults.
+
+        When used against a backend that supports RETURNING, all column
+        values generated by SQL expression or server-side-default will be added
+        to any existing RETURNING clause, excluding one that is specified
+        by the :meth:`.UpdateBase.returning` method.   The column values
+        will then be available on the result using the
+        :meth:`.ResultProxy.server_returned_defaults` method as a
+        dictionary, referring to values keyed to the :meth:`.Column` object
+        as well as its ``.key``.
+
+        This method differs from :meth:`.UpdateBase.returning` in these ways:
+
+        1. It is compatible with any backend.  Backends that don't support
+           RETURNING will skip the usage of the feature, rather than raising
+           an exception.  The return value of :attr:`.ResultProxy.returned_defaults`
+           will be ``None``
+
+        2. It is compatible with the existing logic to fetch auto-generated
+           primary key values, also known as "implicit returning".  Backends that
+           support RETURNING will automatically make use of RETURNING in order
+           to fetch the value of newly generated primary keys; while the
+           :meth:`.UpdateBase.returning` method circumvents this behavior,
+           :meth:`.UpdateBase.return_defaults` leaves it intact.
+
+        3. :meth:`.UpdateBase.returning` leaves the cursor's rows ready for
+           fetching using methods like :meth:`.ResultProxy.fetchone`, whereas
+           :meth:`.ValuesBase.return_defaults` fetches the row internally.
+           While all DBAPI backends observed so far seem to only support
+           RETURNING with single-row executions,
+           technically :meth:`.UpdateBase.returning` would support a backend
+           that can deliver multiple RETURNING rows as well.  However
+           :meth:`.ValuesBase.return_defaults` is single-row by definition.
+
+        :param cols: optional list of column key names or :class:`.Column`
+         objects.  If omitted, all column expressions evaulated on the server
+         are added to the returning list.
+
+        .. versionadded:: 0.9.0
+
+        .. seealso::
+
+            :meth:`.UpdateBase.returning`
+
+            :meth:`.ResultProxy.returned_defaults`
+
+        """
+        self._return_defaults = cols or True
+
 
 class Insert(ValuesBase):
     """Represent an INSERT construct.
@@ -326,52 +383,15 @@ class Insert(ValuesBase):
                 bind=None,
                 prefixes=None,
                 returning=None,
+                return_defaults=False,
                 **kwargs):
-        """Construct an :class:`.Insert` object.
-
-        Similar functionality is available via the
-        :meth:`~.TableClause.insert` method on
-        :class:`~.schema.Table`.
-
-        :param table: :class:`.TableClause` which is the subject of the insert.
-
-        :param values: collection of values to be inserted; see
-         :meth:`.Insert.values` for a description of allowed formats here.
-         Can be omitted entirely; a :class:`.Insert` construct will also
-         dynamically render the VALUES clause at execution time based on
-         the parameters passed to :meth:`.Connection.execute`.
-
-        :param inline: if True, SQL defaults will be compiled 'inline' into the
-          statement and not pre-executed.
-
-        If both `values` and compile-time bind parameters are present, the
-        compile-time bind parameters override the information specified
-        within `values` on a per-key basis.
-
-        The keys within `values` can be either :class:`~sqlalchemy.schema.Column`
-        objects or their string identifiers. Each key may reference one of:
-
-        * a literal data value (i.e. string, number, etc.);
-        * a Column object;
-        * a SELECT statement.
-
-        If a ``SELECT`` statement is specified which references this
-        ``INSERT`` statement's table, the statement will be correlated
-        against the ``INSERT`` statement.
-
-        .. seealso::
-
-            :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial
-
-            :ref:`inserts_and_updates` - SQL Expression Tutorial
-
-        """
         ValuesBase.__init__(self, table, values, prefixes)
         self._bind = bind
         self.select = None
         self.inline = inline
         self._returning = returning
         self.kwargs = kwargs
+        self._return_defaults = return_defaults
 
     def get_children(self, **kwargs):
         if self.select is not None:
@@ -446,109 +466,8 @@ class Update(ValuesBase):
                 bind=None,
                 prefixes=None,
                 returning=None,
+                return_defaults=False,
                 **kwargs):
-        """Construct an :class:`.Update` object.
-
-        E.g.::
-
-            from sqlalchemy import update
-
-            stmt = update(users).where(users.c.id==5).\\
-                    values(name='user #5')
-
-        Similar functionality is available via the
-        :meth:`~.TableClause.update` method on
-        :class:`.Table`::
-
-            stmt = users.update().\\
-                        where(users.c.id==5).\\
-                        values(name='user #5')
-
-        :param table: A :class:`.Table` object representing the database
-         table to be updated.
-
-        :param whereclause: Optional SQL expression describing the ``WHERE``
-         condition of the ``UPDATE`` statement.   Modern applications
-         may prefer to use the generative :meth:`~Update.where()`
-         method to specify the ``WHERE`` clause.
-
-         The WHERE clause can refer to multiple tables.
-         For databases which support this, an ``UPDATE FROM`` clause will
-         be generated, or on MySQL, a multi-table update.  The statement
-         will fail on databases that don't have support for multi-table
-         update statements.  A SQL-standard method of referring to
-         additional tables in the WHERE clause is to use a correlated
-         subquery::
-
-            users.update().values(name='ed').where(
-                    users.c.name==select([addresses.c.email_address]).\\
-                                where(addresses.c.user_id==users.c.id).\\
-                                as_scalar()
-                    )
-
-         .. versionchanged:: 0.7.4
-             The WHERE clause can refer to multiple tables.
-
-        :param values:
-          Optional dictionary which specifies the ``SET`` conditions of the
-          ``UPDATE``.  If left as ``None``, the ``SET``
-          conditions are determined from those parameters passed to the
-          statement during the execution and/or compilation of the
-          statement.   When compiled standalone without any parameters,
-          the ``SET`` clause generates for all columns.
-
-          Modern applications may prefer to use the generative
-          :meth:`.Update.values` method to set the values of the
-          UPDATE statement.
-
-        :param inline:
-          if True, SQL defaults present on :class:`.Column` objects via
-          the ``default`` keyword will be compiled 'inline' into the statement
-          and not pre-executed.  This means that their values will not
-          be available in the dictionary returned from
-          :meth:`.ResultProxy.last_updated_params`.
-
-        If both ``values`` and compile-time bind parameters are present, the
-        compile-time bind parameters override the information specified
-        within ``values`` on a per-key basis.
-
-        The keys within ``values`` can be either :class:`.Column`
-        objects or their string identifiers (specifically the "key" of the
-        :class:`.Column`, normally but not necessarily equivalent to
-        its "name").  Normally, the
-        :class:`.Column` objects used here are expected to be
-        part of the target :class:`.Table` that is the table
-        to be updated.  However when using MySQL, a multiple-table
-        UPDATE statement can refer to columns from any of
-        the tables referred to in the WHERE clause.
-
-        The values referred to in ``values`` are typically:
-
-        * a literal data value (i.e. string, number, etc.)
-        * a SQL expression, such as a related :class:`.Column`,
-          a scalar-returning :func:`.select` construct,
-          etc.
-
-        When combining :func:`.select` constructs within the values
-        clause of an :func:`.update` construct,
-        the subquery represented by the :func:`.select` should be
-        *correlated* to the parent table, that is, providing criterion
-        which links the table inside the subquery to the outer table
-        being updated::
-
-            users.update().values(
-                    name=select([addresses.c.email_address]).\\
-                            where(addresses.c.user_id==users.c.id).\\
-                            as_scalar()
-                )
-
-        .. seealso::
-
-            :ref:`inserts_and_updates` - SQL Expression
-            Language Tutorial
-
-
-        """
         ValuesBase.__init__(self, table, values, prefixes)
         self._bind = bind
         self._returning = returning
@@ -558,6 +477,7 @@ class Update(ValuesBase):
             self._whereclause = None
         self.inline = inline
         self.kwargs = kwargs
+        self._return_defaults = return_defaults
 
 
     def get_children(self, **kwargs):
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index bbbe0b235..01091bc0a 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -49,7 +49,7 @@ from .selectable import Alias, Join, Select, Selectable, TableClause, \
         subquery, HasPrefixes, Exists, ScalarSelect
 
 
-from .dml import Insert, Update, Delete
+from .dml import Insert, Update, Delete, UpdateBase, ValuesBase
 
 # factory functions - these pull class-bound constructors and classmethods
 # from SQL elements and selectables into public functions.  This allows
@@ -101,6 +101,7 @@ from .elements import _literal_as_text, _clause_element_as_expr,\
 from .selectable import _interpret_as_from
 
 
+
 # old names for compatibility
 _Executable = Executable
 _BindParamClause = BindParameter