Temporal tables |
  
|
Temporal tables |
|
Le Temporal Tables, o SYSTEM-VERSIONED Temporal Tables, permettono di mantenere una cronologia completa delle modifiche effettuate sui dati in essa presenti, tramite un aggiornamento automatico da sistema.
L'utilizzo delle Temporal Tables necessita che i Database di IrionEDM abbiano installato SQL Server 2016. |
Sono contraddistinte da
✓ un PERIODO di validità, definito da due campi DATAFrom, DataTo di tipo datetime2;
✓ da un campo Period, definito come FOR SYSTEM_TIME sui due campi definiti nel punto precedente.
Ogni Temporal Table è implementata tramite due tabelle fisiche (devono essere conformi in schema)
✓ Main (o current) Table: contiene le versioni attuali (effettive) delle righe;
✓ History Table: contiene la cronologia.
La definizione di una Temporal Table in IrionEDM avviene principalmente in due diversi modi:
✓ creando la tabella direttamente come System_Versioned. Lo script creerà sia la Main sia la History Table già con il System_Versioned attivo.
✓ convertendo una tabella già esistente, considerata come Main, alla quale verrà associata una altrettanto già esistente tabella, considerata come History.
Per creare la tabella in modalità System_Versioning ON, occorre utilizzare un comune script T-SQL di CREATE TABLE con in aggiunta i campi:
✓ ValidFrom / ValidTo
✓ Period
ValidFrom / ValidTo devono avere le seguenti caratteristiche:
✓ datatype DATETIME2
✓ GENERATED ALWAYS AS ROW START / END
✓ NOT NULL
✓ HIDDEN (opzionale)
Se i campi sono già presenti in tabella, occorre effettuare su di essi una operazione di CONVERT di tipo Datetime2.
La clausola Hidden è opzionale. La dichiarazione esplicita permette di nascondere le nuove colonne a meno dell'esplicito richiamo nella query di interrogazione.
Period si definisce utilizzando la clausola FOR SYSTEM_TIME (ValidFrom , ValidTo)
Occorre, inoltre, inserire in fondo allo script la clausola:
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = <schema_name>.<nome_temptable>History));
di cui:
✓ Schema_name: corrisponde all'identificativo univoco del databox in cui si vuole creare la tabella;
✓ HistoryTable: indica la tabella di cronologia in cui si vogliono storicizzare le modifiche apportate sulla Main Table.
Lo Schema_name viene recuperato tramite la funzione di IrionEDM SCHEMA_NAME() richiamata all’interno di un DataBoxLink.
La clausola ALTER TABLE SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = <schema_name>.<nome_temptable>History)) non può essere utilizzata in IrionEDM perchè non si possiedono particolari permessi.
La Main Table deve avere sempre una Primary Key,dunque i campi in chiave devono essere NOT NULL. |
Per convertire una tabella già esistente in una Temporal Table occorre:
✓ Effettuare una operazione di ALTER TABLE – ADD nella Main Table per aggiungere, se non già presenti, i campi ValidFrom e ValidTo e il PERIOD. I campi dovrano necessariamente avere tutte le caratteristiche descritte in precedenza;
✓ Effettuare una operazione di ALTER TABLE – ADD nella History Table per aggiungere i campi ValidFrom e ValidTo definiti con un default e NOT NULL;
✓ Utilizzare la seguente Stored Procedure [idq].[Changesystemversioningstatus] per abilitare il SYSTEM_VERSIONING:
EXEC [idq].[Changesystemversioningstatus]
@tableName = '<nome_table>',
@logTableName = '<nome_temptableHistory>',
@enable = 1
La [idq].[Changesystemversioningstatus] è una Stored Procedure che agisce su Temporal Table già create. |
La stored procedure riceve, in input, i seguenti parametri:
1. @tableName varchar(256): nome della Temporal (Main) Table;
2. @logTableName varchar(256): nome della History Table;
3. @enable bit: attiva (1) o disattiva (0) il SYSTEM_VERSIONING;
4. @retentionPeriod varchar(128): indica per quanto tempo si vuole mantenere la storicizzazione di un dato;
5. @dataConsistencyCheck bit: attiva (1) o disattiva (0) il controllo di consistenza tra la Main e la History Table.
I parametri @retentionPeriod e @dataConsistencyCheck sono opzionali. Di defaut, @retentionPeriod è illimitato, @dataConsistencyCheck è attivo. |
Per effettuare l’operazione opposta, ossia convertire una Temporal Table in una non dotata di controllo di versione, occorre:
✓ Disabilitare il SYSTEM_VERSIONING richiamando la Stored Procedure [idq].[Changesystemversioningstatus] con parametro @enable bit = 0;
✓ Droppare la colonna PERIOD FOR SYSTEM_TIME;
✓ Droppare, se necessario, anche le colonne di Periodo.
Prima di poter effettuare la conversione da Temporal Table in Main Table è necessario verificare se vi è qualche vincolo di default sulle colonne Required o il SYSTEM_VERSIONING attivo sulla Temporal Table. Nel caso ci fossero, occorre eliminare i vincoli e spegnere il SYSTEM_VERSIONING prima di fare DROP delle colonne. |
Le seguenti operazioni sono consentite su Temporal Table:
✓ INSERT: per inserire nuovi record nelle Temporal Table possono essere utilizzati i comuni script T-SQL di INSERT e l’utente può omettere di valorizzare i campi del PERIOD (ValidFrom, ValidTo);
✓ UPDATE: l’operazione di aggiornamento di record avviene secondo i comuni script T-SQL di UPDATE Table;
✓ DELETE: l’operazione di cancellazione di record avviene secondo i comuni script T-SQL di DELETE Table;
✓ TRUNCATE: l’operazione non è permessa se il SYSTEM_VERSIONIG è attivo;
✓ MERGE: in questo caso l’intera operazione si comporta come se venissero eseguiti tre statement (INSERT, UPDATE, DELETE) in base all’azione specificata;
✓ SELECT: l’operazione di interrogazione di una tabella avviene secondo i comuni script T-SQL di SELECT ma con l’aggiunta della clausola FOR SYSTEM_TIME, che permette di escludere le righe con periodo di validità pari a 0 (ValidFrom = ValidTo).
Per la INSERT, il sistema li setta AUTOMATICAMENTE come:
✓ ValidFrom: con l’inizio della transazione, basato su un sistama di clock.
✓ ValidTo: con il valore di DEFAULT “9999-12-31T23:59:59.00000”
Il clock utilizzato dal sistema per aggiornare le colonne del PERIOD a seguito di cambiamenti utilizza il fuso orario UTC. La conversione al fuso corretto deve avvenire a carico dell’utente. Alcune funzioni utili al recupero dei fusi orari e alla convesione sono AT TIME ZONE '<nome_timezone>'o DATEPART(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|TZOFFSET, AT TIME ZONE '<nome_timezone>') |
Per la UPDATE, il sistema storicizza i valori precedentemente presenti della riga nella History Table e setta:
✓ ValidTo (History Table): con l’inizio delle transazione corrente, basato su un sistema di clock, determinando il nuovo periodo di validità della riga;
✓ ValidFrom (Main Table): con l’inizio delle transazione corrente, basato su un sistema di clock, determinando il nuovo periodo di validità della riga;
✓ ValidTo (Main Table): con il valore di DEFAULT “9999-12-31T23:59:59.00000”.
Per la DELETE, il sistema storicizza i valori precedentemente presenti della riga nella History Table e setta:
✓ DataTo (History Table): con l’inizio delle transazione corrente, basato su un sistema di clock;
✓ nella Main Table la riga viene rimossa.
La clausola FOR SYSTEM_TIME, usata nella SELECT viene accompagnata da da cinque sotto-clausole temporali:
✓ ALL: se occorre eseguire query sui dati correnti e cronologici senza restrizioni;
✓ AS OF: se occorre ricostruire lo stato dei dati esistente in un momento specifico nel passato;
✓ BETWEEN ... END, FROM, CONTAINED IN: se occorre recuperare tutte le modifiche avvenute per una riga specifica nella tabella corrente.
ESEMPIO DI SCRIPT DI CREAZIONE DI UNA TEMPORAL TABLE Per creare una Temporal Table con l'opzione SYSTEM_VERSIONING attiva, si può procedere nel modo seguente:
CREATE TABLE <nome_temptable> , CONSTRAINT pk_<nome_temptable> PRIMARY KEY (name1, name2, name3)
|