Several changes to improve speed and efficiency

Added list_passwords function and implemented auth.read_entry to speed up the authenticator library.
Modified users, values, and params tables to use integer references, rather than varchar.
Made complicated query for list_users, rather than looping in lua. And added some indexes.
The changes to schemas breaks backwards compatibility.

1 files changed, 79 insertions, 56 deletions

diff --git a/vmail-model.lua b/vmail-model.lua
index 11b0597..6441406 100644
--- a/vmail-model.lua
+++ b/vmail-model.lua
@@ -23,30 +23,33 @@ local env
 local con
 
 local voicemail_users_creation_script = {
-	"CREATE TABLE voicemail_users (username VARCHAR(255))",
+	"CREATE TABLE voicemail_users (uid INTEGER PRIMARY KEY, username VARCHAR(255) UNIQUE)",
+	"CREATE INDEX users_username_idx ON voicemail_users (username)",
 }
 
 local voicemail_values_creation_script = {
-	"CREATE TABLE voicemail_values (username VARCHAR(255), name VARCHAR(255), value VARCHAR(255))",
+	"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 (name VARCHAR(255) primary key, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255))",
-	"INSERT INTO voicemail_params VALUES('username', 'text', 'Extension', '', '')",
-	"INSERT INTO voicemail_params VALUES('firstname', 'text', 'User First Name', '', '')",
-	"INSERT INTO voicemail_params VALUES('lastname', 'text', 'User Last Name', '', '')",
-	"INSERT INTO voicemail_params VALUES('vm-password', 'text', 'Voicemail Password', '', '')",
-	"INSERT INTO voicemail_params VALUES('vm-password-confirm', 'text', 'Enter again to confirm', '', '')",
-	"INSERT INTO voicemail_params VALUES('vm-mailto', 'text', 'Email Address', 'Email a notification, including audio file if enabled', '')",
-	"INSERT INTO voicemail_params VALUES('vm-email-all-messages', 'boolean', 'Email Enable', '', 'false')",
-	"INSERT INTO voicemail_params VALUES('vm-attach-file', 'boolean', 'Attach voicemail to email', 'Option to attach audio file to email', 'false')",
-	"INSERT INTO voicemail_params VALUES('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('vm-notify-mailto', 'text', 'Pager Email Address', 'Email a short notification', '')",
-	"INSERT INTO voicemail_params VALUES('vm-notify-email-all-messages', 'boolean', 'Pager Email Enable', '', 'false')",
+	"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')",
 }
 
 local voicemail_folders_creation_script = {
-	"CREATE TABLE voicemail_folders (in_folder VARCHAR(255) primary key, label VARCHAR(255))",
+	"CREATE TABLE voicemail_folders (in_folder VARCHAR(255) PRIMARY KEY, label VARCHAR(255))",
 	"INSERT INTO voicemail_folders VALUES('inbox', 'Inbox')",
 }
 
@@ -154,7 +157,7 @@ local getselectresponse = function(sql)
 	return retval
 end
 
-local generatewhereclause = function(username, message, foldername)
+local generatewhereclause = function(username, message, foldername, uid)
         local sql = ""
 	local where = {}
 	if username and username ~= "" then
@@ -172,6 +175,9 @@ local generatewhereclause = function(username, message, foldername)
 	if foldername and foldername ~= "" then
 		where[#where+1] = "in_folder = '"..escape(foldername).."'"
 	end
+	if uid and uid ~= "" then
+		where[#where+1] = "uid = '"..escape(uid).."'"
+	end
 	if #where > 0 then
 		sql = " WHERE " .. table.concat(where, " AND ")
 	end
@@ -205,8 +211,10 @@ local getuserparams = function(username)
 	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 = {}
 	for i,parm in ipairs(params) do
 		if parm.name then
+			reverse_nids[parm.nid] = parm.name
 			retval[parm.name] = {}
 			for n,v in pairs(parm) do
 				retval[parm.name][n] = v
@@ -227,16 +235,21 @@ local getuserparams = function(username)
 			end
 		end
 
-		-- Get other parameters from voicemail_values
-		if not checktable("voicemail_values") then runscript(voicemail_values_creation_script) end
-		sql = "SELECT * FROM voicemail_values"..generatewhereclause(username)
-		local params = getselectresponse(sql)
-		for i,param in ipairs(params) do
-			if param.name and retval[param.name] and param.value then
-				if retval[param.name].type == "boolean" then
-					param.value = (param.value == "true")
+		-- Get the uid that corresponds to this username
+		sql = "SELECT uid FROM voicemail_users"..generatewhereclause(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
+				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
+						param.value = (param.value == "true")
+					end
+					retval[reverse_nids[param.nid]].value = param.value
 				end
-				retval[param.name].value = param.value
 			end
 		end
 	end
@@ -251,17 +264,22 @@ local setuserparams = function(userparams)
 	if not checktable("voicemail_params") then runscript(voicemail_params_creation_script) end
 	local sql = "SELECT * FROM voicemail_params"
 	local params = getselectresponse(sql)
-	-- 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(userparams.username.value).." and name='"..parm.name.."'"
-			assert( con:execute(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('"..userparams.username.value.."', '"..parm.name.."', '"..tostring(userparams[parm.name].value).."')"
+	-- Get the uid that corresponds to this username
+	sql = "SELECT uid FROM voicemail_users"..generatewhereclause(userparams.username.value)
+	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) )
+				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) )
+				end
 			end
 		end
 	end
@@ -583,29 +601,34 @@ list_folders = function()
 	return cfe({ type="structure", value=folders, label="Voicemail Folders", errtxt=errtxt })
 end
 
+list_passwords = function(username)
+	local errtxt
+	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
+	end)
+	if not res and err then
+		errtxt = err
+	end
+
+	return cfe({ type="structure", value=users, label="Voicemail User Passwords", errtxt=errtxt })
+end
+
 list_users = function()
 	local errtxt
 	local users = {}
         local res, err = pcall(function()
 		local connected = databaseconnect()
-		users = listusers()
-		-- Go in reverse order to remove the temporary users used for e-mailing messages
-		for i=#users,1,-1 do
-			u = users[i]
-			-- Remove the temporary users
-			if string.find(u.username, "^tempuser") then
-				table.remove(users, i)
-			else
-				local sql = "SELECT * FROM voicemail_values"..generatewhereclause(u.username).." and (name='firstname' or name='lastname')"
-				local cur = con:execute(sql)
-				local row = cur:fetch ({}, "a")
-				while row do
-					u[row.name] = row.value
-					row = cur:fetch (row, "a")
-				end
-				cur:close()
-			end
-		end
+		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)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
@@ -633,10 +656,10 @@ delete_user = function(username)
 				end
 			end
 			-- Remove the user parameters
-			sql = "DELETE FROM voicemail_values " .. generatewhereclause(username)
+			sql = "DELETE FROM voicemail_values " .. generatewhereclause(nil, nil, nil, users[1].uid)
 			assert (con:execute(sql))
 			-- Remove the user
-			sql = "DELETE FROM voicemail_users " .. generatewhereclause(username)
+			sql = "DELETE FROM voicemail_users " .. generatewhereclause(nil, nil, nil, users[1].uid)
 			assert (con:execute(sql))
 			result = "Voicemail User Deleted"
 		end
@@ -693,7 +716,7 @@ update_usersettings = function(usersettings, create)
 				errtxt = "User does not exist"
 			else
 				if create then
-					sql = "INSERT INTO voicemail_users VALUES('"..escape(usersettings.value.username.value).."')"
+					sql = "INSERT INTO voicemail_users VALUES(null, '"..escape(usersettings.value.username.value).."')"
 					assert (con:execute(sql))
 				end
 				success,errtxt = setuserparams(usersettings.value)