[Author Prev][Author Next][Thread Prev][Thread Next][Author Index][Thread Index]

[tor-commits] [metrics-web/master] Tweak refresh functions to use partitioned table.



commit 13857257ac1df8c4b4d9bc00e0164b5731289762
Author: Karsten Loesing <karsten.loesing@xxxxxxx>
Date:   Thu Jan 12 14:54:16 2012 +0100

    Tweak refresh functions to use partitioned table.
---
 db/tordir.sql |  162 ++++++++++++++++++++++++++++++++++++++-------------------
 1 files changed, 109 insertions(+), 53 deletions(-)

diff --git a/db/tordir.sql b/db/tordir.sql
index fbb1341..bc93b45 100644
--- a/db/tordir.sql
+++ b/db/tordir.sql
@@ -438,11 +438,18 @@ $$ LANGUAGE plpgsql;
 
 -- FUNCTION refresh_network_size()
 CREATE OR REPLACE FUNCTION refresh_network_size() RETURNS INTEGER AS $$
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
     BEGIN
 
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
     DELETE FROM network_size
     WHERE date IN (SELECT date FROM updates);
 
+    EXECUTE '
         INSERT INTO network_size
         (date, avg_running, avg_exit, avg_guard, avg_fast, avg_stable)
         SELECT date,
@@ -460,12 +467,12 @@ CREATE OR REPLACE FUNCTION refresh_network_size() RETURNS INTEGER AS $$
                 COUNT(NULLIF(isstable, FALSE)) AS isstable
             FROM statusentry
             WHERE isrunning = TRUE
-              AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-              AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+              AND validafter >= ''' || min_date || '''
+              AND validafter < ''' || max_date || '''
               AND DATE(validafter) IN (SELECT date FROM updates)
             GROUP BY DATE(validafter)
             ) b
-        NATURAL JOIN relay_statuses_per_day;
+        NATURAL JOIN relay_statuses_per_day';
 
     RETURN 1;
     END;
@@ -473,11 +480,18 @@ $$ LANGUAGE plpgsql;
 
 -- FUNCTION refresh_network_size_hour()
 CREATE OR REPLACE FUNCTION refresh_network_size_hour() RETURNS INTEGER AS $$
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
     BEGIN
 
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
     DELETE FROM network_size_hour
     WHERE DATE(validafter) IN (SELECT date FROM updates);
 
+    EXECUTE '
     INSERT INTO network_size_hour
     (validafter, avg_running, avg_exit, avg_guard, avg_fast, avg_stable)
     SELECT validafter, COUNT(*) AS avg_running,
@@ -487,10 +501,10 @@ CREATE OR REPLACE FUNCTION refresh_network_size_hour() RETURNS INTEGER AS $$
     COUNT(NULLIF(isstable, FALSE)) AS avg_stable
     FROM statusentry
     WHERE isrunning = TRUE
-    AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-    AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+    AND validafter >= ''' || min_date || '''
+    AND validafter < ''' || max_date || '''
     AND DATE(validafter) IN (SELECT date FROM updates)
-    GROUP BY validafter;
+    GROUP BY validafter';
 
     RETURN 1;
     END;
@@ -498,27 +512,34 @@ $$ LANGUAGE plpgsql;
 
 -- FUNCTION refresh_relay_countries()
 CREATE OR REPLACE FUNCTION refresh_relay_countries() RETURNS INTEGER AS $$
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
     BEGIN
 
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
     DELETE FROM relay_countries
     WHERE date IN (SELECT date FROM updates);
 
+    EXECUTE '
     INSERT INTO relay_countries
     (date, country, relays)
     SELECT date, country, relays / count AS relays
     FROM (
         SELECT DATE(validafter),
-               COALESCE(lower((geoip_lookup(address)).country), 'zz')
+               COALESCE(lower((geoip_lookup(address)).country), ''zz'')
                  AS country,
                COUNT(*) AS relays
         FROM statusentry
         WHERE isrunning = TRUE
-              AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-              AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+              AND validafter >= ''' || min_date || '''
+              AND validafter < ''' || max_date || '''
               AND DATE(validafter) IN (SELECT date FROM updates)
         GROUP BY 1, 2
         ) b
-    NATURAL JOIN relay_statuses_per_day;
+    NATURAL JOIN relay_statuses_per_day';
 
     RETURN 1;
     END;
