PERL MySQL DB conversion - Transfer SugarCRM "users" table from 6.2.x to 6.5.x

SugarCRM has official upgrade packages that are very convenient through SugarCRM management interface. Since I did many customization on the 6.2.x one, the official upgrade packages didn't work well with me( it hangs all the time ).

Thus, I had to find out:

  • the "users" table differences between 6.2.x and 6.5.x.
  • the data in the "users" table has any changes or not.

Based on the above research, wrote the following PERL program to transfer data from 6.2.x to 6.5.x platform without any problem.

#!/usr/bin/perl -w

use strict;  
use DBI;  
use Crypt::PasswdMD5;  
use utf8;  
#use Encode;
use Data::Dumper;

binmode( STDOUT, ":encoding(utf-8)" );  
binmode( STDIN, ":encoding(utf-8)" );  
binmode( STDERR, ":encoding(utf-8)" );

# access info of a newly installed SugarCRM system called newcrm
my %newcrm = ( 'database' => 'sugarcrm',  
               'host' => 'localhost',
               'port' => '3306′,
               'dbuser' => 'dbuser name here',
               'dbpasswd' => 'dbpassword here',
               'table'=> 'users' );

#  access info of the original SugarCRM system called crm
my %crm = ( 'database' => 'sugarcrm',  
            'host' => 'oldcrm host name here',
            'port' => '3306′,
            'dbuser' => 'dbuser name here',
            'dbpasswd' => 'dbpassword here',
            'table' => 'users' );

# Field names string to represent new SugarCRM(6.5.x) table fields
my $sql_select_field = '';  
my %crm_table_fields = ();  
my @crm_table_fields = ();

# new SugarCRM(6.5.x) database information string
my $newcrmdb = "DBI:mysql:database=$newcrm{ 'database' };host=$newcrm{ 'host' };port=$newcrm{ 'port' }";  
# original SugarCRM(6.2.x) database information string
my $crmdb = "DBI:mysql:database=$crm{ 'database' };host=$crm{ 'host' };port=$crm{ 'port' }";

# new crm’s object handler
my $newcrmdbh = DBI->connect( "$newcrmdb", $newcrm{ 'dbuser' }, $newcrm{ 'dbpasswd' }, { RaiseError => 1, mysql_enable_utf8 => 1} );  
# original crm’s object handler
my $crmdbh = DBI->connect( "$crmdb", $crm{ 'dbuser' }, $crm{ 'dbpasswd' }, { RaiseError => 1, mysql_enable_utf8 => 1} );

# prepare new crm sql statement
my $newcrmsth = $newcrmdbh->prepare( "SELECT COLUMN_NAME from information_schema.columns where table_schema='sugarcrm' and table_name=\'$newcrm{ table }\' ");  
# prepare oldcrm sql statement
my $crmsth = $crmdbh->prepare( "SELECT COLUMN_NAME from information_schema.columns where table_schema='sugarcrm' and table_name=\'$crm{ table }\' ");

#executing new crm SQL
$newcrmsth->execute();
#executing old crm SQL
$crmsth->execute();

while( my $crmsthref = $crmsth->fetchrow_hashref() )  
{
    foreach ( keys %$crmsthref )
    {
        $crm_table_fields{ $crmsthref->{ $_ } } = 1;
    }
}

while( my $ref = $newcrmsth->fetchrow_hashref() )  
{
    foreach ( keys %$ref )
    {
        if( exists( $crm_table_fields{ $ref->{ $_ } } ) )
        {
            $sql_select_field .= $ref->{ $_ }.',';
        }
        else
       {

       }
    }
}

$crmsth->finish;
#$newcrmsth->finish;
$sql_select_field =~ s/,$//;

#print $sql_select_field."\n"; # debugging statement

my $query_crm_stmt = "SELECT $sql_select_field FROM $crm{ 'table' } ";

my $crmstmt = $crmdbh->prepare( $query_crm_stmt );  
$crmstmt->execute();

my $newcrm_sql_stmt = "INSERT INTO $newcrm{ 'table' } VALUES (";

my $skip = 0;

while( my $ref = $crmstmt->fetchrow_arrayref() )  
{
    if( $skip == 0 )
    {
        $skip++;
        next;
    }
    my $count = 0;
    foreach ( @$ref )
    {
        #$_ = encode( 'utf8′, $_ );
        if( $count == 36 )
        {
            $newcrm_sql_stmt .= "'".$_."'".'),(';
        }
        elsif( $count == 31 )
        {
            $newcrm_sql_stmt .= "'".$_."'".',"1″,';
        }
        else
       {
           $newcrm_sql_stmt .= $_ ? "'".$_."'".',':"",";
       }
       $count++;
    }

}

$newcrm_sql_stmt =~ s/..$//;
print $newcrm_sql_stmt;

#exit;
my $newcrmstmt = $newcrmdbh->prepare( "$newcrm_sql_stmt" );

$newcrmstmt->execute();