From 3a0e0531c179e598c345e5be24e350c375ce7e22 Mon Sep 17 00:00:00 2001
From: CaselIT <cfederico87@gmail.com>
Date: Mon, 4 Nov 2019 17:11:21 -0500
Subject: 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
---
 lib/sqlalchemy/testing/config.py            |  7 +++--
 lib/sqlalchemy/testing/requirements.py      |  4 +++
 lib/sqlalchemy/testing/suite/test_select.py | 45 +++++++++++++++++++++++++++++
 3 files changed, 54 insertions(+), 2 deletions(-)

(limited to 'lib/sqlalchemy/testing')

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)])
-- 
cgit v1.2.1