Nell’articolo Recursive CTE and Bill Of Materials problem abbiamo largamente parlato delle tecnologie che SQL Server mette a disposizione per ottenere l’esplosione scalare di una distinta materiali, ossia per risolvere il problema noto come “The Bill Of Materials problem”.
Successivamente, nell’articolo Recursive CTEs and Bill Of Materials problem (part two: cyclic references) , per la stessa distinta materiali, abbiamo illustrato una soluzione in grado d’individuare riferimenti ciclici (nei dati) con prestazioni ragionevoli. Per rilevare i riferimenti ciclici abbiamo utilizzato una CTE ricorsiva in cui viene calcolato dinamicamente il path di ogni elemento. Nella SELECT ricorsiva abbiamo utilizzato il predicato LIKE per verificare che l’ID del sotto insieme (elemento) che si sta elaborando non sia già contenuto nel path composto con i riferimenti univoci (IDs) dei sotto insiemi (elementi) a livello superiore.
In questo terzo articolo vedremo, invece, come è possibile materializzare il path di ogni elemento utilizzando un nuovo tipo di dato, basato sul linguaggio CLR e chiamato HIERARCHYID.
Il tipo di dato HIERARCHYID, introdotto con SQL Server 2008:
-
Fornisce una rappresentazione interna per la posizione di un elemento in una gerarchia
-
Facilita l’archiviazione, la manutenzione e l’esecuzione di query su dati gerarchici
-
E’ ottimizzato per la rappresentazione di strutture dati ad albero (che sono il tipo più comune di dati gerarchici)
-
E’ disponibile per client CLR come tipo di dati SqlHierarchyId
Una colonna di tipo HIERARCHYID non rappresenta automaticamente un albero, è compito dell’applicazione (o del codice T-SQL racchiuso ad esempio in una stored procedure) generare e assegnare i valori HIERARCHYID in maniera tale che la relazione desiderata tra le righe sia riflessa nei valori.
Il path materializzato, costruito con il tipo HIERARCHYID, differisce dal path calcolato dinamicamente (nel precedente articolo) per due importanti aspetti:
-
Il path calcolato dinamicamente è basato sugli identificativi univoci (IDs) degli elementi, mentre il path materializzato con HIERARCHYID è basato su valori generati internamente
-
Il path calcolato dinamicamente è basato sul tipo di dato carattere (VARCHAR), mentre HIERARCHYID è binario
Il tipo di dato HIERARCHYID fornisce i seguenti metodi e proprietà che aiutano a manutenzionare e interrogare i dati:
-
GetLevel
-
GetRoot
-
GetAncestor
-
GetDescendant
-
GetReparentedValue
-
IsDescendantOf
-
ToString
-
Parse
-
Read
-
Write
Note sul tipo di dato HIERARCHYID:
-
Come gli identificativi di tipo del linguaggio T-SQL, HIERARCHYID è sempre case insensitive, come tutte le parole chiavi T-SQL
-
I nomi dei metodi associati a questo tipo, come ad esempio il metodo GetAncestor(), sono sempre case sensitive!! come un identificativo CLR
-
hierarchyid (lowercase) è tipicamente più portabile di HIERARCHYID (uppercase)
Procediamo con la creazione della tabella Production.BillOfMaterials sul database AdventureWorks, questa tabella servirà per contenere i dati relativi alle distinte materiali. Rispetto alle precedenti implementazioni, si noti che l’attuale struttura consiste di una sola tabella mentre in precedenza avevamo utilizzato due tabelle (testata/righe). Notiamo anche l’assenza della FOREIGN KEY che relazionava testate e righe di dettaglio.
SET NOCOUNT ON;
use [AdventureWorks];
go
— Setup table
if OBJECT_ID(‘Production.BillOfMaterials’, ‘U’) is not null
drop table Production.BillOfMaterials;
go
create table Production.BillOfMaterials
(
BillOfMaterialsID int identity(1, 1) not null primary key nonclustered
,hid hierarchyid not null unique clustered — HIERARCHYID materialized path
,lvl as hid.GetLevel() PERSISTED — Level for materialized path
,ComponentID varchar(20) not null
,ProductQty decimal(8, 2) not null
);
go
create index idx_BOM_lvl_hid on Production.BillOfMaterials
(
[lvl], [hid]
);
go
In aggiunta alla colonna hid (HIERARCHYID), che materializza il path dell’elemento, la tabella Production.BillOfMaterials ha anche una colonna calcolata persistita basata sul metodo GetLevel() applicato alla colonna hid. Come si deduce dal nome, il metodo restituisce il livello del nodo nell’albero, in altre parole la distanza dalla radice.
Per l’inserimento dei dati nella tabella Production.BillOfMaterials utilizziamo la stored procedure dbo.usp_addMaterials_in_BOM() definita di seguito.
— Stored procedure dbo.usp_addMaterials_in_BOM
if OBJECT_ID(‘usp_addMaterials_in_BOM’, ‘P’) is not null
drop procedure dbo.usp_addMaterials_in_BOM;
go
create procedure dbo.usp_addMaterials_in_BOM
(
@ComponentID as varchar(20)
,@ComponentHeader as varchar(20)
,@ProductQty decimal(8, 2)
)
as
begin
/*
Descrizione: Inserisce l’elemento @ComponentID nella distinta materiali
del prodotto @ComponentHeader
*/
declare
@hid as hierarchyid
,@header_id as hierarchyid
,@last_child_hid as hierarchyid
begin transaction
if (@ComponentHeader is null)
— static method (0x)
set @hid = hierarchyid::GetRoot()
else begin
— hierarchyid
set @header_id = (select
hid
from
Production.BillOfMaterials with (updlock)
where
(ComponentID = @ComponentHeader)
)
— last child hierarchyid
set @last_child_hid = (select
MAX(hid)
from
Production.BillOfMaterials
where
(hid.GetAncestor(1) = @header_id)
)
set @hid = @header_id.GetDescendant(@last_child_hid, null)
end
insert into Production.BillOfMaterials
(hid, ComponentID, ProductQty)
values
(@hid, @ComponentID, @ProductQty)
commit transaction
end;
go
L’inserimento di un elemento al vertice della distinta materiali (@ComponentHeader IS NULL) riceverà un valore HIERARCHYID rappresentativo, per la radice, fornito dal metodo statico hierarchyid::GetRoot(). Il ramo ELSE, invece, viene eseguito quando il componente passato nel parametro di input @ComponentID appartiene alla distinta materiali del finito passato nel parametro @ComponentHeader. In quest’ultimo caso per determinare il path del componente, abbiamo utilizzato il metodo GetDescendant() applicato al valore HIERARCHYID relativo al prodotto @ComponentHeader nella cui distinta vogliamo inserire il componente @ComponentID.
Il metodo GetDescendant() accetta in input due parametri di tipo HIERARCHYID e restituisce in output un valore ancora di tipo HIERARCHYID che rappresenta una posizione al di sotto del nodo in cui è applicato il metodo e tra i due nodi (sinistro e destro) passati come parametri di input.
Se entrambi i parametri di input sono nulli (NULL), GetDescendant() genera semplicemente un valore ad di sotto del nodo padre. Se il parametro di sinistra NON è nullo, il metodo genera un valore maggiore del valore passato nel parametro di sinistra. Se il parametro di destra NON è nullo, il metodo genera un valore minore di quello passato nel parametro di destra. Il metodo GetDescendant() non è a conoscenza degli altri valori HIERARCHYID dell’albero, infatti, se chiamiamo il metodo due volte, applicato sullo stesso valore HIERARCHYID con gli stessi parametri di input, riceveremo in entrambi i casi lo stesso risultato; sarà quindi responsabilità dell’applicativo (o del codice T-SQL) evitare conflitti. Per garantire valori HIERARCHYID distinti per ogni processo il codice T-SQL nella della stored procedure dbo.usp_addMaterials_in_BOM() è stato racchiuso all’interno di una transazione ed è stato specificato l’hint UPDLOCK.
Utilizziamo la stored procedure dbo.usp_addMaterials_in_BOM() per inserire la distinta materiali (dimostrativa) del prodotto “Mountain Bike”.
— Insert data
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Mountain Bike’, @ComponentHeader = null, @ProductQty = 0;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Manubrio’, @ComponentHeader = ‘Mountain Bike’, @ProductQty = 1;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Sella’, @ComponentHeader = ‘Mountain Bike’, @ProductQty = 1;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Gruppo Ruote’, @ComponentHeader = ‘Mountain Bike’, @ProductQty = 1;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Gruppo Luci’, @ComponentHeader = ‘Mountain Bike’, @ProductQty = 2;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Luci Anteriori’, @ComponentHeader = ‘Gruppo Luci’, @ProductQty = 1;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Supporto Luci-A’, @ComponentHeader = ‘Luci Anteriori’, @ProductQty = 1;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Ruota anteriore’, @ComponentHeader = ‘Gruppo Ruote’, @ProductQty = 1;
exec dbo.usp_addMaterials_in_BOM
@ComponentID = ‘Ruota posteriore’, @ComponentHeader = ‘Gruppo Ruote’, @ProductQty = 1;
go
Consultiamo i dati inseriti con il seguente comando T-SQL, l’output è illustrato in Figura 1.
hid
,hid.ToString() as path
,lvl
,ComponentID
from
Production.BillOfMaterials
order by
hid;
go
Figura 1 – Distinta materiali del prodotto “Mountain Bike”
Il metodo ToString() restituisce la rappresentazione canonica del percorso necessario per raggiungere un determinato elemento (distanza dalla radice). Per dividere i valori, ad ogni livello, il metodo ToString() utilizza il separatore slashes (“/”), la radice è quindi rappresentata da “/” (stringa binaria vuota). L’output in Figura 1 lascia intuire la logica applicata dal metodo GetDescendant() per calcolare i valori: il primo figlio al di sotto del nodo HIERARCHYID su cui applichiamo il metodo GetDescendant() con entrambi i parametri di input impostati a NULL avrà un percorso formato dalla radice a cui verrà aggiunto il percorso “1/”.
Il tipo HIERARCHYID supporta un metodo chiamato GetReparentedValue() che ci aiuta a calcolare il nuovo path di un elemento quando c’è la necessità di spostare un intero ramo (sottoalbero) da una posizione ad un’altra. Il metodo si applica al valore HIERARCHYID del nodo che si vuole riposizionare. Il metodo non fornisce l’attuale riposizionamento, ma semplicemente restituisce un valore che si può utilizzare per sovrascrivere un path esistente. Il metodo accetta in input due parametri (@old_root e @new_root) e restituisce in output un nuovo valore in funzione del valore HIERARCHYID su cui è applicato, dove il prefisso @new_root sostituisce il prefisso @old_root.
La seguente stored procedure dbo.usp_MoveSubtree_Materials_in_BOM() utilizza il metodo GetReparentedValue() per spostare un ramo (sottoalbero) da una posizione ad un’altra. La stored procedure accetta in input due parametri: @ComponentID e @New_ComponentID. Il primo rappresenta il componente (elemento, nodo) che si desidera spostare, mentre il secondo rappresenta il componente (elemento, nodo) ad disotto del quale posizionare il ramo dell’elemento spostato.
— Stored procedure dbo.usp_MoveSubtree_Materials_in_BOM
if OBJECT_ID(‘usp_MoveSubtree_Materials_in_BOM’, ‘P’) is not null
drop procedure dbo.usp_MoveSubtree_Materials_in_BOM;
go
create procedure dbo.usp_MoveSubtree_Materials_in_BOM
(
@ComponentID as varchar(20)
,@New_ComponentID as varchar(20)
)
as begin
/*
Descrizione: Spostamento di un sottoalbero da una
posizione ad un’altra
*/
declare
@old_root as hierarchyid
,@new_root as hierarchyid
,@new_ComponentID_hid as hierarchyid
begin transaction
set @new_ComponentID_hid = (select
hid
from
Production.BillOfMaterials with (updlock)
where
(ComponentID = @New_ComponentID)
)
set @old_root = (select
hid
from
Production.BillOfMaterials
where
(ComponentID = @ComponentID)
)
— Richiesta di un nuovo hid per il subtree da muovere
set @new_root = @new_ComponentID_hid.GetDescendant
((select
MAX(hid)
from
Production.BillOfMaterials
where
(hid.GetAncestor(1) = @new_ComponentID_hid)), null)
— Riposiziono tutti i materiali che derivano dal subtree da spostare
update
Production.BillOfMaterials
set
hid = hid.GetReparentedValue(@old_root, @new_root)
where
hid.IsDescendantOf(@old_root) = 1;
commit transaction
end;
go
Ipotizziamo ora di voler spostare l’intero ramo relativo al “Gruppo Luci”, della distinta del prodotto “Mountain Bike”, al disotto dell’elemento “Gruppo Ruote” (anche se nella realtà non ha molto senso ). Il seguente frammento di codice T-SQL utilizza la stored procedure dbo.usp_MoveSubtree_Materials_in_BOM(). Il commit delle modifiche non viene eseguito per consultare i dati prima e dopo lo spostamento del ramo.
— Query
select
bom.BillOfMaterialsID
,(REPLICATE(‘ | ‘, bom.lvl) + bom.ComponentID) as ComponentID
,bom.hid.ToString() as path
from
Production.BillOfMaterials bom
order by
bom.hid
go
— Spostamento del ramo
begin tran
exec dbo.usp_MoveSubtree_Materials_in_BOM
@ComponentID = ‘Gruppo Luci’
,@New_ComponentID = ‘Gruppo Ruote’;
select
bom.BillOfMaterialsID
,(REPLICATE(‘ | ‘, bom.lvl) + bom.ComponentID) as ComponentID
,bom.hid.ToString() as path
from
Production.BillOfMaterials bom
order by
bom.hid
rollback tran
go
Otteniamo l’output illustrato in figura 2.
Figura 2 – Spostamento del ramo relativo all’elemento “Gruppo Luci”
Conclusioni
Interrogare una struttura ad albero, come può essere una distinta materiali, in una soluzione in cui il path è stato materializzato sulla base del tipo di dato HIERARCHYID, è semplice ed elegante.
La query riportata di seguito restituisce la distinta materiali esplosa del prodotto "Mountain Bike" limitando l’output al secondo livello. Per questa interrogazione si utilizza il metodo IsDescendantOf() che restituisce il valore 1 se il nodo su cui viene applicato il metodo discende dal nodo passato in input, restituisce 0 altrimenti.
La query collega due istanze della stessa tabella Production.BillOfMaterials, un’istanza (alias h) è composta solo dalla riga relativa al prodotto “Mountain Bike”, mentre l’altra (alias d) è composta da tutti i componenti che discendono dal prodotto “Mountain Bike”. La clausola WHERE filtra i componenti in modo che vengano restituiti solo i componenti fino al secondo livello (compreso).
d.ComponentID
,d.lvl as level
from
Production.BillOfMaterials h
join
Production.BillOfMaterials d on h.ComponentID = ‘Mountain Bike’
and d.hid.IsDescendantOf(h.hid) = 1
where
(d.lvl – h.lvl) <= 2
Figura 3 – Distinta materiali esplosa del prodotto "Mountain Bike" (fino al secondo livello)
Pulizia DB
/*
Pulizia DB
*/
if OBJECT_ID(‘Production.BillOfMaterials’, ‘U’) is not null
drop table Production.BillOfMaterials;
go