@@ -526,11 +547,18 @@ $$ LANGUAGE plpgsql;
 
 -- FUNCTION refresh_relay_platforms()
 CREATE OR REPLACE FUNCTION refresh_relay_platforms() RETURNS INTEGER AS $$
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
     BEGIN
 
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
     DELETE FROM relay_platforms
     WHERE date IN (SELECT date FROM updates);
 
+    EXECUTE '
     INSERT INTO relay_platforms
     (date, avg_linux, avg_darwin, avg_bsd, avg_windows, avg_other)
     SELECT date,
@@ -541,29 +569,29 @@ CREATE OR REPLACE FUNCTION refresh_relay_platforms() RETURNS INTEGER AS $$
         other / count AS avg_other
     FROM (
         SELECT DATE(validafter) AS date,
-            SUM(CASE WHEN platform LIKE '%Linux%' THEN 1 ELSE 0 END)
+            SUM(CASE WHEN platform LIKE ''%Linux%'' THEN 1 ELSE 0 END)
                 AS linux,
-            SUM(CASE WHEN platform LIKE '%Darwin%' THEN 1 ELSE 0 END)
+            SUM(CASE WHEN platform LIKE ''%Darwin%'' THEN 1 ELSE 0 END)
                 AS darwin,
-            SUM(CASE WHEN platform LIKE '%BSD%' THEN 1 ELSE 0 END)
+            SUM(CASE WHEN platform LIKE ''%BSD%'' THEN 1 ELSE 0 END)
                 AS bsd,
-            SUM(CASE WHEN platform LIKE '%Windows%' THEN 1 ELSE 0 END)
+            SUM(CASE WHEN platform LIKE ''%Windows%'' THEN 1 ELSE 0 END)
                 AS windows,
-            SUM(CASE WHEN platform NOT LIKE '%Windows%'
-                AND platform NOT LIKE '%Darwin%'
-                AND platform NOT LIKE '%BSD%'
-                AND platform NOT LIKE '%Linux%' THEN 1 ELSE 0 END)
+            SUM(CASE WHEN platform NOT LIKE ''%Windows%''
+                AND platform NOT LIKE ''%Darwin%''
+                AND platform NOT LIKE ''%BSD%''
+                AND platform NOT LIKE ''%Linux%'' THEN 1 ELSE 0 END)
                 AS other
         FROM descriptor
         RIGHT JOIN statusentry
         ON statusentry.descriptor = descriptor.descriptor
         WHERE isrunning = TRUE
-          AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-          AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+          AND validafter >= ''' || min_date || '''
+          AND validafter < ''' || max_date || '''
           AND DATE(validafter) IN (SELECT date FROM updates)
         GROUP BY DATE(validafter)
         ) b
-    NATURAL JOIN relay_statuses_per_day;
+    NATURAL JOIN relay_statuses_per_day';
 
     RETURN 1;
     END;
@@ -571,11 +599,18 @@ $$ LANGUAGE plpgsql;
 
 -- FUNCTION refresh_relay_versions()
 CREATE OR REPLACE FUNCTION refresh_relay_versions() RETURNS INTEGER AS $$
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
     BEGIN
 
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
     DELETE FROM relay_versions
     WHERE date IN (SELECT date FROM updates);
 
+    EXECUTE '
     INSERT INTO relay_versions
     (date, version, relays)
     SELECT date, version, relays / count AS relays
@@ -586,12 +621,12 @@ CREATE OR REPLACE FUNCTION refresh_relay_versions() RETURNS INTEGER AS $$
         ON descriptor.descriptor = statusentry.descriptor
         WHERE isrunning = TRUE
               AND platform IS NOT NULL
-              AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-              AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+              AND validafter >= ''' || min_date || '''
+              AND validafter < ''' || max_date || '''
               AND DATE(validafter) IN (SELECT date FROM updates)
         GROUP BY 1, 2
         ) b
-    NATURAL JOIN relay_statuses_per_day;
+    NATURAL JOIN relay_statuses_per_day';
 
     RETURN 1;
     END;
@@ -600,11 +635,18 @@ $$ LANGUAGE plpgsql;
 -- FUNCTION refresh_total_bandwidth()
 -- This keeps the table total_bandwidth up-to-date when necessary.
 CREATE OR REPLACE FUNCTION refresh_total_bandwidth() RETURNS INTEGER AS $$
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
     BEGIN
 
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
     DELETE FROM total_bandwidth
     WHERE date IN (SELECT date FROM updates);
 
+    EXECUTE '
     INSERT INTO total_bandwidth
     (bwavg, bwburst, bwobserved, bwadvertised, date)
     SELECT (SUM(bandwidthavg)
@@ -621,16 +663,14 @@ CREATE OR REPLACE FUNCTION refresh_total_bandwidth() RETURNS INTEGER AS $$
     JOIN relay_statuses_per_day
     ON DATE(validafter) = relay_statuses_per_day.date
     WHERE isrunning = TRUE
-          AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-          AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+          AND validafter >= ''' || min_date || '''
+          AND validafter < ''' || max_date || '''
           AND DATE(validafter) IN (SELECT date FROM updates)
-          AND DATE(relay_statuses_per_day.date) >=
-              (SELECT MIN(date) FROM updates)
-          AND DATE(relay_statuses_per_day.date) <=
-              (SELECT MAX(date) FROM updates)
+          AND relay_statuses_per_day.date >= ''' || min_date || '''
+          AND relay_statuses_per_day.date < ''' || max_date || '''
           AND DATE(relay_statuses_per_day.date) IN
               (SELECT date FROM updates)
-    GROUP BY DATE(validafter), relay_statuses_per_day.count;
+    GROUP BY DATE(validafter), relay_statuses_per_day.count';
 
     RETURN 1;
     END;
@@ -651,8 +691,16 @@ CREATE OR REPLACE FUNCTION refresh_total_bwhist() RETURNS INTEGER AS $$
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION refresh_bwhist_flags() RETURNS INTEGER AS $$
-  BEGIN
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
+    BEGIN
+
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
   DELETE FROM bwhist_flags WHERE date IN (SELECT date FROM updates);
+  EXECUTE '
   INSERT INTO bwhist_flags (date, isexit, isguard, read, written)
   SELECT a.date, isexit, isguard, SUM(read_sum) as read,
       SUM(written_sum) AS written
@@ -663,14 +711,14 @@ CREATE OR REPLACE FUNCTION refresh_bwhist_flags() RETURNS INTEGER AS $$
              BOOL_OR(isguard) AS isguard
       FROM statusentry
       WHERE isrunning = TRUE
-        AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
-        AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+        AND validafter >= ''' || min_date || '''
+        AND validafter < ''' || max_date || '''
         AND DATE(validafter) IN (SELECT date FROM updates)
       GROUP BY 1, 2) a
   JOIN bwhist
   ON a.date = bwhist.date
   AND a.fingerprint = bwhist.fingerprint
-  GROUP BY 1, 2, 3;
+  GROUP BY 1, 2, 3';
   RETURN 1;
   END;
 $$ LANGUAGE plpgsql;
@@ -680,11 +728,19 @@ $$ LANGUAGE plpgsql;
 -- directory request statistics of directory mirrors with bandwidth
 -- histories.
 CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
-  BEGIN
+    DECLARE
+        min_date TIMESTAMP WITHOUT TIME ZONE;
+        max_date TIMESTAMP WITHOUT TIME ZONE;
+    BEGIN
+
+    min_date := (SELECT MIN(date) FROM updates);
+    max_date := (SELECT MAX(date) + 1 FROM updates);
+
   -- Start by deleting user statistics of the dates we're about to
   -- regenerate.
   DELETE FROM user_stats WHERE date IN (SELECT date FROM updates);
   -- Now insert new user statistics.
+  EXECUTE '
   INSERT INTO user_stats (date, country, r, dw, dr, drw, drr, bw, br, bwd,
       brd, bwr, brr, bwdr, brdr, bwp, brp, bwn, brn)
   SELECT
@@ -692,7 +748,7 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
          dirreq_stats_by_country.date AS date,
          dirreq_stats_by_country.country AS country,
          dirreq_stats_by_country.r AS r,
-         -- In order to weight the reported directory requests, we're
+         -- In order to weight the reported directory requests, we are
          -- counting bytes of relays (except directory authorities)
          -- matching certain criteria: whether or not they are reporting
          -- directory requests, whether or not they are reporting
