(5 оценок, среднее: 4,80 из 5)
Загрузка...
Учебная работа. Реферат: Иерархические справочники с линейным временем доступа
Глеб Земсков
Введение
Разработка иерархических справочников – довольно нередко встречающаяся задачка в бизнес-приложениях. Существует довольно много алгоритмов хранения дерева в реляционных СУБД. В данной статье будет поведано о одной из таковых моделей. Ее плюсы – простота реализации, быстрота подборки и прибавления новейшего элемента, а посреди недочетов можно выделить относительную сложность вставки и перемещения данных, также конечную глубину иерархии. Но те либо другие недочеты имеются в хоть какой схеме хранения иерархических данных в РСУБД.
Как неплох метод
Для иерархических справочников мы определим несколько более нередко встречающихся задач, которые затрагивают иерархию.
получение всех потомков узла;
получение конкретных потомков узла;
добавление потомка;
удаление узла с потомками;
перенос узла.
Иерархия Дьюи (Dewey)
Иерархический справочник быть может основан на методе записи, применяемом в системе десятичной систематизации Дьюи (Dewey Decimal Classification). Нас в данный момент интересует не сам классификатор, а применяемый в нем принцип. Попробую его обрисовать.
Любой узел содержит некий идентификатор, неповторимый посреди потомков его родителя. Любой узел содержит путь от корневого элемента к данному. Путь реализуется при помощи идентификаторов, разбитых эмблемой точки.
к примеру:
1 Организация «Рога и копыта».
1.1 Департамент «Рога».
1.1.1 Отдел реализации рогов.
1.1.2 Отдел покупки рогов.
1.1.2.1 Группа оценки свойства рогов.
1.1.3 Отдел проката рогов.
1.2. Департамент «Копыта»
1.2.1 Отдел покупки копыт.
1.2.2 Отдел реализации копыт.
Как можно сходу увидеть, при работе с схожим классификатором комфортно применять оператор LIKE. Если указывается путь, в каком исходные знаки не являются маской, база данных может применять индекс с операцией index scan с диапазонным поиском.
Сделаем тестовый пример.
CREATE TABLE DEPARTMENT
(
ID INT PRIMARY KEY IDENTITY(1,1),
Path VARCHAR(180) UNIQUE,
Position INT NOT NULL,
NAME VARCHAR(128)
)
GO
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1’, 1, ‘Организация «Рога и копыта»’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.1’, 1, ‘Департамент «Рога»’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.1.1’, 1, ‘Отдел реализации рогов’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.1.2’, 2, ‘Отдел покупки рогов’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.1.2.1’, 1, ‘Группа оценки свойства рогов’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.1.3’, 3, ‘Отдел проката рогов’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.2’, 2, ‘Департамент «Копыта»’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.2.1’, 1, ‘Отдел покупки копыт’)
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.2.2’, 2, ‘Отдел реализации копыт’)
GO
Расчет длины поля Path
До этого всего следует уточнить, почему поле Path имеет длину 180. Расчет прост. количество подчиненных отделов всякого узла в справочнике навряд ли быть может больше, чем трехзначная цифра (от 0 до 999 подразделений). Такое не под силу даже таковым гигантам, как Газпром. Делим количество занятых знаков 4 (беря во внимание точку) и получаем уровень вероятных вложений – 60. Цифра также запредельная. Можно подойти с иной стороны. Уровень вложений навряд ли будет больше 20. Делим 180 на 20, и получаем 9 знаков. 8 знаков (беря во внимание точку) в десятичной системе – это 10 миллионов подразделений. Таковым образом, 180 знаков в данном случае довольно, чтоб обрисовать лишнее число организаций и отделов, но недостаточно, чтоб размер очень влиял на производительность базы данных. И это при том, что мы рассчитывали самые нехорошие случаи. В реальности, вместимость иерархии существенно больше. Если количество данных больше, то размер Path можно прирастить. Но для данного справочника его размера довольно. И этого размера хватало для большинства бизнес-приложений, с которыми я встречался.
Получение всех потомков узла.
Допустим, мы собираемся получить все подразделения, входящие в отдел «Рога и Копыта».
При помощи Path родителя создаем обычной запрос.
SELECT * FROM DEPARTMENT WHERE Path LIKE ‘1.1.%’
Добавив к условию в операторе LIKE, мы указали запросу избрать все записи, имеющие Path длиннее, чем у родителей.
Таковой запрос также быть может построен относительно данных родительского узла.
SELECT result.*
FROM DEPARTMENT parent INNER JOIN DEPARTMENT result
ON (result.Path LIKE parent.Path + ‘.%’)
WHERE parent.NAME = ‘Департамент «Рога»’
Получение конкретных потомков узла
Возьмем предшествующий запрос и добавим отрицательное условие для конкретных потомков данного Path.
SELECT * FROM DEPARTMENT
WHERE Path LIKE ‘1.1.%’ AND Path NOT LIKE ‘1.1.%.%’
В итоге мы получим все подчиненные элементы от узла “Департамент «Рога»”. Можно избрать сходу несколько уровней:
SELECT * FROM DEPARTMENT
WHERE Path LIKE ‘1.1.%’ AND Path NOT LIKE ‘1.1.%.%.%’
Добавление потомков.
В данном случае нам необходимо вставить запись по определенному пути с неповторимым идентификатором Position. Сделаем подчиненный элемент узла со значением Path 1.1. Неповторимость идентификатора принципиальна лишь для самих потомков. Потому вычислим наибольшее сходу, то запрос не представляет трудности:
INSERT INTO DEPARTMENT (Path, Position, NAME)
VALUES (‘1.1.4’, 4, ‘Отдел проката копыт’)
Если Position неизвестен, то можно получить его в запросе:
INSERT INTO DEPARTMENT (Path, Position, NAME)
SELECT ‘1.1’ + ‘.’+ ISNULL(CAST(MAX(Position)+1 AS VARCHAR), ‘1’),
ISNULL(MAX(Position)+1, 1), ‘Отдел проката копыт’
FROM DEPARTMENT
WHERE Path LIKE ‘1.1.%’ AND Path NOT LIKE ‘1.1.%.%’
ПРЕДУПРЕЖДЕНИЕ
В многопользовательской среде, для неких баз данных, таковых, как MSSQL, схожее добавление является традиционным случаем фантома. Чтоб преодолеть данную делему, можно повысить уровень транзакции до Serializable, применять в качестве поля Position автоинкрементальное поле либо просто учесть, что можно получить ошибку при вставке схожих значений в неповторимый индекс поля Path.
Удаление узла с потомками
Удаление похоже на операцию подборки кроме того, что мы также должны удалить текущий узел:
DELETE FROM DEPARTMENT
WHERE Path LIKE ‘1.1%’
При помощи доборной точки в аргументе оператора LIKE можно удалить все дочерние элементы без родительского узла:
DELETE FROM DEPARTMENT
WHERE Path LIKE ‘1.1.%’
Имеет смысл выстроить триггер, который будет автоматом удалять дочерние элементы:
CREATE TRIGGER DELETE_NODES_TR
ON DEPARTMENT AFTER DELETE
AS
DECLARE @ParentPath VARCHAR(180)
BEGIN
SELECT @ParentPath=Path FROM deleted
DELETE FROM DEPARTMENT WHERE Path LIKE @ParentPath+’.%’
END
В этом случае можно гарантировать, что узел будет удаляться вкупе с дочерними элементами, и команда удаления еще наиболее упростится.
DELETE FROM DEPARTMENT WHERE Path=’1.1′
Перенос узла
Перенос узла – наиболее непростая операция, чем предшествующая. Для нее необходимо будет выполнить две команды обновления. к примеру, перенесем узел с Path 1.1, сделав его дочерним узлом по отношению к узлу 1.2. Первой командой мы перенесем сам узел:
UPDATE DEPARTMENT
SET Path =
(SELECT ‘1.2.’ + ISNULL(CAST(MAX(D.Position) + 1 AS VARCHAR), ‘1’)
FROM DEPARTMENT D
WHERE D.Path LIKE ‘1.2.%’ AND D.Path NOT LIKE ‘1.2.%.%’),
Position =
(SELECT ISNULL(MAX(D.Position) + 1, ‘1’)
FROM DEPARTMENT D
WHERE D.Path LIKE ‘1.2.%’ AND D.Path NOT LIKE ‘1.2.%.%’)
WHERE Path = ‘1.1’
2-ой командой мы обновим все идентификаторы Path для дочерних частей:
UPDATE DEPARTMENT SET Path=STUFF(Path, 1, 3, ‘1.2.4’)
WHERE Path LIKE ‘1.1.%’
Так же, как и в случае с удалением, мы можем выстроить триггер, который будет гарантированно адаптировать дочерние ссылки, также смотреть за корректностью поля Position:
CREATE TRIGGER UPDATE_NODES_TR
ON DEPARTMENT
AFTER UPDATE
AS
DECLARE
@OldParentPath VARCHAR(180),
@NewParentPath VARCHAR(180),
@ParentPosition INT,
@RealParentPosition INT
BEGIN
IF UPDATE(Path)
BEGIN
SELECT @OldParentPath = Path FROM deleted
SELECT @NewParentPath = Path, @ParentPosition = Position FROM inserted
— если поле Position неправильно, то обновляем его согласно Path
SELECT @RealParentPosition = CAST(RIGHT(@NewParentPath,
CHARINDEX(‘.’, REVERSE(@NewParentPath)) — 1) AS INT)
IF (@RealParentPosition <> @ParentPosition)
UPDATE DEPARTMENT
SET Position = @RealParentPosition
WHERE Path = @NewParentPath
— обновляем все дочерние элементы
UPDATE DEPARTMENT
SET Path = STUFF(Path, 1, LEN(@OldParentPath), @NewParentPath)
WHERE Path LIKE @OldParentPath+’.%’
END
END
Некие дополнения
Одним из нужных параметров данного метода является возможность сортировать данные согласно иерархии. Это весьма полезное и нередко применяемое свойство. Если довольно часты воззвания согласно иерархии, и если дозволяет применяемая СУБД, стоит хранить таблицу в состоянии, сортированном по полю Path.
Если вы желаете сортировать последовательность конкретно подчиненных частей, то можно ввести доп цифру, в какой будет лежать количество цифр в элементе. К примеру, для Position c номером 2 идентификатор в Path будет равен 12, где 1 – количество знаков в идентификаторе. А если Position равен 12, то идентификатор будет равен 212. В этом случае сортировка строковых данных будет совпадать с последовательностью числовых, и мы получим вполне сортированный Path.
Еще ужаснее обстоит дело, если необходимо воплотить операцию вставки. Если адаптировать все Path на подчиненные и примыкающие узлы. При всем этом пропадает основное достоинство метода – линейная скорость вставки. Потому, если предметная область не просит показа классификатора юзерам, можно сохранять раздельно позиции в последовательности подчиненных частей.
У иерархического справочника, построенного по описанному принципу, как, фактически, и у всех узнаваемых алгоритмов построения иерархий в реляционной системе, есть свои недочеты. С его помощью недозволено создавать иерархии с весьма большенный глубиной. Для таковых задач есть остальные методы. Но для большинства бизнес-приложений он не только лишь подходящ, да и владеет таковыми плюсами, как быстрота работы и простота использования.
]]>