audition pymssql once more; retire sane_rowcount_returning

pymssql seems to be maintained again and seems to be working
completely, so let's try re-enabling it.

Fixed issue in the new :class:`.Uuid` datatype which prevented it from
working with the pymssql driver. As pymssql seems to be maintained again,
restored testing support for pymssql.

Tweaked the pymssql dialect to take better advantage of
RETURNING for INSERT statements in order to retrieve last inserted primary
key values, in the same way as occurs for the mssql+pyodbc dialect right
now.

Identified that the ``sqlite`` and ``mssql+pyodbc`` dialects are now
compatible with the SQLAlchemy ORM's "versioned rows" feature, since
SQLAlchemy now computes rowcount for a RETURNING statement in this specific
case by counting the rows returned, rather than relying upon
``cursor.rowcount``.  In particular, the ORM versioned rows use case
(documented at :ref:`mapper_version_counter`) should now be fully
supported with the SQL Server pyodbc dialect.

Change-Id: I38a0666587212327aecf8f98e86031ab25d1f14d
References: #5321
Fixes: #9414

6 files changed, 91 insertions, 13 deletions

diff --git a/test/dialect/mssql/test_query.py b/test/dialect/mssql/test_query.py
index b65e27445..35575bc13 100644
--- a/test/dialect/mssql/test_query.py
+++ b/test/dialect/mssql/test_query.py
@@ -18,6 +18,7 @@ from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy.dialects.mssql import base as mssql
+from sqlalchemy.dialects.mssql import pyodbc as mssql_pyodbc
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import config
 from sqlalchemy.testing import engines
@@ -409,7 +410,7 @@ def full_text_search_missing():
         return result.scalar() == 0
 
 
-class MatchTest(fixtures.TablesTest, AssertsCompiledSQL):
+class MatchTest(AssertsCompiledSQL, fixtures.TablesTest):
 
     __only_on__ = "mssql"
     __skip_if__ = (full_text_search_missing,)
@@ -517,6 +518,7 @@ class MatchTest(fixtures.TablesTest, AssertsCompiledSQL):
         self.assert_compile(
             matchtable.c.title.match("somstr"),
             "CONTAINS (matchtable.title, ?)",
+            dialect=mssql_pyodbc.dialect(paramstyle="qmark"),
         )
 
     def test_simple_match(self, connection):
diff --git a/test/dialect/mssql/test_types.py b/test/dialect/mssql/test_types.py
index 867e42202..cb7ed3102 100644
--- a/test/dialect/mssql/test_types.py
+++ b/test/dialect/mssql/test_types.py
@@ -815,7 +815,36 @@ class TypeRoundTripTest(
         )
         return t, (d1, t1, d2, d3)
 
