diff options
author | Yorhel <git@yorhel.nl> | 2009-10-21 16:21:31 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-10-21 16:21:31 +0200 |
commit | 76154a765f9b69af92495462a5e880d5c2e608ce (patch) | |
tree | d95879eb595405cde6f4006dcdbbb15554827f8d /util/updates | |
parent | a580210dc8e1d9e66b31ad68de9cf2492aba4d4f (diff) |
SQL: Send relgraph notify when needed for producer entries
Diffstat (limited to 'util/updates')
-rw-r--r-- | util/updates/update_2.8.sql | 25 |
1 files changed, 25 insertions, 0 deletions
diff --git a/util/updates/update_2.8.sql b/util/updates/update_2.8.sql index 637c61c1..e299e608 100644 --- a/util/updates/update_2.8.sql +++ b/util/updates/update_2.8.sql @@ -43,6 +43,31 @@ CREATE TABLE producers_relations ( ); ALTER TABLE producers ADD COLUMN rgraph integer REFERENCES relgraphs (id); +CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$ +BEGIN + -- 1. + IF TG_TABLE_NAME = 'producers' THEN + IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN + NOTIFY relgraph; + END IF; + END IF; + IF TG_TABLE_NAME = 'producers' AND TG_OP = 'UPDATE' THEN + IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN + -- 3 & 4 + IF OLD.latest <> NEW.latest AND ( + EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest) + OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest) + OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = NEW.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = OLD.latest) + ) THEN + UPDATE producers 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 producers DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify(); + -- Anime types stored as enum CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); |