Attachment 304295 Details for Bug 431086 – inconsistent data cleanup

inconsistent data cleanup

cleanup_inconsistent_data.pl (text/plain), 17.55 KB, created by Tony Fu on 2008-05-01 06:17:11 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Tony Fu

Created: 2008-05-01 06:17:11 UTC

Size: 17.55 KB

patch

obsolete

>#!/usr/bin/env perl
>use strict;
>use warnings;
>
>################################################################################
># script:   cleanup_inconsistent_data.pl
># purpose:  sanitycheck reported some inconsistent data when checking
>#           foreign keys.
>#           This script cleans up these inconsistent data.
>#           Refer to bug #431086 for details.
>################################################################################
>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 version's fieldid in fielddefs table
>my $field_id_version = $dbh->selectall_arrayref(
>    "SELECT fieldid 
>       FROM fielddefs 
>      WHERE name='version'"
>)->[0][0];
>
># bug_activity prepartion 3:
># get resolution's fieldid in fielddefs table
>my $field_id_resolution = $dbh->selectall_arrayref(
>    "SELECT fieldid 
>       FROM fielddefs 
>      WHERE name='resolution'"
>)->[0][0];
>
># bug_activity prepartion 4:
># get rep_platform's fieldid in fielddefs table
>my $field_id_rep_platform = $dbh->selectall_arrayref(
>    "SELECT fieldid 
>       FROM fielddefs 
>      WHERE name='rep_platform'"
>)->[0][0];
>
>#########################################################
>#   data clean up activity #1:
>#       change bugs.assigned_to to bugzilla@redhat.com if
>#       its current value is an invalid value of
>#       profiles.userid
>##########################################################
>my $updated_record_number
>    = change_invalid_user_id( "bugs", "bug_id", "assigned_to",
>    $admin_user_id );
>print "$updated_record_number invalid assigned_to values have been changed\n";
>
>#########################################################
>#   data clean up activity #2:
>#       change bugs.qa_contact to null if
>#       its current value is an invalid value of
>#       profiles.userid
>##########################################################
>$updated_record_number
>    = change_invalid_user_id( "bugs", "bug_id", "qa_contact", "NULL" );
>print "$updated_record_number invalid qa_contact values have been changed\n";
>
>#########################################################
>#   data clean up activity #3:
>#       change attachments.submitter to bugzilla@redhat.com if
>#       its current value is an invalid value of
>#       profiles.userid
>##########################################################
>
>$updated_record_number
>    = change_invalid_user_id( "attachments", "attach_id", "submitter_id",
>    $admin_user_id );
>print "$updated_record_number invalid submitter values have been changed\n";
>
>#########################################################
>#   data clean up activity #4:
>#       change bugs_activity.who to bugzilla@redhat.com if
>#       its current value is an invalid value of
>#       profiles.userid
>##########################################################
>$updated_record_number
>    = change_invalid_user_id( "bugs_activity", "bug_id", "who",
>    $admin_user_id );
>print
>    "$updated_record_number invalid bugs_activity.who values have been changed\n";
>
>#########################################################
>#   data clean up activity #5:
>#       delete cc.who rows with invalid profiles.userid
>#       value
>##########################################################
>$dbh->do(
>    "DELETE FROM cc WHERE who IN (23, 287, 1014, 1831, 2791, 7963, 7965, 8117, 10172, 12963, 16840, 17808, 34412, 41639, 80221, 85801, 87800, 88029, 105329, 130686, 159676, 160592,
>160812, 162584, 164666, 166720, 182439)"
>);
>print "rows with invalid cc.who value have been deteled\n";
>
>#########################################################
>#   data clean up activity #6:
>#       change longdescs.who to bugzilla@redhat.com if
>#       its current value is an invalid value of
>#       profiles.userid
>##########################################################
>$updated_record_number
>    = change_invalid_user_id( "longdescs", "bug_id", "who", $admin_user_id );
>print
>    "$updated_record_number invalid longdescs.who values have been changed\n";
>
>#########################################################
>#   data clean up activity #7:
>#       delete namedqueries.userid rows with invalid
>#       user_id
>#########################################################
>$dbh->do(
>    "DELETE FROM namedqueries WHERE userid IN (0, 23, 202, 1014, 1763, 11616, 16840, 17808, 33725, 101155)"
>);
>print "records with invalid namedqueries.userid have been deleted\n";
>
>#########################################################
>#   data clean up activity #8:
>#       delete rows with invalid profiles_activity.userid
>#       value
>##########################################################
>$dbh->do(
>    "DELETE FROM profiles_activity WHERE userid IN (105329, 156511, 160812, 161845, 182439, 211658)"
>);
>print "records with invalid profiles_activity.userid have been deleted\n";
>
>#########################################################
>#   data clean up activity #9:
>#       delete rows with series.creator=0
>##########################################################
>$dbh->do(
>    "DELETE FROM series_data WHERE series_id IN (SELECT series_id FROM series WHERE creator=0)"
>);
>$dbh->do("DELETE FROM series WHERE creator=0");
>print "the series record that has creator of 0 has been deleted\n";
>
>#########################################################
>#   data clean up activity #10:
>#       delete rows with watch.watcher=165722
>##########################################################
>$dbh->do("DELETE FROM watch WHERE watcher = 165722");
>print "the watch record that has watcher of 165722 has been deleted\n";
>
>#########################################################
>#   data clean up activity #11:
>#       change components.initialqacontact to null if
>#       its current value is an invalid value of
>#       profiles.userid
>##########################################################
>$updated_record_number
>    = change_invalid_user_id( "components", "id", "initialqacontact",
>    "NULL" );
>print
>    "$updated_record_number invalid initialqacontact values have been changed\n";
>
>#########################################################
>#   data clean up activity #12:
>#       delete rows with bad user_group_map.user_id value
>##########################################################
>$dbh->do(
>    "DELETE FROM user_group_map WHERE user_id IN (156511, 147463, 155642, 159676, 161845, 20998, 25991, 48771, 51175, 52865, 54988, 62727, 67605, 105805, 107014, 16840, 30050, 37703,
>45879, 46333, 46370, 49303, 49312, 49313, 49314, 49315, 130686)"
>);
>print "the row that has bad user_group_map.user_id value has been deleted\n";
>
>#########################################################
>#   data clean up activity #13:
>#       delete rows with product_id is 59
>##########################################################
>$dbh->do("DELETE FROM milestones WHERE product_id = 59");
>$dbh->do("DELETE FROM versions WHERE product_id = 59");
>$dbh->do("DELETE FROM group_control_map WHERE product_id = 59");
>$dbh->do("DELETE FROM flaginclusions WHERE product_id = 59");
>print "records with invalid product_id (59) have been deleted\n";
>
>#########################################################
>#   data clean up activity #14:
>#       change resolution value '' to NULL if status is
>#       not "CLOSED";
>#       change it to 'CURRENTLEASE' if the status is 'CLOSED'
>##########################################################
>$dbh->do(
>    "UPDATE bugs SET resolution=NULL
>           WHERE resolution=''
>           AND bug_status!='CLOSED'"
>);
>my $sth = $dbh->prepare(
>    "SELECT bug_id FROM bugs 
>                WHERE resolution='' AND bug_status='CLOSED'"
>);
>$sth->execute();
>my $counter = 0;
>while ( my $bug_id = $sth->fetchrow_array() ) {
>    $dbh->do(
>        " UPDATE bugs SET resolution='CURRENTRELEASE'
>               WHERE bug_id=$bug_id"
>    );
>
>    #insert an entry into bugs_activity table
>    $dbh->do(
>        "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id_version, 
>                       'CURRENTRELEASE',
>                       '',
>                       $bug_id )"
>    );
>    $counter++;
>}
>print
>    "$counter records with invalid bugs.resolution value('') have been changed to CURRENTRELEASE\n";
>
>#########################################################
>#   data clean up activity #15:
>#       delete record that has invalid components.id(16465)
>#       in  flaginclusions table
>##########################################################
>$dbh->do("DELETE FROM flaginclusions WHERE component_id=16465");
>print "bad component_id 16465 has been delete from flaginclusions table\n";
>
>#########################################################
>#   data clean up activity #16:
>#       delete record that has invalid bug_id
>#       (26178, 36326, 43632) from bugs_activity table
>##########################################################
>$dbh->do(
>    "DELETE FROM bugs_activity WHERE bug_id
>           IN (26178, 36326, 43632)"
>);
>print "bad bug_ids have been delete from bugs_activity table\n";
>
>#########################################################
>#   data clean up activity #17:
>#       delete record that has invalid bug_id
>#       from bug_group_map table
>##########################################################
>$updated_record_number = del_invalid_bug_id("bug_group_map");
>print
>    "$updated_record_number bug_group_map records with invalid bug_id have been deleted\n";
>
>#########################################################
>#   data clean up activity #18:
>#       delete record that has invalid bug_id
>#       from long_desces table
>##########################################################
>$updated_record_number = del_invalid_bug_id("longdescs");
>print
>    "$updated_record_number long_descs records with invalid bug_id have been deleted\n";
>
>#########################################################
>#   data clean up activity #19:
>#       change bugs.rep_platform to "i386" if its current
>#       value is "PC", which is not a valid value any more
>##########################################################
>$dbh->do(" UPDATE bugs SET rep_platform='i386' WHERE rep_platform='PC' ");
>$sth = $dbh->prepare(
>    "SELECT bug_id FROM bugs 
>                WHERE rep_platform='PC'"
>);
>$sth->execute();
>$counter = 0;
>while ( my $bug_id = $sth->fetchrow_array() ) {
>    $dbh->do(
>        " UPDATE bugs SET rep_platform='i386'
>               WHERE bug_id=$bug_id"
>    );
>
>    #insert an entry into bugs_activity table
>    $dbh->do(
>        "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id_rep_platform, 
>                       'i386',
>                       'PC',
>                       $bug_id )"
>    );
>    $counter++;
>}
>print "$counter bugs.rep_platform values have been changed from PC to i386\n";
>
>#########################################################
>#   data clean up activity #20:
>#       delete records with invalid attact_id from
>#       bugs_activity table
>##########################################################
>$dbh->do(
>    " DELETE FROM bugs_activity WHERE attach_id IN
>           (93000, 94077, 96520, 96899, 123296, 154253)"
>);
>print "records with invalid attach_id have been deleted from 
>       bugs_activity table\n";
>
>#########################################################
>#   data clean up activity #21:
>#       change record that has fieldid 15 to fieldid 41
>##########################################################
>$dbh->do(" UPDATE bugs_activity SET fieldid = 41 WHERE fieldid = 15");
>print "bugs_activity.fieldid has been changed from 15 to 41\n";
>
>#########################################################
>#   data clean up activity #22:
>#       change bugs' product_id and version to 17, "6.0"
>#       if current values are 17, "1.0"
>##########################################################
>$sth = $dbh->prepare(
>    "SELECT bug_id FROM bugs 
>                WHERE product_id=17 AND version='1.0'"
>);
>$sth->execute();
>while ( my $bug_id = $sth->fetchrow_array() ) {
>    $dbh->do(
>        " UPDATE bugs SET version='6.0'
>               WHERE bug_id=$bug_id"
>    );
>
>    #insert an entry into bugs_activity table
>    $dbh->do(
>        "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id_version, 
>                       '6.0',
>                       '1.0',
>                       $bug_id )"
>    );
>}
>print
>    "invalid product_id and version combination(17, '1.0') has been corrected.\n";
>
>#########################################################
>#   data clean up activity #23:
>#       change bugs' product_id and version to 39, "none"
>#       if current values are 39, ""
>##########################################################
>$sth = $dbh->prepare(
>    "SELECT bug_id FROM bugs 
>                WHERE product_id=39 AND version=''"
>);
>$sth->execute();
>while ( my $bug_id = $sth->fetchrow_array() ) {
>    $dbh->do(
>        " UPDATE bugs SET version='None'
>               WHERE bug_id=$bug_id"
>    );
>
>    #insert an entry into bugs_activity table
>    $dbh->do(
>        "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id_version, 
>                       'None',
>                       '',
>                       $bug_id )"
>    );
>}
>print
>    "invalid product_id and version combination(39,'') has been corrected.\n";
>
>#########################################################
>#   data clean up activity #24:
>#	delete these records that have 115 as group_id from
>#	user_group_map table
>##########################################################
>$dbh->do(
>    "DELETE FROM user_group_map
>	  WHERE group_id=115"
>);
>print "bad group_id values have been deleted from user_group_map table.\n";
>##################################################################################
>#   two database related functions
>##################################################################################
>sub del_invalid_bug_id {
>    my $table = shift;
>    my $invalid_bug_query
>        = "SELECT $table.bug_id FROM $table LEFT JOIN bugs ON"
>        . " $table.bug_id=bugs.bug_id"
>        . " WHERE bugs.bug_id IS NULL"
>        . " ORDER BY $table.bug_id";
>
>    my $sth = $dbh->prepare($invalid_bug_query);
>    $sth->execute();
>    my $counter = 0;
>    while ( my $invalid_bug_id = $sth->fetchrow_array() ) {
>        $dbh->do("DELETE from $table WHERE bug_id=$invalid_bug_id");
>        $counter++;
>    }
>    return $counter;
>}
>
>sub change_invalid_user_id {
>
>    my ( $table, $primary_key, $column, $new_value ) = @_;
>
>    my $primary_key_query
>        = "SELECT $table.$primary_key FROM $table LEFT JOIN profiles ON"
>        . " $table.$column = profiles.userid"
>        . " WHERE profiles.userid IS NULL"
>        . " AND $table.$column IS NOT NULL"
>        . "  ORDER BY $table.$primary_key";
>    my $update_query = "UPDATE $table SET $column=$new_value";
>    my $sth          = $dbh->prepare($primary_key_query);
>    $sth->execute();
>
>    my $counter = 0;
>    my $field_id;
>    my $new_user_login;
>
>    if ( ( $table eq 'bugs' ) && ( $new_value ne 'NULL' ) ) {
>
>        #prepare bugs_activity table update
>        #get column's fieldid
>        $field_id = $dbh->selectall_arrayref(
>            "SELECT fieldid 
>                                    FROM fielddefs 
>                                    WHERE name='$column'"
>        )->[0][0];
>        $new_user_login = $dbh->quote('bugzilla@redhat.com');
>    }
>
>    while ( my $primary_key_id = $sth->fetchrow_array() ) {
>        my $update_user_id_query
>            = $update_query . " WHERE $primary_key = $primary_key_id";
>        $dbh->do($update_user_id_query);
>        if ( ( $table eq 'bugs' ) && ( $new_value ne 'NULL' ) ) {
>
>            #update bugs_activity table
>            $dbh->do(
>                "INSERT INTO bugs_activity (who, 
>                                    bug_when, 
>                                    fieldid, 
>                                    added, 
>                                    removed,
>                                    bug_id) 
>              VALUES ( $admin_user_id, 
>                       NOW(), 
>                       $field_id, 
>                       $new_user_login,
>                       'unknown',
>                       $primary_key_id )"
>            );
>        }
>        $counter++;
>    }
>
>    return $counter;
>}
>

Actions: View

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