summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-04-08 19:08:45 +0200
committerYorhel <git@yorhel.nl>2011-04-08 19:08:45 +0200
commit50da6516a687442f09a05416ca1daff04d7bbf37 (patch)
tree9316c817560b30e68e15e6a50631fe6c1c3b47c7
parentc0b8fb4a60dda06bbe306273de8ac0cd92156fd6 (diff)
Added char/tag/trait stats to database statistics box
-rw-r--r--ChangeLog1
-rw-r--r--data/lang.txt21
-rw-r--r--data/style.css2
-rw-r--r--lib/Multi/Maintenance.pm3
-rw-r--r--lib/VNDB/Util/LayoutHTML.pm2
-rw-r--r--util/sql/all.sql9
-rw-r--r--util/sql/func.sql16
-rw-r--r--util/updates/update_2.20.sql13
8 files changed, 62 insertions, 5 deletions
diff --git a/ChangeLog b/ChangeLog
index b67a5917..59185a2e 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -6,6 +6,7 @@
- Generate dbedit/dbdel notifications on character edits
- CSS: Hide links in [spoiler] tags
- Added 'select' all to wishlist and moved 'select all' down on notifies
+ - Added char/tag/trait stats to database statistics box
- Bugfix: don't accidentally remove char traits when editing
- Bugfix: fixed possible SQL table name clash on history browser
- Bugfix: properly announce chars and traits in Multi::IRC
diff --git a/data/lang.txt b/data/lang.txt
index ed0033e7..bee15a28 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -1985,6 +1985,27 @@ cs : Producenti
hu : Készítők
nl : Producenten
+:_menu_stat_chars
+en : Characters
+ru*:
+cs*:
+hu*:
+nl : Karakters
+
+:_menu_stat_tags
+en : VN Tags
+ru*:
+cs*:
+hu*:
+nl :
+
+:_menu_stat_traits
+en : Character traits
+ru*:
+cs*:
+hu*:
+nl : Karakter kenmerken
+
:_menu_stat_users
en : Users
ru : Пользователей
diff --git a/data/style.css b/data/style.css
index cb936dbc..1381114c 100644
--- a/data/style.css
+++ b/data/style.css
@@ -289,7 +289,7 @@ b.future, b.standout, a.standout {
#menulist dt {
display: block;
float: left;
- width: 85px;
+ width: 93px;
font-style: italic;
}
#menulist dd {
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 5b092d0a..44cb6552 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -216,6 +216,9 @@ sub statscache {
q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn'|,
q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases'|,
q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers'|,
+ q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars'|,
+ q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags'|,
+ q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits'|,
q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads'|,
q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts'|
diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm
index 22052a75..5dd8038f 100644
--- a/lib/VNDB/Util/LayoutHTML.pm
+++ b/lib/VNDB/Util/LayoutHTML.pm
@@ -130,7 +130,7 @@ sub _menu {
h2 mt '_menu_dbstats';
div;
dl;
- for (qw|vn releases producers users threads posts|) {
+ for (qw|vn releases producers chars tags traits users threads posts|) {
dt mt "_menu_stat_$_";
dd $self->{stats}{$_};
}
diff --git a/util/sql/all.sql b/util/sql/all.sql
index a98fbf95..eac8d58a 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -47,6 +47,12 @@ CREATE TRIGGER stats_cache_new AFTER INSERT ON producers
CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
@@ -126,6 +132,9 @@ INSERT INTO stats_cache (section, count) VALUES
('vn', 0),
('producers', 0),
('releases', 0),
+ ('chars', 0),
+ ('tags', 0),
+ ('traits', 0),
('threads', 0),
('threads_posts', 0);
diff --git a/util/sql/func.sql b/util/sql/func.sql
index b0dbfff2..47b4511d 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -490,6 +490,9 @@ $$ LANGUAGE 'plpgsql';
-- the stats_cache table
CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
+DECLARE
+ unhidden boolean;
+ hidden boolean;
BEGIN
IF TG_OP = 'INSERT' THEN
IF TG_TABLE_NAME = 'users' THEN
@@ -504,13 +507,20 @@ BEGIN
END IF;
END IF;
- ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN
- IF OLD.hidden = TRUE THEN
+ ELSIF TG_OP = 'UPDATE' THEN
+ IF TG_TABLE_NAME IN('tags', 'traits') THEN
+ unhidden := OLD.state <> 2 AND NEW.state = 2;
+ hidden := OLD.state = 2 AND NEW.state <> 2;
+ ELSE
+ unhidden := OLD.hidden AND NOT NEW.hidden;
+ hidden := NOT unhidden;
+ END IF;
+ IF unhidden THEN
IF TG_TABLE_NAME = 'threads' THEN
UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
END IF;
UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- ELSIF OLD.hidden = FALSE THEN
+ ELSIF hidden THEN
IF TG_TABLE_NAME = 'threads' THEN
UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
END IF;
diff --git a/util/updates/update_2.20.sql b/util/updates/update_2.20.sql
index 236161d2..28efd713 100644
--- a/util/updates/update_2.20.sql
+++ b/util/updates/update_2.20.sql
@@ -9,3 +9,16 @@ DROP TYPE tmp;
CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+
+INSERT INTO stats_cache VALUES
+ ('chars', (SELECT COUNT(*) FROM chars WHERE NOT hidden)),
+ ('tags', (SELECT COUNT(*) FROM tags WHERE state = 2)),
+ ('traits', (SELECT COUNT(*) FROM traits WHERE state = 2));
+
+CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
+