Add SQL Server CI coverage

Change-Id: Ida0d01ae9bcc0573b86e24fddea620a38c962822

5 files changed, 47 insertions, 60 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 84be8d0e3..5f936fd76 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -560,17 +560,20 @@ This option can also be specified engine-wide using the
 Rowcount Support / ORM Versioning
 ---------------------------------
 
-The SQL Server drivers have very limited ability to return the number
-of rows updated from an UPDATE or DELETE statement.  In particular, the
-pymssql driver has no support, whereas the pyodbc driver can only return
-this value under certain conditions.
-
-In particular, updated rowcount is not available when OUTPUT INSERTED
-is used.  This impacts the SQLAlchemy ORM's versioning feature when
-server-side versioning schemes are used.  When
-using pyodbc, the "implicit_returning" flag needs to be set to false
-for any ORM mapped class that uses a version_id column in conjunction with
-a server-side version generator::
+The SQL Server drivers may have limited ability to return the number
+of rows updated from an UPDATE or DELETE statement.
+
+As of this writing, the PyODBC driver is not able to return a rowcount when
+OUTPUT INSERTED is used.  This impacts the SQLAlchemy ORM's versioning feature
+in many cases where server-side value generators are in use in that while the
+versioning operations can succeed, the ORM cannot always check that an UPDATE
+or DELETE statement matched the number of rows expected, which is how it
+verifies that the version identifier matched.   When this condition occurs, a
+warning will be emitted but the operation will proceed.
+
+The use of OUTPUT INSERTED can be disabled by setting the
+:paramref:`.Table.implicit_returning` flag to ``False`` on a particular
+:class:`.Table`, which in declarative looks like::
 
     class MyTable(Base):
         __tablename__ = 'mytable'
@@ -585,14 +588,10 @@ a server-side version generator::
             'implicit_returning': False
         }
 
-Without the implicit_returning flag above, the UPDATE statement will
-use ``OUTPUT inserted.timestamp`` and the rowcount will be returned as
--1, causing the versioning logic to fail.
-
 Enabling Snapshot Isolation
 ---------------------------
 
-Not necessarily specific to SQLAlchemy, SQL Server has a default transaction
+SQL Server has a default transaction
 isolation mode that locks entire tables, and causes even mildly concurrent
 applications to have long held locks and frequent deadlocks.
 Enabling snapshot isolation for the database as a whole is recommended
@@ -606,12 +605,6 @@ following ALTER DATABASE commands executed at the SQL prompt::
 Background on SQL Server snapshot isolation is available at
 http://msdn.microsoft.com/en-us/library/ms175095.aspx.
 
-Known Issues
-------------
-
-* No support for more than one ``IDENTITY`` column per table
-* reflection of indexes does not work with versions older than
-  SQL Server 2005
 
 """
 import datetime
diff --git a/lib/sqlalchemy/dialects/mssql/information_schema.py b/lib/sqlalchemy/dialects/mssql/information_schema.py
index 625479be7..a73dbdfad 100644
--- a/lib/sqlalchemy/dialects/mssql/information_schema.py
+++ b/lib/sqlalchemy/dialects/mssql/information_schema.py
@@ -38,7 +38,8 @@ class _cast_on_2005(expression.ColumnElement):
 @compiles(_cast_on_2005)
 def _compile(element, compiler, **kw):
     from . import base
-    if compiler.dialect.server_version_info < base.MS_2005_VERSION:
+    if compiler.dialect.server_version_info is None or \
+             compiler.dialect.server_version_info < base.MS_2005_VERSION:
         return compiler.process(element.bindvalue, **kw)
     else:
         return compiler.process(cast(element.bindvalue, Unicode), **kw)
diff --git a/lib/sqlalchemy/dialects/mssql/pymssql.py b/lib/sqlalchemy/dialects/mssql/pymssql.py
index 51237990e..d9a2d59d0 100644
--- a/lib/sqlalchemy/dialects/mssql/pymssql.py
+++ b/lib/sqlalchemy/dialects/mssql/pymssql.py
@@ -17,6 +17,9 @@ pymssql is a Python module that provides a Python DBAPI interface around
 `FreeTDS <http://www.freetds.org/>`_.  Compatible builds are available for
 Linux, MacOSX and Windows platforms.
 
+Modern versions of this driver work very well with SQL Server and
+FreeTDS from Linux and is highly recommended.
+
 """
 from .base import MSDialect, MSIdentifierPreparer
 from ... import types as sqltypes, util, processors
