Postgres.lua

------------------------------------------------------------------------
--[[ Postgres ]]--
-- Simplified PostgreSQL database connection handler. 
-- Uses the ~/.pgpass file for authentication 
-- see (http://wiki.postgresql.org/wiki/Pgpass)
------------------------------------------------------------------------
local Postgres = torch.class("hypero.Postgres")

function Postgres:__init(config)
   config = config or {}
   local args, database, user, host, env, autocommit
      = xlua.unpack(
      {config},
      'Postgres', 'Default is to get the connection string from an ' ..
      'environment variable. For security reasons, and to allow ' .. 
      'for its serialization, no password is accepted. The password ' ..
      'should be set in the ~/.pgpass file.',
      {arg='database', type='string', help='name of postgres database'},
      {arg='user', type='string', help='username used to connect to db'},
      {arg='host', type='string', help='hostname/IP address of database'},
      {arg='env', type='string', default='HYPER_PG_CONN'},
      {arg='autocommit', type='boolean', default=true}
   )
   if not (database or user or host) then
      self.connStr = os.getenv(env)
      assert(self.connStr, "Environment variable HYPER_PG_CONN not set")
   else
      self.connStr = ""
      if database then self.connStr = self.connStr.."dbname="..database.." " end
      if user then self.connStr = self.connStr.."user="..user.." " end
      if host then self.connStr = self.connStr.."host="..host.." " end
   end
   local env = require('luasql.postgres'):postgres()
   self.conn = assert(env:connect(self.connStr))
   self.conn:setautocommit(autocommit)
   self.autocommit = autocommit
end
   
function Postgres:executeMany(command, param_list)
   local results, errs = {}, {}
   local result, err
   for i, params in pairs (param_list) do
      result, err = self:execute(command, params)
      table.insert(results, result)
      table.insert(errs, err)
   end
   return results, errs
end
	
function Postgres:execute(command, params)
   local result, err
   if params then
      params = torch.type(params) == 'table' and params or {params}
      result, err = self.conn:execute(string.format(command, unpack(params)))
   else
      result, err = self.conn:execute(command)
   end
   return result, err
end

--mode : 'n' returns rows as array, 'a' returns them as key-value
function Postgres:fetch(command, params, mode)
   mode = mode or 'n'
   local cur, err = self:execute(command, params)
   if cur then
      local coltypes = cur:getcoltypes()
      local colnames = cur:getcolnames()
      local row = cur:fetch({}, mode)
      local rows = {}
      while row do
        table.insert(rows, row)
        row = cur:fetch({}, mode)
      end
      cur:close()
      return rows, coltypes, colnames
   else
      return false, err
   end
end

function Postgres:fetchOne(command, params, mode)
   mode = mode or 'n'
   local cur, err = self:execute(command, params)
   if cur then
	   local coltypes = cur:getcoltypes()
	   local colname = cur:getcolnames()
	   local row = cur:fetch({}, mode)
	   cur:close()
      return row, coltypes, colnames
   else
      return false, err
   end
end

function Postgres:close()
   self.conn:close()
end

-- These two methods allow for (de)serialization of Postgres objects:
function Postgres:write(file)
   file:writeObject(self.connStr)
   file:writeObject(self.autocommit)
end

function Postgres:read(file, version)
   self.connStr = file:readObject()
   self.autocommit = file:readObject()
   local env = require('luasql.postgres'):postgres()
   self.conn = assert(env:connect(self.connStr))
end