Database class to works with parameters

native.lua

--
-- Native backend to FusionPBX database class
--

local log = require "resources.functions.log".database

assert(freeswitch, "Require FreeSWITCH environment")

local param_pattern = "%f[%a%d:][:]([%a][%a%d_]*)"

local NULL_PARAM = freeswitch.Dbh.NULL

local function next_placeholder(db, array, value)
  local n = #array + 1
  array[n] = value
  return (db:dbtype() == 'pgsql') and ('$' .. n) or '?'
end

local function apply_params(db, sql, params)
  params = params or {}

  local array = {}

  local err
  local str = string.gsub(sql, param_pattern, function(param)
    if err then return end

    local value = params[param]

    assert(value ~= nil, 'invalid value for parameter: `' .. param .. '`')

    local vtype = type(value)

    if "string"   == vtype then return next_placeholder(db, array, value) end
    if "number"   == vtype then return tostring(value)      end
    if "boolean"  == vtype then return value and '1' or '0' end
    if db.NULL    == value then
      if NULL_PARAM then return next_placeholder(db, array, NULL_PARAM) end
      return 'NULL'
    end
    if db.DEFAULT == value then return 'DEFAULT'            end

    err = 'invalid value for parameter: `' .. param .. '`'
  end)

  if err then return nil, err end

  return str, array
end

-----------------------------------------------------------
local FsDatabase = {} do

require "resources.functions.file_exists"
local json = require "resources.functions.lunajson"
FsDatabase.__index = FsDatabase
FsDatabase._backend_name = 'native'

function FsDatabase.new(name)
  local dbh = assert(name)
  local dbtype = 'sqlite'

  if type(name) == 'string' then
    if name == 'switch' and file_exists(database_dir.."/core.db") then
      dbh = freeswitch.Dbh("sqlite://"..database_dir.."/core.db")
    else
      local connection_string = assert(database[name])
      dbtype = string.match(connection_string, "^(.-)://")
      dbh = freeswitch.Dbh(connection_string)
    end
  end
  assert(dbh:connected())

  local self = setmetatable({
    _dbh = dbh;
    _dbtype = dbtype;
  }, FsDatabase)

  return self
end

function FsDatabase:query(sql, fn)
  if fn then
    return self._dbh:query(sql, fn)
  end
  return self._dbh:query(sql)
end

if NULL_PARAM or freeswitch.Dbh.SUPPORTS_PARAMS then

function FsDatabase:parameter_query(sql, params, fn)
  sql, params = apply_params(self, sql, params)
  if not sql then return nil, params end

  if fn then
    return self._dbh:query(sql, params, fn)
  end
  return self._dbh:query(sql, params)
end

end

function FsDatabase:affected_rows()
  if self._dbh then
    return self._dbh:affected_rows()
  end
end

function FsDatabase:release()
  if self._dbh then
    self._dbh:release()
    self._dbh = nil
  end
end

function FsDatabase:connected()
  return self._dbh and self._dbh:connected()
end

function FsDatabase:dbtype()
  return self._dbtype
end

end
-----------------------------------------------------------

return FsDatabase