CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3.2
harshit
harshit

Reputation: 7951

Interacting with same table across multiple database using rails

My requirement is like an inventory management. I have to create a web app which interacts with same table in all of our dev environments (5 for now) and do update ,delete etc.

I read about connecting with multiple DB using :

 class Qa1 < ActiveRecord::Base
    self.abstract_class = true
      establish_connection "qa1_#{Rails.env}"
    end

    class Qa2 < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "qa2_#{Rails.env}"
end

Now i have a single table 

class Table1Qa1 < Qa1
end

class Table1Qa2 < Qa2
end

class Table1Qa3 < Qa3
end

This works , but i am thinking is there a better way to connect instead of creating so many models object for same table?

I am using Rails 3.2 and ruby 1.9.3

Thanks

Upvotes: 1

Views: 1183

Answers (3)

Andrea Fiore
Andrea Fiore

Reputation: 1638

I guess one important thing to figure out is: do you really need to do it this way? If all what you need is to synchronise the content of one or more tables across different databases, perhaps writing a cron job that periodically copies records from a master table into other tables would be as easy as:

connection_params.each do |params|
  ActiveRecord::Base.establish_connection(params)

  sql = "INSERT INTO #{params[:table_name]} (Foo, Bar, Fizz, Buzz)
        SELECT Foo, Bar, Fizz, Buzz
        FROM master_qa"
        #optionally WHERE ...

  ActiveRecord::Base.connection.execute(sql)
end

Alternatively, if you have to apply different logic to different environments, then implementing one ActiveRecord model for each table might not be such a bad idea. After all, you can keep the common logic into a Ruby module and have the module mixed in by all your models.

module QA
  def self.order_by_foo
    order("foo DESC")
  end

  def self.included(model_class)
    # This hooks gets fired whenever the QA gets included by a class.
    # You can use it to call class methods on your models. Eg:
    model_class.validates :foo, :presence => true
  end
end

class Qa1 < ActiveRecord::Base
  include QA
  establish_connection(:qa1)
end

class Qa2 < ActiveRecord::Base
  include QA
  establish_connection(:qa2)
end

class Qa3 < ActiveRecord::Base
  include QA
  establish_connection(:qa3)
end

Upvotes: 2

beck03076
beck03076

Reputation: 3308

Use the below method is a General model.

When calling this method, pass the conf name in the place of db_conf.

def self.get_data(id,db_conf,table_name)

    connection_hash = configurations[db_conf]

    establish_connection connection_hash

    con = connection()

          sql = "SELECT *
                 FROM " + table_name

     result = con.execute(sql)

     remove_connection

     establish_connection configurations["RAILS_ENV"]

     result

  end

database.yml

db_one_conf:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: one
  pool: 8
  username: root
  password: bomchickuwahwah
  socket: /var/run/mysqld/mysqld.sock

db_two_conf:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: two
  pool: 8
  username: root
  password: bomchickuwahwah
  socket: /var/run/mysqld/mysqld.sock

db_three_conf:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: three
  pool: 8
  username: root
  password: bomchickuwahwah
  socket: /var/run/mysqld/mysqld.sock

Im establishing a connection to a new database and removing the connection and connecting back to the old database.

Upvotes: 0

Sam
Sam

Reputation: 3067

You could try the ar-octopus gem - https://github.com/tchandy/octopus

It looks like you can then do this,

Octopus.using(:db1) do
  User.create(:name => "Mike")
end

Octopus.using(:db2) do
  User.create(:name => "Mike")
end

@user_db1 = User.using(:db1).find_by_name("Sam")
@user_db2 = User.using(:db2).find_by_name("Sam")

Upvotes: 1

Related Questions