-- 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(); -- users.flags -> users.(show_nsfw|show_list) ALTER TABLE users ADD COLUMN show_nsfw boolean NOT NULL DEFAULT FALSE; ALTER TABLE users ADD COLUMN show_list boolean NOT NULL DEFAULT TRUE; UPDATE users SET show_nsfw = (flags & 8 = 8), show_list = (flags & 4 = 4); ALTER TABLE users DROP COLUMN flags; -- get rid of \r and leading and trailing whitespace UPDATE vn_rev SET "desc" = trim(both E'\n ' from translate("desc", E'\r', '')), alias = trim(both E'\n ' from translate(alias, E'\r', '')); UPDATE releases_rev SET notes = trim(both E'\n ' from translate(notes, E'\r', '')); UPDATE producers_rev SET "desc" = trim(both E'\n ' from translate("desc", E'\r', '')); UPDATE changes SET comments = trim(both E'\n ' from translate(comments, E'\r', '')); UPDATE threads_posts SET msg = trim(both E'\n ' from translate(msg, E'\r', '')); -- cache some database statistics CREATE TABLE stats_cache ( section varchar(25) NOT NULL PRIMARY KEY, count integer NOT NULL DEFAULT 0 ); INSERT INTO stats_cache (section, count) VALUES ('users', (SELECT COUNT(*) FROM users)-1), ('vn', (SELECT COUNT(*) FROM vn WHERE hidden = FALSE)), ('producers', (SELECT COUNT(*) FROM producers WHERE hidden = FALSE)), ('releases', (SELECT COUNT(*) FROM releases WHERE hidden = FALSE)), ('threads', (SELECT COUNT(*) FROM threads WHERE hidden = FALSE)), ('threads_posts', (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE))); CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN IF TG_TABLE_NAME = 'users' THEN UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; ELSIF NEW.hidden = FALSE THEN IF TG_TABLE_NAME = 'threads_posts' THEN IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; END IF; ELSE UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; END IF; END IF; ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN IF OLD.hidden = TRUE AND NEW.hidden = FALSE 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 AND NEW.hidden = TRUE 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; END IF; ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; END IF; RETURN NULL; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); -- extra user rank UPDATE users SET rank = rank+1; ALTER TABLE users ALTER COLUMN rank SET DEFAULT 3;