DOC.PROTOTYPES.RU

Главная > Базы данных > Деревья SQL > Materialized Path > ltree >

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

Задача

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

На самом деле, мне лично смешивание Materialized Path и Adjacency List при использовании ltree представляется сомнительным решением, особенно учитывая то, что в большинстве случаем материализованный путь составляется из 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;

Где:

Соответственно, внешний ключ на каскадное обновление и удаление для поля 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 г.)
Valid HTML 4.01 Transitional
Copyright © 2011 Сергей Томулевич