summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--vmail-model.lua142
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