Semplice metodo per storicizzare i dati su database
Abstract: Storicizzare i dati spesso è un attività considerata "secondaria", ma un cattivo design porta a base dati difficili da ottimizzare e spesso poco comprensibili. In questo articolo illustriamo un semplice metodo che rispetta i dettami della teoria relazionale, è facile da capire ed è attivamente usato in produzione.
Autori: Ernesto Ghetti (idea), Giovanni Giorgi (Esposizione e demo trigger)
Supponiamo di avere una tabella di nome "CUSTOMER" e di desiderare storicizzare ogni modifica attuata ai suoi campi, per esempio su base giornaliera. Di norma si è tentati di aggiungere un paio di colonne DATA INIZIO & FINE, per poi accorgersi che fare query su questa tabella per cercare il dato corrente diventa un incubo, soprattutto se magari ci sono altre tabelle correlate. Un altro approccio è usare una tabella CUSTOMER_HISTORY, ma duplicando tutte le tabelle e portando ad un sistema farraginoso di aggiornamento di un gruppo di tabelle correlate.
Partiamo da un approccio diverso, e usiamo come database di riferimento Oracle (nei prossimi articoli vedremo come estenderlo anche ad altri database meno blasonati).
Definiamo una tabella CUSTOMER con una chiave primaria logica NDG ed una chiave primaria fisica XD. Dopodiché definiamo una relazione con la medesima tabella usando come chiave esterna XDS (iDentifer hiStory). Aggiungiamo qualche dato (chessò magari il nome di questo cliente...) e un paio di date per registrare quando il record viene inserito/aggiornato, nonché l'autore (editor) della modifica in oggetto:
CREATE SEQUENCE ID_GEN INCREMENT BY 1 START WITH 1 MINVALUE 1 CACHE 5;
create table CUSTOMER(
xd number not null,
NDG number not null,
xds number,
d_start timestamp,
d_end timestamp,
editor varchar2(40),
name varchar2(80),
Primary key (xd),
CONSTRAINT FK_HISTORY_CUSTOMER
FOREIGN KEY (XDS)
REFERENCES CUSTOMER (XD)
);
Dopodiché definiamo un paio di regole:
- Il record attuale è quello con XD=XDS. Può avere END_DATE=null o ad un valore infinito (es anno 9999)
- Un record storicizzato ha sempre XD > XDS e deve aver definita la END_DATE
Come manipolare i dati
Creare i nuovi record è semplice: si utilizza una sequence e si rispetta la regola (1):INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,ndg,name,editor) values(id_gen.nextval ,id_gen.nextval,sysdate,null,100,'Zeno Parisi','BOT');Per aggiornare un record esistente si usa una modalità a "gambero", e che consiste nel fare prima una copia del dato, e poi modificare quello "corrente":
-- Copy old record XD > XDS INSERT INTO CUSTOMER(XD,XDS,d_start,d_end,editor,name,ndg) select id_gen.nextval,XDS,d_start,sysdate,editor,name,ndg from CUSTOMER where xd=xds and NDG=100; -- Update XD=XDS update CUSTOMER set name='Zeno Paris', editor='GG', d_start=sysdate where xd=xds and ndg=100;Lasciatevi un minuto per "digerire" l'idea.
Per vedere sempre i record attivi si può creare una vista di appoggio:
-- View on active data create view VCUSTOMER as select * from CUSTOMER where xd=xds;
I benefici di questa strategia sono immensi: è semplice trovare tutti i record storici (usando la FK XDS su sé stessi) e si possono avere relazioni esterne stabili (poi vedremo).
Per dimostrare la solidità di questo approccio, vediamo come definire la chiave primaria logica NDG.
Useremo un "function based index" che per fortuna è disponibile in molti database moderni:
-- Forbids different CUSTOMERS with same NDG: create unique index CUSTOMER_UQ_NDG ON CUSTOMER( case when xd=xds then NDG END );
Questo indice garantisce che due record attivi diversi (XD=XDS) non possano avere il medesimo NDG, mentre ignora i record storici, che per costruzione rispetteranno il vincolo nel momento della loro creazione.
Le relazioni esterne
Questo metodo è interessante perché consente di aggiungere relazioni esterne in modo consistente e indipendente. A tal proposito introduciamo la relazione OWNED_SERVICE, che è una relazione molti a uno, in cui un cliente ha zero o più servizi associati, in maniera tale che un servizio sia presente una sola volta per cliente:create table OWNED_SERVICE(
customer_xd number not null,
service_name varchar2(80),
xd number not null,
xds number,
d_start timestamp,
d_end timestamp,
Primary key (xd),
CONSTRAINT FK_HISTORY_OWNED_SERVICE
FOREIGN KEY (XDS)
REFERENCES OWNED_SERVICE (XD),
CONSTRAINT FK_OWNED_SERVICE_CUSTOMER
FOREIGN KEY (customer_xd)
REFERENCES CUSTOMER (XD)
);
-- Dependency relation uniqueness
create unique index OWNED_SERVICE_UQ ON OWNED_SERVICE(
case when xd=xds then customer_xd END,
case when xd=xds then service_name END);
In particolare se i servizi del CUSTOMER non cambiano, la chiave esterna continua a puntare al record "padre" tramite la Foreign key CUSTOMER_XD.
Un record storico può esplorare la relazione da CUSTOMER prendendo un record archiviato e poi navigare XDS->XD<->CUSTOMER_XD per trovare l'OWNED_SERVICE valido al tempo della storicizzazione, incrociando magari le date di validità delle due tabelle.
La seguente query di esempio calcola il "drift", la differenza tra gli aggiornamenti di un servizio e quelli del cliente:
-- Quando è stato registrato il servizio come si chiamava il cliente?
select c.ndg, c.name, c.d_start, s.service_name, s.d_start as service_start, (c.d_start - s.d_start) as drift
from customer c, owned_service s
where ndg=100 and
((c.xd=c.xds and s.customer_xd =c.xd)
or (s.customer_xd=c.xds ) )
order by drift desc ;
100 Zeno Cosimo 06-MAR-21 14:20:36,000000000 AbbonamentoCinema 06-MAR-21 14:12:05,000000000 +00 00:08:31.000000
^^^^ Il cliente è stato modificato dopo la creazione del servizio
100 Zeno Parisi 06-MAR-21 14:09:50,000000000 AbbonamentoCinema 06-MAR-21 14:12:05,000000000 -00 00:02:15.000000
^^^Questo record cliente esisteva prima della creazione del servizio (due minuti prima per l'esattezza)
Questa query ci dice che Zeno Parisi ha comprato un AbbonamentoCinema, e poi ha cambiato nome in Zeno Cosimo otto minuti dopo l'acquisto (e magari dovrebbe rinnovarlo...).
Se cambiamo i servizi, possiamo storicizzare solo la OWNED_SERVICE, mantenendo le due informazioni indipendenti (come è logico che sia).
PRE-UPDATE Trigger
Il seguente trigger (sintassi Oracle 12+) è un semplice esempio per gestire in modo automatico le update, e creare il campo storicizzato.NB: Un trigger di questo tipo è costretto a tenere i record in memoria per funzionare e quindi è una startegia da valutare con cura in caso di situazione in cui sia necessario fare aggiornamenti massivi.
In tali contesti, preferire una implementazione più semplice e "trigger-free".
create or replace TRIGGER AUTO_HISTORY_Customer
FOR UPDATE
ON Customer
COMPOUND TRIGGER
-- See https://www.oracletutorial.com/plsql-tutorial/mutating-table-error-in-oracle/
TYPE t_customer_type IS TABLE OF Customer%ROWTYPE
INDEX BY PLS_INTEGER;
v_data t_customer_type ;
BEFORE EACH ROW IS
BEGIN
IF UPDATING THEN
-- Before update load old data and store it
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATE');
if :old.xds != :old.xd then
RAISE_APPLICATION_ERROR (-20002, '!! HISTORYCAL/INACTIVE RECORDS CANNOT BE UPDATED', TRUE);
END IF;
v_data(v_data.COUNT+1).xd := :old.xd;
v_data(v_data.COUNT).xds := :old.xds;
v_data(v_data.COUNT).d_start := :old.d_start;
v_data(v_data.COUNT).ndg := :old.ndg;
v_data(v_data.COUNT).editor := :old.editor;
v_data(v_data.COUNT).name := :old.name;
END IF;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
if UPDATING then
-- Store copy now
DBMS_OUTPUT.PUT_LINE('AFTER UPDATE');
FOR i IN 1 .. v_data.COUNT
LOOP
v_data(i).xd :=id_gen.nextval;
v_data(i).d_end :=sysdate;
insert into customer values v_data(i) ;
END LOOP;
end if;
END AFTER STATEMENT;
END AUTO_HISTORY_Customer;
/