DB2 and Ruby on Rails, Part 1: Getting started with DB2 and Ruby on Rails

Document options Document options requiring JavaScript are not displayed Discuss Sample code Rate this page Help us improve this content

Level: Intermediate

John Chun, DB2 Advanced Support Specialist, IBM
Alex Pitigoi, Advisory Software Engineer, IBM
Naomi Ngan, Senior Software Engineer, Autonomy
Christine Law, DB2 Advanced Support Specialist, IBM

16 May 2007

The emergence of the Ruby language, coupled with the powerful Rails framework, provides a myriad of opportunities for Web solutions development. With the introduction of the IBM_DB adapter and driver, Rails applications can now interact seamlessly with IBM data servers. This is the first article in the DB2® with Ruby on Rails series. As such, it introduces the Starter Toolkit for DB2 on Rails, covers various methods for installing the IBM_DB driver, and Rails migration with DB2.

Introduction

Released in 2004, Ruby on Rails has quickly become one of the most popular frameworks used in Web application development. Also known as Rails or RoR, this open source project uses the Model-View-Controller (MVC) architecture, the Ruby object-oriented scripting language, and straightforward principles such as "convention over configuration" and "don't repeat yourself." Developers can therefore benefit from building applications faster and easier, with less redundant code and configuration files, while retaining the flexibility to create custom extensions to fulfill their application needs. With support for database persistence, Web applications using the framework can be readily developed using a database server and the built-in WEBrick Web server.

What does IBM_DB bring to the Rails world?

With the availability of the IBM_DB Rails adapter and Ruby driver released on the RubyForge community portal, the Ruby on Rails framework is officially tested and supported on all DB2 data servers. It may come as a surprise to some of you, but a no-charge version of the DB2 9 database called DB2 Express-C is also available to the community. The free to develop, deploy, and distribute DB2 Express-C data server does not have a size, time, or user limit, and is also included with the Starter Toolkit for DB2 on Rails. This package helps developers set up and build Web applications quickly and painlessly in the RoR environment using DB2.

While there are a variety of data stores that could be configured in the Rails framework, the DB2 data server brings some unique and outstanding advantages. Since the introduction of pureXML™ in DB2 9, a chief benefit is the fact that the IBM_DB adapter and driver supports the native XML data type when used with IBM data servers.

Getting started with DB2 on Rails

There are currently two alternatives for setting up the Rails development environment using DB2. If you are new to the DB2 environment, the Starter Toolkit for DB2 on Rails was designed to be the most convenient way to start DB2 on Rails application development. The Starter Toolkit version 2.1 is available for download from alphaWorks and installs a slightly older version of the IBM_DB adapter. This version does not support DB2 on i5 and zOS platforms, but an update is in the works and will soon be available for download.

However, if you are attempting to develop Rails applications within an existing DB2 environment, an equally painless "manual" option has become available since the IBM_DB adapter and driver gem and plugin were released. Please check the RubyForge rubyibm project for the latest version (Production/Stable release 0.6.0 available since April 30).

Manual step-by-step install using DB2 Express-C 9

Let's start by downloading and configuring the DB2 Express-C data server and the Rails runtime environment. The latest version of the IBM_DB adapter and driver requires DB2 9, FixPack 2 or DB2 8, FixPack 15. To download and configure the DB2 Express-C data server and the Rails runtime environment:

• Go to the following website and download DB2 Express-C 9 FixPack 2 .
• Download One-click Ruby Installer (contains both Ruby and Rubygems).

• Install Rails gem and its dependencies:

  gem install rails --include-dependencies

  
  

Note 1: DB2 Client 9 FP2 is required because of a CLI fix (related to numerical values quoting) that associates with some important changes in both the IBM_DB driver and adapter. Note 2: Most Rails applications on DB2 9 require at the minimum APPLHEAPSZ of 1024. db2 update db cfg for <database_name> using APPLHEAPSZ 1024 Updating to db cfg requires DB2 to be recycled (db2stop followed by db2start). This is only an issue for manual installation of the IBM_DB adapter and driver (method 2), as this value is the default in the latest Starter Toolkit for DB2 on Rails.

Installing the IBM_DB adapter and driver as Ruby gem as compared to the Rails plugin

To help understand the distinction between a Ruby gem install versus a Rails plugin install, here is a brief description of the runtime environment.

RubyGems is the standard packaging and installation framework for libraries and applications in the Ruby runtime environment. A single file for each bundle, called gem, which complies to the package format is then distributed and stored in a central repository, allowing simultaneous deployment of multiple versions of the same library or application. Similar to Linux distributions packaging management and bundles (.rpm, .deb), these gems can also be queried, installed, un-installed and manipulated through the gem end-user utility. The gem utility can seamlessly query the remote RubyForge central repository, as well as look-up and install any of the many readily available utilities that make the lives of Rails developers easy. Once IBM_DB gem is installed, the functionality is immediately accessible from any script (therefore application) in the Ruby runtime environment, through:

• require 'rubygems'
• gem 'ibm_db' (require_gem has been deprecated and replaced by gem in Rubygems 0.9)

As an ActiveRecord adapter, IBM_DB gem is required to be registered by the abstract adapter in its list of RAILS_CONNECTION_ADAPTERS (active_record.rb) before it can be utilized in the Rails framework. Once registered, the IBM_DB gem and its dependencies (ibm_db Ruby driver and the IBM Driver for ODBC and CLI) are loaded. This enables any application in the Ruby environment, including Rails, to interact with the IBM data servers. As described in the IBM_DB gem README file (see the Resources section of this article), per a few simple steps, the Ruby runtime is enabled for access to IBM data servers.

Note 3: In a Windows environment while running "gem install ibm_db", you are presented with two choices for each release (mswin32 or ruby). By choosing "mswin32", the pre-built binary file for Windows is installed. Example: D:\>gem install ibm_db Bulk updating Gem source index for: http://gems.rubyforge.org Select which gem to install for your platform (i386-mswin32) 1. ibm_db 0.6.0 (mswin32) 2. ibm_db 0.6.0 (ruby) 3. ibm_db 0.4.6 (ruby) 4. ibm_db 0.4.6 (mswin32) 5. Skip this gem 6. Cancel installation

Installing IBM_DB adapter and driver as a Ruby Gem

1. Issue the gem command to install IBM_DB adapter and driver:

   D:\>gem install ibm_db

   
   

2. Register 'ibm_db' in the list of connection adapters in the Rails framework.

   Manually add ibm_db into gems\1.8\gems\activerecord-1.15.3\lib\active_record.rb:

   RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite ... ibm_db )

   
   

Rails plugins represent the framework's own extension mechanism. This provides a means by which Rails can extend its capabilities within the scope of a specific and individual Rails application. As a result, this provides another method for accessing IBM data servers when IBM_DB gem is not deployed in the Ruby runtime environment. While Rails plugins do not offer the version management specific to Ruby gems, they do provide a useful initialization mechanism which allows the IBM_DB plugin to self insert into the Rails registry during its initialization. Therefore, after installing IBM_DB as plugin in your application, no manual step is required for the Rails framework to load it. As described in the IBM_DB plugin README file and rubyibm project documentation at RubyForge, the Rails application is enabled for IBM data server access simply by registering the plugin source and running the Rails application install script. While RubyForge is yet to enable HTTP access to Subversion (SVN) repositories (projects support is currently only the SVN protocol), a Rails plugin install on Windows requires an SVN client install. On most Linux® and UNIX® distributions, the SVN client is available by default, making the IBM_DB plugin installation rather seamless.

Back to top

Client environment

The IBM_DB adapter (ibm_db_adapter.rb) has a direct dependency on the ibm_db driver, which utilizes the IBM Driver for Open Database Connectivity (ODBC) and Call Level Interface (CLI) to connect to IBM data servers. The IBM CLI is a callable SQL interface to IBM data servers, which are ODBC compliant.

This has several ramifications for the IBM_DB adapter and driver.

1. Installation of the IBM Driver for ODBC and CLI must meet the IBM_DB requirement.

   You can get the IBM Driver for ODBC and CLI with full DB2 database install or obtained these components separately from "IBM DB2 Driver for ODBC and CLI overview".

2. You can modify driver behavior outside of any Ruby application using CLI keywords.

   You can alter certain transactional behavior outside of the Rails application using some CLI keywords. For example, you can use them to set current schema or alter transactional elements such as turning off autocommit behavior. Details regarding CLI keywords can be found in the following DB2 Info Center documents:

   Version 8

   Version 9

3. Any diagnostic gathering will require the CLI driver tracing facilities.

   As all requests through IBM_DB driver are implemented using the API provided by the IBM Driver for ODBC and CLI, the CLI trace is an important tracing mechanism for identifying problems in an application using the IBM_DB adapter and driver.

   A CLI trace captures all of the API calls made by an application to the IBM driver for ODBC and CLI (including all input parameters), and it captures all of the values returned from the driver to the application. It is an interface trace designed to capture how an application interacts with the IBM driver for ODBC and CLI, and offers information about the inner workings of the driver.

   Version 8

   Version 9

Back to top

Database schema evolution

In an everchanging environment, applications need to adapt dynamically to address new requirements and challenges. When application developers change their applications, for instance when adding a new object or class, there is a need to modify the underlying persistence to make sure that the database is in sync with the application. The traditional way to change database schemas is to generate new SQL scripts. However, keeping both the application and database in a versioned sequence through SQL scripts is a challenge. Furthermore, database developers rarely build SQL scripts to reverse the changes made in the database schema associated with application changes. In most development environments, the changes are reversed by dropping all database objects and recreating them using the previous version of the SQL (Data Definition Language [DDL]) scripts.

For many Web developers, the main Rails revelation is its built-in support for schema evolution through migrations. While database developers are certainly more inclined to use SQL through DDL or Data Manipulation Language (DML), most application developers are more than happy to stick to their tools: the Ruby language and its libraries. And that is precisely what Rails offers through migrations: a simple and efficient infrastructure which takes advantage of the ActiveRecord abstraction to create and modify database objects such as tables and columns in a versioned sequence. Rails migration does the job of the majority of the database administrative tasks associated with schema evolution. The Rails framework simplifies development, but the associated tools (rake tasks) are also very effective in driving the database changes and keeping the application code synchronized with its table structures.

