diff options
-rw-r--r-- | data/global.pl | 1 | ||||
-rw-r--r-- | lib/Multi/Wikidata.pm | 126 | ||||
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/DB/Staff.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 9 | ||||
-rw-r--r-- | lib/VNDB/Func.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/Handler/Producers.pm | 27 | ||||
-rw-r--r-- | lib/VNDB/Handler/Staff.pm | 12 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNEdit.pm | 17 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 17 | ||||
-rw-r--r-- | lib/VNDB/Util/FormHTML.pm | 5 | ||||
-rw-r--r-- | lib/VNDB/Util/Misc.pm | 56 | ||||
-rw-r--r-- | lib/VNDB/Util/ValidateTemplates.pm | 7 | ||||
-rw-r--r-- | lib/VNDBSchema.pm | 2 | ||||
-rw-r--r-- | static/f/wikidata.png | bin | 0 -> 35543 bytes | |||
-rwxr-xr-x | util/dbdump.pl | 4 | ||||
-rwxr-xr-x | util/devdump.pl | 3 | ||||
-rw-r--r-- | util/dump/LICENSE-CC0.txt | 121 | ||||
-rw-r--r-- | util/dump/README.txt | 4 | ||||
-rw-r--r-- | util/sql/func.sql | 11 | ||||
-rw-r--r-- | util/sql/perms.sql | 2 | ||||
-rw-r--r-- | util/sql/schema.sql | 45 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 6 | ||||
-rw-r--r-- | util/sql/triggers.sql | 13 | ||||
-rw-r--r-- | util/updates/update_20190809.sql | 60 |
26 files changed, 514 insertions, 58 deletions
diff --git a/data/global.pl b/data/global.pl index 50234141..5f348825 100644 --- a/data/global.pl +++ b/data/global.pl @@ -286,6 +286,7 @@ our %M = ( #Anime => {}, # disabled by default, requires AniDB username/pass Maintenance => {}, #IRC => {}, # disabled by default, no need to run an IRC bot when debugging + #Wikidata => {}, # disabled by default, no need to bother the Wikidata API when debugging }, ); diff --git a/lib/Multi/Wikidata.pm b/lib/Multi/Wikidata.pm new file mode 100644 index 00000000..fea9dbb1 --- /dev/null +++ b/lib/Multi/Wikidata.pm @@ -0,0 +1,126 @@ + +# +# Multi::Wikidata - Fetches information from wikidata +# + +package Multi::Wikidata; + +use strict; +use warnings; +use Multi::Core; +use JSON::XS 'decode_json'; +use AnyEvent::HTTP; + + +my %C = ( + check_timeout => 30, # Check & fetch for entries to update every 30 seconds + fetch_number => 50, # Number of entries to fetch in a single API call + fetch_interval => 24*3600, # Minimum delay between updates of a single entry + api_endpoint => 'https://www.wikidata.org/w/api.php', +); + + +sub run { + shift; + $C{ua} = "VNDB.org Crawler (Multi v$VNDB::S{version}; contact\@vndb.org)"; + %C = (%C, @_); + + push_watcher schedule 0, $C{check_timeout}, \&fetch; +} + + +sub fetch { + pg_cmd q{ + SELECT id + FROM wikidata + WHERE id IN( + SELECT l_wikidata FROM producers WHERE l_wp IS NOT NULL AND NOT hidden + UNION SELECT l_wikidata FROM staff WHERE l_wp IS NOT NULL AND NOT hidden + UNION SELECT l_wikidata FROM vn WHERE l_wp IS NOT NULL AND NOT hidden) + AND (lastfetch IS NULL OR lastfetch < now()-($1 * '1 second'::interval)) + ORDER BY lastfetch NULLS FIRST + LIMIT $2 + }, [ $C{fetch_interval}, $C{fetch_number} ], sub { + my($res) = @_; + return if pg_expect $res, 1 or !$res->nRows; + my @ids = map $res->value($_,0), 0..($res->nRows-1); + + my $ids_q = join '|', map "Q$_", @ids; + my $ts = AE::now; + http_get "$C{api_endpoint}?action=wbgetentities&format=json&props=sitelinks|claims&sitefilter=enwiki|jawiki&ids=$ids_q", + 'User-Agent' => $C{ua}, + timeout => 60, + sub { process(\@ids, $ids_q, $ts, @_) } + } +} + + +my %props = qw/ + P856 website + P3180 vndb + P1933 mobygames + P4773 mobygames_company + P4769 gamefaqs_game + P6182 gamefaqs_company + P5646 anidb_anime + P5649 anidb_person + P1985 ann_anime + P1984 ann_manga + P434 musicbrainz_artist + P2002 twitter + P5659 vgmdb_product + P3435 vgmdb_artist + P1953 discogs_artist + P7013 acdb_char + P7017 acdb_source + P6717 indiedb_game + P2816 howlongtobeat +/; + + +sub process { + my($ids, $ids_q, $ts, $body, $hdr) = @_; + + # Just update lastfetch even if we have some kind of error further on. This + # makes sure we at least don't get into an error loop on the same entry. + my $n = 1; + my $ids_where = join ',', map sprintf('$%d', $n++), @$ids; + pg_cmd "UPDATE wikidata SET lastfetch = NOW() WHERE id IN($ids_where)", $ids; + + return AE::log warn => "$ids_q Http error: $hdr->{Status} $hdr->{Reason}" + if $hdr->{Status} !~ /^2/; + + my $data = eval { decode_json $body }; + return AE::log warn => "$ids_q Error decoding JSON: $@" if !$data; + + save($_, $ts, $data->{entities}{"Q$_"}) for @$ids; +} + + +sub save { + my($id, $ts, $data) = @_; + + my @set = ( 'enwiki = $2', 'jawiki = $3'); + my @val = ($id, $data->{sitelinks}{enwiki}{title}, $data->{sitelinks}{jawiki}{title}); + + for my $p (sort keys %props) { + my $v = $data->{claims}{$p}; + AE::log warn => "Q$id has multiple properties for '$p', storing the first" if $v && @$v > 1; + + $v = $v->[0]{mainsnak}{datavalue}{value}; + if(ref $v) { + AE::log warn => "Q$id has a non-scalar value for '$p'"; + $v = undef; + } + + push @val, $v; + push @set, sprintf '%s = $%d', $props{$p}, scalar @val; + } + + my $set = join ', ', @set; + + pg_cmd "UPDATE wikidata SET $set WHERE id = \$1", \@val; + AE::log info => sprintf "Q%d in %.1fs with %d vals", $id, AE::now()-$ts, -1+scalar grep defined($_), @val; +} + +1; diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 61bb71a2..4a669cc3 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -6,7 +6,7 @@ use warnings; use Exporter 'import'; our @EXPORT = qw| - dbStats dbItemEdit dbRevisionGet dbRandomQuote + dbStats dbItemEdit dbRevisionGet dbRandomQuote dbWikidata |; @@ -121,6 +121,10 @@ sub dbRandomQuote { } +# Returns a row from wikidata +sub dbWikidata { + return $_[0]->dbRow('SELECT * FROM wikidata WHERE id = ?', $_[1]); +} 1; diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index a6a301e5..548f70c5 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -37,7 +37,7 @@ sub dbProducerGet { my $join = $o{what} =~ /relgraph/ ? 'JOIN relgraphs pg ON pg.id = p.rgraph' : ''; my $select = 'p.id, p.type, p.name, p.original, p.lang, p.rgraph'; - $select .= ', p.desc, p.alias, p.website, p.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/; + $select .= ', p.desc, p.alias, p.website, p.l_wp, p.l_wikidata, p.hidden, p.locked' if $o{what} =~ /extended/; $select .= ', pg.svg' if $o{what} =~ /relgraph/; my($order, @order) = ('p.name'); @@ -70,7 +70,7 @@ sub dbProducerGetRev { my $select = 'c.itemid AS id, p.type, p.name, p.original, p.lang, po.rgraph'; $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; - $select .= ', p.desc, p.alias, p.website, p.l_wp, po.hidden, po.locked' if $o{what} =~ /extended/; + $select .= ', p.desc, p.alias, p.website, p.l_wp, p.l_wikidata, po.hidden, po.locked' if $o{what} =~ /extended/; my $r = $self->dbAll(q| SELECT !s @@ -115,7 +115,7 @@ sub dbProducerRevisionInsert { my($self, $o) = @_; my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), - qw|name original website l_wp type lang desc alias|; + qw|name original website l_wp l_wikidata type lang desc alias|; $self->dbExec('UPDATE edit_producers !H', \%set) if keys %set; if($o->{relations}) { diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm index bf2ae325..618a3b88 100644 --- a/lib/VNDB/DB/Staff.pm +++ b/lib/VNDB/DB/Staff.pm @@ -57,7 +57,7 @@ sub dbStaffGet { ); my $select = 's.id, sa.aid, sa.name, sa.original, s.gender, s.lang'; - $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/; + $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.l_wikidata, s.hidden, s.locked' if $o{what} =~ /extended/; my($order, @order) = ('sa.name'); if($o{sort} && $o{sort} eq 'search') { @@ -87,7 +87,7 @@ sub dbStaffGetRev { my $select = 'c.itemid AS id, sa.aid, sa.name, sa.original, s.gender, s.lang'; $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; - $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, so.hidden, so.locked' if $o{what} =~ /extended/; + $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.l_wikidata, so.hidden, so.locked' if $o{what} =~ /extended/; my $r = $self->dbAll(q| SELECT !s @@ -171,7 +171,7 @@ sub dbStaffRevisionInsert { } my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), - qw|aid gender lang desc l_wp l_site l_twitter l_anidb|; + qw|aid gender lang desc l_wp l_site l_twitter l_anidb l_wikidata|; $self->dbExec('UPDATE edit_staff !H', \%staff) if %staff; for my $a (@{$o->{aliases}}) { if($a->{aid}) { diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 75cf0a0d..dd33fef8 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -96,8 +96,7 @@ sub dbVNGet { } my @join = ( - $o{what} =~ /relgraph/ ? - 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), + $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), $uid && $o{what} =~ /wishlist/ ? 'LEFT JOIN wlists wl ON wl.vid = v.id AND wl.uid = ' . $uid : (), $uid && $o{what} =~ /vnlist/ ? ("LEFT JOIN ( @@ -114,7 +113,7 @@ sub dbVNGet { my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages and c_platforms qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] v.title v.original v.rgraph|, $o{what} =~ /extended/ ? ( - qw|v.alias v.image v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai| ) : (), + qw|v.alias v.image v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai v.l_wikidata| ) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (), $o{what} =~ /ranking/ ? ( @@ -164,7 +163,7 @@ sub dbVNGetRev { my $select = 'c.itemid AS id, vo.c_released, vo.c_languages::text[], vo.c_platforms::text[], v.title, v.original, vo.rgraph'; $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock'; $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev'; - $select .= ', v.alias, v.image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, vo.hidden, vo.locked' if $o{what} =~ /extended/; + $select .= ', v.alias, v.image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, v.l_wikidata, vo.hidden, vo.locked' if $o{what} =~ /extended/; $select .= ', vo.c_popularity, vo.c_rating, vo.c_votecount' if $o{what} =~ /rating/; $select .= ', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(vo.c_popularity, 0.0)) AS p_ranking' .', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(vo.c_rating, 0.0)) AS r_ranking' if $o{what} =~ /ranking/; @@ -273,7 +272,7 @@ sub dbVNRevisionInsert { $o->{img_nsfw} = $o->{img_nsfw}?1:0 if exists $o->{img_nsfw}; my %set = map exists($o->{$_}) ? (qq|"$_" = ?| => $o->{$_}) : (), - qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai|; + qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_wikidata|; $self->dbExec('UPDATE edit_vn !H', \%set) if keys %set; if($o->{screenshots}) { diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index 9fe9c56c..fc52ef08 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -328,8 +328,10 @@ sub form_compare { my($old, $new) = @_; for my $k (keys %$old) { my($o, $n) = ($old->{$k}, $new->{$k}); - return 1 if !defined $n || ref $o ne ref $n; - if(!ref $o) { + return 1 if defined $n ne defined $o || ref $o ne ref $n; + if(!defined $o) { + # must be equivalent + } elsif(!ref $o) { return 1 if $o ne $n; } else { # 'json' template return 1 if @$o != @$n; diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index f8bddf9b..782499fd 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -71,6 +71,7 @@ sub page { [ l_wp => 'Wikipedia link',htmlize => sub { $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : '[empty]' }], + [ l_wikidata=> 'Wikidata ID', htmlize => sub { $_[0] ? sprintf '<a href="https://www.wikidata.org/wiki/Q%d">Q%1$d</a>', $_[0] : '[empty]' } ], [ desc => 'Description', diff => qr/[ ,\n\.]/ ], [ relations => 'Relations', join => '<br />', split => sub { my @r = map sprintf('%s: <a href="/p%d" title="%s">%s</a>', @@ -93,14 +94,11 @@ sub page { txt "a.k.a. $alias"; } - my @links = ( - $p->{website} ? [ 'Homepage', $p->{website} ] : (), - $p->{l_wp} ? [ 'Wikipedia', "http://en.wikipedia.org/wiki/$p->{l_wp}" ] : (), - ); - br if @links; - for(@links) { + my $links = $self->entryLinks(p => $p); + br if @$links; + for(@$links) { a href => $_->[1], $_->[0]; - txt ' - ' if $_ ne $links[$#links]; + txt ' - ' if $_ ne $links->[$#$links]; } end 'p'; @@ -280,8 +278,7 @@ sub edit { || $pid && (($p->{locked} || $p->{hidden}) && !$self->authCan('dbmod')); my %b4 = !$pid ? () : ( - (map { $_ => $p->{$_} } qw|type name original lang website desc alias ihid ilock|), - l_wp => $p->{l_wp} || '', + (map { $_ => $p->{$_} } qw|type name original lang website l_wikidata desc alias ihid ilock|), prodrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{name}, sort { $a->{id} <=> $b->{id} } @{$p->{relations}}), ); my $frm; @@ -295,7 +292,7 @@ sub edit { { post => 'alias', required => 0, maxlength => 500, default => '' }, { post => 'lang', required => !$nosubmit, enum => [ keys %{$self->{languages}} ] }, { post => 'website', required => 0, maxlength => 250, default => '', template => 'weburl' }, - { post => 'l_wp', required => 0, maxlength => 150, default => '' }, + { post => 'l_wikidata', required => 0, template => 'wikidata' }, { post => 'desc', required => 0, maxlength => 5000, default => '' }, { post => 'prodrelations', required => 0, maxlength => 5000, default => '' }, { post => 'editsum', required => !$nosubmit, template => 'editsum' }, @@ -314,11 +311,10 @@ sub edit { $frm->{prodrelations} = join '|||', map $_->[0].','.$_->[1].','.$_->[2], sort { $a->[1] <=> $b->[1]} @{$relations}; return $self->resRedirect("/p$pid", 'post') - if $pid && !grep $frm->{$_} ne $b4{$_}, keys %b4; + if $pid && !grep +(($frm->{$_}//'') ne ($b4{$_}//'')), keys %b4; $frm->{relations} = $relations; - $frm->{l_wp} = undef if !$frm->{l_wp}; - my $nrev = $self->dbItemEdit(p => $pid||undef, $pid ? $p->{rev} : undef, %$frm); + my $nrev = $self->dbItemEdit(p => $pid||undef, $pid ? $p->{rev} : undef, %$frm, l_wp => $p->{l_wp}||undef); # update reverse relations if(!$pid && $#$relations >= 0 || $pid && $frm->{prodrelations} ne $b4{prodrelations}) { @@ -351,7 +347,10 @@ sub edit { [ select => name => 'Primary language', short => 'lang', options => [ map [ $_, "$_ ($self->{languages}{$_})" ], keys %{$self->{languages}} ] ], [ input => name => 'Website', short => 'website' ], - [ input => name => 'Wikipedia link', short => 'l_wp', pre => 'http://en.wikipedia.org/wiki/' ], + [ input => short => 'l_wikidata',name => 'Wikidata ID', + value => $frm->{l_wikidata} ? "Q$frm->{l_wikidata}" : '', + post => qq{ (<a href="$self->{url_static}/f/wikidata.png">How to find this</a>)} + ], [ text => name => 'Description<br /><b class="standout">English please!</b>', short => 'desc', rows => 6 ], ], 'pedit_rel' => [ 'Relations', [ hidden => short => 'prodrelations' ], diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm index 25c30073..5e431db5 100644 --- a/lib/VNDB/Handler/Staff.pm +++ b/lib/VNDB/Handler/Staff.pm @@ -47,6 +47,7 @@ sub page { [ l_wp => 'Wikipedia link', htmlize => sub { $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : '[empty]' }], + [ l_wikidata=> 'Wikidata ID', htmlize => sub { $_[0] ? sprintf '<a href="https://www.wikidata.org/wiki/Q%d">Q%1$d</a>', $_[0] : '[empty]' } ], [ l_twitter => 'Twitter account', diff => 1 ], [ l_anidb => 'AniDB creator ID', serialize => sub { $_[0] // '' } ], [ desc => 'Description', diff => qr/[ ,\n\.]/ ], @@ -193,7 +194,7 @@ sub edit { || $sid && (($s->{locked} || $s->{hidden}) && !$self->authCan('dbmod')); my %b4 = !$sid ? () : ( - (map { $_ => $s->{$_} } qw|name original gender lang desc l_wp l_site l_twitter l_anidb ihid ilock|), + (map { $_ => $s->{$_} } qw|name original gender lang desc l_site l_wikidata l_twitter l_anidb ihid ilock|), primary => $s->{aid}, aliases => [ map +{ aid => $_->{aid}, name => $_->{name}, orig => $_->{original} }, @@ -211,8 +212,8 @@ sub edit { { post => 'desc', required => 0, maxlength => 5000, default => '' }, { post => 'gender', required => 0, default => 'unknown', enum => [qw|unknown m f|] }, { post => 'lang', enum => [ keys %{$self->{languages}} ] }, - { post => 'l_wp', required => 0, maxlength => 150, default => '' }, { post => 'l_site', required => 0, template => 'weburl', maxlength => 250, default => '' }, + { post => 'l_wikidata', required => 0, template => 'wikidata' }, { post => 'l_twitter', required => 0, maxlength => 16, default => '', regex => [ qr/^\S+$/, 'Invalid twitter username' ] }, { post => 'l_anidb', required => 0, template => 'id', default => undef }, { post => 'aliases', template => 'json', json_sort => ['name','orig'], json_fields => [ @@ -245,7 +246,7 @@ sub edit { $frm->{desc} = $self->bbSubstLinks($frm->{desc}); return $self->resRedirect("/s$sid", 'post') if $sid && !form_compare(\%b4, $frm); - my $nrev = $self->dbItemEdit(s => $sid ? ($s->{id}, $s->{rev}) : (undef, undef), %$frm); + my $nrev = $self->dbItemEdit(s => $sid ? ($s->{id}, $s->{rev}) : (undef, undef), %$frm, l_wp => $s->{l_wp}||''); return $self->resRedirect("/s$nrev->{itemid}.$nrev->{rev}", 'post'); } } @@ -285,7 +286,10 @@ sub edit { [ select => name => 'Primary language', short => 'lang', options => [ map [ $_, "$_ ($self->{languages}{$_})" ], keys %{$self->{languages}} ] ], [ input => name => 'Official page', short => 'l_site' ], - [ input => name => 'Wikipedia link', short => 'l_wp', pre => 'http://en.wikipedia.org/wiki/' ], + [ input => short => 'l_wikidata',name => 'Wikidata ID', + value => $frm->{l_wikidata} ? "Q$frm->{l_wikidata}" : '', + post => qq{ (<a href="$self->{url_static}/f/wikidata.png">How to find this</a>)} + ], [ input => name => 'Twitter username', short => 'l_twitter' ], [ input => name => 'AniDB creator ID', short => 'l_anidb' ], [ static => content => '<br />' ], diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index cd0d9550..c54b18b1 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -94,7 +94,7 @@ sub edit { my $chars = $v ? $self->dbCharGet(vid => $v->{id}, results => 500) : []; my %b4 = !$vid ? () : ( - (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai image img_nsfw ihid ilock|), + (map { $_ => $v->{$_} } qw|title original desc alias length l_encubed l_renai l_wikidata image img_nsfw ihid ilock|), credits => [ map { my $c = $_; +{ map { $_ => $c->{$_} } qw|aid role note| } } sort { $a->{aid} <=> $b->{aid} || $a->{role} cmp $b->{role} } @{$v->{credits}} @@ -120,9 +120,9 @@ sub edit { { post => 'alias', required => 0, maxlength => 500, default => '' }, { post => 'desc', required => 0, default => '', maxlength => 10240 }, { post => 'length', required => 0, default => 0, enum => [ 0..$#{$self->{vn_lengths}} ] }, - { post => 'l_wp', required => 0, default => '', maxlength => 150 }, { post => 'l_encubed', required => 0, default => '', maxlength => 100 }, { post => 'l_renai', required => 0, default => '', maxlength => 100 }, + { post => 'l_wikidata', required => 0, template => 'wikidata' }, { post => 'anime', required => 0, default => '' }, { post => 'image', required => 0, default => 0, template => 'id' }, { post => 'img_nsfw', required => 0, default => 0 }, @@ -183,9 +183,10 @@ sub edit { # perform the edit/add my $nrev = $self->dbItemEdit(v => $vid ? ($v->{id}, $v->{rev}) : (undef, undef), - (map { $_ => $frm->{$_} } qw|title original image alias desc length l_wp l_encubed l_renai editsum img_nsfw ihid ilock credits seiyuu screenshots|), + (map { $_ => $frm->{$_} } qw|title original image alias desc length l_encubed l_renai l_wikidata editsum img_nsfw ihid ilock credits seiyuu screenshots|), anime => [ keys %$anime ], relations => $relations, + l_wp => $v->{l_wp}||'', ); # update reverse relations & relation graph @@ -270,9 +271,13 @@ sub _form { [ select => short => 'length', name => 'Length', width => 450, options => [ map [ $_ => fmtvnlen $_, 2 ], 0..$#{$self->{vn_lengths}} ] ], - [ input => short => 'l_wp', name => 'External links', pre => 'http://en.wikipedia.org/wiki/' ], - [ input => short => 'l_encubed', pre => 'http://novelnews.net/tag/', post => '/' ], - [ input => short => 'l_renai', pre => 'http://renai.us/game/', post => '.shtml' ], + [ input => short => 'l_wikidata',name => 'Wikidata ID', + pre => 'https://www.wikidata.org/wiki/', + value => $frm->{l_wikidata} ? "Q$frm->{l_wikidata}" : '', + post => qq{ (<a href="$self->{url_static}/f/wikidata.png">How to find this</a>)} + ], + [ input => short => 'l_encubed', name => 'Novelnews link', pre => 'http://novelnews.net/tag/', post => '/' ], + [ input => short => 'l_renai', name => 'Renai.us link', pre => 'http://renai.us/game/', post => '.shtml' ], [ input => short => 'anime', name => 'Anime' ], [ static => content => diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index fa34189d..442ea63b 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -417,18 +417,14 @@ sub page { td fmtvnlen $v->{length}, 1; end; } - my @links = ( - $v->{l_wp} ? [ 'Wikipedia', 'http://en.wikipedia.org/wiki/%s', $v->{l_wp} ] : (), - #$v->{l_encubed} ? [ 'Encubed', 'http://novelnews.net/tag/%s/', $v->{l_encubed} ] : (), - $v->{l_renai} ? [ 'Renai.us', 'http://renai.us/game/%s.shtml', $v->{l_renai} ] : (), - ); - if(@links) { + my $links = $self->entryLinks(v => $v); + if(@$links) { Tr; td 'Links'; td; - for(@links) { - a href => sprintf($_->[1], $_->[2]), $_->[0]; - txt ', ' if $_ ne $links[$#links]; + for(@$links) { + a href => $_->[1], $_->[0]; + txt ', ' if $_ ne $links->[$#$links]; } end; end; @@ -546,11 +542,12 @@ sub _revision { [ l_wp => 'Wikipedia link', htmlize => sub { $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : '[empty]' }], + [ l_wikidata => 'Wikidata ID', htmlize => sub { $_[0] ? sprintf '<a href="https://www.wikidata.org/wiki/Q%d">Q%1$d</a>', $_[0] : '[empty]' } ], [ l_encubed => 'Encubed tag', htmlize => sub { $_[0] ? sprintf '<a href="http://novelnews.net/tag/%s/">%1$s</a>', xml_escape $_[0] : '[empty]' }], [ l_renai => 'Renai.us link', htmlize => sub { - $_[0] ? sprintf '<a href="http://renai.us/game/%s.shtml">%1$s</a>', xml_escape $_[0] : '[empty]' + $_[0] ? sprintf '<a href="https://renai.us/game/%s">%1$s</a>', xml_escape $_[0] : '[empty]' }], [ credits => 'Credits', join => '<br />', split => sub { my @r = map sprintf('<a href="/s%d" title="%s">%s</a> [%s]%s', $_->{id}, diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm index e047dbd9..1a1313e8 100644 --- a/lib/VNDB/Util/FormHTML.pm +++ b/lib/VNDB/Util/FormHTML.pm @@ -53,6 +53,7 @@ sub htmlFormError { li 'Invalid JAN/UPC/EAN' if $rule eq 'gtin'; li "$field: Malformed data or invalid input" if $rule eq 'json'; li 'Invalid release date' if $rule eq 'rdate'; + li 'Invalid Wikidata ID' if $rule eq 'wikidata'; if($rule eq 'editsum') { li; lit 'Please read <a href="/d5#4">the guidelines</a> on how to use the edit summary.'; end; } @@ -70,7 +71,7 @@ sub htmlFormError { # Type Options # hidden short, (value) # json short, (value) # Same as hidden, but value is passed through json_encode() -# input short, name, (allow0, width, pre, post) +# input short, name, (value, allow0, width, pre, post) # passwd short, name # static content, (label, nolabel) # check name, short, (value) @@ -135,7 +136,7 @@ sub htmlFormPart { if(/input/) { lit $o{pre} if $o{pre}; input type => 'text', class => 'text', name => $o{short}, id => $o{short}, tabindex => 10, - value => $o{allow0} ? $frm->{$o{short}}//'' : $frm->{$o{short}}||'', $o{width} ? (style => "width: $o{width}px") : (); + value => $o{value} // ($o{allow0} ? $frm->{$o{short}}//'' : $frm->{$o{short}}||''), $o{width} ? (style => "width: $o{width}px") : (); lit $o{post} if $o{post}; } if(/passwd/) { diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm index d18d19ec..f0a1704a 100644 --- a/lib/VNDB/Util/Misc.pm +++ b/lib/VNDB/Util/Misc.pm @@ -8,7 +8,7 @@ use TUWF ':html'; use VNDB::Func; use VNDB::BBCode (); -our @EXPORT = qw|filFetchDB filCompat bbSubstLinks|; +our @EXPORT = qw|filFetchDB filCompat bbSubstLinks entryLinks|; our %filfields = ( @@ -160,5 +160,59 @@ sub bbSubstLinks { return $result; } + +# Returns an arrayref of links, each link being [$title, $url] +sub entryLinks { + my($self, $type, $obj) = @_; + my $w = $obj->{l_wikidata} ? $self->dbWikidata($obj->{l_wikidata}) : {}; + + # Not everything in the wikidata table is actually used, only those links that + # seem to be directly mappings (i.e. not displaying anime links on VN pages). + my @links = ( + $w->{enwiki} ? [ 'Wikipedia (en)', 'https://en.wikipedia.org/wiki/%s', $w->{enwiki} =~ s/ /_/rg ] : (), + $w->{jawiki} ? [ 'Wikipedia (ja)', 'https://ja.wikipedia.org/wiki/%s', $w->{jawiki} =~ s/ /_/rg ] : (), + + # VN links + $type eq 'v' ? ( + $w->{mobygames} ? [ 'MobyGames', 'https://www.mobygames.com/game/%s', $w->{mobygames} ] : (), + $w->{gamefaqs_game} ? [ 'GameFAQs', 'https://gamefaqs.gamespot.com/-/%s-', $w->{gamefaqs_game} ] : (), + $w->{vgmdb_product} ? [ 'VGMdb', 'https://vgmdb.net/product/%s', $w->{vgmdb_product} ] : (), + $w->{acdb_source} ? [ 'ACDB', 'https://www.animecharactersdatabase.com/source.php?id=%s', $w->{acdb_source} ] : (), + $w->{indiedb_game} ? [ 'IndieDB', 'https://www.indiedb.com/games/%s', $w->{indiedb_game} ] : (), + $w->{howlongtobeat} ? [ 'HowLongToBeat', 'http://howlongtobeat.com/game.php?id=%s', $w->{howlongtobeat} ] : (), + $obj->{l_renai} ? [ 'Renai.us', 'https://renai.us/game/%s', $obj->{l_renai} ] : (), + $obj->{l_wikidata} ? [ 'Wikidata', 'https://www.wikidata.org/wiki/Q%d', $obj->{l_wikidata} ] : (), + #$obj->{l_wp} ? [ 'Wikipedia', 'http://en.wikipedia.org/wiki/%s', $obj->{l_wp} ] : (), # Superseded by l_wikidata + #$obj->{l_encubed} ? [ 'Encubed', 'http://novelnews.net/tag/%s/', $obj->{l_encubed} ] : (), # Seems dead + ) : (), + + # Staff links + $type eq 's' ? ( + $obj->{l_site} ? [ 'Official page', $obj->{l_site} ] : (), + $obj->{l_twitter} ? [ 'Twitter', 'https://twitter.com/%s', $obj->{l_twitter} ] : (), + $obj->{l_anidb} ? [ 'AniDB', 'https://anidb.net/cr%s', $obj->{l_anidb} ] : (), + + !$obj->{l_anidb} && $w->{anidb_person} ? [ 'AniDB', 'https://anidb,net/cr%s', $w->{anidb_person} ] : (), + !$obj->{l_twitter} && $w->{twitter} ? [ 'Twitter', 'https://twitter.com/%s', $w->{twitter} ] : (), + $w->{musicbrainz_artist} ? [ 'MusicBrainz', 'https://musicbrainz.org/artist/%s', $w->{musicbrainz_artist} ] : (), + $w->{vgmdb_artist} ? [ 'VGMdb', 'https://vgmdb.net/artist/%s', $w->{vgmdb_artist} ] : (), + $w->{discogs_artist} ? [ 'Discogs', 'https://www.discogs.com/artist/%s', $w->{discogs_artist} ] : (), + + #$s->{l_wp} ? [ 'Wikipedia', "https://en.wikipedia.org/wiki/$s->{l_wp}" ] : (), # Superseded by l_wikidata + ) : (), + + # Producer links + $type eq 'p' ? ( + $obj->{website} ? [ 'Homepage', $obj->{website} ] : (), + $w->{mobygames_company} ? [ 'MobyGames', 'https://www.mobygames.com/company/%s', $w->{mobygames_company} ] : (), + $w->{gamefaqs_company} ? [ 'GameFAQs', 'https://gamefaqs.gamespot.com/company/%s-', $w->{gamefaqs_company} ] : (), + + #$obj->{l_wp} ? [ 'Wikipedia', "https://en.wikipedia.org/wiki/$obj->{l_wp}" ] : (), # Superseded by l_wikidata + ) : (), + ); + + [ map [ $_->[0], $_->[2] ? sprintf $_->[1], $_->[2] : $_->[1] ], @links ]; +} + 1; diff --git a/lib/VNDB/Util/ValidateTemplates.pm b/lib/VNDB/Util/ValidateTemplates.pm index e7ff3102..540ff709 100644 --- a/lib/VNDB/Util/ValidateTemplates.pm +++ b/lib/VNDB/Util/ValidateTemplates.pm @@ -19,10 +19,17 @@ TUWF::set( editsum => { maxlength => 5000, minlength => 2 }, json => { func => \&json_validate, inherit => ['json_fields','json_maxitems','json_unique','json_sort'], default => [] }, rdate => { template => 'uint', min => 0, max => 99999999, func => \&rdate_validate, default => 0 }, + wikidata => { func => \&wikidata_id, default => undef }, } ); +sub wikidata_id { + $_[0] =~ s/^Q//; + $_[0] =~ /^([0-9]{1,9})$/ +} + + # Figure out if a field is treated as a number in kv_validate(). sub json_validate_is_num { my $opts = shift; diff --git a/lib/VNDBSchema.pm b/lib/VNDBSchema.pm index 5865fe43..c095550d 100644 --- a/lib/VNDBSchema.pm +++ b/lib/VNDBSchema.pm @@ -109,7 +109,7 @@ sub references { open my $F, '<', $fn or die "$fn: $!"; while(<$F>) { chomp; - next if !/^\s*ALTER\s+TABLE\s+([^ ]+)\s+ADD\s+CONSTRAINT\s+([^ ]+)\s+FOREIGN\s+KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^ ]+)\s*\(([^\)]+)\)/; + next if !/^\s*ALTER\s+TABLE\s+([^ ]+)\s+ADD\s+CONSTRAINT\s+([^ ]+)\s+FOREIGN\s+KEY\s+\(([^\)]+)\)\s*REFERENCES\s+([^ ]+)\s*\(([^\)]+)\)/; push @ref, { decl => $_, from_table => $1, diff --git a/static/f/wikidata.png b/static/f/wikidata.png Binary files differnew file mode 100644 index 00000000..e02e8acd --- /dev/null +++ b/static/f/wikidata.png diff --git a/util/dbdump.pl b/util/dbdump.pl index 7a283fb4..bbff0cf5 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -92,6 +92,9 @@ my %tables = ( votes => { where => 'uid NOT IN(SELECT uid FROM users_prefs WHERE key = \'hide_list\')' .' AND uid NOT IN(SELECT id FROM users WHERE ign_votes)' .' AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, + wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden + UNION SELECT l_wikidata FROM staff WHERE NOT hidden + UNION SELECT l_wikidata FROM vn WHERE NOT hidden)} }, wlists => { where => 'uid NOT IN(SELECT uid FROM users_prefs WHERE key = \'hide_list\') AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, ); @@ -191,6 +194,7 @@ sub export_db { my $dest = shift; my @static = qw{ + LICENSE-CC0.txt LICENSE-CC-BY-NC-SA.txt LICENSE-DBCL.txt LICENSE-ODBL.txt diff --git a/util/devdump.pl b/util/devdump.pl index 6adc361b..e001ede2 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -125,6 +125,9 @@ sub copy_entry { copy traits => undef, {addedby => 'user'}; copy 'traits_parents'; + # Wikidata (TODO: This could be a lot more selective) + copy 'wikidata'; + # Threads (announcements) my $threads = join ',', @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") }; copy threads => "SELECT * FROM threads WHERE id IN($threads)"; diff --git a/util/dump/LICENSE-CC0.txt b/util/dump/LICENSE-CC0.txt new file mode 100644 index 00000000..0e259d42 --- /dev/null +++ b/util/dump/LICENSE-CC0.txt @@ -0,0 +1,121 @@ +Creative Commons Legal Code + +CC0 1.0 Universal + + CREATIVE COMMONS CORPORATION IS NOT A LAW FIRM AND DOES NOT PROVIDE + LEGAL SERVICES. DISTRIBUTION OF THIS DOCUMENT DOES NOT CREATE AN + ATTORNEY-CLIENT RELATIONSHIP. CREATIVE COMMONS PROVIDES THIS + INFORMATION ON AN "AS-IS" BASIS. CREATIVE COMMONS MAKES NO WARRANTIES + REGARDING THE USE OF THIS DOCUMENT OR THE INFORMATION OR WORKS + PROVIDED HEREUNDER, AND DISCLAIMS LIABILITY FOR DAMAGES RESULTING FROM + THE USE OF THIS DOCUMENT OR THE INFORMATION OR WORKS PROVIDED + HEREUNDER. + +Statement of Purpose + +The laws of most jurisdictions throughout the world automatically confer +exclusive Copyright and Related Rights (defined below) upon the creator +and subsequent owner(s) (each and all, an "owner") of an original work of +authorship and/or a database (each, a "Work"). + +Certain owners wish to permanently relinquish those rights to a Work for +the purpose of contributing to a commons of creative, cultural and +scientific works ("Commons") that the public can reliably and without fear +of later claims of infringement build upon, modify, incorporate in other +works, reuse and redistribute as freely as possible in any form whatsoever +and for any purposes, including without limitation commercial purposes. +These owners may contribute to the Commons to promote the ideal of a free +culture and the further production of creative, cultural and scientific +works, or to gain reputation or greater distribution for their Work in +part through the use and efforts of others. + +For these and/or other purposes and motivations, and without any +expectation of additional consideration or compensation, the person +associating CC0 with a Work (the "Affirmer"), to the extent that he or she +is an owner of Copyright and Related Rights in the Work, voluntarily +elects to apply CC0 to the Work and publicly distribute the Work under its +terms, with knowledge of his or her Copyright and Related Rights in the +Work and the meaning and intended legal effect of CC0 on those rights. + +1. Copyright and Related Rights. A Work made available under CC0 may be +protected by copyright and related or neighboring rights ("Copyright and +Related Rights"). Copyright and Related Rights include, but are not +limited to, the following: + + i. the right to reproduce, adapt, distribute, perform, display, + communicate, and translate a Work; + ii. moral rights retained by the original author(s) and/or performer(s); +iii. publicity and privacy rights pertaining to a person's image or + likeness depicted in a Work; + iv. rights protecting against unfair competition in regards to a Work, + subject to the limitations in paragraph 4(a), below; + v. rights protecting the extraction, dissemination, use and reuse of data + in a Work; + vi. database rights (such as those arising under Directive 96/9/EC of the + European Parliament and of the Council of 11 March 1996 on the legal + protection of databases, and under any national implementation + thereof, including any amended or successor version of such + directive); and +vii. other similar, equivalent or corresponding rights throughout the + world based on applicable law or treaty, and any national + implementations thereof. + +2. Waiver. To the greatest extent permitted by, but not in contravention +of, applicable law, Affirmer hereby overtly, fully, permanently, +irrevocably and unconditionally waives, abandons, and surrenders all of +Affirmer's Copyright and Related Rights and associated claims and causes +of action, whether now known or unknown (including existing as well as +future claims and causes of action), in the Work (i) in all territories +worldwide, (ii) for the maximum duration provided by applicable law or +treaty (including future time extensions), (iii) in any current or future +medium and for any number of copies, and (iv) for any purpose whatsoever, +including without limitation commercial, advertising or promotional +purposes (the "Waiver"). Affirmer makes the Waiver for the benefit of each +member of the public at large and to the detriment of Affirmer's heirs and +successors, fully intending that such Waiver shall not be subject to +revocation, rescission, cancellation, termination, or any other legal or +equitable action to disrupt the quiet enjoyment of the Work by the public +as contemplated by Affirmer's express Statement of Purpose. + +3. Public License Fallback. Should any part of the Waiver for any reason +be judged legally invalid or ineffective under applicable law, then the +Waiver shall be preserved to the maximum extent permitted taking into +account Affirmer's express Statement of Purpose. In addition, to the +extent the Waiver is so judged Affirmer hereby grants to each affected +person a royalty-free, non transferable, non sublicensable, non exclusive, +irrevocable and unconditional license to exercise Affirmer's Copyright and +Related Rights in the Work (i) in all territories worldwide, (ii) for the +maximum duration provided by applicable law or treaty (including future +time extensions), (iii) in any current or future medium and for any number +of copies, and (iv) for any purpose whatsoever, including without +limitation commercial, advertising or promotional purposes (the +"License"). The License shall be deemed effective as of the date CC0 was +applied by Affirmer to the Work. Should any part of the License for any +reason be judged legally invalid or ineffective under applicable law, such +partial invalidity or ineffectiveness shall not invalidate the remainder +of the License, and in such case Affirmer hereby affirms that he or she +will not (i) exercise any of his or her remaining Copyright and Related +Rights in the Work or (ii) assert any associated claims and causes of +action with respect to the Work, in either case contrary to Affirmer's +express Statement of Purpose. + +4. Limitations and Disclaimers. + + a. No trademark or patent rights held by Affirmer are waived, abandoned, + surrendered, licensed or otherwise affected by this document. + b. Affirmer offers the Work as-is and makes no representations or + warranties of any kind concerning the Work, express, implied, + statutory or otherwise, including without limitation warranties of + title, merchantability, fitness for a particular purpose, non + infringement, or the absence of latent or other defects, accuracy, or + the present or absence of errors, whether or not discoverable, all to + the greatest extent permissible under applicable law. + c. Affirmer disclaims responsibility for clearing rights of other persons + that may apply to the Work or any use thereof, including without + limitation any person's Copyright and Related Rights in the Work. + Further, Affirmer disclaims responsibility for obtaining any necessary + consents, permissions or other rights required for any use of the + Work. + d. Affirmer understands and acknowledges that Creative Commons is not a + party to this document and has no duty or obligation with respect to + this CC0 or use of the Work. diff --git a/util/dump/README.txt b/util/dump/README.txt index dec74115..4d392378 100644 --- a/util/dump/README.txt +++ b/util/dump/README.txt @@ -36,6 +36,9 @@ Database Contents License [DbCL]. With the following exceptions: +Wikidata information (db/wikidata) has been obtained from Wikidata and is +made available under the Creative Commons CC0 License [CC0]. + Anime data (db/anime) is obtained from the AniDB.net UDP API and is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 [CC-BY-NC-SA]. @@ -46,4 +49,5 @@ subject to separate license conditions. [ODbL]: LICENSE-ODBL.txt; http://opendatacommons.org/licenses/odbl/1.0/ [DbCL]: LICENSE-DBCL.txt; http://opendatacommons.org/licenses/dbcl/1.0/ +[CC0]: LICENSE-CC0.txt; https://creativecommons.org/publicdomain/zero/1.0/ [CC-BY-NC-SA]: LICENSE-CC-BY-NC-SA.txt; https://creativecommons.org/licenses/by-nc-sa/4.0/ diff --git a/util/sql/func.sql b/util/sql/func.sql index 93025dfe..af01e0fe 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -479,6 +479,17 @@ $$ LANGUAGE plpgsql; +-- insert rows into wikidata for new l_wikidata items +-- (this is a BEFORE trigger) +CREATE OR REPLACE FUNCTION wikidata_insert() RETURNS trigger AS $$ +BEGIN + INSERT INTO wikidata (id) VALUES (NEW.l_wikidata) ON CONFLICT (id) DO NOTHING; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + + + -- For each row in rlists, there should be at least one corresponding row in -- vnlists for at least one of the VNs linked to that release. -- 1. When a row is deleted from vnlists, also remove all rows from rlists that diff --git a/util/sql/perms.sql b/util/sql/perms.sql index 5b643ed1..93c8e241 100644 --- a/util/sql/perms.sql +++ b/util/sql/perms.sql @@ -81,6 +81,7 @@ GRANT SELECT, INSERT ON vn_staff_hist TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_site; +GRANT SELECT, INSERT ON wikidata TO vndb_site; @@ -152,3 +153,4 @@ GRANT SELECT ON vn_staff_hist TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_multi; GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_multi; +GRANT SELECT, INSERT, UPDATE ON wikidata TO vndb_multi; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index c76fc88a..4daf799d 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -211,7 +211,8 @@ CREATE TABLE producers ( -- dbentry_type=p "desc" text NOT NULL DEFAULT '', -- [pub] alias varchar(500) NOT NULL DEFAULT '', -- [pub] l_wp varchar(150), -- [pub] - rgraph integer -- relgraphs.id + rgraph integer, -- relgraphs.id + l_wikidata integer -- [pub] ); -- producers_hist @@ -224,7 +225,8 @@ CREATE TABLE producers_hist ( lang language NOT NULL DEFAULT 'ja', "desc" text NOT NULL DEFAULT '', alias varchar(500) NOT NULL DEFAULT '', - l_wp varchar(150) + l_wp varchar(150), + l_wikidata integer ); -- producers_relations @@ -419,7 +421,8 @@ CREATE TABLE staff ( -- dbentry_type=s l_wp varchar(150) NOT NULL DEFAULT '', -- [pub] l_site varchar(250) NOT NULL DEFAULT '', -- [pub] l_twitter varchar(16) NOT NULL DEFAULT '', -- [pub] - l_anidb integer -- [pub] + l_anidb integer, -- [pub] + l_wikidata integer -- [pub] ); -- staff_hist @@ -432,7 +435,8 @@ CREATE TABLE staff_hist ( l_wp varchar(150) NOT NULL DEFAULT '', l_site varchar(250) NOT NULL DEFAULT '', l_twitter varchar(16) NOT NULL DEFAULT '', - l_anidb integer + l_anidb integer, + l_wikidata integer ); -- staff_alias @@ -648,7 +652,8 @@ CREATE TABLE vn ( -- dbentry_type=v c_popularity real, c_rating real, c_votecount integer NOT NULL DEFAULT 0, - c_search text + c_search text, + l_wikidata integer -- [pub] ); -- vn_hist @@ -663,7 +668,8 @@ CREATE TABLE vn_hist ( "desc" text NOT NULL DEFAULT '', l_wp varchar(150) NOT NULL DEFAULT '', l_encubed varchar(100) NOT NULL DEFAULT '', - l_renai varchar(100) NOT NULL DEFAULT '' + l_renai varchar(100) NOT NULL DEFAULT '', + l_wikidata integer ); -- vn_anime @@ -771,6 +777,33 @@ CREATE TABLE votes ( PRIMARY KEY(vid, uid) ); +-- wikidata +CREATE TABLE wikidata ( + id integer NOT NULL PRIMARY KEY, -- [pub] + lastfetch timestamptz, + enwiki text, -- [pub] + jawiki text, -- [pub] + website text, -- [pub] P856 + vndb text, -- [pub] P3180 + mobygames text, -- [pub] P1933 + mobygames_company text, -- [pub] P4773 + gamefaqs_game integer, -- [pub] P4769 + gamefaqs_company integer, -- [pub] P6182 + anidb_anime integer, -- [pub] P5646 + anidb_person integer, -- [pub] P5649 + ann_anime integer, -- [pub] P1985 + ann_manga integer, -- [pub] P1984 + musicbrainz_artist uuid, -- [pub] P434 + twitter text, -- [pub] P2002 + vgmdb_product integer, -- [pub] P5659 + vgmdb_artist integer, -- [pub] P3435 + discogs_artist integer, -- [pub] P1953 + acdb_char integer, -- [pub] P7013 + acdb_source integer, -- [pub] P7017 + indiedb_game text, -- [pub] P6717 + howlongtobeat integer -- [pub] P2816 +); + -- wlists CREATE TABLE wlists ( uid integer NOT NULL DEFAULT 0, -- [pub] diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 62800c17..1e5e5f6f 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -16,7 +16,9 @@ ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE producers ADD CONSTRAINT producers_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE producers_hist ADD CONSTRAINT producers_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); @@ -40,7 +42,9 @@ ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE staff ADD CONSTRAINT staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE staff ADD CONSTRAINT staff_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_id_fkey FOREIGN KEY (id) REFERENCES staff (id); ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; @@ -64,7 +68,9 @@ ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id); ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id); ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql index 962610a7..67a49991 100644 --- a/util/sql/triggers.sql +++ b/util/sql/triggers.sql @@ -27,6 +27,19 @@ CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); +CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); + CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages)) diff --git a/util/updates/update_20190809.sql b/util/updates/update_20190809.sql new file mode 100644 index 00000000..cf5baefd --- /dev/null +++ b/util/updates/update_20190809.sql @@ -0,0 +1,60 @@ +-- Update instructions: +-- +-- make +-- psql -U vndb -f util/updates/update_20190809.sql +-- psql -U postgres -f util/sql/perms.sql +CREATE TABLE wikidata ( + id integer NOT NULL PRIMARY KEY, -- [pub] + lastfetch timestamptz, + enwiki text, -- [pub] + jawiki text, -- [pub] + website text, -- [pub] P856 + vndb text, -- [pub] P3180 + mobygames text, -- [pub] P1933 + mobygames_company text, -- [pub] P4773 + gamefaqs_game integer, -- [pub] P4769 + gamefaqs_company integer, -- [pub] P6182 + anidb_anime integer, -- [pub] P5646 + anidb_person integer, -- [pub] P5649 + ann_anime integer, -- [pub] P1985 + ann_manga integer, -- [pub] P1984 + musicbrainz_artist uuid, -- [pub] P434 + twitter text, -- [pub] P2002 + vgmdb_product integer, -- [pub] P5659 + vgmdb_artist integer, -- [pub] P3435 + discogs_artist integer, -- [pub] P1953 + acdb_char integer, -- [pub] P7013 + acdb_source integer, -- [pub] P7017 + indiedb_game text, -- [pub] P6717 + howlongtobeat integer -- [pub] P2816 +); + +ALTER TABLE producers ADD COLUMN l_wikidata integer; +ALTER TABLE producers_hist ADD COLUMN l_wikidata integer; +ALTER TABLE staff ADD COLUMN l_wikidata integer; +ALTER TABLE staff_hist ADD COLUMN l_wikidata integer; +ALTER TABLE vn ADD COLUMN l_wikidata integer; +ALTER TABLE vn_hist ADD COLUMN l_wikidata integer; + +ALTER TABLE producers ADD CONSTRAINT producers_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); +ALTER TABLE producers_hist ADD CONSTRAINT producers_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); +ALTER TABLE staff ADD CONSTRAINT staff_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); +ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); +ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); + +\i util/sql/func.sql +\i util/sql/editfunc.sql + +CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert(); +CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert(); |