add backend agnostic UUID datatype

Added new backend-agnostic :class:`_types.Uuid` datatype generalized from
the PostgreSQL dialects to now be a core type, as well as migrated
:class:`_types.UUID` from the PostgreSQL dialect. Thanks to Trevor Gross
for the help on this.

also includes:

* corrects some missing behaviors in the suite literal fixtures
  test where row round trips weren't being correctly asserted.
* fixes some of the ISO literal date rendering added in
  952383f9ee0 for #5052 to truncate datetime strings for date/time
  datatypes in the same way that drivers typically do for bound
  parameters; this was not working fully and wasn't caught by the
  broken test fixture

Fixes: #7212
Change-Id: I981ac6d34d278c18281c144430a528764c241b04

5 files changed, 48 insertions, 3 deletions

diff --git a/test/dialect/mssql/test_types.py b/test/dialect/mssql/test_types.py
index 90933e05a..ff84f180b 100644
--- a/test/dialect/mssql/test_types.py
+++ b/test/dialect/mssql/test_types.py
@@ -34,6 +34,7 @@ from sqlalchemy import UnicodeText
 from sqlalchemy.dialects.mssql import base as mssql
 from sqlalchemy.dialects.mssql import ROWVERSION
 from sqlalchemy.dialects.mssql import TIMESTAMP
+from sqlalchemy.dialects.mssql import UNIQUEIDENTIFIER
 from sqlalchemy.dialects.mssql.base import _MSDate
 from sqlalchemy.dialects.mssql.base import BIT
 from sqlalchemy.dialects.mssql.base import DATETIMEOFFSET
@@ -53,6 +54,7 @@ from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import is_
 from sqlalchemy.testing import is_not
 from sqlalchemy.testing import pickleable
+from sqlalchemy.testing.suite import test_types
 from sqlalchemy.util import b
 
 
@@ -1234,6 +1236,12 @@ class StringTest(fixtures.TestBase, AssertsCompiledSQL):
         )
 
 
+class UniqueIdentifierTest(test_types.UuidTest):
+    __only_on__ = "mssql"
+    __backend__ = True
+    datatype = UNIQUEIDENTIFIER
+
+
 class MyPickleType(types.TypeDecorator):
     impl = PickleType
     cache_ok = True
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index bca952ade..ddb199aa4 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -2781,7 +2781,11 @@ class SpecialTypesTest(fixtures.TablesTest, ComparesTables):
 
 class UUIDTest(fixtures.TestBase):
 
-    """Test the bind/return values of the UUID type."""
+    """Test postgresql-specific UUID cases.
+
+    See also generic UUID tests in testing/suite/test_types
+
+    """
 
     __only_on__ = "postgresql >= 8.3"
     __backend__ = True
diff --git a/test/orm/declarative/test_typed_mapping.py b/test/orm/declarative/test_typed_mapping.py
index 865735439..ce8cd6bdf 100644
--- a/test/orm/declarative/test_typed_mapping.py
+++ b/test/orm/declarative/test_typed_mapping.py
@@ -9,6 +9,7 @@ from typing import Set
 from typing import Type
 from typing import TypeVar
 from typing import Union
+import uuid
 
 from sqlalchemy import BIGINT
 from sqlalchemy import Column
@@ -22,6 +23,7 @@ from sqlalchemy import select
 from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
+from sqlalchemy import types
 from sqlalchemy import VARCHAR
 from sqlalchemy.exc import ArgumentError
 from sqlalchemy.orm import as_declarative
@@ -479,6 +481,23 @@ class MappedColumnTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "SELECT users.data, users.id FROM users",
         )
 
+    @testing.combinations(
+        (str, types.String),
+        (Decimal, types.Numeric),
+        (float, types.Float),
+        (datetime.datetime, types.DateTime),
+        (uuid.UUID, types.Uuid),
+        argnames="pytype,sqltype",
+    )
+    def test_datatype_lookups(self, decl_base, pytype, sqltype):
+        class MyClass(decl_base):
+            __tablename__ = "mytable"
+            id: Mapped[int] = mapped_column(primary_key=True)
+
+            data: Mapped[pytype]
+
+        assert isinstance(MyClass.__table__.c.data.type, sqltype)
+
 
 class MixinTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = "default"
diff --git a/test/requirements.py b/test/requirements.py
index f7753fbf7..f5cbbbf8d 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -851,6 +851,15 @@ class DefaultRequirements(SuiteRequirements):
         return exclusions.open()
 
     @property
+    def unicode_data_no_special_types(self):
+        """Target database/dialect can receive / deliver / compare data with
+        non-ASCII characters in plain VARCHAR, TEXT columns, without the need
+        for special "national" datatypes like NVARCHAR or similar.
+
+        """
+        return exclusions.fails_on("mssql")
+
+    @property
     def unicode_ddl(self):
         """Target driver must support some degree of non-ascii symbol names."""
 
@@ -1750,3 +1759,8 @@ class DefaultRequirements(SuiteRequirements):
                 return res is not None
 
         return only_on(["mssql"]) + only_if(check)
+
+    @property
+    def uuid_data_type(self):
+        """Return databases that support the UUID datatype."""
+        return only_on(("postgresql >= 8.3", "mariadb >= 10.7.0"))
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index d496b323b..b2afa2dba 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -381,14 +381,14 @@ class TypeAffinityTest(fixtures.TestBase):
 
             def load_dialect_impl(self, dialect):
                 if dialect.name == "postgresql":
-                    return dialect.type_descriptor(postgresql.UUID())
+                    return dialect.type_descriptor(postgresql.INET())
                 else:
                     return dialect.type_descriptor(CHAR(32))
 
         t1 = MyType()
         d = postgresql.dialect()
         assert t1._type_affinity is String
-        assert t1.dialect_impl(d)._type_affinity is postgresql.UUID
+        assert t1.dialect_impl(d)._type_affinity is postgresql.INET
 
 
 class AsGenericTest(fixtures.TestBase):