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

8 files changed, 267 insertions, 26 deletions

diff --git a/provisioning-controller.lua b/provisioning-controller.lua
index 60b5e9e..3f05917 100644
--- a/provisioning-controller.lua
+++ b/provisioning-controller.lua
@@ -20,6 +20,22 @@ deletetemplate = function( self )
 	return self:redirect_to_referrer(self.model.delete_template(self.clientdata.template_id))
 end
 
+listclassgroups = function( self )
+	return self.model.list_class_groups()
+end
+
+editclassgroup = function( self )
+	return controllerfunctions.handle_form(self, function() return self.model.get_class_group(self.clientdata.class_group_id) end, self.model.update_class_group, self.clientdata, "Save", "Edit Class Group", "Class Group Saved")
+end
+
+createclassgroup = function( self )
+	return controllerfunctions.handle_form(self, self.model.get_class_group, self.model.create_class_group, self.clientdata, "Create", "Create Class Group", "Parameter Class Created")
+end
+
+deleteclassgroup = function( self )
+	return self:redirect_to_referrer(self.model.delete_class_group(self.clientdata.class_group_id))
+end
+
 listclasses = function( self )
 	return self.model.list_classes()
 end
diff --git a/provisioning-createclassgroup-html.lsp b/provisioning-createclassgroup-html.lsp
new file mode 120000
index 0000000..3751732
--- /dev/null
+++ b/provisioning-createclassgroup-html.lsp
@@ -0,0 +1 @@
+provisioning-editclassgroup-html.lsp
\ No newline at end of file
diff --git a/provisioning-editclassgroup-html.lsp b/provisioning-editclassgroup-html.lsp
new file mode 100644
index 0000000..77c1145
--- /dev/null
+++ b/provisioning-editclassgroup-html.lsp
@@ -0,0 +1,13 @@
+<% local form, viewlibrary, page_info = ... 
+require("viewfunctions")
+%>
+
+<H1><%= html.html_escape(form.label) %></H1>
+<%
+	if page_info.action == "editclassgroup" then
+		form.value.class_group_id.readonly = "true"
+	else
+		form.value.class_group_id.type = "hidden"
+	end
+	displayform(form, nil, nil, page_info, 2)
+%>
diff --git a/provisioning-listclasses-html.lsp b/provisioning-listclasses-html.lsp
index 81a08d3..5aa8a13 100644
--- a/provisioning-listclasses-html.lsp
+++ b/provisioning-listclasses-html.lsp
@@ -19,7 +19,7 @@ require("viewfunctions")
 <TABLE id="list" class="tablesorter"><THEAD>
 	<TR style="background:#eee;font-weight:bold;">
 		<TH>Action</TH>
-		<TH>Name</TH>
+		<TH>Class Group</TH>
 		<TH>Label</TH>
 	</TR>		
 </THEAD><TBODY>
@@ -33,7 +33,7 @@ require("viewfunctions")
 			<%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editclass?class_id="..v.class_id.."&redir="..page_info.orig_action, label="Edit "} %>
 		<% end %>
 		</TD>
-                <TD><%= html.html_escape(v.name) %></TD>
+                <TD><%= html.html_escape(v.group) %></TD>
                 <TD><%= html.html_escape(v.label) %></TD>
 	</TR>
 <% end %>
diff --git a/provisioning-listclassgroups-html.lsp b/provisioning-listclassgroups-html.lsp
new file mode 100644
index 0000000..757e8a1
--- /dev/null
+++ b/provisioning-listclassgroups-html.lsp
@@ -0,0 +1,55 @@
+<% local view, viewlibrary, page_info, session = ... 
+require("viewfunctions")
+%>
+
+<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery-latest.js"></script>
+<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery.tablesorter.js"></script>
+<script type="text/javascript">
+        $(document).ready(function() {
+                $("#list").tablesorter({headers: {0:{sorter: false}}});
+		$(".deletegroup").click(function(){ return confirm("Are you sure you want to delete this group?")});
+        });
+</script>
+
+<% displaycommandresults({"deleteclassgroup", "editclassgroup"}, session) %>
+<% displaycommandresults({"createclassgroup"}, session, true) %>
+
+<h1>Class Groups</h1>
+<DL>
+<TABLE id="list" class="tablesorter"><THEAD>
+	<TR style="background:#eee;font-weight:bold;">
+		<TH>Action</TH>
+		<TH>Name</TH>
+		<TH>Label</TH>
+		<TH>Sequence</TH>
+	</TR>		
+</THEAD><TBODY>
+<% for k,v in ipairs( view.value ) do %>
+	<TR>
+		<TD>
+		<% if viewlibrary.check_permission("deleteclassgroup") then %>
+			<%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deleteclassgroup?class_group_id="..v.class_group_id, label="Delete ", class="deletegroup"} %>
+		<% end %>
+		<% if viewlibrary.check_permission("editclassgroup") then %>
+			<%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editclassgroup?class_group_id="..v.class_group_id.."&redir="..page_info.orig_action, label="Edit "} %>
+		<% end %>
+		</TD>
+                <TD><%= html.html_escape(v.name) %></TD>
+                <TD><%= html.html_escape(v.label) %></TD>
+                <TD><%= html.html_escape(v.seq) %></TD>
+	</TR>
+<% end %>
+</TBODY>
+</TABLE>
+
+<% if view.errtxt then %>
+<p class="error"><%= html.html_escape(view.errtxt) %></p>
+<% end %>
+<% if #view.value == 0 then %>
+<p>No class groups found</p>
+<% end %>
+
+<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createclassgroup") then
+        viewlibrary.dispatch_component("createclassgroup")
+end %>
+</DL>
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
diff --git a/provisioning.menu b/provisioning.menu
index 7c05049..5a4bdf3 100644
--- a/provisioning.menu
+++ b/provisioning.menu
@@ -3,6 +3,7 @@
 Applications	89Provisioning		Search		searchdevices
 Applications	89Provisioning		Devices		listdevices
 Applications	89Provisioning		Templates	listtemplates
+Applications	89Provisioning		Class_Groups	listclassgroups
 Applications	89Provisioning		Classes		listclasses
 Applications	89Provisioning		Param_Groups	listgroups
 Applications	89Provisioning		Params		listparams
diff --git a/provisioning.roles b/provisioning.roles
index 431c98b..dde3064 100644
--- a/provisioning.roles
+++ b/provisioning.roles
@@ -1,5 +1,5 @@
 GUEST=provisioning:getdevicevalues
 USER=
 EDITOR=provisioning:searchdevices,provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:duplicatedevice,provisioning:deletedevice
-EXPERT=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:overridedeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile
-ADMIN=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:searchdevices,provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:duplicatedevice,provisioning:deletedevice,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:editdeviceparams,provisioning:overridedeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile
+EXPERT=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:listclassgroups,provisioning:editclassgroup,provisioning:createclassgroup,provisioning:deleteclassgroup,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:overridedeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile
+ADMIN=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:searchdevices,provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:duplicatedevice,provisioning:deletedevice,provisioning:listclassgroups,provisioning:editclassgroup,provisioning:createclassgroup,provisioning:deleteclassgroup,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:editdeviceparams,provisioning:overridedeviceparams,provisioning:editoptions,provisioning:listfiles,provisioning:editfile