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

[Ian Jackson <ian.jackson@xxxxxxxxxxxxx>]

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 <ian.jackson@xxxxxxxxxxxxx>
---
 sg-report-host-history | 12 ++++++++----
 1 file changed, 8 insertions(+), 4 deletions(-)

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