data_transit data_transit is a ruby gem/app used to migrate between different relational databases, supporting customized migration procedure.

#1 Introduction data_transit relies on activerecord to generate database Models on the fly. Tt is executed within a database transaction, and should any error occur during data transit, it will cause the transaction to rollback. So don’t worry about introducing dirty data into your target database.

#2 Install data_transit can be installed using gem

gem install data_transit

or

download data_transit.gem
gem install --local /where/you/put/data_transit.gem

In your command line, input data_transit, You can proceed to “how to use” if you can see message prompt as below. data_transit usage: data_transit command args. 4 commands are listed below…

#3 How to use

#3.1 Config DataBase Connection

data_transit setup_db_conn /your/yaml/database/config/file

Your db config file should be compatible with the activerecord adapter you are using and configured properly. As the name suggests, source designates which database you plan to copy data from, while target means the copy destination. Note the key ‘source’ and ‘target’ should be kept unchanged! For example, here is sample file for oracle dbms.

database.yml source:#don’t change this line

adapter: oracle_enhanced
database: dbserver
username: xxx
password: secret

target:#don’t change this line

adapter: oracle_enhanced
database: orcl
username: copy1
password: cipher

#3.2 Create Database Schema(optional) If you can have your target database schema created, move to 3.3 “copy data”, otherwise use your database specific tools to generate an identical schema in your target database. Or if you don’t have a handy tool to generate the schema, for instance when you need to migrate between different database systems, you can use data_transit to dump a schema description file based on your source database schema, and then use this file to create your target schema.

Note this gem is built on activerecord, so it should work well for the database schema compatible with rails conventions. Example, a single-column primary key

rather than a compound primary key (primary key with more than one columns), primary key of integer type instead of other types like guid, timestamp etc.

In data_transit, I coded against the situation where non-integer is used, therefore resulting a minor problem that the batch-query feature provided by activerecord can not be used because of its dependency on integer primary key. In this special case, careless selection of copy range might overburden network, database server because all data in the specified range are transmitted from the source database and then inserted into the target database.

#3.2.1 Dump Source Database Schema

data_transit dump_schema [schema_file] [rule_file]

schema_file

will be used to contain dumped schema, [rule_file] describes how

your want data_transit to copy your data.

Note if your source schema is somewhat a legacy, you might need some manual work to adjust the generated schema_file to better meet your needs.

For example, in my test, the source schema uses obj_id as id, and uses guid as primary key type, so I need to impede activerecord auto-generating “id” column by removing primary_key => “obj_id” and adding :id=>false, and then appending primary key constraint in the end of each table definition. See below.

here is an example dumped schema file

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

create_table "table_one", primary_key => "obj_id", :force => true do |t|
  #other fields
end
#other tables

end

and I manually changed the schema definition to

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

create_table "table_one", :id => false, :force => true do |t|
  t.string "obj_id",           :limit => 42  
  #other fields
end
execute "alter table table_one add primary key(obj_id)"

end

#3.2.2 Create Target Database Schema

data_transit create_table [schema_file]

If everything goes well, you will see a bunch of ddl execution history.

#3.3 Copy Data

data_transit copy_data [rule_file]

rule_file

contains your copy logic. For security reasons, I changed table names

and it looks as follows.

start_date = “2015-01-01 00:00:00” end_date = “2015-02-01 00:00:00”

migrate do

choose_table "APP.TABLE1","APP.TABLE2","APP.TABLE3","APP.TABLE4","APP.TABLE5","APP.TABLE6"
batch_by "ACQUISITION_TIME BETWEEN TO_DATE('#{start_date}','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('#{end_date}', 'yyyy-mm-dd hh24:mi:ss')"
register_primary_key 'OBJ_ID'

end

migrate do

choose_table "APP.TABLE7","APP.TABLE8","APP.TABLE9","APP.TABLE10","APP.TABLE11","APP.TABLE12"
batch_by "ACKTIME BETWEEN TO_DATE('#{start_date}','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('#{end_date}', 'yyyy-mm-dd hh24:mi:ss')"
register_primary_key 'OBJ_ID'

end

migrate do

choose_table "APP.TABLE13","APP.TABLE14","APP.TABLE15","APP.TABLE16","APP.TABLE17","APP.TABLE18"
batch_by "1>0" #query all data because these tables don't have a reasonable range
register_primary_key 'OBJ_ID'
pre_work do |targetCls| targetCls.delete_all("1>0")  end #delete all in target
#post_work do |targetCls|  end

end

Each migrate block contains a data_transit task.

“choose_table” describes which tables are included in this task. These tables share some nature in common, and can be processed with the same rule.

“batch_by” is the query condition

“register_primary_key” describes the primary key of the tables.

“pre_work” is a block executed before each table is processed.

“post_work” is a block executed after each table is processed.