From 01cbf4d7b8acab54a054bb36dc2792b518b5cd1f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 7 Nov 2019 15:31:48 -0500 Subject: Add type accessors for JSON indexed/pathed element access Added new accessors to expressions of type :class:`.JSON` to allow for specific datatype access and comparison, covering strings, integers, numeric, boolean elements. This revises the documented approach of CASTing to string when comparing values, instead adding specific functionality into the PostgreSQL, SQlite, MySQL dialects to reliably deliver these basic types in all cases. The change also delivers a new feature to the test exclusions system so that combinations and exclusions can be used together. Fixes: #4276 Change-Id: Ica5a926c060feb40a0a7cd60b9d6e061d7825728 --- lib/sqlalchemy/testing/requirements.py | 4 + lib/sqlalchemy/testing/suite/test_types.py | 292 ++++++++++++++++++++--------- 2 files changed, 209 insertions(+), 87 deletions(-) (limited to 'lib/sqlalchemy/testing') diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index fd8d82690..5b26ac72e 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -685,6 +685,10 @@ class SuiteRequirements(Requirements): return self.json_type + @property + def json_index_supplementary_unicode_element(self): + return exclusions.open() + @property def precision_numerics_general(self): """target backend has general support for moderately high-precision diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py index 435ab4689..bf5b18d0e 100644 --- a/lib/sqlalchemy/testing/suite/test_types.py +++ b/lib/sqlalchemy/testing/suite/test_types.py @@ -9,6 +9,7 @@ from .. import engines from .. import fixtures from .. import mock from ..assertions import eq_ +from ..assertions import is_ from ..config import requirements from ..schema import Column from ..schema import Table @@ -710,31 +711,6 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): datatype = JSON - data1 = {"key1": "value1", "key2": "value2"} - - data2 = { - "Key 'One'": "value1", - "key two": "value2", - "key three": "value ' three '", - } - - data3 = { - "key1": [1, 2, 3], - "key2": ["one", "two", "three"], - "key3": [{"four": "five"}, {"six": "seven"}], - } - - data4 = ["one", "two", "three"] - - data5 = { - "nested": { - "elem1": [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}], - "elem2": {"elem3": {"elem4": "elem5"}}, - } - } - - data6 = {"a": 5, "b": "some value", "c": {"foo": "bar"}} - @classmethod def define_tables(cls, metadata): Table( @@ -747,7 +723,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): ) def test_round_trip_data1(self): - self._test_round_trip(self.data1) + self._test_round_trip({"key1": "value1", "key2": "value2"}) def _test_round_trip(self, data_element): data_table = self.tables.data_table @@ -760,6 +736,97 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): eq_(row, (data_element,)) + def _index_fixtures(fn): + fn = testing.combinations( + ("boolean", True), + ("boolean", False), + ("boolean", None), + ("string", "some string"), + ("string", None), + ("string", util.u("réve illé")), + ( + "string", + util.u("réve🐍 illé"), + testing.requires.json_index_supplementary_unicode_element, + ), + ("integer", 15), + ("integer", 1), + ("integer", 0), + ("integer", None), + ("float", 28.5), + ("float", None), + # TODO: how to test for comaprison + # ("json", {"foo": "bar"}), + id_="sa", + )(fn) + return fn + + @_index_fixtures + def test_index_typed_access(self, datatype, value): + data_table = self.tables.data_table + data_element = {"key1": value} + with config.db.connect() as conn: + conn.execute( + data_table.insert(), + { + "name": "row1", + "data": data_element, + "nulldata": data_element, + }, + ) + + expr = data_table.c.data["key1"] + expr = getattr(expr, "as_%s" % datatype)() + + roundtrip = conn.scalar(select([expr])) + eq_(roundtrip, value) + if util.py3k: # skip py2k to avoid comparing unicode to str etc. + is_(type(roundtrip), type(value)) + + @_index_fixtures + def test_index_typed_comparison(self, datatype, value): + data_table = self.tables.data_table + data_element = {"key1": value} + with config.db.connect() as conn: + conn.execute( + data_table.insert(), + { + "name": "row1", + "data": data_element, + "nulldata": data_element, + }, + ) + + expr = data_table.c.data["key1"] + expr = getattr(expr, "as_%s" % datatype)() + + row = conn.execute(select([expr]).where(expr == value)).first() + + # make sure we get a row even if value is None + eq_(row, (value,)) + + @_index_fixtures + def test_path_typed_comparison(self, datatype, value): + data_table = self.tables.data_table + data_element = {"key1": {"subkey1": value}} + with config.db.connect() as conn: + conn.execute( + data_table.insert(), + { + "name": "row1", + "data": data_element, + "nulldata": data_element, + }, + ) + + expr = data_table.c.data[("key1", "subkey1")] + expr = getattr(expr, "as_%s" % datatype)() + + row = conn.execute(select([expr]).where(expr == value)).first() + + # make sure we get a row even if value is None + eq_(row, (value,)) + def test_round_trip_custom_json(self): data_table = self.tables.data_table data_element = {"key1": "data1"} @@ -840,6 +907,112 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): eq_(conn.scalar(select([col])), None) + def test_unicode_round_trip(self): + # note we include Unicode supplementary characters as well + with config.db.connect() as conn: + conn.execute( + self.tables.data_table.insert(), + { + "name": "r1", + "data": { + util.u("réve🐍 illé"): util.u("réve🐍 illé"), + "data": {"k1": util.u("drôl🐍e")}, + }, + }, + ) + + eq_( + conn.scalar(select([self.tables.data_table.c.data])), + { + util.u("réve🐍 illé"): util.u("réve🐍 illé"), + "data": {"k1": util.u("drôl🐍e")}, + }, + ) + + def test_eval_none_flag_orm(self): + + Base = declarative_base() + + class Data(Base): + __table__ = self.tables.data_table + + s = Session(testing.db) + + d1 = Data(name="d1", data=None, nulldata=None) + s.add(d1) + s.commit() + + s.bulk_insert_mappings( + Data, [{"name": "d2", "data": None, "nulldata": None}] + ) + eq_( + s.query( + cast(self.tables.data_table.c.data, String()), + cast(self.tables.data_table.c.nulldata, String), + ) + .filter(self.tables.data_table.c.name == "d1") + .first(), + ("null", None), + ) + eq_( + s.query( + cast(self.tables.data_table.c.data, String()), + cast(self.tables.data_table.c.nulldata, String), + ) + .filter(self.tables.data_table.c.name == "d2") + .first(), + ("null", None), + ) + + +class JSONStringCastIndexTest(_LiteralRoundTripFixture, fixtures.TablesTest): + """test JSON index access with "cast to string", which we have documented + for a long time as how to compare JSON values, but is ultimately not + reliable in all cases. + + """ + + __requires__ = ("json_type",) + __backend__ = True + + datatype = JSON + + data1 = {"key1": "value1", "key2": "value2"} + + data2 = { + "Key 'One'": "value1", + "key two": "value2", + "key three": "value ' three '", + } + + data3 = { + "key1": [1, 2, 3], + "key2": ["one", "two", "three"], + "key3": [{"four": "five"}, {"six": "seven"}], + } + + data4 = ["one", "two", "three"] + + data5 = { + "nested": { + "elem1": [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}], + "elem2": {"elem3": {"elem4": "elem5"}}, + } + } + + data6 = {"a": 5, "b": "some value", "c": {"foo": "bar"}} + + @classmethod + def define_tables(cls, metadata): + Table( + "data_table", + metadata, + Column("id", Integer, primary_key=True), + Column("name", String(30), nullable=False), + Column("data", cls.datatype), + Column("nulldata", cls.datatype(none_as_null=True)), + ) + def _criteria_fixture(self): config.db.execute( self.tables.data_table.insert(), @@ -869,7 +1042,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): eq_(conn.scalar(literal_sql), expected) - def test_crit_spaces_in_key(self): + def test_string_cast_crit_spaces_in_key(self): name = self.tables.data_table.c.name col = self.tables.data_table.c["data"] @@ -885,7 +1058,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): ) @config.requirements.json_array_indexes - def test_crit_simple_int(self): + def test_string_cast_crit_simple_int(self): name = self.tables.data_table.c.name col = self.tables.data_table.c["data"] @@ -896,13 +1069,13 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): and_(name == "r4", cast(col[1], String) == '"two"'), "r4" ) - def test_crit_mixed_path(self): + def test_string_cast_crit_mixed_path(self): col = self.tables.data_table.c["data"] self._test_index_criteria( cast(col[("key3", 1, "six")], String) == '"seven"', "r3" ) - def test_crit_string_path(self): + def test_string_cast_crit_string_path(self): col = self.tables.data_table.c["data"] self._test_index_criteria( cast(col[("nested", "elem2", "elem3", "elem4")], String) @@ -910,7 +1083,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): "r5", ) - def test_crit_against_string_basic(self): + def test_string_cast_crit_against_string_basic(self): name = self.tables.data_table.c.name col = self.tables.data_table.c["data"] @@ -949,67 +1122,12 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest): test_literal=False, ) - def test_unicode_round_trip(self): - with config.db.connect() as conn: - conn.execute( - self.tables.data_table.insert(), - { - "name": "r1", - "data": { - util.u("réve🐍 illé"): util.u("réve🐍 illé"), - "data": {"k1": util.u("drôl🐍e")}, - }, - }, - ) - - eq_( - conn.scalar(select([self.tables.data_table.c.data])), - { - util.u("réve🐍 illé"): util.u("réve🐍 illé"), - "data": {"k1": util.u("drôl🐍e")}, - }, - ) - - def test_eval_none_flag_orm(self): - - Base = declarative_base() - - class Data(Base): - __table__ = self.tables.data_table - - s = Session(testing.db) - - d1 = Data(name="d1", data=None, nulldata=None) - s.add(d1) - s.commit() - - s.bulk_insert_mappings( - Data, [{"name": "d2", "data": None, "nulldata": None}] - ) - eq_( - s.query( - cast(self.tables.data_table.c.data, String()), - cast(self.tables.data_table.c.nulldata, String), - ) - .filter(self.tables.data_table.c.name == "d1") - .first(), - ("null", None), - ) - eq_( - s.query( - cast(self.tables.data_table.c.data, String()), - cast(self.tables.data_table.c.nulldata, String), - ) - .filter(self.tables.data_table.c.name == "d2") - .first(), - ("null", None), - ) - __all__ = ( "UnicodeVarcharTest", "UnicodeTextTest", "JSONTest", + "JSONStringCastIndexTest", "DateTest", "DateTimeTest", "TextTest", -- cgit v1.2.1