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

1 files changed, 46 insertions, 152 deletions

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