summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Misc.pm
blob: 8927e1ec7ee8ed09635130f0a4fe374950d1a6ce (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

package VNDB::DB::Misc;

use strict;
use warnings;
use Exporter 'import';

our @EXPORT = qw|
  dbStats dbItemEdit dbRevisionGet dbRandomQuote
|;


# Returns: hashref, key = section, value = number of (visible) entries
# Sections: vn, producers, releases, users, threads, posts
sub dbStats {
  my $s = shift;
  return { map {
    $_->{section} eq 'threads_posts' ? 'posts' : $_->{section}, $_->{count}
  } @{$s->dbAll('SELECT * FROM stats_cache')}};
}


# Inserts a new revision into the database
# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + db[item]RevisionInsert }
#  revision id = changes.id of the revision this edit is based on, undef to create a new DB item
# Returns: { iid, cid, rev }
sub dbItemEdit {
  my($self, $type, $oid, %o) = @_;

  my $fun = {qw|v vn r release p producer c char s staff|}->{$type};
  $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
  $self->dbExec('UPDATE edit_revision !H', {
    'requester = ?' => $o{uid}||$self->authInfo->{id},
    'ip = ?'        => $self->reqIP,
    'comments = ?'  => $o{editsum},
    exists($o{ihid})  ? ('ihid = ?'  => $o{ihid} ?1:0) : (),
    exists($o{ilock}) ? ('ilock = ?' => $o{ilock}?1:0) : (),
  });

  $self->dbVNRevisionInsert(      \%o) if $type eq 'v';
  $self->dbProducerRevisionInsert(\%o) if $type eq 'p';
  $self->dbReleaseRevisionInsert( \%o) if $type eq 'r';
  $self->dbCharRevisionInsert(    \%o) if $type eq 'c';
  $self->dbStaffRevisionInsert(   \%o) if $type eq 's';

  return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun);
}


# Options: type, iid, uid, auto, hidden, edit, page, results, what, releases
# what: item user
# Not very fast in each situation. Can be further optimized by: putting indexes
# on *_rev.?id, or by caching iid, ititle and ihidden in the changes table.
sub dbRevisionGet {
  my($self, %o) = @_;
  $o{results} ||= 10;
  $o{page} ||= 1;
  $o{auto} ||= 0;   # 0:show, -1:only, 1:hide
  $o{hidden} ||= 0;
  $o{edit} ||= 0;   # 0:both, -1:new, 1:edits
  $o{what} ||= '';
  $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid};

  my %tables = qw|v vn r releases p producers c chars s staff|;
  # what types should we join?
  my @types = (
    !$o{type} ? qw(v r p c s) :
    ref($o{type}) ? @{$o{type}} :
    $o{type} ne 'v' ? $o{type} :
    $o{releases} ? ('v', 'r') : 'v'
  );

  my %where = (
    $o{releases} ? (
      q{((h.type = 'v' AND vr.vid = ?) OR (h.type = 'r' AND h.id = ANY(ARRAY(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{iid}, $o{iid}],
    ) : (
      $o{type} ? (
        'h.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (),
      $o{iid} ? (
        '!sr.!sid = ?' => [ $o{type}, $o{type}, $o{iid} ] ) : (),
    ),
    $o{uid} ? (
      'h.requester = ?' => $o{uid} ) : (),
    $o{auto} ? (
      'h.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (),
    $o{hidden} ? (
      '('.join(' OR ', map sprintf('%s.hidden IS NOT NULL AND %s %1$s.hidden', $_, $o{hidden} == 1 ? 'NOT' : ''), @types).')' => 1 ) : (),
    $o{edit} ? (
      'h.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (),
  );

  my @join = (
    $o{iid} || $o{what} =~ /item/ || $o{hidden} || $o{releases} ? (
      map sprintf(q|LEFT JOIN %s_rev %sr ON h.type = '%2$s' AND h.id = %2$sr.id|, $tables{$_}, $_), @types
    ) : (),
    $o{hidden} ? (
      map sprintf(q|LEFT JOIN %s %s ON h.type = '%2$s' AND %2$sr.%2$sid = %2$s.id|, $tables{$_}, $_), @types
    ) : (),
    $o{what} =~ /user/ ? 'JOIN users u ON h.requester = u.id' : (),
  );
  push @join, 'LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid' if grep /s/, @types;

  my %tcolumns = qw(v vr.title r rr.title p pr.name c cr.name s sa.name);
  my @select = (
    qw|h.id h.type h.requester h.comments h.rev|,
    q|extract('epoch' from h.added) as added|,
    $o{what} =~ /user/ ? 'u.username' : (),
    $o{what} =~ /item/ ? (
      'COALESCE('.join(', ', map "${_}r.${_}id", @types).') AS iid',
      'COALESCE('.join(', ', map $tcolumns{$_}, @types).') AS ititle',
      'COALESCE('.join(', ', map /s/ ? 'sa.original' : "${_}r.original", @types).') AS ioriginal',
    ) : (),
  );

  my($r, $np) = $self->dbPage(\%o, q|
    SELECT !s
      FROM changes h
      !s
      !W
      ORDER BY h.id DESC|,
    join(', ', @select), join(' ', @join), \%where
  );
  return wantarray ? ($r, $np) : $r;
}


# Returns a random quote (hashref with keys = vid, quote)
sub dbRandomQuote {
  return $_[0]->dbRow(q|
    SELECT vid, quote
      FROM quotes
      ORDER BY RANDOM()
      LIMIT 1|);
}




1;