Bug 443140 - Write a migration script to convert stored queries to work with new custom fields
Write a migration script to convert stored queries to work with new custom fi...
Status: CLOSED NEXTRELEASE
Product: Bugzilla
Classification: Community
Component: Query/Bug List (Show other bugs)
3.2
All Linux
low Severity low (vote)
: ---
: ---
Assigned To: Noura El hawary
: Reopened
: 438144 (view as bug list)
Depends On:
Blocks: RHBZ30UpgradeTracker 438144
  Show dependency treegraph
 
Reported: 2008-04-18 15:49 EDT by David Lawrence
Modified: 2013-06-24 00:18 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-07-18 18:10:42 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)
v1 script to convert custom fields in stored queries (4.31 KB, text/plain)
2008-05-01 03:46 EDT, Noura El hawary
dkl: review+
nelhawar: review? (kbaker)
Details

  None (edit)
Description David Lawrence 2008-04-18 15:49:58 EDT
Several fields that were normal text fields in the 2.18 bugs table are now
custom fields in the new 3.2 schema. Alot of people have probably stored saved
queries using the old field names and will need to have their queries converted
to work with the new custom fields. The fields that have changed are:

devel_whiteboard    => cf_devel_whiteboard
qa_whiteboard       => cf_qa_whiteboard
internal_whiteboard => cf_internal_whiteboard
fixed_in            => cf_fixed_in
cust_facing         => cf_cust_facing

multiple select fields:
issuetracker        => cf_issuetracker

In 2.18 these fields showed up in the query.cgi page as normal text fields with
the search type drop down next to each field. In 3.2, since they are considered
custom fields they no longer show up as individual search fields and must be
searched by using the boolean charts.

A script needs to be created that will go through the namedqueries table and
convert any search criteria using these older field names to the new format. For
example, a search of the devel_whiteboard would look like this in 2.18:

{
  devel_whiteboard_type => 'allwordssubstr',
  devel_whiteboard      => 'foobar'
}

In 3.2, this would need be converted to:

{
  field0-0-0 => 'cf_devel_whiteboard',
  type0-0-0  => 'equals',
  value0-0-0 => 'foobar',
}

The *_whiteboard_type values would need to be mapped to their related type0-0-0
values. When doing the script we must also take into account any other boolean
searches they have defined and choose a proper sequence number so as not to
overwrite another set. We could possibly choose some high safe value which
should still work on the backend. Such as field100-0-0, type100-0-0,
value100-0-0, etc.
Comment 1 Noura El hawary 2008-04-29 00:14:30 EDT
for the converion between the *_whiteboard_type and their related type0-0-0
values I think they are the same as the types names are the same between the
templates:

./template/en/default/search/boolean-charts.html.tmpl
./template/en/default/search/form.html.tmpl

so I guess the types will stay the same in this case. unless there is something
i misunderstand.

