class Mysqlaudit::Audit

Public Class Methods

new(host, user, password, schema) click to toggle source
# File lib/mysqlaudit/audit.rb, line 15
def initialize(host, user, password, schema)
  $host     = host
  $user     = user
  $password = password
  $schema   = schema

  connect
end

Public Instance Methods

connect() click to toggle source
# File lib/mysqlaudit/audit.rb, line 24
def connect()
  begin
    @mysql = Mysql2::Client.new(:host     => $host,
                                :username => $user,
                                :password => $password,
                                :database => $schema)
  rescue
    puts "Can't connect to MySQL Server."
    exit 1
  end
end
create_table() click to toggle source
# File lib/mysqlaudit/audit.rb, line 36
    def create_table()
      if !has_table
        sql = <<SQL
CREATE TABLE IF NOT EXISTS audits (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  type enum('I', 'U', 'D') NOT NULL,
  `table` char(64) NOT NULL,
  `column` char(64) NOT NULL,
  `primary_key` int(10) unsigned,
  `old` TEXT DEFAULT NULL,
  `new` TEXT DEFAULT NULL,
  `trigger_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY id_UNIQUE (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL

        @mysql.query(sql)
        puts "Created table audits in #{$schema} database."
      end
    end
create_trigger(table, actionName) click to toggle source
# File lib/mysqlaudit/audit.rb, line 86
def create_trigger(table, actionName)
  if !has_trigger(table, actionName)
    columns = get_columns(table)
    key     = get_primary_key(table)

    if key
      sql = "CREATE TRIGGER audit_#{table}_#{actionName.downcase} AFTER #{actionName.upcase} ON #{table}\nFOR EACH ROW BEGIN\n"

      columns.each do | column |
        case actionName.downcase
          when :insert
            sql << "INSERT INTO audits (type, `table`, `column`, primary_key, old, new) VALUES ('#{actionName[0,1].upcase}', '#{table}', '#{column}', NEW.#{key}, NULL, NEW.#{column});\n"
          when :update
            sql << "IF OLD.#{column} <> NEW.#{column} THEN\n"
            sql << "  INSERT INTO audits (type, `table`, `column`, primary_key, old, new) VALUES ('#{actionName[0,1].upcase}', '#{table}', '#{column}', NEW.#{key}, OLD.#{column}, NEW.#{column});\n"
            sql << "END IF;\n"
          when :delete
            sql << "INSERT INTO audits (type, `table`, `column`, primary_key, old, new) VALUES ('#{actionName[0,1].upcase}', '#{table}', '#{column}', OLD.#{key}, OLD.#{column}, NULL);\n"
        end
      end

      sql << "END;"

      @mysql.query(sql)
      puts "Created trigger in #{table} table after #{actionName}."
    else
      puts "Impossible to create trigger in #{table}, not have primary key."
    end
  else
    puts "Impossible to create trigger in #{table}, has a trigger."
  end
end
delete(table) click to toggle source
# File lib/mysqlaudit/audit.rb, line 144
def delete(table)
  columns = get_columns(table)
  key     = get_primary_key(table)

  sql = "SELECT CONCAT('/* ', trigger_at, ' */ INSERT INTO #{table} (#{key}, #{columns.join(', ')}) VALUES (', primary_key, ', ', "
  columns.each do | column |
    sql << " QUOTE(MAX(IF(`column` = '#{column}', `old`, NULL))), ', ', "
  end
  sql  = sql.chomp(" ', ', ")
  sql << "');'"
  sql << ") AS `row` FROM audits WHERE `type`  = 'D' AND `table` = '#{table}' GROUP BY primary_key ORDER BY trigger_at, primary_key, `column` ASC;"
end
drop_table() click to toggle source
# File lib/mysqlaudit/audit.rb, line 63
def drop_table()
  return unless has_table()
  @mysql.query("DROP TABLE IF EXISTS audits;")
  puts "Delete table audits in #{$schema} database."
end
drop_trigger(table, actionName) click to toggle source
# File lib/mysqlaudit/audit.rb, line 119
def drop_trigger(table, actionName)
  if has_trigger(table, actionName)
    sql = "DROP TRIGGER IF EXISTS audit_#{table}_#{actionName};"
    @mysql.query(sql)
    puts "Deleted trigger in #{table} table after #{actionName}."
  end
end
get_columns(table) click to toggle source
# File lib/mysqlaudit/audit.rb, line 80
def get_columns(table)
  sql        = "SELECT column_name AS name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='#{$schema}' AND TABLE_NAME='#{table}' AND column_key <> 'PRI';"
  sql_result = @mysql.query(sql)
  sql_result.map { |table| table['name']}
end
get_primary_key(table) click to toggle source
# File lib/mysqlaudit/audit.rb, line 75
def get_primary_key(table)
  sql = "SELECT column_name AS name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='#{$schema}' AND TABLE_NAME='#{table}' AND column_key = 'PRI';"
  @mysql.query(sql).first['name']
end
get_tables() click to toggle source
# File lib/mysqlaudit/audit.rb, line 69
def get_tables()
  sql        = "SELECT table_name AS name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '#{$schema}' AND table_name <> 'audits';"
  sql_result = @mysql.query(sql)
  sql_result.map { |table| table['name']}
end
has_table() click to toggle source
# File lib/mysqlaudit/audit.rb, line 58
def has_table()
  sql       = "SELECT true AS has FROM information_schema.tables WHERE table_schema = '#{$schema}' AND table_name = 'audits';"
  @mysql.query(sql).count == 1
end
has_trigger(table, actionName) click to toggle source
# File lib/mysqlaudit/audit.rb, line 127
def has_trigger(table, actionName)
  sql = "SELECT true AS has FROM information_schema.triggers WHERE trigger_schema = '#{$schema}' AND trigger_name = 'audit_#{table}_#{actionName}';"
  @mysql.query(sql).count == 1
end
insert(table) click to toggle source
# File lib/mysqlaudit/audit.rb, line 132
def insert(table)
  sql  = "SELECT CONCAT('/* ', trigger_at, ' */ DELETE FROM #{table} WHERE id = ', MAX(primary_key),';') AS `row` "
  sql << "FROM audits "
  sql << "WHERE `type`  = 'I' AND `table` = '#{table}' GROUP BY primary_key "
  sql << "ORDER BY trigger_at, primary_key, `column` ASC;"
end
rollback(table, statement) click to toggle source
# File lib/mysqlaudit/audit.rb, line 157
def rollback(table, statement)
  sqls = []

  case statement
    when :all
      sqls << insert(table)
      sqls << update(table)
      sqls << delete(table)
    when :insert
      sqls << insert(table)
    when :update
      sqls << update(table)
    when :delete
      sqls << delete(table)
  end

  sqls.each do | sql |
    sql_result = @mysql.query(sql)
    sql_result.each(:as => :array) do |row|
      puts row
    end
  end
end
update(table) click to toggle source
# File lib/mysqlaudit/audit.rb, line 139
def update(table)
  sql  = "SELECT CONCAT('/* ', trigger_at, ' */ UPDATE users SET ', `column`, ' = ', QUOTE(old), ' WHERE id = ', primary_key, ';') AS `row`"
  sql << "FROM audits WHERE `type` = 'U' AND `table` = '#{table}' ORDER BY trigger_at, primary_key, `column` ASC;"
end