Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not nullable. Adding these columns to each and every table is a lot of work for developers.
When creating the new migration, we would add all the required columns for every migration we create as shown below
class CreateCustomer < ActiveRecord::Migration
def change
execute 'CREATE SEQUENCE SEQ_CUSTOMER'
create_table :customer, primary_key: 'customer_id', sequence_name: 'seq_customer', id: false do |t|
t.integer :customer_id, limit: 8
t.string :name, null: false
t.string :email, limit: 200
t.datetime :date_joined, null: false
t.string :active_flag, default: 'Y', limit: 1
t.string :created_by, null: false
t.datetime :created_date, null:false
t.string :modified_by, null:true
t.datetime :modified_date, null:true
end
execute 'ALTER TABLE CUSTOMER MODIFY DATE_JOINED DEFAULT SYSDATE'
end
end
Adding the four columns for every table is repetitive and could be easily forgotten, creating follow-on migrations and data loss. Adding a ActiveRecord method like audit_columns that automatically adds the default columns when a new table is being created saves the developers from remembering about the four columns and also enables them to have a standard definition of these columns.
module ActiveRecordTableDefinitionExtension
extend ActiveSupport::Concern
included do
def audit_columns(options={})
column(:created_by, :string, null: false)
column(:created_date, :datetime, null:false)
column(:modified_by, :string)
column(:modified_date, :datetime )
end
end
end
Once the ActiveSupport method is defined, we can add the new customer table and reference the audit_columns method, which will automatically add the four columns when the migration is run
class CreateCustomer < ActiveRecord::Migration
def change
execute 'CREATE SEQUENCE SEQ_CUSTOMER'
create_table :customer, primary_key: 'customer_id', sequence_name: 'seq_customer', id: false do |t|
t.integer :customer_id, limit: 8
t.string :name, null: false
t.string :email, limit: 200
t.datetime :date_joined, null: false
t.string :active_flag, default: 'Y', limit: 1
t.audit_columns
end
execute 'ALTER TABLE CUSTOMER MODIFY DATE_JOINED DEFAULT SYSDATE'
end
end
We still have to run the DEFAULT SYSDATE command as a raw sql as we want the default to be a function and not a literal date. Similar techniques can be applied when other types of columns are required to be present on a large number of tables.
