details: https://code.tryton.org/tryton/commit/65be67251d10
branch: default
user: Cédric Krier <[email protected]>
date: Fri Dec 26 10:58:30 2025 +0100
description:
Use array as IN operators
diffstat:
trytond/CHANGELOG | 1 +
trytond/trytond/backend/database.py | 4 ++
trytond/trytond/backend/postgresql/database.py | 10 +++-
trytond/trytond/model/fields/binary.py | 8 ++--
trytond/trytond/model/fields/field.py | 41 +++++++++++++++++++++-
trytond/trytond/model/fields/many2many.py | 8 ++--
trytond/trytond/model/fields/many2one.py | 25 +++++++-----
trytond/trytond/model/modelsql.py | 48 ++++++++++++++-----------
8 files changed, 99 insertions(+), 46 deletions(-)
diffs (434 lines):
diff -r 7df2a71c0551 -r 65be67251d10 trytond/CHANGELOG
--- a/trytond/CHANGELOG Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/CHANGELOG Fri Dec 26 10:58:30 2025 +0100
@@ -1,3 +1,4 @@
+* Use array for ``in`` operators
* Update to Psycopg 3
* Add support for Python 3.14
* Remove support for Python 3.9
diff -r 7df2a71c0551 -r 65be67251d10 trytond/trytond/backend/database.py
--- a/trytond/trytond/backend/database.py Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/trytond/backend/database.py Fri Dec 26 10:58:30 2025 +0100
@@ -204,6 +204,10 @@
def refresh_materialized_view(self, connection, view_name):
raise NotImplementedError
+ @classmethod
+ def has_array(cls):
+ return False
+
def sql_type(self, type_):
pass
diff -r 7df2a71c0551 -r 65be67251d10
trytond/trytond/backend/postgresql/database.py
--- a/trytond/trytond/backend/postgresql/database.py Tue Nov 25 15:05:02
2025 +0100
+++ b/trytond/trytond/backend/postgresql/database.py Fri Dec 26 10:58:30
2025 +0100
@@ -24,12 +24,12 @@
from sql.aggregate import Count
from sql.conditionals import Coalesce
from sql.functions import Function
-from sql.operators import BinaryOperator, Concat, NotEqual
+from sql.operators import Any, BinaryOperator, Concat, NotEqual
from trytond import __series__, config
from trytond.backend.database import DatabaseInterface, SQLType
from trytond.sql.operators import RangeOperator
-from trytond.tools import grouped_slice, reduce_ids
+from trytond.tools import grouped_slice
from .table import index_method
@@ -487,7 +487,7 @@
table = Table(table)
cursor = connection.cursor()
for sub_ids in grouped_slice(ids):
- where = reduce_ids(table.id, sub_ids)
+ where = table.id == Any(list(sub_ids))
query = table.select(
Literal(1), where=where, for_=For('UPDATE', nowait=True))
cursor.execute(*query)
@@ -899,6 +899,10 @@
Identifier(view_name)
))
+ @classmethod
+ def has_array(cls):
+ return True
+
def convert_json(value):
from trytond.protocols.jsonrpc import JSONDecoder
diff -r 7df2a71c0551 -r 65be67251d10 trytond/trytond/model/fields/binary.py
--- a/trytond/trytond/model/fields/binary.py Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/trytond/model/fields/binary.py Fri Dec 26 10:58:30 2025 +0100
@@ -6,10 +6,10 @@
from sql import Column, Null
from trytond.filestore import filestore
-from trytond.tools import cached_property, grouped_slice, reduce_ids
+from trytond.tools import cached_property, grouped_slice
from trytond.transaction import Transaction
-from .field import Field
+from .field import SQL_OPERATORS, Field
logger = logging.getLogger(__name__)
@@ -86,7 +86,7 @@
for sub_ids in grouped_slice(ids):
cursor.execute(*table.select(
table.id, Column(table, self.file_id),
- where=reduce_ids(table.id, sub_ids)
+ where=SQL_OPERATORS['in'](table.id, sub_ids)
& (Column(table, self.file_id) != Null)
& (Column(table, self.file_id) != '')))
for record_id, file_id in cursor:
@@ -129,7 +129,7 @@
columns = [Column(table, name)]
values = [self.sql_format(value)]
cursor.execute(*table.update(columns, values,
- where=reduce_ids(table.id, ids)))
+ where=SQL_OPERATORS['in'](table.id, ids)))
def definition(self, model, language):
definition = super().definition(model, language)
diff -r 7df2a71c0551 -r 65be67251d10 trytond/trytond/model/fields/field.py
--- a/trytond/trytond/model/fields/field.py Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/trytond/model/fields/field.py Fri Dec 26 10:58:30 2025 +0100
@@ -248,14 +248,51 @@
'not like': partial(operators.NotLike, escape='\\'),
'ilike': partial(operators.ILike, escape='\\'),
'not ilike': partial(operators.NotILike, escape='\\'),
- 'in': operators.In,
- 'not in': operators.NotIn,
'<=': operators.LessEqual,
'>=': operators.GreaterEqual,
'<': operators.Less,
'>': operators.Greater,
}
+if backend.Database.has_array():
+ class _EqualArray(operators.Equal):
+ def __invert__(self):
+ return _NotEqualArray(self.left, operators.All(self.right.operand))
+
+ class _NotEqualArray(operators.NotEqual):
+ def __invert__(self):
+ return _EqualArray(self.left, operators.Any(self.right.operand))
+
+ def in_array(left, right):
+ if isinstance(right, (Query, Expression)):
+ return operators.In(left, right)
+ if not isinstance(right, (list, tuple)):
+ right = list(right)
+ return _EqualArray(left, operators.Any(right))
+
+ def not_in_array(left, right):
+ if isinstance(right, (Query, Expression)):
+ return operators.NotIn(left, right)
+ if not isinstance(right, (list, tuple)):
+ right = list(right)
+ return _NotEqualArray(left, operators.All(right))
+
+ SQL_OPERATORS['in'] = in_array
+ SQL_OPERATORS['not in'] = not_in_array
+else:
+ def in_(left, right):
+ if not isinstance(right, (list, tuple, Query, Expression)):
+ right = list(right)
+ return operators.In(left, right)
+
+ def not_in(left, right):
+ if not isinstance(right, (list, tuple, Query, Expression)):
+ right = list(right)
+ return operators.NotIn(left, right)
+
+ SQL_OPERATORS['in'] = in_
+ SQL_OPERATORS['not in'] = not_in
+
class Field(object):
_type = None
diff -r 7df2a71c0551 -r 65be67251d10 trytond/trytond/model/fields/many2many.py
--- a/trytond/trytond/model/fields/many2many.py Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/trytond/model/fields/many2many.py Fri Dec 26 10:58:30 2025 +0100
@@ -13,9 +13,9 @@
from trytond.transaction import Transaction
from .field import (
- Field, context_validate, domain_method, domain_validate, get_eval_fields,
- instanciate_values, instantiate_context, search_order_validate,
- size_validate)
+ SQL_OPERATORS, Field, context_validate, domain_method, domain_validate,
+ get_eval_fields, instanciate_values, instantiate_context,
+ search_order_validate, size_validate)
class Many2Many(Field):
@@ -359,7 +359,7 @@
if not ids:
expression = Literal(False)
else:
- expression = table.id.in_(ids)
+ expression = SQL_OPERATORS['in'](table.id, ids)
if operator.startswith('not'):
return ~expression
return expression
diff -r 7df2a71c0551 -r 65be67251d10 trytond/trytond/model/fields/many2one.py
--- a/trytond/trytond/model/fields/many2one.py Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/trytond/model/fields/many2one.py Fri Dec 26 10:58:30 2025 +0100
@@ -13,12 +13,12 @@
import trytond.config as config
from trytond.pool import Pool
from trytond.pyson import PYSONEncoder
-from trytond.tools import cached_property, reduce_ids
+from trytond.tools import cached_property
from trytond.transaction import Transaction, inactive_records
from .field import (
- Field, context_validate, domain_method, instantiate_context, order_method,
- search_order_validate)
+ SQL_OPERATORS, Field, context_validate, domain_method, instantiate_context,
+ order_method, search_order_validate)
class Many2One(Field):
@@ -131,12 +131,13 @@
cursor = Transaction().connection.cursor()
table, _ = tables[None]
name, operator, ids = domain
- red_sql = reduce_ids(table.id, (i for i in ids if i is not None))
+ where = SQL_OPERATORS['in'](
+ table.id, [int(i) for i in ids if i is not None])
Target = self.get_target()
path_column = getattr(Target, self.path).sql_column(tables, Target)
path_column = Coalesce(path_column, '')
cursor.execute(*table.select(
- path_column, where=red_sql,
+ path_column, where=where,
order_by=[CharLength(path_column).desc, path_column.asc]))
if operator.endswith('child_of'):
paths = set()
@@ -152,7 +153,7 @@
where.append(path_column.like(path + '%'))
else:
ids = [int(x) for path, in cursor for x in path.split('/')[:-1]]
- where = reduce_ids(table.id, ids)
+ where = SQL_OPERATORS['in'](table.id, ids)
if not where:
where = Literal(False)
if operator.startswith('not'):
@@ -163,12 +164,13 @@
cursor = Transaction().connection.cursor()
table, _ = tables[None]
name, operator, ids = domain
- red_sql = reduce_ids(table.id, (i for i in ids if i is not None))
+ where = SQL_OPERATORS['in'](
+ table.id, [int(i) for i in ids if i is not None])
Target = self.get_target()
left = getattr(Target, self.left).sql_column(tables, Target)
right = getattr(Target, self.right).sql_column(tables, Target)
cursor.execute(*table.select(
- left, right, where=red_sql,
+ left, right, where=where,
order_by=[(right - left).asc, left.asc]))
ranges = set()
for l, r in cursor:
@@ -195,18 +197,19 @@
target = Target.__table__()
table, _ = tables[None]
name, operator, ids = domain
- red_sql = reduce_ids(target.id, (i for i in ids if i is not None))
+ where = SQL_OPERATORS['in'](
+ target.id, [int(i) for i in ids if i is not None])
if operator.endswith('child_of'):
tree = With('id', recursive=True)
- tree.query = target.select(target.id, where=red_sql)
+ tree.query = target.select(target.id, where=where)
tree.query |= (target
.join(tree, condition=Column(target, name) == tree.id)
.select(target.id))
else:
tree = With('id', name, recursive=True)
tree.query = target.select(
- target.id, Column(target, name), where=red_sql)
+ target.id, Column(target, name), where=where)
tree.query |= (target
.join(tree, condition=target.id == Column(tree, name))
.select(target.id, Column(target, name)))
diff -r 7df2a71c0551 -r 65be67251d10 trytond/trytond/model/modelsql.py
--- a/trytond/trytond/model/modelsql.py Tue Nov 25 15:05:02 2025 +0100
+++ b/trytond/trytond/model/modelsql.py Fri Dec 26 10:58:30 2025 +0100
@@ -21,7 +21,7 @@
from trytond.pyson import PYSONDecoder, PYSONEncoder
from trytond.rpc import RPC
from trytond.sql.functions import Range
-from trytond.tools import cursor_dict, grouped_slice, reduce_ids
+from trytond.tools import cursor_dict, grouped_slice
from trytond.tools.domain_inversion import simplify
from trytond.transaction import (
Transaction, inactive_records, record_cache_size, without_check_access)
@@ -758,7 +758,7 @@
columns = [
Coalesce(table.write_date, table.create_date), table.id, user.name]
for sub_ids in grouped_slice(ids):
- where = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
cursor.execute(*table.join(user, 'LEFT',
Coalesce(table.write_uid, table.create_uid) == user.id)
.select(*columns, where=where, group_by=columns))
@@ -799,7 +799,7 @@
hcolumns.append(Column(history, fname))
for sub_ids in grouped_slice(ids):
if not deleted:
- where = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
cursor.execute(*history.insert(hcolumns,
table.select(*columns, where=where)))
else:
@@ -871,7 +871,7 @@
else:
hwhere = (column_datetime < datetime)
- hwhere &= reduce_ids(history.id, sub_ids)
+ hwhere &= fields.SQL_OPERATORS['in'](history.id, sub_ids)
history_select.query.where = hwhere
cursor.execute(*history_values)
@@ -885,8 +885,9 @@
# we need to skip the deleted IDs that are all None history records
# because they could fail the UPDATE
if to_delete:
- history_select.query.where &= ~history.id.in_(
- list(deleted_sub_ids))
+ history_select.query.where &= (
+ fields.SQL_OPERATORS['not in'](
+ history.id, list(deleted_sub_ids)))
# Some of the sub_ids are not updated because they are not in the
# table anymore, they should be undeleted from the value in the
@@ -908,14 +909,15 @@
cursor.execute(*update_query)
if to_undelete:
history_select.query.where = (hwhere
- & history.id.in_(list(to_undelete)))
+ & fields.SQL_OPERATORS['in'](
+ history.id, list(to_undelete)))
cursor.execute(*table.insert(
columns,
history_values.select(*history_columns)))
if to_delete:
for sub_ids in grouped_slice(to_delete):
- where = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
cursor.execute(*table.delete(where=where))
cls._insert_history(list(to_delete), True)
if to_update:
@@ -1256,7 +1258,7 @@
from_ = convert_from(None, tables)
for sub_ids in grouped_slice(ids, in_max):
sub_ids = list(sub_ids)
- red_sql = reduce_ids(table.id, sub_ids)
+ red_sql = fields.SQL_OPERATORS['in'](table.id, sub_ids)
where = red_sql
if history_clause:
where &= history_clause
@@ -1517,10 +1519,10 @@
update_values.append(field.sql_format(value))
for sub_ids in grouped_slice(ids):
- red_sql = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
try:
- cursor.execute(*table.update(columns, update_values,
- where=red_sql))
+ cursor.execute(*table.update(
+ columns, update_values, where=where))
except (
backend.DatabaseIntegrityError,
backend.DatabaseDataError) as exception:
@@ -1591,7 +1593,8 @@
for fname in cls._mptt_fields:
tree_ids[fname] = []
for sub_ids in grouped_slice(ids):
- where = reduce_ids(Column(table, fname), sub_ids)
+ where = fields.SQL_OPERATORS['in'](
+ Column(table, fname), sub_ids)
cursor.execute(*table.select(table.id, where=where))
tree_ids[fname] += [x[0] for x in cursor]
@@ -1628,12 +1631,13 @@
Column(table, n) for n in foreign_fields_to_clean]
cursor.execute(*table.update(
columns, [table.id] * len(foreign_fields_to_clean),
- where=reduce_ids(table.id, sub_ids)))
+ where=fields.SQL_OPERATORS['in'](table.id, sub_ids)
+ ))
def get_related_records(Model, field_name, sub_ids):
if issubclass(Model, ModelSQL):
foreign_table = Model.__table__()
- foreign_red_sql = reduce_ids(
+ foreign_red_sql = fields.SQL_OPERATORS['in'](
Column(foreign_table, field_name), sub_ids)
cursor.execute(*foreign_table.select(foreign_table.id,
where=foreign_red_sql))
@@ -1650,7 +1654,7 @@
for sub_ids, sub_records in zip(
grouped_slice(ids), grouped_slice(records)):
sub_ids = list(sub_ids)
- red_sql = reduce_ids(table.id, sub_ids)
+ red_sql = fields.SQL_OPERATORS['in'](table.id, sub_ids)
for Model, field_name in foreign_keys_toupdate:
related_records = get_related_records(
@@ -1725,7 +1729,7 @@
from_ = convert_from(None, tables)
for sub_ids in grouped_slice(ids, in_max):
sub_ids = set(sub_ids)
- where = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
if history_clause:
where &= history_clause
if domain:
@@ -2114,7 +2118,7 @@
where=parent.id == parent_column),
''), table.id), '/')])
for sub_ids in grouped_slice(ids):
- query.where = reduce_ids(table.id, sub_ids)
+ query.where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
cursor.execute(*query)
@classmethod
@@ -2127,7 +2131,7 @@
def update_path(query, column, sub_ids):
updated = set()
- query.where = reduce_ids(table.id, sub_ids)
+ query.where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
cursor.execute(*query)
for old_path, new_path in cursor:
if old_path == new_path:
@@ -2293,7 +2297,7 @@
columns.insert(0, table.id)
in_max = transaction.database.IN_MAX // (len(columns) + 1)
for sub_ids in grouped_slice(ids, in_max):
- where = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
if isinstance(sql, Exclude) and sql.where:
where &= sql.where
@@ -2329,9 +2333,9 @@
raise SQLConstraintError(gettext(error))
elif isinstance(sql, Check):
for sub_ids in grouped_slice(ids):
- red_sql = reduce_ids(table.id, sub_ids)
+ where = fields.SQL_OPERATORS['in'](table.id, sub_ids)
cursor.execute(*table.select(table.id,
- where=~sql.expression & red_sql,
+ where=~sql.expression & where,
limit=1))
if cursor.fetchone():
raise SQLConstraintError(gettext(error))
