How to interface with databases?

The GNU database manager (gdbm) keeps a set of key-value bindings, like a persistent hash table. lgdbm provides a simple API for managing gdbm databases:

d=gdbm.open("test.gdbm","n")
d:insert("JAN","January")
d:insert("FEB","February")
d:insert("MAR","March")
d:insert("APR","April")
d:insert("MAY","May")
d:insert("JUN","June")
for k,v in d:entries() do
 print(k,v)
end d:close()

There is a fetch method for retrieving values, a replace method for updating entries, and a delete method for removing a key-value pair.

It is straightforward to put a more Lua-like facade on these basic operations:

local M = {
 __index=function (t,k) return t.gdbm:fetch(k) end,
 __newindex=function (t,k,v)
 if v then t.gdbm:replace(k,v) else t.gdbm:delete(k) end
 end
}
function gdbm.proxy(t)
 return setmetatable({gdbm=t},M)
end

Then things can be done in a more natural way:

t=d:proxy()
t.JUL="July"
t.AUG="August"
t.SEP="September"
t.OCT="October"
t.NOV="November"
t.DEC="December"
t.DEC="Dezembro" -- replace the entry t.SEP = nil -- delete the entry

Usually people interpret the word 'database' as meaning 'relational database'.

Here is an example of using Lua-Sqlite3:

require "sqlite3"
db = sqlite3.open("example-db.sqlite3")
db:exec[[ CREATE TABLE test (id, content) ]]
stmt = db:prepare[[ INSERT INTO test VALUES (:key, :value) ]]
stmt:bind{ key = 1, value = "Hello World" }:exec()
stmt:bind{ key = 2, value = "Hello Lua" }:exec()
stmt:bind{ key = 3, value = "Hello Sqlite3" }:exec()
for row in db:rows("SELECT * FROM test") do
 print(row.id, row.content)
end

The LuaSQL project provides back-ends for the popular RDBMS engines. A little more involved, but providing much more database engine support:

require "luasql.postgres"
env = assert (luasql.postgres())
con = assert (env:connect("luasql-test")) -- connect to data source res = con:execute"DROP TABLE people"
res = assert (con:execute[[
 CREATE TABLE people(
 name varchar(50),
 email varchar(50)
 )
]])
-- add a few elements list = {
 { name="Jose das Couves", email="jose@couves.com", },
 { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
 { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
 res = assert (con:execute(string.format([[
 INSERT INTO people
 VALUES ('%s', '%s')]], p.name, p.email)
 ))
end cur = assert (con:execute"SELECT name, email from people") -- retrieve a cursor row = cur:fetch ({}, "a") -- want to index by field name while row do
 print(string.format("Name: %s, E-mail: %s", row.name, row.email))
 row = cur:fetch (row, "a") -- reuse the table of results end cur:close()
con:close()
env:close()

How to interface with databases?

Back