LP#836768 Proximity update trigger

Update org proximity automatically when parent org unit is changed to avoid the need to use the autogen -u parameter,
by means of a trigger on the actor.org_unit table and a new function.

Signed-off-by: Steven Callender <stevecallender@esilibrary.com>
Signed-off-by: Mike Rylander <mrylander@gmail.com>

diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql
index 1f78468..8c6a0c8 100644 (file)
--- a/Open-ILS/src/sql/Pg/020.schema.functions.sql
+++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql
@@ -414,3 +414,44 @@ Given user input, find an appropriate barcode in the proper class.
 Will add prefix/suffix information to do so, and return all results.
 $$;
 
+CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
+BEGIN
+
+
+IF TG_OP = 'DELETE' THEN
+
+    DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
+
+END IF;
+
+IF TG_OP = 'UPDATE' THEN
+
+    IF NEW.parent_ou <> OLD.parent_ou THEN
+
+        DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
+            INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
+            SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
+                FROM  actor.org_unit l, actor.org_unit r
+                WHERE (l.id = NEW.id or r.id = NEW.id);
+
+    END IF;
+
+END IF;
+
+IF TG_OP = 'INSERT' THEN
+
+     INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
+     SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
+         FROM  actor.org_unit l, actor.org_unit r
+         WHERE (l.id = NEW.id or r.id = NEW.id);
+
+END IF;
+
+RETURN null;
+
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER proximity_update_tgr AFTER INSERT OR UPDATE OR DELETE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE actor.org_unit_prox_update ();
+

diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_trigger_prox_update.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_trigger_prox_update.sql
new file mode 100644 (file)
index 0000000..dd0e2b9
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.actor_org_unit_trigger_prox_update.sql
@@ -0,0 +1,46 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('XXX'); -- stevecallender
+
+CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
+BEGIN
+
+
+IF TG_OP = 'DELETE' THEN
+
+    DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
+
+END IF;
+
+IF TG_OP = 'UPDATE' THEN
+
+    IF NEW.parent_ou <> OLD.parent_ou THEN
+
+        DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
+            INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
+            SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
+                FROM  actor.org_unit l, actor.org_unit r
+                WHERE (l.id = NEW.id or r.id = NEW.id);
+
+    END IF;
+
+END IF;
+
+IF TG_OP = 'INSERT' THEN
+
+     INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
+     SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
+         FROM  actor.org_unit l, actor.org_unit r
+         WHERE (l.id = NEW.id or r.id = NEW.id);
+
+END IF;
+
+RETURN null;
+
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER proximity_update_tgr AFTER INSERT OR UPDATE OR DELETE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE actor.org_unit_prox_update ();
+
+COMMIT;