[Rails] Working with Legacy database

[Eric Anderson]

I have an application that I am developing in Rails. Occasionally I need 
to access a database that was not designed with rails in mind. It 
follows the rails pattern pretty well, but occasionally breaks from good 
design. Fixing the database is not an option because other external code 
depends on the current design. To understand my basic problem assume you 
  have the following tables:

CREATE TABLE foos (
	id	INT		AUTO_INCREMENT,
	name	VARCHAR(255)
)

CREATE TABLE bars (
	id		INT		AUTO_INCREMENT,
	baz		VARCHAR(255),
	foo		VARCHAR(255)
)

So in this example foo is in my we designed rails database object. Bar 
is a table in some other database that was designed by someone else. 
bars.foo related to the foos.name field. So basically we are using the 
text instead of the ID to provide the foreign key. So our ruby code 
looks like

class Foo < ActiveRecord::Base
	has_many :bars, :dependent => true, :foreign_key => 'foo'
end

class Bar < ActiveRecord::Base
	belongs_to :foo, :foreign_key => 'foo'
end

The only problem with this setup is that it is matching foos.id to 
bars.foo instead of foos.name to bars.foo. So how do I specify in the 
relationship that it should use a different field for the "id". I looked 
in the Rails code and saw the relevant piece of code that makes the 
association between the two tables is:

"#{@association_class_primary_key_name} = 
#{@owner.quoted_id}#{@conditions ? " AND " + 
interpolate_sql(@conditions) : ""}"

So it uses the quoted_id() method on ActiveRecord::Base to get the id 
field name. I could override that method in Foo, but I only want it to 
use "name" as the id in that relationship. In every other relationship I 
want to use "id" as the id field. Not only that, but since these objects 
are in different databases I am unsure if the join would be valid since 
the table names are not prefixed with the database name.

My other thought is to use the "finder_sql" attribute. But my best 
attempt at that doesn't seem to work either. Here is my attempt at that:

class Foo < ActiveRecord::Base
	has_many :aliases, :dependent => true, :finder_sql => <<-SQL
			SELECT db1.bars.*
			FROM db1.bars
			WHERE db1.bars.foo = #{name}
		SQL
end

class Bar < ActiveRecord::Base
	belongs_to :domain, :finder_sql => <<-SQL
			SELECT db2.foos.*
			FROM db2.foos
			WHERE db2.foos.name = #{foo}
		SQL
end

But this doesn't work either because the sql string is evaluated at the 
time the macro is executed so the method name() and foo() are not 
evaluated on the actual object we are running the search on.

So are there any suggestions or am I just going to have to generate the 
association methods myself with straight SQL. Any thoughts or 
suggestions are appreciated.

Eric
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 187 bytes
Desc: OpenPGP digital signature
Url : http://one.textdrive.com/pipermail/rails/attachments/20050104/e9751bfa/signature.bin