Support for generated columns

Added DDL support for "computed columns"; these are DDL column
specifications for columns that have a server-computed value, either upon
SELECT (known as "virtual") or at the point of which they are INSERTed or
UPDATEd (known as "stored").  Support is established for Postgresql, MySQL,
Oracle SQL Server and Firebird. Thanks to Federico Caselli for lots of work
on this one.

ORM round trip tests included.  The ORM makes use of existing
FetchedValue support and no additional ORM logic is present for
the basic feature.

It has been observed that Oracle RETURNING does not return the
new value of a computed column upon UPDATE; it returns the
prior value.  As this is very dangerous, a warning is emitted
if a computed column is rendered into the RETURNING clause
of an UPDATE statement.

Fixes: #4894
Closes: #4928
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4928
Pull-request-sha: d39c521d5ac6ebfb4fb5b53846451de79752e64c

Change-Id: I2610b2999a5b1b127ed927dcdaeee98b769643ce

13 files changed, 241 insertions, 13 deletions

diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index a1e166a0d..92128ed22 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -17,6 +17,7 @@ from .schema import DefaultClause  # noqa
 from .schema import FetchedValue  # noqa
 from .schema import ForeignKey  # noqa
 from .schema import ForeignKeyConstraint  # noqa
+from .schema import Computed  # noqa
 from .schema import Index  # noqa
 from .schema import MetaData  # noqa
 from .schema import PrimaryKeyConstraint  # noqa
diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py
index c7c921cb4..052ecf05b 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -581,6 +581,17 @@ class FBDDLCompiler(sql.compiler.DDLCompiler):
                 drop.element
             )
 
+    def visit_computed_column(self, generated):
+        if generated.persisted is not None:
+            raise exc.CompileError(
+                "Firebird computed columns do not support a persistence "
+                "method setting; set the 'persisted' flag to None for "
+                "Firebird support."
+            )
+        return "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process(
+            generated.sqltext, include_table=False, literal_binds=True
+        )
+
 
 class FBIdentifierPreparer(sql.compiler.IdentifierPreparer):
     """Install Firebird specific reserved words."""
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 6c7b732ce..5d4de4a33 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1942,13 +1942,15 @@ class MSSQLStrictCompiler(MSSQLCompiler):
 
 class MSDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
-        colspec = (
-            self.preparer.format_column(column)
-            + " "
-            + self.dialect.type_compiler.process(
+        colspec = self.preparer.format_column(column)
+
+        # type is not accepted in a computed column
+        if column.computed is not None:
+            colspec += " " + self.process(column.computed)
+        else:
+            colspec += " " + self.dialect.type_compiler.process(
                 column.type, type_expression=column
             )
-        )
 
         if column.nullable is not None:
             if (
@@ -1958,7 +1960,8 @@ class MSDDLCompiler(compiler.DDLCompiler):
                 or column.autoincrement is True
             ):
                 colspec += " NOT NULL"
-            else:
+            elif column.computed is None:
+                # don't specify "NULL" for computed columns
                 colspec += " NULL"
 
         if column.table is None:
@@ -2110,6 +2113,15 @@ class MSDDLCompiler(compiler.DDLCompiler):
         text += self.define_constraint_deferrability(constraint)
         return text
 
+    def visit_computed_column(self, generated):
+        text = "AS (%s)" % self.sql_compiler.process(
+            generated.sqltext, include_table=False, literal_binds=True
+        )
+        # explicitly check for True|False since None means server default
+        if generated.persisted is True:
+            text += " PERSISTED"
+        return text
+
 
 class MSIdentifierPreparer(compiler.IdentifierPreparer):
     reserved_words = RESERVED_WORDS
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 73484aea1..05edb6310 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1503,6 +1503,9 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             ),
         ]
 
+        if column.computed is not None:
+            colspec.append(self.process(column.computed))
+
         is_timestamp = isinstance(
             column.type._unwrapped_dialect_impl(self.dialect),
             sqltypes.TIMESTAMP,
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 4c5a717b9..c1e91fb12 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -446,6 +446,7 @@ columns for non-unique indexes, all but the last column for unique indexes).
 from itertools import groupby
 import re
 