DB2 schema evolution using Rails migration

Ruby on Rails migration solves some of the problems described above involving the database and data structure changes. Now, let's look at how Rails migration can facilitate the database schema evolution.

Let's examine a Rails migration example using the IBM_DB adapter while attempting some database object changes related to a Rails application evolution. But first, you need to make sure the DB2 on Rails development environment is installed and configured as described previously.

Our example will attempt to build a Team Room, a hosted application which enables registered members to share various documents in text or binary format including images, executable files, presentations and any other media. Also included are XML documents to be shared, given how easy they could be stored natively in DB2 9 through the pureXML data type. This example will also find some good ways to take advantage of their built-in hierarchical structure.

1. First, let's create a Rails project called "teamroom".

   
   
   Listing 1. Creating the Team Room Rails project
   

   D:\rails>rails teamroom create create app/controllers create app/helpers create app/models create app/views/layouts create config/environments create components create db <etc ......> create log/server.log create log/production.log create log/development.log create log/test.log

   
   

   Rails framework generates the project's directory structure automatically. From here on we shall assume we are in the D:\rails\teamroom directory, so all the paths mentioned onwards are relative paths within the Team Room project directory.

2. If you already have an existing DB2 database, skip this step and proceed directly to Step 3 to start configuring your database connection.

   Rails project and database You do not need to create a new database for each Rails project. There is no such one-to-one mapping requirement.

   Anticipating the fact that you may, at some point, want to store XML natively in DB2, let's create the XMLDB database using UTF-8 codeset. A UTF-8 codeset is required in order to allow XML columns to be defined in DB2 tables.

   From the DB2 Command Line Processor, run the following command:

   
   
   Listing 2. Creating the XMLDB database
   

   db2 create db xmldb using codeset utf-8 territory us

   
   

3. Now edit the D:\rails\teamroom\config\database.yml file so that we can make a connection to the DB2 9 XMLDB database.

   
   
   Listing 3. Edit database.yml file
   

   # IBM DB2 Database configuration file # # Install the IBM DB2 driver and get assistance from: # http://www.alphaworks.ibm.com/tech/db2onrails development: adapter: ibm_db database: xmldb username: user password: secret schema: teamroom application: TeamRoom account: devuser workstation: devbox # == remote TCP/IP connection (required when no local database catalog entry available) # host: bigserver // fully qualified hostname or IP address # port: 50000 // data server TCP/IP port number

   
   

Each of the connection attributes for DB2 is explained in the table below:

Note 4: Always specify your schema explicitly It is highly recommended that you explicitly specify a schema to group your database objects logically. Otherwise, multiple Rails projects can unintentionally write to the same <AuthID>.schema_info table if the same authorization ID is used to connect to the same database, shared by a number of Rails projects. This will produce unpredictable results. The Schema_info table is used to track migration versions. Further explanation is provided in the Team Room example below. Note 5: The optional connection attributes host and port associated with remote TCP/IP connections are only required when DB2 catalog information is not available and no data source has been registered in the db2cli.ini configuration file for DB2 CLI. This type of setup is possible while using the IBM Driver for ODBC and CLI instead of the complete DB2 Client installed locally.

Team Room example

Now, you are ready to start performing the actual migration steps.

Step 1: Creating the Documents table

First, you need to start from a storage area in order for all the documents and media files to be shared among the users of this hosted application. So, let's create the DOCUMENTS table to store all the media files team members would like to share.

Let's define the following columns to describe the content of our shared files:

a) To do this by way of migration, generate a migration for table DOCUMENTS by executing ruby script\generate migration create_docs_store. Rails generates the migration and creates db/migrate/001_create_docs_store.rb. You can type ruby script/generate migration create_docs_store on UNIX. (Note the forward slash "/" works on both Windows and UNIX.)

Listing 4. Running ruby script/generate migration create_docs_store

                
D:\rails\teamroom>ruby script/generate migration create_docs_store
      create  db/migrate
      create  db/migrate/001_create_docs_store.rb

Notice that 001_ create_docs_store.rb. is created in db/migrate, and this is numbered '001'. From this step onwards sequentially numbered files will be generated in db/migrate for each migration step. This shows that Rails is managing the sequence of migrations for you.

b) Edit create_docs_store.rb to look like the following:

                
class CreateDocsStore < ActiveRecord::Migration
  def self.up
    create_table :documents do |t|
      t.column :name,         :string,  :null  => false
      t.column :size,         :integer, :null  => false
      t.column :content_type, :string,  :null  => false
      t.column :data,         :binary,  :limit => 2.megabytes
    end
  end

  def self.down
    drop_table :documents
  end
end

Note 6: Table names are in plural form by default Rails labels the table name in plural form by default. Running the migration above will create a table in DB2 called DOCUMENTS rather than DOCUMENT. Note 7: rake db:migrate runs all migrations that are yet to be applied. In this case, there is only one migration to apply.

