diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2023-01-25 08:58:03 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2023-01-25 09:18:10 -0500 |
commit | b710a5286310710d5d0a6d5170696e8fe4d4a0d8 (patch) | |
tree | 2e0980513d4728f1308267f36d1cdccc5793e7a5 /lib/sqlalchemy/dialects/mssql/base.py | |
parent | 55b243a3f22f267c829f9088fd4e801f0b621a31 (diff) | |
download | sqlalchemy-b710a5286310710d5d0a6d5170696e8fe4d4a0d8.tar.gz |
Make comment support conditional on fn_listextendedproperty availability
The newly added comment reflection and rendering capability of the MSSQL
dialect, added in :ticket:`7844`, will now be disabled by default if it
cannot be determined that an unsupported backend such as Azure Synapse may
be in use; this backend does not support table and column comments and does
not support the SQL Server routines in use to generate them as well as to
reflect them. A new parameter ``supports_comments`` is added to the dialect
which defaults to ``None``, indicating that comment support should be
auto-detected. When set to ``True`` or ``False``, the comment support is
either enabled or disabled unconditionally.
Fixes: #9142
Change-Id: Ib5cac31806185e7353e15b3d83b580652d304b3b
Diffstat (limited to 'lib/sqlalchemy/dialects/mssql/base.py')
-rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index eabea88db..a30c57c7f 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -435,6 +435,29 @@ Note that when using LIMIT and/or OFFSET, whether using the older or newer SQL Server syntaxes, the statement must have an ORDER BY as well, else a :class:`.CompileError` is raised. +.. _mssql_comment_support: + +DDL Comment Support +-------------------- + +Comment support, which includes DDL rendering for attributes such as +:paramref:`_schema.Table.comment` and :paramref:`_schema.Column.comment`, as +well as the ability to reflect these comments, is supported assuming a +supported version of SQL Server is in use. If a non-supported version such as +Azure Synapse is detected at first-connect time (based on the presence +of the ``fn_listextendedproperty`` SQL function), comment support including +rendering and table-comment reflection is disabled, as both features rely upon +SQL Server stored procedures and functions that are not available on all +backend types. + +To force comment support to be on or off, bypassing autodetection, set the +parameter ``supports_comments`` within :func:`_sa.create_engine`:: + + e = create_engine("mssql+pyodbc://u:p@dsn", supports_comments=False) + +.. versionadded:: 2.0 Added support for table and column comments for + the SQL Server dialect, including DDL generation and reflection. + .. _mssql_isolation_level: Transaction Isolation Level @@ -3039,6 +3062,7 @@ class MSDialect(default.DefaultDialect): use_scope_identity=True, schema_name="dbo", deprecate_large_types=None, + supports_comments=None, json_serializer=None, json_deserializer=None, legacy_schema_aliasing=None, @@ -3053,6 +3077,9 @@ class MSDialect(default.DefaultDialect): self.ignore_no_transaction_on_rollback = ( ignore_no_transaction_on_rollback ) + self._user_defined_supports_comments = uds = supports_comments + if uds is not None: + self.supports_comments = uds if legacy_schema_aliasing is not None: util.warn_deprecated( @@ -3160,6 +3187,7 @@ class MSDialect(default.DefaultDialect): super().initialize(connection) self._setup_version_attributes() self._setup_supports_nvarchar_max(connection) + self._setup_supports_comments(connection) def _setup_version_attributes(self): if self.server_version_info[0] not in list(range(8, 17)): @@ -3193,6 +3221,23 @@ class MSDialect(default.DefaultDialect): else: self._supports_nvarchar_max = True + def _setup_supports_comments(self, connection): + if self._user_defined_supports_comments is not None: + return + + try: + connection.scalar( + sql.text( + "SELECT 1 FROM fn_listextendedproperty" + "(default, default, default, default, " + "default, default, default)" + ) + ) + except exc.DBAPIError: + self.supports_comments = False + else: + self.supports_comments = True + def _get_default_schema_name(self, connection): query = sql.text("SELECT schema_name()") default_schema_name = connection.scalar(query) @@ -3432,6 +3477,11 @@ class MSDialect(default.DefaultDialect): @reflection.cache def get_table_comment(self, connection, table_name, schema=None, **kw): + if not self.supports_comments: + raise NotImplementedError( + "Can't get table comments on current SQL Server version in use" + ) + schema_name = schema if schema else self.default_schema_name COMMENT_SQL = """ SELECT cast(com.value as nvarchar(max)) |