System Versioned Tables

data

7/8/2020

data progetto

autore

Orbyta Tech

cliente

partnership

url

No items found.

Le tabelle con controllo delle versioni di sistema, spiegate col Signore degli Anelli

Se entraste in una stanza piena di informatici e domandaste ad alta voce: “Chi di voi non ha mai cancellato dei dati per sbaglio?”, contando le mani alzate avreste una stima piuttosto accurata di quanti bugiardi avete di fronte.

Nei miei anni di lavoro su SQL Server, ammetto che mi è capitato più di una volta, per distrazione, per una convinzione errata, o per avere scritto male una condizione, di lanciare un comando che ha distrutto o modificato molti più record di quanto fosse la mia intenzione. Un'azione del genere spalanca prospettive raramente rosee, ma con gradi di gravità che possono distribuirsi su un ventaglio molto ampio: dalla semplice seccatura di doversi inventare 10 nuovi casi in ambiente di sviluppo, allo scenario da incubo di avere destinato all'oblio migliaia di righe in produzione.

Non è scopo di questo articolo una disamina approfondita sulle buone pratiche di backup di un DB, ma vorrei raccontarvi di uno strumento messo a punto su Management Studio, attivo dalla versione 2016, che può rivelarsi molto utile non solo in caso di emergenza, ma anche nella gestione abituale di qualche tabella cruciale di cui si vuole mantenere o analizzare la storia nel tempo.

L’argomento di cui vorrei parlare in questo articolo riguarda le "Temporal Tables" oppure "System-Versioned Tables"; tra le due nomenclature la mia simpatia vira con decisione verso la seconda, dato che la prima (benché sia la più usata) potrebbe generare confusione con le tabelle cancelletto (Temporary Tables), nate con una funzione completamente diversa. La dicitura più esaustiva e corretta sarebbe "System-Versioned Temporal Tables" non immune da una certa prolissità, ma d'ora in poi le chiamerò “tabelle con controllo delle versioni di sistema”, come riportato nella versione italiana di SSMS.

Partiamo con la definizione nuda e cruda:

Una tabella con controllo delle versioni di sistema è un tipo di tabella definita dall'utente, progettata per mantenere una storia completa dei cambiamenti sui dati e permettere una facile gestione nell'analisi di questi cambiamenti nel tempo. Questo tipo di tabella è detta “System-Versioned” perché il periodo di validità di ciascuna riga è gestito dal sistema (cioè dal motore di database).

Per entrare subito in medias res, fornirò un esempio pratico dell'utilizzo di questa funzionalità.

Qui di seguito ho riportato lo script di creazione di una tipica tabella con controllo delle versioni di sistema

Se fino alla quinta riga la sintassi è del tutto abituale, quelle successive aprono scenari molto più interessanti; questo script infatti non si limita a creare una sola tabella, ma due, strettamente collegate tra di loro. La prima è la tabella principale (nel nostro caso la dbo.LOTR), dal comportamento del tutto simile a una normalissima tabella, la seconda è la tabella dello storico (la dbo.LOTRHistory, definita a riga 9 dello script), nella quale sarà registrata la cronologia di tutti i cambiamenti operati nel tempo sulla tabella principale.

Senza specificare un nome personalizzato per la tabella dello storico, SQL Server avrebbe generato automaticamente un nome del genere: dbo.MSSQL_TemporalHistoryFor_xxx, con l’id dell’oggetto al posto di xxx. Suppongo sia meglio scegliersi il nome da soli.

Lo stretto legame tra le due tabelle si può riscontrare facilmente guardando l’esplora oggetti.

Le colonne della tabella dbo.LOTRHistory, creata automaticamente dallo script, saranno (per nome e per tipo), esattamente uguali a quelle della dbo.LOTR, ma senza nessuno dei vincoli originali (chiave primaria, chiavi esterne etc.); anche gli indici e le statistiche non discendono dalla tabella principale e potranno essere gestiti in modo del tutto indipendente.

Che non siano tabelle normali lo si può riscontrare anche osservando che, nel menu di entrambe, non è prevista l’opzione “Elimina”.

Se volessimo eliminare una delle due entità sarebbe necessario utilizzare questo comando per svincolarle dal versionamento.