In Rails applications, schema changes occur through migrations. Each change to the database schema is versioned and defined in Ruby scripts which contain vendor independent syntax. Furthermore, undoing the changes is as easy as making the changes while each migration script features two class methods: the up method and the down method. All the code required to make one logical database schema change is placed within the self.up method. Any code that is required to undo the changes is placed within the self.down method.

c) Run the migration by way of the rake db:migrate command:

                
D:\rails\teamroom>rake db:migrate
(in D:/rails/teamroom)
== CreateDocsStore: migrating =================================================
-- create_table(:documents)
   -> 0.2010s
== CreateDocsStore: migrated (0.2010s) ========================================

d) Now go to a DB2 command prompt and issue db2 list tables for schema teamroom to confirm that the DOCUMENTS table was created:

                
D:\>db2 list tables for schema teamroom

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
DOCUMENTS                       TEAMROOM        T     2007-04-21-21.00.18.131001
SCHEMA_INFO                     TEAMROOM        T     2007-04-21-21.00.17.740001

  2 record(s) selected.  
			

Notice that the DOCUMENTS table was created as expected, with data types as follows:

                
D:\>db2 describe table teamroom.documents

Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
ID                             SYSIBM    INTEGER                   4     0 No
NAME                           SYSIBM    VARCHAR                 255     0 No
SIZE                           SYSIBM    INTEGER                   4     0 No
DATA                           SYSIBM    BLOB                2097152     0 Yes
CONTENT_TYPE                   SYSIBM    VARCHAR                 255     0 No

  5 record(s) selected.

Note 8: Automatic creation of the primary key The default primary key column name in Rails is id. You did not have to explicitly define the primary key ID column as by default Rails does this for us.

Also notice that Rails created a second table called SCHEMA_INFO during this migration step. SCHEMA_INFO was created through the rake db:migrate command. Querying the SCHEMA_INFO table shows the following output:

                
D:\>db2 select * from schema_info

VERSION
-----------
          1
          
  1 record(s) selected.

The value '1' in the version column indicates that you are at version 1 of the migration (Recall you ran db/migrate/001_ create_docs_store.rb)

So rake db:migrate essentially does all of the following:

• Creates the SCHEMA_INFO table if it does not exists and inserts the value '0' to the version column
• Runs all applicable migrations. That is, it runs the up methods of all migrations that have a number greater than the current migration in ascending order. The first migration starts with at version value '1' .
• Updates the SCHEMA_INFO table with the last version of the migration run. In the above example, the version is updated to '1' because it was the first migration for this Rails project.

Step 2: Additional attributes associated with documents

After the DOCUMENTS table was created, say you decided that you would like to store additional information about each document, such as the operating system platform, upload time and the last change time. The resulting DOCUMENTS table should contain the following columns (new columns to be added are highlighted in bold)

Note 9: The two columns names chosen, created_at and updated_at carry special semantics for Rails. As with other "magic column names" (id, type, position, lock_version, parent_id>), Rails will use this names as a convention. In this case, it will automatically update with the timestamp of a row's creation or last update. It only requires the underlying database column to be capable of receiving a date, datetime, or string. The complete Rails convention is to use the _on suffix for date columns and the _at suffix for columns that include a time.

You generate a second migration to add these attributes to the DOCUMENTS table:

a) Run ruby script/generate migration add_docs_attributes.

This generates the db/migrate/002_add_docs_attributes.rb file.

                
D:\rails\teamroom>ruby script/generate migration add_docs_attributes
      exists  db/migrate
      create  db/migrate/002_add_docs_attributes.rb

Note 10: The idea of migration is that if you ever need to change anything in the persistence database, you generate a new migration to perform the changes. You never edit the original migration in order to make the change.

b) Edit 002_add_docs_attributes.rb to look like the following:

                
class AddDocsAttributes < ActiveRecord::Migration
  def self.up
    add_column :documents, :created_at, :timestamp
    add_column :documents, :updated_at, :timestamp
    add_column :documents, :platform,   :string,    :limit => 10
  end

  def self.down
    remove_column :documents, :created_at
    remove_column :documents, :updated_at
    remove_column :documents, :platform
  end
end

Note 11: You use add_column to add attributes in the self.up method. To revert the changes, use remove_column.

c) Again, issue rake db:migrate to perform the second migration.

                
D:\rails\teamroom>rake db:migrate
(in D:/rails/teamroom)
== AddDocsAttributes: migrating ===============================================
-- add_column(:documents, :created_at, :timestamp)
   -> 0.0500s
-- add_column(:documents, :updated_at, :timestamp)
   -> 0.0100s
-- add_column(:documents, :platform, :string, {:limit=>10})
   -> 0.0000s
== AddDocsAttributes: migrated (0.0600s) ======================================

Note 12: You can once again use db2 describe table teamroom.documents to verify that the new columns are added to the DOCUMENTS table. Note 13: <model_name>_id is the default name of a foreign key reference to a table which is named <model_name> , in the plural form. Associations such as foreign keys are achieved in Rails through model relationships. One of the important conventions that the framework uses to manage this model relationship is the default name of a table's primary key column (its id ), while the default name of a foreign key reference to the table is <model_name>_id (the table name has the plural form of <model_name> ). It is important to understand that Rails migrations do not define foreign key constraints. Relationships between tables are set while the developer populates the columns with key values from the tables in a certain relationship. It is recommended that you also establish and enforce such constraints in the database through migration, but this is not a Rails framework requirement.

