WIP add language-specific titles to releases

4 files changed, 61 insertions, 36 deletions

diff --git a/sql/func.sql b/sql/func.sql
index af51303a..1b694211 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -21,7 +21,6 @@ CREATE OR REPLACE FUNCTION fmtip(n ipinfo) RETURNS text AS $$
 $$ LANGUAGE SQL IMMUTABLE;
 
 
-
 CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$
   SELECT coalesce(string_agg(t, ' '), '') FROM (
     SELECT t FROM (
@@ -43,15 +42,25 @@ CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$
           |(?:parts?|vol|volumes?|chapters?|v|ver|versions?)(?:[0-9]+)
           |editions?|version|production|thebest|append|scenario|dlc)+$', '', 'xg')
         FROM (
-          SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
+          SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
           UNION ALL
-          SELECT original FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
+          SELECT latin FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
         ) r(t)
     ) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
   ) x(t);
 $$ LANGUAGE SQL;
 
 
+CREATE OR REPLACE FUNCTION search_gen_release(relid vndbid) RETURNS text AS $$
+  SELECT coalesce(string_agg(t, ' '), '') FROM (
+    SELECT t FROM (
+                SELECT search_norm_term(title) FROM releases_titles WHERE id = relid
+      UNION ALL SELECT search_norm_term(latin) FROM releases_titles WHERE id = relid
+    ) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
+  ) x(t);
+$$ LANGUAGE SQL;
+
+
 -- update_vncache(id) - updates some c_* columns in the vn table
 CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$
   UPDATE vn SET
@@ -68,7 +77,7 @@ CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$
     ), 0),
     c_languages = ARRAY(
       SELECT rl.lang
-        FROM releases_lang rl
+        FROM releases_titles rl
         JOIN releases r ON r.id = rl.id
         JOIN releases_vn rv ON r.id = rv.id
        WHERE rv.vid = $1
@@ -409,7 +418,7 @@ BEGIN
     --WHEN 'v' THEN RETURN QUERY SELECT COALESCE(vo.latin, vo.title), CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END, NULL::vndbid, v.hidden, v.locked
     --                      FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang WHERE v.id = $1;
     WHEN 'v' THEN RETURN QUERY SELECT v.title   ::text, v.alttitle::text,  NULL::vndbid,  v.hidden, v.locked FROM vnt v       WHERE v.id = $1;
-    WHEN 'r' THEN RETURN QUERY SELECT r.title   ::text, r.original::text,  NULL::vndbid,  r.hidden, r.locked FROM releases r  WHERE r.id = $1;
+    WHEN 'r' THEN RETURN QUERY SELECT r.title   ::text, r.alttitle::text,  NULL::vndbid,  r.hidden, r.locked FROM releasest r WHERE r.id = $1;
     WHEN 'p' THEN RETURN QUERY SELECT p.name    ::text, p.original::text,  NULL::vndbid,  p.hidden, p.locked FROM producers p WHERE p.id = $1;
     WHEN 'c' THEN RETURN QUERY SELECT c.name    ::text, c.original::text,  NULL::vndbid,  c.hidden, c.locked FROM chars c     WHERE c.id = $1;
     WHEN 'd' THEN RETURN QUERY SELECT d.title   ::text, NULL,              NULL::vndbid,  d.hidden, d.locked FROM docs d      WHERE d.id = $1;
@@ -424,7 +433,8 @@ BEGIN
   ELSE CASE vndbid_type($1)
     WHEN 'v' THEN RETURN QUERY SELECT COALESCE(vo.latin, vo.title), CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END, h.requester, h.ihid, h.ilock
                           FROM changes h JOIN vn_hist v ON h.id = v.chid JOIN vn_titles_hist vo ON h.id = vo.chid AND vo.lang = v.olang WHERE h.itemid = $1 AND h.rev = $2;
-    WHEN 'r' THEN RETURN QUERY SELECT r.title::text, r.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN releases_hist r  ON h.id = r.chid WHERE h.itemid = $1 AND h.rev = $2;
+    WHEN 'r' THEN RETURN QUERY SELECT COALESCE(ro.latin, ro.title), CASE WHEN ro.latin IS NULL THEN '' ELSE ro.title END, h.requester, h.ihid, h.ilock
+                          FROM changes h JOIN releases_hist r ON h.id = r.chid JOIN releases_titles_hist ro ON h.id = ro.chid AND ro.lang = r.olang WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 'p' THEN RETURN QUERY SELECT p.name ::text, p.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 'c' THEN RETURN QUERY SELECT c.name ::text, c.original::text,  h.requester, h.ihid, h.ilock FROM changes h JOIN chars_hist c     ON h.id = c.chid WHERE h.itemid = $1 AND h.rev = $2;
     WHEN 'd' THEN RETURN QUERY SELECT d.title::text, NULL,              h.requester, h.ihid, h.ilock FROM changes h JOIN docs_hist d      ON h.id = d.chid WHERE h.itemid = $1 AND h.rev = $2;
@@ -485,7 +495,7 @@ BEGIN
   -- Update vn.c_search when
   -- 1. A new release is created
   -- 2. A release has been hidden or unhidden
-  -- 3. The release title/original has changed
+  -- 3. The releases_titles have changed
   -- 4. The releases_vn table differs from a previous revision
   IF vndbid_type(nitemid) = 'r' THEN
     IF -- 1.
@@ -493,7 +503,8 @@ BEGIN
        -- 2.
        EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = nchid AND c2.id = xoldchid) OR
        -- 3.
-       EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = nchid) OR
+       EXISTS(SELECT title, latin FROM releases_titles_hist WHERE chid = xoldchid EXCEPT SELECT title, latin FROM releases_titles_hist WHERE chid = nchid) OR
+       EXISTS(SELECT title, latin FROM releases_titles_hist WHERE chid = nchid    EXCEPT SELECT title, latin FROM releases_titles_hist WHERE chid = xoldchid) OR
        -- 4.
        EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = nchid) OR
        EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = nchid    EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid)
@@ -502,6 +513,11 @@ BEGIN
     END IF;
   END IF;
 
+  -- Update releases.c_search
+  IF vndbid_type(nitemid) = 'r' THEN
+    UPDATE releases SET c_search = search_gen_release(id) WHERE id = nitemid;
+  END IF;
+
   -- Call update_vncache() for related VNs when a release has been created or edited
   -- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity)
   IF vndbid_type(nitemid) = 'r' THEN
diff --git a/sql/perms.sql b/sql/perms.sql
index f13e9d54..be9c531d 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -31,9 +31,10 @@ GRANT SELECT, INSERT                 ON producers_relations_hist TO vndb_site;
 GRANT SELECT                         ON quotes                   TO vndb_site;
 GRANT SELECT, INSERT, UPDATE         ON registration_throttle    TO vndb_site;
 GRANT SELECT, INSERT, UPDATE         ON releases                 TO vndb_site;
+GRANT SELECT                         ON releasest                TO vndb_site;
 GRANT SELECT, INSERT                 ON releases_hist            TO vndb_site;
-GRANT SELECT, INSERT,         DELETE ON releases_lang            TO vndb_site;
-GRANT SELECT, INSERT                 ON releases_lang_hist       TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON releases_titles          TO vndb_site;
+GRANT SELECT, INSERT                 ON releases_titles_hist     TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON releases_media           TO vndb_site;
 GRANT SELECT, INSERT                 ON releases_media_hist      TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON releases_platforms       TO vndb_site;
@@ -137,8 +138,9 @@ GRANT SELECT                         ON producers_relations      TO vndb_multi;
 GRANT SELECT                         ON quotes                   TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON registration_throttle    TO vndb_multi;
 GRANT SELECT                         ON releases                 TO vndb_multi;
+GRANT SELECT                         ON releasest                TO vndb_multi;
 GRANT SELECT                         ON releases_hist            TO vndb_multi;
-GRANT SELECT                         ON releases_lang            TO vndb_multi;
+GRANT SELECT                         ON releases_titles          TO vndb_multi;
 GRANT SELECT                         ON releases_media           TO vndb_multi;
 GRANT SELECT                         ON releases_platforms       TO vndb_multi;
 GRANT SELECT                         ON releases_producers       TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index 47cda09b..cc988749 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -425,8 +425,6 @@ CREATE TABLE releases ( -- dbentry_type=r
   official     boolean NOT NULL DEFAULT TRUE, -- [pub]
   locked       boolean NOT NULL DEFAULT FALSE,
   hidden       boolean NOT NULL DEFAULT FALSE,
-  title        varchar(300) NOT NULL DEFAULT '', -- [pub]
-  original     varchar(250) NOT NULL DEFAULT '', -- [pub]
   website      varchar(1024) NOT NULL DEFAULT '', -- [pub]
   catalog      varchar(50) NOT NULL DEFAULT '', -- [pub]
   engine       varchar(50) NOT NULL DEFAULT '', -- [pub]
@@ -444,7 +442,7 @@ CREATE TABLE releases ( -- dbentry_type=r
   l_gyutto     integer[] NOT NULL DEFAULT '{}', -- [pub]
   l_dmm        text[] NOT NULL DEFAULT '{}', -- [pub]
   l_freegame   text NOT NULL DEFAULT '', -- [pub]
-  c_search     text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[title, original])) STORED,
+  c_search     text,
   l_playstation_jp text NOT NULL DEFAULT '', -- [pub]
   l_playstation_na text NOT NULL DEFAULT '', -- [pub]
   l_playstation_eu text NOT NULL DEFAULT '',  -- [pub]
@@ -457,7 +455,8 @@ CREATE TABLE releases ( -- dbentry_type=r
   ani_ero_cg   animation, -- [pub]
   ani_bg       boolean, -- [pub]
   ani_face     boolean, -- [pub]
-  has_ero      boolean NOT NULL DEFAULT FALSE -- [pub]
+  has_ero      boolean NOT NULL DEFAULT FALSE, -- [pub]
+  olang        language NOT NULL DEFAULT 'ja' -- [pub] Refers to the main title to use for display purposes, not necessarily the original language.
 );
 
 -- releases_hist
