Allow custom column types in your Rails migrations
posted: April 7th, 2007 · by: Sven
We’ll all admit it. Rails’ migrations rock. Totally.
But when you try to change a database column to some vendor specific type (like varbinary for MySQL) that Rails doesn’t implement you notice that Rails doesn’t hand over control that easily here like it does elsewhere in ActiveRecord. You’re just not supposed to use any custom types.
This article explains how to easily change that behaviour.
There’s a workaround that allows you to append column definitions at the end (!) of your table definition:
class CreateForms < ActiveRecord::Migration
def self.up
create_table :cards do |t|
# ...
t.columns << 'value varbinary(255)'
end
end
endObviously this might not be what everbody really wants. If you’re like me you prefer to keep your stuff tidy … and the column order in my tables therefor definitely matters.
No problem: Rails plugins to the rescue here. After some digging through the ActiveRecord source it turns out that it’s pretty easy to convince Rails to accept not only predefined column types (like :string) but also wildly arbitrary strings.
So, how about the following code?
ActiveRecord::ConnectionAdapters::SchemaStatements.module_eval do
def type_to_sql_with_custom_type(type, *params)
return type unless native_database_types.has_key? type
type_to_sql_without_custom_type(type, *params)
end
alias_method_chain :type_to_sql, :custom_type
end
SchemaStatements#type_to_sql tries to look up the column type (like :string) and throws an error if it doesn’t find it in the native_database_types. What we’re doing with that unbelievable hack above is nothing else than silently navigate our custom column definition around this face check so that it gets inserted into the resulting SQL code literally.
So with this (not so) ugly hack plugin applied you can now do things like this:
class CreateForms < ActiveRecord::Migration
def self.up
create_table :cards do |t|
t.column :value, 'varbinary(255)'
end
end
end… and use whatever column order you like :-)
Go, get it
Yes, you can download this stuff. Standing in your Rails home directory point your script/plugin installer to:
script/plugin install http://svn.artweb-design.de/stuff/rails/migration_custom_column_types/
jujudellago said February 14th, 2008 at 10:10 AM ¶
Sven !
you saved me again !! you are as cool as Fonzie ! I had a bug with a system that imports datas with excel sheets, values from the sheet are compared to the database before save to avoid duplications, and it gave me errors like
Mysql::Error: #HY000Illegal mix of collations (latin1swedishci,IMPLICIT) and (utf8generalci,COERCIBLE)
quickly found out that a binary field could help this, searched a bit, back to your blog, and tadaaaa :)
thanks a lot !
will definitely buy you a big beer next time I come to berlin !
cheers
Sven said February 16th, 2008 at 02:38 PM ¶
Hey jujudellago!
Fine, be sure to drop me a note when you come to Berlin next time and we’ll be having some beer and fun :)
jujudellago said February 20th, 2008 at 07:08 PM ¶
That’s for sure ! and soon I hope :)
I just had to digg into the mysql docs to solve a little problem using this plugin, for a simple search form based on a sql query. As it might be useful to someone who’ll read this article, I’ll post it here…
I used to have a sql condition like this in my controller:
sq=params[:search].downcase sql_conditions=”lower(items.name) like ‘%#{sq}%’ ”
works good anywhere I use the regular VARCHAR type, but it doesn’t work with VARBINARY types, a conversion is needed for that, so I used the CONVERT mysql function:
sq=params[:search] sql_conditions=”CONVERT(items.name USING utf8) like ‘%#{s}%’ ”
not even need to downcase searched word, or “lower” in mysql, the search becomes case insensitive :)
cheers
Sven said February 21st, 2008 at 12:35 PM ¶
Thanks for posting this tip here, jujudellago.
I’m sure this will prove useful :)
Dave said March 24th, 2008 at 05:19 PM ¶
Great tips! Does this work with the new Rails 2.0 migration formats?
Sven said May 11th, 2008 at 06:16 PM ¶
Hi Dave,
if you mean by “new Rails 2.0 migration formats” the “sexy” migrations a la
t.string :title
… then, no, that won’t work. It might be an interesting idea to extend the plugin for that if possible.
If you mean Rails edge/2.1’s timestamped migrations then, yes, that works.
hth :)
Mark C said April 14th, 2010 at 05:41 PM ¶
Rails migrations blow. Totally.
Seriously, how afraid of SQL do you need to be to hand over control over your database schema to Rails? It’s pathetic.