Step 3: Managing users and their access to resources

Many people in the organization or community would be interested in these documents, so you need a way to manage such users and their access. So, let's add a USERS table to serve this purpose. You also need to add a foreign key 'user_id' to the DOCUMENTS table so you know the user who uploaded a particular document.

These are the steps necessary to perform these tasks:

a) Run ruby script/generate migration create_users_table which will create the db/migrate/003_create_users_table.rb file

b) Edit the db/migrate/003_create_users_table.rb file as follows:

                
class CreateUsersTable < ActiveRecord::Migration
  def self.up
    create_table :users do |t|      
      t.column :usertype,  :string, :limit => 5, :null => false
      t.column :firstname, :string, :limit => 30
      t.column :lastname,  :string, :limit => 30
      t.column :extension, :string, :limit => 4
    end
    add_column :documents, :user_id, :integer
  end

  def self.down
    drop_table :users
    remove_column :documents, :user_id
  end
end

c) Run rake db:migrate to create the USERS table:

                
D:\rails\teamroom>rake db:migrate
(in D:/rails/teamroom)
== CreateUsersTable: migrating ================================================
-- create_table(:users)
-> 0.1400s
-- add_column(:documents, :user_id, :integer)
-> 0.0000s
== CreateUsersTable: migrated (0.1400s) =======================================

d) You can now issue ruby script/generate scaffold document to generate a scaffold for the DOCUMENTS table. Scaffolding quickly put an Active Record class online by providing standardized actions for listing, showing, creating, updating, and destroying objects of the class. As shown in Listing 15 below, a controller and a number of views are created in /app/controllers and /app/views during a scaffold generation.

                
D:\rails\teamroom>ruby script/generate scaffold document
      exists  app/controllers/
      exists  app/helpers/
      create  app/views/documents
      create  app/views/layouts/
      create  test/functional/
  dependency  model
      create    app/models/
      exists    test/unit/
      exists    test/fixtures/
      create    app/models/document.rb
      create    test/unit/document_test.rb
      create    test/fixtures/documents.yml
      create  app/views/documents/_form.rhtml
      create  app/views/documents/list.rhtml
      create  app/views/documents/show.rhtml
      create  app/views/documents/new.rhtml
      create  app/views/documents/edit.rhtml
      create  app/controllers/documents_controller.rb
      create  test/functional/documents_controller_test.rb
      create  app/helpers/documents_helper.rb
      create  app/views/layouts/documents.rhtml
      create  public/stylesheets/scaffold.css

At this point, you are ready to take a look at what a scaffold does for us. At the command prompt, type ruby script/server to start the built-in WEBrick Web server for Rails:

                D:\rails\teamroom>ruby script/server
=> Booting WEBrick...
=> Rails application started on http://0.0.0.0:3000
=> Ctrl-C to shutdown server; call with --help for options
[2007-04-26 16:54:57] INFO  WEBrick 1.3.1
[2007-04-26 16:54:57] INFO  ruby 1.8.5 (2006-12-25) [i386-mswin32]
[2007-04-26 16:54:57] INFO  WEBrick::HTTPServer#start: pid=444 port=3000

Note the port number. The default is 3000 but it might be different if port 3000 is already in used on your system. Open a Web browser and go to http://localhost:3000/, you will notice a Welcome aboard message. Browse to http://localhost:3000/documents and you will see the following:

e) Edit the generated /app/models/document.rb file to look like the following:

                
class Document < ActiveRecord::Base
  belongs_to :user
end

The belongs_to expresses the one-to-one foreign key relationship between the DOCUMENTS and USERS table. This association states that each document can only be associated with (belongs to) a single user. The document model belongs_to :user if the DOCUMENTS table has a user_id foreign key column.

Note 14: The model for the table that contains the foreign key always has the belongs_to declaration.

We added additional code in /app/models/document.rb so that we can actually upload documents and store the files in the DB2 9 database. Refer to download section for code implementation. So after adding a number of documents to our Team Room, you will see something similar to this in your browser:

f) Similarly, issue ruby script/generate scaffold user to generate a scaffold for the Users table. Then edit /app/models/user.rb to look like the following:

                
class User < ActiveRecord::Base
   has_many :document
end

Note 15: The has_many association indicates that a user can upload multiple documents. Note 16: Refer to http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html for more info on ActiveRecord associations

Now that you understand the first few migration steps in detail, let's briefly go over the rest of the migrations to create the remaining tables and associations necessary.

Step 4: Managing the ever-growing collection of shared documents

It would certainly help to have a way to categorize the wealth of documents that will be contributed by the community, so a taxonomy based on content subject could be added. The SUBJECTS table will be created to help categories our documents. A subject contains many documents, but each document can only belong to one subject. To illustrate this one-to-many relationship between SUBJECTS and DOCUMENTS, we need to add a Subject ID foreign key to DOCUMENTS.

These are the steps necessary to perform these tasks:

