diff options
author | Yorhel <git@yorhel.nl> | 2022-01-20 09:02:15 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2022-02-08 11:36:07 +0100 |
commit | 727a4d84255f13badffe16c5858fdb036ddcdbce (patch) | |
tree | 6c77f72e2423a7c7425caea6aebc6ba8eaf001f8 /lib/VNWeb/TT | |
parent | 9d651b8d138349b3853f23117cc3802fcfe0ba6a (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 'lib/VNWeb/TT')
-rw-r--r-- | lib/VNWeb/TT/TagLinks.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/TT/TagPage.pm | 4 |
2 files changed, 4 insertions, 4 deletions
diff --git a/lib/VNWeb/TT/TagLinks.pm b/lib/VNWeb/TT/TagLinks.pm index 874b3cf9..d953a96e 100644 --- a/lib/VNWeb/TT/TagLinks.pm +++ b/lib/VNWeb/TT/TagLinks.pm @@ -69,7 +69,7 @@ TUWF::get qr{/g/links}, sub { my($lst, $np) = tuwf->dbPagei({ page => $opt->{p}, results => 50 }, ' SELECT tv.vid, tv.uid, tv.tag, tv.vote, tv.spoiler,', sql_totime('tv.date'), 'as date, tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) AS ignore, tv.notes, v.title,', sql_user(), ', t.name FROM tags_vn tv - JOIN vn v ON v.id = tv.vid + JOIN vnt v ON v.id = tv.vid LEFT JOIN users u ON u.id = tv.uid JOIN tags t ON t.id = tv.tag WHERE', $where, ' @@ -98,7 +98,7 @@ TUWF::get qr{/g/links}, sub { li_ sub { txt_ '['; a_ href => url(v=>undef, p=>undef), 'remove'; txt_ '] '; txt_ 'Visual novel'; txt_ ' '; - a_ href => "/$opt->{v}", tuwf->dbVali('SELECT title FROM vn WHERE id=', \$opt->{v})||'Unknown VN'; + a_ href => "/$opt->{v}", tuwf->dbVali('SELECT title FROM vnt WHERE id=', \$opt->{v})||'Unknown VN'; } if defined $opt->{v}; } } diff --git a/lib/VNWeb/TT/TagPage.pm b/lib/VNWeb/TT/TagPage.pm index 0a5fa903..7863ac12 100644 --- a/lib/VNWeb/TT/TagPage.pm +++ b/lib/VNWeb/TT/TagPage.pm @@ -102,9 +102,9 @@ sub vns_ { db_maytimeout { $count = tuwf->dbVali('SELECT count(*) FROM vn v JOIN tags_vn_inherit tvi ON tvi.vid = v.id WHERE', $where); $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, ' - SELECT tvi.rating AS tagscore, v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average + SELECT tvi.rating AS tagscore, v.id, v.title, v.alttitle, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang - FROM vn v + FROM vnt v JOIN tags_vn_inherit tvi ON tvi.vid = v.id WHERE', $where, ' ORDER BY', $opt->{s}->sql_order(), |