|
#!/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";
|
|
}
|