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
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