Un’altra caratteristica essenziale per la creazione di una tabella con controllo delle versioni di sistema è la presenza di due colonne di tipo datetime2, cioè la SysStartTime e la SysEndTime (righe 6 e 7 dello script di creazione), il cui contenuto è infatti gestito direttamente dal sistema; questa loro particolarità ci impedirà di apportare qualunque intervento o modifica su di loro. Grazie a queste due colonne sarà possibile tenere traccia del momento preciso in cui un record della tabella dbo.LOTR ha subìto una variazione di qualche tipo.

Ma procediamo con il nostro esempio pratico, inserendo un po’ di dati all’interno della tabella principale. La popoleremo con i personaggi del romanzo “Il Signore degli Anelli”, di Tolkien, e la qualifica che hanno all’inizio del libro

Proviamo a fare una select della tabella e vediamo cosa restituisce.

Finora non sembrano esserci particolari soprese, anche se avrete notato che le due colonne SysStartTime e SysEndTime, pur non essendo state valorizzate esplicitamente nella insert, hanno assunto rispettivamente i valori della data in cui è stato effettuato l’inserimento, e della data più grande disponibile nel formato datetime2, ovvero: 9999-12-31 23:59:59.9999999.

Proviamo a vedere cosa accade se eseguiamo un’operazione di update sui personaggi del nostro elenco che diventeranno parte della Compagnia dell’Anello, aggiornando la loro qualifica con il valore “Membro della compagnia dell’Anello”.

Se interroghiamo la tabella principale, troviamo esattamente quello che possiamo aspettarci da un’operazione compiuta su una qualsiasi tabella della nostra base dati, ovvero:

È immediato notare che non è cambiata solo la qualifica dei membri interessati, ma anche la loro SysStartTime, che riporta l’ora della modifica. Il record relativo a Saruman, non essendo coinvolto dall’update, ha mantenuto invariata la SysStartTime di prima, corrispondente al momento della sua creazione.

Se non ci interessa visualizzare il valore delle colonne SysStartTime e SysEndTime a ogni select, sarà sufficiente dichiarare “hidden” le due colonne in questione nello script di creazione iniziale.

La faccenda si fa più interessante se guardiamo cosa è successo nella tabella dbo.LOTRHistory, di cui per semplicità tireremo fuori solo i risultati relativi a Boromir.

Dato che la tabella dello storico contiene solo i dati relativi a ciò che c’era nel passato, troveremo un unico record, corrispondente al valore della ormai obsoleta qualifica di Boromir, con data SysEndTime identica alla SysStartTime assegnata allo stesso personaggio sulla tabella dbo.LOTR.

Ovviamente, se cercheremo di ripetere la stessa query sulla dbo.LOTRHistory per Saruman, che non ha subìto modifiche dopo essere stato creato, non troveremo nessun record.

La cronologia sulla dbo.LOTRHistory terrà traccia anche di tutti i cambiamenti successivi, tant’è vero che se eseguo un nuovo update su Boromir, una select su dbo.LOTR restituirà il valore aggiornato come avrebbe fatto una normalissima tabella.

Nella dbo.LOTRHistory, a questo punto, troveremo due record: uno per ciascuno degli stati precedenti di Boromir.

In questo frangente si capisce come mai la tabella dbo.LOTRHistory non abbia gli stessi vincoli della sua gemella: due cambiamenti sul record con Id = 1 nella tabella dbo.LOTR portano ad avere due record con Id = 1 sulla tabella dello storico, condizione che avrebbe generato un errore di violazione di chiave se la dbo.LOTRHistory avesse ereditato la PK dalla tabella principale.

È anche facile notare come tutti i cambiamenti consecutivi registrati nella dbo.LOTRHistory abbiano la SysEndTime del record più vecchio coincidente con la SysStartTime del nuovo, proprio come la SysEndTime del nuovo coinciderà con la SysStartTime del valore corrispondente sulla tabella principale.

Se conoscete il Signore degli Anelli, saprete che alla fine del primo episodio Boromir passa a miglior vita, per questo motivo saremo costretti a cancellarlo dalla nostra tabella dbo.LOTR.

