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.

3 files changed, 55 insertions, 19 deletions

diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index b952c490..1434fa73 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -101,7 +101,7 @@ sub log_stats { # num, res, action, time
 sub vncache {
   # this takes about 50s to complete. We really need to search for an alternative
   # method of keeping the c_* columns in the vn table up-to-date.
-  $_[KERNEL]->post(pg => do => 'SELECT update_vncache(0)', undef, 'log_stats', 'vncache');
+  $_[KERNEL]->post(pg => do => 'SELECT update_vncache(id) FROM vn', undef, 'log_stats', 'vncache');
 }
 
 
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;
+