From 99fe028c64ac14ca89e355ba2473bc36b52c91e3 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Wed, 9 Oct 2013 19:37:26 +0000 Subject: Use acf.db library from acf-lib-0.6.0 --- vmail-model.lua | 179 +++++++++++++++----------------------------------------- 1 file 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"] }) -- cgit v1.2.3