[OSSTEST PATCH 02/11] sg-report-host-history: Find flight limit by flight start date

[Ian Jackson]

By default we look for anything in (roughly) the last year.

This query is in fact quite fast because the flights table is small.

There is still the per-host limit of $limit (2000) recent runs.

Signed-off-by: Ian Jackson <ian.jack...@eu.citrix.com>
---
 sg-report-host-history | 56 ++++++++++++++++++++----------------------
 1 file changed, 27 insertions(+), 29 deletions(-)

diff --git a/sg-report-host-history b/sg-report-host-history
index 54738e68..5dd875c1 100755
--- a/sg-report-host-history
+++ b/sg-report-host-history
@@ -29,6 +29,7 @@ use POSIX;
 use Osstest::Executive qw(:DEFAULT :colours);
 
 our $limit= 2000;
+our $timelimit= 86400 * (366 + 14);
 our $flightlimit;
 our $htmlout = ".";
 our $read_existing=1;
@@ -45,6 +46,8 @@ while (@ARGV && $ARGV[0] =~ m/^-/) {
     last if m/^--?$/;
     if (m/^--(limit)\=([1-9]\d*)$/) {
         $$1= $2;
+    } elsif (m/^--time-limit\=([1-9]\d*)$/) {
+        $timelimit= $1;
     } elsif (m/^--flight-limit\=([1-9]\d*)$/) {
        $flightlimit= $1;
     } elsif (restrictflight_arg($_)) {
@@ -108,38 +111,33 @@ sub read_existing_logs ($) {
 }
 
 sub computeflightsrange () {
-    if (!$flightlimit) {
-       my $flagscond =
-           '('.join(' OR ', map { "f.hostflag = 'blessed-$_'" } 
@blessings).')';
-       my $nhostsq = db_prepare(<<END);
-           SELECT count(*)
-             FROM resources r
-            WHERE restype='host'
-              AND EXISTS (SELECT 1
-                            FROM hostflags f
-                           WHERE f.hostname=r.resname
-                             AND $flagscond)
+    if ($flightlimit) {
+       my $minflightsq = db_prepare(<<END);
+           SELECT flight
+             FROM (
+               SELECT flight
+                 FROM flights
+                WHERE $restrictflight_cond
+                ORDER BY flight DESC
+                LIMIT $flightlimit
+             ) f
+             ORDER BY flight ASC
+             LIMIT 1
 END
-        $nhostsq->execute();
-       my ($nhosts) = $nhostsq->fetchrow_array();
-       print DEBUG "COUNTED $nhosts hosts\n";
-       $flightlimit = $nhosts * $limit * 2;
-    }
-
-    my $minflightsq = db_prepare(<<END);
-       SELECT flight
-         FROM (
+       $minflightsq->execute();
+       ($minflight,) = $minflightsq->fetchrow_array();
+    } else {
+       my $minflightsq = db_prepare(<<END);
            SELECT flight
-             FROM flights
-             WHERE $restrictflight_cond
-            ORDER BY flight DESC
-            LIMIT $flightlimit
-         ) f
-         ORDER BY flight ASC
-         LIMIT 1
+              FROM flights
+             WHERE started >= ?
+          ORDER BY flight ASC
+             LIMIT 1
 END
-    $minflightsq->execute();
-    ($minflight,) = $minflightsq->fetchrow_array();
+       my $now = time // die $!;
+        $minflightsq->execute($now - $timelimit);
+       ($minflight,) = $minflightsq->fetchrow_array();
+    }
     $minflight //= 0;
 
     $flightcond = "(flight > $minflight)";
-- 
2.20.1