schadenfreude

Posted by Corban Brook Wed, 06 Dec 2006 21:08:32 GMT edit

ActiveRecord is a great tool to use for database maintenance as I explored in my previous article, Using ActiveRecord for Simple Maintenance Scripting, but what if you need to do tasks that require you to be connected to multiple databases at once.

Perhaps you want to compare records in one database and copy them to another, well heres how you do it.

Configuration

Setup a database.yml file to contain your different database configurations.

database.yml

database_1:
  adapter: mysql
  host: db1.host
  username: johnny 
  password: secret
  database: db1

database_2:
  adapter: mysql
  host: db2.host
  username: johnny 
  password: secret
  database: db2

Setup database connections

Create a class for each database connection. The establish_connection method will load your YAML config for the desired database. Your models will inherit from these classes.

require 'active_record'

$config = YAML.load_file(File.join(File.dirname(__FILE__), 'database.yml'))

class DatabaseA < ActiveRecord::Base
  establish_connection $config['database1']
end

class DatabaseB < ActiveRecord::Base
  establish_connection $config['database2']
end

Prepare Models

It is a good idea to separate each group of database models into its own module to avoid namespace clashes. In this example I have two databases which have the same schema (a production and development database perhaps). Each model must inherit from the Database class it belongs to, in this example either DatabaseA or DatabaseB.

module A
  class Person < DatabaseA
    has_one :email
  end

  class Email < DatabaseA
    belongs_to :person
  end
end

module B
  class Person < DatabaseB
    has_one :email
  end

  class Email < DatabaseB
    belongs_to :person
  end
end

Separating the models into 2 different modules allows us to reference the same model name across multiple databases without colliding namespaces. eg. A::Person and B::Person.

Usage

For whatever reason I ended up having person records stored in my development database and I need them copied over to a production database. The problem is that each person record has associated table data, in this case an emails table which must also be copied over with each record.

Copy a person from one database to another

First we will gank a person from the Database A (or development db) specifying the :include parameter in the finder args so that the email association is also stored in our receiving person object.

Now that the person is copied we can make a new person specifying the Database B::Person model with the ganked person.attributes as our begining params.

Next we can build associations, in this case the email association with the build_email method.

When saving all primary and foreign keys will be reset to the new record’s id.

  person = A::Person.find_by_name('Corban Brook', :include => [:email])

  new_person = B::Person.new person.attributes
  new_person.build_email person.email.attributes
  new_person.save

I would like to thank tshine from #radrails for his help.

Posted in Ruby | 8 comments

Leave a response | RSS feed for this post

1. # Mr eel said about 4 hours later:

   nukeedit

   Very nice!

   Now I’ve been trying to think of a nice way to share models and data between applications — in particular users — and this looks like it might be the way to do it.

2. # Jon Gretar said 1 day later:

   nukeedit

   Funny… I just wrote a similar tutorial for Camping couple of days ago.

3. # JonGretar said 2 days later:

   nukeedit

   Except this one is more useful and better an all ways….. ;)

4. # Aníbal Rojas said 4 days later:

   nukeedit

   Off Topic: This is just a quick note to invite you to register your blog at RubyCorner.com, a directory for blogs related to the Ruby Programming Language or any of the related technologies and projects.

5. # eee said 4 days later:

   nukeedit

   ee

6. # jan. said 9 days later:

   nukeedit

   Thanks for that! I’ve been looking for this functionality for a long time.

   Unfortunately, I can’t get it to work… Using the exact snippet as above pointing to a working database, I get the error:

   relation "database_as" does not exist

   The DatabaseA class is considered a table instead of a database connection.

7. # jan. said 9 days later:

   nukeedit

   Found the solution to the “database_as does not exist” problem (from the Rails Recipes book): had to add “self.abstract_class = true” to the DatabaseA class.

8. # jimmy said 12 days later:

   nukeedit

   Having troubles… so, is this:

   establish_connection $config[‘database1’]

   actually, supposed to be:

   establish_connection $config[‘database_1’]

   note the underscore in the second one.

Toggle website and email fields

Name:
Website:
Email:
Message: