search

eladmeidar / rails_indexes

A rake task to track down missing database indexes. does not assume that all foreign keys end with the convention of _id.
http://blog.eizesus.com/2009/9/find-where-to-index-your-tables-on-a-rails-app/
MIT License
489 stars 35 forks source link

Update code as Gem for Rails 3 #23

Closed warpc closed 13 years ago

warpc commented 13 years ago

Hello, eladmeidar! I am re-create the plugin as a Gem. All tests are passed. In README.texlte need to change section "Installation".

mexxer commented 13 years ago

your gem did not work for me, Ruby 1.9.2 Rails 3.0.1

warpc commented 13 years ago

You use last version from https://github.com/warpc/rails_indexes? Have you received any error messages?

mexxer commented 13 years ago

Yes, I used the last version. Oh and I have Ruby 1.9.1 not 2

The error I get:

rake aborted! Don't know how to build task 'db:index_migration' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:1720:in []' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2040:ininvoke_task' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2019:in block (2 levels) in top_level' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2019:ineach' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2019:in block in top_level' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2058:instandard_exception_handling' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2013:in top_level' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:1992:inrun' /usr/local/ruby/bin/rake:31:in `

'

warpc commented 13 years ago

Can you add input of command

  bundle exec rake -T db

and content of your Gemfile.lock

mexxer commented 13 years ago

The command gives me:

rake db:create # Create the database from config/database.yml for the current Rails.env (use db:create:al... rake db:drop # Drops the database for the current Rails.env (use db:drop:all to drop all databases) rake db:find_query_indexes # Collect indexes based on AR::Base.find calls. rake db:fixtures:load # Load fixtures into the current environment's database. rake db:index_migration # Collect indexes based on associations rake db:migrate # Migrate the database (options: VERSION=x, VERBOSE=false). rake db:migrate:status # Display status of migrations rake db:rollback # Rolls the schema back to the previous version (specify steps w/ STEP=n). rake db:schema:dump # Create a db/schema.rb file that can be portably used against any DB supported by AR rake db:schema:load # Load a schema.rb file into the database rake db:seed # Load the seed data from db/seeds.rb rake db:setup # Create the database, load the schema, and initialize with the seed data (use db:reset to... rake db:structure:dump # Dump the database structure to an SQL file rake db:version # Retrieves the current schema version number

and my Gemfile.lock :

GIT remote: git://github.com/nesquena/query_reviewer.git revision: 9de60e75d18068d40cd3f8439a3de28eb4df5f3b specs: query_reviewer (0.1.2)

GIT remote: https://github.com/warpc/rails_indexes revision: 0d4e405d5964fac64c91a34aa0400e26cdc2f18e specs: rails_indexes (1.1) actionpack activerecord (>= 2.3.0) railties

PATH remote: ./vendor/plugins/nested_form-0.1.1 specs: nested_form (0.1.1)

GEM remote: http://rubygems.org/ specs: abstract (1.0.0) actionmailer (3.0.1) actionpack (= 3.0.1) mail (~> 2.2.5) actionpack (3.0.1) activemodel (= 3.0.1) activesupport (= 3.0.1) builder (~> 2.1.2) erubis (~> 2.6.6) i18n (~> 0.4.1) rack (~> 1.2.1) rack-mount (~> 0.6.12) rack-test (~> 0.5.4) tzinfo (~> 0.3.23) activemodel (3.0.1) activesupport (= 3.0.1) builder (~> 2.1.2) i18n (~> 0.4.1) activerecord (3.0.1) activemodel (= 3.0.1) activesupport (= 3.0.1) arel (~> 1.0.0) tzinfo (~> 0.3.23) activeresource (3.0.1) activemodel (= 3.0.1) activesupport (= 3.0.1) activesupport (3.0.1) acts_as_list (0.1.3) acts_as_tree (0.1.1) annotate-models (1.0.4) arel (1.0.1) activesupport (~> 3.0.0) bcrypt-ruby (2.1.4) builder (2.1.2) devise (1.4.2) bcrypt-ruby (~> 2.1.2) orm_adapter (~> 0.0.3) warden (~> 1.0.3) diff-lcs (1.1.2) erubis (2.6.6) abstract (>= 1.0.0) factory_girl (1.3.2) factory_girl_rails (1.0) factory_girl (~> 1.3) rails (>= 3.0.0.beta4) faker (0.3.1) formtastic (1.2.4) actionpack (>= 2.3.7) activesupport (>= 2.3.7) i18n (~> 0.4) i18n (0.4.2) jquery-rails (1.0.12) railties (~> 3.0) thor (~> 0.14) mail (2.2.10) activesupport (>= 2.3.6) i18n (~> 0.4.1) mime-types (~> 1.16) treetop (~> 1.4.8) meta_search (0.9.8) actionpack (~> 3.0.0) activerecord (~> 3.0.0) activesupport (~> 3.0.0) arel (~> 1.0.1) meta_where (0.9.6) activerecord (~> 3.0.0) activesupport (~> 3.0.0) arel (~> 1.0.1) mime-types (1.16) nifty-generators (0.4.2) nokogiri (1.4.4) orm_adapter (0.0.5) polyglot (0.3.1) rack (1.2.1) rack-mount (0.6.13) rack (>= 1.0.0) rack-test (0.5.6) rack (>= 1.0) rails (3.0.1) actionmailer (= 3.0.1) actionpack (= 3.0.1) activerecord (= 3.0.1) activeresource (= 3.0.1) activesupport (= 3.0.1) bundler (~> 1.0.0) railties (= 3.0.1) railties (3.0.1) actionpack (= 3.0.1) activesupport (= 3.0.1) rake (>= 0.8.4) thor (~> 0.14.0) rake (0.8.7) rspec (2.0.1) rspec-core (~> 2.0.1) rspec-expectations (~> 2.0.1) rspec-mocks (~> 2.0.1) rspec-core (2.0.1) rspec-expectations (2.0.1) diff-lcs (>= 1.1.2) rspec-mocks (2.0.1) rspec-core (~> 2.0.1) rspec-expectations (~> 2.0.1) rspec-rails (2.0.1) rspec (~> 2.0.0) slim_scrooge (1.0.11) spork (0.8.4) sqlite3-ruby (1.3.1) thor (0.14.6) treetop (1.4.9) polyglot (>= 0.3.1) tzinfo (0.3.23) warden (1.0.5) rack (>= 1.0) webrat (0.7.1) nokogiri (>= 1.2.0) rack (>= 1.0) rack-test (>= 0.5.3) will_paginate (3.0.pre2)

PLATFORMS ruby

DEPENDENCIES acts_as_list acts_as_tree annotate-models (= 1.0.4) devise (= 1.4.2) factory_girl_rails (= 1.0) faker (= 0.3.1) formtastic (~> 1.2.4) jquery-rails (= 1.0.12) meta_search meta_where nested_form (= 0.1.1)! nifty-generators query_reviewer! rails (= 3.0.1) rails_indexes! rspec (= 2.0.1) rspec-rails (= 2.0.1) slim_scrooge spork (= 0.8.4) sqlite3-ruby (= 1.3.1) webrat (= 0.7.1) will_paginate (= 3.0.pre2)

warpc commented 13 years ago

It very strange. In you first variant of your message section about rails_indexes was missing.

Do you use before update previous message

 bundle install

If yes, can you update input of 

 bundle exec rake -T db

In DEPENDENCIES list rails_indexes is missing, but he should be there.

If not, than i try to repeat the conditions and find a problem

mexxer commented 13 years ago

Yea I removed rails_indexes from my Gemfile so that wasn't in there anymore... But the gem is still installed anyway. It just didn't show up in the gemfile.lock because i ran bundle install for a couple of other gems. I put it in again and updated my post. The dependencies are all there but I'm still getting the same error

