1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
-- 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;
|