vyegorov,
Тут получится много, но раз уж начал :)
В общем пытаюсь реализовать систему хранения структуры "дерево", почитать о чем речь можно здесь:
http://habrahabr.ru/post/166699/ Я являюсь автором этой статьи.
На данный момент пытаюсь реализовать работу с деревом на сервере. Все вроде работает кроме удаления (и я подозреваю, что такая же история будет и при групповом перемещении... но это маловероятная операция)
При удалении записи идет проверка на "дырки", которые могут быть вызваны удалением. Их пытаюсь "заделать" перемещением крайне правого элемента на место "дырки"
Когда удаляешь одну запись работает прекрасно. Когда удаляешь группу с
count_childs=0
После разбиение триггера и функции AFTER на 2-е - BEFORE и AFTER удалось побороть часть проблем. Но одна осталась. А именно если "дырки" получаются подряд на одном уровне в нескольких местах. То есть дерево "сворачивается" некорректно.
Вот код, может кого и заинтересует :) А может кто-то и подскажет что я делаю неправильно (не судите строго, это типо мое хобби, в коде много корявостей, и всяких казалось бы лишних вещей, связанных с тем что правлю его еще походу)
CREATE OR REPLACE FUNCTION const_ch()
RETURNS integer AS
$BODY$BEGIN
-- устанавливаем допустимое количество Детей у элемента в дереве
RETURN 3;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION const_ch()
OWNER TO postgres;
CREATE OR REPLACE FUNCTION const_lv()
RETURNS integer AS
$BODY$BEGIN
-- устанавливаем допустимое количество Уровней в дереве
RETURN 4;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION const_lv()
OWNER TO postgres;
CREATE OR REPLACE FUNCTION fill_emp(
old_p_id bigint,
old_p_lv integer,
old_p_ch integer,
id_dep bigint)
RETURNS integer AS
$BODY$DECLARE
lv integer; -- количество уровней в дереве
ch integer; -- допустимое количество детей
max_id_for_parent bigint; -- для крайнего правого ребенка
limit_right bigint;
delta_id bigint;
BEGIN
-- У каждого элемента могут быть дети
-- последовательность детей формируется непрерывной,
-- если удалить (или перенести) крайне правого ребенка то нет проблем
-- Если же "выдернуть" ребенка из начала или середины последовательности,
-- то остается "дырка".
-- Надо "заделать" ее переносом крайне правого ребенка.
ch:=const_ch();
lv:=const_lv();
--RAISE EXCEPTION '% + (((%+1)^(%-%-1))*%)',old_p_id, ch, lv, old_p_lv, old_p_ch;
-- Найдем максимально правого существующего ребенка у родителя "дырки"
max_id_for_parent:=old_p_id+(power((ch+1),(lv-old_p_lv-1))*old_p_ch)::bigint;
---RAISE EXCEPTION '%',max_id_for_parent;
IF id_dep < max_id_for_parent THEN -- если изъятый элемент был не последний
-- имеем "дырку"
-- найдем правую границу для возможной подгруппы Элемента с max_id_for_parent
--limit_right:=(max_id_for_parent-1+ power((ch+1),(lv-old_p_lv-1))::bigint)::bigint;
limit_right:=max_id_for_parent-1+ ((ch+1)^(lv-old_p_lv-1))::bigint;
--RAISE EXCEPTION 'id_dep=%, max_id_for_parent=%, limit_right=%', id_dep, max_id_for_parent, limit_right;
-- делаем апдейт всех детей max_id_for_parent
-- перемещение в пределах уровня - просто дельта между max_id_for_parent и id_dep
-- id = (id - max_id_for_parent + id_dep)::bigint,
delta_id:=max_id_for_parent-id_dep;
UPDATE board_group
SET
id=id-delta_id,
parent_id=parent_id-delta_id
WHERE board_group.id BETWEEN max_id_for_parent+1 AND limit_right;
-- не забудем про сам элемент
-- второй апдейт нужен т.к. родитель у перемещаемого Элемента не изменяется
UPDATE board_group
SET
id=id_dep
WHERE board_group.id=max_id_for_parent;
END IF;
RETURN 0;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fill_emp(bigint, integer, integer, bigint)
OWNER TO postgres;
CREATE TABLE board_group
(
id bigint NOT NULL,
lvl integer NOT NULL,
count_childs integer NOT NULL,
nm character varying(150) NOT NULL,
parent_id bigint NOT NULL,
CONSTRAINT board_group_pkey PRIMARY KEY (id),
CONSTRAINT board_group_parent_id_19050eec0369f2c9_fk_board_group_id FOREIGN KEY (parent_id)
REFERENCES board_group (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
ALTER TABLE board_group
OWNER TO postgres;
CREATE INDEX board_group_6be37982
ON board_group
USING btree
(parent_id);
CREATE TRIGGER after_del
AFTER DELETE
ON board_group
FOR EACH ROW
EXECUTE PROCEDURE del_id_board_after();
CREATE TRIGGER after_upd_parent_id
AFTER UPDATE
ON board_group
FOR EACH ROW
EXECUTE PROCEDURE upd_move_board();
CREATE TRIGGER before_del
BEFORE DELETE
ON board_group
FOR EACH ROW
EXECUTE PROCEDURE del_id_board_before();
CREATE TRIGGER before_new_id
BEFORE INSERT
ON board_group
FOR EACH ROW
EXECUTE PROCEDURE new_id_board();
CREATE OR REPLACE FUNCTION del_id_board_after()
RETURNS trigger AS
$BODY$DECLARE
old_p_id bigint;
old_p_lv integer;
old_p_ch integer;
i integer;
BEGIN
-- возьмем Родителя удаленного Элемента
SELECT id, lvl, count_childs
INTO old_p_id, old_p_lv, old_p_ch
FROM board_group
WHERE id=OLD.parent_id;
IF (old_p_ch > 0) THEN
--- RAISE EXCEPTION 'old_p_id=%, old_p_lv=%, old_p_ch=%, OLD.id=%',old_p_id, old_p_lv, old_p_ch, OLD.id;
i := fill_emp(old_p_id, old_p_lv, old_p_ch+1, OLD.id);
END IF;
RETURN NULL;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION del_id_board_after()
OWNER TO postgres;
CREATE OR REPLACE FUNCTION del_id_board_before()
RETURNS trigger AS
$BODY$DECLARE
old_p_id bigint;
old_p_lv integer;
old_p_ch integer;
i integer;
BEGIN
IF OLD.count_childs > 0 THEN
RAISE EXCEPTION 'У данного элемента имеются дети в количестве % шт. Удалите сначала их.', OLD.count_childs;
END IF;
-- возьмем Родителя удаленного Элемента
SELECT id, lvl, count_childs
INTO old_p_id, old_p_lv, old_p_ch
FROM board_group
WHERE id=OLD.parent_id;
-- уменьшим количество детей у родителя
UPDATE board_group SET count_childs = count_childs-1 WHERE id = OLD.parent_id;
RETURN OLD;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION del_id_board_before()
OWNER TO postgres;
CREATE OR REPLACE FUNCTION new_id_board()
RETURNS trigger AS
$BODY$DECLARE
lv integer; -- количество уровней в дереве
ch integer; -- допустимое количество детей
id_new bigint; -- вычесляемый id нового элемента
id_p bigint; -- id родителя
lv_p integer; -- уровень родителя
count_childs_p integer; -- количество детей родителя
BEGIN
-- для создания нового элемента достаточно заполнить у него поле parent
id_p := NEW.parent_id;
ch := const_ch();
lv := const_lv();
-- найдем родителя
SELECT lvl, count_childs INTO lv_p, count_childs_p FROM board_group WHERE board_group.id=id_p;
-- Проверка 1: может ли родитель еше иметь детей
IF count_childs_p >= ch THEN
RAISE EXCEPTION 'Родителю c id=% более нельзя иметь детей (их количество уже %)', id_p, ch;
END IF;
-- Проверка 2: может ли родитель иметь детей
IF lv_p = lv THEN
RAISE EXCEPTION 'Элементу с id=% нельзя иметь детей (его уровень % максимально возможный)', id_p, lv;
END IF;
NEW.id := ((power((ch+1),(lv-lv_p-1)))*(count_childs_p+1))::bigint+id_p;
NEW.lvl := lv_p + 1;
NEW.count_childs := 0;
NEW.nm := NEW.id; -- ЭТО ТОЛЬКО ДЛЯ ТЕСТОВ, затем убрать
UPDATE board_group SET count_childs = count_childs_p+1 WHERE board_group.id = id_p;
return NEW;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION new_id_board()
OWNER TO postgres;
CREATE OR REPLACE FUNCTION upd_move_board()
RETURNS trigger AS
$BODY$DECLARE
lv integer;
ch integer;
new_p_id bigint;
new_p_lv integer;
new_p_ch integer;
old_p_id bigint;
old_p_lv integer;
old_p_ch integer;
delta_lv integer;
dlv integer;
limit_right bigint;
max_lv integer;
i integer;
id_dest bigint;
id_dep bigint;
x bigint;
BEGIN
IF (NEW.id = OLD.id) AND (NEW.parent_id != OLD.parent_id) THEN
IF NEW.id = NEW.parent_id THEN
RAISE EXCEPTION 'Перенос id=% к самому себе невозможен', NEW.id;
END IF;
ch := const_ch();
lv := const_lv();
-- Имеем перенос к другому родителю
-- найдем этого другого родителя
new_p_id := NEW.parent_id;
SELECT lvl, count_childs INTO new_p_lv, new_p_ch FROM board_group WHERE board_group.id=new_p_id;
-- Проверка 1: может ли родитель еше иметь детей
IF new_p_ch >= ch THEN
RAISE EXCEPTION 'Родителю c id=% более нельзя иметь детей (их количество уже %)', new_p_id, ch;
END IF;
-- Проверка 2: может ли родитель иметь детей
IF new_p_lv = lv THEN
RAISE EXCEPTION 'Элементу с id=% нельзя иметь детей (его уровень % максимально возможный)', new_p_id, lv;
END IF;
old_p_id := OLD.parent_id;
SELECT lvl, count_childs INTO old_p_lv, old_p_ch FROM board_group WHERE board_group.id=old_p_id;
delta_lv := old_p_lv - new_p_lv;
dlv:=@delta_lv;-- модуль
-- Так как переносим элемент и всех его детей
-- то найдем правую границу диапазона всех этих элементов
id_dep := OLD.id;
limit_right := id_dep-1 + ((ch+1)^(lv-old_p_lv-1))::bigint;
-- Найдем максимальное значения поля lvl для продгруппы переноса
SELECT max(lvl) INTO max_lv FROM board_group WHERE board_group.id BETWEEN (id_dep+1) AND limit_right;
IF (max_lv - delta_lv) > lv THEN
RAISE EXCEPTION 'Перенос невозможен из-за выхода переносимых элементов за нижний уровень';
END IF;
-- найдем новый id для головного элемента, он понадобится в апдейте переносимой группы
id_dest :=new_p_id+(((ch+1)^(lv-new_p_lv-1))*(new_p_ch+1))::bigint;
UPDATE board_group
SET
id = id_dest,
lvl = lvl-delta_lv,
parent_id = new_p_id
WHERE id=id_dep;
x := ((ch+1)^dlv)::bigint;
--RAISE EXCEPTION 'x=%',x;
IF delta_lv < 0 THEN -- вниз
UPDATE board_group
SET
id = ((id-id_dep)/x) + id_dest,
lvl = lvl-delta_lv,
parent_id = ((parent_id-id_dep)/x) + id_dest
WHERE id BETWEEN (id_dep+1) AND limit_right;
ELSE -- вверх
UPDATE board_group
SET
id = ((id-id_dep)*x) + id_dest,
lvl = lvl-delta_lv,
parent_id = ((parent_id-id_dep)*x) + id_dest
WHERE id BETWEEN (id_dep+1) AND limit_right;
END IF;
-- Увеличиваем количество детей у нового родителя
UPDATE board_group SET count_childs = count_childs+1 WHERE id = new_p_id;
-- Уменьшаем количество детей у старого родителя
UPDATE board_group SET count_childs = count_childs-1 WHERE id = old_p_id;
-- проверим на дырки
IF (old_p_ch > 1) THEN
i := fill_emp(old_p_id, old_p_lv, old_p_ch, id_dep);
END IF;
END IF;
RETURN NEW;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION upd_move_board()
OWNER TO postgres;