Trabalhando com dados hierarchyid
Este tópico inclui informações sobre as atividades comuns no gerenciamento e na consulta de uma árvore de dados hierárquicos.
Neste tópico
Gerenciando uma árvore com hierarchyid
Aplicando uma árvore
Exemplo com a utilização do CLR
Movendo subárvores
Gerenciando uma árvore com hierarchyid
Embora uma coluna hierarchyid não represente necessariamente uma árvore, um aplicativo pode garantir facilmente que essa representação ocorra.
Para gerar novos valores, execute uma das ações abaixo:
Mantenha registro do último número filho da linha pai.
Compute o último filho. Para executar esse procedimento com eficácia, é necessário um índice de primeira amplitude.
Imponha a exclusividade criando um índice exclusivo na coluna, talvez como parte de uma chave de cluster. Para assegurar a inserção de valores únicos, execute uma das ações a seguir:
Determine a exclusividade de cada novo nó filho e insira-o em uma transação serializável.
Detecte as falhas de violação de chave exclusiva e tente novamente.
Exemplo utilizando detecção de erro
No exemplo a seguir, o código de exemplo computa o novo valor filho de EmployeeId, detectando depois quaisquer violações de chave para retorná-las ao marcador INS_EMP para computar novamente o valor de EmployeeId na nova linha:
USE AdventureWorks2008R2;
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
Exemplo utilizando uma transação serializável
O índice Org_BreadthFirst assegura que a determinação de @last_child seja uma busca de intervalo. Além de outros casos de erro que um aplicativo tente verificar, uma violação da chave duplicada depois da inserção indica uma tentativa de adicionar vários funcionários com o mesmo id e, portanto, @last_child deve ser novamente computado. O código a seguir usa uma transação serializável e um índice de primeira amplitude para computar o valor do novo nó:
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 ;
O código a seguir popula a tabela com três linhas e retorna os resultados:
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2
Aqui está o conjunto de resultados.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
.gif "Ícone de seta usado com o link Voltar ao Início")
Voltar ao início
Aplicando uma árvore
Os exemplos anteriores exemplificam como um aplicativo pode assegurar a manutenção de uma árvore. Para impor uma árvore por meio de restrições, uma coluna computada que define o pai de cada nó pode ser criada com uma restrição de chave estrangeira de volta para o id de chave primária.
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
O método de impor uma relação é preferido quando o código que não é confiável para manter a árvore hierárquica tiver acesso DML direto à tabela. Esse método pode reduzir o desempenho porque a restrição precisa ser verificada em todas as operações DML.
Voltar ao início
Exemplo utilizando CLR
Uma operação comum que envolve dois nós em uma hierarquia é encontrar o mais baixo ancestral comum. Isso pode ser escrito em Transact-SQL ou CLR, porque o tipo hierarchyid está disponível em ambos. CLR é recomendado porque o desempenho é superior.
Use o código CLR a seguir para localizar os ancestrais de lista e o mais baixo antepassado comum:
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 os métodos ListAncestor e CommonAncestor nos exemplos Transact-SQL a seguir, construa a DLL e crie o assembly HierarchyId_Operations em SQL Server executando um código semelhante ao seguinte:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Voltar ao início
Listando os ancestrais
A criação de uma lista de ancestrais de um nó é uma operação comum; por exemplo, para mostrar a posição em uma organização. Uma das formas de fazer isso é usar uma função com valor de tabela, através da classe HierarchyId_Operations definida acima:
Usando Transact-SQL:
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO
Exemplo 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
Localizando o mais baixo ancestral comum
Usando a classe HierarchyId_Operations definida acima, crie a seguinte função Transact-SQL para localizar o mais baixo ancestral comum que envolva dois nós em uma hierarquia:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Exemplo 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) ;
O nó resultante é /1/1/
Voltar ao início
Movendo subárvores
Outra operação comum é mover subárvores. O procedimento abaixo toma uma subárvore de @oldMgr e a transforma em (@oldMgr inclusive) uma subárvore 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 OrgNode.IsDescendantOf(@nold) = 1 ;
COMMIT TRANSACTION
END ;
GO