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