+from ... import Computed
 from ... import exc
 from ... import schema as sa_schema
 from ... import sql
@@ -905,6 +906,16 @@ class OracleCompiler(compiler.SQLCompiler):
         for i, column in enumerate(
             expression._select_iterables(returning_cols)
         ):
+            if self.isupdate and isinstance(column.server_default, Computed):
+                util.warn(
+                    "Computed columns don't work with Oracle UPDATE "
+                    "statements that use RETURNING; the value of the column "
+                    "*before* the UPDATE takes place is returned.   It is "
+                    "advised to not use RETURNING with an Oracle computed "
+                    "column.  Consider setting implicit_returning to False on "
+                    "the Table object in order to avoid implicit RETURNING "
+                    "clauses from being generated for this Table."
+                )
             if column.type._has_column_expression:
                 col_expr = column.type.column_expression(column)
             else:
@@ -1186,6 +1197,19 @@ class OracleDDLCompiler(compiler.DDLCompiler):
 
         return "".join(table_opts)
 
+    def visit_computed_column(self, generated):
+        text = "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process(
+            generated.sqltext, include_table=False, literal_binds=True
+        )
+        if generated.persisted is True:
+            raise exc.CompileError(
+                "Oracle computed columns do not support 'stored' persistence; "
+                "set the 'persisted' flag to None or False for Oracle support."
+            )
+        elif generated.persisted is False:
+            text += " VIRTUAL"
+        return text
+
 
 class OracleIdentifierPreparer(compiler.IdentifierPreparer):
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index e94f9913c..d6fd2623b 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1873,6 +1873,9 @@ class PGDDLCompiler(compiler.DDLCompiler):
             if default is not None:
                 colspec += " DEFAULT " + default
 
+        if column.computed is not None:
+            colspec += " " + self.process(column.computed)
+
         if not column.nullable:
             colspec += " NOT NULL"
         return colspec
@@ -2043,6 +2046,18 @@ class PGDDLCompiler(compiler.DDLCompiler):
 
         return "".join(table_opts)
 
+    def visit_computed_column(self, generated):
+        if generated.persisted is False:
+            raise exc.CompileError(
+                "PostrgreSQL computed columns do not support 'virtual' "
+                "persistence; set the 'persisted' flag to None or True for "
+                "PostgreSQL support."
+            )
+
+        return "GENERATED ALWAYS AS (%s) STORED" % self.sql_compiler.process(
+            generated.sqltext, include_table=False, literal_binds=True
+        )
+
 
 class PGTypeCompiler(compiler.GenericTypeCompiler):
     def visit_TSVECTOR(self, type_, **kw):
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index c1f108a0d..02d44a260 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1032,6 +1032,9 @@ class SQLiteCompiler(compiler.SQLCompiler):
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
+        if column.computed is not None:
+            raise exc.CompileError("SQLite does not support computed columns")
+
         coltype = self.dialect.type_compiler.process(
             column.type, type_expression=column
         )
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index f50acdb1c..6adeded36 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -47,6 +47,7 @@ from .sql.schema import DefaultGenerator  # noqa
 from .sql.schema import FetchedValue  # noqa
 from .sql.schema import ForeignKey  # noqa
 from .sql.schema import ForeignKeyConstraint  # noqa
+from .sql.schema import Computed  # noqa
 from .sql.schema import Index  # noqa
 from .sql.schema import MetaData  # noqa
 from .sql.schema import PrimaryKeyConstraint  # noqa
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 546fffc6c..85c1750b7 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3173,6 +3173,9 @@ class DDLCompiler(Compiled):
         if default is not None:
             colspec += " DEFAULT " + default
 
+        if column.computed is not None:
+            colspec += " " + self.process(column.computed)
+
         if not column.nullable:
             colspec += " NOT NULL"
         return colspec
@@ -3314,6 +3317,16 @@ class DDLCompiler(Compiled):
             text += " MATCH %s" % constraint.match
         return text
 
