Multiple databases + switching databases


#1

Hello,

I have an application that access two different DBMS - Firebird
and Postgres. So far, that’s ok. The problem is, depending on the
customer logged, a different database (Firebird) must be selected.

To make the things clearer:

Postgres: 1 database shared by all customers with these tables:
- user
- vehicle
- driver

Firebird: several databases with different names and identical structure
with this table:
- gps

Each customer has a firebird database. The name of the customer
determines the database name:

- Customer name: C1 - Database name: C1.fdb
- Customer name: C2 - Database name: C2.fdb
- Customer name: C3 - Database name: C3.fdb
...

Model classes:

- User (postgres)
- Vehicle (postgres)
- Driver (postgres)

- Gps (firebird)

If the customer C1 is logged, the Gps class must be associated to
table Gps in C1.fdb, C2.fdb if customer C2, and so on.

How could I do this choice at runtime?
Is there a way to set table name in environment.rb file dynamically?
Is there another way to switch the database without setting
environment.rb?
I’ve tried to do this just setting connection at Gps class but it didn’t
work.

( ActiveRecord::Base.establish_connection(
:adapter => “firebird”,
:host => “localhost”,
:database => customer_name,
:username => “SYSDBA”,
:password => “masterkey”)

Does anybody have any idea?

Thanks a lot!

Marcia


#2

( ActiveRecord::Base.establish_connection(
:adapter => “firebird”,
:host => “localhost”,
:database => customer_name,
:username => “SYSDBA”,
:password => “masterkey”)

Can you provide some more detail on how establish_connection is
failing?

Does anybody have any idea?

I would think tat you’re on the right track with establish_connection;
if you haven’t yet, take a look at:

http://wiki.rubyonrails.com/rails/pages/HowtoUseMultipleDatabases
http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/Connections.rdoc


#3

Hello, Frank

In a page, I have two combos (one of them listing all vehicles and the
other, all drivers); to populate these combos, I use Vehicle.find_all
and Driver.find_all. Both model classes must use Postgres connection as
well as all the other class, BUT Gps (that uses firebird). When I
request that page, the system tries to connect to firebird, but that
tables are in postgres. I was thinking the problem could be the
following: I put the clause ActiveRecord::Base.establish_connection (to
firebird) just in Gps class. Maybe I
have to put the postgres establishment of connection in the others
classes, too. As far as I’ve read, I understood I had to do the
‘mapping’ just to Gps class. But I really don’t know. I’ll try to do the
test and tell you what I get. If you think I’m in the wrong way, please,
tell me.

Thanks a lot, again

Marcia

Frank Cameron wrote:

( ActiveRecord::Base.establish_connection(
:adapter => “firebird”,
:host => “localhost”,
:database => customer_name,
:username => “SYSDBA”,
:password => “masterkey”)

Can you provide some more detail on how establish_connection is
failing?

Does anybody have any idea?

I would think tat you’re on the right track with establish_connection;
if you haven’t yet, take a look at:

http://wiki.rubyonrails.com/rails/pages/HowtoUseMultipleDatabases
http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/Connections.rdoc


#4

‘mapping’ just to Gps class. But I really don’t know. I’ll try to do the
test and tell you what I get. If you think I’m in the wrong way, please,
tell me.

I think it should be sufficient to have the default in database.yml
point to the Postgres database and explicitly connect to Firebird
from the Gps class.


#5

In a page, I have two combos (one of them listing all vehicles and the
other, all drivers); to populate these combos, I use Vehicle.find_all
and Driver.find_all. Both model classes must use Postgres connection as
well as all the other class, BUT Gps (that uses firebird). When I
request that page, the system tries to connect to firebird, but that
tables are in postgres.

I think it should be sufficient to have the default in database.yml
point to the Postgres database and explicitly connect to Firebird
from the Gps class.

Perhaps a setup like this would be “better”:

class PostgresBase < ActiveRecord::Base
establish_connection …
end
class FirebirdBase < ActiveRecord::Base
establish_connection …
end

class Driver < PostgresBase

end

class Vehicle < PostgresBase

end

class Gps < FirebirdBase

end

That way if your app needs to call on more models from either
database, you only establish_connection() once for both.


#6

Hi, people

The point to the Postgres database and explicity connect to Firebird
from the Gps class was not sufficient, the system was lost. But thanks
anyway. However, using this:

class PostgresBase < ActiveRecord::Base
establish_connection …
end …

was effective. It works very well!

Thanks a lot!!!

Marcia

unknown wrote:

In a page, I have two combos (one of them listing all vehicles and the
other, all drivers); to populate these combos, I use Vehicle.find_all
and Driver.find_all. Both model classes must use Postgres connection as
well as all the other class, BUT Gps (that uses firebird). When I
request that page, the system tries to connect to firebird, but that
tables are in postgres.

I think it should be sufficient to have the default in database.yml
point to the Postgres database and explicitly connect to Firebird
from the Gps class.

Perhaps a setup like this would be “better”:

class PostgresBase < ActiveRecord::Base
establish_connection …
end
class FirebirdBase < ActiveRecord::Base
establish_connection …
end

class Driver < PostgresBase

end

class Vehicle < PostgresBase

end

class Gps < FirebirdBase

end

That way if your app needs to call on more models from either
database, you only establish_connection() once for both.