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

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 dbTraitEdit dbTraitAdd|;


# Options: id noid search name state 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} ] ) : (),
  );

  my @select = (
    qw|t.id t.meta 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/ ? ('t.addedby', 'u.username') : (),
  );
  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;
}


# args: trait id, %options->{ columns in the traits table + parents }
sub dbTraitEdit {
  my($self, $id, %o) = @_;

  $self->dbExec('UPDATE traits !H WHERE id = ?', {
    $o{upddate} ? ('added = NOW()' => 1) : (),
    map exists($o{$_}) ? ("\"$_\" = ?" => $o{$_}) : (), qw|name meta description state alias group order sexual defaultspoil|
  }, $id);
  if($o{parents}) {
    $self->dbExec('DELETE FROM traits_parents WHERE trait = ?', $id);
    $self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
  }
}


# same args as dbTraitEdit, without the first trait id
# returns the id of the new trait
sub dbTraitAdd {
  my($self, %o) = @_;
  my $id = $self->dbRow('INSERT INTO traits (name, meta, description, state, alias, "group", "order", sexual, defaultspoil, addedby) VALUES (!l, ?) RETURNING id',
    [ map $o{$_}, qw|name meta description state alias group order sexual defaultspoil| ], $o{addedby}||$self->authInfo->{id}
  )->{id};
  $self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
  return $id;
}


1;