CODEWITHSUNDEEP
sqlitemysql
DEzra
DEzra

Reputation: 3038

script to convert mysql dump sql file into format that can be imported into sqlite3 db

I have an export SQL file containing tables and data from MySQL and I want to import it into a Sqlite 3 DB.

What is the best way to do that?

Just importing the file via the sqlite3 tool doesn't work.

Upvotes: 21

Views: 34773

Answers (9)

ucefkh
ucefkh

Reputation: 2531

This is the best written and well documented shell Script to convert ssql to .db

https://gist.github.com/esperlu/943776

or better use this tools It's amazing and fast ESF Database Migration Toolkit .

after having tried all the script here it didn't work until i did use the esf tool .

Note : 

Trial version add a 'T' to the begingn of each text value you have 
 But the pro version worked like a charm :)

Upvotes: 1

goelectric
goelectric

Reputation: 396

To convert databases with BLOBs in them I added --hex-blob to the mysqldump command and the following to the list of pipelined seds:-

sed -e "s/,0x\([0-9A-Z]*\),/,X'\L\1',/g" |

this replaces the mysql hex dump strings e.g. 0x010A…. with X’010a…’ for importing with sqlite.

Upvotes: 1

nfuria
nfuria

Reputation: 71

I tried some of these scripts that uses sed or awk, but always occurs an error, probably due to the indexes and foreign keys of my MySQL db and the mysqldump options needed.

Then I found the Perl module SQL::Translator "that converts vendor-specific SQL table definitions into other formats..."
This module create all foreign keys and correct the indexes, changing the names if necesssary.

So, I rewrite the shell script, including the dump of the MySQL db. There are two dumps because the script "sqlt" only generates the structure and works fast if the dump has no data. Note that it can be adapted to others conversions suported by the SQL::Translator.

After I posted this shell script I realized that the question is about to convert a MySQL dumpfile, so I did a Perl script that do that, using the module SQL::Translator. In my tests, I used a dumpfile generated without options (mysqldump -u user --password database > dumpfile). I had no problems with character sets.

In other test I had problem with mysql triggers, so I altered the scripts to skip it.

#!/bin/sh
#===============================================================================
#         USAGE: ./mysql2sqlite.sh <MySQL_database> <user>
#   DESCRIPTION: Converts MySQL databases to SQLite
#                Triggers are not converted
#  REQUIREMENTS: mysqldump, Perl and module SQL::Translator, SQLite 
#===============================================================================
if [ "$#" = 2 ]; then
    USER="$2"    
else
    echo "Usage: $0 <MySQL_database> <user>"
    exit
fi
if [ -s $1.db ]; then
    read -p "File <$1.db> exists. Overwrite? [y|n] " ANS
    if [ "$ANS" = "y" ] || [ "$ANS" = "Y" ] ; then
        rm $1.db 
    else 
        echo "*** Aborting..."
        exit
    fi
fi   
# extracts the necessary structure for SQLite:
mysqldump --skip-triggers --skip-add-locks --routines --no-data --compatible=ansi \
    --compact -u $USER --password $1 > /tmp/$1_$$_str.sql
# verify
if [ ! -s /tmp/$1_$$_str.sql ]; then
    echo "*** There are some problem with the dump. Exiting."
    exit
fi
# translates MySQL syntax structure to SQLite using the script "sqlt" of the
# perl module SQL::Translator (that corrects the foreign keys, indexes, etc.)
sqlt -f MySQL -t SQLite --show-warnings /tmp/$1_$$_str.sql \
    1> /tmp/$1_$$.sqlite 2> /tmp/$1_$$_sqlt.log
# verify
if [ ! -s /tmp/$1_$$.sqlite ]; then
    echo "*** There are some problem with the sql translation. Exiting."
    exit
fi
# adds statements to allow to load tables with foreign keys:
echo "PRAGMA foreign_keys=OFF;" >> /tmp/$1_$$.sqlite
echo "BEGIN TRANSACTION;" >> /tmp/$1_$$.sqlite
# extracts the data (simple inserts) without locks/disable keys, 
# to be read in versions of SQLite that do not support multiples inserts:
mysqldump --skip-triggers --no-create-db --no-create-info --skip-add-locks \
    --skip-extended-insert --compatible=ansi --compact -u $USER \
    --password $1 >> /tmp/$1_$$.sqlite
