summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/Multi/Maintenance.pm18
-rw-r--r--lib/VNDB/DB/VN.pm10
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm14
-rw-r--r--lib/VNDB/Util/CommonHTML.pm2
-rw-r--r--util/dump.sql14
-rw-r--r--util/updates/update_2.9.sql12
6 files changed, 42 insertions, 28 deletions
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;
diff --git a/util/dump.sql b/util/dump.sql
index d64fe12f..43562da3 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -291,7 +291,9 @@ CREATE TABLE vn (
c_released integer NOT NULL DEFAULT 0,
c_languages varchar(32) NOT NULL DEFAULT '',
c_platforms varchar(32) NOT NULL DEFAULT '',
- c_popularity real NOT NULL DEFAULT 0
+ c_popularity real NOT NULL DEFAULT 0,
+ c_rating real,
+ c_votecount integer NOT NULL DEFAULT 0
);
-- vn_anime
@@ -829,16 +831,6 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PRO
---------------------------------
--- bayesian rating view
-CREATE OR REPLACE VIEW vn_ratings AS
- SELECT vid, COUNT(uid) AS votecount, (
- ((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)
- ) AS rating
- FROM votes
- GROUP BY vid;
-
-
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 68ea2052..0720681e 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -44,11 +44,13 @@ ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150);
-- bayesian rating
-CREATE OR REPLACE VIEW vn_ratings AS
- SELECT vid, COUNT(uid) AS votecount, (
+ALTER TABLE vn ADD COLUMN c_rating real;
+ALTER TABLE vn ADD COLUMN c_votecount integer NOT NULL DEFAULT 0;
+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)
- ) AS rating
- FROM votes
- GROUP BY vid;
+ ) FROM votes WHERE vid = id
+ ),
+ c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0);
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 17:11:32 +0000