#!C:\Perl64\bin\perl.exe -w
#************************************************************************
# Конвертер из XBase в MySQL v 0.2 (17.05.2010г.)
#
#************************************************************************
use strict;
#use Data::Dump qw(dump);
use DBI;
use XBase;
my (
%conf_xbase, %conf_mysql, @tables, %type, @ntables, $dbh_x, $dbh_m, $sth_x
);
# Настройки для MySQL
%conf_mysql = (
'host' => 'localhost',
'base' => 'test1',
'user' => 'root',
'pass' => '123'
);
# Настройки для XBase
%conf_xbase = (
'dir' => 'D:\DBASES\COMMON\\'
);
# Только перечисленные таблицы (все если пусто)
@tables = (
#'table_name',
);
# соотношение типов столбцов
# XBase MySQL
%type = (
'N' => 'float',
'C' => 'char',
'M' => 'longtext',
'D' => 'date'
);
$dbh_x = xbase_connect_1();
$dbh_m = mysql_connect_1();
$dbh_m->do(qq{SET NAMES
'cp1251'});
# Создание таблиц в MySQL базе
print "Создание таблиц:\n";
my $num_table = 0;
next if !($file =~ /(.+)\.dbf$/i);
if (@tables) {
}
$ntables[$num_table] = $1;
my $table = new XBase
$conf_xbase{'dir'}.$file or die XBase
->errstr;
my @names = $table->field_names;
my @types = $table->field_types;
my @lengths = $table->field_lengths;
my @decimals = $table->field_decimals;
my $i = 0;
my $sql = "CREATE TABLE IF NOT EXISTS `$1` (\n";
foreach (@names) {
die "Неизвесный тип - '$types[$i]'" unless exists $type{$types[$i]};
if ($type{$types[$i]} eq 'float') {
$sql .= " `$_` $type{$types[$i]}($lengths[$i], $decimals[$i])";
} elsif ($type{$types[$i]} eq 'char') {
$sql .= " `$_` $type{$types[$i]}($lengths[$i])";
} else {
$sql .= " `$_` $type{$types[$i]}";
}
#$sql .= " NOT NULL,\n";
$sql .= ",\n";
$i++;
}
# индексы
if (-e $conf_xbase{'dir'}.$1.'.cdx') {
my $cur = $table->prepare_select_with_index($conf_xbase{'dir'}.$1.'.cdx');
my @index = @{$cur->[4
]{tags
}}; # проверить! -> print dump($cur);
foreach my $tag (@index) {
$sql .= " KEY `".lc($tag)."` (`$tag`),\n";
}
}
}
$sql .= "\n) ENGINE=MyISAM DEFAULT CHARSET=cp1251\n\n";
$table->close();
$num_table++;
}
print " таблиц $num_table\n";
# Импорт данных в MySQL базу
print "Импорт данных в таблицу:\n";
foreach my $table (@ntables) {
$sth_x = $dbh_x->prepare("SELECT * FROM $table") or die $dbh_x->errstr();
$sth_x->execute() or die $sth_x->errstr();
my $num_row = 0;
while (my $row = $sth_x->fetchrow_hashref()) {
my @values = @{$row}{@fields};
my $sql = sprintf "INSERT INTO `%s` (`%s`) VALUES (%s)",
$table, join("`,`", @fields), join(",", ("?")x
@fields);
$num_row++;
}
$sth_x->finish();
$dbh_m->do(qq{ OPTIMIZE TABLE
`$table` });
}
$dbh_x->disconnect();
$dbh_m->disconnect();
# Соединение с xBase
sub xbase_connect_1 {
return DBI
->connect("DBI:XBase:$conf_xbase{'dir'}") or die $DBI::errstr;
}
# Соединение с MySQL
sub mysql_connect_1 {
my $dsn = "DBI:mysql:$conf_mysql{'base'}:$conf_mysql{'host'}";
return DBI
->connect($dsn, $conf_mysql{'user'}, $conf_mysql{'pass'}) or die $DBI::errstr;
}