Bug 1062739: add the ability for administrators to limit the number of emails sent to a user per minute and hour

r=dylan,a=glob

11 files changed, 336 insertions, 22 deletions

diff --git a/Bugzilla/Constants.pm b/Bugzilla/Constants.pm
index 4c1f11003..a770e7eb7 100644
--- a/Bugzilla/Constants.pm
+++ b/Bugzilla/Constants.pm
@@ -195,6 +195,12 @@ use Memoize;
     MOST_FREQUENT_THRESHOLD
 
     MARKDOWN_TAB_WIDTH
+
+    EMAIL_LIMIT_PER_MINUTE
+    EMAIL_LIMIT_PER_HOUR
+    EMAIL_LIMIT_EXCEPTION
+
+    JOB_QUEUE_VIEW_MAX_JOBS
 );
 
 @Bugzilla::Constants::EXPORT_OK = qw(contenttypes);
@@ -641,6 +647,18 @@ use constant MOST_FREQUENT_THRESHOLD => 2;
 # by Markdown engine
 use constant MARKDOWN_TAB_WIDTH => 2;
 
+# The maximum number of emails per minute and hour a recipient can receive.
+# Email will be queued/backlogged to avoid exceeeding these limits.
+# Setting a limit to 0 will disable this feature.
+use constant EMAIL_LIMIT_PER_MINUTE => 1000;
+use constant EMAIL_LIMIT_PER_HOUR   => 2500;
+# Don't change this exception message.
+use constant EMAIL_LIMIT_EXCEPTION  => "email_limit_exceeded\n";
+
+# The maximum number of jobs to show when viewing the job queue
+# (view_job_queue.cgi).
+use constant JOB_QUEUE_VIEW_MAX_JOBS => 500;
+
 sub bz_locations {
     # Force memoize() to re-compute data per project, to avoid
     # sharing the same data across different installations.
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index ebe2cb426..0698585bb 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -1640,6 +1640,18 @@ use constant ABSTRACT_SCHEMA => {
         ],
     },
 
+    email_rates => {
+        FIELDS => [
+            id         => {TYPE => 'INTSERIAL', NOTNULL => 1,
+                           PRIMARYKEY => 1},
+            recipient  => {TYPE => 'varchar(255)', NOTNULL => 1},
+            message_ts => {TYPE => 'DATETIME', NOTNULL => 1},
+        ],
+        INDEXES => [
+            email_rates_idx => [qw(recipient message_ts)],
+        ],
+    },
+
     # THESCHWARTZ TABLES
     # ------------------
     # Note: In the standard TheSchwartz schema, most integers are unsigned,
