Cached each users change and vote counts in the users table

And added triggers to keep these up to date, and made it possible
to sort on the change and vote count on the userlist

1 files changed, 46 insertions, 0 deletions

diff --git a/util/updates/update_2.0.sql b/util/updates/update_2.0.sql
new file mode 100644
index 00000000..e7f9025e
--- /dev/null
+++ b/util/updates/update_2.0.sql
@@ -0,0 +1,46 @@
+
+
+-- cache users vote and edit count
+ALTER TABLE users ADD COLUMN c_votes integer NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN c_changes integer NOT NULL DEFAULT 0;
+
+
+-- may be an idea to run this query as a monthly cron or something
+UPDATE users SET
+  c_votes = COALESCE(
+    (SELECT COUNT(vid)
+    FROM votes
+    WHERE uid = users.id
+    GROUP BY uid
+  ), 0),
+  c_changes = COALESCE(
+    (SELECT COUNT(id)
+    FROM changes
+    WHERE requester = users.id
+    GROUP BY requester
+  ), 0);
+
+
+-- one function to rule them all
+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;
+  ELSE
+    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;
+  END IF;
+  RETURN NULL;
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_votes_update   AFTER INSERT OR DELETE ON votes   FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+