sanitycheck.cgi 34.4 KB
Newer Older
1
#!/usr/bin/perl -wT
2 3 4
# 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/.
5
#
6 7
# This Source Code Form is "Incompatible With Secondary Licenses", as
# defined by the Mozilla Public License, v. 2.0.
8

9
use strict;
10

11
use lib qw(. lib);
12

13
use Bugzilla;
14
use Bugzilla::Bug;
15
use Bugzilla::Constants;
16
use Bugzilla::Error;
17 18
use Bugzilla::Hook;
use Bugzilla::Util;
19
use Bugzilla::Status;
20
use Bugzilla::Token;
21

22 23 24
###########################################################################
# General subs
###########################################################################
25

26 27 28 29
sub get_string {
    my ($san_tag, $vars) = @_;
    $vars->{'san_tag'} = $san_tag;
    return get_text('sanitycheck', $vars);
30 31
}

32 33
sub Status {
    my ($san_tag, $vars, $alert) = @_;
34 35 36 37 38 39 40 41 42 43 44
    my $cgi = Bugzilla->cgi;
    return if (!$alert && Bugzilla->usage_mode == USAGE_MODE_CMDLINE && !$cgi->param('verbose'));

    if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
        my $output = $cgi->param('output') || '';
        my $linebreak = $alert ? "\nALERT: " : "\n";
        $cgi->param('error_found', 1) if $alert;
        $cgi->param('output', $output . $linebreak . get_string($san_tag, $vars));
    }
    else {
        my $start_tag = $alert ? '<p class="alert">' : '<p>';
45
        say $start_tag . get_string($san_tag, $vars) . "</p>";
46
    }
47 48
}

49 50 51
###########################################################################
# Start
###########################################################################
52

53
my $user = Bugzilla->login(LOGIN_REQUIRED);
54

55
my $cgi = Bugzilla->cgi;
56
my $dbh = Bugzilla->dbh;
57 58 59 60
# If the result of the sanity check is sent per email, then we have to
# take the user prefs into account rather than querying the web browser.
my $template;
if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
61
    $template = Bugzilla->template_inner($user->setting('lang'));
62 63 64
}
else {
    $template = Bugzilla->template;
65 66 67 68 69 70 71 72 73

    # Only check the token if we are running this script from the
    # web browser and a parameter is passed to the script.
    # XXX - Maybe these two parameters should be deleted once logged in?
    $cgi->delete('GoAheadAndLogIn', 'Bugzilla_restrictlogin');
    if (scalar($cgi->param())) {
        my $token = $cgi->param('token');
        check_hash_token($token, ['sanitycheck']);
    }
74
}
75 76
my $vars = {};

77
print $cgi->header() unless Bugzilla->usage_mode == USAGE_MODE_CMDLINE;
78

79 80 81
# Make sure the user is authorized to access sanitycheck.cgi.
# As this script can now alter the group_control_map table, we no longer
# let users with editbugs privs run it anymore.
82
$user->in_group("editcomponents")
83
  || ThrowUserError("auth_failure", {group  => "editcomponents",
84 85
                                     action => "run",
                                     object => "sanity_check"});
86

87 88 89 90
unless (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
    $template->process('admin/sanitycheck/list.html.tmpl', $vars)
      || ThrowTemplateError($template->error());
}
91

92 93 94 95
###########################################################################
# Create missing group_control_map entries
###########################################################################

96 97
if ($cgi->param('createmissinggroupcontrolmapentries')) {
    Status('group_control_map_entries_creation');
98 99 100 101

    my $na    = CONTROLMAPNA;
    my $shown = CONTROLMAPSHOWN;
    my $insertsth = $dbh->prepare(
102 103 104 105
        qq{INSERT INTO group_control_map
                       (group_id, product_id, membercontrol, othercontrol)
                VALUES (?, ?, $shown, $na)});

106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130
    my $updatesth = $dbh->prepare(qq{UPDATE group_control_map
                                        SET membercontrol = $shown
                                      WHERE group_id   = ?
                                        AND product_id = ?});
    my $counter = 0;

    # Find all group/product combinations used for bugs but not set up
    # correctly in group_control_map
    my $invalid_combinations = $dbh->selectall_arrayref(
        qq{    SELECT bugs.product_id,
                      bgm.group_id,
                      gcm.membercontrol,
                      groups.name,
                      products.name
                 FROM bugs
           INNER JOIN bug_group_map AS bgm
                   ON bugs.bug_id = bgm.bug_id
           INNER JOIN groups
                   ON bgm.group_id = groups.id
           INNER JOIN products
                   ON bugs.product_id = products.id
            LEFT JOIN group_control_map AS gcm
                   ON bugs.product_id = gcm.product_id
                  AND    bgm.group_id = gcm.group_id
                WHERE COALESCE(gcm.membercontrol, $na) = $na
131 132
          } . $dbh->sql_group_by('bugs.product_id, bgm.group_id',
                                 'gcm.membercontrol, groups.name, products.name'));
133 134 135 136 137 138 139

    foreach (@$invalid_combinations) {
        my ($product_id, $group_id, $currentmembercontrol,
            $group_name, $product_name) = @$_;

        $counter++;
        if (defined($currentmembercontrol)) {
140 141
            Status('group_control_map_entries_update',
                   {group_name => $group_name, product_name => $product_name});
142 143 144
            $updatesth->execute($group_id, $product_id);
        }
        else {
145 146
            Status('group_control_map_entries_generation',
                   {group_name => $group_name, product_name => $product_name});
147 148 149 150
            $insertsth->execute($group_id, $product_id);
        }
    }

151
    Status('group_control_map_entries_repaired', {counter => $counter});
152 153
}

