Hi all, I wrote an ActiveRecord extension to emulate a db cursor: http://pastie.org/236367 As you can see, the spec doesn''t really ensure that the records are fetched in chunks. I''d like to mock the call to AR find and ensure that it''s called with an incrementing offset. Any advice? Keith PS If you''re interested, Jamis Buck wrote a (mySQL specific) DB cursor: http://weblog.jamisbuck.org/2007/4/6/faking-cursors-in-activerecord
On Fri, Jul 18, 2008 at 8:11 PM, Keith McDonnell <keith at dancingtext.com> wrote:> I wrote an ActiveRecord extension to emulate a db cursor: > http://pastie.org/236367 > As you can see, the spec doesn''t really ensure that the records are fetched > in chunks. I''d like to mock the call to AR find and ensure that it''s called > with an incrementing offset.I''m using cursors to walk a table with close to a million rows at times. I am just wrapping the action in a transaction and then declaring the cursor within the transaction and then walking the table with a find_by_sql with an SQL fetch command. It works well. Any reason you aren''t doing it this way? Could just call it with a block... Mikel -- http://lindsaar.net/ Rails, RSpec and Life blog....
Hi Mikel, Thanks for the feedback. Much appreciated. >I''m using cursors to walk a table with close to a million rows at times. > >I am just wrapping the action in a transaction and then declaring the >cursor within the transaction and then walking the table with a >find_by_sql with an SQL fetch command. > >It works well. > >Any reason you aren''t doing it this way? Could just call it with a block... I /could/ use an SQL cursor but: - I wanted to write a method that could be re-used by the other developers on the team. - our code base is polluted with SQL, ostensibly for "performance" reasons. I''m trying to refactor out unnecessary SQL. - raw speed is less important that ensuring that the production db doesn''t crash, ie it doesn''t matter if the updates take an hour or two once the live site doesn''t slow down considerably. Our max # rows is 3.3 million. I don''t know if these are valid reasons however :) Thanks for your suggested solution (transaction, cursor, find_by_sql & fetch). I''ll play around with it & see what I can do. Can''t immediately envision how to spec it though. Keith
On Mon, Jul 21, 2008 at 6:38 PM, Keith McDonnell <keith at dancingtext.com> wrote:> I /could/ use an SQL cursor but: > I don''t know if these are valid reasons however :)The only thing you gain with a direct SQL cursor is the guarantee of hitting each row exactly once without having to pull down all the IDs into an array first.> Thanks for your suggested solution (transaction, cursor, find_by_sql & > fetch). I''ll play around with it & see what I can do. Can''t immediately > envision how to spec it though.You''d stub out the model. class Record end -- http://lindsaar.net/ Rails, RSpec and Life blog....
Sorry, hit the tab and enter key too soon :) You''d stub out the model. class DummyModel acts_as_curseable end it "should ask the database to set up a transaction" do DummyModel.connection.should_receive("BEGIN;").once ... then the execution code to make that happen end it "should ask the database to set up a cursor" do DummyModel.connection.should_receive("DECLARE dummy_model_cursor CURSOR FOR...;").once ... then the execution code to make that happen end it "should tell the database to give it another X rows" do DummyModel.should_receive(:find_by_sql).with("FETCH 500 FROM dummy_model_cursor;") ... then the execution code to make that happen end Hope that sort of starts you off :) And if you like ''acts_as_curseable'' for the plugin name, I want acknowledgement :D -- http://lindsaar.net/ Rails, RSpec and Life blog....