Attachment 304299 Details for Bug 443140 – v1 script to convert custom fields in stored queries

v1 script to convert custom fields in stored queries

update_cf_queries.pl (text/plain), 4.31 KB, created by Noura El hawary on 2008-05-01 07:46:08 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Noura El hawary

Created: 2008-05-01 07:46:08 UTC

Size: 4.31 KB

patch

obsolete

>#!/usr/bin/env perl
>use strict;
>use warnings;
>
># script:   update_cf_queries.pl
># descr:    one time script to update stored queries in the Bugzilla namedqueries
>#           table after using custom fields in bugzilla 3.2
># BZ:       https://bugzilla.redhat.com/show_bug.cgi?id=438144
>#           https://bugzilla.redhat.com/show_bug.cgi?id=443140
>
>use DBI;
>use CGI;
>
>if (@ARGV) {
>    my $cmdline_query = shift @ARGV;
>
>    convert_query($cmdline_query);
>}
>else {
>
>    my $user_mysql = 'bugs';
>    my $pass_mysql = '';
>    my $host_mysql = 'bz-db1-test.devel.redhat.com';
>
>    my %attr = (
>        PrintError => 0,
>        RaiseError => 1
>    );
>
>    my $dsn_mysql = "dbi:mysql:dbname=bugs;host=$host_mysql";
>    my $dbh = DBI->connect( $dsn_mysql, $user_mysql, $pass_mysql, \%attr )
>        or die "Couldn't connect database: " . DBI->errstr;
>
>    my @custom_fields
>        = qw/'%devel_whiteboard%' '%qa_whiteboard%' '%internal_whiteboard%' '%fixed_in%' '%cust_facing%' '%issuetracker%'/;
>
>    my $condition = join( " OR query LIKE ", @custom_fields );
>
>    my $query
>        = "SELECT DISTINCT userid, name, query FROM namedqueries WHERE query LIKE $condition";
>
>    my $count = 0;
>
>    my $select_query_sth = $dbh->prepare($query);
>
>    $select_query_sth->execute();
>    while ( my ( $userid, $name, $old_query )
>        = $select_query_sth->fetchrow_array() )
>    {
>        my $new_query = convert_query($old_query);
>
>        # Save change to database
>        my $update_query_sth = $dbh->prepare(
>            "UPDATE namedqueries SET query = ? WHERE userid = ? AND name = ?"
>        );
>        $update_query_sth->execute( $new_query, $userid, $name );
>
>        $count++;
>    }
>
>    print "$count converted.\n";
>}
>
>sub convert_query {
>    my ($old_query) = @_;
>
>    # Load CGI object with old query string from database
>    my $cgi          = new CGI($old_query);
>    my @new_products = ();
>
>    print "Old Query: $old_query\n";
>
>    my @field_no;
>    my @params = $cgi->param;
>    for my $param (@params) {
>        if ($param =~ /field\d/){
>            my @num = $param =~ /field(\d)/;
>            push @field_no, @num; 
>        }
>    }
>
>    @field_no = sort(@field_no);
>
>    my $boolean_chart_count = $field_no[$#field_no] + 1;
>
>    my @custom_fields
>        = qw/devel_whiteboard qa_whiteboard internal_whiteboard fixed_in cust_facing/;
>
>    for my $cf_field (@custom_fields) {
>        if (   $cgi->param("$cf_field")
>            && $cgi->param( "$cf_field" . "_type" ) )
>        {
>            my $field = "field$boolean_chart_count" . "-0-0";
>            my $type  = "type$boolean_chart_count" . "-0-0";
>            my $value = "value$boolean_chart_count" . "-0-0";
>
>            $boolean_chart_count++;
>
>            $cgi->param( -name => $field, -value => "cf_" . "$cf_field" );
>            $cgi->param(
>                -name  => $type,
>                -value => $cgi->param( "$cf_field" . "_type" )
>            );
>            $cgi->param(
>                -name  => $value,
>                -value => $cgi->param("$cf_field")
>            );
>        }
>        $cgi->delete("$cf_field") if $cgi->param("$cf_field");
>        $cgi->delete( "$cf_field" . "_type" )
>            if $cgi->param( "$cf_field" . "_type" );
>    }
>
>    # Create new query string with update params
>    my $new_query = canonicalise_query($cgi);
>    $new_query =~ s/=$_/=cf_$_/g foreach @custom_fields;
>
>    print "New Query: $new_query\n\n";
>
>    return $new_query;
>}
>
>sub canonicalise_query {
>    my ( $cgi, @exclude ) = @_;
>
>    # Reconstruct the URL by concatenating the sorted param=value pairs
>    my @parameters;
>    foreach my $key ( sort( $cgi->param() ) ) {
>
>        # Leave this key out if it's in the exclude list
>        next if lsearch( \@exclude, $key ) != -1;
>
>        my $esc_key = url_quote($key);
>
>        foreach my $value ( $cgi->param($key) ) {
>            if ($value) {
>                my $esc_value = url_quote($value);
>
>                push( @parameters, "$esc_key=$esc_value" );
>            }
>        }
>    }
>
>    return join( "&", @parameters );
>}
>
>sub url_quote {
>    my ($toencode) = (@_);
>    $toencode
>        =~ s/([^a-zA-Z0-9_\-.%;&?\/\\:=~])/uc sprintf("%%%02x",ord($1))/eg;
>    return $toencode;
>}
>
>sub lsearch {
>    my ( $list, $item ) = (@_);
>    my $count = 0;
>    foreach my $i (@$list) {
>        if ( $i eq $item ) {
>            return $count;
>        }
>        $count++;
>    }
>    return -1;
>}
>

Flags:

dkl: review+
nelhawar: review? (kbaker)

Actions: View

Attachments on bug 443140: 304299