Class: Mysqlaudit::Audit

Inherits:
Object
  • Object
show all
Defined in:
lib/mysqlaudit/audit.rb

Instance Method Summary collapse

Constructor Details

#initialize(host, user, password, schema) ⇒ Audit

Returns a new instance of Audit.



15
16
17
18
19
20
21
22
 
# File 'lib/mysqlaudit/audit.rb', line 15

def initialize(host, user, password, schema)
  $host     = host
  $user     = user
  $password = password
  $schema   = schema

  connect
end

Instance Method Details

#connect ⇒ Object 



24
25
26
27
28
29
30
31
32
33
34
 
# 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 ⇒ Object 



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
# 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) ⇒ Object 



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
# 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) ⇒ Object 



144
145
146
147
148
149
150
151
152
153
154
155
 
# 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 ⇒ Object 



63
64
65
66
67
 
# 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) ⇒ Object 



119
120
121
122
123
124
125
 
# 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) ⇒ Object 



80
81
82
83
84
 
# 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) ⇒ Object 



75
76
77
78
 
# 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 ⇒ Object 



69
70
71
72
73
 
# 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 ⇒ Object 



58
59
60
61
 
# 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) ⇒ Object 



127
128
129
130
 
# 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) ⇒ Object 



132
133
134
135
136
137
 
# 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) ⇒ Object 



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
 
# 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) ⇒ Object 



139
140
141
142
 
# 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