Removed provisioning-devices and instead use provisioning-device-seq. So, device label is no more.

Also changed Standard Phone without Call Forwarding to Public Phone.

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")