-- 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;