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

rubyonrails-talk
1

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

2

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:

3

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

4

Ach, of course. Thanks Fred.

5

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?

6

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.

7

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!