Module: SuggestDbIndices

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

Defined Under Namespace

Modules: Clojure Classes: Railtie

Constant Summary collapse

NUM_LINES_TO_READ = 
1000
VERSION = 
"0.1.0"

Class Method Summary collapse

Class Method Details

.column_quote_char ⇒ Object 



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

def column_quote_char
  @column_quote_char ||= connection.quote_column_name("sauerkraut")[0]
end

.config ⇒ Object 



54
55
56
 
# File 'lib/suggest_db_indices/core.rb', line 54

def config
  @config ||= default_config
end

.connection ⇒ Object 



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

def connection
  ActiveRecord::Base.connection
end

.default_config ⇒ Object 



130
131
132
133
134
 
# File 'lib/suggest_db_indices/core.rb', line 130

def default_config
  {:num_lines_to_scan => 10000,
   :examine_logs => false,
   :log_dir => File.join(Rails.root, 'log')}
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(table_col_pairs_to_index_with_attributes) ⇒ Object 



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

def format_index_migration_string table_col_pairs_to_index_with_attributes
  add_index_statements = table_col_pairs_to_index_with_attributes.reduce('') do |s, (table_col_pair, attributes)|
    table, col = table_col_pair
    s += "    add_index :#{table}, :#{col} #"
    comments = []
    comments << "foreign key" if attributes[:foreign_key_column]
    comments << "found in queries #{attributes[:found_count]} times" if attributes[:found_count]
    s += "#{comments.join(', ')}\n"
    s
  end
  "  def change\n#{add_index_statements}\n  end\nend"
end

.generate_migration_file!(migration_contents) ⇒ Object 



117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/suggest_db_indices/core.rb', line 117

def generate_migration_file! migration_contents
  _, migration_file_path = Rails::Generators.invoke("active_record:migration",
                                                    [name_migration_file,
                                                     'BoiledGoose:Animal']) # Bogus param, doesn't matter since contents will be replaced
  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) }
  puts "Migration result: \n #{new_file_contents}"
  migration_file_path
end

.go!(opts = {}) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/suggest_db_indices/core.rb', line 58

def go! opts = {}
  @config = opts.reduce(default_config) { |h, (k, v)| h.merge k => v }
  table_col_pair_attributes = hash_of_hashes

  unindexed_foreign_key_columns_by_table.each do |table, cols|
    cols.each do |col|
      table_col_pair = [table, col]
      table_col_pair_attributes[table_col_pair][:foreign_key_column] = true
    end
  end

  scan_result = scan_log_files
  columns_found_in_logs_count_by_table = scan_result[:queried_columns_by_table]
  columns_found_in_logs_count_by_table.each do |table, column_hashes|
    column_hashes.each do |col, found_count|
      table_col_pair = [table, col]
      table_col_pair_attributes[table_col_pair][:found_count] = found_count
    end
  end

  table_col_pair_validator = @config[:mode] == :conservative \
  ? lambda { |_, attributes| attributes[:foreign_key_column] && attributes[:found_count] }
  : lambda { |_, attributes| attributes[:foreign_key_column] }

  table_col_pairs_to_index_with_attributes =
      table_col_pair_attributes.select &table_col_pair_validator

  if table_col_pairs_to_index_with_attributes.any?
    generate_migration_file! format_index_migration_string table_col_pairs_to_index_with_attributes
  else
    puts "No missing indexes found!"
  end
end

.hash_of_arrays ⇒ Object 



196
197
198
 
# File 'lib/suggest_db_indices/core.rb', line 196

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

.hash_of_hashes ⇒ Object 



200
201
202
 
# File 'lib/suggest_db_indices/core.rb', line 200

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

.hash_of_sets ⇒ Object 



204
205
206
 
# File 'lib/suggest_db_indices/core.rb', line 204

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

.name_migration_file ⇒ Object 



105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/suggest_db_indices/core.rb', line 105

def name_migration_file
  name = "add_indexes_via_suggest_db_indices"
  existing_migration_files = Dir.glob File.join Rails.root, 'db', 'migrate/*.rb'

  if existing_migration_files.any? { |f| f.end_with?("#{name}.rb") }
    i = 1
    i += 1 while existing_migration_files.any? { |f| f.end_with?("#{name}_#{i}.rb") }
    name += "_#{i}"
  end
  name
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 



136
137
138
139
140
141
142
143
144
145
 
# File 'lib/suggest_db_indices/core.rb', line 136

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 #{config[:num_lines_to_scan]} #{f} >> #{tmpfile.path}" }
  tmpfile
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 



208
209
210
211
212
213
214
 
# File 'lib/suggest_db_indices/core.rb', line 208

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

.scan_log_files(log_dir = ) ⇒ Object

Scans log files for queried columns



156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/suggest_db_indices/core.rb', line 156

def scan_log_files log_dir = config()[:log_dir]
  stripped_log_file = prepare_log_file! log_dir

  queried_columns_by_table = hash_of_hashes
  # For debugging: Record from what table and columns we derived from each SQL statement
  inferred_table_columns_by_raw_where_clause = hash_of_sets
  non_matches = Set.new

  while line = stripped_log_file.gets
    line = remove_limit_clause(line.strip)
    if matches = /SELECT.+WHERE(.+)/i.match(line) #Old: /.+SELECT.+FROM\s\W?(\w+)\W?\sWHERE(.+)/
      raw_where_clause = matches[1]
      #          puts "Where: #{raw_where_clause}"
      raw_where_clause.split.each do |identifier|
        next if non_matches.include? identifier
        # Go through the where clause to find columns that were queried
        if identifier.include?('.') # e.g., "post"."user_id"
          current_table, column_candidate = identifier.split('.')
          current_table.gsub! table_quote_char, ''
          column_candidate.gsub! column_quote_char, ''
          if non_pk_columns_by_table[current_table] && 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)
            if Clojure.get_in queried_columns_by_table, [current_table, column_candidate]
              queried_columns_by_table[current_table][column_candidate] += 1
            else
              queried_columns_by_table[current_table] = {column_candidate => 1}
            end
            inferred_table_columns_by_raw_where_clause[raw_where_clause] << [current_table, column_candidate]
          else
            non_matches << identifier
          end
        end
      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 



216
217
218
219
 
# File 'lib/suggest_db_indices/core.rb', line 216

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

.table_quote_char ⇒ Object 



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

def table_quote_char
  @table_quote_char ||= connection.quote_table_name("boiled_goose")[0]
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