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
|
-- multilingual releases
CREATE TABLE releases_lang (
rid integer NOT NULL REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED,
lang varchar NOT NULL,
PRIMARY KEY(rid, lang)
);
INSERT INTO releases_lang (rid, lang) SELECT id, language FROM releases_rev;
ALTER TABLE releases_rev DROP COLUMN language;
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 r1.hidden = FALSE
AND rr1.released <> 0
GROUP BY rv1.vid
), 0),
c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
SELECT rl2.lang
FROM releases_rev rr2
JOIN releases_lang rl2 ON rl2.rid = rr2.id
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
AND r2.hidden = FALSE
GROUP BY rl2.lang
ORDER BY rl2.lang
), ''/''), ''''),
c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
SELECT rp3.platform
FROM releases_platforms rp3
JOIN releases_rev rr3 ON rp3.rid = rr3.id
JOIN releases r3 ON rp3.rid = r3.latest
JOIN releases_vn rv3 ON rp3.rid = rv3.rid
WHERE rv3.vid = vn.id
AND rr3.type <> 2
AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
AND r3.hidden = FALSE
GROUP BY rp3.platform
ORDER BY rp3.platform
), ''/''), '''')
'||w;
END;
$$ LANGUAGE plpgsql;
-- added by field for tags
ALTER TABLE tags ADD COLUMN addedby integer NOT NULL DEFAULT 1 REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
|