Skip to content

Instantly share code, notes, and snippets.

@DaveSanders
Created June 21, 2010 20:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DaveSanders/447467 to your computer and use it in GitHub Desktop.
Save DaveSanders/447467 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment