How can I use two databases at the same time?

Kashia answered:

Please read the new tip I have posted, and rate this answer with a 4 or higher, if the tip is sufficiently explaining how to connect to 2 or more databases.

http://www.oxyliquit.de/tip/25

And thanks for pointing to the mysql_to_psql.rb.

The code is directly from that file. It uses a single Model (unlike the example I posted before) and moves this model to the new DB.

The code here doesn't match my results below. I think that it works, because it first populates the source database. Unlike the desired effect to collect data from an old database and move it into a new database.

To collect data from the old database, you just have to build a Og Model (or more, if there is more than one table to convert) which reflects the old database layout exactly.

# = Mysql to PostgreSQL migration example.
#
# A simple example to demonstrate the flexibility of
# Og. Two connections to different databases are 
# created and data is copied from a MySQL database
# to a PostgreSQL database.
#
# Og makes it easier to switch to a REAL database :)

require 'og'

# Configure databases.

psql_config = {
  :destroy => true,
  :name => 'test',
  :store => 'psql',
  :user => 'postgres',
  :password => 'navelrulez'
}

mysql_config = {
  :destroy => true,
  :name => 'test',
  :store => 'mysql',
  :user => 'root',
  :password => 'navelrulez'
}

# Initialize Og.

psql = Og.connect(psql_config)
mysql = Og.connect(mysql_config)

# An example managed object.
# Looks like an ordinary Ruby object.

class Article
  property :name, :body, String

  def initialize(name = nil, body = nil)
    @name, @body = name, body
  end
end

# First populate the mysql database.

mysql.manage(Article)

a1 = Article.create('name1', 'body1')
a1 = Article.create('name1', 'body1')
a1 = Article.create('name1', 'body1')

# Read all articles from Mysql.

articles = Article.all

# Switch to PostgreSQL.

psql.manage(Article)

# Store all articles.

for article in articles
  article.insert
end

# Fetch an article from PostgreSQL
# as an example. Lookup by name.

article = Article.find_by_name('name1')

platypus answered:

---

Warning note I have been checking my source database's **mysql** schema after a few trial runs with this activity. The things to tell people are:

• Model classes are only the object TABLES. None of the plumbing is visible with the **Og** instance.
• Og generates apparently missing tables. Early tests added new (unwanted) tables.
• Table names are based on the class-name (d'er). Make sure the class names are the final table names you want. These points may be obvious to Og mechanics. For us newbies they can be potential pitfalls.

platypus answered:

I found an example in the gems distribution to take records from mysql to PostgreSQL see

$og/examples/mysql_to_psql.rb

The code here doesn't match my results below. I think that it works, because it first populates the source database. Unlike the desired effect to collect data from an old database and move it into a new database.

Some refinement to the first answer (above). The order of statements is important.

$mysql  = Og.setup(:store => :mysql, ...)
$sqlite = Og.setup(:store => :sqlite, ... )

puts "\n   Source database ... "
  require  'Old_model'
  $mysql.manage_classes  Old_model 

puts "\n   Destination database ... "
  require  'New_model'
  $mysql.manage_classes  New_model 

Unfortunately the second part isn't working for me.

Src_model.all.each do | src |
    ....
  end

  puts "  found: "+ Src_model.all.length.to_s

## --> Gives
  found: 0

The order above will get you this far.

Kashia answered:

Preliminary answer, untested, repost from mail:

Is there a way to have TWO Og databases (say old-model.rb and new-model.rb)?

This is possible, but note that the og classes must be named differently, due to rubys highly dynamic type it is not sufficient to put the classes in different files (sorry if I'm preaching to the converted ;)).

Lets use 2 classes as an example:

class OldOgModel
   property :name
end
 
class NewOgModel
   property :firstname
   property :surename
end

I want to transfer from the "old database" to the "new database" -- I was hoping there'd be a tutorial or something that showed how two distinct databases can be used.

now create 2 Og instances.

$sqlite = Og.setup(:store => :sqlite, :name => 'first')
$mysql = Og.setup(:store => :mysql, ...)
 
require 'old-model.rb'
require 'new-model.rb'

# I think this should work, might be $sqlite.manager.manage_classes..
# just check for the manage_classes function 
# note: if you use this approach with Postgresql, the constraints won't be 
# added.
$sqlite.manage_classes OldOgModel
$mysql.manage_classes NewOgModel
 
OldOgModel.all.each do |m|
   n = NewOgModel.new
   n.firstname, n.surename = m.name.split(" ")
   n.save
end