Wednesday, August 29, 2007

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.
For example, from this table definition in SQL, I defined following ruby file.
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,
) 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

def self.down
drop_table :items
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,
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
add_index :categories, :category, :unique => true, :name => 'category_key'

def self.down
drop_table :categories

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:
adapter: jdbc
driver: org.apache.derby.jdbc.ClientDriver
url: jdbc:derby://localhost:1527/todos
username: foo
password: bar
encoding: utf8

RAILS_GEM_VERSION = '1.2.3' unless defined? RAILS_GEM_VERSION

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

if RUBY_PLATFORM =~ /java/
require 'rubygems'
end do |config|

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;
DONE |INTEGER |0 |10 |10 |0 |NULL |NO
PRIVATE |INTEGER |0 |10 |10 |0 |NULL |NO

ij> describe categories;

ij> show indexes from categories;

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.

No comments: