Attachment 305333 Details for Bug 431086 – delete invalid logins from profiles table

delete invalid logins from profiles table

cleanup_invalid_login.pl (text/plain), 4.13 KB, created by Tony Fu on 2008-05-14 06:15:24 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Tony Fu

Created: 2008-05-14 06:15:24 UTC

Size: 4.13 KB

patch

obsolete

>#!/usr/bin/env perl
>use strict;
>use warnings;
>
>################################################################################
># script:   cleanup_invalid_login.pl
># purpose:  sanitycheck reported some user's login names are not invalid email
>#           addresses.
>#           This script cleans up these invalid user's logins.
>################################################################################
>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;
>
>#########################################################
>#   data clean up activity :
>#       some logins are not valid email addresses.  Delete
>#       these logins if they are used in other tables.
>##########################################################
>my %invalid_login = check_invalid_login();
>while ( my ($user_id, $login_name) = each ( %invalid_login) ) {
>    my ( $user_in_other_table, $table_name, $field )
>        = check_userid_used_by_other_tables($user_id);
>    if ($user_in_other_table) {
>        print
>            "User $login_name (userid:$user_id) is used in $table_name.$field.  Can't delete $login_name from profiles table\n";
>    }
>    else { 
>        print "Delete user $login_name from profiles table\n";
>        $dbh->do( "DELETE FROM profiles WHERE userid=$user_id" ); }
>}
>
>sub check_invalid_login {
>    my %user_with_bad_login_name_list;
>    my $emailregexp = '^[\\w\\.\\+\\-=]+@[\\w\\.\\-]+\\.[\\w\\-]+$';
>
>    my $sth = $dbh->prepare("SELECT login_name, userid  FROM profiles");
>    $sth->execute();
>    while ( my ( $login_name, $userid ) = $sth->fetchrow_array() ) {
>        unless ( $login_name =~ m/$emailregexp/ ) {
>            $user_with_bad_login_name_list{$userid}=$login_name;
>        }
>    }
>    return (%user_with_bad_login_name_list);
>}
>
>sub check_userid_used_by_other_tables {
>    my $user_id               = shift;
>    my @userid_as_foreign_key = (
>        [ "bugs",              "reporter",         "bug_id" ],
>        [ "bugs",              "assigned_to",      "bug_id" ],
>        [ "bugs",              "qa_contact",       "bug_id" ],
>        [ "attachments",       "submitter_id",     "bug_id" ],
>        [ "bugs_activity",     "who",              "bug_id" ],
>        [ "cc",                "who",              "bug_id" ],
>        [ "flags",             "setter_id",        "id" ],
>        [ "flags",             "requestee_id",     "id" ],
>        [ "votes",             "who",              "bug_id" ],
>        [ "longdescs",         "who",              "bug_id" ],
>        [ "logincookies",      "userid",           "cookie" ],
>        [ "namedqueries",      "userid",           "name" ],
>        [ "profiles_activity", "userid",           "userid" ],
>        [ "profiles_activity", "who",              "userid" ],
>        [ "quips",             "userid",           "quip" ],
>        [ "series",            "creator",          "name" ],
>        [ "watch",             "watcher",          "watched" ],
>        [ "watch",             "watched",          "watcher" ],
>        [ "tokens",            "userid",           "token" ],
>        [ "components",        "initialowner",     "name" ],
>        [ "components",        "initialqacontact", "name" ],
>        [ "component_cc",      "user_id",          "component_id" ],
>        [ "whine_events",      "owner_userid",     "id" ],
>        [ "user_group_map",    "user_id",          "group_id" ],
>        [ "whine_schedules",   "mailto",           "id" ]
>    );
>    foreach (@userid_as_foreign_key) {
>        my ( $refertable, $referfield, $keyname ) = @$_;
>        my $user_number = $dbh->selectall_arrayref(
>            "SELECT COUNT($keyname) 
>       FROM $refertable 
>      WHERE $referfield=$user_id"
>        )->[0][0];
>        if ($user_number) {
>            my $user_in_other_table = 1;
>            return ( $user_in_other_table, $refertable, $referfield );
>        }
>    }
>    return ( 0, undef, undef );
>}
>

Flags:

dkl: review+

Actions: View

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