diff options
Diffstat (limited to 'util/updates/update_2.8.sql')
-rw-r--r-- | util/updates/update_2.8.sql | 62 |
1 files changed, 62 insertions, 0 deletions
diff --git a/util/updates/update_2.8.sql b/util/updates/update_2.8.sql index ff8fe9dd..c8bcda38 100644 --- a/util/updates/update_2.8.sql +++ b/util/updates/update_2.8.sql @@ -75,3 +75,65 @@ ALTER TABLE changes ALTER COLUMN type TYPE dbentry_type USING ELSE NULL -- not allowed to happen, otherwise FIX YOUR DATABASE! END; + +-- releases_rev.type stored as enum +CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); +ALTER TABLE releases_rev ALTER COLUMN type DROP DEFAULT; +ALTER TABLE releases_rev ALTER COLUMN type TYPE release_type USING + CASE + WHEN type = 0 THEN 'complete'::release_type + WHEN type = 1 THEN 'partial' + WHEN type = 2 THEN 'trial' + ELSE NULL + END; +ALTER TABLE releases_rev ALTER COLUMN type SET DEFAULT 'complete'; + +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 <> ''trial'' + 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 <> ''trial'' + 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 <> ''trial'' + 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; + |