details: https://code.tryton.org/tryton/commit/ac36937b30f9
branch: 6.0
user: Nicolas Évrard <[email protected]>
date: Tue May 20 18:28:23 2025 +0200
description:
Use SQL queries to restore history
Restoring a fields.Dict value failed before using this pure SQL
solution.
Also such a solution will be more efficient by minimizing the amount of
python
code run.
Closes #14031
(grafted from c81b7d05a51c491f1c7060506144bf50d395c075)
diffstat:
trytond/trytond/model/modelsql.py | 98 +++++++++++++++++++++++++----------
trytond/trytond/tests/history.py | 8 ++-
trytond/trytond/tests/test_history.py | 24 ++++++++
3 files changed, 101 insertions(+), 29 deletions(-)
diffs (202 lines):
diff -r fe70996476b9 -r ac36937b30f9 trytond/trytond/model/modelsql.py
--- a/trytond/trytond/model/modelsql.py Thu Oct 09 18:50:07 2025 +0200
+++ b/trytond/trytond/model/modelsql.py Tue May 20 18:28:23 2025 +0200
@@ -6,8 +6,8 @@
from functools import wraps
from sql import (Table, Column, Literal, Desc, Asc, Expression, Null,
- NullsFirst, NullsLast, For)
-from sql.functions import CurrentTimestamp, Extract
+ NullsFirst, NullsLast, For, Window, With)
+from sql.functions import CurrentTimestamp, Extract, RowNumber
from sql.conditionals import Coalesce
from sql.operators import Or, And, Operator, Equal
from sql.aggregate import Count, Max
@@ -466,16 +466,25 @@
cursor = transaction.connection.cursor()
table = cls.__table__()
history = cls.__table_history__()
+
+ history_select = With()
+ history_values = history_select.select(
+ where=Column(history_select, '__h_rank') == 1,
+ with_=[history_select])
+
columns = []
hcolumns = []
+ history_columns = []
fnames = sorted(n for n, f in cls._fields.items()
if f.sql_type())
+ id_idx = fnames.index('id')
for fname in fnames:
columns.append(Column(table, fname))
+ history_columns.append(Column(history_values, fname))
if fname == 'write_uid':
- hcolumns.append(Literal(transaction.user))
+ hcolumns.append(Literal(transaction.user).as_('write_uid'))
elif fname == 'write_date':
- hcolumns.append(CurrentTimestamp())
+ hcolumns.append(CurrentTimestamp().as_('write_date'))
else:
hcolumns.append(Column(history, fname))
@@ -483,41 +492,74 @@
return all(not v for n, v in zip(fnames, values)
if n not in ['id', 'write_uid', 'write_date'])
- to_delete = []
- to_update = []
- for id_ in ids:
- column_datetime = Coalesce(history.write_date, history.create_date)
+ to_delete = set()
+ to_update = set()
+
+ column_datetime = Coalesce(history.write_date, history.create_date)
+ h_order = (column_datetime.desc, Column(history, '__id').desc)
+ history_select.query = history.select(
+ *hcolumns,
+ RowNumber(
+ window=Window([history.id], order_by=h_order)
+ ).as_('__h_rank'))
+
+ for sub_ids in grouped_slice(ids):
+ sub_ids = list(sub_ids)
if not _before:
hwhere = (column_datetime <= datetime)
else:
hwhere = (column_datetime < datetime)
- hwhere &= (history.id == id_)
- horder = (column_datetime.desc, Column(history, '__id').desc)
- cursor.execute(*history.select(*hcolumns,
- where=hwhere, order_by=horder, limit=1))
- values = cursor.fetchone()
- if not values or is_deleted(values):
- to_delete.append(id_)
+
+ hwhere &= reduce_ids(history.id, sub_ids)
+ history_select.query.where = hwhere
+
+ cursor.execute(*history_values)
+ for values in cursor.fetchall():
+ if is_deleted(values):
+ to_delete.add(values[id_idx])
+ else:
+ to_update.add(values[id_idx])
+ deleted_sub_ids = set(sub_ids)
+ to_delete |= (deleted_sub_ids - to_update)
+
+ # 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))
+
+ # 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
+ # history table
+ to_undelete = set()
+ update_query = table.update(
+ columns, history_columns,
+ from_=[history_values],
+ where=history_values.id == table.id)
+ if transaction.database.has_returning():
+ update_query.returning = [table.id]
+ cursor.execute(*update_query)
+ to_undelete.update(to_update - set(r[0] for r in cursor))
else:
- to_update.append(id_)
- values = list(values)
- cursor.execute(*table.update(columns, values,
- where=table.id == id_))
- rowcount = cursor.rowcount
- if rowcount == -1 or rowcount is None:
- cursor.execute(*table.select(table.id,
- where=table.id == id_))
- rowcount = len(cursor.fetchall())
- if rowcount < 1:
- cursor.execute(*table.insert(columns, [values]))
+ cursor.execute(*history_values
+ .left_join(table, table.id == history_values.id)
+ .select(history_values.id, where=table.id == Null))
+ to_undelete.update(r[0] for r in cursor)
+ cursor.execute(*update_query)
+ if to_undelete:
+ history_select.query.where = (hwhere
+ & history.id.in_(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)
cursor.execute(*table.delete(where=where))
- cls._insert_history(to_delete, True)
+ cls._insert_history(list(to_delete), True)
if to_update:
- cls._insert_history(to_update)
+ cls._insert_history(list(to_update))
@classmethod
def restore_history(cls, ids, datetime):
diff -r fe70996476b9 -r ac36937b30f9 trytond/trytond/tests/history.py
--- a/trytond/trytond/tests/history.py Thu Oct 09 18:50:07 2025 +0200
+++ b/trytond/trytond/tests/history.py Tue May 20 18:28:23 2025 +0200
@@ -1,9 +1,14 @@
# 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 trytond.model import ModelSQL, fields
+
+from trytond.model import DictSchemaMixin, ModelSQL, fields
from trytond.pool import Pool
+class HistoryDictSchema(DictSchemaMixin, ModelSQL):
+ __name__ = 'test.history.dict.schema'
+
+
class TestHistory(ModelSQL):
'Test History'
__name__ = 'test.history'
@@ -14,6 +19,7 @@
'test.history.line', 'history', 'Lines at Stamp',
datetime_field='stamp')
stamp = fields.Timestamp('Stamp')
+ dico = fields.Dict('test.history.dict.schema', "Dict")
class TestHistoryLine(ModelSQL):
diff -r fe70996476b9 -r ac36937b30f9 trytond/trytond/tests/test_history.py
--- a/trytond/trytond/tests/test_history.py Thu Oct 09 18:50:07 2025 +0200
+++ b/trytond/trytond/tests/test_history.py Tue May 20 18:28:23 2025 +0200
@@ -189,6 +189,30 @@
History.read([history_id], ['value'])
@with_transaction()
+ def test_restore_history_dict(self):
+ "Test restoring a record with a field.Dict"
+ pool = Pool()
+ History = pool.get('test.history')
+ transaction = Transaction()
+
+ history = History(dico={'a': 1})
+ history.save()
+ history_id = history.id
+ first = history.create_date
+
+ transaction.commit()
+
+ history = History(history_id)
+ history.dico = {'b': 2}
+ history.save()
+
+ transaction.commit()
+
+ History.restore_history([history_id], first)
+ history = History(history_id)
+ self.assertEqual(history.dico, {'a': 1})
+
+ @with_transaction()
def test_restore_history_before(self):
'Test restore history before'
pool = Pool()
