From e75038abf8eb7715cf89c13d1595e314dd07c127 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Mon, 7 Dec 2015 15:46:52 +0000 Subject: Cleanup database access to always use transactions and properly close/rollback on exceptions Note: Scripts are now called within transactions, so they cannot contain transactions themselves --- provisioning-model.lua | 423 +++++++++++++++++++++++++++---------------------- 1 file changed, 237 insertions(+), 186 deletions(-) (limited to 'provisioning-model.lua') diff --git a/provisioning-model.lua b/provisioning-model.lua index ebec3da..0a86e97 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -36,15 +36,15 @@ local functions -- LOCAL FUNCTIONS -- This function is used by scripts, do not change prototype -local runsqlcommand = function(sql, transaction) +local runsqlcommand = function(sql) mymodule.logevent(sql) - return provdb.runsqlcommand(sql, transaction) + return provdb.runsqlcommand(sql, true) end -- This function is used by scripts, do not change prototype -local getselectresponse = function(sql, transaction) +local getselectresponse = function(sql) mymodule.logevent(sql) - return provdb.getselectresponse(sql, transaction) + return provdb.getselectresponse(sql, true) end local function assert (v, m) @@ -117,16 +117,42 @@ local databaseconnect = function() -- Let's create all the tables from the start for n,v in pairs(provdb.table_creation_scripts) do if not string.match(n, "^_") then - runsqlcommand("SELECT * FROM "..provdb.escape(n).." LIMIT 1") + provdb.runsqlcommand("SELECT * FROM "..provdb.escape(n).." LIMIT 1") end end else assert(res, err) end end + if result then + local sql = "BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE" + mymodule.logevent(sql) + provdb.runsqlcommand(sql) + end return result end +local databasedisconnect = function() + local sql = "COMMIT" + mymodule.logevent(sql) + provdb.runsqlcommand(sql) + provdb.databasedisconnect() +end + +local handlesqlexception = function(connected) + -- Can't trust cache, so clear it + saved_devices = nil + saved_device_params = nil + if connected then + local sql = "ROLLBACK" + mymodule.logevent(sql) + pcall(function() con:execute(sql) end) + provdb.databasedisconnect() + else + error(err) + end +end + local validateparam validateparam = function(p, allowdefault) if p.type == "group" then @@ -280,13 +306,14 @@ end -- This function is used by scripts, do not change prototype local get_device = function(device_id, create) + local connected local retval = {} retval.device_id = cfe({value=device_id or "", label="Device ID", seq=1}) retval.classes = cfe({type="group", value={}, label="Classes", seq=2}) local errtxt local res, err = pcall(function() local classes={} - local connected = databaseconnect() + connected = databaseconnect() if not create and device_id and device_id ~= "" then -- Get the device-to-class mappings local sql = "SELECT class_id FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."'" @@ -308,26 +335,28 @@ local get_device = function(device_id, create) class.value = c.class_id end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end -- Save the device for later use local output = cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt }) - if not create and device_id and device_id ~= "" then saved_devices[device_id] = duplicatestructure(output) end + if not create and device_id and device_id ~= "" and not errtxt then saved_devices[device_id] = duplicatestructure(output) end return output end -- This function is used by scripts, do not change prototype local get_device_params = function(device_id, editable) + local connected local retval = {} retval.device_id = cfe({value=device_id or "", label="Device ID", seq=0}) local errtxt = "Cannot find device" if device_id and device_id ~= "" then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- First, just check to see if device_id exists local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) @@ -382,9 +411,10 @@ local get_device_params = function(device_id, editable) -- Even more finally, add in a flag to show if group defaults have been overridden retval.groupdefaultoverride = cfe({ type="boolean", value=checkgroupdefaultoverride(device_id), label="Group defaults have been overridden", readonly=true }) end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end end @@ -396,13 +426,14 @@ local get_device_params = function(device_id, editable) end -- This function is used by scripts, do not change prototype -local set_device_params = function(params, editable, intransaction) +local set_device_params = function(params, editable) + local connected -- Validate the settings local success = validateparam(params) local errtxt if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() success = validateparamcoded(params) local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(params.value.device_id.value).."' LIMIT 1" local tmp = getselectresponse(sql) @@ -413,12 +444,11 @@ local set_device_params = function(params, editable, intransaction) if success then if not saved_device_params[params.value.device_id.value] then get_device_params(params.value.device_id.value) end - local sql = "BEGIN TRANSACTION" - if not intransaction then runsqlcommand(sql) end + local sql if not editable then -- Delete all values for this device (can't do this if only updating editable) sql = "DELETE FROM provisioning_values WHERE device_id='"..provdb.escape(params.value.device_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) end -- Loop through the groups and params for group,v in pairs(params.value) do @@ -426,18 +456,15 @@ local set_device_params = function(params, editable, intransaction) for name,param in pairs(v.value) do if editable then sql = "DELETE FROM provisioning_values WHERE device_id='"..provdb.escape(params.value.device_id.value).."' AND group_name='"..provdb.escape(group).."' AND param_id='"..provdb.escape(param.param_id).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) end if param.value ~= param.default then sql = "INSERT INTO provisioning_values VALUES('"..provdb.escape(params.value.device_id.value).."', '"..provdb.escape(group).."', '"..provdb.escape(param.param_id).."', '"..provdb.escape(tostring(param.value)).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) end end end end - - sql = "COMMIT" - if not intransaction then runsqlcommand(sql) end local tmp = saved_device_params[params.value.device_id.value] local p = params @@ -448,10 +475,10 @@ local set_device_params = function(params, editable, intransaction) end callscript(updatedeviceparamsscriptfile, p, tmp) end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -463,11 +490,12 @@ local set_device_params = function(params, editable, intransaction) end local get_device_values = function(device_id) + local connected local retval = {} local errtxt if device_id and device_id ~= "" then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT g.name AS group, p.name, p.type, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value ".. "FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) JOIN provisioning_groups g USING(group_id) ".. "JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) ".. @@ -483,9 +511,10 @@ local get_device_values = function(device_id) end retval[p.group][p.name] = p.value end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end else @@ -497,6 +526,7 @@ end -- These are the functions that may be called from within loaded Lua code -- The functions must be added after they're declared +-- All scripts are called within DB transactions functions = { getselectresponse=getselectresponse, runsqlcommand=runsqlcommand, @@ -510,16 +540,18 @@ functions = { -- PUBLIC FUNCTIONS mymodule.list_templates = function() + local connected local retval = {} local errtxt -- Get the templates from the DB local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT value AS filename, label, seq FROM provisioning_options WHERE param_id = (SELECT param_id FROM provisioning_params WHERE name = 'template') ORDER BY seq ASC, label ASC, value ASC" retval = getselectresponse(sql) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end local reversed = {} @@ -547,6 +579,7 @@ mymodule.list_templates = function() end mymodule.get_template = function(self, clientdata) + local connected clientdata = clientdata or {} local filename = clientdata.filename local retval = {} @@ -559,7 +592,7 @@ mymodule.get_template = function(self, clientdata) local errtxt if filename and filename ~= "" then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(filename).."' ORDER BY seq ASC, label ASC, value ASC" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then @@ -571,9 +604,10 @@ mymodule.get_template = function(self, clientdata) end end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end end @@ -590,6 +624,7 @@ mymodule.create_template = function(self, template, action) end mymodule.update_template = function(self, template, action, create) + local connected local success = true local errtxt -- Validate the settings @@ -610,7 +645,7 @@ mymodule.update_template = function(self, template, action, create) end if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(template.value.filename.value).."'" local tmp = getselectresponse(sql) if not create and (not tmp or #tmp == 0) then @@ -630,9 +665,10 @@ mymodule.update_template = function(self, template, action, create) fs.write_file(template.value.filename.value, string.gsub(format.dostounix(template.value.filecontent.value), "\n+$", "")) end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) success = false errtxt = err end @@ -655,10 +691,11 @@ mymodule.get_delete_template = function(self, clientdata) end mymodule.delete_template = function(self, delreq) + local connected local filename = delreq.value.filename.value delreq.errtxt = "Failed to delete template" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(filename).."'" local tmp = getselectresponse(sql) if #tmp == 0 then @@ -671,9 +708,10 @@ mymodule.delete_template = function(self, delreq) os.remove(filename) delreq.errtxt = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) delreq.errtxt = err end @@ -681,16 +719,18 @@ mymodule.delete_template = function(self, delreq) end mymodule.list_class_groups = function() + local connected local retval = {} local errtxt -- Get the groups from the DB local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_class_groups ORDER BY seq ASC, label ASC" retval = getselectresponse(sql) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -698,6 +738,7 @@ mymodule.list_class_groups = function() end mymodule.get_class_group = function(self, clientdata) + local connected clientdata = clientdata or {} local class_group_id = clientdata.class_group_id local retval = {} @@ -707,7 +748,7 @@ mymodule.get_class_group = function(self, clientdata) retval.seq = cfe({label="Sequence", seq=4}) local errtxt local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() if class_group_id and class_group_id ~= "" then sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(class_group_id).."'" tmp = getselectresponse(sql) @@ -721,9 +762,10 @@ mymodule.get_class_group = function(self, clientdata) else retval.class_group_id = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -735,6 +777,7 @@ mymodule.create_class_group = function(self, group, action) end mymodule.update_class_group = function(self, group, action, create) + local connected local success = true local errtxt -- Validate the settings @@ -755,7 +798,7 @@ mymodule.update_class_group = function(self, group, action, create) end if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(group.value.class_group_id.value).."'" local tmp = getselectresponse(sql) @@ -765,13 +808,12 @@ mymodule.update_class_group = function(self, group, action, create) end end if success then - local sql = "BEGIN TRANSACTION" - runsqlcommand(sql) + local sql if create then sql = "INSERT INTO provisioning_class_groups VALUES(DEFAULT, '"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "SELECT class_group_id FROM provisioning_class_groups WHERE label='"..provdb.escape(group.value.label.value).."'" - local tmp = getselectresponse(sql, true) + local tmp = getselectresponse(sql) if tmp and #tmp>0 then group.value.class_group_id = cfe({value=tmp[1].class_group_id, label="Class Group ID", readonly=true, seq=1}) else @@ -779,16 +821,13 @@ mymodule.update_class_group = function(self, group, action, create) end else sql = "UPDATE provisioning_class_groups SET (name, label, seq) = ('"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."') WHERE class_group_id='"..provdb.escape(group.value.class_group_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) end - - sql = "COMMIT" - runsqlcommand(sql) end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -811,10 +850,11 @@ mymodule.get_delete_class_group = function(self, clientdata) end mymodule.delete_class_group = function(self, delreq) + local connected local class_group_id = delreq.value.class_group_id.value delreq.errtxt = "Failed to delete class group" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(class_group_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then @@ -824,9 +864,10 @@ mymodule.delete_class_group = function(self, delreq) runsqlcommand(sql) delreq.errtxt = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) delreq.errtxt = err end @@ -834,16 +875,18 @@ mymodule.delete_class_group = function(self, delreq) end mymodule.list_classes = function() + local connected local retval = {} local errtxt -- Get the classes from the DB local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT class_id, g.label AS group, g.name, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.seq ASC, g.label ASC, c.seq ASC, c.label ASC" retval = getselectresponse(sql) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -851,6 +894,7 @@ mymodule.list_classes = function() end mymodule.get_class = function(self, clientdata) + local connected clientdata = clientdata or {} local class_id = clientdata.class_id local retval = {} @@ -862,7 +906,7 @@ mymodule.get_class = function(self, clientdata) local errtxt local res, err = pcall(function() local groups = {} - local connected = databaseconnect() + connected = databaseconnect() if class_id and class_id ~= "" then local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'" local tmp = getselectresponse(sql) @@ -901,9 +945,10 @@ mymodule.get_class = function(self, clientdata) group.value = g.group_id end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -915,6 +960,7 @@ mymodule.create_class = function(self, class, action) end mymodule.update_class = function(self, class, action, create) + local connected local success = true local errtxt -- Validate the settings @@ -932,7 +978,7 @@ mymodule.update_class = function(self, class, action, create) end if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class.value.class_id.value).."'" local tmp = getselectresponse(sql) @@ -942,13 +988,12 @@ mymodule.update_class = function(self, class, action, create) end end if success then - local sql = "BEGIN TRANSACTION" - runsqlcommand(sql) + local sql if create then sql = "INSERT INTO provisioning_classes VALUES(DEFAULT, '"..provdb.escape(class.value.class_group_id.value).."', '"..provdb.escape(class.value.label.value).."', '"..provdb.escape(class.value.seq.value).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "SELECT class_id FROM provisioning_classes WHERE class_group_id='"..provdb.escape(class.value.class_group_id.value).."' AND label='"..provdb.escape(class.value.label.value).."'" - local tmp = getselectresponse(sql, true) + local tmp = getselectresponse(sql) if tmp and #tmp>0 then class.value.class_id = cfe({value=tmp[1].class_id, label="Class ID", readonly=true, seq=1}) else @@ -956,25 +1001,22 @@ mymodule.update_class = function(self, class, action, create) end else sql = "UPDATE provisioning_classes SET (class_group_id, label, seq) = ('"..provdb.escape(class.value.class_group_id.value).."', '"..provdb.escape(class.value.label.value).."', '"..provdb.escape(class.value.seq.value).."') WHERE class_id='"..provdb.escape(class.value.class_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..provdb.escape(class.value.class_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) end -- Insert the class to group entries for n,g in pairs(class.value.groups.value) do if g.value ~= "" then sql = "INSERT INTO classes_to_param_groups VALUES('"..provdb.escape(class.value.class_id.value).."', '"..provdb.escape(g.value).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) end end - - sql = "COMMIT" - runsqlcommand(sql) end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -997,29 +1039,26 @@ mymodule.get_delete_class = function(self, clientdata) end mymodule.delete_class = function(self, delreq) + local connected local class_id = delreq.value.class_id.value delreq.errtxt = "Failed to delete class" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then delreq.value.class_id.errtxt = "Class does not exist" else - sql = "BEGIN TRANSACTION" - runsqlcommand(sql) sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..provdb.escape(class_id).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "DELETE FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'" - runsqlcommand(sql, true) - sql = "COMMIT" runsqlcommand(sql) delreq.errtxt = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) delreq.errtxt = err end @@ -1027,16 +1066,18 @@ mymodule.delete_class = function(self, delreq) end mymodule.list_groups = function() + local connected local retval = {} local errtxt -- Get the groups from the DB local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_groups ORDER BY seq ASC, name ASC, label ASC" retval = getselectresponse(sql) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -1044,6 +1085,7 @@ mymodule.list_groups = function() end mymodule.get_group = function(self, clientdata) + local connected clientdata = clientdata or {} local group_id = clientdata.group_id local retval = {} @@ -1056,7 +1098,7 @@ mymodule.get_group = function(self, clientdata) retval.defaults = cfe({type="group", value={}, label="Parameter Defaults", seq=8}) local errtxt local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- First, let's get all the parameters to set up the params.options and defaults local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC" local tmp = getselectresponse(sql) @@ -1103,9 +1145,10 @@ mymodule.get_group = function(self, clientdata) else retval.group_id = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -1117,6 +1160,7 @@ mymodule.create_group = function(self, group, action) end mymodule.update_group = function(self, group, action, create) + local connected local success = true local errtxt -- Validate the settings @@ -1147,7 +1191,7 @@ mymodule.update_group = function(self, group, action, create) end if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local devices = {} if not create then local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..provdb.escape(group.value.group_id.value).."'" @@ -1168,13 +1212,11 @@ mymodule.update_group = function(self, group, action, create) end end if success then - local sql = "BEGIN TRANSACTION" - runsqlcommand(sql) if create then sql = "INSERT INTO provisioning_groups VALUES(DEFAULT, '"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "SELECT group_id FROM provisioning_groups WHERE name='"..provdb.escape(group.value.name.value).."' AND label='"..provdb.escape(group.value.label.value).."'" - local tmp = getselectresponse(sql, true) + local tmp = getselectresponse(sql) if tmp and #tmp>0 then group.value.group_id = cfe({value=tmp[1].group_id, label="Group ID", readonly=true, seq=1}) else @@ -1182,9 +1224,9 @@ mymodule.update_group = function(self, group, action, create) end else sql = "UPDATE provisioning_groups SET (name, label, seq) = ('"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."') WHERE group_id='"..provdb.escape(group.value.group_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "DELETE FROM param_groups_to_params WHERE group_id='"..provdb.escape(group.value.group_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) end -- Reverse the editable table for ease of use below local reverseeditable = {} @@ -1200,12 +1242,9 @@ mymodule.update_group = function(self, group, action, create) sql = sql.."null" end sql = sql..", '"..provdb.escape(tostring(reverseeditable[p] ~= nil)).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) end - sql = "COMMIT" - runsqlcommand(sql) - -- Notify the devices that their params might have changed for i,d in ipairs(devices) do local tmp = saved_device_params[d.device_id] @@ -1213,10 +1252,10 @@ mymodule.update_group = function(self, group, action, create) callscript(updatedeviceparamsscriptfile, p, tmp) end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -1239,29 +1278,26 @@ mymodule.get_delete_group = function(self, clientdata) end mymodule.delete_group = function(self, delreq) + local connected local group_id = delreq.value.group_id.value delreq.errtxt = "Failed to delete parameter group" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..provdb.escape(group_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then delreq.value.group_id.errtxt = "Group does not exist" else - sql = "BEGIN TRANSACTION" - runsqlcommand(sql) sql = "DELETE FROM param_groups_to_params WHERE group_id='"..provdb.escape(group_id).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "DELETE FROM provisioning_groups WHERE group_id='"..provdb.escape(group_id).."'" - runsqlcommand(sql, true) - sql = "COMMIT" runsqlcommand(sql) delreq.errtxt = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) delreq.errtxt = err end @@ -1269,16 +1305,18 @@ mymodule.delete_group = function(self, delreq) end mymodule.list_params = function() + local connected local retval = {} local errtxt -- Get the params from the DB local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC, label ASC" retval = getselectresponse(sql) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -1286,6 +1324,7 @@ mymodule.list_params = function() end mymodule.get_param = function(self, clientdata) + local connected clientdata = clientdata or {} local param_id = clientdata.param_id local retval = {} @@ -1300,7 +1339,7 @@ mymodule.get_param = function(self, clientdata) retval.seq = cfe({label="Sequence", seq=9}) local errtxt local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() if param_id and param_id ~= "" then sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'" tmp = getselectresponse(sql) @@ -1314,9 +1353,10 @@ mymodule.get_param = function(self, clientdata) else retval.param_id = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end @@ -1328,6 +1368,7 @@ mymodule.create_param = function(self, param, action) end mymodule.update_param = function(self, param, action, create) + local connected local success = true local errtxt -- Validate the settings @@ -1354,7 +1395,7 @@ mymodule.update_param = function(self, param, action, create) end if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local devices = {} if not create then local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param.value.param_id.value).."'" @@ -1375,13 +1416,12 @@ mymodule.update_param = function(self, param, action, create) end end if success then - local sql = "BEGIN TRANSACTION" - runsqlcommand(sql) + local sql if create then sql = "INSERT INTO provisioning_params VALUES(DEFAULT, '"..provdb.escape(param.value.name.value).."', '"..provdb.escape(param.value.type.value).."', '"..provdb.escape(param.value.label.value).."', '"..provdb.escape(param.value.descr.value).."', '"..provdb.escape(param.value.value.value).."', '"..provdb.escape(param.value.seq.value).."', '"..provdb.escape(param.value.regexp.value).."', '"..provdb.escape(format.dostounix(param.value.validate.value)).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(param.value.name.value).."' AND label='"..provdb.escape(param.value.label.value).."'" - local tmp = getselectresponse(sql, true) + local tmp = getselectresponse(sql) if tmp and #tmp>0 then param.value.param_id = cfe({value=tmp[1].param_id, label="Param ID", readonly=true, seq=1}) else @@ -1389,12 +1429,9 @@ mymodule.update_param = function(self, param, action, create) end else sql = "UPDATE provisioning_params SET (name, type, label, descr, value, seq, regexp, validate) = ('"..provdb.escape(param.value.name.value).."', '"..provdb.escape(param.value.type.value).."', '"..provdb.escape(param.value.label.value).."', '"..provdb.escape(param.value.descr.value).."', '"..provdb.escape(param.value.value.value).."', '"..provdb.escape(param.value.seq.value).."', '"..provdb.escape(param.value.regexp.value).."', '"..provdb.escape(format.dostounix(param.value.validate.value)).."') WHERE param_id='"..provdb.escape(param.value.param_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) end - sql = "COMMIT" - runsqlcommand(sql) - -- Notify the devices that their params might have changed for i,d in ipairs(devices) do local tmp = saved_device_params[d.device_id] @@ -1402,10 +1439,10 @@ mymodule.update_param = function(self, param, action, create) callscript(updatedeviceparamsscriptfile, p, tmp) end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -1428,29 +1465,26 @@ mymodule.get_delete_param = function(self, clientdata) end mymodule.delete_param = function(self, delreq) + local connected local param_id = delreq.value.param_id.value delreq.errtxt = "Failed to delete parameter" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then delreq.value.param_id.errtxt = "Parameter does not exist" else - sql = "BEGIN TRANSACTION" - runsqlcommand(sql) sql = "DELETE FROM provisioning_options WHERE param_id='"..provdb.escape(param_id).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "DELETE FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'" - runsqlcommand(sql, true) - sql = "COMMIT" runsqlcommand(sql) delreq.errtxt = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) delreq.errtxt = err end @@ -1458,6 +1492,7 @@ mymodule.delete_param = function(self, delreq) end mymodule.list_devices = function(self, clientdata) + local connected local retval = cfe({ type="group", value={}, label="Provisioning Devices" }) retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true }) retval.value.pagesize = cfe({ value=10, label="Page Size", key=true }) @@ -1478,7 +1513,7 @@ mymodule.list_devices = function(self, clientdata) -- Get the devices from the DB local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- First, get the columns / class_groups local sql = "SELECT * from provisioning_class_groups ORDER BY seq ASC" @@ -1536,9 +1571,10 @@ mymodule.list_devices = function(self, clientdata) retval.value.rowcount.value = #retval.value.result.value end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) retval.errtxt = err end @@ -1559,7 +1595,8 @@ mymodule.create_device = function(self, device, action) return mymodule.update_device(self, device, action, true) end -mymodule.update_device = function(self, device, action, create, intransaction) +mymodule.update_device = function(self, device, action, create) + local connected local success = true local errtxt -- Validate the settings @@ -1573,7 +1610,7 @@ mymodule.update_device = function(self, device, action, create, intransaction) end if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() if not create then local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device.value.device_id.value).."' LIMIT 1" local tmp = getselectresponse(sql) @@ -1586,36 +1623,32 @@ mymodule.update_device = function(self, device, action, create, intransaction) if not saved_devices[device.value.device_id.value] then get_device(device.value.device_id.value) end if not saved_device_params[device.value.device_id.value] then get_device_params(device.value.device_id.value) end - local sql = "BEGIN TRANSACTION" - if not intransaction then runsqlcommand(sql) end if create then sql = "SELECT nextval('provisioning_device_seq')" - local tmp = getselectresponse(sql, true) + local tmp = getselectresponse(sql) if tmp and #tmp>0 then device.value.device_id.value = tmp[1].nextval end end sql = "DELETE FROM devices_to_classes WHERE device_id='"..provdb.escape(device.value.device_id.value).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) -- Insert the device to class entries for n,c in pairs(device.value.classes.value) do if c.value ~= "" then sql = "INSERT INTO devices_to_classes VALUES('"..provdb.escape(device.value.device_id.value).."', '"..provdb.escape(c.value).."')" - runsqlcommand(sql, true) + runsqlcommand(sql) end end - sql = "COMMIT" - if not intransaction then runsqlcommand(sql) end - local s = saved_device_params[device.value.device_id.value] callscript(updatedevicescriptfile, device, saved_devices[device.value.device_id.value], get_device_params(device.value.device_id.value), s) + saved_devices[device.value.device_id.value] = device end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -1638,10 +1671,11 @@ mymodule.get_delete_device = function(self, clientdata) end mymodule.delete_device = function(self, delreq) + local connected local device_id = delreq.value.device_id.value delreq.errtxt = "Failed to delete device" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) if #tmp == 0 then @@ -1650,13 +1684,9 @@ mymodule.delete_device = function(self, delreq) if not saved_device_params[device_id] then get_device_params(device_id) end if not saved_devices[device_id] then get_device(device_id) end - sql = "BEGIN TRANSACTION" - runsqlcommand(sql) sql = "DELETE FROM provisioning_values WHERE device_id='"..provdb.escape(device_id).."'" - runsqlcommand(sql, true) + runsqlcommand(sql) sql = "DELETE FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."'" - runsqlcommand(sql, true) - sql = "COMMIT" runsqlcommand(sql) delreq.errtxt = nil @@ -1664,10 +1694,10 @@ mymodule.delete_device = function(self, delreq) saved_devices[device_id] = nil saved_device_params[device_id] = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) delreq.errtxt = err end @@ -1693,12 +1723,13 @@ mymodule.get_class_options = function(self, clientdata) end mymodule.get_class_values = function(self, retval) + local connected retval.errtxt = "Failed to find class" if not validator.is_integer(retval.value.class_id.value) then retval.value.class_id.errtxt = "Invalid class ID" else local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- First, just check to see if class_id exists local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(retval.value.class_id.value).."'" local tmp = getselectresponse(sql) @@ -1725,9 +1756,10 @@ mymodule.get_class_values = function(self, retval) else retval.value.class_id.errtxt = "Failed to find Class ID" end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) retval.errtxt = err end end @@ -1744,8 +1776,9 @@ mymodule.set_all_device_params = function(self, params) end mymodule.fetch_device_values = function(self, search) + local connected local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() search = mymodule.search_device_values(self, search) if search.errtxt then -- Just return the error @@ -1757,9 +1790,10 @@ mymodule.fetch_device_values = function(self, search) search.value.values = get_device_values(search.value.result.value[1].device_id) search.value.values.seq = 5 end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) search.errtxt = err end @@ -1768,13 +1802,14 @@ mymodule.fetch_device_values = function(self, search) end mymodule.get_search_options = function() + local connected local errtxt retval = {} retval.id = cfe({type="select", value="device_id", label="Parameter", option={"device_id"}, seq=1}) retval.comparison = cfe({type="select", value="=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!~*"}, seq=2}) retval.value = cfe({label="Parameter Value", descr="Parameter value or SQL regular expression", seq=3}) local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- Get the group/parameter options local sql = "SELECT g.name AS group_name, p.name AS param_name FROM provisioning_groups g JOIN param_groups_to_params USING(group_id) JOIN provisioning_params p USING(param_id) GROUP BY g.name, p.name ORDER BY g.name ASC, p.name ASC" local tmp = getselectresponse(sql) @@ -1791,15 +1826,17 @@ mymodule.get_search_options = function() for i,o in ipairs(blankopt) do retval.id.option[#retval.id.option + 1] = o end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end return cfe({type="group", value=retval, label="Device Search", errtxt=errtxt}) end mymodule.search_device_values = function(self, search) + local connected local success = true success = modelfunctions.validateselect(search.value.id) and success success = modelfunctions.validateselect(search.value.comparison) and success @@ -1816,7 +1853,7 @@ mymodule.search_device_values = function(self, search) if success then retval.result = cfe({type="structure", value={}, label="Devices", seq=4 }) local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- Get the devices from the DB sql = "SELECT d2t.device_id, " local group, param = string.match(search.value.id.value, "([^%.]*)%.(.*)") @@ -1837,9 +1874,10 @@ mymodule.search_device_values = function(self, search) end sql = sql.." ORDER BY d2t.device_id ASC" search.value.result.value = getselectresponse(sql) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) search.errtxt = err end else @@ -1849,6 +1887,7 @@ mymodule.search_device_values = function(self, search) end mymodule.get_param_options = function(self, clientdata) + local connected clientdata = clientdata or {} local param_id = clientdata.param_id local retval = {} @@ -1859,7 +1898,7 @@ mymodule.get_param_options = function(self, clientdata) local errtxt = "Cannot find parameter" if param_id and param_id ~= "" then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- First, just check to see if param_id exists local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'" local tmp = getselectresponse(sql) @@ -1874,9 +1913,10 @@ mymodule.get_param_options = function(self, clientdata) retval.options.value[#retval.options.value + 1] = t.value..","..t.label end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) errtxt = err end end @@ -1884,13 +1924,14 @@ mymodule.get_param_options = function(self, clientdata) end mymodule.set_param_options = function(self, options) + local connected local success = true local errtxt -- Validate the settings --FIXME if success then local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(options.value.param_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then @@ -1898,11 +1939,9 @@ mymodule.set_param_options = function(self, options) errtxt = "Parameter does not exist" end if success then - local sql = "BEGIN TRANSACTION" - runsqlcommand(sql) -- Delete all options for this device - sql = "DELETE FROM provisioning_options WHERE param_id='"..provdb.escape(options.value.param_id.value).."'" - runsqlcommand(sql, true) + local sql = "DELETE FROM provisioning_options WHERE param_id='"..provdb.escape(options.value.param_id.value).."'" + runsqlcommand(sql) -- Loop through the options for i,o in ipairs(options.value.options.value) do local v,l = string.match(o, "^%s*([^,]*),%s*(.*%S?)%s*$") @@ -1913,15 +1952,13 @@ mymodule.set_param_options = function(self, options) l = v end sql = "INSERT INTO provisioning_options VALUES('"..provdb.escape(options.value.param_id.value).."', '"..provdb.escape(l).."', '"..provdb.escape(v).."', '"..i.."')" - runsqlcommand(sql, true) + runsqlcommand(sql) end - sql = "COMMIT" - runsqlcommand(sql) end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() con:execute("ROLLBACK") end) + handlesqlexception(connected) success = false errtxt = err end @@ -1958,8 +1995,9 @@ function mymodule.get_database() end function mymodule.dump_database(self, db) + local connected local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local lines = {} local sql = "SELECT name, label, seq FROM provisioning_class_groups ORDER BY name, label" local tmp = getselectresponse(sql) @@ -2014,15 +2052,17 @@ function mymodule.dump_database(self, db) lines[#lines+1] = "SELECT pg_catalog.setval('provisioning_device_seq', "..provdb.escape(#devices)..", true);" end db.value.data = cfe({ type="longtext", value=table.concat(lines, "\n") or "", label="Database Values" }) - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) db.errtxt = err end return db end function mymodule.get_file(self, clientdata) + local connected clientdata = clientdata or {} local mac = clientdata.mac local ip = clientdata.ip @@ -2036,7 +2076,7 @@ function mymodule.get_file(self, clientdata) end local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- Add the device to the table of requests local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(mac)).."'" @@ -2069,9 +2109,10 @@ function mymodule.get_file(self, clientdata) end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) result.errtxt = err end @@ -2079,6 +2120,7 @@ function mymodule.get_file(self, clientdata) end function mymodule.put_file(self, clientdata) + local connected clientdata = clientdata or {} local mac = clientdata.mac local data = clientdata.data @@ -2091,7 +2133,7 @@ function mymodule.put_file(self, clientdata) end local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- Now, let's see if this device exists local search = mymodule.get_search_options() @@ -2109,15 +2151,17 @@ function mymodule.put_file(self, clientdata) -- If the script doesn't exist, allow the write retval.value = retval.value or data end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) retval.errtxt = err end return retval end mymodule.list_requests = function(self, clientdata) + local connected local retval = cfe({ type="group", value={}, label="Requests" }) retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true }) retval.value.pagesize = cfe({ value=10, label="Page Size", key=true }) @@ -2147,7 +2191,7 @@ mymodule.list_requests = function(self, clientdata) end local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local filter = {} columns.data = nil -- Cannot regex filter based on date because of the timestamp type @@ -2182,9 +2226,10 @@ mymodule.list_requests = function(self, clientdata) retval.value.rowcount.value = #retval.value.result.value end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) retval.errtxt = err end @@ -2199,10 +2244,11 @@ mymodule.get_delete_request = function(self, clientdata) end mymodule.delete_request = function(self, delreq) + local connected local mac = delreq.value.mac.value delreq.errtxt = "Failed to delete request" local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(mac)).."'" local tmp = getselectresponse(sql) if #tmp == 0 then @@ -2213,9 +2259,10 @@ mymodule.delete_request = function(self, delreq) runsqlcommand(sql) delreq.errtxt = nil end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) delreq.errtxt = err end @@ -2231,9 +2278,10 @@ mymodule.get_request = function(self, clientdata) end mymodule.create_from_request = function(self, request) + local connected success = false local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(request.value.mac.value)).."'" local tmp = getselectresponse(sql) if #tmp == 0 then @@ -2276,9 +2324,10 @@ mymodule.create_from_request = function(self, request) request.errtxt = "Failed to create device - could not determine class" end end - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then + handlesqlexception(connected) request.errtxt = err end @@ -2295,13 +2344,14 @@ mymodule.get_bulk_create_request = function(self, clientdata) end mymodule.bulk_create_devices = function(self, devicelist) + local connected -- To allow uploading a file, check the bulkdevicedata format -- Haserl will pass a temporary file name if a file is uploaded if #devicelist.value.bulkdevicedata.value == 1 and string.find(devicelist.value.bulkdevicedata.value[1], "^/tmp/[^.]+$") and fs.is_file(devicelist.value.bulkdevicedata.value[1]) then devicelist.value.bulkdevicedata.value = fs.read_file_as_array(devicelist.value.bulkdevicedata.value[1]) or {} end local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() -- Need to split on ",", but not if found in quoted field function csv_parseline(line,sep) @@ -2342,7 +2392,7 @@ mymodule.bulk_create_devices = function(self, devicelist) return res end - local groups = mymodule.list_class_groups() + local groups = mymodule.list_class_groups(true) local headers = csv_parseline(devicelist.value.bulkdevicedata.value[1], ",") local reverseheaders = {} @@ -2350,7 +2400,6 @@ mymodule.bulk_create_devices = function(self, devicelist) reverseheaders[h] = i end - runsqlcommand("BEGIN TRANSACTION") for i=2,#devicelist.value.bulkdevicedata.value do local values = csv_parseline(devicelist.value.bulkdevicedata.value[i], ",") @@ -2403,7 +2452,7 @@ mymodule.bulk_create_devices = function(self, devicelist) end -- Set the params - local params = get_device_params(device.value.device_id.value, false) + local params = get_device_params(device.value.device_id.value) for j,h in ipairs(headers) do if values[j] and string.find(h, "%.") then local class,param = string.match(h, "^([^.]+)%.(.*)") @@ -2453,11 +2502,10 @@ mymodule.bulk_create_devices = function(self, devicelist) error(table.concat(err, "\n")) end end - runsqlcommand("COMMIT") - if connected then provdb.databasedisconnect() end + if connected then databasedisconnect() end end) if not res and err then - pcall(function() runsqlcommand("ROLLBACK") end) + handlesqlexception(connected) devicelist.errtxt = err end @@ -2470,9 +2518,10 @@ mymodule.get_bulk_dump_request = function(self, clientdata) end mymodule.bulk_dump_devices = function(self, dumprequest) + local connected dumprequest.value.devices = cfe({type="raw", value={}, label="devices.csv"}) local res, err = pcall(function() - local connected = databaseconnect() + connected = databaseconnect() local classes = {} local reverseclasses = {} @@ -2500,6 +2549,9 @@ mymodule.bulk_dump_devices = function(self, dumprequest) end end end + + if connected then databasedisconnect() end + table.sort(classes) table.sort(columns) @@ -2533,10 +2585,9 @@ mymodule.bulk_dump_devices = function(self, dumprequest) dumprequest.value.devices.value[#dumprequest.value.devices.value+1] = table.concat(device, ",") end - - if connected then provdb.databasedisconnect() end end) if not res and err then + handlesqlexception(connected) dumprequest.errtxt = err end -- cgit v1.2.3