Utilizzo di dati hierarchyid
In questo argomento verranno incluse informazioni sulle attività comuni di gestione ed esecuzione di query su un albero dei dati gerarchici.
In questo argomento
Gestione di un albero utilizzando hierarchyid
Applicazione di un albero
Esempio utilizzando CLR
Spostamento di sottoalberi
Gestione di un albero utilizzando hierarchyid
Un'applicazione può assicurarsi facilmente che una colonna hierarchyid rappresenti un albero anche se ciò non accade necessariamente.
Durante la generazione di nuovi valori, eseguire una delle operazioni seguenti:
Monitorare l'ultimo numero figlio nella riga padre.
Calcolare l'ultimo elemento figlio. Ciò richiede un indice breadth-first.
Applicare l'univocità creando un indice univoco sulla colonna, come parte di una chiave di clustering. Per garantire che vengano inseriti valori univoci, eseguire una delle operazioni seguenti:
Determinare l'univocità di ogni nodo figlio nuovo e inserirlo in una transazione serializzabile.
Rilevare errori di violazione di chiave univoca e riprovare.
Esempio di utilizzo del rilevamento degli errori
Nell'esempio seguente, il codice di esempio calcola il nuovo valore EmployeeID figlio, rileva quindi qualsiasi violazione di chiave e torna all'indicatore INS_EMP per ricalcolare il valore EmployeeID per la riga nuova:
USE AdventureWorks ;
GO
CREATE TABLE Org_T1
(
EmployeeId hierarchyid PRIMARY KEY,
OrgLevel AS EmployeeId.GetLevel(),
EmployeeName nvarchar(50)
) ;
GO
CREATE INDEX Org_BreadthFirst ON Org_T1(OrgLevel, EmployeeId)
GO
CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50) )
AS
BEGIN
DECLARE @last_child hierarchyid
INS_EMP:
SELECT @last_child = MAX(EmployeeId) FROM Org_T1
WHERE EmployeeId.GetAncestor(1) = @mgrid
INSERT Org_T1 (EmployeeId, EmployeeName)
SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName
-- On error, return to INS_EMP to recompute @last_child
IF @@error <> 0 GOTO INS_EMP
END ;
GO
Esempio di utilizzo di una transazione serializzabile
L'indice Org_BreadthFirst assicura che la determinazione di @last_child sia una ricerca di intervallo. Oltre ad alcune situazioni di errore in cui un'applicazione potrebbe volere eseguire un controllo, una violazione di chiave duplicata dopo l'inserimento indica un tentativo di aggiunta di più dipendenti con lo stesso id e pertanto è necessario ricalcolare @ last_child. Nel codice seguente sono utilizzati una transazione serializzabile e un indice breadth-first per calcolare il valore del nodo nuovo:
CREATE TABLE Org_T2
(
EmployeeId hierarchyid PRIMARY KEY,
LastChild hierarchyid,
EmployeeName nvarchar(50)
) ;
GO
CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50))
AS
BEGIN
DECLARE @last_child hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE Org_T2
SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL)
WHERE EmployeeId = @mgrid
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(@last_child, @EmpName)
COMMIT
END ;
Il codice seguente popola la tabella con tre righe e restituisce i risultati:
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2
Set di risultati:
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
.gif "Icona freccia utilizzata con il collegamento Torna all'inizio")
Torna all'inizio
Applicazione di un albero
Gli esempi sopra riportati illustrano il modo in cui un'applicazione può garantire la gestione di un albero. Per applicare un albero tramite vincoli, è possibile creare una colonna calcolata che definisca il padre di ogni nodo con un vincolo della chiave esterna relativo all'id della chiave primaria.
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
Questo metodo dell'applicazione di una relazione è preferito quando il codice considerato non affidabile per la gestione dell'albero gerarchico ha un accesso DML diretto alla tabella. Questo metodo potrebbe ridurre la prestazione perché è necessario controllare il vincolo in ogni operazione DML.
Torna all'inizio
Esempio utilizzando CLR
Un'operazione comune che interessa due nodi in una gerarchia è la ricerca del predecessore comune minore. Questo può essere scritto in Transact-SQL o CLR, perché il tipo hierarchyid è disponibile per entrambi. Si consiglia di utilizzare CLR perché la prestazione è più rapida.
Utilizzare il codice CLR seguente per cercare i predecessori dell'elenco e cercare il predecessore comune minore:
using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
public partial class HierarchyId_Operations
{
[SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
public static IEnumerable ListAncestors(SqlHierarchyId h)
{
while (!h.IsNull)
{
yield return (h);
h = h.GetAncestor(1);
}
}
public static void FillRow_ListAncestors(Object obj, out SqlHierarchyId ancestor)
{
ancestor = (SqlHierarchyId)obj;
}
public static HierarchyId CommonAncestor(SqlHierarchyId h1, HierarchyId h2)
{
while (!h1.IsDescendant(h2))
h1 = h1.GetAncestor(1);
return h1;
}
}
Per utilizzare i metodi ListAncestor e CommonAncestor negli esempi Transact-SQL seguenti, generare la DLL e creare l'assembly HierarchyId_Operations in SQL Server eseguendo un codice simile al seguente:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Torna all'inizio
Elenco dei predecessori
La creazione di un elenco di predecessori di un nodo è un'operazione comune che consente, ad esempio, di mostrare le posizioni di un'organizzazione. A tale scopo, è possibile utilizzare una funzione con valori di tabella utilizzando la classe HierarchyId_Operations sopra definita:
Utilizzo di Transact-SQL:
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO
Esempio di utilizzo:
DECLARE @h hierarchyid
SELECT @h = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/
SELECT LoginID, OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
JOIN ListAncestors(@h) AS A
ON ED.OrgNode = A.Node
GO
Ricerca del predecessore comune minore
Utilizzando la classe HierarchyId_Operations sopra definita, creare la funzione Transact-SQL seguente per cercare il predecessore comune minore che interessa due nodi di una gerarchia:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Esempio di utilizzo:
DECLARE @h1 hierarchyid, @h2 hierarchyid
SELECT @h1 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/
SELECT @h2 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/
SELECT OrgNode.ToString() AS LogicalNode, LoginID
FROM HumanResources.EmployeeDemo
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;
Il nodo risultante è /1/1/
Torna all'inizio
Spostamento di sottoalberi
Un'altra operazione comune è lo spostamento di sottoalberi. La procedura descritta di seguito prende in considerazione il sottoalbero di @oldMgr e lo trasforma (includendo @oldMgr) in un sottoalbero di @ newMgr.
CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) )
AS
BEGIN
DECLARE @nold hierarchyid, @nnew hierarchyid
SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ;
SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL)
FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ;
UPDATE HumanResources.EmployeeDemo
SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew)
WHERE @nold.IsDescendant(OrgNode) = 1 ;
COMMIT TRANSACTION
END ;
GO