Partilhar via


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

Ícone de seta usado com o link Voltar ao InícioVoltar 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.

Ícone de seta usado com o link Voltar ao InícioVoltar 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

Ícone de seta usado com o link Voltar ao InícioVoltar 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/

Ícone de seta usado com o link Voltar ao InícioVoltar 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