[OSSTEST,10/60] history reporting (nfc): Bind by name in cacheable_query

Commit Message

Ian Jackson Aug. 14, 2020, 5:21 p.m. UTC

cacheable_query used to simply pass $jr->{flight} and ->{job}.  But we
want this to be reuseable for other kinds of query, with different
cache keys.

So bind by name: we expect the caller to use :name placeholders in the
query.  We can then look through the prepared query parameters, and
fish the corresponding values out of $jr.

Signed-off-by: Ian Jackson <[e-mail]>
---
 sg-report-host-history | 12 ++++++++----
 1 file changed, 8 insertions(+), 4 deletions(-)

Patch

diff --git a/sg-report-host-history b/sg-report-host-history
index 90369ce4..8c5cd105 100755
--- a/sg-report-host-history
+++ b/sg-report-host-history
@@ -197,7 +197,11 @@  sub cacheable_fn ($$$) {
 sub cacheable_query ($$$) {
     my ($q, $jr, $cachekey) = @_;
     cacheable_fn($jr, $cachekey, sub {
-	$q->execute($jr->{flight}, $jr->{job});
+	foreach my $k (keys %{ $q->{ParamTypes} }) {
+	    $k =~ m/^:/ or die "$k ?";
+	    $q->bind_param($k, $jr->{$'} // die "$k ?");
+	}
+	$q->execute();
 	return $q->fetchrow_hashref();
     });
 }
@@ -259,7 +263,7 @@  sub reporthost ($) {
     our $endedq //= db_prepare(<<END);
 	SELECT finished, testid, status AS laststepstatus
 	  FROM steps
-	 WHERE flight=? AND job=? AND finished IS NOT NULL
+	 WHERE flight=:flight AND job=:job AND finished IS NOT NULL
 	 ORDER BY finished DESC
 	 LIMIT 1
 END
@@ -267,13 +271,13 @@  END
     our $infoq //= db_prepare(<<END);
 	SELECT blessing, branch, intended
 	  FROM flights
-	 WHERE flight=? AND ?!='X'
+	 WHERE flight=:flight AND :job != 'X'
 END
 
     our $allocdq //= db_prepare(<<END);
 	SELECT testid, finished, status
 	  FROM steps
-	 WHERE flight=? AND job=?
+	 WHERE flight=:flight AND job=:job
 	   AND (testid='hosts-allocate' OR step='ts-hosts-allocate')
 	 ORDER BY finished ASC
 	 LIMIT 1