On 01/06/2013 12:16 AM, Sean Conner wrote:
> It was thus said that the Great Daniel Gruno once stated:
>> With regards to how :query should work, this could either be done
>> synchronously, wherein all rows are fetched at once, or async where rows
>> are fetched as needed. The sync way is rather easy, but could fill up
>> more memory than needed, while the async has a smaller footprint but
>> proves rather difficult to implement, as the darn dbd driver keeps
>> wanting to free up the result set before it's finished being used
>> (apr_dbd_get_row keeps segfaulting when I request a row that is out of
>> bounds... :( ). Also,there is the consideration of what happens if you
>> query a db, get a result set, close the db handle and try to fetch more
>> rows - this would most likely result in a segfault, as the db handle
>> would have been freed when you try to use it again (how to check that?).
>> Also, getting the number of rows, or even doing: for k, v in pairs(rows)
>> ... proves to be quite difficult with the async method.
>>
>> What I've pieced together so far would work something like this:
>>
>> local results, err = db:query("SELECT .....")
>> it not err then
>> -- Async method here:
>> local x = 1
>> local row = results(x) -- fetch row 1
>> while row do
>> ....
>> x = x + 1
>> row = results(x) -- fetch row 2,3,4,5...N
>> end
>>
>> -- Sync method here:
>> local rows = results() -- fetch all rows at once
>> for index, row in pairs(rows) do
>> ....
>> end
>> end
>
> You could always create an interator for the results and hide the method
> (sync/async) behind it:
>
> function db:query(q)
> ...
> local function results_async()
> local function getnext(state) -- state is the db "object"
> return db:results_next(state)
> end
> return getnext,self
> end
>
> local function results_sync()
> local row = db:results_all(db)
> return pairs(row)
> end
>
> ...
>
> return results_async,err
> end
>
> local results, err = db:query("SELECT ... ")
> if not err then
> for row in results() do
> blah_de_blah_blah()
> end
> end
>
> Untested code and all that (so it may very be wrong, but the intent is
> there)
>
> -spc
>
I may have been a tad too hasty with my proposal there. I realized that
the async fetching can be made quite simple via apr_dbd:
local results, err = db:query("SELECT ....")
if not err then
while true do
row = results(-1) -- fetch the next row
if not row then break
do_stuff()
end
end
The example you provided could be used in the documentation to provide
methods to get a pairs iterator for both sync and async, so thanks for
that :) One could in fact construct a function that takes async as a
boolean parameter:
function rows(resultset, async)
local a = 0
local function getnext()
a = a + 1
local row = resultset(-1)
return row and a or nil, row
end
if not async then
return pairs(resultset(0))
else
return getnext, self
end
end
and then do:
-- async method:
for index, row in rows(resultset, true) do
...
end
-- sync method:
for index, row in rows(resultset, false) do -- or just rows(resultset)
....
end
Now I just have to take a good look at how to rewrite the prepared
statement stuff, and I believe I'll have a decent proposal ready for
commit :)
With regards,
Daniel.
