From 4304d30193646fdecbe2fcd081cfbce46ad82ed6 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Thu, 8 Oct 2009 22:19:35 +0200
Subject: Synchronised and updated dump.sql and dbgraph.pl

Removed the 'SET default_with_oids', as the minimum required PostgreSQL
version (8.3) has OIDS disables by default already. Also uncommented the
CREATE LANGUAGE, plpgsql is a "trusted language" and as such doesn't
need superuser priveleges to create anymore.
---
 util/dump.sql | 31 ++++++++++++++-----------------
 1 file changed, 14 insertions(+), 17 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index aaa1a5e9..7a621103 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -1,15 +1,12 @@
 
-
--- we don't use PgSQL's OIDS
-SET default_with_oids = false;
-
--- for the functions to work, the following query must
--- be executed on the database by a superuser:
---  CREATE PROCEDURAL LANGUAGE plpgsql
-
+-- plpgsql is required for our (trigger) functions
+CREATE LANGUAGE plpgsql;
 
 
+-- data types
 
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+CREATE TYPE anime_type  AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
 
 
 -----------------------------------------
@@ -23,7 +20,7 @@ CREATE TABLE anime (
   year smallint,
   ann_id integer,
   nfo_id varchar(200),
-  type smallint,
+  type anime_type,
   title_romaji,
   title_kanji,
   lastfetch timestamptz
@@ -137,12 +134,6 @@ CREATE TABLE releases_vn (
   PRIMARY KEY(rid, vid)
 );
 
--- relgraph
-CREATE TABLE relgraph (
-  id SERIAL NOT NULL PRIMARY KEY,
-  cmap text NOT NULL DEFAULT ''
-);
-
 -- rlists
 CREATE TABLE rlists (
   uid integer NOT NULL DEFAULT 0,
@@ -288,11 +279,17 @@ CREATE TABLE vn_anime (
   PRIMARY KEY(vid, aid)
 );
 
+-- vn_graphs
+CREATE TABLE vn_graphs (
+  id SERIAL PRIMARY KEY,
+  svg xml NOT NULL
+);
+
 -- vn_relations
 CREATE TABLE vn_relations (
   vid1 integer NOT NULL DEFAULT 0,
   vid2 integer NOT NULL DEFAULT 0,
-  relation integer NOT NULL DEFAULT 0,
+  relation vn_relation NOT NULL,
   PRIMARY KEY(vid1, vid2)
 );
 
@@ -380,7 +377,7 @@ ALTER TABLE threads_posts      ADD FOREIGN KEY (tid)       REFERENCES threads
 ALTER TABLE threads_posts      ADD FOREIGN KEY (uid)       REFERENCES users         (id);
 ALTER TABLE threads_boards     ADD FOREIGN KEY (tid)       REFERENCES threads       (id);
 ALTER TABLE vn                 ADD FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn                 ADD FOREIGN KEY (rgraph)    REFERENCES relgraph      (id);
+ALTER TABLE vn                 ADD FOREIGN KEY (rgraph)    REFERENCES vn_graphs     (id);
 ALTER TABLE vn_anime           ADD FOREIGN KEY (aid)       REFERENCES anime         (id);
 ALTER TABLE vn_anime           ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
 ALTER TABLE vn_relations       ADD FOREIGN KEY (vid1)      REFERENCES vn_rev        (id);
-- 
cgit v1.2.3


From d96c546f8beb1fdc79847b462f46ced9332e4c48 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Fri, 9 Oct 2009 08:52:32 +0200
Subject: SQL: Converted releases_media.medium to an ENUM type

---
 util/dump.sql | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 7a621103..b43d8c62 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -5,8 +5,9 @@ CREATE LANGUAGE plpgsql;
 
 -- data types
 
-CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 CREATE TYPE anime_type  AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+CREATE TYPE medium      AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 
 
 -----------------------------------------
@@ -86,7 +87,7 @@ CREATE TABLE releases_lang (
 -- releases_media
 CREATE TABLE releases_media (
   rid integer NOT NULL DEFAULT 0,
-  medium character(3) NOT NULL DEFAULT '',
+  medium medium NOT NULL,
   qty smallint NOT NULL DEFAULT 1,
   PRIMARY KEY(rid, medium, qty)
 );
-- 
cgit v1.2.3


From d43ae33a587743fc0916930e0a39f561f3174a69 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Fri, 9 Oct 2009 11:45:32 +0200
Subject: Added producer role (developer/publisher) to DB and release editor

They aren't displayed on the site yet, though.
---
 util/dump.sql | 3 +++
 1 file changed, 3 insertions(+)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index b43d8c62..10d13b9d 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -103,6 +103,9 @@ CREATE TABLE releases_platforms (
 CREATE TABLE releases_producers (
   rid integer NOT NULL,
   pid integer NOT NULL,
+  developer boolean NOT NULL DEFAULT FALSE,
+  publisher boolean NOT NULL DEFAULT TRUE,
+  CHECK(developer OR publisher),
   PRIMARY KEY(pid, rid)
 );
 
-- 
cgit v1.2.3


From 5ce2ce4c494feef5940c032549472a52b5282ad1 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 10 Oct 2009 15:51:57 +0200
Subject: Display number of unread posts in user menu

Instead of displaying the total number of threads. Posts are marked as
read when the thread is opened.
---
 util/dump.sql | 1 +
 1 file changed, 1 insertion(+)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 10d13b9d..c952c162 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -240,6 +240,7 @@ CREATE TABLE threads_boards (
   tid integer NOT NULL DEFAULT 0,
   type character(2) NOT NULL DEFAULT 0,
   iid integer NOT NULL DEFAULT 0,
+  lastread smallint NOT NULL,
   PRIMARY KEY(tid, type, iid)
 );
 
-- 
cgit v1.2.3


From 69d8738688ebb72707fe377b7ce7c717407aea96 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Mon, 12 Oct 2009 09:34:06 +0200
Subject: SQL: Converted changes.type to an ENUM

This is a very important column in a very important table, I hope I
didn't forget to update a piece of code somewhere...
---
 util/dump.sql | 9 +++++----
 1 file changed, 5 insertions(+), 4 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index c952c162..8a8d2c61 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -5,9 +5,10 @@ CREATE LANGUAGE plpgsql;
 
 -- data types
 
-CREATE TYPE anime_type  AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
-CREATE TYPE medium      AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
-CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+CREATE TYPE anime_type   AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
+CREATE TYPE medium       AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE vn_relation  AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 
 
 -----------------------------------------
@@ -30,7 +31,7 @@ CREATE TABLE anime (
 -- changes
 CREATE TABLE changes (
   id SERIAL NOT NULL PRIMARY KEY,
-  type smallint NOT NULL DEFAULT 0,
+  type dbentry_type NOT NULL,
   rev integer NOT NULL DEFAULT 1,
   added timestamptz NOT NULL DEFAULT NOW(),
   requester integer NOT NULL DEFAULT 0,
-- 
cgit v1.2.3


From 668833467f8231ebdc622d42edcb49e8374b42b4 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 17 Oct 2009 16:21:38 +0200
Subject: SQL: Converted releases_rev.type to an ENUM data type

---
 util/dump.sql | 9 +++++----
 1 file changed, 5 insertions(+), 4 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 8a8d2c61..145c09e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -8,6 +8,7 @@ CREATE LANGUAGE plpgsql;
 CREATE TYPE anime_type   AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
 CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
 CREATE TYPE medium       AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
 CREATE TYPE vn_relation  AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 
 
@@ -116,7 +117,7 @@ CREATE TABLE releases_rev (
   rid integer NOT NULL DEFAULT 0,
   title varchar(250) NOT NULL DEFAULT '',
   original varchar(250) NOT NULL DEFAULT '',
-  type smallint NOT NULL DEFAULT 0,
+  type release_type NOT NULL DEFAULT 'complete',
   website varchar(250) NOT NULL DEFAULT '',
   released integer NOT NULL,
   notes text NOT NULL DEFAULT '',
@@ -448,7 +449,7 @@ BEGIN
       JOIN releases r1 ON rr1.id = r1.latest
       JOIN releases_vn rv1 ON rr1.id = rv1.rid
       WHERE rv1.vid = vn.id
-      AND rr1.type <> 2
+      AND rr1.type <> ''trial''
       AND r1.hidden = FALSE
       AND rr1.released <> 0
       GROUP BY rv1.vid
@@ -460,7 +461,7 @@ BEGIN
       JOIN releases r2 ON rr2.id = r2.latest
       JOIN releases_vn rv2 ON rr2.id = rv2.rid
       WHERE rv2.vid = vn.id
-      AND rr2.type <> 2
+      AND rr2.type <> ''trial''
       AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
       AND r2.hidden = FALSE
       GROUP BY rl2.lang
@@ -473,7 +474,7 @@ BEGIN
       JOIN releases r3 ON rp3.rid = r3.latest
       JOIN releases_vn rv3 ON rp3.rid = rv3.rid
       WHERE rv3.vid = vn.id
-      AND rr3.type <> 2
+      AND rr3.type <> ''trial''
       AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
       AND r3.hidden = FALSE
       GROUP BY rp3.platform
-- 
cgit v1.2.3


From 62cb41c3b8780bffe5a8ea58a6a7b5053d9e1059 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 18 Oct 2009 18:06:14 +0200
Subject: SQL: Fixed calculation of tags_vn_bayesian.spoiler

---
 util/dump.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 145c09e8..9fbff0a2 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -589,7 +589,7 @@ BEGIN
     SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
   -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
   CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
-    SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+    SELECT tag, vid, uid, MAX(vote)::real AS vote, AVG(spoiler)::real AS spoiler
     FROM tags_vn_all GROUP BY tag, vid, uid;
   -- grouped by (tag, vid) and serialized into a table
   DROP INDEX IF EXISTS tags_vn_bayesian_tag;
-- 
cgit v1.2.3


From 91b6578e17f1ce6d0ff7974d06ae55ad2e39bf1d Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Wed, 21 Oct 2009 13:47:59 +0200
Subject: SQL: Synced update_2.8.sql into dump.sql

This doesn't mean the relations are final, though...
---
 util/dump.sql | 23 ++++++++++++++++++-----
 1 file changed, 18 insertions(+), 5 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 9fbff0a2..f5ce2614 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -5,11 +5,12 @@ CREATE LANGUAGE plpgsql;
 
 -- data types
 
-CREATE TYPE anime_type   AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
-CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
-CREATE TYPE medium       AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
-CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
-CREATE TYPE vn_relation  AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+CREATE TYPE anime_type        AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+CREATE TYPE dbentry_type      AS ENUM ('v', 'r', 'p');
+CREATE TYPE medium            AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'par', 'sub', 'imp', 'ipa');
+CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
+CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 
 
 -----------------------------------------
@@ -49,6 +50,16 @@ CREATE TABLE producers (
   hidden boolean NOT NULL DEFAULT FALSE
 );
 
+
+-- producers_relations
+CREATE TABLE producers_relations (
+  pid1 integer NOT NULL,
+  pid2 integer NOT NULL,
+  relation producer_relation NOT NULL,
+  PRIMARY KEY(pid1, pid2)
+);
+
+
 -- producers_rev
 CREATE TABLE producers_rev (
   id integer NOT NULL PRIMARY KEY,
@@ -356,6 +367,8 @@ CREATE TABLE wlists (
 ALTER TABLE changes            ADD FOREIGN KEY (requester) REFERENCES users         (id);
 ALTER TABLE changes            ADD FOREIGN KEY (causedby)  REFERENCES changes       (id);
 ALTER TABLE producers          ADD FOREIGN KEY (latest)    REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid1)     REFERENCES producers_rev (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid2)     REFERENCES producers     (id);
 ALTER TABLE producers_rev      ADD FOREIGN KEY (id)        REFERENCES changes       (id);
 ALTER TABLE producers_rev      ADD FOREIGN KEY (pid)       REFERENCES producers     (id);
 ALTER TABLE quotes             ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
-- 
cgit v1.2.3


From 96e77619793fbe8351e023d17e257e96cdb37509 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Wed, 21 Oct 2009 14:21:38 +0200
Subject: SQL: Renamed vn_graphs to relgraphs and synced dbgraph.pl

As the same table can easily be used to store producer relation graphs
as well.
---
 util/dump.sql | 18 +++++++-----------
 1 file changed, 7 insertions(+), 11 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index f5ce2614..97d317e0 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -50,7 +50,6 @@ CREATE TABLE producers (
   hidden boolean NOT NULL DEFAULT FALSE
 );
 
-
 -- producers_relations
 CREATE TABLE producers_relations (
   pid1 integer NOT NULL,
@@ -59,7 +58,6 @@ CREATE TABLE producers_relations (
   PRIMARY KEY(pid1, pid2)
 );
 
-
 -- producers_rev
 CREATE TABLE producers_rev (
   id integer NOT NULL PRIMARY KEY,
@@ -73,7 +71,6 @@ CREATE TABLE producers_rev (
   alias varchar(500) NOT NULL DEFAULT ''
 );
 
-
 -- quotes
 CREATE TABLE quotes (
   vid integer NOT NULL,
@@ -81,7 +78,6 @@ CREATE TABLE quotes (
   PRIMARY KEY(vid, quote)
 );
 
-
 -- releases
 CREATE TABLE releases (
   id SERIAL NOT NULL PRIMARY KEY,
@@ -151,6 +147,12 @@ CREATE TABLE releases_vn (
   PRIMARY KEY(rid, vid)
 );
 
+-- relgraphs
+CREATE TABLE relgraphs (
+  id SERIAL PRIMARY KEY,
+  svg xml NOT NULL
+);
+
 -- rlists
 CREATE TABLE rlists (
   uid integer NOT NULL DEFAULT 0,
@@ -297,12 +299,6 @@ CREATE TABLE vn_anime (
   PRIMARY KEY(vid, aid)
 );
 
--- vn_graphs
-CREATE TABLE vn_graphs (
-  id SERIAL PRIMARY KEY,
-  svg xml NOT NULL
-);
-
 -- vn_relations
 CREATE TABLE vn_relations (
   vid1 integer NOT NULL DEFAULT 0,
@@ -397,7 +393,7 @@ ALTER TABLE threads_posts      ADD FOREIGN KEY (tid)       REFERENCES threads
 ALTER TABLE threads_posts      ADD FOREIGN KEY (uid)       REFERENCES users         (id);
 ALTER TABLE threads_boards     ADD FOREIGN KEY (tid)       REFERENCES threads       (id);
 ALTER TABLE vn                 ADD FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn                 ADD FOREIGN KEY (rgraph)    REFERENCES vn_graphs     (id);
+ALTER TABLE vn                 ADD FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
 ALTER TABLE vn_anime           ADD FOREIGN KEY (aid)       REFERENCES anime         (id);
 ALTER TABLE vn_anime           ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
 ALTER TABLE vn_relations       ADD FOREIGN KEY (vid1)      REFERENCES vn_rev        (id);
-- 
cgit v1.2.3


From 6900668e8dad97762e4a4c493c21dec1391998cb Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Wed, 21 Oct 2009 16:01:08 +0200
Subject: Added relation graphs for producers

TODO:
 - document the relations
 - emit a relgraph notify when needed
---
 util/dump.sql | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 97d317e0..84d862fd 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -47,7 +47,8 @@ CREATE TABLE producers (
   id SERIAL NOT NULL PRIMARY KEY,
   latest integer NOT NULL DEFAULT 0,
   locked boolean NOT NULL DEFAULT FALSE,
-  hidden boolean NOT NULL DEFAULT FALSE
+  hidden boolean NOT NULL DEFAULT FALSE,
+  rgraph integer
 );
 
 -- producers_relations
@@ -363,6 +364,7 @@ CREATE TABLE wlists (
 ALTER TABLE changes            ADD FOREIGN KEY (requester) REFERENCES users         (id);
 ALTER TABLE changes            ADD FOREIGN KEY (causedby)  REFERENCES changes       (id);
 ALTER TABLE producers          ADD FOREIGN KEY (latest)    REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers          ADD FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
 ALTER TABLE producers_relations ADD FOREIGN KEY (pid1)     REFERENCES producers_rev (id);
 ALTER TABLE producers_relations ADD FOREIGN KEY (pid2)     REFERENCES producers     (id);
 ALTER TABLE producers_rev      ADD FOREIGN KEY (id)        REFERENCES changes       (id);
-- 
cgit v1.2.3


From 76154a765f9b69af92495462a5e880d5c2e608ce Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Wed, 21 Oct 2009 16:21:31 +0200
Subject: SQL: Send relgraph notify when needed for producer entries

---
 util/dump.sql | 30 ++++++++++++++++++++++++++++++
 1 file changed, 30 insertions(+)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 84d862fd..c04273b6 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -796,6 +796,36 @@ $$ 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();
 
 
+-- Same as above for producers, with slight differences in the steps:
+-- There is no 2, and
+-- 3 = New producer revision of which the name, language or type differs from the previous revision (deferred)
+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();
+
+
 -- NOTIFY on insert into changes/posts/tags
 CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
 BEGIN
-- 
cgit v1.2.3


From 0557a463cf3e1b2cd5dbb4f799a2d08c9a6cd789 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 24 Oct 2009 11:36:57 +0200
Subject: Added Spawned <> Originated from producer relation

---
 util/dump.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index c04273b6..9f8710f8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -8,7 +8,7 @@ CREATE LANGUAGE plpgsql;
 CREATE TYPE anime_type        AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
 CREATE TYPE dbentry_type      AS ENUM ('v', 'r', 'p');
 CREATE TYPE medium            AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
-CREATE TYPE producer_relation AS ENUM ('old', 'new', 'par', 'sub', 'imp', 'ipa');
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
 CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
 CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
 
-- 
cgit v1.2.3


From b4bef02c08a3bc8198f97d0e97701981f9bb85c8 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 24 Oct 2009 11:41:51 +0200
Subject: Removed update_rev() function and monthly revcache cron

The changes.rev column should be correct in the first place, and in the
(most likely) impossible condition that it isn't, the update_rev()
function is more likely to make things worse.
---
 util/dump.sql | 25 -------------------------
 1 file changed, 25 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 9f8710f8..0fdf5696 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -420,31 +420,6 @@ ALTER TABLE wlists             ADD FOREIGN KEY (vid)       REFERENCES vn
 -------------------------
 
 
--- update_rev(table, ids) - updates the rev column in the changes table
-CREATE FUNCTION update_rev(tbl text, ids text) RETURNS void AS $$
-DECLARE
-  r RECORD;
-  r2 RECORD;
-  i integer;
-  t text;
-  e text;
-BEGIN
-  SELECT INTO t SUBSTRING(tbl, 1, 1);
-  e := '';
-  IF ids <> '' THEN
-    e := ' WHERE id IN('||ids||')';
-  END IF;
-  FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP
-    i := 1;
-    FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
-      UPDATE changes SET rev = i WHERE id = r2.id;
-      i := i+1;
-    END LOOP;
-  END LOOP;
-END;
-$$ LANGUAGE plpgsql;
-
-
 -- update_vncache(id) - updates the c_* columns in the vn table
 CREATE FUNCTION update_vncache(id integer) RETURNS void AS $$
 DECLARE
-- 
cgit v1.2.3

