changeset 9a7247a6df7b in trytond:default
details: https://hg.tryton.org/trytond?cmd=changeset;node=9a7247a6df7b
description:
        Allow to search and order on Dict key value

        issue7905
        review62391002
diffstat:

 CHANGELOG                              |    3 +
 doc/ref/models/fields.rst              |    4 +
 doc/ref/models/models.rst              |    3 +-
 doc/topics/domain.rst                  |   20 +-
 trytond/backend/database.py            |   20 +
 trytond/backend/postgresql/database.py |   46 ++-
 trytond/backend/sqlite/database.py     |   18 +-
 trytond/model/fields/dict.py           |   99 +++++-
 trytond/tests/field_dict.py            |   26 +
 trytond/tests/test_field_char.py       |   22 +-
 trytond/tests/test_field_dict.py       |  574 ++++++++++++++++++++++++++++++++-
 11 files changed, 812 insertions(+), 23 deletions(-)

diffs (1049 lines):

diff -r 014c206382b8 -r 9a7247a6df7b CHANGELOG
--- a/CHANGELOG Fri Jan 18 11:01:22 2019 +0100
+++ b/CHANGELOG Fri Jan 18 11:32:47 2019 +0100
@@ -1,3 +1,6 @@
+* Allow to search on key's value of Dict
+* Allow to order Dict by key's value
+* Do not store keys with null value in Dict
 * Add cache timeout for web
 * Allow the bus requests to be redirected to another host
 * Add support for CORS
diff -r 014c206382b8 -r 9a7247a6df7b doc/ref/models/fields.rst
--- a/doc/ref/models/fields.rst Fri Jan 18 11:01:22 2019 +0100
+++ b/doc/ref/models/fields.rst Fri Jan 18 11:32:47 2019 +0100
@@ -927,6 +927,10 @@
     The name of the :class:`DictSchemaMixin` model that stores the definition
     of keys.
 
+.. attribute:: Dict.search_unaccented
+
+    Same as :attr:`Char.search_unaccented` but when searching on key's value.
+
 Instance methods:
 
 .. method:: Dict.translated([name[, type_]])
diff -r 014c206382b8 -r 9a7247a6df7b doc/ref/models/models.rst
--- a/doc/ref/models/models.rst Fri Jan 18 11:01:22 2019 +0100
+++ b/doc/ref/models/models.rst Fri Jan 18 11:32:47 2019 +0100
@@ -437,7 +437,8 @@
 
     In case the field used is a :class:`fields.Many2One`, it is also possible
     to use the dotted notation to sort on a specific field from the target
-    record.
+    record. Or for a :class:`fields.Dict` field, the dotted notation is used to
+    sort on the key's value.
 
     If `count` is set to `True`, then the result is the number of records.
 
diff -r 014c206382b8 -r 9a7247a6df7b doc/topics/domain.rst
--- a/doc/topics/domain.rst     Fri Jan 18 11:01:22 2019 +0100
+++ b/doc/topics/domain.rst     Fri Jan 18 11:32:47 2019 +0100
@@ -22,15 +22,15 @@
     domain = [(<field name>, <operator>, <operand>)]
 
 ``<field name>``
-    Is the name of a :mod:`trytond.model.fields` or a
+    Is the name of a :mod:`~trytond.model.fields` or a
     :ref:`pyson <topics-pyson>` statement, that evaluates to a
     string.
 
-    A field of type :class:`trytond.model.fields.Many2One` or
-    :class:`trytond.model.fields.Many2Many` or
-    :class:`trytond.model.fields.One2Many` or
-    :class:`trytond.model.fields.One2One` or
-    :class:`trytond.model.fields.Reference` can be dereferenced to related
+    A field of type :class:`~trytond.model.fields.Many2One` or
+    :class:`~trytond.model.fields.Many2Many` or
+    :class:`~trytond.model.fields.One2Many` or
+    :class:`~trytond.model.fields.One2One` or
+    :class:`~trytond.model.fields.Reference` can be dereferenced to related
     models. This is illustrated by the following example::
 
         domain = [('country.name', '=', 'Japan')]
