MS SQL: hierarchyid — иерархия по-новому

В наше время среди СУБД самую большую распространенность получили реляционные базы данных, в которых основными объектами являются таблицы и отношения между ними. Таблицы — это очень хорошо, они позволяют решить большинство задач по хранению данных и манипуляции с ними. Но в реальном мире сущности требующие хранения не всегда представлены в табличном виде. Одним из таких очень распространенных видов структуры данных отличных от таблицы является древовидная структура, когда каждый элемент данных имеет предка и потомков. Примером такой структуры может быть структура штата предприятия, в котором во главе стоит директор (корень дерева), его заместители, отделы с начальниками, которые подчиняются определенным заместителям, сотрудники отделов, которые подчиняются начальникам.

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

Однако, разработчики СУБД MS SQL предлагают в своей новой версии MS SQL 2008 для реализации древовидной иерархии новый тип хранения данных hierarchyid.

Введение

Тип hierarchyid — это системный тип базы данных, размер которого может меняться в зависимости от структуры дерева (его глубины) и среднего числа потомков узлов. В MSDN приводятся следующие расчеты: для дерева с иерархией в 6 уровней для 100000 человек hierarchyid займет 38 бит, которые БД округлит до 40 бит или 5 байт. Максимальный же размер, который может занимать hierarchyid равен 892 байта.

Создание таблицы

Для начала создадим весьма простую таблицу, которая будет хранить в себе иерархию персонала в некой компании:

CREATE TABLE Table_1(
  hid hierarchyid NOT NULL,
  userId int NOT NULL,
  userName nvarchar(50) NOT NULL,
CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED
(
  [hid] ASC
))* This source code was highlighted with Source Code Highlighter.

В таблице будет хранится: hierarchyid, id служащего и его имя. Далее мы попробуем воссоздать в этой таблице следующую иерархию персонала:

1 Иванов
    2 Петров
        7 Смирнов
            8 Пупкин
    3 Сидоров
    4 Васечкин
        5 Круглов
        6 Квадратов

Создание иерархии

Первым делом создадим корень иеррахии:

insert into Table_1
values(hierarchyid::GetRoot(), 1, 'Иванов')* This source code was highlighted with Source Code Highlighter.

Обратите внимание на hierarchyid::GetRoot() — это статический метод, который всегда возвращает идентификатор корня иерархии.

Далее, добавим к корневой записи потомков:

declare Id hierarchyid

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = hierarchyid::GetRoot()

insert into Table_1
values(hierarchyid::GetRoot().GetDescendant(@id, null), 2, 'Петров');

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = hierarchyid::GetRoot()

insert into Table_1
values(hierarchyid::GetRoot().GetDescendant(@id, null), 3, 'Сидоров');

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = hierarchyid::GetRoot()

insert into Table_1
values(hierarchyid::GetRoot().GetDescendant(@id, null), 4, 'Васечкин');
* This source code was highlighted with Source Code Highlighter.

В этом коде примечательными являются следующие участки кода:

• hid.GetAncestor(1) = hierarchyid::GetRoot() — выбирает все записи, предком (прямым) которых является корень;
• hierarchyid::GetRoot().GetDescendant(@id, null) — выбирает первый свободный hierarchyid прямых потомков корня дерева. Описание параметров я приведу ниже.

Завершим картину, заполнив таблицу всеми оставшимися записями:

declare @phId hierarchyid
select @phId = (SELECT hid FROM Table_1 WHERE userId = 2);

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = @phId

insert into Table_1
values( @phId.GetDescendant(@id, null), 7, 'Смирнов');

select @phId = (SELECT hid FROM Table_1 WHERE userId = 4);

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = @phId

insert into Table_1
values( @phId.GetDescendant(@id, null), 5, 'Круглов');

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = @phId

insert into Table_1
values( @phId.GetDescendant(@id, null), 6, 'Квадратов');

select @phId = (SELECT hid FROM Table_1 WHERE userId = 7);

select Id = MAX(hid)
from Table_1
where hid.GetAncestor(1) = @phId

insert into Table_1
values( @phId.GetDescendant(@id, null), 8, 'Пупкин');
* This source code was highlighted with Source Code Highlighter.

Обратите внимание, что весь приведенный в статье код следует выполнять одним скриптом

После того, как мы выполним весь этот код мы получим следующую картину

select hid.ToString(), hid.GetLevel(), * from Table_1* This source code was highlighted with Source Code Highlighter.

/               0   0x                    1  Иванов
/1/             1   0x58                  2  Петров
/1/1/           2   0x5AC0                7  Смирнов
/1/1/1/         3   0x5AD6                8  Пупкин
/2/             1   0x68                  3  Сидоров
/3/             1   0x78                  4  Васечкин
/3/1/           2   0x7AC0                5  Круглов
/3/2/           2   0x7B40                6  Квадратов

WIN-Z6U4ALRNDSU(WIN-Z6U4ALRNDSU\Administrator): (8 row(s) affected)

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

Вспомогательные функции

Для работы с hierarchyid MS SQL 2008 предлагает несколько функций. Здесь я кратко приведу предназначение каждой из них, а для полного описания предлагаю обратиться к докуемнтации:

• GetAncestor — выдает hierarchyid предка, можно указать уровень предка, например 1 выберет непосредственного предка;
• GetDescendant — выдает hierarchyid потомка, принимает два параметра, с помощью которых можно управлять тем, какого именно потомка необходимо получить на выходе;
• GetLevel — выдает уровень hierarchyid;
• GetRoot — выдает уровень корня;
• IsDescendant — проверяет является ли hierarchyid переданный через параметр потомком;
• Parse — конвертирует строковое представление hierarchyid в собственно hierarchyid;
• Reparent — позволяет изменить текущего предка;
• ToString — конвертирует hierarchyid в строковое представление.

Заключение

MS SQL 2008 предлагает новый способ хранения данных, которые представляют собой какую-либо древовидную иерархию. Для этих целей вводится тип hierarchyid, который содержит в себе «путь» к элементу иерархии. Для помощи программисту баз данных предлагается набор вспомогательных функции, которые позволяют организовать представление иерархии, перемещение данных, доступ к элементам и добавление новых элементов иерархии.

Предложенный вариант, на мой взгляд, является отличной заменой стандартного способа хранения иерархических данных через поле parentId (предок). Использование hierarchyid позволяет создавать иерархические структуры с написанием минимумом кода.