Tag Archive for 'ruby php migrations mysql postgresql'

In recent weeks I was busy converting a fairly large PHP application to Rails. The existing PHP application is about 65.500 lines of intermingled PHP and HTML/CSS code. Yep, a classic PHP application without any database abstraction layer, no templating, no MVC. This is why I dubbed it “large”, but replacing that with “crappy” would be fine too

I divided the project in a couple of deliverables:

1. Data model analysis, redesign, data migration
2. HTML conversion to layout/partials
3. Business logic analysis and conversion to Ruby
4. Integration with external web application
5. Testing
6. Production deployment

In typical agile/xp/scrum/ad-hoc/you-name-it fashion, all but the last deliverable were not actually delivered in full until production day.

Since I wanted to get something visible as soon as possible and with semi-live data the first thing I did was analyze the current data model. The model consisted of 46 tables. 8 tables were discarded right away (code/data rot). After further analysis the table count was reduced to 23. Next up was converting and migrating the current data set to the new data model. The PHP site was using Mysql 4.23 while the Rails version would be using PostgreSQL 8.1. This was BTW also the first Rails project where I religiously used migrations and migrations absolutely rock!. A total of 53 migration scripts were generated during the course of development. Back to conversion; Instead of writing CSV/YAML exporter/importer scripts I used the following mechanism to import/convert legacy data objects:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# Read in Rails config
config = Rails::Configuration.new

# Set up the class for the table/objects we want to convert
# Of course the legacy database does not follow Rails conventions
# so we always use table_name to fix this
class OldVenue < ActiveRecord::Base
  def self.table_name
    "adressen"
  end
end

# Set up a connection to the legacy MySQL database
ActiveRecord::Base.establish_connection config.database_configuration["old_production"]

# Save the connection
mysql_connection = OldVenue.connection

# Establish a connection to our new shiny PostgreSQL database
ActiveRecord::Base.establish_connection config.database_configuration["production"]

# But restore the mysql connection for the legacy ActiveRecord class
OldVenue.connection = mysql_connection

The above code allows the conversion script to access both MySQL and PostgreSQL databases simultaneously.This means I can do something like:

1
2
3
4
5
6
7
8
9
10

old_venues = OldVenue.find(:all)

for o in old_venues do
  n = Venue.new
  n.id = o.id
  n.name = o.naam
  n.zip = o.postcode
  # More field assignments
  n.save
end

..Instant data migration from MySQL to PostgreSQL without messy CSV/YAML export/import!