Added in some plpgsql triggers for data validation on inserts plus had to make runscript recursive.v0.0.1

1 files changed, 50 insertions, 8 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index 8950055..143fdc9 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -19,6 +19,7 @@ local baseurl = "/etc/provisioning/"
 local env
 local con
 
+-- 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 = {
@@ -31,11 +32,22 @@ local table_creation_scripts = {
 		"CREATE INDEX devices_template_idx ON provisioning_devices (template_id)",
 	},
 	-- Multi-to-multi mapping of devices to classes
--- Need to enforce that devices do not contain more than one class with same name (using triggers)
 	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 with same name (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 $$ \
+			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); \
+				IF FOUND THEN \
+					RAISE EXCEPTION 'Device cannot have multiple classes with the same name'; \
+				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()",
 	},
 	-- List of different device classes
 	provisioning_classes = {
@@ -46,11 +58,22 @@ local table_creation_scripts = {
 		"CREATE UNIQUE INDEX classes_name_label_idx ON provisioning_classes (name, label)",
 	},
 	-- 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)
 	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 = {
@@ -72,12 +95,24 @@ local table_creation_scripts = {
 	-- 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
--- 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)
 		"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 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 = {
@@ -127,7 +162,12 @@ local createdatabase = function()
 	local f = io.popen(cmd)
 	table.insert(result, f:read("*a"))
 	f:close()
-	
+
+	cmd = path..'createlang -U postgres plpgsql '..DatabaseName
+	f = io.popen(cmd)
+	table.insert(result, f:read("*a"))
+	f:close()
+
 	logevent(table.concat(result, "\n"))
 
 	return table.concat(result, "\n")
@@ -182,14 +222,16 @@ local databasedisconnect = function()
 	end
 end
 
-local runscript = function(script)
+-- 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
-		logevent(scr)
-		assert( con:execute(scr) )
+		runsqlcommand(scr, in_transaction)
 	end
 end
 
-local runsqlcommand
 runsqlcommand = function(sql, in_transaction)
 logevent(sql)
 	if in_transaction then assert(con:execute("SAVEPOINT before_command")) end