--- a/Changes	Sat Oct 29 18:29:44 2016 +0200
+++ b/Changes	Sun Nov 20 20:57:40 2016 +0100
@@ -1,6 +1,7 @@
 {{$NEXT}}
   - NEW: Crash groups
   - NEW: Search (for SQL)
+  - NEW: Bug links
   - Rewrite the SQL backend with Mojo::Pg (drop many dependencies)
   - Rewrite the job queue using Minion
   - Many things rewritten

--- a/extras/crash_test.conf	Sat Oct 29 18:29:44 2016 +0200
+++ b/extras/crash_test.conf	Sun Nov 20 20:57:40 2016 +0100
@@ -52,6 +52,13 @@
     ScmLinks => {
       "svn:svn.example.org/testproject" => 'https://redmine.example.org/projects/testproject/repository/entry/<%= $scmpath =%>?rev=<%= $rev =%>#L<%= $line =%>',
     },
+    BugTrackerLinks => {
+      MyRedmine => {
+        type => 'Redmine',
+        match => qr/\d{1,8}/,
+        url_pattern => 'http://redmine.example.org/issues/<%= $bug_key =%>',
+      },
+    },
     ExtraColumns => {
       Index => [
         { id => 'os'        , db_column => "crash_user.os", name => 'Operating System' },

--- a/lib/CrashTest.pm	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest.pm	Sun Nov 20 20:57:40 2016 +0100
@@ -30,6 +30,7 @@
     $self->plugin("CrashTest::Helper::Backtrace");
     $self->plugin("CrashTest::Helper::XmlEscape");
     $self->plugin("CrashTest::Helper::Stats");
+    $self->plugin("CrashTest::Helper::BugLinks", $self->config->{WebInterface}->{BugTrackerLinks});
 
 
     $self->helper(crash_reports     => sub { state $crash_reports   = CrashTest::Model::CrashReport->new    (app => $self); });

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/CrashTest/Helper/BugLinks.pm	Sun Nov 20 20:57:40 2016 +0100
@@ -0,0 +1,81 @@
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+package CrashTest::Helper::BugLinks;
+use Mojo::Base 'Mojolicious::Plugin';
+use Mojo::JSON qw/decode_json/;
+use Mojo::Util qw/dumper/;
+
+has [ qw/config app/ ];
+
+sub register {
+    my ($self, $app, $conf) = @_;
+
+    $self->config($conf);
+    $self->app($app);
+
+    $app->helper(bug_links => sub { $self->_bug_links(@_) } );
+}
+
+sub _link_template {
+    my ($self, $bugtracker_key) = @_;
+
+    if(defined($self->{_template_cache}->{$bugtracker_key})) {
+        return $self->{_template_cache}->{$bugtracker_key};
+    }
+
+    #$self->app->log->debug("Building template for $bugtracker_key");
+    my $template = $self->config->{$bugtracker_key}->{url_pattern};
+
+    return undef if !defined($template);
+
+
+    my $mt = Mojo::Template->new
+        ->vars(1)
+        ->auto_escape(1)
+        ->escape(sub {
+            my $str = shift;
+            return Mojo::ByteStream::b($str)->url_escape;
+        })
+        ->parse($template);
+
+    $self->{_template_cache}->{$bugtracker_key} = $mt;
+    return $mt;
+}
+
+
+sub _bug_links {
+    my ($self, $c, $json) = @_;
+
+    #return dumper $bugs;
+    return "" if(!defined($json) || $json eq "");
+
+    my $bugs = decode_json($json);
+    my @links;
+
+    foreach my $bug(@$bugs) {
+        my $template = $self->_link_template($bug->{bugtracker_key});
+        if(defined($template)) {
+            # build url using the configured template
+            my $url = $template->process({ bug_key => $bug->{bug_key} });
+            # and create a link to it
+            push @links, $self->app->link_to($bug->{bug_key} => $url);
+        } else {
+            push @links, $bug->{bug_key};
+        }
+    }
+
+    return Mojo::ByteStream->new(join ", ", @links);
+}
+
+1;

--- a/lib/CrashTest/Plugin/Storage/Sql/Model/CrashGroup.pm	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest/Plugin/Storage/Sql/Model/CrashGroup.pm	Sun Nov 20 20:57:40 2016 +0100
@@ -14,7 +14,6 @@
 package CrashTest::Plugin::Storage::Sql::Model::CrashGroup;
 use Mojo::Base -base;
 use Data::Page;
-use Search::QueryParser::SQL;
 
 use Storable 'dclone';
 use CrashTest::Model::Thread;
@@ -44,6 +43,7 @@
             version     => { name => 'product.version' },
             channel     => { name => 'product.release_channel' },
             function    => { name => 'crash_group.crash_thread_signature_bt', type => "fuzzy" },
+            bug         => { name => 'crash_reports.crash_group_id IN (SELECT crash_group_id FROM bug_links WHERE bug_key ?op? ?)', type => 'sql' },
         });
 }
 
