summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-03-08 11:36:31 +0100
committerYorhel <git@yorhel.nl>2009-03-08 11:36:31 +0100
commit64eadc1c58c931b7309cf3c405f9a600f67fb8aa (patch)
tree95acbdcc820159891de82e32dc61effd1dc89ddd /util
parent12175dea60e85ae150d962ca0d76757cc1013fc0 (diff)
Tag stats on user pages and list
Diffstat (limited to 'util')
-rw-r--r--util/updates/update_2.3.sql32
1 files changed, 32 insertions, 0 deletions
diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql
index 02a32106..4c60791b 100644
--- a/util/updates/update_2.3.sql
+++ b/util/updates/update_2.3.sql
@@ -161,3 +161,35 @@ $$ LANGUAGE plpgsql;
SELECT tag_vn_calc();
+
+-- Cache users tag vote count
+ALTER TABLE users ADD COLUMN c_tags integer NOT NULL DEFAULT 0;
+UPDATE users SET c_tags = (SELECT COUNT(*) FROM tags_vn WHERE uid = id);
+
+CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
+BEGIN
+ IF TG_TABLE_NAME = 'votes' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'changes' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
+ ELSE
+ UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'tags_vn' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+