diff options
author | Ted Trask <ttrask01@yahoo.com> | 2010-10-04 07:15:42 +0000 |
---|---|---|
committer | Ted Trask <ttrask01@yahoo.com> | 2010-10-04 07:15:42 +0000 |
commit | 5c08b1d37471eba19c16c6ede7eb2c155565d542 (patch) | |
tree | 278e8a7c8c2b814efec239daa82de1426af59eed /vmail-model.lua | |
parent | d854515fd349aa415a366eedf2aa96a0c75caad4 (diff) | |
download | acf-freeswitch-vmail-5c08b1d37471eba19c16c6ede7eb2c155565d542.tar.bz2 acf-freeswitch-vmail-5c08b1d37471eba19c16c6ede7eb2c155565d542.tar.xz |
Restructure some selects to makes getting values slightly more efficient.
Diffstat (limited to 'vmail-model.lua')
-rw-r--r-- | vmail-model.lua | 21 |
1 files 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) |