-    def test_date_roundtrips(self, date_fixture, connection):
+    def test_date_roundtrips_no_offset(self, date_fixture, connection):
+        t, (d1, t1, d2, d3) = date_fixture
+        connection.execute(
+            t.insert(),
+            dict(
+                adate=d1,
+                adatetime=d2,
+                atime1=t1,
+                atime2=d2,
+            ),
+        )
+
+        row = connection.execute(t.select()).first()
+        eq_(
+            (
+                row.adate,
+                row.adatetime,
+                row.atime1,
+                row.atime2,
+            ),
+            (
+                d1,
+                d2,
+                t1,
+                d2.time(),
+            ),
+        )
+
+    @testing.skip_if("+pymssql", "offsets dont seem to work")
+    def test_date_roundtrips_w_offset(self, date_fixture, connection):
         t, (d1, t1, d2, d3) = date_fixture
         connection.execute(
             t.insert(),
@@ -855,6 +884,7 @@ class TypeRoundTripTest(
         (datetime.datetime(2007, 10, 30, 11, 2, 32)),
         argnames="date",
     )
+    @testing.skip_if("+pymssql", "unknown failures")
     def test_tz_present_or_non_in_dates(self, date_fixture, connection, date):
         t, (d1, t1, d2, d3) = date_fixture
         connection.execute(
@@ -954,6 +984,7 @@ class TypeRoundTripTest(
         id_="iaaa",
         argnames="dto_param_value, expected_offset_hours, should_fail",
     )
+    @testing.skip_if("+pymssql", "offsets dont seem to work")
     def test_datetime_offset(
         self,
         datetimeoffset_fixture,
diff --git a/test/orm/test_versioning.py b/test/orm/test_versioning.py
index f6b9f18fc..7de90fc5c 100644
--- a/test/orm/test_versioning.py
+++ b/test/orm/test_versioning.py
@@ -45,12 +45,7 @@ def conditional_sane_rowcount_warnings(
     update=False, delete=False, only_returning=False
 ):
     warnings = ()
-    if (
-        only_returning
-        and not testing.db.dialect.supports_sane_rowcount_returning
-    ) or (
-        not only_returning and not testing.db.dialect.supports_sane_rowcount
-    ):
+    if not testing.db.dialect.supports_sane_rowcount:
         if update:
             warnings += (
                 "Dialect .* does not support "
@@ -1466,7 +1461,6 @@ class ServerVersioningTest(fixtures.MappedTest):
 
         eq_(f1.version_id, 2)
 
-    @testing.requires.sane_rowcount_w_returning
     @testing.requires.updateable_autoincrement_pks
     @testing.requires.update_returning
     def test_sql_expr_w_mods_bump(self):
@@ -1636,7 +1630,6 @@ class ServerVersioningTest(fixtures.MappedTest):
             self.assert_sql_execution(testing.db, sess.flush, *statements)
 
     @testing.requires.independent_connections
-    @testing.requires.sane_rowcount_w_returning
     def test_concurrent_mod_err_expire_on_commit(self):
         sess = self._fixture()
 
@@ -1661,7 +1654,6 @@ class ServerVersioningTest(fixtures.MappedTest):
         )
 
     @testing.requires.independent_connections
-    @testing.requires.sane_rowcount_w_returning
     def test_concurrent_mod_err_noexpire_on_commit(self):
         sess = self._fixture(expire_on_commit=False)
 
diff --git a/test/requirements.py b/test/requirements.py
index 923d98b46..9d51ae477 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -967,6 +967,10 @@ class DefaultRequirements(SuiteRequirements):
         )
 
     @property
+    def arraysize(self):
+        return skip_if("+pymssql", "DBAPI is missing this attribute")
+
+    @property
     def emulated_lastrowid(self):
         """ "target dialect retrieves cursor.lastrowid or an equivalent
         after an insert() construct executes.
@@ -1188,9 +1192,51 @@ class DefaultRequirements(SuiteRequirements):
         return exclusions.open()
 
     @property
+    def date_implicit_bound(self):
+        """target dialect when given a date object will bind it such
+        that the database server knows the object is a date, and not
+        a plain string.
+
+        """
+
+        # mariadbconnector works.  pyodbc we dont know, not supported in
+        # testing.
+        return exclusions.fails_on(
+            [
+                "+mysqldb",
+                "+pymysql",
+                "+asyncmy",
+                "+mysqlconnector",
+                "+cymysql",
+                "+aiomysql",
+            ]
+        )
+
+    @property
+    def time_implicit_bound(self):
+        """target dialect when given a time object will bind it such
+        that the database server knows the object is a time, and not
+        a plain string.
+
+        """
+
+        # mariadbconnector works.  pyodbc we dont know, not supported in
+        # testing.
+        return exclusions.fails_on(
+            [
+                "+mysqldb",
+                "+pymysql",
+                "+asyncmy",
+                "+mysqlconnector",
+                "+cymysql",
+                "+aiomysql",
+            ]
+        )
+
+    @property
     def datetime_implicit_bound(self):
         """target dialect when given a datetime object will bind it such
-        that the database server knows the object is a datetime, and not
+        that the database server knows the object is a date, and not
         a plain string.
 
         """
@@ -1205,6 +1251,7 @@ class DefaultRequirements(SuiteRequirements):
                 "+mysqlconnector",
                 "+cymysql",
                 "+aiomysql",
+                "+pymssql",
             ]
         )
 
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 1fe1b3323..633972b45 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -984,7 +984,11 @@ class PKDefaultTest(fixtures.TestBase):
                 metadata,
                 Column(
                     "date_id",
-                    DateTime(timezone=True),
+                    # we want no tzinfo normally since pymssql doesn't do
+                    # it right now
+                    DateTime().with_variant(
+                        DateTime(timezone=True), "postgresql"
+                    ),
                     default=text("current_timestamp"),
                     primary_key=True,
                 ),
diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py
index 66584c96e..ba812d38b 100644
--- a/test/sql/test_resultset.py
+++ b/test/sql/test_resultset.py
@@ -539,6 +539,7 @@ class CursorResultTest(fixtures.TablesTest):
             rows.append(row)
         eq_(len(rows), 2)
 
+    @testing.requires.arraysize
     def test_fetchmany_arraysize_default(self, connection):
         users = self.tables.users
 
@@ -552,6 +553,7 @@ class CursorResultTest(fixtures.TablesTest):
 
         eq_(len(rows), min(arraysize, 150))
 
+    @testing.requires.arraysize
     def test_fetchmany_arraysize_set(self, connection):
         users = self.tables.users