MySQL到SQLite的数据转换

下面是Perl的程序代码:

#!/usr/bin/perl -w
use strict;

use DBI;
use Getopt::Std;

use vars qw(%opts);
getopts('u:s:d:h',/%opts);
my ($user,$host,$db,$help) = parse_args(/%opts);

USAGE() and exit unless $user and $host and $db and not $help;

my $table = join(' ',@ARGV);
open(DUMP, "mysqldump -u $user -p -h $host $db $table |");
my $sql = do {local $/; };

$sql =~ s/^#.*$//mg;              # chokes on comments
$sql =~ s/auto_increment//g;      # on 'auto_increment'
$sql =~ s/TYPE=/w ;/;/g;          # and on 'TYPE=____'
$sql =~ s///'/''/g;               # and on escaped '

my @table = $sql =~ /CREATE/s TABLE/s (/w )/g;
print "creating tables: ",join(' ',@table),"/n";

my $dbh = DBI->connect(
   ("DBI:SQLite:dbname=$db.dbm"),
   {RaiseError=>1}
);

$dbh->do($sql);

sub parse_args {
   my %opt = %{ shift};
   return @opt{qw(u s d h)};
}

sub USAGE {print "USAGE: $0 -u user -s server(host) -d database/n"}

=pod

=head1 NAME

mysql2sqlite.pl - MySQL database migration script

=head1 DESCRIPTION

This is a simple Perl DBI script for use with the MySQL
and SQLite database drivers. The script opens a pipe to
the mysqldump program to retrieve CREATE and INSERT
statements for the specified tables. This data is then
munged to conform with SQLite, and then fed to a dbm
file named the same as the database.

=head1 SYNOPSIS

 ./mysql2sqlite.pl -u user -s host -d dbase table1 table2 table3

This will create a dbm named 'dbase.dbm' with three tables 
(table1, table2, table3) provided that they all exist in 
the MySQL database. If tables are not supplied, then ALL 
TABLES in the database will be migrated. If a table already
exists in the dbm file, then the script will stop execution
before that table's data is migrated (simplicity vs. 
robustness, i chose simplicity).

=head1 LEGAL STUFF

Mi casa su casa, but if you get hurt or someone gets hurt
from this casa, then it's your casa, not mine.

=cut

对于如何处理Mysql中的ENUM/SET类型,则可以参考下面的代码:

sub JeffasMysql2SQLite {
    my @Ssql = split //n/,shift(@_);
    my %index;
    my @tablename;

    for my $sql( @Ssql ){
        push @tablename, $1 if $sql =~ m/create /s  table /s  (/w ) /i
 x;

        $sql =~ s/^#.*$//mg;              # chokes on comments
        $sql =~ s{^/s*?(KEY(?:/s*/w /s*)?/(.*)}{
                    push @{$index{$tablename[-1]}},$1;
                    "";
                }giemx;
        $sql =~ s/auto_increment//ig;      # on 'auto_increment'
        $sql =~ s/UNSIGNED//ig;
        $sql =~ s/TYPE=/w ;/;/gi;          # and on 'TYPE=____'
        $sql =~ s///'/''/g;               # and on escaped '

        $sql =~ s{^/s*?(/w )/s (?:SET|ENUM)/((.*)$}{ SetOrEnumToVarcha
 r($1,$2); }exig;
    }

    my $ret = join "/n",@Ssql;

    for my $tablename( @tablename ){

        for my $ix( @{ $index{$tablename} } ){
            $ix=~ s/[,/s] $//;

            if( $ix =~ //bKEY/((/w )/)/i ) {
                $ret.=" CREATE INDEX $1 on $tablename ($1);/n";
            } elsif( $ix =~ //bKEY/s (/w )/s /((.*?)/z/i ) {
                $ret.=" CREATE INDEX $1 on $tablename ($2;/n";
            }
        }
    }

    return $ret;
}

sub SetOrEnumToVarchar {
    my( $name, $val ) = @_;
    my $end   = substr $val, rindex($val,')')   1;
       $val   = substr $val, 0, rindex($val,')') - 1;
    my $q     = substr $val, 0, 1, "";
     ( $val ) = sort {
                    $b <=> $a
                } map {
                    s/^/"//;
                    s//"/$//;
                    length 
下面是Perl的程序代码:
#!/usr/bin/perl -w
use strict;

use DBI;
use Getopt::Std;

use vars qw(%opts);
getopts('u:s:d:h',/%opts);
my ($user,$host,$db,$help) = parse_args(/%opts);

USAGE() and exit unless $user and $host and $db and not $help;

my $table = join(' ',@ARGV);
open(DUMP, "mysqldump -u $user -p -h $host $db $table |");
my $sql = do {local $/; };

$sql =~ s/^#.*$//mg;              # chokes on comments
$sql =~ s/auto_increment//g;      # on 'auto_increment'
$sql =~ s/TYPE=/w ;/;/g;          # and on 'TYPE=____'
$sql =~ s///'/''/g;               # and on escaped '

my @table = $sql =~ /CREATE/s TABLE/s (/w )/g;
print "creating tables: ",join(' ',@table),"/n";

my $dbh = DBI->connect(
   ("DBI:SQLite:dbname=$db.dbm"),
   {RaiseError=>1}
);

$dbh->do($sql);

sub parse_args {
   my %opt = %{ shift};
   return @opt{qw(u s d h)};
}

sub USAGE {print "USAGE: $0 -u user -s server(host) -d database/n"}

=pod

=head1 NAME

mysql2sqlite.pl - MySQL database migration script

=head1 DESCRIPTION

This is a simple Perl DBI script for use with the MySQL
and SQLite database drivers. The script opens a pipe to
the mysqldump program to retrieve CREATE and INSERT
statements for the specified tables. This data is then
munged to conform with SQLite, and then fed to a dbm
file named the same as the database.

=head1 SYNOPSIS

 ./mysql2sqlite.pl -u user -s host -d dbase table1 table2 table3

This will create a dbm named 'dbase.dbm' with three tables 
(table1, table2, table3) provided that they all exist in 
the MySQL database. If tables are not supplied, then ALL 
TABLES in the database will be migrated. If a table already
exists in the dbm file, then the script will stop execution
before that table's data is migrated (simplicity vs. 
robustness, i chose simplicity).

=head1 LEGAL STUFF

Mi casa su casa, but if you get hurt or someone gets hurt
from this casa, then it's your casa, not mine.

=cut
 
对于如何处理Mysql中的ENUM/SET类型,则可以参考下面的代码:
___FCKpd___3
;
                }   split /$q,$q/, $val;

#warn "/n/t/tname $name/n/t/t val $val/n/t/t end $end/n/t/t   q $q/n/t
/t";
    return "$name VARCHAR($val) $end";
}