summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-12-05 16:28:43 +0100
committerYorhel <git@yorhel.nl>2009-12-05 17:20:34 +0100
commit1e541e2643929b792089671a60bd40a7bdfee541 (patch)
tree75770ab753e413eae38b6ad7536795a10d014b14 /util
parentc67155961f1903a5e7c6c99b84cae08b7046a1a3 (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')
-rw-r--r--util/dump.sql29
-rw-r--r--util/updates/update_2.10.sql43
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;
+