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.

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.

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”.

Consultiamo i dati inseriti con il seguente comando T-SQL, l’output è illustrato in Figura 1.

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.

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.

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

Figura 3 – Distinta materiali esplosa del prodotto "Mountain Bike" (fino al secondo livello)

Pulizia DB