Module: SuggestDbIndices

Defined in:
lib/suggest_db_indices/core.rb,
lib/suggest_db_indices/version.rb

Constant Summary collapse

NUM_LINES_TO_READ = 
1000
VERSION = 
"0.0.1"

Class Method Summary collapse

Class Method Details

.connection ⇒ Object 



38
39
40
 
# File 'lib/suggest_db_indices/core.rb', line 38

def connection
  ActiveRecord::Base.connection
end

.default_options ⇒ Object 



85
86
87
88
89
 
# File 'lib/suggest_db_indices/core.rb', line 85

def default_options
  {:num_lines_to_scan => 10000,
    :examine_logs => false,
    :log_dir => ""}
end

.foreign_key?(column_name) ⇒ Boolean

Returns:

  • (Boolean) 


32
33
34
 
# File 'lib/suggest_db_indices/core.rb', line 32

def foreign_key? column_name
  column_name.end_with? "_id"
end

.format_index_migration_string(columns_by_table) ⇒ Object 



65
66
67
68
69
70
71
 
# File 'lib/suggest_db_indices/core.rb', line 65

def format_index_migration_string columns_by_table
  add_index_statements = columns_by_table.reduce('') do |s, (table, columns)|
    columns.each {|col| s += "    add_index :#{table}, :#{col}\n" }
    s
  end
  "  def change\n#{add_index_statements}\n  end\nend"
end

.generate_migration_file!(migration_contents) ⇒ Object 



73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/suggest_db_indices/core.rb', line 73

def generate_migration_file! migration_contents
  _ , migration_file_path  = Rails::Generators.invoke("active_record:migration",
                                                      ["add_indexes_via_suggest_db_indices_#{rand(36**8).to_s(36)}",
                                                       'BoiledGoose:Animal'])
  file_contents = File.read migration_file_path
  search_string = "ActiveRecord::Migration"
  stop_index = (file_contents.index(search_string)) + search_string.length
  new_file_contents = file_contents[0..stop_index] + migration_contents
  File.open(migration_file_path, 'w') {|f| f.write(new_file_contents) }
  migration_file_path
end

.go!(config = {}) ⇒ Object 



54
55
56
57
58
59
60
61
62
63
 
# File 'lib/suggest_db_indices/core.rb', line 54

def go! config = {}
  @config = default_options.reduce(config) do |h, (k,v)|
    if h[k]
      h
    else
      h.merge! k => v
    end
  end
  generate_migration_file! format_index_migration_string unindexed_foreign_key_columns_by_table
end

.hash_of_arrays ⇒ Object 



147
148
149
 
# File 'lib/suggest_db_indices/core.rb', line 147

def hash_of_arrays
  Hash.new {|h, k| h[k] = [] }
end

.hash_of_sets ⇒ Object 



151
152
153
 
# File 'lib/suggest_db_indices/core.rb', line 151

def hash_of_sets
  Hash.new {|h, k| h[k] = Set.new }
end

.indexed_columns_by_table ⇒ Object 



3
4
5
6
7
8
9
 
# File 'lib/suggest_db_indices/core.rb', line 3

def indexed_columns_by_table
  @indexed_columns_by_table ||= connection.tables.reduce({}) do |h, table_name|
    # Note: can index on multiple columns, which complicates things.  Assuming user has done
    # this correctly for now...
    h.merge table_name => connection.indexes(table_name).map {|index| index.columns}.flatten
  end
end

.non_pk_column_names(table_name) ⇒ Object 



11
12
13
14
15
 
# File 'lib/suggest_db_indices/core.rb', line 11

def non_pk_column_names table_name
  connection.columns(table_name).reject do |column|
    column.name == primary_key_name(connection, table_name)
  end.map(&:name)
end

.non_pk_columns_by_table ⇒ Object 



17
18
19
20
21
 
# File 'lib/suggest_db_indices/core.rb', line 17

def non_pk_columns_by_table
  @non_pk_columns_by_table ||= connection.tables.reduce({}) do |h, table_name|
    h.merge! table_name => non_pk_column_names(table_name)
  end
end

.prepare_log_file!(log_dir) ⇒ Object 



91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/suggest_db_indices/core.rb', line 91

