Talvolta sul ng appaiono richieste relative alla possibilità di archiviare le modifiche subite da un record.
Le strategie tradizionali per la soluzione di questo problema richiedono tipicamente la duplicazione delle tabelle e la creazione di strutture complesse che diminuiscono di molto la manutenibilità del db.
Qualche giorno fa
Lorenzo ha proposto sul ng l'uso del tipo dato xml di sql 2005 per ottenere il riultato.
Lo script che segue è un tentativo di mettere in atto questa idea. Si tratta ancora di un abbozzo ma sembra promettente. Le strutture in gioco sono denormalizzate ma il tipo di dato archiviato in molti scenari può sopportare questa situazione. Non essendo un gran conoscitore di xml e xpath è assolutamente probabile che ogni strategia utilizzata sia migliorabile di molto. Ogni consiglio è bene accetto. :-D
use tempdb
set nocount on
go
/*Tabella di cui si richiede la storicizzazione delle modifiche*/
create table test(Id uniqueidentifier rowguidcol primary key,a int, b int, c varchar(100))
/*Tabella di storico delle modifiche Type vale 0:Inserimento, 1:Modifica,2:Eliminazione*/
create table history(Id bigint identity primary key,Data xml,Pk uniqueidentifier,Date datetime, [Type] tinyint)
go
/*Procedura di aggiunta riga in History*/
create procedure p_Histrory_Save(@Data xml,@Pk uniqueidentifier,@Type tinyint)
as
insert into History values(@Data,@Pk,getdate(),@Type)
go
/*Procedura tipo di Modifica Test*/
create procedure p_Test_Save(@Id uniqueidentifier output,@a int, @b int, @c varchar(100))
as
/*Variabile locale per valutare la presenza o meno di modifiche [reali o meno!]*/
declare @xd int
set @xd=3
if @Id is null
begin
/*Su Chiave nulla Insert*/
set @Id=newid()
insert into test values(@Id,@a,@b,@c)
end
else
begin
/*Update, la union tra i dati attuali e i passati permette di valutare se l'update è reale o no*/
select @xd=count(*) from(
select * from test where id=@ID
union
select @id,@a,@b,@c)v
end
/*A questo punto @xd vale:
3 -- Inserimento
2 -- Update reale
1 -- Update farlocco [Identici valori]*/
if @xd>1
begin
/*Variabile documento xml*/
declare @xxml xml
/*Recupero dei vecchi dati in modalità raw con nome tag='data'*/
set @xxml=(select * from test where id=@ID for xml raw('data'))
set @xd=3-@xd
/*Con questo giochino @xd vale [e coincide con il type di History:
0 -- Inserimento
1 -- Update reale*/
/*Su update reale o inserimento, storicizzazione*/
exec p_Histrory_Save @xxml,@id,@xd
end
/*Attenzione qui @xd vale:
0 -- Inserimento
1 -- Update reale o farlocco*/
/*su update [anche farlocco], aggiornamento*/
if @xd>0 update test set a=@a,b=@b,c=@c where id=@id
go
/*Test di utilizzo*/
declare @Id uniqueidentifier
/*Creazione record*/
exec p_Test_Save @Id output,12, null, ''
/*Update*/
exec p_Test_Save @Id,12, 3, 'Pippo'
/*Update Farlocco*/
exec p_Test_Save @Id,12, 3, 'Pippo'
/*Update*/
exec p_Test_Save @Id,12, 4, 'Pluto'
/*Update Farlocco*/
exec p_Test_Save @Id,12, 4, 'Pluto'
go
/*Dati di test*/
select * from test
/*Dati storicizzati*/
select * from history
go
/*Test recupero dati storicizzati*/
declare @dh int,@xml xml
/*Recupero il xml relativo alla storicizzazione di id=2*/
select @xml=Data from History where id=2
/*Preparazione del documento*/
exec sp_xml_preparedocument @dh output, @xml
/*Lettura dei dati storici*/
select * from openxml(@dh, N'/data') with test
/*Chiusura del documento*/
EXEC sp_xml_removedocument @dh
go
/*Pulizia*/
drop procedure p_Test_Save,p_Histrory_Save
drop table test,history
La scelta di eseguire l'update anche in caso di aggiornamento "farlocco" nasce dal fatto che la modifica di un valore letterale da 'xx' a'xx ' viene interpretato dalla stored utilizzata come farlocco pur trattandosi tecnicamente di un aggiornamento a tutti gli effetti. A seconda degli scenari si possono ovviamente adottare strategie precise più adatte.
marc.