Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

MySQL 2 SQLite

by jeffa (Bishop)
on Mar 09, 2002 at 01:14 UTC ( [id://150476]=sourcecode: print w/replies, xml ) Need Help??
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);
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(


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"}


=head1 NAME - MySQL database migration script


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.


 ./ -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).


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

Replies are listed 'Best First'.
Re: MySQL 2 SQLite
by PodMaster (Abbot) on Feb 03, 2003 at 17:03 UTC
    Hi ;D

    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"; }
    I'm not sure what to do yet with ENUM/SET TYPES , since ANSI SQL92 does not support it, I guess i'll convert them to VARCHAR(n), where n is the length of the longest value.

    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.

      PODMASTER wrote me earlier to tell me about SQLite and this post. I've since added a (basically functioning) SQLite producer for the SQL::Translator modules. You'll have to look in CVS as this won't be included in the CPAN release until v0.02: With this, you should be able to convert MySQL and PostgreSQL schemas to SQLite. Probably there will be things to fix. Patches welcome! ky

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: sourcecode [id://150476]
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2024-05-28 16:36 GMT
Find Nodes?
    Voting Booth?

    No recent polls found