OpenLink ODBC Data Adapter for Ruby on Rails

11-December-2006

Status

The adapter accompanying this note is a Generic ODBC Adapter for Ruby on Rails being developed by OpenLink Software[http://www.openlinksw.com].

The aim of this development is to provide a single ODBC-based adapter capable of supporting the most popular DBMSes, in contrast to the current approach in the Rails community of each database requiring its own adapter.

It currently supports Ingres r3, Informix 9.3 or later, Oracle 10g, MySQL 5 and OpenLink’s Virtuoso (Open Source Edition), SQL Server 2000, Sybase ASE 15, DB2 v9 and Progress v8/9/10

Testing to date has been limited to the ROR ‘Expenses’ sample application described at developer.apple.com/tools/rubyonrails.html and the ActiveRecord test modules base_test.rb and migration_test.rb.

Testing has been performed on Linux, Windows and Mac OS X using OpenLink’s own ODBC drivers and the native Virtuoso ODBC client.

Pre-Requisites

The OpenLink ODBC Adapter for Ruby on Rails needs the ActiveRecord package installed.

The Adapter also requires Christian Werner’s Ruby ODBC Bridge (release 0.9991 or later), to bridge to an underlying ODBC driver.

In the accompanying sources, the lib directory structure is equivalent to the lib directory located under ACTIVE_RECORD_ROOT in your main Ruby tree.

On Windows ACTIVE_RECORD_ROOT will be something like:

c:\ruby\lib\ruby\gems\1.8\gems\activerecord-x.y.z

On Unix and Mac OS X ACTIVE_RECORD_ROOT will be something like:

/usr/lib/ruby/gems/1.8/gems/activerecord-x.y.z

/usr/local/lib/ruby/gems/1.8/gems/activerecord-x.y.z

On Mac OS X using Locomotive ACTIVE_RECORD_ROOT will be something like:

/Applications/Locomotive2/bundles/rails112.locobundle/i386/lib/ruby/gems/1.8/gems/activerecord-x.y.z

lib/ contains the files which constitute the new ODBC adapter.

test/ contains the fixture definitions for testing the adapter.

support/odbc_rails.diff contains some patches to ActiveRecord.

These will be submitted to the ActiveRecord source maintainer in due course, if we request that the OpenLink ODBC Adapter for Ruby on Rails be added to the ActiveRecord source distribution to accompany the existing Rails adapters.

Only one patch in this diff file is really necessary - that to

lib/active_record/connection_adapters/abstract/schema_definitions.rb

This fixes what appears to be a bug in Active Record.

The patches to base_test.rb and migration_test.rb are not essential. However, they modify or bypass certain tests to cope with limitations of particular databases. Other developers have previously modified the tests similarly to cope with limitations of other Rails-supported databases. The patched versions of files touched by odbc_rails.diff can be found in support/lib and support/test.

Installation

There are 3 ways to install the ODBC Adapter package: either as a gem (recommended), a plugin, or by running the custom installation script. Pick one of the following, depending on whether you want the adapter to be available system-wide or just within a particular Rails project.

Installation as a Gem

Install the odbc-rails gem by running:

# gem install -r odbc-rails --include-dependencies

Enable loading of the adapter by editing your Rails project’s config/environment.rb script:

* Add a <tt>require</tt> to your config/environment.rb immediately after
  the line

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

  i.e.

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

Installation as a Plugin

Installing the OpenLink ODBC Data Adapter as a plugin makes it available to a particular Rails application only.

Automatic Plugin installation

The adapter can be automatically installed as a plugin by navigating to the root of your Rails application and typing either:

script/plugin install odbc-rails

script/plugin install http://odbc-rails.rubyforge.org/plugins/odbc-rails

On Windows, replace script/plugin by ruby script/plugin. e.g. ruby script/plugin install odbc-rails

Manual Plugin Installation

You can also install the plugin manually by unpacking the sources into the vendor/plugins directory of your Rails application. If you use this method, you must run install.rb immediately afterwards. This need only be done once.

install.rb modifies your application’s config/environment.rb file, adding a line to ensure that the database adapter plugin is initialized before ActiveRecord. i.e.

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

# Added by OpenLink ODBC Adapter (odbc-rails) plugin
Rails::Initializer.run(:load_plugins)

Rails::Initializer.run do |config|

Without this change, Rails attempts to initialize ActiveRecord before it loads any plugins, causing an AdapterNotFound error. install.rb saves a copy of environment.rb before making any changes.

Custom installation script

When using rake install or running the install_odbc.rb script by hand, the script tries to determine the location of the activerecord package within your Ruby installation and allows you to overrule the directory it has found with a directory of your choice. If more than one version of activerecord is found, the default will be the latest version.

On Mac OS X the installation script is also capable of locating Locomotive, if installed, and will ask to install in there.

The install_odbc.rb script also verifies that the pre-requisites are installed.

If all else fails this document also describes the steps to install the ODBC adapter into activerecord manually.

Using rake

The simplest way to install the adapter is by using the following command as root:

# rake install

or if your system supports sudo:

$ sudo rake install

The Rakefile also defines some targets specifically for developers:

$ rake rdoc         # Generate rdoc documentation in ~/doc

$ rake package	# Create distribution package in ~/distrib

$ rake clean	# Remove generated files and directories

Running installation script

The second way of installing the adapter is running the install_odbc.rb script as root.

# ruby install_odbc.rb

or if your system supports sudo:

$ sudo ruby install_odbc.rb

Installing files manually

The third way of installing the adapter is by performing the installation steps yourself.

Copy odbc_adapter.rb to the ACTIVE_RECORD_ROOT/lib/active_record/connection_adapters/ directory
Copy the odbcext_*.rb files to the ACTIVE_RECORD_ROOT/lib/active_record/vendor/ directory
Enable loading of the adapter (see below)

Enabling Loading of the Adapter

This step is only necessary if the adapter was installed either as a gem or by copying files into your Rails core manually. It is not required if the adapter was installed as a plugin, using rake install or using install_odbc.rb.

There are two ways to enable loading of the adapter:

Modify active_record.rb in your Rails ‘core’, adding the ODBC adapter to the list of adapters recognized by ActiveRecord:
- Edit ACTIVE_RECORD_ROOT/lib/active_record.rb, appending ‘odbc’ to the line:
```
RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite firebird sqlserver db2 oracle sybase openbase)
```
  i.e.
```
RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql ... openbase odbc)
```
  or
Modify your Rails application’s configuration to require the adapter:
- Add a require to your config/environment.rb immediately after the line
```
require File.join(File.dirname(__FILE__), 'boot')
```
  i.e.
```
require File.join(File.dirname(__FILE__), 'boot')
require 'odbc_adapter'
```

Configuring the Adapter

Examples of the required connection parameters can be found in

test/connections/native_odbc/connection.rb

If you enable call-tracing by setting :trace => true, specify the logger output file as illustrated in connection.rb, e.g.

ActiveRecord::Base.logger = Logger.new("debug_odbc.log")

License

The OpenLink ODBC Adapter for Ruby on Rails is released under the MIT license as detailed in the file COPYING.

Submitting fixes and enhancements

Patches and new contributions can be submitted as diffs from the current CVS archive by:

$ cvs add newfiles

$ cvs -z3 diff -uN > diffs

Patches and contributions can be send to the OpenLink iODBC source archive mainainter at [email protected] to be included in the next distribution. Please provide accompanying documentation on which bugs are fixed or new features are introduced.