a) Run ruby script/generate migration create_subjects_table which will create the db/migrate/004_create_subjects_table.rb file.

b) Edit db/migrate/004_create_subjects_table.rb as follows:

                
class CreateSubjectsTable < ActiveRecord::Migration
  def self.up
    create_table :subjects do |t|
      t.column :name,         :string,     :limit => 20
      t.column :size,         :integer
      t.column :description,  :text
      t.column :tag,          :string,     :limit => 10 
    end
    add_column    :documents, :subject_id, :integer
  end

  def self.down
    drop_table    :subjects
    remove_column :documents, :subject_id
  end
end

c) Run rake db:migrate to create the SUBJECTS table and add the subject_id foreign key.

d) Run ruby script/generate scaffold subject to generate a scaffold for SUBJECTS table.

e) Add the has_many :document association to the newly generated /app/models/subject.rb file.

                
class Subject < ActiveRecord::Base
  has_many :document
end

f) Add a second association belongs_to :subject to the /app/models/document.rb file generated previously in Step 3(d).

                
class Document < ActiveRecord::Base
  belongs_to :user
  belongs_to :subject

  <... code to assist with document uploading ...>
  <...                                        ...>
  
  end
end

Step 5: Managing user notification needs

What if there was a way for any user to be notified when a new document is uploaded to a specific category? That could easily be achieved if you could collect such requests for notification in a SUBSCRIPTIONS table. Some relationships between users, subjects and subscriptions would also need to be considered.

Let's first describe the user case scenario: user Anthony is interested in receiving notifications whenever a new document is uploaded on certain subjects: programming, Linux, and fishing. He will browse to a page which displays all subjects created so far, and then check the boxes associated with the subjects listed above. Following that, every time a new document is uploaded on any of the three subjects, the application will generate a notification message to be emailed to owners of any subscription that includes them. All this would translate in the following set of relationships:

• Each user may have at most a subscription, but subscriptions would not be mandatory (for example, a one-to-none, one... relationship).
• a foreign key user_id needs to be added to the SUBSCRIPTIONS table
• a one-to-one relationship needs to be included between the user and subscription models
• Each user subscription can include a few preferred subjects (for example, a one-to-many relationship). The user will be provided with some means to choose from all of the subjects available.
• a foreign key subscription_id needs to be added to the SUBJECTS table
• a one-to-many relationship needs to be included between the subscription and subject models

You'll achieve these database design goals using migration steps very similar to those you used before:

a) Running ruby script/generate migration create_subscriptions_table will create the db/migrate/005_create_subscriptions_table.rb file.

b) Edit db/migrate/005_create_subscriptions_table.rb:

                
class CreateSubscriptionsTable < ActiveRecord::Migration
  def self.up
    create_table :subscriptions do |t|
      t.column :name,         :string,  :limit => 20
      t.column :description,  :text
      t.column :user_id,      :integer
    end
    add_column    :subjects,  :subscription_id, :integer
  end

  def self.down
    drop_table    :subscriptions
    remove_column :subjects,  :subscription_id
  end
end

c) Run rake db:migrate to create the SUBSCRIPTIONS table and add foreign key columns to SUBJECTS.

d) Run ruby script/generate scaffold subscription to generate a scaffold for SUBSCRIPTIONS.

e) Add the has_many :subject association to the /app/models/subscription.rb file generated at Step 4 d.

f) Add a corresponding association belongs_to :subscription to the newly generated /app/models/subject.rb file

g) Add the has_one :subscription association to the /app/models/user.rb file generated at Step 3 f.

h) Add the belongs_to :user association to the newly generated /app/models/subscription.rb file.

Note 17: A careful reader will certainly spot an inconsistency that makes the current subscription design not entirely real or usable: a subject can only belong to a subscription, therefore once Anthony checks it in his subscription page, it will no longer be available to Bob. Obviously, that is a rather many-to-many relationship, but will make it simple (for instance, no intermediate join tables for now). It will certainly be fixed in Part 2 of this series.

Step 6: Managing user contacts

One way to notify our users about Team Room updates could be through email. For that, you will need to add the email contact for each user, therefore add an email column to the USERS table through another migration:

a) Run ruby script/generate migration addEmailToUser. This generates the db/migrate/006_add_email_to_user.rb file.

b) Edit 006_add_email_to_user.rb.

                
class AddEmailToUser < ActiveRecord::Migration
  def self.up
    add_column    :users, :email, :string, :limit => 30
  end

  def self.down
    remove_column :users, :email
  end
end   

c) Run rake db:migrate to add email column to USERS table.

Here is a diagram that illustrates what you've done in Steps 1 through 6.

Step 7: Storing XML documents in the Team Room

Increasingly often in today's enterprise environment, documents need to be stored in the XML format, and such remarkable structured documents are not always treated much different from some amorphous binary objects. We dare to try something different in this application, while storing XML documents in a data type different from the BLOB data type already defined in the DOCUMENTS table.

We can afford that while using a DB2 9 data server, and taking advantage of the native XML data persistence, which enables well-formed XML documents to be stored in their hierarchical form. XML columns are declared in DB2 9 as XML data types, and this is precisely what we will use in the new migration.

