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

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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Max Williams
Sent: Wednesday, September 17, 2008 9:35 AM
To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> ] On Behalf Of Max Williams
> Sent: Wednesday, September 17, 2008 9:35 AM
> To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ach, of course.  Thanks Fred.

-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Frederick Cheung
Sent: Wednesday, September 17, 2008 11:18 AM
To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> [mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> ] On Behalf Of Max Williams
> Sent: Wednesday, September 17, 2008 9:35 AM
> To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Max Williams
Sent: Thursday, September 18, 2008 1:33 AM
To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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