Главная
Блог разработчиков phpBB
 
+ 17 предустановленных модов
+ SEO-оптимизация форума
+ авторизация через соц. сети
+ защита от спама

Хранение деревьев в базе данных. Часть первая, теоретическая

Anna | 31.05.2014 | нет комментариев

Полгода назад написал бандл ClosureTable для фреймворка Laravel 3. Причиной для написания стала вот этавосхитительная презентация Билла Карвина о методах хранения и обработки иерархических данных в MySQL с применением PHP.

Выходит. Существует несколько образцов проектирования баз данных для хранения и обработки иерархических конструкций:

  • Adjacency List («список смежности»)
  • Materialized Path («материализованный путь»)
  • Nested Sets («вложенные множества»)
  • Closure Table («таблица связей»)

Что такое Closure Table

Суть данного образца проектирования заключается в том, что связи между сущностями хранятся в отдельной таблице, тогда как основная таблица содержит только данные самих сущностей.

Таблица связей должна содержать как минимум два поля:

  • ссылку на родительский элемент (ancestor)
  • ссылку на дочерний элемент (descendant)

Пускай мы трудимся над созданием следующий SuperPuper CMS и приступили к разработке модуля редактирования текстовых страниц. Нам потребуются две таблицы:

  • pages будет содержать данные о странице
  • pages_treepath будет содержать данные об иерархии страниц

Схема таблиц БД
Схема таблиц БД

В качестве примера используем следующую иерархию страниц:

— О компании #1
  — Контакты #3
  — Вакансии #4
     — Администратор по рекламе #5
     — Веб-дизайнер #6
  — Реквизиты
— Цены #2
   — Сайты #7
     — Визитка #10
     — Корпоративный #11
   — Логотипы #8
   — Знаки #9

Выборка дочерних элементов

Вот такой SQL-запрос у нас получится, если мы захотим предпочесть все страницы раздела «О компании».

SELECT * FROM pages p
JOIN pages_treepath t ON (p.id = t.descendant)
WHERE t.ancestor = 1

«Descendant» обозначает «дочерний», а «ancestor» — родительский. Соответственно, Дабы получить все дочерние страницы, мы присоединяем таблицу связей pages_treepath при условии, что идентификатор страницы id имеет то же значение, что и ссылка на дочерний элемент descendant. При этом ссылка ancestorна родительскую страницу равняется 1, идентификатору страницы «О компании».

Выборка родительских элементов

А сейчас снизу вверх: посмотрим всех «родителей» у страницы «Корпоративный».

SELECT * FROM pages p
JOIN pages_treepath t ON (p.id = t.ancestor)
WHERE t.descendant = 11

В этом случае напротив. Мы ищем родительские страницы, следственно присоединяем таблицу связей с условием, что идентификатор страницы id должен равняться ссылке на родительскую страницу ancestor, а выборку осуществляем по ссылке на дочерний элемент descendant, в нашем случае равной 11.

Вставка нового элемента

Дозволено добавить новую вакансию. Данные ценности в нашем случае не представляют, так что посмотрим на сам запрос.

INSERT INTO pages
VALUES (12, 'Администратор по продажам',
        '',
        'Требуется офигенный администратор по продажам',
        '0000-00-00 00:00:00',
        '0000-00-00 00:00:00')

INSERT INTO pages_treepath (ancestor, descendant)
    SELECT ancestor, 12FROM pages_treepath
    WHERE descendant = 4
    UNION ALL
    SELECT 12, 12

С первым запросом все ясно — это простая вставка новых данных. А вот 2-й запрос стоит разобрать по порядку, так что давайте посмотрим, что здесь происходит.

SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4

Исполнив данный запрос, мы получим дальнейший список связей:

-------------------------
| ancestor | descendant |
-------------------------
|    4     |     12     |
|    1     |     12     |
-------------------------

Добавим к предыдущему запросу еще один путем объединения:

SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4
UNION ALL
SELECT 12, 12

В список связей добавится связь-ссылка страницы на саму себя:

-------------------------
| ancestor | descendant |
-------------------------
|    4     |     12     |
|    1     |     12     |
|   12     |     12     |
-------------------------

Как видите, данный SELECT-запрос разрешает установить связи между новой страницей и всеми её «родителями». ancestor — неизменно ссылка на родительский элемент, descendant — ссылка на дочерний элемент. INSERT-запрос, написанный сначала, вставляет полученный итог в таблицу pages_treepath.

Удаление элемента

А сейчас закроем вакансию веб-дизайнера.

DELETE FROM pages_treepath
WHERE descendant = 6

DELETE FROM pages
WHERE id = 6

Тут всё легко. Вначале мы удаляем все связи, где ссылка на дочерний элемент равняется 6 (страница «Веб-дизайнер»), а после этого удаляем и саму страницу.

Удаление вложенного дерева

Внезапно так случилось, что с некоторых пор компания ABC перестала разрабатывать сайты. Нам потребуется исполнить вот такой запрос, Дабы удалить соответствующий подраздел:

DELETE FROM pages
WHERE id IN (
    SELECT descendant FROM (
        SELECT descendant FROM pages p
        JOIN pages_treepath t ON p.id = t.descendant
        WHERE t.ancestor = 7
    ) AS tmptable
)

DELETE FROM pages_treepath
WHERE descendant IN (
    SELECT descendant FROM (
        SELECT descendant FROM pages_treepath
        WHERE ancestor = 7
    ) AS tmptable
)

В различие от предыдущего запроса, данный несколько труднее и вначале удаляются сами страницы и теснее позже этого связи между ними (от того что последние энергично применяются при удалении первых).

Трудность запросов отчасти объясняется тем, что MySQL не разрешает исполнять запрос на удаление записей с условием WHERE, в котором содержится выборка SELECT из той же таблицы. В случае с MySQL мы обязаны разместить SELECT-запросы во временную таблицу. А в всеобщем случае наши запросы выглядели бы так:

DELETE FROM pages
WHERE id IN (
    SELECT descendant FROM pages p
    JOIN pages_treepath t ON p.id = t.descendant
    WHERE t.ancestor = 7
)

DELETE FROM pages_treepath
WHERE descendant IN (
    SELECT descendant FROM pages_treepath
    WHERE ancestor = 7
)

Если вы наблюдательно посмотрите на вложенный SELECT-запрос в DELETE-запросе из таблицы pages, то найдете, что мы теснее рассматривали сходственный запрос. Данный от предыдущего отличается только идентификатором страницы. В итоге выборки мы получаем все дочерние страницы раздела «Сайты» (включая сам раздел), а после этого удаляем все страницы с полученными идентификаторами.

Позже того, как страницы удалены, остаётся удалить связи между ними. Для этого находим все ссылки на дочерний элемент descendant, где ссылка на родительский элемент равняется идентификатору страницы «Сайты».

?рус вложенности

Еще в таблицу связей дозволено добавить поле, контролирующее ярус вложенности элементов. Это поле дозволит составлять больше примитивные запросы на выборку непосредственных прародителей либо непосредственных дочерних элементов. Скажем:

SELECT * FROM pages p
JOIN pages_treepath t ON (p.id = t.descendant)
WHERE t.ancestor = 4
AND t.level = 2

Схема таблиц БД
Схема таблиц БД

Продолжение следует.

Источник: programmingmaster.ru

Оставить комментарий
Форум phpBB, русская поддержка форума phpBB
Рейтинг@Mail.ru 2008 - 2017 © BB3x.ru - русская поддержка форума phpBB