History Database Extension for Sequel

Objective

For all application service, we will create a history database to archive the data record from application operation database. The difference of database schema between operation database and history database is mainly in following two points:

  1. Remove index with option operation: :only;
  2. Remove all foreign key constrain;
  3. Remove column with option operation: :only

Before this, we need to maintain two git repositories of the application, one for original application code repository, another mainly for storing the migration script for history database. This approach is facing many challenge when data schema change in term of migration script synchronisation between these two git repository, and this approach need to repeat in all applications.

How to eliminate this challenge? So that we can remain only one migration script in original application code repository, that can run on operation database and history database without any changes.

Solution

The solution is simple. We create a sequel extension to ignore constrain during index and foreign key creation, so than we can apply this extension on whatever the migration script is applied. After read code of sequel and here is the methods in database class need to be overwrite:

=====Index Method=====
index(columns, opts = OPTS)
add_index(columns, opts = OPTS)
drop_index(columns, options=OPTS)
=====Foreign Key Method=====
foreign_key(name, table=nil, opts = OPTS)
add_foreign_key(name, table, opts = OPTS)
drop_composite_foreign_key(columns, opts)
add_composite_foreign_key(columns, table, opts)
=====Support operation options=====
add_column(name, type, opts = OPTS)
rename_column(name, new_name, opts = OPTS)
drop_column(name, opts=OPTS)

Detail pls checkout my private repositoryhistroy_extension

Usage

Foreign Key Removal

Current version of history_extension is 1.1.1, which is deployed to the gem server. To use this gem, first need to add this gem in Gemfile:

gem 'history_extension', '1.0.1'

Next, we need to determine environment valuable to control enable this extension, such as HISTORY and update sequel.rake file:

 namespace :db do
    require 'sequel'
------------Add from Here------------------------------
    if ENV['HISTORY']
      require 'sequel/extensions/history_extension'
      Sequel::Database.register_extension(:history_extension, Sequel::HistoryExtension)
      Sequel::Database.extension :history_extension
    end
-----------To Here---------------------------------
    namespace :migrate do
      Sequel.extension :migration

Operation Only Column Removal

In order to remove the column only existing in operation database, we need to add an option to indication it is operation only column and will not migration to history database, here is example in migration script

create_table :properties do
  primary_key :id
  String :name
  String :description
  Datetime :purge_at, operation: :only
  index :name
end

create_table :games do
  primary_key :id
  String :name
  String :description
  column :purge_at, DateTime, operation: :only
  index :name, operation: :only
end

alter_table(:rounds) do
  add_column :purge_at, DateTime, operation: :only
end

alter_table(:rounds) do
  rename_column :purge_at, :purged_at, operation: :only
end

Currently, we support these column operation to indicate operation option:

  • Create Table

    1. Missing method to define column(such as: DateTime :purge_at, operation: :only)
    2. column method to define column(such as: column :purge_at, operation: :only)

  • Alter Table

    1. add_column to define column(such as: add_column :purge_at, operation: :only)
    2. rename_column to rename column(such as: rename_column :purge_at, operation: :only)

But we still have some operations of column are not support operation option: 1.set_column_default 2.set_column_type 3.set_column_allow_null 4.set_column_allow_not_null

Command

Above to check HISTORY environment, if defined, then require extension, register, and extend it in Database Class. Here is command to execute for history database migration(using axle as example)

HISTORY=true DATABASE_URL='mysql2://laxino:[email protected]:3306/axle__history' rake db:migrate:up

Todo

  • [ ] set_column_default
  • [ ] set_column_type
  • [ ] set_column_allow_null
  • [ ] set_column_allow_not_null

Thank you!

Pls let me know if you have any problems.