PostgreSQL ltree: обеспечение целостности данных

в 9:53, , рубрики: Ltree, postgresql, sql, данные, древовидные структуры, материализованные пути, Программирование, реляционные субд, хранение данных

Здравствуйте. Это версия моей статьи на русском языке. Она у меня первая, поэтому прошу не судить строго.

Одним из способов хранения древовидных структур в реляционных СУБД является “материализованный путь”. В интернете можно найти множество описаний этого и других способов, следует выбирать исходя из вашей задачи. 

В PostgreSQL существует специальное расширение ltree, предоставляющее дополнительные инструменты для работы с метками и путями. О нем и о решении вопросов, связанных с консистентностью (целостностью) данных, и поговорим. 

Итак, задача: сделать таблицу item с полем path типа ltree для удобной работы с деревом, а также обеспечить целостность данных на уровне базы. 

Терминология:

  • метка — строка, состоящая из строчных и заглавных букв, цифр и знаков подчеркивания. Я в качестве метки буду использовать цифровой id записи.

  • путь — список меток, разделенных точкой. Первой меткой должен быть корневой элемент дерева, а последней — метка самой записи.

Создание таблицы

В качестве примера буду использовать тестовую таблицу item, содержащую поля  id(serial) и path(ltree). Для начала включим расширение и создадим таблицу:

CREATE EXTENSION IF NOT EXISTS ltree;

create table public.item
(
    id  serial  constraint item_pk primary key,
    path ltree default ((lastval())::text)::ltree not null constraint item_pk2 unique
);

Поле path не может быть пустым, т.к. если даже элемент корневой, в этом поле должен содержаться id.

В моей задаче планируется большая вложенность и много item, которые будут часто добавляться и перемещаться. Я выбрал тип serial в качестве первичного ключа, т.к. использование буквенно-цифровых отрицательно скажется на объеме базы при росте кол-ва записей и вложенности. Если у вас планируется небольшое количество записей или небольшая вложенность, возможно, использование алфавитно-буквенных идентификаторов вам подойдет больше, т.к. у этого способа есть как минимум один большой плюс - возможность генерировать id на клиенте. В path нельзя положить UUID, т.к. данные в ltree не могут содержать дефис.

Вставка и обновление записей

Таблица создана, попробуем добавить запись. Первая запись будет корневым item, поэтому path будет состоять только из id записи. Для получения id записи в запросе insert воспользуемся функцией lastval(). Выполним запрос трижды:

insert into item(path) values (lastval()::text::ltree);

В таблице появились три записи с корневыми узлами деревьев (id = path).

select * from item;

| id | path |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |

Теперь попробуем вставить строку с ключом, состоящим из несуществующих меток и проверим, что запрос выполнился удачно:

insert into item (path) values ('12.43.555.22.542');

select * from item;

| id | path |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 12.43.555.22.542 |

Теперь данные в таблице не консистентны, т.к. появилась запись с путем path, состоящим из несуществующих меток. С таким же успехом мы можем записать в поле path и “зацикленное” значение пути, вроде “12.42.12” и что угодно еще. Так произошло потому, что без дополнительных действий PostgreSQL позволяет класть в поле path любое значение, состоящее из разрешенных символов. Решим эти проблемы при помощи триггерных функций.

Создадим функцию: 

CREATE OR REPLACE FUNCTION item_before_update_insert_check_path() RETURNS TRIGGER AS $item_before_update_insert_check_path$
DECLARE
    parentPath  ltree;
    curLabel ltree;
    parentId text;
BEGIN
    parentPath := subpath(NEW.path, 0, -1);
    curLabel := subpath(NEW.path, -1);

    -- последняя метка в пути должна равняться id
    if (curLabel::text != NEW.id::text) THEN
        RAISE EXCEPTION 'The last path label % must be equal id %', curLabel::text, NEW.id::text;
    end if;

    -- должна существовать родительская запись с подходящим путем, если новая запись не корневая
    if (parentPath != '') THEN
        parentId := (select id from item where path = parentPath);
        if (parentId IS NULL) THEN
            RAISE EXCEPTION 'Parent item with path % not found', parentPath;
        end if;
    end if;

    RETURN NEW;
