From 151a8338c931389c6d67389c56c2d8862e882de6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 18:46:59 +0100 Subject: SQL: Cached bayesian VN rating and vote counts Was a good idea after all... --- lib/Multi/Maintenance.pm | 18 ++++++++++++++++-- lib/VNDB/DB/VN.pm | 10 ++++------ lib/VNDB/Handler/VNBrowse.pm | 14 +++++++++++--- lib/VNDB/Util/CommonHTML.pm | 2 +- 4 files changed, 32 insertions(+), 12 deletions(-) (limited to 'lib') diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 4f816e56..422b5970 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -17,12 +17,12 @@ sub spawn { package_states => [ $p => [qw| _start shutdown set_daily daily set_monthly monthly log_stats - vncache tagcache vnpopularity cleangraphs + vncache tagcache vnpopularity vnrating cleangraphs usercache statscache logrotate |], ], heap => { - daily => [qw|vncache tagcache vnpopularity cleangraphs|], + daily => [qw|vncache tagcache vnpopularity vnrating cleangraphs|], monthly => [qw|usercache statscache logrotate|], @_, }, @@ -117,6 +117,20 @@ sub vnpopularity { } +sub vnrating { + # takes less than a second, but can be performed in ranges as well when necessary + $_[KERNEL]->post(pg => do => q| + UPDATE vn SET + c_rating = (SELECT ( + ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) / + ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real) + ) FROM votes WHERE vid = id + ), + c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0) + |, undef, 'log_stats', 'vnrating'); +} + + sub cleangraphs { # should be pretty fast $_[KERNEL]->post(pg => do => q| diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 4201181a..250f5267 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -11,7 +11,7 @@ our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd # Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, order, what -# What: extended anime relations screenshots relgraph ranking changes +# What: extended anime relations screenshots relgraph rating ranking changes sub dbVNGet { my($self, %o) = @_; $o{results} ||= 10; @@ -78,23 +78,21 @@ sub dbVNGet { 'JOIN users u ON u.id = c.requester' : (), $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), - $o{what} =~ /(rating|ranking)/ ? - 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (), ); my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]}; my @select = ( - qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph v.c_popularity|, 'vr.id AS cid', + qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid', $o{what} =~ /extended/ ? ( qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (), $o{what} =~ /changes/ ? ( qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), + $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (), $o{what} =~ /ranking/ ? ( '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking', - '(SELECT COUNT(*)+1 FROM vn_ratings ir WHERE ir.rating > r.rating) AS r_ranking', + '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking', ) : (), - $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (), $tag_ids ? qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (), ); diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index 87f3371a..3aaa1e25 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -55,13 +55,21 @@ sub list { $f->{s} = 'title' if !@ti && $f->{s} eq 'tagscore'; $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o}; + my $sortcol = {qw| + rel c_released + pop c_popularity + rating c_rating + title title + tagscore tagscore + |}->{$f->{s}}; + my($list, $np) = $self->dbVNGet( what => 'rating', $char ne 'all' ? ( char => $char ) : (), $f->{q} ? ( search => $f->{q} ) : (), results => 50, page => $f->{p}, - order => ($f->{s} eq 'rel' ? 'c_released' : $f->{s} eq 'pop' ? 'c_popularity' : $f->{s}).($f->{o} eq 'a' ? ' ASC' : ' DESC'), + order => $sortcol.($f->{o} eq 'a' ? ' ASC' : ' DESC'), $f->{pl}[0] ? ( platform => $f->{pl} ) : (), $f->{ln}[0] ? ( lang => $f->{ln} ) : (), @ti ? (tags_include => [ $f->{sp}, \@ti ]) : (), @@ -117,8 +125,8 @@ sub list { end; td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100; td class => 'tc6'; - txt sprintf '%.2f', $l->{rating}||0; - b class => 'grayedout', sprintf ' (%d)', $l->{votecount}||0; + txt sprintf '%.2f', $l->{c_rating}; + b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount}; end; end; }, diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 857cdcef..bb731a26 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -483,7 +483,7 @@ sub htmlVoteStats { div; h3 mt '_votestats_rank_title'; p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100; - p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{rating}; + p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{c_rating}; end; } end; -- cgit v1.2.3