From: Don Armstrong <don@donarmstrong.com>
Date: Wed, 1 Jul 2020 20:05:35 +0000 (-0700)
Subject: Debbugs::DB::Util is now a component of Debbugs::DB
X-Git-Url: https://git.donarmstrong.com/?p=debbugs.git;a=commitdiff_plain;h=dfd1d5b50f0f2d1bae6deb5168de9f0bed11faa9

Debbugs::DB::Util is now a component of Debbugs::DB

 - select_one and prepare_select can be called from $schema
---

diff --git a/lib/Debbugs/DB.pm b/lib/Debbugs/DB.pm
index 5f6bd04..bcf4788 100644
--- a/lib/Debbugs/DB.pm
+++ b/lib/Debbugs/DB.pm
@@ -19,6 +19,8 @@ __PACKAGE__->load_namespaces;
 # DBIx::Class::DeploymentHandler can do its work
 our $VERSION=12;
 
+__PACKAGE__->load_components('+Debbugs::DB::Util');
+
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
 
 # override connect to handle just passing a bare service
diff --git a/lib/Debbugs/DB/ResultSet/BinAssociation.pm b/lib/Debbugs/DB/ResultSet/BinAssociation.pm
index 5756199..916741b 100644
--- a/lib/Debbugs/DB/ResultSet/BinAssociation.pm
+++ b/lib/Debbugs/DB/ResultSet/BinAssociation.pm
@@ -24,23 +24,15 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
-
 sub insert_suite_bin_ver_association {
     my ($self,$suite_id,$bin_ver_id) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$s_id,$bv_id) = @_;
-		   return select_one($dbh,<<'SQL',$s_id,$bv_id);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$suite_id,$bin_ver_id);
 INSERT INTO bin_associations (suite,bin)
    VALUES (?,?) ON CONFLICT (suite,bin) DO
     UPDATE SET modified = NOW()
    RETURNING id;
 SQL
-	       },
-	       $suite_id,$bin_ver_id
-	      );
 }
 
 1;
diff --git a/lib/Debbugs/DB/ResultSet/BinPkg.pm b/lib/Debbugs/DB/ResultSet/BinPkg.pm
index e938cda..97478ae 100644
--- a/lib/Debbugs/DB/ResultSet/BinPkg.pm
+++ b/lib/Debbugs/DB/ResultSet/BinPkg.pm
@@ -24,8 +24,6 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
 sub bin_pkg_and_ver_in_suite {
     my ($self,$suite) = @_;
     $suite = $self->result_source->schema->
@@ -43,22 +41,16 @@ sub bin_pkg_and_ver_in_suite {
 
 sub get_bin_pkg_id {
     my ($self,$pkg) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$bin_pkg) = @_;
-		   return select_one($dbh,<<'SQL',$bin_pkg);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$pkg);
 SELECT id FROM bin_pkg where pkg = ?;
 SQL
-	       },
-	       $pkg
-	      );
 }
+
 sub get_or_create_bin_pkg_id {
     my ($self,$pkg) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$bin_pkg) = @_;
-		   return select_one($dbh,<<'SQL',$bin_pkg,$bin_pkg);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$pkg,$pkg);
 WITH ins AS (
 INSERT INTO bin_pkg (pkg)
 VALUES (?) ON CONFLICT (pkg) DO NOTHING RETURNING id
@@ -68,9 +60,6 @@ UNION ALL
 SELECT id FROM bin_pkg where pkg = ?
 LIMIT 1;
 SQL
-	       },
-	       $pkg
-	      );
 }
 
 1;
diff --git a/lib/Debbugs/DB/ResultSet/BinVer.pm b/lib/Debbugs/DB/ResultSet/BinVer.pm
index fcd8b59..76fed58 100644
--- a/lib/Debbugs/DB/ResultSet/BinVer.pm
+++ b/lib/Debbugs/DB/ResultSet/BinVer.pm
@@ -24,15 +24,10 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
-
 sub get_bin_ver_id {
     my ($self,$bin_pkg_id,$bin_ver,$arch_id,$src_ver_id) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$bp_id,$bv,$a_id,$sv_id) = @_;
