Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/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;
}
'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.