Migrating Microsoft Access Databases to Ruby on Rails with PostgreSQL
Author: ceefour | Filed under: Opinions, Rails, Ruby, TipsIf you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
I’ve had a bunch of Visual Studio .NET projects using Access databases that I want to port to Ruby on Rails for some reasons (I dare not to list them). It’s impossible to continue using Access databases for “obvious” reasons as well. Since I’m familiar with PostgreSQL, I decided to move them to PostgreSQL.
Creating the Rails app is easy. Converting the database from the legacy one, though, proves to be quite tedious chore.
The first step is installing mdbtools, which is conveniently provided by Ubuntu as packages:
sudo apt-get install mdbtools
Now we’ll need to have a transitional database, to ease migration to a brand new schema. We’ll use mdb-schema to create the schema, and mdb-export to migrate the data. Make a rake task in lib/tasks/import.rake :
namespace :db do
desc 'Prepare transition database.'
task :trans do
`echo 'DROP DATABASE freedom_lyrictrans' | psql postgres`
`echo 'CREATE DATABASE freedom_lyrictrans' | psql postgres`
`mdb-schema ~/LyricMania.mdb postgres | replace 'Postgres_Unknown 0x0c' 'Text' 'Bool' 'Int2' | psql freedom_lyrictrans`
['artist', 'song', 'album'].each do |table|
`mdb-export -I -q \\\' -X \\\\ ~/LyricMania.mdb #{table} | sed -e 's/$/\\;/' | psql freedom_lyrictrans`
end
end
end
Now it’s time to run rake db:trans and we’re done setting up the transitional database. If you’re satisfied with that then there’s nothing else to do. But in my case I want to have a new schema, so some real migration is needed.
The next step is importing the transitional database to the newly created schema. First we have to define our databases in config/database.yml :
development:
adapter: postgresql
database: freedom_lyricdev
username: freedom_lyric
password:
host: localhost
trans:
adapter: postgresql
database: freedom_lyrictrans
username: freedom_lyric
password:
host: localhost
Now let’s make our new schema using the migrations.
db/migrate/001_create_artists.rb :
class CreateArtists < ActiveRecord::Migration
def self.up
transaction do
create_table :artists do |t|
t.column :name, :string
end
end
end
def self.down
drop_table :artists
end
end
db/migrate/002_create_albums.rb :
class CreateAlbums < ActiveRecord::Migration
def self.up
transaction do
create_table :albums do |t|
t.column :artist_id, :integer
t.column :title, :string
end
execute 'ALTER TABLE albums ADD CONSTRAINT fk_albums_artists FOREIGN KEY (artist_id) REFERENCES artists'
execute 'CREATE INDEX i_albums_artist_id ON albums (artist_id)'
end
end
def self.down
transaction do
drop_table :albums
end
end
end
db/migrate/003_create_songs.rb :
class CreateSongs < ActiveRecord::Migration
def self.up
transaction do
create_table :songs do |t|
t.column :album_id, :integer
t.column :artist_id, :integer
t.column :title, :string
t.column :keywords, :string
t.column :contributor, :string
t.column :lyric, :text
t.column :creation_time, :timestamp
t.column :modification_time, :timestamp
t.column :last_access_time, :timestamp
t.column :is_favorite, :bool
end
execute 'ALTER TABLE songs ADD CONSTRAINT fk_songs_album_id FOREIGN KEY (album_id) REFERENCES albums'
execute 'ALTER TABLE songs ADD CONSTRAINT fk_songs_artist_id FOREIGN KEY (artist_id) REFERENCES artists'
execute 'CREATE INDEX i_songs_album_id ON songs (album_id)'
execute 'CREATE INDEX i_songs_artist_id ON songs (artist_id)'
end
end
def self.down
transaction do
drop_table :songs
end
end
end
Don’t forget to do rake db:migrate here to create our new schema.
Let’s make another rake task to import the data from the transitional database to actual (development) database:
namespace :db do
def import_table(from, from_table, to, to_table)
puts "Importing #{from_table} to #{to_table}..."
from.select_all("SELECT * FROM #{from_table}").each_with_index do |row, i|
new_row = yield(row)
cols = []
values = []
new_row.each do |k, v|
cols << k.to_s
values << v
end
to.execute "INSERT INTO #{to_table} (#{cols.join(', ')}) VALUES ("+ values.map{|v|to.quote(v)}.join(', ') +")"
print '.' if (i % 50 == 0)
$stdout.flush
end
puts
end
desc 'Import from transition to current database. (db:trans must be run first)'
task :import => [:environment] do
puts 'Connecting...'
unless Kernel.const_defined?(:TransAR)
class TransAR < ActiveRecord::Base
establish_connection :trans
end
end
unless Kernel.const_defined?(:DevAR)
class DevAR < ActiveRecord::Base
end
end
TransAR.establish_connection(:trans)
trans = TransAR.connection
DevAR.establish_connection(RAILS_ENV)
dev = DevAR.connection
puts 'Clearing...'
['songs', 'albums', 'artists'].each do |table|
dev.execute "DELETE FROM #{table}"
end
import_table(trans, :artist, dev, :artists) do |row|
{ :id => row['artistid'], :name => (row['name'].strip rescue nil) }
&nbs
p; end
import_table(trans, :album, dev, :albums) do |row|
{ :id => row['albumid'], :artist_id => row['artistid'], :title => (row['title'].strip rescue nil) }
end
import_table(trans, :song, dev, :songs) do |r|
{ :id => r['songid'], :album_id => r['albumid'], :artist_id => r['artistid'],
:title => r['title'].strip, :keywords => (r['keywords'].strip rescue nil), :contributor => (r['contributor'].strip rescue nil),
:lyric => r['lyric'].strip, :creation_time => r['creationtime'], :modification_time => r['modificationtime'],
:last_access_time => r['lastaccesstime'], :is_favorite => r['isfavorite'] }
end
end
end
Importing the data is actually a straightforward SELECT then INSERT. Here, I used a utility method (import_table) that’s quasi-dynamic: it will accept a block that will be run for each row. This provides greater flexibility, down to per-row manipulation (i.e., you can manipulate/shuffle/process column values to your will), while still maintaining ease of use.
A simple rake db:import will output some eye candy. Then my app is ready to fly. 
Technorati Tags: activerecord, active record, rubyonrails, ruby, rails, migrate, migration, convert, conversion, access, ms access, microsoft access, postgres, postgresql
Related posts:
- Three Ways You Can Speed Up Your Fresh Rails Development A Ruby on Rails web application I’ve been developing...
Related posts brought to you by Yet Another Related Posts Plugin.