#!/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

[download]

In reply to MySQL 2 SQLite by jeffa

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.