@@ -44,6 +44,14 @@
         domain = [('origin.party.name', '=', 'John Doe', 'sale.sale')]
 ..
 
+    A field of type :class:`~trytond.model.fields.Dict` can be searched by key
+    also by using one *dot*. For example::
+
+        domain = [('attributes.color', '=', 'yellow')]
+
+.. warning::
+    Order comparison of `date` and `datetime` types is not supported.
+
 ``operator``
     Is an operator out of `Domain Operators`_ or a
     :ref:`pyson <topics-pyson>` statement, that evaluates to
diff -r 014c206382b8 -r 9a7247a6df7b trytond/backend/database.py
--- a/trytond/backend/database.py       Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/backend/database.py       Fri Jan 18 11:32:47 2019 +0100
@@ -230,3 +230,23 @@
     def sql_format(self, type_, value):
         'Return value correctly casted into type_'
         pass
+
+    def json_get(self, column, key=None):
+        "Return the JSON value of the JSON key"
+        raise NotImplementedError
+
+    def json_key_exists(self, column, key):
+        "Return expression for key exists in JSON column"
+        raise NotImplementedError
+
+    def json_any_keys_exist(self, column, keys):
+        "Return expression for any keys exist in JSON column"
+        raise NotImplementedError
+
+    def json_all_keys_exist(self, column, keys):
+        "Rteurn expression for all keys exist in JSON column"
+        raise NotImplementedError
+
+    def json_contains(self, column, json):
+        "Return expression for column contains JSON"
+        raise NotImplementedError
diff -r 014c206382b8 -r 9a7247a6df7b trytond/backend/postgresql/database.py
--- a/trytond/backend/postgresql/database.py    Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/backend/postgresql/database.py    Fri Jan 18 11:32:47 2019 +0100
@@ -29,8 +29,9 @@
 from psycopg2 import OperationalError as DatabaseOperationalError
 from psycopg2.extras import register_default_json, register_default_jsonb
 
-from sql import Flavor
+from sql import Flavor, Cast
 from sql.functions import Function
+from sql.operators import BinaryOperator
 
 from trytond.backend.database import DatabaseInterface, SQLType
 from trytond.config import config, parse_uri
@@ -78,6 +79,31 @@
     _function = 'pg_try_advisory_xact_lock'
 
 
+class JSONBExtractPath(Function):
+    __slots__ = ()
+    _function = 'jsonb_extract_path'
+
+
+class JSONKeyExists(BinaryOperator):
+    __slots__ = ()
+    _operator = '?'
+
+
+class JSONAnyKeyExist(BinaryOperator):
+    __slots__ = ()
+    _operator = '?|'
+
+
+class JSONAllKeyExist(BinaryOperator):
+    __slots__ = ()
+    _operator = '?&'
+
+
+class JSONContains(BinaryOperator):
+    __slots__ = ()
+    _operator = '@>'
+
+
 class Database(DatabaseInterface):
 
     _lock = RLock()
@@ -476,6 +502,24 @@
     def has_channel(self):
         return True
 
+    def json_get(self, column, key=None):
+        column = Cast(column, 'jsonb')
+        if key:
+            column = JSONBExtractPath(column, key)
+        return column
+
+    def json_key_exists(self, column, key):
+        return JSONKeyExists(Cast(column, 'jsonb'), key)
+
+    def json_any_keys_exist(self, column, keys):
+        return JSONAnyKeyExist(Cast(column, 'jsonb'), keys)
+
+    def json_all_keys_exist(self, column, keys):
+        return JSONAllKeyExist(Cast(column, 'jsonb'), keys)
+
+    def json_contains(self, column, json):
+        return JSONContains(Cast(column, 'jsonb'), Cast(json, 'jsonb'))
+
 
 register_type(UNICODE)
 if PYDATE:
diff -r 014c206382b8 -r 9a7247a6df7b trytond/backend/sqlite/database.py
--- a/trytond/backend/sqlite/database.py        Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/backend/sqlite/database.py        Fri Jan 18 11:32:47 2019 +0100
@@ -18,7 +18,8 @@
     import sqlite3 as sqlite
     from sqlite3 import IntegrityError as DatabaseIntegrityError
     from sqlite3 import OperationalError as DatabaseOperationalError
