changeset 03ec47b65b78 in trytond:default
details: https://hg.tryton.org/trytond?cmd=changeset&node=03ec47b65b78
description:
Add full text and similarity search
issue10023
review320601007
diffstat:
CHANGELOG | 1 +
doc/ref/models/fields.rst | 51 ++++++-
doc/ref/tools/misc.rst | 10 +
trytond/backend/database.py | 25 +++
trytond/backend/postgresql/database.py | 171 +++++++++++++++++++++-
trytond/backend/sqlite/database.py | 1 +
trytond/ir/lang.py | 3 +
trytond/model/fields/__init__.py | 9 +-
trytond/model/fields/char.py | 103 +++++++++++++-
trytond/model/fields/field.py | 34 ++-
trytond/model/fields/text.py | 83 +++++++++++
trytond/tests/field_text.py | 7 +
trytond/tests/test_field_char.py | 83 ++++++----
trytond/tests/test_field_dict.py | 6 +-
trytond/tests/test_field_text.py | 242 +++++++++++++++++++++++++++++---
trytond/tests/test_tools.py | 20 ++-
trytond/tests/test_tryton.py | 36 ++++
trytond/tools/__init__.py | 5 +-
trytond/tools/misc.py | 14 +
19 files changed, 806 insertions(+), 98 deletions(-)
diffs (1302 lines):
diff -r 130d98839af1 -r 03ec47b65b78 CHANGELOG
--- a/CHANGELOG Sun Mar 21 16:09:13 2021 +0100
+++ b/CHANGELOG Sun Mar 21 16:21:05 2021 +0100
@@ -1,3 +1,4 @@
+* Add full text and similarity search and FullText field
* Add URL type to icon
* Use instance method to get next sequence value
* Store the sequence type directly on sequence
diff -r 130d98839af1 -r 03ec47b65b78 doc/ref/models/fields.rst
--- a/doc/ref/models/fields.rst Sun Mar 21 16:09:13 2021 +0100
+++ b/doc/ref/models/fields.rst Sun Mar 21 16:21:05 2021 +0100
@@ -268,7 +268,15 @@
A single line string field.
-:class:`Char` has two extra optional arguments:
+Search by similarity is used for the ``ilike`` operator and
+:meth:`~trytond.tools.is_full_text` value if the backend supports it.
+The similarity threshold is defined for the context key ``<model name>.<field
+name>.search_similarity`` (default value is ``0.3``).
+
+The field is ordered using the similarity with the context value from the key
+``<model name>.<field name>.order`` if it is set.
+
+:class:`Char` has some extra optional arguments:
.. attribute:: Char.size
@@ -301,6 +309,26 @@
The database backend must supports unaccented search.
+.. attribute:: Char.search_full_text
+
+ If this attribute is set to True, ``ilike`` searches with an
+ :meth:`~trytond.tools.is_full_text` value use the full text search of the
+ backend.
+ The default value is False.
+
+ The context can be used to force the full text search behaviour.
+ This is done using the key ``<model name>.<field name>.search_full_text``.
+ If True, the full text search is used no matter what the value.
+ If False, no full text search is peformed.
+
+ The full text ranking value is added to the similarity if the
+ ``search_full_text`` is True.
+
+.. note::
+
+ The database backend must support full text search otherwise ``ilike`` is
+ always used.
+
Text
----
@@ -308,7 +336,7 @@
A multi line string field.
-:class:`Text` has two extra optional arguments:
+:class:`Text` has four extra optional arguments:
.. attribute:: Text.size
@@ -318,6 +346,25 @@
Same as :attr:`Char.translate`
+.. attribute:: Text.search_unaccented
+
+ Same as :attr:`Char.search_unaccented`
+
+.. attribute:: Text.search_full_text
+
+ Same as :attr:`Char.search_full_text`
+ The default value is True.
+
+FullText
+--------
+
+.. class:: FullText(\**options)
+
+An internal field to store a list of parsed strings ordered by weights.
+The field is ordered using the full text ranking with the context value from
+the key `<model name>.<field name>.order`` if it is set.
+
+
Float
-----
diff -r 130d98839af1 -r 03ec47b65b78 doc/ref/tools/misc.rst
--- a/doc/ref/tools/misc.rst Sun Mar 21 16:09:13 2021 +0100
+++ b/doc/ref/tools/misc.rst Sun Mar 21 16:21:05 2021 +0100
@@ -9,6 +9,16 @@
Resolve a dotted name to a global object.
+.. method:: unescape_wildcard(string[, wildcards[, escape]])
+
+Return the string without the wild card escapes.
+
+.. method:: is_full_text(value[, escape])
+
+Determine if the value can be used as full text search.
+This is the case when the value starts and ends with a ``%`` or does not
+contain any wild cards.
+
.. method:: sql_pairing(x, y)
Return an SQL expression that pairs SQL integers x and y.
diff -r 130d98839af1 -r 03ec47b65b78 trytond/backend/database.py
--- a/trytond/backend/database.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/backend/database.py Sun Mar 21 16:21:05 2021 +0100
@@ -181,6 +181,31 @@
"Return the expression to use for unaccentuated columns"
return value
+ def has_similarity(self):
+ "Return if database supports similarity"
+ return False
+
+ def similarity(self, column, value):
+ raise NotImplementedError
+
+ def has_search_full_text(self):
+ "Return if database supports full text search"
+ return False
+
+ def format_full_text(self, *documents, language=None):
+ return '\n'.join(documents)
+
+ def format_full_text_query(self, query, language=None):
+ raise NotImplementedError
+
+ def search_full_text(self, document, query):
+ "Return the clause expression for searching document against query"
+ raise NotImplementedError
+
+ def rank_full_text(self, document, query, normalize=None):
+ "Return the expression that ranks query on document"
+ raise NotImplementedError
+
@classmethod
def has_sequence(cls):
"Return if database supports sequence querying and assignation"
diff -r 130d98839af1 -r 03ec47b65b78 trytond/backend/postgresql/database.py
--- a/trytond/backend/postgresql/database.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/backend/postgresql/database.py Sun Mar 21 16:21:05 2021 +0100
@@ -8,6 +8,7 @@
import json
from datetime import datetime
from decimal import Decimal
+from itertools import chain, repeat
from threading import RLock
try:
@@ -31,9 +32,10 @@
from psycopg2 import ProgrammingError
from psycopg2.extras import register_default_json, register_default_jsonb
-from sql import Flavor, Cast, For
+from sql import Flavor, Cast, For, Table
+from sql.conditionals import Coalesce
from sql.functions import Function
-from sql.operators import BinaryOperator
+from sql.operators import BinaryOperator, Concat
from trytond.backend.database import DatabaseInterface, SQLType
from trytond.config import config, parse_uri
@@ -80,6 +82,55 @@
_function = 'unaccent'
+class Similarity(Function):
+ __slots__ = ()
+ _function = 'similarity'
+
+
+class Match(BinaryOperator):
+ __slots__ = ()
+ _operator = '@@'
+
+
+class ToTsvector(Function):
+ __slots__ = ()
+ _function = 'to_tsvector'
+
+
+class Setweight(Function):
+ __slots__ = ()
+ _function = 'setweight'
+
+
+class TsQuery(Function):
+ __slots__ = ()
+
+
+class ToTsQuery(TsQuery):
+ __slots__ = ()
+ _function = 'to_tsquery'
+
+
+class PlainToTsQuery(TsQuery):
+ __slots__ = ()
+ _function = 'plainto_tsquery'
+
+
+class PhraseToTsQuery(TsQuery):
+ __slots__ = ()
+ _function = 'phraseto_tsquery'
+
+
+class WebsearchToTsQuery(TsQuery):
+ __slots__ = ()
+ _function = 'websearch_to_tsquery'
+
+
+class TsRank(Function):
+ __slots__ = ()
+ _function = 'ts_rank'
+
+
class AdvisoryLock(Function):
_function = 'pg_advisory_xact_lock'
@@ -141,7 +192,8 @@
_current_user = None
_has_returning = None
_has_select_for_skip_locked = None
- _has_unaccent = {}
+ _has_proc = defaultdict(dict)
+ _search_full_text_languages = defaultdict(dict)
flavor = Flavor(ilike=True)
TYPES_MAPPING = {
@@ -151,6 +203,7 @@
'BLOB': SQLType('BYTEA', 'BYTEA'),
'DATETIME': SQLType('TIMESTAMP', 'TIMESTAMP(0)'),
'TIMESTAMP': SQLType('TIMESTAMP', 'TIMESTAMP(6)'),
+ 'FULLTEXT': SQLType('TSVECTOR', 'TSVECTOR'),
}
def __new__(cls, name=_default_name):
@@ -468,21 +521,117 @@
def has_sequence(cls):
return True
- def has_unaccent(self):
- if self.name in self._has_unaccent:
- return self._has_unaccent[self.name]
+ def has_proc(self, name):
+ if name in self._has_proc[self.name]:
+ return self._has_proc[self.name][name]
connection = self.get_connection()
- unaccent = False
+ result = False
try:
cursor = connection.cursor()
cursor.execute(
"SELECT 1 FROM pg_proc WHERE proname=%s",
- (Unaccent._function,))
- unaccent = bool(cursor.rowcount)
+ (name,))
+ result = bool(cursor.rowcount)
finally:
self.put_connection(connection)
- self._has_unaccent[self.name] = unaccent
- return unaccent
+ self._has_proc[self.name][name] = result
+ return result
+
+ def has_unaccent(self):
+ return self.has_proc(Unaccent._function)
+
+ def has_similarity(self):
+ return self.has_proc(Similarity._function)
+
+ def similarity(self, column, value):
+ return Similarity(column, value)
+
+ def has_search_full_text(self):
+ return True
+
+ def _search_full_text_language(self, language):
+ languages = self._search_full_text_languages[self.name]
+ if language not in languages:
+ lang = Table('ir_lang')
+ connection = self.get_connection()
+ try:
+ cursor = connection.cursor()
+ cursor.execute(*lang.select(
+ Coalesce(lang.pg_text_search, 'simple'),
+ where=lang.code == language,
+ limit=1))
+ config_name, = cursor.fetchone()
+ finally:
+ self.put_connection(connection)
+ languages[language] = config_name
+ else:
+ config_name = languages[language]
+ return config_name
+
+ def format_full_text(self, *documents, language=None):
+ size = max(len(documents) // 4, 1)
+ if len(documents) > 1:
+ weights = chain(
+ ['A'] * size, ['B'] * size, ['C'] * size, repeat('D'))
+ else:
+ weights = [None]
+ expression = None
+ if language:
+ config_name = self._search_full_text_language(language)
+ else:
+ config_name = None
+ for document, weight in zip(documents, weights):
+ if not document:
+ continue
+ if config_name:
+ ts_vector = ToTsvector(config_name, document)
+ else:
+ ts_vector = ToTsvector('simple', document)
+ if weight:
+ ts_vector = Setweight(ts_vector, weight)
+ if expression is None:
+ expression = ts_vector
+ else:
+ expression = Concat(expression, ts_vector)
+ return expression
+
+ def format_full_text_query(self, query, language=None):
+ connection = self.get_connection()
+ try:
+ version = self.get_version(connection)
+ finally:
+ self.put_connection(connection)
+ if version >= (11, 0):
+ ToTsQuery = WebsearchToTsQuery
+ else:
+ ToTsQuery = PlainToTsQuery
+ if language:
+ config_name = self._search_full_text_language(language)
+ if not isinstance(query, TsQuery):
+ query = ToTsQuery(config_name, query)
+ else:
+ if not isinstance(query, TsQuery):
+ query = ToTsQuery(query)
+ return query
+
+ def search_full_text(self, document, query):
+ return Match(document, query)
+
+ def rank_full_text(self, document, query, normalize=None):
+ # TODO: weights and cover density
+ norm_int = 0
+ if normalize:
+ values = {
+ 'document log': 1,
+ 'document': 2,
+ 'mean': 4,
+ 'word': 8,
+ 'word log': 16,
+ 'rank': 32,
+ }
+ for norm in normalize:
+ norm_int |= values.get(norm, 0)
+ return TsRank(document, query, norm_int)
def sql_type(self, type_):
if type_ in self.TYPES_MAPPING:
diff -r 130d98839af1 -r 03ec47b65b78 trytond/backend/sqlite/database.py
--- a/trytond/backend/sqlite/database.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/backend/sqlite/database.py Sun Mar 21 16:21:05 2021 +0100
@@ -321,6 +321,7 @@
'DATETIME': SQLType('TIMESTAMP', 'TIMESTAMP'),
'BIGINT': SQLType('INTEGER', 'INTEGER'),
'BOOL': SQLType('BOOLEAN', 'BOOLEAN'),
+ 'FULLTEXT': SQLType('TEXT', 'TEXT'),
}
def __new__(cls, name=_default_name):
diff -r 130d98839af1 -r 03ec47b65b78 trytond/ir/lang.py
--- a/trytond/ir/lang.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/ir/lang.py Sun Mar 21 16:21:05 2021 +0100
@@ -73,6 +73,9 @@
p_sep_by_space = fields.Boolean('Positive Separate by Space')
n_sep_by_space = fields.Boolean('Negative Separate by Space')
+ pg_text_search = fields.Char(
+ "PostgreSQL Text Search Configuration", readonly=True)
+
_lang_cache = Cache('ir.lang')
_code_cache = Cache('ir.lang.code', context=False)
diff -r 130d98839af1 -r 03ec47b65b78 trytond/model/fields/__init__.py
--- a/trytond/model/fields/__init__.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/model/fields/__init__.py Sun Mar 21 16:21:05 2021 +0100
@@ -7,7 +7,7 @@
from .boolean import Boolean
from .integer import Integer, BigInteger
from .char import Char
-from .text import Text
+from .text import Text, FullText
from .float import Float
from .numeric import Numeric
from .date import Date, Timestamp, DateTime, Time, TimeDelta
@@ -25,6 +25,7 @@
__all__ = [
depends, with_inactive_records, SQL_OPERATORS, on_change_result,
get_eval_fields, states_validate, domain_validate, context_validate, Field,
- Boolean, Integer, BigInteger, Char, Text, Float, Numeric, Date, Timestamp,
- DateTime, Time, TimeDelta, Binary, Selection, Reference, Many2One,
- One2Many, Many2Many, Function, MultiValue, One2One, Dict, MultiSelection]
+ Boolean, Integer, BigInteger, Char, Text, FullText, Float, Numeric, Date,
+ Timestamp, DateTime, Time, TimeDelta, Binary, Selection, Reference,
+ Many2One, One2Many, Many2Many, Function, MultiValue, One2One, Dict,
+ MultiSelection]
diff -r 130d98839af1 -r 03ec47b65b78 trytond/model/fields/char.py
--- a/trytond/model/fields/char.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/model/fields/char.py Sun Mar 21 16:21:05 2021 +0100
@@ -2,7 +2,10 @@
# this repository contains the full copyright notices and license terms.
import warnings
+from sql.operators import Not
+
from trytond.rpc import RPC
+from trytond.tools import unescape_wildcard, is_full_text
from trytond.transaction import Transaction
from .field import Field, FieldTranslate, size_validate
@@ -14,6 +17,8 @@
_type = 'char'
_py_type = str
forbidden_chars = '\t\n\r\x0b\x0c'
+ search_unaccented = True
+ search_full_text = False
def __init__(self, string='', size=None, help='', required=False,
readonly=False, domain=None, states=None, translate=False,
@@ -37,7 +42,6 @@
self.translate = translate
self.__size = None
self.size = size
- self.search_unaccented = True
__init__.__doc__ += Field.__init__.__doc__
def _get_size(self):
@@ -75,6 +79,103 @@
value = database.unaccent(value)
return value
+ def convert_domain(self, domain, tables, Model):
+ transaction = Transaction()
+ context = transaction.context
+ database = transaction.database
+ expression = super().convert_domain(domain, tables, Model)
+ name, operator, value = domain
+ if operator.endswith('ilike'):
+ table, _ = tables[None]
+ if self.translate:
+ language = transaction.language
+ model, join, column = self._get_translation_column(
+ Model, name)
+ else:
+ language = None
+ column = self.sql_column(table)
+ column = self._domain_column(operator, column)
+
+ if database.has_similarity() and is_full_text(value):
+ threshold = context.get(
+ '%s.%s.search_similarity' % (Model.__name__, name),
+ context.get('search_similarity', 0.3))
+ sim_value = unescape_wildcard(value)
+ sim_value = self._domain_value(operator, sim_value)
+ expression = (
+ database.similarity(column, sim_value) >= threshold)
+ if operator.startswith('not'):
+ expression = Not(expression)
+
+ key = '%s.%s.search_full_text' % (Model.__name__, name)
+ if ((self.search_full_text or context.get(key))
+ and context.get(key, True)
+ and database.has_search_full_text()):
+ if context.get(key) or is_full_text(value):
+ fts_column = database.format_full_text(
+ column, language=language)
+ fts_value = value
+ if key not in context:
+ fts_value = unescape_wildcard(fts_value)
+ fts_value = self._domain_value(operator, fts_value)
+ fts_value = database.format_full_text_query(
+ fts_value, language=language)
+ fts = database.search_full_text(fts_column, fts_value)
+ if operator.startswith('not'):
+ fts = Not(fts)
+ if self.translate:
+ fts = table.id.in_(
+ join.select(model.id, where=fts))
+ if database.has_similarity() and is_full_text(value):
+ if operator.startswith('not'):
+ expression |= fts
+ else:
+ expression &= fts
+ else:
+ expression = fts
+ return expression
+
+ def convert_order(self, name, tables, Model):
+ method = getattr(Model, 'order_%s' % name, None)
+ if method:
+ return method(tables)
+ transaction = Transaction()
+ context = transaction.context
+ database = transaction.database
+ key = '%s.%s.order' % (Model.__name__, name)
+ value = context.get(key)
+ order = super().convert_order(name, tables, Model)
+ if value:
+ expression = None
+ table, _ = tables[None]
+ if self.translate:
+ language = transaction.language
+ column = self._get_translation_order(tables, Model, name)
+ else:
+ language = None
+ column = self.sql_column(table)
+ column = self._domain_column('ilike', column)
+ if database.has_similarity():
+ sim_value = unescape_wildcard(value)
+ sim_value = self._domain_value('ilike', sim_value)
+ expression = database.similarity(column, sim_value)
+ key = '%s.%s.search_full_text' % (Model.__name__, name)
+ if ((self.search_full_text or context.get(key))
+ and database.has_search_full_text()):
+ column = database.format_full_text(column, language=language)
+ value = self._domain_value('ilike', value)
+ value = database.format_full_text_query(
+ value, language=language)
+ rank = database.rank_full_text(
+ column, value, normalize=['rank'])
+ if expression:
+ expression += rank
+ else:
+ expression = rank
+ if expression:
+ order = [expression]
+ return order
+
def definition(self, model, language):
definition = super().definition(model, language)
definition['autocomplete'] = list(self.autocomplete)
diff -r 130d98839af1 -r 03ec47b65b78 trytond/model/fields/field.py
--- a/trytond/model/fields/field.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/model/fields/field.py Sun Mar 21 16:21:05 2021 +0100
@@ -544,18 +544,14 @@
return query, from_.join(query, 'LEFT',
condition=(query.res_id == res_id))
- def convert_domain(self, domain, tables, Model):
+ def _get_translation_column(self, Model, name):
from trytond.ir.lang import get_parent_language
pool = Pool()
Translation = pool.get('ir.translation')
IrModel = pool.get('ir.model')
- if not self.translate:
- return super(FieldTranslate, self).convert_domain(
- domain, tables, Model)
table = join = Model.__table__()
model = IrModel.__table__()
- name, operator, value = domain
language = Transaction().language
column = None
while language:
@@ -564,7 +560,16 @@
Model, name, translation, model, table, join, language)
column = Coalesce(NullIf(column, ''), translation.value)
language = get_parent_language(language)
- column = Coalesce(NullIf(column, ''), self.sql_column(table))
+ return table, join, column
+
+ def convert_domain(self, domain, tables, Model):
+ if not self.translate:
+ return super(FieldTranslate, self).convert_domain(
+ domain, tables, Model)
+ table, _ = tables[None]
+ name, operator, value = domain
+ model, join, column = self._get_translation_column(Model, name)
+ column = Coalesce(NullIf(column, ''), self.sql_column(model))
column = self._domain_column(operator, column)
Operator = SQL_OPERATORS[operator]
assert name == self.name
@@ -574,20 +579,14 @@
elif isinstance(where, operators.NotIn) and not where.right:
where = Literal(True)
where = self._domain_add_null(column, operator, value, where)
- return tables[None][0].id.in_(join.select(table.id, where=where))
+ return table.id.in_(join.select(model.id, where=where))
- def convert_order(self, name, tables, Model):
+ def _get_translation_order(self, tables, Model, name):
from trytond.ir.lang import get_parent_language
pool = Pool()
Translation = pool.get('ir.translation')
IrModel = pool.get('ir.model')
- if not self.translate:
- return super(FieldTranslate, self).convert_order(name, tables,
- Model)
- assert name == self.name
-
table, _ = tables[None]
-
join = table
language = Transaction().language
column = None
@@ -616,7 +615,14 @@
translation, _ = tables[key]['translation'][None]
column = Coalesce(NullIf(column, ''), translation.value)
language = get_parent_language(language)
+ return column
+ def convert_order(self, name, tables, Model):
+ if not self.translate:
+ return super().convert_order(name, tables, Model)
+ assert name == self.name
+ table, _ = tables[None]
+ column = self._get_translation_order(tables, Model, name)
return [Coalesce(NullIf(column, ''), self.sql_column(table))]
def definition(self, model, language):
diff -r 130d98839af1 -r 03ec47b65b78 trytond/model/fields/text.py
--- a/trytond/model/fields/text.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/model/fields/text.py Sun Mar 21 16:21:05 2021 +0100
@@ -1,5 +1,11 @@
# This file is part of Tryton. The COPYRIGHT file at the top level of
# this repository contains the full copyright notices and license terms.
+from sql import Query, Expression, Null, Literal
+from sql.operators import ILike, Not, In, NotIn
+
+from trytond.transaction import Transaction
+
+from .field import Field, SQL_OPERATORS
from .char import Char
@@ -10,3 +16,80 @@
_type = 'text'
_sql_type = 'TEXT'
forbidden_chars = ''
+ search_full_text = True
+
+
+class FullText(Field):
+ '''
+ Define a full text field.
+ '''
+ _type = 'full_text'
+ _sql_type = 'FULLTEXT'
+ _py_type = str
+
+ def sql_format(self, value):
+ if isinstance(value, (Query, Expression)):
+ return value
+ if not value:
+ return Null
+ if isinstance(value, (int, float)):
+ return value
+ transaction = Transaction()
+ database = transaction.database
+ if isinstance(value, str):
+ value = [value]
+ return database.format_full_text(*value, language=transaction.language)
+
+ def _rank_column(self, column, name, Model):
+ transaction = Transaction()
+ context = transaction.context
+ database = transaction.database
+ key = '%s.%s.order' % (Model.__name__, name)
+ value = context.get(key)
+ if value and database.has_search_full_text():
+ value = database.format_full_text_query(
+ value, language=transaction.language)
+ column = database.rank_full_text(column, value, normalize=['rank'])
+ else:
+ column = Null
+ return column
+
+ def convert_domain(self, domain, tables, Model):
+ transaction = Transaction()
+ database = transaction.database
+ table, _ = tables[None]
+ name, operator, value = domain
+ assert name == self.name
+ column = self.sql_column(table)
+ column = self._domain_column(operator, column)
+ if operator.endswith('like'):
+ if database.has_search_full_text():
+ value = database.format_full_text_query(
+ value, language=transaction.language)
+ expression = database.search_full_text(column, value)
+ else:
+ expression = Literal(True)
+ for v in value.split():
+ expression &= ILike(column, '%' + v + '%')
+ if operator.startswith('not'):
+ expression = Not(expression)
+ else:
+ Operator = SQL_OPERATORS[operator]
+ column = self._rank_column(column, name, Model)
+ expression = Operator(column, self._domain_value(operator, value))
+ if isinstance(expression, In) and not expression.right:
+ expression = Literal(False)
+ elif isinstance(expression, 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):
+ method = getattr(Model, 'order_%s' % name, None)
+ if method:
+ return method(tables)
+ table, _ = tables[None]
+ column = self.sql_column(table)
+ column = self._domain_column('ilike', column)
+ return [self._rank_column(column, name, Model)]
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tests/field_text.py
--- a/trytond/tests/field_text.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tests/field_text.py Sun Mar 21 16:21:05 2021 +0100
@@ -44,6 +44,12 @@
required=False, translate=True)
+class FullText(ModelSQL):
+ "FullText"
+ __name__ = 'test.text_full'
+ full_text = fields.FullText("Full Text")
+
+
def register(module):
Pool.register(
Text,
@@ -51,4 +57,5 @@
TextRequired,
TextSize,
TextTranslate,
+ FullText,
module=module, type_='model')
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tests/test_field_char.py
--- a/trytond/tests/test_field_char.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tests/test_field_char.py Sun Mar 21 16:21:05 2021 +0100
@@ -10,6 +10,7 @@
RequiredValidationError, ForbiddenCharValidationError)
from trytond.pool import Pool
from trytond.tests.test_tryton import activate_module, with_transaction
+from trytond.tests.test_tryton import ExtensionTestCase
from trytond.transaction import Transaction
@@ -494,44 +495,11 @@
self.assertEqual(char.char, "bar")
-class UnaccentedTestCase(unittest.TestCase):
- @classmethod
- def setUpClass(cls):
- super().setUpClass()
- cls._activate_extension()
-
- @classmethod
- def tearDownClass(cls):
- super().tearDownClass()
- cls._deactivate_extension()
-
- @classmethod
- @with_transaction()
- def _activate_extension(cls):
- connection = Transaction().connection
- cursor = connection.cursor()
- cursor.execute('CREATE EXTENSION "unaccent"')
- connection.commit()
- cls._clear_unaccent_cache()
-
- @classmethod
- @with_transaction()
- def _deactivate_extension(cls):
- connection = Transaction().connection
- cursor = connection.cursor()
- cursor.execute('DROP EXTENSION "unaccent"')
- connection.commit()
- cls._clear_unaccent_cache()
-
- @classmethod
- def _clear_unaccent_cache(cls):
- backend.Database._has_unaccent.clear()
-
-
@unittest.skipUnless(backend.name == 'postgresql',
"unaccent works only on postgresql")
-class FieldCharUnaccentedTestCase(UnaccentedTestCase):
+class FieldCharUnaccentedTestCase(ExtensionTestCase):
"Test Field Char with unaccented searches"
+ extension = 'unaccent'
@classmethod
def setUpClass(cls):
@@ -607,10 +575,55 @@
self.assertListEqual(chars_ecole, [char])
[email protected](backend.name == 'postgresql',
+ "similarity works only on postgresql")
+class FieldCharSimilarityTestCase(ExtensionTestCase):
+ "Test Field Char with similarity searches"
+ extension = 'pg_trgm'
+
+ @classmethod
+ def setUpClass(cls):
+ activate_module('tests')
+ super().setUpClass()
+
+ @with_transaction()
+ def test_search(self):
+ "Test search"
+ pool = Pool()
+ Model = pool.get('test.char')
+ record1, record2 = Model.create([{
+ 'char': "word",
+ }, {
+ 'char': "Foo",
+ }])
+
+ self.assertListEqual(Model.search([
+ ('char', 'ilike', 'two words'),
+ ]), [record1])
+
+ @with_transaction()
+ def test_order(self):
+ "Test order"
+ pool = Pool()
+ Model = pool.get('test.char')
+ record1, record2 = Model.create([{
+ 'char': "word",
+ }, {
+ 'char': "Foo",
+ }])
+ with Transaction().set_context({
+ '%s.char.order' % Model.__name__: 'foo bar',
+ }):
+ self.assertListEqual(Model.search([
+ ], order=[('char', 'DESC')]),
+ [record2, record1])
+
+
def suite():
suite_ = unittest.TestSuite()
loader = unittest.TestLoader()
suite_.addTests(loader.loadTestsFromTestCase(FieldCharTestCase))
suite_.addTests(loader.loadTestsFromTestCase(FieldCharTranslatedTestCase))
suite_.addTests(loader.loadTestsFromTestCase(FieldCharUnaccentedTestCase))
+ suite_.addTests(loader.loadTestsFromTestCase(FieldCharSimilarityTestCase))
return suite_
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tests/test_field_dict.py
--- a/trytond/tests/test_field_dict.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tests/test_field_dict.py Sun Mar 21 16:21:05 2021 +0100
@@ -7,10 +7,9 @@
from trytond.model.exceptions import RequiredValidationError
from trytond.pool import Pool
from trytond.tests.test_tryton import activate_module, with_transaction
+from trytond.tests.test_tryton import ExtensionTestCase
from trytond.transaction import Transaction
-from .test_field_char import UnaccentedTestCase
-
class FieldDictTestCase(unittest.TestCase):
"Test Field Dict"
@@ -890,8 +889,9 @@
@unittest.skipUnless(backend.name == 'postgresql',
"unaccent works only on postgresql")
-class FieldDictUnaccentedTestCase(UnaccentedTestCase):
+class FieldDictUnaccentedTestCase(ExtensionTestCase):
"Test Field Dict with unaccented searched"
+ extension = 'unaccent'
@classmethod
def setUpClass(cls):
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tests/test_field_text.py
--- a/trytond/tests/test_field_text.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tests/test_field_text.py Sun Mar 21 16:21:05 2021 +0100
@@ -3,10 +3,12 @@
# this repository contains the full copyright notices and license terms.
import unittest
+from trytond import backend
from trytond.model.exceptions import (
RequiredValidationError, SizeValidationError)
from trytond.pool import Pool
from trytond.tests.test_tryton import activate_module, with_transaction
+from trytond.transaction import Transaction
class CommonTestCaseMixin:
@@ -244,25 +246,73 @@
text, = Text.create([{
'text': "Bar",
}])
-
- texts_bar = Text.search([
- ('text', 'ilike', "bar"),
- ])
- texts_b = Text.search([
- ('text', 'ilike', "b%"),
- ])
- texts_foo = Text.search([
- ('text', 'ilike', "foo"),
- ])
- texts_f = Text.search([
- ('text', 'ilike', "f%"),
- ])
+ with Transaction().set_context({
+ '%s.text.search_full_text' % Text.__name__: False,
+ }):
+ texts_bar = Text.search([
+ ('text', 'ilike', "bar"),
+ ])
+ texts_b = Text.search([
+ ('text', 'ilike', "b%"),
+ ])
+ texts_foo = Text.search([
+ ('text', 'ilike', "foo"),
+ ])
+ texts_f = Text.search([
+ ('text', 'ilike', "f%"),
+ ])
self.assertListEqual(texts_bar, [text])
self.assertListEqual(texts_b, [text])
self.assertListEqual(texts_foo, [])
self.assertListEqual(texts_f, [])
+ @unittest.skipIf(backend.name == 'sqlite',
+ "SQLite does not have full text search")
+ @with_transaction()
+ def test_search_full_text(self):
+ "Test search full text"
+ Text = self.Text()
+ fat_cat, = Text.create([{
+ 'text': "a fat cat sat on a mat and ate a fat rat",
+ }])
+ sad_cat, = Text.create([{
+ 'text': "a sad cat sat",
+ }])
+
+ for value, result in [
+ ("Fat rat", [fat_cat]),
+ ('"sad cat" or "fat rat"', [fat_cat, sad_cat]),
+ ]:
+ with self.subTest(value=value):
+ self.assertListEqual(Text.search([
+ ('text', 'ilike', value),
+ ]), result)
+
+ @unittest.skipIf(backend.name == 'sqlite',
+ "SQLite does not have full text search")
+ @with_transaction()
+ def test_search_full_text_order(self):
+ "Test order search full text"
+ Text = self.Text()
+ sad_cat, = Text.create([{
+ 'text': "a sad cat sat",
+ }])
+ dog, = Text.create([{
+ 'text': "a dog",
+ }])
+ fat_cat, = Text.create([{
+ 'text': "a fat cat sat on a mat and ate a fat rat",
+ }])
+
+ with Transaction().set_context({
+ '%s.text.order' % Text.__name__: (
+ '"sad cat" or "fat rat"'),
+ }):
+ self.assertListEqual(
+ Text.search([], order=[('text', 'DESC')]),
+ [fat_cat, sad_cat, dog])
+
@with_transaction()
def test_search_not_like(self):
"Test search text not like"
@@ -297,18 +347,21 @@
'text': "Bar",
}])
- texts_bar = Text.search([
- ('text', 'not ilike', "bar"),
- ])
- texts_b = Text.search([
- ('text', 'not ilike', "b%"),
- ])
- texts_foo = Text.search([
- ('text', 'not ilike', "foo"),
- ])
- texts_f = Text.search([
- ('text', 'not ilike', "f%"),
- ])
+ with Transaction().set_context({
+ '%s.text.search_full_text' % Text.__name__: False,
+ }):
+ texts_bar = Text.search([
+ ('text', 'not ilike', "bar"),
+ ])
+ texts_b = Text.search([
+ ('text', 'not ilike', "b%"),
+ ])
+ texts_foo = Text.search([
+ ('text', 'not ilike', "foo"),
+ ])
+ texts_f = Text.search([
+ ('text', 'not ilike', "f%"),
+ ])
self.assertListEqual(texts_bar, [])
self.assertListEqual(texts_b, [])
@@ -458,9 +511,148 @@
return Pool().get('test.text_translate')
+class FieldFullTextTestCase(unittest.TestCase):
+ "Test Field FullText"
+
+ @classmethod
+ def setUpClass(cls):
+ activate_module('tests')
+
+ @with_transaction()
+ def test_create(self):
+ "Test create full text"
+ pool = Pool()
+ Model = pool.get('test.text_full')
+
+ record, = Model.create([{
+ 'full_text': "a fat cat sat on a mat and ate a fat rat",
+ }])
+
+ self.assertTrue(record.full_text)
+
+ @with_transaction()
+ def test_create_list(self):
+ "Test create full text with a list"
+ pool = Pool()
+ Model = pool.get('test.text_full')
+
+ record, = Model.create([{
+ 'full_text': [
+ "The Fat Cats",
+ "a fat cat sat on a mat and ate a fat rat",
+ ],
+ }])
+
+ self.assertTrue(record.full_text)
+
+ @with_transaction()
+ def test_search(self):
+ "Test search full text"
+ pool = Pool()
+ Model = pool.get('test.text_full')
+
+ fat_cat, sad_cat = Model.create([{
+ 'full_text': "a fat cat sat on a mat and ate a fat rat",
+ }, {
+ 'full_text': "a sad cat sat",
+ }])
+
+ for clause, result, neg_result in [
+ ('fat', [fat_cat], [sad_cat]),
+ ('cat', [fat_cat, sad_cat], []),
+ ]:
+ with self.subTest(clause=clause):
+ self.assertListEqual(Model.search([
+ ('full_text', 'ilike', clause),
+ ]), result)
+ self.assertListEqual(Model.search([
+ ('full_text', 'not ilike', clause),
+ ]), neg_result)
+
+ @unittest.skipIf(backend.name == 'sqlite',
+ "SQLite does not have full text search")
+ @with_transaction()
+ def test_search_full_text(self):
+ "Test search full text"
+ pool = Pool()
+ Model = pool.get('test.text_full')
+
+ fat_cat, sad_cat = Model.create([{
+ 'full_text': "a fat cat sat on a mat and ate a fat rat",
+ }, {
+ 'full_text': "a sad cat sat",
+ }])
+
+ for clause, result in [
+ ("Fat rat", [fat_cat]),
+ ('"sad cat" or "fat rat"', [fat_cat, sad_cat]),
+ ]:
+ with self.subTest(clause=clause):
+ self.assertListEqual(Model.search([
+ ('full_text', 'ilike', clause),
+ ]), result)
+
+ @unittest.skipIf(backend.name == 'sqlite',
+ "SQLite does not have full text search")
+ @with_transaction()
+ def test_search_full_text_filter(self):
+ "Test search full text"
+ pool = Pool()
+ Model = pool.get('test.text_full')
+
+ fat_cat, sad_cat = Model.create([{
+ 'full_text': "a fat cat sat on a mat and ate a fat rat",
+ }, {
+ 'full_text': "a sad cat sat",
+ }])
+
+ for clause, result in [
+ ("Fat rat", [fat_cat]),
+ ('"sad cat" or "fat rat"', [fat_cat, sad_cat]),
+ ]:
+ with self.subTest(clause=clause):
+ with Transaction().set_context({
+ '%s.full_text.order' % Model.__name__: clause,
+ }):
+ self.assertListEqual(Model.search([
+ ('full_text', '>', 0.01),
+ ]), result)
+
+ @unittest.skipIf(backend.name == 'sqlite',
+ "SQLite does not have full text search")
+ @with_transaction()
+ def test_search_full_text_order(self):
+ "Test order full text"
+ pool = Pool()
+ Model = pool.get('test.text_full')
+
+ sad_cat, fat_cat, dog = Model.create([{
+ 'full_text': [
+ None,
+ "a fat cat sat on a mat and ate a fat rat",
+ ],
+ }, {
+ 'full_text': [
+ "The Fat Cats",
+ "a fat cat sat on a mat and ate a fat rat",
+ ]
+ }, {
+ 'full_text': "a dog",
+ }])
+
+ with Transaction().set_context({
+ '%s.full_text.order' % Model.__name__: (
+ '"sad cat" or "fat rat"'),
+ }):
+ self.assertListEqual(
+ Model.search([], order=[('full_text', 'DESC')]),
+ [fat_cat, sad_cat, dog])
+
+
def suite():
suite_ = unittest.TestSuite()
loader = unittest.TestLoader()
suite_.addTests(loader.loadTestsFromTestCase(FieldTextTestCase))
suite_.addTests(loader.loadTestsFromTestCase(FieldTextTranslatedTestCase))
+ suite_.addTests(loader.loadTestsFromTestCase(FieldFullTextTestCase))
return suite_
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tests/test_tools.py
--- a/trytond/tests/test_tools.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tests/test_tools.py Sun Mar 21 16:21:05 2021 +0100
@@ -13,7 +13,7 @@
from trytond.tools import (
reduce_ids, reduce_domain, decimal_, is_instance_method, file_open,
strip_wildcard, lstrip_wildcard, rstrip_wildcard, slugify, sortable_values,
- escape_wildcard, firstline)
+ escape_wildcard, unescape_wildcard, is_full_text, firstline)
from trytond.tools.string_ import StringPartitioned, LazyString
from trytond.tools.domain_inversion import (
domain_inversion, parse, simplify, merge, concat, unique_value,
@@ -201,6 +201,24 @@
escape_wildcard('foo%bar_baz\\'),
'foo\\%bar\\_baz\\\\')
+ def test_unescape_wildcard(self):
+ "Test unescape_wildcard"
+ self.assertEqual(
+ unescape_wildcard('foo\\%bar\\_baz\\\\'),
+ 'foo%bar_baz\\')
+
+ def test_is_full_text(self):
+ "Test is_full_text"
+ for value, result in [
+ ('foo', True),
+ ('%foo bar%', True),
+ ('foo%', False),
+ ('foo_bar', False),
+ ('foo\\_bar', True),
+ ]:
+ with self.subTest(value=value):
+ self.assertEqual(is_full_text(value), result)
+
def test_slugify(self):
"Test slugify"
self.assertEqual(slugify('unicode ♥ is ☢'), 'unicode-is')
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tests/test_tryton.py
--- a/trytond/tests/test_tryton.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tests/test_tryton.py Sun Mar 21 16:21:05 2021 +0100
@@ -842,6 +842,42 @@
Module.update_list()
+class ExtensionTestCase(unittest.TestCase):
+ extension = None
+
+ @classmethod
+ def setUpClass(cls):
+ super().setUpClass()
+ cls._activate_extension()
+
+ @classmethod
+ def tearDownClass(cls):
+ super().tearDownClass()
+ cls._deactivate_extension()
+
+ @classmethod
+ @with_transaction()
+ def _activate_extension(cls):
+ connection = Transaction().connection
+ cursor = connection.cursor()
+ cursor.execute('CREATE EXTENSION "%s"' % cls.extension)
+ connection.commit()
+ cls._clear_cache()
+
+ @classmethod
+ @with_transaction()
+ def _deactivate_extension(cls):
+ connection = Transaction().connection
+ cursor = connection.cursor()
+ cursor.execute('DROP EXTENSION "%s"' % cls.extension)
+ connection.commit()
+ cls._clear_cache()
+
+ @classmethod
+ def _clear_cache(cls):
+ backend.Database._has_proc.clear()
+
+
def drop_db(name=DB_NAME):
if db_exist(name):
database = backend.Database(name)
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tools/__init__.py
--- a/trytond/tools/__init__.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tools/__init__.py Sun Mar 21 16:21:05 2021 +0100
@@ -9,14 +9,15 @@
file_open, get_smtp_server, reduce_ids, reduce_domain,
grouped_slice, is_instance_method, resolve, strip_wildcard,
lstrip_wildcard, rstrip_wildcard, slugify, sortable_values,
- escape_wildcard, sql_pairing, firstline)
+ escape_wildcard, unescape_wildcard, is_full_text, sql_pairing, firstline)
from .decimal_ import decistmt
__all__ = ['file_open', 'get_smtp_server', 'reduce_ids',
'reduce_domain', 'grouped_slice', 'is_instance_method', 'resolve',
'strip_wildcard', 'lstrip_wildcard', 'rstrip_wildcard', 'slugify',
'decistmt', 'ClassProperty', 'cursor_dict', 'cached_property',
- 'sortable_values', 'escape_wildcard', 'sql_pairing', 'firstline']
+ 'sortable_values', 'escape_wildcard', 'unescape_wildcard', 'is_full_text',
+ 'sql_pairing', 'firstline']
class ClassProperty(property):
diff -r 130d98839af1 -r 03ec47b65b78 trytond/tools/misc.py
--- a/trytond/tools/misc.py Sun Mar 21 16:09:13 2021 +0100
+++ b/trytond/tools/misc.py Sun Mar 21 16:21:05 2021 +0100
@@ -221,6 +221,20 @@
return string
+def unescape_wildcard(string, wildcards='%_', escape='\\'):
+ for wildcard in wildcards + escape:
+ string = string.replace(escape + wildcard, wildcard)
+ return string
+
+
+def is_full_text(value, escape='\\'):
+ escaped = value.strip('%')
+ escaped = escaped.replace(escape + '%', '').replace(escape + '_', '')
+ if '%' in escaped or '_' in escaped:
+ return False
+ return value.startswith('%') == value.endswith('%')
+
+
_slugify_strip_re = re.compile(r'[^\w\s-]')
_slugify_hyphenate_re = re.compile(r'[-\s]+')
