Rails - Jan 2005 - Working with Legacy database

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


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Eric Anderson wrote:
> 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
Slight typo. The above should be:

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

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

Left bits of the real objects in there instead of my reduced example.

Eric


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Eric Anderson wrote:
> 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.
Just figured out the solution. The finder_sql option is interpolated 
while in the context of the object. So if I pass it a single quoted 
string it will delay interpolation and work how I want.

The docs didn''t explicitly say it does this although the example the 
docs give show the delayed interpolation.

Eric


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails