[:][c][o][n][d][i][t][i][o][n][s].[=][>].
Er, what? Why not just match for :conditions?
-----
Ryan Bigg
Freelancer
http://frozenplague.net
On 06/01/2009, at 10:03 AM, Ben wrote:
>
> If you are looking to improve your database performance, you might
> want to index your database, esspecially if it is large. Here are
> some rake tasks that will look at your code and active record and
> automatically add indexes to your devel db.
>
> These two tasks are fairly rough drafts. I am open to any input that
> would expand on what I have here.
>
> lib/tasks/db_indexing.rake
> (then run rake db_indexing:index_fks_in_model and rake
> db_indexing:index_sql_calls_in_app )
> ------
>
> require 'ActiveRecord'
> require(File.join(RAILS_ROOT, 'config', 'environment'))
>
> namespace :db_indexing do
> desc "This task will search text files for rails queries and,
> checking for duplicates, generate sql that properly indexes these
> commands"
> task :index_fks_in_model do
> connection = ActiveRecord::Base.connection
> connection.tables.each do |table|
> begin
> table_columns = eval(table.singularize.capitalize +
> ".column_names")
> table_columns.each do |column_string|
> if column_string.include?('_id')
> connection.execute("CREATE INDEX " + table + "_" +
> column_string + "_index" + "ON" + "#{table}(#{column_string});")
> end
> end
> rescue
> end
> end
> puts "Task completed"
> end
>
> desc "This task will iterate through the objects in your model and
> find out which columns end with _id and generate an sql commands to
> index these columns"
> task :index_sql_calls_in_app do
> connection = ActiveRecord::Base.connection
> controller_files = File.join("**", "*_controller.rb")
> helper_files = File.join("**", "*_helper.rb")
> no_of_calls = 0
> array_of_files = Dir.glob(controller_files).concat(Dir.glob
> (helper_files))
> array_of_files.each do |file|
> temp_f = File.new(file)
> temp_f.each do |line|
> if line.to_s.include?(":conditions")
> array_of_columns_to_index = []
> critical_string = line.to_s.match('([:][c][o][n][d][i][t][i]
> [o][n][s].[=][>].)\[.+\]')[0] if line.to_s.match('([:][c][o][n][d][i]
> [t][i][o][n][s].[=][>].)\[.+\]')
> table = line.to_s.match('=.+')[0]
> table = table[1, table.index('.') - 1]
> table = table.chomp.strip
> temp_string = ''
> table.each_char do |char|
> if char.downcase.to_s == char.to_s
> temp_string = temp_string + char
> else
> temp_string = temp_string + '_' + char.downcase
> end
> end
> table = temp_string[1, temp_string.length]
> table = table.chomp.strip.downcase.pluralize
> cs2 = critical_string.to_s.match('\[.+\]')[0] if
> critical_string.to_s.match('\[.+\]')
> if cs2
> cs3 = cs2[1, cs2.index(',') - 1] if cs2.index(',')
> else
> end
> if cs3
> cs3 = cs3[1,cs3.length - 2]
> cs3.to_s.each(' ') do |tstr|
> tstr = tstr.chomp.strip
> tstr = tstr.downcase
> if !tstr.match('\W') && tstr != 'and' && tstr != 'or' &&
> tstr != 'is' && tstr != '1' && tstr != 'null' && tstr != '0' && tstr !
> = 'id'
> array_of_columns_to_index << tstr
> end
> end
> end
> index_name = table.to_s + "_"
> index_string2 = ''
> if array_of_columns_to_index.length > 0
> array_of_columns_to_index.each do |column_to_index|
> index_name = index_name + column_to_index + "_"
> index_string2 = index_string2 + column_to_index + ','
> end
> index_string2 = index_string2[0,index_string2.length - 1]
> index_name = index_name + 'index'
> sql_command = "CREATE INDEX " + index_name + " ON " +
> table.to_s + "(" + index_string2 + ")" + ";"
>
>
> begin
> puts sql_command
> connection.execute(sql_command)
> rescue StandardError =>e
> puts "ERROR"
> # if !e.clean_message.to_s.include?("42000")
> puts e.clean_message.to_s
> puts e.backtrace
> # end
> end
> end
> end
>
> end
> end
> puts "Task completed"
> end
>
>
> end
>
>
> ------
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