Il risultato che otteniamo dopo questa operazione non è molto diverso da quanto possiamo aspettarci, cioè la tabella principale non avrà più nessun record relativo a Boromir, ma la sua tabella dello storico dbo.LOTRHistory ospiterà tutte le sue precedenti incarnazioni.

Finora abbiamo incontrato una netta separazione tra presente e passato; o abbiamo eseguito una query sulla dbo.LOTR per avere un quadro della situazione al momento attuale, o abbiamo eseguito una query sulla dbo.LOTRHistory, per ottenere lo scenario completo sugli stati passati di uno dei personaggi. Verrebbe da chiedersi se esiste un modo per avere una visione d’insieme di tutti gli stati, quelli passati e quello attuale; la risposta ovviamente è sì, e consiste nell’eseguire una query sulla dbo.LOTR utilizzando la clausola FOR SYSTEM_TIME ALL.

Dato che Boromir non è più nella tabella principale, faremo la prova con un altro personaggio ancora attivo, cioè Sam.

È chiaro che, nella storia di ciascun personaggio, possiamo sempre riconoscere come attualmente valido il solo e unico record che reca come data nella SysEndTime il valore 9999-12-31 23:59:59.999999; nel caso di valori cancellati, invece (come per Boromir), nessuna SysEndTime assumerà tale valore, essendo la SysEndTime con data più alta quella corrispondente al momento dell’eliminazione del record.

Ci sono in tutto cinque possibili condizioni per la clausola FOR SYSTEM_TIME, che ci consentono una buona flessibilità al fine di ottenere le informazioni che vogliamo rispetto al tempo, vale a dire:

ALL
AS OF
FROM TO
BETWEEN AND
CONTAINED IN ( , )

Scorrendo velocemente il loro utilizzo abbiamo che la AS OF dà l’immagine del dato in un preciso istante nel tempo. Sapendo che il personaggio di Boromir è stato creato alle 8:56 della mattina del 2 Agosto 2020 con il titolo di Capitano di Gondor e modificato più di tre ore dopo, non è sconvolgente scoprire che il risultato della query che ne richiede lo stato alle 8:57 restituisce la sua qualifica iniziale.

La FROM … TO e la BETWEEN hanno sostanzialmente lo stesso scopo, cioè quello di fornire tutta la storia di un record in un determinato intervallo di date

La sottile differenza tra le due è data dal fatto che la BETWEEN mostrerà anche un cambiamento il cui inizio coincide esattamente con l’estremo superiore dell’intervallo di date scelto. Abbiamo perciò che il cambiamento di stato di Boromir a “Membro poco convinto della Compagnia dell’Anello” avvenuto in questa data: 2020-08-02 12:55:23.3687849, a parità di intervalli sarà mostrato solo dalla BETWEEN e non dalla FROM … TO.

Da ultima, la CONTAINED si limita a restituire i periodi contenuti interamente (quindi iniziati e conclusi) nell’intervallo di tempo specificato

Per avere un promemoria del funzionamento di queste clausole, trovo sempre utile aiutarmi con una rappresentazione grafica; in questo caso i quattro stati rappresentati racchiudono tutta la storia degli stati, compreso quello in corso (se esiste).

Conclusioni

Le tabelle con controllo delle versioni di sistema sono uno strumento molto utile non solo per recuperare dati cancellati inavvertitamente da tabelle sensibili (una sorta di backup tabellare), ma anche per ricostruire la storia di un determinato dato e il suo andamento nel tempo senza dover ricorrere a procedure, trigger o altri mezzi dalla manutenzione macchinosa. Sicuramente, data la grande mole di informazioni che può accumularsi nel tempo (ad es. una tabella con molte colonne che viene aggiornata spesso), può valere la pena di considerare qualche meccanismo di pulizia periodica di tali tabelle; è però importante tenere a mente che questo strumento è stato creato pensando ad una conservazione del dato per lunghi periodi, e che un uso intelligente di indici e statistiche sulla tabella dello storico (indipendenti dalla tabella principale) può aumentarne considerevolmente l’efficienza senza dover ricorrere a misure che rischierebbero di snaturarne lo scopo.

Script con il codice usato nell’articolo: https://bit.ly/2PvKyMo