summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql36
1 files changed, 36 insertions, 0 deletions
diff --git a/util/dump.sql b/util/dump.sql
index eb502602..84cbecbc 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -737,6 +737,42 @@ $$ LANGUAGE plpgsql;
CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify();
+-- Update vn.rgraph column and send notify when a relation graph needs to be regenerated
+-- 1. NOTIFY is sent on an UPDATE or INSERT on vn with rgraph = NULL and with entries in vn_relations (deferred)
+-- vn.rgraph is set to NULL when:
+-- 2. UPDATE on vn where c_released or c_languages has changed (deferred, but doesn't have to be)
+-- 3. New VN revision of which the title differs from previous revision (deferred)
+-- 4. New VN revision with items in vn_relations that differ from previous revision (deferred)
+CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF TG_TABLE_NAME = 'vn' THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF TG_TABLE_NAME = 'vn' AND TG_OP = 'UPDATE' THEN
+ IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
+ -- 2.
+ IF OLD.c_released <> NEW.c_released OR OLD.c_languages <> NEW.c_languages THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ -- 3 & 4
+ IF OLD.latest <> NEW.latest AND (
+ EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 WHERE v2.title <> v1.title AND v1.id = OLD.latest AND v2.id = NEW.latest)
+ OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = OLD.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = NEW.latest)
+ OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = NEW.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = OLD.latest)
+ ) THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+