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

[Adam Akhtar-2]

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
--
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

[Pardee, Roy]

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? ;-)

-----Original Message-----
From: rubyonrails-talk@... [mailto:rubyonrails-talk@...] On Behalf Of Max Williams
Sent: Wednesday, September 17, 2008 9:35 AM
To: rubyonrails-talk@...
Subject: [Rails] 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
--
Posted via http://www.ruby-forum.com/.



--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

[Frederick Cheung-2]

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

> I guess you could troll the files under /app/models...  But you were  
> looking to go *less* hacky, eh? ;-)
>
> -----Original Message-----
> From: rubyonrails-talk@... [mailto:rubyonrails-talk@...
> ] On Behalf Of Max Williams
> Sent: Wednesday, September 17, 2008 9:35 AM
> To: rubyonrails-talk@...
> Subject: [Rails] 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
> --
> Posted via http://www.ruby-forum.com/.
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

[Pardee, Roy]

Ach, of course.  Thanks Fred.

-----Original Message-----
From: rubyonrails-talk@... [mailto:rubyonrails-talk@...] On Behalf Of Frederick Cheung
Sent: Wednesday, September 17, 2008 11:18 AM
To: rubyonrails-talk@...
Subject: [Rails] Re: Get a list of all foreign keys (with their respective tables



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

> I guess you could troll the files under /app/models...  But you were
> looking to go *less* hacky, eh? ;-)
>
> -----Original Message-----
> From: rubyonrails-talk@...
> [mailto:rubyonrails-talk@...
> ] On Behalf Of Max Williams
> Sent: Wednesday, September 17, 2008 9:35 AM
> To: rubyonrails-talk@...
> Subject: [Rails] 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
> --
> Posted via http://www.ruby-forum.com/.
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

[Adam Akhtar-2]

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?
--
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

[Pardee, Roy]

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

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.

-----Original Message-----
From: rubyonrails-talk@... [mailto:rubyonrails-talk@...] On Behalf Of Max Williams
Sent: Thursday, September 18, 2008 1:33 AM
To: rubyonrails-talk@...
Subject: [Rails] Re: Get a list of all foreign keys (with their respective tables


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?
--
Posted via http://www.ruby-forum.com/.



--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

[Adam Akhtar-2]

Roy Pardee wrote:
> I'm no guru, so I'll give the non-guru answer.  If it works, it's
> sensible. ;-)
>
> 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 :)  I can't see us ever switching from mysql but
that does seem much nicer.  I didn't know about those, thanks!
--
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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 rubyonrails-talk@...
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---