Hystrix


Il mondo è la totalità dei fatti non delle cose.
Archivio Posts
Anno 2018

Anno 2016

Anno 2015

Anno 2009

Anno 2008

Anno 2007

Anno 2006
Statistiche
  • Views Home Page: 56.012
  • Views Posts: 105.659
  • Views Gallerie: 0
  • n° Posts: 41
  • n° Commenti: 84

Storicizzazione dei dati con SQL Server 2005

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.
Categoria: SQL Server
mercoledì, 21 feb 2007 Ore. 22.26
Calendario
agosto 2019
lmmgvsd
2930311234
567891011
12131415161718
19202122232425
2627282930311
2345678
Ora e Data
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003