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

Rails
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 =
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

On 17 Sep 2008, at 19:00, Pardee, Roy wrote:

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 P. 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 P. 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!