END;
$item_before_update_insert_check_path$ LANGUAGE plpgsql;

И сделаем так, чтобы она выполнялась при вставке записи или при изменении path:

CREATE TRIGGER tr_item_before_update_insert_check_path
    BEFORE
        INSERT OR
        UPDATE OF path ON item
    FOR EACH ROW
EXECUTE FUNCTION item_before_update_insert_check_path();

Теперь не получится при вставке или изменении path установить значение, нарушающее консистентность данных в таблице. Для удобства я сделал запись с id=4 корневой, как и остальные. Идем дальше. Сделаем item c id=1 родителем id=2, а затем потом положим 1 в 3:

update item set path = '1.2' where id = 2;
update item set path = '3.1' where id = 1;
select * from item;

| id | path |
| 3 | 3 |
| 2 | 1.2 |
| 4 | 4 |
| 1 | 3.1 |

И снова нарушена целостность. У записи с id 2 неверный path: вместо ‘1.2’ должен быть ‘3.1.2’. Это особенность материализованного списка: при изменении пути элемента надо изменять пути всех дочерних элементов. Обеспечим это. Сначала сделаем снова все записи корневыми:

update item set path = id::text::ltree;

Затем добавим новый триггер:

CREATE OR REPLACE FUNCTION item_after_update_set_children_path() RETURNS TRIGGER AS $item_after_update_set_children_path$
BEGIN

    IF (NEW.path != OLD.path) THEN
        UPDATE item
        SET path = NEW.path || subpath(path, nlevel(OLD.path))
        WHERE path <@ OLD.path;
    END IF;

    RETURN NULL;
END;
$item_after_update_set_children_path$ LANGUAGE plpgsql;


CREATE TRIGGER tr_update_children_item_path
    AFTER UPDATE OF path ON item
    FOR EACH ROW
EXECUTE FUNCTION item_after_update_set_children_path();

а затем снова выполним действия с путями записей 2 и 1:

update item set path = '1.2' where id = 2;
update item set path = '3.1' where id = 1;
select * from item;

| id | path |
| 3 | 3 |
| 4 | 4 |
| 1 | 3.1 |
| 2 | 3.1.2 |

Теперь path дочерних узлов обновляются с path родительского узла. Получилось своего рода каскадное обновление.

Удаление записей

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

CREATE OR REPLACE FUNCTION item_before_delete_check_children() RETURNS TRIGGER AS $item_check_children_before_delete$
DECLARE
    childrenCount bigint;
BEGIN
    SELECT COUNT(*) into childrenCount
    FROM item WHERE path <@ OLD.path AND id != OLD.id;

    IF (childrenCount > 0) THEN
        RAISE EXCEPTION 'Deleting items containing child items by the path field is prohibited.';
    end if;
RETURN OLD;
END;
$item_check_children_before_delete$ LANGUAGE plpgsql;


CREATE TRIGGER tr_item_check_children_before_delete
    BEFORE DELETE ON item
    FOR EACH ROW
EXECUTE FUNCTION item_before_delete_check_children();

Готово.

Итого

Для обеспечения консистентности данных в таблице, содержащей поле ltree, нужно:

  1. Сделать поле path not null;

  2. Добавить уникальный индекс для path;

  3. Добавить триггеры:

    • BEFORE INSERT и BEFORE UPDATE, проверяющие, что последняя метка в поле path является id записи и что в таблице существует запись с path, соответствующим path записи без последней метки.

    • AFTER UPDATE, обновляющий path всех дочерних записей при изменении path (перемещении).

    • BEFORE DELETE, запрещающий удалять записи, содержащие дочерние записи.

Если у вас есть, что добавить, пожалуйста, оставьте комментарий.

Автор: Максим

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js