BugsSite/sanitycheck.cgi

   1 #!/usr/bin/env perl -wT
   2 # -*- Mode: perl; indent-tabs-mode: nil -*-
   3 #
   4 # The contents of this file are subject to the Mozilla Public
   5 # License Version 1.1 (the "License"); you may not use this file
   6 # except in compliance with the License. You may obtain a copy of
   7 # the License at http://www.mozilla.org/MPL/
   8 #
   9 # Software distributed under the License is distributed on an "AS
  10 # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
  11 # implied. See the License for the specific language governing
  12 # rights and limitations under the License.
  13 #
  14 # The Original Code is the Bugzilla Bug Tracking System.
  15 #
  16 # The Initial Developer of the Original Code is Netscape Communications
  17 # Corporation. Portions created by Netscape are
  18 # Copyright (C) 1998 Netscape Communications Corporation. All
  19 # Rights Reserved.
  20 #
  21 # Contributor(s): Terry Weissman <terry@mozilla.org>
  22 #                 Matthew Tuck <matty@chariot.net.au>
  23 #                 Max Kanat-Alexander <mkanat@bugzilla.org>
  24 #                 Marc Schumann <wurblzap@gmail.com>
  25 #                 Frédéric Buclin <LpSolit@gmail.com>
  26
  27 use strict;
  28
  29 use lib qw(. lib);
  30
  31 use Bugzilla;
  32 use Bugzilla::Bug;
  33 use Bugzilla::Constants;
  34 use Bugzilla::Util;
  35 use Bugzilla::Error;
  36 use Bugzilla::Status;
  37
  38 ###########################################################################
  39 # General subs
  40 ###########################################################################
  41
  42 sub get_string {
  43     my ($san_tag, $vars) = @_;
  44     $vars->{'san_tag'} = $san_tag;
  45     return get_text('sanitycheck', $vars);
  46 }
  47
  48 sub Status {
  49     my ($san_tag, $vars, $alert) = @_;
  50     my $cgi = Bugzilla->cgi;
  51     return if (!$alert && Bugzilla->usage_mode == USAGE_MODE_CMDLINE && !$cgi->param('verbose'));
  52
  53     if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
  54         my $output = $cgi->param('output') || '';
  55         my $linebreak = $alert ? "\nALERT: " : "\n";
  56         $cgi->param('error_found', 1) if $alert;
  57         $cgi->param('output', $output . $linebreak . get_string($san_tag, $vars));
  58     }
  59     else {
  60         my $start_tag = $alert ? '<p class="alert">' : '<p>';
  61         print $start_tag . get_string($san_tag, $vars) . "</p>\n";
  62     }
  63 }
  64
  65 ###########################################################################
  66 # Start
  67 ###########################################################################
  68
  69 my $user = Bugzilla->login(LOGIN_REQUIRED);
  70
  71 my $cgi = Bugzilla->cgi;
  72 my $dbh = Bugzilla->dbh;
  73 # If the result of the sanity check is sent per email, then we have to
  74 # take the user prefs into account rather than querying the web browser.
  75 my $template;
  76 if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
  77     $template = Bugzilla->template_inner($user->settings->{'lang'}->{'value'});
  78 }
  79 else {
  80     $template = Bugzilla->template;
  81 }
  82 my $vars = {};
  83
  84 print $cgi->header() unless Bugzilla->usage_mode == USAGE_MODE_CMDLINE;
  85
  86 # Make sure the user is authorized to access sanitycheck.cgi.
  87 # As this script can now alter the group_control_map table, we no longer
  88 # let users with editbugs privs run it anymore.
  89 $user->in_group("editcomponents")
  90   || ($user->in_group('editkeywords') && $cgi->param('rebuildkeywordcache'))
  91   || ThrowUserError("auth_failure", {group  => "editcomponents",
  92                                      action => "run",
  93                                      object => "sanity_check"});
  94
  95 unless (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
  96     $template->process('admin/sanitycheck/list.html.tmpl', $vars)
  97       || ThrowTemplateError($template->error());
  98 }
  99
 100 ###########################################################################
 101 # Users with 'editkeywords' privs only can only check keywords.
 102 ###########################################################################
 103 unless ($user->in_group('editcomponents')) {
 104     check_votes_or_keywords('keywords');
 105     Status('checks_completed');
 106
 107     $template->process('global/footer.html.tmpl', $vars)
 108         || ThrowTemplateError($template->error());
 109     exit;
 110 }
 111
 112 ###########################################################################
 113 # Fix vote cache
 114 ###########################################################################
 115
 116 if ($cgi->param('rebuildvotecache')) {
 117     Status('vote_cache_rebuild_start');
 118     $dbh->bz_start_transaction();
 119     $dbh->do(q{UPDATE bugs SET votes = 0});
 120     my $sth_update = $dbh->prepare(q{UPDATE bugs
 121                                         SET votes = ?
 122                                       WHERE bug_id = ?});
 123     my $sth = $dbh->prepare(q{SELECT bug_id, SUM(vote_count)
 124                                 FROM votes }. $dbh->sql_group_by('bug_id'));
 125     $sth->execute();
 126     while (my ($id, $v) = $sth->fetchrow_array) {
 127         $sth_update->execute($v, $id);
 128     }
 129     $dbh->bz_commit_transaction();
 130     Status('vote_cache_rebuild_end');
 131 }
 132
 133 ###########################################################################
 134 # Create missing group_control_map entries
 135 ###########################################################################
 136
 137 if ($cgi->param('createmissinggroupcontrolmapentries')) {
 138     Status('group_control_map_entries_creation');
 139
 140     my $na    = CONTROLMAPNA;
 141     my $shown = CONTROLMAPSHOWN;
 142     my $insertsth = $dbh->prepare(
 143         qq{INSERT INTO group_control_map (
 144                        group_id, product_id, entry,
 145                        membercontrol, othercontrol, canedit
 146                       )
 147                VALUES (
 148                        ?, ?, 0,
 149                        $shown, $na, 0
 150                       )});
 151     my $updatesth = $dbh->prepare(qq{UPDATE group_control_map
 152                                         SET membercontrol = $shown
 153                                       WHERE group_id   = ?
 154                                         AND product_id = ?});
 155     my $counter = 0;
 156
 157     # Find all group/product combinations used for bugs but not set up
 158     # correctly in group_control_map
 159     my $invalid_combinations = $dbh->selectall_arrayref(
 160         qq{    SELECT bugs.product_id,
 161                       bgm.group_id,
 162                       gcm.membercontrol,
 163                       groups.name,
 164                       products.name
 165                  FROM bugs
 166            INNER JOIN bug_group_map AS bgm
 167                    ON bugs.bug_id = bgm.bug_id
 168            INNER JOIN groups
 169                    ON bgm.group_id = groups.id
 170            INNER JOIN products
 171                    ON bugs.product_id = products.id
 172             LEFT JOIN group_control_map AS gcm
 173                    ON bugs.product_id = gcm.product_id
 174                   AND    bgm.group_id = gcm.group_id
 175                 WHERE COALESCE(gcm.membercontrol, $na) = $na
 176           } . $dbh->sql_group_by('bugs.product_id, bgm.group_id',
 177                                  'gcm.membercontrol, groups.name, products.name'));
 178
 179     foreach (@$invalid_combinations) {
 180         my ($product_id, $group_id, $currentmembercontrol,
 181             $group_name, $product_name) = @$_;
 182
 183         $counter++;
 184         if (defined($currentmembercontrol)) {
 185             Status('group_control_map_entries_update',
 186                    {group_name => $group_name, product_name => $product_name});
 187             $updatesth->execute($group_id, $product_id);
 188         }
 189         else {
 190             Status('group_control_map_entries_generation',
 191                    {group_name => $group_name, product_name => $product_name});
 192             $insertsth->execute($group_id, $product_id);
 193         }
 194     }
 195
 196     Status('group_control_map_entries_repaired', {counter => $counter});
 197 }
 198
 199 ###########################################################################
 200 # Fix missing creation date
 201 ###########################################################################
 202
 203 if ($cgi->param('repair_creation_date')) {
 204     Status('bug_creation_date_start');
 205
 206     my $bug_ids = $dbh->selectcol_arrayref('SELECT bug_id FROM bugs
 207                                             WHERE creation_ts IS NULL');
 208
 209     my $sth_UpdateDate = $dbh->prepare('UPDATE bugs SET creation_ts = ?
 210                                         WHERE bug_id = ?');
 211
 212     # All bugs have an entry in the 'longdescs' table when they are created,
 213     # even if no comment is required.
 214     my $sth_getDate = $dbh->prepare('SELECT MIN(bug_when) FROM longdescs
 215                                      WHERE bug_id = ?');
 216
 217     foreach my $bugid (@$bug_ids) {
 218         $sth_getDate->execute($bugid);
 219         my $date = $sth_getDate->fetchrow_array;
 220         $sth_UpdateDate->execute($date, $bugid);
 221     }
 222     Status('bug_creation_date_fixed', {bug_count => scalar(@$bug_ids)});
 223 }
 224
 225 ###########################################################################
 226 # Fix entries in Bugs full_text
 227 ###########################################################################
 228
 229 if ($cgi->param('repair_bugs_fulltext')) {
 230     Status('bugs_fulltext_start');
 231
 232     my $bug_ids = $dbh->selectcol_arrayref('SELECT bugs.bug_id
 233                                             FROM bugs
 234                                             LEFT JOIN bugs_fulltext
 235                                             ON bugs_fulltext.bug_id = bugs.bug_id
 236                                             WHERE bugs_fulltext.bug_id IS NULL');
 237
 238    foreach my $bugid (@$bug_ids) {
 239        Bugzilla::Bug->new($bugid)->_sync_fulltext('new_bug');
 240    }
 241
 242    Status('bugs_fulltext_fixed', {bug_count => scalar(@$bug_ids)});
 243 }
 244
 245 ###########################################################################
 246 # Send unsent mail
 247 ###########################################################################
 248
 249 if ($cgi->param('rescanallBugMail')) {
 250     require Bugzilla::BugMail;
 251
 252     Status('send_bugmail_start');
 253     my $time = $dbh->sql_interval(30, 'MINUTE');
 254
 255     my $list = $dbh->selectcol_arrayref(qq{
 256                                         SELECT bug_id
 257                                           FROM bugs
 258                                          WHERE (lastdiffed IS NULL
 259                                                 OR lastdiffed < delta_ts)
 260                                            AND delta_ts < now() - $time
 261                                       ORDER BY bug_id});
 262
 263     Status('send_bugmail_status', {bug_count => scalar(@$list)});
 264
 265     # We cannot simply look at the bugs_activity table to find who did the
 266     # last change in a given bug, as e.g. adding a comment doesn't add any
 267     # entry to this table. And some other changes may be private
 268     # (such as time-related changes or private attachments or comments)
 269     # and so choosing this user as being the last one having done a change
 270     # for the bug may be problematic. So the best we can do at this point
 271     # is to choose the currently logged in user for email notification.
 272     $vars->{'changer'} = Bugzilla->user->login;
 273
 274     foreach my $bugid (@$list) {
 275         Bugzilla::BugMail::Send($bugid, $vars);
 276     }
 277
 278     Status('send_bugmail_end') if scalar(@$list);
 279
 280     unless (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
 281         $template->process('global/footer.html.tmpl', $vars)
 282           || ThrowTemplateError($template->error());
 283     }
 284     exit;
 285 }
 286
 287 ###########################################################################
 288 # Remove all references to deleted bugs
 289 ###########################################################################
 290
 291 if ($cgi->param('remove_invalid_bug_references')) {
 292     Status('bug_reference_deletion_start');
 293
 294     $dbh->bz_start_transaction();
 295
 296     # Include custom multi-select fields to the list.
 297     my @multi_selects = Bugzilla->get_fields({custom => 1, type => FIELD_TYPE_MULTI_SELECT});
 298     my @addl_fields = map { 'bug_' . $_->name . '/' } @multi_selects;
 299
 300     foreach my $pair ('attachments/', 'bug_group_map/', 'bugs_activity/',
 301                       'bugs_fulltext/', 'cc/',
 302                       'dependencies/blocked', 'dependencies/dependson',
 303                       'duplicates/dupe', 'duplicates/dupe_of',
 304                       'flags/', 'keywords/', 'longdescs/', 'votes/',
 305                       @addl_fields)
 306     {
 307         my ($table, $field) = split('/', $pair);
 308         $field ||= "bug_id";
 309
 310         my $bug_ids =
 311           $dbh->selectcol_arrayref("SELECT $table.$field FROM $table
 312                                     LEFT JOIN bugs ON $table.$field = bugs.bug_id
 313                                     WHERE bugs.bug_id IS NULL");
 314
 315         if (scalar(@$bug_ids)) {
 316             $dbh->do("DELETE FROM $table WHERE $field IN (" . join(',', @$bug_ids) . ")");
 317         }
 318     }
 319
 320     $dbh->bz_commit_transaction();
 321     Status('bug_reference_deletion_end');
 322 }
 323
 324 ###########################################################################
 325 # Remove all references to deleted attachments
 326 ###########################################################################
 327
 328 if ($cgi->param('remove_invalid_attach_references')) {
 329     Status('attachment_reference_deletion_start');
 330
 331     $dbh->bz_start_transaction();
 332
 333     my $attach_ids =
 334         $dbh->selectcol_arrayref('SELECT attach_data.id
 335                                     FROM attach_data
 336                                LEFT JOIN attachments
 337                                       ON attachments.attach_id = attach_data.id
 338                                    WHERE attachments.attach_id IS NULL');
 339
 340     if (scalar(@$attach_ids)) {
 341         $dbh->do('DELETE FROM attach_data WHERE id IN (' .
 342                  join(',', @$attach_ids) . ')');
 343     }
 344
 345     $dbh->bz_commit_transaction();
 346     Status('attachment_reference_deletion_end');
 347 }
 348
 349 ###########################################################################
 350 # Remove all references to deleted users or groups from whines
 351 ###########################################################################
 352
 353 if ($cgi->param('remove_old_whine_targets')) {
 354     Status('whines_obsolete_target_deletion_start');
 355
 356     $dbh->bz_start_transaction();
 357
 358     foreach my $target (['groups', 'id', MAILTO_GROUP],
 359                         ['profiles', 'userid', MAILTO_USER])
 360     {
 361         my ($table, $col, $type) = @$target;
 362         my $old_ids =
 363           $dbh->selectcol_arrayref("SELECT DISTINCT mailto
 364                                       FROM whine_schedules
 365                                  LEFT JOIN $table
 366                                         ON $table.$col = whine_schedules.mailto
 367                                      WHERE mailto_type = $type AND $table.$col IS NULL");
 368
 369         if (scalar(@$old_ids)) {
 370             $dbh->do("DELETE FROM whine_schedules
 371                        WHERE mailto_type = $type AND mailto IN (" .
 372                        join(',', @$old_ids) . ")");
 373         }
 374     }
 375     $dbh->bz_commit_transaction();
 376     Status('whines_obsolete_target_deletion_end');
 377 }
 378
 379 Status('checks_start');
 380
 381 ###########################################################################
 382 # Perform referential (cross) checks
 383 ###########################################################################
 384
 385 # This checks that a simple foreign key has a valid primary key value.  NULL
 386 # references are acceptable and cause no problem.
 387 #
 388 # The first parameter is the primary key table name.
 389 # The second parameter is the primary key field name.
 390 # Each successive parameter represents a foreign key, it must be a list
 391 # reference, where the list has:
 392 #   the first value is the foreign key table name.
 393 #   the second value is the foreign key field name.
 394 #   the third value is optional and represents a field on the foreign key
 395 #     table to display when the check fails.
 396 #   the fourth value is optional and is a list reference to values that
 397 #     are excluded from checking.
 398 #
 399 # FIXME: The excluded values parameter should go away - the QA contact
 400 #        fields should use NULL instead - see bug #109474.
 401 #        The same goes for series; no bug for that yet.
 402
 403 sub CrossCheck {
 404     my $table = shift @_;
 405     my $field = shift @_;
 406     my $dbh = Bugzilla->dbh;
 407
 408     Status('cross_check_to', {table => $table, field => $field});
 409
 410     while (@_) {
 411         my $ref = shift @_;
 412         my ($refertable, $referfield, $keyname, $exceptions) = @$ref;
 413
 414         $exceptions ||= [];
 415         my %exceptions = map { $_ => 1 } @$exceptions;
 416
 417         Status('cross_check_from', {table => $refertable, field => $referfield});
 418
 419         my $query = qq{SELECT DISTINCT $refertable.$referfield} .
 420             ($keyname ? qq{, $refertable.$keyname } : q{}) .
 421                      qq{ FROM $refertable
 422                     LEFT JOIN $table
 423                            ON $refertable.$referfield = $table.$field
 424                         WHERE $table.$field IS NULL
 425                           AND $refertable.$referfield IS NOT NULL};
 426
 427         my $sth = $dbh->prepare($query);
 428         $sth->execute;
 429
 430         my $has_bad_references = 0;
 431
 432         while (my ($value, $key) = $sth->fetchrow_array) {
 433             next if $exceptions{$value};
 434             Status('cross_check_alert', {value => $value, table => $refertable,
 435                                          field => $referfield, keyname => $keyname,
 436                                          key => $key}, 'alert');
 437             $has_bad_references = 1;
 438         }
 439         # References to non existent bugs can be safely removed, bug 288461
 440         if ($table eq 'bugs' && $has_bad_references) {
 441             Status('cross_check_bug_has_references');
 442         }
 443         # References to non existent attachments can be safely removed.
 444         if ($table eq 'attachments' && $has_bad_references) {
 445             Status('cross_check_attachment_has_references');
 446         }
 447     }
 448 }
 449
 450 CrossCheck('classifications', 'id',
 451            ['products', 'classification_id']);
 452
 453 CrossCheck("keyworddefs", "id",
 454            ["keywords", "keywordid"]);
 455
 456 CrossCheck("fielddefs", "id",
 457            ["bugs_activity", "fieldid"],
 458            ['profiles_activity', 'fieldid']);
 459
 460 CrossCheck("flagtypes", "id",
 461            ["flags", "type_id"],
 462            ["flagexclusions", "type_id"],
 463            ["flaginclusions", "type_id"]);
 464
 465 # Include custom multi-select fields to the list.
 466 my @multi_selects = Bugzilla->get_fields({custom => 1, type => FIELD_TYPE_MULTI_SELECT});
 467 my @addl_fields = map { ['bug_' . $_->name, 'bug_id'] } @multi_selects;
 468
 469 CrossCheck("bugs", "bug_id",
 470            ["bugs_activity", "bug_id"],
 471            ["bug_group_map", "bug_id"],
 472            ["bugs_fulltext", "bug_id"],
 473            ["attachments", "bug_id"],
 474            ["cc", "bug_id"],
 475            ["longdescs", "bug_id"],
 476            ["dependencies", "blocked"],
 477            ["dependencies", "dependson"],
 478            ['flags', 'bug_id'],
 479            ["votes", "bug_id"],
 480            ["keywords", "bug_id"],
 481            ["duplicates", "dupe_of", "dupe"],
 482            ["duplicates", "dupe", "dupe_of"],
 483            @addl_fields);
 484
 485 CrossCheck("groups", "id",
 486            ["bug_group_map", "group_id"],
 487            ['category_group_map', 'group_id'],
 488            ["group_group_map", "grantor_id"],
 489            ["group_group_map", "member_id"],
 490            ["group_control_map", "group_id"],
 491            ["namedquery_group_map", "group_id"],
 492            ["user_group_map", "group_id"],
 493            ["flagtypes", "grant_group_id"],
 494            ["flagtypes", "request_group_id"]);
 495
 496 CrossCheck("namedqueries", "id",
 497            ["namedqueries_link_in_footer", "namedquery_id"],
 498            ["namedquery_group_map", "namedquery_id"],
 499           );
 500
 501 CrossCheck("profiles", "userid",
 502            ['profiles_activity', 'userid'],
 503            ['profiles_activity', 'who'],
 504            ['email_setting', 'user_id'],
 505            ['profile_setting', 'user_id'],
 506            ["bugs", "reporter", "bug_id"],
 507            ["bugs", "assigned_to", "bug_id"],
 508            ["bugs", "qa_contact", "bug_id"],
 509            ["attachments", "submitter_id", "bug_id"],
 510            ['flags', 'setter_id', 'bug_id'],
 511            ['flags', 'requestee_id', 'bug_id'],
 512            ["bugs_activity", "who", "bug_id"],
 513            ["cc", "who", "bug_id"],
 514            ['quips', 'userid'],
 515            ["votes", "who", "bug_id"],
 516            ["longdescs", "who", "bug_id"],
 517            ["logincookies", "userid"],
 518            ["namedqueries", "userid"],
 519            ["namedqueries_link_in_footer", "user_id"],
 520            ['series', 'creator', 'series_id'],
 521            ["watch", "watcher"],
 522            ["watch", "watched"],
 523            ['whine_events', 'owner_userid'],
 524            ["tokens", "userid"],
 525            ["user_group_map", "user_id"],
 526            ["components", "initialowner", "name"],
 527            ["components", "initialqacontact", "name"],
 528            ["component_cc", "user_id"]);
 529
 530 CrossCheck("products", "id",
 531            ["bugs", "product_id", "bug_id"],
 532            ["components", "product_id", "name"],
 533            ["milestones", "product_id", "value"],
 534            ["versions", "product_id", "value"],
 535            ["group_control_map", "product_id"],
 536            ["flaginclusions", "product_id", "type_id"],
 537            ["flagexclusions", "product_id", "type_id"]);
 538
 539 CrossCheck("components", "id",
 540            ["component_cc", "component_id"],
 541            ["flagexclusions", "component_id", "type_id"],
 542            ["flaginclusions", "component_id", "type_id"]);
 543
 544 # Check the former enum types -mkanat@bugzilla.org
 545 CrossCheck("bug_status", "value",
 546             ["bugs", "bug_status", "bug_id"]);
 547
 548 CrossCheck("resolution", "value",
 549             ["bugs", "resolution", "bug_id"]);
 550
 551 CrossCheck("bug_severity", "value",
 552             ["bugs", "bug_severity", "bug_id"]);
 553
 554 CrossCheck("op_sys", "value",
 555             ["bugs", "op_sys", "bug_id"]);
 556
 557 CrossCheck("priority", "value",
 558             ["bugs", "priority", "bug_id"]);
 559
 560 CrossCheck("rep_platform", "value",
 561             ["bugs", "rep_platform", "bug_id"]);
 562
 563 CrossCheck('series', 'series_id',
 564            ['series_data', 'series_id']);
 565
 566 CrossCheck('series_categories', 'id',
 567            ['series', 'category'],
 568            ["category_group_map", "category_id"],
 569            ["series", "subcategory"]);
 570
 571 CrossCheck('whine_events', 'id',
 572            ['whine_queries', 'eventid'],
 573            ['whine_schedules', 'eventid']);
 574
 575 CrossCheck('attachments', 'attach_id',
 576            ['attach_data', 'id'],
 577            ['bugs_activity', 'attach_id']);
 578
 579 CrossCheck('bug_status', 'id',
 580            ['status_workflow', 'old_status'],
 581            ['status_workflow', 'new_status']);
 582
 583 ###########################################################################
 584 # Perform double field referential (cross) checks
 585 ###########################################################################
 586
 587 # This checks that a compound two-field foreign key has a valid primary key
 588 # value.  NULL references are acceptable and cause no problem.
 589 #
 590 # The first parameter is the primary key table name.
 591 # The second parameter is the primary key first field name.
 592 # The third parameter is the primary key second field name.
 593 # Each successive parameter represents a foreign key, it must be a list
 594 # reference, where the list has:
 595 #   the first value is the foreign key table name
 596 #   the second value is the foreign key first field name.
 597 #   the third value is the foreign key second field name.
 598 #   the fourth value is optional and represents a field on the foreign key
 599 #     table to display when the check fails
 600
 601 sub DoubleCrossCheck {
 602     my $table = shift @_;
 603     my $field1 = shift @_;
 604     my $field2 = shift @_;
 605     my $dbh = Bugzilla->dbh;
 606
 607     Status('double_cross_check_to',
 608            {table => $table, field1 => $field1, field2 => $field2});
 609
 610     while (@_) {
 611         my $ref = shift @_;
 612         my ($refertable, $referfield1, $referfield2, $keyname) = @$ref;
 613
 614         Status('double_cross_check_from',
 615                {table => $refertable, field1 => $referfield1, field2 =>$referfield2});
 616
 617         my $d_cross_check = $dbh->selectall_arrayref(qq{
 618                         SELECT DISTINCT $refertable.$referfield1,
 619                                         $refertable.$referfield2 } .
 620                        ($keyname ? qq{, $refertable.$keyname } : q{}) .
 621                       qq{ FROM $refertable
 622                      LEFT JOIN $table
 623                             ON $refertable.$referfield1 = $table.$field1
 624                            AND $refertable.$referfield2 = $table.$field2
 625                          WHERE $table.$field1 IS NULL
 626                            AND $table.$field2 IS NULL
 627                            AND $refertable.$referfield1 IS NOT NULL
 628                            AND $refertable.$referfield2 IS NOT NULL});
 629
 630         foreach my $check (@$d_cross_check) {
 631             my ($value1, $value2, $key) = @$check;
 632             Status('double_cross_check_alert',
 633                    {value1 => $value1, value2 => $value2,
 634                     table => $refertable,
 635                     field1 => $referfield1, field2 => $referfield2,
 636                     keyname => $keyname, key => $key}, 'alert');
 637         }
 638     }
 639 }
 640
 641 DoubleCrossCheck('attachments', 'bug_id', 'attach_id',
 642                  ['flags', 'bug_id', 'attach_id'],
 643                  ['bugs_activity', 'bug_id', 'attach_id']);
 644
 645 DoubleCrossCheck("components", "product_id", "id",
 646                  ["bugs", "product_id", "component_id", "bug_id"],
 647                  ['flagexclusions', 'product_id', 'component_id'],
 648                  ['flaginclusions', 'product_id', 'component_id']);
 649
 650 DoubleCrossCheck("versions", "product_id", "value",
 651                  ["bugs", "product_id", "version", "bug_id"]);
 652
 653 DoubleCrossCheck("milestones", "product_id", "value",
 654                  ["bugs", "product_id", "target_milestone", "bug_id"],
 655                  ["products", "id", "defaultmilestone", "name"]);
 656
 657 ###########################################################################
 658 # Perform login checks
 659 ###########################################################################
 660
 661 Status('profile_login_start');
 662
 663 my $sth = $dbh->prepare(q{SELECT userid, login_name FROM profiles});
 664 $sth->execute;
 665
 666 while (my ($id, $email) = $sth->fetchrow_array) {
 667     validate_email_syntax($email)
 668       || Status('profile_login_alert', {id => $id, email => $email}, 'alert');
 669 }
 670
 671 ###########################################################################
 672 # Perform vote/keyword cache checks
 673 ###########################################################################
 674
 675 check_votes_or_keywords();
 676
 677 sub check_votes_or_keywords {
 678     my $check = shift || 'all';
 679
 680     my $dbh = Bugzilla->dbh;
 681     my $sth = $dbh->prepare(q{SELECT bug_id, votes, keywords
 682                                 FROM bugs
 683                                WHERE votes != 0 OR keywords != ''});
 684     $sth->execute;
 685
 686     my %votes;
 687     my %keyword;
 688
 689     while (my ($id, $v, $k) = $sth->fetchrow_array) {
 690         if ($v != 0) {
 691             $votes{$id} = $v;
 692         }
 693         if ($k) {
 694             $keyword{$id} = $k;
 695         }
 696     }
 697
 698     # If we only want to check keywords, skip checks about votes.
 699     _check_votes(\%votes) unless ($check eq 'keywords');
 700     # If we only want to check votes, skip checks about keywords.
 701     _check_keywords(\%keyword) unless ($check eq 'votes');
 702 }
 703
 704 sub _check_votes {
 705     my $votes = shift;
 706
 707     Status('vote_count_start');
 708     my $dbh = Bugzilla->dbh;
 709     my $sth = $dbh->prepare(q{SELECT bug_id, SUM(vote_count)
 710                                 FROM votes }.
 711                                 $dbh->sql_group_by('bug_id'));
 712     $sth->execute;
 713
 714     my $offer_votecache_rebuild = 0;
 715
 716     while (my ($id, $v) = $sth->fetchrow_array) {
 717         if ($v <= 0) {
 718             Status('vote_count_alert', {id => $id}, 'alert');
 719         } else {
 720             if (!defined $votes->{$id} || $votes->{$id} != $v) {
 721                 Status('vote_cache_alert', {id => $id}, 'alert');
 722                 $offer_votecache_rebuild = 1;
 723             }
 724             delete $votes->{$id};
 725         }
 726     }
 727     foreach my $id (keys %$votes) {
 728         Status('vote_cache_alert', {id => $id}, 'alert');
 729         $offer_votecache_rebuild = 1;
 730     }
 731
 732     Status('vote_cache_rebuild_fix') if $offer_votecache_rebuild;
 733 }
 734
 735 sub _check_keywords {
 736     my $keyword = shift;
 737
 738     Status('keyword_check_start');
 739     my $dbh = Bugzilla->dbh;
 740     my $cgi = Bugzilla->cgi;
 741
 742     my %keywordids;
 743     my $keywords = $dbh->selectall_arrayref(q{SELECT id, name
 744                                                 FROM keyworddefs});
 745
 746     foreach (@$keywords) {
 747         my ($id, $name) = @$_;
 748         if ($keywordids{$id}) {
 749             Status('keyword_check_alert', {id => $id}, 'alert');
 750         }
 751         $keywordids{$id} = 1;
 752         if ($name =~ /[\s,]/) {
 753             Status('keyword_check_invalid_name', {id => $id}, 'alert');
 754         }
 755     }
 756
 757     my $sth = $dbh->prepare(q{SELECT bug_id, keywordid
 758                                 FROM keywords
 759                             ORDER BY bug_id, keywordid});
 760     $sth->execute;
 761     my $lastid;
 762     my $lastk;
 763     while (my ($id, $k) = $sth->fetchrow_array) {
 764         if (!$keywordids{$k}) {
 765             Status('keyword_check_invalid_id', {id => $k}, 'alert');
 766         }
 767         if (defined $lastid && $id eq $lastid && $k eq $lastk) {
 768             Status('keyword_check_duplicated_ids', {id => $id}, 'alert');
 769         }
 770         $lastid = $id;
 771         $lastk = $k;
 772     }
 773
 774     Status('keyword_cache_start');
 775
 776     if ($cgi->param('rebuildkeywordcache')) {
 777         $dbh->bz_start_transaction();
 778     }
 779
 780     my $query = q{SELECT keywords.bug_id, keyworddefs.name
 781                     FROM keywords
 782               INNER JOIN keyworddefs
 783                       ON keyworddefs.id = keywords.keywordid
 784               INNER JOIN bugs
 785                       ON keywords.bug_id = bugs.bug_id
 786                 ORDER BY keywords.bug_id, keyworddefs.name};
 787
 788     $sth = $dbh->prepare($query);
 789     $sth->execute;
 790
 791     my $lastb = 0;
 792     my @list;
 793     my %realk;
 794     while (1) {
 795         my ($b, $k) = $sth->fetchrow_array;
 796         if (!defined $b || $b != $lastb) {
 797             if (@list) {
 798                 $realk{$lastb} = join(', ', @list);
 799             }
 800             last unless $b;
 801
 802             $lastb = $b;
 803             @list = ();
 804         }
 805         push(@list, $k);
 806     }
 807
 808     my @badbugs = ();
 809
 810     foreach my $b (keys(%$keyword)) {
 811         if (!exists $realk{$b} || $realk{$b} ne $keyword->{$b}) {
 812             push(@badbugs, $b);
 813         }
 814     }
 815     foreach my $b (keys(%realk)) {
 816         if (!exists $keyword->{$b}) {
 817             push(@badbugs, $b);
 818         }
 819     }
 820     if (@badbugs) {
 821         @badbugs = sort {$a <=> $b} @badbugs;
 822
 823         if ($cgi->param('rebuildkeywordcache')) {
 824             my $sth_update = $dbh->prepare(q{UPDATE bugs
 825                                                 SET keywords = ?
 826                                               WHERE bug_id = ?});
 827
 828             Status('keyword_cache_fixing');
 829             foreach my $b (@badbugs) {
 830                 my $k = '';
 831                 if (exists($realk{$b})) {
 832                     $k = $realk{$b};
 833                 }
 834                 $sth_update->execute($k, $b);
 835             }
 836             Status('keyword_cache_fixed');
 837         } else {
 838             Status('keyword_cache_alert', {badbugs => \@badbugs}, 'alert');
 839             Status('keyword_cache_rebuild');
 840         }
 841     }
 842
 843     if ($cgi->param('rebuildkeywordcache')) {
 844         $dbh->bz_commit_transaction();
 845     }
 846 }
 847
 848 ###########################################################################
 849 # Check for flags being in incorrect products and components
 850 ###########################################################################
 851
 852 Status('flag_check_start');
 853
 854 my $invalid_flags = $dbh->selectall_arrayref(
 855        'SELECT DISTINCT flags.id, flags.bug_id, flags.attach_id
 856           FROM flags
 857     INNER JOIN bugs
 858             ON flags.bug_id = bugs.bug_id
 859      LEFT JOIN flaginclusions AS i
 860             ON flags.type_id = i.type_id
 861            AND (bugs.product_id = i.product_id OR i.product_id IS NULL)
 862            AND (bugs.component_id = i.component_id OR i.component_id IS NULL)
 863          WHERE i.type_id IS NULL');
 864
 865 my @invalid_flags = @$invalid_flags;
 866
 867 $invalid_flags = $dbh->selectall_arrayref(
 868        'SELECT DISTINCT flags.id, flags.bug_id, flags.attach_id
 869           FROM flags
 870     INNER JOIN bugs
 871             ON flags.bug_id = bugs.bug_id
 872     INNER JOIN flagexclusions AS e
 873             ON flags.type_id = e.type_id
 874          WHERE (bugs.product_id = e.product_id OR e.product_id IS NULL)
 875            AND (bugs.component_id = e.component_id OR e.component_id IS NULL)');
 876
 877 push(@invalid_flags, @$invalid_flags);
 878
 879 if (scalar(@invalid_flags)) {
 880     if ($cgi->param('remove_invalid_flags')) {
 881         Status('flag_deletion_start');
 882         my @flag_ids = map {$_->[0]} @invalid_flags;
 883         # Silently delete these flags, with no notification to requesters/setters.
 884         $dbh->do('DELETE FROM flags WHERE id IN (' . join(',', @flag_ids) .')');
 885         Status('flag_deletion_end');
 886     }
 887     else {
 888         foreach my $flag (@$invalid_flags) {
 889             my ($flag_id, $bug_id, $attach_id) = @$flag;
 890             Status('flag_alert',
 891                    {flag_id => $flag_id, attach_id => $attach_id, bug_id => $bug_id},
 892                    'alert');
 893         }
 894         Status('flag_fix');
 895     }
 896 }
 897
 898 ###########################################################################
 899 # General bug checks
 900 ###########################################################################
 901
 902 sub BugCheck {
 903     my ($middlesql, $errortext, $repairparam, $repairtext) = @_;
 904     my $dbh = Bugzilla->dbh;
 905
 906     my $badbugs = $dbh->selectcol_arrayref(qq{SELECT DISTINCT bugs.bug_id
 907                                                 FROM $middlesql
 908                                             ORDER BY bugs.bug_id});
 909
 910     if (scalar(@$badbugs)) {
 911         Status('bug_check_alert',
 912                {errortext => get_string($errortext), badbugs => $badbugs},
 913                'alert');
 914
 915         if ($repairparam) {
 916             $repairtext ||= 'repair_bugs';
 917             Status('bug_check_repair',
 918                    {param => $repairparam, text => get_string($repairtext)});
 919         }
 920     }
 921 }
 922
 923 Status('bug_check_creation_date');
 924
 925 BugCheck("bugs WHERE creation_ts IS NULL", 'bug_check_creation_date_error_text',
 926          'repair_creation_date', 'bug_check_creation_date_repair_text');
 927
 928 Status('bug_check_bugs_fulltext');
 929
 930 BugCheck("bugs LEFT JOIN bugs_fulltext ON bugs_fulltext.bug_id = bugs.bug_id " .
 931          "WHERE bugs_fulltext.bug_id IS NULL", 'bug_check_bugs_fulltext_error_text',
 932          'repair_bugs_fulltext', 'bug_check_bugs_fulltext_repair_text');
 933
 934 Status('bug_check_res_dupl');
 935
 936 BugCheck("bugs INNER JOIN duplicates ON bugs.bug_id = duplicates.dupe " .
 937          "WHERE bugs.resolution != 'DUPLICATE'", 'bug_check_res_dupl_error_text');
 938
 939 BugCheck("bugs LEFT JOIN duplicates ON bugs.bug_id = duplicates.dupe WHERE " .
 940          "bugs.resolution = 'DUPLICATE' AND " .
 941          "duplicates.dupe IS NULL", 'bug_check_res_dupl_error_text2');
 942
 943 Status('bug_check_status_res');
 944
 945 my @open_states = map($dbh->quote($_), BUG_STATE_OPEN);
 946 my $open_states = join(', ', @open_states);
 947
 948 BugCheck("bugs WHERE bug_status IN ($open_states) AND resolution != ''",
 949          'bug_check_status_res_error_text');
 950 BugCheck("bugs WHERE bug_status NOT IN ($open_states) AND resolution = ''",
 951          'bug_check_status_res_error_text2');
 952
 953 Status('bug_check_status_everconfirmed');
 954
 955 BugCheck("bugs WHERE bug_status = 'UNCONFIRMED' AND everconfirmed = 1",
 956          'bug_check_status_everconfirmed_error_text');
 957
 958 my @confirmed_open_states = grep {$_ ne 'UNCONFIRMED'} BUG_STATE_OPEN;
 959 my $confirmed_open_states = join(', ', map {$dbh->quote($_)} @confirmed_open_states);
 960
 961 BugCheck("bugs WHERE bug_status IN ($confirmed_open_states) AND everconfirmed = 0",
 962          'bug_check_status_everconfirmed_error_text2');
 963
 964 Status('bug_check_votes_everconfirmed');
 965
 966 BugCheck("bugs INNER JOIN products ON bugs.product_id = products.id " .
 967          "WHERE everconfirmed = 0 AND votestoconfirm <= votes",
 968          'bug_check_votes_everconfirmed_error_text');
 969
 970 ###########################################################################
 971 # Control Values
 972 ###########################################################################
 973
 974 # Checks for values that are invalid OR
 975 # not among the 9 valid combinations
 976 Status('bug_check_control_values');
 977 my $groups = join(", ", (CONTROLMAPNA, CONTROLMAPSHOWN, CONTROLMAPDEFAULT,
 978 CONTROLMAPMANDATORY));
 979 my $query = qq{
 980      SELECT COUNT(product_id)
 981        FROM group_control_map
 982       WHERE membercontrol NOT IN( $groups )
 983          OR othercontrol NOT IN( $groups )
 984          OR ((membercontrol != othercontrol)
 985              AND (membercontrol != } . CONTROLMAPSHOWN . q{)
 986              AND ((membercontrol != } . CONTROLMAPDEFAULT . q{)
 987                   OR (othercontrol = } . CONTROLMAPSHOWN . q{)))};
 988
 989 my $entries = $dbh->selectrow_array($query);
 990 Status('bug_check_control_values_alert', {entries => $entries}, 'alert') if $entries;
 991
 992 Status('bug_check_control_values_violation');
 993 BugCheck("bugs
 994          INNER JOIN bug_group_map
 995             ON bugs.bug_id = bug_group_map.bug_id
 996           LEFT JOIN group_control_map
 997             ON bugs.product_id = group_control_map.product_id
 998            AND bug_group_map.group_id = group_control_map.group_id
 999          WHERE ((group_control_map.membercontrol = " . CONTROLMAPNA . ")
1000          OR (group_control_map.membercontrol IS NULL))",
1001          'bug_check_control_values_error_text',
1002          'createmissinggroupcontrolmapentries',
1003          'bug_check_control_values_repair_text');
1004
1005 BugCheck("bugs
1006          INNER JOIN group_control_map
1007             ON bugs.product_id = group_control_map.product_id
1008          INNER JOIN groups
1009             ON group_control_map.group_id = groups.id
1010           LEFT JOIN bug_group_map
1011             ON bugs.bug_id = bug_group_map.bug_id
1012            AND group_control_map.group_id = bug_group_map.group_id
1013          WHERE group_control_map.membercontrol = " . CONTROLMAPMANDATORY . "
1014            AND bug_group_map.group_id IS NULL
1015            AND groups.isactive != 0",
1016          'bug_check_control_values_error_text2');
1017
1018 ###########################################################################
1019 # Unsent mail
1020 ###########################################################################
1021
1022 Status('unsent_bugmail_check');
1023
1024 my $time = $dbh->sql_interval(30, 'MINUTE');
1025 my $badbugs = $dbh->selectcol_arrayref(qq{
1026                     SELECT bug_id
1027                       FROM bugs
1028                      WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts)
1029                        AND delta_ts < now() - $time
1030                   ORDER BY bug_id});
1031
1032
1033 if (scalar(@$badbugs > 0)) {
1034     Status('unsent_bugmail_alert', {badbugs => $badbugs}, 'alert');
1035     Status('unsent_bugmail_fix');
1036 }
1037
1038 ###########################################################################
1039 # Whines
1040 ###########################################################################
1041
1042 Status('whines_obsolete_target_start');
1043
1044 my $display_repair_whines_link = 0;
1045 foreach my $target (['groups', 'id', MAILTO_GROUP],
1046                     ['profiles', 'userid', MAILTO_USER])
1047 {
1048     my ($table, $col, $type) = @$target;
1049     my $old = $dbh->selectall_arrayref("SELECT whine_schedules.id, mailto
1050                                           FROM whine_schedules
1051                                      LEFT JOIN $table
1052                                             ON $table.$col = whine_schedules.mailto
1053                                          WHERE mailto_type = $type AND $table.$col IS NULL");
1054
1055     if (scalar(@$old)) {
1056         Status('whines_obsolete_target_alert', {schedules => $old, type => $type}, 'alert');
1057         $display_repair_whines_link = 1;
1058     }
1059 }
1060 Status('whines_obsolete_target_fix') if $display_repair_whines_link;
1061
1062 ###########################################################################
1063 # End
1064 ###########################################################################
1065
1066 Status('checks_completed');
1067
1068 unless (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
1069     $template->process('global/footer.html.tmpl', $vars)
1070       || ThrowTemplateError($template->error());
1071 }