The ELC Community Blog

A knowledge exchange on Ruby on Rails and Agile Development

Using and Testing Rails with Multiple Databases

by stevend on March 08, 2007

Using multiple databases

   1  <pre>class SomeBase < ActiveRecord::Base  
   2    use_db :prefix => "secdb_"
   3    self.abstract_class = true
   4  end
   5  
   6  class OtherDbModel < SomeBase
   7  end</pre>

   1  <pre>secdb_development:
   2    adapter: mysql
   3    database: secdb_development
   4    username: root
   5  
   6  secdb_test:
   7    adapter: mysql
   8    database: secdb_test
   9    username: root</pre>

Testing multiple databases

One issue with my plugin, as stated on the original article, is that testing becomes very difficult. First, fixtures are automatically inserted into the primary database. Second, other databases will not automatically have their schemas migrated from dev to test.

Solving the fixture problem

I first examined active_record/fixtures.rb and noticed the following problem:

   1  <pre>  def delete_existing_fixtures
   2      @connection.delete "DELETE FROM #{@table_name}", 'Fixture Delete'
   3    end
   4  
   5    def insert_fixtures
   6      values.each do |fixture|
   7        @connection.execute "INSERT INTO #{@table_name} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
   8      end
   9    end</pre>

These two methods are called automatically by the test helper when loading fixtures for a test. @connection was originally set to ActiveRecord::Base.connection, so the existing solution was not going to work. To solve this, I overrode those two methods in my plugin and replaced them with the following code:

   1  <pre>  alias_method :rails_delete_existing_fixtures, :delete_existing_fixtures
   2  
   3    def delete_existing_fixtures    
   4      m = get_model
   5      return rails_delete_existing_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
   6      connection = m.connection
   7      connection.delete "DELETE FROM #{m.table_name}", 'Fixture Delete'
   8    end
   9  
  10    alias_method :rails_insert_fixtures, :insert_fixtures
  11  
  12    def insert_fixtures
  13      m = get_model
  14      return rails_insert_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
  15      connection = m.connection
  16      values.each do |fixture|
  17        connection.execute "INSERT INTO #{m.table_name} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert'
  18      end
  19    end</pre>

This first code attempts to get the model associated with a fixture. If found, it asks that model if it uses a different database. FInally, it uses the connection of the model to execute the fixture INSERT and DELETE SQL commands. If any of this process fails, it falls back on the existing rails fixture methods.

Solving the schema migration problem

Rails typically does schema migrations using a rake task which runs before "rake test". It typically divides the work into 3 segments, dump_db_structure, clone_db_structure, and purge_db. The sequence is as follows:

• dump_db_structure dumps the development schema without data to an adapter-specific SQL file
• purge_db deletes all rows from the test database
• clone_db_structure imports the SQL dump into the test database

I simply duplicated the existing rake code, and modified it to use a different database connection. At the end of the day, I could execute a single command to migrate a second database. I chose to execute the command in my test helped in the following manner:

   1  <pre>unless defined?(MIGRATED_SEC_DB_FOR_TEST)
   2    UseDbTest.prepare_test_db(:prefix => "secdb_")
   3    MIGRATED_SEC_DB_FOR_TEST = true
   4  end</pre>

The syntax is very similar to the "use_db" helper.

Source code

Download the first release 0.0.1 of use_db rails plugin here.

See our other Rails Plugins

Comments

Zickzackv at 7:42 AM on June 24 2008

Using erb in database.yml does not work with your Plugin.

Her is a patch for it.

Index: lib/use_db.rb

- lib/use_db.rb (revision 108) + lib/use_db.rb (working copy)

   1  @ -55,7 +55,7 

@ return options else str = ”#{prefix}#{rails_env}#{suffix}” - connections = YAML.load(File.read ”#{RAILS_ROOT}/config/database.yml”) + connections = YAML::load(ERB.new(IO.read(”#{RAILS_ROOT}/config/database.yml”)).result) raise “Cannot find database specification. Configuration ’#{str}’ expected in config/database.yml” if (connections[str].nil?) return connections[str] end

Thanks Zickzackv

NgTzeYang at 10:50 PM on July 21 2008

I’m using primarily mysql and happen to have only a model ExampleModel using postgresql, during fixture loading, the column names for ExampleModel are quoted the mysql way using ”`” (backquotes), which causes the postgresql adapter to throw error. So i’ve to add the followings to override_fixtures.rb:

class Fixture
  alias_method :rails_key_list, :key_list
  alias_method :rails_value_list, :value_list
  def key_list(connection=nil) 
    if connection.nil?
      rails_key_list
    else
      columns = @fixture.keys.collect{ |column_name| connection.quote_column_name(column_name) }
      columns.join(", ")
    end
  end
  def value_list(connection=nil)
    if connection.nil?
      rails_value_list
    else
      list = @fixture.inject([]) do |fixtures, (key, value)|
        col = model_class.columns_hash[key] if model_class.respond_to?(:ancestors) && model_class.ancestors.include?(ActiveRecord::Base)
        fixtures << connection.quote(value, col).gsub('[^\]\\n', "\n").gsub('[^\]\\r', "\r")
      end
      list * ', '
    end
  end
end

Hope the above helps for anyone facing the same prob. Btw, pls forgive if the above code doesn’t format properly.

NgTzeYang at 11:23 PM on July 21 2008

Sorry … missed out the other alteration needed to get the previous post to work:

class Fixtures
   ...
  def insert_fixtures
    m = get_model
    return rails_insert_fixtures unless m && m.respond_to?(:uses_db?) && m.uses_db?
    connection = m.connection
    values.each do |fixture|
     # note the additional arg for  fixture.key_list() & fixture.value_list()
      connection.execute "INSERT INTO #{m.table_name} (#{fixture.key_list(connection)}) VALUES (#{fixture.value_list(connection)})", 'Fixture Insert' 
    end
  end
  ...
end

Add a comment

Name: E-Mail: URL:

Comment: