summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-02-04 11:46:00 +0100
committerYorhel <git@yorhel.nl>2011-02-04 11:46:00 +0100
commit7a5283dc5cb35aca7cefe3348d8697e29aedda15 (patch)
tree207a89f89c128ce61082a3a019b391b4cb8374c7 /lib/VNDB/DB
parent422451344f20a69b43b6fc4c51f89a5174ef8760 (diff)
parent876119b300473a5232b5fcc60eb403afe36e6163 (diff)
Merge branch 'beta'2.17
Conflicts: ChangeLog
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r--lib/VNDB/DB/Releases.pm4
-rw-r--r--lib/VNDB/DB/Tags.pm98
-rw-r--r--lib/VNDB/DB/Users.pm6
3 files changed, 38 insertions, 70 deletions
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index ffffb2a6..87685ce6 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -10,7 +10,7 @@ use VNDB::Func 'gtintype';
our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|;
-# Options: id vid pid rev unreleased page results what med sort reverse date_before date_after
+# Options: id vid pid rev released page results what med sort reverse date_before date_after
# plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero
# What: extended changes vn producers platforms media
# Sort: title released minage
@@ -37,7 +37,7 @@ sub dbReleaseGet {
defined $o{voiced} ? ( 'rr.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (),
defined $o{ani_story} ? ( 'rr.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (),
defined $o{ani_ero} ? ( 'rr.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (),
- defined $o{unreleased} ? ( 'rr.released !s ?' => [ $o{unreleased} ? '>' : '<=', strftime('%Y%m%d', gmtime) ] ) : (),
+ defined $o{released} ? ( 'rr.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
$o{lang} ? (
'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
$o{olang} ? (
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;
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index bd7db201..36ae02af 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -178,7 +178,7 @@ sub dbSessionUpdateLastUsed {
}
-# %options->{ uid id what results page }
+# %options->{ uid id what results page reverse }
# what: titles
sub dbNotifyGet {
my($s, %o) = @_;
@@ -210,8 +210,8 @@ sub dbNotifyGet {
FROM notifications n
!s
!W
- ORDER BY n.id
- |, join(', ', @select), join(' ', @join), \%where);
+ ORDER BY n.id !s
+ |, join(', ', @select), join(' ', @join), \%where, $o{reverse} ? 'DESC' : 'ASC');
return wantarray ? ($r, $np) : $r;
}