summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/Multi/Wikidata.pm63
-rw-r--r--lib/VNDB/Util/Misc.pm89
-rw-r--r--util/sql/schema.sql42
-rw-r--r--util/updates/update_20190814.sql19
4 files changed, 116 insertions, 97 deletions
diff --git a/lib/Multi/Wikidata.pm b/lib/Multi/Wikidata.pm
index 1681700f..bd938007 100644
--- a/lib/Multi/Wikidata.pm
+++ b/lib/Multi/Wikidata.pm
@@ -55,27 +55,27 @@ sub fetch {
}
-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
-/;
+my %props = (
+ P856 => [ 'website', 'text' ],
+ P3180 => [ 'vndb', 'text' ],
+ P1933 => [ 'mobygames', 'text' ],
+ P4773 => [ 'mobygames_company', 'text' ],
+ P4769 => [ 'gamefaqs_game', 'integer' ],
+ P6182 => [ 'gamefaqs_company', 'integer' ],
+ P5646 => [ 'anidb_anime', 'integer' ],
+ P5649 => [ 'anidb_person', 'integer' ],
+ P1985 => [ 'ann_anime', 'integer' ],
+ P1984 => [ 'ann_manga', 'integer' ],
+ P434 => [ 'musicbrainz_artist', 'uuid' ],
+ P2002 => [ 'twitter', 'text' ],
+ P5659 => [ 'vgmdb_product', 'integer' ],
+ P3435 => [ 'vgmdb_artist', 'integer' ],
+ P1953 => [ 'discogs_artist', 'integer' ],
+ P7013 => [ 'acdb_char', 'integer' ],
+ P7017 => [ 'acdb_source', 'integer' ],
+ P6717 => [ 'indiedb_game', 'text' ],
+ P2816 => [ 'howlongtobeat', 'integer' ],
+);
sub process {
@@ -104,17 +104,20 @@ sub save {
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;
+ my @v;
+ for (@{$data->{claims}{$p}}) {
+ my $v = $_->{mainsnak}{datavalue}{value};
+ if(ref $v) {
+ AE::log warn => "Q$id has a non-scalar value for '$p'";
+ } else {
+ push @val, $v;
+ push @v, sprintf '$%d::%s', scalar @val, $props{$p}[1];
+ }
}
- push @val, $v;
- push @set, sprintf '%s = $%d', $props{$p}, scalar @val;
+ push @set, @v
+ ? sprintf '%s = ARRAY[%s]', $props{$p}[0], join ',', @v
+ : "$props{$p}[0] = NULL";
}
my $set = join ', ', @set;
diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm
index ded13893..a8aac323 100644
--- a/lib/VNDB/Util/Misc.pm
+++ b/lib/VNDB/Util/Misc.pm
@@ -166,54 +166,51 @@ sub entryLinks {
my($self, $type, $obj) = @_;
my $w = $obj->{l_wikidata} ? $self->dbWikidata($obj->{l_wikidata}) : {};
+ my @links;
+ my $lnk = sub {
+ my($v, $title, $url, $xform) = @_;
+ push @links, map [ $title, sprintf $url, $xform ? $xform->($_) : $_ ], ref $v ? @$v : $v ? ($v) : ();
+ };
+
+ $lnk->($obj->{l_site}, 'Official website', '%s'); # Homepage always comes first
+ $lnk->($w->{enwiki}, 'Wikipedia (en)', 'https://en.wikipedia.org/wiki/%s', sub { shift =~ s/ /_/rg });
+ $lnk->($w->{jawiki}, 'Wikipedia (ja)', 'https://ja.wikipedia.org/wiki/%s', sub { shift =~ s/ /_/rg });
+ $lnk->($obj->{l_wikidata}, 'Wikidata', 'https://www.wikidata.org/wiki/Q%d');
+
# 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 ] : (),
- $obj->{l_wikidata} ? [ 'Wikidata', 'https://www.wikidata.org/wiki/Q%d', $obj->{l_wikidata} ] : (),
-
- # 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->{c_votecount}>=20 ? [ 'VNStat', 'https://vnstat.net/novel/%d', $obj->{id} ] : (),
- #$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} ] : (),
- [ 'VNStat', 'https://vnstat.net/developer/%d', $obj->{id} ],
-
- #$obj->{l_wp} ? [ 'Wikipedia', "https://en.wikipedia.org/wiki/$obj->{l_wp}" ] : (), # Superseded by l_wikidata
- ) : (),
- );
-
- [ map [ $_->[0], $_->[2] ? sprintf $_->[1], $_->[2] : $_->[1] ], @links ];
+
+ # VN links
+ if($type eq 'v') {
+ $lnk->($w->{mobygames}, 'MobyGames', 'https://www.mobygames.com/game/%s');
+ $lnk->($w->{gamefaqs_game}, 'GameFAQs', 'https://gamefaqs.gamespot.com/-/%s-');
+ $lnk->($w->{vgmdb_product}, 'VGMdb', 'https://vgmdb.net/product/%s');
+ $lnk->($w->{acdb_source}, 'ACDB', 'https://www.animecharactersdatabase.com/source.php?id=%s');
+ $lnk->($w->{indiedb_game}, 'IndieDB', 'https://www.indiedb.com/games/%s');
+ $lnk->($w->{howlongtobeat}, 'HowLongToBeat', 'http://howlongtobeat.com/game.php?id=%s');
+ $lnk->($obj->{l_renai}, 'Renai.us', 'https://renai.us/game/%s');
+ push @links, [ 'VNStat', sprintf 'https://vnstat.net/novel/%d', $obj->{id} ] if $obj->{c_votecount}>=20;
+ }
+
+ # Staff links
+ if($type eq 's') {
+ $lnk->($obj->{l_twitter}, 'Twitter', 'https://twitter.com/%s');
+ $lnk->($w->{twitter}, 'Twitter', 'https://twitter.com/%s') if !$obj->{l_twitter};
+ $lnk->($obj->{l_anidb}, 'AniDB', 'https://anidb.net/cr%s');
+ $lnk->($w->{anidb_person}, 'AniDB', 'https://anidb.net/cr%s') if !$obj->{l_anidb};
+ $lnk->($w->{musicbrainz_artist}, 'MusicBrainz', 'https://musicbrainz.org/artist/%s');
+ $lnk->($w->{vgmdb_artist}, 'VGMdb', 'https://vgmdb.net/artist/%s');
+ $lnk->($w->{discogs_artist}, 'Discogs', 'https://www.discogs.com/artist/%s');
+ }
+
+ # Producer links
+ if($type eq 'p') {
+ $lnk->($w->{mobygames_company}, 'MobyGames', 'https://www.mobygames.com/company/%s');
+ $lnk->($w->{gamefaqs_company}, 'GameFAQs', 'https://gamefaqs.gamespot.com/company/%s-');
+ push @links, [ 'VNStat', sprintf 'https://vnstat.net/developer/%d', $obj->{id} ];
+ }
+
+ \@links
}
1;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 4daf799d..7786e9b5 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -781,27 +781,27 @@ CREATE TABLE votes (
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
+ 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
diff --git a/util/updates/update_20190814.sql b/util/updates/update_20190814.sql
new file mode 100644
index 00000000..ddb04563
--- /dev/null
+++ b/util/updates/update_20190814.sql
@@ -0,0 +1,19 @@
+ALTER TABLE wikidata ALTER COLUMN website TYPE text[] USING CASE WHEN website IS NULL THEN NULL ELSE ARRAY[website ] END;
+ALTER TABLE wikidata ALTER COLUMN vndb TYPE text[] USING CASE WHEN vndb IS NULL THEN NULL ELSE ARRAY[vndb ] END;
+ALTER TABLE wikidata ALTER COLUMN mobygames TYPE text[] USING CASE WHEN mobygames IS NULL THEN NULL ELSE ARRAY[mobygames ] END;
+ALTER TABLE wikidata ALTER COLUMN mobygames_company TYPE text[] USING CASE WHEN mobygames_company IS NULL THEN NULL ELSE ARRAY[mobygames_company ] END;
+ALTER TABLE wikidata ALTER COLUMN gamefaqs_game TYPE integer[] USING CASE WHEN gamefaqs_game IS NULL THEN NULL ELSE ARRAY[gamefaqs_game ] END;
+ALTER TABLE wikidata ALTER COLUMN gamefaqs_company TYPE integer[] USING CASE WHEN gamefaqs_company IS NULL THEN NULL ELSE ARRAY[gamefaqs_company ] END;
+ALTER TABLE wikidata ALTER COLUMN anidb_anime TYPE integer[] USING CASE WHEN anidb_anime IS NULL THEN NULL ELSE ARRAY[anidb_anime ] END;
+ALTER TABLE wikidata ALTER COLUMN anidb_person TYPE integer[] USING CASE WHEN anidb_person IS NULL THEN NULL ELSE ARRAY[anidb_person ] END;
+ALTER TABLE wikidata ALTER COLUMN ann_anime TYPE integer[] USING CASE WHEN ann_anime IS NULL THEN NULL ELSE ARRAY[ann_anime ] END;
+ALTER TABLE wikidata ALTER COLUMN ann_manga TYPE integer[] USING CASE WHEN ann_manga IS NULL THEN NULL ELSE ARRAY[ann_manga ] END;
+ALTER TABLE wikidata ALTER COLUMN musicbrainz_artist TYPE uuid[] USING CASE WHEN musicbrainz_artist IS NULL THEN NULL ELSE ARRAY[musicbrainz_artist] END;
+ALTER TABLE wikidata ALTER COLUMN twitter TYPE text[] USING CASE WHEN twitter IS NULL THEN NULL ELSE ARRAY[twitter ] END;
+ALTER TABLE wikidata ALTER COLUMN vgmdb_product TYPE integer[] USING CASE WHEN vgmdb_product IS NULL THEN NULL ELSE ARRAY[vgmdb_product ] END;
+ALTER TABLE wikidata ALTER COLUMN vgmdb_artist TYPE integer[] USING CASE WHEN vgmdb_artist IS NULL THEN NULL ELSE ARRAY[vgmdb_artist ] END;
+ALTER TABLE wikidata ALTER COLUMN discogs_artist TYPE integer[] USING CASE WHEN discogs_artist IS NULL THEN NULL ELSE ARRAY[discogs_artist ] END;
+ALTER TABLE wikidata ALTER COLUMN acdb_char TYPE integer[] USING CASE WHEN acdb_char IS NULL THEN NULL ELSE ARRAY[acdb_char ] END;
+ALTER TABLE wikidata ALTER COLUMN acdb_source TYPE integer[] USING CASE WHEN acdb_source IS NULL THEN NULL ELSE ARRAY[acdb_source ] END;
+ALTER TABLE wikidata ALTER COLUMN indiedb_game TYPE text[] USING CASE WHEN indiedb_game IS NULL THEN NULL ELSE ARRAY[indiedb_game ] END;
+ALTER TABLE wikidata ALTER COLUMN howlongtobeat TYPE integer[] USING CASE WHEN howlongtobeat IS NULL THEN NULL ELSE ARRAY[howlongtobeat ] END;
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-18 23:53:55 +0000