Home > Articoli > Bill Of Materials problem (part three: using HIERARCHYID data type)

Bill Of Materials problem (part three: using HIERARCHYID data type)

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.

select
  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).

select
  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

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …