Новые типы данных в SQL Server 2008 - Тип данных hierarchyid

 

Тип данных hierarchyid

Тип данных hierarchyid позволяет создавать отношения между элементами данных в таблице, для того, чтобы задать позицию в иерархии. Начнем изучать этот тип данных, создав базу данных MyCompany и заполнив ее данными о сотрудниках с помощью сценария с рис. 5.

Рис. 5 Create and populate the MyCompany database

USE MASTER
GO

CREATE DATABASE MyCompany
GO
USE MyCompany
GO

--Create a table called employee that will store
--the data for the employees for MyCompany.
    
CREATE TABLE employee
(
    EmployeeID int NOT NULL,
    EmpName    varchar(20) NOT NULL,
    Title      varchar(20) NULL,
    Salary     decimal(18, 2) NOT NULL,
    hireDate   datetimeoffset(0) NOT NULL,
)
GO

--These statements will insert the data for the employees of MyCompany.

INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00, '2000-05-23T08:30:00-08:00')

INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(271, 'John',   'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(119, 'Jill',   'Specialist', 14000.00, '2007-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(269, 'Wanida', 'Assistant', 8000.00, '2003-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(272, 'Mary',   'Assistant', 8000.00, '2004-05-23T09:00:00-08:00')
GO
--Results
--EmployeeID  EmpName Title      Salary   hireDate
------------- ------- ---------- -------- --------------------------
--6           David   CEO        35900.00 2000-05-23 08:30:00 -08:00
--46          Sariya  Specialist 14000.00 2002-05-23 09:00:00 -08:00
--271         John    Specialist 14000.00 2002-05-23 09:00:00 -08:00
--119         Jill    Specialist 14000.00 2007-05-23 09:00:00 -08:00
--269         Wanida  Assistant  8000.00  2003-05-23 09:00:00 -08:00
--272         Mary    Assistant  8000.00  2004-05-23 09:00:00 -08:00

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

Рис. 6 Таблица сотрудников MyCompany 

Однако деловая информация чаще всего имеет некую внутренную структуру. Например, каждая фирма имеет некую схему отчетности, как та, которая показана для MyCompany на рис. 7. Все сотрудники MyCompany отчитываются перед директором (CEO) Дэвидом (David). Некоторые отчитываются напрямую, как Джилл (Jill). Некоторые, как Мэри (Mary), отчитываются через промежуточное звено. В терминах программирования, структура отчетности MyCompany можно назвать деревом, она напоминает его по форме. Дэвид, на самом верху, ни перед кем не отчитывается; он «родитель» или «предок». Сотрудники, отчитывающиеся перед Дэвидом, находятся ниже. Такие узлы называются «дочерними» или «потомками». У Дэвида может быть столько «потомков», сколько нужно, чтобы обозначить прямые отчеты.

Рис. 7 Организационная структура MyCompany 

Сценарий на рис. 8 строит базу данных MyCompany с помощью типа данных hierarchyid, создавая отношения, соответствующие структуре отчетности MyCompany. Дял добавления столбца типа hierarchyid используется инструкция ALTER TABLE. Затем, с помощью метода hierarchyid GetRoot вставляется узел Дэвида. Затем в дерево добавляются отчитывающиеся перед Дэвидом напрямую с помощью метода GetDescendant.

Рис. 8 Rebuild the database using hierarchyid

DELETE employee
GO
ALTER TABLE employee ADD OrgNode hierarchyid NOT NULL
GO

DECLARE @child hierarchyid,
@Manager hierarchyid = hierarchyid::GetRoot()

--The first step is to add the node at the top of the
--tree. Since David is the CEO his node will be the
--root node.

INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00,
       '2000-05-23T08:30:00-08:00', @Manager)

--The next step is to insert the records for
--the employees that report directly to David.

SELECT @child = @Manager.GetDescendant(NULL, NULL)

INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)

SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(271, ‚John',   ‚Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)

SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(119, ‚Jill',   ‚Specialist', 14000.00,
       ‚2007-05-23T09:00:00-08:00', @child)

--We can now insert the employee that reports to
--Sariya.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 46

INSERT INTO employee
VALUES(269, ‚Wanida', ‚Assistant', 8000.00,
       ‚2003-05-23T09:00:00-08:00', @manager)

--Next insert the employee that report to John.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 271

INSERT INTO employee
VALUES(272, ‚Mary',   ‚Assistant', 8000.00,
       ‚2004-05-23T09:00:00-08:00', @manager)

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

SELECT EmpName, Title, Salary, OrgNode.ToString() AS OrgNode
FROM employee ORDER BY OrgNode
GO
--Results
--EmpName  Title      Salary    OrgNode
---------- ---------- --------- -------
--David    CEO        35900.00  /
--Sariya   Specialist 14000.00  /1/
--Wanida   Assistant  8000.00   /1/1/
--John     Specialist 14000.00  /2/
--Mary     Assistant  8000.00   /2/1/
--Jill     Specialist 14000.00  /3/

OrgNode – столбец hierarchyid. Каждый символ косой черты (/) в результатах означает узел в дереве иерархии. Дэвид находится в корне, что обозначено одной чертой. Сария (Sariya), Джон (John) и Джил отчитываются перед Дэвидом и имеют по две черты, это значит, что они – второй узел иерархии. Числа 1, 2 и 3 показывают порядковый номер соответствующего дочернего узла. Эта система обладает большой гибкостью. Дочерние узлы можно удалять, добавлять и вставлять как угодно. Если мы, например, добавим сотрудника между Джоном и Джилл, этот сотрудник будет обозначен в результатах как /2.1/.

Чтобы найти ответ на вопрос, положим, «Кто отчитывается перед Сарией?», можно создать запрос вроде этого:

DECLARE @Sariya hierarchyid

SELECT @Sariya = OrgNode
FROM employee WHERE EmployeeID = 46

SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @Sariya
GO
--Results
--EmpName Title     Salary  OrgNode
--------- --------- ------- -------
--Wanida  Assistant 8000.00 /1/1/

Этот запрос использует метод hierarchyid GetAncestor, который возвращает родительский узел текущего узла hierarchyid. В приведенном коде, переменная @Sariya установлена на узел иерархии Сарии. Так получается потому, что Сария – прямой «предок» любого сотрудника, отчитывающегося перед ней. Поэтому запрос, возвращающий сотрудников, отчитывающихся прямо перед Сарией, должен состоять из получения ее узла из дерева, а затем выбора всех сотрудников, для которых узел-предок – это узел Сарии.

Столбцы hierarchyid обычно очень компактные, потому что количество бит, требующихся для представления узла дерева, зависит от среднего числа дочерних узлов (которое обычно называют ветвлением узла). Например, новый узел в организационной иерархии 100000 сотрудников со средним уровнем ветвления шесть уровней, займет примерно пять байт места.

Тип данных hierarchyid обеспечивает несколько методов, облегачающих работу с иерархическими данными. Они обобщены на рис. 9. Подробную информацию о всех методах можно найти на SQL Server Books Online (электронные книги по SQL Server) (msdn2.microsoft.com/ms130214).

Рис. 9 Methods provided by the hierarchyid data type

Метод	Описание
GetAncestor	Возвращает hierarchyid, представляющего n-ного предка этого узла hierarchyid.
GetDescendant	Возвращает дочерний узел этого узла hierarchyid.
GetLevel	Возвращает целое число, представляющее глубину это узла hierarchyid в общей иерархии.
GetRoot	Возвращает корневой узел hierarchyid этого дерева иерархии. Статический.
IsDescendant	Возвращает true, если переданный дочерный узел является потомком этого узла hierarchyid.
Parse	Переводит строчное представление иерархии в значение hierarchyid. Статический.
Reparent	Передвигает узел в другое положение в иерархии.
ToString	Возвращает строку, содержащую логическое представление этого hierarchyid.