mexxer commented 13 years ago

Nope sry, it's not the same error ... now I get a different one:

rake aborted! uninitialized constant Recipient /usr/local/ruby/lib/ruby/gems/1.9.1/gems/rspec-core-2.0.1/lib/rspec/core/backward_compatibility.rb:20:in const_missing' /usr/local/ruby/lib/ruby/gems/1.9.1/gems/rspec-expectations-2.0.1/lib/rspec/expectations/backward_compatibility.rb:6:inco nst_missing' /usr/local/ruby/lib/ruby/gems/1.9.1/gems/activesupport-3.0.1/lib/active_support/inflector/methods.rb:124:in block in const antize' /usr/local/ruby/lib/ruby/gems/1.9.1/gems/activesupport-3.0.1/lib/active_support/inflector/methods.rb:123:ineach' /usr/local/ruby/lib/ruby/gems/1.9.1/gems/activesupport-3.0.1/lib/active_support/inflector/methods.rb:123:in constantize' /usr/local/ruby/lib/ruby/gems/1.9.1/gems/activesupport-3.0.1/lib/active_support/core_ext/string/inflections.rb:43:inconstantize' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/rails_indexes.rb:153:in block (2 levels) in check_for_indexes ' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/rails_indexes.rb:117:ineach_pair' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/rails_indexes.rb:117:in block in check_for_indexes' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/rails_indexes.rb:107:ineach' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/rails_indexes.rb:107:in check_for_indexes' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/rails_indexes.rb:284:insimple_migration' /home/max/.bundler/ruby/1.9.1/rails_indexes-0d4e405d5964/lib/tasks/rails_indexes.rake:10:in block (2 levels) in <top (requ ired)>' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:634:incall' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:634:in block in execute' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:629:ineach' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:629:in execute' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:595:inblock in invoke_with_call_chain' /usr/local/ruby/lib/ruby/1.9.1/monitor.rb:201:in mon_synchronize' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:588:ininvoke_with_call_chain' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:581:in invoke' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2041:ininvoke_task' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2019:in block (2 levels) in top_level' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2019:ineach' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2019:in block in top_level' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2058:instandard_exception_handling' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:2013:in top_level' /usr/local/ruby/lib/ruby/1.9.1/rake.rb:1992:inrun' /usr/local/ruby/bin/rake:31:in `

'

mexxer commented 13 years ago

There maybe something wrong with my message system and not your gem. I'm gonna try something real quick.

mexxer commented 13 years ago

appearently rails_indexes can't interpret this:

belongs_to :recipient, :class_name => "User"

warpc commented 13 years ago

This is error on has_many :through relations. Can you post it here?

mexxer commented 13 years ago

class Message < ActiveRecord::Base belongs_to :author, :class_name => "User" has_many :message_copies has_many :recipients, :through => :message_copies

class MessageCopy < ActiveRecord::Base belongs_to :message belongs_to :recipient, :class_name => "User" belongs_to :folder delegate :author, :created_at, :subject, :body, :recipients, :to => :message

class User < ActiveRecord::Base has_many :sent_messages, :class_name => "Message", :foreign_key => "author_id", :dependent => :destroy has_many :received_messages, :class_name => "MessageCopy", :foreign_key => "recipient_id" has_many :new_messages, :class_name => "MessageCopy", :foreign_key => "recipient_id", :conditions => "read = 'f'"

warpc commented 13 years ago

Can you add has_many form User model

mexxer commented 13 years ago

added

warpc commented 13 years ago

Tomorrow i pull same fix for more correct error message. At now moment i have no idea, there error is.

It will be great, if you are able to launch new version and send feedback.

mexxer commented 13 years ago

sure no problem!

warpc commented 13 years ago

@mexxer, i update gem. At now it must work on your example. Also i add issue in https://github.com/warpc/rails_indexes/issues/1 for discussions.

Please check it and send feedback. Thanks!

mexxer commented 13 years ago

NIce! It works now! The list of missing indexes is created. I still get a couple of errors in the beginning though:

WARNING: Global access to Rake DSL methods is deprecated. Please include ... Rake::DSL into classes and modules which use the Rake DSL methods. WARNING: DSL method Quhub::Application#task called at /usr/local/ruby/lib/ruby/gems/1.9.1/gems/railties-3.0.1/lib/rails/app lication.rb:214:in `initialize_tasks' "Some errors here:" "Please add info after this string in to https://github.com/warpc/rails_indexes/issues" "Class: User" "Association type: has_many" "Association options: {:through=>:user_visits, :source=>:visitor, :dependent=>:destroy, :extend=>[]}" "uninitialized constant Visitor"

