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
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
|
-- why did we still have this column?
ALTER TABLE releases_rev DROP COLUMN relation;
-- fix update_prev
CREATE OR REPLACE FUNCTION update_prev(tbl text, ids text) RETURNS void AS $$
DECLARE
r RECORD;
r2 RECORD;
i integer;
t text;
e text;
BEGIN
SELECT INTO t SUBSTRING(tbl, 1, 1);
e := '';
IF ids <> '' THEN
e := ' WHERE id IN('||ids||')';
END IF;
FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP
i := 0;
FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
UPDATE changes SET prev = i WHERE id = r2.id;
i := r2.id;
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT update_prev('vn',''), update_prev('releases',''), update_prev('producers','');
-- change votes treshold to 3
CREATE OR REPLACE FUNCTION calculate_rating() RETURNS void AS $$
DECLARE
av RECORD;
BEGIN
SELECT INTO av
COUNT(vote)::real / COUNT(DISTINCT vid)::real AS num_votes,
AVG(vote)::real AS rating
FROM votes;
UPDATE vn
SET c_votes = COALESCE((SELECT
TO_CHAR(CASE WHEN COUNT(uid) < 3 THEN 0 ELSE
( (av.num_votes * av.rating) + SUM(vote)::real ) / (av.num_votes + COUNT(uid)::real ) END,
'FM00D00'
)||'|'||TO_CHAR(
COUNT(votes.vote), 'FM0000'
)
FROM votes
WHERE votes.vid = vn.id
GROUP BY votes.vid
), '00.00|0000');
END
$$ LANGUAGE plpgsql;
SELECT calculate_rating();
-- store release dates as integers
ALTER TABLE releases_rev ALTER COLUMN released TYPE integer USING REPLACE(released, '-', '')::integer;
UPDATE releases_rev SET released = 0 WHERE released IS NULL;
ALTER TABLE releases_rev ALTER COLUMN released SET NOT NULL;
ALTER TABLE vn ALTER COLUMN c_released SET DEFAULT 0;
ALTER TABLE vn ALTER COLUMN c_released TYPE integer USING 0;
CREATE OR REPLACE FUNCTION update_vncache(id integer) RETURNS void AS $$
DECLARE
w text := '';
BEGIN
IF id > 0 THEN
w := ' WHERE id = '||id;
END IF;
EXECUTE 'UPDATE vn SET
c_released = COALESCE((SELECT
MIN(rr1.released)
FROM releases_rev rr1
JOIN releases r1 ON rr1.id = r1.latest
JOIN releases_vn rv1 ON rr1.id = rv1.rid
WHERE rv1.vid = vn.id
AND rr1.type <> 2
AND rr1.released <> 0
GROUP BY rv1.vid
), 0),
c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
SELECT language
FROM releases_rev rr2
JOIN releases r2 ON rr2.id = r2.latest
JOIN releases_vn rv2 ON rr2.id = rv2.rid
WHERE rv2.vid = vn.id
AND rr2.type <> 2
AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
GROUP BY rr2.language
ORDER BY rr2.language
), ''/''), '''')
'||w;
END;
$$ LANGUAGE plpgsql;
SELECT update_vncache(0);
-- Rewrite category system
CREATE TABLE vn_categories (
vid integer NOT NULL DEFAULT 0,
cat char(3) NOT NULL DEFAULT '',
lvl smallint NOT NULL DEFAULT 3,
PRIMARY KEY(vid, cat)
) WITHOUT OIDS;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gaa', 1 FROM vn_rev WHERE (categories & (1<<0)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gab', 1 FROM vn_rev WHERE (categories & (1<<1)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gac', 3 FROM vn_rev WHERE (categories & (1<<2)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'grp', 3 FROM vn_rev WHERE (categories & (1<<3)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gst', 3 FROM vn_rev WHERE (categories & (1<<4)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gsi', 3 FROM vn_rev WHERE (categories & (1<<5)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'pli', 1 FROM vn_rev WHERE (categories & (1<<6)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'pbr', 1 FROM vn_rev WHERE (categories & (1<<7)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'eac', 3 FROM vn_rev WHERE (categories & (1<<8)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'eco', 3 FROM vn_rev WHERE (categories & (1<<9)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'edr', 3 FROM vn_rev WHERE (categories & (1<<10)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'efa', 3 FROM vn_rev WHERE (categories & (1<<11)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'eho', 3 FROM vn_rev WHERE (categories & (1<<12)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'emy', 3 FROM vn_rev WHERE (categories & (1<<13)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'ero', 3 FROM vn_rev WHERE (categories & (1<<14)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'esf', 3 FROM vn_rev WHERE (categories & (1<<15)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'esj', 3 FROM vn_rev WHERE (categories & (1<<16)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'esn', 3 FROM vn_rev WHERE (categories & (1<<17)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'tfu', 3 FROM vn_rev WHERE (categories & (1<<18)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'tpa', 3 FROM vn_rev WHERE (categories & (1<<19)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'tpr', 3 FROM vn_rev WHERE (categories & (1<<20)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'lea', 3 FROM vn_rev WHERE (categories & (1<<21)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'lfa', 3 FROM vn_rev WHERE (categories & (1<<22)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'lsp', 3 FROM vn_rev WHERE (categories & (1<<23)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'saa', 3 FROM vn_rev WHERE (categories & (1<<24)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sbe', 3 FROM vn_rev WHERE (categories & (1<<25)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sin', 3 FROM vn_rev WHERE (categories & (1<<26)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'slo', 3 FROM vn_rev WHERE (categories & (1<<27)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'ssh', 3 FROM vn_rev WHERE (categories & (1<<28)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sya', 3 FROM vn_rev WHERE (categories & (1<<29)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'syu', 3 FROM vn_rev WHERE (categories & (1<<30)) > 0;
INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sra', 3 FROM vn_rev WHERE (categories & (1<<31)) < 0; -- MSB, mind you!
ALTER TABLE vn_rev DROP COLUMN categories;
-- Remove all previously defined constraints
ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_id_fkey;
ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_rid_fkey;
ALTER TABLE releases DROP CONSTRAINT releases_latest_fkey;
ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_rid_fkey;
ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_vid_fkey;
ALTER TABLE releases_platforms DROP CONSTRAINT releases_platforms_rid_fkey;
ALTER TABLE releases_media DROP CONSTRAINT releases_media_rid_fkey;
ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_rid_fkey;
ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_pid_fkey;
ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_id_fkey;
ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_vid_fkey;
ALTER TABLE vn DROP CONSTRAINT vn_latest_fkey;
ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid1_fkey;
ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid2_fkey;
ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey;
ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey;
ALTER TABLE votes DROP CONSTRAINT votes_vid_fkey;
ALTER TABLE vnlists DROP CONSTRAINT vnlists_uid_fkey;
ALTER TABLE vnlists DROP CONSTRAINT vnlists_vid_fkey;
-- And re-add them... LOLZ
ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED;
--ALTER TABLE releases_rev ADD FOREIGN KEY (id, NULL) REFERENCES releases_vn (rid, vid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;-- ON DELETE SET DEFAULT
ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
--ALTER TABLE releases_rev ADD COLUMN ref_vid_hack integer NULL DEFAULT NULL;
--ALTER TABLE releases_rev ADD FOREIGN KEY (id, ref_vid_hack) REFERENCES releases_vn (rid, vid) ON DELETE CASCADE;
-- TODO:
-- - make sure that changes.id should always refer to a row in *_rev
-- - make sure that there is always at least one row in releases_vn for every releases_rev
-- deletion of items in *_rev should trigger deletion in changes
--CREATE OR REPLACE FUNCTION changes_reference_del() RETURNS trigger AS $$
--BEGIN
-- DELETE FROM changes WHERE id = OLD.id;
--END
--$$ LANGUAGE PLPGSQL;
--CREATE TRIGGER vn_rev_cdel AFTER DELETE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE changes_reference_del();
--CREATE TRIGGER releases_rev_cdel AFTER DELETE ON releases_rev FOR EACH ROW EXECUTE PROCEDURE changes_reference_del();
--CREATE TRIGGER producers_rev_cdel AFTER DELETE ON producers_rev FOR EACH ROW EXECUTE PROCEDURE changes_reference_del();
|