diff options
author | Yorhel <git@yorhel.nl> | 2009-12-05 16:28:43 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-12-05 17:20:34 +0100 |
commit | 1e541e2643929b792089671a60bd40a7bdfee541 (patch) | |
tree | 75770ab753e413eae38b6ad7536795a10d014b14 /util/updates | |
parent | c67155961f1903a5e7c6c99b84cae08b7046a1a3 (diff) |
SQL: Don't allow argument of update_vncache() to be 0
To batch update, simply do a
SELECT update_vncache(id) FROM vn;
The function is now more readable as well.
Diffstat (limited to 'util/updates')
-rw-r--r-- | util/updates/update_2.10.sql | 43 |
1 files changed, 43 insertions, 0 deletions
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index a1606591..c2437136 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -123,4 +123,47 @@ $$ LANGUAGE plpgsql; CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify(); +-- don't allow vid=0 for update_vncache +CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ + 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 + ), '/'), '') + WHERE id = $1; +$$ LANGUAGE sql; + |