Skip to main content
  1. Blog Archive/

Migrating Typo from MySQL over to SQLite

I've moved this blog installation from my locally hosted MythTV server over to the recoil.org box we've designated for hosting dynamic content. In doing so, I've re-hosted all of the content from MySQL to SQLite, as this is going to be easier to maintain in the future in a secure and simple way.

In doing this, I've discovered that there's apparently no neat way of doing this through the current Rails rake tasks. Eventually I've settled on preparing a blank SQLite database via:

rake db:schema:load

And then running the following script, which was modified from something found on the SQLite trac installation:

cp db/typo_blank.db /tmp/temp.db ; /usr/local/mysql/bin/mysqldump --no-create-info --compact --extended-insert=FALSE --quote-names=FALSE --complete-insert -u root -pYOURROOTPASSWORD typo | perl -pe '
        if (/^(INSERT.+?)\(/) {
                $a=$1;
                s/\\'\''/'\'\''/g;
                s/\\\"/"/g;
                s/\\n/\n/g;
                s/\\r/\r/g;
                s/\),\(/\);\n$a\(/g;
        }
' | sqlite /tmp/temp.db;  mv /tmp/temp.db db/typo_dev.db

And the last thing I found I needed to do, which may or may not have something to do with being on the SVN trunk:

UPDATE contents SET published='t';

After the migration, the contents.published column seemed to contain a 1 instead of a 't', which is related, I assume, to the way the differing systems choose to store boolean values

Nick Ludlam
Author
Nick Ludlam
Software Developer and Prototyper in Mixed Media