views:

1072

answers:

4

Hello,

I'm trying to put a rails face on a legacy database. It is an old Sybase 11 database installation. I've gotten an ODBC connection working that uses unixODBC and FreeTDS to work. I'm also using the activerecord-odbc-adapter gem.

I've had to use set_table_name and set_primary_key to make it work so far. However, none of the dynamic find_by methods work. I always get a method missing error. Also, find through association doesn't work as well with the same error. Normal finds on the models work, but I was hoping I can shorthand some of this.

Am I SOL on this because it's a legacy DB or is there something I can do or check to make that work?

If I can, that would save me some work writing SQL.

Thank you.

Edit:

Console Output:

Person.find_by_last_name("Smith")


NoMethodError: undefined method `find_by_last_name' for Person(Table doesn't exist):Class
    from /opt/ruby-enterprise-1.8.6-20090201/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:1778:in `method_missing'
    from (irb):1


Person.find(:first, :conditions => { :last_name => "Smith" })

#<Person Person_ID: <redacted>, Title: "Mr.", First_Name: "Aaron", Middle_Name: "Michael", Last_Name: "Smith", Suffix: nil, Preferred_Name: nil

Further Edit:

I took a wild guess and capitalized Last_Name. That returned something. It looks like I'm going to have to do that. I still don't know how the association part would work though. That's still an issue.

+1  A: 

Does this work:

User.find(:all, :conditions => ['name = ?', "bob"])

... while this doesn't?

User.find_all_by_name("bob")

(This should be in a comment, but I'm new and can't comment yet:) A stack trace would help me dig through and see what's going on. What version of rails are you using?

wesgarrison
Correct. Normal finds work. I'm using 2.2.2. I will post what my console says when I try to do what I mentioned.
Robert Rouse
salt.racer has it. Nice patch he's got there, too.
wesgarrison
+4  A: 

Your issue is that the finders are case sensitive. I have the same exact issue with my legacy database.

Try this to watch it work:

Person.find_by_Last_Name("Smith")

That should do the trick.

I have code that I have written to fix issues like this. It's a little monkey-patch for ActiveRecord that you can insert into the specific models that you want to modify.

module ActiveRecord
  class Base
    # Indicates whether field names should be lowercased for legacy databse fields.
    # If true, the field Product_Name will be +product_name+. If false, it will remain +Product_Name+.
    # This is false, by default.
    cattr_accessor :downcase_legacy_field_names, :instance_writer => false
    @@downcase_legacy_field_names = false
  end
end

The code above creates a new accessor on ActiveRecord called downcase_legacy_field_names. It defaults to false. When this accessor is set to true at the top of a model it will trigger the code below.

# set all accessor methods to lowercase (underscore)
# add set_columns_to_lower to each model that needs it 
class << ActiveRecord::Base

    # Returns a hash of all the methods added to query each of the columns in the table with the name of the method as the key
    # and true as the value. This makes it possible to do O(1) lookups in respond_to? to check if a given method for attribute
    # is available.
    def column_methods_hash #:nodoc:
      @dynamic_methods_hash ||= column_names.inject(Hash.new(false)) do |methods, attr|

        attr_final = downcase_legacy_field_names ? attr.to_s.downcase : attr

        attr_name = attr_final
        methods[attr_final.to_sym]       = attr_name
        methods["#{attr_final}=".to_sym] = attr_name
        methods["#{attr_final}?".to_sym] = attr_name
        methods["#{attr_final}_before_type_cast".to_sym] = attr_name
        methods
      end
    end

   # adapted from: http://wiki.rubyonrails.org/rails/pages/HowToUseLegacySchemas
    def downcase_legacy_field_methods
      column_names.each do |name|
       next if name == primary_key
       a = name.to_s.underscore

       define_method(a.to_sym) do
         read_attribute(name)
       end

       define_method("#{a}=".to_sym) do |value|
         write_attribute(name, value)
       end

       define_method("#{a}?".to_sym) do
         self.send("#{name}?".to_sym)
       end

      end
    end


end 




ActiveRecord::Base.downcase_legacy_field_names = true

This code was adapted from: http://wiki.rubyonrails.org/rails/pages/HowToUseLegacySchemas

In column_methods_hash we are overriding the ActiveRecord method. This method is used to generated the list of method names that are created at runtime for your database model. We don't want to override any earlier in the process because we would be messing with ActiveRecord's ability to convert our dynamic finders (and other methods) to proper SQL statements for the legacy database.

The second method, downcase_legacy_field_methods, is a new method that will actually generate the code that the downcase'd method will execute.

All of the above code patches ActiveRecord. It's a monkey patch so it can be required anywhere after ActiveRecord is loaded. I have mine in environment.rb.

Once you have patched ActiveRecord, there is one more step that you will need to do. At the top of your legacy database model you need to have the line downcase_legacy_field_methods. It should look something like this:

class LegacyDatabaseModel < ActiveRecord::Base
  downcase_legacy_field_methods

  def cubits_to_feet
    #conversion code goes here
  end
end
salt.racer
+1  A: 

I encountered similar problems with a Legacy SQL Server database that had no naming conventions and a vast sprawl of ASP.net code with SQL embedded in code-behind pages etc. This precluded us from modifying the existing ASP.net application, not that I wanted to do that anyway.

This database structure was quite painful for rails and we took the view that as table names were all singular we could rename all the tables and columns to be consistent with Rails and then create views that reflected the original table/column structure for the ASP.net application.

This allowed us to make changes to the newly renamed tables including adding new fields etc whilst isolating the legacy application from these DB structure changes.

This solution will obviously not work for everyone but it proved very successful for us.

Steve Weet
A: 

This post was a great help to me, and I formalized this into a plugin: http://github.com/reidmix/legacy_mappings which I wanted to share. Click through, looks like the url gets encoded.