Add SQL Server CI coverage

Change-Id: Ida0d01ae9bcc0573b86e24fddea620a38c962822

9 files changed, 195 insertions, 150 deletions

diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py
index 66acf0072..b95b2964f 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -9,7 +9,6 @@ from . import Connector
 from .. import util
 
 
-import sys
 import re
 
 
@@ -19,10 +18,8 @@ class PyODBCConnector(Connector):
     supports_sane_rowcount_returning = False
     supports_sane_multi_rowcount = False
 
-    if util.py2k:
-        # PyODBC unicode is broken on UCS-4 builds
-        supports_unicode = sys.maxunicode == 65535
-        supports_unicode_statements = supports_unicode
+    supports_unicode_statements = True
+    supports_unicode_binds = True
 
     supports_native_decimal = True
     default_paramstyle = 'named'
@@ -31,21 +28,10 @@ class PyODBCConnector(Connector):
     # hold the desired driver name
     pyodbc_driver_name = None
 
-    # will be set to True after initialize()
-    # if the freetds.so is detected
-    freetds = False
-
-    # will be set to the string version of
-    # the FreeTDS driver if freetds is detected
-    freetds_driver_version = None
-
-    # will be set to True after initialize()
-    # if the libessqlsrv.so is detected
-    easysoft = False
-
     def __init__(self, supports_unicode_binds=None, **kw):
         super(PyODBCConnector, self).__init__(**kw)
-        self._user_supports_unicode_binds = supports_unicode_binds
+        if supports_unicode_binds is not None:
+            self.supports_unicode_binds = supports_unicode_binds
 
     @classmethod
     def dbapi(cls):
@@ -130,40 +116,8 @@ class PyODBCConnector(Connector):
         else:
             return False
 
-    def initialize(self, connection):
-        # determine FreeTDS first.   can't issue SQL easily
-        # without getting unicode_statements/binds set up.
-
-        pyodbc = self.dbapi
-
-        dbapi_con = connection.connection
-
-        _sql_driver_name = dbapi_con.getinfo(pyodbc.SQL_DRIVER_NAME)
-        self.freetds = bool(re.match(r".*libtdsodbc.*\.so", _sql_driver_name
-                                     ))
-        self.easysoft = bool(re.match(r".*libessqlsrv.*\.so", _sql_driver_name
-                                      ))
-
-        if self.freetds:
-            self.freetds_driver_version = dbapi_con.getinfo(
-                pyodbc.SQL_DRIVER_VER)
-
-        self.supports_unicode_statements = (
-            not util.py2k or
-            (not self.freetds and not self.easysoft)
-        )
-
-        if self._user_supports_unicode_binds is not None:
-            self.supports_unicode_binds = self._user_supports_unicode_binds
-        elif util.py2k:
-            self.supports_unicode_binds = (
-                not self.freetds or self.freetds_driver_version >= '0.91'
-            ) and not self.easysoft
-        else:
-            self.supports_unicode_binds = True
-
-        # run other initialization which asks for user name, etc.
-        super(PyODBCConnector, self).initialize(connection)
+    # def initialize(self, connection):
+    #   super(PyODBCConnector, self).initialize(connection)
 
     def _dbapi_version(self):
         if not self.dbapi:
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)
diff --git a/lib/sqlalchemy/testing/provision.py b/lib/sqlalchemy/testing/provision.py
index 033537156..17ddbb567 100644
--- a/lib/sqlalchemy/testing/provision.py
+++ b/lib/sqlalchemy/testing/provision.py
@@ -287,49 +287,61 @@ def _oracle_update_db_opts(db_url, db_opts):
     db_opts['_retry_on_12516'] = True
 
 
-def reap_oracle_dbs(idents_file):
-    log.info("Reaping Oracle dbs...")
+def reap_dbs(idents_file):
+    log.info("Reaping databases...")
+
+    urls = collections.defaultdict(set)
+    idents = collections.defaultdict(set)
 
-    urls = collections.defaultdict(list)
     with open(idents_file) as file_:
         for line in file_:
             line = line.strip()
             db_name, db_url = line.split(" ")
-            urls[db_url].append(db_name)
-
-    for url in urls:
-        if not url.startswith("oracle"):
-            continue
-        idents = urls[url]
-        log.info("db reaper connecting to %r", url)
-        eng = create_engine(url)
-        with eng.connect() as conn:
-
-            log.info("identifiers in file: %s", ", ".join(idents))
-
-            to_reap = conn.execute(
-                "select u.username from all_users u where username "
-                "like 'TEST_%' and not exists (select username "
-                "from v$session where username=u.username)")
-            all_names = {username.lower() for (username, ) in to_reap}
-            to_drop = set()
-            for name in all_names:
-                if name.endswith("_ts1") or name.endswith("_ts2"):
-                    continue
-                elif name in idents:
-                    to_drop.add(name)
-                    if "%s_ts1" % name in all_names:
-                        to_drop.add("%s_ts1" % name)
-                    if "%s_ts2" % name in all_names:
-                        to_drop.add("%s_ts2" % name)
-
-            dropped = total = 0
-            for total, username in enumerate(to_drop, 1):
-                if _ora_drop_ignore(conn, username):
-                    dropped += 1
-            log.info(
-                "Dropped %d out of %d stale databases detected",
-                dropped, total)
+            url_obj = sa_url.make_url(db_url)
+            url_key = (url_obj.get_backend_name(), url_obj.host)
+            urls[url_key].add(db_url)
+            idents[url_key].add(db_name)
+
+    for url_key in urls:
+        backend = url_key[0]
+        url = list(urls[url_key])[0]
+        ident = idents[url_key]
+        if backend == "oracle":
+            _reap_oracle_dbs(url, ident)
+        elif backend == "mssql":
+            _reap_mssql_dbs(url, ident)
+
+def _reap_oracle_dbs(url, idents):
+    log.info("db reaper connecting to %r", url)
+    eng = create_engine(url)
+    with eng.connect() as conn:
+
+        log.info("identifiers in file: %s", ", ".join(idents))
+
+        to_reap = conn.execute(
+            "select u.username from all_users u where username "
+            "like 'TEST_%' and not exists (select username "
+            "from v$session where username=u.username)")
+        all_names = {username.lower() for (username, ) in to_reap}
+        to_drop = set()
+        for name in all_names:
+            if name.endswith("_ts1") or name.endswith("_ts2"):
+                continue
+            elif name in idents:
+                to_drop.add(name)
+                if "%s_ts1" % name in all_names:
+                    to_drop.add("%s_ts1" % name)
+                if "%s_ts2" % name in all_names:
+                    to_drop.add("%s_ts2" % name)
+
+        dropped = total = 0
+        for total, username in enumerate(to_drop, 1):
+            if _ora_drop_ignore(conn, username):
+                dropped += 1
+        log.info(
+            "Dropped %d out of %d stale databases detected",
+            dropped, total)
+
 
 
 @_follower_url_from_main.for_db("oracle")
@@ -340,3 +352,65 @@ def _oracle_follower_url_from_main(url, ident):
     return url
 
 
+@_create_db.for_db("mssql")
+def _mssql_create_db(cfg, eng, ident):
+    with eng.connect().execution_options(
+            isolation_level="AUTOCOMMIT") as conn:
+        conn.execute("create database %s" % ident)
+        conn.execute(
+            "ALTER DATABASE %s SET ALLOW_SNAPSHOT_ISOLATION ON" % ident)
+        conn.execute(
+            "ALTER DATABASE %s SET READ_COMMITTED_SNAPSHOT ON" % ident)
+        conn.execute("use %s" % ident)
+        conn.execute("create schema test_schema")
+
+
+@_drop_db.for_db("mssql")
+def _mssql_drop_db(cfg, eng, ident):
+    with eng.connect().execution_options(
+            isolation_level="AUTOCOMMIT") as conn:
+        _mssql_drop_ignore(conn, ident)
+
+def _mssql_drop_ignore(conn, ident):
+    try:
+        # typically when this happens, we can't KILL the session anyway,
+        # so let the cleanup process drop the DBs
+        # for row in conn.execute("select session_id from sys.dm_exec_sessions "
+        #        "where database_id=db_id('%s')" % ident):
+        #    log.info("killing SQL server sesssion %s", row['session_id'])
+        #    conn.execute("kill %s" % row['session_id'])
+
+        conn.execute("drop database %s" % ident)
+        log.info("Reaped db: %s", ident)
+        return True
+    except exc.DatabaseError as err:
+        log.warning("couldn't drop db: %s", err)
+        return False
+
+
+def _reap_mssql_dbs(url, idents):
+    log.info("db reaper connecting to %r", url)
+    eng = create_engine(url)
+    with eng.connect().execution_options(
+            isolation_level="AUTOCOMMIT") as conn:
+
+        log.info("identifiers in file: %s", ", ".join(idents))
+
+        to_reap = conn.execute(
+            "select d.name from sys.databases as d where name "
+            "like 'TEST_%' and not exists (select session_id "
+            "from sys.dm_exec_sessions "
+            "where database_id=d.database_id)")
+        all_names = {dbname.lower() for (dbname, ) in to_reap}
+        to_drop = set()
+        for name in all_names:
+            if name in idents:
+                to_drop.add(name)
+
+        dropped = total = 0
+        for total, dbname in enumerate(to_drop, 1):
+            if _mssql_drop_ignore(conn, dbname):
+                dropped += 1
+        log.info(
+            "Dropped %d out of %d stale databases detected",
+            dropped, total)
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 327362bf6..b3ad29a3b 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -174,6 +174,19 @@ class SuiteRequirements(Requirements):
         return exclusions.closed()
 
     @property
+    def ctes(self):
+        """Target database supports CTEs"""
+
+        return exclusions.closed()
+
+    @property
+    def ctes_on_dml(self):
+        """target database supports CTES which consist of INSERT, UPDATE
+        or DELETE"""
+
+        return exclusions.closed()
+
+    @property
     def autoincrement_insert(self):
         """target platform generates new surrogate integer primary key values
         when insert() is executed, excluding the pk column."""
@@ -580,6 +593,22 @@ class SuiteRequirements(Requirements):
         return exclusions.closed()
 
     @property
+    def nested_aggregates(self):
+        """target database can select an aggregate from a subquery that's
+        also using an aggregate
+
+        """
+        return exclusions.open()
+
+    @property
+    def recursive_fk_cascade(self):
+        """target database must support ON DELETE CASCADE on a self-referential
+        foreign key
+
+        """
+        return exclusions.open()
+
+    @property
     def precision_numerics_retains_significant_digits(self):
         """A precision numeric type will return empty significant digits,
         i.e. a value such as 10.000 will come back in Decimal form with
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index 4086a4c24..22ae1d8c5 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -242,6 +242,7 @@ class CompoundSelectTest(fixtures.TablesTest):
             [(2, 2, 3), (3, 3, 4)]
         )
 
+    @testing.requires.order_by_col_from_union
     @testing.requires.parens_in_union_contained_select_w_limit_offset
     def test_limit_offset_selectable_in_unions(self):
         table = self.tables.some_table