Sunday, July 31, 2005

 

Fun with ActiveRecord

In my spare time, I've been playing around with the Ruby On Rails web application framework. It's really a lot of fun. I grew up using embedded SQL and MFC's ODBC wrapper classes to do database access, which were definitely not fun, so The Rails Way of accessing databases seems magical and wonderful.

To give myself a playground in which to explore the Rails framework, I've started a pilot's logbook web application. It has an appearance and functionality similar to that of LogShare, but has a few extra features (like recording of instruction and rental costs). After doing all the typical Rails scaffold-generation-and-tweaking to get a few CRUD screens working, I decided that rather than re-entering all my logbook information manually, I'd rather just import the data from my existing LogShare account. LogShare provides an "Export Your Logbook" feature that dumps all the data into a comma-separated or tab-separated file. So I dumped the data and then started figuring out how to write a utility that uses the Rails ActiveRecord class to insert all this data into my database.

The ActiveRecord class, which provides an object-relational mapping (ORM), can be used independently of the rest of the Rails framework, so it makes sense to use it for a simple database-loading utility. The first thing I did was write a simple test program to verify that I knew how to connect to a database and access its tables. Here's what I wrote:

require 'rubygems'
require_gem 'activerecord'

# Connect to MySQL 'flightlog_development' database
database_spec = {
  :adapter  => 'mysql',
  :host     => 'localhost',
  :database => 'flightlog_development',
  :socket   => '/usr/local/mysql/run/mysql_socket',
  :username => 'kdj',
  :password => 'mypassword'
}
ActiveRecord::Base.establish_connection database_spec

# Mapping for 'airplanes' table
class Airplane < ActiveRecord::Base
end

# Mapping for 'flights' table
class Flight < ActiveRecord::Base
  belongs_to :airplane
end

# Print info for each record in the 'flights' table
flights = Flight.find :all, :order => 'occurred_on'
flights.each { |f|
  puts "#{f.occurred_on}\t#{f.total_hours}\t#{f.airplane.tail_number}\t#{f.route_of_flight}"
}

The MySQL database I'm using for this app is called "flightlog_development". There are a few parameters I have to pass to the establish_connection function to get connected to that database.

My database has a table called "flights" that contains the data for each logged flight, and a table called "airplanes" that contains the data for each airplane I fly. To get object-oriented access to the contents of these tables, all I have to do is declare classes called "Flight" and "Airplane" descended from ActiveRecord. Note that I don't have to specify anything about how these classes and their attributes get mapped to the database tables; the framework automatically figures out the table names from the names of the classes, and will automatically map database columns to instance attributes.

The only thing the framework can't figure out for itself is the relationship between the two tables. The flights table contains a column called "airplane_id" which is a foreign key into the airplanes table's "id" column. All I have to do is add the belongs_to declaration to the Flights class, and the framework handles the relationship automagically. (I could also add "has_many :flights" to the Airplane class so that the framework could handle the relationship in the other direction, but it's not necessary for this application.)

So, with all that stuff declared, it is pretty easy to query the Flights table and show its data. The expression "Flight.find :all, :order => 'occurred_on'" creates a list of Flight instances corresponding to the rows in the table, sorted by date. For each row, I print a few attributes. Note the use of "f.airplane.tail_number", which causes the framework to automatically query the database for the Airplane instance associated with the Flight's airplane_id. I didn't have to write my own SQL join. Cool.

I really like this minimal-coding approach. All you have to do is follow a few naming conventions, and the framework handles all the mapping for you. I know many programmers hate it when there is too much "magic" like this, but I believe that it really can boost one's productivity. (Yes, the Kool-Aid tastes good. I'd like some more, please.)

OK, so I wrote a little program that accessed the database. Now I just needed to figure out how to parse the LogShare data, transform it as necessary, and insert it into my database. Here's what I ended up with:

# This program reads lines from a tab-separated file generated by the
# 'Export Your Logbook' operation from http://www.logshare.com, then
# imports the data into the flightlog database.

require 'rubygems'
require_gem 'activerecord'

# Connect to MySQL 'flightlog_development' database
database_spec = {
  :adapter  => 'mysql',
  :host     => 'localhost',
  :database => 'flightlog_development',
  :socket   => '/usr/local/mysql/run/mysql_socket',
  :username => 'kdj',
  :password => 'mypassword'
}
ActiveRecord::Base.establish_connection database_spec

# Use the database's 'airplane_types' table
class AirplaneType < ActiveRecord::Base
  # Find an airplane type with the given name
  def self.find_by_name(name)
    AirplaneType.find :first, :conditions => [ 'short_name = ?', name ]
  end

  # Create a new airplane type with the given name
  def self.create_new_airplane_type(name)
    airplane_type = AirplaneType.new
    airplane_type.full_name = airplane_type.short_name = airplane_type.abbreviation = name;
    if airplane_type.save
      $stderr.puts "Created new airplane type \"#{name}\""
    end
    find_by_name(name)
  end
end

# Use the database's 'airplanes' table
class Airplane < ActiveRecord::Base
  # Declare relationship between Airplane and AirplaneType
  belongs_to :airplane_type

  # Get the Airplane instance with the specified tail number
  def self.find_by_tail_number(tail_number)
    Airplane.find :first, :conditions => ['tail_number = ?', tail_number]
  end

  # Create a new Airplane instance with the specified tail number and type
  def self.create_new_airplane(tail_number, airplane_type_name)
    airplane = Airplane.new
    airplane.tail_number = tail_number;
    airplane.airplane_type = AirplaneType.find_by_name(airplane_type_name)
    if ! airplane.airplane_type
      airplane.airplane_type = AirplaneType.create_new_airplane_type(airplane_type_name)
    end
    if airplane.save
      $stderr.puts "Created new airplane \"#{tail_number}\" of type \"#{airplane_type_name}\""
    end
    find_by_tail_number(tail_number)
  end
end

# Use the database's 'flights' table
class Flight < ActiveRecord::Base
  # declare relationship between Flight and Airplane
  belongs_to :airplane
end

# Convert MM/DD/YY to YYYY-MM-DD
def import_date_to_sql_date(date)
  date =~ /(..)\/(..)\/(..)/
  "20#{$3}-#{$1}-#{$2}"
end

# For a given input line, extract the data, transform it as necessary,
# and insert it into the database
def import_line(line)
  # fill the params hash with data to be inserted into the database
  params = {}

  # break up the tab-delimited line
  columns = line.split("\t")

  occurred_on                         = columns[0]
  airplane_type_name                  = columns[1]
  tail_number                         = columns[2]
  params[:route_of_flight]            = columns[3]
  params[:total_hours]                = columns[4]
  params[:day_landings]               = columns[5]
  params[:night_landings]             = columns[6]
  params[:actual_instrument_hours]    = columns[7]
  params[:simulated_instrument_hours] = columns[8]
  approaches                          = columns[9]
  simulator_hours                     = columns[10]
  params[:night_hours]                = columns[11]
  params[:cross_country_hours]        = columns[12]
  solo_hours                          = columns[13]
  params[:pilot_in_command_hours]     = columns[14]
  second_in_command_hours             = columns[15]
  params[:dual_received_hours]        = columns[16]
  dual_given_hours                    = columns[17]
  remarks                             = columns[18]

  # find or create the Airplane for the given tail number
  airplane = Airplane.find_by_tail_number(tail_number)
  if ! airplane
    airplane = Airplane.create_new_airplane(tail_number, airplane_type_name)
  end
  params[:airplane] = airplane

  params[:occurred_on] = import_date_to_sql_date(occurred_on)

  # strip opening and closing quotation marks from 'remarks' column
  params[:remarks] = remarks[1..(remarks.size - 3)]

  # logshare.com doesn't record number of takeoffs, so let's assume that
  # our number of takeoffs will equal our number of landings
  params[:day_takeoffs] = params[:day_landings]
  params[:night_takeoffs] = params[:night_landings]

  # create the new record and save it to the database
  if ! Flight.new(params).save
    $stderr.puts "Unable to save flight #{params[:date_occurred]} #{params[:route_of_flight]}"
  end
end


# MAIN PROGRAM STARTS HERE

# skip the first line of input, which contains column headings
gets

# process each remaining line of input
while gets
  import_line $_
end

Obviously, this is a little longer than the previous program, but the database access is not much more complicated. In addition to the flights and airplanes tables I mentioned earlier, there is a table called "airplane_types" that contains the data for each particular type of airplane. For example, there is an airplane_types row for a Piper Warrior, and another row for a Cessna 172. The airplanes table has a column "airplane_type_id" that is a foreign key to airplane_type's "id" column. So I declared a class AirplaneType, and added "belongs_to :airplane_type" to the Airplane class so that the framework would handle the relationship.

For each line read from the imported file, I create a new Flight with the appropriate values and save it to the database. If a particular Flight refers to an airplane that doesn't already exist in the database, then a new Airplane is created, and if that Airplane refers to an airplane type that doesn't already exist, then a new AirplaneType is created. So starting with an empty database, I can run my utility and get all my tables populated.

So there it is: a simple utility that uses ActiveRecord. This Rails stuff is fun. I really wish I could get somebody to pay me to do stuff like this, instead of struggling with coin acceptors.


Comments:
Kris, thanks for the excellent example. It helped me a great deal in migrating some legacy data.

Mike Stolove
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?