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 | |
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')
33 files changed, 151 insertions, 125 deletions
diff --git a/lib/VNWeb/Chars/Edit.pm b/lib/VNWeb/Chars/Edit.pm index 0bc00d5c..4beefaa5 100644 --- a/lib/VNWeb/Chars/Edit.pm +++ b/lib/VNWeb/Chars/Edit.pm @@ -73,7 +73,7 @@ TUWF::get qr{/$RE{crev}/(?<action>edit|copy)} => sub { enrich_merge tid => 'SELECT t.id AS tid, t.name, t.hidden, t.locked, t.applicable, g.name AS group, g.order AS order, false AS new FROM traits t LEFT JOIN traits g ON g.id = t.group WHERE t.id IN', $e->{traits}; $e->{traits} = [ sort { ($a->{order}//99) <=> ($b->{order}//99) || $a->{name} cmp $b->{name} } grep !$copy || $_->{applicable}, $e->{traits}->@* ]; - enrich_merge vid => 'SELECT id AS vid, title FROM vn WHERE id IN', $e->{vns}; + enrich_merge vid => 'SELECT id AS vid, title FROM vnt WHERE id IN', $e->{vns}; $e->{vns} = [ sort { $a->{title} cmp $b->{title} || idcmp($a->{vid}, $b->{vid}) || idcmp($a->{rid}||'r0', $b->{rid}||'r0') } $e->{vns}->@* ]; my %vns; @@ -100,7 +100,7 @@ TUWF::get qr{/$RE{crev}/(?<action>edit|copy)} => sub { TUWF::get qr{/$RE{vid}/addchar}, sub { return tuwf->resDenied if !can_edit c => undef; - my $v = tuwf->dbRowi('SELECT id, title FROM vn WHERE NOT hidden AND id =', \tuwf->capture('id')); + my $v = tuwf->dbRowi('SELECT id, title FROM vnt WHERE NOT hidden AND id =', \tuwf->capture('id')); return tuwf->resNotFound if !$v->{id}; my $e = elm_empty($FORM_OUT); diff --git a/lib/VNWeb/Chars/List.pm b/lib/VNWeb/Chars/List.pm index 5e44a606..81d2dc19 100644 --- a/lib/VNWeb/Chars/List.pm +++ b/lib/VNWeb/Chars/List.pm @@ -26,7 +26,7 @@ sub listing_ { td_ class => 'tc2', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name}; b_ class => 'grayedout', sub { - join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }, $_->{vn}->@*; + join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title} }, $_->{vn}->@*; }; }; } for @$list; @@ -49,7 +49,7 @@ sub listing_ { a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name}; br_; b_ class => 'grayedout', sub { - join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }, $_->{vn}->@*; + join_ ', ', sub { a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title} }, $_->{vn}->@*; }; }; } for @$list; @@ -71,9 +71,9 @@ sub listing_ { # Also used by VNWeb::TT::TraitPage sub enrich_listing { enrich vn => id => cid => sub { sql ' - SELECT DISTINCT cv.id AS cid, v.id, v.title, v.original + SELECT DISTINCT cv.id AS cid, v.id, v.title, v.alttitle FROM chars_vns cv - JOIN vn v ON v.id = cv.vid + JOIN vnt v ON v.id = cv.vid WHERE NOT v.hidden AND cv.spoil = 0 AND cv.id IN', $_, ' ORDER BY v.title' }, @_; diff --git a/lib/VNWeb/Chars/Page.pm b/lib/VNWeb/Chars/Page.pm index e2d10068..c16b1cd1 100644 --- a/lib/VNWeb/Chars/Page.pm +++ b/lib/VNWeb/Chars/Page.pm @@ -20,7 +20,7 @@ sub enrich_item { my($c) = @_; enrich_image_obj image => $c; - enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $c->{vns}; + enrich_merge vid => 'SELECT id AS vid, title, alttitle FROM vnt WHERE id IN', $c->{vns}; enrich_merge rid => 'SELECT id AS rid, title AS rtitle, original AS roriginal FROM releases WHERE id IN', grep $_->{rid}, $c->{vns}->@*; enrich_merge tid => 'SELECT t.id AS tid, t.name, t.hidden, t.locked, t.applicable, t.sexual, coalesce(g.id, t.id) AS group, coalesce(g.name, t.name) AS groupname, coalesce(g.order,0) AS order @@ -42,9 +42,9 @@ sub fetch_chars { '); enrich vns => id => id => sub { sql ' - SELECT cv.id, cv.vid, cv.rid, cv.spoil, cv.role, v.title, v.original, r.title AS rtitle, r.original AS roriginal + SELECT cv.id, cv.vid, cv.rid, cv.spoil, cv.role, v.title, v.alttitle, r.title AS rtitle, r.original AS roriginal FROM chars_vns cv - JOIN vn v ON v.id = cv.vid + JOIN vnt v ON v.id = cv.vid LEFT JOIN releases r ON r.id = cv.rid WHERE cv.id IN', $_, $vid ? ('AND cv.vid =', \$vid) : (), ' ORDER BY v.title, cv.vid, cv.rid NULLS LAST' @@ -91,7 +91,7 @@ sub _rev_ { [ main_spoil => 'Spoiler', fmt => sub { txt_ fmtspoil $_ } ], [ image => 'Image', fmt => sub { image_ $_ } ], [ vns => 'Visual novels', fmt => sub { - a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{vid}; + a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{vid}; if($_->{rid}) { txt_ ' ['; a_ href => "/$_->{rid}", $_->{rid}; txt_ ']'; } @@ -181,11 +181,11 @@ sub chartable_ { # Just a VN link, no releases if(!$vn && $v->{rels}->@* == 1 && !$v->{rels}[0]{rid}) { txt_ $CHAR_ROLE{$v->{role}}{txt}.' - '; - a_ href => "/$v->{vid}", title => $v->{original}||$v->{title}, $v->{title}; + a_ href => "/$v->{vid}", title => $v->{alttitle}||$v->{title}, $v->{title}; spoil_ $v->{spoil}; # With releases } else { - a_ href => "/$v->{vid}", title => $v->{original}||$v->{title}, $v->{title} if !$vn; + a_ href => "/$v->{vid}", title => $v->{alttitle}||$v->{title}, $v->{title} if !$vn; br_ if !$vn; join_ \&br_, sub { b_ class => 'grayedout', '> '; diff --git a/lib/VNWeb/Discussions/Edit.pm b/lib/VNWeb/Discussions/Edit.pm index 321de780..b68ef432 100644 --- a/lib/VNWeb/Discussions/Edit.pm +++ b/lib/VNWeb/Discussions/Edit.pm @@ -112,9 +112,11 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub { TUWF::get qr{(?:/t/(?<board>$BOARD_RE)/new|/$RE{tid}\.1/edit)}, sub { my $board_id = tuwf->capture('board')||''; my($board_type) = $board_id =~ /^([^0-9]+)/; - $board_id = undef if $board_id !~ /[0-9]$/; + $board_id = $board_id =~ /[0-9]$/ ? dbobj $board_id : undef; my $tid = tuwf->capture('id'); + return tuwf->resNotFound if $board_id && !$board_id->{id}; + $board_type = 'ge' if $board_type && $board_type eq 'an' && !auth->permBoardmod; my $t = !$tid ? {} : tuwf->dbRowi(' @@ -136,13 +138,11 @@ TUWF::get qr{(?:/t/(?<board>$BOARD_RE)/new|/$RE{tid}\.1/edit)}, sub { } else { $t->{boards} = [ { btype => $board_type, - iid => $board_id||undef, - title => !$board_id ? undef : - tuwf->dbVali('SELECT title FROM', sql_boards(), 'x WHERE btype =', \$board_type, 'AND iid =', \$board_id) + iid => $board_id ? $board_id->{id} : undef, + title => $board_id ? $board_id->{title} : undef, } ]; - return tuwf->resNotFound if $board_id && !length $t->{boards}[0]{title}; push $t->{boards}->@*, { btype => 'u', iid => auth->uid, title => auth->user->{user_name} } - if $board_type eq 'u' && $board_id ne auth->uid; + if $board_type eq 'u' && $board_id->{id} ne auth->uid; } $t->{can_mod} = auth->permBoardmod; diff --git a/lib/VNWeb/Discussions/Elm.pm b/lib/VNWeb/Discussions/Elm.pm index 27bd12f0..08329763 100644 --- a/lib/VNWeb/Discussions/Elm.pm +++ b/lib/VNWeb/Discussions/Elm.pm @@ -27,11 +27,11 @@ elm_api Boards => undef, { 'SELECT btype, iid, title FROM (', sql_join('UNION ALL', - (map sql('SELECT 1, ', \$_, '::board_type, NULL::vndbid, NULL'),#, \$BOARD_TYPE{$_}{txt}), + (map sql('SELECT 1, ', \$_, '::board_type, NULL::vndbid, NULL'), grep $q eq $_ || $BOARD_TYPE{$_}{txt} =~ /\Q$q/i, grep !$BOARD_TYPE{$_}{dbitem} && ($BOARD_TYPE{$_}{post_perm} eq 'board' || auth->permBoardmod), keys %BOARD_TYPE), - item('vn', 'v', 'title', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'), + item('vnt', 'v', 'title', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'), item('producers', 'p', 'name', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'), item('users', 'u', 'username', 'true', sql 'lower(username) LIKE', \lc "%$qs%"), ), ') x(prio, btype, iid, title) diff --git a/lib/VNWeb/Discussions/Lib.pm b/lib/VNWeb/Discussions/Lib.pm index 7e9465b2..e5131934 100644 --- a/lib/VNWeb/Discussions/Lib.pm +++ b/lib/VNWeb/Discussions/Lib.pm @@ -3,7 +3,7 @@ package VNWeb::Discussions::Lib; use VNWeb::Prelude; use Exporter 'import'; -our @EXPORT = qw/$BOARD_RE sql_visible_threads sql_boards enrich_boards threadlist_ boardsearch_ boardtypes_/; +our @EXPORT = qw/$BOARD_RE sql_visible_threads enrich_boards threadlist_ boardsearch_ boardtypes_/; our $BOARD_RE = join '|', map $_.($BOARD_TYPE{$_}{dbitem}?'(?:[1-9][0-9]{0,5})?':''), keys %BOARD_TYPE; @@ -18,25 +18,20 @@ sub sql_visible_threads { } -# Returns a SELECT subquery with all board IDs -sub sql_boards { - sql q{( SELECT 'v'::board_type AS btype, id AS iid, title, original, hidden FROM vn - UNION ALL SELECT 'p'::board_type AS btype, id AS iid, name, original, hidden FROM producers - UNION ALL SELECT 'u'::board_type AS btype, id AS iid, username, NULL, false FROM users - )} -} - - # Adds a 'boards' array to threads. sub enrich_boards { my($filt, @lst) = @_; - enrich boards => id => tid => sub { sql q{ - SELECT tb.tid, tb.type AS btype, tb.iid, b.title, b.original + enrich boards => id => tid => sub { sql ' + SELECT tb.tid, tb.type AS btype, tb.iid + , COALESCE(v.title, p.name, u.username) AS title + , COALESCE(v.alttitle, p.original) AS alttitle FROM threads_boards tb - LEFT JOIN }, sql_boards(), q{b ON b.btype = tb.type AND b.iid = tb.iid - WHERE }, sql_and(sql('tb.tid IN', $_[0]), $filt||()), q{ + LEFT JOIN vnt v ON tb.type = \'v\' AND v.id = tb.iid + LEFT JOIN producers p ON tb.type = \'p\' AND p.id = tb.iid + LEFT JOIN users u ON tb.type = \'u\' AND u.id = tb.iid + WHERE ', sql_and(sql('tb.tid IN', $_[0]), $filt||()), ' ORDER BY tb.type, tb.iid - }}, @lst; + '}, @lst; } @@ -94,7 +89,7 @@ sub threadlist_ { b_ class => 'boards', sub { join_ ', ', sub { a_ href => '/t/'.($_->{iid}||$_->{btype}), - title => $_->{original}||$BOARD_TYPE{$_->{btype}}{txt}, + title => $_->{alttitle}||$BOARD_TYPE{$_->{btype}}{txt}, shorten $_->{title}||$BOARD_TYPE{$_->{btype}}{txt}, 30; }, $l->{boards}->@[0 .. min 4, $#{$l->{boards}}]; txt_ ', ...' if $l->{boards}->@* > 4; diff --git a/lib/VNWeb/Discussions/Search.pm b/lib/VNWeb/Discussions/Search.pm index cb7a31b7..6a3babc0 100644 --- a/lib/VNWeb/Discussions/Search.pm +++ b/lib/VNWeb/Discussions/Search.pm @@ -86,12 +86,12 @@ sub posts_ { ) : (), $reviews ? ( sql('SELECT w.id, 0, v.title, w.uid, w.date, w.text FROM reviews w - JOIN vn v ON v.id = w.vid + JOIN vnt v ON v.id = w.vid WHERE NOT w.c_flagged AND bb_tsvector(w.text) @@ to_tsquery(', \$ts, ')'), sql('SELECT wp.id, wp.num, v.title, wp.uid, wp.date, wp.msg FROM reviews_posts wp JOIN reviews w ON w.id = wp.id - JOIN vn v ON v.id = w.vid + JOIN vnt v ON v.id = w.vid WHERE NOT w.c_flagged AND wp.hidden IS NULL AND bb_tsvector(wp.msg) @@ to_tsquery(', \$ts, ')'), ) : ()), ') m (id, num, title, uid, date, msg) LEFT JOIN users u ON u.id = m.uid diff --git a/lib/VNWeb/Discussions/Thread.pm b/lib/VNWeb/Discussions/Thread.pm index 84ce9977..669fa33b 100644 --- a/lib/VNWeb/Discussions/Thread.pm +++ b/lib/VNWeb/Discussions/Thread.pm @@ -83,7 +83,7 @@ sub metabox_ { a_ style => 'font-weight: bold', href => "/t/$_->{iid}", $_->{iid}; txt_ ':'; if($_->{title}) { - a_ href => "/$_->{iid}", title => $_->{original}||$_->{title}, $_->{title}; + a_ href => "/$_->{iid}", title => $_->{alttitle}||$_->{title}, $_->{title}; } else { b_ '[deleted]'; } diff --git a/lib/VNWeb/Discussions/UPosts.pm b/lib/VNWeb/Discussions/UPosts.pm index a8cb437c..afe7c256 100644 --- a/lib/VNWeb/Discussions/UPosts.pm +++ b/lib/VNWeb/Discussions/UPosts.pm @@ -49,7 +49,7 @@ TUWF::get qr{/$RE{uid}/posts}, sub { SELECT rp.id, rp.num, rp.msg, v.title, rp.date, rp.hidden IS NOT NULL FROM reviews_posts rp JOIN reviews r ON r.id = rp.id - JOIN vn v ON v.id = r.vid + JOIN vnt v ON v.id = r.vid WHERE rp.uid =', \$u->{id}, auth->permBoardmod ? () : 'AND rp.hidden IS NULL', ' ) p(id,num,msg,title,date,hidden)'; diff --git a/lib/VNWeb/Elm.pm b/lib/VNWeb/Elm.pm index da98ae2b..0d90d0f8 100644 --- a/lib/VNWeb/Elm.pm +++ b/lib/VNWeb/Elm.pm @@ -104,7 +104,7 @@ our %apis = ( VNResult => [ { aoh => { # Response to 'VN' id => { vndbid => 'v' }, title => {}, - original => { required => 0, default => '' }, + alttitle => { required => 0, default => '' }, hidden => { anybool => 1 }, } } ], ProducerResult => [ { aoh => { # Response to 'Producers' diff --git a/lib/VNWeb/Images/Lib.pm b/lib/VNWeb/Images/Lib.pm index 3adb142d..fb5c57f5 100644 --- a/lib/VNWeb/Images/Lib.pm +++ b/lib/VNWeb/Images/Lib.pm @@ -29,10 +29,10 @@ sub enrich_image { , COALESCE(v.title, c.name, vsv.title) AS entry_title FROM images i LEFT JOIN image_votes iv ON iv.id = i.id AND iv.uid =}, \auth->uid, q{ - LEFT JOIN vn v ON i.id BETWEEN 'cv1' AND vndbid_max('cv') AND v.image = i.id + LEFT JOIN vnt v ON i.id BETWEEN 'cv1' AND vndbid_max('cv') AND v.image = i.id LEFT JOIN chars c ON i.id BETWEEN 'ch1' AND vndbid_max('ch') AND c.image = i.id LEFT JOIN vn_screenshots vs ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vs.scr = i.id - LEFT JOIN vn vsv ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vsv.id = vs.id + LEFT JOIN vnt vsv ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vsv.id = vs.id WHERE i.id IN}, $_ }, $l; diff --git a/lib/VNWeb/Misc/HomePage.pm b/lib/VNWeb/Misc/HomePage.pm index 36b241c6..6dcac4a0 100644 --- a/lib/VNWeb/Misc/HomePage.pm +++ b/lib/VNWeb/Misc/HomePage.pm @@ -16,7 +16,7 @@ sub screens_ { # (As of Sep 2020, over half of the VNs in the database have screenshots, so that assumption usually works) 'SELECT * FROM ( SELECT DISTINCT ON (v.id) i.id, i.width, i,height, v.id AS vid, v.title - FROM (SELECT id, title FROM vn v WHERE NOT v.hidden AND ', $filt->sql_where(), ' ORDER BY random() LIMIT', \30, ') v + FROM (SELECT id, title FROM vnt v WHERE NOT v.hidden AND ', $filt->sql_where(), ' ORDER BY random() LIMIT', \30, ') v JOIN vn_screenshots vs ON v.id = vs.id JOIN images i ON i.id = vs.scr WHERE ', $where, ' @@ -26,7 +26,7 @@ sub screens_ { SELECT i.id, i.width, i.height, v.id AS vid, v.title FROM (SELECT id, width, height FROM images i TABLESAMPLE SYSTEM (', \$sample, ') WHERE', $where, ' ORDER BY random() LIMIT', \4, ') i(id) JOIN vn_screenshots vs ON vs.scr = i.id - JOIN vn v ON v.id = vs.id + JOIN vnt v ON v.id = vs.id WHERE NOT v.hidden ORDER BY random() LIMIT', \4 @@ -125,7 +125,7 @@ sub recent_vn_posts_ { SELECT w.id, v.title, wp.num, wp.date, wp.uid FROM reviews w JOIN reviews_posts wp ON wp.id = w.id AND wp.num = w.c_lastnum - JOIN vn v ON v.id = w.vid + JOIN vnt v ON v.id = w.vid LEFT JOIN users u ON wp.uid = u.id WHERE NOT w.c_flagged AND wp.hidden IS NULL ORDER BY wp.date DESC LIMIT 10 @@ -207,7 +207,7 @@ sub reviews_ { my $lst = tuwf->dbAlli(' SELECT w.id, v.title, w.isfull, ', sql_user(), ',', sql_totime('w.date'), 'AS date FROM reviews w - JOIN vn v ON v.id = w.vid + JOIN vnt v ON v.id = w.vid LEFT JOIN users u ON u.id = w.uid WHERE NOT w.c_flagged ORDER BY w.id DESC LIMIT 10' diff --git a/lib/VNWeb/Producers/Page.pm b/lib/VNWeb/Producers/Page.pm index c5a2b2e5..ac722145 100644 --- a/lib/VNWeb/Producers/Page.pm +++ b/lib/VNWeb/Producers/Page.pm @@ -78,8 +78,8 @@ sub rel_ { enrich_extlinks r => $r; enrich_release $r; enrich vn => id => rid => sub { sql ' - SELECT rv.id as rid, rv.rtype, v.id, v.title, v.original - FROM vn v + SELECT rv.id as rid, rv.rtype, v.id, v.title, v.alttitle + FROM vnt v JOIN releases_vn rv ON rv.vid = v.id WHERE NOT v.hidden AND rv.id IN', $_, ' ORDER BY v.title @@ -101,7 +101,7 @@ sub rel_ { tr_ class => 'vn', sub { td_ colspan => 8, sub { ulists_widget_ $v; - a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, $v->{title}; + a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, $v->{title}; }; my $ropt = { id => $v->{id}, prod => 1 }; for my $rel ($vn{$v->{id}}->@*) { @@ -118,8 +118,8 @@ sub rel_ { sub vns_ { my($p) = @_; my $v = tuwf->dbAlli(q{ - SELECT v.id, v.title, v.original, rels.developer, rels.publisher, rels.released - FROM vn v + SELECT v.id, v.title, v.alttitle, rels.developer, rels.publisher, rels.released + FROM vnt v JOIN ( SELECT rv.vid, bool_or(rp.developer), bool_or(rp.publisher) , COALESCE(MIN(r.released) FILTER(WHERE rv.rtype <> 'trial'), MIN(r.released)) @@ -141,7 +141,7 @@ sub vns_ { li_ sub { span_ sub { rdate_ $_->{released} }; ulists_widget_ $_; - a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title}; + a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title}; span_ join ' & ', $_->{publisher} ? 'Publisher' : (), $_->{developer} ? 'Developer' : (); diff --git a/lib/VNWeb/Releases/Edit.pm b/lib/VNWeb/Releases/Edit.pm index 1c44ffb3..c4abf8f0 100644 --- a/lib/VNWeb/Releases/Edit.pm +++ b/lib/VNWeb/Releases/Edit.pm @@ -69,7 +69,7 @@ TUWF::get qr{/$RE{rrev}/(?<action>edit|copy)} => sub { to_extlinks $e; - enrich_merge vid => 'SELECT id AS vid, title FROM vn WHERE id IN', $e->{vn}; + enrich_merge vid => 'SELECT id AS vid, title FROM vnt WHERE id IN', $e->{vn}; enrich_merge pid => 'SELECT id AS pid, name FROM producers WHERE id IN', $e->{producers}; $e->@{qw/gtin catalog extlinks/} = elm_empty($FORM_OUT)->@{qw/gtin catalog extlinks/} if $copy; @@ -85,7 +85,12 @@ TUWF::get qr{/$RE{rrev}/(?<action>edit|copy)} => sub { TUWF::get qr{/$RE{vid}/add}, sub { return tuwf->resDenied if !can_edit r => undef; - my $v = tuwf->dbRowi('SELECT id, title, original FROM vn WHERE id =', \tuwf->capture('id')); + my $v = tuwf->dbRowi(' + SELECT v.id, v.title AS displaytitle, vo.title, vo.latin + FROM vnt v + JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang + WHERE v.id =', \tuwf->capture('id') + ); return tuwf->resNotFound if !$v->{id}; my $delrel = tuwf->dbAlli('SELECT r.id, r.title, r.original FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE r.hidden AND rv.vid =', \$v->{id}, 'ORDER BY id'); @@ -93,16 +98,16 @@ TUWF::get qr{/$RE{vid}/add}, sub { my $e = { elm_empty($FORM_OUT)->%*, - title => $v->{title}, - original => $v->{original}, - vn => [{vid => $v->{id}, title => $v->{title}, rtype => 'complete'}], + title => $v->{latin}//$v->{title}, + original => $v->{latin} ? $v->{title} : '', + vn => [{vid => $v->{id}, title => $v->{displaytitle}, rtype => 'complete'}], official => 1, }; $e->{authmod} = auth->permDbmod; - framework_ title => "Add release to $v->{title}", + framework_ title => "Add release to $v->{displaytitle}", sub { - editmsg_ r => undef, "Add release to $v->{title}"; + editmsg_ r => undef, "Add release to $v->{displaytitle}"; div_ class => 'mainbox', sub { h1_ 'Deleted releases'; diff --git a/lib/VNWeb/Releases/Page.pm b/lib/VNWeb/Releases/Page.pm index 5078376a..0cf2e4a9 100644 --- a/lib/VNWeb/Releases/Page.pm +++ b/lib/VNWeb/Releases/Page.pm @@ -7,7 +7,7 @@ sub enrich_item { my($r) = @_; enrich_merge pid => 'SELECT id AS pid, name, original FROM producers WHERE id IN', $r->{producers}; - enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $r->{vn}; + enrich_merge vid => 'SELECT id AS vid, title, alttitle FROM vnt WHERE id IN', $r->{vn}; $r->{lang} = [ sort { ($a->{mtl}?1:0) <=> ($b->{mtl}?1:0) || $a->{lang} cmp $b->{lang} } $r->{lang}->@* ]; $r->{platforms} = [ sort map $_->{platform}, $r->{platforms}->@* ]; @@ -24,7 +24,7 @@ sub _rev_ { revision_ $r, \&enrich_item, [ vn => 'Relations', fmt => sub { abbr_ class => "icons rt$_->{rtype}", title => $_->{rtype}, ' '; - a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title}; + a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title}; txt_ " ($_->{rtype})" if $_->{rtype} ne 'complete'; } ], [ official => 'Official', fmt => 'bool' ], @@ -66,7 +66,7 @@ sub _infotable_ { td_ sub { join_ \&br_, sub { abbr_ class => "icons rt$_->{rtype}", title => $_->{rtype}, ' '; - a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title}; + a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title}; txt_ " ($_->{rtype})" if $_->{rtype} ne 'complete'; }, $r->{vn}->@* } diff --git a/lib/VNWeb/Reviews/Edit.pm b/lib/VNWeb/Reviews/Edit.pm index 9c76f5fb..befc93b5 100644 --- a/lib/VNWeb/Reviews/Edit.pm +++ b/lib/VNWeb/Reviews/Edit.pm @@ -33,7 +33,7 @@ sub releases { TUWF::get qr{/$RE{vid}/addreview}, sub { - my $v = tuwf->dbRowi('SELECT id, title FROM vn WHERE NOT hidden AND id =', \tuwf->capture('id')); + my $v = tuwf->dbRowi('SELECT id, title FROM vnt WHERE NOT hidden AND id =', \tuwf->capture('id')); return tuwf->resNotFound if !$v->{id}; my $id = tuwf->dbVali('SELECT id FROM reviews WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid); @@ -58,7 +58,7 @@ TUWF::get qr{/$RE{vid}/addreview}, sub { TUWF::get qr{/$RE{wid}/edit}, sub { my $e = tuwf->dbRowi( 'SELECT r.id, r.uid AS user_id, r.vid, r.rid, r.isfull, r.modnote, r.text, r.spoiler, r.locked, v.title AS vntitle - FROM reviews r JOIN vn v ON v.id = r.vid WHERE r.id =', \tuwf->capture('id') + FROM reviews r JOIN vnt v ON v.id = r.vid WHERE r.id =', \tuwf->capture('id') ); return tuwf->resNotFound if !$e->{id}; return tuwf->resDenied if !can_edit w => $e; diff --git a/lib/VNWeb/Reviews/List.pm b/lib/VNWeb/Reviews/List.pm index 5d33e134..33b2e0ac 100644 --- a/lib/VNWeb/Reviews/List.pm +++ b/lib/VNWeb/Reviews/List.pm @@ -62,7 +62,7 @@ TUWF::get qr{/w}, sub { , ', sql_user(), ',', sql_totime('w.date'), 'as date , ', sql_user('wpu','lu_'), ',', sql_totime('wp.date'), 'as ldate FROM reviews w - JOIN vn v ON v.id = w.vid + JOIN vnt v ON v.id = w.vid LEFT JOIN users u ON u.id = w.uid LEFT JOIN reviews_posts wp ON w.id = wp.id AND w.c_lastnum = wp.num LEFT JOIN users wpu ON wpu.id = wp.uid diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm index 4a270553..16bb0f56 100644 --- a/lib/VNWeb/Reviews/Page.pm +++ b/lib/VNWeb/Reviews/Page.pm @@ -97,7 +97,7 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub { , v.title, rel.title AS rtitle, rel.original AS roriginal, relv.rtype, rv.vote AS my, COALESCE(rv.overrule,false) AS overrule , ', sql_user(), ',', sql_totime('r.date'), 'AS date,', sql_totime('r.lastmod'), 'AS lastmod FROM reviews r - JOIN vn v ON v.id = r.vid + JOIN vnt v ON v.id = r.vid LEFT JOIN releases rel ON rel.id = r.rid LEFT JOIN releases_vn relv ON relv.id = r.rid AND relv.vid = r.vid LEFT JOIN users u ON u.id = r.uid diff --git a/lib/VNWeb/Staff/Page.pm b/lib/VNWeb/Staff/Page.pm index 81746048..8d143fcc 100644 --- a/lib/VNWeb/Staff/Page.pm +++ b/lib/VNWeb/Staff/Page.pm @@ -74,9 +74,9 @@ sub _roles_ { my %alias = map +($_->{aid}, $_), $s->{alias}->@*; my $roles = tuwf->dbAlli(q{ - SELECT v.id, vs.aid, vs.role, vs.note, v.c_released, v.title, v.original + SELECT v.id, vs.aid, vs.role, vs.note, v.c_released, v.title, v.alttitle FROM vn_staff vs - JOIN vn v ON v.id = vs.id + JOIN vnt v ON v.id = vs.id WHERE vs.aid IN}, [ keys %alias ], q{ AND NOT v.hidden ORDER BY v.c_released ASC, v.title ASC, vs.role ASC @@ -100,7 +100,7 @@ sub _roles_ { my($v, $a) = ($_, $alias{$_->{aid}}); td_ class => 'tc_ulist', sub { ulists_widget_ $v if !$vns{$v->{id}}++ } if auth; td_ class => 'tc1', sub { - a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 60; + a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 60; }; td_ class => 'tc2', sub { rdate_ $v->{c_released} }; td_ class => 'tc3', $CREDIT_TYPE{$v->{role}}; @@ -117,10 +117,10 @@ sub _cast_ { my %alias = map +($_->{aid}, $_), $s->{alias}->@*; my $cast = tuwf->dbAlli(q{ - SELECT vs.aid, v.id, v.c_released, v.title, v.original, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note, + SELECT vs.aid, v.id, v.c_released, v.title, v.alttitle, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note, (SELECT MIN(cv.spoil) FROM chars_vns cv WHERE cv.id = c.id AND cv.vid = v.id) AS spoil FROM vn_seiyuu vs - JOIN vn v ON v.id = vs.id + JOIN vnt v ON v.id = vs.id JOIN chars c ON c.id = vs.cid WHERE vs.aid IN}, [ keys %alias ], q{ AND NOT v.hidden @@ -156,7 +156,7 @@ sub _cast_ { my($v, $a) = ($_, $alias{$_->{aid}}); td_ class => 'tc_ulist', sub { ulists_widget_ $v if !$vns{$v->{id}}++ } if auth; td_ class => 'tc1', sub { - a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 60; + a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 60; }; td_ class => 'tc2', sub { rdate_ $v->{c_released} }; td_ class => 'tc3', sub { 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(), diff --git a/lib/VNWeb/ULists/Elm.pm b/lib/VNWeb/ULists/Elm.pm index ab2839e0..ceaf6eb5 100644 --- a/lib/VNWeb/ULists/Elm.pm +++ b/lib/VNWeb/ULists/Elm.pm @@ -226,7 +226,7 @@ our $WIDGET = form_compile out => $VNWeb::Elm::apis{UListWidget}[0]{keys}; elm_api UListWidget => $WIDGET, { uid => { vndbid => 'u' }, vid => { vndbid => 'v' } }, sub { my($data) = @_; return elm_Unauth if !ulists_own $data->{uid}; - my $v = tuwf->dbRowi('SELECT id, title, c_released FROM vn WHERE id =', \$data->{vid}); + my $v = tuwf->dbRowi('SELECT id, title, c_released FROM vnt WHERE id =', \$data->{vid}); return elm_Invalid if !defined $v->{title}; elm_UListWidget ulists_widget_full_data $v, $data->{uid}; }; diff --git a/lib/VNWeb/ULists/Export.pm b/lib/VNWeb/ULists/Export.pm index 655bbd54..50ab3b9e 100644 --- a/lib/VNWeb/ULists/Export.pm +++ b/lib/VNWeb/ULists/Export.pm @@ -15,17 +15,21 @@ sub data { # We'd like ISO7601/RFC3339 timestamps in UTC with accuracy to the second. my sub tz { sql 'to_char(', $_[0], ' at time zone \'utc\',', \'YYYY-MM-DD"T"HH24:MM:SS"Z"', ') as', $_[1] } + # XXX: This keeps the old "title"/"original" fields for compatibility, but + # should the export take user title preferences into account instead? Or + # export all known titles? my $d = { 'export-date' => tuwf->dbVali(select => tz('NOW()', 'now')), user => tuwf->dbRowi('SELECT id, username as name FROM users WHERE id =', \$uid), labels => tuwf->dbAlli('SELECT id, label, private FROM ulist_labels WHERE uid =', \$uid, 'ORDER BY id'), vns => tuwf->dbAlli(' - SELECT v.id, v.title, v.original, uv.vote, uv.started, uv.finished, uv.notes + SELECT v.id, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN \'\' ELSE vo.title END AS original, uv.vote, uv.started, uv.finished, uv.notes , ', sql_comma(tz('uv.added', 'added'), tz('uv.lastmod', 'lastmod'), tz('uv.vote_date', 'vote_date')), ' FROM ulist_vns uv JOIN vn v ON v.id = uv.vid + JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang WHERE uv.uid =', \$uid, ' - ORDER BY v.title') + ORDER BY title') }; enrich labels => id => vid => sub { sql ' SELECT uvl.vid, ul.id, ul.label, ul.private diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm index 0df4ac41..62040de0 100644 --- a/lib/VNWeb/ULists/List.pm +++ b/lib/VNWeb/ULists/List.pm @@ -219,7 +219,7 @@ sub vn_ { } if $opt->{s}->vis('label'); td_ class => 'tc_title', sub { - a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 70; + a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 70; b_ class => 'grayedout', id => 'ulist_notes_'.$v->{id}, $v->{notes} if $v->{notes} || $own; }; @@ -272,15 +272,15 @@ sub listing_ { $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) ? sql 'match_firstchar(v.title, ', \$opt->{ch}, ')' : (); - my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vn v ON v.id = uv.vid WHERE', $where); + my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vnt v ON v.id = uv.vid WHERE', $where); my $lst = tuwf->dbPagei({ page => $opt->{p}, results => $opt->{s}->results }, - 'SELECT v.id, v.title, v.original, uv.vote, uv.notes, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released + 'SELECT v.id, v.title, v.alttitle, uv.vote, uv.notes, uv.started, uv.finished, v.c_rating, v.c_votecount, v.c_released ,', sql_totime('uv.added'), ' as added ,', sql_totime('uv.lastmod'), ' as lastmod ,', sql_totime('uv.vote_date'), ' as vote_date FROM ulist_vns uv - JOIN vn v ON v.id = uv.vid + JOIN vnt v ON v.id = uv.vid WHERE', $where, ' ORDER BY', $opt->{s}->sql_order(), 'NULLS LAST, v.title' ); diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm index 803fff65..43d4dd8e 100644 --- a/lib/VNWeb/User/Page.pm +++ b/lib/VNWeb/User/Page.pm @@ -142,9 +142,9 @@ sub _votestats_ { }; my $recent = tuwf->dbAlli(' - SELECT vn.id, vn.title, vn.original, uv.vote,', sql_totime('uv.vote_date'), 'AS date + SELECT v.id, v.title, v.alttitle, uv.vote,', sql_totime('uv.vote_date'), 'AS date FROM ulist_vns uv - JOIN vn ON vn.id = uv.vid + JOIN vnt v ON v.id = uv.vid WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id}, $own ? () : ( 'AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)' @@ -159,7 +159,7 @@ sub _votestats_ { } } }; tr_ sub { my $v = $_; - td_ sub { a_ href => "/$v->{id}", title => $v->{original}||$v->{title}, shorten $v->{title}, 30 }; + td_ sub { a_ href => "/$v->{id}", title => $v->{alttitle}||$v->{title}, shorten $v->{title}, 30 }; td_ fmtvote $v->{vote}; td_ fmtdate $v->{date}; } for @$recent; diff --git a/lib/VNWeb/VN/Edit.pm b/lib/VNWeb/VN/Edit.pm index 9decd145..ee14a789 100644 --- a/lib/VNWeb/VN/Edit.pm +++ b/lib/VNWeb/VN/Edit.pm @@ -7,8 +7,12 @@ use VNWeb::Releases::Lib; my $FORM = { id => { required => 0, vndbid => 'v' }, - title => { maxlength => 250 }, - original => { required => 0, default => '', maxlength => 250 }, + titles => { sort_keys => 'lang', aoh => { + lang => { enum => \%LANGUAGE }, + title => { maxlength => 250 }, + latin => { required => 0, default => undef, maxlength => 250 }, + official => { anybool => 1 }, + } }, alias => { required => 0, default => '', maxlength => 500 }, desc => { required => 0, default => '', maxlength => 10240 }, olang => { enum => \%LANGUAGE, default => 'ja' }, @@ -20,7 +24,6 @@ my $FORM = { relation => { enum => \%VN_RELATION }, official => { anybool => 1 }, title => { _when => 'out' }, - original => { _when => 'out', required => 0, default => '' }, } }, anime => { sort_keys => 'aid', aoh => { aid => { id => 1 }, @@ -76,6 +79,7 @@ TUWF::get qr{/$RE{vrev}/edit} => sub { $e->{authmod} = auth->permDbmod; $e->{editsum} = $e->{chrev} == $e->{maxrev} ? '' : "Reverted to revision $e->{id}.$e->{chrev}"; + $e->{titles} = [ sort { $a->{lang} cmp $b->{lang} } $e->{titles}->@* ]; if($e->{image}) { $e->{image_info} = { id => $e->{image} }; enrich_image 0, [$e->{image_info}]; @@ -85,7 +89,7 @@ TUWF::get qr{/$RE{vrev}/edit} => sub { $_->{info} = {id=>$_->{scr}} for $e->{screenshots}->@*; enrich_image 0, [map $_->{info}, $e->{screenshots}->@*]; - enrich_merge vid => 'SELECT id AS vid, title, original FROM vn WHERE id IN', $e->{relations}; + enrich_merge vid => 'SELECT id AS vid, title, alttitle FROM vnt WHERE id IN', $e->{relations}; enrich_merge aid => 'SELECT id AS aid, title_romaji AS title, COALESCE(title_kanji, \'\') AS original FROM anime WHERE id IN', $e->{anime}; enrich_merge aid => 'SELECT id, aid, name, original FROM staff_alias WHERE aid IN', $e->{staff}, $e->{seiyuu}; @@ -134,6 +138,7 @@ elm_api VNEdit => $FORM_OUT, $FORM_IN, sub { } $data->{desc} = bb_subst_links $data->{desc}; $data->{alias} =~ s/\n\n+/\n/; + die "No title in original language" if !grep $_->{lang} eq $data->{olang}, $data->{titles}->@*; validate_dbid 'SELECT id FROM anime WHERE id IN', map $_->{aid}, $data->{anime}->@*; validate_dbid 'SELECT id FROM images WHERE id IN', $data->{image} if $data->{image}; diff --git a/lib/VNWeb/VN/Elm.pm b/lib/VNWeb/VN/Elm.pm index 0f6ca5d4..0a018b4d 100644 --- a/lib/VNWeb/VN/Elm.pm +++ b/lib/VNWeb/VN/Elm.pm @@ -11,16 +11,16 @@ elm_api VN => undef, { die "No query" if !@q; elm_VNResult tuwf->dbPagei({ results => $data->{hidden}?50:15, page => 1 }, - 'SELECT v.id, v.title, v.original, v.hidden + 'SELECT v.id, v.title, v.alttitle, v.hidden FROM (', sql_join('UNION ALL', map +( - /^$RE{vid}$/ ? sql('SELECT 1, id FROM vn WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(title),', \sql_like($_), '), id FROM vn WHERE c_search LIKE ALL (search_query(', \"$_", '))'), + /^$RE{vid}$/ ? sql('SELECT 1, id FROM vnt WHERE id =', \"$+{id}") : (), + sql('SELECT 1+substr_score(lower(title),', \sql_like($_), '), id FROM vnt WHERE c_search LIKE ALL (search_query(', \"$_", '))'), ), @q), ') x(prio, id) - JOIN vn v ON v.id = x.id + JOIN vnt v ON v.id = x.id WHERE', sql_and($data->{hidden} ? () : 'NOT v.hidden'), ' - GROUP BY v.id, v.title, v.original, v.hidden + GROUP BY v.id, v.title, v.alttitle, v.hidden ORDER BY MIN(x.prio), v.title '); }; diff --git a/lib/VNWeb/VN/Graph.pm b/lib/VNWeb/VN/Graph.pm index a9227108..940c3a39 100644 --- a/lib/VNWeb/VN/Graph.pm +++ b/lib/VNWeb/VN/Graph.pm @@ -8,7 +8,7 @@ TUWF::get qr{/$RE{vid}/rg}, sub { my $id = tuwf->capture(1); my $num = tuwf->validate(get => num => { uint => 1, onerror => 15 })->data; my $unoff = tuwf->validate(get => unoff => { default => 1, anybool => 1 })->data; - my $v = tuwf->dbRowi('SELECT id, title, original, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \$id); + my $v = tuwf->dbRowi('SELECT id, title, title, hidden AS entry_hidden, locked AS entry_locked FROM vnt WHERE id =', \$id); my $has = tuwf->dbRowi('SELECT bool_or(official) AS official, bool_or(not official) AS unofficial FROM vn_relations WHERE id =', \$id, 'GROUP BY id'); $unoff = 1 if !$has->{official}; @@ -27,7 +27,7 @@ TUWF::get qr{/$RE{vid}/rg}, sub { # Fetch the nodes my $nodes = gen_nodes $id, $rel, $num; - enrich_merge id => "SELECT id, title, c_released, array_to_string(c_languages, '/') AS lang FROM vn WHERE id IN", values %$nodes; + enrich_merge id => "SELECT id, title, c_released, array_to_string(c_languages, '/') AS lang FROM vnt WHERE id IN", values %$nodes; my $total_nodes = keys { map +($_->{id0},1), @$rel }->%*; my $visible_nodes = keys %$nodes; diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm index f1a2635e..338e5edb 100644 --- a/lib/VNWeb/VN/Length.pm +++ b/lib/VNWeb/VN/Length.pm @@ -46,7 +46,7 @@ sub listing_ { td_ class => 'tc1', fmtdate $_->{date}; td_ class => 'tc2', sub { user_ $_ } if $mode ne 'u'; td_ class => 'tc2', sub { - a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title}; + a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title}; } if $mode ne 'v'; td_ class => 'tc3'.($_->{ignore}?' grayedout':''), sub { vnlength_ $_->{length} }; td_ class => 'tc4'.($_->{ignore}?' grayedout':''), ['Slow','Normal','Fast']->[$_->{speed}]; @@ -129,10 +129,10 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub { 'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, ' , sql_totime('l.date'), 'AS date, l.ignore OR u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore', $mode ne 'u' ? (', ', sql_user()) : (), - $mode ne 'v' ? ', v.title, v.original' : (), ' + $mode ne 'v' ? ', v.title, v.alttitle' : (), ' FROM vn_length_votes l LEFT JOIN users u ON u.id = l.uid', - $mode ne 'v' ? 'JOIN vn v ON v.id = l.vid' : (), + $mode ne 'v' ? 'JOIN vnt v ON v.id = l.vid' : (), 'WHERE', $where, 'ORDER BY', $opt->{s}->sql_order(), ); diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm index 40916777..29a76bd0 100644 --- a/lib/VNWeb/VN/List.pm +++ b/lib/VNWeb/VN/List.pm @@ -107,7 +107,7 @@ sub listing_ { tr_ sub { td_ class => 'tc_score', sub { tagscore_ $_->{tagscore} } if $tagscore; td_ class => 'tc_ulist', sub { ulists_widget_ $_ } if auth; - td_ class => 'tc_title', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }; + td_ class => 'tc_title', sub { a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, $_->{title} }; td_ class => 'tc_dev', sub { join_ ' & ', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name}; @@ -138,7 +138,7 @@ sub listing_ { a_ href => $url, title => $title, $label if $canlink; span_ $label if !$canlink; } - lnk_ "/$_->{id}", $_->{original}||$_->{title}, $_->{title}; + lnk_ "/$_->{id}", $_->{alttitle}||$_->{title}, $_->{title}; br_; join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@*; join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@*; @@ -200,7 +200,7 @@ sub listing_ { div_ class => 'mainbox vngrid', sub { div_ !$_->{image} || image_hidden($_->{image}) ? (class => 'noimage') : (style => 'background-image: url("'.imgurl($_->{image}{id}).'")'), sub { ulists_widget_ $_; - a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, sub { infoblock_ 0 }; + a_ href => "/$_->{id}", title => $_->{alttitle}||$_->{title}, sub { infoblock_ 0 }; } for @$list; } if $opt->{s}->grid; @@ -269,12 +269,12 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub { my $time = time; my($count, $list); db_maytimeout { - $count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where); + $count = tuwf->dbVali('SELECT count(*) FROM vnt v WHERE', $where); $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, ' - SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average + SELECT 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', $opt->{s}->vis('length') ? ', v.length, v.c_length, v.c_lengthnum' : (), ' - FROM vn v + FROM vnt v WHERE', $where, ' ORDER BY', $opt->{s}->sql_order(), ) : []; diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm index 4cfb86dd..0551d237 100644 --- a/lib/VNWeb/VN/Page.pm +++ b/lib/VNWeb/VN/Page.pm @@ -11,8 +11,8 @@ use VNDB::Func 'fmtrating'; # Also used by Chars::VNTab & Reviews::VNTab sub enrich_vn { my($v, $revonly) = @_; - enrich_merge id => 'SELECT id, c_votecount, c_length, c_lengthnum FROM vn WHERE id IN', $v; - enrich_merge vid => 'SELECT id AS vid, title, original, c_released FROM vn WHERE id IN', $v->{relations}; + enrich_merge id => 'SELECT id, c_votecount, c_length, c_lengthnum, title, alttitle FROM vnt WHERE id IN', $v; + enrich_merge vid => 'SELECT id AS vid, title, alttitle, c_released FROM vnt WHERE id IN', $v->{relations}; enrich_merge aid => 'SELECT id AS aid, title_romaji, title_kanji, year, type, ann_id, lastfetch FROM anime WHERE id IN', $v->{anime}; enrich_extlinks v => $v; enrich_image_obj image => $v; @@ -87,8 +87,15 @@ sub canvote { sub rev_ { my($v) = @_; revision_ $v, \&enrich_item, - [ title => 'Title (romaji)' ], - [ original => 'Original title' ], + [ titles => 'Title(s)', fmt => sub { + abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, ''; + txt_ $_->{title}; + if($_->{latin}) { + b_ class => 'grayedout', ' / '; + txt_ $_->{latin}; + } + b_ class => 'grayedout', ' (unofficial)' if !$_->{official}; + }], [ alias => 'Alias' ], [ olang => 'Original language', fmt => \%LANGUAGE ], [ desc => 'Description' ], @@ -108,7 +115,7 @@ sub rev_ { }], [ relations => 'Relations', fmt => sub { txt_ sprintf '[%s] %s: ', $_->{official} ? 'official' : 'unofficial', $VN_RELATION{$_->{relation}}{txt}; - a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title}; + a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, $_->{title}; }], [ anime => 'Anime', fmt => sub { a_ href => "https://anidb.net/anime/$_->{aid}", "a$_->{aid}" }], [ screenshots => 'Screenshots', fmt => sub { @@ -145,7 +152,7 @@ sub infobox_relations_ { dd_ sub { join_ \&br_, sub { b_ class => 'grayedout', '[unofficial] ' if !$_->{official}; - a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, shorten $_->{title}, 40; + a_ href => "/$_->{vid}", title => $_->{alttitle}||$_->{title}, shorten $_->{title}, 40; }, $rel{$_}->@*; } } @@ -366,27 +373,37 @@ sub infobox_ { div_ class => 'mainbox', sub { itemmsg_ $v; h1_ $v->{title}; - h2_ class => 'alttitle', lang_attr($v->{olang}), $v->{original} if $v->{original}; + h2_ class => 'alttitle', lang_attr($v->{olang}), $v->{alttitle} if $v->{alttitle}; div_ class => 'vndetails', sub { div_ class => 'vnimg', sub { image_ $v->{image}, alt => $v->{title}; }; table_ class => 'stripe', sub { tr_ sub { - td_ class => 'key', 'Title'; - td_ class => 'title', sub { - txt_ $v->{title}; + td_ class => 'key', sub{ + txt_ $v->{titles}->@* > 1 ? 'Titles' : 'Title'; debug_ $v; - abbr_ class => "icons lang $v->{olang}", title => "Original language: $LANGUAGE{$v->{olang}}", ''; + }; + td_ class => 'title', sub { + table_ sub { + tr_ class => $_->{official}?undef:'grayedout', sub { + td_ sub { + abbr_ class => "icons lang $_->{lang}", title => $LANGUAGE{$_->{lang}}, ''; + }; + td_ sub { + span_ lang_attr($_->{lang}), $_->{title}; + if($_->{latin}) { + br_; + txt_ $_->{latin}; + } + } + } for (grep $_->{lang} eq $v->{olang}, $v->{titles}->@*), + grep $_->{lang} ne $v->{olang}, sort { $a->{official} cmp $b->{official} || $a->{lang} cmp $b->{lang} } $v->{titles}->@* + }; }; }; tr_ sub { - td_ 'Original title'; - td_ lang_attr($v->{olang}), $v->{original}; - } if $v->{original}; - - tr_ sub { td_ 'Aliases'; td_ $v->{alias} =~ s/\n/, /gr; } if $v->{alias}; diff --git a/lib/VNWeb/VN/Tagmod.pm b/lib/VNWeb/VN/Tagmod.pm index 7a35846d..767f9ab5 100644 --- a/lib/VNWeb/VN/Tagmod.pm +++ b/lib/VNWeb/VN/Tagmod.pm @@ -72,7 +72,7 @@ elm_api Tagmod => $FORM_OUT, $FORM_IN, sub { TUWF::get qr{/$RE{vid}/tagmod}, sub { - my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \tuwf->capture('id')); + my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vnt WHERE id =', \tuwf->capture('id')); return tuwf->resNotFound if !$v->{id} || (!auth->permDbmod && $v->{entry_hidden}); return tuwf->resDenied if !can_tag; diff --git a/lib/VNWeb/VN/Votes.pm b/lib/VNWeb/VN/Votes.pm index a5bce3f7..b70ca342 100644 --- a/lib/VNWeb/VN/Votes.pm +++ b/lib/VNWeb/VN/Votes.pm @@ -31,7 +31,7 @@ sub listing_ { TUWF::get qr{/$RE{vid}/votes}, sub { my $id = tuwf->capture('id'); - my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \$id); + my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vnt WHERE id =', \$id); return tuwf->resNotFound if !$v->{id} || $v->{hidden}; my $opt = tuwf->validate(get => |