Rails - Jun 2008 - Multiple databases and establishing associations

Hi,

Rails 1.2.6

I''ve got two models from two different databases that I wish to
associate if possible. The association is not based on a primary key
for either model, but on a "job_number" field that both tables have
(which is unique in both tables). Here''s what I tried:

require ''active_record''

class Job < ActiveRecord::Base
   establish_connection(:adapter => "oracle", ...)

   self.table_name  = ''job''
   self.primary_key = ''job_id''

   has_one :schedule, :foreign_key => :job_number
end

class Schedule < ActiveRecord::Base
   establish_connection(:adapter => "postgresql", ...)

   belongs_to :job, :foreign_key => :job_number
end

j = Job.find_by_job_number(''12345A'')
j.schedule => nil # ???

# Also tried:
s = Schedule.find_by_job_number(''12345A'')
s.job => nil # ???

No error, but the association should definitely not be nil.

What''s the proper way to do what I''m trying? On a side note,
how can I
inspect the sql that AR is generating?

Thanks,

Dan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 23, 2008, at 1:36 PM, Daniel Berger wrote:
> I''ve got two models from two different databases that I wish to
> associate if possible. The association is not based on a primary key
> for either model, but on a "job_number" field that both tables
have
> (which is unique in both tables). Here''s what I tried:
I''m no expert on this one, but considering Rails isn''t very
excited
about working with more than one DB to start with (no way to declare  
multiple DBs in it''s own config system), I can''t imagine it
doing
associations across databases for you -- let alone ones with  
completely different RDBMS engines.

I would really expect you''ll have to build the composition of the  
object yourself.
> how can I inspect the sql that AR is generating?
Look at the file /log/development.log

-- gw



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 24, 2:22 pm, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org>
wrote:
> On Jun 23, 2008, at 1:36 PM, Daniel Berger wrote:
>
> > I''ve got two models from two different databases that I wish
to
> > associate if possible. The association is not based on a primary key
> > for either model, but on a "job_number" field that both
tables have
> > (which is unique in both tables). Here''s what I tried:
>
> I''m no expert on this one, but considering Rails isn''t
very excited  
> about working with more than one DB to start with (no way to declare  
> multiple DBs in it''s own config system), I can''t imagine
it doing  
> associations across databases for you -- let alone ones with  
> completely different RDBMS engines.
>
> I would really expect you''ll have to build the composition of the
 
> object yourself.
Any suggestions on how to do that exactly?
> > how can I inspect the sql that AR is generating?
>
> Look at the file /log/development.log
Ok, thanks. Is there a way to inspect the sql in a standalone AR
script?

Thanks,

Dan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 26 Jun 2008, at 17:57, Daniel Berger wrote:
>
> On Jun 24, 2:22 pm, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org> wrote:
>> On Jun 23, 2008, at 1:36 PM, Daniel Berger wrote:
>>
>>> I''ve got two models from two different databases that I
wish to
>>> associate if possible. The association is not based on a primary
key
>>> for either model, but on a "job_number" field that both
tables have
>>> (which is unique in both tables). Here''s what I tried:
>>
>> I''m no expert on this one, but considering Rails
isn''t very excited
>> about working with more than one DB to start with (no way to declare
>> multiple DBs in it''s own config system), I can''t
imagine it doing
>> associations across databases for you -- let alone ones with
>> completely different RDBMS engines.
>>
Actually I can''t see why it wouldn''t work. All it''s
going to do is run
select from bars where foo_id = x.

The real problem here is that active record doesn''t do assocations  
where the foreign key isn''t pointing at the primary key on some other  
table.

Fred

>> I would really expect you''ll have to build the composition of
the
>> object yourself.
>
> Any suggestions on how to do that exactly?
>
>>> how can I inspect the sql that AR is generating?
>>
>> Look at the file /log/development.log
>
> Ok, thanks. Is there a way to inspect the sql in a standalone AR
> script?
>
not that I know of
> Thanks,
>
> Dan
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Daniel Berger wrote:
> On Jun 24, 2:22�pm, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org> wrote:
>> associations across databases for you -- let alone ones with �
>> completely different RDBMS engines.
>>
>> I would really expect you''ll have to build the composition of
the �
>> object yourself.
> 
> Any suggestions on how to do that exactly?
> 
What if you create something like ActiveRecord2 that uses ActiveRecord 
library but makes a connection to a database defined in database.yml 
(like schedule_db:)?

