Cleaned up some things with ordering and with transaction rollback.

Also removed unique restriction from class label.

1 files changed, 15 insertions, 12 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index 8a51e31..fa2ddad 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -34,8 +34,9 @@ local table_creation_scripts = {
 		"CREATE TABLE devices_to_classes (device_id INTEGER, class_id INTEGER)",
 	},
 	-- List of different device classes
+-- Need to enforce that name/label combo is unique
 	provisioning_classes = {
-		"CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE)",
+		"CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255))",
 	},
 	-- Multi-to-multi mapping of classes to parameter groups
 -- Need to enforce that classes do not contain more than one parameter group with same name (using triggers)
@@ -214,7 +215,6 @@ logevent(err)
 			runscript(table_creation_scripts[table])
 			runsqlcommand(sql)
 		else
-			if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end
 			assert(res, err)
 		end
 	else
@@ -248,7 +248,6 @@ logevent(err)
 			runscript(table_creation_scripts[table])
 			return getselectresponse(sql)
 		else
-			if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end
 			assert(res, err)
 		end
 	else
@@ -266,7 +265,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 template_id ASC"
+		local sql = "SELECT * FROM provisioning_templates ORDER BY label ASC, filename ASC"
 		retval = getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
@@ -307,7 +306,7 @@ get_template = function(template_id, filename)
 	if template_id and template_id ~= "" 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"
+			local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template_id).."' ORDER BY label ASC, filename ASC"
 			local tmp = getselectresponse(sql)
 			if tmp and #tmp > 0 then
 				for n,v in pairs(tmp[1]) do
@@ -419,7 +418,7 @@ list_classes = function()
 	-- Get the classes from the DB
         local res, err = pcall(function()
 		local connected = databaseconnect()
-		local sql = "SELECT * FROM provisioning_classes ORDER BY class_id ASC"
+		local sql = "SELECT * FROM provisioning_classes ORDER BY name ASC, label ASC"
 		retval = getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
@@ -458,11 +457,11 @@ get_class = function(class_id)
 			end
 		end
 		-- Finally, get the paramgroup options
-		sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY label ASC"
+		sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY seq 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=""}}})
+				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}
@@ -578,6 +577,7 @@ delete_class = function(class_id)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		pcall(function() con:execute("ROLLBACK") end)
 		errtxt = err
 	end
 
@@ -763,6 +763,7 @@ delete_group = function(group_id)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		pcall(function() con:execute("ROLLBACK") end)
 		errtxt = err
 	end
 
@@ -909,6 +910,7 @@ delete_param = function(param_id)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		pcall(function() con:execute("ROLLBACK") end)
 		errtxt = err
 	end
 
@@ -921,7 +923,7 @@ list_devices = function()
 	-- Get the devices from the DB
         local res, err = pcall(function()
 		local connected = databaseconnect()
-		local sql = "SELECT * FROM provisioning_devices ORDER BY name ASC, label ASC"
+		local sql = "SELECT * FROM provisioning_devices WHERE name IS NOT NULL ORDER BY name ASC, label ASC"
 		retval = getselectresponse(sql)
 		if connected then databasedisconnect() end
 	end)
@@ -961,13 +963,13 @@ get_device = function(device_id)
 			end
 		end
 		-- Next, get the template options
-		sql = "SELECT template_id, label FROM provisioning_templates ORDER BY template_id ASC"
+		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 * FROM provisioning_classes ORDER BY class_id ASC"
+		sql = "SELECT * FROM provisioning_classes ORDER BY name ASC, label ASC"
 		tmp = getselectresponse(sql)
 		for i,c in ipairs(tmp) do
 			if not retval.classes.value[c.name] then
@@ -1084,6 +1086,7 @@ delete_device = function(device_id)
 		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		pcall(function() con:execute("ROLLBACK") end)
 		errtxt = err
 	end
 
@@ -1236,7 +1239,7 @@ get_device_values = function(name)
 				
 				-- Next, get all of the parameters for this device
 				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) "..
+					"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(tmp[1].device_id).."'"
 				local tmp = getselectresponse(sql)