Added concept of class groups to allow separate name and label for class groups

Added four new actions - listclassgroups, editclassgroup, createclassgroup, and deleteclassgroup
Added new provisioning_class_groups table
Changed provisioning_classes to change name to class_group_id plus added seq field
Modified groups to only accept alphanumeric names and display param group label instead of name when editing params

1 files changed, 177 insertions, 22 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index be12903..5567ed5 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -42,26 +42,30 @@ local table_creation_scripts = {
 		"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 with same name (using triggers)
+		-- 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_names() RETURNS TRIGGER AS $$ \
+		"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 name=(select name from provisioning_classes where class_id=NEW.class_id); \
+				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 with the same name'; \
+					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_names()",
+		"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), label VARCHAR(255) UNIQUE, seq INTEGER)",
 	},
 	-- List of different device classes
 	provisioning_classes = {
-		"CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255))",
-		"CREATE INDEX classes_name_idx ON provisioning_classes (name)",
+		"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 name/label combo is unique
-		"CREATE UNIQUE INDEX classes_name_label_idx ON provisioning_classes (name, 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 = {
@@ -527,13 +531,153 @@ delete_template = function(template_id)
 	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 * FROM provisioning_classes ORDER BY name ASC, label ASC"
+		local sql = "SELECT class_id, 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.label ASC"
 		retval = getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
@@ -547,9 +691,10 @@ end
 get_class = function(class_id)
 	local retval = {}
 	retval.class_id = cfe({value=class_id or "", label="Class ID", seq=1})
-	retval.name = cfe({label="Name", seq=2})
+	retval.class_group_id = cfe({type="select", label="Class Group", option={}, seq=2})
 	retval.label = cfe({label="Label", seq=3})
-	retval.groups = cfe({type="group", value={}, label="Parameter Groups", seq=4})
+	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 = {}
@@ -571,6 +716,12 @@ get_class = function(class_id)
 				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)
@@ -601,14 +752,15 @@ update_class = function(class, create)
 	local success = true
 	local errtxt
 	-- Validate the settings
-	if class.value.name.value == "" then
-		success = false
-		class.value.name.errtxt = "Cannot be blank"
-	end
+	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
@@ -627,15 +779,15 @@ update_class = function(class, create)
 				local sql = "BEGIN TRANSACTION"
 				runsqlcommand(sql)
 				if create then
-					sql = "INSERT INTO provisioning_classes VALUES(DEFAULT, '"..escape(class.value.name.value).."', '"..escape(class.value.label.value).."')"
+					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 name='"..escape(class.value.name.value).."' AND label='"..escape(class.value.label.value).."'"
+					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 (name, label) = ('"..escape(class.value.name.value).."', '"..escape(class.value.label.value).."') WHERE class_id='"..escape(class.value.class_id.value).."'"
+					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)
@@ -794,6 +946,9 @@ update_group = function(group, create)
 	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
@@ -1097,11 +1252,11 @@ get_device = function(device_id)
 			retval.template_id.option[#retval.template_id.option + 1] = {value=t.template_id, label=t.label}
 		end
 		-- Finally, get the class options
-		sql = "SELECT * FROM provisioning_classes ORDER BY name ASC, label ASC"
+		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.label ASC"
 		tmp = getselectresponse(sql)
 		for i,c in ipairs(tmp) do
 			if not retval.classes.value[c.name] then
-				retval.classes.value[c.name] = cfe({type="select", label=c.name, option={{value="", label=""}}, seq=i})
+				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}
@@ -1266,7 +1421,7 @@ get_device_params = function(device_id, editable)
 				-- 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].label = g.label
 					retval[g.name].type="group"
 				end
 				-- Then, get all of the parameters for this device