diff options
Diffstat (limited to 'util')
| -rw-r--r-- | util/dump.sql | 29 | ||||
| -rw-r--r-- | util/updates/update_2.10.sql | 43 |
2 files changed, 54 insertions, 18 deletions
diff --git a/util/dump.sql b/util/dump.sql index 6376857a..70f79b2d 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -424,21 +424,15 @@ ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn -- update_vncache(id) - updates the c_* columns in the vn table -CREATE 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 +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 rr1.type <> 'trial' AND r1.hidden = FALSE AND rr1.released <> 0 GROUP BY rv1.vid @@ -450,12 +444,12 @@ BEGIN 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 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 @@ -463,15 +457,14 @@ BEGIN 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 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; + ), '/'), '') + WHERE id = $1; +$$ LANGUAGE sql; -- recalculate vn.c_popularity 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; + |