Rails - Mar 2010 - [2.3.5] How can I tell a model to use a specific DB and table?

So I have these two databases:

 - Website
 - Company Intranet

I''ve built the DB and schema for the company intranet application to
allow certain users (authenticated by AD within my rails app) to input
data that will then be brought over to the first database (company
public-facing website) when a rake task is run.  That''s the idea,
anyway.

There is going to be a LOT of overlap between the two databases and
their schemas.  For example, both databases have a table named
"jobs".  So I''m trying to use multiple ActiveRecord
connections to
move data from the company intranet database to the website database
without resorting to raw SQL.

So I have a Job class on the company intranet app:
class Job < ActiveRecord::Base
  # ...
end

And one for the company website - it''s in the company intranet
application, but it''s a different model:
class WebsiteJob < ActiveRecord::Base
  ActiveRecord::Base.establish_connection("website_#{RAILS_ENV}")
  set_table_name "jobs"
  # ...
end

As you might be able to see, I''m running into a bit of a collision
here.  If I fire up script/console and call:
>> Job.first
I wind up with a Job from the right database (the "company intranet"
database).  However, if I call:
>> CompanyJob.delete_all # purge stale crap that nobody needs
>> Job.first
It queries the FIRST database, the one that the CompanyJob model
connects to.  It seems to be switching the default connection behind
the scenes and I''m not sure how to override that.

I tried manually assigning the ActiveRecord connection for the right
database to the Job model, but it still exhibits this exact same
behavior.

Does anyone know how I can force Rails to use the remote/other DB ONLY
for that specific model, regardless of the fact that both databases
contain identically named tables (that I''m unable to change)?

Thanks.

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 4 March 2010 15:33, Phoenix Rising
<polarisrising-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> So I have these two databases:
>
>  - Website
>  - Company Intranet
>
> I''ve built the DB and schema for the company intranet application
to
> allow certain users (authenticated by AD within my rails app) to input
> data that will then be brought over to the first database (company
> public-facing website) when a rake task is run.  That''s the idea,
> anyway.
>
> There is going to be a LOT of overlap between the two databases and
> their schemas.  For example, both databases have a table named
> "jobs".  So I''m trying to use multiple ActiveRecord
connections to
> move data from the company intranet database to the website database
> without resorting to raw SQL.
>
> So I have a Job class on the company intranet app:
> class Job < ActiveRecord::Base
>  # ...
> end
>
> And one for the company website - it''s in the company intranet
> application, but it''s a different model:
> class WebsiteJob < ActiveRecord::Base
>  ActiveRecord::Base.establish_connection("website_#{RAILS_ENV}")
>  set_table_name "jobs"
>  # ...
> end
>
> As you might be able to see, I''m running into a bit of a collision
> here.  If I fire up script/console and call:
> >> Job.first
>
> I wind up with a Job from the right database (the "company
intranet"
> database).  However, if I call:
> >> CompanyJob.delete_all # purge stale crap that nobody needs
> >> Job.first
>
> It queries the FIRST database, the one that the CompanyJob model
> connects to.  It seems to be switching the default connection behind
> the scenes and I''m not sure how to override that.
>
> I tried manually assigning the ActiveRecord connection for the right
> database to the Job model, but it still exhibits this exact same
> behavior.
>
> Does anyone know how I can force Rails to use the remote/other DB ONLY
> for that specific model, regardless of the fact that both databases
> contain identically named tables (that I''m unable to change)?
>
>
Might this be a symptom of poor design?


Share with us the tables for both databases.




> Thanks.
>
> --
> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>

-- 
Charles A. Lopez
charlesalopez-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

What''s your vision for your organization?
What''s your biggest challenge?

Let''s talk.
(IBM Partner)

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Hey Charles, thanks for the feedback.

In the company intranet database we have:
--------------
DESCRIBE jobs;
id
title
category
status
city
state_id
zip_code
description
qualifications
education
experience
application_link
store_id
created_at
updated_at

In the company website database, we have:
=======DESCRIBE jobs;
id
title
category_id
city
state
zip
status_id
description
qualifications
required_skills
required_education
application_link
created_at
updated_at

So as you can see, they''re very similar, though there are some
discrepancies.  The _id fields:
In the portal DB:
---
 - store_id : FK to a "stores" table, so HR personnel can just select
the store that the job is located at, and not have to enter city/state/
zip unless it''s at a location we don''t have in our system yet
(intranet portal only)

In the website DB:
---
 - category_id = FK to a "job_categories" table where we have entries
like "Accounting", "IT", "HR", etc. - designed to
prevent duplication
of those text labels
 - status_id = FK to a "job_statuses" table where there are entries
like "Full Time", "Part Time", "Seasonal", etc.,
again to prevent
duplication

Unfortunately I''m unable to change the "website" database
structure to
match that of the "portal" structure; I can do this all with raw SQL
(in fact I had a rake task finished that does it that way), but I''d
prefer to do it using ActiveRecord, as the syntax is much shorter.

I''m 99.99999% sure that the problem lies in the fact that the tables
have the same name, even though they''re in different databases.  If I
don''t call methods on any of the "remote" classes I have set
up,
everything works perfectly, but if I call one of those remote classes,
and then make a call to the Job model, everything goes to hell in a
handbasket, complaining, for example, that the column
''category''
doesn''t exist in the table.  It does exist on the table, just *in a
different database*.

On Mar 4, 1:58 pm, "Charles A. Lopez"
<charlesalo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 4 March 2010 15:33, Phoenix Rising
<polarisris...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
>
>
> > So I have these two databases:
>
> >  - Website
> >  - Company Intranet
>
> > I''ve built the DB and schema for the company intranet
application to
> > allow certain users (authenticated by AD within my rails app) to input
> > data that will then be brought over to the first database (company
> > public-facing website) when a rake task is run.  That''s the
idea,
> > anyway.
>
> > There is going to be a LOT of overlap between the two databases and
> > their schemas.  For example, both databases have a table named
> > "jobs".  So I''m trying to use multiple ActiveRecord
connections to
> > move data from the company intranet database to the website database
> > without resorting to raw SQL.
>
> > So I have a Job class on the company intranet app:
> > class Job < ActiveRecord::Base
> >  # ...
> > end
>
> > And one for the company website - it''s in the company
intranet
> > application, but it''s a different model:
> > class WebsiteJob < ActiveRecord::Base
> >
 ActiveRecord::Base.establish_connection("website_#{RAILS_ENV}")
> >  set_table_name "jobs"
> >  # ...
> > end
>
> > As you might be able to see, I''m running into a bit of a
collision
> > here.  If I fire up script/console and call:
> > >> Job.first
>
> > I wind up with a Job from the right database (the "company
intranet"
> > database).  However, if I call:
> > >> CompanyJob.delete_all # purge stale crap that nobody needs
> > >> Job.first
>
> > It queries the FIRST database, the one that the CompanyJob model
> > connects to.  It seems to be switching the default connection behind
> > the scenes and I''m not sure how to override that.
>
> > I tried manually assigning the ActiveRecord connection for the right
> > database to the Job model, but it still exhibits this exact same
> > behavior.
>
> > Does anyone know how I can force Rails to use the remote/other DB ONLY
> > for that specific model, regardless of the fact that both databases
> > contain identically named tables (that I''m unable to change)?
>
> Might this be a symptom of poor design?
>
> Share with us the tables for both databases.
>
> > Thanks.
>
> > --
> > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> > To unsubscribe from this group, send email to
> >
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscrib
e@googlegroups.com>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/rubyonrails-talk?hl=en.
>
> --
> Charles A. Lopez
> charlesalo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
>
> What''s your vision for your organization?
> What''s your biggest challenge?
>
> Let''s talk.
> (IBM Partner)
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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 Mar 4, 8:33 pm, Phoenix Rising
<polarisris...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> It queries the FIRST database, the one that the CompanyJob model
> connects to.  It seems to be switching the default connection behind
> the scenes and I''m not sure how to override that.
It''s switching the default connection because you''re asking it
to -
you''re calling establish_connection on ActiveRecord::Base. You should
just be calling it on the class you want to change.

Fred
>
> I tried manually assigning the ActiveRecord connection for the right
> database to the Job model, but it still exhibits this exact same
> behavior.
>
> Does anyone know how I can force Rails to use the remote/other DB ONLY
> for that specific model, regardless of the fact that both databases
> contain identically named tables (that I''m unable to change)?
>
> Thanks.
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.

Fred, as always, you are the man.  Thanks for idiot checking me!

For any searchers/googlers with the same issue:

# THIS IS WRONG
class Something < ActiveRecord::Base
 
ActiveRecord::Base.establish_connection("name_of_your_database_dot_yml_connection")
  # ...
end

# This appears right
class Something < ActiveRecord::Base
  self.establish_connection("name_of_connection_in_database_dot_yml")
  # ...
end

On Mar 4, 2:32 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Mar 4, 8:33 pm, Phoenix Rising
<polarisris...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > It queries the FIRST database, the one that the CompanyJob model
> > connects to.  It seems to be switching the default connection behind
> > the scenes and I''m not sure how to override that.
>
> It''s switching the default connection because you''re
asking it to -
> you''re calling establish_connection on ActiveRecord::Base. You
should
> just be calling it on the class you want to change.
>
> Fred
>
>
>
>
>
> > I tried manually assigning the ActiveRecord connection for the right
> > database to the Job model, but it still exhibits this exact same
> > behavior.
>
> > Does anyone know how I can force Rails to use the remote/other DB ONLY
> > for that specific model, regardless of the fact that both databases
> > contain identically named tables (that I''m unable to change)?
>
> > Thanks.
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.