From 81c0adb739d2b85d72041e4e9ff04c7a879ea1ed Mon Sep 17 00:00:00 2001 From: Thomas Berezansky <tsbere@mvlc.org> Date: Wed, 15 Jun 2011 22:03:47 -0400 Subject: [PATCH] Unwrapped upgrade script for ou loop protect May need to be split into "create function" and "add trigger" pieces. Signed-off-by: Thomas Berezansky <tsbere@mvlc.org> Signed-off-by: Mike Rylander <mrylander@gmail.com> --- .../src/sql/Pg/upgrade/XXXX.aou_parent_protect.sql | 33 ++++++++++++++++++++++ 1 file changed, 33 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.aou_parent_protect.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.aou_parent_protect.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.aou_parent_protect.sql new file mode 100644 index 0000000000..589aa973f6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.aou_parent_protect.sql @@ -0,0 +1,33 @@ +CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$ + DECLARE + current_aou actor.org_unit%ROWTYPE; + seen_ous INT[]; + depth_count INT; + BEGIN + current_aou := NEW; + depth_count := 0; + seen_ous := ARRAY[NEW.id]; + IF TG_OP = 'INSERT' OR NEW.parent_ou IS DISTINCT FROM OLD.parent_ou THEN + LOOP + IF current_aou.parent_ou IS NULL THEN -- Top of the org tree? + RETURN NEW; -- No loop. Carry on. + END IF; + IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen? + RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT! + END IF; + -- Get the next one! + SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou; + seen_ous := seen_ous || current_aou.id; + depth_count := depth_count + 1; + IF depth_count = 100 THEN + RAISE 'OU CHECK TOO DEEP'; + END IF; + END LOOP; + END IF; + RETURN NEW; + END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER actor_org_unit_parent_protect_trigger + BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW + EXECUTE PROCEDURE actor.org_unit_parent_protect (); -- 2.11.0