diff options
Diffstat (limited to 'lib/VNDB/DB/VN.pm')
-rw-r--r-- | lib/VNDB/DB/VN.pm | 282 |
1 files changed, 282 insertions, 0 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm new file mode 100644 index 00000000..df2e0a03 --- /dev/null +++ b/lib/VNDB/DB/VN.pm @@ -0,0 +1,282 @@ + +package VNDB::DB::VN; + +use strict; +use warnings; +use Exporter 'import'; +use VNDB::Func 'gtintype'; + +our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; + + +# Options: id, rev, char, search, cati, cate, lang, platform, results, page, order, what +# What: extended categories anime relations screenshots relgraph changes +sub dbVNGet { + my($self, %o) = @_; + $o{results} ||= 10; + $o{page} ||= 1; + $o{order} ||= 'vr.title ASC'; + $o{what} ||= ''; + + my %where = ( + $o{id} ? ( + 'v.id = ?' => $o{id} ) : (), + $o{rev} ? ( + 'c.rev = ?' => $o{rev} ) : (), + $o{char} ? ( + 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (), + defined $o{char} && !$o{char} ? ( + '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + $o{cati} && @{$o{cati}} ? ( q| + v.id IN(SELECT iv.id + FROM vn_categories ivc + JOIN vn iv ON iv.latest = ivc.vid + WHERE cat IN(!l) + GROUP BY iv.id + HAVING COUNT(cat) = ?)| => [ $o{cati}, $#{$o{cati}}+1 ] ) : (), + $o{cate} && @{$o{cate}} ? ( q| + v.id NOT IN(SELECT iv.id + FROM vn_categories ivc + JOIN vn iv ON iv.latest = ivc.vid + WHERE cat IN(!l) + GROUP BY iv.id)| => [ $o{cate} ] ) : (), + $o{lang} && @{$o{lang}} ? ( + '('.join(' OR ', map "v.c_languages ILIKE '%%$_%%'", @{$o{lang}}).')' => 1 ) : (), + $o{platform} && @{$o{platform}} ? ( + '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (), + # don't fetch hidden items unless we ask for an ID + !$o{id} && !$o{rev} ? ( + 'v.hidden = FALSE' => 0 ) : (), + ); + + if($o{search}) { + my @w; + for (split /[ -,]/, $o{search}) { + s/%//g; + next if length($_) < 2; + if(/^\d+$/ && gtintype($_)) { + push @w, 'irr.gtin = ?', $_; + } else { + $_ = "%$_%"; + push @w, '(ivr.title ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)', + [ $_, $_, $_, $_ ]; + } + } + $where{ q| + v.id IN(SELECT iv.id + FROM vn iv + JOIN vn_rev ivr ON iv.latest = ivr.id + LEFT JOIN releases_vn irv ON irv.vid = iv.id + LEFT JOIN releases_rev irr ON irr.id = irv.rid + LEFT JOIN releases ir ON ir.latest = irr.id + !W + GROUP BY iv.id)| + } = [ \@w ] if @w; + } + + my @join = ( + $o{rev} ? + 'JOIN vn v ON v.id = vr.vid' : + 'JOIN vn v ON vr.id = v.latest', + $o{rev} || $o{what} =~ /changes/ ? + 'JOIN changes c ON c.id = vr.id' : (), + $o{what} =~ /changes/ ? + 'JOIN users u ON u.id = c.requester' : (), + $o{what} =~ /relgraph/ ? + 'JOIN relgraph rg ON rg.id = v.rgraph' : (), + ); + + my @select = ( + qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid', + $o{what} =~ /extended/ ? ( + qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (), + $o{what} =~ /changes/ ? ( + qw|c.added c.requester c.comments v.latest u.username c.rev c.causedby|) : (), + $o{what} =~ /relgraph/ ? 'rg.cmap' : (), + ); + + my($r, $np) = $self->dbPage(\%o, q| + SELECT !s + FROM vn_rev vr + !s + !W + ORDER BY !s|, + join(', ', @select), join(' ', @join), \%where, $o{order}, + ); + + if(@$r && $o{what} =~ /(categories|anime|relations|screenshots)/) { + my %r = map { + $r->[$_]{categories} = []; + $r->[$_]{anime} = []; + $r->[$_]{relations} = []; + $r->[$_]{screenshots} = []; + ($r->[$_]{cid}, $_) + } 0..$#$r; + + if($o{what} =~ /categories/) { + push(@{$r->[$r{$_->{vid}}]{categories}}, [ $_->{cat}, $_->{lvl} ]) for (@{$self->dbAll(q| + SELECT vid, cat, lvl + FROM vn_categories + WHERE vid IN(!l)|, + [ keys %r ] + )}); + } + + if($o{what} =~ /anime/) { + push(@{$r->[$r{$_->{vid}}]{anime}}, $_) && delete $_->{vid} for (@{$self->dbAll(q| + SELECT va.vid, a.* + FROM vn_anime va + JOIN anime a ON va.aid = a.id + WHERE va.vid IN(!l)|, + [ keys %r ] + )}); + } + + if($o{what} =~ /relations/) { + push(@{$r->[$r{$_->{vid1}}]{relations}}, { + relation => $_->{relation}, + id => $_->{vid2}, + title => $_->{title}, + original => $_->{original} + }) for(@{$self->dbAll(q| + SELECT rel.vid1, rel.vid2, rel.relation, vr.title, vr.original + FROM vn_relations rel + JOIN vn v ON rel.vid2 = v.id + JOIN vn_rev vr ON v.latest = vr.id + WHERE rel.vid1 IN(!l)|, + [ keys %r ] + )}); + } + + if($o{what} =~ /screenshots/) { + push(@{$r->[$r{$_->{vid}}]{screenshots}}, $_) && delete $_->{vid} for (@{$self->dbAll(q| + SELECT vs.vid, s.id, vs.nsfw, vs.rid, s.width, s.height + FROM vn_screenshots vs + JOIN screenshots s ON vs.scr = s.id + WHERE vs.vid IN(!l) + ORDER BY vs.scr|, + [ keys %r ] + )}); + } + } + + return wantarray ? ($r, $np) : $r; +} + + +# arguments: id, %options ->( editsum uid + insert_rev ) +# returns: ( local revision, global revision ) +sub dbVNEdit { + my($self, $id, %o) = @_; + my($rev, $cid) = $self->dbRevisionInsert(0, $id, $o{editsum}, $o{uid}); + insert_rev($self, $cid, $id, \%o); + return ($rev, $cid); +} + + +# arguments: %options ->( editsum uid + insert_rev ) +# returns: ( item id, global revision ) +sub dbVNAdd { + my($self, %o) = @_; + my($id, $cid) = $self->dbItemInsert(0, $o{editsum}, $o{uid}); + insert_rev($self, $cid, $id, \%o); + return ($id, $cid); +} + + +# helper function, inserts a producer revision +# Arguments: global revision, item id, { columns in producers_rev + categories + anime + relations + screenshots } +# categories = [ [ catid, level ], .. ] +# screenshots = [ [ scrid, nsfw, rid ], .. ] +# relations = [ [ rel, vid ], .. ] +# anime = [ aid, .. ] +sub insert_rev { + my($self, $cid, $vid, $o) = @_; + + $o->{img_nsfw} = $o->{img_nsfw}?1:0; + $self->dbExec(q| + INSERT INTO vn_rev (id, vid, title, original, "desc", alias, image, img_nsfw, length, l_wp, l_encubed, l_renai, l_vnn) + VALUES (!l)|, + [ $cid, $vid, @$o{qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|} ]); + + $self->dbExec(q| + INSERT INTO vn_categories (vid, cat, lvl) + VALUES (?, ?, ?)|, + $cid, $_->[0], $_->[1] + ) for (@{$o->{categories}}); + + $self->dbExec(q| + INSERT INTO vn_screenshots (vid, scr, nsfw, rid) + VALUES (?, ?, ?, ?)|, + $cid, $_->[0], $_->[1]?1:0, $_->[2] + ) for (@{$o->{screenshots}}); + + $self->dbExec(q| + INSERT INTO vn_relations (vid1, vid2, relation) + VALUES (?, ?, ?)|, + $cid, $_->[1], $_->[0] + ) for (@{$o->{relations}}); + + if(@{$o->{anime}}) { + $self->dbExec(q| + INSERT INTO vn_anime (vid, aid) + VALUES (?, ?)|, + $cid, $_ + ) for (@{$o->{anime}}); + + # insert unknown anime + my $a = $self->dbAll(q| + SELECT id FROM anime WHERE id IN(!l)|, + $o->{anime}); + $self->dbExec(q| + INSERT INTO anime (id) VALUES (?)|, $_ + ) for (grep { + my $ia = $_; + !(scalar grep $ia == $_->{id}, @$a) + } @{$o->{anime}}); + } +} + + +# fetches an ID for a new image +sub dbVNImageId { + return shift->dbRow("SELECT nextval('covers_seq') AS ni")->{ni}; +} + + +# Updates the vn.c_ columns +sub dbVNCache { + my($self, @vn) = @_; + $self->dbExec('SELECT update_vncache(?)', $_) for (@vn); +} + + +# insert a new screenshot and return it's ID +# (no arguments required, as Multi is responsible for filling the entry with information) +sub dbScreenshotAdd { + return shift->dbRow(q|INSERT INTO screenshots (status) VALUES(0) RETURNING id|)->{id}; +} + + +# arrayref of screenshot IDs as argument +sub dbScreenshotGet { + return shift->dbAll(q|SELECT * FROM screenshots WHERE id IN(!l)|, shift); +} + + +# Fetch random VN + screenshots +sub dbScreenshotRandom { + return shift->dbAll(q| + SELECT vs.scr, vr.vid, vr.title + FROM vn_screenshots vs + JOIN vn v ON v.latest = vs.vid + JOIN vn_rev vr ON vr.id = v.latest + WHERE vs.nsfw = FALSE + ORDER BY RANDOM() + LIMIT 4| + ); +} + + +1; + |