li7y/migrations/20240703122248_item_add_parent.up.sql

51 lines
1.1 KiB
MySQL
Raw Permalink Normal View History

2024-07-05 12:38:45 +02:00
-- SPDX-FileCopyrightText: 2024 Simon Bruder <simon@sbruder.de>
--
-- SPDX-License-Identifier: AGPL-3.0-or-later
ALTER TABLE items
ADD parent UUID REFERENCES items(id);
CREATE RECURSIVE VIEW item_tree (id, parents) AS (
SELECT id, ARRAY[]::UUID[] AS parents FROM items WHERE parent IS NULL
UNION
SELECT items.id, item_tree.parents || items.parent FROM items, item_tree WHERE items.parent = item_tree.id
);
CREATE FUNCTION check_item_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 items
WHERE id = NEW.parent
UNION
SELECT items.id, items.parent
FROM items, cte
WHERE items.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_cycle
BEFORE INSERT OR UPDATE ON items
FOR EACH ROW
EXECUTE FUNCTION check_item_cycle();