喬遷啟示:因應實體教室租約到期,富捷培訓於八月起將在長安新址,以線上直播教學為您服務,懇請舊雨新知繼續支持與指教。 地址:台北市長安東路二段201巷19號之1一樓,電話: 02-27116373

13 九月 2013

SugarCRM DataBase 轉移 – PERL程式範例轉移 users table 篇

SugarCRM 從6.2.x資料庫轉移到6.5.x

使用SugarCRM所提供之升級套件升級失敗,故須了解資料庫及欄位差異,使用PERL程式移轉舊資料庫資料到新的資料庫。

原始碼如下:

#!/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)” );

# newcrm.geego.com.tw information
my %newcrm = ( ‘database’ => ‘sugarcrm’,
‘host’ => ‘localhost’,
‘port’ => ‘3306’,
‘dbuser’ => ‘dbuser name here’,
‘dbpasswd’ => ‘dbpassword here’,
‘table’=> ‘users’ );

# crm.geego.com.tw information
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’ }”;
# old 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} );
# old 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();