ActiveRecord Migration: how to write and the results

Recently, I started learning Ruby on Rails which is getting more and more popular than a Java based web application framework. Ideas and cultures are quite different from those of Java, so I have struggled to understand them. What I figured out first was that Ruby on Rails programming is tightly coupled to database tables. Creating a table in a database is the first step of every programming. To cope with a database, Rails provides us a tool, ActiveRecord, to connect to the database, and to create tables in the database. ActiveRecord enables us to migrate from a table definition written by ruby to the database schema. This is the memo how a migration can be defined and what tables in databases I got as a result of the migration.

When I wrote a database table definition for a migration, I referred to the web pages listed below.

• Classes in Rails Framework Documentation
  http://api.rubyonrails.org/
  Class ActiveRecord::ConnectionAdapters::TableDefinition
  Class ActiveRecord::Migration
  Class ActiveRecord::Schema
  ... and other ActiveRecord documentations might be helpful
  
• ActiveRecordMigration in Ruby on Rails
  http://wiki.rubyonrails.org/rails/pages/ActiveRecordMigration

For example, from this table definition in SQL, I defined following ruby file.

CREATE TABLE items (
  id smallint(5) unsigned NOT NULL auto_increment,
  done tinyint(1) unsigned NOT NULL default '0',
  priority tinyint(1) unsigned NOT NULL default '3',
  description varchar(40) NOT NULL default '',
  due_date date default NULL,
  category_id smallint(5) unsigned NOT NULL default '0',
  node_id smallint(5) unsigned default NULL,
  private tinyint(3) unsigned NOT NULL default '0',
  created_on timestamp(14) NOT NULL,
  updated_on timestamp(14) NOT NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='List of items to be done';

class CreateItems < ActiveRecord::Migration
  def self.up
    create_table :items do |t|
      t.column :done, :integer, :null => false, :default => 0
      t.column :priority, :integer, :null => false, :default => 3
      t.column :description, :string, :limit => 40, :null => false, :default =>''
      t.column :due_date, :date, :null => true, :default => nil
      t.column :category_id, :integer, :null => false, :default => 0
      t.column :note_id, :integer, :null => true, :default => nil
      t.column :private, :integer, :null => false, :default => 0
      t.column :created_on, :timestamp, :null => false
      t.column :updated_on, :timestamp, :null => false
    end
  end

  def self.down
    drop_table :items
  end
end

According to the API documentation of ActiveRecord::ConnectionAdapters::TableDefinition, data type must be one of primary_key, string, text, integer, float, decimal, datetime, timestamp, time, date, binary, and boolean. Therefore, I mapped both smallint and tinyint types to a integer type. I could write the size of integer precisely by adding :limit, but I didn't do that. There's no unsigned integer type, and I have to care not to have negative values.

What I didn't mapped to the ruby file at all was “TYPE=MyISAM COMMENT='List of items to be done';” in the last line of SQL. I could have written like this:

create_table :items, :options => 'engine=MyISAM' do |t|

However, this MySQL specific parameter doesn't have any special effect because my DBMS is Apache Derby that comes with NetBeans.

Here's another mapping example. This shows how I can define UNIQUE KEY in a ruby file.

CREATE TABLE 'categories' (
  id smallint(5) unsigned NOT NULL auto_increment,
  category varchar(20) NOT NULL default ''
  created_on timestamp(14) NOT NULL,
  updated_on timestamp(14) NOT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'category_key' ('category')
) TYPE=MyISAM COMMENT='List of categories'

class CreateCategories < ActiveRecord::Migration
  def self.up
    create_table :categories do |t|
      t.column :category, :string, :limit => 20, :default => '', :null => false
      t.column :created_on, :timestamp, :null => false
      t.column :updated_on, :timestamp, :null => false
    end
    add_index :categories, :category, :unique => true, :name => 'category_key'
  end

  def self.down
    drop_table :categories
  end
end

Executing migration is the next step after defining the database table in ruby. Before doing it, I edited config/databse.yml and config/environment.rb to set up a connection to the database as follows:

databse.yml(exerpt)
development:
adapter: jdbc
driver: org.apache.derby.jdbc.ClientDriver
url: jdbc:derby://localhost:1527/todos
username: foo
password: bar
encoding: utf8

evironment.rb(excerpt)
RAILS_GEM_VERSION = '1.2.3' unless defined? RAILS_GEM_VERSION

require File.join(File.dirname(__FILE__), 'boot')

if RUBY_PLATFORM =~ /java/
  require 'rubygems'
  RAILS_CONNECTION_ADAPTERS = %w(jdbc)
end

Rails::Initializer.run do |config|
end

To make it happen, I chose “Migrate Database” from menus provided by NetBeans IDE(Go to the Projects window, right click on the project name, choose“Migrate Database” from a popup menu then select “To Current Version”). This is equivalent to run “rake db:migrate” command. NetBeans showed some messages on Output window, and create the table in the database. Though I can see what table was created on NetBeans, I dared check it interactively by running ij command of Apache Derby because outputs in interactive mode was more familiar than properties in the
popup window.

$ java -jar lib/derbyrun.jar ij
ij> connect 'jdbc:derby://localhost:1527/todos' user 'foo' password 'bar';
ij> describe items;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID                  |INTEGER  |0   |10  |10    |GENERATED&|NULL      |NO   
DONE                |INTEGER  |0   |10  |10    |0         |NULL      |NO   
PRIORITY            |INTEGER  |0   |10  |10    |3         |NULL      |NO   
DESCRIPTION         |VARCHAR  |NULL|NULL|40    |''        |80        |NO   
DUE_DATE            |DATE     |0   |10  |10    |NULL      |NULL      |YES  
CATEGORY_ID         |INTEGER  |0   |10  |10    |0         |NULL      |NO   
NOTE_ID             |INTEGER  |0   |10  |10    |NULL      |NULL      |YES  
PRIVATE             |INTEGER  |0   |10  |10    |0         |NULL      |NO   
CREATED_ON          |TIMESTAMP|6   |10  |26    |NULL      |NULL      |NO   
UPDATED_ON          |TIMESTAMP|6   |10  |26    |NULL      |NULL      |NO   

ij> describe categories;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID                  |INTEGER  |0   |10  |10    |GENERATED&|NULL      |NO   
CATEGORY            |VARCHAR  |NULL|NULL|20    |''        |40        |NO   
CREATED_ON          |TIMESTAMP|6   |10  |26    |NULL      |NULL      |NO   
UPDATED_ON          |TIMESTAMP|6   |10  |26    |NULL      |NULL      |NO   

ij> show indexes from categories;
TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
CATEGORIES          |CATEGORY            |0     |3   |A   |NULL    |NULL 
CATEGORIES          |ID                  |0     |3   |A   |NULL    |NULL    

For comparison, I tried the migration in the same way by using well-used MySQL. On MySQL, the table was created like this:

mysql> describe items;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| done        | int(11)     | NO   |     | 0       |                |
| priority    | int(11)     | NO   |     | 3       |                |
| description | varchar(40) | NO   |     | NULL    |                |
| due_date    | date        | YES  |     | NULL    |                |
| category_id | int(11)     | NO   |     | 0       |                |
| note_id     | int(11)     | YES  |     | NULL    |                |
| private     | int(11)     | NO   |     | 0       |                |
| created_on  | datetime    | NO   |     | NULL    |                |
| updated_on  | datetime    | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

We can see small differences in length of an integer type, a mapping of a timestamp type and default value of a varchar type.

Every time I try to run Rails samples, I'm often puzzled how I can write schema definitions in ruby and how the migration works from definitions to real tables on database. This memo would help me not to be confused next time.