Noura
Comment 2 David Lawrence 2008-04-29 01:55:39 EDT
(In reply to comment #1)
> for the converion between the *_whiteboard_type and their related type0-0-0
> values I think they are the same as the types names are the same between the
> templates:
> 
> ./template/en/default/search/boolean-charts.html.tmpl
> ./template/en/default/search/form.html.tmpl
> 
> so I guess the types will stay the same in this case. unless there is something
> i misunderstand.

You are correct Noura. They are in fact the same so those should be easy to
migrate from *_whiteboard_type to the proper typeX-X-X value.

Dave
Comment 3 Noura El hawary 2008-05-01 03:46:08 EDT
Created attachment 304299 [details]
v1 script to convert custom fields in stored queries

HI,

Attached is a script to convert custom fields to cf_* in the stored queries to
work with bz 3.2,

the script does 2 conversion:

1- for custom fields that exist as field_name => something and field_name_type
=> type it is changed to the fieldX-X-X , typeX-X-X and valueX-X-X basically i
had to get the right number for the boolean chart enty and it had to be exactly
the next increment of the existing one in the namedquery or otherwise the query
will not give the right result. 

2- if the custom field already did exist in the boolean chart for example
field0-0-0=devel_whiteboard to replace devel_whiteboard with
cf_devel_whiteboard. Note - this is the only case for issuetracker.

Please review and let me know what you think. I haven't run the whole script on
bz-db1 bugs database yet just done testing on few queries.

Noura
Comment 4 Noura El hawary 2008-05-01 03:48:02 EDT
worked 6 hours
Comment 5 David Lawrence 2008-05-01 15:21:27 EDT
(In reply to comment #3)
> 1- for custom fields that exist as field_name => something and field_name_type
> => type it is changed to the fieldX-X-X , typeX-X-X and valueX-X-X basically i
> had to get the right number for the boolean chart enty and it had to be exactly
> the next increment of the existing one in the namedquery or otherwise the query
> will not give the right result. 

My fault Noura. I suggest using some high integer value for the first number but
the code is set up that it will quit looping if it does not see the next chart
id in a range of integers.

Bugzilla/Search.pm Line 646:

    for ($chart=-1 ;
         $chart < 0 || $params->param("field$chart-0-0") ;
         $chart++) {

So as soon as $params->param("field$chart-0-0") for the next increment of
$chart, it will break out of the loop. So if there is a gap in the numbers it
will not work.

> 2- if the custom field already did exist in the boolean chart for example
> field0-0-0=devel_whiteboard to replace devel_whiteboard with
> cf_devel_whiteboard. Note - this is the only case for issuetracker.
> 

Good. Yeah those should be simple.

Will review.

Dave
Comment 6 David Lawrence 2008-05-01 16:09:33 EDT
Comment on attachment 304299 [details]
v1 script to convert custom fields in stored queries

>use DBI;
>use CGI;
>

I assume you did not just reuse Bugzilla.pm cause you wanted this script to be
usable standalone without having to be in the Bugzilla root?

>if (@ARGV) {
>    my $cmdline_query = shift @ARGV;
>
>    convert_query($cmdline_query);
>}

Currently you have to pass the full query as an actual argument to the script
instead of being able to pipe it in using STDIN. So instead of being able to do 

perl /tmp/update_cf_queries.pl < /tmp/dkl_query

I had to do

perl /tmp/update_cf_queries.pl `cat /tmp/dkl_query`

Maybe just use Getopt::Long and allow an option like '--stdin|-s'
that would allow using STDIN?

Not a big deal since we will just use this script on occasion so maybe this is
not necessary.

>    my @field_no;
>    my @params = $cgi->param;
>    for my $param (@params) {
>        if ($param =~ /field\d/){
>            my @num = $param =~ /field(\d)/;
>            push @field_no, @num; 
>        }
>    }

You may need to use \d+ here instead of just \d in case the number is more than
one digit, such as field12-0-0, etc.

The rest looks good to me. I tried a couple test queries and it converted them
as expected. Please run this on the  bz-db1-test bz3 database when you can and
see what happens. Create some of your own test queries in the database. You may
need to manually convert them back to the old format before doing your
conversion. Then check them after the conversion to see that they reload
properly in query.cgi.

Dave
Comment 7 Noura El hawary 2008-05-02 11:36:36 EDT
(In reply to comment #6)
> (From update of attachment 304299 [details] [edit])
> >use DBI;
> >use CGI;
> >
> 
> I assume you did not just reuse Bugzilla.pm cause you wanted this script to be
> usable standalone without having to be in the Bugzilla root?
> 
Yes true, I made it standalone script.

> >if (@ARGV) {
> >    my $cmdline_query = shift @ARGV;
> >
> >    convert_query($cmdline_query);
> >}
> 
> Currently you have to pass the full query as an actual argument to the script
> instead of being able to pipe it in using STDIN. So instead of being able to do 
> 
> perl /tmp/update_cf_queries.pl < /tmp/dkl_query
> 
> I had to do
> 
> perl /tmp/update_cf_queries.pl `cat /tmp/dkl_query`
> 
> Maybe just use Getopt::Long and allow an option like '--stdin|-s'
> that would allow using STDIN?
> 
> Not a big deal since we will just use this script on occasion so maybe this is
> not necessary.
> 
Ok will leave it for now, might change it later if needed.

> >    my @field_no;
> >    my @params = $cgi->param;
> >    for my $param (@params) {
> >        if ($param =~ /field\d/){
> >            my @num = $param =~ /field(\d)/;
> >            push @field_no, @num; 
> >        }
> >    }
> 
> You may need to use \d+ here instead of just \d in case the number is more than
> one digit, such as field12-0-0, etc.
> 

Modified

> The rest looks good to me. I tried a couple test queries and it converted them
> as expected. Please run this on the  bz-db1-test bz3 database when you can and
> see what happens. Create some of your own test queries in the database. You may
> need to manually convert them back to the old format before doing your
> conversion. Then check them after the conversion to see that they reload
> properly in query.cgi.
> 
> Dave
> 

Thanks for the review Dave. I committed the script to rh_bugzilla_3/redhat dir 
and ran it on bz-db1-test bugs and I think it is looking good.
 
Note: it took about 20 mins to run.

Noura
Comment 8 Noura El hawary 2008-05-02 11:37:10 EDT
worked 2 hours
Comment 9 David Lawrence 2008-05-02 11:45:43 EDT
(In reply to comment #7)
> Thanks for the review Dave. I committed the script to rh_bugzilla_3/redhat dir 
> and ran it on bz-db1-test bugs and I think it is looking good.
>  
> Note: it took about 20 mins to run.

Will add this to the wiki page outlining the migration steps.

https://engineering.redhat.com/trac/bugzilla-3.0-rh/wiki/DbMigrationProcess

Dave

Comment 10 David Lawrence 2008-05-12 13:57:48 EDT
*** Bug 438144 has been marked as a duplicate of this bug. ***
Comment 11 Noura El hawary 2008-05-22 02:44:46 EDT
Hey Dave,

shall we get this script to run on partner-bugzilla database now?

Noura
Comment 12 David Lawrence 2008-05-23 11:15:53 EDT
(In reply to comment #11)
> Hey Dave,
> 
> shall we get this script to run on partner-bugzilla database now?
> 
> Noura

Yes, lets go ahead and run this script on partner now so that people can test
that their stored queries still work. I will have eng-sysadmin run it today.

Let's also keep this bug open since it is more of an ongoing process bug that
will need to be done at each beta release and then once again for final.

Dave
Comment 13 David Lawrence 2008-07-18 18:10:42 EDT
Actually we can close this as it is documented on the Bugzilla 3 wiki migration
process page.

https://engineering.redhat.com/trac/bugzilla-3.0-rh/wiki/DbMigrationProcess

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