[OSSTEST PATCH 2/3] sg-report-flight: Actually look at retest flights (part 1)

[Ian Jackson <iwj@xxxxxxxxxxxxxx>]

The existing approach does not find retest flights.  This is because
it starts by looking for flights whose runvars say they built the
version in question, and then looks to see if they contain the
relevant job.

Retest flights don't contain build jobs; they reuse the builds from
the primary flight.

Rather than making a fully general recursion scheme (which would
involve adding another index so we could quickly find all flights
which refer to this one), we add a one-level recursion.

This recursion is restricted to the flights named on the command line.
This means it takes nearly no time (as opposed to searching the whole
db for things that might be relevant - see above re need for an
index).

We filter the command line flights, looking ones which refer to the
only the primarily found flights as build jobs.

Signed-off-by: Ian Jackson <iwj@xxxxxxxxxxxxxx>
---
 sg-report-flight | 25 ++++++++++++++++++++++++-
 1 file changed, 24 insertions(+), 1 deletion(-)

diff --git a/sg-report-flight b/sg-report-flight
index 40a3cc92..70cad230 100755
--- a/sg-report-flight
+++ b/sg-report-flight
@@ -315,6 +315,11 @@ END
     # Sadly this trick is not formally documented but it is our
     # best option.
 
+    my $cmdline_flight_cond =
+      join ' OR ',
+      (defined $specflight ? "f1.flight=$specflight" : "FALSE"),
+      map { "f1.flight=$_->{Flight}" } @refer_to_flights;
+
     my $flightsq= <<END;
       WITH
       relevant_flights AS (
@@ -327,9 +332,27 @@ $runvars_conds
             ORDER BY flight DESC
             OFFSET 0
             LIMIT 1000
+      ),
+      retest_flights AS (
+        SELECT DISTINCT f1.flight flight, f1.blessing blessing
+             FROM flights f1
+             JOIN jobs j USING (flight) 
+       CROSS JOIN relevant_flights f0
+            WHERE ($cmdline_flight_cond)
+              AND (
+                SELECT bool_and( val LIKE f0.flight || '.%' )
+                       IS TRUE
+                 FROM runvars r
+                WHERE r.flight=j.flight
+                  AND r.job=j.job 
+                  AND r.name LIKE '%buildjob'
+                  )
       )
       SELECT flight, jobs.status
-        FROM relevant_flights
+        FROM (
+           SELECT * FROM relevant_flights
+     UNION SELECT * FROM retest_flights
+             ) all_relevant_flights
 $flightsq_jobs_join
        WHERE (1=1)
 $flightsq_jobcond
-- 
2.20.1