summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/Multi/Maintenance.pm17
-rw-r--r--lib/VNDB/DB/Tags.pm17
-rw-r--r--lib/VNDB/Handler/Tags.pm6
-rw-r--r--util/updates/update_2.3.sql36
4 files changed, 40 insertions, 36 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index e9c9b337..10f02681 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -18,7 +18,7 @@ sub spawn {
my $p = shift;
POE::Session->create(
package_states => [
- $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate vnpopularity |],
+ $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate vnpopularity tagcache |],
],
);
}
@@ -26,8 +26,10 @@ sub spawn {
sub _start {
$_[KERNEL]->alias_set('maintenance');
- $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate|vnpopularity))+)$/, 'cmd_maintenance');
-
+ $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate|vnpopularity|tagcache))+)$/, 'cmd_maintenance');
+
+ # recalculate tag<->vn cache each hour (better do this once every 24 hours when the DB grows)
+ $_[KERNEL]->post(core => addcron => '0 * * * *', 'maintenance tagcache');
# Perform some maintenance functions every day on 0:00
$_[KERNEL]->post(core => addcron => '0 0 * * *', 'maintenance vncache integrity unkanime vnpopularity');
# update caches and rotate logs every 1st day of the month at 0:05
@@ -132,7 +134,7 @@ sub unkanime {
WHERE a.lastfetch < 0|);
$q->execute();
my $r = $q->fetchall_arrayref([]);
- my %aid = map {
+ my %aid = map {
my $a=$_;
$a->[1] => join(',', map { $a->[1] == $_->[1] ? $_->[0] : () } @$r)
} @$r;
@@ -177,6 +179,13 @@ sub vnpopularity {
}
+sub tagcache {
+ my $S = [gettimeofday];
+ $Multi::SQL->do(q|SELECT tag_vn_calc()|);
+ $_[KERNEL]->call(core => log => 3 => '(Re)calculated tags_vn_stored in %.2fs', tv_interval($S));
+}
+
+
1;
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 27485229..a50e6d38 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -32,7 +32,7 @@ sub dbTagGet {
);
my($r, $np) = $self->dbPage(\%o, q|
- SELECT t.id, t.meta, t.name, t.alias, t.description
+ SELECT t.id, t.meta, t.name, t.alias, t.description, t.c_vns
FROM tags t
!W
ORDER BY !s|,
@@ -40,22 +40,13 @@ sub dbTagGet {
);
if($o{what} =~ /parents\((\d+)\)/) {
- $_->{parents} = $self->dbAll(q|SELECT lvl, tag, name FROM tag_tree(?, ?, false)|, $_->{id}, $1) for (@$r);
+ $_->{parents} = $self->dbAll(q|SELECT lvl, tag, name, c_vns FROM tag_tree(?, ?, false)|, $_->{id}, $1) for (@$r);
}
if($o{what} =~ /childs\((\d+)\)/) {
- $_->{childs} = $self->dbAll(
- q|SELECT lvl, tag, name, COALESCE((SELECT COUNT(*) FROM tags_vn_bayesian tb WHERE tb.tag = tt.tag), 0) AS vns FROM tag_tree(?, ?, true) tt|,
- $_->{id}, $1
- ) for (@$r);
+ $_->{childs} = $self->dbAll(q|SELECT lvl, tag, name, c_vns FROM tag_tree(?, ?, true)|, $_->{id}, $1) for (@$r);
}
- #if(@$r && $o{what} =~ /(?:parents)/) {
- #my %r = map {
- # ($r->[$_]{id}, $_)
- #} 0..$#$r;
- #}
-
return wantarray ? ($r, $np) : $r;
}
@@ -178,7 +169,7 @@ sub dbTagVNs {
my($r, $np) = $self->dbPage(\%o, q|
SELECT tb.tag, tb.vid, tb.users, tb.rating, tb.spoiler, vr.title, vr.original, v.c_languages, v.c_released, v.c_platforms, v.c_popularity
- FROM tags_vn_bayesian tb
+ FROM tags_vn_stored tb
JOIN vn v ON v.id = tb.vid
JOIN vn_rev vr ON vr.id = v.latest
!W
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index 53c84a85..b0854d25 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -108,7 +108,7 @@ sub _childtags {
for my $p (sort { @{$b->{childs}} <=> @{$a->{childs}} } @tags) {
li;
a href => "/g$p->{tag}", $p->{name};
- b class => 'grayedout', " ($p->{vns})";
+ b class => 'grayedout', " ($p->{c_vns})";
end, next if !@{$p->{childs}};
ul;
for (0..$#{$p->{childs}}) {
@@ -116,7 +116,7 @@ sub _childtags {
li;
txt '> ';
a href => "/g$p->{childs}[$_]{tag}", $p->{childs}[$_]{name};
- b class => 'grayedout', " ($p->{childs}[$_]{vns})";
+ b class => 'grayedout', " ($p->{childs}[$_]{c_vns})";
end;
}
if(@{$p->{childs}} > 6) {
@@ -311,7 +311,7 @@ sub vntagmod {
ul;
li "Don't forget to hit the submit button on the bottom of the page after changing anything here!";
li 'Tag guidelines?';
- li '!IMPORTANT! The current user interface is just for testing, and likely doesn\'t reflect the final form!';
+ li 'Some tag information on the site is cached, it can take up to an hour for your changes to be visible everywhere.';
end;
end;
end;
diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql
index 4c60791b..b0662302 100644
--- a/util/updates/update_2.3.sql
+++ b/util/updates/update_2.3.sql
@@ -20,7 +20,8 @@ CREATE TABLE tags (
name varchar(250) NOT NULL UNIQUE,
alias text NOT NULL DEFAULT '',
description text NOT NULL DEFAULT '',
- meta boolean NOT NULL DEFAULT FALSE
+ meta boolean NOT NULL DEFAULT FALSE,
+ c_vns integer NOT NULL DEFAULT 0
) WITHOUT OIDS;
CREATE TABLE tags_parents (
@@ -39,7 +40,7 @@ CREATE TABLE tags_vn (
) WITHOUT OIDS;
-CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text);
+CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer);
-- tag: tag to start with,
-- lvl: recursion level
@@ -51,7 +52,7 @@ DECLARE
BEGIN
IF dir AND tag = 0 THEN
FOR r IN
- SELECT lvl, t.id, t.name
+ SELECT lvl, t.id, t.name, t.c_vns
FROM tags t
WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
ORDER BY t.name
@@ -65,7 +66,7 @@ BEGIN
END LOOP;
ELSIF dir THEN
FOR r IN
- SELECT lvl, tp.tag, t.name
+ SELECT lvl, tp.tag, t.name, t.c_vns
FROM tags_parents tp
JOIN tags t ON t.id = tp.tag
WHERE tp.parent = tag
@@ -80,7 +81,7 @@ BEGIN
END LOOP;
ELSE
FOR r IN
- SELECT lvl, tp.parent, t.name
+ SELECT lvl, tp.parent, t.name, t.c_vns
FROM tags_parents tp
JOIN tags t ON t.id = tp.parent
WHERE tp.tag = tag
@@ -131,18 +132,19 @@ CREATE OR REPLACE VIEW tags_vn_grouped AS
-- grouped by (tag, vid), so we now finally have a list of VN entries for a tag (including inherited tags)
CREATE OR REPLACE VIEW tags_vn_inherited AS
- SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating, AVG(spoiler)::real AS spoiler
+ SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating,
+ (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
FROM tags_vn_grouped
GROUP BY tag, vid;
-- bayesian average on the above view, to provide better rankings as to how much a tag applies to a VN
-- details of the calculation @ http://www.thebroth.com/blog/118/bayesian-rating
-CREATE OR REPLACE VIEW tags_vn_bayesian AS
- SELECT tag, vid, users,
- ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating )
- / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating,
- (CASE WHEN spoiler < 0.7 THEN 0 WHEN spoiler > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
- FROM tags_vn_inherited tvi;
+--CREATE OR REPLACE VIEW tags_vn_bayesian AS
+-- SELECT tag, vid, users,
+-- ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating )
+-- / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating,
+-- spoiler
+-- FROM tags_vn_inherited tvi;
-- creates/updates a table eqvuivalent to tags_vn_bayesian
@@ -151,10 +153,12 @@ BEGIN
DROP TABLE IF EXISTS tags_vn_stored;
CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);
- -- The following method may be faster on larger DBs, because tag_vn_childs() only has to be called once
- --UPDATE tags_vn_stored tvs SET rating =
- -- ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
- -- / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
+
+ UPDATE tags_vn_stored tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
+
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
RETURN;
END;
$$ LANGUAGE plpgsql;
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 21:11:51 +0000