summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-14 19:04:44 +0100
committerYorhel <git@yorhel.nl>2009-11-14 19:07:55 +0100
commit7791d0236a9bcd85ea3542360b0c68a8492a909e (patch)
tree0777720f6fa5d79a68fcf1e410dd6aa454ea3f79
parent2c6f62c89c93dbb5ec757c11f2af38534f2c760a (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--ChangeLog1
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm4
-rw-r--r--lib/VNDB/Util/CommonHTML.pm2
-rw-r--r--util/dump.sql4
-rw-r--r--util/updates/update_2.9.sql20
5 files changed, 26 insertions, 5 deletions
diff --git a/ChangeLog b/ChangeLog
index 92cfa0e8..bf3620af 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -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();
+