DBUtil.rb

class DBUtil < ActiveRecord::Migration
  
  def self.create_history(table, history_table, columns, to_use)
   
  #create the history table 
  create_table(history_table)    
  to_use += ["id", "updated_at", "updated_by"] if to_use != nil
  
  columns.each do |c|
    next if to_use != nil && !to_use.include?(c.name)
    if c.name == "id"
      add_column(history_table, table + "_id", :integer, {:null => false})
    else
      add_column(history_table, c.name, c.type, {:default => c.default, :limit => c.limit, :null => true, :precision => c.precision, :scale => c.scale })
    end
  end
  
  #create the insert history trigger
  sql = "CREATE TRIGGER insert_#{history_table} AFTER INSERT ON #{table} FOR EACH ROW BEGIN\n"
  sql += "INSERT #{history_table} ("
  columns.each do |c|
     next if to_use != nil && !to_use.include?(c.name)
     sql += c.name == "id" ? table + "_id" : c.name
     sql += ","
   end
   sql = sql.chomp(",")
   sql += ")\n VALUES ("
   columns.each do |c|
       next if to_use != nil && !to_use.include?(c.name)
       sql += "NEW." + c.name + ","
     end
  sql = sql.chomp(",")
  sql += ");"
  sql += "END"
  execute sql
  
  #create the update history trigger
  sql = "CREATE TRIGGER update_#{history_table} AFTER UPDATE ON #{table} FOR EACH ROW BEGIN\n"
  sql += "set @#{table}_id = NEW.id;"
  
  to_use -= ["id", "updated_at", "updated_by"] if to_use != nil
  
  columns.each do |c|
    next if to_use != nil && !to_use.include?(c.name)
    sql += "SET @#{c.name} = IF (NEW.#{c.name} = (SELECT #{c.name} FROM #{history_table} WHERE #{c.name} IS NOT NULL AND #{table}_id = @#{table}_id ORDER BY updated_at DESC LIMIT 1), null, NEW.#{c.name});\n"
  end
  
  sql += "IF "
  columns.each do |c|
    next if to_use != nil && !to_use.include?(c.name)
    sql += "@#{c.name} is not null OR "
  end
  sql = sql.chomp("OR ")
  sql += "THEN INSERT #{history_table} (#{table}_id,"
  
  columns.each do |c|
    next if to_use != nil && !to_use.include?(c.name)
    sql += c.name == "id" ? table + "_id" : c.name
    sql += ","
  end
  sql += "updated_by, updated_at)\n" 
   
  sql += "SELECT "
  sql += "NEW.id,"
  columns.each do |c|
    next if to_use != nil && !to_use.include?(c.name)
    sql += "@#{c.name},"
  end
  sql += "NEW.updated_by, NEW.updated_at"
  sql += "; "
  sql += "END IF;"
  sql += "END;"
  
  execute sql
        
  end
  
end