Mailing List Archive

[OSSTEST PATCH v2 21/41] sg-report-host-history: Find flight limit by flight start date
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.jackson@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 9730ae7a..a159df3e 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