Added foreign key references to table definitions.

1 files changed, 10 insertions, 8 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index 962da67..8950055 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -26,14 +26,14 @@ local table_creation_scripts = {
 	},
 	-- List of each device that we manage
 	provisioning_devices = {
-		"CREATE TABLE provisioning_devices (device_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255), template_id INTEGER)",
+		"CREATE TABLE provisioning_devices (device_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255), template_id INTEGER REFERENCES provisioning_templates)",
 		"CREATE INDEX devices_name_idx ON provisioning_devices (name)",
 		"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, class_id INTEGER)",
+		"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)",
 	},
@@ -48,7 +48,7 @@ local table_creation_scripts = {
 	-- 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, group_id INTEGER)",
+		"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)",
 	},
@@ -59,7 +59,7 @@ local table_creation_scripts = {
 	},
 	-- Multi-to-multi mapping of parameter groups to parameters
 	param_groups_to_params = {
-		"CREATE TABLE param_groups_to_params (group_id INTEGER, param_id INTEGER, value VARCHAR(255), editable BOOLEAN)",
+		"CREATE TABLE param_groups_to_params (group_id INTEGER REFERENCES provisioning_groups, param_id INTEGER REFERENCES provisioning_params, value VARCHAR(255), editable BOOLEAN)",
 		"CREATE INDEX g2p_group_idx ON param_groups_to_params (group_id)",
 		"CREATE INDEX g2p_param_idx ON param_groups_to_params (param_id)",
 		"CREATE INDEX g2p_editable_idx ON param_groups_to_params (editable)",
@@ -72,7 +72,8 @@ 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
-		"CREATE TABLE provisioning_values (device_id INTEGER, group_name VARCHAR(255), param_id INTEGER, value VARCHAR(255))",
+-- 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)",
@@ -80,10 +81,9 @@ local table_creation_scripts = {
 	},
 	-- List of options for parameters
 	provisioning_options = {
-		"CREATE TABLE provisioning_options (param_id INTEGER, label VARCHAR(255), value VARCHAR(255), seq INTEGER)",
+		"CREATE TABLE provisioning_options (param_id INTEGER REFERENCES provisioning_params, label VARCHAR(255), value VARCHAR(255), seq INTEGER)",
 		"CREATE INDEX options_param_idx ON provisioning_options (param_id)",
 	},
--- Where do we define the triggers to enforce the foreign keys? Triggers are only on updates, so don't have to worry about read speed
 }
 
 -- ################################################################################
@@ -1096,10 +1096,12 @@ delete_device = function(device_id)
 		else
 			sql = "BEGIN TRANSACTION"
 			runsqlcommand(sql)
-			sql = "DELETE FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
+			sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(device_id).."'"
 			runsqlcommand(sql, true)
 			sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device_id).."'"
 			runsqlcommand(sql, true)
+			sql = "DELETE FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
+			runsqlcommand(sql, true)
 			result = "Device Deleted"
 			sql = "COMMIT"
 			runsqlcommand(sql)