43 lines
1.2 KiB
MySQL
43 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 TYPE item_class_type AS ENUM ('generic', 'specific');
|
||
|
|
|
||
|
|
ALTER TABLE item_classes
|
||
|
|
ADD type item_class_type;
|
||
|
|
|
||
|
|
UPDATE item_classes SET type='generic' WHERE parent IS NULL;
|
||
|
|
UPDATE item_classes SET type='specific' WHERE parent IS NOT NULL;
|
||
|
|
|
||
|
|
ALTER TABLE item_classes
|
||
|
|
ALTER type SET NOT NULL;
|
||
|
|
ALTER TABLE item_classes
|
||
|
|
ALTER type SET DEFAULT 'generic';
|
||
|
|
|
||
|
|
ALTER TABLE item_classes
|
||
|
|
ADD CONSTRAINT parent_only_for_specific CHECK (type = 'generic' AND parent IS NULL OR type = 'specific' AND parent IS NOT NULL);
|
||
|
|
|
||
|
|
CREATE FUNCTION check_item_class_parent()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
IF NEW.parent IS NULL THEN
|
||
|
|
RETURN NEW;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF (SELECT type FROM item_classes WHERE id = NEW.parent) = 'generic' THEN
|
||
|
|
RETURN NEW;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RAISE EXCEPTION 'Specific item classes may only have a generic parent (to avoid recursion)';
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER prevent_item_class_recursion
|
||
|
|
BEFORE INSERT OR UPDATE ON item_classes
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_item_class_parent();
|