-		   return select_one($dbh,<<'SQL',
+    return $self->result_source->schema->
+	select_one(<<'SQL',
 WITH ins AS (
 INSERT INTO bin_ver (bin_pkg,src_ver,arch,ver)
 VALUES (?,?,?,?) ON CONFLICT (bin_pkg,arch,ver) DO NOTHING RETURNING id
@@ -42,13 +37,10 @@ UNION ALL
 SELECT id FROM bin_ver WHERE bin_pkg = ? AND arch = ? AND ver = ?
 LIMIT 1;
 SQL
-				     $bp_id,$sv_id,
-				     $a_id,$bv,
-				     $bp_id,$a_id,
-				     $bv);
-	       },
-	       $bin_pkg_id,$bin_ver,$arch_id,$src_ver_id
-	      );
+		   $bin_pkg_id,$src_ver_id,
+		   $arch_id,$bin_ver,
+		   $bin_pkg_id,$arch_id,
+		   $bin_ver);
 }
 
 1;
diff --git a/lib/Debbugs/DB/ResultSet/Bug.pm b/lib/Debbugs/DB/ResultSet/Bug.pm
index 265d4d9..bb25793 100644
--- a/lib/Debbugs/DB/ResultSet/Bug.pm
+++ b/lib/Debbugs/DB/ResultSet/Bug.pm
@@ -24,8 +24,6 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
 use List::AllUtils qw(natatime);
 
 
@@ -68,17 +66,11 @@ actually be calling C<Debbugs::DB::Load::load_bug> instead of this function.
 
 sub quick_insert_bug {
     my ($self,$bug) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$b) = @_;
-		   select_one($dbh,<<'SQL',$b);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$bug);
 INSERT INTO bug (id,subject,severity) VALUES (?,'',1)
 ON CONFLICT (id) DO NOTHING RETURNING id;
 SQL
-	       },
-	       $bug
-	      );
-
 }
 
 
diff --git a/lib/Debbugs/DB/ResultSet/BugStatusCache.pm b/lib/Debbugs/DB/ResultSet/BugStatusCache.pm
index 7ad8f0e..e39fcbb 100644
--- a/lib/Debbugs/DB/ResultSet/BugStatusCache.pm
+++ b/lib/Debbugs/DB/ResultSet/BugStatusCache.pm
@@ -24,8 +24,6 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
 use List::AllUtils qw(natatime);
 
 
@@ -45,11 +43,9 @@ Update the status information for a particular bug at a particular suite
 =cut
 
 sub update_bug_status {
-    my ($self,@args) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$bug,$suite,$arch,$status,$modified,$asof) = @_;
-		   select_one($dbh,<<'SQL',$bug,$suite,$arch,$status,$status);
+    my ($self,$bug,$suite,$arch,$status,$modified,$asof) = @_;
+    return $self->result_source->schema->
+	select_one(<<'SQL',$bug,$suite,$arch,$status,$status);
 INSERT INTO bug_status_cache AS bsc
 (bug,suite,arch,status,modified,asof)
 VALUES (?,?,?,?,NOW(),NOW())
@@ -58,9 +54,6 @@ UPDATE
  SET asof=NOW(),modified=CASE WHEN bsc.status=? THEN bsc.modified ELSE NOW() END
 RETURNING status;
 SQL
-	       },
-           @args
-	      );
 }
 
 
diff --git a/lib/Debbugs/DB/ResultSet/Correspondent.pm b/lib/Debbugs/DB/ResultSet/Correspondent.pm
index d722a5f..02df40b 100644
--- a/lib/Debbugs/DB/ResultSet/Correspondent.pm
+++ b/lib/Debbugs/DB/ResultSet/Correspondent.pm
@@ -27,7 +27,6 @@ use base 'DBIx::Class::ResultSet';
 use Debbugs::DB::Util qw(select_one);
 
 use Debbugs::Common qw(getparsedaddrs);
