Brian Corrigan
2006-Mar-28 22:01 UTC
[Rails] Efficiently convert this SQL statement to ActiveRecord loop
Hey Folks, My app compares the last known set of files located on a series of servers against a current snapshot for auditing purposes. Its working really well, and I''ll open source it when its complete! Here''s my current issue (and forgive me, I started life as a DBA and I constantly want to revert to connection.execute.) The tables to store my objects are organized in this way: COMPUTER <- belongs_to / has_many => CONFIGRATIONS <= has_many / belongs_to -> ITEMS I have another table called SNAPSHOT that has: table.column :computername, :string table.column :dirname, :string table.column :filename, :string table.column :file_modified, :datetime Issues: * CONFIGURATIONS and SNAPSHOT both have about 500 millions rows The goal: * Add configurations based on the the snapshot that do not currently exists * Delete configurations that do not exists in the current snapshot * Update configuration that have a file_modified data newer than the one stored in the CONIGURATION object. * Observe all changes and log to an AUDIT table This is pretty easy in SQL, but hard in a non set based language.. Can anyone point me in the right direction? Can anyone point me in the right direction? Best, Brian Brian Corrigan Operations Manager MGAM Systems, Inc. 1 Broadway Place Schenectady, NY 12305 <http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=1+Broadway+Place&csz=Sch enectady%2C+NY+12305&country=us> brian.corrigan@mm-games.com <mailto:brian.corrigan@mm-games.com> tel: fax: mobile: 518-881-1121 518-881-1128 518-727-6652 -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060328/054aaac4/attachment-0001.html
Brian Corrigan
2006-Mar-28 22:46 UTC
[Rails] Efficiently convert this SQL statement to ActiveRecord loop
PS: I realize this can be done with a loop. But it will be VERY slow right? Brian Corrigan Operations Manager MGAM Systems, Inc. 1 Broadway Place Schenectady, NY 12305 <http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=1+Broadway+Place&csz=Sch enectady%2C+NY+12305&country=us> brian.corrigan@mm-games.com <mailto:brian.corrigan@mm-games.com> tel: fax: mobile: 518-881-1121 518-881-1128 518-727-6652 ________________________________ From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Brian Corrigan Sent: Tuesday, March 28, 2006 4:38 PM To: rails@lists.rubyonrails.org Subject: [Rails] Efficiently convert this SQL statement to ActiveRecord loop Hey Folks, My app compares the last known set of files located on a series of servers against a current snapshot for auditing purposes. Its working really well, and I''ll open source it when its complete! Here''s my current issue (and forgive me, I started life as a DBA and I constantly want to revert to connection.execute.) The tables to store my objects are organized in this way: COMPUTER <- belongs_to / has_many => CONFIGRATIONS <= has_many / belongs_to -> ITEMS I have another table called SNAPSHOT that has: table.column :computername, :string table.column :dirname, :string table.column :filename, :string table.column :file_modified, :datetime Issues: * CONFIGURATIONS and SNAPSHOT both have about 500 millions rows The goal: * Add configurations based on the the snapshot that do not currently exists * Delete configurations that do not exists in the current snapshot * Update configuration that have a file_modified data newer than the one stored in the CONIGURATION object. * Observe all changes and log to an AUDIT table This is pretty easy in SQL, but hard in a non set based language.. Can anyone point me in the right direction? Can anyone point me in the right direction? Best, Brian Brian Corrigan Operations Manager MGAM Systems, Inc. 1 Broadway Place Schenectady, NY 12305 <http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=1+Broadway+Place&csz=Sch enectady%2C+NY+12305&country=us> brian.corrigan@mm-games.com <mailto:brian.corrigan@mm-games.com> tel: fax: mobile: 518-881-1121 518-881-1128 518-727-6652 -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060328/301f4f4d/attachment-0001.html