Re: Result sets - what's the "Lua way"

[Drake Wilson <drake@...>]

Quoth "Henderson, Michael D" <michael.d.henderson@lmco.com>, on 2011-05-04 13:58:39 -0600:
> > I can't speak for Lua-in-general, of course, but roughly speaking,
> > I'd prefer something like:
> >
> >  Result set: integer keys -> rows;
> >              string keys -> various metadata if needed.
> >  Row: integer keys -> cell values in order (by column index);
> >       string keys -> cell values by column name.
> 
> Can you show how you'd like the code to look? That's what I'm trying
> to work out. I can code behind it but I'm not sure on how it should
> look.

You mean for the data consumer?

A hasty scribbling, so take with some salt:

For result sets that are loaded entirely into tables I'd want to be
able to do something like:

  results = assert(fetch_all(...))
  local columns = results.columns
  for i = 1, #results do
    local row = results[i]
    -- Or: for _, row in ipairs(results) do ...
    print("All columns: " .. table.concat(row, ', ')) -- Columns in sequence
    print("First one: " .. row[1])
    print("Named column X: " .. row.X)
    print("(Column X happens to be #"..(columns.X.position)..".)")
    for j = 1, #row do
      print("Individually, #"..j.." ("..columns[j].name..") is: "..row[j])
    end
  end
  -- Note the length operator returning valid results since the
  -- integer keys for each table form a dense array.  You could
  -- provide results.count == #results if it were convenient
  -- somewhere, but it doesn't matter much.

Alternatively, for lazy loading:

  handle = assert(open_cursor(...))
  for row in iterate_rows(handle)
    print("Row is about the same as above.")
    print("Column Z: " .. row.Z)
  end

Also consider:

  for a, b, c in unpacked_rows(handle)
    print("Note that this doesn't work if there's ever a legitimate "
          .."null in the first column.")
    print("Compare with some existing Lua SQL libraries.")
    print("The second column: " .. b)
  end

And possibly:

  -- Either the next row from a cursor, or a single-row query; the
  -- funcall wouldn't look the same for those, but the return values
  -- should.
  row = assert(fetch_one(...))
  err, a, b, c = fetch_unpacked(...)

   ---> Drake Wilson