SQLite to MySQL

Migrating a database dump from SQLite to MySQL can be somewhat of a hassle. After various trials I came up with the following that worked for me quite well:

echo .dump | sqlite3 mydb.db |\
  egrep -v "\b(BEGIN TRANSACTION|COMMIT|PRAGMA|sqlite_sequence)\b" |\
  perl -pe 's/^([^'\''"]*)"([^"]+)"/\1`\2`/' |\
  perl -pe 's/\bautoincrement\b/auto_increment/' |\
  mysql -uroot mydb

The most important line is probably line 3: SQLite encapsulates all table and column names in double quotes and this is not understood by MySQL, so we’re replacing them with backticks. One must be careful here, though, because data INSERTs might also contain double quotes and to avoid having these wrongly replaced as well we only replace them until we encounter the first single quote (these are used in SQLite to encapsulate strings).

Line 3 might also have to be called several times in a row in case multi-column indexes are part of the schema as well, as it will only replace the very first, but no subsequent matches. (I couldn’t get zero-width lookbehind to work properly to remedy this problem; help is welcome.)