summaryrefslogtreecommitdiff
path: root/util/updates/wip-release-titles.sql
blob: ad25de84846dd765648709f74c6d75fb1ee85a21 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
BEGIN;

CREATE TABLE releases_titles (
  id         vndbid NOT NULL,
  lang       language NOT NULL,
  mtl        boolean NOT NULL DEFAULT false,
  title      text NOT NULL DEFAULT '',
  latin      text,
  PRIMARY KEY(id, lang)
);

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)
);

-- Fixup some old (deleted) entries that are missing a language field
INSERT INTO releases_lang SELECT rv.id, v.olang, false FROM releases_vn rv JOIN vn v ON v.id = rv.vid WHERE NOT EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = rv.id);
INSERT INTO releases_lang_hist SELECT rv.chid, v.olang, false FROM releases_vn_hist rv JOIN vn v ON v.id = rv.vid WHERE NOT EXISTS(SELECT 1 FROM releases_lang_hist rl WHERE rl.chid = rv.chid);

-- Copy the existing titles to every language.
-- TODO: Is this the right solution? Not sure :/
INSERT INTO releases_titles
    SELECT rl.id, rl.lang, rl.mtl
         , CASE WHEN r.original = '' THEN r.title ELSE r.original END
         , CASE WHEN r.original = '' THEN NULL ELSE r.title END
      FROM releases_lang rl
      JOIN releases r ON r.id = rl.id;

INSERT INTO releases_titles_hist
    SELECT rl.chid, rl.lang, rl.mtl
         , CASE WHEN r.original = '' THEN r.title ELSE r.original END
         , CASE WHEN r.original = '' THEN NULL ELSE r.title END
      FROM releases_lang_hist rl
      JOIN releases_hist r ON r.chid = rl.chid;

ALTER TABLE releases      ADD COLUMN olang language NOT NULL DEFAULT 'ja';
ALTER TABLE releases_hist ADD COLUMN olang language NOT NULL DEFAULT 'ja';

-- 'releases' table needs an olang field now in order to select the proper
-- default title to display. Inherit these from the related (lowest-id) VN
-- entry if the release language matches, otherwise select an arbitrary one
-- (preferring English).
WITH rl (id, ol) AS (
    SELECT DISTINCT ON(rv.id) rv.id, COALESCE(rl.lang, re.lang, rf.lang, v.olang)
      FROM releases_vn rv
      JOIN vn v ON v.id = rv.vid
      LEFT JOIN releases_lang rl ON rl.id = rv.id AND rl.lang = v.olang
      LEFT JOIN releases_lang re ON re.id = rv.id AND re.lang = 'en'
      LEFT JOIN releases_lang rf ON rf.id = rv.id AND (rf.lang <> v.olang AND rf.lang <> 'en')
     ORDER BY rv.id, rl.id NULLS LAST, rv.vid, rl.lang
) UPDATE releases SET olang = ol FROM rl WHERE releases.id = rl.id AND ol <> 'ja';

WITH rl (id, ol) AS (
    SELECT DISTINCT ON(rv.chid) rv.chid, COALESCE(rl.lang, re.lang, rf.lang, v.olang)
      FROM releases_vn_hist rv
      JOIN vn v ON v.id = rv.vid
      LEFT JOIN releases_lang_hist rl ON rl.chid = rv.chid AND rl.lang = v.olang
      LEFT JOIN releases_lang_hist re ON re.chid = rv.chid AND re.lang = 'en'
      LEFT JOIN releases_lang_hist rf ON rf.chid = rv.chid AND (rf.lang <> v.olang AND rf.lang <> 'en')
     ORDER BY rv.chid, rl.chid NULLS LAST, rv.vid, rl.lang
) UPDATE releases_hist SET olang = ol FROM rl WHERE chid = id AND ol <> 'ja';

ALTER TABLE releases ALTER COLUMN c_search DROP NOT NULL, ALTER COLUMN c_search DROP EXPRESSION;

ALTER TABLE releases      DROP COLUMN title, DROP COLUMN original;
ALTER TABLE releases_hist DROP COLUMN title, DROP COLUMN original;

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;

DROP TABLE releases_lang, releases_lang_hist;

COMMIT;

\i sql/tableattrs.sql
\i sql/func.sql
\i sql/editfunc.sql
\i sql/perms.sql