Use acf.db library from acf-lib-0.6.0

1 files changed, 47 insertions, 132 deletions

diff --git a/vmail-model.lua b/vmail-model.lua
index b91b4eb..4be6fa9 100644
--- a/vmail-model.lua
+++ b/vmail-model.lua
@@ -6,7 +6,7 @@ require("posix")
 fs = require("acf.fs")
 format = require("acf.format")
 validator = require("acf.validator")
-require("luasql.sqlite3")
+db = require("acf.db")
 require("session")
 
 -- Set variables
@@ -21,10 +21,8 @@ config.event_socket_password = config.event_socket_password or "ClueCon"
 config.callback_command = config.callback_command or "originate {ignore_early_media=true,origination_caller_id_name='Voicemail',origination_caller_id_number='Voicemail'}sofia/gateway/asterlink.com/$1 &playback($2)"
 local recording_path = "/var/lib/freeswitch/voicemail/"
 
-local env
-local con
-
-local table_creation_scripts = {
+local vmaildb = db.create(db.engine.sqlite3, config.database)
+vmaildb.table_creation_scripts = {
 	voicemail_users = {
 		"CREATE TABLE voicemail_users (uid INTEGER PRIMARY KEY, username VARCHAR(255) UNIQUE)",
 		"CREATE INDEX users_username_idx ON voicemail_users (username)",
@@ -91,94 +89,11 @@ local function voicemail_read(user, domain, message)
 	return modelfunctions.run_executable({"nc", config.event_socket_ip, config.event_socket_port}, true, cmd)
 end
 
-local function assert (v, m)
-	if not v then
-		m = m or "Assertion failed!"
-		error(m, 0)
-	end
-	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
-		-- create environment object
-		env = assert (luasql.sqlite3())
-		-- connect to data source
-		con = assert (env:connect(config.database))
-		return true
-	end
-	return false
-end
-
 local databasedisconnect = function()
-	if env then
-		env:close()
-		env = nil
-	end
-	if con then
-		con:close()
-		con = nil
-	end
+	vmaildb.databasedisconnect()
 	posix.chown(config.database, posix.getpasswd("freeswitch", "uid") or 0, posix.getpasswd("freeswitch", "gid") or 0)
 end
 
-local runscript = function(script)
-	for i,scr in ipairs(script) do
-		logevent(scr)
-		assert( con:execute(scr) )
-	end
-end
-
-local runsqlcommand
-runsqlcommand = function(sql)
-        local res, err = con:execute(sql)
-	if not res and err then
-		-- Catch the error to see if it's caused by lack of table
-		local table = string.match(err, "LuaSQL: no such table: (%S+)")
-		if table and table_creation_scripts[table] then
-			runscript(table_creation_scripts[table])
-			runsqlcommand(sql)
-		else
-			assert(res, err)
-		end
-	end
-end
-
-local getselectresponse
-getselectresponse = function(sql)
-	local retval = {}
-        local res, err = pcall(function()
-		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()
-	end)
-	if not res and err then
-		-- Catch the error to see if it's caused by lack of table
-		local table = string.match(err, "LuaSQL: no such table: (%S+)")
-		if table and table_creation_scripts[table] then
-			runscript(table_creation_scripts[table])
-			return getselectresponse(sql)
-		else
-			assert(res, err)
-		end
-	end
-	return retval
-end
-
 local generatewhereclause = function(username, message, foldername, uid)
         local sql = ""
 	local where = {}
@@ -210,7 +125,7 @@ end
 
 local listfolders = function(foldername)
 	local sql = "SELECT * FROM voicemail_folders" .. generatewhereclause(nil, nil, foldername).." ORDER BY label"
-	return getselectresponse(sql)
+	return vmaildb.getselectresponse(sql)
 end
 
 local validfolder = function(foldername)
@@ -219,7 +134,7 @@ end
 
 local listusers = function(username)
 	local sql = "SELECT * FROM voicemail_users" .. generatewhereclause(username).." ORDER BY username"
-	return getselectresponse(sql)
+	return vmaildb.getselectresponse(sql)
 end
 
 local validuser = function(username)
@@ -229,7 +144,7 @@ end
 local getuserparams = function(username)
 	local retval = {}
 	local sql = "SELECT * FROM voicemail_params"
-	local params = getselectresponse(sql)
+	local params = vmaildb.getselectresponse(sql)
 	local reverse_nids = {}
 	for i,parm in ipairs(params) do
 		if parm.name then
@@ -251,7 +166,7 @@ local getuserparams = function(username)
 		if #users == 1 then
 			-- Get password from voicemail_prefs
 			sql = "SELECT password FROM voicemail_prefs"..generatewhereclause(username)
-			local password = getselectresponse(sql)
+			local password = vmaildb.getselectresponse(sql)
 			if retval["vm-password"] and password[1] then
 				retval["vm-password"].value = password[1].password
 			end
@@ -260,7 +175,7 @@ local getuserparams = function(username)
 			if uid then
 				-- Get other parameters from voicemail_values
 				sql = "SELECT * FROM voicemail_values"..generatewhereclause(nil, nil, nil, uid)
-				local params = getselectresponse(sql)
+				local params = vmaildb.getselectresponse(sql)
 				for i,param in ipairs(params) do
 					if param.nid and reverse_nids[param.nid] and retval[reverse_nids[param.nid]] and param.value then
 						if retval[reverse_nids[param.nid]].type == "boolean" then
@@ -281,21 +196,21 @@ local setuserparams = function(userparams)
 	end
 	local success = true
 	local sql = "SELECT * FROM voicemail_params"
-	local params = getselectresponse(sql)
+	local params = vmaildb.getselectresponse(sql)
 	-- Get the uid that corresponds to this username
 	sql = "SELECT uid FROM voicemail_users"..generatewhereclause(userparams.username.value)
-	local uid = getselectresponse(sql)
+	local uid = vmaildb.getselectresponse(sql)
 	if #uid == 1 then
 		-- There are a few params not to put in the voicemail_values table
 		local ignoreparam = { username=true, ["vm-password"]=true, ["vm-password-confirm"]=true }
-		con:execute("START TRANSACTION")
+		vmaildb.runsqlcommand("START TRANSACTION")
 		sql = "DELETE FROM voicemail_values"..generatewhereclause(nil, nil, nil, uid[1].uid).." AND nid IN (SELECT nid FROM voicemail_params)"
-		runsqlcommand(sql)
+		vmaildb.runsqlcommand(sql, true)
 		for i,parm in ipairs(params) do
 			if parm.name and not ignoreparam[parm.name] then
 				if userparams[parm.name] and (userparams[parm.name].value ~= nil) and tostring(userparams[parm.name].value) ~= parm.value then
 					sql = "INSERT INTO voicemail_values VALUES('"..escape(uid[1].uid).."', '"..escape(parm.nid).."', '"..escape(tostring(userparams[parm.name].value)).."')"
-					runsqlcommand(sql)
+					vmaildb.runsqlcommand(sql, true)
 				end
 			end
 		end
@@ -303,7 +218,7 @@ local setuserparams = function(userparams)
 	-- Set password to voicemail_prefs
 	if userparams["vm-password"] and userparams["vm-password"].value and userparams["vm-password"].value ~= "" then
 		sql = "SELECT password FROM voicemail_prefs"..generatewhereclause(userparams.username.value)
-		local password = getselectresponse(sql)
+		local password = vmaildb.getselectresponse(sql, true)
 		if #password > 0 then
 			-- update
 			sql = "UPDATE voicemail_prefs SET password='"..escape(userparams["vm-password"].value).."'"..generatewhereclause(userparams.username.value)
@@ -311,9 +226,9 @@ local setuserparams = function(userparams)
 			-- insert
 			sql = "INSERT INTO voicemail_prefs (username, domain, password) VALUES ('"..escape(userparams.username.value).."', '"..escape(config.domain).."', '"..escape(userparams["vm-password"].value).."')"
 		end
-		runsqlcommand(sql)
+		vmaildb.runsqlcommand(sql, true)
 	end
-	con:execute("COMMIT")
+	vmaildb.runsqlcommand("COMMIT")
 	return success
 end
 
@@ -351,10 +266,10 @@ local delete_message = function(messages, username)
 		
 	local sql = "SELECT * FROM voicemail_msgs"
 	sql = sql .. generatewhereclause(username, messages)
-	local tmp = getselectresponse(sql)
+	local tmp = vmaildb.getselectresponse(sql)
 	if #tmp == #messages then
 		sql = "DELETE FROM voicemail_msgs" .. generatewhereclause(username, messages)
-		runsqlcommand(sql)
+		vmaildb.runsqlcommand(sql)
 		for i,t in ipairs(tmp) do
 			os.remove(t.file_path)
 		end
@@ -393,13 +308,13 @@ local delete_user = function(username)
 		end
 		-- Remove the user parameters
 		local sql = "DELETE FROM voicemail_values " .. generatewhereclause(nil, nil, nil, users[1].uid)
-		runsqlcommand(sql)
+		vmaildb.runsqlcommand(sql)
 		-- Remove the user password
 		sql = "DELETE FROM voicemail_prefs " .. generatewhereclause(username)
-		runsqlcommand(sql)
+		vmaildb.runsqlcommand(sql)
 		-- Remove the user
 		sql = "DELETE FROM voicemail_users " .. generatewhereclause(nil, nil, nil, users[1].uid)
-		runsqlcommand(sql)
+		vmaildb.runsqlcommand(sql)
 		result = "Voicemail User Deleted"
 		-- Remove the greetings
 		fs.remove_directory(recording_path..(users[1].domain or config.domain).."/"..username)
@@ -441,11 +356,11 @@ list_messages = function(username)
 	local retval = {}
 	local errtxt
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		local sql = "SELECT * FROM voicemail_msgs"
 		sql = sql .. generatewhereclause(username)
 		sql = sql .. " ORDER BY username ASC, created_epoch ASC"
-		retval = getselectresponse(sql)
+		retval = vmaildb.getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
@@ -457,10 +372,10 @@ end
 get_message = function(message, username)
 	local retval = cfe({ type="raw", label="error", option="audio/x-wav" })
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		local sql = "SELECT username, file_path FROM voicemail_msgs"
 		sql = sql .. generatewhereclause(username, message)
-		local tmp = getselectresponse(sql)
+		local tmp = vmaildb.getselectresponse(sql)
 		if connected then databasedisconnect() end
 		if #tmp == 0 then
 			retval.errtxt = "Invalid message"
@@ -492,7 +407,7 @@ set_delete_message = function(self, deleterequest)
 	local messages = format.string_to_table(deleterequest.value.message.value, "%s*,%s*")
 
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		res, err = delete_message(messages, deleterequest.value.username.value)
 		if connected then databasedisconnect() end
 	end)
@@ -519,10 +434,10 @@ end
 forward_message = function(self, forwardrequest)
 	local messages = format.string_to_table(forwardrequest.value.message.value, "%s*,%s*")
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		-- Check if message exists
 		local sql = "SELECT * FROM voicemail_msgs" .. generatewhereclause(forwardrequest.value.username.value, messages)
-		local mess = getselectresponse(sql)
+		local mess = vmaildb.getselectresponse(sql)
 		if #mess == #messages then
 			-- Check if newuser exists
 			if validuser(forwardrequest.value.newuser.value) then
@@ -565,10 +480,10 @@ email_message = function(self, emailrequest)
 		emailrequest.errtxt = "Failed to e-mail message - invalid address"
 	else
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			local connected = vmaildb.databaseconnect()
 			-- Check if message exists
 			local sql = "SELECT * FROM voicemail_msgs" .. generatewhereclause(emailrequest.value.username.value, messages)
-			local mess = getselectresponse(sql)
+			local mess = vmaildb.getselectresponse(sql)
 			if #mess == #messages then
 				-- Create a temporary user and settings
 				local newuser = "tempuser"..session.random_hash(128)
@@ -627,16 +542,16 @@ end
 move_message = function(self, moverequest)
 	local messages = format.string_to_table(moverequest.value.message.value, "%s*,%s*")
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		-- Check if message exists
 		local sql = "SELECT * FROM voicemail_msgs" .. generatewhereclause(moverequest.value.username.value, messages)
-		local mess = getselectresponse(sql)
+		local mess = vmaildb.getselectresponse(sql)
 		if #mess == #messages then
 			-- Check if newfolder exists
 			if validfolder(moverequest.value.newfolder.value) then
 				for i,m in ipairs(mess) do
 					local sql = "UPDATE voicemail_msgs SET in_folder='"..escape(moverequest.value.newfolder.value).."'" .. generatewhereclause(moverequest.value.username.value, messages)
-					runsqlcommand(sql)
+					vmaildb.runsqlcommand(sql)
 				end
 				if #mess == 1 then
 					moverequest.descr = "Moved message"
@@ -680,10 +595,10 @@ callback_message = function(self, callbackrequest)
 		callbackrequest.errtxt = "Failed to callback message - invalid extension"
 	else
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			local connected = vmaildb.databaseconnect()
 			-- Check if message exists
 			local sql = "SELECT * FROM voicemail_msgs" .. generatewhereclause(callbackrequest.value.username.value, callbackrequest.value.message.value)
-			local mess = getselectresponse(sql)
+			local mess = vmaildb.getselectresponse(sql)
 			if #mess == 1 then
 				-- Mark the message as read
 				voicemail_read(mess[1].username, config.domain, callbackrequest.value.message.value)
@@ -707,7 +622,7 @@ list_folders = function()
 	local errtxt
 	local folders = {}
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		folders = listfolders()
 		if connected then databasedisconnect() end
 	end)
@@ -722,9 +637,9 @@ list_passwords = function(username)
 	local errtxt
 	local users = {}
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		local sql = "SELECT username, password FROM voicemail_prefs"..generatewhereclause(username)
-		users = getselectresponse(sql)
+		users = vmaildb.getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
@@ -738,10 +653,10 @@ list_users = function()
 	local errtxt
 	local users = {}
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		-- This crazy query gets the username from voicemail_users, the firstname and lastname from two instances of voicemail_values (using voicemail_params to determine the corresponding nid values) drops usernames starting with "tempuser" and ordering by username 
 		local sql = "SELECT u.username, v1.value lastname, v2.value firstname FROM voicemail_users u LEFT OUTER JOIN voicemail_values v1 ON u.uid = v1.uid AND v1.nid=(SELECT nid FROM voicemail_params WHERE name='lastname') LEFT OUTER JOIN voicemail_values v2 ON u.uid = v2.uid AND v2.nid=(SELECT nid FROM voicemail_params WHERE name='firstname') WHERE u.username NOT LIKE 'tempuser%' ORDER BY u.username"
-		users = getselectresponse(sql)
+		users = vmaildb.getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
@@ -766,7 +681,7 @@ set_delete_user = function(self, deleterequest)
 	end
 	if success then
 	       	local res, err = pcall(function()
-			local connected = databaseconnect()
+			local connected = vmaildb.databaseconnect()
 			success, deleterequest.errtxt = delete_user(deleterequest.value.username.value)
 			if connected then databasedisconnect() end
 		end)
@@ -784,7 +699,7 @@ get_usersettings = function(self, clientdata)
 	local retval = {}
 	local errtxt
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		retval = getuserparams(clientdata.username)
 		if connected then databasedisconnect() end
 	end)
@@ -814,7 +729,7 @@ update_usersettings = function(self, usersettings, action, create)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			local connected = vmaildb.databaseconnect()
 			local u = listusers(usersettings.value.username.value)
 			if create and #u > 0 then
 				success = false
@@ -825,7 +740,7 @@ update_usersettings = function(self, usersettings, action, create)
 			else
 				if create then
 					sql = "INSERT INTO voicemail_users VALUES(null, '"..escape(usersettings.value.username.value).."')"
-					runsqlcommand(sql)
+					vmaildb.runsqlcommand(sql)
 				end
 				success,errtxt = setuserparams(usersettings.value)
 			end
@@ -850,7 +765,7 @@ process_directory_xml_request = function(input)
 	local output = {}
 	local errtxt
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		if validuser(input.user) then
 			output = getuserparams(input.user)
 			-- Add the domain
@@ -870,7 +785,7 @@ process_dialplan_xml_request = function(input)
 	local output = {}
 	local errtxt
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		local connected = vmaildb.databaseconnect()
 		if validuser(input["Caller-Destination-Number"]) then
 			output.domain = cfe({ value=config.domain })
 			output.username = cfe({ value=input["Caller-Destination-Number"] })