[OSSTEST PATCH 1/2] sg-report-job-history: Separate out $cond in queries

[OSSTEST PATCH 1/2] sg-report-job-history: Separate out $cond in queries

[Ian Jackson]

We are going to want to reuse this, so separate out the computation of
$cond and @params.

Signed-off-by: Ian Jackson <Ian.Jackson@eu.citrix.com>
---
 sg-report-job-history |   15 ++++++---------
 1 file changed, 6 insertions(+), 9 deletions(-)

diff --git a/sg-report-job-history b/sg-report-job-history
index ee021b6..479e347 100755
--- a/sg-report-job-history
+++ b/sg-report-job-history
@@ -155,24 +155,21 @@ sub processjobbranch ($$) {
     my @test_rows;
 
     my $blessingscond= '('.join(' OR ', map { "blessing=?" } @blessings).')';
-    my $stmt= <<END;
-        SELECT *
-          FROM jobs JOIN flights USING (flight)
-         WHERE job = ?
-           AND $blessingscond
-END
+    my $cond = "job = ? AND $blessingscond";
     my (@params) = ($j, @blessings);
     if (defined $bra) {
-        $stmt .= <<END;
+        $cond .= <<END;
            AND branch = ?
 END
         push @params, $bra;
     }
-    $stmt .= <<END;
+    my $flightsq= $dbh_tests->prepare(<<END);
+        SELECT *
+          FROM jobs JOIN flights USING (flight)
+         WHERE ($cond)
       ORDER BY flight DESC
          LIMIT 100
 END
-    my $flightsq= $dbh_tests->prepare($stmt);
     $flightsq->execute(@params);
 
     while (my $f= $flightsq->fetchrow_hashref()) {
-- 
1.7.10.4

[OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on

[Ian Jackson]

Add a column listing the host(s) used.  We first find the relevant set
of host runvars (with a SELECT DISTINCT) and then look up each var for
each actual flight.

We do the pattern-matching on runvar names in perl to avoid giving the
postgresql optimiser a chance to turn this query into a full table
scan of the runvars table.  (A previous iteration of this patch
searched, in the per-flight loop, for all runvars whose name was
appropriately LIKE, with appalling performance.)

Signed-off-by: Ian Jackson <Ian.Jackson@eu.citrix.com>
---
 sg-report-job-history |   28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)

diff --git a/sg-report-job-history b/sg-report-job-history
index 479e347..3e4b0ef 100755
--- a/sg-report-job-history
+++ b/sg-report-job-history
@@ -172,9 +172,34 @@ END
 END
     $flightsq->execute(@params);
 
+    my $hostsq= $dbh_tests->prepare(<<END);
+        SELECT DISTINCT name
+	 FROM runvars
+	 JOIN flights USING (flight)
+	WHERE ($cond)
+     ORDER BY name;
+END
+    $hostsq->execute(@params);
+    my @hostvarcols;
+    while (my ($hostvar) = $hostsq->fetchrow_array()) {
+	next unless $hostvar =~ m/(^|_)host$/;
+	push @hostvarcols, $hostvar;
+    }
+
+    my $hostq= $dbh_tests->prepare(<<END);
+        SELECT val FROM runvars WHERE flight=? AND job=? AND name=?
+END
+
     while (my $f= $flightsq->fetchrow_hashref()) {
         my $ri= run_getinfo($f);
 
+	$ri->{Hosts} = [ ];
+	foreach my $hostvar (@hostvarcols) {
+	    $hostq->execute($f->{flight}, $f->{job}, $hostvar);
+	    my ($host) = $hostq->fetchrow_array();
+	    push @{ $ri->{Hosts} }, ($host // "-");
+	}
+
         my %revisions;
         add_revisions(\%revisions, $f->{flight}, $f->{job}, '');
         
@@ -217,6 +242,7 @@ END
         print H "<h1>$title</h1>\n";
         print H "<table rules=all>";
         print H "<tr><th>flight</th><th>branch</th><th>failure</th>\n";
+        print H "<th>", (join ", ", @hostvarcols), "</th>\n";
         foreach my $c (@rev_grid_cols) {
             print H "<th>".encode_entities($c)."</th>\n";
         }
@@ -236,6 +262,8 @@ END
             print H "<td $colour><a href=\"$url\">".
                 encode_entities($r->{Summary})."</a></td>\n";
             my $lastrev;
+            my $hosts = join ", ", map { $_ // "-" } @{ $r->{Hosts} };
+            print H "<td>".encode_entities($hosts)."</td>\n";
             foreach my $i (0..$#rev_grid_cols) {
                 my $v= $r->{Revisions}[$i];
                 my $same=
-- 
1.7.10.4

Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on

[Ian Campbell]

On Mon, 2015-02-09 at 18:07 +0000, Ian Jackson wrote:
> Add a column

Did you mean row? (looks to be in the HTML, as opposed to the email)

>  listing the host(s) used.  We first find the relevant set
> of host runvars (with a SELECT DISTINCT) and then look up each var for
> each actual flight.
> 
> We do the pattern-matching on runvar names in perl to avoid giving the
> postgresql optimiser a chance to turn this query into a full table
> scan of the runvars table.  (A previous iteration of this patch
> searched, in the per-flight loop, for all runvars whose name was
> appropriately LIKE, with appalling performance.)
> 
> Signed-off-by: Ian Jackson <Ian.Jackson@eu.citrix.com>
> ---
>  sg-report-job-history |   28 ++++++++++++++++++++++++++++
>  1 file changed, 28 insertions(+)
> 
> diff --git a/sg-report-job-history b/sg-report-job-history
> index 479e347..3e4b0ef 100755
> --- a/sg-report-job-history
> +++ b/sg-report-job-history
> @@ -172,9 +172,34 @@ END
>  END
>      $flightsq->execute(@params);
>  
> +    my $hostsq= $dbh_tests->prepare(<<END);
> +        SELECT DISTINCT name
> +	 FROM runvars
> +	 JOIN flights USING (flight)
> +	WHERE ($cond)
> +     ORDER BY name;
> +END
> +    $hostsq->execute(@params);
> +    my @hostvarcols;
> +    while (my ($hostvar) = $hostsq->fetchrow_array()) {
> +	next unless $hostvar =~ m/(^|_)host$/;
> +	push @hostvarcols, $hostvar;
> +    }
> +
> +    my $hostq= $dbh_tests->prepare(<<END);
> +        SELECT val FROM runvars WHERE flight=? AND job=? AND name=?
> +END
> +
>      while (my $f= $flightsq->fetchrow_hashref()) {
>          my $ri= run_getinfo($f);
>  
> +	$ri->{Hosts} = [ ];
> +	foreach my $hostvar (@hostvarcols) {
> +	    $hostq->execute($f->{flight}, $f->{job}, $hostvar);
> +	    my ($host) = $hostq->fetchrow_array();
> +	    push @{ $ri->{Hosts} }, ($host // "-");
> +	}
> +
>          my %revisions;
>          add_revisions(\%revisions, $f->{flight}, $f->{job}, '');
>          
> @@ -217,6 +242,7 @@ END
>          print H "<h1>$title</h1>\n";
>          print H "<table rules=all>";
>          print H "<tr><th>flight</th><th>branch</th><th>failure</th>\n";
> +        print H "<th>", (join ", ", @hostvarcols), "</th>\n";
>          foreach my $c (@rev_grid_cols) {
>              print H "<th>".encode_entities($c)."</th>\n";
>          }
> @@ -236,6 +262,8 @@ END
>              print H "<td $colour><a href=\"$url\">".
>                  encode_entities($r->{Summary})."</a></td>\n";
>              my $lastrev;
> +            my $hosts = join ", ", map { $_ // "-" } @{ $r->{Hosts} };
> +            print H "<td>".encode_entities($hosts)."</td>\n";
>              foreach my $i (0..$#rev_grid_cols) {
>                  my $v= $r->{Revisions}[$i];
>                  my $same=

Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on

[Ian Jackson]

Ian Campbell writes ("Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on"):
> On Mon, 2015-02-09 at 18:07 +0000, Ian Jackson wrote:
> > Add a column
> 
> Did you mean row? (looks to be in the HTML, as opposed to the email)

No, this is in the job history page.

Ie changing this:
   http://www.chiark.greenend.org.uk/~xensrcts/results/history.test-amd64-i386-libvirt.html
to look more like this:
   http://xenbits.xen.org/people/iwj/2015/history.test-amd64-i386-libvirt.html

Ian.

Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on

[Ian Campbell]

On Tue, 2015-02-10 at 10:52 +0000, Ian Jackson wrote:
> Ian Campbell writes ("Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on"):
> > On Mon, 2015-02-09 at 18:07 +0000, Ian Jackson wrote:
> > > Add a column
> > 
> > Did you mean row? (looks to be in the HTML, as opposed to the email)
> 
> No, this is in the job history page.
> 
> Ie changing this:
>    http://www.chiark.greenend.org.uk/~xensrcts/results/history.test-amd64-i386-libvirt.html
> to look more like this:
>    http://xenbits.xen.org/people/iwj/2015/history.test-amd64-i386-libvirt.html

Ah, that makes perfect sense, and is more useful than either of the
things I was thinking of...

Both patches:
Acked-by: Ian Campbell <ian.campbell@citrix.com>