Pragmatic Development Notes

After finishing first version of Rmre and issuing fix gem dependency in version 0.0.2, I got an idea for additional functionality. Why not use Rmre for dumping complete schema with all foreign keys data? What would be possible scenario for using this, one might ask? We have possibility to create ActiveRecord models in order to move to Ruby on Rails where main premise is to keep logic out of database and maintain it in application. Therefore we do not need foreign keys since we already have constraints defined in models.

But what if you cannot move to Ruby on Rails and you only have to change DBE, i.e. instead of MS SQL you must use Oracle? In that case you still have to work with legacy database from PHP or Hibernate in Java and “only” thing you have to do is to make create script for all tables but for another DBE. When database has hundreds of tables with lots of relations this can turn into nightmare, especially if you have to maintain both versions.

Rmre should simplify this. First you use Rmre to dump schema to some file and later you can use ActiveRecord’s capabilities to load it on different DBE. Since loading schema in ActiveRecord is DBE agnostic it should correctly create tables, indices and foreign keys on any database engine. That’s theory and, as usual, practice is a little bit different. On a very first step I’ve faced problem in MySQL database.

Let’s examine database with just a two tables – city and country. Create script would look like (example from Sakila database):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE TABLE city (
  city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT 
    CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (city_id),
  KEY idx_fk_country_id (country_id),
  CONSTRAINT `fk_city_country` 
    FOREIGN KEY (country_id)
    REFERENCES country (country_id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE country (
  country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country VARCHAR(50) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As can be seen from above script table city has foreign key on table country. Now let’s see what is result of a dump:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

ActiveRecord::Schema.define(:version => 0) do

  create_table "actor", :primary_key => "actor_id", :force => true do |t|
  create_table "city", :primary_key => "city_id", :force => true do |t|
    t.string    "city",        :limit => 50, :null => false
    t.integer   "country_id",  :limit => 2,  :null => false
    t.timestamp "last_update",               :null => false
  end

  add_index "city", ["country_id"], :name => "idx_fk_country_id"

  create_table "country", :primary_key => "country_id", :force => true do |t|
    t.string    "country",     :limit => 50, :null => false
    t.timestamp "last_update",               :null => false
  end
      
    execute "ALTER TABLE city
        ADD CONSTRAINT fk_city_country
        FOREIGN KEY (country_id)
        REFERENCES country(country_id)"
end

At the first glance this looks good but unfortunately doesn’t work. Problem is that loading this schema through ActiveRecord will create columns city_id in table city and country_id in table country as integer type but column country_id in table city is created as smallint. Defining constraint on columns which are not of same type is not allowed so last statement for altering table fails. At the moment I have no idea how to fix this and any suggestion is very welcome. I still have to check what happens on other DBEs: PostgreSQL, Oracle and MS SQL.

Very often I have to work on databases which do not follow ActiveRecord convention and making ActiveRecord models, if number of tables is large, is very slow and boring task. In order to speed up and simplify it I’ve created Rmre gem. Gem is quite simple yet you might find it useful if you want to create fixtures, migrations or simply port application to Ruby on Rails.

So how it works? For each table in the database, gem creates model. Name of the model is created using Rails classify method. Moreover, if table’s primary key is not column named “id” gem sets primary key by adding set_primary_key "primaryKeyColumnName" line to the model. In addition for MySQL, PostgreSQL, Oracle or MS SQL foreign keys are analyzed and for each constraint gem generates belongs_to or has_many lines. Here is model created for table store in Sakila MySQL test database:

1
2
3
4
5
6
7
8
9

class Store < ActiveRecord::Base
  set_primary_key :store_id
  set_table_name 'store'
  has_many :customers, :class_name => 'Customer'
  has_many :inventories, :class_name => 'Inventory'
  has_many :staffs, :class_name => 'Staff'
  belongs_to :address, :class_name => 'Addres', :foreign_key => :address_id
  belongs_to :staff, :class_name => 'Staff', :foreign_key => :manager_staff_id
end

Setting up Rails and Ruby to use MS SQL server was always painful task. Fortunately things have changed – a lot! With new Rails SQL Server 2000, 2005 and 2008 Adapter and Christian Werner’s ruby-odbc gem you can do it in a few minutes.

If you want to use these gems on Windows grab Ruby installation from RubyInstaller site and be sure to install DevKit prior to installing ruby-odbc.

Versions of ruby-odbc before 0.9999 do not work on mingw based (RubyInstaller) Ruby. Luckily author was very fast and made new version very quickly after I sent him a patch. Thanks Christian!

Both gems work well on Ruby 1.8.6 and 1.9.1 Ruby versions on Windows with old ActiveRecords, but I hope rails adapter will be ported to ActiveRecords 3 soon.