diff --git a/Bugzilla/Install/Requirements.pm b/Bugzilla/Install/Requirements.pm
index db3d7b028..491bf8a72 100644
--- a/Bugzilla/Install/Requirements.pm
+++ b/Bugzilla/Install/Requirements.pm
@@ -349,8 +349,8 @@ sub OPTIONAL_MODULES {
     {
         package => 'TheSchwartz',
         module  => 'TheSchwartz',
-        # 1.07 supports the prioritization of jobs.
-        version => 1.07,
+        # 1.10 supports declining of jobs.
+        version => 1.10,
         feature => ['jobqueue'],
     },
     {
diff --git a/Bugzilla/Job/BugMail.pm b/Bugzilla/Job/BugMail.pm
index e0b7f5448..b4887c470 100644
--- a/Bugzilla/Job/BugMail.pm
+++ b/Bugzilla/Job/BugMail.pm
@@ -14,19 +14,9 @@ use warnings;
 use Bugzilla::BugMail;
 BEGIN { eval "use parent qw(Bugzilla::Job::Mailer)"; }
 
-sub work {
-    my ($class, $job) = @_;
-    my $success = eval {
-        Bugzilla::BugMail::dequeue($job->arg->{vars});
-        1;
-    };
-    if (!$success) {
-        $job->failed($@);
-        undef $@;
-    }
-    else {
-        $job->completed;
-    }
+sub process_job {
+    my ($class, $arg) = @_;
+    Bugzilla::BugMail::dequeue($arg->{vars});
 }
 
 1;
diff --git a/Bugzilla/Job/Mailer.pm b/Bugzilla/Job/Mailer.pm
index cd1c23445..7e7549de8 100644
--- a/Bugzilla/Job/Mailer.pm
+++ b/Bugzilla/Job/Mailer.pm
@@ -11,6 +11,7 @@ use 5.10.1;
 use strict;
 use warnings;
 
+use Bugzilla::Constants;
 use Bugzilla::Mailer;
 BEGIN { eval "use parent qw(TheSchwartz::Worker)"; }
 
@@ -32,15 +33,24 @@ sub retry_delay {
 
 sub work {
     my ($class, $job) = @_;
-    my $msg = $job->arg->{msg};
-    my $success = eval { MessageToMTA($msg, 1); 1; };
-    if (!$success) {
-        $job->failed($@);
+    eval { $class->process_job($job->arg) };
+    if (my $error = $@) {
+        if ($error eq EMAIL_LIMIT_EXCEPTION) {
+            $job->declined();
+        }
+        else {
+            $job->failed($error);
+        }
         undef $@;
-    } 
+    }
     else {
         $job->completed;
     }
 }
 
+sub process_job {
+    my ($class, $arg) = @_;
+    MessageToMTA($arg, 1);
+}
+
 1;
diff --git a/Bugzilla/Mailer.pm b/Bugzilla/Mailer.pm
index 4447d4046..389b6f69e 100644
--- a/Bugzilla/Mailer.pm
+++ b/Bugzilla/Mailer.pm
@@ -41,6 +41,8 @@ sub MessageToMTA {
         return;
     }
 
+    my $dbh = Bugzilla->dbh;
+
     my $email;
     if (ref $msg) {
         $email = $msg;
@@ -58,14 +60,50 @@ sub MessageToMTA {
     # email immediately, in case the transaction is rolled back. Instead we
     # insert it into the mail_staging table, and bz_commit_transaction calls
     # send_staged_mail() after the transaction is committed.
-    if (! $send_now && Bugzilla->dbh->bz_in_transaction()) {
+    if (! $send_now && $dbh->bz_in_transaction()) {
         # The e-mail string may contain tainted values.
         my $string = $email->as_string;
         trick_taint($string);
-        Bugzilla->dbh->do("INSERT INTO mail_staging (message) VALUES(?)", undef, $string);
+        $dbh->do("INSERT INTO mail_staging (message) VALUES(?)", undef, $string);
         return;
     }
 
+    # Ensure that we are not sending emails too quickly to recipients.
+    if (Bugzilla->params->{use_mailer_queue}
+        && (EMAIL_LIMIT_PER_MINUTE || EMAIL_LIMIT_PER_HOUR))
+    {
+        $dbh->do(
+            "DELETE FROM email_rates WHERE message_ts < "
+            . $dbh->sql_date_math('LOCALTIMESTAMP(0)', '-', '1', 'HOUR'));
+
+        my $recipient = $email->header('To');
+
+        if (EMAIL_LIMIT_PER_MINUTE) {
+            my $minute_rate = $dbh->selectrow_array(
+                "SELECT COUNT(*)
+                   FROM email_rates
+                  WHERE recipient = ?  AND message_ts >= "
+                        . $dbh->sql_date_math('LOCALTIMESTAMP(0)', '-', '1', 'MINUTE'),
+                undef,
+                $recipient);
+            if ($minute_rate >= EMAIL_LIMIT_PER_MINUTE) {
+                die EMAIL_LIMIT_EXCEPTION;
+            }
+        }
+        if (EMAIL_LIMIT_PER_HOUR) {
+            my $hour_rate = $dbh->selectrow_array(
+                "SELECT COUNT(*)
+                   FROM email_rates
+                  WHERE recipient = ?  AND message_ts >= "
+                        . $dbh->sql_date_math('LOCALTIMESTAMP(0)', '-', '1', 'HOUR'),
+                undef,
+                $recipient);
+            if ($hour_rate >= EMAIL_LIMIT_PER_HOUR) {
+                die EMAIL_LIMIT_EXCEPTION;
+            }
+        }
+    }
+
     # We add this header to uniquely identify all email that we
     # send as coming from this Bugzilla installation.
     #
@@ -181,6 +219,17 @@ sub MessageToMTA {
             ThrowCodeError('mail_send_error', { msg => $@->message, mail => $email });
         }
     }
+
+    # insert into email_rates
+    if (Bugzilla->params->{use_mailer_queue}
+        && (EMAIL_LIMIT_PER_MINUTE || EMAIL_LIMIT_PER_HOUR))
+    {
+        $dbh->do(
+            "INSERT INTO email_rates(recipient, message_ts) VALUES (?, LOCALTIMESTAMP(0))",
+            undef,
+            $email->header('To')
+        );
+    }
 }
 
 # Builds header suitable for use as a threading marker in email notifications
diff --git a/skins/standard/admin.css b/skins/standard/admin.css
index cdf75ac2c..232076846 100644
--- a/skins/standard/admin.css
+++ b/skins/standard/admin.css
@@ -303,3 +303,38 @@ table.schedule_list th, table.search_list th {
     padding: .5em;
     font-size: small;
 }
+
+#report {
+    border: 1px solid #888888;
+}
+
+#report td, #report th {
+    padding: 3px 10px 3px 3px;
+    border: 0px;
+}
+
+#report th {
+    text-align: left;
+}
+
+#report-header {
+    background-color: #cccccc;
+}
+
+.report_row_odd {
+    background-color: #eeeeee;
+    color: #000000;
+}
+
+.report_row_even {
+    background-color: #ffffff;
+    color: #000000;
+}
+
+#report.hover tr:hover {
+    background-color: #ccccff;
+}
+
+.report_information {
+    font-style: italic;
+}
diff --git a/template/en/default/admin/admin.html.tmpl b/template/en/default/admin/admin.html.tmpl
index 9f60eb662..01e9f309b 100644
--- a/template/en/default/admin/admin.html.tmpl
+++ b/template/en/default/admin/admin.html.tmpl
@@ -118,6 +118,12 @@
         and time, and get the result of these queries directly per email. This is a
         good way to create reminders and to keep track of the activity in your installation.</dd>
 
