diff options
-rw-r--r-- | doc/build/changelog/changelog_11.rst | 12 | ||||
-rw-r--r-- | doc/build/changelog/migration_11.rst | 34 | ||||
-rw-r--r-- | doc/build/index.rst | 3 | ||||
-rw-r--r-- | doc/build/orm/extensions/index.rst | 1 | ||||
-rw-r--r-- | lib/sqlalchemy/ext/indexable.py | 320 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 4 | ||||
-rw-r--r-- | test/ext/test_indexable.py | 344 | ||||
-rw-r--r-- | test/requirements.py | 7 |
8 files changed, 724 insertions, 1 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 662bad00a..7194ac116 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -22,6 +22,18 @@ :version: 1.1.0b1 .. change:: + :tags: feature, orm, ext + + A new ORM extension :ref:`indexable_toplevel` is added, which allows + construction of Python attributes which refer to specific elements + of "indexed" structures such as arrays and JSON fields. Pull request + courtesy Jeong YunWon. + + .. seealso:: + + :ref:`feature_indexable` + + .. change:: :tags: bug, sql :tickets: 3724 diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index ea932b509..766b06f2e 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -258,6 +258,40 @@ relationship attribute to an object, which is handled distinctly:: :ticket:`3321` +.. _feature_indexable: + +New Indexable ORM extension +--------------------------- + +The :ref:`indexable_toplevel` extension is an extension to the hybrid +attribute feature which allows the construction of attributes which +refer to specific elements of an "indexable" data type, such as an array +or JSON field:: + + class Person(Base): + __tablename__ = 'person' + + id = Column(Integer, primary_key=True) + data = Column(JSON) + + name = index_property('data', 'name') + +Above, the ``name`` attribute will read/write the field ``"name"`` +from the JSON column ``data``, after initializing it to an +empty dictionary:: + + >>> person = Person(name='foobar') + >>> person.name + foobar + +The extension also triggers a change event when the attribute is modified, +so that there's no need to use :class:`~.mutable.MutableDict` in order +to track this change. + +.. seealso:: + + :ref:`indexable_toplevel` + .. _change_3250: New options allowing explicit persistence of NULL over a default diff --git a/doc/build/index.rst b/doc/build/index.rst index a28dfca82..377ccfb41 100644 --- a/doc/build/index.rst +++ b/doc/build/index.rst @@ -38,7 +38,8 @@ of Python objects, proceed first to the tutorial. :doc:`Association Proxy <orm/extensions/associationproxy>` | :doc:`Hybrid Attributes <orm/extensions/hybrid>` | :doc:`Automap <orm/extensions/automap>` | - :doc:`Mutable Scalars <orm/extensions/mutable>` + :doc:`Mutable Scalars <orm/extensions/mutable>` | + :doc:`Indexable <orm/extensions/indexable>` * **ORM Usage:** :doc:`Session Usage and Guidelines <orm/session>` | diff --git a/doc/build/orm/extensions/index.rst b/doc/build/orm/extensions/index.rst index 091ceb40a..e23fd55ee 100644 --- a/doc/build/orm/extensions/index.rst +++ b/doc/build/orm/extensions/index.rst @@ -23,5 +23,6 @@ behavior. In particular the "Horizontal Sharding", "Hybrid Attributes", and orderinglist horizontal_shard hybrid + indexable instrumentation diff --git a/lib/sqlalchemy/ext/indexable.py b/lib/sqlalchemy/ext/indexable.py new file mode 100644 index 000000000..5002e9beb --- /dev/null +++ b/lib/sqlalchemy/ext/indexable.py @@ -0,0 +1,320 @@ +# ext/index.py +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: http://www.opensource.org/licenses/mit-license.php + +"""Define attributes on ORM-mapped classes that have "index" attributes for +columns with :class:`~.types.Indexable` types. + +"index" means the attribute is associated with an element of an +:class:`~.types.Indexable` column with the predefined index to access it. +The :class:`~.types.Indexable` types include types such as +:class:`~.types.ARRAY`, :class:`~.types.JSON` and +:class:`~.postgresql.HSTORE`. + + + +The :mod:`~sqlalchemy.ext.indexable` extension provides +:class:`~.schema.Column`-like interface for any element of an +:class:`~.types.Indexable` typed column. In simple cases, it can be +treated as a :class:`~.schema.Column` - mapped attribute. + + +.. versionadded:: 1.1 + +Synopsis +======== + +Given ``Person`` as a model with a primary key and JSON data field. +While this field may have any number of elements encoded within it, +we would like to refer to the element called ``name`` individually +as a dedicated attribute which behaves like a standalone column:: + + from sqlalchemy import Column, JSON, Integer + from sqlalchemy.ext.declarative import declarative_base + from sqlalchemy.ext.indexable import index_property + + Base = declarative_base() + + class Person(Base): + __tablename__ = 'person' + + id = Column(Integer, primary_key=True) + data = Column(JSON) + + name = index_property('data', 'name') + + +Above, the ``name`` attribute now behaves like a mapped column. We +can compose a new ``Person`` and set the value of ``name``:: + + >>> person = Person(name='Alchemist') + +The value is now accessible:: + + >>> person.name + 'Alchemist' + +Behind the scenes, the JSON field was initialized to a new blank dictionary +and the field was set:: + + >>> person.data + {"name": "Alchemist'} + +The field is mutable in place:: + + >>> person.name = 'Renamed' + >>> person.name + 'Renamed' + >>> person.data + {'name': 'Renamed'} + +When using :class:`.index_property`, the change that we make to the indexable +structure is also automatically tracked as history; we no longer need +to use :class:`~.mutable.MutableDict` in order to track this change +for the unit of work. + +Deletions work normally as well:: + + >>> del person.name + >>> person.data + {} + +Above, deletion of ``person.name`` deletes the value from the dictionary, +but not the dictionary itself. + +A missing key will produce ``AttributeError``:: + + >>> person = Person() + >>> person.name + ... + AttributeError: 'name' + +The attributes are also accessible at the class level. +Below, we illustrate ``Person.name`` used to generate +an indexed SQL criteria:: + + >>> from sqlalchemy.orm import Session + >>> session = Session() + >>> query = session.query(Person).filter(Person.name == 'Alchemist') + +The above query is equivalent to:: + + >>> query = session.query(Person).filter(Person.data['name'] == 'Alchemist') + +Multiple :class:`.index_property` objects can be chained to produce +multiple levels of indexing:: + + from sqlalchemy import Column, JSON, Integer + from sqlalchemy.ext.declarative import declarative_base + from sqlalchemy.ext.indexable import index_property + + Base = declarative_base() + + class Person(Base): + __tablename__ = 'person' + + id = Column(Integer, primary_key=True) + data = Column(JSON) + + birthday = index_property('data', 'birthday') + year = index_property('birthday', 'year') + month = index_property('birthday', 'month') + day = index_property('birthday', 'day') + +Above, a query such as:: + + q = session.query(Person).filter(Person.year == '1980') + +On a Postgresql backend, the above query will render as:: + + SELECT person.id, person.data + FROM person + WHERE person.data -> %(data_1)s -> %(param_1)s = %(param_2)s + +Default Values +============== + +:class:`.index_property` includes special behaviors for when the indexed +data structure does not exist, and a set operation is called: + +* For an :class:`.index_property` that is given an integer index value, + the default data structure will be a Python list of ``None`` values, + at least as long as the index value; the value is then set at its + place in the list. This means for an index value of zero, the list + will be initalized to ``[None]`` before setting the given value, + and for an index value of five, the list will be initialized to + ``[None, None, None, None, None]`` before setting the fifth element + to the given value. Note that an existing list is **not** extended + in place to receive a value. + +* for an :class:`.index_property` that is given any other kind of index + value (e.g. strings usually), a Python dictionary is used as the + default data structure. + +* The default data structure can be set to any Python callable using the + :paramref:`.index_property.datatype` parameter, overriding the previous + rules. + + + + + + +Subclassing +=========== + +:class:`.index_property` can be subclassed, in particular for the common +use case of providing coercion of values or SQL expressions as they are +accessed. Below is a common recipe for use with a Postgresql JSON type, +where we want to also include automatic casting plus ``astext()``:: + + class pg_json_property(index_property): + def __init__(self, attr_name, index, cast_type): + super(pg_json_property, self).__init__(attr_name, index) + self.cast_type = cast_type + + def expr(self, model): + expr = super(pg_json_property, self).expr(model) + return expr.astext.cast(self.cast_type) + +The above subclass can be used with the Postgresql-specific +version of :class:`.postgresql.JSON`:: + + from sqlalchemy import Column, Integer + from sqlalchemy.ext.declarative import declarative_base + from sqlalchemy.dialects.postgresql import JSON + + Base = declarative_base() + + class Person(Base): + __tablename__ = 'person' + + id = Column(Integer, primary_key=True) + data = Column(JSON) + + age = pg_json_property('data', 'age', Integer) + +The ``age`` attribute at the instance level works as before; however +when rendering SQL, Postgresql's ``->>`` operator will be used +for indexed access, instead of the usual index opearator of ``->``:: + + >>> query = session.query(Person).filter(Person.age < 20) + +The above query will render:: + + SELECT person.id, person.data + FROM person + WHERE CAST(person.data ->> %(data_1)s AS INTEGER) < %(param_1)s + +""" +from __future__ import absolute_import + +from ..orm.attributes import flag_modified +from ..ext.hybrid import hybrid_property + + +__all__ = ['index_property'] + + +class index_property(hybrid_property): # noqa + """A property generator. The generated property describes an object + attribute that corresponds to an :class:`~.types.Indexable` + column. + + .. versionadded:: 1.1 + + .. seealso:: + + :mod:`sqlalchemy.ext.indexable` + + """ + + def __init__( + self, attr_name, index, datatype=None, + mutable=True, onebased=True): + """Create a new :class:`.index_property`. + + :param attr_name: + An attribute name of an `Indexable` typed column, or other + attribute that returns an indexable structure. + :param index: + The index to be used for getting and setting this value. This + should be the Python-side index value for integers. + :param datatype: default datatype to use when the field is empty. + By default, this is derived from the type of index used; a + Python list for an integer index, or a Python dictionary for + any other style of index. For a list, the list will be + initialized to a list of None values that is at least + ``index`` elements long. + :param mutable: if False, writes and deletes to the attribute will + be disallowed. + :param onebased: assume the SQL representation of this value is + one-based; that is, the first index in SQL is 1, not zero. + """ + + if mutable: + super(index_property, self).__init__( + self.fget, self.fset, self.fdel, self.expr + ) + else: + super(index_property, self).__init__( + self.fget, None, None, self.expr + ) + self.attr_name = attr_name + self.index = index + is_numeric = isinstance(index, int) + onebased = is_numeric and onebased + + if datatype is not None: + self.datatype = datatype + else: + if is_numeric: + self.datatype = lambda: [None for x in range(index + 1)] + else: + self.datatype = dict + self.onebased = onebased + + def fget(self, instance): + attr_name = self.attr_name + column_value = getattr(instance, attr_name) + if column_value is None: + raise AttributeError(self.attr_name) + try: + value = column_value[self.index] + except KeyError: + raise AttributeError(self.attr_name) + else: + return value + + def fset(self, instance, value): + attr_name = self.attr_name + column_value = getattr(instance, attr_name) + if column_value is None: + column_value = self.datatype() + setattr(instance, attr_name, column_value) + column_value[self.index] = value + setattr(instance, attr_name, column_value) + flag_modified(instance, attr_name) + + def fdel(self, instance): + attr_name = self.attr_name + column_value = getattr(instance, attr_name) + if column_value is None: + raise AttributeError(self.attr_name) + try: + del column_value[self.index] + except KeyError: + raise AttributeError(self.attr_name) + else: + setattr(instance, attr_name, column_value) + flag_modified(instance, attr_name) + + def expr(self, model): + column = getattr(model, self.attr_name) + index = self.index + if self.onebased: + index += 1 + return column[index] diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 81630fe4f..166e61822 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -1809,6 +1809,10 @@ class JSON(Indexable, TypeEngine): comparator_factory = Comparator @property + def python_type(self): + return dict + + @property def should_evaluate_none(self): return not self.none_as_null diff --git a/test/ext/test_indexable.py b/test/ext/test_indexable.py new file mode 100644 index 000000000..c8346e4c3 --- /dev/null +++ b/test/ext/test_indexable.py @@ -0,0 +1,344 @@ +from sqlalchemy.testing import assert_raises +import sqlalchemy as sa +from sqlalchemy import testing +from sqlalchemy import Integer, Text +from sqlalchemy.sql.sqltypes import ARRAY, JSON +from sqlalchemy.testing.schema import Column +from sqlalchemy.orm import Session +from sqlalchemy.testing import fixtures +from sqlalchemy.ext.indexable import index_property +from sqlalchemy.ext.declarative import declarative_base +from sqlalchemy.testing import eq_, ne_, is_, in_, not_in_ +from sqlalchemy import inspect + + +class IndexPropertyTest(fixtures.TestBase): + + def test_array(self): + Base = declarative_base() + + class A(Base): + __tablename__ = 'a' + id = Column('id', Integer, primary_key=True) + array = Column('_array', ARRAY(Integer), + default=[]) + first = index_property('array', 0) + + a = A(array=[1, 2, 3]) + eq_(a.first, 1) + a.first = 100 + eq_(a.first, 100) + eq_(a.array, [100, 2, 3]) + del a.first + eq_(a.first, 2) + + a2 = A(first=5) + eq_(a2.first, 5) + eq_(a2.array, [5]) + + def test_array_longinit(self): + Base = declarative_base() + + class A(Base): + __tablename__ = 'a' + id = Column('id', Integer, primary_key=True) + array = Column('_array', ARRAY(Integer), + default=[]) + first = index_property('array', 0) + + fifth = index_property('array', 4) + + a1 = A(fifth=10) + a2 = A(first=5) + + eq_(a1.array, [None, None, None, None, 10]) + eq_(a2.array, [5]) + + assert_raises(IndexError, setattr, a2, "fifth", 10) + + def test_json(self): + Base = declarative_base() + + class J(Base): + __tablename__ = 'j' + id = Column('id', Integer, primary_key=True) + json = Column('_json', JSON, default={}) + field = index_property('json', 'field') + + j = J(json={'a': 1, 'b': 2}) + assert_raises(AttributeError, lambda: j.field) + j.field = 'test' + eq_(j.field, 'test') + eq_(j.json, {'a': 1, 'b': 2, 'field': 'test'}) + + j2 = J(field='test') + eq_(j2.json, {"field": "test"}) + eq_(j2.field, "test") + + def test_value_is_none_attributeerror(self): + Base = declarative_base() + + class A(Base): + __tablename__ = 'a' + id = Column('id', Integer, primary_key=True) + array = Column('_array', ARRAY(Integer)) + first = index_property('array', 1) + + a = A() + assert_raises(AttributeError, getattr, a, "first") + + assert_raises(AttributeError, delattr, a, "first") + + def test_get_index_error(self): + Base = declarative_base() + + class A(Base): + __tablename__ = 'a' + id = Column('id', Integer, primary_key=True) + array = Column('_array', ARRAY(Integer)) + first = index_property('array', 1) + + a = A(array=[]) + assert_raises(IndexError, lambda: a.first) + + def test_set_immutable(self): + Base = declarative_base() + + class A(Base): + __tablename__ = 'a' + id = Column(Integer, primary_key=True) + array = Column(ARRAY(Integer)) + first = index_property('array', 1, mutable=False) + + a = A() + + def set_(): + a.first = 10 + assert_raises(AttributeError, set_) + + def test_set_mutable_dict(self): + Base = declarative_base() + + class J(Base): + __tablename__ = 'j' + id = Column(Integer, primary_key=True) + json = Column(JSON, default={}) + field = index_property('json', 'field') + + j = J() + + j.field = 10 + + j.json = {} + assert_raises(AttributeError, lambda: j.field) + assert_raises(AttributeError, delattr, j, "field") + + j.field = 10 + eq_(j.field, 10) + + +class IndexPropertyArrayTest(fixtures.DeclarativeMappedTest): + + __requires__ = ('array_type',) + __backend__ = True + + @classmethod + def setup_classes(cls): + Base = cls.DeclarativeBasic + + class Array(fixtures.ComparableEntity, Base): + __tablename__ = "array" + + id = Column(sa.Integer, primary_key=True, + test_needs_autoincrement=True) + array = Column(ARRAY(Integer), default=[]) + array0 = Column(ARRAY(Integer, zero_indexes=True), default=[]) + first = index_property('array', 0) + first0 = index_property('array0', 0, onebased=False) + + def test_query(self): + Array = self.classes.Array + s = Session(testing.db) + + s.add_all([ + Array(), + Array(array=[1, 2, 3], array0=[1, 2, 3]), + Array(array=[4, 5, 6], array0=[4, 5, 6])]) + s.commit() + + a1 = s.query(Array).filter(Array.array == [1, 2, 3]).one() + a2 = s.query(Array).filter(Array.first == 1).one() + eq_(a1.id, a2.id) + a3 = s.query(Array).filter(Array.first == 4).one() + ne_(a1.id, a3.id) + a4 = s.query(Array).filter(Array.first0 == 1).one() + eq_(a1.id, a4.id) + a5 = s.query(Array).filter(Array.first0 == 4).one() + ne_(a1.id, a5.id) + + def test_mutable(self): + Array = self.classes.Array + s = Session(testing.db) + + a = Array(array=[1, 2, 3]) + s.add(a) + s.commit() + + a.first = 42 + eq_(a.first, 42) + s.commit() + eq_(a.first, 42) + + del a.first + eq_(a.first, 2) + s.commit() + eq_(a.first, 2) + + def test_modified(self): + from sqlalchemy import inspect + + Array = self.classes.Array + s = Session(testing.db) + + a = Array(array=[1, 2, 3]) + s.add(a) + s.commit() + + i = inspect(a) + is_(i.modified, False) + in_('array', i.unmodified) + + a.first = 10 + + is_(i.modified, True) + not_in_('array', i.unmodified) + + +class IndexPropertyJsonTest(fixtures.DeclarativeMappedTest): + + __requires__ = ('json_type',) + __backend__ = True + + @classmethod + def setup_classes(cls): + from sqlalchemy.dialects.postgresql import JSON + + Base = cls.DeclarativeBasic + + class json_property(index_property): + def __init__(self, attr_name, index, cast_type): + super(json_property, self).__init__(attr_name, index) + self.cast_type = cast_type + + def expr(self, model): + expr = super(json_property, self).expr(model) + return expr.astext.cast(self.cast_type) + + class Json(fixtures.ComparableEntity, Base): + __tablename__ = "json" + + id = Column(sa.Integer, primary_key=True, + test_needs_autoincrement=True) + json = Column(JSON, default={}) + field = index_property('json', 'field') + json_field = index_property('json', 'field') + int_field = json_property('json', 'field', Integer) + text_field = json_property('json', 'field', Text) + other = index_property('json', 'other') + subfield = json_property('other', 'field', Text) + + def test_query(self): + Json = self.classes.Json + s = Session(testing.db) + + s.add_all([ + Json(), + Json(json={'field': 10}), + Json(json={'field': 20})]) + s.commit() + + a1 = s.query(Json).filter(Json.json['field'].astext.cast(Integer) == 10)\ + .one() + a2 = s.query(Json).filter(Json.field.astext == '10').one() + eq_(a1.id, a2.id) + a3 = s.query(Json).filter(Json.field.astext == '20').one() + ne_(a1.id, a3.id) + + a4 = s.query(Json).filter(Json.json_field.astext == '10').one() + eq_(a2.id, a4.id) + a5 = s.query(Json).filter(Json.int_field == 10).one() + eq_(a2.id, a5.id) + a6 = s.query(Json).filter(Json.text_field == '10').one() + eq_(a2.id, a6.id) + + def test_mutable(self): + Json = self.classes.Json + s = Session(testing.db) + + j = Json(json={}) + s.add(j) + s.commit() + + j.other = 42 + eq_(j.other, 42) + s.commit() + eq_(j.other, 42) + + def test_modified(self): + + Json = self.classes.Json + s = Session(testing.db) + + j = Json(json={}) + s.add(j) + s.commit() + + i = inspect(j) + is_(i.modified, False) + in_('json', i.unmodified) + + j.other = 42 + + is_(i.modified, True) + not_in_('json', i.unmodified) + + def test_cast_type(self): + Json = self.classes.Json + s = Session(testing.db) + + j = Json(json={'field': 10}) + s.add(j) + s.commit() + + jq = s.query(Json).filter(Json.int_field == 10).one() + eq_(j.id, jq.id) + + jq = s.query(Json).filter(Json.text_field == '10').one() + eq_(j.id, jq.id) + + jq = s.query(Json).filter(Json.json_field.astext == '10').one() + eq_(j.id, jq.id) + + jq = s.query(Json).filter(Json.text_field == 'wrong').first() + is_(jq, None) + + j.json = {'field': True} + s.commit() + + jq = s.query(Json).filter(Json.text_field == 'true').one() + eq_(j.id, jq.id) + + def test_multi_dimension(self): + Json = self.classes.Json + + s = Session(testing.db) + + j = Json(json={'other': {'field': 'multi'}}) + s.add(j) + s.commit() + + eq_(j.other, {'field': 'multi'}) + eq_(j.subfield, 'multi') + + jq = s.query(Json).filter(Json.subfield == 'multi').first() + eq_(j.id, jq.id) diff --git a/test/requirements.py b/test/requirements.py index 554e5296f..d31088e16 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -545,6 +545,13 @@ class DefaultRequirements(SuiteRequirements): 'sybase', 'sqlite') @property + def array_type(self): + return only_on([ + lambda config: against(config, "postgresql") and + not against(config, "+pg8000") and not against(config, "+zxjdbc") + ]) + + @property def json_type(self): return only_on([ lambda config: against(config, "mysql >= 5.7") and |