a) Run ruby script/generate migration add_xml_doc_column.

This generates db/migrate/007_add_xml_doc_column.rb.

b) Edit 007_add_xml_doc_column.rb:

                
class AddXmlDocColumn < ActiveRecord::Migration
  def self.up
    add_column :documents, :xmldata, :xml
  end
  
  # Currently, a column that is part of a table containing an XML column
  # cannot be dropped.  To remove the column, the table must be dropped 
  # and recreated without the previous XML column.
  def self.down
    drop_table :documents
    create_table :documents do |t|
      t.column :name,         :string,      :null  => false
      t.column :size,         :integer,     :null  => false
      t.column :data,         :binary,      :limit => 2.megabytes
      t.column :content_type, :string,      :null  => false
      t.column :created_at,   :timestamp
      t.column :updated_at,   :timestamp
      t.column :platform,     :string,      :limit => 10
      t.column :user_id,      :integer
      t.column :subject_id,   :integer
    end
  end
end

c) Run rake db:migrate to add the new column to the DOCUMENTS table.

While the add_column looks already familiar, you might be surprised to see the entire table needs to be dropped and then recreated, instead of using the remove_column. This should come as no surprise, given the important changes that the XML brings to the table as a native data type. Although this can be perceived as a relative inconvenience until XML column removal is supported, in the next part of this series, we will present an alternative database schema design, and also the major advantages the native XML data type support brings in exchange. But first, let's gain experience with this previously considered "amorphous" blob of data.

d) Analyze marketing data collected and formatted in an XML document.

Let's try to determine all Toronto neighbourhoods represented in the marketing data collected, by extracting customer's postal code information from one of the documents posted in the Team Room. Use the documents scaffold while browsing to http://localhost:3000/ and upload the teamroom/test/fixtures/CAN-Central.xml document provided with the sample code. Then, add the following action in the documents_controller.rb:

                
def zips
  @id = params[:id]
  @xmldata = Document.find_by_sql "select xmlquery(\
                                      '<zipcodes>\
                                         {for $i in $t/marketinfo/sales/customer/address\
                                            where  $i/city = \"Toronto\"\
                                            return <zip>{$i/zip/text()}</zip>} \
                                      </zipcodes>'\
                                      passing c.xmldata as \"t\")\
                                   from documents c where id = #{@id}"
  p @xmldata[0]
  redirect_to :action => 'list'
end

For the current stage of this application, the result of this complex SQL and embedded XQuery is only printed in your server console while following the zips link for the CAN-Central.xml document previously uploaded. The result will contain the list of postal code areas associated with Toronto customers existing in the CAN-Central.xml document. The XQuery embedded into the SQL statement might look complicated at first sight, but in most cases an XPath query expression could simplify:

                
Document.find_by_sql "select xmlquery('<zipcodes>\
                      {$t/marketinfo/sales/customer/address/zip[../city = \"Toronto\"]}\
                                       </zipcodes>' passing c.xmldata as \"t\")\
                             from documents c where id = #{@id}"

In the next part of this series, we will find better ways to simplify Rails application interaction through SQL embedded XQuery and XPath expression. For the time being, rest assured that you will no longer need to retrieve the entire content of large XML documents to merely inspect a small fraction of it. It can be done inside the DB2 native XML data store, while taking advantage of the DB2 engine optimizer.

Figure 4 shows what the Team Room database objects should look like at the end of Step 7:

Rolling back the changes through migration

Finally, we are done. But what if we want to undo all the changes now? This is very simple, just run rake db:migrate VERSION=number where number indicates the version you wish to rollback to. For example, if you wanted to destroy the tables we created for the Rails Team Room project in the XMLDB database and undo all the changes you have made so far, you would issue rake db:migrate VERSION=0. Rails reverses each migration step in order, rolling back the changes for the largest migration first, eventually downgrading the database to version 0 as specified.

                
D:\rails\teamroom>rake db:migrate VERSION=0
(in D:/rails/teamroom)
== AddXmlDocColumn: reverting =================================================
-- drop_table(:documents)
   -> 0.0150s
-- create_table(:documents)
   -> 0.1880s
== AddXmlDocColumn: reverted (0.2030s) ========================================

== AddEmailToUser: reverting ==================================================
-- remove_column(:users, :email)
   -> 0.1250s
== AddEmailToUser: reverted (0.1250s) =========================================

== CreateSubscriptionsTable: reverting ========================================
-- drop_table(:subscriptions)
   -> 0.0000s
-- remove_column(:subjects, :subscription_id)
   -> 0.1560s
== CreateSubscriptionsTable: reverted (0.1560s) ===============================

== CreateSubjectsTable: reverting =============================================
-- drop_table(:subjects)
   -> 0.0000s
-- remove_column(:documents, :subject_id)
   -> 0.1570s
== CreateSubjectsTable: reverted (0.1570s) ====================================

== CreateUsersTable: reverting ================================================
-- drop_table(:users)
   -> 0.0000s
-- remove_column(:documents, :user_id)
   -> 0.1400s
== CreateUsersTable: reverted (0.1400s) =======================================

