Un database OLTP (On-Line Transaction Processing), per sua natura, registra le transazioni non conservando memoria alcuna dei cambiamenti avvenuti nel tempo. Non è possibile quindi tracciare la storia di un’entità del database, da cui emergano informazioni circa i mutamenti dei suoi dati nel tempo e ad opera di chi essi siano avvenuti. Intendo riferirmi, quindi, ad una vera e propria operazione di logging attraverso cui risalire, mediante semplici interrogazioni, allo stato di un’entità di database, incluso l’utente di database che lo ha determinato e le sue coordinate temporali. È possibile imbattersi in diverse situazioni in cui assume un’importanza particolare, e talvolta cruciale, la possibilità di risalire al momento esatto e all’individuo, o più genericamente all’utente, che ha fatto scaturire una cancellazione piuttosto che un aggiornamento o un inserimento. Un tale sistema di log può essere implementato sia a livello di applicazione che a livello di database. Questo articolo propone una risposta a questa esigenza, con una soluzione a livello di database implementata in SQL Server 2000 mediante l’uso di trigger.
[ Fattori di rischio ]
Progettare una soluzione di log che registri le transazioni in un database piuttosto che in un formato testo o XML, pone il progettista del sistema di fronte ad alcuni fattori la cui analisi preventiva può risultare fondamentale per la sua buona riuscita. Registrare ogni cambiamento avvenuto nell’arco di vita di una o più entità di un database operazionale, implica il far fronte a costi di manutenzione delle procedure software realizzate a tale scopo - sia a livello di applicazione che a livello di database -, come anche a maggiori costi di manutenzione dei database coinvolti e di ottimizzazione delle performance - si pensi, per esempio, alla memoria su disco necessaria ad ospitare i dati di log e al fatto che ad ogni operazione di inserimento/modifica/cancellazione nel database operazionale ne corrisponde una nel database di log. Sarà necessario valutare l’impatto della procedura di log sulle performance del database operazionale, ed eventualmente considerare una procedura di storicizzazione o di aggregazione dei dati di log i quali potrebbero raggiungere dimensioni su disco considerevoli. Una soluzione che prevede un database come repository dei dati di log - come quella proposta in questo articolo -, fa nascere problematiche simili a quelle che scaturiscono nella progettazione dei più comuni database OLTP, perché sarà soggetto a frequenti scritture. Mi riferisco, per esempio, alla scelta dei campi da indicizzare, che in un database OLTP privilegia i dati il cui tipo occupa quanto meno spazio possibile, allo scopo di rendere più efficienti le operazioni di scrittura; questo perché in un sistema di registrazione delle transazioni saranno di gran lunga più numerose le operazioni di scrittura rispetto a quelle di lettura. Per lo stesso motivo, la creazione di un qualsiasi indice sarà il risultato di una scelta oculata e più che giustificata, e nella manutenzione degli indici si imposterà un FILLFACTOR (fattore di riempimento) ragionevolmente basso. A questo proposito, ricordo che tanto più il fillfactor di un indice - sia esso clustered o meno - è prossimo al 100% tanto meno spazio libero viene riservato nelle pagine di dati dell’indice per i futuri inserimenti. Un valore alto di fillfactor causa frequenti split dei dati dell’indice che incidono negativamente sui tempi di esecuzione di operazioni di scrittura. Per impostare il valore di riempimento basta eseguire il comando di ricostruzione degli indici (DBCC DBREINDEX) specificando il nome del database e opzionalmente il nome dell’indice specifico, per esempio:
DBCC DBREINDEX ('MyDB', my_index_name, 65)
È importante notare che il valore del fillfactor, così come lo stato di frammentazione di un indice, si altera col tempo e si ristabilisce unicamente con la ricostruzione dell’indice stesso. Per approfondire queste ad altre considerazioni circa la progettazione degli indici di SQL Server 2000, rimando all’ottimo eBook disponibile gratuitamente per il download dal sito [1].
[ Un’architettura possibile ]
Supponiamo di voler tracciare il log della tabella [Products] del database di esempio [Northwind] (Figura 1) e di volerlo fare in un database diverso da quello operazionale - quest’ultima scelta potrebbe avere diverse motivazioni che sono fuori dallo scopo di questo articolo, in cui intendo, comunque, dimostrare la sua fattibilità. Innanzitutto, creiamo un nuovo database [NorthwindLog] e disegniamo le tabelle che ospiteranno il log della tabella [Products] (Listato 1): la [Products_InsertedDeletedLog] che registrerà la nascita e morte del prodotto (eventi che coincidono con l’inserimento e la cancellazione) e la [Products_UpdatedLog] che registrerà invece i cambiamenti avvenuti durante la vita del prodotto (che coincidono con l’inserimento e gli aggiornamenti) (Figura 2). (Figura 1)  (Figura2)  Analizziamo in dettaglio la struttura delle due tabelle e la relazione che le lega. Products_InsertedDeletedLog] presenta la chiave primaria surrogata [PKey] - comodamente autogestita attraverso l’autoincremento del tipo di dato IDENTITY -, il [ProductID] che rappresenta la chiave primaria della tabella sorgente [Products], i campi [InsertedUser] e [InsertedDate], in cui saranno registrati rispettivamente l’utente di database e la data e ora dell’inserimento del prodotto, e [DeletedUser] e [DeletedDate], rispettivamente l’utente di database e la data e ora della cancellazione del prodotto. Vi è un ultimo campo [UniqueID] di tipo UNIQUEIDENTIFIER che sarà discusso opportunamente nel seguito dell’articolo. La chiave primaria surrogata [PKey] ha la funzione di identificare univocamente il prodotto, superando il limite rappresentato dalla possibile modifica del valore della chiave primaria della tabella di cui si intende tracciare il log. Questo non è il caso della tabella [Products] del nostro esempio, la cui chiave primaria è di tipo IDENTITY, ma supponendo che [ProductName] fosse stata la chiave primaria e che il suo valore fosse stato modificato per uno o più prodotti, il nostro log avrebbe dovuto tenerne traccia considerandolo però non come un nuovo prodotto, bensì un prodotto già in precedenza creato e a cui si fossero apportate modifiche. La tabella [Products_UpdatedLog] presenta tutti i campi della tabella [Products] con l’aggiunta di una chiave primaria [PKey] di tipo IDENTITY, una chiave esterna [FKey] alla chiave primaria [PKey] di [Products_InsertedDeletedLog], i campi [UpdateUser] e [UpdateTime] in cui saranno registrati l’utente di database e la data e l’ora dell’inserimento/aggiornamento. Quindi, per la buona riuscita dell’operazione di logging, ecco quanto deve avvenire in sintesi: per ogni prodotto inserito in [Produtcs], si crea una riga in [Products_InsertedDeletedLog] che ne registra i dati di nascita (chiave primaria del prodotto, utente e data inserimento), con una chiave primaria surrogata; in [Products_UpdatedLog] si inserisce una riga che riporta lo stato di ogni singolo campo nell’operazione di inserimento; le eventuali successive modifiche a uno qualsiasi dei campi di [Products], producono l’inserimento di una riga in [Products_UpdatedLog] che conserva la relazione con la [Products_InsertedDeletedLog] attraverso la chiave esterna [FKey]; infine, la cancellazione di un prodotto in [Products] comporta l’aggiornamento dei campi [DeletedUser] e [DeletedDate] della riga di [Product_InsertedDeletedLog. [ Usare i trigger ] Prima di analizzare i trigger creati per implementare il sistema di log, è opportuno analizzare un problema, alla cui soluzione si è solo accennato, circa la possibilità di effettuare un inserimento di massa, per esempio:
INSERT INTO [Products] (ProductName, QuantityPerUnit, UnitPrice) SELECT ProductName, QuantityPerUnit, UnitPrice FROM [Products2004]
In un trigger AFTER INSERT sulla tabella [Products], la pseudo-tabella Inserted è costituita da più righe risultanti dalla SELECT. In questo caso, non è possibile risalire al valore del campo [PKey] autogeneratosi con incremento automatico, poiché la pseudo-tabella Inserted è un insieme non ordinato di righe (come ogni altra tabella di un database relazionale). Per il nostro trigger, conoscere il valore assegnato ad ogni [PKey] inserito, è fondamentale al fine di stabilire una relazione tra [Products_InsertedDeletedLog] e [Products_UpdatedLog] attraverso i campi [PKey] e [FKey]. Il problema è stato risolto aggiungendo il campo [UniqueID] di tipo UNIQUEIDENTIFIER nella tabella di origine, con un vincolo di DEFAULT che ne imposta il valore attraverso la funzione NEWID(), il quale genera un identificatore univoco globale (GUID, Globally Unique Identifier):
ALTER TABLE [Northwind].[dbo].[Products] ADD UniqueID UNIQUEIDENTIFIER DEFAULT(NEWID()) GO
Più semplicemente si potrebbe usare un semplice tipo IDENTITY con autoincremento, ma la scelta è caduta sulla prima soluzione per rendere più chiara la distinzione tra il campo [UniqueID] e [ProductID]. Il Listato 2 mostra il trigger AFTER INSERT che alimenta il database di log durante la fase di creazione di un nuovo prodotto. In questo modo, per ogni nuovo prodotto si ha un identificatore univoco rappresentato dal campo [UniqueID] che consente di stabilire una relazione tra le due pseudo-tabelle Deleted e Inserted nel trigger AFTER UPDATE (Listato 3). In esso, si noti come la JOIN tra Deleted e [Products_InsertedDeletedLog] sia costruita sui due campi [UniqueID] delle rispettive tabelle. Per finire, il trigger AFTER DELETE (Listato 4) produce un aggiornamento dei campi [DeleteUser] e [DeleteTime] della riga di [Products_InsertedDeletedLog]. Una vista sul database consente di leggere i dati del log, ottenendo per ogni riga tutte le informazioni utili: data di inserimento, aggiornamento e cancellazione, oltre che lo stato di ogni singolo campo nell’ambito di una operazione di trattamento dei dati.
CREATE VIEW dbo.[ProductsLog_VIEW] AS SELECT TOP 100 PERCENT P_IDL.ProductID AS FirstProductID, P_UL.*, InsertUser, InsertTime, DeleteUser, DeleteTime FROM dbo.Products_InsertedDeletedLog P_IDL INNER JOIN dbo.Products_UpdatedLog P_UL ON P_IDL.PKey = P_UL.FKey ORDER BY P_UL.FKey, P_UL.PKey
[ Conclusioni ]
L’idea di partenza era quella di avere in tempo reale informazioni sullo stato di un’entità del database. Per realizzarla si è pensato all’uso degli AFTER TRIGGER, i quali sono solitamente utilizzati per mantere integri i dati in database normalizzati e non, o per implementare logiche applicative particolarmente complesse [2] [3]. Nel caso affrontato in questo articolo i trigger si sono dimostrati – ancora una volta - un approccio vincente alla manutenzione dei dati. Personalmente ritengo che i trigger siano uno strumento molto versatile, ma da usare con parsimonia poiché inducono ad una naturale proliferazione di procedure di cui è facile perdere il controllo. Inoltre, la loro particolare natura di procedure scatenate in background, talvolta rende il loro comportamento inaspettato agli occhi di un DBA un po’ distratto.
[ Bibliografia ]
[1] D. Jones – "The definitive guide to: SQL Server performance optimization", Realtimepublishers.com, 2003
[2] D. Esposito – "Dati integri e controllati con i trigger", VBJ N.59, 2004
[3] F. Quaratino – "Azioni referenziali con SQL Server 2000", VBJ N.61, 2005
Nota: l'articolo è stato precedentemente pubblicato sul N. 63 di Visual Basic Journal (Mag/Giu '05)
|
©Copyright 2005 - xplayn.org