Tratto, tradotto e leggermente modificato da: https://postgis.net/workshops/postgis-intro/history_tracking.html
Questo articolo non è assolutamente “farina del mio sacco”; ho soltanto tradotto e leggermente modificato quanto riportato in una pagina fatta molto bene (ringrazio sentitamente l’autore o gli autori) che si trova qui: https://postgis.net/workshops/postgis-intro/history_tracking.html adattando l’esempio ai dati che ho utilizzato per fare un test. Funziona davvero bene!
Un requisito comune per i database è la capacità di tenere traccia della cronologia: come sono cambiati i dati tra due date, chi ha apportato le modifiche e dove si sono verificate?
Utilizzando il database e il sistema di trigger, è possibile aggiungere il monitoraggio della cronologia a qualsiasi tabella, mantenendo un semplice accesso di “modifica diretta” alla tabella principale.
Il monitoraggio della cronologia funziona mantenendo una tabella della cronologia che registra, per ogni modifica:
- Se un record è stato creato, quando è stato aggiunto e da chi.
- Se un record è stato eliminato, quando è stato eliminato e da chi.
- Se un record è stato aggiornato, aggiungendo un record di cancellazione (per il vecchio stato) e un record di creazione (per il nuovo stato).
Utilizzo di TSTZRANGE
La tabella della cronologia utilizza una funzionalità specifica di PostgreSQL, il tipo “intervallo di timestamp” (tstzrange), per memorizzare l’intervallo di tempo in cui un record della cronologia era il record “live”. Tutti gli intervalli di timestamp nella tabella della cronologia per una particolare caratteristica non si sovrappongono ma restano adiacenti.
L’intervallo per un nuovo record inizierà a now() e avrà un punto finale aperto, in modo che l’intervallo copra tutto il tempo dall’ora corrente al futuro.
Impartendo il comando seguente:
=# SELECT tstzrange(current_timestamp, NULL);
Otterremo per esempio:
=#
tstzrange
————————————
[“2023-08-01 14:49:40.910074-07”,)
Allo stesso modo, l’intervallo di tempo per un record eliminato verrà aggiornato per includere l’ora corrente come punto finale dell’intervallo di tempo.
La ricerca di intervalli di tempo è molto più semplice della ricerca di una coppia di timestamp: un intervallo di tempo aperto comprende tutto il tempo dal punto iniziale all’infinito. Per gli intervalli useremo l’operatore “contiene” @>. Per esempio:
=# SELECT tstzrange(current_timestamp – ’10m’::interval, NULL) @> current_timestamp;
Gli intervalli possono essere indicizzati in modo molto efficiente utilizzando un indice GIST, proprio come i dati spaziali, come mostreremo di seguito. Ciò rende le query di cronologia molto efficienti.
Costruire la tabella della storia (history)
Utilizzando queste informazioni è possibile ricostruire lo stato della tabella di modifica in qualsiasi momento. Partiamo per esempio da una tabella che contiene lo stradario (strade); aggiungeremo quindi il monitoraggio della cronologia alla nostra tabella “strade”.
Innanzitutto, aggiungiamo una nuova tabella “strade_history”. Questa è la tabella che useremo per memorizzare tutte le informazioni di modifica storiche. Oltre a tutti i campi di “strade”, aggiungiamo altri quattro campi.
- hid: la chiave primaria per la tabella della cronologia
- created_by: l’utente del database che ha creato il record
- deleted_by: l’utente del database che ha causato la marcatura del record come cancellato
- valid_range: l’intervallo di tempo entro il quale il record era “live”
Si noti che in realtà non eliminiamo alcun record nella tabella della cronologia, contrassegniamo solo l’ora in cui hanno cessato di far parte dello stato corrente della tabella di modifica.
=# CREATE TABLE strade_history (hid SERIAL PRIMARY KEY, gid INTEGER, nome VARCHAR(50), geom GEOMETRY (MULTILINESTRING, 3003), valid_range TSTZRANGE, created_by VARCHAR(32), deleted_by VARCHAR(32));
=# CREATE INDEX strade_history_geom_x ON strade_history USING GIST (geom);
=# CREATE INDEX strade_history_tstz_x ON strade_history USING GIST (valid_range);
Successivamente, importiamo lo stato corrente della tabella attiva, “strade” nella tabella della cronologia, in modo da avere un punto di partenza da cui tracciare la cronologia. Si noti che inseriamo l’ora di creazione e l’utente di creazione, ma lasciamo NULL la fine dell’intervallo di tempo e l’informazione “deleted_by”.
=# INSERT INTO strade_history (gid, nome, geom, valid_range, created_by) SELECT gid, nome, geom, tstzrange(now(), NULL), current_user FROM strade;
Ora abbiamo bisogno di tre trigger sulla tabella attiva, per le azioni INSERT, DELETE e UPDATE. Per prima cosa creiamo le funzioni trigger, quindi le colleghiamo alla tabella come trigger.
Per un nuovo inserimento, aggiungiamo semplicemente un nuovo record nella tabella della cronologia con l’ora di creazione/utente.
=# CREATE OR REPLACE FUNCTION strade_insert() RETURNS trigger AS
$$
BEGIN
INSERT INTO strade_history
(gid, nome, geom, valid_range, created_by)
VALUES
(NEW.gid, NEW.nome, NEW.geom,
tstzrange(current_timestamp, NULL), current_user);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
Quindi creiamo il primo trigger per l’inserimento
=# CREATE TRIGGER strade_insert_trigger AFTER INSERT ON strade FOR EACH ROW EXECUTE PROCEDURE strade_insert();
Per la cancellazione, contrassegniamo semplicemente il record della cronologia attualmente attivo (quello con un tempo di cancellazione NULL) come cancellato.
=# CREATE OR REPLACE FUNCTION strade_delete() RETURNS trigger AS
$$
BEGIN
UPDATE strade_history
SET valid_range = tstzrange(lower(valid_range), current_timestamp),
deleted_by = current_user
WHERE valid_range @> current_timestamp AND gid = OLD.gid;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
E relativo trigger per l’eliminazione:
=# CREATE TRIGGER strade_delete_trigger AFTER DELETE ON strade FOR EACH ROW EXECUTE PROCEDURE strade_delete();
Per un aggiornamento, prima contrassegniamo il record della cronologia attivo come eliminato, quindi inseriamo un nuovo record per lo stato aggiornato.
=# CREATE OR REPLACE FUNCTION strade_update() RETURNS trigger AS
$$
BEGIN
UPDATE strade_history
SET valid_range = tstzrange(lower(valid_range), current_timestamp),
deleted_by = current_user
WHERE valid_range @> current_timestamp AND gid = OLD.gid;
INSERT INTO strade_history
(gid, nome, geom, valid_range, created_by)
VALUES
(NEW.gid, NEW.nome, NEW.geom,
tstzrange(current_timestamp, NULL), current_user);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
E relativo trigger per l’aggiornamento:
=# CREATE TRIGGER strade_update_trigger AFTER UPDATE ON strade FOR EACH ROW EXECUTE PROCEDURE strade_update();
Modificare la tabella
Ora che la tabella della cronologia è abilitata, possiamo apportare modifiche alla tabella principale (“strade”) e guardare le voci del registro apparire nella tabella della cronologia (“history_strade”).
Da notare la potenza di questo approccio alla cronologia basato su database: indipendentemente dallo strumento utilizzato per apportare le modifiche, che si tratti della riga di comando SQL, di uno strumento JDBC basato sul Web o di uno strumento desktop come QGIS, la cronologia viene costantemente monitorata.
Modifiche SQL
Trasformiamo la due strada denominata “VIA ROMA” in “VIA ROMA IMPERIALE” (per esempio):
L’aggiornamento della strada farà sì che la strada originale venga contrassegnata come cancellata nella tabella della cronologia, con un’ora di eliminazione di adesso, e una nuova strada con l’aggiunta del nuovo nome, con un’ora di aggiunta di adesso. Possiamo consultare i dati storici.
Interrogazione della tabella cronologica
Ora che abbiamo una tabella cronologica, a cosa serve? È utile per viaggiare nel tempo! Per viaggiare in un particolare tempo T, è necessario costruire una query che includa:
- Tutti i record creati prima di T e non ancora cancellati; e anche
- Tutti i record creati prima di T, ma eliminati dopo T.
Possiamo usare questa logica per creare una query, o una vista, dello stato dei dati nel passato. Poiché presumibilmente tutte le tue modifiche di prova sono avvenute negli ultimi due minuti, creiamo una vista della tabella della cronologia che mostri lo stato della tabella 10 minuti fa, prima che si iniziasse a modificare (quindi, i dati originali).
=# CREATE OR REPLACE VIEW strade_dieci_min_ago AS SELECT * FROM strade_history WHERE valid_range @> (now() – ’10min’::interval);
Caricando questa vista su QGIS possiamo vedere i dati come erano 10 minuti fa
Se invece volessimo recuperare la versione della tabella ad una certa data (per esempio alla data del 01 agosto 2023):
=# CREATE OR REPLACE VIEW strade_al_20230801 AS SELECT strade_history.hid, strade_history.gid, strade_history.nome, strade_history.geom, strade_history.valid_range, strade_history.created_by, strade_history.deleted_by FROM strade_history WHERE lower(strade_history.valid_range) < ‘2023-08-02 13:10:38’;
In questo modo avremo tutti gli oggetti presenti nel database prima del 2 agosto 2023 alle ore 13:10:38″. Verranno pertanto esclusi tutti gli oggetti creati, modificati o eliminati dopo quel momento.
map freely 