diff options
author | Yorhel <git@yorhel.nl> | 2009-03-08 11:36:31 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-03-08 11:36:31 +0100 |
commit | 64eadc1c58c931b7309cf3c405f9a600f67fb8aa (patch) | |
tree | 95acbdcc820159891de82e32dc61effd1dc89ddd /util | |
parent | 12175dea60e85ae150d962ca0d76757cc1013fc0 (diff) |
Tag stats on user pages and list
Diffstat (limited to 'util')
-rw-r--r-- | util/updates/update_2.3.sql | 32 |
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(); + |