diff options
-rw-r--r-- | provisioning-editdeviceparams-html.lsp | 1 | ||||
-rw-r--r-- | provisioning-listdevices-html.lsp | 4 | ||||
-rw-r--r-- | provisioning-model.lua | 62 | ||||
-rwxr-xr-x | provisioning-scripts.lua | 26 | ||||
-rw-r--r-- | provisioning-searchdevices-html.lsp | 1 |
5 files changed, 36 insertions, 58 deletions
diff --git a/provisioning-editdeviceparams-html.lsp b/provisioning-editdeviceparams-html.lsp index 947e6b8..4d976ca 100644 --- a/provisioning-editdeviceparams-html.lsp +++ b/provisioning-editdeviceparams-html.lsp @@ -5,6 +5,5 @@ require("viewfunctions") <H1><%= html.html_escape(form.label) %></H1> <% form.value.device_id.readonly = "true" - form.value.label.readonly = "true" displayform(form, nil, nil, page_info, 2) %> diff --git a/provisioning-listdevices-html.lsp b/provisioning-listdevices-html.lsp index e93bdca..fe3114f 100644 --- a/provisioning-listdevices-html.lsp +++ b/provisioning-listdevices-html.lsp @@ -19,7 +19,7 @@ require("viewfunctions") <TABLE id="list" class="tablesorter"><THEAD> <TR style="background:#eee;font-weight:bold;"> <TH>Action</TH> - <TH>Label</TH> + <TH>Device ID</TH> <% if page_info.action == "searchdevices" then %> <% if view.value[1] and view.value[1].group and view.value[1].group ~= "" then %> <TH>Group</TH> @@ -47,7 +47,7 @@ require("viewfunctions") <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/getdevicevalues?id=device_id&value="..v.device_id.."&Search=Search&viewtype=templated", label="View "} %> <% end %> </TD> - <TD><%= html.html_escape(v.label) %></TD> + <TD><%= html.html_escape(v.device_id) %></TD> <% if page_info.action == "searchdevices" then %> <% if v.group and v.group ~= "" then %> <TD><%= html.html_escape(v.group) %></TD> diff --git a/provisioning-model.lua b/provisioning-model.lua index 77bd39a..628cc8a 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -1133,7 +1133,7 @@ list_devices = function() -- Get the devices from the DB local res, err = pcall(function() local connected = databaseconnect() - local sql = "SELECT * FROM provisioning_devices ORDER BY label ASC" + local sql = "SELECT device_id FROM devices_to_classes GROUP BY device_id ORDER BY device_id ASC" retval = getselectresponse(sql) if connected then databasedisconnect() end end) @@ -1147,25 +1147,15 @@ end get_device = function(device_id) local retval = {} retval.device_id = cfe({value=device_id or "", label="Device ID", seq=1}) - retval.label = cfe({label="Label", seq=2}) - retval.classes = cfe({type="group", value={}, label="Classes", seq=3}) + retval.classes = cfe({type="group", value={}, label="Classes", seq=2}) local errtxt local res, err = pcall(function() local classes={} local connected = databaseconnect() if device_id and device_id ~= "" then - local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" + -- Get the device-to-class mappings + local sql = "SELECT class_id FROM devices_to_classes WHERE device_id="..escape(device_id) local tmp = getselectresponse(sql) - if tmp and #tmp > 0 then - for n,v in pairs(tmp[1]) do - if retval[n] then - retval[n].value = v - end - end - end - -- Now, get the device-to-class mappings - sql = "SELECT class_id FROM devices_to_classes WHERE device_id="..escape(device_id) - tmp = getselectresponse(sql) for i,g in ipairs(tmp) do classes[g.class_id] = true end @@ -1203,10 +1193,6 @@ update_device = function(device, create) local success = true local errtxt -- Validate the settings - if device.value.label.value == "" then - success = false - device.value.label.errtxt = "Cannot be blank" - end for n,c in pairs(device.value.classes.value) do success = modelfunctions.validateselect(c) and success end @@ -1214,11 +1200,11 @@ update_device = function(device, create) local res, err = pcall(function() local connected = databaseconnect() if not create then - local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device.value.device_id.value).."'" + local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."' LIMIT 1" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false - errtxt = "Device does not exist" + device.value.device_id.errtxt = "Device does not exist" end end if success then @@ -1228,19 +1214,14 @@ update_device = function(device, create) local sql = "BEGIN TRANSACTION" runsqlcommand(sql) if create then - sql = "INSERT INTO provisioning_devices VALUES(DEFAULT, '"..escape(device.value.label.value).."')" - runsqlcommand(sql, true) - sql = "SELECT device_id FROM provisioning_devices WHERE label='"..escape(device.value.label.value).."'" + sql = "SELECT nextval('provisioning_device_seq')" local tmp = getselectresponse(sql, true) if tmp and #tmp>0 then - device.value.device_id.value = tmp[1].device_id + device.value.device_id.value = tmp[1].nextval end - else - sql = "UPDATE provisioning_devices SET (label) = ('"..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) end + sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."'" + runsqlcommand(sql, true) -- Insert the device to class entries for n,c in pairs(device.value.classes.value) do if c.value ~= "" then @@ -1279,7 +1260,7 @@ delete_device = function(device_id) local errtxt local res, err = pcall(function() local connected = databaseconnect() - local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" + local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) if #tmp == 0 then errtxt = "Device does not exist" @@ -1293,8 +1274,6 @@ delete_device = function(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) @@ -1320,17 +1299,15 @@ end get_device_params = function(device_id, editable) local retval = {} retval.device_id = cfe({value=device_id or "", label="Device ID", seq=0}) - retval.label = cfe({label="Label", seq=0}) local errtxt = "Cannot find device" if device_id and device_id ~= "" then local res, err = pcall(function() local connected = databaseconnect() -- First, just check to see if device_id exists - local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" + local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..escape(device_id).."' LIMIT 1" local tmp = getselectresponse(sql) if tmp and #tmp > 0 then errtxt = nil - retval.label.value = tmp[1].label -- Next, get all of the param groups sql = "SELECT * FROM provisioning_groups" local tmp = getselectresponse(sql) @@ -1399,7 +1376,7 @@ set_device_params = function(params, editable) local res, err = pcall(function() local connected = databaseconnect() if not create then - local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(params.value.device_id.value).."'" + local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..escape(params.value.device_id.value).."' LIMIT 1" local tmp = getselectresponse(sql) if not tmp or #tmp == 0 then success = false @@ -1518,7 +1495,7 @@ end get_search_options = function() local errtxt retval = {} - retval.id = cfe({type="select", value="label", label="Parameter", option={"device_id", "label"}, seq=1}) + retval.id = cfe({type="select", value="device_id", label="Parameter", option={"device_id"}, seq=1}) retval.comparison = cfe({type="select", value="=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!*~"}, seq=2}) retval.value = cfe({label="Parameter Value", descr="Parameter value or SQL regular expression", seq=3}) local res, err = pcall(function() @@ -1556,15 +1533,14 @@ search_device_values = function(search) local res, err = pcall(function() local connected = databaseconnect() -- Get the devices from the DB - sql = "SELECT d.device_id, d.label, " + sql = "SELECT d2t.device_id, " local group, param = string.match(search.value.id.value, "([^%.]*)%.(.*)") if not group then - sql = sql.."'"..search.value.id.value.."' AS param, " - sql = sql.."d."..search.value.id.value.." AS value FROM provisioning_devices d WHERE d."..search.value.id.value - sql = sql..search.value.comparison.value.."'"..escape(search.value.value.value).."'" + sql = sql.."'"..search.value.id.value.."' AS param, d2t."..search.value.id.value.." AS value FROM devices_to_classes d2t WHERE d2t."..search.value.id.value.. + search.value.comparison.value.."'"..escape(search.value.value.value).."' GROUP BY device_id" else sql = sql.."g.name as group, p.name as param, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END as value FROM ".. - "provisioning_devices d JOIN devices_to_classes d2t USING(device_id) JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) ".. + "devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) ".. "JOIN provisioning_groups g USING(group_id) JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id) ".. "LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) " if group and group ~= "" then @@ -1574,7 +1550,7 @@ search_device_values = function(search) end sql = sql.." p.name='"..escape(param).."' AND CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END"..search.value.comparison.value.."'"..escape(search.value.value.value).."'" end - sql = sql.." ORDER BY d.label ASC" + sql = sql.." ORDER BY d2t.device_id ASC" search.value.result.value = getselectresponse(sql) if connected then databasedisconnect() end end) diff --git a/provisioning-scripts.lua b/provisioning-scripts.lua index 3464d68..84899da 100755 --- a/provisioning-scripts.lua +++ b/provisioning-scripts.lua @@ -3,13 +3,13 @@ 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, label VARCHAR(255) UNIQUE)", +provisioning_device_seq = { + "CREATE SEQUENCE provisioning_device_seq", } -- 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 TABLE devices_to_classes (device_id INTEGER, 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) @@ -45,7 +45,7 @@ provisioning_classes = { "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'), 'Public Phone', '2')", "INSERT INTO provisioning_classes VALUES(default, (SELECT class_group_id FROM provisioning_class_groups WHERE name='services'), 'Hotline', '3')", } @@ -111,7 +111,7 @@ classes_to_param_groups = { "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='Public Phone'), (SELECT group_id FROM provisioning_groups WHERE label='Public Phone'))", "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'))", } @@ -156,7 +156,7 @@ provisioning_groups = { "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', 'Public Phone', '22')", "INSERT INTO provisioning_groups VALUES(default, 'services', 'Hotline', '23')", } @@ -347,12 +347,15 @@ param_groups_to_params = { "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='Standard Phone'), (SELECT param_id FROM provisioning_params WHERE name='callhistoryenable'), 'true', false)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Public Phone'), (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='Public 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='Public 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='Public Phone'), (SELECT param_id FROM provisioning_params WHERE name='callhistoryenable'), '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='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)", + "INSERT INTO param_groups_to_params VALUES((SELECT group_id FROM provisioning_groups WHERE label='Hotline'), (SELECT param_id FROM provisioning_params WHERE name='callhistoryenable'), 'false', false)", } -- List of each parameter used in any way for any device - mostly for how to display @@ -369,12 +372,13 @@ provisioning_params = { "INSERT INTO provisioning_params VALUES(default, 'forwarding', 'boolean', 'Forwarding Enable', '', 'true', '21', '')", "INSERT INTO provisioning_params VALUES(default, 'hotlineenable', 'boolean', 'Hotline Enable', '', 'false', '22', '')", "INSERT INTO provisioning_params VALUES(default, 'hotlinedestination', 'text', 'Hotline Destination', '', '', '23', '')", + "INSERT INTO provisioning_params VALUES(default, 'callhistoryenable', 'boolean', 'Call History Enable', '', 'true', '24', '')", } -- 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))", + -- device_id is a device id from provisioning_device_seq and param_id is a param id from provisioning_params + "CREATE TABLE provisioning_values (device_id INTEGER, 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)", diff --git a/provisioning-searchdevices-html.lsp b/provisioning-searchdevices-html.lsp index b000c3e..3529448 100644 --- a/provisioning-searchdevices-html.lsp +++ b/provisioning-searchdevices-html.lsp @@ -7,7 +7,6 @@ require("viewfunctions") <DL> <% if form.errtxt then io.write("<P CLASS='error'>" .. string.gsub(html.html_escape(form.errtxt), "\n", "<BR>") .. "</P>\n") end %> <DT>Device ID</DT><DD><input class="text" type="text" value="<%= html.html_escape(form.value.result.value[1].device_id) %>" readonly="true"></DD> - <DT>Label</DT><DD><input class="text" type="text" value="<%= html.html_escape(form.value.result.value[1].label) %>" readonly="true"></DD> <H2>Parameter Values</H2> <% require("session") |