From 62b7dace0c1d03acf3224085d03a03684a969031 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Sat, 14 Mar 2020 13:57:42 +0100 Subject: [PATCH] Support inspection of computed column Added support for reflection of "computed" columns, which are now returned as part of the structure returned by :meth:`.Inspector.get_columns`. When reflecting full :class:`.Table` objects, computed columns will be represented using the :class:`.Computed` construct. Also improve the documentation in :meth:`Inspector.get_columns`, correctly listing all the returned keys. Fixes: #5063 Fixes: #4051 Closes: #5064 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5064 Pull-request-sha: ba00fc321ce468f8885aad23b3dd33c789e50fbe Change-Id: I789986554fc8ac7f084270474d0b2c12046b1cc2 --- README.unittests.rst | 2 +- doc/build/changelog/unreleased_13/5063.rst | 8 ++ lib/sqlalchemy/dialects/mssql/base.py | 63 +++++++++----- .../dialects/mssql/information_schema.py | 12 +++ lib/sqlalchemy/dialects/mysql/reflection.py | 10 +++ lib/sqlalchemy/dialects/oracle/base.py | 14 ++- lib/sqlalchemy/dialects/postgresql/base.py | 28 +++++- lib/sqlalchemy/engine/reflection.py | 25 +++++- lib/sqlalchemy/testing/fixtures.py | 77 +++++++++++++++++ lib/sqlalchemy/testing/requirements.py | 23 +++++ .../testing/suite/test_reflection.py | 86 +++++++++++++++++++ test/dialect/postgresql/test_reflection.py | 2 +- test/engine/test_reflection.py | 36 ++++++++ test/requirements.py | 16 ++++ 14 files changed, 374 insertions(+), 28 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/5063.rst diff --git a/README.unittests.rst b/README.unittests.rst index 95f4c1a2bd..49fc669d9a 100644 --- a/README.unittests.rst +++ b/README.unittests.rst @@ -162,7 +162,7 @@ and MySQL they are schemas. The requirement applies to all backends except SQLite and Firebird. The names are:: test_schema - test_schema_2 (only used on PostgreSQL) + test_schema_2 (only used on PostgreSQL and mssql) Please refer to your vendor documentation for the proper syntax to create these namespaces - the database user must have permission to create and drop diff --git a/doc/build/changelog/unreleased_13/5063.rst b/doc/build/changelog/unreleased_13/5063.rst new file mode 100644 index 0000000000..ca8ad66910 --- /dev/null +++ b/doc/build/changelog/unreleased_13/5063.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: schema, reflection + :tickets: 5063 + + Added support for reflection of "computed" columns, which are now returned + as part of the structure returned by :meth:`.Inspector.get_columns`. + When reflecting full :class:`.Table` objects, computed columns will + be represented using the :class:`.Computed` construct. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 955a0f23b1..94db296bfe 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -696,6 +696,7 @@ from ...engine import result as _result from ...sql import compiler from ...sql import elements from ...sql import expression +from ...sql import func from ...sql import quoted_name from ...sql import util as sql_util from ...types import BIGINT @@ -2649,40 +2650,53 @@ class MSDialect(default.DefaultDialect): def get_columns(self, connection, tablename, dbname, owner, schema, **kw): # Get base columns columns = ischema.columns + computed_cols = ischema.computed_columns if owner: whereclause = sql.and_( columns.c.table_name == tablename, columns.c.table_schema == owner, ) + table_fullname = "%s.%s" % (owner, tablename) + concat = func.concat( + columns.c.table_schema, ".", columns.c.table_name + ) + join_on = computed_cols.c.object_id == func.object_id(concat) else: whereclause = columns.c.table_name == tablename + table_fullname = tablename + join_on = computed_cols.c.object_id == func.object_id( + columns.c.table_name + ) + + join_on = sql.and_( + join_on, columns.c.column_name == computed_cols.c.name + ) + join = columns.join(computed_cols, onclause=join_on, isouter=True) s = sql.select( - [columns], whereclause, order_by=[columns.c.ordinal_position] + [ + columns, + computed_cols.c.definition, + computed_cols.c.is_persisted, + ], + whereclause, + from_obj=join, + order_by=[columns.c.ordinal_position], ) c = connection.execution_options(future_result=True).execute(s) cols = [] - for row in c.mappings(): - ( - name, - type_, - nullable, - charlen, - numericprec, - numericscale, - default, - collation, - ) = ( - row[columns.c.column_name], - row[columns.c.data_type], - row[columns.c.is_nullable] == "YES", - row[columns.c.character_maximum_length], - row[columns.c.numeric_precision], - row[columns.c.numeric_scale], - row[columns.c.column_default], - row[columns.c.collation_name], - ) + name = row[columns.c.column_name] + type_ = row[columns.c.data_type] + nullable = row[columns.c.is_nullable] == "YES" + charlen = row[columns.c.character_maximum_length] + numericprec = row[columns.c.numeric_precision] + numericscale = row[columns.c.numeric_scale] + default = row[columns.c.column_default] + collation = row[columns.c.collation_name] + definition = row[computed_cols.c.definition] + is_persisted = row[computed_cols.c.is_persisted] + coltype = self.ischema_names.get(type_, None) kwargs = {} @@ -2724,6 +2738,13 @@ class MSDialect(default.DefaultDialect): "default": default, "autoincrement": False, } + + if definition is not None and is_persisted is not None: + cdict["computed"] = { + "sqltext": definition, + "persisted": is_persisted, + } + cols.append(cdict) # autoincrement and identity colmap = {} diff --git a/lib/sqlalchemy/dialects/mssql/information_schema.py b/lib/sqlalchemy/dialects/mssql/information_schema.py index 0b6ad137e9..e9ab6f4f3b 100644 --- a/lib/sqlalchemy/dialects/mssql/information_schema.py +++ b/lib/sqlalchemy/dialects/mssql/information_schema.py @@ -15,6 +15,7 @@ from ... import Table from ... import util from ...ext.compiler import compiles from ...sql import expression +from ...types import Boolean from ...types import Integer from ...types import String from ...types import TypeDecorator @@ -161,3 +162,14 @@ views = Table( Column("IS_UPDATABLE", String, key="is_updatable"), schema="INFORMATION_SCHEMA", ) + +computed_columns = Table( + "computed_columns", + ischema, + Column("object_id", Integer), + Column("name", CoerceUnicode), + Column("is_computed", Boolean), + Column("is_persisted", Boolean), + Column("definition", CoerceUnicode), + schema="sys", +) diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py index 94b50ad4f6..8aeb1dc960 100644 --- a/lib/sqlalchemy/dialects/mysql/reflection.py +++ b/lib/sqlalchemy/dialects/mysql/reflection.py @@ -249,6 +249,14 @@ class MySQLTableDefinitionParser(object): if comment is not None: comment = comment.replace("\\\\", "\\").replace("''", "'") + sqltext = spec.get("generated") + if sqltext is not None: + computed = dict(sqltext=sqltext) + persisted = spec.get("persistence") + if persisted is not None: + computed["persisted"] = persisted == "STORED" + col_kw["computed"] = computed + col_d = dict( name=name, type=type_instance, default=default, comment=comment ) @@ -376,6 +384,8 @@ class MySQLTableDefinitionParser(object): r"(?:NULL|'(?:''|[^'])*'|[\w\(\)]+" r"(?: +ON UPDATE [\w\(\)]+)?)" r"))?" + r"(?: +(?:GENERATED ALWAYS)? ?AS +(?P\(" + r".*\))? ?(?PVIRTUAL|STORED)?)?" r"(?: +(?PAUTO_INCREMENT))?" r"(?: +COMMENT +'(?P(?:''|[^'])*)')?" r"(?: +COLUMN_FORMAT +(?P\w+))?" diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 11ddabfc33..356c375625 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -1673,13 +1673,14 @@ class OracleDialect(default.DefaultDialect): text = """ SELECT col.column_name, col.data_type, col.%(char_length_col)s, col.data_precision, col.data_scale, col.nullable, - col.data_default, com.comments\ - FROM all_tab_columns%(dblink)s col + col.data_default, com.comments, col.virtual_column\ + FROM all_tab_cols%(dblink)s col LEFT JOIN all_col_comments%(dblink)s com ON col.table_name = com.table_name AND col.column_name = com.column_name AND col.owner = com.owner WHERE col.table_name = :table_name + AND col.hidden_column = 'NO' """ if schema is not None: params["owner"] = schema @@ -1699,6 +1700,7 @@ class OracleDialect(default.DefaultDialect): nullable = row[5] == "Y" default = row[6] comment = row[7] + generated = row[8] if coltype == "NUMBER": if precision is None and scale == 0: @@ -1723,6 +1725,12 @@ class OracleDialect(default.DefaultDialect): ) coltype = sqltypes.NULLTYPE + if generated == "YES": + computed = dict(sqltext=default) + default = None + else: + computed = None + cdict = { "name": colname, "type": coltype, @@ -1733,6 +1741,8 @@ class OracleDialect(default.DefaultDialect): } if orig_colname.lower() == orig_colname: cdict["quote"] = True + if computed is not None: + cdict["computed"] = computed columns.append(cdict) return columns diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index bfe3812bed..8105244fa4 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2826,7 +2826,14 @@ class PGDialect(default.DefaultDialect): table_oid = self.get_table_oid( connection, table_name, schema, info_cache=kw.get("info_cache") ) - SQL_COLS = """ + + generated = ( + "a.attgenerated as generated" + if self.server_version_info >= (12,) + else "NULL as generated" + ) + SQL_COLS = ( + """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid) @@ -2835,7 +2842,8 @@ class PGDialect(default.DefaultDialect): AND a.atthasdef) AS DEFAULT, a.attnotnull, a.attnum, a.attrelid as table_oid, - pgd.description as comment + pgd.description as comment, + %s FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_description pgd ON ( pgd.objoid = a.attrelid AND pgd.objsubid = a.attnum) @@ -2843,6 +2851,8 @@ class PGDialect(default.DefaultDialect): AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum """ + % generated + ) s = ( sql.text(SQL_COLS) .bindparams(sql.bindparam("table_oid", type_=sqltypes.Integer)) @@ -2875,6 +2885,7 @@ class PGDialect(default.DefaultDialect): attnum, table_oid, comment, + generated, ) in rows: column_info = self._get_column_info( name, @@ -2885,6 +2896,7 @@ class PGDialect(default.DefaultDialect): enums, schema, comment, + generated, ) columns.append(column_info) return columns @@ -2899,6 +2911,7 @@ class PGDialect(default.DefaultDialect): enums, schema, comment, + generated, ): def _handle_array_type(attype): return ( @@ -3009,6 +3022,15 @@ class PGDialect(default.DefaultDialect): "Did not recognize type '%s' of column '%s'" % (attype, name) ) coltype = sqltypes.NULLTYPE + + # If a zero byte (''), then not a generated column. + # Otherwise, s = stored. (Other values might be added in the future.) + if generated: + computed = dict(sqltext=default, persisted=generated == "s") + default = None + else: + computed = None + # adjust the default value autoincrement = False if default is not None: @@ -3038,6 +3060,8 @@ class PGDialect(default.DefaultDialect): autoincrement=autoincrement, comment=comment, ) + if computed is not None: + column_info["computed"] = computed return column_info @reflection.cache diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py index 25538fddb5..ba60d634e7 100644 --- a/lib/sqlalchemy/engine/reflection.py +++ b/lib/sqlalchemy/engine/reflection.py @@ -419,7 +419,26 @@ class Inspector(object): * ``default`` - the column's server default value - this is returned as a string SQL expression. - * ``attrs`` - dict containing optional column attributes + * ``autoincrement`` - indicates that the column is auto incremented - + this is returned as a boolean or 'auto' + + * ``comment`` - (optional) the commnet on the column. Only some + dialects return this key + + * ``computed`` - (optional) when present it indicates that this column + is computed by the database. Only some dialects return this key. + Returned as a dict with the keys: + + * ``sqltext`` - the expression used to generate this column returned + as a string SQL expression + + * ``persisted`` - (optional) boolean that indicates if the column is + stored in the table + + .. versionadded:: 1.3.16 - added support for computed reflection. + + * ``dialect_options`` - (optional) a dict with dialect specific options + :param table_name: string name of the table. For special quoting, use :class:`.quoted_name`. @@ -825,6 +844,10 @@ class Inspector(object): colargs.append(default) + if "computed" in col_d: + computed = sa_schema.Computed(**col_d["computed"]) + colargs.append(computed) + if "sequence" in col_d: self._reflect_col_sequence(col_d, colargs) diff --git a/lib/sqlalchemy/testing/fixtures.py b/lib/sqlalchemy/testing/fixtures.py index bae0cee89d..4914d49ecb 100644 --- a/lib/sqlalchemy/testing/fixtures.py +++ b/lib/sqlalchemy/testing/fixtures.py @@ -5,6 +5,7 @@ # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php +import re import sys import sqlalchemy as sa @@ -424,3 +425,79 @@ class DeclarativeMappedTest(MappedTest): if cls.metadata.tables and cls.run_create_tables: cls.metadata.create_all(config.db) + + +class ComputedReflectionFixtureTest(TablesTest): + run_inserts = run_deletes = None + + __backend__ = True + __requires__ = ("computed_columns", "table_reflection") + + regexp = re.compile(r"[\[\]\(\)\s`'\"]*") + + def normalize(self, text): + return self.regexp.sub("", text).lower() + + @classmethod + def define_tables(cls, metadata): + from .. import Integer + from .. import testing + from ..schema import Column + from ..schema import Computed + from ..schema import Table + + Table( + "computed_default_table", + metadata, + Column("id", Integer, primary_key=True), + Column("normal", Integer), + Column("computed_col", Integer, Computed("normal + 42")), + Column("with_default", Integer, server_default="42"), + ) + + t = Table( + "computed_column_table", + metadata, + Column("id", Integer, primary_key=True), + Column("normal", Integer), + Column("computed_no_flag", Integer, Computed("normal + 42")), + ) + + t2 = Table( + "computed_column_table", + metadata, + Column("id", Integer, primary_key=True), + Column("normal", Integer), + Column("computed_no_flag", Integer, Computed("normal / 42")), + schema=config.test_schema, + ) + if testing.requires.computed_columns_virtual.enabled: + t.append_column( + Column( + "computed_virtual", + Integer, + Computed("normal + 2", persisted=False), + ) + ) + t2.append_column( + Column( + "computed_virtual", + Integer, + Computed("normal / 2", persisted=False), + ) + ) + if testing.requires.computed_columns_stored.enabled: + t.append_column( + Column( + "computed_stored", + Integer, + Computed("normal - 42", persisted=True), + ) + ) + t2.append_column( + Column( + "computed_stored", + Integer, + Computed("normal * 42", persisted=True), + ) + ) diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index c12d1e6e8d..5eb769bf3d 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -1108,4 +1108,27 @@ class SuiteRequirements(Requirements): @property def computed_columns(self): + "Supports computed columns" + return exclusions.closed() + + @property + def computed_columns_stored(self): + "Supports computed columns with `persisted=True`" + return exclusions.closed() + + @property + def computed_columns_virtual(self): + "Supports computed columns with `persisted=False`" + return exclusions.closed() + + @property + def computed_columns_default_persisted(self): + """If the default persistence is virtual or stored when `persisted` + is omitted""" + return exclusions.closed() + + @property + def computed_columns_reflect_persisted(self): + """If persistence information is returned by the reflection of + computed columns""" return exclusions.closed() diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py index d375f02794..473c981160 100644 --- a/lib/sqlalchemy/testing/suite/test_reflection.py +++ b/lib/sqlalchemy/testing/suite/test_reflection.py @@ -1183,9 +1183,95 @@ class NormalizedNameTest(fixtures.TablesTest): eq_(tablenames[1].upper(), tablenames[1].lower()) +class ComputedReflectionTest(fixtures.ComputedReflectionFixtureTest): + def test_computed_col_default_not_set(self): + insp = inspect(config.db) + + cols = insp.get_columns("computed_column_table") + for col in cols: + if col["name"] == "with_default": + is_true("42" in col["default"]) + elif not col["autoincrement"]: + is_(col["default"], None) + + def test_get_column_returns_computed(self): + insp = inspect(config.db) + + cols = insp.get_columns("computed_default_table") + data = {c["name"]: c for c in cols} + for key in ("id", "normal", "with_default"): + is_true("computed" not in data[key]) + compData = data["computed_col"] + is_true("computed" in compData) + is_true("sqltext" in compData["computed"]) + eq_(self.normalize(compData["computed"]["sqltext"]), "normal+42") + eq_( + "persisted" in compData["computed"], + testing.requires.computed_columns_reflect_persisted.enabled, + ) + if testing.requires.computed_columns_reflect_persisted.enabled: + eq_( + compData["computed"]["persisted"], + testing.requires.computed_columns_default_persisted.enabled, + ) + + def check_column(self, data, column, sqltext, persisted): + is_true("computed" in data[column]) + compData = data[column]["computed"] + eq_(self.normalize(compData["sqltext"]), sqltext) + if testing.requires.computed_columns_reflect_persisted.enabled: + is_true("persisted" in compData) + is_(compData["persisted"], persisted) + + def test_get_column_returns_persisted(self): + insp = inspect(config.db) + + cols = insp.get_columns("computed_column_table") + data = {c["name"]: c for c in cols} + + self.check_column( + data, + "computed_no_flag", + "normal+42", + testing.requires.computed_columns_default_persisted.enabled, + ) + if testing.requires.computed_columns_virtual.enabled: + self.check_column( + data, "computed_virtual", "normal+2", False, + ) + if testing.requires.computed_columns_stored.enabled: + self.check_column( + data, "computed_stored", "normal-42", True, + ) + + def test_get_column_returns_persisted_with_schama(self): + insp = inspect(config.db) + + cols = insp.get_columns( + "computed_column_table", schema=config.test_schema + ) + data = {c["name"]: c for c in cols} + + self.check_column( + data, + "computed_no_flag", + "normal/42", + testing.requires.computed_columns_default_persisted.enabled, + ) + if testing.requires.computed_columns_virtual.enabled: + self.check_column( + data, "computed_virtual", "normal/2", False, + ) + if testing.requires.computed_columns_stored.enabled: + self.check_column( + data, "computed_stored", "normal*42", True, + ) + + __all__ = ( "ComponentReflectionTest", "HasTableTest", "HasIndexTest", "NormalizedNameTest", + "ComputedReflectionTest", ) diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index c665372503..374bda5658 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -1669,7 +1669,7 @@ class CustomTypeReflectionTest(fixtures.TestBase): ("my_custom_type(ARG1, ARG2)", ("ARG1", "ARG2")), ]: column_info = dialect._get_column_info( - "colname", sch, None, False, {}, {}, "public", None + "colname", sch, None, False, {}, {}, "public", None, "" ) assert isinstance(column_info["type"], self.CustomType) eq_(column_info["type"].arg1, args[0]) diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py index 579f1aecec..9de1812161 100644 --- a/test/engine/test_reflection.py +++ b/test/engine/test_reflection.py @@ -1,6 +1,7 @@ import unicodedata import sqlalchemy as sa +from sqlalchemy import Computed from sqlalchemy import DefaultClause from sqlalchemy import FetchedValue from sqlalchemy import ForeignKey @@ -26,6 +27,8 @@ from sqlalchemy.testing import fixtures from sqlalchemy.testing import in_ from sqlalchemy.testing import is_ from sqlalchemy.testing import is_false +from sqlalchemy.testing import is_instance_of +from sqlalchemy.testing import is_not_ from sqlalchemy.testing import is_true from sqlalchemy.testing import mock from sqlalchemy.testing import not_in_ @@ -2263,3 +2266,36 @@ class ColumnEventsTest(fixtures.RemovesEvents, fixtures.TestBase): eq_(str(table.c.x.server_default.arg), "1") self._do_test("x", {"default": my_default}, assert_text_of_one) + + +class ComputedColumnTest(fixtures.ComputedReflectionFixtureTest): + def check_table_column(self, table, name, text, persisted): + is_true(name in table.columns) + col = table.columns[name] + is_not_(col.computed, None) + is_instance_of(col.computed, Computed) + + eq_(self.normalize(str(col.computed.sqltext)), text) + if testing.requires.computed_columns_reflect_persisted.enabled: + eq_(col.computed.persisted, persisted) + else: + is_(col.computed.persisted, None) + + def test_table_reflection(self): + meta = MetaData() + table = Table("computed_column_table", meta, autoload_with=config.db) + + self.check_table_column( + table, + "computed_no_flag", + "normal+42", + testing.requires.computed_columns_default_persisted.enabled, + ) + if testing.requires.computed_columns_virtual.enabled: + self.check_table_column( + table, "computed_virtual", "normal+2", False, + ) + if testing.requires.computed_columns_stored.enabled: + self.check_table_column( + table, "computed_stored", "normal-42", True, + ) diff --git a/test/requirements.py b/test/requirements.py index a1bd57aea0..913d06a01b 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -1509,3 +1509,19 @@ class DefaultRequirements(SuiteRequirements): @property def python_profiling_backend(self): return only_on([self._sqlite_memory_db]) + + @property + def computed_columns_stored(self): + return self.computed_columns + skip_if(["oracle", "firebird"]) + + @property + def computed_columns_virtual(self): + return self.computed_columns + skip_if(["postgresql", "firebird"]) + + @property + def computed_columns_default_persisted(self): + return self.computed_columns + only_if("postgresql") + + @property + def computed_columns_reflect_persisted(self): + return self.computed_columns + skip_if("oracle") -- 2.39.5