summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-01-20 09:02:15 +0100
committerYorhel <git@yorhel.nl>2022-02-08 11:36:07 +0100
commit727a4d84255f13badffe16c5858fdb036ddcdbce (patch)
tree6c77f72e2423a7c7425caea6aebc6ba8eaf001f8 /util
parent9d651b8d138349b3853f23117cc3802fcfe0ba6a (diff)
VN: Add support for multiple titles in different languages
This implements the main database model part of custom title languages (https://vndb.org/t12465). Selecting the right title for display is done in SQL through the 'vnt' VIEW, which can be overridden in each session with a TEMPORARY VIEW in order to support user title preferences, but that part has not been implemented yet. I had started out using an sql_vn() function that returned a subquery instead of using a VIEW, but then ran into trouble with the item_info() SQL function. This VIEW approach also happened to simplify much of the code. I did have to get rid of the Discusssions::Lib::sql_boards() function, as Postgres was unable to optimize the subquery inside a UNION inside a subquery for some reason. Haven't run into any other noticeable performance regressions yet. TODO: - Implement actual user title preferences - Add the correct 'lang' HTML attributes everywhere a title is displayed (we do have the information now, though it still isn't trivial) - Add title fetching support to API
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbdump.pl1
-rwxr-xr-xutil/devdump.pl2
-rw-r--r--util/updates/wip-vn-titles.sql41
3 files changed, 43 insertions, 1 deletions
diff --git a/util/dbdump.pl b/util/dbdump.pl
index f71b9598..5488d6e4 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -107,6 +107,7 @@ my %tables = (
.' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)'
.' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' },
vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' },
+ vn_titles => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_length_votes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
, order => 'vid, uid' },
wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden
diff --git a/util/devdump.pl b/util/devdump.pl
index 2a20544c..9d801912 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -157,7 +157,7 @@ sub copy_entry {
# Visual novels
copy anime => "SELECT DISTINCT a.* FROM anime a JOIN vn_anime_hist v ON v.aid = a.id JOIN changes c ON c.id = v.chid WHERE c.itemid IN($vids)";
- copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids;
+ copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots vn_titles/], \@vids;
# VN-related niceties
copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE vid IN($vids)", {uid => 'user'};
diff --git a/util/updates/wip-vn-titles.sql b/util/updates/wip-vn-titles.sql
new file mode 100644
index 00000000..b8162ea7
--- /dev/null
+++ b/util/updates/wip-vn-titles.sql
@@ -0,0 +1,41 @@
+BEGIN;
+
+CREATE TABLE vn_titles (
+ id vndbid NOT NULL,
+ lang language NOT NULL,
+ title text NOT NULL,
+ latin text,
+ official boolean NOT NULL,
+ PRIMARY KEY(id, lang)
+);
+
+CREATE TABLE vn_titles_hist (
+ chid integer NOT NULL,
+ lang language NOT NULL,
+ title text NOT NULL,
+ latin text,
+ official boolean NOT NULL,
+ PRIMARY KEY(chid, lang)
+);
+
+INSERT INTO vn_titles SELECT id, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn;
+INSERT INTO vn_titles_hist SELECT chid, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn_hist;
+
+ALTER TABLE vn_titles ADD CONSTRAINT vn_titles_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_titles_hist ADD CONSTRAINT vn_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
+ALTER TABLE vn ADD CONSTRAINT vn_olang_fkey FOREIGN KEY (id,olang) REFERENCES vn_titles (id,lang) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_olang_fkey FOREIGN KEY (chid,olang)REFERENCES vn_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED;
+
+-- TODO: actually drop
+ALTER TABLE vn RENAME COLUMN original TO old_original;
+ALTER TABLE vn RENAME COLUMN title TO old_title;
+--ALTER TABLE vn RENAME COLUMN old_original TO original;
+--ALTER TABLE vn RENAME COLUMN old_title TO title;
+--ALTER TABLE vn DROP COLUMN original, DROP COLUMN title;
+
+CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, 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;
+
+COMMIT;
+\i sql/func.sql
+\i sql/editfunc.sql
+\i sql/perms.sql