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 -
country. Create script would look like (example from Sakila
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
city_id in table
country_id in table
integer type but column
country_id in table
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.