-use Debbugs::DB::Util qw(select_one);
 use Scalar::Util qw(blessed);
 
 sub get_correspondent_id {
@@ -54,10 +53,9 @@ sub get_correspondent_id {
     if (defined $rs) {
 	return $rs->{id};
     }
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$addr,$full_name) = @_;
-		   my $ci = select_one($dbh,<<'SQL',$addr,$addr);
+    my $ci =
+	$self->result_source->schema->
+	select_one(<<'SQL',$addr,$addr);
 WITH ins AS (
 INSERT INTO correspondent (addr) VALUES (?)
  ON CONFLICT (addr) DO NOTHING RETURNING id
@@ -67,8 +65,9 @@ UNION ALL
 SELECT id FROM correspondent WHERE addr = ?
 LIMIT 1;
 SQL
-		   if (defined $full_name) {
-		       select_one($dbh,<<'SQL',$ci,$full_name);
+    if (defined $full_name) {
+	$self->result_source->schema->
+	    select_one(<<'SQL',$ci,$full_name);
 WITH ins AS (
 INSERT INTO correspondent_full_name (correspondent,full_name)
    VALUES (?,?) ON CONFLICT (correspondent,full_name) DO NOTHING RETURNING 1
@@ -76,13 +75,8 @@ INSERT INTO correspondent_full_name (correspondent,full_name)
 UNION ALL
 SELECT 1;
 SQL
-		   }
-		   return $ci;
-},
-	       $addr,
-	       $full_name
-	      );
-
+    }
+    return $ci;
 }
 
 
diff --git a/lib/Debbugs/DB/ResultSet/Maintainer.pm b/lib/Debbugs/DB/ResultSet/Maintainer.pm
index 7c889f3..e90e761 100644
--- a/lib/Debbugs/DB/ResultSet/Maintainer.pm
+++ b/lib/Debbugs/DB/ResultSet/Maintainer.pm
@@ -24,9 +24,6 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
-
 =over
 
 =item get_maintainers 
@@ -90,10 +87,8 @@ sub get_maintainer_id {
     my $ci =
 	$self->result_source->schema->resultset('Correspondent')->
 	get_correspondent_id($maint);
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$maint,$ci) = @_;
-		   return select_one($dbh,<<'SQL',$maint,$ci,$maint);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$maint,$ci,$maint);
 WITH ins AS (
 INSERT INTO maintainer (name,correspondent) VALUES (?,?)
 ON CONFLICT (name) DO NOTHING RETURNING id
@@ -103,9 +98,6 @@ UNION ALL
 SELECT id FROM maintainer WHERE name = ?
 LIMIT 1;
 SQL
-	       },
-	       $maint,$ci
-	      );
 }
 
 =back
diff --git a/lib/Debbugs/DB/ResultSet/Message.pm b/lib/Debbugs/DB/ResultSet/Message.pm
index 08509ce..f5cf894 100644
--- a/lib/Debbugs/DB/ResultSet/Message.pm
+++ b/lib/Debbugs/DB/ResultSet/Message.pm
@@ -24,14 +24,10 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
 sub get_message_id {
     my ($self,$msg_id,$from,$to,$subject) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($dbh,$msg_id,$from,$to,$subject) = @_;
-		   my $mi = select_one($dbh,<<'SQL',@_[1..$#_],@_[1..$#_]);
+    return $self->result_source->schema->
+	select_one(<<'SQL',@_,@_);
 WITH ins AS (
 INSERT INTO message (msgid,from_complete,to_complete,subject) VALUES (?,?,?,?)
  ON CONFLICT (msgid,from_complete,to_complete,subject) DO NOTHING RETURNING id
@@ -42,11 +38,6 @@ SELECT id FROM correspondent WHERE msgid=? AND from_complete = ?
 AND to_complete = ? AND subject = ?
 LIMIT 1;
 SQL
-		   return $mi;
-},
-	       @_[1..$#_]
-	      );
-
 }
 
 
diff --git a/lib/Debbugs/DB/ResultSet/SrcAssociation.pm b/lib/Debbugs/DB/ResultSet/SrcAssociation.pm
index 047c54d..3b7e557 100644
--- a/lib/Debbugs/DB/ResultSet/SrcAssociation.pm
+++ b/lib/Debbugs/DB/ResultSet/SrcAssociation.pm
@@ -24,23 +24,15 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
-
 sub insert_suite_src_ver_association {
     my ($self,$suite_id,$src_ver_id) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$suite_id,$src_ver_id) = @_;
-		   return select_one($dbh,<<'SQL',$suite_id,$src_ver_id);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$suite_id,$src_ver_id);
 INSERT INTO src_associations (suite,source)
    VALUES (?,?) ON CONFLICT (suite,source) DO
      UPDATE SET modified = NOW()
 RETURNING id;
 SQL
-	       },
-	       $suite_id,$src_ver_id
-	      );
 }
 
 1;
diff --git a/lib/Debbugs/DB/ResultSet/SrcPkg.pm b/lib/Debbugs/DB/ResultSet/SrcPkg.pm
index 36fab13..43cc774 100644
--- a/lib/Debbugs/DB/ResultSet/SrcPkg.pm
+++ b/lib/Debbugs/DB/ResultSet/SrcPkg.pm
@@ -24,8 +24,6 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
 sub src_pkg_and_ver_in_suite {
     my ($self,$suite) = @_;
     if (ref($suite)) {
@@ -59,23 +57,16 @@ sub src_pkg_and_ver_in_suite {
 
 sub get_src_pkg_id {
     my ($self,$source) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$src_pkg) = @_;
-		   return select_one($dbh,<<'SQL',$src_pkg);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$source);
 SELECT id FROM src_pkg where pkg = ?;
 SQL
-	       },
-	       $source
-	      );
 }
 
 sub get_or_create_src_pkg_id {
     my ($self,$source) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$source) = @_;
-		   return select_one($dbh,<<'SQL',$source,$source);
+    return $self->result_source->schema->
+	select_one(<<'SQL',$source,$source);
 WITH ins AS (
 INSERT INTO src_pkg (pkg)
    VALUES (?) ON CONFLICT (pkg,disabled) DO NOTHING RETURNING id
@@ -85,9 +76,6 @@ UNION ALL
 SELECT id FROM src_pkg where pkg = ? AND disabled = 'infinity'::timestamptz
 LIMIT 1;
 SQL
-	       },
-	       $source
-	      );
 }
 
 1;
diff --git a/lib/Debbugs/DB/ResultSet/SrcVer.pm b/lib/Debbugs/DB/ResultSet/SrcVer.pm
index 254816c..ddc4c0f 100644
--- a/lib/Debbugs/DB/ResultSet/SrcVer.pm
+++ b/lib/Debbugs/DB/ResultSet/SrcVer.pm
@@ -24,25 +24,17 @@ use warnings;
 
 use base 'DBIx::Class::ResultSet';
 
-use Debbugs::DB::Util qw(select_one);
-
-
 sub get_src_ver_id {
     my ($self,$src_pkg_id,$src_ver,$maint_id) = @_;
-    return $self->result_source->schema->storage->
-	dbh_do(sub {
-		   my ($s,$dbh,$src_pkg_id,$src_ver,$maint_id) = @_;
-		   return select_one($dbh,<<'SQL',
+    return $self->result_source->schema->
+	select_one(<<'SQL',
 INSERT INTO src_ver (src_pkg,ver,maintainer)
    VALUES (?,?,?) ON CONFLICT (src_pkg,ver) DO
      UPDATE SET maintainer = ?
    RETURNING id;
 SQL
-				     $src_pkg_id,$src_ver,
-				     $maint_id,$maint_id);
-	       },
-	       $src_pkg_id,$src_ver,$maint_id
-	      );
+		   $src_pkg_id,$src_ver,
+		   $maint_id,$maint_id);
 }
 
 1;
