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
CREATE 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.
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
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&amp;|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&amp;|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&amp;|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.
