nick.recoil.org

Rails DB migrations with clauses for SQLite or MySQL

When writing migrations, different databases have different syntaxes for certain aspects of table construction. SQLite doesn’t require you to specify the length of a key added to a column of type TEXT, but MySQL does.

If you’re using different databases for development and production, then writing one migration which copes with both databases is important. This can be done with the following code placed inside the migration file:

1
2
3
4
5
6


if ActiveRecord::Base.connection.adapter_name == "SQLite"
  add_index(:assets, :notes)
elsif ActiveRecord::Base.connection.adapter_name == "MySQL"
  execute "CREATE INDEX index_assets_on_notes ON assets (notes(50))"
end

Written on March 27th, 2008 @ 11:35 AM by Nick | Posted in | Permalink
Tagged with:

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

Written on May 7th, 2006 @ 12:47 AM by Nick | Posted in General, Ruby & Rails | Permalink
Tagged with:

Search

Sections

Friends and neighbours

Archives

About Nick

I am a freelance technology consultant and developer working in London, with a particular interest in web development and video media.

This site contains my thoughts about technology, the universe and everything. If you would like to get in contact, have a look at the About me page.