db.pm

bugzilla

            $dbh->bz_drop_index('milestones', 'milestones_product_id_idx');
            $dbh->bz_drop_column('milestones', 'product_id');
            $dbh->bz_add_column('milestones', 'product',
                                {TYPE => 'varchar(64)', NOTNULL => 1}, '');
        }

        # Populate the milestone table with all existing values in the database
        my $sth = $dbh->prepare("SELECT DISTINCT target_milestone, product 
                                   FROM bugs");
        $sth->execute();

        print "Populating milestones table...\n";

        while (my ($value, $product) = $sth->fetchrow_array()) {
            # check if the value already exists
            my $sortkey = substr($value, 1);
            if ($sortkey !~ /^\d+$/) {
                $sortkey = 0;
            } else {
                $sortkey *= 10;
            }
            my $ms_exists = $dbh->selectrow_array(
                "SELECT value FROM milestones
                  WHERE value = ? AND product = ?", undef, $value, $product);

            if (!$ms_exists) {
                $dbh->do("INSERT INTO milestones(value, product, sortkey) 
                          VALUES (?,?,?)", undef, $value, $product, $sortkey);
            }
        }
    }
}

sub _add_products_defaultmilestone {
    my $dbh = Bugzilla->dbh;

    # 2000-03-23 Added a defaultmilestone field to the products table, so that
    # we know which milestone to initially assign bugs to.
    if (!$dbh->bz_column_info('products', 'defaultmilestone')) {
        $dbh->bz_add_column('products', 'defaultmilestone',
                 {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"});
        my $sth = $dbh->prepare(
            "SELECT product, defaultmilestone FROM products");
        $sth->execute();
        while (my ($product, $default_ms) = $sth->fetchrow_array()) {
            my $exists = $dbh->selectrow_array(
                "SELECT value FROM milestones
                  WHERE value = ? AND product = ?", 
                undef, $default_ms, $product);
            if (!$exists) {
                $dbh->do("INSERT INTO milestones(value, product) " .
                         "VALUES (?, ?)", undef, $default_ms, $product);
            }
        }
    }
}

sub _copy_from_comments_to_longdescs {
    my $dbh = Bugzilla->dbh;
    # 2000-11-27 For Bugzilla 2.5 and later. Copy data from 'comments' to
    # 'longdescs' - the new name of the comments table.
    if ($dbh->bz_table_info('comments')) {
        my $quoted_when = $dbh->quote_identifier('when');
        $dbh->do("INSERT INTO longdescs (bug_when, bug_id, who, thetext)
                  SELECT $quoted_when, bug_id, who, comment
                    FROM comments");
        $dbh->bz_drop_table("comments");
    }
}

sub _populate_duplicates_table {
    my $dbh = Bugzilla->dbh;
    # 2000-07-15 Added duplicates table so Bugzilla tracks duplicates in a
    # better way than it used to. This code searches the comments to populate
    # the table initially. It's executed if the table is empty; if it's 
    # empty because there are no dupes (as opposed to having just created 
    # the table) it won't have any effect anyway, so it doesn't matter.
    my ($dups_exist) = $dbh->selectrow_array(
        "SELECT DISTINCT 1 FROM duplicates");
    # We also check against a schema change that happened later.
    if (!$dups_exist && !$dbh->bz_column_info('groups', 'isactive')) {
        # populate table
        print "Populating duplicates table from comments...\n";

        my $sth = $dbh->prepare(
             "SELECT longdescs.bug_id, thetext
                FROM longdescs LEFT JOIN bugs 
                     ON longdescs.bug_id = bugs.bug_id
               WHERE (" . $dbh->sql_regexp("thetext", 
                          "'[.*.]{3} This bug has been marked as a duplicate"
                          . " of [[:digit:]]+ [.*.]{3}'") 
                  . ")
                     AND resolution = 'DUPLICATE'
            ORDER BY longdescs.bug_when");
        $sth->execute();

        my (%dupes, $key);
        # Because of the way hashes work, this loop removes all but the 
        # last dupe resolution found for a given bug.
        while (my ($dupe, $dupe_of) = $sth->fetchrow_array()) {
            $dupes{$dupe} = $dupe_of;
        }

        foreach $key (keys(%dupes)){
            $dupes{$key} =~ /^.*\*\*\* This bug has been marked as a duplicate of (\d+) \*\*\*$/ms;
            $dupes{$key} = $1;
            $dbh->do("INSERT INTO duplicates VALUES(?, ?)", undef,
                     $dupes{$key},  $key);
            #        BugItsADupeOf  Dupe
        }
    }
}

sub _recrypt_plaintext_passwords {
    my $dbh = Bugzilla->dbh;
    # 2001-06-12; [e-mail]; bugs 74032, 77473:
    # Recrypt passwords using Perl &crypt instead of the mysql equivalent
    # and delete plaintext passwords from the database.
    if ($dbh->bz_column_info('profiles', 'password')) {

        print <<ENDTEXT;
Your current installation of Bugzilla stores passwords in plaintext
in the database and uses mysql's encrypt function instead of Perl's
crypt function to crypt passwords.  Passwords are now going to be
re-crypted with the Perl function, and plaintext passwords will be
deleted from the database.  This could take a while if your
installation has many users.
ENDTEXT

        # Re-crypt everyone's password.
        my $sth = $dbh->prepare("SELECT userid, password FROM profiles");
        $sth->execute();

        my $total = $sth->rows;
        my $i = 1;

        print "Fixing passwords...\n";
        while (my ($userid, $password) = $sth->fetchrow_array()) {
            my $cryptpassword = $dbh->quote(bz_crypt($password));
            $dbh->do("UPDATE profiles " .
                        "SET cryptpassword = $cryptpassword " .
                      "WHERE userid = $userid");
            indicate_progress({ total => $total, current => $i, every => 10 });
        }
        print "\n";

        # Drop the plaintext password field.
        $dbh->bz_drop_column('profiles', 'password');
    }
}

sub _update_bugs_activity_to_only_record_changes {
    my $dbh = Bugzilla->dbh;
    # 2001-07-20 [e-mail] - Change bugs_activity to only record changes
    #  http://bugzilla.mozilla.org/show_bug.cgi?id=55161
    if ($dbh->bz_column_info('bugs_activity', 'oldvalue')) {
        $dbh->bz_add_column("bugs_activity", "removed", {TYPE => "TINYTEXT"});
        $dbh->bz_add_column("bugs_activity", "added", {TYPE => "TINYTEXT"});

        # Need to get field id's for the fields that have multiple values
        my @multi;
        foreach my $f ("cc", "dependson", "blocked", "keywords") {
            my $sth = $dbh->prepare("SELECT id " .
                                    "FROM fielddefs " .
                                    "WHERE name = '$f'");
            $sth->execute();
            my ($fid) = $sth->fetchrow_array();
            push (@multi, $fid);
        }

        # Now we need to process the bugs_activity table and reformat the data
        print "Fixing activity log...\n";
        my $sth = $dbh->prepare("SELECT bug_id, who, bug_when, fieldid,
                                oldvalue, newvalue FROM bugs_activity");
        $sth->execute;
        my $i = 0;
        my $total = $sth->rows;
        while (my ($bug_id, $who, $bug_when, $fieldid, $oldvalue, $newvalue) 
                   = $sth->fetchrow_array()) 
        {
            $i++;
            indicate_progress({ total => $total, current => $i, every => 10 });
            # Make sure (old|new)value isn't null (to suppress warnings)
            $oldvalue ||= "";
            $newvalue ||= "";
            my ($added, $removed) = "";
            if (grep ($_ eq $fieldid, @multi)) {
                $oldvalue =~ s/[\s,]+/ /g;
                $newvalue =~ s/[\s,]+/ /g;
                my @old = split(" ", $oldvalue);
                my @new = split(" ", $newvalue);
                my (@add, @remove) = ();
                # Find values that were "added"
                foreach my $value(@new) {
                    if (! grep ($_ eq $value, @old)) {
                        push (@add, $value);
                    }
                }
                # Find values that were removed
                foreach my $value(@old) {
                    if (! grep ($_ eq $value, @new)) {
                        push (@remove, $value);
                    }
                }
                $added = join (", ", @add);
                $removed = join (", ", @remove);
                # If we can't determine what changed, put a ? in both fields
                unless ($added || $removed) {
                    $added = "?";
                    $removed = "?";
                }
                # If the original field (old|new)value was full, then this
                # could be incomplete data.
                if (length($oldvalue) == 255 || length($newvalue) == 255) {
                    $added = "? $added";
                    $removed = "? $removed";
                }
            } else {
                $removed = $oldvalue;
                $added = $newvalue;
            }
            $added = $dbh->quote($added);
            $removed = $dbh->quote($removed);
            $dbh->do("UPDATE bugs_activity 
                         SET removed = $removed, added = $added
                       WHERE bug_id = $bug_id AND who = $who
                             AND bug_when = '$bug_when' 
                             AND fieldid = $fieldid");
        }
        print "\n";
        $dbh->bz_drop_column("bugs_activity", "oldvalue");
        $dbh->bz_drop_column("bugs_activity", "newvalue");
    }
}

sub _delete_logincookies_cryptpassword_and_handle_invalid_cookies {
    my $dbh = Bugzilla->dbh;
    # 2002-02-04 [e-mail] bug 95732
    # Remove logincookies.cryptpassword, and delete entries which become
    # invalid
    if ($dbh->bz_column_info("logincookies", "cryptpassword")) {
        # We need to delete any cookies which are invalid before dropping the
        # column
        print "Removing invalid login cookies...\n";

        # mysql doesn't support DELETE with multi-table queries, so we have
        # to iterate
        my $sth = $dbh->prepare("SELECT cookie FROM logincookies, profiles " .
                                "WHERE logincookies.cryptpassword != " .
                                "profiles.cryptpassword AND " .
                                "logincookies.userid = profiles.userid");
        $sth->execute();
        while (my ($cookie) = $sth->fetchrow_array()) {
            $dbh->do("DELETE FROM logincookies WHERE cookie = $cookie");
        }

        $dbh->bz_drop_column("logincookies", "cryptpassword");
    }
}

sub _use_ip_instead_of_hostname_in_logincookies {
    my $dbh = Bugzilla->dbh;

    # 2002-03-15 [e-mail] - bug 129466
    # 2002-05-13 [e-mail] - bug 129446 patch backported to the
    #  BUGZILLA-2_14_1-BRANCH as a security blocker for the 2.14.2 release
    #
    # Use the ip, not the hostname, in the logincookies table
    if ($dbh->bz_column_info("logincookies", "hostname")) {
        # We've changed what we match against, so all entries are now invalid
        $dbh->do("DELETE FROM logincookies");

        # Now update the logincookies schema
        $dbh->bz_drop_column("logincookies", "hostname");
        $dbh->bz_add_column("logincookies", "ipaddr",
                            {TYPE => 'varchar(40)', NOTNULL => 1}, '');
    }
}

sub _move_quips_into_db {
    my $dbh = Bugzilla->dbh;
    my $datadir = bz_locations->{'datadir'};
    # 2002-07-15 [e-mail] - bug 67950
    # Move quips to the db.
    if (-e "$datadir/comments") {
        print "Populating quips table from $datadir/comments...\n";
        my $comments = new IO::File("$datadir/comments", 'r')
            || die "$datadir/comments: $!";
        $comments->untaint;
        while (<$comments>) {
            chomp;
            $dbh->do("INSERT INTO quips (quip) VALUES (?)", undef, $_);
        }

        print <<EOT;

Quips are now stored in the database, rather than in an external file.
The quips previously stored in $datadir/comments have been copied into
the database, and that file has been renamed to $datadir/comments.bak
You may delete the renamed file once you have confirmed that all your
quips were moved successfully.