From 8c89e96d5937722b08cf4f5f024603bc30fbeffc Mon Sep 17 00:00:00 2001
From: Don Armstrong <don@donarmstrong.com>
Date: Mon, 7 Aug 2017 09:30:13 -0700
Subject: [PATCH] use COALESCE indexes for bug_status_cache

Add prepare_execute util to DB::Util
---
 Debbugs/DB.pm                          |  2 +-
 Debbugs/DB/Result/BugStatusCache.pm    | 14 +++++++++++++-
 Debbugs/DB/ResultSet/BugStatusCache.pm |  2 +-
 Debbugs/DB/Util.pm                     | 20 ++++++++++++++++++++
 bin/debbugs-installsql                 | 15 +++++++++++++++
 5 files changed, 50 insertions(+), 3 deletions(-)

diff --git a/Debbugs/DB.pm b/Debbugs/DB.pm
index 29abb21..e4ec1da 100644
--- a/Debbugs/DB.pm
+++ b/Debbugs/DB.pm
@@ -17,7 +17,7 @@ __PACKAGE__->load_namespaces;
 
 # This version must be incremented any time the schema changes so that
 # DBIx::Class::DeploymentHandler can do its work
-our $VERSION=6;
+our $VERSION=7;
 
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
 
diff --git a/Debbugs/DB/Result/BugStatusCache.pm b/Debbugs/DB/Result/BugStatusCache.pm
index 3d2dcc9..62acdac 100644
--- a/Debbugs/DB/Result/BugStatusCache.pm
+++ b/Debbugs/DB/Result/BugStatusCache.pm
@@ -210,6 +210,18 @@ __PACKAGE__->belongs_to(
 # Created by DBIx::Class::Schema::Loader v0.07046 @ 2017-03-04 10:59:03
 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:dgaCogdpUWo99BQhdH68Mg
 
+sub sqlt_deploy_hook {
+    my ($self, $sqlt_table) = @_;
+#     $sqlt_table->add_index(name => 'bug_status_cache_bug_suite_arch_idx',
+# 			   fields => ['bug',
+# 				      q{COALESCE(suite,0)},
+# 				      q{COALESCE(arch,0)},]
+# 			  );
+    for my $f (qw(bug status arch suite asof)) {
+	$sqlt_table->add_index(name => 'bug_status_cache_idx_'.$f,
+			       fields => [$f],
+			      );
+    }
+}
 
-# You can replace this text with custom code or comments, and it will be preserved on regeneration
 1;
diff --git a/Debbugs/DB/ResultSet/BugStatusCache.pm b/Debbugs/DB/ResultSet/BugStatusCache.pm
index 18edf0c..278d0e6 100644
--- a/Debbugs/DB/ResultSet/BugStatusCache.pm
+++ b/Debbugs/DB/ResultSet/BugStatusCache.pm
@@ -54,7 +54,7 @@ sub update_bug_status {
 INSERT INTO bug_status_cache AS bsc
 (bug,suite,arch,status,modified,asof)
 VALUES (?,?,?,?,NOW(),NOW())
-ON CONFLICT (bug,suite,arch) DO
+ON CONFLICT (bug,COALESCE(suite,0),COALESCE(arch,0)) DO
 UPDATE
  SET asof=NOW(),modified=CASE WHEN bsc.status=? THEN bsc.modified ELSE NOW() END
 RETURNING status;
diff --git a/Debbugs/DB/Util.pm b/Debbugs/DB/Util.pm
index 5565244..d241f33 100644
--- a/Debbugs/DB/Util.pm
+++ b/Debbugs/DB/Util.pm
@@ -32,6 +32,7 @@ BEGIN{
 
      @EXPORT = ();
      %EXPORT_TAGS = (select => [qw(select_one)],
+		     execute => [qw(prepare_execute)]
 		    );
      @EXPORT_OK = ();
      Exporter::export_ok_tags(keys %EXPORT_TAGS);
@@ -65,6 +66,25 @@ sub select_one {
     return (ref($results) and ref($results->[0]))?$results->[0][0]:undef;
 }
 
+=item prepare_execute
+
+	prepare_execute($dbh,$sql,@bind_vals)
+
+Prepares and executes a statement
+
+=cut
+
+sub prepare_execute {
+    my ($dbh,$sql,@bind_vals) = @_;
+    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();
+}
+
 
 =back
 
diff --git a/bin/debbugs-installsql b/bin/debbugs-installsql
index 317b272..892aef4 100755
--- a/bin/debbugs-installsql
+++ b/bin/debbugs-installsql
@@ -69,6 +69,7 @@ debbugs-installsql
 use vars qw($DEBUG);
 
 use Debbugs::DB;
+use Debbugs::DB::Util qw(prepare_execute);
 use aliased 'DBIx::Class::DeploymentHandler' => 'DH';
 
 my %options = (debug           => 0,
@@ -137,6 +138,20 @@ if ($options{current_version}) {
 } elsif ($options{install}) {
     $dh->prepare_install if $options{developer_prepare};
     $dh->install unless $options{developer_prepare};
+    ## this is lame, but because the current release of DeploymentHandler does
+    ## not support WHERE or quoted indexes properly (fixed in git), we create
+    ## these indexes manually here.
+    $schema->storage->
+	dbh_do(sub{my ($s,$dbh) = @_;
+		   prepare_execute($dbh,<<SQL);
+CREATE UNIQUE INDEX bug_status_cache_bug_col_suite_col_arch_idx ON
+ bug_status_cache(bug,COALESCE(suite,0),COALESCE(arch,0));
+CREATE UNIQUE INDEX bug_status_cache_bug_suite_idx ON
+ bug_status_cache(bug,suite) WHERE arch is NULL;
+CREATE UNIQUE INDEX bug_status_cache_bug_idx ON
+ bug_status_cache(bug) WHERE arch is NULL AND suite IS NULL;
+SQL
+		   });
 } elsif ($options{upgrade}) {
     $dh->prepare_deploy if $options{developer_prepare};
     $dh->prepare_upgrade() if $options{developer_prepare};
-- 
2.39.2