On the Floor

We recently moved some of our sites, and in the process moved the older sites off of sqlite3, in favour of mysql.  This sounds like a really annoying task, but it was actually rather painless.  I made use of the handy plugin yaml_db, which does most of the heavy lifting for you.

Here it is in 3 easy steps:

First step is to install the plugin: 

script/plugin install git://github.com/adamwiggins/yaml_db.git

The second step is to dump the database, using your current database.

rake db:dump

The final step (this one is tricky) is to build your new database with your old data.

Change your database.yml to your mysql connection.

Run rake db:load.

That's all there is to it! 

One final thought is to make sure you modify any database specific functions, such as "RAND" to their mysql counterparts "RANDOM"

 -- EDIT --

Ok, so I'm full of shit.

This works a lot of the time, but sometimes.... it doesn't.

I found another route, which is actually just a one liner:

sqlite3 db/development.sqlite3 .dump | perl db/convert.pl | mysql -uroot XXXXXXX_development

This assumes you are running from your project root, and have the convert.pl script in your db folder (and that you're using the root db user with no password).  Just put your database name in where the XXXX's are. 

The convert.pl script is a slightly modified version of a script I found on stackoverflow.com.  I needed to add a couple of regular expressions to clean up some improperly escaped single quotes, and allow for the indexes to be created. 

Here is my updated version:

This MAY work for you, or may need to be modified to catch whatever quicks are in your specific schema.  Download and save the file locally as convert.pl.

Now that's all there is to it.