Re: Lua and Postgres

[Marc Balmer <marc@...>]

Steve

> At our local Postgres meeting tonight, I asked how to interface Lua with 
> Postgres, and the people there tried to get it set up. A quick googling 
> indicated it should look something like this.
> 
>                                Stored
> App     Iface       DB      Proc Language
> 
>                              .--PLLua
> Lua<--->LuaSQL<--->Postgres<-:
>                              `--plpgsql
> 
> We spent the whole night trying to install LuaSQL. LuaRocks got installed just 
> fine, but no matter what they did, they couldn't get LuaSQL installed from 
> LuaRocks or any other way.
> 
> I've promised to give a Lua/Postgres dog and pony show at the next Orlando 
> Postres Users Group meeting, so I need to know these things:
> 
> 1) Is LuaSQL the best way to interface between Lua and Postgres?

Not in my opinion.  LuaSQL adds a layer of abstraction on top of several
databases, to make you independent of the DB.  But afaik it allows you
do use DB specific extensions which then will make your application DB
specific again.

The road we went is to write our own PostgreSQL interface, because we
don't need portability, we just need a solid interface to PostgreSQL:

local res = db:exec("SELECT name FROM persons WHERE id = " .. id)
for n = 1, res:ntuples() do
  print(res:getvalue(n, 1)
end

You get the idea...  Currently we are trying to make use of PostgreSQL's
asynchronous notification feature, linking a notificatin to the
execution of a Lua function:

db:listen('AN_EVENT', function()
  print('Event AN_EVENT was signalled')
end)

Then when e.g. on a psql prompt you enter 'notify AN_EVENT;', above
function would be called.

This feature should be ready around FOSDEM, I hope....

And before you ask:  Our interface is not published anyware at the
moment....

marc

> 2) Is it necessary to go through LuaRocks? Is there a better way?
> 3) What is the procedure to install the Lua/Postgres interface?
> 
> Thanks
> 
> SteveT
> 
> Steve Litt
> Recession Relief Package
> http://www.recession-relief.US
> Twitter: http://www.twitter.com/stevelitt
> 
>