diff options
author | Yorhel <git@yorhel.nl> | 2009-11-14 19:04:44 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-14 19:07:55 +0100 |
commit | 7791d0236a9bcd85ea3542360b0c68a8492a909e (patch) | |
tree | 0777720f6fa5d79a68fcf1e410dd6aa454ea3f79 | |
parent | 2c6f62c89c93dbb5ec757c11f2af38534f2c760a (diff) |
SQL: Allow NULL for vn.c_popularity
Sorting from least to most popular VN make sense now, you won't have to
wade through those entries without any vote at all.
-rw-r--r-- | ChangeLog | 1 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 2 | ||||
-rw-r--r-- | util/dump.sql | 4 | ||||
-rw-r--r-- | util/updates/update_2.9.sql | 20 |
5 files changed, 26 insertions, 5 deletions
@@ -10,6 +10,7 @@ git - ? - Made age ratings and external VN link titles translatable - Added wikipedia link for producers - Added bayesian rating for VNs + - Improved popularity sorting on VN list 2.8 - 2009-10-24 - Converted relation graphs to use inline SVG diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index 3aaa1e25..dbe80ac1 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -123,9 +123,9 @@ sub list { td class => 'tc4'; lit $self->{l10n}->datestr($l->{c_released}); end; - td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100; + td class => 'tc5', sprintf '%.2f', ($l->{c_popularity}||0)*100; td class => 'tc6'; - txt sprintf '%.2f', $l->{c_rating}; + txt sprintf '%.2f', $l->{c_rating}||0; 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 bb731a26..dc87d737 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -479,7 +479,7 @@ sub htmlVoteStats { } clearfloat; - if($type eq 'v') { + if($type eq 'v' && $obj->{c_votecount}) { div; h3 mt '_votestats_rank_title'; p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100; diff --git a/util/dump.sql b/util/dump.sql index 43562da3..a5451395 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -291,7 +291,7 @@ 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, c_rating real, c_votecount integer NOT NULL DEFAULT 0 ); @@ -485,7 +485,7 @@ BEGIN GROUP BY v.vid, v.uid; CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; - UPDATE vn SET c_popularity = COALESCE((SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id), 0); + UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id); RETURN; END; $$ LANGUAGE plpgsql; diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql index cbf6af63..78a8d88a 100644 --- a/util/updates/update_2.9.sql +++ b/util/updates/update_2.9.sql @@ -54,3 +54,23 @@ UPDATE vn SET ), c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0); + +-- vn.c_popularity can be NULL +ALTER TABLE vn ALTER COLUMN c_popularity DROP NOT NULL; +ALTER TABLE vn ALTER COLUMN c_popularity DROP DEFAULT; +CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$ +BEGIN + CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS + SELECT v.uid, v.vid, sqrt(count(*))::real + FROM votes v + JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote + WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes) + GROUP BY v.vid, v.uid; + CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS + SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; + UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id); + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT update_vnpopularity(); + |