@@ -79,9 +79,10 @@
     my $extra_columns = join(",", @extra_cols);
 
     my $results = $self->db->query("
-        SELECT crash_groups.uuid, title, group_by_count.*
+        SELECT crash_groups.uuid, title, group_by_count.*,
+            (SELECT json_agg(to_json(bug_links)) FROM bug_links WHERE bug_links.crash_group_id = crash_groups.id) AS bug_links
         FROM crash_groups,
-             ( SELECT crash_group_id AS id,
+             ( SELECT crash_group.id AS id,
                     min(version) AS first_version,
                     max(version) AS last_version,
                     string_agg(distinct(name), ', ') AS product_names,
@@ -92,7 +93,7 @@
                LEFT JOIN products AS product ON crash_reports.product_id = product.id
                JOIN crash_groups AS crash_group ON crash_reports.crash_group_id = crash_group.id
                $where
-               GROUP BY crash_group_id ) AS group_by_count
+               GROUP BY crash_group.id ) AS group_by_count
         WHERE group_by_count.id = crash_groups.id
         ORDER BY crash_count DESC, crash_groups.id DESC
         OFFSET (?) ROWS

--- a/lib/CrashTest/Plugin/Storage/Sql/Model/CrashReport.pm	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest/Plugin/Storage/Sql/Model/CrashReport.pm	Sun Nov 20 20:57:40 2016 +0100
@@ -19,7 +19,6 @@
 use Data::Page;
 #use DBI::Log;
 #$DBI::Log::trace = 0;
-use Search::QueryParser::SQL;
 
 use CrashTest::Plugin::Storage::Sql::Model::CrashGroup;
 use CrashTest::Plugin::Storage::Sql::Utils;
@@ -49,6 +48,7 @@
             channel     => { name => 'product.release_channel' },
             group_id    => { name => 'crash_reports.crash_group_id' },
             function    => { name => 'crash_group.crash_thread_signature_bt', type => "fuzzy" },
+            bug         => { name => 'crash_reports.crash_group_id IN (SELECT crash_group_id FROM bug_links WHERE bug_key ?op? ?)', type => 'sql' },
         });
 }
 
@@ -86,6 +86,7 @@
         SELECT  crash_reports.*,
                 product.distributor AS p_distributor, product.name AS p_name, product.version AS p_version, product.release_channel AS p_release_channel,
                 crash_user.os AS u_os, crash_user.cpu_arch AS u_cpu_arch, crash_user.cpu_count AS u_cpu_count, crash_user.extra_info AS u_extra_info,
+                (SELECT json_agg(to_json(bug_links)) FROM bug_links WHERE bug_links.crash_group_id = crash_group.id) AS bug_links,
                 $extra_columns
         FROM crash_reports
         LEFT JOIN crash_users AS crash_user ON crash_reports.crash_user_id = crash_user.id

