Use acf.db library from acf-lib-0.6.0

1 files 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