Re: [Discuss] mod_lua and database connectivity
On 01/06/2013 07:21 PM, Daniel Gruno wrote:
> On 01/06/2013 04:23 PM, Stefan Fritsch wrote:
>> On Sun, 6 Jan 2013, Daniel Gruno wrote:
> -- Regular prepared statement creation:
> local prepped, err = db:prepare("SELECT * FROM `tbl` WHERE `id` = %s")
> if not err then
> local result, err = prepped("foobar") -- insert foobar as id
> ...
> end
>
> -- Fetch from DBDPrepareSQL:
> local prepped, err = db:prepared("someTag")
> if not err then
> local result, err = prepped("foobar") -- insert foobar as id
> ...
> end
>
One last change before I commit the code; prepared statements now have
two functions identical to the database object; select and query. These
were formerly known as query and run in the db object, but I have
renamed them to comply with the naming conventions of apr_dbd, thus
'query' is for running a command and fetching the no. of rows affected,
and 'select' is for...selecting :)
So, prepared statements are now run as follows:
-- select stuff
local prepared, err = db:prepare("SELECT * FROM `tbl` WHERE `age` > %u")
if not err then
local result, err = prepared:select(1234)
end
-- run stuff
local prepared, err = db:prepare("DELETE FROM `tbl` WHERE `age` > %u")
if not err then
local result, err = prepared:query(1234)
end
With regards,
Daniel.
Re: [Discuss] mod_lua and database connectivity
On 01/06/2013 04:23 PM, Stefan Fritsch wrote:
> On Sun, 6 Jan 2013, Daniel Gruno wrote:
>> 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 :)
>
> I am a bit late in the discussion, but maybe you also want to support
> using statements that have been prepared with DBDPrepareSQL from mod_dbd?
>
> Other important things (but I think these have already been pointed out):
>
> - don't let lua scripts open connections, instead make them aquire
> connections from mod_dbd's connection pool.
>
> - use parametrized prepared statements in all examples and make that as
> easy to use as possible. Don't show any examples that put parameters
> directly into the statement strings.
First off, I should mention that mod_dbd is indeed supported in the
proposal, in fact, I use only mod_dbd for my own Lua stuff.
As per our discussion on IRC, I have made some additions to the prepared
statements, so that statements prepared using DBDPrepareSQL can also be
fetched using db:prepared(r, tag). Thus prepared statements can be used
in two ways:
-- Regular prepared statement creation:
local prepped, err = db:prepare("SELECT * FROM `tbl` WHERE `id` = %s")
if not err then
local result, err = prepped("foobar") -- insert foobar as id
...
end
-- Fetch from DBDPrepareSQL:
local prepped, err = db:prepared("someTag")
if not err then
local result, err = prepped("foobar") -- insert foobar as id
...
end
This and the rest is discussed in my draft for the docs changes, which
can be found at:
http://www.humbedooh.com/apache/mod_lua.html.en#databases
I hope you guys will review it and let me know if you think it's time to
actually commit the code ;)
With regards,
Daniel.
Re: [Discuss] mod_lua and database connectivity
On Sun, 6 Jan 2013, Daniel Gruno wrote: 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 :) I am a bit late in the discussion, but maybe you also want to support using statements that have been prepared with DBDPrepareSQL from mod_dbd? Other important things (but I think these have already been pointed out): - don't let lua scripts open connections, instead make them aquire connections from mod_dbd's connection pool. - use parametrized prepared statements in all examples and make that as easy to use as possible. Don't show any examples that put parameters directly into the statement strings.
Re: [Discuss] mod_lua and database connectivity
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.
Re: [Discuss] mod_lua and database connectivity
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
Re: [Discuss] mod_lua and database connectivity
On 01/05/2013 10:49 AM, Igor Galić wrote:
>
>> Implied semantics matter a LOT in API design.
>
> +1
>
>>>
>>> Check your errors :-)
>> I don't need to check errors, I just need to check whether 'rows' is
>> a
>> table or a nil value in the case of an error. I could've checked if
>> 'err' was anything, but the result is the same.
>>
>> An API which returns (foo, err) should be error checked on the error,
>> not the foo. This style of API will sometimes return a foo in a bad
>> state, and an err to let you know. In your example this is fine, I
>> am just being a pedant because if an API is designed a certain way,
>> we should use it that way :-)
>>
>
> My take away is that we want mod_dbd bindings in mod_lua, but we
> want the API to be more carefully crafted.
>
> i
>
My attempt at humour seems to have failed, so I'll be more to the point.
Yes, calling it acquire rather than open makes sense, and it's a good
suggestion that I have applied to my draft.
Regarding the return values of 'acquire' (dbacquire, as it's currently
called), this, as well as the query/run (or should it be called
select/query? I'm not sure, but running db:select("SELECT...") seems a
bit redundant in my mind) will never return a 'bad state', they will
return nil if an error occurred, followed by an error message. How one
chooses to check for an error, whether it be checking the nil value or
checking for an error message, the result will be the same. However, in
my documentation addition to mod_lua, I have consistently made the
examples check for an error message, so no need to worry there - I was
only being brief in my example snippets in the previous email, apologies.
Return values are as follows:
r:dbacquire(type, connstring): DB handle on success, nil + error on
failure (error is either can't connect, can't load driver or mod_dbd not
loaded)
db:query("SELECT "): Result set on success, nil + error on failure
(syntax error, permission issue or db handle not connected to backend)
db:run("DELETE "): Number of affected rows on success, nil + error
on failure (same errors as :query)
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
As usual, suggestions and comments are most welcome!
And as for the inject/prepare stuff, I'm working on a new approach to
that as well, will keep you guys posted.
With regards,
Daniel.
Re: [Discuss] mod_lua and database connectivity
> Implied semantics matter a LOT in API design. +1 > > > > Check your errors :-) > I don't need to check errors, I just need to check whether 'rows' is > a > table or a nil value in the case of an error. I could've checked if > 'err' was anything, but the result is the same. > > An API which returns (foo, err) should be error checked on the error, > not the foo. This style of API will sometimes return a foo in a bad > state, and an err to let you know. In your example this is fine, I > am just being a pedant because if an API is designed a certain way, > we should use it that way :-) > My take away is that we want mod_dbd bindings in mod_lua, but we want the API to be more carefully crafted. i -- Igor Galić Tel: +43 (0) 664 886 22 883 Mail: i.ga...@brainsware.org URL: http://brainsware.org/ GPG: 6880 4155 74BD FD7C B515 2EA5 4B1D 9E08 A097 C9AE
Re: [Discuss] mod_lua and database connectivity
On Thu, Jan 3, 2013 at 11:05 PM, Daniel Gruno wrote:
> On 01/04/2013 12:57 AM, Brian McCallister wrote:
> ...
> > Supporting luasql would be a big bonus, though I understand if goal is to
> > provide a "quick and dirty api which is backed by mod_dbd"
> Quick and dirty makes it sound so...dirty. But yes, essentially, the
> purpose is to provide very basic database features for those just
> getting started or those who either cannot be bothered (we know who you
> lot are!) or can't figure out how to use luasql or other external
> libraries. I'd want mod_lua to be something you can sit down and get
> started on without having to learn how to compile lua or install
> libraries, BUT if you wanted/needed the extra capabilities that fx
> luasql provides, you could simply switch. It is not in any way intended
> as a full replacement, rather as a 'starter kit' so people load the
> module and go 'okay, what can this mod_lua puppy do for me?" and not
> have to think about the sometimes cumbersome process of extending Lua's
> capabilities.
>
> I suppose I can boil my reasoning down to two major points:
> 1) We have apr_dbd capabilities in httpd, so why not support it in Lua?
> This would mean a much smaller foot print when using databases in
> mod_lua compared to loading an external library into every VM.
> 2) We'd want something that can connect through mod_dbd, which is
> unlikely to be supported by third party database libraries.
>
>
> > Shouldn't this be a method on the server representation, not the request
> > representation?
> There is no server handle, only a request handle given to mod_lua
> scripts, you should know that ;). The server handle is obtained through
> the request handle, so all is well and good here.
>
It is still a method on the server, not the request, even if you get passed
a request.
> > ...
> > Hmm, if db here represents a handle, it should prolly be paired with
> > acquire not open.
>
> Semantics ;) but sure, we could call it 'acquire' instead of 'open'.
>
Implied semantics matter a LOT in API design.
> >
> > Check your errors :-)
> I don't need to check errors, I just need to check whether 'rows' is a
> table or a nil value in the case of an error. I could've checked if
> 'err' was anything, but the result is the same.
>
An API which returns (foo, err) should be error checked on the error, not
the foo. This style of API will sometimes return a foo in a bad state, and
an err to let you know. In your example this is fine, I am just being a
pedant because if an API is designed a certain way, we shoul duse it that
way :-)
> >
> > Also, be careful what you return, you don't want to the API to force you
> > to realize all results from a query eagerly.
> Currently, it works that way - it fetches everything at once, which I
> grant can be something you may or may not want. I am considering adding
> a metatable to the 'rows' object with an __index hook that fetches new
> rows only when needed. Good call! I will hold off on this for a while
> though, as there are some memory pool concerns. Imagine if one runs a
> query and uses the request pool for fetching data, then waits till
> another request comes through before iterating through the rows, that
> would cause an error, unless the query was stored in the global server
> pool, in which case it could not get released (and then I'd have to,
> instead, use another pool for allocating memory, which would have to be
> tied to the db object - so much to do!). The global pool option is what
> lua-apr does with everything, which is not something I'd want
> mod_lua to do.
>
>
> > -- Run a prepared statement and inject values into it:
> > local rows, err = db:inject(r, "SELECT `name` FROM `tbl` WHERE `id` =
> > %u", 1234)
> >
> >
> >
> > Hmm, I would expect an API like
> >
> > local pstmt, err = h:prepare("...")
> > ... = pstmt:execute("hello", 7")
> > -- or ... = pstmt:query("hello", 7")
> >
> > or such style api. "Injecting" into implicit prepared statement is a
> > strange api.
> >
>
> Not necessarily. If the injection function stores a key/value pair of
> injected statements and prepares them beforehand, then a subsequent call
> using the same statement would just look up whether that statement has
> already been prepared, and there would be no need to recompile. Though,
> I do see the benefits of instead associating a prepared statement with a
> tag of your own, so I will definitely consider this as well :)
>
> Thanks for your feedback, it's very much appreciated!
>
> With regards,
> Daniel.
>
>
Re: [Discuss] mod_lua and database connectivity
On 01/04/2013 12:57 AM, Brian McCallister wrote:
...
> Supporting luasql would be a big bonus, though I understand if goal is to
> provide a "quick and dirty api which is backed by mod_dbd"
Quick and dirty makes it sound so...dirty. But yes, essentially, the
purpose is to provide very basic database features for those just
getting started or those who either cannot be bothered (we know who you
lot are!) or can't figure out how to use luasql or other external
libraries. I'd want mod_lua to be something you can sit down and get
started on without having to learn how to compile lua or install
libraries, BUT if you wanted/needed the extra capabilities that fx
luasql provides, you could simply switch. It is not in any way intended
as a full replacement, rather as a 'starter kit' so people load the
module and go 'okay, what can this mod_lua puppy do for me?" and not
have to think about the sometimes cumbersome process of extending Lua's
capabilities.
I suppose I can boil my reasoning down to two major points:
1) We have apr_dbd capabilities in httpd, so why not support it in Lua?
This would mean a much smaller foot print when using databases in
mod_lua compared to loading an external library into every VM.
2) We'd want something that can connect through mod_dbd, which is
unlikely to be supported by third party database libraries.
> Shouldn't this be a method on the server representation, not the request
> representation?
There is no server handle, only a request handle given to mod_lua
scripts, you should know that ;). The server handle is obtained through
the request handle, so all is well and good here.
> ...
> Hmm, if db here represents a handle, it should prolly be paired with
> acquire not open.
Semantics ;) but sure, we could call it 'acquire' instead of 'open'.
>
> Check your errors :-)
I don't need to check errors, I just need to check whether 'rows' is a
table or a nil value in the case of an error. I could've checked if
'err' was anything, but the result is the same.
>
> Also, be careful what you return, you don't want to the API to force you
> to realize all results from a query eagerly.
Currently, it works that way - it fetches everything at once, which I
grant can be something you may or may not want. I am considering adding
a metatable to the 'rows' object with an __index hook that fetches new
rows only when needed. Good call! I will hold off on this for a while
though, as there are some memory pool concerns. Imagine if one runs a
query and uses the request pool for fetching data, then waits till
another request comes through before iterating through the rows, that
would cause an error, unless the query was stored in the global server
pool, in which case it could not get released (and then I'd have to,
instead, use another pool for allocating memory, which would have to be
tied to the db object - so much to do!). The global pool option is what
lua-apr does with everything, which is not something I'd want
mod_lua to do.
> -- Run a prepared statement and inject values into it:
> local rows, err = db:inject(r, "SELECT `name` FROM `tbl` WHERE `id` =
> %u", 1234)
>
>
>
> Hmm, I would expect an API like
>
> local pstmt, err = h:prepare("...")
> ... = pstmt:execute("hello", 7")
> -- or ... = pstmt:query("hello", 7")
>
> or such style api. "Injecting" into implicit prepared statement is a
> strange api.
>
Not necessarily. If the injection function stores a key/value pair of
injected statements and prepares them beforehand, then a subsequent call
using the same statement would just look up whether that statement has
already been prepared, and there would be no need to recompile. Though,
I do see the benefits of instead associating a prepared statement with a
tag of your own, so I will definitely consider this as well :)
Thanks for your feedback, it's very much appreciated!
With regards,
Daniel.
Re: [Discuss] mod_lua and database connectivity
Maybe you need this: https://github.com/zhaozg/mod_luaex
Example on http://kkhub.com
2013/1/4 Brian McCallister
> Supporting luasql would be a big bonus, though I understand if goal is to
> provide a "quick and dirty api which is backed by mod_dbd"
>
> http://www.keplerproject.org/luasql/manual.html
>
> -Brian
>
>
> On Thu, Jan 3, 2013 at 4:57 PM, Brian McCallister wrote:
>
>> On Thu, Jan 3, 2013 at 5:32 AM, Daniel Gruno wrote:
>>
>>> -
>>> Connecting to a database:
>>> -
>>> function handler(r)
>>> local db, error = r:dbopen("mod_dbd") -- Open a mod_dbd connection
>>>
>>
>> Shouldn't this be a method on the server representation, not the request
>> representation?
>>
>>
>>> if error then ... end
>>> -- or...
>>> local db, error = r:dbopen("mysql",
>>> "server=localhost,user=root,database=somedb")
>>> -- essentially the same as mod_dbd's connection string.
>>> do_stuff()
>>> db:close() -- close the db handle (can also be done by GC)
>>>
>>
>> Hmm, if db here represents a handle, it should prolly be paired with
>> acquire not open.
>>
>>
>>> local still_running = db:active() -- returns false, since we closed
>>> -- the connection.
>>> end
>>>
>>> -
>>> Querying:
>>> -
>>> -- Run a command and get the no. of rows affected:
>>> local affected, err = db:do(r, "DELETE FROM `table` WHERE 1")
>>> if err then
>>> print("DB error: " .. err)
>>> else
>>> print("Deleted " .. affected .. " rows!")
>>> end
>>>
>>> -- Run a query and get the rows returned:
>>> local rows, err = db:query(r, "SELECT `name` FROM `table` WHERE 1")
>>>
>>
>> Check your errors :-)
>>
>> Also, be careful what you return, you don't want to the API to force you
>> to realize all results from a query eagerly.
>>
>>
>>> if rows then
>>> r:puts("We got " .. #rows " .. " results!")
>>> for k, row in pairs(rows) do
>>> print("Name: " .. row[1] .. "")
>>> end
>>> else
>>> r:puts("DB error: " .. err)
>>> end
>>>
>>> -- Run a prepared statement and inject values into it:
>>> local rows, err = db:inject(r, "SELECT `name` FROM `tbl` WHERE `id` =
>>> %u", 1234)
>>>
>>
>>
>> Hmm, I would expect an API like
>>
>> local pstmt, err = h:prepare("...")
>> ... = pstmt:execute("hello", 7")
>> -- or ... = pstmt:query("hello", 7")
>>
>> or such style api. "Injecting" into implicit prepared statement is a
>> strange api.
>>
>>
>>> if rows then
>>>
>>> else
>>>
>>> end
>>>
>>>
>>> --
>>> Miscellaneous:
>>> --
>>>
>>> -- escaping strings for use in db queries:
>>> local escaped = db:escape(r, [[foo"bar'&|baz]])
>>>
>>>
>>> So, any comments, suggestions, remarks, objections and so on is very
>>> much welcomed. If there are no big objections to implementing this
>>> feature, I will consider it as lazy consensus and commit the bindings to
>>> trunk sometime soon along with updated documentation.
>>>
>>> With regards,
>>> Daniel.
>>>
>>> PS: I _have_ checked and double checked the code properly this time, so
>>> it conforms to the style requirements and works with maintainer mode. I
>>> know I usually get something wrong, but this time I think it's as close
>>> to perfect as it can get :) (but then again, I always write something
>>> bad, so apologies in advance if you find a bug)
>>>
>>
>>
>
Re: [Discuss] mod_lua and database connectivity
Supporting luasql would be a big bonus, though I understand if goal is to
provide a "quick and dirty api which is backed by mod_dbd"
http://www.keplerproject.org/luasql/manual.html
-Brian
On Thu, Jan 3, 2013 at 4:57 PM, Brian McCallister wrote:
> On Thu, Jan 3, 2013 at 5:32 AM, Daniel Gruno wrote:
>
>> -
>> Connecting to a database:
>> -
>> function handler(r)
>> local db, error = r:dbopen("mod_dbd") -- Open a mod_dbd connection
>>
>
> Shouldn't this be a method on the server representation, not the request
> representation?
>
>
>> if error then ... end
>> -- or...
>> local db, error = r:dbopen("mysql",
>> "server=localhost,user=root,database=somedb")
>> -- essentially the same as mod_dbd's connection string.
>> do_stuff()
>> db:close() -- close the db handle (can also be done by GC)
>>
>
> Hmm, if db here represents a handle, it should prolly be paired with
> acquire not open.
>
>
>> local still_running = db:active() -- returns false, since we closed
>> -- the connection.
>> end
>>
>> -
>> Querying:
>> -
>> -- Run a command and get the no. of rows affected:
>> local affected, err = db:do(r, "DELETE FROM `table` WHERE 1")
>> if err then
>> print("DB error: " .. err)
>> else
>> print("Deleted " .. affected .. " rows!")
>> end
>>
>> -- Run a query and get the rows returned:
>> local rows, err = db:query(r, "SELECT `name` FROM `table` WHERE 1")
>>
>
> Check your errors :-)
>
> Also, be careful what you return, you don't want to the API to force you
> to realize all results from a query eagerly.
>
>
>> if rows then
>> r:puts("We got " .. #rows " .. " results!")
>> for k, row in pairs(rows) do
>> print("Name: " .. row[1] .. "")
>> end
>> else
>> r:puts("DB error: " .. err)
>> end
>>
>> -- Run a prepared statement and inject values into it:
>> local rows, err = db:inject(r, "SELECT `name` FROM `tbl` WHERE `id` =
>> %u", 1234)
>>
>
>
> Hmm, I would expect an API like
>
> local pstmt, err = h:prepare("...")
> ... = pstmt:execute("hello", 7")
> -- or ... = pstmt:query("hello", 7")
>
> or such style api. "Injecting" into implicit prepared statement is a
> strange api.
>
>
>> if rows then
>>
>> else
>>
>> end
>>
>>
>> --
>> Miscellaneous:
>> --
>>
>> -- escaping strings for use in db queries:
>> local escaped = db:escape(r, [[foo"bar'&|baz]])
>>
>>
>> So, any comments, suggestions, remarks, objections and so on is very
>> much welcomed. If there are no big objections to implementing this
>> feature, I will consider it as lazy consensus and commit the bindings to
>> trunk sometime soon along with updated documentation.
>>
>> With regards,
>> Daniel.
>>
>> PS: I _have_ checked and double checked the code properly this time, so
>> it conforms to the style requirements and works with maintainer mode. I
>> know I usually get something wrong, but this time I think it's as close
>> to perfect as it can get :) (but then again, I always write something
>> bad, so apologies in advance if you find a bug)
>>
>
>
Re: [Discuss] mod_lua and database connectivity
On Thu, Jan 3, 2013 at 5:32 AM, Daniel Gruno wrote:
> -
> Connecting to a database:
> -
> function handler(r)
> local db, error = r:dbopen("mod_dbd") -- Open a mod_dbd connection
>
Shouldn't this be a method on the server representation, not the request
representation?
> if error then ... end
> -- or...
> local db, error = r:dbopen("mysql",
> "server=localhost,user=root,database=somedb")
> -- essentially the same as mod_dbd's connection string.
> do_stuff()
> db:close() -- close the db handle (can also be done by GC)
>
Hmm, if db here represents a handle, it should prolly be paired with
acquire not open.
> local still_running = db:active() -- returns false, since we closed
> -- the connection.
> end
>
> -
> Querying:
> -
> -- Run a command and get the no. of rows affected:
> local affected, err = db:do(r, "DELETE FROM `table` WHERE 1")
> if err then
> print("DB error: " .. err)
> else
> print("Deleted " .. affected .. " rows!")
> end
>
> -- Run a query and get the rows returned:
> local rows, err = db:query(r, "SELECT `name` FROM `table` WHERE 1")
>
Check your errors :-)
Also, be careful what you return, you don't want to the API to force you to
realize all results from a query eagerly.
> if rows then
> r:puts("We got " .. #rows " .. " results!")
> for k, row in pairs(rows) do
> print("Name: " .. row[1] .. "")
> end
> else
> r:puts("DB error: " .. err)
> end
>
> -- Run a prepared statement and inject values into it:
> local rows, err = db:inject(r, "SELECT `name` FROM `tbl` WHERE `id` =
> %u", 1234)
>
Hmm, I would expect an API like
local pstmt, err = h:prepare("...")
... = pstmt:execute("hello", 7")
-- or ... = pstmt:query("hello", 7")
or such style api. "Injecting" into implicit prepared statement is a
strange api.
> if rows then
>
> else
>
> end
>
>
> --
> Miscellaneous:
> --
>
> -- escaping strings for use in db queries:
> local escaped = db:escape(r, [[foo"bar'&|baz]])
>
>
> So, any comments, suggestions, remarks, objections and so on is very
> much welcomed. If there are no big objections to implementing this
> feature, I will consider it as lazy consensus and commit the bindings to
> trunk sometime soon along with updated documentation.
>
> With regards,
> Daniel.
>
> PS: I _have_ checked and double checked the code properly this time, so
> it conforms to the style requirements and works with maintainer mode. I
> know I usually get something wrong, but this time I think it's as close
> to perfect as it can get :) (but then again, I always write something
> bad, so apologies in advance if you find a bug)
>
[Discuss] mod_lua and database connectivity
Hello, fellow dev@ people, it's time for my monthly mod_lua rambling!
This time, I have set my eyes on creating bindings for the apr_dbd
features and mod_dbd in httpd. The purpose of this would be to both
enable people to easily use databases for Lua scripts, as well as lua
"modules" (hooks) in a way that both minimizes the need for external
libraries and takes advantage of mod_dbd if the module is loaded and in use.
While this could also be solved using an external library that was
required()'d in the Lua scripts, I feel that it would greatly boost the
usage and popularity of mod_lua if it were to be included as a core
feature. Otherwise, people would have to go look for external libraries,
which would both be redundant, since we already have db features inside
httpd, as well as make it more difficult to get things running with
mod_lua, as people would have to either resort to luarocks (which I find
to be quite lacking) or write/compile libraries themselves.
I do not wish to attempt to compare mod_lua to the likes of mod_php,
mod_python etc, but the ease of which these modules are hooked up with
databases of all sorts is perhaps one of the key elements in their
popularity, and I want mod_lua to be just as accessible for the _basic_
needs of a programmer/sysadmin (no, I do not want to bloat it, but I
want the core features that you need on a modern web site to be
available, and as such, db connectivity is a must)
The use-cases for this is many; Authenticating against a database with a
different approach than mod_auth_dbd, mass virtual hosting using cached
database lookups, web sites like our comments.a.o (which uses the exact
same Lua bindings as the one I am proposing, so it's been tested and is
working ;) ), my own paste bin site apaste.info (people from #httpd
probably know and use this one) and other nifty sorts of hooks and scripts.
Database objects will be assigned a metatable with a garbage collector
routine that automatically frees database handles when they are no
longer used, or they can be manually closed (which is preferred).
The bindings I propose can roughly be summarized with these example
snippets:
-
Connecting to a database:
-
function handler(r)
local db, error = r:dbopen("mod_dbd") -- Open a mod_dbd connection
if error then ... end
-- or...
local db, error = r:dbopen("mysql",
"server=localhost,user=root,database=somedb")
-- essentially the same as mod_dbd's connection string.
do_stuff()
db:close() -- close the db handle (can also be done by GC)
local still_running = db:active() -- returns false, since we closed
-- the connection.
end
-
Querying:
-
-- Run a command and get the no. of rows affected:
local affected, err = db:do(r, "DELETE FROM `table` WHERE 1")
if err then
print("DB error: " .. err)
else
print("Deleted " .. affected .. " rows!")
end
-- Run a query and get the rows returned:
local rows, err = db:query(r, "SELECT `name` FROM `table` WHERE 1")
if rows then
r:puts("We got " .. #rows " .. " results!")
for k, row in pairs(rows) do
print("Name: " .. row[1] .. "")
end
else
r:puts("DB error: " .. err)
end
-- Run a prepared statement and inject values into it:
local rows, err = db:inject(r, "SELECT `name` FROM `tbl` WHERE `id` =
%u", 1234)
if rows then
else
end
--
Miscellaneous:
--
-- escaping strings for use in db queries:
local escaped = db:escape(r, [[foo"bar'&|baz]])
So, any comments, suggestions, remarks, objections and so on is very
much welcomed. If there are no big objections to implementing this
feature, I will consider it as lazy consensus and commit the bindings to
trunk sometime soon along with updated documentation.
With regards,
Daniel.
PS: I _have_ checked and double checked the code properly this time, so
it conforms to the style requirements and works with maintainer mode. I
know I usually get something wrong, but this time I think it's as close
to perfect as it can get :) (but then again, I always write something
bad, so apologies in advance if you find a bug)
