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.