It would be an interesting project. And I''d be willing to help if you
go
that route.

- Ivan V.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 26, 2008, at 9:57 AM, Daniel Berger wrote:
> On Jun 24, 2:22 pm, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org> wrote:
>> On Jun 23, 2008, at 1:36 PM, Daniel Berger wrote:
>>
>>> I''ve got two models from two different databases that I
wish to
>>> associate if possible. The association is not based on a primary
key
>>> for either model, but on a "job_number" field that both
tables have
>>> (which is unique in both tables). Here''s what I tried:
>>
>> I''m no expert on this one, but considering Rails
isn''t very excited
>> about working with more than one DB to start with (no way to declare
>> multiple DBs in it''s own config system), I can''t
imagine it doing
>> associations across databases for you -- let alone ones with
>> completely different RDBMS engines.
>>
>> I would really expect you''ll have to build the composition of
the
>> object yourself.
>
> Any suggestions on how to do that exactly?
It''s just brute force. You write all the code to coordinate the two  
AR models, but encapsulate it inside a wrapper object (aka composition).

You build a plain Ruby class composed of a Job object and a Schedule  
object. You use your existing AR classes for each (minus the  
associations code).

Rough 2-minute idea:

Class ScheduledJob
   def initialize(job_number)
     @job = Job.find_by_job_number(job_number)
     @schedule = Schedule.find_by_job_number(job_number)
   end
end

Now you can

   my_job = ScheduledJob.new(''12345A'')

and get at details of either sub-object

   my_job.job.description
   my_job.schedule.completed?

Something like that. Then actions that have to deal with bits of info  
from each, you just add as methods to ScheduledJob and interface to  
Job and Schedule as necessary. That way you encapsulate the logic  
that manages ScheduledJob. From the oustide, you treat ScheduledJob  
as a single object, but on the inside it''s made up of more than one.

How do you save a ScheduledJob? Write a save method that accepts the  
possible params, then divy up the params into the respective Job and  
Schedule saves that are necessary. Brute force, but at least you get  
a nice interface to it all.

-- gw


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 26, 1:17 pm, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org>
wrote:
> On Jun 26, 2008, at 9:57 AM, Daniel Berger wrote:
>
>
>
>
>
> > On Jun 24, 2:22 pm, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org> wrote:
> >> On Jun 23, 2008, at 1:36 PM, Daniel Berger wrote:
>
> >>> I''ve got two models from two different databases that
I wish to
> >>> associate if possible. The association is not based on a
primary key
> >>> for either model, but on a "job_number" field that
both tables have
> >>> (which is unique in both tables). Here''s what I
tried:
>
> >> I''m no expert on this one, but considering Rails
isn''t very excited
> >> about working with more than one DB to start with (no way to
declare
> >> multiple DBs in it''s own config system), I can''t
imagine it doing
> >> associations across databases for you -- let alone ones with
> >> completely different RDBMS engines.
>
> >> I would really expect you''ll have to build the
composition of the
> >> object yourself.
>
> > Any suggestions on how to do that exactly?
>
> It''s just brute force. You write all the code to coordinate the
two  
> AR models, but encapsulate it inside a wrapper object (aka composition).
>
> You build a plain Ruby class composed of a Job object and a Schedule  
> object. You use your existing AR classes for each (minus the  
> associations code).
>
> Rough 2-minute idea:
>
> Class ScheduledJob
>    def initialize(job_number)
>      @job = Job.find_by_job_number(job_number)
>      @schedule = Schedule.find_by_job_number(job_number)
>    end
> end
>
> Now you can
>
>    my_job = ScheduledJob.new(''12345A'')
>
> and get at details of either sub-object
>
>    my_job.job.description
>    my_job.schedule.completed?
>
> Something like that. Then actions that have to deal with bits of info  
> from each, you just add as methods to ScheduledJob and interface to  
> Job and Schedule as necessary. That way you encapsulate the logic  
> that manages ScheduledJob. From the oustide, you treat ScheduledJob  
> as a single object, but on the inside it''s made up of more than
one.
>
> How do you save a ScheduledJob? Write a save method that accepts the  
> possible params, then divy up the params into the respective Job and  
> Schedule saves that are necessary. Brute force, but at least you get  
> a nice interface to it all.
Ok, I think this is the approach I''m going to take, probably combined
with some sort of delegation scheme to ease the pain. Many thanks for
the suggestion.

Regards,

Dan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---