Базы данных

Иерархические данные

Что требуется

Хранение леса деревьев
Элементы одного типа

Операции

Содержание

Операции
Модель близости
Вложенные множества
Модель путей
Литература

Базовые представления

Узлы
- forest(id, data)
Ребёнок-родитель
- parent(id, pid)
Наследник-предок
- ancestor(id, aid)

Навигационные запросы

Родитель?
- ```
select pid from parent where id = :id;
```
Дети?
- ```
select id from parent where pid = :id;
```

Предки?

select aid from ancestor where id = :id;

Потомки?

select id from ancestor where aid = :id;

Пути

Путь к предку?

select aid from ancestor
where id = :id and
    aid not in (select aid from ancestor where id = :aid)

Общие предки?

select aid from ancestor where id in (:id1, :id2)
group by aid having count(id) = 2

Путь между узлами?

select aid from ancestor where id in (:id1, :id2)
group by aid having count(id) = 1

Предикаты

Корни?

select id from forest where
    id not in (select id from ancestor)

select id from forest where
    id not in (select id from parent)

Листы?

select id from forest where
    id not in (select aid from ancestor)

select id from forest where id
    not in (select pid from parent)

Статистика

Глубины узлов?

select id, count(aid) as depth
from forest natural join ancestor
group by id

Глубины поддеревьев?

select aid, max(depth) as depth
from ancestor natural join forest
group by aid;

Упорядочивание

Порядок на детях
- Неупорядоченные деревья
- Упорядоченные деревья
Порядок на результате
- Inorder
- Удобно при выводе в виде дерева

Обновление

Добавление корня
Добавление листа
Удаление листа
Удаление поддерева
Перемещение поддерева

Модель близости

Описание

Ссылка на предка

Модель близости

Представление данных
- Храним предка узла

Представление в базе данных

create table forest (
    id int not null primary key,
    pid int references forest(id),
    data ...
);

Как описать корень?
- pid == null или pid == id

Ребёнок-родитель

Как получить?

create view parent(id, pid, data) as
select id, pid, data 
from forest where pid is not null

create view parent(id, pid, data) as
select id, pid, data
from forest where pid <> id

Наследник-предок

Как получить?

create view ancestor(id, aid) as
with recursive anc(id, aid) as
    select id, pid from forest
    union 
    select forest.id, ancestor.aid
      from anc inner join forest
         on forest.pid = acestor.id;
select id, aid 
from anc

Вставка

Добавление корня?

insert into forest (id, pid, data)
values (:id, null, ...)

insert into forest (id, pid, data)
values (:id, :id, ...)

Добавление листа?

insert into forest (id, pid, data)
values (:id, :pid, ...)

Удаление и перемещение

Удаление листа?
- ```
delete from forest where id = :id
```

Удаление поддерева?

delete from forest where id = :id
    or id in (select id from ancestor where aid = :id)

Перемещение поддерева?

update forest set pid = :id where id = :id

Упорядочивание

Порядок на детях?
- Добавить поле index
Как упорядочить результаты?
- Страшная боль

Характеристики

Плюсы
- Мало данных
- Простота обновлений
Минусы
- Рекурсивные запросы
- Нет упорядочивания

Вложенные множества

Содержание

Операции
Модель близости
Вложенные множества
Модель путей
Литература

Описание

Времена входа и выхода в эйлеровом обходе

Представление

Обход дерева в глубину
- Время входа
- Время выхода

Представление в базе данных

create table forest (
    id int not null primary key,
    l int,
    r int,
    data ...,
    constraint ordered check (l < r)
);

Наследник-предок

Как получить?

create view ancestor(id, aid) as
select d.id, a.id
from forest d, forest a
where d.l between a.l and d.r

Ребёнок-родитель

Как получить?

create view parents(id, pid) as
select c.id, p.id
from forest c, forest p
where c.l between p.l and p.r 
    and not exists
        (select * from forest i
            where (i.l between p.l and p.r)
                and (c.l between i.l and i.r))

Вставка

Добавление корня?

select max(r) + 1 into m from forest;
insert into forest (id, l, r, data)
    values (:id, m + 1, m + 2, ...)

Добавление листа?

select r in rr from forest where id = :pid;
update forest set l = l + 2 where l >= rr;
update forest set r = r + 2 where r > = rr;
insert into forest (id, l, r, data)
    values (:id, rr, rr + 1, ...);

Удаление

Удаление листа?

select r into rr from forest where id = :id;
update forest set l = l - 2 where l > rr;
update forest set r = r - 2 where r > rr;
delete from forest where id = :id

Удаление поддерева?

select l into ll, r into rr from forest where id = :id;
delete from forest where l between(ll, rr)
update forest set l = l - (rr - ll + 1) where l > rr;
update forest set r = r - (rr - ll + 1) where r > rr;

Перемещение поддерева

Как сделать?
- Что и куда вставляем
- Перенести в бесконечность
- Переместить середину
- Переместить на место

Ускорение

Как можно ускорить изменения?
- Разреженные деревья
- Проблемы с поддержкой разрежения
  - Вставка узлов разной глубины
- Ленивая поддержка разрежения
  - Переразрежение части дерева

Упорядочивание

Порядок на детях?
- Уже есть
Как упорядочить результаты?
- order by l

Характеристики

Плюсы
- Мало данных
- Упорядочивание
- Хорошая работа с индексами
Минусы
- Сложность обновлений
- Обновление за $O(n)$ или разреженные деревья

Модель путей

Содержание

Операции
Модель близости
Вложенные множества
Модель путей
Литература

Описание

Храним путь от корня

Представление

Составные идентификаторы пути
- $l_1.l_2.l_3...l_k$
Представление в базе данных
- ```
create table forest (
    id int not null primary key,
    path varchar(100)
);
```
- Фиксированная длина меток L
- Операции на строках: like, replace

Наследник-предок

Как получить?

create view ancestor(id, aid) as
select d.id, a.id
from forest d, forest a
where a.path like (d.path || '.%')

Ребёнок-родитель

Как получить?

create view child(id, aid) as
select c.id, p.id
from forest c, forest p
where p.path like (c.path || '.%') and
    length(p.path) + L + 1 = length(c.path)

Вставка

Добавление корня?

insert into forest (id, path, data)
values (:id, :label, ...)

Добавление листа?

insert into forest (id, ppath, data)
values (:id, :ppath || '.' || :label, ...)

insert into forest (id, path, data)
values (
    :id,
    (select path from forest where id = :pid) || '.' || :label,
    ...
)

Удаление и перемещение

Удаление листа?
- ```
delete from forest where id = :id
```

Удаление поддерева?

delete from forest 
where path like 
    ((select path from forest where id = :id) || ".%")

Перемещение поддерева?
- Заменить префиксы путей себе и потомкам

Упорядочивание

Порядок на детях?
- По увеличению меток
Как упорядочить результаты?
- order by path

Характеристики

Плюсы
- Упорядочивание
- Простой поиск детей и потомков
- Простота обновлений
Минусы
- $O(n^2)$ данных для глубоких деревьев
- Индексы на строках

Литература

Содержание

Операции
Модель близости
Вложенные множества
Модель путей
Литература

Основная литература

Celko J. Деревья в SQL http://www.codenet.ru/db/other/trees/
Celko J. SQL for Smarties (глава 28)