module SuggestDbIndices

Constants

NUM_LINES_TO_READ
VERSION

Public Class Methods

column_quote_char() click to toggle source
# File lib/suggest_db_indices/core.rb, line 151
def column_quote_char
  @column_quote_char ||= connection.quote_column_name("sauerkraut")[0]
end
config() click to toggle source
# File lib/suggest_db_indices/core.rb, line 54
def config
  @config ||= default_config
end
connection() click to toggle source
# File lib/suggest_db_indices/core.rb, line 38
def connection
  ActiveRecord::Base.connection
end
default_config() click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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 = {}) click to toggle source
# 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() click to toggle source
# File lib/suggest_db_indices/core.rb, line 196
def hash_of_arrays
  Hash.new { |h, k| h[k] = [] }
end
hash_of_hashes() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# 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) click to toggle source
# 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) click to toggle source

Stole this from activerecord schema dumper code

# 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) click to toggle source
# 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 = config()[:log_dir]) click to toggle source

Scans log files for queried columns

# 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) click to toggle source
# File lib/suggest_db_indices/core.rb, line 216
def sh_dbg cmd
  puts "Shelling:   #{cmd}"
  `#{cmd}`
end
table_quote_char() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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