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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
|
-- some random VN quotes
CREATE TABLE quotes (
vid integer NOT NULL REFERENCES vn (id),
quote varchar(250) NOT NULL,
PRIMARY KEY(vid, quote)
) WITHOUT OIDS;
-- catalog numbers for releases
ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT '';
-- aliases field for producers
ALTER TABLE producers_rev ADD COLUMN alias varchar(500) NOT NULL DEFAULT '';
-- tagging system
CREATE TABLE tags (
id SERIAL NOT NULL PRIMARY KEY,
name varchar(250) NOT NULL UNIQUE,
description text NOT NULL DEFAULT '',
meta boolean NOT NULL DEFAULT FALSE,
added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()),
state smallint NOT NULL DEFAULT 0, -- 0: awaiting moderation, 1: deleted, 2: accepted
c_vns integer NOT NULL DEFAULT 0
) WITHOUT OIDS;
CREATE TABLE tags_aliases (
alias varchar(250) NOT NULL PRIMARY KEY,
tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
CREATE TABLE tags_parents (
tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED,
parent integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED,
PRIMARY KEY(tag, parent)
) WITHOUT OIDS;
CREATE TABLE tags_vn (
tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED,
vid integer NOT NULL REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED,
uid integer NOT NULL REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED,
vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
PRIMARY KEY(tag, vid, uid)
) WITHOUT OIDS;
CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer);
-- tag: tag to start with,
-- lvl: recursion level
-- dir: direction, true = parent->child, false = child->parent
CREATE OR REPLACE FUNCTION tag_tree(tag integer, lvl integer, dir boolean) RETURNS SETOF tag_tree_item AS $$
DECLARE
r tag_tree_item%rowtype;
r2 tag_tree_item%rowtype;
BEGIN
IF dir AND tag = 0 THEN
FOR r IN
SELECT lvl, t.id, t.name, t.c_vns
FROM tags t
WHERE state = 2 AND NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
ORDER BY t.name
LOOP
RETURN NEXT r;
IF lvl-1 <> 0 THEN
FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
RETURN NEXT r2;
END LOOP;
END IF;
END LOOP;
ELSIF dir THEN
FOR r IN
SELECT lvl, tp.tag, t.name, t.c_vns
FROM tags_parents tp
JOIN tags t ON t.id = tp.tag
WHERE tp.parent = tag
AND state = 2
ORDER BY t.name
LOOP
RETURN NEXT r;
IF lvl-1 <> 0 THEN
FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
RETURN NEXT r2;
END LOOP;
END IF;
END LOOP;
ELSE
FOR r IN
SELECT lvl, tp.parent, t.name, t.c_vns
FROM tags_parents tp
JOIN tags t ON t.id = tp.parent
WHERE tp.tag = tag
AND state = 2
ORDER BY t.name
LOOP
RETURN NEXT r;
IF lvl-1 <> 0 THEN
FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
RETURN NEXT r2;
END LOOP;
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;
-- returns all votes inherited by childs
-- UNION this with tags_vn and you have all votes for all tags
CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$
DECLARE
r tags_vn%rowtype;
i RECORD;
l RECORD;
BEGIN
FOR l IN SElECT id FROM tags WHERE meta = FALSE AND state = 2 AND EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
RETURN NEXT r;
END LOOP;
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- all votes for all tags
CREATE OR REPLACE VIEW tags_vn_all AS
SELECT *
FROM tags_vn
UNION
SELECT *
FROM tag_vn_childs();
-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
CREATE OR REPLACE VIEW tags_vn_grouped AS
SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
FROM tags_vn_all
GROUP BY tag, vid, uid;
-- grouped by (tag, vid), so we now finally have a list of VN entries for a tag (including inherited tags)
CREATE OR REPLACE VIEW tags_vn_inherited AS
SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating,
(CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
FROM tags_vn_grouped
GROUP BY tag, vid;
-- creates/updates a table with bayesian rankings of tags
CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
BEGIN
DROP TABLE IF EXISTS tags_vn_stored;
CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);
UPDATE tags_vn_stored tvs SET rating =
((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
/ ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
RETURN;
END;
$$ 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();
-- rename threads tags to boards
ALTER TABLE threads_tags RENAME TO threads_boards;
|