+        [% IF Param('use_mailer_queue') %]
+          [% class = user.in_group('admin') ? "" : "forbidden" %]
+          <dt id="view_job_queue" class="[% class %]"><a href="view_job_queue.cgi">Job Queue</a></dt>
+          <dd class="[% class %]">View the queue of undelivered/deferred jobs/emails.</dd>
+        [% END %]
+
         [% Hook.process('end_links_right') %]
       </dl>
     </td>
diff --git a/template/en/default/admin/reports/job_queue.html.tmpl b/template/en/default/admin/reports/job_queue.html.tmpl
new file mode 100644
index 000000000..6057eac7f
--- /dev/null
+++ b/template/en/default/admin/reports/job_queue.html.tmpl
@@ -0,0 +1,74 @@
+[%# This Source Code Form is subject to the terms of the Mozilla Public
+  # License, v. 2.0. If a copy of the MPL was not distributed with this
+  # file, You can obtain one at http://mozilla.org/MPL/2.0/.
+  #
+  # This Source Code Form is "Incompatible With Secondary Licenses", as
+  # defined by the Mozilla Public License, v. 2.0.
+  #%]
+
+[% INCLUDE global/header.html.tmpl
+  title = "Job Queue Status"
+  style_urls = [ "skins/standard/admin.css" ]
+%]
+
+[% IF jobs.size %]
+
+  <p class="report_information">
+    [% IF too_many_jobs %]
+      [% job_count FILTER html %] jobs found,
+      limiting results to [% constants.JOB_QUEUE_VIEW_MAX_JOBS FILTER html %] jobs.
+    [% ELSE %]
+      [% jobs.size FILTER none %] jobs(s) in the queue.
+    [% END %]
+  </p>
+
+  <table id="report" class="hover" cellspacing="0" border="0" width="100%">
+  <tr id="report-header">
+    <th>Next Attempt After</th>
+    <th>Error Count</th>
+    <th>Error Time</th>
+    <th>Error Message</th>
+    <th>Job</th>
+  </tr>
+  [% FOREACH job IN jobs %]
+    <tr class="report item [% loop.count % 2 == 1 ? "report_row_odd" : "report_row_even" %]">
+      <td nowrap>
+        [% IF job.grabbed_until %]
+          [% time2str("%Y-%m-%d %H:%M:%S %Z", job.grabbed_until) FILTER html %]
+        [% ELSE %]
+          [% time2str("%Y-%m-%d %H:%M:%S %Z", job.run_time) FILTER html %]
+        [% END %]
+      </td>
+      <td>
+        [% job.error_count || "-" FILTER html %]
+      </td>
+      <td nowrap>
+        [% IF job.error_count %]
+          [% time2str("%Y-%m-%d %H:%M:%S %Z", job.error_time) FILTER html %]
+        [% ELSE %]
+          -
+        [% END %]
+      </td>
+      <td>
+        [% IF job.grabbed_until %]
+          Deferred
+        [% ELSIF job.error_count %]
+          [% job.error_message FILTER html %]
+        [% ELSE %]
+          -
+        [% END %]
+      </td>
+      <td>[% job.subject || '-' FILTER html %]</td>
+    </tr>
+  [% END %]
+  </table>
+
+[% ELSE %]
+
+  <p class="report_information">
+    The job queue is empty.
+  </p>
+
+[% END %]
+
+[% INCLUDE global/footer.html.tmpl %]
diff --git a/template/en/default/global/user-error.html.tmpl b/template/en/default/global/user-error.html.tmpl
index e9bdb63c4..78d8823f5 100644
--- a/template/en/default/global/user-error.html.tmpl
+++ b/template/en/default/global/user-error.html.tmpl
@@ -191,6 +191,8 @@
       group access
     [% ELSIF object == "groups" %]
       groups
+    [% ELSIF object == "job_queue" %]
+      the job queue
     [% ELSIF object == "keywords" %]
       keywords
     [% ELSIF object == "milestones" %]
diff --git a/view_job_queue.cgi b/view_job_queue.cgi
new file mode 100755
index 000000000..5d0ed9530
--- /dev/null
+++ b/view_job_queue.cgi
@@ -0,0 +1,118 @@
+#!/usr/bin/perl -T
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+
+use 5.10.1;
+use strict;
+use warnings;
+
+use lib qw(. lib);
+
+use Bugzilla;
+use Bugzilla::Constants;
+use Bugzilla::Error;
+use Bugzilla::Util qw(template_var);
+use Scalar::Util qw(blessed);
+use Storable qw(read_magic thaw);
+
+my $user = Bugzilla->login(LOGIN_REQUIRED);
+$user->in_group("admin")
+    || ThrowUserError("auth_failure", { group  => "admin",
+                                        action => "access",
+                                        object => "job_queue" });
+
+my $vars = {};
+generate_report($vars);
+
+print Bugzilla->cgi->header();
+my $template = Bugzilla->template;
+$template->process('admin/reports/job_queue.html.tmpl', $vars)
+    || ThrowTemplateError($template->error());
+
+sub generate_report {
+    my ($vars) = @_;
+    my $dbh = Bugzilla->dbh;
+    my $user = Bugzilla->user;
+
+    my $query = "
+        SELECT
+            j.jobid,
+            j.arg,
+            j.run_after AS run_time,
+            j.grabbed_until,
+            f.funcname AS func,
+            e.jobid AS error_count,
+            e.error_time AS error_time,
+            e.message AS error_message
+        FROM
+            ts_job j
+            INNER JOIN ts_funcmap f
+                ON f.funcid = j.funcid
+            NATURAL LEFT JOIN (
+                SELECT MAX(error_time) AS error_time, jobid
+                  FROM ts_error
+                 GROUP BY jobid
+            ) t
+            LEFT JOIN ts_error e
+                ON (e.error_time  = t.error_time) AND (e.jobid = t.jobid)
+        ORDER BY
+            j.run_after, j.grabbed_until, j.insert_time, j.jobid
+        " . $dbh->sql_limit(JOB_QUEUE_VIEW_MAX_JOBS + 1);
+
+    $vars->{jobs} = $dbh->selectall_arrayref($query, { Slice => {} });
+    if (@{ $vars->{jobs} } == JOB_QUEUE_VIEW_MAX_JOBS + 1) {
+        pop @{ $vars->{jobs} };
+        $vars->{job_count} = $dbh->selectrow_array("SELECT COUNT(*) FROM ts_job");
+        $vars->{too_many_jobs} = 1;
+    }
+
+    my $bug_word = template_var('terms')->{bug};
+    foreach my $job (@{ $vars->{jobs} }) {
+        my ($recipient, $description);
+        eval {
+            if ($job->{func} eq 'Bugzilla::Job::BugMail') {
+                my $arg = _cond_thaw(delete $job->{arg});
+                next unless $arg;
+                my $vars = $arg->{vars};
+                $recipient = $vars->{to_user}->{login_name};
+                $description = "[$bug_word " . $vars->{bug}->{bug_id} . '] '
+                               . $vars->{bug}->{short_desc};
+            }
+
+            elsif ($job->{func} eq 'Bugzilla::Job::Mailer') {
+                my $arg = _cond_thaw(delete $job->{arg});
+                next unless $arg;
+                my $msg = $arg->{msg};
+                if (ref($msg) && blessed($msg) eq 'Email::MIME') {
+                    $recipient = $msg->header('to');
+                    $description = $msg->header('subject');
+                } else {
+                    ($recipient) = $msg =~ /\nTo: ([^\n]+)/i;
+                    ($description) = $msg =~ /\nSubject: ([^\n]+)/i;
+                }
+            }
+        };
+        if ($recipient) {
+            $job->{subject} = "<$recipient> $description";
+        }
+    }
+}
+
+sub _cond_thaw {
+    my $data = shift;
+    my $magic = eval { read_magic($data); };
+    if ($magic && $magic->{major} && $magic->{major} >= 2 && $magic->{major} <= 5) {
+        my $thawed = eval { thaw($data) };
+        if ($@) {
+            # false alarm... looked like a Storable, but wasn't
+            return undef;
+        }
+        return $thawed;
+    } else {
+        return undef;
+    }
+}