Attachment 305640 Details for Bug 431086 – clean up inconsistent data, reported by general_bug_check.orig file

clean up inconsistent data, reported by general_bug_check.orig file

cleanup_general_bug_check_data.pl (text/plain), 5.13 KB, created by Tony Fu on 2008-05-16 06:42:52 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Tony Fu

Created: 2008-05-16 06:42:52 UTC

Size: 5.13 KB

patch

obsolete

>#!/usr/bin/env perl
>use strict;
>use warnings;
>
>################################################################################
># script:   cleanup_general_bug_check.pl
># purpose:  sanitycheck reported some inconsistent data by general_bug_check
>#           This script cleans up these inconsistent data.
>################################################################################
>use DBI;
>use Carp;
>
>###############################################
>#   variables of accessing pg db
>###############################################
>
>my $user = 'bugs';
>
>my $pass = '';
>my $host = 'localhost';
>
>my $dsn = "dbi:mysql:dbname=bugs;host=$host";
>
>my $dbh
>    = DBI->connect( $dsn, $user, $pass, { RaiseError => 1, PrintError => 0 } )
>    or croak "Couldn't connect database: " . DBI->errstr;
>
># bug_activity prepartion 1:
># get Admin User profiles.userid.
>my $login_name    = $dbh->quote('bugzilla@redhat.com');
>my $admin_user_id = $dbh->selectall_arrayref(
>    "SELECT userid 
>        FROM profiles 
>       WHERE login_name = $login_name"
>)->[0][0];
>
># bug_activity prepartion 2:
># get resolution's fieldid in fielddefs table
>my $field_id_resolution = $dbh->selectall_arrayref(
>    "SELECT fieldid 
>       FROM fielddefs 
>      WHERE name='resolution'"
>)->[0][0];
>
>#########################################################
>#   data clean up activity 1:
>#       some bugs with open status and a resolution.
>#       for those bugs, clear the resolution field
>#       (change it to default value '')
>##########################################################
>my $resolution_default_value = '';
>my $no_resolution            = $dbh->quote($resolution_default_value);
>my $open_status
>    = "'NEW', 'REOPENED', 'ASSIGNED', 'NEEDINFO', 'NEEDINFO_REPORTER',
>            'MODIFIED', 'ON_DEV', 'VERIFIED',
>            'ON_QA', 'FAILS_QA', 'RELEASE_PENDING',
>            'POST'";
>my $check_sql_query = "SELECT bug_id, resolution FROM bugs"
>    . " WHERE bug_status IN ($open_status)"
>    . " AND (resolution !='' AND resolution IS NOT NULL)";
>
>my $sth = $dbh->prepare($check_sql_query);
>$sth->execute();
>while ( my ( $bug_id, $resolution ) = $sth->fetchrow_array() ) {
>    $dbh->do(
>        "UPDATE bugs SET resolution=$no_resolution WHERE bug_id=$bug_id");
>    print "resolution of open bug $bug_id has been cleared\n";
>
>    #insert an entry into bugs_activity table
>    update_bugs_activity_table( $admin_user_id, $field_id_resolution,
>        $resolution_default_value, $resolution, $bug_id );
>
>}
>
>#########################################################
>#   data clean up activity 2:
>#       some bugs with non-open status and without a resolution
>#       for those bugs, set resolution field as 'CURRENTRELEASE'
>#       (change it to default value '')
>##########################################################
>my $new_resolution_value = 'CURRENTRELEASE';
>my $new_resolution       = $dbh->quote($new_resolution_value);
>
>$check_sql_query = "SELECT bug_id, bug_status FROM bugs"
>    . " WHERE bug_status NOT IN ($open_status)"
>    . " AND (resolution ='' OR resolution IS NULL)";
>
>$sth = $dbh->prepare($check_sql_query);
>$sth->execute();
>while ( my ( $bug_id, $resolution ) = $sth->fetchrow_array() ) {
>    $dbh->do(
>        "UPDATE bugs SET resolution=$new_resolution WHERE bug_id=$bug_id");
>    print
>        "the resolution of bug $bug_id has been changed to CURRENTRELEASE\n";
>
>    #insert an entry into bugs_activity table
>    update_bugs_activity_table( $admin_user_id, $field_id_resolution,
>        $new_resolution_value, $resolution, $bug_id );
>
>}
>
>#########################################################
>#   data clean up activity 3:
>#    some bugs found on duplicates table that are not
>#    marked duplicate in bugs table
>#    delete these bugs from duplicates table
>##########################################################
>$check_sql_query = "SELECT bug_id FROM bugs"
>    . " LEFT JOIN duplicates ON bugs.bug_id=duplicates.dupe"
>    . " WHERE bugs.resolution != 'DUPLICATE'"
>    . " AND duplicates.dupe IS NOT NULL";
>
>$sth = $dbh->prepare($check_sql_query);
>$sth->execute();
>while ( my $bug_id = $sth->fetchrow_array() ) {
>    $dbh->do("DELETE FROM duplicates WHERE dupe=$bug_id");
>    print "bug $bug_id has been removed from duplicates table\n";
>}
>
>#########################################################
>#   TODO: data clean up activity 4:
>#    some bugs found on duplicates table that are not
>#    marked duplicate in bugs table
>#    delete these bugs from duplicates table
>#########################################################
>
>sub update_bugs_activity_table {
>    my ( $admin_user_id, $field_id, $added_value, $removed_value, $bug_id )
>        = @_;
>    my $added   = $dbh->quote($added_value);
>    my $removed = $dbh->quote($removed_value);
>    $dbh->do(
>        "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id, 
>                       $added,
>                       $removed,
>                       $bug_id )"
>    );
>}
>

Flags:

dkl: review+

Actions: View

Attachments on bug 431086: 302838 | 303334 | 303599 | 304295 | 305333 | 305640 | 313167 | 324678