Merge "Support for generated columns"

6 files changed, 74 insertions, 6 deletions

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
         )