* [OSSTEST PATCH 1/2] sg-report-job-history: Separate out $cond in queries @ 2015-02-09 18:07 Ian Jackson 2015-02-09 18:07 ` [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on Ian Jackson 0 siblings, 1 reply; 5+ messages in thread From: Ian Jackson @ 2015-02-09 18:07 UTC (permalink / raw) To: xen-devel; +Cc: Ian Jackson, Ian Campbell 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 ^ permalink raw reply related [flat|nested] 5+ messages in thread
* [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on 2015-02-09 18:07 [OSSTEST PATCH 1/2] sg-report-job-history: Separate out $cond in queries Ian Jackson @ 2015-02-09 18:07 ` Ian Jackson 2015-02-09 23:43 ` Ian Campbell 0 siblings, 1 reply; 5+ messages in thread From: Ian Jackson @ 2015-02-09 18:07 UTC (permalink / raw) To: xen-devel; +Cc: Ian Jackson, Ian Campbell 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 ^ permalink raw reply related [flat|nested] 5+ messages in thread
* Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on 2015-02-09 18:07 ` [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on Ian Jackson @ 2015-02-09 23:43 ` Ian Campbell 2015-02-10 10:52 ` Ian Jackson 0 siblings, 1 reply; 5+ messages in thread From: Ian Campbell @ 2015-02-09 23:43 UTC (permalink / raw) To: Ian Jackson; +Cc: xen-devel 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= ^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on 2015-02-09 23:43 ` Ian Campbell @ 2015-02-10 10:52 ` Ian Jackson 2015-02-10 11:01 ` Ian Campbell 0 siblings, 1 reply; 5+ messages in thread From: Ian Jackson @ 2015-02-10 10:52 UTC (permalink / raw) To: Ian Campbell; +Cc: xen-devel 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. ^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on 2015-02-10 10:52 ` Ian Jackson @ 2015-02-10 11:01 ` Ian Campbell 0 siblings, 0 replies; 5+ messages in thread From: Ian Campbell @ 2015-02-10 11:01 UTC (permalink / raw) To: Ian Jackson; +Cc: xen-devel 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> ^ permalink raw reply [flat|nested] 5+ messages in thread
end of thread, other threads:[~2015-02-10 11:01 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed -- links below jump to the message on this page -- 2015-02-09 18:07 [OSSTEST PATCH 1/2] sg-report-job-history: Separate out $cond in queries Ian Jackson 2015-02-09 18:07 ` [OSSTEST PATCH 2/2] sg-report-job-history: Show which host test ran on Ian Jackson 2015-02-09 23:43 ` Ian Campbell 2015-02-10 10:52 ` Ian Jackson 2015-02-10 11:01 ` Ian Campbell
This is an external index of several public inboxes, see mirroring instructions on how to clone and mirror all data and code used by this external index.