Trabajar con datos hierarchyid
En este tema se incluye información sobre las actividades comunes para administrar y consultar un árbol de datos jerárquico.
En este tema
Administrar un árbol mediante hierarchyid
Exigir un árbol
Ejemplo usando CLR
Mover los subárboles
Administrar un árbol mediante hierarchyid
Aunque una columna de hierarchyid no representa necesariamente un árbol, una aplicación puede exigir fácilmente que sí lo haga.
Cuando genere nuevos valores, realice una de las siguientes operaciones:
Realice el seguimiento del último número secundario en la fila primaria.
Calcule el último elemento secundario. Si desea realizar eficazmente este proceso, deberá ejecutar un índice con prioridad a la amplitud.
Exija la singularidad creando un índice único en la columna, tal vez como parte de una clave de agrupación en clústeres. Para asegurarse de que se insertan los valores únicos, realice una de las siguientes tareas:
Determine la singularidad de cada nuevo nodo secundario e insértelo en una transacción serializable.
Detecte errores y reintentos de infracción de clave única.
Ejemplo usando la detección de errores
En el ejemplo siguiente, el código de ejemplo calcula el nuevo valor secundario de EmployeeId y, a continuación, detecta cualquier infracción de clave y la devuelve al marcador INS_EMP para volver a calcular el valor de EmployeeId para la nueva fila:
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
Ejemplo usando una transacción serializable
El índice Org_BreadthFirst garantiza que la determinación de @ last_child es una búsqueda del intervalo. Además de otros casos de error, es posible que una aplicación desee comprobar una infracción de clave duplicada después de que la inserción indique un intento de agregar varios empleados con el mismo identificador y, por lo tanto, es necesario volver a calcular @last_child. El código siguiente usa una transacción serializable y un índice con prioridad a la amplitud para calcular el nuevo valor de nodo:
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 ;
El código siguiente rellena la tabla con tres filas y devuelve los resultados:
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2
Éste es el conjunto de resultados.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
.gif "Icono de flecha usado con el vínculo Volver al principio")
Volver al principio
Exigir un árbol
Los ejemplos anteriores muestran cómo una aplicación puede exigir que se mantenga un árbol. Para exigir un árbol a través de restricciones, se puede crear una columna calculada que defina el elemento primario de cada nodo con una restricción de clave externa respecto al identificador de clave principal.
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
Se prefiere este método que exige una relación cuando el código que no es de confianza para mantener el árbol jerárquico tiene acceso DML directo a la tabla. Este método puede reducir el rendimiento porque es necesario comprobar la restricción para cada operación de DML.
Volver al principio
Ejemplo usando CLR
Una operación común, en la que se implican dos nodos en una jerarquía, es buscar el antecesor común más bajo. Esto se puede escribir en Transact-SQL o CLR porque el tipo de hierarchyid está disponible en ambos. Se recomienda CLR porque la ejecución es más rápida.
Use el siguiente código de CLR para buscar antecesores en la lista, así como el antecesor común más bajo:
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;
}
}
Para usar los métodos ListAncestor y CommonAncestor en los siguientes ejemplos de Transact-SQL, genere la DLL y cree el ensamblado de HierarchyId_Operations en SQL Server ejecutando un código similar al siguiente:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Volver al principio
Enumerar antecesores
La creación de una lista de antecesores de un nodo es una operación común que sirve, por ejemplo, para mostrar la posición en una organización. Esto se puede realizar, por ejemplo, mediante una función con valores de tabla que utilice la clase HierarchyId_Operations definida anteriormente:
Usar Transact-SQL:
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO
Ejemplo de uso:
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
Buscar el antecesor común más bajo
Use la clase HierarchyId_Operations definida anteriormente para crear la siguiente función de Transact-SQL a fin de buscar el antecesor común más bajo que implica dos nodos en una jerarquía:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Ejemplo de uso:
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) ;
El nodo resultante es /1/1/
Volver al principio
Mover subárboles
Otra operación común es mover subárboles. El procedimiento siguiente toma el subárbol de @oldMgr y lo convierte (incluido @oldMgr) en un subárbol de @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