summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-08-09 16:53:54 +0200
committerYorhel <git@yorhel.nl>2019-08-09 16:53:56 +0200
commit414daab355c8392dc21d5623cd458426d221fedf (patch)
tree14853fa252b5130e24dcad779505f8b86b9c909b
parent76da9bdcf9a6492888f02457a9c7a23454da1092 (diff)
Replace Wikipedia links with Wikidata IDs
+ Automatically fetch other links from Wikidata
-rw-r--r--data/global.pl1
-rw-r--r--lib/Multi/Wikidata.pm126
-rw-r--r--lib/VNDB/DB/Misc.pm6
-rw-r--r--lib/VNDB/DB/Producers.pm6
-rw-r--r--lib/VNDB/DB/Staff.pm6
-rw-r--r--lib/VNDB/DB/VN.pm9
-rw-r--r--lib/VNDB/Func.pm6
-rw-r--r--lib/VNDB/Handler/Producers.pm27
-rw-r--r--lib/VNDB/Handler/Staff.pm12
-rw-r--r--lib/VNDB/Handler/VNEdit.pm17
-rw-r--r--lib/VNDB/Handler/VNPage.pm17
-rw-r--r--lib/VNDB/Util/FormHTML.pm5
-rw-r--r--lib/VNDB/Util/Misc.pm56
-rw-r--r--lib/VNDB/Util/ValidateTemplates.pm7
-rw-r--r--lib/VNDBSchema.pm2
-rw-r--r--static/f/wikidata.pngbin0 -> 35543 bytes
-rwxr-xr-xutil/dbdump.pl4
-rwxr-xr-xutil/devdump.pl3
-rw-r--r--util/dump/LICENSE-CC0.txt121
-rw-r--r--util/dump/README.txt4
-rw-r--r--util/sql/func.sql11
-rw-r--r--util/sql/perms.sql2
-rw-r--r--util/sql/schema.sql45
-rw-r--r--util/sql/tableattrs.sql6
-rw-r--r--util/sql/triggers.sql13
-rw-r--r--util/updates/update_20190809.sql60
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
new file mode 100644
index 00000000..e02e8acd
--- /dev/null
+++ b/static/f/wikidata.png
Binary files differ
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();