Rails Models Reverse Engineering

Rmre is utility gem for creating Ruby on Rails, at the moment only ActiveRecord, models for legacy databases. Besides creating all models it sets proper table name and primary key if tables and columns naming doesn’t follow Rails convention. It also tries to read all foreign keys data from a database if DBE is MySql, PostgreSQL, Oracle or MS SQL and sets models relationships on a basic level through belongs_to and has_many declarations.

Installation

Rmre can be installed with

gem install rmre

How to use

Rmre is very simple to use:

rmre -a mysql2 -d my_database -u my_username -p my_password -o /path/where/models/will/be/created

That’s all! Of course there is standard help which you can print at any time:

rmre --help

or

rmre -h

I believe that command line options are self explanatory especially if you are familliar with Ruby on Rails database handling. Apart from Ruby on Rails related options there are several that should be explained.

MS SQL options

Options:

-m or --mode
-n or --dsn

are user for setting ODBC specific arguments. First one must be used with MS SQL and must be set to ODBC and the second one is data source name.

General options

Otput directory can be set with:

-o /path/to/target/directory
--out /path/to/target/directory

otherwise Rmre will create models in the directory where it is started.

Rmre can also filter tables for which it will create models. Filtering is very basic and it mathes whether name of the table starts with passed patterns:

-i ec_,vi_
--include ic_,vi_

with create models only for tables names with prefixes ec_ or vi_.

Test databases

If you want to try Rmre and you do not have sample database you can use Sakila at dev.mysql.com/doc/sakila/en/sakila.html#sakila-installation for MySQL and Pagila at pgfoundry.org/projects/dbsamples for PostgreSQL.

Copying databases

Rmre gem has built-in support for copying databases (structure and data). This feature is currently experimental.

During copy, Rmre will create primary keys on target tables. Since Rmre uses ActiveRecord, composite primary keys are not supported.

Copying structure between different RDBMS can be tricky due to different data types. Some adapters do not convert all types to Rails value. Example is oracle_enhanced adapter which for ‘LONG’ column type sets column’s type to nil but keeps sql_type as ‘LONG’. Rmre handles these cases through Rmre::DbUtils module. Currently it properly converts Oracle’s raw and LONG types to MySQL’s binary and text. Support for more conversions will be added (if I find or get info about needed conversions). However if you write your own script and do not use db_copy runner you can set additional conversion rules by adding values to Rmre::DbUtils::COLUMN_CONVERSIONS hash.

Keys in this hash are target sdapter names and values are hashes with source column type as key and target column type as value:

COLUMN_CONVERSIONS = {
    "Mysql2" => {
      :raw => :binary,
      "LONG" => :text
    }
  }

In order to copy one database to another RDBMS you must start db_copy with -f option and with path to YAML configuration file. Full sample of configuration file is:

:source:
  adapter: sqlserver
  mode: dblib
  dataserver:
  host: localhost
  port: 1433
  database: source_db
  username: source_username
  password: source_pass
  timeout: 5000
:target:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: target_db
  pool: 5
  username: target_username
  password: target_pass
  host: localhost
:verbose: true
:force: true
:skip_existing: false
:skip:
  - do_not_copy_table_1
  - do_not_copy_table_2

Source and target options are standard Rails configurations for source and target databases. Parameter :verbose is optional and can be omitted. If set to try will db_copy will pring out progress during copy. This parameter can be set also by passing -v options to db_copy

db_copy -f /path/to/config/file.yml -v

Value from file will override the one given on the command line.

Next optional parameter is :force. If it is set to true Migrator class will force table creation Similar to verbose parameter this value can be set by passing -o option to db_copy and value from configuration file will override the value given on the command line.

Parameter :skip_existing signals db_copy to skip tables that already exist in target database.

If you do not want to copy some tables add them to the array :skip in configuration file.

There is a big probability that db_copy will not be able to copy database which is not Rails compliant. Reasons for this are numerous: unsupported column types, composite primary keys, etc. If you face such a problem create an issue and I will try to implement support for various special cases. However if used on Rails compliant databases db_copy should be able to peform full copy between any of supported RDBMS.

TODO

  • Improve filtering

  • Write more tests

  • Foreign key support for other DBEs (firebird, SQLite, Sybase,…)

  • Probably much more which I cannot remember right now