+    def visit_computed_column(self, generated):
+        text = "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process(
+            generated.sqltext, include_table=False, literal_binds=True
+        )
+        if generated.persisted is True:
+            text += " STORED"
+        elif generated.persisted is False:
+            text += " VIRTUAL"
+        return text
+
 
 class GenericTypeCompiler(TypeCompiler):
     def visit_FLOAT(self, type_, **kw):
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index ee7dc61ce..8c325538c 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -1028,9 +1028,9 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause):
           :class:`.SchemaItem` derived constructs which will be applied
           as options to the column.  These include instances of
           :class:`.Constraint`, :class:`.ForeignKey`, :class:`.ColumnDefault`,
-          and :class:`.Sequence`.  In some cases an equivalent keyword
-          argument is available such as ``server_default``, ``default``
-          and ``unique``.
+          :class:`.Sequence`, :class:`.Computed`.  In some cases an
+          equivalent keyword argument is available such as ``server_default``,
+          ``default`` and ``unique``.
 
         :param autoincrement: Set up "auto increment" semantics for an integer
           primary key column.  The default value is the string ``"auto"``
@@ -1296,6 +1296,7 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause):
         self.constraints = set()
         self.foreign_keys = set()
         self.comment = kwargs.pop("comment", None)
+        self.computed = None
 
         # check if this Column is proxying another column
         if "_proxies" in kwargs:
@@ -1502,6 +1503,12 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause):
             c.copy(**kw) for c in self.constraints if not c._type_bound
         ] + [c.copy(**kw) for c in self.foreign_keys if not c.constraint]
 
+        server_default = self.server_default
+        server_onupdate = self.server_onupdate
+        if isinstance(server_default, Computed):
+            server_default = server_onupdate = None
+            args.append(self.server_default.copy(**kw))
+
         type_ = self.type
         if isinstance(type_, SchemaEventTarget):
             type_ = type_.copy(**kw)
@@ -1518,9 +1525,9 @@ class Column(DialectKWArgs, SchemaItem, ColumnClause):
             index=self.index,
             autoincrement=self.autoincrement,
             default=self.default,
-            server_default=self.server_default,
+            server_default=server_default,
             onupdate=self.onupdate,
-            server_onupdate=self.server_onupdate,
+            server_onupdate=server_onupdate,
             doc=self.doc,
             comment=self.comment,
             *args
@@ -4348,3 +4355,89 @@ class _SchemaTranslateMap(object):
 
 _default_schema_map = _SchemaTranslateMap(None)
 _schema_getter = _SchemaTranslateMap._schema_getter