"Some errors here:" "Please add info after this string in to https://github.com/warpc/rails_indexes/issues" "Class: User" "Association type: has_many" "Association options: {:through=>:friendships_out, :source=>\"user_target\", :foreign_key=>\"user_id\", :conditions=>\"is_a ccepted = 't'\", :extend=>[]}" "uninitialized constant FriendshipsOut"

"Some errors here:" "Please add info after this string in to https://github.com/warpc/rails_indexes/issues" "Class: User" "Association type: has_many" "Association options: {:through=>:friendships_in, :source=>\"user\", :foreign_key=>\"user_id_target\", :conditions=>\"is_ac cepted = 't'\", :extend=>[]}" "uninitialized constant FriendshipsIn"

And those are appeartenly the indexes that are missing:

add_index :user_questions, [:id, :type] add_index :user_questions, :user_id add_index :user_questions, :question_id add_index :folders, [:id, :type] add_index :folders, :user_id add_index :message_copies, [:id, :type] add_index :message_copies, :message_id add_index :message_copies, :recipient_id add_index :message_copies, :folder_id add_index :message_copies, [:recipient_id, :message_id] add_index :message_copies, [:message_id, :recipient_id] add_index :questions, [:id, :type] add_index :questions, :user_id add_index :questions, :question_genre_id add_index :answers, [:id, :type] add_index :answers, :question_id add_index :accepted_answers, [:, :answer_id] add_index :accepted_answers, [:answer_id, :] add_index :accepted_answers, [:id, :type] add_index :accepted_answers, :user_question_id add_index :importance_counts, [:id, :type] add_index :importance_counts, :question_id add_index :friendships, [:id, :type] add_index :friendships, :user_id add_index :friendships, :user_id_target add_index :messages, [:id, :type] add_index :messages, :author_id add_index :user_visits, [:id, :type] add_index :user_visits, :user_id add_index :user_visits, :visit_id add_index :answer_counts, [:id, :type] add_index :answer_counts, :question_id add_index :question_genres, [:id, :type] add_index :microposts, [:id, :type] add_index :question_skips, [:id, :type] add_index :question_skips, :user_id add_index :question_skips, :question_id add_index :importances, [:id, :type] add_index :importances, :question_id add_index :users, [:id, :type]

What does that :type attribute mean?

warpc commented 13 years ago

Very good. You use very complex association. Can you add information about visitor association in User model?

mexxer commented 13 years ago

has_many :user_visits, :foreign_key => "user_id", :dependent => :destroy has_many :visitors, :through => :user_visits, :source => :visitor, :dependent => :destroy

class UserVisit < ActiveRecord::Base attr_accessible :visit_id

belongs_to :user belongs_to :visitor, :foreign_key => 'visit_id', :class_name => 'User'

warpc commented 13 years ago

@mexxer, i remember about this problem. I will try to resolve it in this week. Sorry for the delay

plentz commented 11 years ago

@mexxer @warpc hey guys, just an update: I think I've fixed this problem in my branch/new gem lol_dba. Someone could give it a shot? I also improved a few things and fixed other bugs that users posted.