db.pm

bugzilla

# -*- Mode: perl; indent-tabs-mode: nil -*-
#
# The contents of this file are subject to the Mozilla Public
# License Version 1.1 (the "License"); you may not use this file
# except in compliance with the License. You may obtain a copy of
# the License at http://www.mozilla.org/MPL/
#
# Software distributed under the License is distributed on an "AS
# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
# implied. See the License for the specific language governing
# rights and limitations under the License.
#
# The Original Code is the Bugzilla Bug Tracking System.
#
# Contributor(s): Max Kanat-Alexander <[e-mail]>
#                 Noel Cragg <[e-mail]>

package Bugzilla::Install::DB;

# NOTE: This package may "use" any modules that it likes,
# localconfig is available, and params are up to date. 

use strict;

use Bugzilla::Bug qw(is_open_state);
use Bugzilla::Constants;
use Bugzilla::Hook;
use Bugzilla::Util;
use Bugzilla::Series;

use Date::Parse;
use Date::Format;
use IO::File;

use base qw(Exporter);
our @EXPORT_OK = qw(
    indicate_progress
);

sub indicate_progress {
    my ($params) = @_;
    my $current = $params->{current};
    my $total   = $params->{total};
    my $every   = $params->{every} || 1;

    print "." if !($current % $every);
    if ($current % ($every * 60) == 0) {
        print "$current/$total (" . int($current * 100 / $total) . "%)\n";
    }
}

# NOTE: This is NOT the function for general table updates. See
# update_table_definitions for that. This is only for the fielddefs table.
sub update_fielddefs_definition {
    my $dbh = Bugzilla->dbh;

    # 2005-02-21 - [e-mail] - Bug 279910
    # qacontact_accessible and assignee_accessible field names no longer exist
    # in the 'bugs' table. Their corresponding entries in the 'bugs_activity'
    # table should therefore be marked as obsolete, meaning that they cannot
    # be used anymore when querying the database - they are not deleted in
    # order to keep track of these fields in the activity table.
    if (!$dbh->bz_column_info('fielddefs', 'obsolete')) {
        $dbh->bz_add_column('fielddefs', 'obsolete',
            {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
        print "Marking qacontact_accessible and assignee_accessible as",
              " obsolete fields...\n";
        $dbh->do("UPDATE fielddefs SET obsolete = 1
                  WHERE name = 'qacontact_accessible'
                        OR name = 'assignee_accessible'");
    }

    # 2005-08-10 Myk Melez <[e-mail]> bug 287325
    # Record each field's type and whether or not it's a custom field,
    # in fielddefs.
    $dbh->bz_add_column('fielddefs', 'type',
                        {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0});
    $dbh->bz_add_column('fielddefs', 'custom',
        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});

    $dbh->bz_add_column('fielddefs', 'enter_bug',
        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});

    # Change the name of the fieldid column to id, so that fielddefs
    # can use Bugzilla::Object easily. We have to do this up here, because
    # otherwise adding these field definitions will fail.
    $dbh->bz_rename_column('fielddefs', 'fieldid', 'id');

    # If the largest fielddefs sortkey is less than 100, then
    # we're using the old sorting system, and we should convert
    # it to the new one before adding any new definitions.
    if (!$dbh->selectrow_arrayref(
            'SELECT COUNT(id) FROM fielddefs WHERE sortkey >= 100'))
    {
        print "Updating the sortkeys for the fielddefs table...\n";
        my $field_ids = $dbh->selectcol_arrayref(
            'SELECT id FROM fielddefs ORDER BY sortkey');
        my $sortkey = 100;
        foreach my $field_id (@$field_ids) {
            $dbh->do('UPDATE fielddefs SET sortkey = ? WHERE id = ?',
                     undef, $sortkey, $field_id);
            $sortkey += 100;
        }
    }

    # Remember, this is not the function for adding general table changes.
    # That is below. Add new changes to the fielddefs table above this
    # comment.
}

# Small changes can be put directly into this function.
# However, larger changes (more than three or four lines) should
# go into their own private subroutine, and you should call that
# subroutine from this function. That keeps this function readable.
#
# This function runs in historical order--from upgrades that older
# installations need, to upgrades that newer installations need.
# The order of items inside this function should only be changed if 
# absolutely necessary.
#
# The subroutines should have long, descriptive names, so that you
# can easily see what is being done, just by reading this function.
#
# This function is mostly self-documenting. If you're curious about
# what each of the added/removed columns does, you should see the schema 
# docs at:
# http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/
#
# When you add a change, you should only add a comment if you want
# to describe why the change was made. You don't need to describe
# the purpose of a column.
#
sub update_table_definitions {
    my $dbh = Bugzilla->dbh;
    _update_pre_checksetup_bugzillas();

    $dbh->bz_add_column('attachments', 'submitter_id',
                        {TYPE => 'INT3', NOTNULL => 1}, 0); 

    $dbh->bz_rename_column('bugs_activity', 'when', 'bug_when');

    _add_bug_vote_cache();
    _update_product_name_definition();
    _add_bug_keyword_cache();

    $dbh->bz_add_column('profiles', 'disabledtext',
                        {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, '');

    _populate_longdescs();
    _update_bugs_activity_field_to_fieldid();

    if (!$dbh->bz_column_info('bugs', 'lastdiffed')) {
        $dbh->bz_add_column('bugs', 'lastdiffed', {TYPE =>'DATETIME'});
        $dbh->do('UPDATE bugs SET lastdiffed = NOW()');
    }

    _add_unique_login_name_index_to_profiles();

    $dbh->bz_add_column('profiles', 'mybugslink', 
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});

    _update_component_user_fields_to_ids();

    $dbh->bz_add_column('bugs', 'everconfirmed',
                        {TYPE => 'BOOLEAN', NOTNULL => 1}, 1);

    $dbh->bz_add_column('products', 'maxvotesperbug',
                        {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '10000'});
    $dbh->bz_add_column('products', 'votestoconfirm',
                        {TYPE => 'INT2', NOTNULL => 1}, 0);

    _populate_milestones_table();

    # 2000-03-22 Changed the default value for target_milestone to be "---"
    # (which is still not quite correct, but much better than what it was
    # doing), and made the size of the value field in the milestones table match
    # the size of the target_milestone field in the bugs table.
    $dbh->bz_alter_column('bugs', 'target_milestone',
        {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"});
    $dbh->bz_alter_column('milestones', 'value',
                          {TYPE => 'varchar(20)', NOTNULL => 1});

    _add_products_defaultmilestone();

    # 2000-03-24 Added unique indexes into the cc and keyword tables.  This
    # prevents certain database inconsistencies, and, moreover, is required for
    # new generalized list code to work.
    if (!$dbh->bz_index_info('cc', 'cc_bug_id_idx')
        || !$dbh->bz_index_info('cc', 'cc_bug_id_idx')->{TYPE})
    {
        $dbh->bz_drop_index('cc', 'cc_bug_id_idx');
        $dbh->bz_add_index('cc', 'cc_bug_id_idx',
                           {TYPE => 'UNIQUE', FIELDS => [qw(bug_id who)]});
    }
    if (!$dbh->bz_index_info('keywords', 'keywords_bug_id_idx')
        || !$dbh->bz_index_info('keywords', 'keywords_bug_id_idx')->{TYPE})
    {
        $dbh->bz_drop_index('keywords', 'keywords_bug_id_idx');
        $dbh->bz_add_index('keywords', 'keywords_bug_id_idx',
            {TYPE => 'UNIQUE', FIELDS => [qw(bug_id keywordid)]});
    }

    _copy_from_comments_to_longdescs();
    _populate_duplicates_table();

    if (!$dbh->bz_column_info('email_setting', 'user_id')) {
        $dbh->bz_add_column('profiles', 'emailflags', {TYPE => 'MEDIUMTEXT'});
    }

    $dbh->bz_add_column('groups', 'isactive',
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});

    $dbh->bz_add_column('attachments', 'isobsolete',
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});

    $dbh->bz_drop_column("profiles", "emailnotification");
    $dbh->bz_drop_column("profiles", "newemailtech");

    # 2003-11-19; [e-mail]; bug 225973: fix field size to accommodate
    # wider algorithms such as Blowfish. Note that this needs to be run
    # before recrypting passwords in the following block.
    $dbh->bz_alter_column('profiles', 'cryptpassword',
                          {TYPE => 'varchar(128)'});

    _recrypt_plaintext_passwords();

    # 2001-06-15 [e-mail] - Change bug:version size to avoid
    # truncates re http://bugzilla.mozilla.org/show_bug.cgi?id=9352
    $dbh->bz_alter_column('bugs', 'version',
                          {TYPE => 'varchar(64)', NOTNULL => 1});

    _update_bugs_activity_to_only_record_changes();

    # bug 90933: Make disabledtext NOT NULL
    if (!$dbh->bz_column_info('profiles', 'disabledtext')->{NOTNULL}) {
        $dbh->bz_alter_column("profiles", "disabledtext",
                              {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, '');
    }

    $dbh->bz_add_column("bugs", "reporter_accessible",
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
    $dbh->bz_add_column("bugs", "cclist_accessible",
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});

    $dbh->bz_add_column("bugs_activity", "attach_id", {TYPE => 'INT3'});

    _delete_logincookies_cryptpassword_and_handle_invalid_cookies();

    # qacontact/assignee should always be able to see bugs: bug 97471
    $dbh->bz_drop_column("bugs", "qacontact_accessible");
    $dbh->bz_drop_column("bugs", "assignee_accessible");

    # 2002-02-20 [e-mail] - bug 24789 time tracking
    $dbh->bz_add_column("longdescs", "work_time",
                        {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'});
    $dbh->bz_add_column("bugs", "estimated_time",
                        {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'});
    $dbh->bz_add_column("bugs", "remaining_time",
                        {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'});
    $dbh->bz_add_column("bugs", "deadline", {TYPE => 'DATETIME'});

    _use_ip_instead_of_hostname_in_logincookies();

    $dbh->bz_add_column('longdescs', 'isprivate',
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
    $dbh->bz_add_column('attachments', 'isprivate',
                        {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});

    $dbh->bz_add_column("bugs", "alias", {TYPE => "varchar(20)"});
    $dbh->bz_add_index('bugs', 'bugs_alias_idx',
                       {TYPE => 'UNIQUE', FIELDS => [qw(alias)]});

    _move_quips_into_db();

    $dbh->bz_drop_column("namedqueries", "watchfordiffs");

    _use_ids_for_products_and_components();
    _convert_groups_system_from_groupset();
    _convert_attachment_statuses_to_flags();
    _remove_spaces_and_commas_from_flagtypes();
    _setup_usebuggroups_backward_compatibility();
    _remove_user_series_map();

    # 2006-08-03 [e-mail] bug 346241, make series.creator nullable
    # This must happen before calling _copy_old_charts_into_database().
    if ($dbh->bz_column_info('series', 'creator')->{NOTNULL}) {
        $dbh->bz_alter_column('series', 'creator', {TYPE => 'INT3'});
        $dbh->do("UPDATE series SET creator = NULL WHERE creator = 0");
    }

    _copy_old_charts_into_database();

    _add_user_group_map_grant_type();
    _add_group_group_map_grant_type();

    $dbh->bz_add_column("profiles", "extern_id", {TYPE => 'varchar(64)'});

    $dbh->bz_add_column('flagtypes', 'grant_group_id', {TYPE => 'INT3'});
    $dbh->bz_add_column('flagtypes', 'request_group_id', {TYPE => 'INT3'});

    # mailto is no longer just userids
    $dbh->bz_rename_column('whine_schedules', 'mailto_userid', 'mailto');