diff --git a/lib/Debbugs/DB/Util.pm b/lib/Debbugs/DB/Util.pm
index d241f33..7c52a04 100644
--- a/lib/Debbugs/DB/Util.pm
+++ b/lib/Debbugs/DB/Util.pm
@@ -23,21 +23,10 @@ None known.
 
 use warnings;
 use strict;
-use vars qw($VERSION $DEBUG %EXPORT_TAGS @EXPORT_OK @EXPORT);
-use base qw(Exporter);
-
-BEGIN{
-     ($VERSION) = q$Revision$ =~ /^Revision:\s+([^\s+])/;
-     $DEBUG = 0 unless defined $DEBUG;
-
-     @EXPORT = ();
-     %EXPORT_TAGS = (select => [qw(select_one)],
-		     execute => [qw(prepare_execute)]
-		    );
-     @EXPORT_OK = ();
-     Exporter::export_ok_tags(keys %EXPORT_TAGS);
-     $EXPORT_TAGS{all} = [@EXPORT_OK];
-}
+
+use base qw(DBIx::Class);
+
+use Debbugs::Common qw(open_compressed_file);
 
 =head2 select
 
@@ -47,42 +36,69 @@ Routines for select requests
 
 =item select_one
 
-	select_one($dbh,$sql,@bind_vals)
+	$schema->select_one($sql,@bind_vals)
 
 Returns the first column from the first row returned from a select statement
 
 =cut
 
 sub select_one {
-    my ($dbh,$sql,@bind_vals) = @_;
-    my $sth = $dbh->
+    my ($self,$sql,@bind_vals) = @_;
+    my $results =
+	$self->storage->
+	dbh_do(sub {
+		   my ($s,$dbh) = @_;
+		   my $sth = $dbh->
         prepare_cached($sql,
                       {dbi_dummy => __FILE__.__LINE__ })
         or die "Unable to prepare statement: $sql";
-    $sth->execute(@bind_vals) or
-        die "Unable to select one: ".$dbh->errstr();
-    my $results = $sth->fetchall_arrayref([0]);
-    $sth->finish();
+		   $sth->execute(@bind_vals) or
+		       die "Unable to select one: ".$dbh->errstr();
+		   my $results = $sth->fetchall_arrayref([0]);
+		   $sth->finish();
+		   return $results;
+	       });
     return (ref($results) and ref($results->[0]))?$results->[0][0]:undef;
 }
 
 =item prepare_execute
 
-	prepare_execute($dbh,$sql,@bind_vals)
+	$schema->prepare_execute($sql,@bind_vals)
 
 Prepares and executes a statement
 
 =cut
 
 sub prepare_execute {
-    my ($dbh,$sql,@bind_vals) = @_;
-    my $sth = $dbh->
-        prepare_cached($sql,
+    my ($self,$sql,@bind_vals) = @_;
+    $self->storage->
+	dbh_do(sub {
+		   my ($s,$dbh) = @_;
+		   my $sth = $dbh->
+		       prepare_cached($sql,
                       {dbi_dummy => __FILE__.__LINE__ })
-        or die "Unable to prepare statement: $sql";
-    $sth->execute(@bind_vals) or
-        die "Unable to execute statement: ".$dbh->errstr();
-    $sth->finish();
+		       or die "Unable to prepare statement: $sql";
+		   $sth->execute(@bind_vals) or
+		       die "Unable to execute statement: ".$dbh->errstr();
+		   $sth->finish();
+	       });
+}
+
+=item sql_file_in_txn
+
+C<sql_file_in_txn();>
+
+
+
+=cut
+sub sql_file_in_txn {
+    my ($self,$fn) = @_;
+    my $fh = open_compressed_file($fn) or
+	die "Unable to open $fn for reading: $!";
+    local $/;
+    my $sql = <$fh>;
+    defined($sql) or die "Unable to read from file: $!";
+    $self->prepare_execute($sql);
 }