[REF] core: new flush strategy

Rationale
=========

The ORM holds the pending row updates as long as it can, because the
second update on a row in a transaction is much slower than the first
one.  Then we need to flush pending updates that may affect the result
of an SQL query.  Initially, the consistency mechanism within the ORM
was to call _flush_search() before creating the query itself. The
purpose of this method was to flush all fields used in the search domain
(including the ir.rule domain), the order, and extra fields (used for
_read_group()).  This current solution has some drawbacks:

- Because expression (the class that translates a domain into a WHERE
  clause) evolved, there are already inconsistencies between fields used
  in the WHERE clause and the fields flushed by _flush_search().
- The method _search() returns a lazy query object, and we execute it
  only when needed or add it as a subquery. But _flush_search() is not
  lazy and will force necessary updates directly even if the query isn't
  executed at the end (or later, which can lead to some inconsistencies).
- Tracking field used in SQL expression makes some API heavier than
  necessary. For example, for each _read_group*() hooks, we always need
  to return a list of fields along with the SQL expression.  Also, if we
  want to extend _leaf_to_sql() for related fields, we will need to
  track fields used in the paths (i.e. add this information in the
  return value and then change each use of it).

Change
======

The idea is to integrate the information about what field was used to
create an SQL expression and use that information just before executing
the query. We then add a _metadata attribute to the SQL object that
holds the field used to create it. This metadata attribute is used
only by _field_to_sql(), which we want to be the only method to generate
SQL from a field name.

These metadata is flushed just before the query is executed.  Since we
always need the current env to flush with the right context/user, we
cannot flush automatically flush in cr.execute().  Instead, we add a
method execute_query() on the environment itself.  This method is
currently almost only used by the ORM itself now and it only accepts
(and asserts) SQL object as query.  This method may become a mandatory
way to execute SQL in order to enforce security (combined with the
pylint check) and avoid SQL injection in the long run.

Part-of: odoo/odoo#144747

1 files changed, 23 insertions, 1 deletions

diff --git a/odoo/api.py b/odoo/api.py
index aa59357c785..1828de5ce35 100644
--- a/odoo/api.py
+++ b/odoo/api.py
@@ -822,6 +822,28 @@ class Environment(Mapping):
             self._cache_key[field] = result
             return result
 
+    def flush_query(self, query: SQL):
+        """ Flush all the fields in the metadata of ``query``. """
+        fields_to_flush = tuple(query.to_flush)
+        if not fields_to_flush:
+            return
+
+        fnames_to_flush = defaultdict(OrderedSet)
+        for field in fields_to_flush:
+            fnames_to_flush[field.model_name].add(field.name)
+        for model_name, field_names in fnames_to_flush.items():
+            self[model_name].flush_model(field_names)
+
+    def execute_query(self, query: SQL) -> list[tuple]:
+        """ Execute the given query, fetch its result and it as a list of tuples
+        (or an empty list if no result to fetch).  The method automatically
+        flushes all the fields in the metadata of the query.
+        """
+        assert isinstance(query, SQL)
+        self.flush_query(query)
+        self.cr.execute(query)
+        return self.cr.fetchall() if self.cr.rowcount > 0 else []
+
 
 class Transaction:
     """ A object holding ORM data structures for a transaction. """
@@ -1261,7 +1283,7 @@ class Cache(object):
                 return
 
             # select the column for the given ids
-            query = Query(env.cr, model._table, model._table_query)
+            query = Query(env, model._table, model._table_sql)
             sql_id = SQL.identifier(model._table, 'id')
             sql_field = model._field_to_sql(model._table, field.name, query)
             if field.type == 'binary' and (