+
+
+class Computed(FetchedValue, SchemaItem):
+    """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
+
+    The :class:`.Computed` construct is an inline construct added to the
+    argument list of a :class:`.Column` object::
+
+        from sqlalchemy import Computed
+
+        Table('square', meta,
+            Column('side', Float, nullable=False),
+            Column('area', Float, Computed('side * side'))
+        )
+
+    See the linked documentation below for complete details.
+
+    .. versionadded:: 1.3.11
+
+    .. seealso::
+
+        :ref:`computed_ddl`
+
+    """
+
+    __visit_name__ = "computed_column"
+
+    @_document_text_coercion(
+        "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
+    )
+    def __init__(self, sqltext, persisted=None):
+        """Construct a GENERATED ALWAYS AS DDL construct to accompany a
+        :class:`.Column`.
+
+        :param sqltext:
+          A string containing the column generation expression, which will be
+          used verbatim, or a SQL expression construct, such as a :func:`.text`
+          object.   If given as a string, the object is converted to a
+          :func:`.text` object.
+
+        :param persisted:
+          Optional, controls how this column should be persisted by the
+          database.   Possible values are:
+
+          * None, the default, it will use the default persistence defined
+            by the database.
+          * True, will render ``GENERATED ALWAYS AS ... STORED``, or the
+            equivalent for the target database if supported
+          * False, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or the
+            equivalent for the target database if supported.
+
+          Specifying ``True`` or ``False`` may raise an error when the DDL
+          is emitted to the target database if the databse does not support
+          that persistence option.   Leaving this parameter at its default
+          of ``None`` is guaranteed to succeed for all databases that support
+          ``GENERATED ALWAYS AS``.
+
+        """
+        self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
+        self.persisted = persisted
+        self.column = None
+
+    def _set_parent(self, parent):
+        if not isinstance(
+            parent.server_default, (type(None), Computed)
+        ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
+            raise exc.ArgumentError(
+                "A generated column cannot specify a server_default or a "
+                "server_onupdate argument"
+            )
+        self.column = parent
+        parent.computed = self
+        self.column.server_onupdate = self
+        self.column.server_default = self
+
+    def _as_for_update(self, for_update):
+        return self
+
+    def copy(self, target_table=None, **kw):
+        if target_table is not None:
+            sqltext = _copy_expression(self.sqltext, self.table, target_table)
+        else:
+            sqltext = self.sqltext
+        g = Computed(sqltext, persisted=self.persisted)
+
+        return self._schema_item_copy(g)
diff --git a/lib/sqlalchemy/testing/config.py b/lib/sqlalchemy/testing/config.py
index 87bbc6a0f..8262142ec 100644
--- a/lib/sqlalchemy/testing/config.py
+++ b/lib/sqlalchemy/testing/config.py
@@ -44,7 +44,7 @@ def combinations(*comb, **kw):
      well as if it is included in the tokens used to create the id of the
      parameter set.
 
-     If omitted, the argment combinations are passed to parametrize as is.  If
+     If omitted, the argument combinations are passed to parametrize as is.  If
      passed, each argument combination is turned into a pytest.param() object,
      mapping the elements of the argument tuple to produce an id based on a
      character value in the same position within the string template using the
@@ -59,9 +59,12 @@ def combinations(*comb, **kw):
         r - the given argument should be passed and it should be added to the
             id by calling repr()
 
-        s-  the given argument should be passed and it should be added to the
+        s - the given argument should be passed and it should be added to the
             id by calling str()
 
+        a - (argument) the given argument should be passed and it should not
+            be used to generated the id
+
      e.g.::
 
         @testing.combinations(
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index c45156d6b..fd8d82690 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -1064,3 +1064,7 @@ class SuiteRequirements(Requirements):
             return True
         except ImportError:
             return False
+
+    @property
+    def computed_columns(self):
+        return exclusions.closed()
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index 02cdcf4f5..9db2daf7a 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -9,6 +9,7 @@ from ..schema import Column
 from ..schema import Table
 from ... import bindparam
 from ... import case
+from ... import Computed
 from ... import false
 from ... import func
 from ... import Integer
@@ -858,3 +859,47 @@ class LikeFunctionsTest(fixtures.TablesTest):
         col = self.tables.some_table.c.data
         self._test(col.contains("b%cd", autoescape=True, escape="#"), {3})
         self._test(col.contains("b#cd", autoescape=True, escape="#"), {7})
+
+
+class ComputedColumnTest(fixtures.TablesTest):
+    __backend__ = True
+    __requires__ = ("computed_columns",)
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "square",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("side", Integer),
+            Column("area", Integer, Computed("side * side")),
+            Column("perimeter", Integer, Computed("4 * side")),
+        )
+
+    @classmethod
+    def insert_data(cls):
+        with config.db.begin() as conn:
+            conn.execute(
+                cls.tables.square.insert(),
+                [{"id": 1, "side": 10}, {"id": 10, "side": 42}],
+            )
+
+    def test_select_all(self):
+        with config.db.connect() as conn:
+            res = conn.execute(
+                select([text("*")])
+                .select_from(self.tables.square)
+                .order_by(self.tables.square.c.id)
+            ).fetchall()
+            eq_(res, [(1, 10, 100, 40), (10, 42, 1764, 168)])
+
+    def test_select_columns(self):
+        with config.db.connect() as conn:
+            res = conn.execute(
+                select(
+                    [self.tables.square.c.area, self.tables.square.c.perimeter]
+                )
+                .select_from(self.tables.square)
+                .order_by(self.tables.square.c.id)
+            ).fetchall()
+            eq_(res, [(100, 40), (1764, 168)])