[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.

On 4 March 2010 15:33, Phoenix R. [email protected] wrote:

Job.first
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.

http://groups.google.com/group/rubyonrails-talk?hl=en.


Charles A. Lopez
[email protected]

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

Let’s talk.
(IBM Partner)

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, 8:33 pm, Phoenix R. [email protected] 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

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 C. [email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs