mysql2sqlite.sh

#!/bin/sh
#
# This script is an adaptation from http://www.sqlite.org/cvstrac/wiki?p=ConverterTools
# It expects stdin input from mysqldump with the following params:
# mysqldump --skip-extended-insert --add-drop-table --compatible=ansi --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset
# Note: Don't use --skip-quote-names as this script expects quoted names
#
# Note: This script imports boolean values as they are stored in Mysql, e.g., as the integers 0 and 1.
#       As a result, boolean fields need to be further normalized after this transform step
#       such that false field values are 'f' and true field values are 't', which is
#       how SQLite's ActiveRecord adapter translates true/false.
#
# grep -v ' KEY "' |
# grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
grep -v -e '^SET ' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed "s/ tinyint(1) default '0'/ boolean default 'f'/g" |
sed "s/ tinyint(1) default '1'/ boolean default 't'/g" |
sed 's/ tinyint(1) / boolean /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' |

# The following inline ruby script shifts around indexes. MySQL creates indexes
# within the CREATE TABLE statement, sqlite needs them defined outside the
# CREATE TABLE statement.
ruby -e '
current_table = nil
indexes = []

while line = $stdin.gets
  line.chomp!
  case line
  when /^CREATE TABLE "([^"]+)"/i
    current_table = $1
    puts line
  when /^(\s*)UNIQUE KEY (.+?) (\(.+\))(,)?/i
    puts "#{$1}UNIQUE #{$3}#{$4}"
  when /^\s*KEY (.+?) (\(.+\))?/i
    idx_name, col_def = $1, $2
    if current_table
      indexes << "CREATE INDEX #{idx_name} ON \"#{current_table}\" #{col_def};"
    end
  when /^\);/
    puts line
    if current_table
      current_table = nil
      puts indexes.join("\n")
      indexes = []
    end
  else
    puts line
  end
end
' |
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;
}
'