diff options
-rw-r--r-- | provisioning-model.lua | 58 |
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 |