summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-09-23 13:56:01 +0200
committerYorhel <git@yorhel.nl>2022-09-28 09:35:55 +0200
commit5515ca02b09500332dc0f4b8ca4819fce6106fd5 (patch)
treec8dc38d566438640ad788fc9556c93d806115439 /util
parent91b1931420806c661210f3f8299846eade131686 (diff)
Add update script
Diffstat (limited to 'util')
-rw-r--r--util/updates/wip-release-titles.sql82
1 files changed, 82 insertions, 0 deletions
diff --git a/util/updates/wip-release-titles.sql b/util/updates/wip-release-titles.sql
new file mode 100644
index 00000000..ad25de84
--- /dev/null
+++ b/util/updates/wip-release-titles.sql
@@ -0,0 +1,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