If you have a suggestion for the replacing the line of code, I'll test it. Regular expressions aren't my forte.
On Jan 5, 4:07 pm, Ryan Bigg <[email protected]> wrote: > [:][c][o][n][d][i][t][i][o][n][s].[=][>]. > > Er, what? Why not just match for :conditions? > ----- > Ryan Bigg > Freelancerhttp://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 -~----------~----~----~----~------~----~------~--~---
