doc.prototypes.ru: Проекты: Деревья SQL: Materialized Path: ltree: Управление с использованием алгоритма Adjacency List

Управление с использованием алгоритма Adjacency List

Задача

"Прозрачное" управление, совместно с алгоритмом Adjacency List.

На самом деле, мне лично смешивание Materialized Path и Adjacency List при использовании ltree представляется сомнительным решением, особенно учитывая то, что в большинстве случаем материализованный путь составляется из ID узлов, и ID родителя получить не представляется сложной задачей. Тем не менее, определенные плюсы от этого есть, а именно:

Прямая связь с родителем и возможность навесить FOREIGN KEY;
Возможность просто указывать id родителя не тратя время на формирование материализованного пути изменяегмого узла;

Универсальная хранимая процедура

Вообще идея подобной процедуры не моя, а моего коллеги, но он так до конца её и не реализовал. Эту реализацию я сделал в том числе и для него.

Таблица должна иметь следующие свойства:

материализованных путь формируется из id узлов;
имеется возможность хранения нескольких независимых деревьев;

В таблице должны присутсвовать следующие обязательные поля:

SQL код (1)

CREATE TABLE "public"."section" (
    "id"            SERIAL, 
    "parent"        INTEGER,
    "root"          INTEGER             NOT NULL,
    "path"          "public"."ltree"    NOT NULL,
        CONSTRAINT "section__path__key" UNIQUE("path"), 
        CONSTRAINT "section__pkey" PRIMARY KEY("id"), 
        CONSTRAINT "section__section__fkey" FOREIGN KEY ("parent")
        REFERENCES "public"."section"("id")
            ON DELETE CASCADE
            ON UPDATE CASCADE
            NOT DEFERRABLE
) WITHOUT OIDS;

Где:

id - собственно id;
pid - id родителя, NULL если узел корневой;
root - идентификатор дерева, обязательное поле. Если в таблице будет храниться одно дерево, то для него можно поставить значение по умолчанию, например, 1;
path - материализованный путь;

Соответственно, внешний ключ на каскадное обновление и удаление для поля pid.

Сама хранимая процедура выглядит так:

SQL код (2)

CREATE OR REPLACE FUNCTION "public"."ltree_modify" (
)
RETURNS trigger AS
$body$
DECLARE
    prnt            record;
    ppath           ltree           := '';
BEGIN 
-- Операции использующие переменную NEW
    IF tg_op <> 'DELETE' THEN
        IF NEW.parent IS NOT NULL THEN
            EXECUTE 'SELECT * FROM ' || tg_table_schema || '.' || tg_table_name ||
                       ' WHERE id = ' || NEW.parent INTO prnt;
            IF prnt.id IS NULL THEN
                RAISE EXCEPTION 'parent is not exists: %', NEW.parent;
            END IF;
-- проверяем принадлежность к дереву в таблице
            IF NEW.root IS NULL THEN
                NEW.root := prnt.root;
            ELSE
                IF NEW.root <> prnt.root THEN
                    RAISE EXCEPTION 'different roots: % <=> %',
                        prnt.root, NEW.root;
                END IF;
            END IF;
            ppath := prnt.path;
        ELSE
            IF NEW.root IS NULL THEN
                RAISE EXCEPTION 'root is required';
            END IF;
        END IF;
        NEW.path := ppath || NEW.id::text;
    END IF;
-- Операции использующие переменную NEW и OLD
    IF tg_op = 'UPDATE' THEN
-- Проверяем что бы перенос узла был не пределах подчинения
        IF NEW.path <> OLD.path AND NEW.path <@ OLD.path THEN
            RAISE EXCEPTION 'can''t move node: % => %', OLD.path, NEW.path;
        END IF;
    END IF;
-- Возвращаем
    IF tg_op <> 'DELETE' THEN RETURN NEW; ELSE RETURN OLD; END IF;
END;
$body$
LANGUAGE 'plpgsql';

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

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

Создаем триггер для таблицы.

SQL код (3)

CREATE TRIGGER "section_tr" BEFORE INSERT OR UPDATE OR DELETE 
ON "public"."section" FOR EACH ROW 
EXECUTE PROCEDURE "public"."ltree_modify"();

Увы, при изменении подчиненности узла, вышеуказанный триггер не распространится на детей, соответсвенно потребуется произвести ручное обновление (внешний ключ не поможет так как id не обновляется):

SQL код (4)

CREATE OR REPLACE FUNCTION "public"."ltree_after_update" (
)
RETURNS trigger AS
$body$
BEGIN 
    IF NEW.path <> OLD.path THEN
        EXECUTE 'UPDATE ' || tg_table_schema || '.' || tg_table_name ||
                   ' SET path = NULL
                     WHERE parent = ' || NEW.id;
    END IF;
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER "section_tr1" AFTER UPDATE 
ON "public"."section" FOR EACH ROW 
EXECUTE PROCEDURE "public"."ltree_after_update"();

То что удаление подчиненных записей производится до удаления родительской тоже не совсем хорошо, но опять же, все зависит от того, насколько часто производится удаление.

Сергей Томулевич aka Phoinix (10.06.2010 г.)

DOC.PROTOTYPES.RU

Управление с использованием алгоритма Adjacency List

Задача

Универсальная хранимая процедура