154 155 156 157
###########################################################################
# Fix missing creation date
###########################################################################

158 159
if ($cgi->param('repair_creation_date')) {
    Status('bug_creation_date_start');
160 161 162 163 164 165 166 167

    my $bug_ids = $dbh->selectcol_arrayref('SELECT bug_id FROM bugs
                                            WHERE creation_ts IS NULL');

    my $sth_UpdateDate = $dbh->prepare('UPDATE bugs SET creation_ts = ?
                                        WHERE bug_id = ?');

    # All bugs have an entry in the 'longdescs' table when they are created,
168
    # even if no comment is required.
169 170 171 172 173 174 175 176
    my $sth_getDate = $dbh->prepare('SELECT MIN(bug_when) FROM longdescs
                                     WHERE bug_id = ?');

    foreach my $bugid (@$bug_ids) {
        $sth_getDate->execute($bugid);
        my $date = $sth_getDate->fetchrow_array;
        $sth_UpdateDate->execute($date, $bugid);
    }
177
    Status('bug_creation_date_fixed', {bug_count => scalar(@$bug_ids)});
178 179
}

180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195
###########################################################################
# Fix everconfirmed
###########################################################################

if ($cgi->param('repair_everconfirmed')) {
    Status('everconfirmed_start');

    my @confirmed_open_states = grep {$_ ne 'UNCONFIRMED'} BUG_STATE_OPEN;
    my $confirmed_open_states = join(', ', map {$dbh->quote($_)} @confirmed_open_states);

    $dbh->do("UPDATE bugs SET everconfirmed = 0 WHERE bug_status = 'UNCONFIRMED'");
    $dbh->do("UPDATE bugs SET everconfirmed = 1 WHERE bug_status IN ($confirmed_open_states)");

    Status('everconfirmed_end');
}

196 197 198 199 200 201 202 203 204 205 206 207 208 209
###########################################################################
# Fix entries in Bugs full_text
###########################################################################

if ($cgi->param('repair_bugs_fulltext')) {
    Status('bugs_fulltext_start');

    my $bug_ids = $dbh->selectcol_arrayref('SELECT bugs.bug_id
                                            FROM bugs
                                            LEFT JOIN bugs_fulltext
                                            ON bugs_fulltext.bug_id = bugs.bug_id
                                            WHERE bugs_fulltext.bug_id IS NULL');

   foreach my $bugid (@$bug_ids) {
210
       Bugzilla::Bug->new($bugid)->_sync_fulltext( new_bug => 1 );
211 212 213 214 215
   }

   Status('bugs_fulltext_fixed', {bug_count => scalar(@$bug_ids)});
}

216 217 218 219
###########################################################################
# Send unsent mail
###########################################################################

220
if ($cgi->param('rescanallBugMail')) {
221 222
    require Bugzilla::BugMail;

223
    Status('send_bugmail_start');
224
    my $time = $dbh->sql_date_math('NOW()', '-', 30, 'MINUTE');
225

226 227 228 229 230
    my $list = $dbh->selectcol_arrayref(qq{
                                        SELECT bug_id
                                          FROM bugs 
                                         WHERE (lastdiffed IS NULL
                                                OR lastdiffed < delta_ts)
231
                                           AND delta_ts < $time
232
                                      ORDER BY bug_id});
233

234 235
    Status('send_bugmail_status', {bug_count => scalar(@$list)});

236 237 238 239 240 241 242
    # We cannot simply look at the bugs_activity table to find who did the
    # last change in a given bug, as e.g. adding a comment doesn't add any
    # entry to this table. And some other changes may be private
    # (such as time-related changes or private attachments or comments)
    # and so choosing this user as being the last one having done a change
    # for the bug may be problematic. So the best we can do at this point
    # is to choose the currently logged in user for email notification.
243
    $vars->{'changer'} = $user;
244

245
    foreach my $bugid (@$list) {
246
        Bugzilla::BugMail::Send($bugid, $vars);
247 248
    }

249
    Status('send_bugmail_end') if scalar(@$list);
250

251 252 253 254
    unless (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
        $template->process('global/footer.html.tmpl', $vars)
          || ThrowTemplateError($template->error());
    }
255 256 257
    exit;
}

258 259 260 261
###########################################################################
# Remove all references to deleted bugs
###########################################################################

262 263
if ($cgi->param('remove_invalid_bug_references')) {
    Status('bug_reference_deletion_start');
264

265
    $dbh->bz_start_transaction();
266

267 268
    foreach my $pair ('attachments/', 'bug_group_map/', 'bugs_activity/',
                      'bugs_fulltext/', 'cc/',
269 270
                      'dependencies/blocked', 'dependencies/dependson',
                      'duplicates/dupe', 'duplicates/dupe_of',
271
                      'flags/', 'keywords/', 'longdescs/') {
272 273 274 275 276 277 278 279 280 281 282 283 284 285

        my ($table, $field) = split('/', $pair);
        $field ||= "bug_id";

        my $bug_ids =
          $dbh->selectcol_arrayref("SELECT $table.$field FROM $table
                                    LEFT JOIN bugs ON $table.$field = bugs.bug_id
                                    WHERE bugs.bug_id IS NULL");

        if (scalar(@$bug_ids)) {
            $dbh->do("DELETE FROM $table WHERE $field IN (" . join(',', @$bug_ids) . ")");
        }
    }

286
    $dbh->bz_commit_transaction();
287
    Status('bug_reference_deletion_end');
288 289
}

290 291 292 293
###########################################################################
# Remove all references to deleted attachments
###########################################################################

294 295
if ($cgi->param('remove_invalid_attach_references')) {
    Status('attachment_reference_deletion_start');
296

297
    $dbh->bz_start_transaction();
298 299 300 301 302 303 304 305 306 307 308 309 310

    my $attach_ids =
        $dbh->selectcol_arrayref('SELECT attach_data.id
                                    FROM attach_data
                               LEFT JOIN attachments
                                      ON attachments.attach_id = attach_data.id
                                   WHERE attachments.attach_id IS NULL');

    if (scalar(@$attach_ids)) {
        $dbh->do('DELETE FROM attach_data WHERE id IN (' .
                 join(',', @$attach_ids) . ')');
    }

311
    $dbh->bz_commit_transaction();
312
    Status('attachment_reference_deletion_end');
313
}
314

315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344
###########################################################################
# Remove all references to deleted users or groups from whines
###########################################################################

if ($cgi->param('remove_old_whine_targets')) {
    Status('whines_obsolete_target_deletion_start');

    $dbh->bz_start_transaction();

    foreach my $target (['groups', 'id', MAILTO_GROUP],
                        ['profiles', 'userid', MAILTO_USER])
    {
        my ($table, $col, $type) = @$target;
        my $old_ids =
          $dbh->selectcol_arrayref("SELECT DISTINCT mailto
                                      FROM whine_schedules
                                 LEFT JOIN $table
                                        ON $table.$col = whine_schedules.mailto
                                     WHERE mailto_type = $type AND $table.$col IS NULL");

        if (scalar(@$old_ids)) {
            $dbh->do("DELETE FROM whine_schedules
                       WHERE mailto_type = $type AND mailto IN (" .
                       join(',', @$old_ids) . ")");
        }
    }
    $dbh->bz_commit_transaction();
    Status('whines_obsolete_target_deletion_end');
}

345 346 347 348
###########################################################################
# Repair hook
###########################################################################

349
Bugzilla::Hook::process('sanitycheck_repair', { status => \&Status });
350 351 352 353

###########################################################################
# Checks
###########################################################################
354
Status('checks_start');
355

356 357 358 359
###########################################################################
# Perform referential (cross) checks
###########################################################################

360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375
# This checks that a simple foreign key has a valid primary key value.  NULL
# references are acceptable and cause no problem.
#
# The first parameter is the primary key table name.
# The second parameter is the primary key field name.
# Each successive parameter represents a foreign key, it must be a list
# reference, where the list has:
#   the first value is the foreign key table name.
#   the second value is the foreign key field name.
#   the third value is optional and represents a field on the foreign key
#     table to display when the check fails.
#   the fourth value is optional and is a list reference to values that
#     are excluded from checking.
#
# FIXME: The excluded values parameter should go away - the QA contact
#        fields should use NULL instead - see bug #109474.
376
#        The same goes for series; no bug for that yet.
377

378 379 380
sub CrossCheck {
    my $table = shift @_;
    my $field = shift @_;
381
    my $dbh = Bugzilla->dbh;
382

383
    Status('cross_check_to', {table => $table, field => $field});
384 385 386 387 388 389 390 391

    while (@_) {
        my $ref = shift @_;
        my ($refertable, $referfield, $keyname, $exceptions) = @$ref;

        $exceptions ||= [];
        my %exceptions = map { $_ => 1 } @$exceptions;

392 393
        Status('cross_check_from', {table => $refertable, field => $referfield});

394 395 396 397 398 399 400
        my $query = qq{SELECT DISTINCT $refertable.$referfield} .
            ($keyname ? qq{, $refertable.$keyname } : q{}) .
                     qq{ FROM $refertable
                    LEFT JOIN $table
                           ON $refertable.$referfield = $table.$field
                        WHERE $table.$field IS NULL
                          AND $refertable.$referfield IS NOT NULL};
401

402 403
        my $sth = $dbh->prepare($query);
        $sth->execute;
404

405
        my $has_bad_references = 0;
406 407 408

        while (my ($value, $key) = $sth->fetchrow_array) {
            next if $exceptions{$value};
409 410 411
            Status('cross_check_alert', {value => $value, table => $refertable,
                                         field => $referfield, keyname => $keyname,
                                         key => $key}, 'alert');
412
            $has_bad_references = 1;
413
        }
414 415
        # References to non existent bugs can be safely removed, bug 288461
        if ($table eq 'bugs' && $has_bad_references) {
416
            Status('cross_check_bug_has_references');
417 418 419
        }
        # References to non existent attachments can be safely removed.
        if ($table eq 'attachments' && $has_bad_references) {
420
            Status('cross_check_attachment_has_references');
421
        }
422 423 424
    }
}

425 426 427
CrossCheck('classifications', 'id',
           ['products', 'classification_id']);

428 429 430
CrossCheck("keyworddefs", "id",
           ["keywords", "keywordid"]);

431
CrossCheck("fielddefs", "id",
432 433
           ["bugs_activity", "fieldid"],
           ['profiles_activity', 'fieldid']);
434

435
CrossCheck("flagtypes", "id",
436 437 438
           ["flags", "type_id"],
           ["flagexclusions", "type_id"],
           ["flaginclusions", "type_id"]);
439 440 441

CrossCheck("bugs", "bug_id",
           ["bugs_activity", "bug_id"],
442
           ["bug_group_map", "bug_id"],
443
           ["bugs_fulltext", "bug_id"],
444 445 446 447 448
           ["attachments", "bug_id"],
           ["cc", "bug_id"],
           ["longdescs", "bug_id"],
           ["dependencies", "blocked"],
           ["dependencies", "dependson"],
449
           ['flags', 'bug_id'],
450 451 452
           ["keywords", "bug_id"],
           ["duplicates", "dupe_of", "dupe"],
           ["duplicates", "dupe", "dupe_of"]);
453

454 455
CrossCheck("groups", "id",
           ["bug_group_map", "group_id"],
456
           ['category_group_map', 'group_id'],
457 458
           ["group_group_map", "grantor_id"],
           ["group_group_map", "member_id"],
459
           ["group_control_map", "group_id"],
460
           ["namedquery_group_map", "group_id"],
461 462 463
           ["user_group_map", "group_id"],
           ["flagtypes", "grant_group_id"],
           ["flagtypes", "request_group_id"]);
464

465 466 467 468 469
CrossCheck("namedqueries", "id",
           ["namedqueries_link_in_footer", "namedquery_id"],
           ["namedquery_group_map", "namedquery_id"],
          );

470
CrossCheck("profiles", "userid",
471 472
           ['profiles_activity', 'userid'],
           ['profiles_activity', 'who'],
473 474
           ['email_setting', 'user_id'],
           ['profile_setting', 'user_id'],
475 476
           ["bugs", "reporter", "bug_id"],
           ["bugs", "assigned_to", "bug_id"],
477
           ["bugs", "qa_contact", "bug_id"],
478
           ["attachments", "submitter_id", "bug_id"],
479 480
           ['flags', 'setter_id', 'bug_id'],
           ['flags', 'requestee_id', 'bug_id'],
481 482
           ["bugs_activity", "who", "bug_id"],
           ["cc", "who", "bug_id"],
483
           ['quips', 'userid'],
484
           ["longdescs", "who", "bug_id"],
485
           ["logincookies", "userid"],
486
           ["namedqueries", "userid"],
487
           ["namedqueries_link_in_footer", "user_id"],
488
           ['series', 'creator', 'series_id'],
489 490
           ["watch", "watcher"],
           ["watch", "watched"],
491
           ['whine_events', 'owner_userid'],
492
           ["tokens", "userid"],
493
           ["user_group_map", "user_id"],
494
           ["components", "initialowner", "name"],
495 496
           ["components", "initialqacontact", "name"],
           ["component_cc", "user_id"]);
497

498 499 500 501 502
CrossCheck("products", "id",
           ["bugs", "product_id", "bug_id"],
           ["components", "product_id", "name"],
           ["milestones", "product_id", "value"],
           ["versions", "product_id", "value"],
503
           ["group_control_map", "product_id"],
504 505
           ["flaginclusions", "product_id", "type_id"],
           ["flagexclusions", "product_id", "type_id"]);
506

507
CrossCheck("components", "id",
508 509 510
           ["component_cc", "component_id"],
           ["flagexclusions", "component_id", "type_id"],
           ["flaginclusions", "component_id", "type_id"]);
511

512
# Check the former enum types -mkanat@bugzilla.org
513
CrossCheck("bug_status", "value",
514
            ["bugs", "bug_status", "bug_id"]);
515 516

CrossCheck("resolution", "value",
517
            ["bugs", "resolution", "bug_id"]);
518 519

CrossCheck("bug_severity", "value",
520
            ["bugs", "bug_severity", "bug_id"]);
521 522

CrossCheck("op_sys", "value",
523
            ["bugs", "op_sys", "bug_id"]);
524 525

CrossCheck("priority", "value",
526
            ["bugs", "priority", "bug_id"]);
527 528

CrossCheck("rep_platform", "value",
529
            ["bugs", "rep_platform", "bug_id"]);
530

531 532 533 534
CrossCheck('series', 'series_id',
           ['series_data', 'series_id']);

CrossCheck('series_categories', 'id',
535 536 537
           ['series', 'category'],
           ["category_group_map", "category_id"],
           ["series", "subcategory"]);
538 539 540 541 542

CrossCheck('whine_events', 'id',
           ['whine_queries', 'eventid'],
           ['whine_schedules', 'eventid']);

543
CrossCheck('attachments', 'attach_id',
544 545
           ['attach_data', 'id'],
           ['bugs_activity', 'attach_id']);
546

547 548 549 550
CrossCheck('bug_status', 'id',
           ['status_workflow', 'old_status'],
           ['status_workflow', 'new_status']);

551
###########################################################################
552
# Perform double field referential (cross) checks
553
###########################################################################
554
 
555 556 557 558 559 560 561 562 563 564 565 566 567 568
# This checks that a compound two-field foreign key has a valid primary key
# value.  NULL references are acceptable and cause no problem.
#
# The first parameter is the primary key table name.
# The second parameter is the primary key first field name.
# The third parameter is the primary key second field name.
# Each successive parameter represents a foreign key, it must be a list
# reference, where the list has:
#   the first value is the foreign key table name
#   the second value is the foreign key first field name.
#   the third value is the foreign key second field name.
#   the fourth value is optional and represents a field on the foreign key
#     table to display when the check fails

569 570 571 572
sub DoubleCrossCheck {
    my $table = shift @_;
    my $field1 = shift @_;
    my $field2 = shift @_;
573
    my $dbh = Bugzilla->dbh;
574 575 576 577

    Status('double_cross_check_to',
           {table => $table, field1 => $field1, field2 => $field2});

578 579 580
    while (@_) {
        my $ref = shift @_;
        my ($refertable, $referfield1, $referfield2, $keyname) = @$ref;
581 582 583

        Status('double_cross_check_from',
               {table => $refertable, field1 => $referfield1, field2 =>$referfield2});
584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599

        my $d_cross_check = $dbh->selectall_arrayref(qq{
                        SELECT DISTINCT $refertable.$referfield1, 
                                        $refertable.$referfield2 } .
                       ($keyname ? qq{, $refertable.$keyname } : q{}) .
                      qq{ FROM $refertable
                     LEFT JOIN $table
                            ON $refertable.$referfield1 = $table.$field1
                           AND $refertable.$referfield2 = $table.$field2 
                         WHERE $table.$field1 IS NULL 
                           AND $table.$field2 IS NULL 
                           AND $refertable.$referfield1 IS NOT NULL 
                           AND $refertable.$referfield2 IS NOT NULL});

        foreach my $check (@$d_cross_check) {
            my ($value1, $value2, $key) = @$check;
600 601 602 603 604
            Status('double_cross_check_alert',
                   {value1 => $value1, value2 => $value2,
                    table => $refertable,
                    field1 => $referfield1, field2 => $referfield2,
                    keyname => $keyname, key => $key}, 'alert');
605
        }
606 607 608
    }
}

609 610 611 612
DoubleCrossCheck('attachments', 'bug_id', 'attach_id',
                 ['flags', 'bug_id', 'attach_id'],
                 ['bugs_activity', 'bug_id', 'attach_id']);

613
DoubleCrossCheck("components", "product_id", "id",
614 615 616
                 ["bugs", "product_id", "component_id", "bug_id"],
                 ['flagexclusions', 'product_id', 'component_id'],
                 ['flaginclusions', 'product_id', 'component_id']);
617

618 619 620 621 622 623
DoubleCrossCheck("versions", "product_id", "value",
                 ["bugs", "product_id", "version", "bug_id"]);
 
DoubleCrossCheck("milestones", "product_id", "value",
                 ["bugs", "product_id", "target_milestone", "bug_id"],
                 ["products", "id", "defaultmilestone", "name"]);
624

625 626 627
###########################################################################
# Perform login checks
###########################################################################
628 629

Status('profile_login_start');
630

631 632
my $sth = $dbh->prepare(q{SELECT userid, login_name FROM profiles});
$sth->execute;
633

634
while (my ($id, $email) = $sth->fetchrow_array) {
635
    validate_email_syntax($email)
636
      || Status('profile_login_alert', {id => $id, email => $email}, 'alert');
637
}
638

639
###########################################################################
640
# Perform keyword checks
641
###########################################################################
642

643
sub check_keywords {
644
    my $dbh = Bugzilla->dbh;
645
    my $cgi = Bugzilla->cgi;
646

647
    Status('keyword_check_start');
648

649 650 651
    my %keywordids;
    my $keywords = $dbh->selectall_arrayref(q{SELECT id, name
                                                FROM keyworddefs});
652

653 654 655
    foreach (@$keywords) {
        my ($id, $name) = @$_;
        if ($keywordids{$id}) {
656
            Status('keyword_check_alert', {id => $id}, 'alert');
657 658 659
        }
        $keywordids{$id} = 1;
        if ($name =~ /[\s,]/) {
660
            Status('keyword_check_invalid_name', {id => $id}, 'alert');
661 662
        }
    }
663

664 665 666 667 668 669 670 671
    my $sth = $dbh->prepare(q{SELECT bug_id, keywordid
                                FROM keywords
                            ORDER BY bug_id, keywordid});
    $sth->execute;
    my $lastid;
    my $lastk;
    while (my ($id, $k) = $sth->fetchrow_array) {
        if (!$keywordids{$k}) {
672
            Status('keyword_check_invalid_id', {id => $k}, 'alert');
673
        }
674
        if (defined $lastid && $id eq $lastid && $k eq $lastk) {
675
            Status('keyword_check_duplicated_ids', {id => $id}, 'alert');
676
        }
677 678
        $lastid = $id;
        $lastk = $k;
679
    }
680
}
681

682 683 684 685
###########################################################################
# Check for flags being in incorrect products and components
###########################################################################

686
Status('flag_check_start');
687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714

my $invalid_flags = $dbh->selectall_arrayref(
       'SELECT DISTINCT flags.id, flags.bug_id, flags.attach_id
          FROM flags
    INNER JOIN bugs
            ON flags.bug_id = bugs.bug_id
     LEFT JOIN flaginclusions AS i
            ON flags.type_id = i.type_id
           AND (bugs.product_id = i.product_id OR i.product_id IS NULL)
           AND (bugs.component_id = i.component_id OR i.component_id IS NULL)
         WHERE i.type_id IS NULL');

my @invalid_flags = @$invalid_flags;

$invalid_flags = $dbh->selectall_arrayref(
       'SELECT DISTINCT flags.id, flags.bug_id, flags.attach_id
          FROM flags
    INNER JOIN bugs
            ON flags.bug_id = bugs.bug_id
    INNER JOIN flagexclusions AS e
            ON flags.type_id = e.type_id
         WHERE (bugs.product_id = e.product_id OR e.product_id IS NULL)
           AND (bugs.component_id = e.component_id OR e.component_id IS NULL)');

push(@invalid_flags, @$invalid_flags);

if (scalar(@invalid_flags)) {
    if ($cgi->param('remove_invalid_flags')) {
715
        Status('flag_deletion_start');
716 717 718
        my @flag_ids = map {$_->[0]} @invalid_flags;
        # Silently delete these flags, with no notification to requesters/setters.
        $dbh->do('DELETE FROM flags WHERE id IN (' . join(',', @flag_ids) .')');
719
        Status('flag_deletion_end');
720 721 722 723
    }
    else {
        foreach my $flag (@$invalid_flags) {
            my ($flag_id, $bug_id, $attach_id) = @$flag;
724 725 726
            Status('flag_alert',
                   {flag_id => $flag_id, attach_id => $attach_id, bug_id => $bug_id},
                   'alert');
727
        }
728
        Status('flag_fix');
729 730 731
    }
}

732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751
###########################################################################
# Check for products with no component
###########################################################################

Status('product_check_start');

my $products_missing_data = $dbh->selectcol_arrayref(
      'SELECT DISTINCT products.name
         FROM products
    LEFT JOIN components
           ON components.product_id = products.id
    LEFT JOIN versions
           ON versions.product_id = products.id
        WHERE components.id IS NULL
           OR versions.id IS NULL');

if (scalar(@$products_missing_data)) {
    Status('product_alert', { name => $_ }, 'alert') foreach @$products_missing_data;
}

752
###########################################################################
753
# General bug checks
754 755
###########################################################################

756
sub BugCheck {
757
    my ($middlesql, $errortext, $repairparam, $repairtext) = @_;
758
    my $dbh = Bugzilla->dbh;
759 760 761 762
 
    my $badbugs = $dbh->selectcol_arrayref(qq{SELECT DISTINCT bugs.bug_id
                                                FROM $middlesql 
                                            ORDER BY bugs.bug_id});
763

764
    if (scalar(@$badbugs)) {
765 766 767 768
        Status('bug_check_alert',
               {errortext => get_string($errortext), badbugs => $badbugs},
               'alert');

769
        if ($repairparam) {
770 771 772
            $repairtext ||= 'repair_bugs';
            Status('bug_check_repair',
                   {param => $repairparam, text => get_string($repairtext)});
773
        }
774
    }
775 776
}

777
Status('bug_check_creation_date');
778

779 780
BugCheck("bugs WHERE creation_ts IS NULL", 'bug_check_creation_date_error_text',
         'repair_creation_date', 'bug_check_creation_date_repair_text');
781

782 783 784 785 786 787
Status('bug_check_bugs_fulltext');

BugCheck("bugs LEFT JOIN bugs_fulltext ON bugs_fulltext.bug_id = bugs.bug_id " .
         "WHERE bugs_fulltext.bug_id IS NULL", 'bug_check_bugs_fulltext_error_text',
         'repair_bugs_fulltext', 'bug_check_bugs_fulltext_repair_text');

788
Status('bug_check_res_dupl');
789

790
BugCheck("bugs INNER JOIN duplicates ON bugs.bug_id = duplicates.dupe " .
791
         "WHERE bugs.resolution != 'DUPLICATE'", 'bug_check_res_dupl_error_text');
792

793 794
BugCheck("bugs LEFT JOIN duplicates ON bugs.bug_id = duplicates.dupe WHERE " .
         "bugs.resolution = 'DUPLICATE' AND " .
795
         "duplicates.dupe IS NULL", 'bug_check_res_dupl_error_text2');
796

797
Status('bug_check_status_res');
798

799
my @open_states = map($dbh->quote($_), BUG_STATE_OPEN);
800 801
my $open_states = join(', ', @open_states);

802
BugCheck("bugs WHERE bug_status IN ($open_states) AND resolution != ''",
803
         'bug_check_status_res_error_text');
804
BugCheck("bugs WHERE bug_status NOT IN ($open_states) AND resolution = ''",
805
         'bug_check_status_res_error_text2');
806

807
Status('bug_check_status_everconfirmed');
808

809
BugCheck("bugs WHERE bug_status = 'UNCONFIRMED' AND everconfirmed = 1",
810
         'bug_check_status_everconfirmed_error_text', 'repair_everconfirmed');
811 812 813 814 815

my @confirmed_open_states = grep {$_ ne 'UNCONFIRMED'} BUG_STATE_OPEN;
my $confirmed_open_states = join(', ', map {$dbh->quote($_)} @confirmed_open_states);

BugCheck("bugs WHERE bug_status IN ($confirmed_open_states) AND everconfirmed = 0",
816
         'bug_check_status_everconfirmed_error_text2', 'repair_everconfirmed');
817

818 819 820 821 822 823
###########################################################################
# Control Values
###########################################################################

# Checks for values that are invalid OR
# not among the 9 valid combinations
824
Status('bug_check_control_values');
825 826
my $groups = join(", ", (CONTROLMAPNA, CONTROLMAPSHOWN, CONTROLMAPDEFAULT,
CONTROLMAPMANDATORY));
827
my $query = qq{
828 829 830 831 832 833 834 835
     SELECT COUNT(product_id) 
       FROM group_control_map 
      WHERE membercontrol NOT IN( $groups )
         OR othercontrol NOT IN( $groups )
         OR ((membercontrol != othercontrol)
             AND (membercontrol != } . CONTROLMAPSHOWN . q{)
             AND ((membercontrol != } . CONTROLMAPDEFAULT . q{)
                  OR (othercontrol = } . CONTROLMAPSHOWN . q{)))};
836

837 838 839 840
my $entries = $dbh->selectrow_array($query);
Status('bug_check_control_values_alert', {entries => $entries}, 'alert') if $entries;

Status('bug_check_control_values_violation');
841
BugCheck("bugs
842 843 844 845
         INNER JOIN bug_group_map
            ON bugs.bug_id = bug_group_map.bug_id
          LEFT JOIN group_control_map
            ON bugs.product_id = group_control_map.product_id
846
           AND bug_group_map.group_id = group_control_map.group_id
847
         WHERE ((group_control_map.membercontrol = " . CONTROLMAPNA . ")
848
         OR (group_control_map.membercontrol IS NULL))",
849
         'bug_check_control_values_error_text',
850
         'createmissinggroupcontrolmapentries',
851
         'bug_check_control_values_repair_text');
852

853
BugCheck("bugs
854
         INNER JOIN group_control_map
855
            ON bugs.product_id = group_control_map.product_id
856 857 858 859 860
         INNER JOIN groups
            ON group_control_map.group_id = groups.id
          LEFT JOIN bug_group_map
            ON bugs.bug_id = bug_group_map.bug_id
           AND group_control_map.group_id = bug_group_map.group_id
861
         WHERE group_control_map.membercontrol = " . CONTROLMAPMANDATORY . "
862 863
           AND bug_group_map.group_id IS NULL
           AND groups.isactive != 0",
864
         'bug_check_control_values_error_text2');
865

866 867 868 869
###########################################################################
# Unsent mail
###########################################################################

870
Status('unsent_bugmail_check');
871

872
my $time = $dbh->sql_date_math('NOW()', '-', 30, 'MINUTE');
873 874 875 876
my $badbugs = $dbh->selectcol_arrayref(qq{
                    SELECT bug_id 
                      FROM bugs 
                     WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts)
877
                       AND delta_ts < $time
878
                  ORDER BY bug_id});
879 880


881
if (scalar(@$badbugs > 0)) {
882 883
    Status('unsent_bugmail_alert', {badbugs => $badbugs}, 'alert');
    Status('unsent_bugmail_fix');
884 885
}

886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909
###########################################################################
# Whines
###########################################################################

Status('whines_obsolete_target_start');

my $display_repair_whines_link = 0;
foreach my $target (['groups', 'id', MAILTO_GROUP],
                    ['profiles', 'userid', MAILTO_USER])
{
    my ($table, $col, $type) = @$target;
    my $old = $dbh->selectall_arrayref("SELECT whine_schedules.id, mailto
                                          FROM whine_schedules
                                     LEFT JOIN $table
                                            ON $table.$col = whine_schedules.mailto
                                         WHERE mailto_type = $type AND $table.$col IS NULL");

    if (scalar(@$old)) {
        Status('whines_obsolete_target_alert', {schedules => $old, type => $type}, 'alert');
        $display_repair_whines_link = 1;
    }
}
Status('whines_obsolete_target_fix') if $display_repair_whines_link;

910 911 912 913
###########################################################################
# Check hook
###########################################################################

914
Bugzilla::Hook::process('sanitycheck_check', { status => \&Status });
915

916 917 918
###########################################################################
# End
###########################################################################
919

920 921
Status('checks_completed');

922 923 924 925
unless (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) {
    $template->process('global/footer.html.tmpl', $vars)
      || ThrowTemplateError($template->error());
}