Add reflection for Identity columns

Added support for reflecting "identity" columns, which are now returned
as part of the structure returned by :meth:`_reflection.Inspector.get_columns`.
When reflecting full :class:`_schema.Table` objects, identity columns will
be represented using the :class:`_schema.Identity` construct.

Fixed compilation error on oracle for sequence and identity column
``nominvalue`` and ``nomaxvalue`` options that require no space in them.

Improved test compatibility with oracle 18.

As part of the support for reflecting :class:`_schema.Identity` objects,
the method :meth:`_reflection.Inspector.get_columns` no longer returns
``mssql_identity_start`` and ``mssql_identity_increment`` as part of the
``dialect_options``. Use the information in the ``identity`` key instead.

The mssql dialect will assume that at least MSSQL 2005 is used.
There is no hard exception raised if a previous version is detected,
but operations may fail for older versions.

Fixes: #5527
Fixes: #5324
Change-Id: If039fe637c46b424499e6bac54a2cbc0dc54cb57

1 files changed, 30 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/information_schema.py b/lib/sqlalchemy/dialects/mssql/information_schema.py
index f80110b7d..974a55963 100644
--- a/lib/sqlalchemy/dialects/mssql/information_schema.py
+++ b/lib/sqlalchemy/dialects/mssql/information_schema.py
@@ -14,6 +14,7 @@ from ...ext.compiler import compiles
 from ...sql import expression
 from ...types import Boolean
 from ...types import Integer
+from ...types import Numeric
 from ...types import String
 from ...types import TypeDecorator
 from ...types import Unicode
@@ -198,3 +199,32 @@ sequences = Table(
     Column("SEQUENCE_NAME", CoerceUnicode, key="sequence_name"),
     schema="INFORMATION_SCHEMA",
 )
+
+
+class IdentitySqlVariant(TypeDecorator):
+    r"""This type casts sql_variant columns in the identity_columns view
+    to numeric. This is required because:
+
+    * pyodbc does not support sql_variant
+    * pymssql under python 2 return the byte representation of the number,
+      int 1 is returned as "\x01\x00\x00\x00". On python 3 it returns the
+      correct value as string.
+    """
+    impl = Unicode
+
+    def column_expression(self, colexpr):
+        return cast(colexpr, Numeric)
+
+
+identity_columns = Table(
+    "identity_columns",
+    ischema,
+    Column("object_id", Integer),
+    Column("name", CoerceUnicode),
+    Column("is_identity", Boolean),
+    Column("seed_value", IdentitySqlVariant),
+    Column("increment_value", IdentitySqlVariant),
+    Column("last_value", IdentitySqlVariant),
+    Column("is_not_for_replication", Boolean),
+    schema="sys",
+)