Historisieren mit Hibernate

March 04, 2022

Wie kann man Daten am besten historisieren?

Viele Wege führen nach Rom – ich möchte an dieser Stelle einen beschreiben.

Möglichkeit 1: Man nutzt Frameworks in der Zielsprache. Zu erwähnen wäre Hibernate Envers oder Entity Framework Auto-History bzw. Audit.NET.

Möglichkeit 2: Man lässt das die Persistenz erledigen. Datenbank wie SQL Server unterstützen Temporale Tabellen. PostgreSQL hat Plugins – aber man ist auch gut beraten, selber Hand anzulegen – warum, zeige ich gleich. Temporal Tables sind im SQL:2011 Standard zu finden.

Folgende Überlegungen haben mich zu Möglichkeit 2 geführt: Komplexität. Hibernate bereitet sehr oft Kopf weh. Ja – mag es mit Unwissenheit bzw. Fehlbedienung zu tun haben. Trotzdem will man sich keinen Datenverlust leisten durch „Fehlkonfigurationen“ und „Vergessene Annotationen“. Betrachtet man Separation of Concerns (SoC), so sagt mir mein Bauchgefühl, dass Historisierung eher bei der Persistenz zu sehen ist.

Die Anforderungen:

In einem Service ändert meist ein Request Daten. Ich will wissen, welcher Request welchen Datensatz geändert hat.
Der Zeitstempel des Requests soll sich über mehrere Tabellen hinwegziehen. Somit kann ich sagen: Wie war der Datenstand vor und nach dem Request
Transaktionssicherheit
Einfach zu Reviewen

Die erste Frage war natürlich: Historisierung in einer Tabelle oder in zwei Tabellen.

Für eine Tabelle spricht: Einfach, da weniger Tabellen.

Für zwei Tabellen spricht:

Indizes können für beide Tabellen anders gestaltet werden – die Art der Abfragen sind auch anders
Partitioning oder andere Optimierungen kann man getrennt vornehmen
Hibernate fühlt sich auch wohler, da Hibernate unbedingt einen eindeutigen Identifier braucht (sonst bekommt man einen „Multiple Row“ Fehler)

Dazu habe ich ein Beispiel konstruiert:

CREATE SCHEMA IF NOT EXISTS playground;

DROP TABLE IF EXISTS playground.notices_history, playground.persons_history, playground.notices, playground.persons, playground.requests;

CREATE TABLE playground.requests
(
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    request_time TIMESTAMPTZ NOT NULL, -- always with TZ if you want to use UTC
    -- needed for foreign key - but we assume: 1 request per microsecond (maximal resolution --> 14 digits)
    UNIQUE(id, request_time)
);

CREATE TABLE playground.persons
(
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR (255) NOT NULL,
    changed_by_request_id INTEGER NOT NULL REFERENCES playground.requests(id),
    -- soft delete is in most cases a good idea
    deleted_by_request_id INTEGER REFERENCES playground.requests(id)
);

CREATE TABLE playground.notices
(
    id INTEGER GENERATED ALWAYS AS IDENTITY,
    person_id INTEGER NOT NULL REFERENCES playground.persons(id),
    notice VARCHAR(255) NOT NULL,
    changed_by_request_id INTEGER NOT NULL REFERENCES playground.requests(id),
    -- soft delete is in most cases a good idea
    deleted_by_request_id INTEGER REFERENCES playground.requests(id)
);

CREATE TABLE playground.persons_history
(
    id INTEGER NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR (255) NOT NULL,
    changed_by_request_id INTEGER NOT NULL,
    deleted_by_request_id INTEGER,
    history_valid_from TIMESTAMPTZ NOT NULL,
    history_valid_to TIMESTAMPTZ CHECK (history_valid_from < history_valid_to),
    FOREIGN KEY (changed_by_request_id, history_valid_from) REFERENCES playground.requests(id, request_time),
    FOREIGN KEY (deleted_by_request_id, history_valid_to) REFERENCES playground.requests(id, request_time),
     -- a record can be just changed once by one request
    UNIQUE(id, changed_by_request_id),
     -- a record can be just deleted once by one request
    UNIQUE(id, deleted_by_request_id)
);

CREATE TABLE playground.notices_history
(
    id INTEGER NOT NULL,
    person_id INTEGER NOT NULL REFERENCES playground.persons(id),
    notice VARCHAR(255) NOT NULL,
    changed_by_request_id INTEGER NOT NULL REFERENCES playground.requests(id),
    deleted_by_request_id INTEGER REFERENCES playground.requests(id),
    history_valid_from TIMESTAMPTZ NOT NULL,
    history_valid_to TIMESTAMPTZ CHECK (history_valid_from < history_valid_to),
    FOREIGN KEY (changed_by_request_id, history_valid_from) REFERENCES playground.requests(id, request_time),
    FOREIGN KEY (deleted_by_request_id, history_valid_to) REFERENCES playground.requests(id, request_time),
     -- a record can be just changed once by one request
    UNIQUE(id, changed_by_request_id),
     -- a record can be just deleted once by one request
    UNIQUE(id, deleted_by_request_id)
);


CREATE OR REPLACE FUNCTION playground.insert_persons_history() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO playground.persons_history
        (id, first_name, last_name, changed_by_request_id, deleted_by_request_id, history_valid_from)
        VALUES(NEW.id, NEW.first_name, NEW.last_name, NEW.changed_by_request_id, NEW.deleted_by_request_id, (SELECT request_time FROM playground.requests WHERE id = NEW.changed_by_request_id));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION playground.insert_notices_history() RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO playground.notices_history
        (id, person_id, notice, changed_by_request_id, deleted_by_request_id, history_valid_from)
        VALUES(NEW.id, NEW.person_id, NEW.notice, NEW.changed_by_request_id, NEW.deleted_by_request_id, (SELECT request_time FROM playground.requests WHERE id = NEW.changed_by_request_id));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION playground.update_persons_history() RETURNS TRIGGER AS
