DBF to mysql converter with ruby

I write this ruby script because I have to port some legacy program made with foxpro to web based application made with PHP, CodeIgniter framework and mysql database. First I want to write the whole thing, but I don’t know anything about DBF format, so I switch to google, and promptly get the answer. Turn out that ruby had a gem to deal with DBF files. The name is, you guess it, dbf gem.

To install it, just run the command:

sudo gem install dbf

Each of the DBF files represent a single table in the database, and there are a bunch of them, so obviously I need to have the script to be able to generate the table schema and insert every single data from it into mysql table, for each table. I also like to make use of Active Record way to insert the data, so I don’t have to fiddle with raw SQL command, alas, I have to meet my first experience with metaprogramming in ruby in order to create the model class dynamicaly on the fly. The result is my first working and usefull ( for me at least ) ruby program.

The script (the comments shows the process):

#!/usr/bin/ruby

require 'rubygems'
require 'dbf'
require 'yaml'
require 'active_record'

def check_argument
  if ARGV.count.zero? # ARGV hold all the arguments given on the command line
    puts "Usage: dbf2mysql dirname" # puts always returns nil
  else
    dir = ARGV.first # get the first argument
    unless File.directory? dir # is it a directory?
      puts "#{dir} is not a folder"
    else
      Dir.new dir
    end
  end
end

def check_conf(dir,conf_file)
  if dir.entries.include? conf_file # the configuration must reside in the directory
    YAML::load_file(File.join(dir.path,conf_file)) # load the yaml file
  else
    puts 'configuration not found'
  end
end

dir = check_argument
db = check_conf(dir,'database.yml') unless dir.nil?
if db
  ActiveRecord::Base.establish_connection(db['default'])
  dir.entries.each do |file|
    fname = File.join(dir.path, file)
    table_name = file[0...(file.index '.' )]
    if file =~ /\.DBF$/
      table = DBF::Table.new(fname)
      eval(table.schema.gsub(table_name, table_name.downcase))
      self.table_name = table_name.downcase
      Object.const_set table_name.capitalize, self
    end

    table.records.each do |rec|
      model.create(rec.attributes)
    end
    puts "#{table.records.count} record(s) inserted"
  end
end

The script get the database configuration from the database.yaml file which reside in the same directory with the DBF files. To use this script first we create the destination database

sudo mysqladmin -u database-username -p create database-name

After that collect the DBF files in a directory along with the database.yml file. The database.yml may go like this

default:
adapter: mysql
encoding: utf8
database: pdam_stock_01_2008
pool: 5
username: root
password:
socket: /var/run/mysqld/mysqld.sock

to be able to run the script from the command line, give the script execute permission

chmod a+x dbf2mysql

if all set,  run the script with

dbf2mysql a-directory-with-bunch-of-dbf-files

if everything goes without error you’ll have a database with all the table and data already populated, so for a first timer in ruby this will be a milestone for me, cheers….