== AddDocsAttributes: reverting ===============================================
-- remove_column(:documents, :created_at)
   -> 0.1250s
-- remove_column(:documents, :updated_at)
   -> 0.1870s
-- remove_column(:documents, :platform)
   -> 0.1260s
== AddDocsAttributes: reverted (0.4380s) ======================================

== CreateDocsStore: reverting =================================================
-- drop_table(:documents)
   -> 0.0000s
== CreateDocsStore: reverted (0.0000s) ========================================

Conclusion

We presented how to get started with Rails application development using IBM_DB adapter/driver and DB2 data servers. The Starter Toolkit for DB2 on Rails provides the easiest method for those who are new to DB2 and Ruby on Rails, but for existing DB2 developers, the IBM_DB adapter and driver gem or plugin installation will provide a similarly easy entry.

We have also discussed Rails migration in detail. Managing database schema changes can be tedious. The Rails migration feature helps application developers manage this schema evolution, making it easy to synchronize changes between the application code and the database objects. By utilizing Rails migration and defining your database schema changes in Ruby files, versioning of changes takes place in a logical manner. Furthermore, because Rails' ActiveRecord is not vendor specific, a developer only needs to create a single Ruby migration file to handle the same changes over multiple database platforms while using various ActiveRecord adapters.

Stay tuned for Part 2 of the Ruby on Rails Series, where we will showcase how to take DB2 on Rails and XML support to the next level! XML manipulation through Ruby on Rails will be discussed in further detail.

Back to top

Download

	Information about download methods

Resources

• "Ruby on Rails and J2EE:Is there room for both?" (developerWorks, July 2005): Compare two Web application frameworks: J2EE vs. Ruby on Rails.
  
  
• "Crossing borders: Exploring Active Record" (developerWorks, March 2006): Explore Active Record, the persistence engine behind Ruby on Rails.
  
  
• "Crossing borders: What's the secret Sauce in Ruby on Rails?" (developerWorks, May 2006): Explore what makes Rails productive and look at Rails-inspired ideas that should get more attention within the Java community.
  
  
• "An introduction to Ruby on Rails for DB2 developers" (developerWorks, June 2006): Read a step-by-step introductory article to Ruby on Rails.
  
  
• "Crossing borders: Rails migrations" (developerWorks, August 2006): Find a good overview of Rails migration and a demonstration of Rails migration with MySQL.
  
  
• "Make Ruby on Rails easy with RadRails and Eclipse" (developerWorks, September 2006): Read this step-by-step introductory article to Ruby on Rails.
  
  
• ActiveRecord association documentation.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  
• Download DB2 Express-C.
  
  
• Rails Adapter/Driver for IBM Databases (rubyforge.org)
  
  
• Download the IBM_DB adapter/driver gem and plugin.
  
  
• Download the Starter Toolkit for DB2 on Rails.
  
  
• Download RadRails.

• Participate in the discussion forum.
  
  
• Visit the alphaWorks Forum for Starter Toolkit for DB2 on Rails.
  
  
• Visit the RubyForge rubyibm Forum.
  
  

About the authors

		John Chun is a specialist of the DB2 Advanced Support team working in the area of application development and tooling. He has worked in the IBM DBT Toronto lab for 7 years resolving DB2 application issues with various languages including Java, C, C++, Perl, REXX, C# and others. John has worked on a number of projects involving the DB2 CLI and OLEDB driver, as well as the .NET data provider. John is a DB2 Certified Solutions Expert and Certified Websphere Administrator.

		Alex Pitigoi is an advisory software engineer at the IBM Toronto Lab. He has worked on various software development projects in the Information Management since 1998, focusing on Web technologies and database administration. Most recently, he drove the development of the SQLModel project, now incorporated into the Eclipse Data Tools Project, as well as the overall architecture for the database administrative Web Tools across multiple IBM data servers. Alex also worked on the DB2 Satellite Administration Center, the IBM Express Runtime, and lead the development of the first set of Web Tools delivered for DB2. His current focus is IBM's data servers enablement for new open source technologies (Ruby, Python, PHP).

		Naomi Ngan completed an Honours degree in Computer Science and Statistics at the University of Toronto in Canada in 2000. Upon graduating, she joined IBM, where she was responsible for resolving IBM DB2 RDBMS product defects and problems in an application development environment. After almost 4 years at IBM, she went on to the Ernest Gallo Clinic and Research Center at UCSF to develop Bioinformatics software. This includes designing and developing database objects, JSPs, Java standalone applications and stored procedures within an XML environment on Linux and Windows platforms. Currently, she is a senior software engineer at Autonomy Corporation developing J2EE enterprise software. She has in-depth knowledge of application development and tooling for DB2 and holds numerous IBM and Sun developer certifications in DB2, XML, WebSphere, and Java/J2EE.

		Christine Law is senior DB2 specialist and an IBM Certified Expert at the IBM Toronto Lab, where she is responsible for resolving DB2 applications problems and defects. She has extensive application development experience on Linux, UNIX and Windows platforms with different programming languages and scripting languages, specializing in JDBC, SQLJ, stored procedures and embedded SQL. Her recent interests includes Open Source technologies such as AJAX and Ruby.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top