use COALESCE indexes for bug_status_cache

Add prepare_execute util to DB::Util

diff --git a/Debbugs/DB.pm b/Debbugs/DB.pm
index 29abb21efac8f6ee686f2e3ea26c831bfcd8185a..e4ec1dad446986da3580455b88fab8528e703777 100644 (file)
--- 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 3d2dcc9d9a277376529138dfedfcc2fa86e1827d..62acdaca04ea4163d80e064b6fd2e471f187e35b 100644 (file)
--- 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 18edf0c8df28188137af18c3de3b7493900eb131..278d0e615c3b8cdfdf67f820ae400e1dba7d83dc 100644 (file)
--- 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 556524415a177236f6c818db3c4eb9ecd20bd034..d241f3343ee39b7f27f2e74f59943d65a84addb3 100644 (file)
--- 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 317b2723ce152f607963546259dae92d182361ed..892aef4b10107eeebab39b0e192440f7fa9dada9 100755 (executable)
--- 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};