db.pm

bugzilla

    # bug.  This is so that I can easily sort and display a keywords
    # column in bug lists.

    if (!$dbh->bz_column_info('bugs', 'keywords')) {
        $dbh->bz_add_column('bugs', 'keywords',
            {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"});

        my @kwords;
        print "Making sure 'keywords' field of table 'bugs' is empty...\n";
        $dbh->do("UPDATE bugs SET keywords = '' WHERE keywords != ''");
        print "Repopulating 'keywords' field of table 'bugs'...\n";
        my $sth = $dbh->prepare("SELECT keywords.bug_id, keyworddefs.name " .
                                  "FROM keywords, keyworddefs " .
                                 "WHERE keyworddefs.id = keywords.keywordid " .
                              "ORDER BY keywords.bug_id, keyworddefs.name");
        $sth->execute;
        my @list;
        my $bugid = 0;
        my @row;
        while (1) {
            my ($b, $k) = ($sth->fetchrow_array());
            if (!defined $b || $b ne $bugid) {
                if (@list) {
                    $dbh->do("UPDATE bugs SET keywords = " .
                             $dbh->quote(join(', ', @list)) .
                             " WHERE bug_id = $bugid");
                }
                last if !$b;
                $bugid = $b;
                @list = ();
            }
            push(@list, $k);
        }
    }
}

# A helper for the function below.
sub _write_one_longdesc {
    my ($id, $who, $when, $buffer) = (@_);
    my $dbh = Bugzilla->dbh;
    $buffer = trim($buffer);
    return if !$buffer;
    $dbh->do("INSERT INTO longdescs (bug_id, who, bug_when, thetext)
                   VALUES (?,?,?,?)", undef, $id, $who, 
             time2str("%Y/%m/%d %H:%M:%S", $when), $buffer);
}

sub _populate_longdescs {
    my $dbh = Bugzilla->dbh;
    # 2000-01-20 Added a new "longdescs" table, which is supposed to have 
    # all the long descriptions in it, replacing the old long_desc field 
    # in the bugs table. The below hideous code populates this new table 
    # with things from the old field, with ugly parsing and heuristics.

    if ($dbh->bz_column_info('bugs', 'long_desc')) {
        my ($total) = $dbh->selectrow_array("SELECT COUNT(*) FROM bugs");

        print "Populating new long_desc table. This is slow. There are",
              " $total\nbugs to process; a line of dots will be printed",
              " for each 50.\n\n";
        local $| = 1;

        $dbh->bz_lock_tables('bugs write', 'longdescs write', 'profiles write',
                             'bz_schema WRITE');

        $dbh->do('DELETE FROM longdescs');

        my $sth = $dbh->prepare("SELECT bug_id, creation_ts, reporter,
                                        long_desc FROM bugs ORDER BY bug_id");
        $sth->execute();
        my $count = 0;
        while (my ($id, $createtime, $reporterid, $desc) = 
                   $sth->fetchrow_array()) 
        {
            $count++;
            indicate_progress({ total => $total, current => $count });
            $desc =~ s/\r//g;
            my $who = $reporterid;
            my $when = str2time($createtime);
            my $buffer = "";
            foreach my $line (split(/\n/, $desc)) {
                $line =~ s/\s+$//g; # Trim trailing whitespace.
                if ($line =~ /^------- Additional Comments From ([^\s]+)\s+(\d.+\d)\s+-------$/) 
                {
                    my $name = $1;
                    my $date = str2time($2);
                    # Oy, what a hack.  The creation time is accurate to the
                    # second. But the long text only contains things accurate
                    # to the And so, if someone makes a comment within a 
                    # minute of the original bug creation, then the comment can
                    # come *before* the bug creation.  So, we add 59 seconds to
                    # the time of all comments, so that they are always 
                    # considered to have happened at the *end* of the given
                    # minute, not the beginning.
                    $date += 59;
                    if ($date >= $when) {
                        _write_one_longdesc($id, $who, $when, $buffer);
                        $buffer = "";
                        $when = $date;
                        my $s2 = $dbh->prepare("SELECT userid FROM profiles " .
                                                "WHERE login_name = ?");
                        $s2->execute($name);
                        ($who) = ($s2->fetchrow_array());

                        if (!$who) {
                            # This username doesn't exist.  Maybe someone
                            # renamed him or something.  Invent a new profile
                            # entry disabled, just to represent him.
                            $dbh->do("INSERT INTO profiles (login_name, 
                                      cryptpassword, disabledtext) 
                                      VALUES (?,?,?)", undef, $name, '*',
                                      "Account created only to maintain"
                                      . " database integrity");
                            $who = $dbh->bz_last_key('profiles', 'userid');
                        }
                        next;
                    }
                }
                $buffer .= $line . "\n";
            }
            _write_one_longdesc($id, $who, $when, $buffer);
        } # while loop

        print "\n\n";
        $dbh->bz_drop_column('bugs', 'long_desc');
        $dbh->bz_unlock_tables();
    } # main if
}

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

    # 2000-01-18 Added a new table fielddefs that records information about the
    # different fields we keep an activity log on.  The bugs_activity table
    # now has a pointer into that table instead of recording the name directly.
    if ($dbh->bz_column_info('bugs_activity', 'field')) {
        $dbh->bz_add_column('bugs_activity', 'fieldid',
                            {TYPE => 'INT3', NOTNULL => 1}, 0);

        $dbh->bz_add_index('bugs_activity', 'bugs_activity_fieldid_idx',
                           [qw(fieldid)]);
        print "Populating new bugs_activity.fieldid field...\n";

        $dbh->bz_lock_tables('bugs_activity WRITE', 'fielddefs WRITE');


        my $ids = $dbh->selectall_arrayref(
            'SELECT DISTINCT fielddefs.id, bugs_activity.field
               FROM bugs_activity LEFT JOIN fielddefs 
                    ON bugs_activity.field = fielddefs.name', {Slice=>{}});

        foreach my $item (@$ids) {
            my $id    = $item->{id};
            my $field = $item->{field};
            # If the id is NULL
            if (!$id) {
                $dbh->do("INSERT INTO fielddefs (name, description) VALUES " .
                         "(?, ?)", undef, $field, $field);
                $id = $dbh->bz_last_key('fielddefs', 'id');
            }
            $dbh->do("UPDATE bugs_activity SET fieldid = ? WHERE field = ?",
                     undef, $id, $field);
        }
        $dbh->bz_unlock_tables();

        $dbh->bz_drop_column('bugs_activity', 'field');
    }
}

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

    # 2000-01-22 The "login_name" field in the "profiles" table was not
    # declared to be unique.  Sure enough, somehow, I got 22 duplicated entries
    # in my database.  This code detects that, cleans up the duplicates, and
    # then tweaks the table to declare the field to be unique.  What a pain.
    if (!$dbh->bz_index_info('profiles', 'profiles_login_name_idx')
        || !$dbh->bz_index_info('profiles', 'profiles_login_name_idx')->{TYPE})
    {
        print "Searching for duplicate entries in the profiles table...\n";
        while (1) {
            # This code is weird in that it loops around and keeps doing this
            # select again.  That's because I'm paranoid about deleting entries
            # out from under us in the profiles table.  Things get weird if
            # there are *three* or more entries for the same user...
            my $sth = $dbh->prepare("SELECT p1.userid, p2.userid, p1.login_name
                                       FROM profiles AS p1, profiles AS p2
                                      WHERE p1.userid < p2.userid
                                            AND p1.login_name = p2.login_name
                                   ORDER BY p1.login_name");
            $sth->execute();
            my ($u1, $u2, $n) = ($sth->fetchrow_array);
            last if !$u1;

            print "Both $u1 & $u2 are ids for $n!  Merging $u2 into $u1...\n";
            foreach my $i (["bugs", "reporter"],
                           ["bugs", "assigned_to"],
                           ["bugs", "qa_contact"],
                           ["attachments", "submitter_id"],
                           ["bugs_activity", "who"],
                           ["cc", "who"],
                           ["votes", "who"],
                           ["longdescs", "who"]) {
                my ($table, $field) = (@$i);
                print "   Updating $table.$field...\n";
                $dbh->do("UPDATE $table SET $field = $u1 " .
                          "WHERE $field = $u2");
            }
            $dbh->do("DELETE FROM profiles WHERE userid = $u2");
        }
        print "OK, changing index type to prevent duplicates in the",
              " future...\n";

        $dbh->bz_drop_index('profiles', 'profiles_login_name_idx');
        $dbh->bz_add_index('profiles', 'profiles_login_name_idx',
                           {TYPE => 'UNIQUE', FIELDS => [qw(login_name)]});
    }
}

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

    # components.initialowner
    my $comp_init_owner = $dbh->bz_column_info('components', 'initialowner');
    if ($comp_init_owner && $comp_init_owner->{TYPE} eq 'TINYTEXT') {
        my $sth = $dbh->prepare("SELECT program, value, initialowner
                                   FROM components");
        $sth->execute();
        while (my ($program, $value, $initialowner) = $sth->fetchrow_array()) {
            my ($id) = $dbh->selectrow_array(
                "SELECT userid FROM profiles WHERE login_name = ?",
                undef, $initialowner);

            unless (defined $id) {
                print "Warning: You have an invalid default assignee",
                      " '$initialowner'\n in component '$value' of program",
                      " '$program'!\n";
                $id = 0;
            }

            $dbh->do("UPDATE components SET initialowner = ?
                       WHERE program = ? AND value = ?", undef,
                     $id, $program, $value);
        }
        $dbh->bz_alter_column('components','initialowner',{TYPE => 'INT3'});
    }

    # components.initialqacontact
    my $comp_init_qa = $dbh->bz_column_info('components', 'initialqacontact');
    if ($comp_init_qa && $comp_init_qa->{TYPE} eq 'TINYTEXT') {
        my $sth = $dbh->prepare("SELECT program, value, initialqacontact
                                   FROM components");
        $sth->execute();
        while (my ($program, $value, $initialqacontact) = 
                   $sth->fetchrow_array()) 
        {
            my ($id) = $dbh->selectrow_array(
                "SELECT userid FROM profiles WHERE login_name = ?",
                undef, $initialqacontact);

            unless (defined $id) {
                if ($initialqacontact) {
                    print "Warning: You have an invalid default QA contact",
                          " $initialqacontact' in program '$program',",
                          " component '$value'!\n";
                }
                $id = 0;
            }

            $dbh->do("UPDATE components SET initialqacontact = ?
                       WHERE program = ? AND value = ?", undef,
                     $id, $program, $value);
        }

        $dbh->bz_alter_column('components','initialqacontact',{TYPE => 'INT3'});
    }
}

sub _populate_milestones_table {
    my $dbh = Bugzilla->dbh;
    # 2000-03-21 Adding a table for target milestones to
    # database - [e-mail]
    # If the milestones table is empty, and we're still back in a Bugzilla
    # that has a bugs.product field, that means that we just created
    # the milestones table and it needs to be populated.
    my $milestones_exist = $dbh->selectrow_array(
        "SELECT DISTINCT 1 FROM milestones");
    if (!$milestones_exist && $dbh->bz_column_info('bugs', 'product')) {
        print "Replacing blank milestones...\n";

        $dbh->do("UPDATE bugs
                     SET target_milestone = '---'
                   WHERE target_milestone = ' '");

        # If we are upgrading from 2.8 or earlier, we will have *created*
        # the milestones table with a product_id field, but Bugzilla expects
        # it to have a "product" field. So we change the field backward so
        # other code can run. The change will be reversed later in checksetup.
        if ($dbh->bz_column_info('milestones', 'product_id')) {
            # Dropping the column leaves us with a milestones_product_id_idx
            # index that is only on the "value" column. We need to drop the
            # whole index so that it can be correctly re-created later.