Bug 497463 - create an entire set of db by running checksetup.pl
create an entire set of db by running checksetup.pl
Status: CLOSED NEXTRELEASE
Product: Bugzilla
Classification: Community
Component: Database (Show other bugs)
3.2
All Linux
medium Severity medium (vote)
: ---
: ---
Assigned To: Tony Fu
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2009-04-23 23:45 EDT by Tony Fu
Modified: 2013-05-06 21:55 EDT (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2009-05-19 03:59:36 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
update Bugzilla/Install/DB.pm file to add cf_* fields into bugs table (1.87 KB, patch)
2009-05-01 00:00 EDT, Tony Fu
dkl: review-
Details | Diff
update Bugzilla/Install/DB.pm file to add cf_* fields into bugs table (5.33 KB, patch)
2009-05-05 06:14 EDT, Tony Fu
dkl: review-
Details | Diff
install-update_db.pl (2.76 KB, text/plain)
2009-05-06 00:02 EDT, Tony Fu
no flags Details
install-update_db.pl (3.46 KB, text/plain)
2009-05-06 11:36 EDT, David Lawrence
no flags Details
patch for install-update_db.pl (4.76 KB, patch)
2009-05-06 22:30 EDT, Tony Fu
dkl: review+
Details | Diff

  None (edit)
Description Tony Fu 2009-04-23 23:45:01 EDT
Description of problem:

After running checksetup.pl on a brand new Bugzilla system, some cf_* fields are missing from what we have in the live DB.  We need to add them to the right places so they can be created by checksetup.pl.
Comment 1 Tony Fu 2009-04-24 00:48:16 EDT
The following cf tables and cf_* fields are missing after running checksetup.pl 

CREATE TABLE `bug_cf_conditional_nak` (
  `bug_id` mediumint(9) NOT NULL,
  `value` varchar(64) NOT NULL,
  UNIQUE KEY `bug_cf_conditional_nak_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `bug_cf_issuetracker` (
  `bug_id` mediumint(9) NOT NULL,
  `value` mediumint(9) NOT NULL,
  UNIQUE KEY `bug_cf_issuetracker_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `bug_cf_partner` (
  `bug_id` mediumint(9) NOT NULL,
  `value` varchar(64) NOT NULL,
  UNIQUE KEY `bug_cf_partner_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `bug_cf_partner_qe` (
  `bug_id` mediumint(9) NOT NULL,
  `value` varchar(64) NOT NULL,
  UNIQUE KEY `bug_cf_partner_qe_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `bug_cf_partners` (
  `bug_id` mediumint(9) NOT NULL,
  `value` varchar(64) NOT NULL,
  UNIQUE KEY `bug_cf_partners_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `bug_cf_testing` (
  `bug_id` mediumint(9) NOT NULL,
  `value` mediumint(9) NOT NULL,
  UNIQUE KEY `bug_cf_testing_bug_id_idx` (`bug_id`,`value`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `bug_cf_verified` (
  `bug_id` mediumint(9) NOT NULL,
  `value` varchar(64) NOT NULL,
  UNIQUE KEY `bug_cf_verified_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cf_conditional_nak` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_conditional_nak_value_idx` (`value`),
  KEY `cf_conditional_nak_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `cf_cust_facing` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_cust_facing_value_idx` (`value`),
  KEY `cf_cust_facing_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `cf_partner` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_partner_value_idx` (`value`),
  KEY `cf_partner_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `cf_partner_qe` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_partner_qe_value_idx` (`value`),
  KEY `cf_partner_qe_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `cf_partners` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_partners_value_idx` (`value`),
  KEY `cf_partners_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

CREATE TABLE `cf_targetrelease` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_targetrelease_value_idx` (`value`),
  KEY `cf_targetrelease_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `cf_verified` (
  `id` smallint(6) NOT NULL auto_increment,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL default '0',
  `isactive` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cf_verified_value_idx` (`value`),
  KEY `cf_verified_sortkey_idx` (`sortkey`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

============================================================
missing cf_* fields in bugs table
`cf_internal_whiteboard` varchar(255) NOT NULL,
  `cf_devel_whiteboard` varchar(255) NOT NULL,
  `cf_qa_whiteboard` varchar(255) NOT NULL,
  `cf_fixed_in` varchar(255) NOT NULL,
  `cf_cust_facing` varchar(64) NOT NULL default '---',
  `cf_release_notes` mediumtext,
  `cf_pm_score` varchar(200) default NULL,
  `cf_build_id` varchar(255) default NULL,
  `cf_targetrelease` varchar(64) NOT NULL default '---',
  `cf_clone_of` varchar(255) default NULL,
  `cf_start_date` datetime default NULL,
Comment 2 Tony Fu 2009-04-30 23:58:52 EDT
Dave,

I added the code of adding cf_* fileds into bugs table.  Please see attached patch.

Also, do you think we should update the Bugzilla/DB/Schema.pm file to include all cf_* tables or we are still doing that by running redhat/db_fixup.sql?


Thanks,
Tony
Comment 3 Tony Fu 2009-05-01 00:00:27 EDT
Created attachment 342037 [details]
update Bugzilla/Install/DB.pm file to add cf_* fields into bugs table
Comment 4 David Lawrence 2009-05-01 15:20:10 EDT
Comment on attachment 342037 [details]
update Bugzilla/Install/DB.pm file to add cf_* fields into bugs table

The custom field creation also needs to add the proper information about the field to the fielddefs table. Your best bet is to just call Bugzilla::Field->create() for each custom field that we need.
Comment 5 David Lawrence 2009-05-01 15:25:01 EDT
For example:

Bugzilla::Field->create({
    name        => 'cf_internal_whiteboard',
    description => 'Internal Whiteboard',
    type        => FIELD_TYPE_FREETEXT,
    mailhead    => 0,
    enter_bug   => 1,
    obsolete    => 0,
    custom      => 1,
    public      => 0,
);

and so on for each.

Dave
Comment 6 David Lawrence 2009-05-01 15:32:55 EDT
You can also create an extension file for these in 

extensions/redhat_fields/code/install-update_db.pl

and just put them all in there. We should probably eventually move all of our custom changes there over time as well. 

Something I forgot to mention before is you will 
need to also include Bugzilla::Field and Bugzilla::Constants. The Constants module has the field types in it.

Current field type names are:

FIELD_TYPE_FREETEXT
FIELD_TYPE_SINGLE_SELECT
FIELD_TYPE_MULTI_SELECT
FIELD_TYPE_TEXTAREA
FIELD_TYPE_DATETIME
FIELD_TYPE_FREETEXT_MULTI_INTEGER
FIELD_TYPE_LAST
FIELD_TYPE_BUG_ID
FIELD_TYPE_BUG_URLS
Comment 7 Tony Fu 2009-05-05 06:10:27 EDT
(In reply to comment #5)
> For example:
> 
> Bugzilla::Field->create({
>     name        => 'cf_internal_whiteboard',
>     description => 'Internal Whiteboard',
>     type        => FIELD_TYPE_FREETEXT,
>     mailhead    => 0,
>     enter_bug   => 1,
>     obsolete    => 0,
>     custom      => 1,
>     public      => 0,
> );
> 
> and so on for each.
> 
> Dave  

Hi Dave,

Thanks for the review.  

I have added the code to update fielddefs table.  When I ran my code, it existed with an error after creating the cf_targetrelease field and cf_targetrelease table.  I am not sure if it was caused by some setup of my Mysql db, if I reduce the length of the field name (for example, change the field name to 'cf_target'), the checksetup will run successfully with any errors.  Could you please run it on your test db and see if you have the same problem?

Thanks,
Tony
Comment 8 Tony Fu 2009-05-05 06:14:54 EDT
Created attachment 342430 [details]
update Bugzilla/Install/DB.pm file to add cf_* fields into bugs table

the checksetup gave me the following error:

........
........
Adding new column 'cf_pm_score' to the 'bugs' table...
Adding new column 'cf_build_id' to the 'bugs' table...
Adding new column 'cf_targetrelease' to the 'bugs' table...
Adding new table cf_targetrelease ...
Undefined subroutine &Bugzilla::Util::ThrowTemplateError called at Bugzilla/Util.pm line 591.

If I reduced the length of cf field name to less 16 characters, it ran successfully without any errors.
Comment 9 David Lawrence 2009-05-05 10:46:39 EDT
Comment on attachment 342430 [details]
update Bugzilla/Install/DB.pm file to add cf_* fields into bugs table

>Index: Bugzilla/Install/DB.pm
>===================================================================
>--- Bugzilla/Install/DB.pm	(revision 1204)
>+++ Bugzilla/Install/DB.pm	(working copy)
>@@ -27,6 +27,7 @@
> use Bugzilla::Install::Util qw(indicate_progress install_string);
> use Bugzilla::Util;
> use Bugzilla::Series;
>+use Bugzilla::Field;
> 
> use Date::Parse;
> use Date::Format;
>@@ -556,6 +557,8 @@
>     $dbh->bz_add_column('profiles', 'password_expire_days', 
>                         {TYPE => 'INT2', NOTNULL => 0});
>     
>+    # 2009-0424 tfu@redhat.com - Bug 497463
>+    _add_cf_fields_to_bugs();
>     ################################################################
>     # New --TABLE-- changes should go *** A B O V E *** this point #
>     ################################################################
>@@ -3080,6 +3083,154 @@
>     }
> }

Let's move all of this to an extension script instead of keeping it in Bugzilla/Install/DB.pm
directly. You can put this in extensions/redhat_fields/code/install-update_db.pl it will get
executed automatically. No need to have a subroutine either just put the Bugzilla::Field->create() calls
one after the other in the script. You will need to include Bugzilla::{Field,Util,Error} probably in your
install-update_db.pl script.

>+sub _add_cf_fields_to_bugs {
>+    my $dbh = Bugzilla->dbh;
>+    $dbh->bz_add_column('bugs', 'cf_internal_whiteboard',
>+        {TYPE => 'varchar(255)', NOTNULL => 1}, '');

There is no need for the $dbh->bz_add_column() for each of the fields as Bugzilla::Field->create() will call that for you
so the create() is all you need. Also I did not check but make sure the values for mailhead, enter_bug, obsolete, custom,
and public match up to what the values are set to currently in the fielddefs table for live.

>+    Bugzilla::Field->create({
>+        name        => 'cf_internal_whiteboard',
>+        description => 'Internal Whiteboard',
>+        type        => FIELD_TYPE_FREETEXT,
>+        mailhead    => 0,
>+        enter_bug   => 1,
>+        obsolete    => 0,
>+        custom      => 1,
>+        public      => 0,
>+    });
>+

>+
>+    $dbh->bz_add_column('bugs', 'cf_start_date',
>+        {TYPE => 'DATETIME', DEFAULT => 'NULL'}, '');
>+    Bugzilla::Field->create({
>+        name        => 'cf_start_date',
>+        description => 'Start date',
>+        type        => FIELD_TYPE_DATETIME,
>+        mailhead    => 0,
>+        enter_bug   => 1,
>+        obsolete    => 0,
>+        custom      => 1,
>+        public      => 0,
>+    });
>+}

No need to create cf_start_date for now as that is an experimental field I was playing around with and is not on our production server.
Comment 10 Tony Fu 2009-05-06 00:00:26 EDT
Dave,

Here is the extensions/redhat_fields/code/install-update_db.pl file.  Please give it a try and see if it has the same problem on your test system.


Thanks,
Tony
Comment 11 Tony Fu 2009-05-06 00:02:05 EDT
Created attachment 342589 [details]
install-update_db.pl
Comment 12 Tony Fu 2009-05-06 03:24:34 EDT
(In reply to comment #10)
> Dave,
> 
> Here is the extensions/redhat_fields/code/install-update_db.pl file.  Please
> give it a try and see if it has the same problem on your test system.
> 
> 
> Thanks,
> Tony  

Dave,

Somehow, the Bugzilla::Field->create() function calls the Bugzilla->get_fields(), which hooks the filter_custom_fields.pl script.  In filter_custom_fields.pl, it will check if a user has the right to access cf_partner, cf_verified and cf_targetrelease.  Well, when we first run checkup.pl script, there is not user existing, so it will cause the filter_custom_fields.pl 'undefined value' error.

In order to get rid of this error, maybe we can add a eval{} around the code hooks the filter_custom_fileds in Bugzilla->get_fields(), like

sub get_fields {
    my $class = shift;
    my $criteria = shift;
    # This function may be called during installation, and Field::match
    # may fail at that time. so we want to return an empty list in that
    # case.
    my $fields = eval { Bugzilla::Field->match($criteria) } || [];

    # REDHAT EXTENSION START 406151
    eval {
    Bugzilla::Hook::process('filter_custom_fields', { custom_fields => \$fields }); } ;
    # REDHAT EXTENSION END 406151

    return @$fields;
}


Or we can add some if conditional code to check if a Bugzilla->user is undef in
 extensions/redhat_fields/code/filter_custom_fields.pl file.

What do you think?


Thanks,
Tony
Comment 13 Tony Fu 2009-05-06 03:31:37 EDT
Dave,

Another question about cf fields.  What are cf_verified, cf_partner and cf_conditional_nak for?  They are not in bugs table.


Thanks,
Tony
Comment 14 Noura El hawary 2009-05-06 09:40:10 EDT
(In reply to comment #13)
> Dave,
> 
> Another question about cf fields.  What are cf_verified, cf_partner and
> cf_conditional_nak for?  They are not in bugs table.
> 
> 
> Thanks,
> Tony  

Hey Tony,

Those fields above are multi select custom fields, so each field can contain more than one value, that is why they are not included in the bugs table instead they are included in separate tables called bug_cf_partner, bug_cf_verified and bug_cf_conditional_nak. 

Noura
Comment 15 Noura El hawary 2009-05-06 09:49:16 EDT
(In reply to comment #12)
> (In reply to comment #10)
> > Dave,
> > 
> > Here is the extensions/redhat_fields/code/install-update_db.pl file.  Please
> > give it a try and see if it has the same problem on your test system.
> > 
> > 
> > Thanks,
> > Tony  
> 
> Dave,
> 
> Somehow, the Bugzilla::Field->create() function calls the
> Bugzilla->get_fields(), which hooks the filter_custom_fields.pl script.  In
> filter_custom_fields.pl, it will check if a user has the right to access
> cf_partner, cf_verified and cf_targetrelease.  

Tony I think that filter_custom_fields.pl checks user access for most of the custom fields not only for the ones you mentioned above, so are the errors you are getting are only related to the custom fields cf_partner, cf_verified and cf_targetrelease? maybe because they are in separate tables?
Comment 16 David Lawrence 2009-05-06 11:05:10 EDT
(In reply to comment #14)
> (In reply to comment #13)
> > Dave,
> > 
> > Another question about cf fields.  What are cf_verified, cf_partner and
> > cf_conditional_nak for?  They are not in bugs table.
> > 
> > 
> > Thanks,
> > Tony  
> 
> Hey Tony,
> 
> Those fields above are multi select custom fields, so each field can contain
> more than one value, that is why they are not included in the bugs table
> instead they are included in separate tables called bug_cf_partner,
> bug_cf_verified and bug_cf_conditional_nak. 
> 
> Noura  

Right. They are fields of type FIELD_TYPE_MULTI_SELECT which are stored in separate mapping tables. The Bugzilla::Field->create() call with the type set to multi select will create the mapping tables automatically.
Comment 17 David Lawrence 2009-05-06 11:36:53 EDT
Created attachment 342677 [details]
install-update_db.pl

Turns out it was just an ordering issue. The cf_partner field needs to be created before the cf_targetrelease since $user->can_set_targetrelease() references cf_partner also. So I am attaching a install-update_db.pl script which worked for me with the way the fields are ordered. Make sure you drop the database completely or you still get error. Check this in if it looks good and you do not find any other issues.

Dave
Comment 18 Tony Fu 2009-05-06 22:29:17 EDT
(In reply to comment #17)
> Created an attachment (id=342677) [details]
> install-update_db.pl
> 
> Turns out it was just an ordering issue. The cf_partner field needs to be
> created before the cf_targetrelease since $user->can_set_targetrelease()
> references cf_partner also. So I am attaching a install-update_db.pl script
> which worked for me with the way the fields are ordered. Make sure you drop the
> database completely or you still get error. Check this in if it looks good and
> you do not find any other issues.
> 
> Dave  

Dave,

Your code works good for me without any other issues.

I have made a patch for it and if you are happy with it, I can commit it to svn repo.


Thanks,
Tony
Comment 19 Tony Fu 2009-05-06 22:30:36 EDT
Created attachment 342759 [details]
patch for install-update_db.pl
Comment 20 David Lawrence 2009-05-06 23:08:05 EDT
Comment on attachment 342759 [details]
patch for install-update_db.pl

Looks good Tony.
Comment 21 Tony Fu 2009-05-19 03:59:36 EDT
Have committed it to svn repository.

Close this ticket

Note You need to log in before you can comment on or make changes to this bug.