Module: Indexer

Defined in:
lib/indexer.rb

Class Method Summary (collapse)

Class Method Details

+ (Object) ar_find_indexes(migration_mode = true)



278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
# File 'lib/indexer.rb', line 278

def self.ar_find_indexes(migration_mode=true)
  find_indexes = self.scan_finds
  
  if migration_mode
    unless find_indexes.keys.empty?
      add = []
      remove = []
      find_indexes.each do |table_name, keys_to_add|
        keys_to_add.each do |key|
          next if key_exists?(table_name,key)
          next if key.blank?
          if key.is_a?(Array)
            keys = key.collect {|k| ":#{k}"}
            add << "add_index :#{table_name}, [#{keys.join(', ')}]"
            remove << "remove_index :#{table_name}, :column => [#{keys.join(', ')}]"
          else
            add << "add_index :#{table_name}, :#{key}"
            remove << "remove_index :#{table_name}, :#{key}"
          end
        
        end
      end
    
      migration = <<EOM      
class AddFindsMissingIndexes < ActiveRecord::Migration
def self.up

  # These indexes were found by searching for AR::Base finds on your application
  # It is strongly recommanded that you will consult a professional DBA about your infrastucture and implemntation before
  # changing your database in that matter.
  # There is a possibility that some of the indexes offered below is not required and can be removed and not added, if you require
  # further assistance with your rails application, database infrastructure or any other problem, visit:
  #
  # http://www.railsmentors.org
  # http://www.railstutor.org
  # http://guides.rubyonrails.org

  #{add.uniq.join("\n    ")}
end

def self.down
  #{remove.uniq.join("\n    ")}
end
end
EOM
 
      puts "## Drop this into a file in db/migrate ##"
      puts migration
    end
  end
else
  find_indexes
end

+ (Object) check_for_indexes(migration_format = false)



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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
91
92
# File 'lib/indexer.rb', line 9

def self.check_for_indexes(migration_format = false)
  model_names = []
  Dir.chdir(Rails.root) do 
    model_names = Dir["**/app/models/**/*.rb"].collect {|filename| File.basename(filename) }.uniq
  end

  model_classes = []
  model_names.each do |model_name|
    class_name = model_name.sub(/\.rb$/,'').camelize
    begin
      klass = class_name.split('::').inject(Object){ |klass,part| klass.const_get(part) }
      if klass < ActiveRecord::Base && !klass.abstract_class?
        model_classes << klass
      end
    rescue
      # No-op
    end
  end
  
  @index_migrations = Hash.new([])

  model_classes.each do |class_name|

    # check if this is an STI child instance      
    if class_name.base_class.name != class_name.name && (class_name.column_names.include?(class_name.base_class.inheritance_column) || class_name.column_names.include?(class_name.inheritance_column))
      
      # add the inharitance column on the parent table
      # index migration for STI should require both the primary key and the inheritance_column in a composite index.
      @index_migrations[class_name.base_class.table_name] += [[class_name.inheritance_column, class_name.base_class.primary_key].sort] unless @index_migrations[class_name.base_class.table_name].include?([class_name.base_class.inheritance_column].sort)
    end
    #puts "class name: #{class_name}"
    class_name.reflections.each_pair do |reflection_name, reflection_options|
      #puts "reflection => #{reflection_name}"
      case reflection_options.macro
      when :belongs_to
        # polymorphic?
        @table_name = class_name.table_name.to_s 
        if reflection_options.options.has_key?(:polymorphic) && (reflection_options.options[:polymorphic] == true)
          poly_type = "#{reflection_options.name.to_s}_type"
          poly_id = "#{reflection_options.name.to_s}_id"
  
          @index_migrations[@table_name.to_s] += [[poly_type, poly_id].sort] unless @index_migrations[@table_name.to_s].include?([poly_type, poly_id].sort)
        else

          foreign_key = reflection_options.options[:foreign_key] ||= reflection_options.primary_key_name
            @index_migrations[@table_name.to_s] += [foreign_key] unless @index_migrations[@table_name.to_s].include?(foreign_key)
        end
      when :has_and_belongs_to_many
        table_name = reflection_options.options[:join_table] ||= [class_name.table_name, reflection_name.to_s].sort.join('_')
        association_foreign_key = reflection_options.options[:association_foreign_key] ||= "#{reflection_name.to_s.singularize}_id"
        
        # Guess foreign key?
        if reflection_options.options[:foreign_key]
          foreign_key = reflection_options.options[:foreign_key]
        elsif reflection_options.options[:class_name]
          foreign_key = reflection_options.options[:class_name].foreign_key
        else
          foreign_key = "#{class_name.name.tableize.singularize}_id"
        end
        
        composite_keys = [association_foreign_key, foreign_key]
        
        @index_migrations[table_name.to_s] += [composite_keys] unless @index_migrations[table_name].include?(composite_keys)
        @index_migrations[table_name.to_s] += [composite_keys.reverse] unless @index_migrations[table_name].include?(composite_keys.reverse)

      else
        #nothing
      end
    end
  end

  @missing_indexes = {}
  
  @index_migrations.each do |table_name, foreign_keys|

    unless foreign_keys.blank?
      existing_indexes = ActiveRecord::Base.connection.indexes(table_name.to_sym).collect {|index| index.columns.size > 1 ? index.columns : index.columns.first}
      keys_to_add = foreign_keys.uniq - existing_indexes #self.sortalize(foreign_keys.uniq) - self.sortalize(existing_indexes)
      @missing_indexes[table_name] = keys_to_add unless keys_to_add.empty?
    end
  end
  
  @missing_indexes
end

+ (Object) check_line_for_find_indexes(file_name, line)

Check line for find* methods (include find_all, find_by and just find)



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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
# File 'lib/indexer.rb', line 161

def self.check_line_for_find_indexes(file_name, line)
  
  # TODO: Assumes that you have a called on #find. you can actually call #find without a caller in a model code. ex:
  # def something
  #   find(self.id)
  # end
  #
  # find_regexp = Regexp.new(/([A-Z]{1}[A-Za-z]+|self).(find){1}((_all){0,1}(_by_){0,1}([A-Za-z_]+))?\(([0-9A-Za-z"\':=>. \[\]{},]*)\)/)
  
  find_regexp = Regexp.new(/(([A-Z]{1}[A-Za-z]+|self).)?(find){1}((_all){0,1}(_by_){0,1}([A-Za-z_]+))?\(([0-9A-Za-z"\':=>. \[\]{},]*)\)/)
  
  # If line matched a finder
  if matches = find_regexp.match(line)

    model_name, column_names, options = matches[2], matches[7], matches[8]
    
    # if the finder class is "self" or empty (can be a simple "find()" in a model)
    if model_name == "self" || model_name.blank?
      model_name = File.basename(file_name).sub(/\.rb$/,'').camelize
      table_name = model_name.constantize.table_name            
    else
      if model_name.respond_to?(:constantize)
        if model_name.constantize.respond_to?(:table_name)             
          table_name = model_name.constantize.table_name
        end
      end
    end
    
    # Check that all prerequisites are met
    if model_name.present? && table_name.present? && model_name.constantize.ancestors.include?(ActiveRecord::Base)
      primary_key = model_name.constantize.primary_key
      @indexes_required[table_name] += [primary_key] unless @indexes_required[table_name].include?(primary_key)

      if column_names.present?
        column_names = column_names.split('_and_')

        # remove find_by_sql references.
        column_names.delete("sql")

        column_names = model_name.constantize.column_names & column_names

        # Check if there were more than 1 column
        if column_names.size == 1
          column_name = column_names.first
          @indexes_required[table_name] += [column_name] unless @indexes_required[table_name].include?(column_name)
        else
          @indexes_required[table_name] += [column_names] unless @indexes_required[table_name].include?(column_names)
          @indexes_required[table_name] += [column_names.reverse] unless @indexes_required[table_name].include?(column_names.reverse)
        end
      end
    end
  end
end

+ (Object) indexes_list



272
273
274
275
276
# File 'lib/indexer.rb', line 272

def self.indexes_list
  check_for_indexes.each do |table_name, keys_to_add|
    puts "Table '#{table_name}' => #{keys_to_add.to_sentence}"
  end
end

+ (Boolean) key_exists?(table, key_columns)

Returns:

  • (Boolean)


215
216
217
218
# File 'lib/indexer.rb', line 215

def self.key_exists?(table,key_columns)     
  result = (key_columns.to_a - ActiveRecord::Base.connection.indexes(table).map { |i| i.columns }.flatten)
  result.empty?
end

+ (Object) scan_finds



94
95
96
97
98
99
100
101
102
103
104
105
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
146
147
148
149
150
151
152
153
154
155
156
157
158
# File 'lib/indexer.rb', line 94

def self.scan_finds
  
  
  # Collect all files that can contain queries, in app/ directories (includes plugins and such)
  # TODO: add lib too ? 
  file_names = []
  
  Dir.chdir(Rails.root) do 
    file_names = Dir["**/app/**/*.rb"].uniq.reject {|file_with_path| file_with_path.include?('test')}
  end
  
  @indexes_required = Hash.new([])
  
  # Scan each file
  file_names.each do |file_name| 
    current_file = File.open(File.join(Rails.root, file_name), 'r')

    # Scan each line
    current_file.each do |line|
      
      # by default, try to add index on primary key, based on file name
      # this will fail if the file isnot a model file
      
      begin
        current_model_name = File.basename(file_name).sub(/\.rb$/,'').camelize
      rescue
        # NO-OP
      end
      
      # Get the model class
      klass = current_model_name.split('::').inject(Object){ |klass,part| klass.const_get(part) } rescue nil
      
      # Only add primary key for active record dependent classes and non abstract ones too.
      if klass.present? && klass < ActiveRecord::Base && !klass.abstract_class?
        current_model = current_model_name.constantize
        primary_key = current_model.primary_key
        table_name = current_model.table_name
        @indexes_required[table_name] += [primary_key] unless @indexes_required[table_name].include?(primary_key)
      end
      
      check_line_for_find_indexes(file_name, line)
      
    end
  end
  
  @missing_indexes = {}
  @indexes_required.each do |table_name, foreign_keys|

    unless foreign_keys.blank?          
      begin
        if ActiveRecord::Base.connection.tables.include?(table_name.to_s)
          existing_indexes = ActiveRecord::Base.connection.indexes(table_name.to_sym).collect {|index| index.columns.size > 1 ? index.columns : index.columns.first}
          keys_to_add = self.sortalize(foreign_keys.uniq) - self.sortalize(existing_indexes)
          @missing_indexes[table_name] = keys_to_add unless keys_to_add.empty?
        else
          puts "BUG: table '#{table_name.to_s}' does not exist, please report this bug."
        end
      rescue Exception => e
        puts "ERROR: #{e}"
      end
    end
  end
  
  @indexes_required
end

+ (Object) simple_migration



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
# File 'lib/indexer.rb', line 220

def self.simple_migration
  migration_format = true
  missing_indexes = check_for_indexes(migration_format)

  unless missing_indexes.keys.empty?
    add = []
    remove = []
    missing_indexes.each do |table_name, keys_to_add|
      keys_to_add.each do |key|
        next if key_exists?(table_name,key)
        next if key.blank?
        if key.is_a?(Array)
          keys = key.collect {|k| ":#{k}"}
          add << "add_index :#{table_name}, [#{keys.join(', ')}]"
          remove << "remove_index :#{table_name}, :column => [#{keys.join(', ')}]"
        else
          add << "add_index :#{table_name}, :#{key}"
          remove << "remove_index :#{table_name}, :#{key}"
        end
        
      end
    end
    
    migration = <<EOM  
class AddMissingIndexes < ActiveRecord::Migration
def self.up
  
  # These indexes were found by searching for AR::Base finds on your application
  # It is strongly recommanded that you will consult a professional DBA about your infrastucture and implemntation before
  # changing your database in that matter.
  # There is a possibility that some of the indexes offered below is not required and can be removed and not added, if you require
  # further assistance with your rails application, database infrastructure or any other problem, visit:
  #
  # http://www.railsmentors.org
  # http://www.railstutor.org
  # http://guides.rubyonrails.org

  
  #{add.uniq.join("\n    ")}
end

def self.down
  #{remove.uniq.join("\n    ")}
end
end
EOM

    puts "## Drop this into a file in db/migrate ##"
    puts migration
  end
end

+ (Object) sortalize(array)



3
4
5
6
7
# File 'lib/indexer.rb', line 3

def self.sortalize(array)
  Marshal.load(Marshal.dump(array)).each do |element|
    element.sort! if element.is_a?(Array)
  end
end