From ee6ab85e49e6c555c5b80dcd88a3c8de9885869e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 19 May 2023 15:56:31 -0400 Subject: [PATCH] establish Uuid as emulated, pg.UUID as nativeforemulated Repaired the base :class:`.Uuid` datatype for the PostgreSQL dialect to make proper use of the PG-specific ``UUID`` internal datatype when "native_uuid" is selected, so that PG driver behaviors are included. This issue became apparent due to the insertmanyvalues improvement made as part of :ticket:`9618`, where in a similar manner as that of :ticket:` 9739` where the asyncpg driver is very sensitive to datatype casts being present or not, the PostgreSQL driver-specific native ``UUID`` datatype must be invoked when this generic type is used. also consolidate imv tests for largebinary, uuid into suite/test_insert.py Fixes: #9808 Change-Id: Ibadfaff86ddf7db37145b4d003ef4802bd6e8f26 --- doc/build/changelog/unreleased_20/9808.rst | 13 +++ lib/sqlalchemy/dialects/postgresql/base.py | 2 +- .../dialects/postgresql/named_types.py | 2 +- lib/sqlalchemy/sql/sqltypes.py | 13 ++- lib/sqlalchemy/testing/suite/test_insert.py | 95 ++++++++++++++++++- lib/sqlalchemy/testing/suite/test_types.py | 64 ------------- 6 files changed, 118 insertions(+), 71 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/9808.rst diff --git a/doc/build/changelog/unreleased_20/9808.rst b/doc/build/changelog/unreleased_20/9808.rst new file mode 100644 index 0000000000..1b82564262 --- /dev/null +++ b/doc/build/changelog/unreleased_20/9808.rst @@ -0,0 +1,13 @@ +.. change:: + :tags: bug, postgresql, regression + :tickets: 9808 + + Repaired the base :class:`.Uuid` datatype for the PostgreSQL dialect to + make proper use of the PG-specific ``UUID`` internal datatype when + "native_uuid" is selected, so that PG driver behaviors are included. This + issue became apparent due to the insertmanyvalues improvement made as part + of :ticket:`9618`, where in a similar manner as that of :ticket:` 9739` + where the asyncpg driver is very sensitive to datatype casts being present + or not, the PostgreSQL driver-specific native ``UUID`` datatype must be + invoked when this generic type is used. + diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 8e99942930..43d57fc38d 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1598,7 +1598,7 @@ colspecs = { sqltypes.Enum: ENUM, sqltypes.JSON.JSONPathType: _json.JSONPATH, sqltypes.JSON: _json.JSON, - UUID: PGUuid, + sqltypes.Uuid: PGUuid, } diff --git a/lib/sqlalchemy/dialects/postgresql/named_types.py b/lib/sqlalchemy/dialects/postgresql/named_types.py index 4412d841b7..19994d4b99 100644 --- a/lib/sqlalchemy/dialects/postgresql/named_types.py +++ b/lib/sqlalchemy/dialects/postgresql/named_types.py @@ -162,7 +162,7 @@ class EnumDropper(NamedTypeDropper): self.connection.execute(DropEnumType(enum)) -class ENUM(NamedType, sqltypes.NativeForEmulated, sqltypes.Enum): +class ENUM(NamedType, type_api.NativeForEmulated, sqltypes.Enum): """PostgreSQL ENUM type. diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index ce579cca2a..c8d3e3b7e0 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -3519,7 +3519,7 @@ class MatchType(Boolean): _UUID_RETURN = TypeVar("_UUID_RETURN", str, _python_UUID) -class Uuid(TypeEngine[_UUID_RETURN]): +class Uuid(Emulated, TypeEngine[_UUID_RETURN]): """Represent a database agnostic UUID datatype. @@ -3614,6 +3614,10 @@ class Uuid(TypeEngine[_UUID_RETURN]): def python_type(self): return _python_UUID if self.as_uuid else str + @property + def native(self): + return self.native_uuid + def coerce_compared_value(self, op, value): """See :meth:`.TypeEngine.coerce_compared_value` for a description.""" @@ -3716,7 +3720,7 @@ class Uuid(TypeEngine[_UUID_RETURN]): return process -class UUID(Uuid[_UUID_RETURN]): +class UUID(Uuid[_UUID_RETURN], type_api.NativeForEmulated): """Represent the SQL UUID type. @@ -3762,6 +3766,11 @@ class UUID(Uuid[_UUID_RETURN]): self.as_uuid = as_uuid self.native_uuid = True + @classmethod + def adapt_emulated_to_native(cls, impl, **kw): + kw.setdefault("as_uuid", impl.as_uuid) + return cls(**kw) + NULLTYPE = NullType() BOOLEANTYPE = Boolean() diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py index 3915034220..283e67f8dc 100644 --- a/lib/sqlalchemy/testing/suite/test_insert.py +++ b/lib/sqlalchemy/testing/suite/test_insert.py @@ -1,6 +1,7 @@ # mypy: ignore-errors from decimal import Decimal +import uuid from . import testing from .. import fixtures @@ -17,6 +18,10 @@ from ... import literal_column from ... import Numeric from ... import select from ... import String +from ...dialects.postgresql import BYTEA +from ...types import LargeBinary +from ...types import UUID +from ...types import Uuid class LastrowidTest(fixtures.TablesTest): @@ -430,12 +435,13 @@ class ReturningTest(fixtures.TablesTest): this tests insertmanyvalues as well as decimal / floating point RETURNING types - TODO: this might be better in suite/test_types? - """ t = Table( - "t", + # Oracle backends seems to be getting confused if + # this table is named the same as the one + # in test_imv_returning_datatypes. use a different name + "f_t", metadata, Column("id", Integer, Identity(), primary_key=True), Column("value", type_), @@ -491,5 +497,88 @@ class ReturningTest(fixtures.TablesTest): {value}, ) + @testing.combinations( + ( + "non_native_uuid", + Uuid(native_uuid=False), + uuid.uuid4(), + ), + ( + "non_native_uuid_str", + Uuid(as_uuid=False, native_uuid=False), + str(uuid.uuid4()), + ), + ( + "generic_native_uuid", + Uuid(native_uuid=True), + uuid.uuid4(), + testing.requires.uuid_data_type, + ), + ("UUID", UUID(), uuid.uuid4(), testing.requires.uuid_data_type), + ( + "LargeBinary1", + LargeBinary(), + b"this is binary", + ), + ("LargeBinary2", LargeBinary(), b"7\xe7\x9f"), + ("PG BYTEA", BYTEA(), b"7\xe7\x9f", testing.only_on("postgresql")), + argnames="type_,value", + id_="iaa", + ) + @testing.variation("sort_by_parameter_order", [True, False]) + @testing.variation("multiple_rows", [True, False]) + @testing.requires.insert_returning + def test_imv_returning_datatypes( + self, + connection, + metadata, + sort_by_parameter_order, + type_, + value, + multiple_rows, + ): + """test #9739, #9808 (similar to #9701). + + this tests insertmanyvalues in conjunction with various datatypes. + + These tests are particularly for the asyncpg driver which needs + most types to be explicitly cast for the new IMV format + + """ + t = Table( + "d_t", + metadata, + Column("id", Integer, Identity(), primary_key=True), + Column("value", type_), + ) + + t.create(connection) + + result = connection.execute( + t.insert().returning( + t.c.id, + t.c.value, + sort_by_parameter_order=bool(sort_by_parameter_order), + ), + [{"value": value} for i in range(10)] + if multiple_rows + else {"value": value}, + ) + + if multiple_rows: + i_range = range(1, 11) + else: + i_range = range(1, 2) + + eq_( + set(result), + {(id_, value) for id_ in i_range}, + ) + + eq_( + set(connection.scalars(select(t.c.value))), + {value}, + ) + __all__ = ("LastrowidTest", "InsertBehaviorTest", "ReturningTest") diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py index b93d65becd..92781cc1b3 100644 --- a/lib/sqlalchemy/testing/suite/test_types.py +++ b/lib/sqlalchemy/testing/suite/test_types.py @@ -27,7 +27,6 @@ from ... import cast from ... import Date from ... import DateTime from ... import Float -from ... import Identity from ... import Integer from ... import JSON from ... import literal @@ -47,7 +46,6 @@ from ... import Unicode from ... import UnicodeText from ... import UUID from ... import Uuid -from ...dialects.postgresql import BYTEA from ...orm import declarative_base from ...orm import Session from ...sql import sqltypes @@ -317,68 +315,6 @@ class BinaryTest(_LiteralRoundTripFixture, fixtures.TablesTest): row = connection.execute(select(binary_table.c.pickle_data)).first() eq_(row, ({"foo": [1, 2, 3], "bar": "bat"},)) - @testing.combinations( - ( - LargeBinary(), - b"this is binary", - ), - (LargeBinary(), b"7\xe7\x9f"), - (BYTEA(), b"7\xe7\x9f", testing.only_on("postgresql")), - argnames="type_,value", - ) - @testing.variation("sort_by_parameter_order", [True, False]) - @testing.variation("multiple_rows", [True, False]) - @testing.requires.insert_returning - def test_imv_returning( - self, - connection, - metadata, - sort_by_parameter_order, - type_, - value, - multiple_rows, - ): - """test #9739 (similar to #9701). - - this tests insertmanyvalues as well as binary - RETURNING types - - """ - t = Table( - "t", - metadata, - Column("id", Integer, Identity(), primary_key=True), - Column("value", type_), - ) - - t.create(connection) - - result = connection.execute( - t.insert().returning( - t.c.id, - t.c.value, - sort_by_parameter_order=bool(sort_by_parameter_order), - ), - [{"value": value} for i in range(10)] - if multiple_rows - else {"value": value}, - ) - - if multiple_rows: - i_range = range(1, 11) - else: - i_range = range(1, 2) - - eq_( - set(result), - {(id_, value) for id_ in i_range}, - ) - - eq_( - set(connection.scalars(select(t.c.value))), - {value}, - ) - class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest): __requires__ = ("text_type",) -- 2.47.3