diff options
author | Yorhel <git@yorhel.nl> | 2011-04-08 19:08:45 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2011-04-08 19:08:45 +0200 |
commit | 50da6516a687442f09a05416ca1daff04d7bbf37 (patch) | |
tree | 9316c817560b30e68e15e6a50631fe6c1c3b47c7 /util/sql | |
parent | c0b8fb4a60dda06bbe306273de8ac0cd92156fd6 (diff) |
Added char/tag/trait stats to database statistics box
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/all.sql | 9 | ||||
-rw-r--r-- | util/sql/func.sql | 16 |
2 files changed, 22 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; |