Re: Names for database access functions

[tomas <tomas@...>]

Hi Geoff

> Can anyone suggest a better naming convention?  I ask because I’ve
> written myself a yet-another database-adapter on top of the other
> database adapters [2] to make them look the same, and (to my eyes at
> least) slightly more pleasant.
I'm not sure if you checked Dado [6] which is also a "yet-another 
database-adapter on top of" LuaSQL :-)  Maybe I'm too used to it, but it 
seems quite simple to reproduce your constructions:

for first, last in db:select("first, last", "people") do print(first, 
last) end

or

for array in db:select("first, last", "people", nil, nil, "n") do 
print(array[1], array[2]) end

or

for table in db:select("first, last", "people", nil, nil, "a") do 
print(table.first, table.last) end

Note that the first nil is usually replaced by a condition-string and 
the second nil is sometimes used to complement the statement ("group 
by... order by...").  Dado also offers a variation:

result = db:selectall("first, last", "people")
for i = 1, #result do
   local row = result[i]
   print(row.first, row.last)
end

The first form is used all the time; the second form almost never, but I 
think it has its niche; the third form is used mainly in functions that 
abstract the database access to the rest of the application.  I 
particularly like this kind of function:

function user_data (db, id, mode)
   local fields = "u.name, u.number, u.email, u.code, t.description" -- 
usually this list is bigger
   local tabs = "user u inner join type t using(id_type)" -- usually 
there are more joins
   if not mode then
      mode = 'a'
   elseif mode ~= 'a' and mode ~= 'n' then
      fields = mode:gsub ("(%w+)", "u.%1")
      tabs = "user"
      mode = nil
   end
   return db:select (fields, tabs, "u.id_user="..sql.quote(id), nil, 
mode)()
end

You could use to get all data:

user = user_data (db, id)

Or you could get only what you need:

name = user_data (db, id, "name")

This way you don't have to retrieve more data than needed...

Regards,
Tomás

> [1] lsqlite3[3].  luapqsql[4] and luasql[5] offer primitives that allow 
> you to build your own iterators
> [2] https://github.com/geoffleyland/lua-patengi
> [3] https://github.com/LuaDist/lsqlite3
> [4] https://github.com/mbalmer/luapgsql
> [5] 
> https://keplerproject.github.io/luasql/doc/us/manual.html#introduction
[6] http://www.ccpa.puc-rio.br/software/dado/