-from sql import Flavor, Table, Query, Expression, Literal
+from sql import Flavor, Table, Query, Expression, Literal, Null
+from sql.conditionals import NullIf
 from sql.functions import (Function, Extract, Position, Substring,
     Overlay, CharLength, CurrentTimestamp, Trim)
 
@@ -215,6 +216,16 @@
     }
 
 
+class JSONExtract(Function):
+    __slots__ = ()
+    _function = 'JSON_EXTRACT'
+
+
+class JSONQuote(Function):
+    __slots__ = ()
+    _function = 'JSON_QUOTE'
+
+
 class SQLiteCursor(sqlite.Cursor):
 
     def __enter__(self):
@@ -442,6 +453,11 @@
                 value = int(value)
         return value
 
+    def json_get(self, column, key):
+        if key:
+            column = JSONExtract(column, '$.%s' % key)
+        return NullIf(JSONQuote(column), JSONQuote(Null))
+
 sqlite.register_converter('NUMERIC', lambda val: Decimal(val.decode('utf-8')))
 sqlite.register_adapter(Decimal, lambda val: str(val).encode('utf-8'))
 
diff -r 014c206382b8 -r 9a7247a6df7b trytond/model/fields/dict.py
--- a/trytond/model/fields/dict.py      Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/model/fields/dict.py      Fri Jan 18 11:32:47 2019 +0100
@@ -1,12 +1,21 @@
 # This file is part of Tryton.  The COPYRIGHT file at the toplevel of this
 # repository contains the full copyright notices and license terms.
+from functools import partial
 import json
 
-from .field import Field
+from sql import operators, Literal, Select, CombiningQuery, Cast, Null
+
+from trytond import backend
+from trytond.transaction import Transaction
+from .field import Field, SQL_OPERATORS
 from ...protocols.jsonrpc import JSONDecoder, JSONEncoder
 from ...pool import Pool
 from ...tools import grouped_slice
 
+# Use canonical form
+dumps = partial(
+    json.dumps, cls=JSONEncoder, separators=(',', ':'), sort_keys=True)
+
 
 class Dict(Field):
     'Define dict field.'
@@ -21,6 +30,7 @@
             states, select, on_change, on_change_with, depends, context,
             loading)
         self.schema_model = schema_model
+        self.search_unaccented = True
 
     def get(self, ids, model, name, values=None):
         dicts = dict((id, None) for id in ids)
@@ -37,9 +47,8 @@
         if value is None:
             return None
         assert isinstance(value, dict)
-        # Use canonical form
-        return json.dumps(
-            value, cls=JSONEncoder, separators=(',', ':'), sort_keys=True)
+        value = {k: v for k, v in value.items() if v is not None}
+        return dumps(value)
 
     def translated(self, name=None, type_='values'):
         "Return a descriptor for the translated value of the field"
@@ -49,6 +58,88 @@
             raise ValueError('Missing name argument')
         return TranslatedDict(name, type_)
 