# adds statements to finish the transaction:
echo "COMMIT;" >> /tmp/$1_$$.sqlite
echo "PRAGMA foreign_keys=ON;" >> /tmp/$1_$$.sqlite
# correct single quotes in inserts
perl -pi -e ' if (/^INSERT INTO/) { s/\\'\''/'\'\''/g; } ' /tmp/$1_$$.sqlite
# load the sql file and generate the SQLite db with the same name 
# of the MySQL database
sqlite3 $1.db < /tmp/$1_$$.sqlite 2> /tmp/$1_$$sqlite.errlog
# verify
ERRORS=`cat /tmp/$1_$$sqlite.errlog | wc -l`
if [ $ERRORS = 0 ]; then
    echo "* Conversion complete. Verify the file < $1.db >"
    rm /tmp/$1_$$*
else
    echo "*** There are some problem. Verify the files < /tmp/$1_$$* >"
fi

Here the Perl script to convert a dumpfile in a SQLite database file. 

#!/usr/bin/perl 
#===============================================================================
#        USAGE: ./mysql2sqlite.pl <MySQL_dumpfile>
#  DESCRIPTION: Converts MySQL dumpfile to SQLite database
#               Triggers are not converted
#               The dump must be done with
#               > mysqldump --skip-triggers -u [user] --p [database] > dumpfile
# REQUIREMENTS: Perl and module SQL::Translator, SQLite
#===============================================================================
use strict;
use warnings;
use Carp;
use English qw( -no_match_vars );
use SQL::Translator;
use 5.012;

my $file = $ARGV[0];
my $filedb = $file;
$filedb =~ s/\.*[^.]*$/.db/;
if ( -s $filedb ) { 
    say "*** Ja existe o arquivo < $filedb >. Abandonando...";
    exit;
}
my @stru;
my @data;

open( my $SQLFILE, "<", $file )
    or croak "Can't open $file: $OS_ERROR";
