RFC-01: Implemented (most) of the SQL part

- Created vnlists table
- Converted rlists.vstat into that table
- Added triggers to make sure that there is always a corresponding row
  in vnlists for every row in rlists.
- Added a check on vnlists for the 'listdel' notify

5 files changed, 106 insertions, 7 deletions

diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index a8f1cbe2..b785f582 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -23,7 +23,7 @@ my %subgraphs = (
   'Producers'        => [qw| FFFFCC producers producers_rev producers_relations |],
   'Releases'         => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |],
   'Visual Novels'    => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |],
-  'Users'            => [qw| CCFFFF users votes rlists wlists sessions notifications |],
+  'Users'            => [qw| CCFFFF users votes rlists wlists vnlists sessions notifications |],
   'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |],
   'Tags'             => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |],
   'Misc'             => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |],
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 4408c6df..8f55ed88 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -96,6 +96,9 @@ CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
   WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
   EXECUTE PROCEDURE vn_vnsearch_notify();
 
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists  DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+
 
 -- Sequences used for ID generation of items not in the DB
 CREATE SEQUENCE covers_seq;
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 53af121c..3f1372b5 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -455,6 +455,53 @@ $$ LANGUAGE plpgsql;
 
 
 
+-- For each row in rlists, there should be at least one corresponding row in
+-- vnlists for at least one of the VNs linked to that release.
+-- 1. When a row is deleted from vnlists, also remove all rows from rlists that
+--    would otherwise not have a corresponding row in vnlists
+-- 2. When a row is inserted to rlists and there is not yet a corresponding row
+--    in vnlists, add a row in vnlists (with status=unknown) for each vn linked
+--    to the release.
+CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$
+BEGIN
+  -- 1.
+  IF TG_TABLE_NAME = 'vnlists' THEN
+    DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT r.id
+      -- fetch all related rows in rlists
+      FROM releases_vn rv
+      JOIN releases r ON r.latest = rv.rid
+      JOIN rlists rl ON rl.rid = r.id
+     WHERE rv.vid = OLD.vid AND rl.uid = OLD.uid
+       -- and test for a corresponding row in vnlists
+       AND NOT EXISTS(
+        SELECT 1
+          FROM releases_vn rvi
+          JOIN vnlists vl ON vl.vid = rvi.vid AND uid = OLD.uid
+         WHERE rvi.rid = r.latest
+       ));
+
+  -- 2.
+  ELSE
+   INSERT INTO vnlists (uid, vid) SELECT NEW.uid, rv.vid
+     -- all VNs linked to the release
+      FROM releases_vn rv
+      JOIN releases r ON rv.rid = r.latest
+     WHERE r.id = NEW.rid
+       -- but only if there are no corresponding rows in vnlists yet
+       AND NOT EXISTS(
+        SELECT 1
+          FROM releases_vn rvi
+          JOIN releases ri ON rvi.rid = ri.latest
+          JOIN vnlists vl ON vl.vid = rvi.vid
+         WHERE ri.id = NEW.rid AND vl.uid = NEW.uid
+       );
+  END IF;
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
 -- Send a notify whenever anime info should be fetched
 CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
   BEGIN NOTIFY anime; RETURN NULL; END;
@@ -702,14 +749,13 @@ BEGIN
       -- look for users who should get this notify
       FROM (
         -- voted on the VN
-              SELECT uid FROM votes  WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
+              SELECT uid FROM votes   WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
+        -- VN in vnlist
+        UNION SELECT uid FROM vnlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
         -- VN in wishlist
-        UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
+        UNION SELECT uid FROM wlists  WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
         -- release in release list
-        UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
-        -- there's also a special case which we're ignoring here:
-        --  when a VN linked to a release in a user's release list is deleted
-        -- normally, the releases are also deleted, so a notify is generated anyway
+        UNION SELECT uid FROM rlists  WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
       ) u
       -- fetch info about this edit
       JOIN changes c ON c.id = NEW.latest
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 48367e4c..2ea03f4a 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -340,6 +340,16 @@ CREATE TABLE vn_screenshots (
   PRIMARY KEY(vid, scr)
 );
 
+
+-- vnlists
+CREATE TABLE vnlists (
+  uid integer NOT NULL,
+  vid integer NOT NULL,
+  status smallint NOT NULL DEFAULT 0,
+  added TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  PRIMARY KEY(uid, vid)
+);
+
 -- votes
 CREATE TABLE votes (
   vid integer NOT NULL DEFAULT 0,
@@ -405,6 +415,8 @@ ALTER TABLE vn_rev              ADD FOREIGN KEY (vid)       REFERENCES vn
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (scr)       REFERENCES screenshots   (id);
 ALTER TABLE vn_screenshots      ADD FOREIGN KEY (rid)       REFERENCES releases      (id);
+ALTER TABLE vnlists             ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE vnlists             ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
 ALTER TABLE votes               ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE votes               ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
 ALTER TABLE wlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql
index 8ffbd6ae..580be925 100644
--- a/util/updates/update_2.16.sql
+++ b/util/updates/update_2.16.sql
@@ -4,3 +4,41 @@ UPDATE releases_rev SET minage = -1 WHERE minage IS NULL;
 ALTER TABLE releases_rev ALTER COLUMN minage SET DEFAULT -1;
 ALTER TABLE releases_rev ALTER COLUMN minage DROP NOT NULL;
 
+
+-- speed up get-releases-by-vn queries
+CREATE INDEX releases_vn_vid ON releases_vn (vid);
+
+
+-- add vnlists table
+CREATE TABLE vnlists (
+  uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
+  vid integer NOT NULL REFERENCES vn (id),
+  status smallint NOT NULL DEFAULT 0,
+  added TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  PRIMARY KEY(uid, vid)
+);
+
+
+-- load new function(s)
+\i util/sql/func.sql
+
+
+-- convert from rlists.vstat
+INSERT INTO vnlists SELECT
+    i.uid, i.vid, COALESCE(MIN(CASE WHEN rl.vstat = 0 THEN NULL ELSE rl.vstat END), 0), MIN(rl.added)
+  FROM (
+    SELECT DISTINCT rl.uid, rv.vid
+      FROM rlists rl
+      JOIN releases r ON r.id = rl.rid
+      JOIN releases_vn rv ON rv.rid = r.latest
+  ) AS i(uid,vid)
+  JOIN rlists rl ON rl.uid = i.uid
+  JOIN releases r ON r.id = rl.rid
+  JOIN releases_vn rv ON rv.rid = r.latest AND rv.vid = i.vid
+  GROUP BY i.uid, i.vid;
+
+
+-- add constraints triggers
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists  DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+