I recently found myself Googling for some data wizardry. I have a web app which holds some data that I want to work with locally, and the RDBMS requirements are a little bit incompatible.

Unfortunately, neither this impressive sed script nor this eloquent mix of sed, ruby and perl could do this with fewer than 100 syntax errors in the output, so I had to get creative.

This blog post is just some quick notes on what I’ve learned.

Simplify the problem

I decided to convert the structure to SQLite manually, as it is not likely to change. The parts you will need to convert often (thousands of INSERT statements) are the parts which are more important to have a script for. The extra mysqldump options for getting the data only, without nasty backticks were:

--compatible=ansi --skip-extended-insert --compact --no-create-info

Use sed

I used sed to fix the escaping. MySQL escapes single quotes with \', and double quotes with \" but SQLite uses '' and "". This one-liner made the conversion:

sed -e "s/\'/''/g" -e 's/\"/"/g' db.sql > db.sqlite

The resulting file could be imported directly into SQLite, provided that the tables had already been defined.