summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Releases.pm
blob: d17bfd00257a339aa87b8dd5201c1a501aeb3d9c (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222

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 r.resolution IN(!l)' => [ 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.resolution 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.resolution %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.resolution, 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;