51 lines
1.2 KiB
MySQL
51 lines
1.2 KiB
MySQL
|
-- SPDX-FileCopyrightText: 2024 Simon Bruder <simon@sbruder.de>
|
||
|
|
--
|
||
|
|
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
||
|
|
|
||
|
|
DROP TRIGGER prevent_item_class_recursion ON item_classes;
|
||
|
|
DROP FUNCTION check_item_class_recursion_depth;
|
||
|
|
|
||
|
|
CREATE FUNCTION check_item_class_cycle()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
IF NEW.parent IS NULL THEN
|
||
|
|
RETURN NEW;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF NEW.id = NEW.parent THEN
|
||
|
|
RAISE EXCEPTION 'Cycle detected';
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF (WITH RECURSIVE cte AS (
|
||
|
|
SELECT id, parent
|
||
|
|
FROM item_classes
|
||
|
|
WHERE id = NEW.parent
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
SELECT item_classes.id, item_classes.parent
|
||
|
|
FROM item_classes, cte
|
||
|
|
WHERE item_classes.id = cte.parent
|
||
|
|
)
|
||
|
|
SELECT 1
|
||
|
|
FROM cte
|
||
|
|
WHERE parent = NEW.id
|
||
|
|
LIMIT 1) THEN
|
||
|
|
RAISE EXCEPTION 'Cycle detected';
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER prevent_item_class_cycle
|
||
|
|
BEFORE INSERT OR UPDATE ON item_classes
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_item_class_cycle();
|
||
|
|
|
||
|
|
CREATE RECURSIVE VIEW item_class_tree (id, parents) AS (
|
||
|
|
SELECT id, ARRAY[]::UUID[] AS parents FROM item_classes WHERE parent IS NULL
|
||
|
|
UNION ALL
|
||
|
|
SELECT item_classes.id, item_class_tree.parents || item_classes.parent FROM item_classes, item_class_tree WHERE item_classes.parent = item_class_tree.id
|
||
|
|
);
|