@@ -41,7 +44,7 @@ class MSIdentifierPreparer_pymssql(MSIdentifierPreparer):
 
 
 class MSDialect_pymssql(MSDialect):
-    supports_sane_rowcount = False
+    supports_native_decimal = True
     driver = 'pymssql'
 
     preparer = MSIdentifierPreparer_pymssql
@@ -68,10 +71,6 @@ class MSDialect_pymssql(MSDialect):
                       "the 1.0 series of the pymssql DBAPI.")
         return module
 
-    def __init__(self, **params):
-        super(MSDialect_pymssql, self).__init__(**params)
-        self.use_scope_identity = True
-
     def _get_server_version_info(self, connection):
         vers = connection.scalar("select @@version")
         m = re.match(
@@ -111,6 +110,7 @@ class MSDialect_pymssql(MSDialect):
         else:
             connection.autocommit(False)
             super(MSDialect_pymssql, self).set_isolation_level(connection,
-                                                                level)
+                                                               level)
+
 
 dialect = MSDialect_pymssql
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index c6368f969..a667b671e 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -64,34 +64,19 @@ as illustrated below using ``urllib.quote_plus``::
     engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
 
 
-Unicode Binds
--------------
-
-The current state of PyODBC on a unix backend with FreeTDS and/or
-EasySoft is poor regarding unicode; different OS platforms and versions of
-UnixODBC versus IODBC versus FreeTDS/EasySoft versus PyODBC itself
-dramatically alter how strings are received.  The PyODBC dialect attempts to
-use all the information it knows to determine whether or not a Python unicode
-literal can be passed directly to the PyODBC driver or not; while SQLAlchemy
-can encode these to bytestrings first, some users have reported that PyODBC
-mis-handles bytestrings for certain encodings and requires a Python unicode
-object, while the author has observed widespread cases where a Python unicode
-is completely misinterpreted by PyODBC, particularly when dealing with
-the information schema tables used in table reflection, and the value
-must first be encoded to a bytestring.
-
-It is for this reason that whether or not unicode literals for bound
-parameters be sent to PyODBC can be controlled using the
-``supports_unicode_binds`` parameter to ``create_engine()``.  When
-left at its default of ``None``, the PyODBC dialect will use its
-best guess as to whether or not the driver deals with unicode literals
-well.  When ``False``, unicode literals will be encoded first, and when
-``True`` unicode literals will be passed straight through.  This is an interim
-flag that hopefully should not be needed when the unicode situation stabilizes
-for unix + PyODBC.
-
-.. versionadded:: 0.7.7
-    ``supports_unicode_binds`` parameter to ``create_engine()``\ .
+Driver / Unicode Support
+-------------------------
+
+PyODBC works best with Microsoft ODBC drivers, particularly in the area
+of Unicode support on both Python 2 and Python 3.
+
+Using the FreeTDS ODBC drivers on Linux or OSX with PyODBC is **not**
+recommended; there have been historically many Unicode-related issues
+in this area, including before Microsoft offered ODBC drivers for Linux
+and OSX.   Now that Microsoft offers drivers for all platforms, for
+PyODBC support these are recommended.  FreeTDS remains relevant for
+non-ODBC drivers such as pymssql where it works very well.
+
 
 Rowcount Support
 ----------------
@@ -272,11 +257,12 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
 
     def _get_server_version_info(self, connection):
         try:
-            raw = connection.scalar("SELECT  SERVERPROPERTY('ProductVersion')")
+            raw = connection.scalar(
+                "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)")
         except exc.DBAPIError:
             # SQL Server docs indicate this function isn't present prior to
-            # 2008; additionally, unknown combinations of pyodbc aren't
-            # able to run this query.
+            # 2008.  Before we had the VARCHAR cast above, pyodbc would also
+            # fail on this query.
             return super(MSDialect_pyodbc, self).\
                 _get_server_version_info(connection)
         else:
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 9d6dd7188..5b01b2c1f 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1872,6 +1872,13 @@ class MySQLDialect(default.DefaultDialect):
         return 'MariaDB' in self.server_version_info
 
     @property
+    def _mariadb_normalized_version_info(self):
+        if len(self.server_version_info) > 5:
+            return self.server_version_info[3:]
+        else:
+            return self.server_version_info
+
+    @property
     def _supports_cast(self):
         return self.server_version_info is None or \
             self.server_version_info >= (4, 0, 2)