Re: [Discuss] mod_lua and database connectivity

[Daniel Gruno]

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

[Daniel Gruno]

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

[Stefan Fritsch]

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

[Daniel Gruno]

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

[Igor Galić]

 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

[Daniel Gruno]

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

[Sean Conner]

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

[Brian McCallister]

On Thu, Jan 3, 2013 at 11:05 PM, Daniel Gruno rum...@cord.dk 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.

[Discuss] mod_lua and database connectivity

[Daniel Gruno]

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] .. br/)
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, [[foobar'|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

[Brian McCallister]

On Thu, Jan 3, 2013 at 5:32 AM, Daniel Gruno rum...@cord.dk 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] .. br/)
 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, [[foobar'|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

[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 bri...@skife.org wrote:

 On Thu, Jan 3, 2013 at 5:32 AM, Daniel Gruno rum...@cord.dk 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] .. br/)
 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, [[foobar'|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

[zhiguo zhao]

Maybe you need this: https://github.com/zhaozg/mod_luaex
Example on http://kkhub.com



2013/1/4 Brian McCallister bri...@skife.org

 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 bri...@skife.orgwrote:

 On Thu, Jan 3, 2013 at 5:32 AM, Daniel Gruno rum...@cord.dk 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] .. br/)
 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, [[foobar'|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

[Daniel Gruno]

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.