summaryrefslogtreecommitdiff
path: root/util/updates/update_2.8.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.8.sql')
-rw-r--r--util/updates/update_2.8.sql62
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;
+