diff options
| -rw-r--r-- | provisioning-controller.lua | 6 | ||||
| -rw-r--r-- | provisioning-editdevice-html.lsp | 1 | ||||
| -rw-r--r-- | provisioning-edittemplate-html.lsp | 3 | ||||
| -rw-r--r-- | provisioning-getdevicevalues-html.lsp | 1 | ||||
| -rw-r--r-- | provisioning-getdevicevalues-templated.lsp | 4 | ||||
| -rw-r--r-- | provisioning-listtemplates-html.lsp | 12 | ||||
| -rw-r--r-- | provisioning-model.lua | 198 | ||||
| -rwxr-xr-x | provisioning-scripts.lua | 403 |
8 files changed, 462 insertions, 166 deletions
diff --git a/provisioning-controller.lua b/provisioning-controller.lua index 3f05917..ab8dc2b 100644 --- a/provisioning-controller.lua +++ b/provisioning-controller.lua @@ -9,15 +9,15 @@ listtemplates = function( self ) end edittemplate = function( self ) - return controllerfunctions.handle_form(self, function() return self.model.get_template(self.clientdata.template_id) end, self.model.update_template, self.clientdata, "Save", "Edit Template", "Template Saved") + return controllerfunctions.handle_form(self, function() return self.model.get_template(self.clientdata.filename) end, self.model.update_template, self.clientdata, "Save", "Edit Template", "Template Saved") end createtemplate = function( self ) - return controllerfunctions.handle_form(self, function() return self.model.get_template(nil, self.clientdata.filename) end, self.model.create_template, self.clientdata, "Create", "Create Template", "Template Created") + return controllerfunctions.handle_form(self, function() return self.model.get_template(self.clientdata.filename) end, self.model.create_template, self.clientdata, "Create", "Create Template", "Template Created") end deletetemplate = function( self ) - return self:redirect_to_referrer(self.model.delete_template(self.clientdata.template_id)) + return self:redirect_to_referrer(self.model.delete_template(self.clientdata.filename)) end listclassgroups = function( self ) diff --git a/provisioning-editdevice-html.lsp b/provisioning-editdevice-html.lsp index 829d1f7..fd6ad40 100644 --- a/provisioning-editdevice-html.lsp +++ b/provisioning-editdevice-html.lsp @@ -7,7 +7,6 @@ require("viewfunctions") if page_info.action == "editdevice" then form.value.device_id.readonly = "true" elseif page_info.action == "duplicatedevice" then - form.value.template_id = nil form.value.classes = nil else form.value.device_id.type = "hidden" diff --git a/provisioning-edittemplate-html.lsp b/provisioning-edittemplate-html.lsp index df3b4d1..8e6d54c 100644 --- a/provisioning-edittemplate-html.lsp +++ b/provisioning-edittemplate-html.lsp @@ -7,12 +7,11 @@ <% displayformstart(form) %> <% if page_info.action == "edittemplate" then - form.value.template_id.readonly = "true" form.value.filename.readonly = "true" - displayformitem(form.value.template_id, "template_id") end displayformitem(form.value.filename, "filename") displayformitem(form.value.label, "label") +displayformitem(form.value.seq, "seq") %> </DL> diff --git a/provisioning-getdevicevalues-html.lsp b/provisioning-getdevicevalues-html.lsp index c942a5c..cb1824a 100644 --- a/provisioning-getdevicevalues-html.lsp +++ b/provisioning-getdevicevalues-html.lsp @@ -7,7 +7,6 @@ if form.errtxt then io.write("<P CLASS='error'>" .. string.gsub(html.html_escape displayitem(form.value.device_id) displayitem(form.value.name) displayitem(form.value.label) -displayitem(form.value.template) %> <H2>Parameter Values</H2> diff --git a/provisioning-getdevicevalues-templated.lsp b/provisioning-getdevicevalues-templated.lsp index f58907e..77cb63b 100644 --- a/provisioning-getdevicevalues-templated.lsp +++ b/provisioning-getdevicevalues-templated.lsp @@ -1,10 +1,10 @@ <% local data, viewlibrary, page_info = ... %> <% -if not data.errtxt then +if not data.errtxt and data.value.values.value.device and data.value.values.value.device.template then print("Status: 200 OK") print() - local func = haserl.loadfile(data.value.template.value) + local func = haserl.loadfile(data.value.values.value.device.template) func(data.value.values.value) else print("Status: 404 Not Found") diff --git a/provisioning-listtemplates-html.lsp b/provisioning-listtemplates-html.lsp index 9875b83..52edc6d 100644 --- a/provisioning-listtemplates-html.lsp +++ b/provisioning-listtemplates-html.lsp @@ -21,6 +21,7 @@ require("viewfunctions") <TH>Action</TH> <TH>File Name</TH> <TH>Label</TH> + <TH>Sequence</TH> <TH>File Size</TH> <TH>Last Modified</TH> </TR> @@ -28,18 +29,19 @@ require("viewfunctions") <% for k,v in ipairs( view.value ) do %> <TR> <TD> - <% if viewlibrary.check_permission("deletetemplate") and v.template_id then %> - <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deletetemplate?template_id="..v.template_id, label="Delete ", class="deletetemplate"} %> + <% if viewlibrary.check_permission("deletetemplate") and v.label then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deletetemplate?filename="..v.filename, label="Delete ", class="deletetemplate"} %> <% end %> - <% if viewlibrary.check_permission("edittemplate") and v.template_id then %> - <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/edittemplate?template_id="..v.template_id.."&redir="..page_info.orig_action, label="Edit "} %> + <% if viewlibrary.check_permission("edittemplate") and v.label then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/edittemplate?filename="..v.filename.."&redir="..page_info.orig_action, label="Edit "} %> <% end %> - <% if viewlibrary.check_permission("createtemplate") and not v.template_id then %> + <% if viewlibrary.check_permission("createtemplate") and not v.label then %> <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/createtemplate?filename="..v.filename.."&redir="..page_info.orig_action, label="Create "} %> <% end %> </TD> <TD><%= html.html_escape(v.filename) %></TD> <TD><%= html.html_escape(v.label) %></TD> + <TD><%= html.html_escape(v.seq) %></TD> <TD><%= html.html_escape(v.filesize) %></TD> <TD><%= html.html_escape(v.mtime) %></TD> </TR> diff --git a/provisioning-model.lua b/provisioning-model.lua index e9f493c..041d64e 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -25,113 +25,7 @@ local con local saved_devices = {} local saved_device_params = {} --- 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 = { - "CREATE TABLE provisioning_templates (template_id SERIAL PRIMARY KEY, filename VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE)", - }, - -- 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 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 - 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 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_groups() RETURNS TRIGGER AS $$ \ - BEGIN \ - 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 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_groups()", - }, - -- List of different class groups - provisioning_class_groups = { - "CREATE TABLE provisioning_class_groups (class_group_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE, seq INTEGER)", - }, - -- List of different device classes - provisioning_classes = { - "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 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 = { - "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 = { - "CREATE TABLE provisioning_groups (group_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE, seq INTEGER)", - "CREATE INDEX groups_name_idx ON provisioning_groups (name)", - }, - -- Multi-to-multi mapping of parameter groups to parameters - param_groups_to_params = { - "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)", - "CREATE UNIQUE INDEX g2p_group_param_idx ON param_groups_to_params (group_id, param_id)", - }, - -- List of each parameter used in any way for any device - mostly for how to display - provisioning_params = { - "CREATE TABLE provisioning_params (param_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER, regexp VARCHAR(255))", - "CREATE INDEX params_name_idx ON provisioning_params (name)", - }, - -- 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 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 UNIQUE 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 = { - "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)", - "CREATE UNIQUE INDEX options_param_label_idx ON provisioning_options (param_id, label)", - }, -} +local table_creation_scripts = require("provisioning/provisioning-scripts") -- ################################################################################ -- LOCAL FUNCTIONS @@ -156,7 +50,6 @@ end local createdatabase = function() local result = {} - -- First, create the user if DatabaseUser ~= "postgres" then local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'' @@ -206,6 +99,10 @@ local deletedatabase = function() return table.concat(result, "\n") end +-- 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 databaseconnect = function() if not con then -- create environment object @@ -216,6 +113,14 @@ local databaseconnect = function() if err and string.match(err, "Error connecting to database.") then createdatabase() con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword) + if con then + -- Let's create all the tables from the start + for n,v in pairs(table_creation_scripts) do + if not string.match(n, "^_") then + runsqlcommand("SELECT * FROM "..n.." LIMIT 1") + end + end + end end assert(con, err) return true @@ -234,10 +139,6 @@ local databasedisconnect = function() end end --- 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 runsqlcommand(scr, in_transaction) @@ -383,7 +284,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 label ASC, filename ASC" + local sql = "SELECT value AS filename, label, seq FROM provisioning_options WHERE param_id = (SELECT param_id FROM provisioning_params WHERE name = 'template') ORDER BY seq ASC, label ASC, value ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) @@ -413,23 +314,25 @@ list_templates = function() return cfe({ type="structure", value=retval, label="List of Templates", errtxt=errtxt }) end -get_template = function(template_id, filename) +get_template = function(filename) local retval = {} if filename and not string.match(filename, "/") then filename = baseurl .. filename end - retval.template_id = cfe({value=template_id or "", label="Template ID"}) - retval.filename = cfe({value=filename or "", label="File Name", descr="Must be in "..baseurl}) - retval.label = cfe({label="Template Label"}) + retval.filename = cfe({value=filename or "", label="File Name", descr="Must be in "..baseurl, seq=1}) + retval.label = cfe({label="Label", seq=2}) + retval.seq = cfe({label="Sequence", seq=3}) local errtxt - if template_id and template_id ~= "" then + if filename and filename ~= "" 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, filename ASC" + local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(filename).."' ORDER BY seq ASC, label ASC, value ASC" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do - if retval[n] then + if n == "value" then + retval.filename.value = v + elseif retval[n] then retval[n].value = v end end @@ -467,22 +370,27 @@ update_template = function(template, create) success = false template.value.label.errtxt = "Cannot be blank" end + if not validator.is_integer(template.value.seq.value) then + success = false + template.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_templates WHERE template_id='"..escape(template.value.template_id.value).."'" - local tmp = getselectresponse(sql) - if not tmp or #tmp == 0 then - success = false - errtxt = "Template does not exist" - end + local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(template.value.filename.value).."'" + local tmp = getselectresponse(sql) + if not create and (not tmp or #tmp == 0) then + success = false + errtxt = "Template does not exist" + elseif create and #tmp > 0 then + success = false + errtxt = "Template already exists" end if success then if create then - sql = "INSERT INTO provisioning_templates VALUES(DEFAULT, '"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."')" + sql = "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='template'), '"..escape(template.value.label.value).."', '"..escape(template.value.filename.value).."', '"..escape(template.value.seq.value).."')" else - sql = "UPDATE provisioning_templates SET (filename, label) = ('"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."') WHERE template_id='"..escape(template.value.template_id.value).."'" + sql = "UPDATE provisioning_options SET (label, seq) = ('"..escape(template.value.label.value).."', '"..escape(template.value.seq.value).."') WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(template.value.filename.value).."'" end runsqlcommand(sql) @@ -505,21 +413,21 @@ update_template = function(template, create) return template end -delete_template = function(template_id) +delete_template = function(filename) local result = "" local errtxt local res, err = pcall(function() local connected = databaseconnect() - local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template_id).."'" + local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(filename).."'" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Template does not exist" else -- Remove the template - sql = "DELETE FROM provisioning_templates WHERE template_id='"..escape(template_id).."'" + sql = "DELETE FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..escape(filename).."'" runsqlcommand(sql) -- Delete the template file - os.remove(tmp[1].filename) + os.remove(filename) result = "Template Deleted" end if connected then databasedisconnect() end @@ -1222,7 +1130,6 @@ get_device = function(device_id) retval.device_id = cfe({value=device_id or "", label="Device ID", seq=1}) retval.name = cfe({label="Name", seq=2}) retval.label = cfe({label="Label", seq=3}) - retval.template_id = cfe({type="select", label="Template", option={}, seq=4}) retval.classes = cfe({type="group", value={}, label="Classes", seq=5}) local errtxt local res, err = pcall(function() @@ -1245,12 +1152,6 @@ get_device = function(device_id) classes[g.class_id] = true end end - -- Next, get the template options - 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 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.seq ASC, c.label ASC" tmp = getselectresponse(sql) @@ -1284,7 +1185,6 @@ update_device = function(device, create) local success = true local errtxt -- Validate the settings - success = modelfunctions.validateselect(device.value.template_id) if device.value.name.value == "" then success = false device.value.name.errtxt = "Cannot be blank" @@ -1310,7 +1210,7 @@ update_device = function(device, create) local sql = "BEGIN TRANSACTION" runsqlcommand(sql) if create then - sql = "INSERT INTO provisioning_devices VALUES(DEFAULT, '"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."', '"..escape(device.value.template_id.value).."')" + sql = "INSERT INTO provisioning_devices VALUES(DEFAULT, '"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."')" runsqlcommand(sql, true) sql = "SELECT device_id FROM provisioning_devices WHERE name='"..escape(device.value.name.value).."' AND label='"..escape(device.value.label.value).."'" local tmp = getselectresponse(sql, true) @@ -1318,7 +1218,7 @@ update_device = function(device, create) device.value.device_id.value = tmp[1].device_id end else - sql = "UPDATE provisioning_devices SET (name, label, template_id) = ('"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."', '"..escape(device.value.template_id.value).."') WHERE device_id='"..escape(device.value.device_id.value).."'" + sql = "UPDATE provisioning_devices SET (name, label) = ('"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."') WHERE device_id='"..escape(device.value.device_id.value).."'" runsqlcommand(sql, true) sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."'" runsqlcommand(sql, true) @@ -1547,13 +1447,12 @@ get_device_values_by_name = function(name) retval.device_id = cfe({label="Device ID", seq=1}) retval.name = cfe({value=name or "", label="Name", seq=2}) retval.label = cfe({label="Label", seq=3}) - retval.template = cfe({type="select", label="Template", option={}, seq=4}) retval.values = cfe({type="structure", value={}, label="Parameter Values", option={}, seq=5}) local errtxt local res, err = pcall(function() local connected = databaseconnect() if name and name ~= "" then - local sql = "SELECT d.device_id, d.name, d.label, t.filename AS template FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE name='"..escape(name).."'" + local sql = "SELECT * FROM provisioning_devices WHERE name='"..escape(name).."'" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then for n,v in pairs(tmp[1]) do @@ -1616,7 +1515,7 @@ end search_device_values = function(parameter_id, parameter_value, comparison) local errtxt retval = {} - retval.id = cfe({type="select", value=parameter_id or "name", label="Parameter", option={"name", "label", "template_id", "template"}, seq=1}) + retval.id = cfe({type="select", value=parameter_id or "name", label="Parameter", option={"name", "label"}, seq=1}) retval.comparison = cfe({type="select", value=comparison or "=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!*~"}, seq=2}) retval.value = cfe({label="Parameter Value", value=parameter_value, descr="Parameter value or SQL regular expression", seq=3}) retval.result = cfe({type="structure", value={}, label="List of Devices", seq=4 }) @@ -1641,17 +1540,12 @@ search_device_values = function(parameter_id, parameter_value, comparison) -- Get the devices from the DB if parameter_id and modelfunctions.validateselect(retval.id) and modelfunctions.validateselect(retval.comparison) then parameter_value = parameter_value or "" - sql = "SELECT d.device_id, d.name, d.label, d.template_id, " + sql = "SELECT d.device_id, d.name, d.label, " local group, param = string.match(parameter_id or "", "([^%.]*)%.(.*)") if not group then sql = sql.."'"..escape(parameter_id).."' AS param, " if parameter_id == "name" or parameter_id=="label" then sql = sql.."d."..escape(parameter_id).." AS value FROM provisioning_devices d WHERE d."..escape(parameter_id) - elseif parameter_id=="template_id" then - sql = sql.."d.template_id AS value FROM provisioning_devices d WHERE CAST(d.template_id AS text)" - elseif parameter_id=="template" then - -- Search by template label - sql = sql.."t.label AS value FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE t.label" end sql = sql..retval.comparison.value.."'"..escape(parameter_value).."'" else diff --git a/provisioning-scripts.lua b/provisioning-scripts.lua new file mode 100755 index 0000000..ed953dc --- /dev/null +++ b/provisioning-scripts.lua @@ -0,0 +1,403 @@ +module (..., package.seeall) + +-- 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 + +-- 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))", + "CREATE INDEX devices_name_idx ON provisioning_devices (name)", +} + +-- Multi-to-multi mapping of devices to classes +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 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_groups() RETURNS TRIGGER AS $$ \ + BEGIN \ + 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 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_groups()", +} + +-- List of different class groups +provisioning_class_groups = { + "CREATE TABLE provisioning_class_groups (class_group_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE, seq INTEGER)", + "INSERT INTO provisioning_class_groups VALUES(default, 'device', 'Device Model', '1')", + "INSERT INTO provisioning_class_groups VALUES(default, 'services', 'Services', '2')", +} + +-- List of different device classes +provisioning_classes = { + "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 group/label combo is unique + "CREATE UNIQUE INDEX classes_group_label_idx ON provisioning_classes (class_group_id, label)", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='device'), 'Polycom SoundStation IP 6000 SIP', '1')", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='device'), 'Polycom SoundPoint IP 321 SIP', '2')", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='device'), 'Polycom SoundPoint IP 650 SIP', '3')", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='device'), 'Linksys Internet Phone Adapter PAP2T', '4')", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='services'), 'Standard Phone', '1')", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='services'), 'Standard Phone without Call Forwarding', '2')", + "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='services'), 'Hotline', '3')", +} + +-- Multi-to-multi mapping of classes to parameter groups +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()", + -- Devices + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundStation IP 6000 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 1'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundStation IP 6000 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Polycom Device'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 321 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 1'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 321 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 2'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 321 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Polycom Device'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 1'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 2'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 3'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 4'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 5'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 6'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 7'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 8'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 9'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 10'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 11'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 12'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 13'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 14'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 15'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 16'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 17'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 18'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 19'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 20'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 21'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 22'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 23'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 24'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 25'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 26'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 27'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 28'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 29'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 30'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 31'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 32'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 33'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 34'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Polycom SoundPoint IP 650 SIP'), (SELECT group_id FROM provisioning_groups WHERE label='Polycom Device'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Linksys Internet Phone Adapter PAP2T'), (SELECT group_id FROM provisioning_groups WHERE label='Registration 1'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Linksys Internet Phone Adapter PAP2T'), (SELECT group_id FROM provisioning_groups WHERE label='Linksys Device'))", + -- Services + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Standard Phone'), (SELECT group_id FROM provisioning_groups WHERE label='Standard Phone'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Standard Phone without Call Forwarding'), (SELECT group_id FROM provisioning_groups WHERE label='Standard Phone without Call Forwarding'))", + "INSERT INTO classes_to_param_groups VALUES((SELECT class_id FROM provisioning_classes WHERE label='Hotline'), (SELECT group_id FROM provisioning_groups WHERE label='Hotline'))", +} + +-- List of different parameter groups +provisioning_groups = { + "CREATE TABLE provisioning_groups (group_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE, seq INTEGER)", + "CREATE INDEX groups_name_idx ON provisioning_groups (name)", + "INSERT INTO provisioning_groups VALUES(default, 'device', 'Linksys Device', '1')", + "INSERT INTO provisioning_groups VALUES(default, 'device', 'Polycom Device', '1')", + "INSERT INTO provisioning_groups VALUES(default, 'reg1', 'Registration 1', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg2', 'Registration 2', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg3', 'Registration 3', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg4', 'Registration 4', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg5', 'Registration 5', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg6', 'Registration 6', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg7', 'Registration 7', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg8', 'Registration 8', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg9', 'Registration 9', '10')", + "INSERT INTO provisioning_groups VALUES(default, 'reg10', 'Registration 10', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg11', 'Registration 11', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg12', 'Registration 12', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg13', 'Registration 13', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg14', 'Registration 14', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg15', 'Registration 15', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg16', 'Registration 16', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg17', 'Registration 17', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg18', 'Registration 18', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg19', 'Registration 19', '11')", + "INSERT INTO provisioning_groups VALUES(default, 'reg20', 'Registration 20', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg21', 'Registration 21', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg22', 'Registration 22', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg23', 'Registration 23', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg24', 'Registration 24', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg25', 'Registration 25', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg26', 'Registration 26', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg27', 'Registration 27', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg28', 'Registration 28', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg29', 'Registration 29', '12')", + "INSERT INTO provisioning_groups VALUES(default, 'reg30', 'Registration 30', '13')", + "INSERT INTO provisioning_groups VALUES(default, 'reg31', 'Registration 31', '13')", + "INSERT INTO provisioning_groups VALUES(default, 'reg32', 'Registration 32', '13')", + "INSERT INTO provisioning_groups VALUES(default, 'reg33', 'Registration 33', '13')", + "INSERT INTO provisioning_groups VALUES(default, 'reg34', 'Registration 34', '13')", + "INSERT INTO provisioning_groups VALUES(default, 'services', 'Standard Phone', '21')", + "INSERT INTO provisioning_groups VALUES(default, 'services', 'Standard Phone without Call Forwarding', '22')", + "INSERT INTO provisioning_groups VALUES(default, 'services', 'Hotline', '23')", +} + +-- Multi-to-multi mapping of parameter groups to parameters +param_groups_to_params = { + "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)", + "CREATE UNIQUE INDEX g2p_group_param_idx ON param_groups_to_params (group_id, param_id)", + -- Device + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Linksys ATA Device'), (SELECT param_id FROM provisioning_params WHERE name='template'), '/etc/provisioning/templates/linksysata-template.lua', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Polycom Device'), (SELECT param_id FROM provisioning_params WHERE name='template'), '/etc/provisioning/templates/polycom-template.lua', false)", + -- Registrations + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 1'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 1'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 1'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 1'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 1'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 2'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 2'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 2'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 2'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 2'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 3'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 3'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 3'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 3'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 3'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 4'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 4'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 4'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 4'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 4'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 5'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 5'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 5'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 5'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 5'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 6'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 6'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 6'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 6'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 6'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 7'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 7'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 7'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 7'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 7'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 8'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 8'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 8'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 8'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 8'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 9'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 9'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 9'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 9'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 9'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 10'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 10'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 10'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 10'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 10'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 11'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 11'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 11'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 11'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 11'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 12'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 12'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 12'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 12'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 12'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 13'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 13'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 13'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 13'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 13'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 14'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 14'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 14'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 14'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 14'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 15'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 15'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 15'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 15'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 15'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 16'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 16'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 16'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 16'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 16'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 17'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 17'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 17'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 17'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 17'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 18'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 18'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 18'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 18'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 18'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 19'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 19'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 19'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 19'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 19'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 20'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 20'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 20'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 20'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 20'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 21'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 21'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 21'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 21'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 21'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 22'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 22'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 22'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 22'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 22'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 23'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 23'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 23'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 23'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 23'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 24'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 24'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 24'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 24'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 24'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 25'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 25'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 25'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 25'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 25'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 26'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 26'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 26'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 26'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 26'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 27'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 27'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 27'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 27'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 27'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 28'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 28'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 28'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 28'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 28'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 29'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 29'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 29'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 29'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 29'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 30'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 30'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 30'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 30'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 30'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 31'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 31'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 31'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 31'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 31'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 32'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 32'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 32'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 32'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 32'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 33'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 33'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 33'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 33'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 33'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 34'), (SELECT param_id FROM provisioning_params WHERE name='extension'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 34'), (SELECT param_id FROM provisioning_params WHERE name='password'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 34'), (SELECT param_id FROM provisioning_params WHERE name='forwardnoanswer'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 34'), (SELECT param_id FROM provisioning_params WHERE name='forwardbusy'), '', true)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Registration 34'), (SELECT param_id FROM provisioning_params WHERE name='forwardall'), '', true)", + -- Services + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Standard Phone'), (SELECT param_id FROM provisioning_params WHERE name='forwarding'), 'true', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Standard Phone'), (SELECT param_id FROM provisioning_params WHERE name='hotlineenable'), 'false', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Standard Phone'), (SELECT param_id FROM provisioning_params WHERE name='hotlinedestination'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Standard Phone without Call Forwarding'), (SELECT param_id FROM provisioning_params WHERE name='forwarding'), 'false', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Standard Phone without Call Forwarding'), (SELECT param_id FROM provisioning_params WHERE name='hotlineenable'), 'false', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Standard Phone without Call Forwarding'), (SELECT param_id FROM provisioning_params WHERE name='hotlinedestination'), '', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Hotline'), (SELECT param_id FROM provisioning_params WHERE name='forwarding'), 'false', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Hotline'), (SELECT param_id FROM provisioning_params WHERE name='hotlineenable'), 'true', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Hotline'), (SELECT param_id FROM provisioning_params WHERE name='hotlinedestination'), '', true)", +} + +-- List of each parameter used in any way for any device - mostly for how to display +provisioning_params = { + "CREATE TABLE provisioning_params (param_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER, regexp VARCHAR(255))", + "CREATE INDEX params_name_idx ON provisioning_params (name)", + "INSERT INTO provisioning_params VALUES(default, 'template', 'select', 'Template', '', '', '0', '')", + "INSERT INTO provisioning_params VALUES(default, 'extension', 'text', 'Extension', '', '', '1', '^%d*$')", + "INSERT INTO provisioning_params VALUES(default, 'password', 'text', 'Password', '', '', '2', '')", + "INSERT INTO provisioning_params VALUES(default, 'forwardnoanswer', 'text', 'Forward on No-answer Destination', 'Callers will be transferred to this extension when you don''t answer after 20 seconds. Blank disables.', '', '3', '')", + "INSERT INTO provisioning_params VALUES(default, 'forwardbusy', 'text', 'Forward on Busy Destination', 'Callers will be transferred to this extension when the line is busy. Blank disables.', '', '4', '')", + "INSERT INTO provisioning_params VALUES(default, 'forwardall', 'text', 'Forward All Calls Destination', 'All calls will be transferred to this extension. Blank disables.', '', '5', '')", + "INSERT INTO provisioning_params VALUES(default, 'forwarding', 'boolean', 'Forwarding Enable', '', 'true', '11', '')", + "INSERT INTO provisioning_params VALUES(default, 'hotlineenable', 'boolean', 'Hotline Enable', '', 'false', '12', '')", + "INSERT INTO provisioning_params VALUES(default, 'hotlinedestination', 'text', 'Hotline Destination', '', '', '13', '')", +} + +-- 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 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 UNIQUE 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 = { + "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)", + "CREATE UNIQUE INDEX options_param_label_idx ON provisioning_options (param_id, label)", + -- Templates + "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='template'), 'Polycom', '/etc/provisioning/templates/polycom-template.lua', '1')", + "INSERT INTO provisioning_options VALUES((SELECT param_id FROM provisioning_params WHERE name='template'), 'Linksys', '/etc/provisioning/templates/linksysata-template.lua', '2')", +} |