def prepare_log_file! log_dir
  puts "Preparing log files..."
  tmpfile = Tempfile.new('tmplog')
  log_file_names = Dir.glob File.join log_dir, '*.log'
  puts "Found log files: #{log_file_names.inspect}"

  puts "Tailing each file!"
  log_file_names.each {|f| sh_dbg "tail -n #{NUM_LINES_TO_READ} #{f} >> #{tmpfile.path}" }
  puts "Stripping color codes!"
  stripped_log_file = Tempfile.new('stripped')
  # Because text search is too tricky with colors
  strip_color_codes! tmpfile.path, stripped_log_file.path
  stripped_log_file
end

.primary_key_name(connection, table_name) ⇒ Object

Stole this from activerecord schema dumper code



24
25
26
27
28
29
30
 
# File 'lib/suggest_db_indices/core.rb', line 24

def primary_key_name connection, table_name
  if connection.respond_to?(:pk_and_sequence_for)
    connection.pk_and_sequence_for(table_name).first rescue nil
  elsif connection.respond_to?(:primary_key)
    connection.primary_key(table_name)
  end
end

.remove_limit_clause(s) ⇒ Object 



155
156
157
158
159
160
161
 
# File 'lib/suggest_db_indices/core.rb', line 155

def remove_limit_clause s
  if matches = /(.+)\sLIMIT/.match(s)
    return matches[1]
  else
    return s
  end
end

.scan_log_files_for_queried_columns(log_dir, non_pk_columns_by_table = non_pk_columns_by_table) ⇒ Object 



106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/suggest_db_indices/core.rb', line 106

def scan_log_files_for_queried_columns log_dir, non_pk_columns_by_table = non_pk_columns_by_table
  stripped_log_file = prepare_log_file! log_dir

  queried_columns_by_table = hash_of_arrays
  # For debugging: Record from which SQL statement we got each column
  inferred_table_columns_by_raw_where_clause = hash_of_sets

  while line = stripped_log_file.gets
    line = remove_limit_clause(line.strip)
    if matches = /SELECT.+FROM\s\W?(\w+)\W?\sWHERE(.+)/i.match(line)
      table = matches[1]

      raw_where_clause = matches[2]
#          puts "Where: #{raw_where_clause}"

      raw_where_clause.split.map do |s|
        s.gsub('`','')
      end.reduce([]) do |memo, identifier| #TODO: Stop reducing to array, reduce to counter
        if identifier.include?('.')
          current_table, column_candidate = identifier.split('.')
        else
          current_table, column_candidate = [table, identifier]
        end

        if non_pk_columns_by_table[current_table].include? column_candidate
          # We only care about the identifiers that match up to a table and column.
          # This is a ghetto way to to avoid having to parse SQL (extremely difficult)
          memo << [current_table, column_candidate]
        else
          memo
        end
      end.each do |(table, column)|
        queried_columns_by_table[table] << column
        inferred_table_columns_by_raw_where_clause[raw_where_clause] << [table, column]
      end
    end
  end
  {:queried_columns_by_table => queried_columns_by_table,
   :inferred_table_columns_by_raw_where_clause => inferred_table_columns_by_raw_where_clause}
end

.sh_dbg(cmd) ⇒ Object 



169
170
171
172
 
# File 'lib/suggest_db_indices/core.rb', line 169

def sh_dbg cmd
  puts "Shelling:   #{cmd}"
  `#{cmd}`
end

.strip_color_codes!(file_name, output_path) ⇒ Object 



163
164
165
166
167
 
# File 'lib/suggest_db_indices/core.rb', line 163

def strip_color_codes! file_name, output_path
  # From: http://serverfault.com/a/154200
  sh_dbg 'sed "s/${esc}[^m]*m//g" ' + "#{file_name} >> #{output_path}"
  raise "There was a problem stripping colors" unless $?.success?
end

.unindexed_columns_by_table ⇒ Object 



42
43
44
45
46
 
# File 'lib/suggest_db_indices/core.rb', line 42

def unindexed_columns_by_table
  non_pk_columns_by_table.reduce({}) do |h, (table, columns)|
    h.merge table => columns - (indexed_columns_by_table[table] || [])
  end
end

.unindexed_foreign_key_columns_by_table ⇒ Object 



48
49
50
51
52
 
# File 'lib/suggest_db_indices/core.rb', line 48

def unindexed_foreign_key_columns_by_table
  unindexed_columns_by_table.reduce({}) do |h, (table, columns)|
    h.merge table => columns.select {|col| foreign_key?(col) }
  end
end