summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r--lib/VNDB/DB/Chars.pm150
-rw-r--r--lib/VNDB/DB/Producers.pm108
-rw-r--r--lib/VNDB/DB/Releases.pm224
-rw-r--r--lib/VNDB/DB/Staff.pm79
-rw-r--r--lib/VNDB/DB/Tags.pm162
-rw-r--r--lib/VNDB/DB/Traits.pm86
-rw-r--r--lib/VNDB/DB/Users.pm49
-rw-r--r--lib/VNDB/DB/VN.pm257
8 files changed, 0 insertions, 1115 deletions
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
deleted file mode 100644
index 0b159452..00000000
--- a/lib/VNDB/DB/Chars.pm
+++ /dev/null
@@ -1,150 +0,0 @@
-
-package VNDB::DB::Chars;
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw|dbCharFilters dbCharGet|;
-
-
-# Character filters shared by dbCharGet and dbVNGet
-sub dbCharFilters {
- my($self, %o) = @_;
- return (
- defined $o{gender} ? ( 'c.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
- defined $o{bloodt} ? ( 'c.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (),
- defined $o{bust_min} ? ( 'c.s_bust >= ?' => $o{bust_min} ) : (),
- defined $o{bust_max} ? ( 'c.s_bust <= ? AND c.s_bust > 0' => $o{bust_max} ) : (),
- defined $o{waist_min} ? ( 'c.s_waist >= ?' => $o{waist_min} ) : (),
- defined $o{waist_max} ? ( 'c.s_waist <= ? AND c.s_waist > 0' => $o{waist_max} ) : (),
- defined $o{hip_min} ? ( 'c.s_hip >= ?' => $o{hip_min} ) : (),
- defined $o{hip_max} ? ( 'c.s_hip <= ? AND c.s_hip > 0' => $o{hip_max} ) : (),
- defined $o{height_min} ? ( 'c.height >= ?' => $o{height_min} ) : (),
- defined $o{height_max} ? ( 'c.height <= ? AND c.height > 0' => $o{height_max} ) : (),
- defined $o{weight_min} ? ( 'c.weight >= ?' => $o{weight_min} ) : (),
- defined $o{weight_max} ? ( 'c.weight <= ?' => $o{weight_max} ) : (),
- defined $o{cup_min} ? ( 'c.cup_size >= ?' => $o{cup_min} ) : (),
- defined $o{cup_max} ? ( 'c.cup_size <= ?' => $o{cup_max} ) : (),
- $o{role} ? (
- 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.id = c.id AND cvi.role IN(!l))',
- [ ref $o{role} ? $o{role} : [$o{role}] ] ) : (),
- $o{trait_inc} ? (
- 'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)',
- [ ref $o{trait_inc} ? $o{trait_inc} : [$o{trait_inc}], $o{tagspoil}, ref $o{trait_inc} ? $#{$o{trait_inc}}+1 : 1 ]) : (),
- $o{trait_exc} ? (
- 'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (),
- $o{va_inc} ? ( 'c.id IN(SELECT ivs.cid FROM vn_seiyuu ivs JOIN staff_alias isa ON isa.aid = ivs.aid WHERE isa.id IN(!l))' => [ ref $o{va_inc} ? $o{va_inc} : [$o{va_inc}] ] ) : (),
- $o{va_exc} ? ( 'c.id NOT IN(SELECT ivs.cid FROM vn_seiyuu ivs JOIN staff_alias isa ON isa.aid = ivs.aid WHERE isa.id IN(!l))' => [ ref $o{va_exc} ? $o{va_exc} : [$o{va_exc}] ] ) : (),
- )
-}
-
-
-# options: id instance tagspoil trait_inc trait_exc char what results page gender bloodt
-# bust_min bust_max waist_min waist_max hip_min hip_max height_min height_max weight_min weight_max role
-# what: extended traits vns changes
-sub dbCharGet {
- my $self = shift;
- my %o = (
- page => 1,
- results => 10,
- what => '',
- tagspoil => 0,
- @_
- );
-
- $o{search} =~ s/%//g if $o{search};
-
- my %where = (
- !$o{id} ? ( 'c.hidden = FALSE' => 1 ) : (),
- $o{id} ? ( 'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
- $o{notid} ? ( 'c.id <> ?' => $o{notid} ) : (),
- $o{instance} ? ( 'c.main = ?' => $o{instance} ) : (),
- $o{vid} ? ( 'c.id IN(SELECT id FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (),
- $o{search} ? (
- "(c.name ILIKE ? OR translate(c.original,' ','') ILIKE translate(?,' ','') OR c.alias ILIKE ?)", [ map '%'.$o{search}.'%', 1..3 ] ) : (),
- $o{char} ? (
- 'LOWER(SUBSTR(c.name, 1, 1)) = ?' => $o{char} ) : (),
- defined $o{char} && !$o{char} ? (
- '(ASCII(c.name) < 97 OR ASCII(c.name) > 122) AND (ASCII(c.name) < 65 OR ASCII(c.name) > 90)' => 1 ) : (),
- $self->dbCharFilters(%o),
- );
-
- my @select = (qw|c.id c.name c.original c.gender|);
- push @select, qw|c.hidden c.locked c.alias c.desc c.b_month c.b_day c.s_bust c.s_waist c.s_hip c.height c.weight c.bloodt c.cup_size c.age c.main c.main_spoil|,
- 'coalesce(vndbid_num(c.image),0) AS image' if $o{what} =~ /extended/;
-
- my($r, $np) = $self->dbPage(\%o, q|
- SELECT !s
- FROM chars c
- !W
- ORDER BY c.name|,
- join(', ', @select), \%where
- );
-
- return _enrich($self, $r, $np, 0, $o{what}, $o{vid});
-}
-
-
-sub _enrich {
- my($self, $r, $np, $rev, $what, $vid) = @_;
-
- if(@$r && $what =~ /vns|traits/) {
- my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
- my %r = map {
- $_->{traits} = [];
- $_->{vns} = [];
- ($_->{$col}, $_)
- } @$r;
-
- if($what =~ /traits/) {
- push @{$r{ delete $_->{xid} }{traits}}, $_ for (@{$self->dbAll(qq|
- SELECT ct.$colname AS xid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname
- FROM chars_traits$hist ct
- JOIN traits t ON t.id = ct.tid
- JOIN traits tg ON tg.id = t."group"
- WHERE ct.$colname IN(!l)
- ORDER BY tg."order", t.name|, [ keys %r ]
- )});
- }
-
- if($what =~ /vns(?:\((\d+)\))?/) {
- push @{$r{ delete $_->{xid} }{vns}}, $_ for (@{$self->dbAll("
- SELECT cv.$colname AS xid, cv.vid, cv.rid, cv.spoil, cv.role, v.title AS vntitle, r.title AS rtitle
- FROM chars_vns$hist cv
- JOIN vn v ON cv.vid = v.id
- LEFT JOIN releases r ON cv.rid = r.id
- !W
- ORDER BY v.c_released",
- { "cv.$colname IN(!l)" => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () }
- )});
- }
- }
-
- # Depends on the VN revision rather than char revision
- if(@$r && $what =~ /seiyuu/) {
- my %r = map {
- $_->{seiyuu} = [];
- ($_->{id}, $_)
- } @$r;
-
- push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q|
- SELECT vs.cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, v.title AS vntitle
- FROM vn_seiyuu vs
- JOIN staff_alias sa ON sa.aid = vs.aid
- JOIN staff s ON s.id = sa.id
- JOIN vn v ON v.id = vs.id
- !W
- ORDER BY v.c_released, sa.name|, {
- 's.hidden = FALSE' => 1,
- 'vs.cid IN(!l)' => [[ keys %r ]],
- $vid ? ('v.id = ?' => $vid) : (),
- }
- )});
- }
-
- return wantarray ? ($r, $np) : $r;
-}
-
-
-1;
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
deleted file mode 100644
index c9d4f95f..00000000
--- a/lib/VNDB/DB/Producers.pm
+++ /dev/null
@@ -1,108 +0,0 @@
-
-package VNDB::DB::Producers;
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw|dbProducerGet dbProducerGetRev|;
-
-
-# options: results, page, id, search, char, sort, inc_hidden
-# what: extended relations
-sub dbProducerGet {
- my $self = shift;
- my %o = (
- results => 10,
- page => 1,
- what => '',
- @_
- );
-
- $o{search} =~ s/%//g if $o{search};
-
- my %where = (
- !$o{id} && !$o{inc_hidden} ? (
- 'p.hidden = FALSE' => 1 ) : (),
- $o{id} ? (
- 'p.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
- $o{search} ? (
- '(p.name ILIKE ? OR p.original ILIKE ? OR p.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
- $o{char} ? (
- 'LOWER(SUBSTR(p.name, 1, 1)) = ?' => $o{char} ) : (),
- defined $o{char} && !$o{char} ? (
- '(ASCII(p.name) < 97 OR ASCII(p.name) > 122) AND (ASCII(p.name) < 65 OR ASCII(p.name) > 90)' => 1 ) : (),
- );
-
- my $select = 'p.id, p.type, p.name, p.original, p.lang';
- $select .= ', p.desc, p.alias, p.website, p.l_wp, p.l_wikidata, p.hidden, p.locked' if $o{what} =~ /extended/;
-
- my($order, @order) = ('p.name');
- if($o{sort} && $o{sort} eq 'search') {
- $order = 'least(substr_score(p.name, ?), substr_score(p.original, ?)), p.name';
- @order = ($o{search}) x 2;
- }
-
- my($r, $np) = $self->dbPage(\%o, qq|
- SELECT !s
- FROM producers p
- !W
- ORDER BY $order|,
- $select, \%where, @order
- );
-
- return _enrich($self, $r, $np, 0, $o{what});
-}
-
-
-# options: id, rev, what
-# what: extended relations
-sub dbProducerGetRev {
- my $self = shift;
- my %o = (what => '', @_);
-
- $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'p\' AND itemid = ?', $o{id})->{rev};
-
- my $select = 'c.itemid AS id, p.type, p.name, p.original, p.lang';
- $select .= ', extract(\'epoch\' from c.added) as added, c.comments, c.rev, c.ihid, c.ilock, '.VNWeb::DB::sql_user();
- $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, p.l_wikidata, po.hidden, po.locked' if $o{what} =~ /extended/;
-
- my $r = $self->dbAll(q|
- SELECT !s
- FROM changes c
- JOIN producers po ON po.id = c.itemid
- JOIN producers_hist p ON p.chid = c.id
- JOIN users u ON u.id = c.requester
- WHERE c.type = 'p' AND c.itemid = ? AND c.rev = ?|,
- $select, $o{id}, $o{rev}
- );
-
- return _enrich($self, $r, 0, 1, $o{what});
-}
-
-
-sub _enrich {
- my($self, $r, $np, $rev, $what) = @_;
-
- if(@$r && $what =~ /relations/) {
- my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
- my %r = map {
- $r->[$_]{relations} = [];
- ($r->[$_]{$col}, $_)
- } 0..$#$r;
-
- push @{$r->[$r{$_->{xid}}]{relations}}, $_ for(@{$self->dbAll(qq|
- SELECT rel.$colname AS xid, rel.pid AS id, rel.relation, p.name, p.original
- FROM producers_relations$hist rel
- JOIN producers p ON rel.pid = p.id
- WHERE rel.$colname IN(!l)|,
- [ keys %r ]
- )});
- }
-
- return wantarray ? ($r, $np) : $r;
-}
-
-1;
-
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
deleted file mode 100644
index 2bfe8032..00000000
--- a/lib/VNDB/DB/Releases.pm
+++ /dev/null
@@ -1,224 +0,0 @@
-
-package VNDB::DB::Releases;
-
-use strict;
-use warnings;
-use POSIX 'strftime';
-use Exporter 'import';
-use VNDB::Func 'gtintype';
-
-our @EXPORT = qw|dbReleaseFilters dbReleaseGet dbReleaseGetRev dbReleaseEngines|;
-
-
-# Release filters shared by dbReleaseGet and dbVNGet
-sub dbReleaseFilters {
- my($self, %o) = @_;
- $o{plat} = [ $o{plat} ] if $o{plat} && !ref $o{plat};
- $o{med} = [ $o{med} ] if $o{med} && !ref $o{med};
- return (
- defined $o{patch} ? ( 'r.patch = ?' => $o{patch} == 1 ? 1 : 0) : (),
- defined $o{freeware} ? ( 'r.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (),
- defined $o{uncensored} ? ( 'r.uncensored = ?' => $o{uncensored} == 1 ? 1 : 0) : (),
- defined $o{type} ? ( 'r.type = ?' => $o{type} ) : (),
- defined $o{date_before} ? ( 'r.released <= ?' => $o{date_before} ) : (),
- defined $o{date_after} ? ( 'r.released >= ?' => $o{date_after} ) : (),
- defined $o{minage} ? ( 'r.minage IN(!l)' => [ ref $o{minage} ? $o{minage} : [$o{minage}] ] ) : (),
- defined $o{doujin} ? ( 'NOT r.patch AND r.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (),
- defined $o{resolution} ? ( 'NOT r.patch AND ARRAY[r.reso_x, r.reso_y] IN(!l)' =>
- [[ map $_ eq 'unknown' ? '{0,0}' : $_ eq 'nonstandard' ? '{0,1}' : '{'.(s/x/,/r).'}',
- ref $o{resolution} ? $o{resolution}->@* : $o{resolution} ]] ) : (),
- defined $o{voiced} ? ( 'NOT r.patch AND r.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (),
- defined $o{ani_story} ? ( 'NOT r.patch AND r.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (),
- defined $o{ani_ero} ? ( 'NOT r.patch AND r.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (),
- defined $o{engine} ? ( 'r.engine = ?' => $o{engine} ) : (),
- defined $o{released} ? ( 'r.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
- $o{lang} ? (
- 'r.id IN(SELECT irl.id FROM releases_lang irl WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
- $o{olang} ? (
- 'r.id IN(SELECT irv.id FROM releases_vn irv JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (),
- $o{plat} ? ('('.join(' OR ',
- grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.id = r.id)' : (),
- grep(!/^unk$/, @{$o{plat}}) ? 'r.id IN(SELECT irp.id FROM releases_platforms irp WHERE irp.platform IN(!l))' : (),
- ).')', [ [ grep !/^unk$/, @{$o{plat}} ] ]) : (),
- $o{med} ? ('('.join(' OR ',
- grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.id = r.id)' : (),
- grep(!/^unk$/, @{$o{med}}) ? 'r.id IN(SELECT irm.id FROM releases_media irm WHERE irm.medium IN(!l))' : ()
- ).')', [ [ grep(!/^unk$/, @{$o{med}}) ] ]) : (),
- $o{prod_inc} ? ('r.id IN(SELECT irp.id FROM releases_producers irp WHERE irp.pid IN(!l))' => [ ref $o{prod_inc} ? $o{prod_inc} : [$o{prod_inc}] ]) : (),
- $o{prod_exc} ? ('r.id NOT IN(SELECT irp.id FROM releases_producers irp WHERE irp.pid IN(!l))' => [ ref $o{prod_exc} ? $o{prod_exc} : [$o{prod_exc}] ]) : (),
- );
-}
-
-
-# Options: id vid pid released page results what med sort reverse date_before date_after
-# plat prod_inc prod_exc lang olang type minage search resolution freeware doujin voiced uncensored ani_story ani_ero hidden_only
-# What: extended vn producers platforms media
-# Sort: title released minage
-sub dbReleaseGet {
- my($self, %o) = @_;
- $o{results} ||= 50;
- $o{page} ||= 1;
- $o{what} ||= '';
-
- my @where = (
- !$o{id} && !$o{hidden_only} ? ( 'r.hidden = FALSE' => 0 ) : (),
- $o{hidden_only} ? ('r.hidden = TRUE' => 1) : (),
- $o{id} ? ( 'r.id = ?' => $o{id} ) : (),
- $o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (),
- $o{vid} ? ( 'r.id IN(SELECT id FROM releases_vn WHERE vid IN(!l))' => [ ref $o{vid} ? $o{vid} : [$o{vid}] ] ) : (),
- $self->dbReleaseFilters(%o),
- );
-
- if($o{search}) {
- for (split /[ -,._]/, $o{search}) {
- s/%//g;
- if(/^\d+$/ && gtintype($_)) {
- push @where, 'r.gtin = ?', $_;
- } elsif(length($_) > 0) {
- $_ = "%$_%";
- push @where, '(r.title ILIKE ? OR r.original ILIKE ? OR r.catalog = ?)',
- [ $_, $_, $_ ];
- }
- }
- }
-
- my @join = (
- $o{pid} ? 'JOIN releases_producers rp ON rp.id = r.id' : (),
- );
-
- my @select = (
- qw|r.id r.title r.original r.website r.released r.minage r.type r.patch|,
- $o{what} =~ /extended/ ? qw|
- r.notes r.catalog r.gtin r.reso_x r.reso_y r.voiced r.freeware r.doujin r.uncensored r.ani_story r.ani_ero r.engine r.hidden r.locked
- | : (),
- $o{pid} ? ('rp.developer', 'rp.publisher') : (),
- $o{what} =~ /links/ ? qw|
- r.gtin r.l_steam r.l_gog r.l_gyutto r.l_digiket r.l_melon r.l_getchu r.l_getchudl r.l_dmm r.l_itch r.l_jastusa r.l_egs r.l_erotrail r.l_mg r.l_denpa r.l_jlist r.l_dlsite r.l_dlsiteen r.l_melonjp r.l_toranoana r.l_gamejolt r.l_nutaku
- | : ()
- );
-
- my $order = sprintf {
- title => 'r.title %s, r.released %1$s',
- type => 'r.patch %s, r.type %1$s, r.released %1$s, r.title %1$s',
- publication => 'r.doujin %s, r.freeware %1$s, r.patch %1$s, r.released %1$s, r.title %1$s',
- resolution => 'r.reso_x %s, r.reso_y %1$s, r.patch %2$s, r.released %1$s, r.title %1$s',
- voiced => 'r.voiced %s, r.patch %2$s, r.released %1$s, r.title %1$s',
- ani_ero => 'r.ani_story %s, r.ani_ero %1$s, r.patch %2$s, r.released %1$s, r.title %1$s',
- released => 'r.released %s, r.id %1$s',
- minage => 'r.minage %s, r.released %1$s, r.title %1$s',
- notes => 'r.notes %s, r.released %1$s, r.title %1$s',
- }->{ $o{sort}||'released' }, $o{reverse} ? 'DESC' : 'ASC', !$o{reverse} ? 'DESC' : 'ASC';
-
- my($r, $np) = $self->dbPage(\%o, q|
- SELECT !s
- FROM releases r
- !s
- !W
- ORDER BY !s|,
- join(', ', @select), join(' ', @join), \@where, $order
- );
-
- return _enrich($self, $r, $np, 0, $o{what});
-}
-
-
-# options: id, rev, what
-# what: extended vn producers platforms media
-sub dbReleaseGetRev {
- my $self = shift;
- my %o = (what => '', @_);
-
- $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'r\' AND itemid = ?', $o{id})->{rev};
-
- my $select = 'c.itemid AS id, r.title, r.original, r.website, r.released, r.minage, r.type, r.patch';
- $select .= ', r.notes, r.catalog, r.gtin, r.reso_x, r.reso_y, r.voiced, r.freeware, r.doujin, r.uncensored, r.ani_story, r.ani_ero, r.engine, ro.hidden, ro.locked' if $o{what} =~ /extended/;
- $select .= ', extract(\'epoch\' from c.added) as added, c.comments, c.rev, c.ihid, c.ilock, '.VNWeb::DB::sql_user();
- $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 .= ', r.gtin, r.l_steam, r.l_gog, r.l_gyutto, r.l_digiket, r.l_melon, r.l_getchu, r.l_getchudl, r.l_dmm, r.l_itch, r.l_jastusa, r.l_egs, r.l_erotrail, r.l_mg, r.l_denpa, r.l_jlist, r.l_dlsite, r.l_dlsiteen, r.l_melonjp, r.l_toranoana, r.l_gamejolt, r.l_nutaku' if $o{what} =~ /links/;
-
- my $r = $self->dbAll(q|
- SELECT !s
- FROM changes c
- JOIN releases ro ON ro.id = c.itemid
- JOIN releases_hist r ON r.chid = c.id
- JOIN users u ON u.id = c.requester
- WHERE c.type = 'r' AND c.itemid = ? AND c.rev = ?|,
- $select, $o{id}, $o{rev}
- );
-
- return _enrich($self, $r, 0, 1, $o{what});
-}
-
-
-sub _enrich {
- my($self, $r, $np, $rev, $what) = @_;
-
- if(@$r) {
- my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
- my %r = map {
- $r->[$_]{producers} = [];
- $r->[$_]{platforms} = [];
- $r->[$_]{media} = [];
- $r->[$_]{vn} = [];
- $r->[$_]{languages} = [];
- ($r->[$_]{$col}, $_)
- } 0..$#$r;
-
- push(@{$r->[$r{$_->{xid}}]{languages}}, $_->{lang}) for (@{$self->dbAll("
- SELECT $colname AS xid, lang
- FROM releases_lang$hist
- WHERE $colname IN(!l)",
- [ keys %r ]
- )});
-
- if($what =~ /vn/) {
- push(@{$r->[$r{$_->{xid}}]{vn}}, $_) for (@{$self->dbAll("
- SELECT rv.$colname AS xid, v.id AS vid, v.title, v.original
- FROM releases_vn$hist rv
- JOIN vn v ON v.id = rv.vid
- WHERE rv.$colname IN(!l)
- ORDER BY v.title",
- [ keys %r ]
- )});
- }
-
- if($what =~ /producers/) {
- push(@{$r->[$r{$_->{xid}}]{producers}}, $_) for (@{$self->dbAll("
- SELECT rp.$colname AS xid, rp.developer, rp.publisher, p.id, p.name, p.original, p.type
- FROM releases_producers$hist rp
- JOIN producers p ON rp.pid = p.id
- WHERE rp.$colname IN(!l)
- ORDER BY p.name",
- [ keys %r ]
- )});
- }
-
- if($what =~ /platforms/) {
- push(@{$r->[$r{$_->{xid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll("
- SELECT $colname AS xid, platform
- FROM releases_platforms$hist
- WHERE $colname IN(!l)",
- [ keys %r ]
- )});
- }
-
- if($what =~ /media/) {
- push(@{$r->[$r{$_->{xid}}]{media}}, $_) for (@{$self->dbAll("
- SELECT $colname AS xid, medium, qty
- FROM releases_media$hist
- WHERE $colname IN(!l)",
- [ keys %r ]
- )});
- }
- }
-
- return wantarray ? ($r, $np) : $r;
-}
-
-
-sub dbReleaseEngines {
- shift->dbAll(q{SELECT engine, count(*) as cnt FROM releases WHERE engine <> '' GROUP BY engine ORDER BY COUNT(*) desc, engine});
-}
-
-1;
-
diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm
deleted file mode 100644
index 5a393dbb..00000000
--- a/lib/VNDB/DB/Staff.pm
+++ /dev/null
@@ -1,79 +0,0 @@
-
-package VNDB::DB::Staff;
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw|dbStaffGet |;
-
-# options: results, page, id, aid, search, exact, truename, role, gender
-sub dbStaffGet {
- my $self = shift;
- my %o = (
- results => 10,
- page => 1,
- what => '',
- @_
- );
- my(@roles, $seiyuu);
- if(defined $o{role}) {
- if(ref $o{role}) {
- $seiyuu = grep /^seiyuu$/, @{$o{role}};
- @roles = grep !/^seiyuu$/, @{$o{role}};
- } else {
- $seiyuu = $o{role} eq 'seiyuu';
- @roles = $o{role} unless $seiyuu;
- }
- }
-
- $o{search} =~ s/%//g if $o{search};
-
- my %where = (
- !$o{id} ? ( 's.hidden = FALSE' => 1 ) : (),
- $o{id} ? ( ref $o{id} ? ('s.id IN(!l)' => [$o{id}]) : ('s.id = ?' => $o{id}) ) : (),
- $o{aid} ? ( ref $o{aid} ? ('sa.aid IN(!l)' => [$o{aid}]) : ('sa.aid = ?' => $o{aid}) ) : (),
- $o{id} || $o{truename} ? ( 's.aid = sa.aid' => 1 ) : (),
- defined $o{gender} ? ( 's.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
- defined $o{lang} ? ( 's.lang IN(!l)' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (),
- defined $o{role} ? (
- '('.join(' OR ',
- @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE vs.aid = sa.aid AND vs.role IN(!l) AND NOT v.hidden)' ) : (),
- $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.id = vsy.id WHERE vsy.aid = sa.aid AND NOT v.hidden)' ) : ()
- ).')' => ( @roles ? [ \@roles ] : 1 ),
- ) : (),
- $o{exact} ? ( '(lower(sa.name) = lower(?) OR lower(sa.original) = lower(?))' => [ ($o{exact}) x 2 ] ) : (),
- $o{search} ?
- $o{search} =~ /[\x{3000}-\x{9fff}\x{ff00}-\x{ff9f}]/ ?
- # match against 'original' column only if search string contains any
- # japanese character.
- # note: more precise regex would be /[\p{Hiragana}\p{Katakana}\p{Han}]/
- ( q|(sa.original LIKE ? OR translate(sa.original,' ','') LIKE ?)| => [ '%'.$o{search}.'%', ($o{search} =~ s/\s+//gr).'%' ] ) :
- ( '(sa.name ILIKE ? OR sa.original ILIKE ?)' => [ map '%'.$o{search}.'%', 1..2 ] ) : (),
- $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (),
- defined $o{char} && !$o{char} ?
- ( '(ASCII(sa.name) < 97 OR ASCII(sa.name) > 122) AND (ASCII(sa.name) < 65 OR ASCII(sa.name) > 90)' => 1 ) : (),
- );
-
- my $select = 's.id, sa.aid, sa.name, sa.original, s.gender, s.lang';
-
- my($order, @order) = ('sa.name');
- if($o{sort} && $o{sort} eq 'search') {
- $order = 'least(substr_score(sa.name, ?), substr_score(sa.original, ?)), sa.name';
- @order = ($o{search}) x 2;
- }
-
- my($r, $np) = $self->dbPage(\%o, qq|
- SELECT !s
- FROM staff s
- JOIN staff_alias sa ON sa.id = s.id
- !W
- ORDER BY $order|,
- $select, \%where, @order
- );
-
- return wantarray ? ($r, $np) : $r;
-}
-
-
-1;
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
deleted file mode 100644
index 1104bad8..00000000
--- a/lib/VNDB/DB/Tags.pm
+++ /dev/null
@@ -1,162 +0,0 @@
-
-package VNDB::DB::Tags;
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw|dbTagGet dbTTTree dbTagStats|;
-
-
-# %options->{ id noid name search state searchable applicable page results what sort reverse }
-# what: parents childs(n) aliases addedby
-# sort: id name added items search
-sub dbTagGet {
- my $self = shift;
- my %o = (
- page => 1,
- results => 10,
- what => '',
- @_
- );
-
- $o{search} =~ s/%//g if $o{search};
-
- my %where = (
- $o{id} ? (
- 't.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
- $o{noid} ? (
- 't.id <> ?' => $o{noid} ) : (),
- $o{name} ? (
- 't.id = (SELECT id FROM tags LEFT JOIN tags_aliases ON id = tag WHERE lower(name) = ? OR lower(alias) = ? LIMIT 1)' => [ lc $o{name}, lc $o{name} ]) : (),
- defined $o{state} && $o{state} != -1 ? (
- 't.state = ?' => $o{state} ) : (),
- !defined $o{state} && !$o{id} && !$o{name} ? (
- 't.state <> 1' => 1 ) : (),
- $o{search} ? (
- 't.id IN (SELECT id FROM tags LEFT JOIN tags_aliases ON id = tag WHERE name ILIKE ? OR alias ILIKE ?)' => [ "%$o{search}%", "%$o{search}%" ] ) : (),
- defined $o{searchable} ? ('t.searchable = ?' => $o{searchable}?1:0 ) : (),
- defined $o{applicable} ? ('t.applicable = ?' => $o{applicable}?1:0 ) : (),
- );
- my @select = (
- qw|t.id t.searchable t.applicable t.name t.description t.state t.cat t.c_items t.defaultspoil|,
- q|extract('epoch' from t.added) as added|,
- $o{what} =~ /addedby/ ? (VNWeb::DB::sql_user()) : (),
- );
- my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : ();
-
- my $order = sprintf {
- id => 't.id %s',
- name => 't.name %s',
- added => 't.added %s',
- items => 't.c_items %s',
- search=> 'substr_score(t.name, ?) ASC, t.name %s', # Assigning a matching score for aliases is also possible, but more involved
- }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
- my @order = $o{sort} && $o{sort} eq 'search' ? ($o{search}) : ();
-
-
- my($r, $np) = $self->dbPage(\%o, qq|
- SELECT !s
- FROM tags t
- !s
- !W
- ORDER BY $order|,
- join(', ', @select), join(' ', @join), \%where, @order
- );
-
- if(@$r && $o{what} =~ /aliases/) {
- my %r = map {
- $_->{aliases} = [];
- ($_->{id}, $_->{aliases})
- } @$r;
-
- push @{$r{$_->{tag}}}, $_->{alias} for (@{$self->dbAll(q|
- SELECT tag, alias FROM tags_aliases WHERE tag IN(!l)|, [ keys %r ]
- )});
- }
-
- if($o{what} =~ /parents\((\d+)\)/) {
- $_->{parents} = $self->dbTTTree(tag => $_->{id}, $1, 1) for(@$r);
- }
-
- if($o{what} =~ /childs\((\d+)\)/) {
- $_->{childs} = $self->dbTTTree(tag => $_->{id}, $1) for(@$r);
- }
-
- return wantarray ? ($r, $np) : $r;
-}
-
-
-# Walks the tag/trait tree
-# type = tag | trait
-# id = tag to start with, or 0 to start with top-level tags
-# lvl = max. recursion level
-# back = false for parent->child, true for child->parent
-# Returns: [ { id, name, c_items, sub => [ { id, name, c_items, sub => [..] }, .. ] }, .. ]
-sub dbTTTree {
- my($self, $type, $id, $lvl, $back) = @_;
- $lvl ||= 15;
- my $xtra = $type eq 'trait' ? ', "order"' : '';
- my $xtra2 = $type eq 'trait' ? ', t."order"' : '';
- my $r = $self->dbAll(qq|
- WITH RECURSIVE thetree(lvl, id, parent, name, c_items) AS (
- SELECT ?::integer, id, 0, name, c_items$xtra
- FROM ${type}s
- !W
- UNION ALL
- SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_items$xtra2
- FROM thetree tt
- JOIN ${type}s_parents tp ON !s
- JOIN ${type}s t ON !s
- WHERE tt.lvl > 0
- AND t.state = 2
- ) SELECT DISTINCT id, parent, name, c_items$xtra FROM thetree ORDER BY name|, $lvl,
- $id ? {'id = ?' => $id} : {"NOT EXISTS(SELECT 1 FROM ${type}s_parents WHERE $type = id)" => 1, 'state = 2' => 1},
- !$back ? ('tp.parent = tt.id', "t.id = tp.$type") : ("tp.$type = tt.id", 't.id = tp.parent')
- );
-
- my %pars; # parent-id -> [ child-object, .. ]
- push @{$pars{$_->{parent}}}, $_ for(@$r);
- $_->{'sub'} = $pars{$_->{id}} || [] for(@$r);
- my @r = grep !delete($_->{parent}), @$r;
- return $id ? $r[0]{'sub'} : \@r;
-}
-
-
-# Fetch all tags related to a VN
-# Argument: %options->{ vid minrating state results what page sort reverse }
-# sort: name, rating
-sub dbTagStats {
- my($self, %o) = @_;
- $o{results} ||= 10;
- $o{page} ||= 1;
-
- my $rating = 'avg(CASE WHEN tv.ignore THEN NULL ELSE tv.vote END)';
- my $order = sprintf {
- name => 't.name %s',
- rating => "$rating %s",
- }->{ $o{sort}||'name' }, $o{reverse} ? 'DESC' : 'ASC';
-
- my %where = (
- 'tv.vid = ?' => $o{vid},
- defined $o{state} ? ('t.state = ?', $o{state}) : (),
- );
-
- my($r, $np) = $self->dbPage(\%o, qq|
- SELECT t.id, t.name, t.cat, count(*) as cnt, $rating as rating,
- COALESCE(avg(CASE WHEN tv.ignore THEN NULL ELSE tv.spoiler END), t.defaultspoil) as spoiler,
- bool_or(tv.ignore) AS overruled
- FROM tags t
- JOIN tags_vn tv ON tv.tag = t.id
- !W
- GROUP BY t.id, t.name, t.cat
- !s
- ORDER BY !s|,
- \%where, defined $o{minrating} ? "HAVING $rating > $o{minrating}" : '', $order
- );
-
- return wantarray ? ($r, $np) : $r;
-}
-
-1;
-
diff --git a/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm
deleted file mode 100644
index ac0e81b4..00000000
--- a/lib/VNDB/DB/Traits.pm
+++ /dev/null
@@ -1,86 +0,0 @@
-
-package VNDB::DB::Traits;
-
-# This module is for a large part a copy of VNDB::DB::Tags. I could have chosen
-# to modify that module to work for both traits and tags but that would have
-# complicated the code, so I chose to maintain two versions with similar
-# functionality instead.
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw|dbTraitGet|;
-
-
-# Options: id noid search name state searchable applicable what results page sort reverse
-# what: parents childs(n) addedby
-# sort: id name name added items search
-sub dbTraitGet {
- my $self = shift;
- my %o = (
- page => 1,
- results => 10,
- what => '',
- @_,
- );
-
- $o{search} =~ s/%//g if $o{search};
-
- my %where = (
- $o{id} ? ( 't.id IN(!l)' => [ ref($o{id}) ? $o{id} : [$o{id}] ]) : (),
- $o{group} ? ( 't.group = ?' => $o{group} ) : (),
- $o{noid} ? ( 't.id <> ?' => $o{noid} ) : (),
- defined $o{state} && $o{state} != -1 ? (
- 't.state = ?' => $o{state} ) : (),
- !defined $o{state} && !$o{id} && !$o{name} ? (
- 't.state = 2' => 1 ) : (),
- $o{search} ? (
- '(t.name ILIKE ? OR t.alias ILIKE ?)' => [ "%$o{search}%", "%$o{search}%" ] ) : (),
- $o{name} ? ( # TODO: This is terribly ugly, use an aliases table.
- q{(LOWER(t.name) = LOWER(?) OR t.alias ~ ('(!sin)^'||?||'$'))} => [ $o{name}, '?', quotemeta $o{name} ] ) : (),
- defined $o{applicable} ? ('t.applicable = ?' => $o{applicable}?1:0 ) : (),
- defined $o{searchable} ? ('t.searchable = ?' => $o{searchable}?1:0 ) : (),
- );
-
- my @select = (
- qw|t.id t.searchable t.applicable t.name t.description t.state t.alias t."group" t."order" t.sexual t.c_items t.defaultspoil|,
- 'tg.name AS groupname', 'tg."order" AS grouporder', q|extract('epoch' from t.added) as added|,
- $o{what} =~ /addedby/ ? (VNWeb::DB::sql_user()) : (),
- );
- my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : ();
- push @join, 'LEFT JOIN traits tg ON tg.id = t."group"';
-
- my $order = sprintf {
- id => 't.id %s',
- name => 't.name %s',
- group => 'tg."order" %s, t.name %1$s',
- added => 't.added %s',
- items => 't.c_items %s',
- search=> 'substr_score(t.name, ?) ASC, t.name %s', # Can't score aliases at the moment
- }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
- my @order = $o{sort} && $o{sort} eq 'search' ? ($o{search}) : ();
-
- my($r, $np) = $self->dbPage(\%o, qq|
- SELECT !s
- FROM traits t
- !s
- !W
- ORDER BY $order|,
- join(', ', @select), join(' ', @join), \%where, @order,
- );
-
- if($o{what} =~ /parents\((\d+)\)/) {
- $_->{parents} = $self->dbTTTree(trait => $_->{id}, $1, 1) for(@$r);
- }
-
- if($o{what} =~ /childs\((\d+)\)/) {
- $_->{childs} = $self->dbTTTree(trait => $_->{id}, $1) for(@$r);
- }
-
- return wantarray ? ($r, $np) : $r;
-}
-
-
-1;
-
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
deleted file mode 100644
index 85654180..00000000
--- a/lib/VNDB/DB/Users.pm
+++ /dev/null
@@ -1,49 +0,0 @@
-
-package VNDB::DB::Users;
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw|
- dbUserGet
-|;
-
-
-# %options->{ uid results page what }
-# sort: username registered votes changes tags
-sub dbUserGet {
- my $s = shift;
- my %o = (
- page => 1,
- results => 10,
- what => '',
- @_
- );
-
- my %where = (
- $o{uid} && !ref($o{uid}) ? (
- 'id = ?' => $o{uid} ) : (),
- $o{uid} && ref($o{uid}) ? (
- 'id IN(!l)' => [ $o{uid} ]) : (),
- );
-
- my @select = (
- qw|id username c_votes c_changes c_tags|,
- VNWeb::DB::sql_user(), # XXX: This duplicates id and username, but updating all the code isn't going to be easy
- q|extract('epoch' from registered) as registered|,
- );
-
- my($r, $np) = $s->dbPage(\%o, q|
- SELECT !s
- FROM users u
- !W
- ORDER BY id DESC|,
- join(', ', @select), \%where
- );
-
- return wantarray ? ($r, $np) : $r;
-}
-
-1;
-
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
deleted file mode 100644
index 668b7fec..00000000
--- a/lib/VNDB/DB/VN.pm
+++ /dev/null
@@ -1,257 +0,0 @@
-
-package VNDB::DB::VN;
-
-use strict;
-use warnings;
-use v5.10;
-use TUWF 'sqlprint';
-use POSIX 'strftime';
-use Exporter 'import';
-use VNDB::Func 'normalize_query', 'gtintype';
-
-our @EXPORT = qw|dbVNGet dbVNGetRev|;
-
-
-# Options: id, char, search, gtin, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
-# hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort,
-# reverse, inc_hidden, date_before, date_after, released, release, character
-# What: extended anime staff seiyuu relations rating ranking vnlist
-# Note: vnlist is ignored (no db search) unless a user is logged in
-# Sort: id rel pop rating title tagscore rand
-sub dbVNGet {
- my($self, %o) = @_;
- $o{results} ||= 10;
- $o{page} ||= 1;
- $o{what} ||= '';
- $o{sort} ||= 'title';
- $o{tagspoil} //= 2;
-
- # user input that is literally added to the query should be checked...
- die "Invalid input for tagspoil or tag_inc at dbVNGet()\n" if
- grep !defined($_) || $_!~/^\d+$/, $o{tagspoil},
- !$o{tag_inc} ? () : (ref($o{tag_inc}) ? @{$o{tag_inc}} : $o{tag_inc});
-
- my $uid = $self->authInfo->{id};
-
- $o{gtin} = delete $o{search} if $o{search} && $o{search} =~ /^\d+$/ && gtintype(local $_ = $o{search});
-
- my @where = (
- $o{id} ? (
- 'v.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
- $o{char} ? (
- 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (),
- defined $o{char} && !$o{char} ? (
- '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
- defined $o{length} ? (
- 'v.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (),
- $o{lang} ? (
- 'v.c_languages && ARRAY[!l]::language[]' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (),
- $o{olang} ? (
- 'v.c_olang && ARRAY[!l]::language[]' => [ ref $o{olang} ? $o{olang} : [$o{olang}] ]) : (),
- $o{plat} ? (
- 'v.c_platforms && ARRAY[!l]::platform[]' => [ ref $o{plat} ? $o{plat} : [$o{plat}] ]) : (),
- defined $o{hasani} ? (
- '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
- defined $o{hasshot} ? (
- '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.id = v.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (),
- $o{tag_inc} ? (
- 'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
- [ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (),
- $o{tag_exc} ? (
- 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ ref $o{tag_exc} ? $o{tag_exc} : [$o{tag_exc}] ] ) : (),
- $o{search} ? (
- map +('v.c_search like ?', "%$_%"), normalize_query($o{search})) : (),
- $o{gtin} ? (
- 'v.id IN(SELECT irv.vid FROM releases_vn irv JOIN releases ir ON ir.id = irv.id WHERE ir.gtin = ?)' => $o{gtin}) : (),
- $o{staff_inc} ? ( 'v.id IN(SELECT ivs.id FROM vn_staff ivs JOIN staff_alias isa ON isa.aid = ivs.aid WHERE isa.id IN(!l))' => [ ref $o{staff_inc} ? $o{staff_inc} : [$o{staff_inc}] ] ) : (),
- $o{staff_exc} ? ( 'v.id NOT IN(SELECT ivs.id FROM vn_staff ivs JOIN staff_alias isa ON isa.aid = ivs.aid WHERE isa.id IN(!l))' => [ ref $o{staff_exc} ? $o{staff_exc} : [$o{staff_exc}] ] ) : (),
- $uid && $o{ul_notblack} ? (
- 'v.id NOT IN(SELECT vid FROM ulist_vns_labels WHERE uid = ? AND lbl = 6)' => $uid ) : (),
- $uid && defined $o{ul_onwish} ? (
- 'v.id !s IN(SELECT vid FROM ulist_vns_labels WHERE uid = ? AND lbl = 5)' => [ $o{ul_onwish} ? '' : 'NOT', $uid ] ) : (),
- $uid && defined $o{ul_voted} ? (
- 'v.id !s IN(SELECT vid FROM ulist_vns_labels WHERE uid = ? AND lbl = 7)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (),
- $uid && defined $o{ul_onlist} ? (
- 'v.id !s IN(SELECT vid FROM ulist_vns WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (),
- !$o{id} && !$o{inc_hidden} ? (
- 'v.hidden = FALSE' => 0 ) : (),
- # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
- $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort|tagspoil)$/, keys %o) ? (
- 'v.id IN(SELECT floor(random() * last_value)::integer FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM vn) s1 LIMIT 20)' ) : (),
- defined $o{date_before} ? ( 'v.c_released <= ?' => $o{date_before} ) : (),
- defined $o{date_after} ? ( 'v.c_released >= ?' => $o{date_after} ) : (),
- defined $o{released} ? ( 'v.c_released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
- );
-
- if($o{release}) {
- my($q, @p) = sqlprint
- 'v.id IN(SELECT rv.vid FROM releases r JOIN releases_vn rv ON rv.id = r.id !W)',
- [ 'NOT r.hidden' => 1, $self->dbReleaseFilters(%{$o{release}}), ];
- push @where, $q, \@p;
- }
- if($o{character}) {
- my($q, @p) = sqlprint
- 'v.id IN(SELECT cv.vid FROM chars c JOIN chars_vns cv ON cv.id = c.id !W)',
- [ 'NOT c.hidden' => 1, $self->dbCharFilters(%{$o{character}}) ];
- push @where, $q, \@p;
- }
-
- my @join = (
- $uid && $o{what} =~ /vnlist/ ? ("LEFT JOIN (
- SELECT irv.vid, COUNT(*) AS userlist_all,
- SUM(CASE WHEN irl.status = 2 THEN 1 ELSE 0 END) AS userlist_obtained
- FROM rlists irl
- JOIN releases_vn irv ON irv.id = irl.rid
- WHERE irl.uid = $uid
- GROUP BY irv.vid
- ) AS vnlist ON vnlist.vid = v.id") : (),
- );
-
- my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc};
- 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_olang::text[] v.c_platforms::text[] v.title v.original|,
- $o{what} =~ /extended/ ? (
- qw|v.alias v.length v.desc v.l_wp v.l_encubed v.l_renai v.l_wikidata|, 'coalesce(vndbid_num(v.image),0) as image' ) : (),
- $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
- $o{what} =~ /ranking/ ? (
- '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(v.c_popularity, 0.0)) AS p_ranking',
- '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(v.c_rating, 0.0)) AS r_ranking',
- ) : (),
- $uid && $o{what} =~ /vnlist/ ? (qw|vnlist.userlist_all vnlist.userlist_obtained|) : (),
- # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000)
- $tag_ids ?
- qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tagspoil} GROUP BY tvh.vid) AS tagscore| : (),
- );
-
- no if $] >= 5.022, warnings => 'redundant';
- my $order = sprintf {
- id => 'v.id %s',
- rel => 'v.c_released %s, v.title ASC',
- pop => 'v.c_popularity %s NULLS LAST',
- rating => 'v.c_rating %s NULLS LAST',
- title => 'v.title %s',
- tagscore => 'tagscore %s, v.title ASC',
- rand => 'RANDOM()',
- }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
-
- my($r, $np) = $self->dbPage(\%o, q|
- SELECT !s
- FROM vn v
- !s
- !W
- ORDER BY !s|,
- join(', ', @select), join(' ', @join), \@where, $order,
- );
-
- return _enrich($self, $r, $np, 0, $o{what});
-}
-
-
-sub dbVNGetRev {
- my $self = shift;
- my %o = (what => '', @_);
-
- $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'v\' AND itemid = ?', $o{id})->{rev};
-
- # XXX: Too much duplication with code in dbVNGet() here. Can we combine some code here?
- my $uid = $self->authInfo->{id};
-
- my $select = 'c.itemid AS id, vo.c_released, vo.c_languages::text[], vo.c_olang::text[], vo.c_platforms::text[], v.title, v.original';
- $select .= ', extract(\'epoch\' from c.added) as added, c.comments, c.rev, c.ihid, c.ilock, '.VNWeb::DB::sql_user();
- $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, coalesce(vndbid_num(v.image), 0) as image, 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/;
-
- my $r = $self->dbAll(q|
- SELECT !s
- FROM changes c
- JOIN vn vo ON vo.id = c.itemid
- JOIN vn_hist v ON v.chid = c.id
- JOIN users u ON u.id = c.requester
- WHERE c.type = 'v' AND c.itemid = ? AND c.rev = ?|,
- $select, $o{id}, $o{rev}
- );
-
- return _enrich($self, $r, 0, 1, $o{what});
-}
-
-
-sub _enrich {
- my($self, $r, $np, $rev, $what) = @_;
-
- if(@$r && $what =~ /anime|relations|staff|seiyuu/) {
- my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
- my %r = map {
- $r->[$_]{anime} = [];
- $r->[$_]{credits} = [];
- $r->[$_]{seiyuu} = [];
- $r->[$_]{relations} = [];
- ($r->[$_]{$col}, $_)
- } 0..$#$r;
-
- if($what =~ /staff/) {
- push(@{$r->[$r{ delete $_->{xid} }]{credits}}, $_) for (@{$self->dbAll("
- SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, vs.role, vs.note
- FROM vn_staff$hist vs
- JOIN staff_alias sa ON vs.aid = sa.aid
- JOIN staff s ON s.id = sa.id
- WHERE vs.$colname IN(!l)
- ORDER BY vs.role ASC, sa.name ASC",
- [ keys %r ]
- )});
- }
-
- if($what =~ /seiyuu/) {
- # The seiyuu query needs the VN id to get the VN<->Char spoiler level.
- # Obtaining this ID is different when using the hist table.
- my($vid, $join) = $rev ? ('h.itemid', 'JOIN changes h ON h.id = vs.chid') : ('vs.id', '');
- push(@{$r->[$r{ delete $_->{xid} }]{seiyuu}}, $_) for (@{$self->dbAll("
- SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, c.id AS cid, c.name AS cname, vs.note,
- (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = $vid AND cv.id = c.id) AS spoil
- FROM vn_seiyuu$hist vs
- JOIN staff_alias sa ON vs.aid = sa.aid
- JOIN staff s ON s.id = sa.id
- JOIN chars c ON c.id = vs.cid
- $join
- WHERE vs.$colname IN(!l)
- ORDER BY c.name",
- [ keys %r ]
- )});
- }
-
- if($what =~ /anime/) {
- push(@{$r->[$r{ delete $_->{xid} }]{anime}}, $_) for (@{$self->dbAll("
- SELECT va.$colname AS xid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch
- FROM vn_anime$hist va
- JOIN anime a ON va.aid = a.id
- WHERE va.$colname IN(!l)",
- [ keys %r ]
- )});
- }
-
- if($what =~ /relations/) {
- push(@{$r->[$r{ delete $_->{xid} }]{relations}}, $_) for(@{$self->dbAll("
- SELECT rel.$colname AS xid, rel.vid AS id, rel.relation, rel.official, v.title, v.original
- FROM vn_relations$hist rel
- JOIN vn v ON rel.vid = v.id
- WHERE rel.$colname IN(!l)",
- [ keys %r ]
- )});
- }
- }
-
- VNWeb::DB::enrich_flatten(vnlist_labels => id => vid => sub { VNWeb::DB::sql('
- SELECT uvl.vid, ul.label
- FROM ulist_vns_labels uvl
- JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
- WHERE uvl.uid =', \$self->authInfo->{id}, 'AND uvl.vid IN', $_[0], '
- ORDER BY CASE WHEN ul.id < 10 THEN ul.id ELSE 10 END, ul.label'
- )}, $r) if $what =~ /vnlist/ && $self->authInfo->{id};
-
- return wantarray ? ($r, $np) : $r;
-}
-
-
-1;