bgervan
commented
1 week ago I have move forward a bit with the actual implementation:
def _get_select(self, event_model):
where_clause = self._get_where_clause(event_model)
(
final_context_columns_clause,
context_column_clause,
context_id_column_clause,
context_join_clause,
annotated_context_columns_clause,
) = self._get_context_clauses(event_model)
pgh_obj_model = event_model._meta.get_field("pgh_obj").remote_field.model
pgh_obj_table = pgh_obj_model._meta.db_table
update_trackers = [
tracker for tracker in event_model.pgh_trackers if tracker.label == "update"
]
curr_data_clause = "row_to_json(_event) AS _curr_data"
prev_data_clause = """
LAG(row_to_json(_event))
OVER (
PARTITION BY _event.pgh_obj_id
ORDER BY _event.pgh_id
) AS _prev_data
"""
pgh_obj_id_column_clause = "pgh_obj_id::TEXT"
if not hasattr(event_model, "pgh_obj_id"):
prev_data_clause = "NULL::JSONB AS _prev_data"
pgh_obj_id_column_clause = "NULL::TEXT AS pgh_obj_id"
elif update_trackers and update_trackers[0].row == "OLD": # Check if Old or New
curr_data_clause = f"""
COALESCE(
LEAD(row_to_json(_event)) OVER (PARTITION BY _event.pgh_obj_id ORDER BY _event.pgh_id),
(SELECT row_to_json(m) FROM {pgh_obj_table} m WHERE m.id = _event.pgh_obj_id)
) AS _curr_data
"""
prev_data_clause = "row_to_json(_event) AS _prev_data"
event_table = event_model._meta.db_table
return f"""
SELECT
CONCAT('{event_model._meta.label}', ':', _pgh_obj_event.pgh_id) AS pgh_slug,
_pgh_obj_event.pgh_id,
_pgh_obj_event.pgh_created_at,
_pgh_obj_event.pgh_label,
{final_context_columns_clause}
_pgh_obj_event.pgh_obj_id,
'{event_model._meta.label}' AS pgh_model,
'{event_model.pgh_tracked_model._meta.label}' AS pgh_obj_model,
(
SELECT JSONB_OBJECT_AGG(filtered.key, filtered.value)
FROM
(
SELECT key, value
FROM JSONB_EACH(_pgh_obj_event._curr_data::JSONB)
) filtered
WHERE filtered.key NOT LIKE 'pgh_%%'
) AS pgh_data,
(
SELECT JSONB_OBJECT_AGG(curr.key, array[prev.value, curr.value])
FROM
(
SELECT key, value
FROM JSONB_EACH(_pgh_obj_event._curr_data::JSONB)
) curr
LEFT OUTER JOIN
(
SELECT key, value
FROM JSONB_EACH(_pgh_obj_event._prev_data::JSONB)
) prev
ON curr.key = prev.key
WHERE curr.key NOT LIKE 'pgh_%%'
AND curr.value != prev.value
AND prev IS NOT NULL
) AS pgh_diff,
_pgh_obj_event.pgh_context_id,
_pgh_obj_event.pgh_context
FROM (
SELECT
pgh_id,
pgh_created_at,
pgh_label,
{curr_data_clause},
{annotated_context_columns_clause}
{prev_data_clause},
{context_id_column_clause},
{context_column_clause},
{pgh_obj_id_column_clause}
FROM {event_table} _event
{context_join_clause}
{where_clause}
ORDER BY _event.pgh_id
) _pgh_obj_event
"""I can open a PR and update to align with project scopes as needed.
This part is not quite clear for me
if not hasattr(event_model, "pgh_obj_id"):
prev_data_clause = "NULL::JSONB AS _prev_data"
pgh_obj_id_column_clause = "NULL::TEXT AS pgh_obj_id"
wesleykendall
Hi, first of all, great package.
My use case: I am storing the old data, to make it easier to revert to something. I am storing transaction_id in context, so I can revert all related changes in a request easily. (At least this is the goal)
If I am using the following settings:
the pgh_diff is wrong, as it fetch the current and previous data of the event. I have changed the raw sql to reach my goal to the this:
Important part is:
Also added pgh_old_data, so it is easier to debug. In this setup:
Can we add changes like this optionally? As I understand, all these affect only the admin, and relevant only when using the pghistory.Old
The phg_diff is important, but the diff_to_real is also nice to have, as with that, we can show the diffs between the revert and current state before the user click on the revert.
EDIT: Added important sql sections separately for easier reading