From 5d8563ffe7ebfe596179baebc016ba7fa1905381 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Thu, 26 Sep 2013 13:29:41 +0000 Subject: Use acf.db library from acf-lib-0.6.0 --- kamailio-model.lua | 117 +++++++++++++++-------------------------------------- 1 file changed, 32 insertions(+), 85 deletions(-) diff --git a/kamailio-model.lua b/kamailio-model.lua index 9289e3d..2243145 100644 --- a/kamailio-model.lua +++ b/kamailio-model.lua @@ -6,6 +6,7 @@ require("modelfunctions") fs = require("acf.fs") format = require("acf.format") validator = require("acf.validator") +db = require("acf.db") -- Set variables local processname = "kamailio" @@ -13,9 +14,7 @@ local packagename = "kamailio" local baseurl = "/etc/kamailio" local kamctlrc_file = "/etc/kamailio/kamctlrc" -local env -local con -local DBENGINE +local dbkam -- ################################################################################ -- DATABASE FUNCTIONS @@ -28,14 +27,9 @@ local function assert (v, m) return v, m end --- Escape special characters in sql statements -local escape = function(sql) - sql = sql or "" - return con:escape(sql) -end - local databaseconnect = function() - if not con then + if not dbkam then + local engine -- parse the kamctlrc file local config = format.parse_ini_file(fs.read_file(kamctlrc_file), "") or {} if not config.DBENGINE then @@ -46,9 +40,7 @@ local databaseconnect = function() if config.DBENGINE == "MYSQL" or config.DBENGINE == "mysql" or config.DBENGINE == "MySQL" then error("MYSQL database not supported") elseif config.DBENGINE == "PGSQL" or config.DBENGINE == "pgsql" or config.DBENGINE == "postgres" or config.DBENGINE == "postgresql" or config.DBENGINE == "POSTGRESQL" then - require("luasql.postgres") - env = assert (luasql.postgres()) - DBENGINE = "PGSQL" + engine = db.engine.postgresql elseif config.DBENGINE == "ORACLE" or config.DBENGINE == "oracle" or config.DBENGINE == "Oracle" then error("ORACLE database not supported") elseif config.DBENGINE == "DBTEXT" or config.DBENGINE == "dbtext" or config.DBENGINE == "textdb" then @@ -60,68 +52,23 @@ local databaseconnect = function() end -- connect to data source - con = assert(env:connect(config.DBNAME or "openser", config.DBRWUSER or "openser", config.DBRWPW or "openserrw", config.DBHOST, config.DBPORT)) - return true + dbkam = db.create(engine, config.DBNAME or "openser", config.DBRWUSER or "openser", config.DBRWPW or "openserrw", config.DBHOST, config.DBPORT) end - return false + return dbkam.databaseconnect() end local databasedisconnect = function() - if env then - env:close() - env = nil - end - if con then - con:close() - con = nil - end + return dbkam.databasedisconnect() end local runsqlcommand = function(sql) logevent(sql) - assert(con:execute(sql)) + return dbkam.runsqlcommand(sql) end local getselectresponse = function(sql) - local retval = {} logevent(sql) - local cur = assert (con:execute(sql)) - local row = cur:fetch ({}, "a") - while row do - local tmp = {} - for name,val in pairs(row) do - tmp[name] = val - end - retval[#retval + 1] = tmp - row = cur:fetch (row, "a") - end - cur:close() - return retval -end - -local listtables = function() - local result = {} - if DBENGINE == "PGSQL" then - local tab = getselectresponse("SELECT tablename FROM pg_tables WHERE tablename !~* 'pg_*' ORDER BY tablename ASC") - for i,t in ipairs(tab) do - result[#result+1] = t.tablename - end - else - -- untested - result = con:tables() - end - return result -end - -local listcolumns = function(table) - local result = {} - if DBENGINE == "PGSQL" then - local col = getselectresponse("SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '"..escape(table).."' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum") - for i,c in ipairs(col) do - result[#result+1] = c.field - end - end - return result + return dbkam.getselectresponse(sql) end -- ################################################################################ @@ -193,7 +140,7 @@ local function parse_db_show(table) local f local res, err = pcall(function() local connected = databaseconnect() - f = modelfunctions.run_executable({"kamctl", "db", "show", escape(table)}) + f = modelfunctions.run_executable({"kamctl", "db", "show", dbkam.escape(table)}) if connected then databasedisconnect() end end) if not res and err then @@ -251,7 +198,7 @@ function create_new_user(self, user) if success then local res, err = pcall(function() local connected = databaseconnect() - user.descr, user.errtxt = modelfunctions.run_executable({"kamctl", "add", escape(user.value.username.value), escape(user.value.password.value)}) + user.descr, user.errtxt = modelfunctions.run_executable({"kamctl", "add", dbkam.escape(user.value.username.value), dbkam.escape(user.value.password.value)}) if connected then databasedisconnect() end end) if not res and err then @@ -277,7 +224,7 @@ function delete_user(self, user) else local res, err = pcall(function() local connected = databaseconnect() - user.descr, user.errtxt = modelfunctions.run_executable({"kamctl", "rm", escape(user.value.username.value)}) + user.descr, user.errtxt = modelfunctions.run_executable({"kamctl", "rm", dbkam.escape(user.value.username.value)}) if connected then databasedisconnect() end end) if not res and err then @@ -307,7 +254,7 @@ function update_user(self, user) if success then local res, err = pcall(function() local connected = databaseconnect() - user.descr, user.errtxt = modelfunctions.run_executable({"kamctl", "passwd", escape(user.value.username.value), escape(user.value.password.value)}) + user.descr, user.errtxt = modelfunctions.run_executable({"kamctl", "passwd", dbkam.escape(user.value.username.value), dbkam.escape(user.value.password.value)}) if connected then databasedisconnect() end end) if not res and err then @@ -326,7 +273,7 @@ function list_tables() -- Get the devices from the DB local res, err = pcall(function() local connected = databaseconnect() - retval = listtables() + retval = dbkam.listtables() if connected then databasedisconnect() end end) if not res and err then @@ -345,15 +292,15 @@ function list_table_entries(table) -- Get the devices from the DB local res, err = pcall(function() local connected = databaseconnect() - local tables = listtables() + local tables = dbkam.listtables() retval.table.errtxt = "Table does not exist" errtxt = "Table does not exist" for i,t in ipairs(tables) do if t == table then retval.table.errtxt = nil errtxt = nil - retval.entries.value = getselectresponse("SELECT * FROM "..escape(table).." ORDER BY id ASC") or {} - retval.fields.value = listcolumns(table) or {} + retval.entries.value = getselectresponse("SELECT * FROM "..dbkam.escape(table).." ORDER BY id ASC") or {} + retval.fields.value = dbkam.listcolumns(table) or {} end end if connected then databasedisconnect() end @@ -372,7 +319,7 @@ function get_new_table_entry(self, clientdata) if clientdata.table and clientdata.table ~= "" then local res, err = pcall(function() local connected = databaseconnect() - local tables = listtables() + local tables = dbkam.listtables() for i,t in ipairs(tables) do if t == clientdata.table then retval.table.errtxt = nil @@ -381,7 +328,7 @@ function get_new_table_entry(self, clientdata) end end if not errtxt then - local fields = listcolumns(clientdata.table) + local fields = dbkam.listcolumns(clientdata.table) for i,f in ipairs(fields) do retval[f] = cfe({ label=f, seq=i }) end @@ -410,7 +357,7 @@ function get_table_entry(self, clientdata) retval.value.id.value = clientdata.id or "" retval.value.id.errtxt = "Entry does not exist" if clientdata.id and clientdata.id ~= "" then - local entry = getselectresponse("SELECT * FROM "..escape(clientdata.table).." WHERE id='"..escape(clientdata.id).."'") + local entry = getselectresponse("SELECT * FROM "..dbkam.escape(clientdata.table).." WHERE id='"..dbkam.escape(clientdata.id).."'") if entry and #entry > 0 then for n,v in pairs(entry[1]) do if retval.value[n] then retval.value[n].value = v end @@ -457,7 +404,7 @@ function update_table_entry(self, entry, action, create) if success then local res, err = pcall(function() local connected = databaseconnect() - local tables = listtables() + local tables = dbkam.listtables() success = false entry.value.table.errtxt = "Table does not exist" for i,t in ipairs(tables) do @@ -468,7 +415,7 @@ function update_table_entry(self, entry, action, create) end end if success and not create then - local sql = "SELECT * FROM "..escape(entry.value.table.value).." WHERE id='"..escape(entry.value.id.value).."'" + local sql = "SELECT * FROM "..dbkam.escape(entry.value.table.value).." WHERE id='"..dbkam.escape(entry.value.id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false @@ -481,14 +428,14 @@ function update_table_entry(self, entry, action, create) local values = {} for n,v in pairs(entry.value) do if n ~= "table" and n ~= "id" then - names[#names+1] = escape(n) - values[#values+1] = escape(v.value) + names[#names+1] = dbkam.escape(n) + values[#values+1] = dbkam.escape(v.value) end end if create then - sql = "INSERT INTO "..escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')" + sql = "INSERT INTO "..dbkam.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')" else - sql = "UPDATE "..escape(entry.value.table.value).." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..escape(entry.value.id.value).."'" + sql = "UPDATE "..dbkam.escape(entry.value.table.value).." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..dbkam.escape(entry.value.id.value).."'" end runsqlcommand(sql) end @@ -526,7 +473,7 @@ function delete_table_entry(self, entry) local res, err = pcall(function() local connected = databaseconnect() entry.value.table.errtxt = "Invalid table" - local tables = listtables() + local tables = dbkam.listtables() for i,t in ipairs(tables) do if t == entry.value.table.value then entry.value.table.errtxt = nil @@ -534,7 +481,7 @@ function delete_table_entry(self, entry) end end if not entry.value.table.errtxt then - local sql = "DELETE FROM "..escape(entry.value.table.value).." WHERE id='"..escape(entry.value.id.value).."'" + local sql = "DELETE FROM "..dbkam.escape(entry.value.table.value).." WHERE id='"..dbkam.escape(entry.value.id.value).."'" runsqlcommand(sql) entry.errtxt = nil end @@ -565,9 +512,9 @@ function search_database(id, value, comparison) retval.value = cfe({label="Value", value=value or "", descr="Value or SQL regular expression", seq=3}) local res, err = pcall(function() local connected = databaseconnect() - local tables = listtables() or {} + local tables = dbkam.listtables() or {} for i,t in ipairs(tables) do - local columns = listcolumns(t) or {} + local columns = dbkam.listcolumns(t) or {} for i,c in ipairs(columns) do retval.id.option[#retval.id.option + 1] = t.."."..c end @@ -577,7 +524,7 @@ function search_database(id, value, comparison) retval.result = cfe({type="structure", value={}, label="List of Rows", seq=4 }) local table, column = string.match(id, "^([^.]*)%.(.*)") if table then - local sql = "SELECT * FROM "..escape(table).." WHERE "..escape(column)..escape(comparison).."'"..escape(value).."'" + local sql = "SELECT * FROM "..dbkam.escape(table).." WHERE "..dbkam.escape(column)..dbkam.escape(comparison).."'"..dbkam.escape(value).."'" retval.result.value = getselectresponse(sql) end end -- cgit v1.2.3