Yan Pritzker photographer, entrepreneur, software engineer, musician, skier

ActiveRecord magic for importing across different database types

Posted 29 February 2008 @ 7pm | Tagged thoughts

Submit to HN

I recently had to import some old tickets from Collaboa (postgres) into Trac (sqlite3). After trying some conventional data dumping methods and getting frustrated with dealing with all the formatting issues, I came up with a rather simple solution by using ActiveRecord to do the dirty work with two database connections:

require 'rubygems'
require 'active_record'
$config = YAML.load_file("#{RAILS_ROOT}/config/database.yml")

module Trac
  class Ticket < ActiveRecord::Base
    establish_connection $config['trac'];
    set_table_name 'ticket'
  end
end

module Collaboa
  class Ticket < ActiveRecord::Base
    establish_connection $config['collaboa']
    set_table_name 'tickets'
  end
end

# To import tickets, just do this!
# Collaboa::Ticket.find(:all, :conditions => {:status_id => 1, :project_id => 1}).each do |t|
#   Trac::Ticket.create(:summary => t.summary, :description => t.content, :time => Time.now)
# end

Make sure you set up your database.yml properly

trac:
  adapter: sqlite3
  dbfile: # your file goes here

collaboa:
  adapter: postgresql
  # other settings...

Afterwards, all I had to fix some minor data issues with the imported data to make Trac happy:


sqlite> update ticket set type='boog',status='new',time=12345,changetime=12345;


Voila!