A note on multiple databases & associations

Hi,

I have two databases (due to legacy) and have tried the two methods of
having records stored in multiple databases as mentioned in
http://groups.google.com/group/rubyonrails-talk/browse_frm/thread/9b002dee39ecb9f4/06f8856e8a82300e?lnk=gst&q=multiple+database&rnum=3#06f8856e8a82300e

The models are:

Revision:
belongs_to :project

Project:
has_many :revision

The tables are:

ProjectDB.projects (storing the Project model)

val_results_development.revisions (to store the Revision models)

So for the first approach to dealing with multiple databases, in
config/database.yml I have:

ProjectsDB:
adapter: mysql
database: ProjectsDB

and in the Project model:

establish_connection “ProjectsDB”

Which all seems to work. It can find projects

But if I do:

Project.find(:all, :include=>:revisions)

I get:

ActiveRecord::StatementInvalid: Mysql::Error: #42S02Table
‘ProjectsDB.revisions’ doesn’t exist: SELECT projects.id AS t0_r0,
projects.name AS t0_r1, projects.configfile AS t0_r2,
projects.descript AS t0_r3, projects.updated AS t0_r4,
revisions.id AS t1_r0, revisions.tag AS t1_r1,
revisions.project_id AS t1_r2 FROM projects LEFT OUTER JOIN
revisions ON revisions.project_id = projects.id

It’s looking in the ProjectsDB for the revisions table. The revisions
table actually lives in the database called
‘val_results_development’.

There’s a similar problem with Revision.find(:all,:include=>:project),
it searches for the projects table in the ‘val_results_development’
database when it should be looking in the ‘ProjectsDB’ database.

OK, so let’s try another approach, in the Project model I say:

set_table_name “ProjectsDB.projects”

and this seems to work, I can get projects from revisions and vice
versa.

But one of the caveats (as described in the Wiki) is that this second
approach won’t work if the database is on another host machine - which
is what I’m going to hit soon!

Does anyone have a way around that, other than resorting to custom
sql? I don’t really want custom sql since I’m hoping to scaffold much
of the app.

Thanks,

Allan

PS This entry
http://groups.google.com/group/rubyonrails-talk/browse_frm/thread/57e275b63c3e4813/d3151e812114bde5?lnk=gst&q=multiple+database&rnum=9#d3151e812114bde5
suggests that it is not possible, but the set_table_name trick works
as far as I can see.

Bad form to follow up on one’s own post but I have been thinking …

On Jun 14, 12:48 pm, Allan [email protected] wrote:

But one of the caveats (as described in the Wiki) is that this second
approach won’t work if the database is on another host machine - which
is what I’m going to hit soon!

Does anyone have a way around that, other than resorting to custom
sql? I don’t really want custom sql since I’m hoping to scaffold much
of the app.

So this isn’t going to work, you can’t access stuff on another server
in a ‘standard’ query.

But it would be nice to not have to use the set_table_name trick.

Allan

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