{"id":346,"date":"2011-03-10T15:48:56","date_gmt":"2011-03-10T21:48:56","guid":{"rendered":"http:\/\/benincosa.com\/blog\/?p=346"},"modified":"2014-11-19T11:25:16","modified_gmt":"2014-11-19T17:25:16","slug":"migrating-a-php-database-to-a-new-rails-application","status":"publish","type":"post","link":"https:\/\/benincosa.com\/?p=346","title":{"rendered":"Migrating a PHP database to a new Rails application"},"content":{"rendered":"
After rewriting most of a PHP application in Ruby on Rails, I realized I had a problem with the database. \u00a0How to I get all the data from the database and put it into my rails database? \u00a0The easy answer would probably be to copy it all into the \/db\/seeds.rb file and just migrate. \u00a0But that sounds like a lot of typing! \u00a0And typing means more mistakes. \u00a0Also, how would I do this in the future? \u00a0The app wasn’t that big, but it was big enough that a little strategy was called for.<\/div>\n

I started following this pos<\/a>t. \u00a0This told me that I really needed to create a plugin to do it. \u00a0Looking a bit further, I found Tim Riley’s acts-as-importable<\/a>. That\u00a0looked very promising and was what I eventually used. \u00a0This app is on Rails 2.5. \u00a0Here’s what I did:<\/p>\n

Step 1 – Get the code!<\/strong><\/p>\n

First up, I got the data.<\/p>\n

cd vendor\/plugin\/\r\ngit clone https:\/\/github.com\/timriley\/acts-as-importable.git<\/pre>\n
After that I didn’t know where to put it. \u00a0Turns out, you just expand it so that you have a directory called \/vendor\/plugins\/acts-as-importable.<\/strong><\/p>\n
From there, you should have a directory structure that looks like this:<\/p>\n
vendor\/plugins\/acts-as-importable\r\n|- lib\r\n|    |- acts_as_importable.rb\r\n|    |- core_extensions.rb\r\n|\r\n|- spec\r\n|    |- bunch of stuff I didn't touch\r\n|\r\n|- tasks\r\n|    |- acts_as_importable_tasks.rake\r\n|\r\n|- README.textile (not useful at all just points to the blog post)\r\n|-init.rb<\/pre>\n
While we’re here, I don’t remember if I modified the init.rb<\/strong>, but mine looks like this:<\/p>\n
$:.unshift \"#{File.dirname(__FILE__)}\/lib\"\r\nrequire 'core_extensions'\r\nrequire 'acts_as_importable'\r\nActiveRecord::Base.class_eval { include Acts::Importable }<\/pre>\n
Step 2 add database config and back it up!<\/strong><\/p>\n
Then I figured I’d add the database of my legacy system to the config\/database.yml<\/strong> file:<\/p>\n
legacy:\r\n  :adapter: mysql\r\n  :host: somehost.example.com\r\n  :username: root\r\n  :password: password\r\n  :database: mydatabase<\/pre>\n
Since this database was active and on the other server, I backed it up just in case!<\/p>\n
mysqldump -p --databases mydatabase >\/tmp\/myapp.msqlback<\/pre>\n
Then I took some time to make sure I could indeed access it with that username and password remotely, since I develop remotely. \u00a0This took some fun mysql-foo. \u00a0It turned out that my Mac didn’t have any mysql client on there. \u00a0Not a problem:<\/p>\n
sudo port install mysql5<\/pre>\n
Next up, we had to create some classes.<\/p>\n
step 3: Create classes<\/strong><\/p>\n
This was well documented on Tim’s blog<\/a>. \u00a0What wasn’t obvious to me was where to put the classes. \u00a0Luckily, in the comments section there was somebody who asked the question. \u00a0Thanks for asking \u00a0questions! \u00a0They really help!<\/p>\n
So I created app\/models\/legacy.rb <\/strong>Here it is in all its glory. \u00a0I broke up the fille into 3 sections, but its all just one file.<\/p>\n
\r\nclass Legacy\r\nend\r\n\r\nclass Legacy::Base < ActiveRecord::Base \r\n  self.abstract_class = true\r\n  establish_connection \"legacy\"\r\n  acts_as_importable\r\nend \r\n\r\n# imports all my categories\r\nclass Legacy::Category < Legacy::Base\r\n  # this is the legacy table\r\n  set_table_name 'Categories'\r\n  set_primary_key 'id'\r\n  has_many :recipes\r\n\r\n  def to_model\r\n    # only insert them if really needed.\r\n    unless ::Category.find_by_title(self.cat)\r\n      ::Category.new do |c| \r\n        c.title = self.cat\r\n        c.legacy_class = \"Legacy::Category\"\r\n        c.legacy_id = self.id\r\n      end \r\n    end \r\n  end \r\nend<\/pre>\n
class Legacy::User < Legacy::Base\r\n  set_table_name 'Users'\r\n  set_primary_key 'id'\r\n  has_many :recipes\r\n\r\n  def to_model\r\n    # self refers to the old class\r\n    # check to see if its already there.\r\n    unless ::User.find_by_username(self.userName)\r\n      ::User.new do |u| \r\n        u.username = self.userName\r\n        u.legacy_class = \"Legacy::User\"\r\n        u.legacy_id = self.id\r\n        u.email = self.email\r\n        u.firstname = self.firstName\r\n        u.lastname = self.lastName\r\n        u.password = self.passwd\r\n      end\r\n    end\r\n  end\r\nend<\/pre>\n
class Legacy::Recipe < Legacy::Base\r\n\r\n  set_table_name 'Recipes'\r\n  set_primary_key 'id'\r\n\r\n  belongs_to :category,\r\n            :class_name => 'Legacy::Category',\r\n            :foreign_key  => 'category'\r\n\r\n  belongs_to :user,\r\n            :class_name => 'Legacy::User',\r\n            :foreign_key => 'submittedBy'\r\n\r\n  def to_model\r\n    self.submittedBy = self.submittedBy.to_i\r\n    ::Recipe.new do |r|\r\n      r.name = self.name\r\n      r.category_id = Legacy::Category.lookup(self.category.try(:id__))\r\n      # no foreign key in old table :-( so this didn't work...\r\n      #r.user_id = Legacy::User.lookup(self.submittedBy.try(:id__)) \r\n      luser = Legacy::User.find(submittedBy)\r\n      r.user_id = ::User.find_by_email(luser.email).id\r\n      r.name = self.name\r\n      r.source = self.author\r\n      r.ingredients = self.ingredients\r\n      r.directions = self.directions\r\n      r.updated_at = self.date_entered\r\n    end\r\n  end\r\nend\r\n\r\nclass Legacy::Importer\r\n  def self.run\r\n    Legacy::Category.import_all\r\n    Legacy::User.import_all\r\n    Legacy::Recipe.import_all\r\n\r\n    Legacy::Recipe.flush_lookups!\r\n    Legacy::User.flush_lookups!\r\n    Legacy::Category.flush_lookups!\r\n  end\r\nend<\/pre>\n
Now for some explanations. \u00a0This is a really simple app. \u00a0Its just a cooking web site with users, recipes, and categories. \u00a0Nothing more fancy than that. \u00a0 Starting with the Category class, we imported all the old ‘cat’ columns into the new Category.title attribute. \u00a0In addition, we did a find_by_title to make sure it wasn’t already in the application. \u00a0This 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.<\/p>\n
Next up the user stuff. \u00a0This was pretty much the same as the Category, but added a few more things. \u00a0I lost the users passwords this way because I set up a new password system with bcrypt that was more secure. \u00a0It was ok, I notified all the users later by generating default passwords.<\/p>\n
The last model class is the recipe. \u00a0This was a bit more tricky because of errors in the way the first database was created. \u00a0Notice we had to use the belongs_to in this class and the has_many in the previous classes. \u00a0One of the issues I had was that in the legacy database, there was no foreign key from the Recipes to the Users. \u00a0Who designed this thing anyway? \u00a0So I couldn’t use the lookup class that acts_as_importable uses. \u00a0Bummer! \u00a0That’s ok, I just used the Rails stuff to see if it was defined to get the new users ID.<\/p>\n
The last thing there is the Legacy::Importer. \u00a0That was where we do all the importing. \u00a0Tim’s blog does a good job explaining that.<\/p>\n
step 4: add some columns to the tables<\/strong>.<\/p>\n
Since we needed to keep track of the new entries we added some columns to our databases:\r\nruby script\/generate migration add_legacy_id_to_category legacy_id:integer\r\nruby script\/generate migration add_legacy_class_to_category legacy_class:string\r\nruby script\/generate migration add_legacy_id_to_user legacy_id:integer\r\nruby script\/generate migration add_legacy_class_to_user legacy_class:string<\/pre>\n
rake db:migrate<\/pre>\n
\n
This filled in the entries so that the\u00a0foreign\u00a0key look up stuff would work.<\/p>\n
step 5: Set up rake task<\/strong><\/p>\n
This was tricky for me too. \u00a0I ended up creating\u00a0vendor\/plugins\/acts-as-importable\/tasks\/acts_as_importable_tasks.rake<\/em><\/strong>. \u00a0The contents are as follows:<\/p>\n
namespace :acts_as_importable do\r\n  desc \"Import the legacy data.\"\r\n  task :import => :environment do\r\n    Legacy::Importer.run\r\n  end\r\nend<\/pre>\n
Step 6: Ok, now do it!… oh no…<\/strong><\/p>\n
 <\/strong> To import the data, we run:<\/p>\n
rake acts_as_importable:import<\/pre>\n
But this kept giving me som AMC errors…<\/p>\n
step7: Fix\/modify the plugin<\/strong><\/p>\n
After reading up more on plugins and all that jazz I dared change the code. \u00a0I changed the file \/vendor\/plugin\/acts-as-importable\/lib\/acts_as_importable.rb.<\/strong> There was a reference to the AMC class (which was the origninal author’s project.). \u00a0So I removed that so that from the acts_as_importable method. \u00a0It now looks like this:<\/p>\n
def acts_as_importable(options = {})\r\n        # Store the import target class with the legacy class\r\n        write_inheritable_attribute :importable_to, options[:to]\r\n\r\n        # Don't extend or include twice. This will allow acts_as_importable to be called multiple times.\r\n        # eg. once in a parent class and once again in the child class, where it can override some options.\r\n        #extend  AMC::Acts::Importable::SingletonMethods unless self.methods.include?('import') && self.methods.include?('import_all')\r\n        #include AMC::Acts::Importable::InstanceMethods unless self.included_modules.include?(AMC::Acts::Importable::InstanceMethods)\r\n        extend  Acts::Importable::SingletonMethods unless self.methods.include?('import') && self.methods.include?('import_all')\r\n        include Acts::Importable::InstanceMethods unless self.included_modules.include?(Acts::Importable::InstanceMethods)\r\n      end<\/pre>\n
(Notice I simply copied the 2 lines from above and removed the AMC:: portion from them. \u00a0That seemed to get me further.<\/p>\n
Step 8: Run it again!<\/strong><\/p>\n
rake acts_as_importable:import<\/pre>\n
After all that, it finally happened. \u00a0It was incredible! \u00a0Thanks so much Tim! \u00a0All the tables were pulled and the new site took on all the attributes of the old site. \u00a0 But now I wonder what I did wrong or if there were easier steps I could have taken. \u00a0Probably, but it worked, and that’s the important part. \u00a0I launched the new site<\/a>, patted myself on the back and went to bed. \u00a0Hope this helps you migrate data!<\/p>\n","protected":false},"excerpt":{"rendered":"
After rewriting most of a PHP application in Ruby on Rails, I realized I had a problem with the database. \u00a0How to I get all the data from the database and put it into my rails database? \u00a0The easy answer would probably be to copy it all into the \/db\/seeds.rb file and just migrate. \u00a0But…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[24],"tags":[113,114,983],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/benincosa.com\/index.php?rest_route=\/wp\/v2\/posts\/346"}],"collection":[{"href":"https:\/\/benincosa.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/benincosa.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/benincosa.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/benincosa.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=346"}],"version-history":[{"count":3,"href":"https:\/\/benincosa.com\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions"}],"predecessor-version":[{"id":2797,"href":"https:\/\/benincosa.com\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions\/2797"}],"wp:attachment":[{"href":"https:\/\/benincosa.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/benincosa.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/benincosa.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}