Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Моделирование иерархических объектов средствами...doc
Скачиваний:
4
Добавлен:
08.09.2019
Размер:
9.85 Mб
Скачать

Задачи повышенной сложности

  1. Выбрать всех непосредственных потомков для некоторого заданного элемента.

  2. Найти ближайшего общего предка для нескольких наперёд заданных узлов.

  3. Написать хранимые процедуры для пересчёта правого и левого коэффициентов при удалении узлов в иерархии.

  4. Написать хранимые процедуры для пересчёта правого и левого коэффициентов при добавлении узлов в иерархию.

  5. Рассмотреть вариант модели, когда значения правого и левого коэффициентов для листьев в дереве устанавливаются равными.

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;

-- Добавляем строку нового узла в базовую таблицу