@@ -491,8 +490,6 @@ CREATE TABLE releases_hist (
   doujin       boolean NOT NULL DEFAULT FALSE,
   uncensored   boolean,
   official     boolean NOT NULL DEFAULT TRUE,
-  title        varchar(300) NOT NULL DEFAULT '',
-  original     varchar(250) NOT NULL DEFAULT '',
   website      varchar(1024) NOT NULL DEFAULT '',
   catalog      varchar(50) NOT NULL DEFAULT '',
   engine       varchar(50) NOT NULL DEFAULT '',
@@ -520,23 +517,8 @@ CREATE TABLE releases_hist (
   ani_ero_cg   animation,
   ani_bg       boolean,
   ani_face     boolean,
-  has_ero      boolean NOT NULL DEFAULT FALSE
-);
-
--- releases_lang
-CREATE TABLE releases_lang (
-  id         vndbid NOT NULL, -- [pub]
-  lang       language NOT NULL, -- [pub]
-  mtl        boolean NOT NULL DEFAULT false, -- [pub]
-  PRIMARY KEY(id, lang)
-);
-
--- releases_lang_hist
-CREATE TABLE releases_lang_hist (
-  chid       integer NOT NULL,
-  lang       language NOT NULL,
-  mtl        boolean NOT NULL DEFAULT false, -- [pub]
-  PRIMARY KEY(chid, lang)
+  has_ero      boolean NOT NULL DEFAULT FALSE,
+  olang        language NOT NULL DEFAULT 'ja'
 );
 
 -- releases_media
@@ -589,6 +571,26 @@ CREATE TABLE releases_producers_hist (
   PRIMARY KEY(chid, pid)
 );
 
+-- releases_titles
+CREATE TABLE releases_titles (
+  id         vndbid NOT NULL, -- [pub]
+  lang       language NOT NULL, -- [pub]
+  mtl        boolean NOT NULL DEFAULT false, -- [pub]
+  title      text NOT NULL DEFAULT '', -- [pub]
+  latin      text, -- [pub]
+  PRIMARY KEY(id, lang)
+);
+
+-- releases_titles_hist
+CREATE TABLE releases_titles_hist (
+  chid       integer NOT NULL,
+  lang       language NOT NULL,
+  mtl        boolean NOT NULL DEFAULT false,
+  title      text NOT NULL DEFAULT '',
+  latin      text,
+  PRIMARY KEY(chid, lang)
+);
+
 -- releases_vn
 CREATE TABLE releases_vn (
   id         vndbid NOT NULL, -- [pub]
@@ -1386,3 +1388,6 @@ CREATE TABLE wikidata (
 -- This view can be redefined as a TEMPORARY VIEW in sessions to override the
 -- default behavior.
 CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, COALESCE(vo.latin, vo.title) AS sorttitle, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;
+
+-- Same for releases
+CREATE VIEW releasest AS SELECT r.*, COALESCE(ro.latin, ro.title) AS title, COALESCE(ro.latin, ro.title) AS sorttitle, CASE WHEN ro.latin IS NULL THEN '' ELSE ro.title END AS alttitle FROM releases r JOIN releases_titles ro ON ro.id = r.id AND ro.lang = r.olang;
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 1ce39b30..96cab630 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -82,9 +82,11 @@ ALTER TABLE producers_relations      ADD CONSTRAINT producers_relations_pid_fkey
 ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey   FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey  FOREIGN KEY (pid)       REFERENCES producers     (id);
 ALTER TABLE quotes                   ADD CONSTRAINT quotes_vid_fkey                    FOREIGN KEY (vid)       REFERENCES vn            (id);
+ALTER TABLE releases                 ADD CONSTRAINT releases_olang_fkey                FOREIGN KEY (id,olang)  REFERENCES releases_titles(id,lang) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE releases_hist            ADD CONSTRAINT releases_hist_chid_fkey            FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
-ALTER TABLE releases_lang            ADD CONSTRAINT releases_lang_id_fkey              FOREIGN KEY (id)        REFERENCES releases      (id);
-ALTER TABLE releases_lang_hist       ADD CONSTRAINT releases_lang_hist_chid_fkey       FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
+ALTER TABLE releases_hist            ADD CONSTRAINT releases_hist_olang_fkey           FOREIGN KEY (chid,olang)REFERENCES releases_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_titles          ADD CONSTRAINT releases_titles_id_fkey            FOREIGN KEY (id)        REFERENCES releases      (id);
+ALTER TABLE releases_titles_hist     ADD CONSTRAINT releases_titles_hist_chid_fkey     FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE releases_media           ADD CONSTRAINT releases_media_id_fkey             FOREIGN KEY (id)        REFERENCES releases      (id);
 ALTER TABLE releases_media_hist      ADD CONSTRAINT releases_media_hist_chid_fkey      FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE releases_platforms       ADD CONSTRAINT releases_platforms_id_fkey         FOREIGN KEY (id)        REFERENCES releases      (id);