diff options
-rw-r--r-- | vmail-model.lua | 142 |
1 files changed, 70 insertions, 72 deletions
diff --git a/vmail-model.lua b/vmail-model.lua index 9032dca..038703d 100644 --- a/vmail-model.lua +++ b/vmail-model.lua @@ -22,40 +22,38 @@ config.callback_url = config.callback_url or "sofia/gateway/asterlink.com/$1" local env local con -local voicemail_users_creation_script = { - "CREATE TABLE voicemail_users (uid INTEGER PRIMARY KEY, username VARCHAR(255) UNIQUE)", - "CREATE INDEX users_username_idx ON voicemail_users (username)", +local 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)", + }, + voicemail_values = { + "CREATE TABLE voicemail_values (uid INTEGER, nid INTEGER, value VARCHAR(255))", + "CREATE INDEX values_uid_nid_idx on voicemail_values (uid, nid)", + }, + voicemail_params = { + "CREATE TABLE voicemail_params (nid INTEGER PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255))", + "CREATE INDEX params_name_idx ON voicemail_params (name)", + "INSERT INTO voicemail_params VALUES(null, 'username', 'text', 'Extension', '', '')", + "INSERT INTO voicemail_params VALUES(null, 'firstname', 'text', 'User First Name', '', '')", + "INSERT INTO voicemail_params VALUES(null, 'lastname', 'text', 'User Last Name', '', '')", + "INSERT INTO voicemail_params VALUES(null, 'vm-password', 'text', 'Voicemail Password', '', '')", + "INSERT INTO voicemail_params VALUES(null, 'vm-password-confirm', 'text', 'Enter again to confirm', '', '')", + "INSERT INTO voicemail_params VALUES(null, 'vm-mailto', 'text', 'Email Address', 'Email a notification, including audio file if enabled', '')", + "INSERT INTO voicemail_params VALUES(null, 'vm-email-all-messages', 'boolean', 'Email Enable', '', 'false')", + "INSERT INTO voicemail_params VALUES(null, 'vm-attach-file', 'boolean', 'Attach voicemail to email', 'Option to attach audio file to email', 'false')", + "INSERT INTO voicemail_params VALUES(null, 'vm-keep-local-after-email', 'boolean', 'Keep voicemail after emailed', 'When disabled the message will be deleted from the voicemailbox after the notification email is sent. This allows receiving voicemail via email alone, rather than having the voicemail available from the Web interface or by telephone. CAUTION: Attach voicemail to email must be enabled, OTHERWISE YOUR MESSAGES WILL BE LOST FOREVER.', 'true')", + "INSERT INTO voicemail_params VALUES(null, 'vm-notify-mailto', 'text', 'Pager Email Address', 'Email a short notification', '')", + "INSERT INTO voicemail_params VALUES(null, 'vm-notify-email-all-messages', 'boolean', 'Pager Email Enable', '', 'false')", + "INSERT INTO voicemail_params VALUES(null, 'callmenumber', 'text', 'Call Me Number', '', '')", + }, + voicemail_folders = { + "CREATE TABLE voicemail_folders (in_folder VARCHAR(255) PRIMARY KEY, label VARCHAR(255))", + "INSERT INTO voicemail_folders VALUES('inbox', 'Inbox')", + }, + voicemail_prefs = {"CREATE TABLE voicemail_prefs (username VARCHAR(255), domain VARCHAR(255), name_path VARCHAR(255), greeting_path VARCHAR(255), password VARCHAR(255))"}, } -local voicemail_values_creation_script = { - "CREATE TABLE voicemail_values (uid INTEGER, nid INTEGER, value VARCHAR(255))", - "CREATE INDEX values_uid_nid_idx on voicemail_values (uid, nid)", -} - -local voicemail_params_creation_script = { - "CREATE TABLE voicemail_params (nid INTEGER PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255))", - "CREATE INDEX params_name_idx ON voicemail_params (name)", - "INSERT INTO voicemail_params VALUES(null, 'username', 'text', 'Extension', '', '')", - "INSERT INTO voicemail_params VALUES(null, 'firstname', 'text', 'User First Name', '', '')", - "INSERT INTO voicemail_params VALUES(null, 'lastname', 'text', 'User Last Name', '', '')", - "INSERT INTO voicemail_params VALUES(null, 'vm-password', 'text', 'Voicemail Password', '', '')", - "INSERT INTO voicemail_params VALUES(null, 'vm-password-confirm', 'text', 'Enter again to confirm', '', '')", - "INSERT INTO voicemail_params VALUES(null, 'vm-mailto', 'text', 'Email Address', 'Email a notification, including audio file if enabled', '')", - "INSERT INTO voicemail_params VALUES(null, 'vm-email-all-messages', 'boolean', 'Email Enable', '', 'false')", - "INSERT INTO voicemail_params VALUES(null, 'vm-attach-file', 'boolean', 'Attach voicemail to email', 'Option to attach audio file to email', 'false')", - "INSERT INTO voicemail_params VALUES(null, 'vm-keep-local-after-email', 'boolean', 'Keep voicemail after emailed', 'When disabled the message will be deleted from the voicemailbox after the notification email is sent. This allows receiving voicemail via email alone, rather than having the voicemail available from the Web interface or by telephone. CAUTION: Attach voicemail to email must be enabled, OTHERWISE YOUR MESSAGES WILL BE LOST FOREVER.', 'true')", - "INSERT INTO voicemail_params VALUES(null, 'vm-notify-mailto', 'text', 'Pager Email Address', 'Email a short notification', '')", - "INSERT INTO voicemail_params VALUES(null, 'vm-notify-email-all-messages', 'boolean', 'Pager Email Enable', '', 'false')", - "INSERT INTO voicemail_params VALUES(null, 'callmenumber', 'text', 'Call Me Number', '', '')", -} - -local voicemail_folders_creation_script = { - "CREATE TABLE voicemail_folders (in_folder VARCHAR(255) PRIMARY KEY, label VARCHAR(255))", - "INSERT INTO voicemail_folders VALUES('inbox', 'Inbox')", -} - -local voicemail_prefs_creation_script = {"CREATE TABLE voicemail_prefs (username VARCHAR(255), domain VARCHAR(255), name_path VARCHAR(255), greeting_path VARCHAR(255), password VARCHAR(255))"} - -- ################################################################################ -- LOCAL FUNCTIONS local function escape_quotes(str) @@ -127,34 +125,45 @@ local runscript = function(script) end end -local checktable = function(table) - local success = false - local errtxt - local res, err = pcall(function() - local sql = "SELECT * FROM "..table.." LIMIT 1" - local cur = assert (con:execute(sql)) - cur:close() - success = true - end) +runsqlcommand = function(sql) + local res, err = con:execute(sql) if not res and err then - errtxt = err + -- 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 - return success, errtxt end -local getselectresponse = function(sql) +getselectresponse = function(sql) local retval = {} - 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 + 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 - retval[#retval + 1] = tmp - row = cur:fetch (row, "a") end - cur:close() return retval end @@ -188,7 +197,6 @@ end -- These funtions access the new voicemail tables added for ACF local listfolders = function(foldername) - if not checktable("voicemail_folders") then runscript(voicemail_folders_creation_script) end local sql = "SELECT * FROM voicemail_folders" .. generatewhereclause(nil, nil, foldername).." ORDER BY label" return getselectresponse(sql) end @@ -198,7 +206,6 @@ local validfolder = function(foldername) end local listusers = function(username) - if not checktable("voicemail_users") then runscript(voicemail_users_creation_script) end local sql = "SELECT * FROM voicemail_users" .. generatewhereclause(username).." ORDER BY username" return getselectresponse(sql) end @@ -209,7 +216,6 @@ end local getuserparams = function(username) local retval = {} - if not checktable("voicemail_params") then runscript(voicemail_params_creation_script) end local sql = "SELECT * FROM voicemail_params" local params = getselectresponse(sql) local reverse_nids = {} @@ -241,7 +247,6 @@ local getuserparams = function(username) local uid = getselectresponse(sql) if #uid == 1 then -- Get other parameters from voicemail_values - if not checktable("voicemail_values") then runscript(voicemail_values_creation_script) end sql = "SELECT * FROM voicemail_values"..generatewhereclause(nil, nil, nil, uid[1].uid) local params = getselectresponse(sql) for i,param in ipairs(params) do @@ -262,7 +267,6 @@ local setuserparams = function(userparams) return false, "Invalid User" end local success = true - if not checktable("voicemail_params") then runscript(voicemail_params_creation_script) end local sql = "SELECT * FROM voicemail_params" local params = getselectresponse(sql) -- Get the uid that corresponds to this username @@ -270,23 +274,21 @@ local setuserparams = function(userparams) local uid = getselectresponse(sql) if #uid == 1 then -- There are a few params not to put in the voicemail_values table - if not checktable("voicemail_values") then runscript(voicemail_values_creation_script) end local ignoreparam = { username=true, ["vm-password"]=true, ["vm-password-confirm"]=true } con:execute("START TRANSACTION") for i,parm in ipairs(params) do if parm.name and not ignoreparam[parm.name] then sql = "DELETE FROM voicemail_values"..generatewhereclause(nil, nil, nil, uid[1].uid).." and nid='"..parm.nid.."'" - assert( con:execute(sql) ) + runsqlcommand(sql) 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('"..uid[1].uid.."', '"..parm.nid.."', '"..tostring(userparams[parm.name].value).."')" - assert( con:execute(sql) ) + runsqlcommand(sql) end end end end -- Set password to voicemail_prefs if userparams["vm-password"] and userparams["vm-password"].value and userparams["vm-password"].value ~= "" then - if not checktable("voicemail_prefs") then runscript(voicemail_prefs_creation_script) end sql = "SELECT password FROM voicemail_prefs"..generatewhereclause(userparams.username.value) local password = getselectresponse(sql) if #password > 0 then @@ -296,7 +298,7 @@ local setuserparams = function(userparams) -- insert sql = "INSERT INTO voicemail_prefs (username, domain, password) VALUES ('"..userparams.username.value.."', '"..config.domain.."', '"..userparams["vm-password"].value.."')" end - assert( con:execute(sql) ) + runsqlcommand(sql) end con:execute("COMMIT") return success @@ -409,7 +411,7 @@ delete_message = function(message, username) local tmp = getselectresponse(sql) if #tmp == #messages then sql = "DELETE FROM voicemail_msgs" .. generatewhereclause(username, messages) - assert (con:execute(sql)) + runsqlcommand(sql) for i,t in ipairs(tmp) do os.remove(t.file_path) end @@ -536,7 +538,7 @@ move_message = function(message, newfolder, username) if validfolder(newfolder) then for i,m in ipairs(mess) do local sql = "UPDATE voicemail_msgs SET in_folder='"..newfolder.."'" .. generatewhereclause(username, messages) - assert (con:execute(sql)) + runsqlcommand(sql) end if #mess == 1 then retval.value = "Moved message" @@ -607,7 +609,6 @@ list_passwords = function(username) local users = {} local res, err = pcall(function() local connected = databaseconnect() - if not checktable("voicemail_prefs") then runscript(voicemail_prefs_creation_script) end local sql = "select username, password from voicemail_prefs"..generatewhereclause(username) users = getselectresponse(sql) if connected then databasedisconnect() end @@ -624,9 +625,6 @@ list_users = function() local users = {} local res, err = pcall(function() local connected = databaseconnect() - if not checktable("voicemail_users") then runscript(voicemail_users_creation_script) end - if not checktable("voicemail_values") then runscript(voicemail_values_creation_script) end - if not checktable("voicemail_params") then runscript(voicemail_params_creation_script) end -- 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) @@ -658,10 +656,10 @@ delete_user = function(username) end -- Remove the user parameters sql = "DELETE FROM voicemail_values " .. generatewhereclause(nil, nil, nil, users[1].uid) - assert (con:execute(sql)) + runsqlcommand(sql) -- Remove the user sql = "DELETE FROM voicemail_users " .. generatewhereclause(nil, nil, nil, users[1].uid) - assert (con:execute(sql)) + runsqlcommand(sql) result = "Voicemail User Deleted" end if connected then databasedisconnect() end @@ -718,7 +716,7 @@ update_usersettings = function(usersettings, create) else if create then sql = "INSERT INTO voicemail_users VALUES(null, '"..escape(usersettings.value.username.value).."')" - assert (con:execute(sql)) + runsqlcommand(sql) end success,errtxt = setuserparams(usersettings.value) end |