Proyecto

General

Perfil

Descargar (11,7 KB) Estadísticas
| Revisión:

root/abiesweb/mdb2mysql @ 520

471 pkom
#!/usr/bin/perl
##########
#
# File: mdb2mysql
#
# Created By: Bill Lewis (bill@enobis.com)
# Created On: Aug 5, 2004
#
# Description: Perl script to convert MS Access (mdb) files to an import
# schema suitable for MySQL.
#
# Requirements: Perl (obvioulsy)
# mdbtools (mdbtools.sourceforge.net)
#
# This is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free
# Software Foundation. You should have received a copy of the GNU
# General Public License along with this software; if not, visit:
# http://www.gnu.org/copyleft/gpl.html
#
# Revision History:
#
# v1.0 - First version released for use on Oct 14, 2004
#
#############################################################################
use strict;
use Getopt::Std;

my $VERSION = "MDB2MySQL v1.0";
my $BASEDATOS = "USE abies;";

##########
#
# Initialize Execution Environment
#
#############################################################################
our %opts;

# Set defaults...
$opts{"r"} = "_";
$opts{"U"} = "blob";
$Getopt::Std::STANDARD_HELP_VERSION=1;

getopts('cdeilhsuxMVo:r:t:U:',\%opts);

if($opts{"h"}) { &VERSION_MESSAGE; &HELP_MESSAGE; exit; }
if($opts{"V"}) { &VERSION_MESSAGE; exit; }

