summaryrefslogtreecommitdiff
path: root/util
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 /util
parentc0b8fb4a60dda06bbe306273de8ac0cd92156fd6 (diff)
Added char/tag/trait stats to database statistics box
Diffstat (limited to 'util')
-rw-r--r--util/sql/all.sql9
-rw-r--r--util/sql/func.sql16
-rw-r--r--util/updates/update_2.20.sql13
3 files changed, 35 insertions, 3 deletions
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();
+