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 database):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
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
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
country_id in table
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.