Введение в базы данных

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

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

  • Хранение леса деревьев

Операции

Содержание

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

  • Узлы
    • 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 f.id, count(a.aid)
      from forest f left join ancestor a on f.id = a.id
      group by f.id
      
  • Глубины поддеревьев
    • select aid, max(d.d)
      from ancestor a
          inner join depths d on a.id = d.id
      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(chId, parId, data) as
      select * from forest where pid is not null
      
    • create view parent(id, pid) as
      select * from forest where pid <> id
      

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

  • Как получить
    • create view ancestor(id, aid) as
          with recursive anc(id, aid) as
              select id, pid from forest
              union select d.id, a.aid
                  from anc a inner join forest f
                      on f.pid = a.id;
          select * from anc
      

Вставка

  • Добавление корня
    • insert into forest (id, pid, data)
          values (:id, null, ...)
      
  • Добавление листа
    • 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 < p.l and c.r < p.r and not exists
              (select * from ancestor
                  where (l between p.l and p.r)
                      and (c.l between l and 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 forst (
          id int not null primary key,
          path varchar(100),
          name 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, 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)$ данных для глубоких деревьев
    • Индексы на строках

Литература

Содержание

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