module (..., package.seeall) -- Load libraries require("modelfunctions") require("posix") fs = require("acf.fs") format = require("acf.format") validator = require("acf.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/templates/" local updatedevicescriptfile = "/etc/provisioning/update_device.lua" local updatedeviceparamsscriptfile = "/etc/provisioning/update_device_params.lua" local deletedevicescriptfile = "/etc/provisioning/delete_device.lua" local determineclassscriptfile = "/etc/provisioning/determine_class.lua" local processputscriptfile = "/etc/provisioning/process_put.lua" local scriptfiles = {updatedevicescriptfile, updatedeviceparamsscriptfile, deletedevicescriptfile, determineclassscriptfile, processputscriptfile} local env local con local saved_devices = {} local saved_device_params = {} local table_creation_scripts = require("provisioning/provisioning-scripts") -- ################################################################################ -- LOCAL FUNCTIONS 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 "" 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() cmd = path..'createlang -U postgres plpgsql '..DatabaseName 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 -- Declare runsqlcommand first because it's recursive -- we also have recursion when runsqlcommand calls runscript, so we have to be careful in creating table_creation_scripts local runsqlcommand 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) if con then -- Let's create all the tables from the start for n,v in pairs(table_creation_scripts) do if not string.match(n, "^_") then runsqlcommand("SELECT * FROM "..n.." LIMIT 1") end end end 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, in_transaction) for i,scr in ipairs(script) do runsqlcommand(scr, in_transaction) end end 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, allowdefault) if p.type == "group" then local success = true for n,p2 in pairs(p.value) do success = validateparam(p2, allowdefault) and success end return success elseif allowdefault and p.value == p.default then return true elseif p.type == "select" then return modelfunctions.validateselect(p) elseif p.type == "text" and p.regexp and p.regexp ~= "" then if not string.match(p.value, p.regexp) then p.errtxt = "Invalid value" return false end end return true end -- These are the functions that may be called from within loaded Lua code -- The actual functions are added at the end of the file, after they're declared local functions = {} local validateparamcoded validateparamcoded = function(p, top) top = top or p local success = true if p.type == "group" then for n,p2 in pairs(p.value) do success = validateparamcoded(p2, top) and success end return success end if p.validate and p.validate ~= "" then -- We have Lua validation code local env = {} setmetatable (env, {__index = _G}) -- loadfile loads into the global environment -- so we set env 0, not env 1 setfenv (0, env) local f = loadstring(p.validate) if (f) then local res, err = pcall(function() p.value, p.errtxt = f(p.value, functions, top) if p.errtxt then success = false end end) if not res and err then success = false p.errtxt = "Exception in validate code\n"..err end end setfenv (0, _G) end return success end local function callscript(script, ...) local result local env = {} setmetatable (env, {__index = _G}) -- loadfile loads into the global environment -- so we set env 0, not env 1 setfenv (0, env) local f = loadfile(script) if f then local res, err = pcall(function(...) result = { f(functions, ...) } end, ...) if not res and err then assert(res, "Exception in "..script.." script\n"..err) end end setfenv (0, _G) return unpack(result) end local function validateluacode(code) local success = true -- Validate that contents are valid lua code local env = {} setmetatable (env, {__index = _G}) -- loadfile loads into the global environment -- so we set env 0, not env 1 setfenv (0, env) local f,errtxt = loadstring(code) if not f then success = false end setfenv (0, _G) -- setmetatable (self.conf.app_hooks, {}) return success, errtxt end local function validatefiledetails(filedetails) local success, errtxt = validateluacode(filedetails.value.filecontent.value) if not success then filedetails.value.filecontent.errtxt = "Invalid Lua code\n"..(errtxt or "") end return success, filedetails end local duplicatestructure duplicatestructure = function(value, saved) saved = saved or {} if type(value) == "table" then if saved[value] then return saved[value] else local output = {} saved[value] = output for k,v in pairs(value) do output[k] = duplicatestructure(v, saved) end return output end else return value end 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 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 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 fs.create_directory(baseurl) 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(filename) local retval = {} if filename and not string.match(filename, "/") then filename = baseurl .. filename end retval.filename = cfe({value=filename or "", label="File Name", descr="Must be in "..baseurl, seq=1}) retval.label = cfe({label="Label", seq=2}) retval.seq = cfe({label="Sequence", seq=3}) local errtxt if filename and filename ~= "" then local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(filename).."' ORDER BY seq ASC, label ASC, value ASC" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do if n == "value" then retval.filename.value = v elseif 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 not validator.is_integer(template.value.seq.value) then success = false template.value.seq.errtxt = "Must be an integer" end if success then local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(template.value.filename.value).."'" local tmp = getselectresponse(sql) if not create and (not tmp or #tmp == 0) then success = false errtxt = "Template does not exist" elseif create and #tmp > 0 then success = false errtxt = "Template already exists" end if success then if create then sql = "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='template'), '"..escape(template.value.label.value).."', '"..escape(template.value.filename.value).."', '"..escape(template.value.seq.value).."')" else sql = "UPDATE provisioning_options SET (label, seq) = ('"..escape(template.value.label.value).."', '"..escape(template.value.seq.value).."') WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(template.value.filename.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(filename) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(filename).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Template does not exist" else -- Remove the template sql = "DELETE FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(filename).."'" runsqlcommand(sql) -- Delete the template file os.remove(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_class_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_class_groups ORDER BY seq 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 Class Groups", errtxt=errtxt }) end get_class_group = function(class_group_id) local retval = {} retval.class_group_id = cfe({value=class_group_id or "", label="Class 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}) local errtxt local res, err = pcall(function() local connected = databaseconnect() if class_group_id and class_group_id ~= "" then sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..escape(class_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 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 Group", errtxt=errtxt }) end create_class_group = function(group) return update_class_group(group, true) end update_class_group = function(group, create) local success = true local errtxt -- Validate the settings if group.value.name.value == "" then success = false group.value.name.errtxt = "Cannot be blank" elseif string.find(group.value.name.value, "%W") then success = false group.value.name.errtxt = "Must only contain alphanumeric characters" 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 if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..escape(group.value.class_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_class_groups VALUES(DEFAULT, '"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."')" runsqlcommand(sql, true) sql = "SELECT class_group_id FROM provisioning_class_groups WHERE label='"..escape(group.value.label.value).."'" local tmp = getselectresponse(sql, true) if tmp and #tmp>0 then group.value.class_group_id.value = tmp[1].class_group_id end else sql = "UPDATE provisioning_class_groups SET (name, label, seq) = ('"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."') WHERE class_group_id='"..escape(group.value.class_group_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 group.errtxt = errtxt or "Failed to create class group" else group.errtxt = errtxt or "Failed to save class group" end end return group end delete_class_group = function(class_group_id) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..escape(class_group_id).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Group does not exist" else sql = "DELETE FROM provisioning_class_groups WHERE class_group_id='"..escape(class_group_id).."'" runsqlcommand(sql) result = "Class Group 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 Class Group 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 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 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.class_group_id = cfe({type="select", label="Class Group", option={}, seq=2}) retval.label = cfe({label="Label", seq=3}) retval.seq = cfe({label="Sequence", seq=4}) retval.groups = cfe({type="group", value={}, label="Parameter Groups", seq=5}) 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 -- Get the class_group_id options sql = "SELECT * from provisioning_class_groups ORDER BY seq ASC, label ASC" tmp = getselectresponse(sql) for i,g in ipairs(tmp) do retval.class_group_id.option[#retval.class_group_id.option + 1] = {value=g.class_group_id, label=g.label} 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 success = modelfunctions.validateselect(class.value.class_group_id) and success if class.value.label.value == "" then success = false class.value.label.errtxt = "Cannot be blank" end if not validator.is_integer(class.value.seq.value) then success = false class.value.seq.errtxt = "Must be an integer" 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.class_group_id.value).."', '"..escape(class.value.label.value).."', '"..escape(class.value.seq.value).."')" runsqlcommand(sql, true) sql = "SELECT class_id FROM provisioning_classes WHERE class_group_id='"..escape(class.value.class_group_id.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 (class_group_id, label, seq) = ('"..escape(class.value.class_group_id.value).."', '"..escape(class.value.label.value).."', '"..escape(class.value.seq.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 classes_to_param_groups WHERE class_id='"..escape(class_id).."'" runsqlcommand(sql, true) sql = "DELETE FROM provisioning_classes 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=8}) 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 p.default = p.value 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 p.value then 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 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) and success success = modelfunctions.validatemulti(group.value.editable) and success 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" elseif string.find(group.value.name.value, "%W") then success = false group.value.name.errtxt = "Must only contain alphanumeric characters" 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 for i,p in pairs(group.value.defaults.value) do success = validateparam(p, true) and success end 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).."', " if group.value.defaults.value[p].value ~= group.value.defaults.value[p].default then sql = sql.."'"..escape(tostring(group.value.defaults.value[p].value)).."'" else sql = sql.."null" end sql = sql..", '"..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 param_groups_to_params WHERE group_id='"..escape(group_id).."'" runsqlcommand(sql, true) sql = "DELETE FROM provisioning_groups 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", descr="Warning, this value is not validated", seq=6}) retval.regexp = cfe({label="Regular Expression", descr="Lua regular expression for validating the text parameter value", seq=7}) retval.validate = cfe({type="longtext", label="Validation Code", descr="Lua code to validate the parameter value. Returns updated value and error text. Not used to validate group defaults.", seq=8}) retval.seq = cfe({label="Sequence", seq=9}) 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 local s,e = validateluacode(param.value.validate.value) if not s then success = false param.value.validate.errtxt = "Invalid Lua code" if e and e ~= "" then param.value.validate.errtxt = param.value.validate.errtxt.."\n"..e end 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).."', '"..escape(param.value.regexp.value).."', '"..escape(format.dostounix(param.value.validate.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, regexp, validate) = ('"..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).."', '"..escape(param.value.regexp.value).."', '"..escape(format.dostounix(param.value.validate.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_options WHERE param_id='"..escape(param_id).."'" runsqlcommand(sql, true) 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 device_id, c.label AS class, g.label AS group FROM devices_to_classes d2c JOIN provisioning_classes c USING(class_id) JOIN provisioning_class_groups g USING(class_group_id) ORDER BY device_id" local tmp = getselectresponse(sql) local reverse_device_id = {} for i,d in ipairs(tmp) do if not reverse_device_id[d.device_id] then retval[#retval+1] = {device_id=d.device_id} reverse_device_id[d.device_id] = #retval end retval[reverse_device_id[d.device_id]][d.group] = d.class end 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.classes = cfe({type="group", value={}, label="Classes", seq=2}) local errtxt local res, err = pcall(function() local classes={} local connected = databaseconnect() if device_id and device_id ~= "" then -- Get the device-to-class mappings local sql = "SELECT class_id FROM devices_to_classes WHERE device_id='"..escape(device_id).."'" local tmp = getselectresponse(sql) for i,g in ipairs(tmp) do classes[g.class_id] = true end end -- Finally, get the class options sql = "SELECT class_id, g.name, g.label AS group, 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" 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.group, 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 -- Save the device for later use local output = cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt }) if device_id and device_id ~= "" then saved_devices[device_id] = duplicatestructure(output) end return output end create_device = function(device) return update_device(device, true) end update_device = function(device, create) local success = true local errtxt -- Validate the settings local something = false for n,c in pairs(device.value.classes.value) do success = modelfunctions.validateselect(c) and success if c.value ~= "" then something = true end end if not something then success = false end if success then local res, err = pcall(function() local connected = databaseconnect() if not create then local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."' LIMIT 1" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false device.value.device_id.errtxt = "Device does not exist" end end if success then 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" runsqlcommand(sql) if create then sql = "SELECT nextval('provisioning_device_seq')" local tmp = getselectresponse(sql, true) 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='"..escape(device.value.device_id.value).."'" runsqlcommand(sql, true) -- 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) 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 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 devices_to_classes WHERE device_id='"..escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Device does not exist" else 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='"..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) callscript(deletedevicescriptfile, saved_devices[device_id], saved_device_params[device_id]) saved_devices[device_id] = nil saved_device_params[device_id] = nil 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}) 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 devices_to_classes WHERE device_id='"..escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then errtxt = nil -- 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, g.label AS grouplabel, p.param_id, p.name, p.type, p.label, p.descr, p.seq, p.regexp, p.validate, 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, CASE WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END 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 retval[p.group].label = p.grouplabel local value = retval[p.group].value if p.type == "boolean" then p.value = (p.value == "true") p.default = (p.default == "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 -- Save the device for later use local output = cfe({ type="group", value=retval, label="Provisioning Device Parameters", errtxt=errtxt }) if device_id and device_id ~= "" and not editable then saved_device_params[device_id] = duplicatestructure(output) end return output end get_class_options = function(class_id) local retval = {} retval.class_id = cfe({value=class_id or "", label="Class ID", seq=1}) return cfe({ type="group", value=retval, label="Provisioning Class Values" }) end get_class_values = function(retval) 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() -- First, just check to see if class_id exists local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(retval.value.class_id.value).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then retval.errtxt = nil retval.value.label = cfe({ value=tmp[1].label or "", label="Label", seq=3}) retval.value.values = cfe({type="structure", value={}, label="Parameter Values"}) local sql = "SELECT g.name AS group, p.name, p.type, CASE WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value ".. "FROM (provisioning_classes t 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)) ".. "WHERE t.class_id='"..escape(retval.value.class_id.value).."'" 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.value.values.value[p.group] then retval.value.values.value[p.group] = {} end retval.value.values.value[p.group][p.name] = p.value end else retval.value.class_id.errtxt = "Failed to find Class ID" end if connected then databasedisconnect() end end) if not res and err then retval.errtxt = err end end return retval 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() success = validateparamcoded(params) if not create then local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..escape(params.value.device_id.value).."' LIMIT 1" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false errtxt = "Device does not exist" end end 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" 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 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) local tmp = saved_device_params[params.value.device_id.value] local p = params if not editable then saved_device_params[params.value.device_id.value] = params else p = get_device_params(params.value.device_id.value) end callscript(updatedeviceparamsscriptfile, p, tmp) 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 fetch_device_values = function(search) local res, err = pcall(function() local connected = databaseconnect() search = search_device_values(search) if search.errtxt then -- Just return the error elseif #search.value.result.value == 0 then search.errtxt = "Device not found" elseif #search.value.result.value > 1 then search.errtxt = "Multiple devices found" else search.value.values = get_device_values(search.value.result.value[1].device_id) search.value.values.seq = 5 end if connected then databasedisconnect() end end) if not res and err then search.errtxt = err end search.label="Provisioning Device Parameter Values" return search 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 get_search_options = function() 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() -- 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 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 search_device_values = function(search) local success = true success = modelfunctions.validateselect(search.value.id) and success success = modelfunctions.validateselect(search.value.comparison) and success if search.value.id.value == "device_id" then if string.match(search.value.comparison.value, "~") then success = false search.value.comparison.errtxt = "Cannot do regular expression comparison to device_id" end if not validator.is_integer(search.value.value.value) then success = false search.value.value.errtxt = "Must be an integer for comparison to device_id" end end if success then retval.result = cfe({type="structure", value={}, label="List of Devices", seq=4 }) local res, err = pcall(function() local connected = databaseconnect() -- Get the devices from the DB sql = "SELECT d2t.device_id, " local group, param = string.match(search.value.id.value, "([^%.]*)%.(.*)") if not group then sql = sql.."'"..search.value.id.value.."' AS param, d2t."..search.value.id.value.." AS value FROM devices_to_classes d2t WHERE d2t."..search.value.id.value.. search.value.comparison.value.."'"..escape(search.value.value.value).."' GROUP BY device_id" 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 ".. "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 ) " 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"..search.value.comparison.value.."'"..escape(search.value.value.value).."'" end sql = sql.." ORDER BY d2t.device_id ASC" search.value.result.value = getselectresponse(sql) if connected then databasedisconnect() end end) if not res and err then search.errtxt = err end else search.errtxt = "Search failed" end return search 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 function get_filedetails(filename) return modelfunctions.getfiledetails(filename, scriptfiles) end function update_filedetails(filedetails) return modelfunctions.setfiledetails(filedetails, scriptfiles) end function list_files() local retval = {} for i,file in ipairs(scriptfiles) do local details = fs.stat(file) or {} details.filename = file table.insert(retval, details) end table.sort(retval, function(a,b) return a.filename < b.filename end) return cfe({ type="structure", value=retval, label="List of Provisioning Script files" }) end function get_database() local devices = cfe({ type="boolean", value=false, label="Include Device Data" }) return cfe({ type="group", value={devices=devices}, label="Provisioning Database Dump" }) end function dump_database(db) local res, err = pcall(function() local connected = databaseconnect() local lines = {} local sql = "SELECT name, label, seq FROM provisioning_class_groups ORDER BY name, label" local tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO provisioning_class_groups VALUES(default, '"..escape(t.name).."', '"..escape(t.label).."', '"..escape(t.seq).."');" end sql = "SELECT g.name AS group, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.name, c.label" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='"..escape(t.group).."'), '"..escape(t.label).."', '"..escape(t.seq).."');" end sql = "SELECT name, label, seq FROM provisioning_groups ORDER BY name, label" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO provisioning_groups VALUES(default, '"..escape(t.name).."', '"..escape(t.label).."', '"..escape(t.seq).."');" end sql = "SELECT name, type, label, descr, value, seq, regexp, validate FROM provisioning_params ORDER BY name" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO provisioning_params VALUES(default, '"..escape(t.name).."', '"..escape(t.type).."', '"..escape(t.label).."', '"..escape(t.descr).."', '"..escape(t.value).."', '"..escape(t.seq).."', '"..escape(t.regexp).."', '"..escape(t.validate).."');" end sql = "SELECT p.name AS param, o.label, o.value, o.seq FROM provisioning_options o JOIN provisioning_params p USING(param_id) ORDER BY p.name, o.seq, o.label" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='"..escape(t.param).."'), '"..escape(t.label).."', '"..escape(t.value).."', '"..escape(t.seq).."');" end sql = "SELECT c.label AS class, g.label AS group FROM provisioning_classes c JOIN classes_to_param_groups USING(class_id) JOIN provisioning_groups g USING(group_id) ORDER BY c.label, g.label" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='"..escape(t.class).."'), (SELECT group_id FROM provisioning_groups WHERE label='"..escape(t.group).."'));" end sql = "SELECT p.name AS param, g.label AS group, t.value, t.editable FROM provisioning_params p JOIN param_groups_to_params t USING(param_id) JOIN provisioning_groups g USING(group_id) ORDER BY p.name, g.label" tmp = getselectresponse(sql) for i,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='"..escape(t.group).."'), (SELECT param_id FROM provisioning_params WHERE name='"..escape(t.param).."'), '"..escape(t.value).."', '"..escape(t.editable).."');" end if db.value.devices and db.value.devices.value then sql = "SELECT device_id FROM devices_to_classes GROUP BY device_id ORDER BY device_id ASC" devices = getselectresponse(sql) for i,d in ipairs(devices) do sql = "SELECT label FROM devices_to_classes JOIN provisioning_classes USING(class_id) WHERE device_id='"..escape(d.device_id).."'" tmp = getselectresponse(sql) for j,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO devices_to_classes VALUES("..i..", (SELECT class_id FROM provisioning_classes WHERE label='"..escape(t.label).."'));" end sql = "SELECT group_name, p.name AS param, v.value FROM provisioning_values v JOIN provisioning_params p USING(param_id) WHERE device_id='"..escape(d.device_id).."'" tmp = getselectresponse(sql) for j,t in ipairs(tmp) do lines[#lines+1] = "INSERT INTO provisioning_values VALUES("..i..", '"..escape(t.group_name).."', (SELECT param_id FROM provisioning_params WHERE name='"..escape(t.param).."'), '"..t.value.."');" end end lines[#lines+1] = "SELECT pg_catalog.setval('provisioning_device_seq', "..#devices..", true);" end db.value.data = cfe({ type="longtext", value=table.concat(lines, "\n") or "", label="Database Values" }) if connected then databasedisconnect() end end) if not res and err then db.errtxt = err end return db end function get_file(mac, ip, agent) local result = cfe() -- Error if there's no mac if not mac or mac == "" then result.errtxt = "No MAC Address" return result end local res, err = pcall(function() local connected = databaseconnect() -- Add the device to the table of requests local sql = "SELECT * FROM provisioning_requests WHERE mac='"..escape(string.upper(mac)).."'" local requests = getselectresponse(sql) if requests and #requests > 0 then sql = "UPDATE provisioning_requests SET ip='"..escape(ip).."', agent='"..escape(agent).."', date=now() WHERE mac='"..escape(string.upper(mac)).."'" else sql = "INSERT INTO provisioning_requests VALUES('"..escape(string.upper(mac)).."', '"..escape(ip).."', '"..escape(agent).."', now())" end runsqlcommand(sql) -- Now, let's see if this device exists result = get_search_options() result.value.id.value = "device.mac" result.value.value.value = string.upper(mac) result = fetch_device_values(result) -- If can't find the device, return a file with default settings if #result.value.result.value == 0 then -- Determine which class to use (need a class that specifies a template) local c = list_classes() local class = callscript(determineclassscriptfile, agent, c) if class then local options = get_class_options() options.value.class_id.value = class result = get_class_values(options) else result.errtxt = "Unknown device" end end if connected then databasedisconnect() end end) if not res and err then result.errtxt = err end return result end function put_file(mac, data) local retval = cfe({ label="PUT Data" }) -- Error if there's no mac if not mac or mac == "" then retval.errtxt = "No MAC Address" return retval end local res, err = pcall(function() local connected = databaseconnect() -- Now, let's see if this device exists local search = get_search_options() search.value.id.value = "device.mac" search.value.value.value = string.upper(mac) search = search_device_values(search) if search.errtxt then retval.errtxt = search.errtxt elseif #search.value.result.value == 0 then retval.errtxt = "Device not found" elseif #search.value.result.value > 1 then retval.errtxt = "Multiple devices found" else retval.value, retval.errtxt = callscript(processputscriptfile, mac, data, search.value.result.value[1].device_id) -- If the script doesn't exist, allow the write retval.value = retval.value or data end if connected then databasedisconnect() end end) if not res and err then retval.errtxt = err end return retval end -- The functions must be added after they're declared functions = { getselectresponse=getselectresponse, runsqlcommand=runsqlcommand, get_device=get_device, get_device_params=get_device_params, set_device_params=set_device_params, } list_requests = function() local retval = {} local errtxt -- Get the templates from the DB local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_requests ORDER BY date DESC" retval = getselectresponse(sql) -- Get the corresponding device_id's for each request sql = "SELECT * FROM provisioning_values WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name='mac')" local ids = getselectresponse(sql) local reverseids = {} for i,v in ipairs(ids) do reverseids[v.value] = v.device_id end for i,v in ipairs(retval) do v.device_id = reverseids[v.mac] end if connected then databasedisconnect() end end) if not res and err then errtxt = err end return cfe({ type="structure", value=retval, label="List of Requests", errtxt=errtxt }) end delete_request = function(mac) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_requests WHERE mac='"..escape(string.upper(mac)).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Request does not exist" else -- Remove the request sql = "DELETE FROM provisioning_requests WHERE mac='"..escape(string.upper(mac)).."'" runsqlcommand(sql) result = "Request 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 Request Result" }) end get_request = function(mac) local retval = {} retval.mac = cfe({ value=mac or "", label="MAC Address" }) return cfe({ type="group", value=retval, label="Create Device from Request" }) end create_from_request = function(request) success = false local res, err = pcall(function() local connected = databaseconnect() local sql = "SELECT * FROM provisioning_requests WHERE mac='"..escape(string.upper(request.value.mac.value)).."'" local tmp = getselectresponse(sql) if #tmp == 0 then request.value.mac.errtxt = "Request does not exist" else -- Determine which class to use (need a class that specifies a template) local c = list_classes() local class, group = callscript(determineclassscriptfile, tmp[1].agent, c) -- Create the device local device = get_device() if class and group and device.value.classes.value[group] then device.value.classes.value[group].value = class end device = create_device(device) if device.errtxt then request.errtxt = device.errtxt else local params = get_device_params(device.value.device_id.value) -- Set the MAC Address if params.value.device and params.value.device.value.mac then params.value.device.value.mac.value = string.upper(request.value.mac.value) params = set_device_params(params) end if params.errtxt then request.errtxt = params.errtxt delete_device(device.value.device_id.value) else success = true request.value.device_id = device.value.device_id end end end if connected then databasedisconnect() end end) if not res and err then request.errtxt = err end if not success and not request.errtxt then request.errtxt = "Failed to create device" end return request end