Oomoo

March 5, 2008

Using Legacy Table in Rails

Filed under: Uncategorized — oomoo @ 11:09 pm

Example of using Legacy Tables in Rails

Using “legacy” data does not follow the easy Rails way of doing things, but hey, life never seems to be that perfert most of the time. Since nobody handed me a napkin with a design and say “hey, take this and go learn Ruby/Rails you crazy kid”, I have had to learn using tables that are currently still in use in a lan-based application. This is learning the “old fashioned way” (in the snow), which involves falling down a lot and taking a frustratingly long time to do even seemingly simple tasks. But, I have gotten to peak behind the curtain a bit at things I otherwise wouldn’t have cared about.

Rails is cool because it hides much of the whackiness of web programming
from you, but it is also frustrating if you think you need to see those details. Usually, you don’t really need to see the details, its just that you are ignorant and seeing the details gives us anal-retentive types some false feeling of control. It’s like switching between a Windows PC and a Mac. Everytime I use a Mac, my heart skips a beat when I can’t bring up Explorer and see all the nitty gritty details of every file on the hard drive. But after time, you realize you don’t need to see all that crap, because guess why… it just works.

Do you wanna know the dirtly little secret
about using legacy data with Rails (and ActiveRecord)? It ‘aint hard!! That’s right, I said it. ActiveRecord is actually flexible enough that you just need to specify the “table name” and “primary key” of your table in your Model.

You can still use all the “generators” and “rake tools”.
You will just probably need to tweak the output. The Rails “pluralizer” will assume your table named “Company” is actually named “companies”. That’s cool, just generate the models and controllers the usual way, then change the file names, model classname, and controller classname name back to what matches your actual table. You will ‘educate’ (or ‘edumacate’ as we say in Georgia) ActiveRecord about this change as shown below.

You can turn off pluralization
(for your whole project) by subclassing ActiveRecord and forcing your Models to use your subclass, but this seems drastic to me and can have nasty side-effects on some plugins that use ActiveRecord. What is Pluralization? Here’s a good description: http://www.slash7.com/articles/2005/11/17/rails-howto-pluralizing
 
Here is a real example of some tables that I was dealing with (and these are actually pretty clean compared to some)

MySQL tables
 
tblorders
   tblcustomers
   These are MyISAM (not InnoDB) tables.
   They have no foreign keys.
   They do have auto-incrementing Integer primary keys (yippeee).
    I did create a regular index on tblorders.ordCustPK
Primary keys
  tblorders.ordPK          (notice the capitalization, MySQL is case-sensitive)
  tblcustomers.CustPK (notice the capitalization, MySQL is case-sensitive)
The Relationship   (not enforced/constrained in database)
  tblorder.ordCustPK —refers to—> tblcustomers.CustPK
     …many orders…                                  …one customer…

Models
\app\models\tblorder.rb
class Tblorder < ActiveRecord::Base
  set_table_name
“tblorders”   <- table_name
  set_primary_key “ordPK”      <- primary_key
 
belongs_to
:tblcustomer , :foreign_key => “ordCustPK”   <- fk

  def self
.find_all_orders
    find(
:all , :order => “ordPK”   <- pk
 
end
  def self
.find_recent_orders
    find_by_sql(
“SELECT * FROM tblorders
                where year(ordDateOrderTaken) = year(current_date)
                order by ordPK desc”
)
 
end
end
class Tblcustomer < ActiveRecord::Base
  set_table_name
“tblcustomers”   <- table_name
  set_primary_key
“CustPK”        <- primary_key
  
  has_many
:tblorders , :foreign_key => “ordCustPK”   <- :fk
end
Controller
#\app\controllers\tblorders_controller.rb

#- Index ————————-
def index
  list
  render
:action => ‘list’
end

#- List ————————–
def list
  @tblorders = Tblorder.find_recent_orders
end

#- Show ————————–
def show
  @tblorder = Tblorder.find(params[ :ordPK ])   <- pk
end

#- Edit ————————–
def edit
  @tblorder = Tblorder.find(params[ :ordPK ]])   <- pk
end

#- Update ————————
def update
  @tblorder = Tblorder.find(params[ :ordPK ]])   <- pk

  if @tblorder
.update_attributesparams[ :tblorder ]])
    flash[
:notice ] = ‘Successfully updated.’
   
redirect_to :action => ‘show’ , :ordPK => @tblorder   <- pk
 
else
   
render :action => ‘edit’
  end
end

View
  Order #: <% = @tblorder.ordPK %>&<br />
  Status: <%= @tblorder.ordStatus %> &<br />
  Customer: <%= @tblorder.tblcustomer.CustName %> &<br />   <- RELATED DATA!

For a different take on this subject (including using non-incrementing primary keys), see the excellent tutorial at
http://sl33p3r.free.fr/tutorials/rails/legacy/legacy_databases.html
More information can be found at:
http://www.robbyonrails.com/articles/2005/07/25/the-legacy-of-databases-with-rails

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: