MySQL, Ruby, Rails, and enumerated types

topic posted Mon, February 25, 2008 - 9:46 PM by  Bob
Here's a little something I discovered that might help people out. Especially if you're just getting started with RoR.

I was creating a site for a friend where he needs users to fill out a form with pertinent info: first/last/email/address, etc. So I set out creating a model, and realized when I got to the address that checking to make sure the $state entry was a valid, two letter, USPS-approved, state abbreviation was going to be a pain in the butt someday.
So I asked myself, "Well, doesn't MySQL have enumerated types? Maybe I can just make $state only be one of the abbreviations." Sure enough, with a little checking I found out that MySQL does.
Second thought: "Does RoR know?" With a little checking....it didn't. Well, not really. I found a patch that was kinda old, but after checking with a seasoned RoR friend, he pointed me to a plugin for enumerated types here:
enum-column.rubyforge.org

Following the directions, using
script/plugin install svn://rubyforge.org/var/svn/enum-column/plugins/enum-column
the plugin installed cleanly!

The two examples I saw of using :enum used different syntax for the enum types, however. Apparently you can use either. When you're creating the enumerated types, you can use: [ :a, :b, :c, :d ] or [ 'a', 'b', 'c', 'd' ]. I chose the latter, even though the plugin example uses the former, and it still worked.

So, here's my model schema for the db:
ActiveRecord::Schema.define(:version => 1) do

create_table "clients", :force => true do |t|
t.column "firstname", :string
t.column "lastname", :string
t.column "mail_address1", :string
t.column "mail_address2", :string
t.column "mail_city", :string
t.column "mail_state", :enum, :limit => [:AL, :AK, :AR, :AS, :AZ, :CA,
:CO, :CT, :DC, :DE, :FL, :FM, :GA, :GU, :HI, :IA, :ID, :IL, :IN, :KS, :KY, :LA,
:MA, :MD, :ME, :MH, :MI, :MN, :MO, :MP, :MS, :MT, :NC, :ND, :NE, :NH, :NJ, :NM,
:NV, :NY, :OH, :OK, :OR, :PA, :PR, :PW, :RI, :SC, :SD, :TN, :TX, :UT, :VA, :VI,
:VT, :WA, :WI, :WV, :WY]
t.column "mail_zip", :string
end

end

push the migration to the db:
rake db:migrate

create your admin controller:
ruby script/generate controller admin

create a scaffold:
class AdminController < ApplicationController
scaffold :client
end

start your server:
ruby script/server

and check your /admin page - it will present you with text boxes for everything, except the state - which will be a drop-down selection box! Nifty!
posted by:
Bob
offline Bob
Denver
  • Re: MySQL, Ruby, Rails, and enumerated types

    Wed, February 27, 2008 - 4:00 PM
    shouldnt your example really be handled outside the db in configuration?

    If you were to store this in db why not just use a m2m relationship instead of an enum? I know youre going to take a hit on performance but it might prove better in the long run (what if they decide they need all the chinese provinces in a year or two?). Granted I assume the model migration tools in RoR would help mitigate the complications....


    (NOTE: I dont touch rails that much... im a PHP nerd by choice, but most of the same patterns and alot of the same features are implemented, albeit sometimes differently, in the php framework i use.)
    • Bob
      Bob
      offline 3

      Re: MySQL, Ruby, Rails, and enumerated types

      Fri, February 29, 2008 - 7:53 AM
      Yeah, I thought about that. Though for quite a few little "shop" sites people have, I doubt most of them want to deal with overseas/international transactions.

      Plus, I thought it was cool that it automatically creates a drop-down menu populated with all the types automatically!

      But you're right; I think this method would make migrations (specifically, changes to the enum values) a real pain.

Recent topics in "Ruby on Rails"

Topic Author Replies Last Post
Offer to moderate Bob 0 February 25, 2008
Looking for rails developer James 0 December 13, 2007
RailsConf b3gl 1 May 19, 2007
Ruby On Rails BZ 0 March 17, 2007
Rails and Beer Greg 2 March 13, 2007