ActiveRecord SchemaDumper and MySQL problem

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):

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:

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.