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

package VNDB::DB::Producers;

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

our @EXPORT = qw|dbProducerGet dbProducerEdit dbProducerAdd|;


# options: results, page, id, search, char, rev
# what: extended changes vn relations relgraph
sub dbProducerGet {
  my $self = shift;
  my %o = (
    results => 10,
    page => 1,
    what => '',
    @_
  );

  $o{search} =~ s/%//g if $o{search};

  my %where = (
    !$o{id} && !$o{rev} ? (
      'p.hidden = FALSE' => 1 ) : (),
    $o{id} ? (
      'p.id = ?' => $o{id} ) : (),
    $o{search} ? (
      '(pr.name ILIKE ? OR pr.original ILIKE ? OR pr.alias ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..3 ] ) : (),
    $o{char} ? (
      'LOWER(SUBSTR(pr.name, 1, 1)) = ?' => $o{char} ) : (),
    defined $o{char} && !$o{char} ? (
      '(ASCII(pr.name) < 97 OR ASCII(pr.name) > 122) AND (ASCII(pr.name) < 65 OR ASCII(pr.name) > 90)' => 1 ) : (),
    $o{rev} ? (
      'c.rev = ?' => $o{rev} ) : (),
  );

  my @join;
  push @join, $o{rev} ? 'JOIN producers p ON p.id = pr.pid' : 'JOIN producers p ON pr.id = p.latest';
  push @join, 'JOIN changes c ON c.id = pr.id' if $o{what} =~ /changes/ || $o{rev};
  push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/;
  push @join, 'JOIN relgraphs pg ON pg.id = p.rgraph' if $o{what} =~ /relgraph/;

  my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph';
  $select .= ', pr.desc, pr.alias, pr.website, p.hidden, p.locked' if $o{what} =~ /extended/;
  $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev| if $o{what} =~ /changes/;
  $select .= ', pg.svg' if $o{what} =~ /relgraph/;

  my($r, $np) = $self->dbPage(\%o, q|
    SELECT !s
      FROM producers_rev pr
      !s
      !W
      ORDER BY pr.name ASC|,
    $select, join(' ', @join), \%where,
  );

  if(@$r && $o{what} =~ /vn/) {
    my %r = map {
      $r->[$_]{vn} = [];
      ($r->[$_]{id}, $_)
    } 0..$#$r;

    push @{$r->[$r{$_->{pid}}]{vn}}, $_ for (@{$self->dbAll(q|
      SELECT MAX(vp.pid) AS pid, v.id, MAX(vr.title) AS title, MAX(vr.original) AS original, MIN(rr.released) AS date,
          MAX(CASE WHEN vp.developer = true THEN 1 ELSE 0 END) AS developer, MAX(CASE WHEN vp.publisher = true THEN 1 ELSE 0 END) AS publisher
        FROM releases_producers vp
        JOIN releases_rev rr ON rr.id = vp.rid
        JOIN releases r ON r.latest = rr.id
        JOIN releases_vn rv ON rv.rid = rr.id
        JOIN vn v ON v.id = rv.vid
        JOIN vn_rev vr ON vr.id = v.latest
        WHERE vp.pid IN(!l)
          AND v.hidden = FALSE
          AND r.hidden = FALSE
        GROUP BY v.id
        ORDER BY date|,
      [ keys %r ]
    )});
  }

  if(@$r && $o{what} =~ /relations/) {
    my %r = map {
      $r->[$_]{relations} = [];
      ($r->[$_]{cid}, $_)
    } 0..$#$r;

    push @{$r->[$r{$_->{pid1}}]{relations}}, $_ for(@{$self->dbAll(q|
      SELECT rel.pid1, rel.pid2 AS id, rel.relation, pr.name, pr.original
        FROM producers_relations rel
        JOIN producers p ON rel.pid2 = p.id
        JOIN producers_rev pr ON p.latest = pr.id
        WHERE rel.pid1 IN(!l)|,
      [ keys %r ]
    )});
  }

  return wantarray ? ($r, $np) : $r;
}


# arguments: id, %options ->( editsum uid + insert_rev )
# returns: ( local revision, global revision )
sub dbProducerEdit {
  my($self, $pid, %o) = @_;
  my($rev, $cid) = $self->dbRevisionInsert('p', $pid, $o{editsum}, $o{uid});
  insert_rev($self, $cid, $pid, \%o);
  return ($rev, $cid);
}


# arguments: %options ->( editsum uid + insert_rev )
# returns: ( item id, global revision )
sub dbProducerAdd {
  my($self, %o) = @_;
  my($pid, $cid) = $self->dbItemInsert('p', $o{editsum}, $o{uid});
  insert_rev($self, $cid, $pid, \%o);
  return ($pid, $cid);
}


# helper function, inserts a producer revision
# Arguments: global revision, item id, { columns in producers_rev }, relations
sub insert_rev {
  my($self, $cid, $pid, $o) = @_;
  $self->dbExec(q|
    INSERT INTO producers_rev (id, pid, name, original, website, type, lang, "desc", alias)
      VALUES (!l)|,
    [ $cid, $pid, @$o{qw| name original website type lang desc alias|} ]
  );

  $self->dbExec(q|
    INSERT INTO producers_relations (pid1, pid2, relation)
      VALUES (?, ?, ?)|,
    $cid, $_->[1], $_->[0]
  ) for (@{$o->{relations}});
}


1;