$$
DECLARE var_request_time TIMESTAMPTZ;
BEGIN
    SELECT request_time INTO var_request_time FROM playground.requests WHERE id = NEW.changed_by_request_id;

    IF NEW.deleted_by_request_id IS NULL THEN
        UPDATE playground.persons_history SET history_valid_to = var_request_time WHERE id = NEW.id AND history_valid_to IS NULL;

        INSERT INTO playground.persons_history
            (id, first_name, last_name, changed_by_request_id, deleted_by_request_id, history_valid_from)
            VALUES(NEW.id, NEW.first_name, NEW.last_name, NEW.changed_by_request_id, NEW.deleted_by_request_id, var_request_time);
    ELSE
        UPDATE playground.persons_history SET deleted_by_request_id = NEW.deleted_by_request_id, history_valid_to = var_request_time WHERE id = NEW.id AND history_valid_to IS NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION playground.update_notices_history() RETURNS TRIGGER AS
$$
DECLARE var_request_time TIMESTAMPTZ;
BEGIN
    SELECT request_time INTO var_request_time FROM playground.requests WHERE id = NEW.changed_by_request_id;

    IF NEW.deleted_by_request_id IS NULL THEN
        UPDATE playground.notices_history SET history_valid_to = var_request_time WHERE id = NEW.id AND history_valid_to IS NULL;

        INSERT INTO playground.notices_history
        (id, person_id, notice, changed_by_request_id, deleted_by_request_id, history_valid_from)
        VALUES(NEW.id, NEW.person_id, NEW.notice, NEW.changed_by_request_id, NEW.deleted_by_request_id, var_request_time);
    ELSE
        UPDATE playground.notices_history SET deleted_by_request_id = NEW.deleted_by_request_id, history_valid_to = var_request_time WHERE id = NEW.id AND history_valid_to IS NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER insert_persons
    AFTER INSERT ON playground.persons
    FOR EACH ROW
    EXECUTE PROCEDURE playground.insert_persons_history();

CREATE TRIGGER insert_notices
    AFTER INSERT ON playground.notices
    FOR EACH ROW
    EXECUTE PROCEDURE playground.insert_notices_history();

CREATE TRIGGER update_persons
    AFTER UPDATE ON playground.persons
    FOR EACH ROW
    EXECUTE PROCEDURE playground.update_persons_history();

CREATE TRIGGER update_notices
    AFTER UPDATE ON playground.notices
    FOR EACH ROW
    EXECUTE PROCEDURE playground.update_notices_history();

Points of Interest:

Jede Tabelle halt folgende Spalten:
- changed_by_request_id: Welcher Request hat den Datensatz in den aktuellen Zustand gebracht?
- deleted_by_request_id: Soft-delete finde ich persönlich eine gute Variante, weil es vor Datenverlust schützt. Man kann bei zu viel „Müll“ noch immer mit einem simplen Query aufräumen.
History Tabellen haben immer folgende Spalten:
- history_valid_from: Seit wann ist der Datensatz gültig?
- history_valid_to: Bis wann ist der Datensatz gültig? Zu beachten: PostgreSQL hat auch Range Types, die nochmal gesondert indiziert werden können. Allerdings wird die Menge an Datensätzen im Beispiel über die Id so stark eingeschränkt, dass ich keine Notwendigkeit sehe.
Trigger „erkennen“ Updates und fügen eine Kopie in die Historie Tabelle ein. Einzige Challenge: Man muss natürlich bei Soft-Deletes aufpassen. Annahme: Es wird nur gelöscht – aber die Daten nicht mehr geändert. Trigger sind auch Concurrency-sicher.

package at.playground.history;

import at.playground.Request;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Filter;
import org.hibernate.annotations.FilterDef;
import org.hibernate.annotations.ParamDef;

import javax.persistence.*;
import java.time.Instant;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "persons_history")
@FilterDef(name = "HistoryFilter", parameters = { @ParamDef(name = "filterDate", type = "java.time.Instant") })
@Filter(name = "HistoryFilter", condition = "history_valid_from <= :filterDate and (history_valid_to is null or :filterDate < history_valid_to)")
public class PersonHistory {
    @Id
    private Long id;

    @Column(name = "first_name", nullable = false, length = 255)
    private String firstName;

    @Column(name = "last_name", nullable = false, length = 255)
    private String lastName;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
    @Filter(name = "HistoryFilter", condition = "history_valid_from <= :filterDate and (history_valid_to is null or :filterDate < history_valid_to)")
    private List<NoticeHistory> notices;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="changed_by_request_id", nullable = false)
    private Request changedByRequest;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="deleted_by_request_id")
    private Request deleteByRequest;

    @Column(name = "history_valid_from", nullable = false)
    private Instant historyValidFrom;

    @Column(name = "history_valid_to")
    private Instant historyValidTo;
}

Points of Interest:

Ein Datensatz mit der Id = 123 kann in der History mehrfach vorkommen. Es ist daher wichtig, dass man einen Filter anlegt, der nicht erwünschte Datensätze rausfiltern. Weil zum Zeitpunkt X hatte Id = 123 auch nur einen Zustand …
Filter müssen auch auf alle Abhängigkeiten angewandt werden
Filter werden pro Session angewandt

Fazit: Mir gefällt die Methode sehr gut und man kann sie definitiv für viele Bereiche empfehlen.