Get a list of all foreign keys (with their respective tables

As a general speed-up, i want to index all of my foreign keys. Rather
than hand-code them all into a migration (thus probably making some
mistakes), i'd like to make a little script, or method to do it.

I thought of maybe something like

sql = ActiveRecord::Base.connection
klasses = <get all model classes somehow>
klasses.each do |klass|
  foreign_key_columns = klass.column_names.select{|s| s.match(/_id$/)}
  foreign_key_columns.each do |column_name|
    sql.execute("alter table #{klass.tablename} add index
(#{column_name})")
  end
end

but, this seems a bit hacky and dirty. Can anyone see a nicer way?
And, how can i get all of my model classes?

I'm using mysql, in case that's relevant.

thanks
max

Re: generating a list of model classes, I was hoping something like this would work:

  ObjectSpace.each_object(ActiveRecord::Base) {|o| puts(o)}

But no dice (in a rails console anyway)--that will enumerate instances of model classes, but not the classes themselves. I guess ObjectSpace doesn't consider classes objects? Confusing...

I guess you could troll the files under /app/models... But you were looking to go *less* hacky, eh? :wink:

Re: generating a list of model classes, I was hoping something like
this would work:

ObjectSpace.each_object(ActiveRecord::Base) {|o| puts(o)}

But no dice (in a rails console anyway)--that will enumerate
instances of model classes, but not the classes themselves. I guess
ObjectSpace doesn't consider classes objects? Confusing...

Classes are objects, but subclasses of ActiveRecord::Base are instance
of ActiveRecord::Base, they're instances of Class. You sort of have to
walk app/models anyway, to make sure all the classes are actually
loaded.

Fred

Ach, of course. Thanks Fred.

Roy Pardee wrote:

Ach, of course. Thanks Fred.

I actually ended up not going via the classes, but just dealing with the
database direct:

def index_all_tables_foreign_keys
  sql = ActiveRecord::Base.connection
  tables = sql.select_values("show tables")
  messages = []
  tables.each do |table|
    foreign_key_column_hashes = sql.select_all("desc
#{table}").select{|hash| hash["Field"].match(/_id$/)}
    foreign_key_column_hashes.each do |column_hash|
      begin
        if column_hash["Key"] == ""
          sql.execute("alter table #{table} add index
(#{column_hash["Field"]})")
          messages << "added index to #{table}:#{column_hash["Field"]}"
        end
      rescue
        messages << "FAILED to add index to
#{table}:#{column_hash["Field"]}"
      end
    end
  end
  messages
end

I invite (genuinely, without sarcasm) the ruby/rails gurus to rip it to
shreds. Is this a sensible approach?

I'm no guru, so I'll give the non-guru answer. If it works, it's sensible. :wink:

Seriously, if you wanted to make it a bit more db-agnostic (is that a goal?) you could use the .tables(), .columns() and .indexes() methods on ActiveRecord::Base.connection to get your list of those things. Of course if that's a goal, you'll want to switch over to add_index as well, instead of doing the raw sql thing.

Roy Pardee wrote:

I'm no guru, so I'll give the non-guru answer. If it works, it's
sensible. :wink:

Seriously, if you wanted to make it a bit more db-agnostic (is that a
goal?) you could use the .tables(), .columns() and .indexes() methods on
ActiveRecord::Base.connection to get your list of those things. Of
course if that's a goal, you'll want to switch over to add_index as
well, instead of doing the raw sql thing.

That sounds sensible :slight_smile: I can't see us ever switching from mysql but
that does seem much nicer. I didn't know about those, thanks!