diff options
author | Yorhel <git@yorhel.nl> | 2011-02-04 11:46:00 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2011-02-04 11:46:00 +0100 |
commit | 7a5283dc5cb35aca7cefe3348d8697e29aedda15 (patch) | |
tree | 207a89f89c128ce61082a3a019b391b4cb8374c7 /lib/VNDB/DB | |
parent | 422451344f20a69b43b6fc4c51f89a5174ef8760 (diff) | |
parent | 876119b300473a5232b5fcc60eb403afe36e6163 (diff) |
Merge branch 'beta'2.17
Conflicts:
ChangeLog
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/Releases.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 98 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 6 |
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; } |