From e839d6e272fb8481d18063a75d6e63a2409c82c7 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Sat, 26 Feb 2011 08:58:29 +0000 Subject: Removed template from device and set it up as a parameter instead, moved scripts into privisioning-scripts.lua, and added DB values for all tables to set up phone provisioning --- provisioning-model.lua | 198 ++++++++++++------------------------------------- 1 file changed, 46 insertions(+), 152 deletions(-) (limited to 'provisioning-model.lua') diff --git a/provisioning-model.lua b/provisioning-model.lua index e9f493c..041d64e 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -25,113 +25,7 @@ local con local saved_devices = {} local saved_device_params = {} --- if a table_creation_script does not create the named table or throw an exception then you will get an infinite loop, so be careful -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 REFERENCES provisioning_templates)", - "CREATE INDEX devices_name_idx ON provisioning_devices (name)", - "CREATE INDEX devices_template_idx ON provisioning_devices (template_id)", - }, - -- Multi-to-multi mapping of devices to classes - devices_to_classes = { - "CREATE TABLE devices_to_classes (device_id INTEGER REFERENCES provisioning_devices, class_id INTEGER REFERENCES provisioning_classes)", - "CREATE INDEX d2c_device_idx ON devices_to_classes (device_id)", - "CREATE INDEX d2c_class_idx ON devices_to_classes (class_id)", - -- Need to enforce that devices do not contain more than one class in the same class group (using triggers) - -- Theoretically should also check on updates and updates of classes, but not going to bother - "CREATE OR REPLACE FUNCTION check_class_groups() RETURNS TRIGGER AS $$ \ - BEGIN \ - PERFORM * from provisioning_classes join devices_to_classes using(class_id) where device_id=NEW.device_id and class_group_id=(select class_group_id from provisioning_classes where class_id=NEW.class_id); \ - IF FOUND THEN \ - RAISE EXCEPTION 'Device cannot have multiple classes within the same class group'; \ - END IF; \ - RETURN NEW; \ - END; \ - $$ LANGUAGE plpgsql", - "CREATE TRIGGER check_class_trigger BEFORE INSERT ON devices_to_classes FOR EACH ROW EXECUTE PROCEDURE check_class_groups()", - }, - -- List of different class groups - provisioning_class_groups = { - "CREATE TABLE provisioning_class_groups (class_group_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE, seq INTEGER)", - }, - -- List of different device classes - provisioning_classes = { - "CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, class_group_id INTEGER REFERENCES provisioning_class_groups, label VARCHAR(255), seq INTEGER)", - "CREATE INDEX classes_group_idx ON provisioning_classes (class_group_id)", - "CREATE INDEX classes_label_idx ON provisioning_classes (label)", - -- Need to enforce that group/label combo is unique - "CREATE UNIQUE INDEX classes_group_label_idx ON provisioning_classes (class_group_id, label)", - }, - -- Multi-to-multi mapping of classes to parameter groups - classes_to_param_groups = { - "CREATE TABLE classes_to_param_groups (class_id INTEGER REFERENCES provisioning_classes, group_id INTEGER REFERENCES provisioning_groups)", - "CREATE INDEX c2g_class_idx ON classes_to_param_groups (class_id)", - "CREATE INDEX c2g_group_idx ON classes_to_param_groups (group_id)", - -- Need to enforce that classes do not contain more than one parameter group with same name (using triggers) - -- Theoretically should also check on updates and updates of groups, but not going to bother - "CREATE OR REPLACE FUNCTION check_group_names() RETURNS TRIGGER AS $$ \ - BEGIN \ - PERFORM * from provisioning_groups join classes_to_param_groups using(group_id) where class_id=NEW.class_id and name=(select name from provisioning_groups where group_id=NEW.group_id); \ - IF FOUND THEN \ - RAISE EXCEPTION 'Classes cannot have multiple parameter groups with the same name'; \ - END IF; \ - RETURN NEW; \ - END; \ - $$ LANGUAGE plpgsql", - "CREATE TRIGGER check_group_trigger BEFORE INSERT ON classes_to_param_groups FOR EACH ROW EXECUTE PROCEDURE check_group_names()", - }, - -- 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)", - "CREATE INDEX groups_name_idx ON provisioning_groups (name)", - }, - -- Multi-to-multi mapping of parameter groups to parameters - param_groups_to_params = { - "CREATE TABLE param_groups_to_params (group_id INTEGER REFERENCES provisioning_groups, param_id INTEGER REFERENCES provisioning_params, value VARCHAR(255), editable BOOLEAN)", - "CREATE INDEX g2p_group_idx ON param_groups_to_params (group_id)", - "CREATE INDEX g2p_param_idx ON param_groups_to_params (param_id)", - "CREATE INDEX g2p_editable_idx ON param_groups_to_params (editable)", - "CREATE UNIQUE INDEX g2p_group_param_idx ON param_groups_to_params (group_id, param_id)", - }, - -- 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, regexp VARCHAR(255))", - "CREATE INDEX params_name_idx ON provisioning_params (name)", - }, - -- 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 REFERENCES provisioning_devices, group_name VARCHAR(255), param_id INTEGER REFERENCES provisioning_params, value VARCHAR(255))", - "CREATE INDEX values_device_idx ON provisioning_values (device_id)", - "CREATE INDEX values_group_idx ON provisioning_values (group_name)", - "CREATE INDEX values_param_idx ON provisioning_values (param_id)", - "CREATE UNIQUE INDEX values_device_group_param_idx ON provisioning_values (device_id, group_name, param_id)", - -- Need to enforce that group_name is a valid name in provisioning_groups (cannot use foreign key because name is not unique in provisioning_groups) - -- Better yet, we'll check for a valid combination of device, group, and param (using triggers) - -- Theoretically should also check on updates and updates of devices, groups, or parameters, but not going to bother - "CREATE OR REPLACE FUNCTION check_valid_param() RETURNS TRIGGER AS $$ \ - BEGIN \ - PERFORM * from devices_to_classes d join provisioning_classes using(class_id) join classes_to_param_groups using(class_id) join provisioning_groups g using(group_id) join param_groups_to_params p using(group_id) where d.device_id=NEW.device_id and g.name=NEW.group_name and p.param_id=NEW.param_id; \ - IF NOT FOUND THEN \ - RAISE EXCEPTION 'Invalid combination of device, group, and parameter'; \ - END IF; \ - RETURN NEW; \ - END; \ - $$ LANGUAGE plpgsql", - "CREATE TRIGGER valid_param_trigger BEFORE INSERT ON provisioning_values FOR EACH ROW EXECUTE PROCEDURE check_valid_param()", - }, - -- List of options for parameters - provisioning_options = { - "CREATE TABLE provisioning_options (param_id INTEGER REFERENCES provisioning_params, label VARCHAR(255), value VARCHAR(255), seq INTEGER)", - "CREATE INDEX options_param_idx ON provisioning_options (param_id)", - "CREATE UNIQUE INDEX options_param_label_idx ON provisioning_options (param_id, label)", - }, -} +local table_creation_scripts = require("provisioning/provisioning-scripts") -- ################################################################################ -- LOCAL FUNCTIONS @@ -156,7 +50,6 @@ end local createdatabase = function() local result = {} - -- First, create the user if DatabaseUser ~= "postgres" then local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'' @@ -206,6 +99,10 @@ local deletedatabase = function() 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 @@ -216,6 +113,14 @@ local databaseconnect = function() 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 @@ -234,10 +139,6 @@ local databasedisconnect = function() end 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 runscript = function(script, in_transaction) for i,scr in ipairs(script) do runsqlcommand(scr, in_transaction) @@ -383,7 +284,7 @@ list_templates = function() -- 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" + 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) @@ -413,23 +314,25 @@ list_templates = function() return cfe({ type="structure", value=retval, label="List of Templates", errtxt=errtxt }) end -get_template = function(template_id, filename) +get_template = function(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"}) + 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 template_id and template_id ~= "" then + if filename and filename ~= "" 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 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 retval[n] then + if n == "value" then + retval.filename.value = v + elseif retval[n] then retval[n].value = v end end @@ -467,22 +370,27 @@ update_template = function(template, create) 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() - 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 + 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_templates VALUES(DEFAULT, '"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."')" + 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_templates SET (filename, label) = ('"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."') WHERE template_id='"..escape(template.value.template_id.value).."'" + 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) @@ -505,21 +413,21 @@ update_template = function(template, create) return template end -delete_template = function(template_id) +delete_template = function(filename) 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 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_templates WHERE template_id='"..escape(template_id).."'" + 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(tmp[1].filename) + os.remove(filename) result = "Template Deleted" end if connected then databasedisconnect() end @@ -1222,7 +1130,6 @@ get_device = function(device_id) 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() @@ -1245,12 +1152,6 @@ get_device = function(device_id) 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 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) @@ -1284,7 +1185,6 @@ 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" @@ -1310,7 +1210,7 @@ update_device = function(device, create) 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).."')" + sql = "INSERT INTO provisioning_devices VALUES(DEFAULT, '"..escape(device.value.name.value).."', '"..escape(device.value.label.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) @@ -1318,7 +1218,7 @@ update_device = function(device, create) 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).."'" + sql = "UPDATE provisioning_devices SET (name, label) = ('"..escape(device.value.name.value).."', '"..escape(device.value.label.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) @@ -1547,13 +1447,12 @@ get_device_values_by_name = function(name) 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 sql = "SELECT * FROM provisioning_devices WHERE name='"..escape(name).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do @@ -1616,7 +1515,7 @@ 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.id = cfe({type="select", value=parameter_id or "name", label="Parameter", option={"name", "label"}, seq=1}) retval.comparison = cfe({type="select", value=comparison or "=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!*~"}, seq=2}) retval.value = cfe({label="Parameter Value", value=parameter_value, descr="Parameter value or SQL regular expression", seq=3}) retval.result = cfe({type="structure", value={}, label="List of Devices", seq=4 }) @@ -1641,17 +1540,12 @@ search_device_values = function(parameter_id, parameter_value, comparison) -- 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, " + sql = "SELECT d.device_id, d.name, d.label, " 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 -- cgit v1.2.3