while (<$SQLFILE>) {
    # nao considera linhas com comentarios e lock/unlock/drop
    next if ( /^--/ || /^\/\*/ || /^lock/i || /^unlock/i || /^drop/i );
    # processa os inserts
    if (/^(INSERT.+?)[(]/) {     
        my $ins = $1;            # captura o nome da tabela
        s/\\[']/''/g;            # substitue aspas simples - \'
        s/[)],[(]/);\n$ins(/g;   # divide multiplos inserts
        push( @data, $_ );
    }
    # processa a estrutura
    else { push( @stru, $_ ); }
}
close($SQLFILE);

my $strusql = join( '', @stru );
my $datasql = join( '', @data );
#open( my $STRU,   ">", "stru.sql" ); # to verify the results
#open( my $DATA,  ">", "data.sql" );
#print $STRU  $strusql;
#print $DATA  $datasql;

# here the conversion
my $translator = SQL::Translator->new(
    no_comments       => 0,
    show_warnings     => 0,
    quote_table_names => 1,
    quote_field_names => 1,
    validate          => 1,
);
my $struout = $translator->translate(
    from => 'MySQL',
    to   => 'SQLite',
    data => \$strusql,
    # filename => $file,
) or croak "Error: " . $translator->error;

# define inicio e final da transacao de inserts
my $prgini = "PRAGMA foreign_keys=OFF;\n";
my $traini = "BEGIN TRANSACTION;\n";
my $trafin = "COMMIT;\n";
my $prgfin = "PRAGMA foreign_keys=ON;\n";

#gera o arquivo final sqlite
my $sqlout = join( "\n", $struout, $prgini, $traini, $datasql, $trafin, $prgfin);
open( my $FINAL, ">", "/tmp/final.sql" );
print $FINAL $sqlout;

# Monta o SQLite database
my $log = "/tmp/sqlite.errlog";
my $command = "sqlite3 $filedb < /tmp/final.sql 2> $log";
system($command) == 0 or die "system $command failed: $?";
if ( -s $log ) { 
    say "*** Houve algum problema. Verifique o arquivo < /tmp/sqlite.errlog > "; 
}
else { 
    say "*** Conversao completa. Verifique o arquivo < $filedb > "; 
}

Upvotes: 5

Srinidhi
Srinidhi

Reputation: 290

At least, with mysql 5.0.x, I had to remove collate utf8_unicode_ci from mysql's dump before importing it to sqlite3. So I modified the script to include the following to the list of seds:

sed 's/ collate utf8_unicode_ci/ /g' |

Update:

MySQL treats boolean fields as "tinyint(1)", so I had to add the following before tinyint([0-9]*) sed:

sed 's/ tinyint(1) / boolean /g' |

Also, since I'm trying to replicate a mysql db (production) to a sqlite3 db (development) of a Ruby On Rails application, I had to add the following line in order to set an auto-incrementing primary key:

sed 's/) NOT NULL/) PRIMARY KEY AUTOINCREMENT NOT NULL/g' |

I'm still trying to figure out a way to change the KEY entries from mysql to its corresponding CREATE INDEX entry of sqlite3.

Upvotes: 1

amjoconn
amjoconn

Reputation: 2203

I had an issue with the mysql db being ISO-8859-1 (Latin-1). When did the conversion to sqlite3 assumed the data was UTF-8 resulting in decoding errors.

It was easy to fix with this:

iconv -f ISO-8859-1 -t UTF-8 mysql_dump_file > mysql_dump_file_utf8

Incase this helps someone.

Upvotes: 2

daicoden
daicoden

Reputation: 69

When the sqlite3 database is going to be used with ruby you may want to change:

tinyint([0-9]*)

to:

sed 's/ tinyint(1*) / boolean/g ' |
sed 's/ tinyint([0|2-9]*) / integer /g' |

alas, this only half works because even though you are inserting 1's and 0's into a field marked boolean, sqlite3 stores them as 1's and 0's so you have to go through and do something like:

Table.find(:all, :conditions => {:column => 1 }).each { |t| t.column = true }.each(&:save)
Table.find(:all, :conditions => {:column => 0 }).each { |t| t.column = false}.each(&:save)

but it was helpful to have the sql file to look at to find all the booleans.

Upvotes: 1

themattreid
themattreid

Reputation: 102

works fine on Centos 5.3 64bit. once you have the output file load it like so:

shell> sqlite3 file_name.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> .databases seq name file

0 main /current_directory/file_name.db
sqlite> select * from table; . . . . . results... sqlite>.quit

Upvotes: 0

Mike Fogel
Mike Fogel

Reputation: 3197

To get the above script to work, I made the following changes:

  1. run it with #!/bin/bash
  2. add two seds to the list of pipelined seds:
    • sed 's/\\r\\n/\\n/g'
    • sed 's/\\"/"/g'
  3. the 'rm tmp' line is a no-op (unless you have a file named 'tmp' lying around :O )

  4. my mysqldump command looked like this:

    $ mysqldump -u usernmae -h host --compatible=ansi --skip-opt -p database_name > dump_file

Then it worked nicely... thanks for the script.

Upvotes: 7

Igor
Igor

Reputation: 1258

This shell script help you

#!/bin/sh
if [ "x$1" == "x" ]; then
   echo "Usage: $0 <dumpname>"
   exit
fi
cat $1 |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed 's/ tinyint([0-9]*) / integer /g' |
sed 's/ int([0-9]*) / integer /g' |
sed 's/ character set [^ ]* / /g' |
sed 's/ enum([^)]*) / varchar(255) /g' |
sed 's/ on update [^,]*//g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
  if (/^(INSERT.+?)\(/) {
     $a=$1;
     s/\\'\''/'\'\''/g;
     s/\\n/\n/g;
     s/\),\(/\);\n$a\(/g;
  }
  ' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql
  rm $1.err
    rm tmp
else
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi

Upvotes: 25

Related Questions