Names for database access functions

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Names for database access functions

Geoff Leyland
Hi,

When you access a database, you might want to receive results from a query as a multiple return value, an array, or as a table with column names.  For example:

    for first, last in db:rows“SELECT first, last FROM people” do print(first, last) end

or

    for array in db:rows“SELECT first, last FROM people” do print(array[1], array[2]) end

or

    for table in db:rows“SELECT first, last FROM people” do print(table.first, table.last) end

I find that I use the first and last example, but not so much the middle, array, form (but that’s just me).

What should each of these functions be called?  The database binding I use frequently that actually offers a “rows" iterator [1] calls them:
 - “rows” for the array case that I don’t really use
 - “nrows” for the table case (I guess that stands for “named rows”?)
 - “urows” for the multiple return case (I’m not sure what the u stands for)

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.

Cheers,
Geoff

[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


Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Brigham Toskin
I might call them rows (hashed tables), and rowvals or values or something similar (multi returns). I probably wouldn't ever use the array indexed style, unless it was actually meaningful to the data. And in that case, it would probably be a list packed into single column, so it would look more like:

   for t in db:rows("...") do print(table.unpack(t.phonenumbers)) end

But that feels pretty janky anyway, and I'd probably slap my DBA for doing something like that.

On Wed, Jun 17, 2015 at 2:43 PM, Geoff Leyland <[hidden email]> wrote:
Hi,

When you access a database, you might want to receive results from a query as a multiple return value, an array, or as a table with column names.  For example:

    for first, last in db:rows“SELECT first, last FROM people” do print(first, last) end

or

    for array in db:rows“SELECT first, last FROM people” do print(array[1], array[2]) end

or

    for table in db:rows“SELECT first, last FROM people” do print(table.first, table.last) end

I find that I use the first and last example, but not so much the middle, array, form (but that’s just me).

What should each of these functions be called?  The database binding I use frequently that actually offers a “rows" iterator [1] calls them:
 - “rows” for the array case that I don’t really use
 - “nrows” for the table case (I guess that stands for “named rows”?)
 - “urows” for the multiple return case (I’m not sure what the u stands for)

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.

Cheers,
Geoff

[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





--
Brigham Toskin
Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Coda Highland
In reply to this post by Geoff Leyland
On Wed, Jun 17, 2015 at 2:43 PM, Geoff Leyland
<[hidden email]> wrote:

> Hi,
>
> When you access a database, you might want to receive results from a query as a multiple return value, an array, or as a table with column names.  For example:
>
>     for first, last in db:rows“SELECT first, last FROM people” do print(first, last) end
>
> or
>
>     for array in db:rows“SELECT first, last FROM people” do print(array[1], array[2]) end
>
> or
>
>     for table in db:rows“SELECT first, last FROM people” do print(table.first, table.last) end
>
> I find that I use the first and last example, but not so much the middle, array, form (but that’s just me).
>
> What should each of these functions be called?  The database binding I use frequently that actually offers a “rows" iterator [1] calls them:
>  - “rows” for the array case that I don’t really use
>  - “nrows” for the table case (I guess that stands for “named rows”?)
>  - “urows” for the multiple return case (I’m not sure what the u stands for)
>
> 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.
>
> Cheers,
> Geoff
>
> [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
>

I would guess the u stands for "unpacked" since that's the operation
that would transform a row stored in an array into multiple returns.

Personally I would offer rows() as the array version and then extend
that with adapters, such as rows():asTable() or rows():asValues().

/s/ Adam

Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Geoff Leyland
On 18/06/2015, at 2:19 pm, Coda Highland <[hidden email]> wrote:
>
> I would guess the u stands for "unpacked" since that's the operation
> that would transform a row stored in an array into multiple returns.

That makes sense.  Thanks.

> Personally I would offer rows() as the array version and then extend
> that with adapters, such as rows():asTable() or rows():asValues().

Depending on the API you get from the database, you can build asTable and asValues without an intermediate table. In the case of asValues, this means avoiding unpack, which is a little slow, and you can return values from the database without creating any tables at all, which can help if there’s a lot of rows.

But the naming convention you suggest isn’t bad.  I’d be inclined to mark the array one too.
Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Coda Highland
On Wed, Jun 17, 2015 at 9:01 PM, Geoff Leyland
<[hidden email]> wrote:

> On 18/06/2015, at 2:19 pm, Coda Highland <[hidden email]> wrote:
>>
>> I would guess the u stands for "unpacked" since that's the operation
>> that would transform a row stored in an array into multiple returns.
>
> That makes sense.  Thanks.
>
>> Personally I would offer rows() as the array version and then extend
>> that with adapters, such as rows():asTable() or rows():asValues().
>
> Depending on the API you get from the database, you can build asTable and asValues without an intermediate table. In the case of asValues, this means avoiding unpack, which is a little slow, and you can return values from the database without creating any tables at all, which can help if there’s a lot of rows.
>
> But the naming convention you suggest isn’t bad.  I’d be inclined to mark the array one too.

If you're going to mark one for arrays too, then instead of calling it
rows() you could call it results() or something, such that you don't
actually invoke the database API until you've selected the appropriate
output form.

/s/ Adam

Reply | Threaded
Open this post in threaded view
|

RE: Names for database access functions

Thijs Schreijer
In reply to this post by Brigham Toskin


> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On
> Behalf Of Brigham Toskin
> Sent: donderdag 18 juni 2015 1:25
> To: Lua mailing list
> Subject: Re: Names for database access functions
>
> I might call them rows (hashed tables), and rowvals or values or something
> similar (multi returns). I probably wouldn't ever use the array indexed
> style, unless it was actually meaningful to the data. And in that case, it
> would probably be a list packed into single column, so it would look more
> like:
>
>    for t in db:rows("...") do print(table.unpack(t.phonenumbers)) end
>
> But that feels pretty janky anyway, and I'd probably slap my DBA for doing
> something like that.
>
> On Wed, Jun 17, 2015 at 2:43 PM, Geoff Leyland <[hidden email]>
> wrote:
> Hi,
>
> When you access a database, you might want to receive results from a query
> as a multiple return value, an array, or as a table with column names.  For
> example:
>
>     for first, last in db:rows“SELECT first, last FROM people” do
> print(first, last) end
>
> or
>
>     for array in db:rows“SELECT first, last FROM people” do print(array[1],
> array[2]) end
>
> or
>
>     for table in db:rows“SELECT first, last FROM people” do
> print(table.first, table.last) end
>
> I find that I use the first and last example, but not so much the middle,
> array, form (but that’s just me).
>
> What should each of these functions be called?  The database binding I use
> frequently that actually offers a “rows" iterator [1] calls them:
>  - “rows” for the array case that I don’t really use
>  - “nrows” for the table case (I guess that stands for “named rows”?)
>  - “urows” for the multiple return case (I’m not sure what the u stands for)
>
> 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.
>
> Cheers,
> Geoff
>
> [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
>
>
>
>
>
> --
> Brigham Toskin


@Brigham; in case nobody on this list has commented on your postings before, let me warn you. In general top-posting is considered a bad habit around here. I have seen others vigorously attack posters who did that, really nasty to witness as a by stander, bits and pieces all over the place.... Now I, for one, would never do such a thing of course, me being such a nice guy overall. As you also seem to be a pretty decent guy, I felt obliged to warn you...

:)

Thijs


Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Geoff Leyland
In reply to this post by Coda Highland
On 18/06/2015, at 4:51 pm, Coda Highland <[hidden email]> wrote:
>
> If you're going to mark one for arrays too, then instead of calling it
> rows() you could call it results() or something, such that you don't
> actually invoke the database API until you've selected the appropriate
> output form.

I quite like the idea results():values(), but I’m having trouble working out if it's practical.

First, by the time you call the existing rows(), you’re pretty committed to invoking the DB API, so there doesn’t seem much point putting it off.

Second, what does results() return?  Another object that’s really just there to make things look pretty?  It’s probably a good idea to avoid excess objects?

And then, there’s not just a row api, there’s a single (or no) result exec which just return values, not an iterator so there’s twice as many cases, and the sql can be an argument, as can arguments to the sql:

for name in db:results(“SELECT name FROM table WHERE id = :1”, 20):values() do…

I s’pose it could work?



Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Coda Highland
On Thu, Jun 18, 2015 at 1:28 AM, Geoff Leyland
<[hidden email]> wrote:

> On 18/06/2015, at 4:51 pm, Coda Highland <[hidden email]> wrote:
>>
>> If you're going to mark one for arrays too, then instead of calling it
>> rows() you could call it results() or something, such that you don't
>> actually invoke the database API until you've selected the appropriate
>> output form.
>
> I quite like the idea results():values(), but I’m having trouble working out if it's practical.
>
> First, by the time you call the existing rows(), you’re pretty committed to invoking the DB API, so there doesn’t seem much point putting it off.
>
> Second, what does results() return?  Another object that’s really just there to make things look pretty?  It’s probably a good idea to avoid excess objects?
>
> And then, there’s not just a row api, there’s a single (or no) result exec which just return values, not an iterator so there’s twice as many cases, and the sql can be an argument, as can arguments to the sql:
>
> for name in db:results(“SELECT name FROM table WHERE id = :1”, 20):values() do…
>
> I s’pose it could work?
>

Good point on results() being extraneous. db:query("..."):asValues()
is probably good enough.

/s/ Adam

Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Dirk Laurie-2
In reply to this post by Geoff Leyland
2015-06-17 23:43 GMT+02:00 Geoff Leyland <[hidden email]>:

> What should each of these functions be called?  The database binding
> I use frequently that actually offers a “rows" iterator [1] calls them:
>  - “rows” for the array case that I don’t really use
>  - “nrows” for the table case (I guess that stands for “named rows”?)
>  - “urows” for the multiple return case (I’m not sure what the u stands for)
>
> Can anyone suggest a better naming convention?

If the above naming convention is used by a commonly available,
well documented module, the decision has been taken out of your
hands. Anything else, despite equally good documentation on your
part, would just be confusing, no matter how much better it is.

Your darmstadtium is my ununnilium :-)

Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Tomás Guisasola-2
In reply to this post by Geoff Leyland
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/

Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Alexey Melnichuk-2
In reply to this post by Geoff Leyland
In my ODBC[1] binding I use

 local sql = 'select ID, NAME from Clients where NAME = :NAME'
 local params = {NAME='ALEX'}
 for ID,NAME in db:rows(sql,params) do print(ID,NAME) end
 for r in db:irows(sql,params) do print(r[1],r[2]) end
 for r in db:nrows(sql,params) do print(r.ID,r.NAME) end

`rows` is to mimic basic iterator
`irows` is just like `table.foreachi` (indexed?)
`nrows` is `named rows`

[1] http://moteus.github.io/dba/modules/Connection.html#Connection:rows

> Hi,

> When you access a database, you might want to receive results from
> a query as a multiple return value, an array, or as a table with column names.  For example:

>     for first, last in db:rows“SELECT first, last FROM people” do print(first, last) end

> or

>     for array in db:rows“SELECT first, last FROM people” do print(array[1], array[2]) end

> or

>     for table in db:rows“SELECT first, last FROM people” do
> print(table.first, table.last) end

> I find that I use the first and last example, but not so much the
> middle, array, form (but that’s just me).

> What should each of these functions be called?  The database
> binding I use frequently that actually offers a “rows" iterator [1] calls them:
>  - “rows” for the array case that I don’t really use
>  - “nrows” for the table case (I guess that stands for “named rows”?)
>  - “urows” for the multiple return case (I’m not sure what the u stands for)

> 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.

> Cheers,
> Geoff

> [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





--
С уважением,
 Alexey                          mailto:[hidden email]


---
Это сообщение проверено на вирусы антивирусом Avast.
https://www.avast.com/antivirus


Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Geoff Leyland
In reply to this post by Dirk Laurie-2
> On 18/06/2015, at 9:07 pm, Dirk Laurie <[hidden email]> wrote:
>
> If the above naming convention is used by a commonly available,
> well documented module, the decision has been taken out of your
> hands. Anything else, despite equally good documentation on your
> part, would just be confusing, no matter how much better it is.

Fair point Dirk, but I think you’re overestimating a few things:

 - the coherence of the current set of DB bindings for Lua (relevant XKCD[1])
 - the influence of my particular binding (it has two followers on github and 1 download on LuaRocks)

so I don’t think there’s much danger of a community being fractured by me choosing to not follow any of the available existing standards.


[1] https://xkcd.com/927/
Reply | Threaded
Open this post in threaded view
|

Re[2]: Names for database access functions

Roman Tsisyk
 Saturday, June 20, 2015 3:56 PM +12:00 from Geoff Leyland < [hidden email] >:
> > On 18/06/2015, at 9:07 pm, Dirk Laurie < [hidden email] > wrote:
>

I'm interested in having formal or informal standardization of Lua DB API as it was done, say, with Python DB-API.
You can joke about 15 competitive standards, but common, predictive API for all SQL database drivers are mandatory for high-speed software development.
We can form a working group to do that.

--
WBR,
   Roman Tsisyk < [hidden email] >
    http://tarantool.org/ - an efficient in-memory data store and a Lua application server
Reply | Threaded
Open this post in threaded view
|

Re: Names for database access functions

Marc Balmer
Am 20.06.15 um 09:49 schrieb Roman Tsisyk:
>  Saturday, June 20, 2015 3:56 PM +12:00 from Geoff Leyland < [hidden email] >:
>>> On 18/06/2015, at 9:07 pm, Dirk Laurie < [hidden email] > wrote:
>>
>
> I'm interested in having formal or informal standardization of Lua DB API as it was done, say, with Python DB-API.
> You can joke about 15 competitive standards, but common, predictive API for all SQL database drivers are mandatory for high-speed software development.
> We can form a working group to do that.

There is a problem with common DB APIs:  It is hard (or even
impossible?) to make use of advanced database features.  You risk to end
up with a DB layer that only covers functionality common to all
supported databases.  That will be very portable on one hand, but little
useful on the other hand.

With luapgsql e.g. I trade portability for completeness:  It is a
complete interface for _PostgreSQL users_.

I think "DB agnostic software" is one of the worst concepts ever...
(ymmv, of course).

- mb