Migrating a PHP database to a new Rails application

After rewriting most of a PHP application in Ruby on Rails, I realized I had a problem with the database. How to I get all the data from the database and put it into my rails database? The easy answer would probably be to copy it all into the /db/seeds.rb file and just migrate. But that sounds like a lot of typing! And typing means more mistakes. Also, how would I do this in the future? The app wasn’t that big, but it was big enough that a little strategy was called for.

I started following this post. This told me that I really needed to create a plugin to do it. Looking a bit further, I found Tim Riley’s acts-as-importable. That looked very promising and was what I eventually used. This app is on Rails 2.5. Here’s what I did:

Step 1 – Get the code!

First up, I got the data.

cd vendor/plugin/
git clone https://github.com/timriley/acts-as-importable.git

1 2	cd vendor/plugin/ git clone https://github.com/timriley/acts-as-importable.git

After that I didn’t know where to put it. Turns out, you just expand it so that you have a directory called /vendor/plugins/acts-as-importable.

From there, you should have a directory structure that looks like this:

vendor/plugins/acts-as-importable
|- lib
|    |- acts_as_importable.rb
|    |- core_extensions.rb
|
|- spec
|    |- bunch of stuff I didn't touch
|
|- tasks
|    |- acts_as_importable_tasks.rake
|
|- README.textile (not useful at all just points to the blog post)
|-init.rb

vendor/plugins/acts-as-importable

|- lib

| |- acts_as_importable.rb

| |- core_extensions.rb

|- spec

| |- bunch of stuff I didn't touch

|- tasks

| |- acts_as_importable_tasks.rake

|- README.textile (not useful at all just points to the blog post)

|-init.rb

While we’re here, I don’t remember if I modified the init.rb, but mine looks like this:

$:.unshift "#{File.dirname(__FILE__)}/lib"
require 'core_extensions'
require 'acts_as_importable'
ActiveRecord::Base.class_eval { include Acts::Importable }

$:.unshift "#{File.dirname(__FILE__)}/lib"

require 'core_extensions'

require 'acts_as_importable'

ActiveRecord::Base.class_eval { include Acts::Importable }

Step 2 add database config and back it up!

Then I figured I’d add the database of my legacy system to the config/database.yml file:

legacy:
  :adapter: mysql
  :host: somehost.example.com
  :username: root
  :password: password
  :database: mydatabase

legacy:

:adapter: mysql

:host: somehost.example.com

:username: root

:password: password

:database: mydatabase

Since this database was active and on the other server, I backed it up just in case!

mysqldump -p --databases mydatabase &gt;/tmp/myapp.msqlback

1	mysqldump -p --databases mydatabase >/tmp/myapp.msqlback

Then I took some time to make sure I could indeed access it with that username and password remotely, since I develop remotely. This took some fun mysql-foo. It turned out that my Mac didn’t have any mysql client on there. Not a problem:

sudo port install mysql5

1	sudo port install mysql5

Next up, we had to create some classes.

step 3: Create classes

This was well documented on Tim’s blog. What wasn’t obvious to me was where to put the classes. Luckily, in the comments section there was somebody who asked the question. Thanks for asking questions! They really help!

So I created app/models/legacy.rb Here it is in all its glory. I broke up the fille into 3 sections, but its all just one file.

class Legacy
end

class Legacy::Base &lt; ActiveRecord::Base 
  self.abstract_class = true
  establish_connection "legacy"
  acts_as_importable
end 

# imports all my categories
class Legacy::Category &lt; Legacy::Base
  # this is the legacy table
  set_table_name 'Categories'
  set_primary_key 'id'
  has_many :recipes

  def to_model
    # only insert them if really needed.
    unless ::Category.find_by_title(self.cat)
      ::Category.new do |c| 
        c.title = self.cat
        c.legacy_class = "Legacy::Category"
        c.legacy_id = self.id
      end 
    end 
  end 
end

class Legacy

end

class Legacy::Base < ActiveRecord::Base

self.abstract_class = true

establish_connection "legacy"

acts_as_importable

end

# imports all my categories

class Legacy::Category < Legacy::Base

# this is the legacy table

set_table_name 'Categories'

set_primary_key 'id'

has_many :recipes

def to_model

# only insert them if really needed.

unless ::Category.find_by_title(self.cat)

::Category.new do |c|

c.title = self.cat

c.legacy_class = "Legacy::Category"

c.legacy_id = self.id

end

class Legacy::User &lt; Legacy::Base
  set_table_name 'Users'
  set_primary_key 'id'
  has_many :recipes

  def to_model
    # self refers to the old class
    # check to see if its already there.
    unless ::User.find_by_username(self.userName)
      ::User.new do |u| 
        u.username = self.userName
        u.legacy_class = "Legacy::User"
        u.legacy_id = self.id
        u.email = self.email
        u.firstname = self.firstName
        u.lastname = self.lastName
        u.password = self.passwd
      end
    end
  end
end

class Legacy::User < Legacy::Base

set_table_name 'Users'

set_primary_key 'id'

has_many :recipes

def to_model

# self refers to the old class

# check to see if its already there.

unless ::User.find_by_username(self.userName)

::User.new do |u|

u.username = self.userName

u.legacy_class = "Legacy::User"

u.legacy_id = self.id

u.email = self.email

u.firstname = self.firstName

u.lastname = self.lastName

u.password = self.passwd

end

class Legacy::Recipe &lt; Legacy::Base

  set_table_name 'Recipes'
  set_primary_key 'id'

  belongs_to :category,
            :class_name =&gt; 'Legacy::Category',
            :foreign_key  =&gt; 'category'

  belongs_to :user,
            :class_name =&gt; 'Legacy::User',
            :foreign_key =&gt; 'submittedBy'

  def to_model
    self.submittedBy = self.submittedBy.to_i
    ::Recipe.new do |r|
      r.name = self.name
      r.category_id = Legacy::Category.lookup(self.category.try(:id__))
      # no foreign key in old table :-( so this didn't work...
      #r.user_id = Legacy::User.lookup(self.submittedBy.try(:id__)) 
      luser = Legacy::User.find(submittedBy)
      r.user_id = ::User.find_by_email(luser.email).id
      r.name = self.name
      r.source = self.author
      r.ingredients = self.ingredients
      r.directions = self.directions
      r.updated_at = self.date_entered
    end
  end
end

class Legacy::Importer
  def self.run
    Legacy::Category.import_all
    Legacy::User.import_all
    Legacy::Recipe.import_all

    Legacy::Recipe.flush_lookups!
    Legacy::User.flush_lookups!
    Legacy::Category.flush_lookups!
  end
end

class Legacy::Recipe < Legacy::Base

set_table_name 'Recipes'

set_primary_key 'id'

belongs_to :category,

:class_name => 'Legacy::Category',

:foreign_key => 'category'

belongs_to :user,

:class_name => 'Legacy::User',

:foreign_key => 'submittedBy'

def to_model

self.submittedBy = self.submittedBy.to_i

::Recipe.new do |r|

r.name = self.name

r.category_id = Legacy::Category.lookup(self.category.try(:id__))

# no foreign key in old table :-( so this didn't work...

#r.user_id = Legacy::User.lookup(self.submittedBy.try(:id__))

luser = Legacy::User.find(submittedBy)

r.user_id = ::User.find_by_email(luser.email).id

r.name = self.name

r.source = self.author

r.ingredients = self.ingredients

r.directions = self.directions

r.updated_at = self.date_entered

end

class Legacy::Importer

def self.run

Legacy::Category.import_all

Legacy::User.import_all

Legacy::Recipe.import_all

Legacy::Recipe.flush_lookups!

Legacy::User.flush_lookups!

Legacy::Category.flush_lookups!

end

Now for some explanations. This is a really simple app. Its just a cooking web site with users, recipes, and categories. Nothing more fancy than that. Starting with the Category class, we imported all the old ‘cat’ columns into the new Category.title attribute. In addition, we did a find_by_title to make sure it wasn’t already in the application. This made it so we didn’t have duplicate entries and I could run this as much as I want while letting the legacy application stay in place until I was finished developing.

Next up the user stuff. This was pretty much the same as the Category, but added a few more things. I lost the users passwords this way because I set up a new password system with bcrypt that was more secure. It was ok, I notified all the users later by generating default passwords.

