module (..., package.seeall) -- Load libraries require("modelfunctions") require("posix") require("fs") require("format") require("validator") require("luasql.postgres") require("session") -- Set variables local DatabaseName = "provisioning" local DatabaseUser = "postgres" local DatabasePassword local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin " local baseurl = "/etc/provisioning/" local env local con local table_creation_scripts = { -- List of all available templates provisioning_templates = { "CREATE TABLE provisioning_templates (template_id SERIAL PRIMARY KEY, filename VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE)", }, -- List of each device that we manage provisioning_devices = { "CREATE TABLE provisioning_devices (device_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255), template_id INTEGER)", }, -- Multi-to-multi mapping of devices to classes -- Need to enforce that devices do not contain more than one class with same name (using triggers) devices_to_classes = { "CREATE TABLE devices_to_classes (device_id INTEGER, class_id INTEGER)", }, -- List of different device classes -- Need to enforce that name/label combo is unique provisioning_classes = { "CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255))", }, -- Multi-to-multi mapping of classes to parameter groups -- Need to enforce that classes do not contain more than one parameter group with same name (using triggers) classes_to_param_groups = { "CREATE TABLE classes_to_param_groups (class_id INTEGER, group_id INTEGER)", }, -- List of different parameter groups provisioning_groups = { "CREATE TABLE provisioning_groups (group_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE, seq INTEGER)", }, -- Multi-to-multi mapping of parameter groups to parameters param_groups_to_params = { "CREATE TABLE param_groups_to_params (group_id INTEGER, param_id INTEGER, value VARCHAR(255), editable BOOLEAN)", }, -- List of each parameter used in any way for any device - mostly for how to display provisioning_params = { "CREATE TABLE provisioning_params (param_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER)", -- Add a way to include validation code and/or options list }, -- All of the (non-default) parameter values for all devices are stored here provisioning_values = { -- device_id is a device id from provisioning_devices and param_id is a param id from provisioning_params "CREATE TABLE provisioning_values (device_id INTEGER, group_name VARCHAR(255), param_id INTEGER, value VARCHAR(255))", }, -- List of options for parameters provisioning_options = { "CREATE TABLE provisioning_options (param_id INTEGER, label VARCHAR(255), value VARCHAR(255), seq INTEGER)", }, -- Where do we define the triggers to enforce the foreign keys? Triggers are only on updates, so don't have to worry about read speed -- Need to define indices } -- ################################################################################ -- LOCAL FUNCTIONS local function escape_quotes(str) return string.gsub(str or "", "'", "'\\''") end local function assert (v, m) if not v then m = m or "Assertion failed!" error(m, 0) end return v, m end -- Escape special characters in sql statements local escape = function(sql) sql = sql or "" sql = string.gsub(sql, "'", "''") return string.gsub(sql, "\\", "\\\\") end local createdatabase = function() local result = {} -- First, create the user if DatabaseUser ~= "postgres" then local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'' if DatabasePassword then cmd = cmd .. ' WITH PASSWORD \''..DatabasePassword..'\'' end cmd = cmd .. '" 2>&1' local f = io.popen(cmd) table.insert(result, f:read("*a")) f:close() end -- Create the database local cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseUser..'" 2>&1' local f = io.popen(cmd) table.insert(result, f:read("*a")) f:close() logevent(table.concat(result, "\n")) return table.concat(result, "\n") end -- Delete the database and roles local deletedatabase = function() local result = {} local cmd = path..'psql -U postgres -c "DROP DATABASE '..DatabaseName..'" 2>&1' local f = io.popen(cmd) table.insert(result, f:read("*a")) f:close() if DatabaseUser ~= "postgres" then cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1' f = io.popen(cmd) table.insert(result, f:read("*a")) f:close() end logevent(table.concat(result, "\n")) return table.concat(result, "\n") end local databaseconnect = function() if not con then -- create environment object env = assert (luasql.postgres()) -- connect to data source local err con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword) if err and string.match(err, "Error connecting to database.") then createdatabase() con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword) end assert(con, err) return true end return false end local databasedisconnect = function() if env then env:close() env = nil end if con then con:close() con = nil end end local runscript = function(script) for i,scr in ipairs(script) do logevent(scr) assert( con:execute(scr) ) end end local runsqlcommand runsqlcommand = function(sql, in_transaction) logevent(sql) if in_transaction then assert(con:execute("SAVEPOINT before_command")) end local res, err = con:execute(sql) if not res and err then -- Catch the error to see if it's caused by lack of table logevent(err) local table = string.match(err, "relation \"(%S+)\" does not exist") if table and table_creation_scripts[table] then if in_transaction then assert(con:execute("ROLLBACK TO before_command")) end runscript(table_creation_scripts[table]) runsqlcommand(sql) else assert(res, err) end else if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end end end local getselectresponse getselectresponse = function(sql, in_transaction) local retval = {} if in_transaction then assert(con:execute("SAVEPOINT before_select")) end local res, err = pcall(function() logevent(sql) 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 logevent(err) -- Catch the error to see if it's caused by lack of table local table = string.match(err, "relation \"(%S+)\" does not exist") if table and table_creation_scripts[table] then if in_transaction then assert(con:execute("ROLLBACK TO before_select")) end runscript(table_creation_scripts[table]) return getselectresponse(sql) else assert(res, err) end else if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end end return retval end local validateparam validateparam = function(p) if p.type == "group" then local success = true for n,p2 in pairs(p.value) do success = validateparam(p2) and success end return success elseif p.type == "select" then return modelfunctions.validateselect(p) end return true end -- ################################################################################ -- PUBLIC FUNCTIONS list_templates = function() local retval = {} local errtxt -- Get the templates from the DB local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_templates ORDER BY label ASC, filename ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) if not res and err then errtxt = err end local reversed = {} for i,t in ipairs(retval) do reversed[t.filename] = i end -- Get the file stats for each template and add in any template files that aren't in the DB for f in posix.files(baseurl) do local file = baseurl..f local details = fs.stat(file) if details.type == "regular" then if not reversed[file] then retval[#retval+1] = {filename=file} reversed[file] = #retval end local t = retval[reversed[file]] t.filesize = details.size t.mtime = details.mtime end end return cfe({ type="structure", value=retval, label="List of Templates", errtxt=errtxt }) end get_template = function(template_id, filename) local retval = {} if filename and not string.match(filename, "/") then filename = baseurl .. filename end retval.template_id = cfe({value=template_id or "", label="Template ID"}) retval.filename = cfe({value=filename or "", label="File Name", descr="Must be in "..baseurl}) retval.label = cfe({label="Template Label"}) local errtxt if template_id and template_id ~= "" then local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template_id).."' ORDER BY label ASC, filename ASC" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if retval[n] then retval[n].value = v end end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end end local filedetails = modelfunctions.getfiledetails(retval.filename.value, function(filename) return validator.is_valid_filename(filename, baseurl) end) for i,n in ipairs({"filecontent", "filesize", "mtime"}) do retval[n] = filedetails.value[n] end return cfe({ type="group", value=retval, label="Provisioning Template", errtxt=errtxt }) end create_template = function(template) return update_template(template, true) end update_template = function(template, create) local success = true local errtxt -- Validate the settings if template.value.filename and not string.match(template.value.filename.value, "/") then template.value.filename.value = baseurl .. template.value.filename.value end if not validator.is_valid_filename(template.value.filename.value, baseurl) then success = false template.value.filename.errtxt = "Invalid filename" end if template.value.label.value == "" then success = false template.value.label.errtxt = "Cannot be blank" end if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template.value.template_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Template does not exist" end end if success then if create then sql = "INSERT INTO provisioning_templates VALUES(DEFAULT, '"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."')" else sql = "UPDATE provisioning_templates SET (filename, label) = ('"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."') WHERE template_id='"..escape(template.value.template_id.value).."'" end runsqlcommand(sql) fs.write_file(template.value.filename.value, string.gsub(format.dostounix(template.value.filecontent.value), "\n+$", "")) end if connected then databasedisconnect() end end) if not res and err then success = false errtxt = err end end if not success then if create then template.errtxt = errtxt or "Failed to create template" else template.errtxt = errtxt or "Failed to save template" end end return template end delete_template = function(template_id) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Template does not exist" else -- Remove the template sql = "DELETE FROM provisioning_templates WHERE template_id='"..escape(template_id).."'" runsqlcommand(sql) -- Delete the template file os.remove(tmp[1].filename) result = "Template Deleted" end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ value=result, errtxt=errtxt, label="Delete Template Result" }) end list_classes = function() local retval = {} local errtxt -- Get the classes from the DB local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_classes ORDER BY name ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="structure", value=retval, label="List of Classes", errtxt=errtxt }) end get_class = function(class_id) local retval = {} retval.class_id = cfe({value=class_id or "", label="Class ID", seq=1}) retval.name = cfe({label="Name", seq=2}) retval.label = cfe({label="Label", seq=3}) retval.groups = cfe({type="group", value={}, label="Parameter Groups", seq=4}) local errtxt local res, err = pcall(function() local groups = {} local connected = databaseconnect() if class_id and class_id ~= "" then local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class_id).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if retval[n] then retval[n].value = v end end end -- Now, get the class-to-paramgroup mappings sql = "SELECT group_id FROM classes_to_param_groups WHERE class_id="..escape(class_id) tmp = getselectresponse(sql) for i,g in ipairs(tmp) do groups[g.group_id] = true end end -- Finally, get the paramgroup options sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY seq ASC, name ASC" tmp = getselectresponse(sql) for i,g in ipairs(tmp) do if not retval.groups.value[g.name] then retval.groups.value[g.name] = cfe({type="select", label=g.name, option={{value="", label=""}}, seq=i}) end local group = retval.groups.value[g.name] group.option[#group.option + 1] = {value=g.group_id, label=g.label} if groups[g.group_id] then group.value = g.group_id end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="group", value=retval, label="Provisioning Class", errtxt=errtxt }) end create_class = function(class) return update_class(class, true) end update_class = function(class, create) local success = true local errtxt -- Validate the settings if class.value.name.value == "" then success = false class.value.name.errtxt = "Cannot be blank" end if class.value.label.value == "" then success = false class.value.label.errtxt = "Cannot be blank" end for n,g in pairs(class.value.groups.value) do success = modelfunctions.validateselect(g) and success end if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class.value.class_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Class does not exist" end end if success then local sql = "BEGIN TRANSACTION" runsqlcommand(sql) if create then sql = "INSERT INTO provisioning_classes VALUES(DEFAULT, '"..escape(class.value.name.value).."', '"..escape(class.value.label.value).."')" runsqlcommand(sql, true) sql = "SELECT class_id FROM provisioning_classes WHERE name='"..escape(class.value.name.value).."' AND label='"..escape(class.value.label.value).."'" local tmp = getselectresponse(sql, true) if tmp and #tmp>0 then class.value.class_id.value = tmp[1].class_id end else sql = "UPDATE provisioning_classes SET (name, label) = ('"..escape(class.value.name.value).."', '"..escape(class.value.label.value).."') WHERE class_id='"..escape(class.value.class_id.value).."'" runsqlcommand(sql, true) sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..escape(class.value.class_id.value).."'" runsqlcommand(sql, true) 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('"..escape(class.value.class_id.value).."', '"..escape(g.value).."')" runsqlcommand(sql, true) end end sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) success = false errtxt = err end end if not success then if create then class.errtxt = errtxt or "Failed to create class" else class.errtxt = errtxt or "Failed to save class" end end return class end delete_class = function(class_id) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Class does not exist" else sql = "BEGIN TRANSACTION" runsqlcommand(sql) sql = "DELETE FROM provisioning_classes WHERE class_id='"..escape(class_id).."'" runsqlcommand(sql, true) sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..escape(class_id).."'" runsqlcommand(sql, true) result = "Class Deleted" sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) errtxt = err end return cfe({ value=result, errtxt=errtxt, label="Delete Class Result" }) end list_groups = function() local retval = {} local errtxt -- Get the groups from the DB local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_groups ORDER BY seq ASC, name ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="structure", value=retval, label="List of Parameter Groups", errtxt=errtxt }) end get_group = function(group_id) local retval = {} retval.group_id = cfe({value=group_id or "", label="Group ID", seq=1}) retval.name = cfe({label="Name", seq=2}) retval.label = cfe({label="Label", seq=3}) retval.seq = cfe({label="Sequence", seq=4}) retval.params = cfe({type="multi", value={}, label="Parameters", option={}, descr="Each selected parameter will be included in the group", seq=5}) retval.editable = cfe({type="multi", value={}, label="Editable Parameters", option={}, descr="Each selected parameter will be user editable", seq=6}) retval.defaults = cfe({type="group", value={}, label="Parameter Defaults", seq=7}) local errtxt local res, err = pcall(function() local 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) for i,p in ipairs(tmp) do retval.params.option[#retval.params.option + 1] = {value=p.param_id, label=p.label} retval.editable.option[#retval.editable.option + 1] = {value=p.param_id, label=p.label} p.seq = i if p.type == "select" then sql = "SELECT * FROM provisioning_options WHERE param_id='"..escape(p.param_id).."' ORDER BY seq ASC" p.option = getselectresponse(sql) or {} end if p.type == "boolean" then p.value = (p.value == "true") end retval.defaults.value[p.param_id] = p end if group_id and group_id ~= "" then sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group_id).."'" tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if retval[n] then retval[n].value = v end end end -- Now, get the paramgroup-to-param mappings sql = "SELECT * FROM param_groups_to_params WHERE group_id="..escape(group_id) tmp = getselectresponse(sql) for i,p in ipairs(tmp) do retval.params.value[#retval.params.value + 1] = p.param_id if (p.editable == "t") then retval.editable.value[#retval.editable.value + 1] = p.param_id end if retval.defaults.value[p.param_id].type == "boolean" then retval.defaults.value[p.param_id].value = (p.value == "true") else retval.defaults.value[p.param_id].value = p.value end end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="group", value=retval, label="Provisioning Parameter Group", errtxt=errtxt }) end create_group = function(group) return update_group(group, true) end update_group = function(group, create) local success = true local errtxt -- Validate the settings success = modelfunctions.validatemulti(group.value.params) if group.value.name.value == "" then success = false group.value.name.errtxt = "Cannot be blank" -- Group name cannot be device_id, name, or label or will mess up get_device_params elseif group.value.name.value == "device_id" or group.value.name.value == "name" or group.value.name.value == "label" then success = false group.value.name.errtxt = "Illegal name" end if group.value.label.value == "" then success = false group.value.label.errtxt = "Cannot be blank" end if not validator.is_integer(group.value.seq.value) then success = false group.value.seq.errtxt = "Must be an integer" end -- Validate the param defaults success = validateparam(group.value.defaults) and success if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group.value.group_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Group does not exist" end end if success then local sql = "BEGIN TRANSACTION" runsqlcommand(sql) if create then sql = "INSERT INTO provisioning_groups VALUES(DEFAULT, '"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."')" runsqlcommand(sql, true) sql = "SELECT group_id FROM provisioning_groups WHERE name='"..escape(group.value.name.value).."' AND label='"..escape(group.value.label.value).."'" local tmp = getselectresponse(sql, true) if tmp and #tmp>0 then group.value.group_id.value = tmp[1].group_id end else sql = "UPDATE provisioning_groups SET (name, label, seq) = ('"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."') WHERE group_id='"..escape(group.value.group_id.value).."'" runsqlcommand(sql, true) sql = "DELETE FROM param_groups_to_params WHERE group_id='"..escape(group.value.group_id.value).."'" runsqlcommand(sql, true) end -- Reverse the editable table for ease of use below local reverseeditable = {} for i,p in ipairs(group.value.editable.value) do reverseeditable[p] = i end -- Insert the group to param entries for i,p in ipairs(group.value.params.value) do sql = "INSERT INTO param_groups_to_params VALUES('"..escape(group.value.group_id.value).."', '"..escape(p).."', '"..escape(tostring(group.value.defaults.value[p].value)).."', '"..tostring(reverseeditable[p] ~= nil).."')" runsqlcommand(sql, true) end sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) success = false errtxt = err end end if not success then if create then group.errtxt = errtxt or "Failed to create parameter group" else group.errtxt = errtxt or "Failed to save parameter group" end end return group end delete_group = function(group_id) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Group does not exist" else sql = "BEGIN TRANSACTION" runsqlcommand(sql) sql = "DELETE FROM provisioning_groups WHERE group_id='"..escape(group_id).."'" runsqlcommand(sql, true) sql = "DELETE FROM param_groups_to_params WHERE group_id='"..escape(group_id).."'" runsqlcommand(sql, true) result = "Parameter Group Deleted" sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) errtxt = err end return cfe({ value=result, errtxt=errtxt, label="Delete Parameter Group Result" }) end list_params = function() local retval = {} local errtxt -- Get the params from the DB local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="structure", value=retval, label="List of Parameters", errtxt=errtxt }) end get_param = function(param_id) local retval = {} retval.param_id = cfe({value=param_id or "", label="Param ID", seq=1}) retval.name = cfe({label="Name", seq=2}) retval.type = cfe({type="select", label="Type", option={"text", "boolean", "select"}, seq=3}) retval.label = cfe({label="Label", seq=4}) retval.descr = cfe({label="Description", seq=5}) retval.value = cfe({label="Default Value", seq=6}) retval.seq = cfe({label="Sequence", seq=7}) -- FIXME - we should add validation and option stuff here local errtxt local res, err = pcall(function() local connected = databaseconnect() if param_id and param_id ~= "" then sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'" tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if retval[n] then retval[n].value = v end end end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="group", value=retval, label="Provisioning Parameter", errtxt=errtxt }) end create_param = function(param) return update_param(param, true) end update_param = function(param, create) local success = true local errtxt -- Validate the settings success = modelfunctions.validateselect(param.value.type) if param.value.name.value == "" then success = false param.value.name.errtxt = "Cannot be blank" end if param.value.label.value == "" then success = false param.value.label.errtxt = "Cannot be blank" end if not validator.is_integer(param.value.seq.value) then success = false param.value.seq.errtxt = "Must be an integer" end if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param.value.param_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Param does not exist" end end if success then local sql = "BEGIN TRANSACTION" runsqlcommand(sql) if create then sql = "INSERT INTO provisioning_params VALUES(DEFAULT, '"..escape(param.value.name.value).."', '"..escape(param.value.type.value).."', '"..escape(param.value.label.value).."', '"..escape(param.value.descr.value).."', '"..escape(param.value.value.value).."', '"..escape(param.value.seq.value).."')" runsqlcommand(sql, true) sql = "SELECT param_id FROM provisioning_params WHERE name='"..escape(param.value.name.value).."' AND label='"..escape(param.value.label.value).."'" local tmp = getselectresponse(sql, true) if tmp and #tmp>0 then param.value.param_id.value = tmp[1].param_id end else sql = "UPDATE provisioning_params SET (name, type, label, descr, value, seq) = ('"..escape(param.value.name.value).."', '"..escape(param.value.type.value).."', '"..escape(param.value.label.value).."', '"..escape(param.value.descr.value).."', '"..escape(param.value.value.value).."', '"..escape(param.value.seq.value).."') WHERE param_id='"..escape(param.value.param_id.value).."'" runsqlcommand(sql, true) end sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) success = false errtxt = err end end if not success then if create then param.errtxt = errtxt or "Failed to create parameter" else param.errtxt = errtxt or "Failed to save parameter" end end return param end delete_param = function(param_id) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Parameter does not exist" else sql = "BEGIN TRANSACTION" runsqlcommand(sql) sql = "DELETE FROM provisioning_params WHERE param_id='"..escape(param_id).."'" runsqlcommand(sql, true) result = "Parameter Deleted" sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) errtxt = err end return cfe({ value=result, errtxt=errtxt, label="Delete Parameter Result" }) end list_devices = function() local retval = {} local errtxt -- Get the devices from the DB local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_devices WHERE name IS NOT NULL ORDER BY name ASC, label ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="structure", value=retval, label="List of Devices", errtxt=errtxt }) end get_device = function(device_id) local retval = {} retval.device_id = cfe({value=device_id or "", label="Device ID", seq=1}) retval.name = cfe({label="Name", seq=2}) retval.label = cfe({label="Label", seq=3}) retval.template_id = cfe({type="select", label="Template", option={}, seq=4}) retval.classes = cfe({type="group", value={}, label="Classes", seq=5}) local errtxt local res, err = pcall(function() local classes={} local connected = databaseconnect() if device_id and device_id ~= "" then local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if retval[n] then retval[n].value = v end end end -- Now, get the device-to-class mappings sql = "SELECT class_id FROM devices_to_classes WHERE device_id="..escape(device_id) tmp = getselectresponse(sql) for i,g in ipairs(tmp) do classes[g.class_id] = true end end -- Next, get the template options sql = "SELECT template_id, label FROM provisioning_templates ORDER BY label ASC, filename ASC" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do retval.template_id.option[#retval.template_id.option + 1] = {value=t.template_id, label=t.label} end -- Finally, get the class options sql = "SELECT * FROM provisioning_classes ORDER BY name ASC, label ASC" tmp = getselectresponse(sql) for i,c in ipairs(tmp) do if not retval.classes.value[c.name] then retval.classes.value[c.name] = cfe({type="select", label=c.name, option={{value="", label=""}}, seq=i}) end local class = retval.classes.value[c.name] class.option[#class.option + 1] = {value=c.class_id, label=c.label} if classes[c.class_id] then class.value = c.class_id end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt }) end create_device = function(device) return update_device(device, true) end update_device = function(device, create) local success = true local errtxt -- Validate the settings success = modelfunctions.validateselect(device.value.template_id) if device.value.name.value == "" then success = false device.value.name.errtxt = "Cannot be blank" end for n,c in pairs(device.value.classes.value) do success = modelfunctions.validateselect(c) and success end if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device.value.device_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Device does not exist" end end if success then local sql = "BEGIN TRANSACTION" runsqlcommand(sql) if create then sql = "INSERT INTO provisioning_devices VALUES(DEFAULT, '"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."', '"..escape(device.value.template_id.value).."')" runsqlcommand(sql, true) sql = "SELECT device_id FROM provisioning_devices WHERE name='"..escape(device.value.name.value).."' AND label='"..escape(device.value.label.value).."'" local tmp = getselectresponse(sql, true) if tmp and #tmp>0 then device.value.device_id.value = tmp[1].device_id end else sql = "UPDATE provisioning_devices SET (name, label, template_id) = ('"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."', '"..escape(device.value.template_id.value).."') WHERE device_id='"..escape(device.value.device_id.value).."'" runsqlcommand(sql, true) sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."'" runsqlcommand(sql, true) end -- 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('"..escape(device.value.device_id.value).."', '"..escape(c.value).."')" runsqlcommand(sql, true) end end sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) success = false errtxt = err end end if not success then if create then device.errtxt = errtxt or "Failed to create device" else device.errtxt = errtxt or "Failed to save device" end end return device end delete_device = function(device_id) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Device does not exist" else sql = "BEGIN TRANSACTION" runsqlcommand(sql) sql = "DELETE FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" runsqlcommand(sql, true) sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device_id).."'" runsqlcommand(sql, true) result = "Device Deleted" sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) errtxt = err end return cfe({ value=result, errtxt=errtxt, label="Delete Device Result" }) end get_editable_device_params = function(device_id) return get_device_params(device_id, true) end get_device_params = function(device_id, editable) local retval = {} retval.device_id = cfe({value=device_id or "", label="Device ID", seq=0}) retval.name = cfe({label="Name", seq=0}) retval.label = cfe({label="Label", seq=0}) local errtxt = "Cannot find device" if device_id and device_id ~= "" then local res, err = pcall(function() local connected = databaseconnect() -- First, just check to see if device_id exists local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then errtxt = nil retval.name.value = tmp[1].name retval.label.value = tmp[1].label -- Next, get all of the param groups sql = "SELECT * FROM provisioning_groups" local tmp = getselectresponse(sql) -- Loop through the groups and put them into the result for i,g in ipairs(tmp) do retval[g.name] = g retval[g.name].label = g.name retval[g.name].type="group" end -- Then, get all of the parameters for this device sql = "SELECT g.name AS group, p.param_id, p.name, p.type, p.label, p.descr, p.seq, 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, g2p.value AS default ".. "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 ) ".. "WHERE d2t.device_id='"..escape(device_id).."'" if editable then sql = sql.." AND g2p.editable='t'" end local tmp = getselectresponse(sql) -- Loop through the params to figure out options and put them into the groups for i,p in ipairs(tmp) do -- Options if (p.type == "select") then sql = "SELECT * FROM provisioning_options WHERE param_id='"..escape(p.param_id).."' ORDER BY seq ASC" p.option = getselectresponse(sql) or {} end -- Groups if not retval[p.group].value then retval[p.group].value = {} end local value = retval[p.group].value if p.type == "boolean" then p.value = (p.value == "true") end value[p.name] = p end -- Finally, loop through the result and remove empty groups for name,val in pairs(retval) do if not val.value then retval[name] = nil end end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end end return cfe({ type="group", value=retval, label="Provisioning Device Parameters", errtxt=errtxt }) end set_editable_device_params = function(params) return set_device_params(params, true) end set_device_params = function(params, editable) -- Validate the settings local success = validateparam(params) local errtxt if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(params.value.device_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Device does not exist" end end if success then local sql = "BEGIN TRANSACTION" runsqlcommand(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='"..escape(params.value.device_id.value).."'" runsqlcommand(sql, true) end -- Loop through the groups and params for group,v in pairs(params.value) do if v.type == "group" then for name,param in pairs(v.value) do if editable then sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(params.value.device_id.value).."' AND group_name='"..escape(group).."' AND param_id='"..escape(param.param_id).."'" runsqlcommand(sql, true) end if tostring(param.value) ~= param.default then sql = "INSERT INTO provisioning_values VALUES('"..escape(params.value.device_id.value).."', '"..escape(group).."', '"..escape(param.param_id).."', '"..escape(tostring(param.value)).."')" runsqlcommand(sql, true) end end end end sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) success = false errtxt = err end end if not success then params.errtxt = errtxt or "Failed to save device parameters" end return params end get_device_values_by_name = function(name) local retval = {} retval.device_id = cfe({label="Device ID", seq=1}) retval.name = cfe({value=name or "", label="Name", seq=2}) retval.label = cfe({label="Label", seq=3}) retval.template = cfe({type="select", label="Template", option={}, seq=4}) retval.values = cfe({type="structure", value={}, label="Parameter Values", option={}, seq=5}) local errtxt local res, err = pcall(function() local connected = databaseconnect() if name and name ~= "" then local sql = "SELECT d.device_id, d.name, d.label, t.filename AS template FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE name='"..escape(name).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if retval[n] then retval[n].value = v end end -- Next, get all of the parameter values for this device retval.values = get_device_values(tmp[1].device_id) retval.values.seq = 5 else errtxt = "Invalid device name" end if connected then databasedisconnect() end else errtxt = "Invalid device name" end end) if not res and err then errtxt = err end return cfe({ type="group", value=retval, label="Provisioning Device Parameter Values", errtxt=errtxt }) end get_device_values = function(device_id) local retval = {} local errtxt if device_id and device_id ~= "" then local res, err = pcall(function() local 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 ) ".. "WHERE d2t.device_id='"..escape(device_id).."'" local tmp = getselectresponse(sql) -- Loop through the params and put them into the groups for i,p in ipairs(tmp) do if p.type == "boolean" then p.value = (p.value == "true") end if not retval[p.group] then retval[p.group] = {} end retval[p.group][p.name] = p.value end if connected then databasedisconnect() end end) if not res and err then errtxt = err end else errtxt = "Invalid device id" end return cfe({type="structure", value=retval, label="Parameter Values", errtxt=errtxt}) end search_device_values = function(parameter_id, parameter_value, comparison) local errtxt retval = {} retval.id = cfe({type="select", value=parameter_id or "name", label="Parameter", option={"name", "label", "template_id", "template"}, seq=1}) retval.comparison = cfe({type="select", value=comparison or "=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!*~"}, seq=2}) retval.value = cfe({label="Parameter Value", value=parameter_value, seq=3}) retval.result = cfe({type="structure", value={}, label="List of Devices", seq=4 }) local res, err = pcall(function() local 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) local blankopt = {} local blankexists = {} for i,v in ipairs(tmp) do retval.id.option[#retval.id.option + 1] = v.group_name.."."..v.param_name if not blankexists[v.param_name] then blankopt[#blankopt+1] = "."..v.param_name blankexists[v.param_name] = true end end table.sort(blankopt) for i,o in ipairs(blankopt) do retval.id.option[#retval.id.option + 1] = o end -- Get the devices from the DB if parameter_id and modelfunctions.validateselect(retval.id) and modelfunctions.validateselect(retval.comparison) then parameter_value = parameter_value or "" sql = "SELECT d.device_id, d.name, d.label, d.template_id, " local group, param = string.match(parameter_id or "", "([^%.]*)%.(.*)") if not group then sql = sql.."'"..escape(parameter_id).."' AS param, " if parameter_id == "name" or parameter_id=="label" then sql = sql.."d."..escape(parameter_id).." AS value FROM provisioning_devices d WHERE d."..escape(parameter_id) elseif parameter_id=="template_id" then sql = sql.."d.template_id AS value FROM provisioning_devices d WHERE CAST(d.template_id AS text)" elseif parameter_id=="template" then -- Search by template label sql = sql.."t.label AS value FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE t.label" end sql = sql..retval.comparison.value.."'"..escape(parameter_value).."'" else sql = sql.."g.name as group, p.name as param, 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 ".. "provisioning_devices d JOIN devices_to_classes d2t USING(device_id) 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 ) " if group and group ~= "" then sql = sql.."WHERE g.name='"..escape(group).."' AND" else sql = sql.."WHERE" end sql = sql.." p.name='"..escape(param).."' AND CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END"..retval.comparison.value.."'"..escape(parameter_value).."'" end sql = sql.." ORDER BY d.name ASC, d.label ASC" retval.result.value = getselectresponse(sql) end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({type="group", value=retval, label="Device Search", errtxt=errtxt}) end get_param_options = function(param_id) local retval = {} retval.param_id = cfe({value=param_id or "", label="Parameter ID", seq=0}) retval.name = cfe({label="Name", seq=1}) retval.label = cfe({label="Label", seq=2}) retval.options = cfe({type="list", value={}, label="Value Options", descr="Ordered list of options where each option is made up of 'value' or 'value,label'", seq=3}) local errtxt = "Cannot find parameter" if param_id and param_id ~= "" then local res, err = pcall(function() local connected = databaseconnect() -- First, just check to see if param_id exists local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then errtxt = nil retval.name.value = tmp[1].name retval.label.value = tmp[1].label -- Next, get all of the param options sql = "SELECT * FROM provisioning_options WHERE param_id='"..escape(param_id).."' ORDER BY seq ASC" local tmp = getselectresponse(sql) or {} for i,t in ipairs(tmp) do retval.options.value[#retval.options.value + 1] = t.value..","..t.label end end if connected then databasedisconnect() end end) if not res and err then errtxt = err end end return cfe({ type="group", value=retval, label="Provisioning Parameter Options", errtxt=errtxt }) end set_param_options = function(options) local success = true local errtxt -- Validate the settings --FIXME if success then local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(options.value.param_id.value).."'" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false 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='"..escape(options.value.param_id.value).."'" runsqlcommand(sql, true) -- Loop through the options for i,o in ipairs(options.value.options.value) do local v,l = string.match(o, "^%s*([^,]+),%s*(.*%S)%s*$") if v then v = string.match(v, "^(.*%S)%s*$") else v = string.match(o, "^%s*(.*%S)%s*$") l = v end sql = "INSERT INTO provisioning_options VALUES('"..escape(options.value.param_id.value).."', '"..escape(l).."', '"..escape(v).."', '"..i.."')" runsqlcommand(sql, true) end sql = "COMMIT" runsqlcommand(sql) end if connected then databasedisconnect() end end) if not res and err then pcall(function() con:execute("ROLLBACK") end) success = false errtxt = err end end if not success then options.errtxt = errtxt or "Failed to save options" end return options end