--- a/lib/CrashTest/Plugin/Storage/Sql/Utils.pm	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest/Plugin/Storage/Sql/Utils.pm	Sun Nov 20 20:57:40 2016 +0100
@@ -14,6 +14,7 @@
 package CrashTest::Plugin::Storage::Sql::Utils;
 use Mojo::Base -base;
 use Storable 'dclone';
+use Search::QueryParser::SQL;
 
 has [ qw/instance app config/ ];
 
@@ -35,6 +36,8 @@
     # define a callback to collect values safely (without magic markers)
     my $cb = sub {
         my ($column, $this_op, $value) = @_;
+        my $sql = join('', $column->stringify, $this_op, '?');
+
         if($column->type eq "fuzzy") {
             if ( $this_op =~ /!|NOT/ ) {
                 $this_op = " NOT ILIKE ";
@@ -42,9 +45,12 @@
                 $this_op = " ILIKE ";
             }
             $value = "%$value%";
+        } elsif($column->type eq "sql") {
+            $sql = $column->name;
+            $sql =~ s/\s+\?op\?\s+/ $this_op /;
         }
         push @values, $value;
-        return join('', $column->stringify, $this_op, '?');
+        return $sql;
     };
 
     my $search_fields = dclone($extra_search_fields);

--- a/lib/CrashTest/Plugin/Storage/Sql/migrations_pg.sql	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest/Plugin/Storage/Sql/migrations_pg.sql	Sun Nov 20 20:57:40 2016 +0100
@@ -105,4 +105,25 @@
 ALTER TABLE "crash_reports" DROP COLUMN crash_group_distance;
 DROP TABLE "crash_groups" CASCADE;
 
+-- ###########################################################################
+-- 3 up
+-- ###########################################################################
+
+CREATE TABLE "bug_links" (
+  "crash_group_id" integer NOT NULL,
+  "bugtracker_key" character varying(32) NOT NULL,
+  "bug_key" character varying(32) NOT NULL,
+
+  CONSTRAINT "bug_links_fk_crash_group_id" FOREIGN KEY ("crash_group_id") REFERENCES "crash_groups" ("id"),
+  PRIMARY KEY ("crash_group_id", "bugtracker_key", "bug_key")
+);
+CREATE INDEX bug_links_bug_key_idx ON bug_links ( bug_key );
+
+-- ###########################################################################
+-- 3 down
+-- ###########################################################################
+
+DROP TABLE "bug_links" CASCADE;
+
+
 -- vim:ft=pgsql:

--- a/lib/CrashTest/files/templates/groups/index.html.ep	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest/files/templates/groups/index.html.ep	Sun Nov 20 20:57:40 2016 +0100
@@ -8,6 +8,7 @@
     <th>First version seen</th>
     <th>Last version seen</th>
     <th>Products</th>
+    <th>Bugs</th>
     % foreach my $extra_col(@$extra_columns) {
     %= t th => $extra_col->{name}
     % }
@@ -22,6 +23,7 @@
     %= t td => $crash->{first_version}
     %= t td => $crash->{last_version}
     %= t td => $crash->{product_names}
+    %= t td => bug_links($crash->{bug_links})
     % foreach my $extra_col(@$extra_columns) {
     %= t td => $crash->{$extra_col->{id}}
     % }

--- a/lib/CrashTest/files/templates/reports/_list.html.ep	Sat Oct 29 18:29:44 2016 +0200
+++ b/lib/CrashTest/files/templates/reports/_list.html.ep	Sun Nov 20 20:57:40 2016 +0100
@@ -7,6 +7,7 @@
     % foreach my $extra_col(@$extra_columns) {
     %= t th => $extra_col->{name}
     % }
+    <th>Bugs</th>
     <th>Date</th>
   </tr>
 </thead>
@@ -20,6 +21,7 @@
     % foreach my $extra_col(@$extra_columns) {
     %= t td => $crash->{$extra_col->{id}}
     % }
+    %= t td => bug_links($crash->{bug_links})
     %= t td => date_from_db_utc($crash->{crash_time})
   % end
 % }