if(@ARGV < 1) { &VERSION_MESSAGE; &HELP_MESSAGE; exit; }
my $dataFile = $ARGV[$#ARGV];

if($opts{"x"})
{
$opts{"d"} = 1;
$opts{"e"} = 1;
$opts{"l"} = 1;
}

my $delim = "__zz__";

my %mdbversions = ( "JET3" => "Access 97", "JET4" => "Access 2000/XP" );

##########
#
# MDB2MySQL Header Information
#
#############################################################################

# Get MDB File Version
open(VER,"mdb-ver $dataFile|") || die "Error Reading MDB File: $!\n";
my $dataFileVersion = <VER>;
close(VER);
chop $dataFileVersion;
$dataFileVersion .= " (".$mdbversions{$dataFileVersion}.")";

# Print Header Information

print $BASEDATOS;
print "\n\n";

##########
#
# Script body
#
#############################################################################

# Get List of Tables
my @tables;
my $mdbtables = "mdb-tables -d".$delim;
if($opts{"M"}) { $mdbtables .= " -S"; }

if($opts{"t"})
{
@tables = split(/,/,$opts{"t"});
}
else
{
open(TABLES,"$mdbtables $dataFile|") || die "Error Reading Tables: $!\n";
if(!$opts{"M"})
{
$_ = <TABLES>;
chop;
@tables = split(/$delim/);
}
else
{
while(<TABLES>)
{
chop;
push(@tables,$_);
}
}
close(TABLES);
if($opts{"o"})
{
my %hash = ();
foreach (@tables,split(/,/,$opts{"o"})) { $hash{$_}++; }
@tables = ();
foreach (keys %hash) { if($hash{$_} == 1) { push(@tables,$_); } }
}
}

# Loop through the tables to build the MySQL import/SQL format
my @headers;
my ($tbl,$record,$firstrecord,$first,$multirow,$startmulti,$endmulti,$values);
my $mdbexport = "mdb-export -d".$delim." %s %s|";
foreach $tbl (@tables)
{
if(!$opts{"i"}) { &createTableSchema($dataFile,$tbl); }

if($opts{"c"}) { next; }

next if ($tbl eq 'Politica');

print "--\n-- Dumping data for table \'$tbl\'\n--\n\n";

# Get Table Data Records
open(RECORDS,sprintf($mdbexport,$dataFile,$tbl)) ||
die "Error Exporting Record Data: $!\n";

# Get Headers in case of future development/features...
foreach (split(/$delim/,<RECORDS>))
{
s/[^a-zA-Z0-9_\$]/$opts{"r"}/g;
push(@headers,$_);
}

$firstrecord = 1;
$multirow = $startmulti = $endmulti = 0;
if($opts{"l"}) { print "LOCK TABLES $tbl WRITE;\n"; }
if($opts{"e"}) { print "INSERT INTO $tbl VALUES "; }
while(<RECORDS>)
{
if(!$multirow)
{
chop;
$first = 1;
$values = "";
}
foreach (split(/$delim/))
{
if(!$multirow)
{
# Strip Quotes from both sides of data value
if(substr($_,0,1) eq '"' && substr($_,-1,1) eq '"')
{
$_ = substr($_,1,$#_);
}
# Strip out quotes from both sides on an unchoped row resulting
# from a multirow data record.
elsif(substr($_,0,1) eq '"' && substr($_,-2,1) eq '"')
{
$_ = substr($_,1,$#_-1);
}
# Check to see if this data value is the start of a multirow
elsif(substr($_,0,1) eq '"')
{
$_ = substr($_,1);
$multirow = 1;
$startmulti = 1;
}
}
else
{
# Check to see if this data is the end of a multirow
# Start by verifying that a quotation mark at the end of the
# line is not part of the data...
if(substr($_,-2) ne '""' || substr($_,-3,2) ne '""')
{
if(substr($_,-1,1) eq '"')
{
$_ = substr($_,0,$#_);
$multirow = 0;
$endmulti = 1;
}
elsif(substr($_,-2,1) eq '"')
{
$_ = substr($_,0,$#_-1);
$multirow = 0;
$endmulti = 1;
}
}
}

# Need to check if the field is a date type
# and convert to acceptable MySQL format if so...
if(!$multirow && !$endmulti)
{
if(/^(\d{1,2})\/(\d{1,2})\/(\d{2,4})\s+(\d{1,2}):(\d{1,2}):(\d{1,2})/ ||
/^(\d{1,2})\/(\d{1,2})\/(\d{2,4})/)
{
$_ = sprintf("%s%02s%02s%02s%02s%02s",$3,$1,$2,$4,$5,$6);
}
}

# Escape and convert certain characters for MySQL format
s/\\/\\\\/g;
s/'/\\'/g;
s/""/"/g;
s/"/\\"/g;

# Create MySQL format for dump values
if(!$multirow)
{
if(!$first && !$endmulti) { $values .= ","; }

if(!$endmulti) { $values .= "'".$_."'"; }
else { $values .= $_."'"; $endmulti = 0; }
}
else
{
if($startmulti)
{
if(!$first) { $values .= ","; }
$values .= "'".$_;
$startmulti = 0;
}
else
{
$values .= $_;
}
}

$first = 0;
}
if(!$multirow)
{
if($opts{"e"})
{
if(!$firstrecord) { print ","; }
print "($values)";
$firstrecord = 0;
}
else
{
printf("INSERT INTO %s VALUES (%s);\n",$tbl,$values);
}
}
}
if($opts{"e"}) { print ";\n"; }
if($opts{"l"}) { print "UNLOCK TABLES;\n\n"; }

close(RECORDS);
}

exit;

##########
#
# Subroutine: createTableSchema
#
# Description: Creates the table structure in MySQL format.
#
# Arguments: $mdbFile - MDB Database file containing table
# $table - the MDB Database table
#
# Return: void
#
#############################################################################

sub createTableSchema
{
my ($colsDefinition);
my ($mdbFile,$table) = @_;
my $mdbschema = "echo \"DESCRIBE TABLE \"".$table." | mdb-sql ".$mdbFile;

# Get Table Schema for the given table
open(SCHEMA,"$mdbschema|") || die "Error Reading Table Schema: $!\n";
while(<SCHEMA>)
{
chop;
s/\s+//g;
if(/^\|(\S+)\|(\S+)\|(\d+)\|/)
{
$colsDefinition .= &convertColumnType($1,$2,$3);
}
}
close(SCHEMA);
chop $colsDefinition;
chop $colsDefinition;

print "--\n-- Table structure for table \'$table\'\n--\n\n";
if($opts{"d"}) { print "DROP TABLE IF EXISTS $table;\n"; }
print "CREATE TABLE $table (\n";
print $colsDefinition, "\n";
print ");\n\n";
}

##########
#
# Subroutine: convertColumnType
#
# Description: Converts the MDB (MS Access) column data type to the
# corresponding MySQL data type and creates the column
# structure.
#
# Arguments: $field - MDB Database column/field name
# $type - MDB Database data type
# $size - MDB data type size
#
# Return: The schema for the converted column data type.
#
#############################################################################

sub convertColumnType
{
my $def;
my ($field,$type,$size) = @_;

$field =~ s/[^a-zA-Z0-9_\$]/$opts{"r"}/g;

$def = " ";
if($type eq "Text")
{
if($size > 2)
{
$def .= sprintf("%s varchar(%d) NOT NULL default '',\n",$field,$size);
}
else
{
$def .= sprintf("%s char(%d) NOT NULL default '',\n",$field,$size);
}
}
elsif($type eq "Memo/Hyperlink")
{
$def .= sprintf("%s text NOT NULL default '',\n",$field);
}
elsif($type eq "Byte")
{
$def .= sprintf("%s tinyint NOT NULL default 0,\n",$field);
}
elsif($type eq "Integer")
{
$def .= sprintf("%s smallint NOT NULL default 0,\n",$field);
}
elsif($type eq "LongInteger")
{
$def .= sprintf("%s int NOT NULL default 0,\n",$field);
}
elsif($type eq "Single" || $type eq "Double")
{
$def .= sprintf("%s double NOT NULL default 0,\n",$field);
}
elsif($type eq "Numeric")
{
$def .= sprintf("%s float NOT NULL default 0,\n",$field);
}
elsif($type eq "Currency")
{
$def .= sprintf("%s decimal(10,2) NOT NULL default '0.00',\n",$field);
}
elsif($type eq "DateTime" || $type eq "DateTime(Short)")
{
$def .= sprintf("%s datetime NOT NULL default '0000-00-00 00:00:00',\n",
$field);
}
elsif($type eq "Boolean" || $type eq "Bit")
{
$def .= sprintf("%s enum('1','0') NOT NULL default '1',\n",$field);
}
elsif($type eq "ReplicationID")
{
$def .= sprintf("%s tinyblob NOT NULL default '',\n",$field);
}
elsif($type eq "OLE")
{
$def .= sprintf("%s longblob NOT NULL default '',\n",$field);
}
else
{
if($opts{"u"})
{
print "??? Unknown Access/MDB Field Data Type!\n";
print "??? Field: $field\n";
print "??? Data Type: $type($size)\n";
print "??? Resolution Options:\n";
print "??? 1. Change the field to a known data type within Access\n";
print "??? 2. Let MDB2MySQL use a known MySQL data type instead.\n";
print "??? The default replacement is a 'blob' but can be\n";
print "??? changed using the -U flag.\n";
exit;
}
else
{
$def .= sprintf("%s %s NOT NULL default '',\n",$field,$opts{"U"});
}
}

return $def;
}

##########
#
# Subroutine: VERSION_MESSAGE
#
# Description: Displays the version message. Complies to the Getopts
# perl module.
#
# Arguments: none
#
# Return: void
#
#############################################################################

sub VERSION_MESSAGE()
{
print $VERSION, "\n";
print "Created By: Bill Lewis (bill\@enobis.com)\n";
print "GNU General Public License (http://www.gnu.org/copyleft/gpl.html)\n";
}

##########
#
# Subroutine: HELP_MESSAGE
#
# Description: Displays the help message. Complies to the Getopts
# perl module.
#
# Arguments: none
#
# Return: void
#
#############################################################################

sub HELP_MESSAGE()
{
print "\nUsage: mdb2mysql [options] <mdb file>\n";
print " -c Create table structure only, no data.\n";
print " -d Add a 'drop table' before each create.\n";
print " -e Use the much faster, extended INSERT syntax.\n";
print " -i Export data inserts only.\n";
print " -l Add locks around insert statements.\n";
print " -o <tables> Omit tables in this comma seperated list.\n";
print " -r <character> Replace illegal characters with given character.\n";
print " The default character is an underscore.\n";
print " -t <tables> Export only this list of comma seperated tables.\n";
print " -u Report unknown Access data type and exit.\n";
print " -x Same as using -d -e -l combined options.\n";
#print " -M Convert Access System tables ('MSys') as well.\n";
print " -U <type> Use the MySQL data type for unknown Access types.\n";
print " Unless given, 'blob' will be used by default.\n";
print " -h, --help This message and exit.\n";
print " -V, --version Output version information and exit.\n";
}