Using multiple databases in Rails 3: how?

Question

Ruby on Rails

I am writing a website that will have to work with a complex system that has its own, non-rail, database structure. More specifically, this is a site for maintaining the WOW server infrastructure on the MaNGOS fork. There are a lot of difficulties here: starting with the fact that the conventions adopted in Rails and ActiveRecord are not always followed in MaNGOS tables, and ending with the fact that there are a lot of databases with which you need to work: 1 common realmd database with account tables, etc., and 3 each ( characters_N, mangos_N, scriptdev2_N) for each realm (game world). There are 2 realms running now, so there are 1 + 2*3 = 7 bases to work with. Well, there is a separate base for the site itself.
Here is the last problem (about several bases) and I don’t know how to solve it. I would like to stay on ActiveRecord, because I'm already used to it, so DataMapper and alternatives are considered last.
The database and table structures in the characters_N, mangos_N, and scriptdev2_N databases are similar, but differ in some places. For example, in the characters_1.characters table there are 29 columns, and in characters_2.characters there are 30 of them, one column has been added, but all such changes are insignificant and can be omitted in the model.
And now, the question is how it is more convenient to organize work with such databases ... The task is probably very non-trivial for Rails programmers.
In addition, I would like to make a modular system, so that it would be possible then to easily expand the number of worlds and, accordingly, add characters_N + 1, mangos_N + 1 and scriptdev2_N + 1 tables to the system. Plus, I would like to be able to create a model, maybe without a base, but that it would have all these bases of the worlds, and we could do something like World.each { |w| puts w.characters.online } where online is the model method in the characters_N.characters table.
For now, I’m procrastinating on the following idea: make a separate namespace for each base in the model (respectively, a separate directory: app\models\wow\server_N\ for example), and copy all models there, then change the namespace in each and establish_connection to the desired base do ...
For example, like this:

<pre>
class Wow::Base &lt; ActiveRecord::Base
  # ... тут что-то посвященное всем моделям из того
  def server_name
    # тут по неймспейсу определим номер нужной нам группы серверов
    1 # например выдается единица 
  end

  def database_name database, name; &quot;#{database.to_s}_#{id}&quot; end

end

class Wow::Server1::Base &lt; Wow::Base
end


class Wow::Server1::Character &lt; Wow::Server1::Base
  # тут соединяемся как-то так
  establish_connection database_name(:characters, server_name)
end
</pre><br/>

Then it would be possible to use them in the controller like this: @chars = Wow::Server1::Character.where(:online => 1)
.all , namely Server1) are involved in this. Maybe you can do something like Wow::Server[1]::Character.all or SERVERS[1]::Characters.all or Wow::Characters[1].all or Wow::Characters.set_server(1). all… By the way, I probably even know how to implement the last one… But I'm not sure that the associations (has_many, etc) will work :) But that's all right: what else can you think of?
Maybe you can try to apply the model extension method (google on "Rails model extensions (mincemeat models revisited)").
In general, how logical does it all look and who has any other ideas? Please help, my head can't take it all...

Answer 1

I found a solution! Hooray!
The main sugar
is app/models/wow/base.rb

class Wow::Base < ActiveRecord::Base
  self.abstract_class = true
  
  class << self
  
    def [](database_id)
      raise "DB name was not set! Use set_db_name to set it in yor model!" unless db_name
      self.abstract_class = true
      class_name = "#{database_id.to_s.classify}_#{self.name}"
      
      return Object.const_get(class_name) if Object.const_defined?(class_name)
      
      db_info = connection_info(database_id)
      raise ArgumentError, "There is no database with such ID!" unless db_info
      
      klass = Object.const_set class_name, Class.new(self)
      klass.establish_connection(db_info)
      klass
    end
    
    def set_db_name name; @@db_name = name; end
    def db_name; @@db_name; end
    
    private
    
    def connection_info database_id
      # эту не поясняю т.к. длинно, должна возвращать то, что принимает establish_connection
      WOW_CONFIG[database_id][:databases][db_name] 
    end
    
  end
end

Then for all bases we create a file, in my case:
app/models/wow/characters_db.rb
app/models/wow/mangos_db.rb
app/models/wow/realmd_db.rb
app/models/wow/scriptdev2_db.rb
With something like this (example for characters_db.rb, replace underline):
app/models/wow/characters_db.rb That's it!!! Well, almost... :) Now let's change all the model files a little, inherit them all from the tables we need: app/models/characters.rb And for other models we make similar replacements. Use after all this like this: Character[:main].count Account[:legacy].all ... etc.
class Wow::CharactersDb < Wow::Base
self.abstract_class = true
set_db_name :characters
end

class Character < Wow::CharactersDb
set_primary_key :guid
belongs_to :account, :foreign_key => :account
scope :online, where(:online => 1)
scope :offline, where(:online => 0)
scope :alliance, where(:race => [1,3,4,7,11])
scope :horde, where(:race => [2,5,6,8,10])
end
This is not really tested for the work of migrations, associations and other things, but it's already night, I'm going to sleep in general ... :) Thanks to everyone who thought about the problem.
PS: I think that everything will be fine with the associations... Although it may be necessary to change something in one place (where "...}_#{...")...

Answer 2

dug up the sources of the old project there, so -
app/models/film.rb config/database.yml
class Film < ActiveRecord::Base
establish_connection "feed"
set_table_name "film"
has_many :presets, :class_name => "Preset", :foreign_key => "subject_id"
end


feed:
adapter: mysql2
encoding: utf8
reconnect: false
database: feed
pool: 5
username: {someusername}
.........
production:
..........