I recently found myself Googling for some data voodoo. I have a web app which 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 <100 syntax errors in the output, so I had to get creative. Here is what I have 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 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 have the structure cat‘d on to the start and imported into SQLite.