- •1. Основные понятия и определения 4
- •2. Три базовых способа моделирования иерархий 11
- •3. Два важных частных случая 125
- •1. Основные понятия и определения
- •1.1. Иерархическая модель данных
- •1.2. Реляционная модель данных
- •1.3. Задача моделирования
- •2. Три базовых способа моделирования иерархий
- •2.1. Рекурсивный способ представления иерархии1
- •Insert into @tree (Name, Level) values (@NodeName, @Level);
- •Insert into @tree (Name, Level) values
- •Insert @tree ([name], id, parent, [level], [order])
- •Insert @tree ([name], id, parent, [level], [order])
- •Insert @tree ([name], id, parent, [level], [order])
- •Insert @tree ([name], id, parent, [level], [order])
- •Insert @tree ([name], id, parent, [level], [order])
- •Insert into t(Parent, Data) values(1, 'b');
- •Id int not null,
- •Insert into @tree ([Name], id, Parent, [Level], [Check], [Path])
- •Values (@NodeName, @id, @Parent, @Level, @Check, @Path);
- •Insert into @tree ([Name],
- •Values (@NodeName,
- •Id int not null,
- •Id not in (select distinct Parent from t);
- •Insert into @tree ([Name],
- •Задачи повышенной сложности
- •2.2. Способ правого и левого коэффициентов
- •Задачи повышенной сложности
- •2.3. Способ вспомогательной таблицы
- •Insert into t_Base(Node) values (@NewRoot);
- •Insert into t_Helper(uid, ParentId, [Level])
- •Values (@RootId, @RootId, 0);
- •Instead of delete
- •3. Два важных частных случая
- •3.1. Случай ограниченного количества уровней иерархии
- •3.2. Случай ограниченного числа потомков
- •Заключение
- •Библиографический список
- •Ермаков Дмитрий Германович
- •620002, Екатеринбург, ул. Мира, 19
Задачи повышенной сложности
Выбрать всех непосредственных потомков для некоторого заданного элемента.
Найти ближайшего общего предка для нескольких наперёд заданных узлов.
Написать хранимые процедуры для пересчёта правого и левого коэффициентов при удалении узлов в иерархии.
Написать хранимые процедуры для пересчёта правого и левого коэффициентов при добавлении узлов в иерархию.
Рассмотреть вариант модели, когда значения правого и левого коэффициентов для листьев в дереве устанавливаются равными.
2.3. Способ вспомогательной таблицы
Способ, описанный Ральфом Кимбаллом, может рассматриваться как расширенный вариант рекурсивного способа. Здесь модель дерева строится на основе двух таблиц. Первая таблица (базовая) хранит список всех узлов, снабжённых уникальными идентификаторами, и всю содержательную информацию по каждому узлу.
Таблица 5
Пример основной таблицы
Уникальный идентификатор узла |
Содержательная часть |
A |
A |
B |
B |
C |
C |
D |
D |
E |
E |
F |
F |
G |
G |
H |
H |
I |
I |
J |
J |
K |
K |
L |
L |
M |
M |
N |
N |
O |
O |
P |
P |
Создадим эту таблицу:
CREATE TABLE T_Base
(ID uniqueidentifier DEFAULT NEWID() primary key,
Node varchar not null);
В этом примере для идентификаторов узлов дерева, хранимых в столбце ID, использован тип данных UNIQUEIDETIFIER, который будет заполняться автоматически при добавлении записей в таблицу (значение по умолчанию для этого столбца генерируется функцией NEWID()) и является первичным ключом.
Заполним таблицу данными (значения для столбца ID вводить не требуется):
INSERT INTO T_Base (Node) VALUES ('A');
INSERT INTO T_Base (Node) VALUES ('B');
INSERT INTO T_Base (Node) VALUES ('C');
INSERT INTO T_Base (Node) VALUES ('D');
INSERT INTO T_Base (Node) VALUES ('E');
INSERT INTO T_Base (Node) VALUES ('F');
INSERT INTO T_Base (Node) VALUES ('G');
INSERT INTO T_Base (Node) VALUES ('H');
INSERT INTO T_Base (Node) VALUES ('I');
INSERT INTO T_Base (Node) VALUES ('J');
INSERT INTO T_Base (Node) VALUES ('K');
INSERT INTO T_Base (Node) VALUES ('L');
INSERT INTO T_Base (Node) VALUES ('M');
INSERT INTO T_Base (Node) VALUES ('N');
INSERT INTO T_Base (Node) VALUES ('O');
INSERT INTO T_Base (Node) VALUES ('P');
Выберем все столбцы и строки полученной таблицы, отсортировав строки по полю Node:
SELECT * FROM T_Base ORDER BY Node;
Результат выполнения этого предложения SQL в среде SQL Server Management Studio Express представлен на рис. 75.
Рис. 75. Заполнение основной таблицы модели иерархии
Теперь дополним основную таблицу, содержащую перечень всех узлов иерархии, вспомогательной таблицей. Конечно, иерархию можно было представить и в виде одной таблицы, но в этом случае содержательная часть каждого узла будет повторяться для всех строк, связанных с каждым из узлов, что нежелательно с точки зрения нормализации.
Вспомогательная таблица по своей структуре похожа на таблицу со ссылкой на непосредственного предка, но, в отличие от неё, содержит все полные пути от корневого элемента до каждого узла иерархии в виде наборов пар «родитель-потомок». Здесь потомок может не быть непосредственным (прямым) потомком родителя, поэтому для каждой такой пары указывается расстояние («степень родства») от предка до потомка.
Корневые узлы деревьев могут быть обозначены как имеющие в качестве предка самих себя с нулевым расстоянием или, в вариантном исполнении, запись, соответствующая корневому узлу дерева, может просто отсутствовать во вспомогательной таблице.
Первичный ключ во вспомогательной таблице составят все три столбца.
Структура такой таблицы и ее содержимое для иерархии, представленной на рис. 1, показана в таблице 6.
Таблица 6
Пример структуры вспомогательной таблицы
Уникальный идентификатор узла |
Указатель на предка узла |
Расстояние до предка |
A |
A или NULL |
0 |
B |
A |
1 |
C |
A |
1 |
D |
A |
2 |
D |
B |
1 |
E |
A |
2 |
E |
B |
1 |
F |
A |
2 |
F |
C |
1 |
G |
A |
2 |
G |
C |
1 |
H |
A |
2 |
H |
C |
1 |
I |
A |
3 |
I |
B |
2 |
I |
D |
1 |
J |
A |
3 |
J |
B |
2 |
J |
E |
1 |
K |
A |
3 |
K |
B |
2 |
K |
E |
1 |
L |
A |
3 |
L |
B |
2 |
L |
E |
1 |
O |
A |
3 |
O |
C |
2 |
O |
F |
1 |
M |
A |
3 |
M |
C |
2 |
M |
G |
1 |
N |
A |
3 |
N |
C |
2 |
N |
G |
1 |
P |
A |
3 |
P |
C |
2 |
P |
H |
1 |
Создадим вспомогательную таблицу для уже имеющейся базовой таблицы с помощью следующего предложения SQL:
CREATE TABLE T_Helper (
UID uniqueidentifier references T_Base(ID) NOT NULL,
ParentID uniqueidentifier references T_Base(ID) NOT NULL,
[Level] int NOT NULL,
PRIMARY KEY (UID, ParentID, [Level]));
После заполнения данными базовая таблица имеет следующий вид:
Таблица 7
Таблица T_Base после заполнения данными
ID |
Node |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
A |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
B |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
C |
07EDFC1D-2506-426E-BE2A-38BC86BA9EA2 |
D |
777A3A0E-6079-43B9-8B9D-325230D48A3A |
E |
CF8D4BA4-89E3-4729-9B12-DDF2987D5949 |
F |
9A42488B-536B-4269-A90C-BE164269425B |
G |
8E8CD289-7098-4C4F-A5A1-3DF2C25963AD |
H |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B |
I |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 |
J |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F |
K |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 |
L |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 |
M |
5BDF65BE-2940-48F3-BBB1-B3BA8B48287B |
N |
561D8D98-9410-4ECC-BA0E-CE4CC204705B |
O |
FC579270-CA86-4153-8DA3-BEA0F5DB6E24 |
P |
Обратите внимание! Значения в столбце ID на каждом рабочем месте будут свои! Они также будут различаться и на одном рабочем месте при каждой новой попытке заполнения таблицы.
Созданную вспомогательную таблицу заполним данными, используя уникальные идентификаторы, автоматически назначенные узлам иерархии при заполнении базовой таблицы.
NSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',0);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'BB31DC0F-BB57-48FF-B768-79D205F17F33',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'07EDFC1D-2506-426E-BE2A-38BC86BA9EA2',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'07EDFC1D-2506-426E-BE2A-38BC86BA9EA2',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'777A3A0E-6079-43B9-8B9D-325230D48A3A',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'777A3A0E-6079-43B9-8B9D-325230D48A3A',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'CF8D4BA4-89E3-4729-9B12-DDF2987D5949',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'CF8D4BA4-89E3-4729-9B12-DDF2987D5949',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'9A42488B-536B-4269-A90C-BE164269425B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'9A42488B-536B-4269-A90C-BE164269425B',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'8E8CD289-7098-4C4F-A5A1-3DF2C25963AD',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'8E8CD289-7098-4C4F-A5A1-3DF2C25963AD',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'D9826E05-BFD1-4845-AEFC-215C2E5BA78B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'D9826E05-BFD1-4845-AEFC-215C2E5BA78B',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'D9826E05-BFD1-4845-AEFC-215C2E5BA78B',
'07EDFC1D-2506-426E-BE2A-38BC86BA9EA2',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8',
'777A3A0E-6079-43B9-8B9D-325230D48A3A',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AEB398A7-F653-4BE8-8844-AE9CAEC1A26F',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AEB398A7-F653-4BE8-8844-AE9CAEC1A26F',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'AEB398A7-F653-4BE8-8844-AE9CAEC1A26F',
'777A3A0E-6079-43B9-8B9D-325230D48A3A',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2',
'06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2',
'777A3A0E-6079-43B9-8B9D-325230D48A3A',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'561D8D98-9410-4ECC-BA0E-CE4CC204705B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'561D8D98-9410-4ECC-BA0E-CE4CC204705B',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'561D8D98-9410-4ECC-BA0E-CE4CC204705B',
'CF8D4BA4-89E3-4729-9B12-DDF2987D5949',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'0FBD7863-B4B5-456A-8FAC-63B972AAD010',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'0FBD7863-B4B5-456A-8FAC-63B972AAD010',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'0FBD7863-B4B5-456A-8FAC-63B972AAD010',
'9A42488B-536B-4269-A90C-BE164269425B',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'5BDF65BE-2940-48F3-BBB1-B3BA8B48287B',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'5BDF65BE-2940-48F3-BBB1-B3BA8B48287B',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'5BDF65BE-2940-48F3-BBB1-B3BA8B48287B',
'9A42488B-536B-4269-A90C-BE164269425B',1);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'FC579270-CA86-4153-8DA3-BEA0F5DB6E24',
'7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4',3);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'FC579270-CA86-4153-8DA3-BEA0F5DB6E24',
'BB31DC0F-BB57-48FF-B768-79D205F17F33',2);
INSERT INTO T_Helper (UID, ParentID, Level) VALUES (
'FC579270-CA86-4153-8DA3-BEA0F5DB6E24',
'8E8CD289-7098-4C4F-A5A1-3DF2C25963AD',1);
Таблица 8
Вспомогательная таблица T_Helper после заполнения данными
UID |
ParentID |
Level |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
0 |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
1 |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
1 |
07EDFC1D-2506-426E-BE2A-38BC86BA9EA2 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
2 |
07EDFC1D-2506-426E-BE2A-38BC86BA9EA2 |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
1 |
777A3A0E-6079-43B9-8B9D-325230D48A3A |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
2 |
777A3A0E-6079-43B9-8B9D-325230D48A3A |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
1 |
CF8D4BA4-89E3-4729-9B12-DDF2987D5949 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
2 |
CF8D4BA4-89E3-4729-9B12-DDF2987D5949 |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
1 |
9A42488B-536B-4269-A90C-BE164269425B |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
2 |
9A42488B-536B-4269-A90C-BE164269425B |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
1 |
8E8CD289-7098-4C4F-A5A1-3DF2C25963AD |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
2 |
8E8CD289-7098-4C4F-A5A1-3DF2C25963AD |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
1 |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
2 |
D9826E05-BFD1-4845-AEFC-215C2E5BA78B |
07EDFC1D-2506-426E-BE2A-38BC86BA9EA2 |
1 |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
2 |
AF1A9E1A-4747-4BC1-A5F5-FA30272BD4A8 |
777A3A0E-6079-43B9-8B9D-325230D48A3A |
1 |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
2 |
AEB398A7-F653-4BE8-8844-AE9CAEC1A26F |
777A3A0E-6079-43B9-8B9D-325230D48A3A |
1 |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 |
06B5F4F9-4B40-4CC2-982F-F258CDAEBB1F |
2 |
2520E3D9-B40F-4A6A-ADD8-A334CABCFBB2 |
777A3A0E-6079-43B9-8B9D-325230D48A3A |
1 |
561D8D98-9410-4ECC-BA0E-CE4CC204705B |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
561D8D98-9410-4ECC-BA0E-CE4CC204705B |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
2 |
561D8D98-9410-4ECC-BA0E-CE4CC204705B |
CF8D4BA4-89E3-4729-9B12-DDF2987D5949 |
1 |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
2 |
0FBD7863-B4B5-456A-8FAC-63B972AAD010 |
9A42488B-536B-4269-A90C-BE164269425B |
1 |
5BDF65BE-2940-48F3-BBB1-B3BA8B48287B |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
5BDF65BE-2940-48F3-BBB1-B3BA8B48287B |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
2 |
5BDF65BE-2940-48F3-BBB1-B3BA8B48287B |
9A42488B-536B-4269-A90C-BE164269425B |
1 |
FC579270-CA86-4153-8DA3-BEA0F5DB6E24 |
7F3FF030-0E7D-4BFF-B560-B43CA5E12CE4 |
3 |
FC579270-CA86-4153-8DA3-BEA0F5DB6E24 |
BB31DC0F-BB57-48FF-B768-79D205F17F33 |
2 |
FC579270-CA86-4153-8DA3-BEA0F5DB6E24 |
8E8CD289-7098-4C4F-A5A1-3DF2C25963AD |
1 |
Обратите внимание. Аналогично способу со структурой со ссылкой на предка столбец «Указатель на предка узла» содержит список всех узлов дерева, имеющих потомков.
Данная модель позволяет проще, чем в случае рекурсивного метода, но несколько сложнее, чем в случае метода правого и левого коэффициентов (из-за необходимости связывания двух таблиц), выполнять практически все выборки, специфические для иерархий.
Приведём несколько простых примеров таких выборок.
Выбор всех потомков для заданного узла (в примере это узел B) может быть осуществлен следующим предложением SQL:
SELECT T_Base.Node
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.UID and
T_Helper.ParentID=(SELECT ID FROM T_Base
WHERE Node='B');
Результат данного запроса представлен на рис. 76.
Рис. 76. Выборка всех потомков узла B
Выбор всех предков для заданного узла (в примере это узел J) осуществляется следующим образом:
SELECT T_Base.Node, T_Helper.Level
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID and
T_Helper.UID=(SELECT ID FROM T_Base
WHERE Node='J')
ORDER BY T_Helper.Level DESC;
Результат выполнения этого запроса представлен на рис. 77.
Рис. 77. Выбор всех предков узла J
Обратите внимание на схожесть этих запросов. Сравните эти запросы с решением данных задач в случае использования рекурсивной модели иерархии.
Выбор узлов, не имеющих потомков, реализуется, например, следующим образом:
SELECT DISTINCT T_Base.Node FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.UID and T_Helper.UID NOT IN (
SELECT DISTINCT ParentID FROM T_Helper);
Результат выполнения этого запроса представлен на рис. 78.
Рис. 78. Узлы, не имеющие потомков
Обратите внимание, что данный запрос очень похож на запрос, который мы использовали для решения той же задачи в случае рекурсивной модели иерархии.
Теперь рассмотрим задачу отыскания ближайшего общего предка для двух заданных узлов. Пусть выбраны узлы I и K. Как видно на рис. 8, их ближайшим общим предком является узел B. Получим полный список общих предков для узлов I и K (рис. 79):
SELECT T_Base.Node, T_Helper.Level, T_Helper.ParentID
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID and
T_Helper.UID=(SELECT ID FROM T_Base WHERE Node='K') AND T_Helper.ParentID IN (
SELECT T_Helper.ParentID
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID and
T_Helper.UID=(
SELECT ID FROM T_Base WHERE Node='I'));
Рис. 79. Полный список общих предков узлов I и K
Из этого списка надо оставить единственную строку, в которой значение в столбце Level минимально. Окончательно получаем (рис. 80):
SELECT T_Base.Node, T_Helper.Level, T_Helper.ParentID
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID AND
T_Helper.UID=(SELECT ID FROM T_Base WHERE Node='K') AND
T_Helper.ParentID IN
(SELECT T_Helper.ParentID
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID AND
T_Helper.UID=(SELECT ID FROM T_Base WHERE Node='I')) AND
T_Helper.Level=(SELECT MIN(T_Helper.Level)
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID AND
T_Helper.UID=(SELECT ID FROM T_Base WHERE Node='K') AND
T_Helper.ParentID IN
(SELECT T_Helper.ParentID
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID AND
T_Helper.UID=(SELECT ID FROM T_Base WHERE Node='I')));
Рис. 80. Ближайший общий предок узлов I и K
Поддержка целостности структуры иерархии требует дополнительныx средств.
Приведём пример хранимой процедуры для добавления листьев в дерево (триггер и в этом случае неприменим, так как надо указывать непосредственного предка добавляемого узла):
CREATE PROCEDURE AddNode (@Parent VARCHAR, @New VARCHAR) AS
-- Добавляем лист
-- ВНИМАНИЕ! Тут должна быть проверка
-- входного параметра!
-- После каждой операции необходима проверка ошибок,
-- отсутствующая в данном примере!
BEGIN
DECLARE @ParentID uniqueidentifier;
DECLARE @NewID uniqueidentifier;
-- Транзакция
BEGIN TRANSACTION AddNodeTransaction;
-- Добавляем строку нового узла в базовую таблицу
INSERT INTO T_Base(Node) VALUES (@New);
-- Получаем уникальный идентификатор нового элемента
SELECT @NewID=ID FROM T_Base WHERE Node=@New;
-- Получаем уникальный идентификатор
-- родительского элемента
SELECT @ParentID=ID FROM T_Base WHERE Node=@Parent;
-- ВНИМАНИЕ! Тут должна быть проверка
-- на существование родительского узла!
-- Добавляем строку с новым листом
-- во вспомогательную таблицу
INSERT INTO T_Helper(UID, ParentID, [Level])
VALUES (@NewID, @ParentID, 1);
-- Добавляем во вспомогательную таблицу строки
-- указывающие предков предка
INSERT INTO T_Helper(UID, ParentID, [Level])
SELECT @NewID, ParentID, [Level]+1
FROM T_Helper
WHERE UID=@ParentID AND
-- условие [Level]>0 нужно для отсечения
-- вставки дублей записей
-- указывающих на корень дерева
-- (иначе получаем сообщение об ошибке)
[Level]>0;
-- ВНИМАНИЕ! Тут должна быть проверка результатов и,
-- если были ошибки, откат транзакции!
COMMIT TRANSACTION AddNodeTransaction;
END;
Создадим эту хранимую процедуру (рис. 81)
Рис. 81. Создание хранимой процедуры
При помощи этой процедуры добавим в нашу иерархию новый узел Z – потомок узла P:
EXEC AddNode 'P', 'Z';
Проверим правильность добавления узла Z, выбрав всех его предков:
SELECT T_Base.Node, T_Helper.Level
FROM T_Base, T_Helper
WHERE T_Base.ID=T_Helper.ParentID and
T_Helper.UID=(
SELECT ID FROM T_Base WHERE Node='Z')
ORDER BY T_Helper.Level DESC;
В результате должен быть получен следующий набор узлов: A–C–H–P (рис. 82).
Рис. 82. Проверка правильности добавления узла Z
Эта хранимая процедура, добавляющая узлы, требует наличия уже существующей записи для корневого узла. Для добавления корневого узла создадим специальную хранимую процедуру:
CREATE PROCEDURE AddRootNode (@NewRoot VARCHAR) AS
-- Добавляем корневой элемент иерархии
-- ВНИМАНИЕ! Тут должна быть проверка
-- входного параметра!
-- После каждой операции необходима проверка ошибок,
-- отсутствующая в данном примере!
BEGIN
DECLARE @RootID uniqueidentifier;
-- Транзакция
BEGIN TRANSACTION AddNodeTransaction;
-- Добавляем строку нового узла в базовую таблицу