MySQL 2 SQLite

Category:	Database Migration
Author/Contact Info	/msg jeffa
Description:	I am excited about DBD::SQLite (thanks again, Matts)!! Instead of peeing my pants over it, i wrote a very simple migration script for use with a MySQL database. I plan on using this to help with the creation of demo sites (see Re: Would you use SQLite?). Comments and suggestions are always welcome. Error handling could be better ... but it works for me. I am more interested in improving the process of filtering out the MySQL specific SQL so SQLite doesn't choke on them, as well as being able to migrate from other database vendors. (And shame on me for shoving the entire output of mysqldump into DBI's do() method as a scalar!) Written for Linux, sorry Windows programmers. UPDATE: (Wed Mar 5 09:04:52 CST 2003) Indeed Podmaster ... go SQL Fairy!!!! :D
#!/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 $/; <DUMP>}; $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

Comment on MySQL 2 SQLite Download Code

Replies are listed 'Best First'.

Re: MySQL 2 SQLite
by PodMaster (Abbot) on Feb 03, 2003 at 17:03 UTC

I've abstracted some of yer conversion code into a subroutine. It's more complete (not by too much).

update: made it work for all my tables (much better than my initial version ;D)

# thanks to Jeffa ([id://150476|MySQL 2 SQLite])
# with improvements
# I'd use SQL::Parser, but it don't support Mysql
# so I gotta do it ghetto (works for all my tables ;)
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 $_;
                }   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";
}
[download]

update: Did it.

keyboard ENUM("dvorak","asdf","qwerty") NOT NULL DEFAULT "qwerty",

now translates to

keyboard VARCHAR(6) NOT NULL DEFAULT "qwerty",

update (Wed Mar 5 06:33:17 2003 GMT): Whoohooo, go SQL Fairy!!!! :D

MJD says you can't just make shit up and expect the computer to know what you mean, retardo!
** The Third rule of perl club is a statement of fact: pod is sexy.


"be consistent"
	PerlMonks