+    def _domain_column(self, operator, column, key=None):
+        database = Transaction().database
+        column = database.json_get(
+            super()._domain_column(operator, column), key)
+        if operator.endswith('like'):
+            column = Cast(column, database.sql_type('VARCHAR').base)
+            if self.search_unaccented and operator.endswith('ilike'):
+                column = database.unaccent(column)
+        return column
+
+    def _domain_value(self, operator, value):
+        if backend.name() == 'sqlite' and isinstance(value, bool):
+            # json_extract returns 0 for JSON false and 1 for JSON true
+            value = int(value)
+        if isinstance(value, (Select, CombiningQuery)):
+            return value
+        if operator.endswith('in'):
+            return [dumps(v) for v in value]
+        else:
+            value = dumps(value)
+            if self.search_unaccented and operator.endswith('ilike'):
+                database = Transaction().database
+                value = database.unaccent(value)
+            return value
+
+    def _domain_add_null(self, column, operator, value, expression):
+        expression = super()._domain_add_null(
+            column, operator, value, expression)
+        if value is None and operator.endswith('='):
+            if operator == '=':
+                expression |= (column == Null)
+            else:
+                expression &= (column != Null)
+        return expression
+
+    def convert_domain(self, domain, tables, Model):
+        name, operator, value = domain[:3]
+        if '.' not in name:
+            return super().convert_domain(domain, tables, Model)
+        database = Transaction().database
+        table, _ = tables[None]
+        name, key = name.split('.', 1)
+        Operator = SQL_OPERATORS[operator]
+        column = self.sql_column(table)
+        column = self._domain_column(operator, column, key)
+        expression = Operator(column, self._domain_value(operator, value))
+        if operator in {'=', '!='}:
+            # Try to use custom operators in case there is indexes
+            raw_column = self.sql_column(table)
+            try:
+                if value is None:
+                    expression = database.json_key_exists(
+                        raw_column, key)
+                    if operator == '=':
+                        expression = operators.Not(expression)
+                    return expression
+                else:
+                    expression = database.json_contains(
+                        raw_column, dumps({key: value}))
+                    if operator == '!=':
+                        expression = operators.Not(expression)
+                        expression &= database.json_key_exists(
+                            raw_column, key)
+                    return expression
+            except NotImplementedError:
+                pass
+        if isinstance(expression, operators.In) and not expression.right:
+            expression = Literal(False)
+        elif isinstance(expression, operators.NotIn) and not expression.right:
+            expression = Literal(True)
+        expression = self._domain_add_null(column, operator, value, expression)
+        return expression
+
+    def convert_order(self, name, tables, Model):
+        fname, _, key = name.partition('.')
+        if not key:
+            return super().convert_order(fname, tables, Model)
+        database = Transaction().database
+        table, _ = tables[None]
+        column = self.sql_column(table)
+        return [database.json_get(column, key)]
+
 
 class TranslatedDict(object):
     'A descriptor for translated values of Dict field'
diff -r 014c206382b8 -r 9a7247a6df7b trytond/tests/field_dict.py
--- a/trytond/tests/field_dict.py       Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/tests/field_dict.py       Fri Jan 18 11:32:47 2019 +0100
@@ -40,6 +40,29 @@
     dico = fields.Dict('test.dict.schema', 'Test Dict')
 
 
+class DictNoSchema(ModelSQL):
+    "Dict No Schema"
+    __name__ = 'test.dict_noschema'
+    dico = fields.Dict(None, "Dict")
+
+
+class DictUnaccentedOn(ModelSQL):
+    "Dict Unaccented On"
+    __name__ = 'test.dict_unaccented_on'
+    dico = fields.Dict(None, "Dict")
+
+
+class DictUnaccentedOff(ModelSQL):
+    "Dict Unaccented Off"
+    __name__ = 'test.dict_unaccented_off'
+    dico = fields.Dict(None, "Dict")
+
+    @classmethod
+    def __setup__(cls):
+        super().__setup__()
+        cls.dico.search_unaccented = False
+
+
 def register(module):
     Pool.register(
         DictSchema,
@@ -47,4 +70,7 @@
         DictDefault,
         DictRequired,
         DictJSONB,
+        DictNoSchema,
+        DictUnaccentedOn,
+        DictUnaccentedOff,
         module=module, type_='model')
diff -r 014c206382b8 -r 9a7247a6df7b trytond/tests/test_field_char.py
--- a/trytond/tests/test_field_char.py  Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/tests/test_field_char.py  Fri Jan 18 11:32:47 2019 +0100
@@ -454,20 +454,15 @@
         return Pool().get('test.char_translate')
 
 
[email protected](backend.name() == 'postgresql',
-    "unaccent works only on postgresql")
-class FieldCharUnaccentedTestCase(unittest.TestCase):
-    "Test Field Char with unaccented searches"
-
+class UnaccentedTestCase(unittest.TestCase):
     @classmethod
     def setUpClass(cls):
-        super(FieldCharUnaccentedTestCase, cls).setUpClass()
-        activate_module('tests')
+        super().setUpClass()
         cls._activate_extension()
 
     @classmethod
     def tearDownClass(cls):
