summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Tags.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNDB/DB/Tags.pm')
-rw-r--r--lib/VNDB/DB/Tags.pm98
1 files changed, 33 insertions, 65 deletions
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index b3e16960..b7792eb8 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -176,7 +176,7 @@ sub dbTagLinks {
);
my @select = (
- qw|tv.tag tv.vid tv.uid tv.vote tv.spoiler|, "EXTRACT('epoch' from tv.date) AS date",
+ qw|tv.tag tv.vid tv.uid tv.vote tv.spoiler tv.ignore|, "EXTRACT('epoch' from tv.date) AS date",
$o{what} =~ /details/ ? (qw|vr.title u.username t.name|) : (),
);
@@ -202,95 +202,63 @@ sub dbTagLinks {
# Change a user's tags for a VN entry
-# Arguments: uid, vid, [ [ tag, vote, spoiler ], .. ]
sub dbTagLinkEdit {
- my($self, $uid, $vid, $new) = @_;
-
- # compare with the old votes and determine which to delete, and/or insert
- my %old = map +($_->{tag}, [ $_->{vote}, $_->{spoiler} // -1 ]),
- @{$self->dbTagLinks(vid => $vid, uid => $self->authInfo->{id})};
- my %new = map +($_->[0], [ $_->[1], $_->[2] ]), @$new;
-
- my(%delete, %update, %insert);
- for my $tag (keys %old, keys %new) {
- if($old{$tag} && !$new{$tag}) {
- $delete{$tag} = 1;
- } elsif(!$old{$tag} && $new{$tag}) {
- $insert{$tag} = $new{$tag};
- } elsif($old{$tag}[0] != $new{$tag}[0] || $old{$tag}[1] != $new{$tag}[1]) {
- $update{$tag} = $new{$tag};
- }
- }
+ my($self, $uid, $vid, $insert, $update, $delete, $overrule) = @_;
+
+ # overrule
+ # 1. set ignore flag for everyone except $uid
+ $self->dbExec('UPDATE tags_vn SET ignore = ? WHERE tag = ? AND vid = ? AND uid <> ?',
+ $overrule->{$_}?1:0, $_, $vid, $uid) for(keys %$overrule);
+ # 2. make sure $uid isn't ignored when others are set to ignore
+ # (this happens when a mod takes over an other mods' overrule)
+ $self->dbExec('UPDATE tags_vn SET ignore = false WHERE tag = ? AND vid = ? AND uid = ?',
+ $_, $vid, $uid) for(grep $overrule->{$_}, keys %$overrule);
+
+ # delete
+ $self->dbExec('DELETE FROM tags_vn WHERE vid = ? AND uid = ? AND tag IN(!l)',
+ $vid, $uid, [ keys %$delete ]) if keys %$delete;
- # spoiler '-1' -> NULL
- $new{$_}[1] == -1 && ($new{$_}[1] = undef) for keys %new;
+ # insert
+ my $val = join ',', map '(?,?,?,?,?,?)', keys %$insert;
+ $self->dbExec("INSERT INTO tags_vn (tag, vid, uid, vote, spoiler, ignore) VALUES $val", map
+ +($_, $vid, $uid, $insert->{$_}[0], $insert->{$_}[1]<0?undef:$insert->{$_}[1], $insert->{$_}[2]?1:0),
+ keys %$insert) if keys %$insert;
- # perform the changes
- $self->dbExec('DELETE FROM tags_vn WHERE vid = ? AND uid = ? AND tag IN(!l)',
- $vid, $uid, [ keys %delete ]) if keys %delete;
- $self->dbExec('INSERT INTO tags_vn (tag, vid, uid, vote, spoiler) VALUES (?, ?, ?, ?, ?)',
- $_, $vid, $uid, $insert{$_}[0], $insert{$_}[1]) for (keys %insert);
+ # update
$self->dbExec('UPDATE tags_vn SET vote = ?, spoiler = ?, date = NOW() WHERE tag = ? AND vid = ? AND uid = ?',
- $update{$_}[0], $update{$_}[1], $_, $vid, $uid) for (keys %update);
+ $update->{$_}[0], $update->{$_}[1]<0?undef:$update->{$_}[1], $_, $vid, $uid) for (keys %$update);
}
-# Fetch all tags related to a VN or User
-# Argument: %options->{ uid vid minrating results what page sort reverse }
-# what: vns
-# sort: name, count, rating
+# Fetch all tags related to a VN
+# Argument: %options->{ vid minrating results what page sort reverse }
+# sort: name, rating
sub dbTagStats {
my($self, %o) = @_;
$o{results} ||= 10;
$o{page} ||= 1;
- $o{what} ||= '';
-
- my %where = (
- $o{uid} ? (
- 'tv.uid = ?' => $o{uid} ) : (),
- $o{vid} ? (
- 'tv.vid = ?' => $o{vid} ) : (),
- );
+ my $rating = 'avg(CASE WHEN tv.ignore THEN NULL ELSE tv.vote END)';
my $order = sprintf {
name => 't.name %s',
- count => 'count(*) %s',
- rating => 'avg(tv.vote) %s',
+ rating => "$rating %s",
}->{ $o{sort}||'name' }, $o{reverse} ? 'DESC' : 'ASC';
- my($r, $np) = $self->dbPage(\%o, q|
- SELECT t.id, t.name, count(*) as cnt, avg(tv.vote) as rating, COALESCE(avg(tv.spoiler), 0) as spoiler
+ my($r, $np) = $self->dbPage(\%o, qq|
+ SELECT t.id, t.name, count(*) as cnt, $rating as rating,
+ COALESCE(avg(CASE WHEN tv.ignore THEN NULL ELSE tv.spoiler END), 0) as spoiler,
+ bool_or(tv.ignore) AS overruled
FROM tags t
JOIN tags_vn tv ON tv.tag = t.id
- !W
+ WHERE tv.vid = ?
GROUP BY t.id, t.name
!s
ORDER BY !s|,
- \%where, defined $o{minrating} ? "HAVING avg(tv.vote) > $o{minrating}" : '',
- $order
+ $o{vid}, defined $o{minrating} ? "HAVING $rating > $o{minrating}" : '', $order
);
- if(@$r && $o{what} =~ /vns/ && $o{uid}) {
- my %r = map {
- $_->{vns} = [];
- ($_->{id}, $_->{vns})
- } @$r;
-
- push @{$r{$_->{tag}}}, $_ for (@{$self->dbAll(q|
- SELECT tv.tag, tv.vote, tv.spoiler, vr.vid, vr.title, vr.original
- FROM tags_vn tv
- JOIN vn v ON v.id = tv.vid
- JOIN vn_rev vr ON vr.id = v.latest
- WHERE tv.uid = ?
- AND tv.tag IN(!l)
- ORDER BY vr.title ASC|,
- $o{uid}, [ keys %r ]
- )});
- }
-
return wantarray ? ($r, $np) : $r;
}
-
1;