Monday, January 14, 2013

Shell script to import MySQL dumpfile into SQLite

Download shell script: mysql2sqlite.sh


# Compiled by Polo Chau, by adapting Igor's StackOverflow answer on 
# http://stackoverflow.com/questions/489277/script-to-convert-mysql-dump-sql-file-into-format-that-can-be-imported-into-sqli

#!/bin/sh
if [ "x$1" == "x" ]; then
   echo ""
   echo "Usage: $0 "
   echo "This is a shell script that imports a MySQL dumpfile into an SQLite database."
   echo "Tested on a Mac with OS X 10.8.2 (late 2012)"
   echo "Creates 3 output files."
   echo "1) out.db  -- SQLite database created from the MySQL dumpfile"
   echo "2) out.sql -- a cleaned-up version of the MySQL dumpfile, readable by SQLite"
   echo "3) out.err -- SQLite's error messages generated during importation"
   echo ""
   exit
fi

cat $1 |

# delete the lines containing "KEY", "UNIQUE KEY", "PRIMARY KEY"
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |

# use sed to substitute mysql-specific keywords with general SQL keywords that sqlite knows
# tell sed to read the text as unicode text, by specifying LANG=en-US.UTF-8
LANG=en-US.UTF-8 sed '/^SET/d' |
LANG=en-US.UTF-8 sed 's/ unsigned / /g' |
LANG=en-US.UTF-8 sed 's/ auto_increment,/,/g' |
LANG=en-US.UTF-8 sed 's/ auto_increment/ primary key autoincrement/g' |
LANG=en-US.UTF-8 sed 's/ smallint([0-9]*) / integer /g' |
LANG=en-US.UTF-8 sed 's/ tinyint([0-9]*) / integer /g' |
LANG=en-US.UTF-8 sed 's/ int([0-9]*) / integer /g' |
LANG=en-US.UTF-8 sed 's/ character set [^ ]* / /g' |
LANG=en-US.UTF-8 sed 's/ enum([^)]*) / varchar(255) /g' |
LANG=en-US.UTF-8 sed 's/ on update [^,]*//g' |
LANG=en-US.UTF-8 sed 's/UNLOCK TABLES;//g' |
LANG=en-US.UTF-8 sed 's/LOCK TABLES `[a-zA-Z0-9]*` WRITE;//g' |
# tell perl to read the text as unicode text, by specifying -C
perl -C -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -C -pe '
  if (/^(INSERT.+?)\(/) {
     $a=$1;
     s/\\'\''/'\'\''/g;
     s/\\n/\n/g;
     s/\),\(/\);\n$a\(/g;
  }
  ' > out.sql

# import the cleaned-up version of mysql dump into out.db sqlite database
# errors saved to out.err
cat out.sql | sqlite3 out.db &> out.err