Dynamic connection


#1

Hello all,

In my Rails app I have a model which I want to use to maintain records
in several tables (identical structure) in multiple databases.
Is there a way to use establish_connection and set_table_name in a
dynamic way?


#2

Yes, there is. establish_connection and not set_table_name is the
answer.
I’ve been down both routes and establish_connection is much lighter on
the
necessary code than set_table_name.

RSL


#3

Russell N. wrote:

Yes, there is. establish_connection and not set_table_name is the
answer.
I’ve been down both routes and establish_connection is much lighter on
the
necessary code than set_table_name.

RSL

Hi RSL,

Thanks for answering. However, I think I need both set_table_name and
establish_connection I want to access multiple tables on multiple
databases. Maybe I can overload the ActiveRecord class methods create,
save, etc. by defining extra parameters for both connection and
table_name:

def self.create(attributes, connection, table_name)
set_table_name = table_name
establish_connection connection
super(attributes)
end

but I haven’t tried this appraach yet.


#4

I figured out that it’s actually pretty easy to set connection and
tablename dynamically by using establish_connection and set_table_name
for my model only:

Site.establish_connection some_method
Site.set_table_name another_method
Site.create (or whatever class method)

In my situation I want to update several MSSQL records on several
servers. The default database is mySQL.
However… since I use the RedHill lpugin
(http://www.redhillonrails.org/#redhillonrails_core) it seems that there
is a conflict when I try to create or update on MSSQL when my default
connection connects to mySQL:
NoMethodError: undefined method []=' for #<Mysql> from c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/connection_adapters/sqlserver_adapter.rb:439:inindexes’
from
./script/…/config/…/config/…/vendor/plugins/redhillonrails_core/lib/red_hill_consulting/core/active_record/base.rb:42:in
indexes' from ./script/../config/../config/../vendor/plugins/redhillonrails_core/lib/red_hill_consulting/core/active_record/base.rb:27:incolumns’
from
c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:776:in
column_names' from c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:789:incolumn_methods_hash’
from
c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:1263:in
all_attributes_exists?' from (irb):9:inall?’
from
c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:1263:in
each' from c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:1263:inall?’
from
c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:1263:in
all_attributes_exists?' from c:/DEV/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/base.rb:1197:inmethod_missing’
from (irb):9

The story continues, but thanks for the reply!

Patrick


#5

Here’s what I did. Site model is in the default db and contains a
db_name
attribute which holds the name of the db for that site. In Site…

DB_PREFIX = “whatever_”

def ensure_connection
[Attachment, Document, Category, Tag].each do |klass|
unless ActiveRecord::Base.configurations.has_key? db_name
config = ActiveRecord::Base.configurations[RAILS_ENV]
ActiveRecord::Base.configurations[db_name] = {
:adapter => “mysql”,
:host => config[“host”],
:username => config[“username”],
:password => config[“password”],
:database => “#{DB_PREFIX}#{db_name}”
}
end
[Attachment, Category, Comment, Document, Tag, Source].each do
|model|
if model.connection.config[:database] != “#{DB_PREFIX}#{db_name}”
model.establish_connection db_name
# You have to do this manually…
model.connection.logger = RAILS_DEFAULT_LOGGER
end
end
end
end

and in my controllers that need to access that data just create a
before_filter which calls Site#ensure_connections. Unless you’re on to
something I wasn’t set_table_name is more trouble than it’s worth. Hope
that
helps.

RSL