Работа с данными hierarchyid

В этот раздел входят сведения о наиболее часто встречающихся операциях при управлении и работе с запросами по иерархическому дереву данных.

В этом разделе

Управление древовидной структурой с помощью hierarchyid

Принудительное формирование древовидной структуры

Пример, использующий среду CLR

Перемещение поддеревьев

Управление древовидной структурой с помощью hierarchyid

Хотя столбец hierarchyid не обязательно представляет дерево, приложение легко может обеспечить это.

• При формировании новых значений выполните одно из следующих действий.

  • Следите за последним номером потомка в строке родитель.

  • Вычислите последнего потомка. Для эффективного вычисления требуется наличие индекса по ширине.

• Обеспечьте уникальность, создав для столбца уникальный индекс, возможно, как часть ключа кластеризации. Чтобы обеспечить уникальность вставляемых значений, выполните одно из следующих действий.

  • Определяйте уникальность каждого нового дочернего узла перед его вставкой, используя сериализуемую транзакцию.

  • Определяйте нарушения уникальных ключей и проводите повторные попытки.

Пример использования обнаружения ошибок

В следующем примере образец кода вычисляет значение нового потомка EmployeeId, определяет любое нарушение ключа, а затем возвращается к маркеру INS_EMP для повторного вычисления значения новой строки EmployeeId:

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

Пример использования сериализуемой транзакции

Индекс Org_BreadthFirst обеспечивает использование поиска по диапазону для определения значения @last_child. В дополнение к другим случаям ошибок, проверка которых может потребоваться приложению, нарушение повторяющихся ключей после вставки может свидетельствовать о попытке добавления нескольких сотрудников с одним и тем же идентификатором; вследствие этого вычисление значения @last_child должно быть выполнено повторно. Следующий код использует сериализуемую транзакцию и индекс по ширине для вычисления значения нового узла:

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 ;

Следующий код заполняет таблицу тремя строками и возвращает результаты:

INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2

Ниже приводится результирующий набор.

EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

В начало

Принудительное формирование древовидной структуры

Приведенный выше пример показывает, как можно использовать приложение для поддержания целостности дерева. Обеспечить целостность дерева с помощью ограничений можно, создав для вычисляемого столбца, который определяет родителя для каждого узла, ограничение внешнего ключа относительно идентификатора первичного ключа.

CREATE TABLE Org_T3
(
   EmployeeId hierarchyid PRIMARY KEY,
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED  
      REFERENCES Org_T3(EmployeeId),
   LastChild hierarchyid, 
   EmployeeName nvarchar(50)
)
GO

Этот метод обеспечения взаимосвязи предпочтительней в случае, если прямой DML-доступ к таблице имеет код, который не в состоянии обеспечить целостность ее иерархического дерева. Этот метод может снизить производительность, поскольку ограничение необходимо проверять при каждой DML-операции.

В начало

Пример, использующий среду CLR

Одной из частых операций, в которых участвуют два узла из иерархии, является нахождение ближайшего общего предка. Эта операция может быть описана в среде Transact-SQL либо в среде CLR, поскольку тип данных hierarchyid доступен в обеих. Рекомендуется использовать среду CLR, поскольку она обеспечивает большую производительность.

Используйте следующий код CLR, чтобы найти список предков и ближайшего общего предка:

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;
    }
}

Чтобы получить возможность использовать методы ListAncestor и CommonAncestor в следующих примерах Transact-SQL, постройте библиотеки DLL и создайте сборку HierarchyId_Operations в SQL Server, выполнив код, аналогичный следующему:

CREATE ASSEMBLY HierarchyId_Operations 
FROM '<path to DLL>\ListAncestors.dll'
GO

В начало

Перечисление предков

Создание списка предков узла — это распространенная операция, использующаяся, например, для демонстрации позиции в организации. Одним из способов ее реализации является применение возвращающей табличное значение функции, использующей класс HierarchyId_Operations, определенный выше:

Использование среды Transact-SQL:

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

Пример использования:

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

Нахождение ближайшего общего предка

С помощью класса HierarchyId_Operations, определенного выше, создайте следующую функцию Transact-SQL для нахождения ближайшего общего предка, затрагивающую два узла в иерархии:

CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO

Пример использования:

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

Результирующий узел — /1/1/

В начало

Перемещение поддеревьев

Другой распространенной операцией является перемещение поддеревьев. Описанная ниже процедура берет поддерево узла @oldMgr и делает его (включая сам узел @oldMgr) поддеревом узла @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

См. также

Справочник

hierarchyid (Transact-SQL)

Основные понятия

Использование типов данных hierarchyid (компонент Database Engine)

Справочник по методам типа данных hierarchyid

Учебник. Использование типа данных hierarchyid