From 5c08b1d37471eba19c16c6ede7eb2c155565d542 Mon Sep 17 00:00:00 2001
From: Ted Trask <ttrask01@yahoo.com>
Date: Mon, 4 Oct 2010 07:15:42 +0000
Subject: Restructure some selects to makes getting values slightly more
 efficient.

---
 vmail-model.lua | 21 ++++++++++-----------
 1 file changed, 10 insertions(+), 11 deletions(-)

diff --git a/vmail-model.lua b/vmail-model.lua
index 9954cf4..f8f3ae3 100644
--- a/vmail-model.lua
+++ b/vmail-model.lua
@@ -232,20 +232,19 @@ local getuserparams = function(username)
 		end
 	end
 	if retval.username and username then retval.username.value = username end
-	if validuser(username) then
+	local users = listusers(username)
+	if #users == 1 then
 		-- Get password from voicemail_prefs
 		sql = "SELECT password FROM voicemail_prefs"..generatewhereclause(username)
 		local password = getselectresponse(sql)
 		if retval["vm-password"] and password[1] then
 			retval["vm-password"].value = password[1].password
- 		end
+		end
 
-		-- Get the uid that corresponds to this username
-		sql = "SELECT uid FROM voicemail_users"..generatewhereclause(username)
-		local uid = getselectresponse(sql)
-		if #uid == 1 then
+		local uid = users[1].uid
+		if uid then
 			-- Get other parameters from voicemail_values
-			sql = "SELECT * FROM voicemail_values"..generatewhereclause(nil, nil, nil, uid[1].uid)
+			sql = "SELECT * FROM voicemail_values"..generatewhereclause(nil, nil, nil, 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
@@ -274,10 +273,10 @@ local setuserparams = function(userparams)
 		-- 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")
+		sql = "DELETE FROM voicemail_values"..generatewhereclause(nil, nil, nil, uid[1].uid).." AND nid IN (SELECT nid FROM voicemail_params)"
+		runsqlcommand(sql)
 		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.."'"
-				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).."')"
 					runsqlcommand(sql)
@@ -607,7 +606,7 @@ list_passwords = function(username)
 	local users = {}
         local res, err = pcall(function()
 		local connected = databaseconnect()
-		local sql = "select username, password from voicemail_prefs"..generatewhereclause(username)
+		local sql = "SELECT username, password FROM voicemail_prefs"..generatewhereclause(username)
 		users = getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
@@ -624,7 +623,7 @@ list_users = function()
         local res, err = pcall(function()
 		local connected = 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"
+		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)
-- 
cgit v1.2.3