@@ -759,14 +815,14 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
       ) dirreq_stats_split
       GROUP BY 1, 2, 3
     ) dirreq_stats_by_date
-    -- We're only interested in requests by directory mirrors, not
+    -- We are only interested in requests by directory mirrors, not
     -- directory authorities, so we exclude all relays with the Authority
     -- flag.
     RIGHT JOIN (
       SELECT fingerprint, DATE(validafter) AS date
       FROM statusentry
-      WHERE DATE(validafter) >= (SELECT MIN(date) FROM updates)
-      AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+      WHERE validafter >= ''' || min_date || '''
+      AND validafter < ''' || max_date || '''
       AND DATE(validafter) IN (SELECT date FROM updates)
       AND isauthority IS FALSE
       GROUP BY 1, 2
@@ -782,8 +838,8 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
     SELECT fingerprint, date, read_sum AS read, written_sum AS written,
            dirread_sum AS dirread, dirwritten_sum AS dirwritten
     FROM bwhist
-    WHERE date >= (SELECT MIN(date) FROM updates)
-    AND date <= (SELECT MAX(date) FROM updates)
+    WHERE date >= ''' || min_date || '''
+    AND date < ''' || max_date || '''
     AND date IN (SELECT date FROM updates)
   ) bwhist_by_relay
   ON dirreq_stats_by_country.date = bwhist_by_relay.date
@@ -794,8 +850,8 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
       SUM(CASE WHEN dirport > 0 THEN 1 ELSE NULL END) AS opendirport,
       SUM(CASE WHEN isauthority IS TRUE THEN 1 ELSE NULL END) AS authority
     FROM statusentry
-    WHERE DATE(validafter) >= (SELECT MIN(date) FROM updates)
-    AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+    WHERE validafter >= ''' || min_date || '''
+    AND validafter < ''' || max_date || '''
     AND DATE(validafter) IN (SELECT date FROM updates)
     GROUP BY 1, 2
   ) statusentry_by_relay
@@ -805,23 +861,23 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
     -- For each relay, tell if it has reported directory request
     -- statistics on a given date. Again, we have to take into account
     -- that statistics intervals cover more than one calendar date in most
-    -- cases. The exact number of requests isn't relevant here, but only
+    -- cases. The exact number of requests is not relevant here, but only
     -- whether the relay reported directory requests or not.
     SELECT fingerprint, date, 1 AS requests
     FROM (
       SELECT LOWER(source) AS fingerprint, DATE(statsend) AS date
       FROM dirreq_stats
-      WHERE DATE(statsend) >= (SELECT MIN(date) FROM updates)
-      AND DATE(statsend) <= (SELECT MAX(date) FROM updates)
+      WHERE DATE(statsend) >= ''' || min_date || '''
+      AND DATE(statsend) < ''' || max_date || '''
       AND DATE(statsend) IN (SELECT date FROM updates)
-      AND country = 'zy'
+      AND country = ''zy''
       UNION
       SELECT LOWER(source) AS fingerprint, DATE(statsend) - 1 AS date
       FROM dirreq_stats
-      WHERE DATE(statsend) - 1 >= (SELECT MIN(date) FROM updates)
-      AND DATE(statsend) - 1 <= (SELECT MAX(date) FROM updates)
-      AND DATE(statsend) - 1 IN (SELECT date FROM updates)
-      AND country = 'zy'
+      WHERE DATE(statsend) - 1 >= ''' || min_date || '''
+      AND DATE(statsend) - 1 < ''' || max_date || '''
+      AND DATE(statsend) IN (SELECT date FROM updates)
+      AND country = ''zy''
       AND EXTRACT(EPOCH FROM DATE(statsend)) -
       EXTRACT(EPOCH FROM statsend) + seconds > 0
     ) dirreq_stats_split
@@ -832,7 +888,7 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$
   WHERE dirreq_stats_by_country.country IS NOT NULL
   -- Group by date, country, and total reported directory requests,
   -- summing up the bandwidth histories.
-  GROUP BY 1, 2, 3;
+  GROUP BY 1, 2, 3';
   RETURN 1;
   END;
 $$ LANGUAGE plpgsql;



_______________________________________________
tor-commits mailing list
tor-commits@xxxxxxxxxxxxxxxxxxxx
https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-commits