The last model class is the recipe. This was a bit more tricky because of errors in the way the first database was created. Notice we had to use the belongs_to in this class and the has_many in the previous classes. One of the issues I had was that in the legacy database, there was no foreign key from the Recipes to the Users. Who designed this thing anyway? So I couldn’t use the lookup class that acts_as_importable uses. Bummer! That’s ok, I just used the Rails stuff to see if it was defined to get the new users ID.

The last thing there is the Legacy::Importer. That was where we do all the importing. Tim’s blog does a good job explaining that.

step 4: add some columns to the tables.

Since we needed to keep track of the new entries we added some columns to our databases:
ruby script/generate migration add_legacy_id_to_category legacy_id:integer
ruby script/generate migration add_legacy_class_to_category legacy_class:string
ruby script/generate migration add_legacy_id_to_user legacy_id:integer
ruby script/generate migration add_legacy_class_to_user legacy_class:string

Since we needed to keep track of the new entries we added some columns to our databases:

ruby script/generate migration add_legacy_id_to_category legacy_id:integer

ruby script/generate migration add_legacy_class_to_category legacy_class:string

ruby script/generate migration add_legacy_id_to_user legacy_id:integer

ruby script/generate migration add_legacy_class_to_user legacy_class:string

rake db:migrate

1	rake db:migrate

This filled in the entries so that the foreign key look up stuff would work.

step 5: Set up rake task

This was tricky for me too. I ended up creating vendor/plugins/acts-as-importable/tasks/acts_as_importable_tasks.rake. The contents are as follows:

namespace :acts_as_importable do
  desc "Import the legacy data."
  task :import =&gt; :environment do
    Legacy::Importer.run
  end
end

namespace :acts_as_importable do

desc "Import the legacy data."

task :import => :environment do

Legacy::Importer.run

end

Step 6: Ok, now do it!… oh no…

To import the data, we run:

rake acts_as_importable:import

1	rake acts_as_importable:import

But this kept giving me som AMC errors…

step7: Fix/modify the plugin

After reading up more on plugins and all that jazz I dared change the code. I changed the file /vendor/plugin/acts-as-importable/lib/acts_as_importable.rb. There was a reference to the AMC class (which was the origninal author’s project.). So I removed that so that from the acts_as_importable method. It now looks like this:

def acts_as_importable(options = {})
        # Store the import target class with the legacy class
        write_inheritable_attribute :importable_to, options[:to]

        # Don't extend or include twice. This will allow acts_as_importable to be called multiple times.
        # eg. once in a parent class and once again in the child class, where it can override some options.
        #extend  AMC::Acts::Importable::SingletonMethods unless self.methods.include?('import') &amp;&amp; self.methods.include?('import_all')
        #include AMC::Acts::Importable::InstanceMethods unless self.included_modules.include?(AMC::Acts::Importable::InstanceMethods)
        extend  Acts::Importable::SingletonMethods unless self.methods.include?('import') &amp;&amp; self.methods.include?('import_all')
        include Acts::Importable::InstanceMethods unless self.included_modules.include?(Acts::Importable::InstanceMethods)
      end

def acts_as_importable(options = {})

# Store the import target class with the legacy class

write_inheritable_attribute :importable_to, options[:to]

# Don't extend or include twice. This will allow acts_as_importable to be called multiple times.

# eg. once in a parent class and once again in the child class, where it can override some options.

#extend AMC::Acts::Importable::SingletonMethods unless self.methods.include?('import') && self.methods.include?('import_all')

#include AMC::Acts::Importable::InstanceMethods unless self.included_modules.include?(AMC::Acts::Importable::InstanceMethods)

extend Acts::Importable::SingletonMethods unless self.methods.include?('import') && self.methods.include?('import_all')

include Acts::Importable::InstanceMethods unless self.included_modules.include?(Acts::Importable::InstanceMethods)

end

(Notice I simply copied the 2 lines from above and removed the AMC:: portion from them. That seemed to get me further.

Step 8: Run it again!

rake acts_as_importable:import

1	rake acts_as_importable:import

After all that, it finally happened. It was incredible! Thanks so much Tim! All the tables were pulled and the new site took on all the attributes of the old site. But now I wonder what I did wrong or if there were easier steps I could have taken. Probably, but it worked, and that’s the important part. I launched the new site, patted myself on the back and went to bed. Hope this helps you migrate data!