-        super(FieldCharUnaccentedTestCase, cls).tearDownClass()
+        super().tearDownClass()
         cls._deactivate_extension()
 
     @classmethod
@@ -493,6 +488,17 @@
         Database = backend.get('Database')
         Database._has_unaccent.clear()
 
+
[email protected](backend.name() == 'postgresql',
+    "unaccent works only on postgresql")
+class FieldCharUnaccentedTestCase(UnaccentedTestCase):
+    "Test Field Char with unaccented searches"
+
+    @classmethod
+    def setUpClass(cls):
+        activate_module('tests')
+        super().setUpClass()
+
     @with_transaction()
     def test_normal_search(self):
         "Test searches without the unaccented feature"
diff -r 014c206382b8 -r 9a7247a6df7b trytond/tests/test_field_dict.py
--- a/trytond/tests/test_field_dict.py  Fri Jan 18 11:01:22 2019 +0100
+++ b/trytond/tests/test_field_dict.py  Fri Jan 18 11:32:47 2019 +0100
@@ -8,6 +8,8 @@
 from trytond.tests.test_tryton import activate_module, with_transaction
 from trytond.transaction import Transaction
 
+from .test_field_char import UnaccentedTestCase
+
 
 class FieldDictTestCase(unittest.TestCase):
     "Test Field Dict"
@@ -129,7 +131,7 @@
 
     @with_transaction()
     @unittest.skipIf(
-        backend.name() != 'postgresql', 'jsonb only suported by postgresql')
+        backend.name() != 'postgresql', 'jsonb only supported by postgresql')
     def test_create_jsonb(self):
         "Test create dict as jsonb"
         connection = Transaction().connection
@@ -177,7 +179,7 @@
 
     @with_transaction()
     @unittest.skipIf(
-        backend.name() != 'postgresql', 'jsonb only suported by postgresql')
+        backend.name() != 'postgresql', 'jsonb only supported by postgresql')
     def test_write_jsonb(self):
         "Test write dict as jsonb"
         connection = Transaction().connection
@@ -196,6 +198,518 @@
         self.assertDictEqual(dict_.dico, {'z': 26})
 
     @with_transaction()
+    def test_search_element_equals(self):
+        "Test search dict element equals"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': 'Foo'},
+                    }])
+
+        dicts_foo = Dict.search([
+                ('dico.a', '=', "Foo"),
+                ])
+        dicts_bar = Dict.search([
+                ('dico.a', '=', "Bar"),
+                ])
+        dicts_foo_b = Dict.search([
+                ('dico.b', '=', "Foo"),
+                ])
+
+        self.assertListEqual(dicts_foo, [dict_])
+        self.assertListEqual(dicts_bar, [])
+        self.assertListEqual(dicts_foo_b, [])
+
+    @with_transaction()
+    def test_search_element_equals_none(self):
+        "Test search dict element equals None"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': None},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', '=', None),
+                ])
+
+        self.assertListEqual(dicts, [dict_])
+
+    @with_transaction()
+    def test_search_non_element_equals_none(self):
+        "Test search dict non element equals None"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Foo"},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.b', '=', None),
+                ])
+
+        self.assertListEqual(dicts, [dict_])
+
+    @with_transaction()
+    def test_search_element_non_equals(self):
+        "Test search dict element non equals"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Foo"},
+                    }])
+
+        dicts_foo = Dict.search([
+                ('dico.a', '!=', "Foo"),
+                ])
+        dicts_bar = Dict.search([
+                ('dico.a', '!=', "Bar"),
+                ])
+        dicts_foo_b = Dict.search([
+                ('dico.b', '!=', "Foo"),
+                ])
+
+        self.assertListEqual(dicts_foo, [])
+        self.assertListEqual(dicts_bar, [dict_])
+        self.assertListEqual(dicts_foo_b, [])
+
+    @with_transaction()
+    def test_search_element_non_equals_none(self):
+        "Test search dict element non equals None"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': None},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', '!=', None),
+                ])
+
+        self.assertListEqual(dicts, [])
+
+    @with_transaction()
+    def test_search_non_element_non_equals_none(self):
+        "Test search dict non element non equals None"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Foo"},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.b', '!=', None),
+                ])
+
+        self.assertListEqual(dicts, [])
+
+    @with_transaction()
+    def test_search_element_equals_true(self):
+        "Test search dict element equals True"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict1, dict2 = Dict.create([{
+                    'dico': {'a': True},
+                    }, {
+                    'dico': {'a': False},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', '=', True),
+                ])
+
+        self.assertListEqual(dicts, [dict1])
+
+    @with_transaction()
+    def test_search_element_equals_false(self):
+        "Test search dict element equals False"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict1, dict2 = Dict.create([{
+                    'dico': {'a': True},
+                    }, {
+                    'dico': {'a': False},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', '=', False),
+                ])
+
+        self.assertListEqual(dicts, [dict2])
+
+    @with_transaction()
+    def test_search_element_non_equals_true(self):
+        "Test search dict element non equals True"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': True},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', '!=', True),
+                ])
+
+        self.assertListEqual(dicts, [])
+
+    @with_transaction()
+    def test_search_element_in(self):
+        "Test search dict element in"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Foo"},
+                    }])
+
+        dicts_foo = Dict.search([
+                ('dico.a', 'in', ["Foo"]),
+                ])
+        dicts_bar = Dict.search([
+                ('dico.a', 'in', ["Bar"]),
+                ])
+        dicts_empty = Dict.search([
+                ('dico.a', 'in', []),
+                ])
+        dicts_foo_b = Dict.search([
+                ('dico.b', 'in', ["Foo"]),
+                ])
+
+        self.assertListEqual(dicts_foo, [dict_])
+        self.assertListEqual(dicts_bar, [])
+        self.assertListEqual(dicts_empty, [])
+        self.assertListEqual(dicts_foo_b, [])
+
+    @with_transaction()
+    def test_search_element_in_none(self):
+        "Test search dict element in [None]"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': None},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', 'in', [None]),
+                ])
+
+        self.assertListEqual(dicts, [dict_])
+
+    @with_transaction()
+    def test_search_element_not_in(self):
+        "Test search dict element not in"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Foo"},
+                    }])
+
+        dicts_foo = Dict.search([
+                ('dico.a', 'not in', ["Foo"]),
+                ])
+        dicts_bar = Dict.search([
+                ('dico.a', 'not in', ["Bar"]),
+                ])
+        dicts_empty = Dict.search([
+                ('dico.a', 'not in', []),
+                ])
+        dicts_foo_b = Dict.search([
+                ('dico.b', 'not in', ["Foo"]),
+                ])
+
+        self.assertListEqual(dicts_foo, [])
+        self.assertListEqual(dicts_bar, [dict_])
+        self.assertListEqual(dicts_empty, [dict_])
+        self.assertListEqual(dicts_foo_b, [])
+
+    @with_transaction()
+    def test_search_element_not_in_none(self):
+        "Test search dict element not in [None]"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': None},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', 'not in', [None]),
+                ])
+
+        self.assertListEqual(dicts, [])
+
+    @with_transaction()
+    def test_search_element_less(self):
+        "Test search dict element less than"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': 1.1},
+                    }])
+
+        dicts_5 = Dict.search([
+                ('dico.a', '<', 5),
+                ])
+        dicts__5 = Dict.search([
+                ('dico.a', '<', -5),
+                ])
+        dicts_1_1 = Dict.search([
+                ('dico.a', '<', 1.1),
+                ])
+
+        self.assertListEqual(dicts_5, [dict_])
+        self.assertListEqual(dicts__5, [])
+        self.assertListEqual(dicts_1_1, [])
+
+    @with_transaction()
+    def test_search_element_less_equals(self):
+        "Test search dict element less than or equals"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': 1.1},
+                    }])
+
+        dicts_5 = Dict.search([
+                ('dico.a', '<=', 5),
+                ])
+        dicts__5 = Dict.search([
+                ('dico.a', '<=', -5),
+                ])
+        dicts_1_1 = Dict.search([
+                ('dico.a', '<=', 1.1),
+                ])
+
+        self.assertListEqual(dicts_5, [dict_])
+        self.assertListEqual(dicts__5, [])
+        self.assertListEqual(dicts_1_1, [dict_])
+
+    @with_transaction()
+    def test_search_element_greater(self):
+        "Test search dict element greater than"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': 1.1},
+                    }])
+
+        dicts_5 = Dict.search([
+                ('dico.a', '>', 5),
+                ])
+        dicts__5 = Dict.search([
+                ('dico.a', '>', -5),
+                ])
+        dicts_1_1 = Dict.search([
+                ('dico.a', '>', 1.1),
+                ])
+
+        self.assertListEqual(dicts_5, [])
+        self.assertListEqual(dicts__5, [dict_])
+        self.assertListEqual(dicts_1_1, [])
+
+    @with_transaction()
+    def test_search_element_greater_equals(self):
+        "Test search dict element greater than or equals"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': 1.1},
+                    }])
+
+        dicts_5 = Dict.search([
+                ('dico.a', '>=', 5),
+                ])
+        dicts__5 = Dict.search([
+                ('dico.a', '>=', -5),
+                ])
+        dicts_1_1 = Dict.search([
+                ('dico.a', '>=', 1.1),
+                ])
+
+        self.assertListEqual(dicts_5, [])
+        self.assertListEqual(dicts__5, [dict_])
+        self.assertListEqual(dicts_1_1, [dict_])
+
+    @with_transaction()
+    def test_search_element_like(self):
+        "Test search dict element like"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Bar"},
+                    }])
+
+        dicts_bar = Dict.search([
+                ('dico.a', 'like', "Bar"),
+                ])
+        dicts_b = Dict.search([
+                ('dico.a', 'like', "B%"),
+                ])
+        dicts_foo = Dict.search([
+                ('dico.a', 'like', "Foo"),
+                ])
+        dicts_f = Dict.search([
+                ('dico.a', 'like', "F%"),
+                ])
+        dicts_b_b = Dict.search([
+                ('dico.b', 'like', "B%"),
+                ])
+
+        self.assertListEqual(dicts_bar, [dict_])
+        self.assertListEqual(dicts_b, [dict_])
+        self.assertListEqual(dicts_foo, [])
+        self.assertListEqual(dicts_f, [])
+        self.assertListEqual(dicts_b_b, [])
+
+    @with_transaction()
+    def test_search_element_ilike(self):
+        "Test search dict element ilike"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Bar"},
+                    }])
+
+        dicts_bar = Dict.search([
+                ('dico.a', 'ilike', "bar"),
+                ])
+        dicts_b = Dict.search([
+                ('dico.a', 'ilike', "b%"),
+                ])
+        dicts_foo = Dict.search([
+                ('dico.a', 'ilike', "foo"),
+                ])
+        dicts_f = Dict.search([
+                ('dico.a', 'ilike', "f%"),
+                ])
+        dicts_b_b = Dict.search([
+                ('dico.b', 'ilike', "b%"),
+                ])
+
+        self.assertListEqual(dicts_bar, [dict_])
+        self.assertListEqual(dicts_b, [dict_])
+        self.assertListEqual(dicts_foo, [])
+        self.assertListEqual(dicts_f, [])
+        self.assertListEqual(dicts_b_b, [])
+
+    @with_transaction()
+    def test_search_element_not_like(self):
+        "Test search dict element not like"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Bar"},
+                    }])
+
+        dicts_bar = Dict.search([
+                ('dico.a', 'not like', "Bar"),
+                ])
+        dicts_b = Dict.search([
+                ('dico.a', 'not like', "B%"),
+                ])
+        dicts_foo = Dict.search([
+                ('dico.a', 'not like', "Foo"),
+                ])
+        dicts_f = Dict.search([
+                ('dico.a', 'not like', "F%"),
+                ])
+        dicts_b_b = Dict.search([
+                ('dico.b', 'not like', "B%"),
+                ])
+
+        self.assertListEqual(dicts_bar, [])
+        self.assertListEqual(dicts_b, [])
+        self.assertListEqual(dicts_foo, [dict_])
+        self.assertListEqual(dicts_f, [dict_])
+        self.assertListEqual(dicts_b_b, [])
+
+    @with_transaction()
+    def test_search_element_not_ilike(self):
+        "Test search dict element not ilike"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+
+        dict_, = Dict.create([{
+                    'dico': {'a': "Bar"},
+                    }])
+
+        dicts_bar = Dict.search([
+                ('dico.a', 'not ilike', "bar"),
+                ])
+        dicts_b = Dict.search([
+                ('dico.a', 'not ilike', "b%"),
+                ])
+        dicts_foo = Dict.search([
+                ('dico.a', 'not ilike', "foo"),
+                ])
+        dicts_f = Dict.search([
+                ('dico.a', 'not ilike', "f%"),
+                ])
+        dicts_b_b = Dict.search([
+                ('dico.b', 'not ilike', "b%"),
+                ])
+
+        self.assertListEqual(dicts_bar, [])
+        self.assertListEqual(dicts_b, [])
+        self.assertListEqual(dicts_foo, [dict_])
+        self.assertListEqual(dicts_f, [dict_])
+        self.assertListEqual(dicts_b_b, [])
+
+    @with_transaction()
+    @unittest.skipIf(
+        backend.name() != 'postgresql', 'jsonb only supported by postgresql')
+    def test_search_element_jsonb(self):
+        "Test search dict element on jsonb"
+        connection = Transaction().connection
+        Database = backend.get('Database')
+        if Database().get_version(connection) < (9, 2):
+            return
+
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+        self.set_jsonb(Dict._table)
+        dict_, = Dict.create([{
+                    'dico': {'a': 'Foo'},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', '=', "Foo"),
+                ])
+
+        self.assertListEqual(dicts, [dict_])
+
+    @with_transaction()
+    def test_search_order_element(self):
+        "Test search order by dict element"
+        pool = Pool()
+        Dict = pool.get('test.dict_noschema')
+        for value in [5, 3, 2]:
+            Dict.create([{'dico': {'a': 5 - value, 'b': value}}])
+
+        records = Dict.search([], order=[('dico.b', 'ASC')])
+        values = [r.dico['b'] for r in records]
+
+        self.assertListEqual(values, [2, 3, 5])
+
+    @with_transaction()
     def test_string(self):
         "Test string dict"
         Dict = Pool().get('test.dict')
@@ -221,5 +735,61 @@
             dict_.dico_string_keys, {'a': 'A', 'type': "Type"})
 
 
[email protected](backend.name() == 'postgresql',
+    "unaccent works only on postgresql")
+class FieldDictUnaccentedTestCase(UnaccentedTestCase):
+    "Test Field Dict with unaccented searched"
+
+    @classmethod
+    def setUpClass(cls):
+        activate_module('tests')
+        super().setUpClass()
+
+    @with_transaction()
+    def test_search_unaccented_off(self):
+        "Test searches without the unaccented feature"
+        pool = Pool()
+        Dict = pool.get('test.dict_unaccented_off')
+        dict_, = Dict.create([{
+                    'dico': {'a': 'Stéphanie'},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', 'ilike', 'Stephanie'),
+                ])
+
+        self.assertListEqual(dicts, [])
+
+    @with_transaction()
+    def test_search_accented(self):
+        "Test searches of accented value"
+        pool = Pool()
+        Dict = pool.get('test.dict_unaccented_on')
+        dict_, = Dict.create([{
+                    'dico': {'a': 'Stéphanie'},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', 'ilike', 'Stephanie'),
+                ])
+
+        self.assertListEqual(dicts, [dict_])
+
+    @with_transaction()
+    def test_search_unaccented(self):
+        "Test searches of unaccented value"
+        pool = Pool()
+        Dict = pool.get('test.dict_unaccented_on')
+        dict_, = Dict.create([{
+                    'dico': {'a': 'Stephanie'},
+                    }])
+
+        dicts = Dict.search([
+                ('dico.a', 'ilike', 'Stéphanie'),
+                ])
+
+        self.assertListEqual(dicts, [dict_])
+
+
 def suite():
     return unittest.TestLoader().loadTestsFromTestCase(FieldDictTestCase)

Reply via email to