Switching databases in Rails v1.0 app


#1

Hello, I need some guidance. I have a need to:
a) Login, validating against a “system-level” user database - no big
deal, then
b) Depending upon the login, select a customer database for processing.

For instance, assume that my login profile lists an area of interest:
cars, motorcycles or boats. Each of these has a database, with identical
table formats. If my login shows my area of interest to be boats, then I
want to disconnect from the system-level database, and establish a
connection to the boats database. At this point in time only the
system-level database appears in the database.yml file.

The example at
http://wiki.rubyonrails.com/rails/pages/HowtoUseMultipleDatabases seemed
pretty straight-forward, but I’ve been unable to make this work. The
code in “hijack_db” is executing, but the new database is not connected
when the change to my “items” connector is called. The example looks
like it’s for a version of Rails prior to 1.0, which I’m now using, and
using the Webrick server for testing.

Does anybody have any thoughts on getting this switch to take place?

Thanks much,

Gene


#2

I tried it with a Postgres database and it worked with no problem.
Please note that it does introduce some performance penalties, as
every time you do a reconnect it will have to kill the original
connection and restart a new one.

On Postgres (and I presume some other databases as well) you can use
schemas to achieve the same without causing the reconnects. If you are
using Postgres I can give you some details on how to do it.

Bye,

Guy.

Coming soon - family management on rails: http://www.famundo.com


#3

Thanks, I’m using MySQL for my testing. I’m also trying to keep the
penalty down by checking the requested database name against the current
(tracking these in the session[] hash and only re-connecting when
there’s a change.

Gene

Just S. wrote:

I tried it with a Postgres database and it worked with no problem.
Please note that it does introduce some performance penalties, as
every time you do a reconnect it will have to kill the original
connection and restart a new one.

On Postgres (and I presume some other databases as well) you can use
schemas to achieve the same without causing the reconnects. If you are
using Postgres I can give you some details on how to do it.

Bye,

Guy.

Coming soon - family management on rails: http://www.famundo.com


#4

Hi Gene,

If you have a very limited set of different databases, why not have
the web server point to different rails processes (I assume you’ll be
using FastCGI or SCGI) and having each point to the appropriate one?

Of course that won’t work for anything over a small number of
different databases.

Bye,

Guy.

Coming soon - family management on rails: http://www.famundo.com


#5

This little app is just to prototype a technique that will be used in a
much larger application, which supports associations. We will be
supporting on the order of twenty-thirty associations, perhaps more,
each with a large information database. Each assn will have its own
database, and the application will select the appropriate one based upon
user login. We wish to use a single application for all, because the app
is quite large.

I got this running this morning. Turns out I had to call “hijack_db”
prior to the redirect_to my item controller. I read the HowTo to believe
that the application.rb was entered (and the before_filter executed)
before completing the redirect. Apparently doesn’t work exactly this way
in v1.0???

Thanks much,

Gene

Just S. wrote:

Hi Gene,

If you have a very limited set of different databases, why not have
the web server point to different rails processes (I assume you’ll be
using FastCGI or SCGI) and having each point to the appropriate one?

Of course that won’t work for anything over a small number of
different databases.

Bye,

Guy.

Coming soon - family management on rails: http://www.famundo.com