diff options
author | Yorhel <git@yorhel.nl> | 2022-11-18 11:14:18 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2022-11-18 11:14:18 +0100 |
commit | 7dbcb01fcc6fd1cf2743e82d2318eb7c276e2dca (patch) | |
tree | 4b7c27dacbc7dc164d195945e241e427184786bc | |
parent | ebbe7f0f0c363010943b24bcaea5944c1e720287 (diff) |
API2: Add extlinks field to releases
-rw-r--r-- | data/api-kana.md | 29 | ||||
-rw-r--r-- | lib/VNDB/ExtLinks.pm | 67 | ||||
-rw-r--r-- | lib/VNWeb/API.pm | 13 | ||||
-rw-r--r-- | lib/VNWeb/Producers/Page.pm | 6 | ||||
-rw-r--r-- | lib/VNWeb/Releases/Lib.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/Releases/List.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Releases/Page.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/Staff/Page.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 8 |
9 files changed, 106 insertions, 35 deletions
diff --git a/data/api-kana.md b/data/api-kana.md index 90a1b4d3..40b2c2a6 100644 --- a/data/api-kana.md +++ b/data/api-kana.md @@ -622,7 +622,8 @@ Name [F] Description The `extlink` filter can be used with three types of values: - Just a site name, e.g. `["extlink","=","steam"]` matches all releases that - have a steam ID. + have a steam ID. The site names here are equivalent to those returned by the + `extlinks.name` field. - A two-element array indicating the site name and the remote identifier, e.g. `["extlink","=",["steam",702050]]` to match the Saya no Uta release on Steam. The second element can be either an int or a string, depending on the site, @@ -738,7 +739,31 @@ engine notes : String, possibly null, may contain [formatting codes](https://vndb.org/d9#4). -*Missing: External links, gtin, catalog number, animation, voiced.* +extlinks +: Array, links to external websites. This list is equivalent to the links + displayed on the release pages on the site, so it may include redundant + entries (e.g. if a Steam ID is known, links to both Steam and SteamDB are + included) and links that are automatically fetched from external resources + (e.g. PlayAsia, for which a GTIN lookup is performed). + +extlinks.url +: String, URL. + +extlinks.label +: String, English human-readable label for this link. + +extlinks.name +: Internal identifier of the site, intended for applications that want to + localize the label or to parse/format/extract remote identifiers. Keep in + mind that the list of supported sites, their internal names and their ID + types are subject to change, but I'll try to keep things stable. + +extlinks.id +: Remote identifier for this link. Not all sites have a sensible identifier + as part of their URL format, in such cases this field is simply equivalent + to the URL. + +*Missing: gtin, catalog number, animation, voiced.* diff --git a/lib/VNDB/ExtLinks.pm b/lib/VNDB/ExtLinks.pm index cdedcdb1..8a6dc752 100644 --- a/lib/VNDB/ExtLinks.pm +++ b/lib/VNDB/ExtLinks.pm @@ -224,13 +224,14 @@ sub sql_extlinks { # following field to each object: # # extlinks => [ -# [ $title, $url, $price ], +# { name, label, id, url, url2, price }, # depending on which fields are $enabled # .. # ] # -# (It also adds a few other fields in some cases, but you can ignore those) +# Assumes the columns returned by sql_extlinks() are already available. sub enrich_extlinks { - my($type, @obj) = @_; + my($type, $enabled, @obj) = @_; + $enabled ||= { label => 1, url2 => 1, price => 1 }; @obj = map ref $_ eq 'ARRAY' ? @$_ : ($_), @obj; my $l = $LINKS{$type} || die "DB entry type $type has no links"; @@ -239,6 +240,7 @@ sub enrich_extlinks { my $w = @w_ids ? { map +($_->{id}, $_), $TUWF::OBJ->dbAlli('SELECT * FROM wikidata WHERE id IN', \@w_ids)->@* } : {}; # Fetch shop info for releases + my @cleanup; if($type eq 'r') { VNWeb::DB::enrich_merge(id => q{ SELECT r.id @@ -253,11 +255,21 @@ sub enrich_extlinks { LEFT JOIN shop_mg smg ON smg.id = r.l_mg AND smg.lastfetch IS NOT NULL AND smg.deadsince IS NULL WHERE r.id IN}, grep $_->{l_mg}||$_->{l_denpa}||$_->{l_jlist}||$_->{l_dlsite}, @obj - ); - VNWeb::DB::enrich(l_playasia => gtin => gtin => - "SELECT gtin, price, url FROM shop_playasia WHERE price <> '' AND gtin IN", - grep $_->{gtin}, @obj - ); + ) if $enabled->{price} || $enabled->{url2}; + + if(grep exists $_->{gtin}, @obj) { + VNWeb::DB::enrich(l_playasia => gtin => gtin => + "SELECT gtin, price, url FROM shop_playasia WHERE price <> '' AND gtin IN", + grep $_->{gtin}, @obj + ); + } else { + VNWeb::DB::enrich(l_playasia => id => id => + "SELECT r.id, s.gtin, s.price, s.url FROM releases r JOIN shop_playasia s ON s.gtin = r.gtin WHERE s.price <> '' AND r.id IN", + @obj + ); + } + + @cleanup = qw{l_mg_price l_mg_r18 l_denpa_price l_jlist_price l_jlist_jbox l_dlsite_price l_dlsite_shop l_playasia}; } for my $obj (@obj) { @@ -265,12 +277,36 @@ sub enrich_extlinks { my sub w { return if !$obj->{l_wikidata}; my($v, $fmt, $label) = ($w->{$obj->{l_wikidata}}{$_[0]}, @{$WIKIDATA{$_[0]}}{'fmt', 'label'}); - push @links, map [ $label, ref $fmt ? $fmt->($_) : sprintf($fmt, $_), undef ], ref $v ? @$v : $v ? $v : () + push @links, map +{ + $enabled->{name} ? (name => $_[0]) : (), + $enabled->{label} ? (label => $label) : (), + $enabled->{id} ? (id => $_) : (), + $enabled->{url} ? (url => ref $fmt ? $fmt->($_) : sprintf $fmt, $_) : (), + $enabled->{url2} ? (url2 => ref $fmt ? $fmt->($_) : sprintf $fmt, $_) : (), + }, ref $v ? @$v : $v ? $v : () } my sub l { my($f, $price) = @_; my($v, $fmt, $fmt2, $label) = ($obj->{$f}, $l->{$f} ? @{$l->{$f}}{'fmt', 'fmt2', 'label'} : ()); - push @links, map [ $label, sprintf((ref $fmt2 ? $fmt2->($obj) : $fmt2) || $fmt, $_), $price ], ref $v ? @$v : $v ? $v : () + push @links, map +{ + $enabled->{name} ? (name => $_[0] =~ s/^l_//r) : (), + $enabled->{label} ? (label => $label) : (), + $enabled->{id} ? (id => $_) : (), + $enabled->{url} ? (url => sprintf($fmt, $_)) : (), + $enabled->{url2} ? (url2 => sprintf((ref $fmt2 ? $fmt2->($obj) : $fmt2) || $fmt, $_)) : (), + $enabled->{price} && length $price ? (price => $price) : (), + }, ref $v ? @$v : $v ? $v : () + } + my sub c { + my($name, $label, $fmt, $id, $price) = @_; + push @links, { + $enabled->{name} ? (name => $name) : (), + $enabled->{label} ? (label => $label) : (), + $enabled->{id} ? (id => $id) : (), + $enabled->{url} ? (url => sprintf($fmt, $id)) : (), + $enabled->{url2} ? (url2 => sprintf($fmt, $id)) : (), + $enabled->{price} && length $price ? (price => $price) : (), + } } l 'l_site'; @@ -290,14 +326,14 @@ sub enrich_extlinks { w 'igdb_game'; w 'pcgamingwiki'; l 'l_renai'; - push @links, [ 'VNStat', sprintf('https://vnstat.net/novel/%d', $obj->{id} =~ s/^.//r), undef ] if $obj->{c_votecount}>=20; + c 'vnstat', 'VNStat', 'https://vnstat.net/novel/%d', $obj->{id} =~ s/^.//r if $obj->{c_votecount}>=20; } # Release links if($type eq 'r') { l 'l_egs'; l 'l_steam'; - push @links, [ 'SteamDB', sprintf('https://steamdb.info/app/%d/info', $obj->{l_steam}), undef ] if $obj->{l_steam}; + c 'steamdb', 'SteamDB', 'https://steamdb.info/app/%d/info', $obj->{l_steam} if $obj->{l_steam}; l 'l_dlsite', $obj->{l_dlsite_price}; l 'l_gog'; l 'l_itch'; @@ -329,7 +365,7 @@ sub enrich_extlinks { l 'l_nintendo'; l 'l_nintendo_jp'; l 'l_nintendo_hk'; - push @links, map [ 'PlayAsia', $_->{url}, $_->{price} ], @{$obj->{l_playasia}} if $obj->{l_playasia}; + c 'playasia', 'PlayAsia', '%s', $_->{url}, $_->{price} for $obj->{l_playasia}->@*; } # Staff links @@ -351,10 +387,11 @@ sub enrich_extlinks { w 'gamefaqs_company'; w 'doujinshi_author'; w 'soundcloud'; - push @links, [ 'VNStat', sprintf('https://vnstat.net/developer/%d', $obj->{id} =~ s/^.//r), undef ]; + c 'vnstat', 'VNStat', 'https://vnstat.net/developer/%d', $obj->{id} =~ s/^.//r; } - $obj->{extlinks} = \@links + $obj->{extlinks} = \@links; + delete @{$obj}{ @cleanup }; } } diff --git a/lib/VNWeb/API.pm b/lib/VNWeb/API.pm index e33d45d1..fc35edf1 100644 --- a/lib/VNWeb/API.pm +++ b/lib/VNWeb/API.pm @@ -6,6 +6,7 @@ use TUWF; use Time::HiRes 'time', 'alarm'; use VNDB::Config; use VNDB::Func; +use VNDB::ExtLinks; use VNWeb::DB; use VNWeb::Validation; use VNWeb::AdvSearch; @@ -245,6 +246,7 @@ sub api_query { sub parse_fields { my @tokens = split /\s*([,.{}])\s*/, $_[1]; + state %extlinks = map +($_,{}), qw{name label id url}; $_[1] = {}; return (sub { my($lvl, $f, $out) = @_; @@ -269,7 +271,7 @@ sub parse_fields { $t = shift(@tokens) // return { msg => "Expected name after '.'" }; } my $d = $nf->{$t} // return { msg => "Field '$t' not found", name => $t }; - $nf = $d->{fields}; + $nf = $d->{fields} || ($d->{extlinks} && \%extlinks); $of->{$t} ||= {}; $of = $of->{$t}; } @@ -309,6 +311,7 @@ sub prepare_fields { $join{$d->{join}} = 1 if $d->{join}; push @select, $d->{select} if $d->{select}; push @select, $d->{nullif} if $d->{nullif}; + push @select, sql_extlinks $d->{extlinks}, $d->{extlinks}.'.' if $d->{extlinks}; __SUB__->($d->{fields}, $_[1]{$f}) if $d->{fields} && !$d->{enrich}; } })->($fields, $enabled); @@ -331,8 +334,13 @@ sub proc_results { for my $f (keys %$enabled) { my $d = $fields->{$f}; + # extlinks + if($d->{extlinks}) { + enrich_extlinks $d->{extlinks}, $enabled->{$f}, $results; + delete @{$_}{ keys $VNDB::ExtLinks::LINKS{$d->{extlinks}}->%* } for @$results; + # nested 1-to-many objects - if($d->{enrich}) { + } if($d->{enrich}) { my($select, $join) = prepare_fields($d->{fields}, $d->{joins}, $enabled->{$f}); # DB::enrich() logic has been duplicated here to allow for # efficient handling of nested proc_results() and `atmostone`. @@ -554,6 +562,7 @@ api_query '/release', , proc => sub { $_[0] = $_[0][1] == 0 ? undef : 'non-standard' if $_[0][0] == 0 } }, engine => { select => 'r.engine', @NSTR }, notes => { select => 'r.notes', @NSTR }, + extlinks => { extlinks => 'r' }, }, sort => [ id => 'r.id', diff --git a/lib/VNWeb/Producers/Page.pm b/lib/VNWeb/Producers/Page.pm index a0e69216..f91b8893 100644 --- a/lib/VNWeb/Producers/Page.pm +++ b/lib/VNWeb/Producers/Page.pm @@ -7,7 +7,7 @@ use VNWeb::ULists::Lib; sub enrich_item { my($p) = @_; - enrich_extlinks p => $p; + enrich_extlinks p => 0, $p; enrich_merge pid => 'SELECT id AS pid, name, original FROM producers WHERE id IN', $p->{relations}; $p->{relations} = [ sort { $a->{name} cmp $b->{name} || idcmp($a->{pid}, $b->{pid}) } $p->{relations}->@* ]; } @@ -45,7 +45,7 @@ sub info_ { txt_ $p->{alias} =~ s/\n/, /gr; } br_ if $p->{extlinks}->@*; - join_ ' - ', sub { a_ href => $_->[1], $_->[0] }, $p->{extlinks}->@*; + join_ ' - ', sub { a_ href => $_->{url2}, $_->{label} }, $p->{extlinks}->@*; }; p_ class => 'center', sub { @@ -75,7 +75,7 @@ sub rel_ { ORDER BY r.released '); $_->{rtype} = 1 for @$r; # prevent enrich_release() from fetching rtypes - enrich_extlinks r => $r; + enrich_extlinks r => 0, $r; enrich_release $r; enrich vn => id => rid => sub { sql ' SELECT rv.id as rid, rv.rtype, v.id, v.title, v.alttitle diff --git a/lib/VNWeb/Releases/Lib.pm b/lib/VNWeb/Releases/Lib.pm index 0b899bb5..ca8a937f 100644 --- a/lib/VNWeb/Releases/Lib.pm +++ b/lib/VNWeb/Releases/Lib.pm @@ -60,7 +60,7 @@ sub release_extlinks_ { return if !$r->{extlinks}->@*; if($r->{extlinks}->@* == 1 && $r->{website}) { - a_ href => $r->{extlinks}[0][1], sub { + a_ href => $r->{extlinks}[0]{url2}, sub { abbr_ class => 'icons external', title => 'Official website', ''; }; return @@ -76,9 +76,9 @@ sub release_extlinks_ { div_ sub { ul_ sub { li_ sub { - a_ href => $_->[1], sub { - span_ $_->[2] if length $_->[2]; - txt_ $_->[0]; + a_ href => $_->{url2}, sub { + span_ $_->{price} if length $_->{price}; + txt_ $_->{label}; } } for $r->{extlinks}->@*; } diff --git a/lib/VNWeb/Releases/List.pm b/lib/VNWeb/Releases/List.pm index 8244ab16..8b5c31ea 100644 --- a/lib/VNWeb/Releases/List.pm +++ b/lib/VNWeb/Releases/List.pm @@ -72,7 +72,7 @@ TUWF::get qr{/r}, sub { ) : []; } || (($count, $list) = (undef, [])); - enrich_extlinks r => $list; + enrich_extlinks r => 0, $list; enrich_release $list; $time = time - $time; diff --git a/lib/VNWeb/Releases/Page.pm b/lib/VNWeb/Releases/Page.pm index 71612c8e..846c2bdb 100644 --- a/lib/VNWeb/Releases/Page.pm +++ b/lib/VNWeb/Releases/Page.pm @@ -249,7 +249,7 @@ sub _infotable_ { tr_ sub { td_ 'Links'; td_ sub { - join_ ', ', sub { a_ href => $_->[1], $_->[0] }, $r->{extlinks}->@*; + join_ ', ', sub { a_ href => $_->{url2}, $_->{label} }, $r->{extlinks}->@*; } } if $r->{extlinks}->@*; @@ -272,7 +272,7 @@ TUWF::get qr{/$RE{rrev}} => sub { @{$r}{'title', 'alttitle'} = langpref_titles $r->{olang}, $r->{titles}; enrich_item $r; - enrich_extlinks r => $r; + enrich_extlinks r => 0, $r; framework_ title => $r->{title}, index => !tuwf->capture('rev'), dbobj => $r, hiddenmsg => 1, og => { diff --git a/lib/VNWeb/Staff/Page.pm b/lib/VNWeb/Staff/Page.pm index 223d6910..653c5237 100644 --- a/lib/VNWeb/Staff/Page.pm +++ b/lib/VNWeb/Staff/Page.pm @@ -62,7 +62,7 @@ sub _infotable_ { tr_ sub { td_ class => 'key', 'Links'; td_ sub { - join_ \&br_, sub { a_ href => $_->[1], $_->[0] }, $s->{extlinks}->@*; + join_ \&br_, sub { a_ href => $_->{url2}, $_->{label} }, $s->{extlinks}->@*; }; } if $s->{extlinks}->@*; }; @@ -181,7 +181,7 @@ TUWF::get qr{/$RE{srev}} => sub { return tuwf->resNotFound if !$s; enrich_item $s; - enrich_extlinks s => $s; + enrich_extlinks s => 0, $s; my($main) = grep $_->{aid} == $s->{aid}, $s->{alias}->@*; framework_ title => $main->{name}, index => !tuwf->capture('rev'), dbobj => $s, hiddenmsg => 1, diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm index 8bf7acbc..7b6f3a91 100644 --- a/lib/VNWeb/VN/Page.pm +++ b/lib/VNWeb/VN/Page.pm @@ -16,7 +16,7 @@ sub enrich_vn { enrich_merge id => sql('SELECT id, c_votecount, c_length, c_lengthnum 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_extlinks v => 0, $v; enrich_image_obj image => $v; enrich_image_obj scr => $v->{screenshots}; @@ -32,7 +32,7 @@ sub enrich_vn { JOIN releases_vn rv ON rv.id = r.id WHERE NOT r.hidden AND rv.vid =', \$v->{id} ); - enrich_extlinks r => $v->{releases}; + enrich_extlinks r => 0, $v->{releases}; $v->{reviews} = tuwf->dbRowi(' SELECT COUNT(*) FILTER(WHERE isfull) AS full, COUNT(*) FILTER(WHERE NOT isfull) AS mini, COUNT(*) AS total @@ -318,7 +318,7 @@ sub infobox_affiliates_ { $rel->{rtype} eq 'partial' ? 2 : $rel->{num_vns} > 1 ? 0 : 1; - $links{$_->[1]} = [ @$_, min $type, $links{$_->[1]}[3]||9 ] for grep $_->[2], $rel->{extlinks}->@*; + $links{$_->{url2}} = [ @{$_}{qw/label url2 price/}, min $type, $links{$_->{url2}}[3]||9 ] for grep $_->{price}, $rel->{extlinks}->@*; } return if !keys %links; @@ -479,7 +479,7 @@ sub infobox_ { tr_ sub { td_ 'Links'; - td_ sub { join_ ', ', sub { a_ href => $_->[1], $_->[0] }, $v->{extlinks}->@* }; + td_ sub { join_ ', ', sub { a_ href => $_->{url2}, $_->{label} }, $v->{extlinks}->@* }; } if $v->{extlinks}->@*; infobox_affiliates_ $v; |