From 5c93a38c2cc263727be345ff5a197ea9bc78595b Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Thu, 28 Oct 2010 13:47:54 +0000 Subject: Starting to develop provisioning application. It has the basics for creating/editing/deleting devices/templates/classes/groups/parameters. --- Makefile | 43 + README | 1 + config.mk | 10 + provisioning-controller.lua | 97 +++ provisioning-createclass-html.lsp | 1 + provisioning-createdevice-html.lsp | 1 + provisioning-creategroup-html.lsp | 1 + provisioning-createparam-html.lsp | 1 + provisioning-createtemplate-html.lsp | 1 + provisioning-editclass-html.lsp | 13 + provisioning-editdevice-html.lsp | 13 + provisioning-editdeviceparams-html.lsp | 11 + provisioning-editgroup-html.lsp | 13 + provisioning-editparam-html.lsp | 13 + provisioning-edittemplate-html.lsp | 27 + provisioning-getdevicevalues-html.lsp | 18 + provisioning-html.lsp | 1 + provisioning-listclasses-html.lsp | 52 ++ provisioning-listdevices-html.lsp | 57 ++ provisioning-listgroups-html.lsp | 54 ++ provisioning-listparams-html.lsp | 54 ++ provisioning-listtemplates-html.lsp | 59 ++ provisioning-model.lua | 1240 ++++++++++++++++++++++++++++ provisioning-overridedeviceparams-html.lsp | 1 + provisioning.menu | 7 + provisioning.roles | 5 + 26 files changed, 1794 insertions(+) create mode 100644 Makefile create mode 100644 README create mode 100644 config.mk create mode 100644 provisioning-controller.lua create mode 120000 provisioning-createclass-html.lsp create mode 120000 provisioning-createdevice-html.lsp create mode 120000 provisioning-creategroup-html.lsp create mode 120000 provisioning-createparam-html.lsp create mode 120000 provisioning-createtemplate-html.lsp create mode 100644 provisioning-editclass-html.lsp create mode 100644 provisioning-editdevice-html.lsp create mode 100644 provisioning-editdeviceparams-html.lsp create mode 100644 provisioning-editgroup-html.lsp create mode 100644 provisioning-editparam-html.lsp create mode 100644 provisioning-edittemplate-html.lsp create mode 100644 provisioning-getdevicevalues-html.lsp create mode 120000 provisioning-html.lsp create mode 100644 provisioning-listclasses-html.lsp create mode 100644 provisioning-listdevices-html.lsp create mode 100644 provisioning-listgroups-html.lsp create mode 100644 provisioning-listparams-html.lsp create mode 100644 provisioning-listtemplates-html.lsp create mode 100644 provisioning-model.lua create mode 120000 provisioning-overridedeviceparams-html.lsp create mode 100644 provisioning.menu create mode 100644 provisioning.roles diff --git a/Makefile b/Makefile new file mode 100644 index 0000000..ff706df --- /dev/null +++ b/Makefile @@ -0,0 +1,43 @@ +APP_NAME=provisioning +PACKAGE=acf-$(APP_NAME) +VERSION=0.0.1 + +APP_DIST=\ + provisioning* \ + +EXTRA_DIST=README Makefile config.mk + +DISTFILES=$(APP_DIST) $(EXTRA_DIST) + +TAR=tar + +P=$(PACKAGE)-$(VERSION) +tarball=$(P).tar.bz2 +install_dir=$(DESTDIR)/$(appdir)/$(APP_NAME) + +all: +clean: + rm -rf $(tarball) $(P) + +dist: $(tarball) + +install: + mkdir -p "$(install_dir)" + cp -a $(APP_DIST) "$(install_dir)" + +$(tarball): $(DISTFILES) + rm -rf $(P) + mkdir -p $(P) + cp -a $(DISTFILES) $(P) + $(TAR) -jcf $@ $(P) + rm -rf $(P) + +# target that creates a tar package, unpacks is and install from package +dist-install: $(tarball) + $(TAR) -jxf $(tarball) + $(MAKE) -C $(P) install DESTDIR=$(DESTDIR) + rm -rf $(P) + +include config.mk + +.PHONY: all clean dist install dist-install diff --git a/README b/README new file mode 100644 index 0000000..8277e0a --- /dev/null +++ b/README @@ -0,0 +1 @@ +acf-provisioning is a template based provisioning system designed for SIP devices diff --git a/config.mk b/config.mk new file mode 100644 index 0000000..45f4d21 --- /dev/null +++ b/config.mk @@ -0,0 +1,10 @@ +prefix=/usr +datadir=${prefix}/share +sysconfdir=${prefix}/etc +localstatedir=${prefix}/var +acfdir=${datadir}/acf +wwwdir=${acfdir}/www +cgibindir=${acfdir}/cgi-bin +appdir=${acfdir}/app +acflibdir=${acfdir}/lib +sessionsdir=${localstatedir}/lib/acf/sessions diff --git a/provisioning-controller.lua b/provisioning-controller.lua new file mode 100644 index 0000000..31a1c43 --- /dev/null +++ b/provisioning-controller.lua @@ -0,0 +1,97 @@ +module (..., package.seeall) + +require("controllerfunctions") + +default_action = "listdevices" + +listtemplates = function( self ) + return self.model.list_templates() +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") +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") +end + +deletetemplate = function( self ) + return self:redirect_to_referrer(self.model.delete_template(self.clientdata.template_id)) +end + +listclasses = function( self ) + return self.model.list_classes() +end + +editclass = function( self ) + return controllerfunctions.handle_form(self, function() return self.model.get_class(self.clientdata.class_id) end, self.model.update_class, self.clientdata, "Save", "Edit Class", "Class Saved") +end + +createclass = function( self ) + return controllerfunctions.handle_form(self, self.model.get_class, self.model.create_class, self.clientdata, "Create", "Create Class", "Class Created") +end + +deleteclass = function( self ) + return self:redirect_to_referrer(self.model.delete_class(self.clientdata.class_id)) +end + +listgroups = function( self ) + return self.model.list_groups() +end + +editgroup = function( self ) + return controllerfunctions.handle_form(self, function() return self.model.get_group(self.clientdata.group_id) end, self.model.update_group, self.clientdata, "Save", "Edit Parameter Group", "Parameter Group Saved") +end + +creategroup = function( self ) + return controllerfunctions.handle_form(self, self.model.get_group, self.model.create_group, self.clientdata, "Create", "Create Parameter Group", "Parameter Group Created") +end + +deletegroup = function( self ) + return self:redirect_to_referrer(self.model.delete_group(self.clientdata.group_id)) +end + +listparams = function( self ) + return self.model.list_params() +end + +editparam = function( self ) + return controllerfunctions.handle_form(self, function() return self.model.get_param(self.clientdata.param_id) end, self.model.update_param, self.clientdata, "Save", "Edit Parameter", "Parameter Saved") +end + +createparam = function( self ) + return controllerfunctions.handle_form(self, self.model.get_param, self.model.create_param, self.clientdata, "Create", "Create Parameter", "Parameter Created") +end + +deleteparam = function( self ) + return self:redirect_to_referrer(self.model.delete_param(self.clientdata.param_id)) +end + +listdevices = function( self ) + return self.model.list_devices() +end + +editdevice = function( self ) + return controllerfunctions.handle_form(self, function() return self.model.get_device(self.clientdata.device_id) end, self.model.update_device, self.clientdata, "Save", "Edit Device", "Device Saved") +end + +createdevice = function( self ) + return controllerfunctions.handle_form(self, self.model.get_device, self.model.create_device, self.clientdata, "Create", "Create Device", "Device Created") +end + +deletedevice = function( self ) + return self:redirect_to_referrer(self.model.delete_device(self.clientdata.device_id)) +end + +editdeviceparams = function( self ) + return controllerfunctions.handle_form(self, function() return self.model.get_editable_device_params(self.clientdata.device_id) end, self.model.set_editable_device_params, self.clientdata, "Save", "Edit Device Parameters", "Device Parameters Saved") +end + +overridedeviceparams = function( self ) + return controllerfunctions.handle_form(self, function() return self.model.get_device_params(self.clientdata.device_id) end, self.model.set_device_params, self.clientdata, "Save", "Override Device Parameters", "Device Parameters Saved") +end + +getdevicevalues = function( self ) + return self.model.get_device_values(self.clientdata.name) +end diff --git a/provisioning-createclass-html.lsp b/provisioning-createclass-html.lsp new file mode 120000 index 0000000..ae8c744 --- /dev/null +++ b/provisioning-createclass-html.lsp @@ -0,0 +1 @@ +provisioning-editclass-html.lsp \ No newline at end of file diff --git a/provisioning-createdevice-html.lsp b/provisioning-createdevice-html.lsp new file mode 120000 index 0000000..5fef703 --- /dev/null +++ b/provisioning-createdevice-html.lsp @@ -0,0 +1 @@ +provisioning-editdevice-html.lsp \ No newline at end of file diff --git a/provisioning-creategroup-html.lsp b/provisioning-creategroup-html.lsp new file mode 120000 index 0000000..efa6903 --- /dev/null +++ b/provisioning-creategroup-html.lsp @@ -0,0 +1 @@ +provisioning-editgroup-html.lsp \ No newline at end of file diff --git a/provisioning-createparam-html.lsp b/provisioning-createparam-html.lsp new file mode 120000 index 0000000..c936821 --- /dev/null +++ b/provisioning-createparam-html.lsp @@ -0,0 +1 @@ +provisioning-editparam-html.lsp \ No newline at end of file diff --git a/provisioning-createtemplate-html.lsp b/provisioning-createtemplate-html.lsp new file mode 120000 index 0000000..6797683 --- /dev/null +++ b/provisioning-createtemplate-html.lsp @@ -0,0 +1 @@ +provisioning-edittemplate-html.lsp \ No newline at end of file diff --git a/provisioning-editclass-html.lsp b/provisioning-editclass-html.lsp new file mode 100644 index 0000000..b9a7f5d --- /dev/null +++ b/provisioning-editclass-html.lsp @@ -0,0 +1,13 @@ +<% local form, viewlibrary, page_info = ... +require("viewfunctions") +%> + +

<%= html.html_escape(form.label) %>

+<% + if page_info.action == "editclass" then + form.value.class_id.readonly = "true" + else + form.value.class_id.type = "hidden" + end + displayform(form, nil, nil, page_info, 2) +%> diff --git a/provisioning-editdevice-html.lsp b/provisioning-editdevice-html.lsp new file mode 100644 index 0000000..571091b --- /dev/null +++ b/provisioning-editdevice-html.lsp @@ -0,0 +1,13 @@ +<% local form, viewlibrary, page_info = ... +require("viewfunctions") +%> + +

<%= html.html_escape(form.label) %>

+<% + if page_info.action == "editdevice" then + form.value.device_id.readonly = "true" + else + form.value.device_id.type = "hidden" + end + displayform(form, nil, nil, page_info, 2) +%> diff --git a/provisioning-editdeviceparams-html.lsp b/provisioning-editdeviceparams-html.lsp new file mode 100644 index 0000000..70c6e46 --- /dev/null +++ b/provisioning-editdeviceparams-html.lsp @@ -0,0 +1,11 @@ +<% local form, viewlibrary, page_info = ... +require("viewfunctions") +%> + +

<%= html.html_escape(form.label) %>

+<% + form.value.device_id.readonly = "true" + form.value.name.readonly = "true" + form.value.label.readonly = "true" + displayform(form, nil, nil, page_info, 2) +%> diff --git a/provisioning-editgroup-html.lsp b/provisioning-editgroup-html.lsp new file mode 100644 index 0000000..b75458c --- /dev/null +++ b/provisioning-editgroup-html.lsp @@ -0,0 +1,13 @@ +<% local form, viewlibrary, page_info = ... +require("viewfunctions") +%> + +

<%= html.html_escape(form.label) %>

+<% + if page_info.action == "editgroup" then + form.value.group_id.readonly = "true" + else + form.value.group_id.type = "hidden" + end + displayform(form, nil, nil, page_info, 2) +%> diff --git a/provisioning-editparam-html.lsp b/provisioning-editparam-html.lsp new file mode 100644 index 0000000..73fca08 --- /dev/null +++ b/provisioning-editparam-html.lsp @@ -0,0 +1,13 @@ +<% local form, viewlibrary, page_info = ... +require("viewfunctions") +%> + +

<%= html.html_escape(form.label) %>

+<% + if page_info.action == "editparam" then + form.value.param_id.readonly = "true" + else + form.value.param_id.type = "hidden" + end + displayform(form, nil, nil, page_info, 2) +%> diff --git a/provisioning-edittemplate-html.lsp b/provisioning-edittemplate-html.lsp new file mode 100644 index 0000000..df3b4d1 --- /dev/null +++ b/provisioning-edittemplate-html.lsp @@ -0,0 +1,27 @@ +<% local form, viewlibrary, page_info = ... %> +<% require("viewfunctions") %> + +

<%= html.html_escape(form.label) %>

+

Template Details

+<% form.action = page_info.script .. page_info.prefix .. page_info.controller .. "/" .. page_info.action %> +<% 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") +%> + + +

File Content

+
+ +<% if form.value.filecontent.errtxt then %>

<%= string.gsub(html.html_escape(form.value.filecontent.errtxt), "\n", "
") %>

<% end %> +<% if form.value.filecontent.descr then %>

<%= string.gsub(html.html_escape(form.value.filecontent.descr), "\n", "
") %>

<% end %> + +<% displayformend(form) %> diff --git a/provisioning-getdevicevalues-html.lsp b/provisioning-getdevicevalues-html.lsp new file mode 100644 index 0000000..7404ca7 --- /dev/null +++ b/provisioning-getdevicevalues-html.lsp @@ -0,0 +1,18 @@ +<% local form, viewlibrary, page_info = ... %> +<% require("viewfunctions") %> + +

<%= html.html_escape(form.label) %>

+<% +displayitem(form.value.device_id) +displayitem(form.value.name) +displayitem(form.value.label) +displayitem(form.value.template) +%> + +

Parameter Values

+<% +require("session") +val = session.serialize("values", form.value.values.value) +val = string.gsub(val, "[^\n]*%{%}\n", "") +print("
"..val.."
") +%> diff --git a/provisioning-html.lsp b/provisioning-html.lsp new file mode 120000 index 0000000..4b6b762 --- /dev/null +++ b/provisioning-html.lsp @@ -0,0 +1 @@ +../form-html.lsp \ No newline at end of file diff --git a/provisioning-listclasses-html.lsp b/provisioning-listclasses-html.lsp new file mode 100644 index 0000000..21a2b3f --- /dev/null +++ b/provisioning-listclasses-html.lsp @@ -0,0 +1,52 @@ +<% local view, viewlibrary, page_info, session = ... +require("viewfunctions") +%> + + + + + +<% displaycommandresults({"deleteclass", "editclass"}, session) %> +<% displaycommandresults({"createclass"}, session, true) %> + +

Classes

+
+ + + + + + + +<% for k,v in ipairs( view.value ) do %> + + + + + +<% end %> + +
ActionNameLabel
+ <% if viewlibrary.check_permission("deleteclass") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deleteclass?class_id="..v.class_id, label="Delete "} %> + <% end %> + <% if viewlibrary.check_permission("editclass") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editclass?class_id="..v.class_id.."&redir="..page_info.orig_action, label="Edit "} %> + <% end %> + <%= html.html_escape(v.name) %><%= html.html_escape(v.label) %>
+ +<% if view.errtxt then %> +

<%= html.html_escape(view.errtxt) %>

+<% end %> +<% if #view.value == 0 then %> +

No classes found

+<% end %> + +<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createclass") then + viewlibrary.dispatch_component("createclass") +end %> +
diff --git a/provisioning-listdevices-html.lsp b/provisioning-listdevices-html.lsp new file mode 100644 index 0000000..d93c096 --- /dev/null +++ b/provisioning-listdevices-html.lsp @@ -0,0 +1,57 @@ +<% local view, viewlibrary, page_info, session = ... +require("viewfunctions") +%> + + + + + +<% displaycommandresults({"deletedevice", "editdevice", "editdeviceparams", "overridedeviceparams"}, session) %> +<% displaycommandresults({"createdevice"}, session, true) %> + +

Devices

+
+ + + + + + + +<% for k,v in ipairs( view.value ) do %> + + + + + +<% end %> + +
ActionNameLabel
+ <% if viewlibrary.check_permission("deletedevice") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deletedevice?device_id="..v.device_id, label="Delete "} %> + <% end %> + <% if viewlibrary.check_permission("editdevice") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editdevice?device_id="..v.device_id.."&redir="..page_info.orig_action, label="Edit "} %> + <% end %> + <% if viewlibrary.check_permission("overridedeviceparams") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/overridedeviceparams?device_id="..v.device_id.."&redir="..page_info.orig_action, label="Params "} %> + <% elseif viewlibrary.check_permission("editdeviceparams") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editdeviceparams?device_id="..v.device_id.."&redir="..page_info.orig_action, label="Params "} %> + <% end %> + <%= html.html_escape(v.name) %><%= html.html_escape(v.label) %>
+ +<% if view.errtxt then %> +

<%= html.html_escape(view.errtxt) %>

+<% end %> +<% if #view.value == 0 then %> +

No devices found

+<% end %> + +<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createdevice") then + viewlibrary.dispatch_component("createdevice") +end %> +
diff --git a/provisioning-listgroups-html.lsp b/provisioning-listgroups-html.lsp new file mode 100644 index 0000000..df3887a --- /dev/null +++ b/provisioning-listgroups-html.lsp @@ -0,0 +1,54 @@ +<% local view, viewlibrary, page_info, session = ... +require("viewfunctions") +%> + + + + + +<% displaycommandresults({"deletegroup", "editgroup"}, session) %> +<% displaycommandresults({"creategroup"}, session, true) %> + +

Groups

+
+ + + + + + + + +<% for k,v in ipairs( view.value ) do %> + + + + + + +<% end %> + +
ActionNameLabelSequence
+ <% if viewlibrary.check_permission("deletegroup") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deletegroup?group_id="..v.group_id, label="Delete "} %> + <% end %> + <% if viewlibrary.check_permission("editgroup") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editgroup?group_id="..v.group_id.."&redir="..page_info.orig_action, label="Edit "} %> + <% end %> + <%= html.html_escape(v.name) %><%= html.html_escape(v.label) %><%= html.html_escape(v.seq) %>
+ +<% if view.errtxt then %> +

<%= html.html_escape(view.errtxt) %>

+<% end %> +<% if #view.value == 0 then %> +

No groups found

+<% end %> + +<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("creategroup") then + viewlibrary.dispatch_component("creategroup") +end %> +
diff --git a/provisioning-listparams-html.lsp b/provisioning-listparams-html.lsp new file mode 100644 index 0000000..ed35560 --- /dev/null +++ b/provisioning-listparams-html.lsp @@ -0,0 +1,54 @@ +<% local view, viewlibrary, page_info, session = ... +require("viewfunctions") +%> + + + + + +<% displaycommandresults({"deleteparam", "editparam"}, session) %> +<% displaycommandresults({"createparam"}, session, true) %> + +

Parameters

+
+ + + + + + + + +<% for k,v in ipairs( view.value ) do %> + + + + + + +<% end %> + +
ActionNameLabelSequence
+ <% if viewlibrary.check_permission("deleteparam") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/deleteparam?param_id="..v.param_id, label="Delete "} %> + <% end %> + <% if viewlibrary.check_permission("editparam") then %> + <%= html.link{value=page_info.script..page_info.prefix..page_info.controller.."/editparam?param_id="..v.param_id.."&redir="..page_info.orig_action, label="Edit "} %> + <% end %> + <%= html.html_escape(v.name) %><%= html.html_escape(v.label) %><%= html.html_escape(v.seq) %>
+ +<% if view.errtxt then %> +

<%= html.html_escape(view.errtxt) %>

+<% end %> +<% if #view.value == 0 then %> +

No parameters found

+<% end %> + +<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createparam") then + viewlibrary.dispatch_component("createparam") +end %> +
diff --git a/provisioning-listtemplates-html.lsp b/provisioning-listtemplates-html.lsp new file mode 100644 index 0000000..09e76df --- /dev/null +++ b/provisioning-listtemplates-html.lsp @@ -0,0 +1,59 @@ +<% local view, viewlibrary, page_info, session = ... +require("viewfunctions") +%> + + + + + +<% displaycommandresults({"deletetemplate", "edittemplate"}, session) %> +<% displaycommandresults({"createtemplate"}, session, true) %> + +

Templates

+
+ + + + + + + + + +<% for k,v in ipairs( view.value ) do %> + + + + + + + +<% end %> + +
ActionFile NameLabelFile SizeLast Modified
+ <% 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 "} %> + <% 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 "} %> + <% end %> + <% if viewlibrary.check_permission("createtemplate") and not v.template_id 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 %> + <%= html.html_escape(v.filename) %><%= html.html_escape(v.label) %><%= html.html_escape(v.filesize) %><%= html.html_escape(v.mtime) %>
+ +<% if view.errtxt then %> +

<%= html.html_escape(view.errtxt) %>

+<% end %> +<% if #view.value == 0 then %> +

No templates found

+<% end %> + +<% if viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createtemplate") then + viewlibrary.dispatch_component("createtemplate") +end %> +
diff --git a/provisioning-model.lua b/provisioning-model.lua new file mode 100644 index 0000000..6d3d480 --- /dev/null +++ b/provisioning-model.lua @@ -0,0 +1,1240 @@ +module (..., package.seeall) + +-- Load libraries +require("modelfunctions") +require("posix") +require("fs") +require("format") +require("validator") +require("luasql.postgres") +require("session") + +-- Set variables +local DatabaseName = "provisioning" +local DatabaseUser = "postgres" +local DatabasePassword + +local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin " +local baseurl = "/etc/provisioning/" +local env +local con + +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)", + }, + -- Multi-to-multi mapping of devices to classes +-- Need to enforce that devices do not contain more than one class with same name (using triggers) + devices_to_classes = { + "CREATE TABLE devices_to_classes (device_id INTEGER, class_id INTEGER)", + }, + -- List of different device classes + provisioning_classes = { + "CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE)", + }, + -- Multi-to-multi mapping of classes to parameter groups +-- Need to enforce that classes do not contain more than one parameter group with same name (using triggers) + classes_to_param_groups = { + "CREATE TABLE classes_to_param_groups (class_id INTEGER, group_id INTEGER)", + }, + -- 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)", + }, + -- Multi-to-multi mapping of parameter groups to parameters + param_groups_to_params = { + "CREATE TABLE param_groups_to_params (group_id INTEGER, param_id INTEGER, value VARCHAR(255), editable BOOLEAN)", + }, + -- 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)", +-- Add a way to include validation code and/or options list + }, + -- All of the (non-default) parameter values for all devices are stored here + provisioning_values = { + -- device_id is a device id from provisioning_devices and param_id is a param id from provisioning_params + "CREATE TABLE provisioning_values (device_id INTEGER, group_name VARCHAR(255), param_id INTEGER, value VARCHAR(255))", + }, + -- List of options for parameters + provisioning_options = { + "CREATE TABLE provisioning_options (param_id INTEGER, label VARCHAR(255), value VARCHAR(255), seq INTEGER)", + }, +-- Where do we define the triggers to enforce the foreign keys? Triggers are only on updates, so don't have to worry about read speed +-- Need to define indices +} + +--[[ +-- Get the values for a particular device for use +1) select * from provisioning_devices where name = 'devicename' +2) SELECT g.name AS group, p.name, 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 (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 USING(device_id, group_id, param_id) + WHERE d2t.device_id='yyyyy' + +-- Get the values for a particular device for editing in ACF +1) Same as above +2) Same as above, but replace step 2 first line with: +SELECT g.name AS group, p.name, p.type, p.label, p.descr, 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, CASE WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS default, g2p.seq, g.label AS grouplabel +3) Get the options + + + + +-- Get the params for a class of device +a = select * from provisioning_types where name = '%1' +b = select * from provisioning_params c, a.params d where c.pid = d.pid order by d.order +(loop through looking for type="group" or option ~= null) +to get the options, it is: +select * from e.option order by order + +-- Creating new param_table or option_table is just a matter of copying the schema (and indexes) of param_table or option_table +--]] + + + + +-- ################################################################################ +-- LOCAL FUNCTIONS +local function escape_quotes(str) + return string.gsub(str or "", "'", "'\\''") +end + +local function assert (v, m) + if not v then + m = m or "Assertion failed!" + error(m, 0) + end + return v, m +end + +-- Escape special characters in sql statements +local escape = function(sql) + sql = sql or "" + sql = string.gsub(sql, "'", "''") + return string.gsub(sql, "\\", "\\\\") +end + +local createdatabase = function() + local result = {} + + -- First, create the user + if DatabaseUser ~= "postgres" then + local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'' + if DatabasePassword then + cmd = cmd .. ' WITH PASSWORD \''..DatabasePassword..'\'' + end + cmd = cmd .. '" 2>&1' + local f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + end + + -- Create the database + local cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseUser..'" 2>&1' + local f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + + logevent(table.concat(result, "\n")) + + return table.concat(result, "\n") +end + +-- Delete the database and roles +local deletedatabase = function() + local result = {} + + local cmd = path..'psql -U postgres -c "DROP DATABASE '..DatabaseName..'" 2>&1' + local f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + + if DatabaseUser ~= "postgres" then + cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1' + f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + end + + logevent(table.concat(result, "\n")) + + return table.concat(result, "\n") +end + +local databaseconnect = function() + if not con then + -- create environment object + env = assert (luasql.postgres()) + -- connect to data source + local err + con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword) + if err and string.match(err, "Error connecting to database.") then + createdatabase() + con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword) + end + assert(con, err) + return true + end + return false +end + +local databasedisconnect = function() + if env then + env:close() + env = nil + end + if con then + con:close() + con = nil + end +end + +local runscript = function(script) + for i,scr in ipairs(script) do + logevent(scr) + assert( con:execute(scr) ) + end +end + +runsqlcommand = function(sql, in_transaction) +logevent(sql) + if in_transaction then assert(con:execute("SAVEPOINT before_command")) end + local res, err = con:execute(sql) + if not res and err then + -- Catch the error to see if it's caused by lack of table +logevent(err) + local table = string.match(err, "relation \"(%S+)\" does not exist") + if table and table_creation_scripts[table] then + if in_transaction then assert(con:execute("ROLLBACK TO before_command")) end + runscript(table_creation_scripts[table]) + runsqlcommand(sql) + else + if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end + assert(res, err) + end + else + if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end + end +end + +getselectresponse = function(sql, in_transaction) + local retval = {} + if in_transaction then assert(con:execute("SAVEPOINT before_select")) end + local res, err = pcall(function() +logevent(sql) + local cur = assert (con:execute(sql)) + local row = cur:fetch ({}, "a") + while row do + local tmp = {} + for name,val in pairs(row) do + tmp[name] = val + end + retval[#retval + 1] = tmp + row = cur:fetch (row, "a") + end + cur:close() + end) + if not res and err then +logevent(err) + -- Catch the error to see if it's caused by lack of table + local table = string.match(err, "relation \"(%S+)\" does not exist") + if table and table_creation_scripts[table] then + if in_transaction then assert(con:execute("ROLLBACK TO before_select")) end + runscript(table_creation_scripts[table]) + return getselectresponse(sql) + else + if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end + assert(res, err) + end + else + if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end + end + return retval +end + +-- ################################################################################ +-- PUBLIC FUNCTIONS + +list_templates = function() + local retval = {} + local errtxt + -- Get the templates from the DB + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_templates ORDER BY template_id ASC" + retval = getselectresponse(sql) + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + local reversed = {} + for i,t in ipairs(retval) do + reversed[t.filename] = i + end + -- Get the file stats for each template and add in any template files that aren't in the DB + for f in posix.files(baseurl) do + local file = baseurl..f + local details = fs.stat(file) + if details.type == "regular" then + if not reversed[file] then + retval[#retval+1] = {filename=file} + reversed[file] = #retval + end + local t = retval[reversed[file]] + t.filesize = details.size + t.mtime = details.mtime + end + end + + return cfe({ type="structure", value=retval, label="List of Templates", errtxt=errtxt }) +end + +get_template = function(template_id, 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"}) + local errtxt + if template_id and template_id ~= "" 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" + 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 + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + end + local filedetails = modelfunctions.getfiledetails(retval.filename.value, function(filename) return validator.is_valid_filename(filename, baseurl) end) + for i,n in ipairs({"filecontent", "filesize", "mtime"}) do + retval[n] = filedetails.value[n] + end + + return cfe({ type="group", value=retval, label="Provisioning Template", errtxt=errtxt }) +end + +create_template = function(template) + return update_template(template, true) +end + +update_template = function(template, create) + local success = true + local errtxt + -- Validate the settings + if template.value.filename and not string.match(template.value.filename.value, "/") then + template.value.filename.value = baseurl .. template.value.filename.value + end + if not validator.is_valid_filename(template.value.filename.value, baseurl) then + success = false + template.value.filename.errtxt = "Invalid filename" + end + if template.value.label.value == "" then + success = false + template.value.label.errtxt = "Cannot be blank" + 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 + end + if success then + if create then + sql = "INSERT INTO provisioning_templates VALUES(DEFAULT, '"..escape(template.value.filename.value).."', '"..escape(template.value.label.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).."'" + end + runsqlcommand(sql) + + fs.write_file(template.value.filename.value, template.value.filecontent.value) + end + if connected then databasedisconnect() end + end) + if not res and err then + success = false + errtxt = err + end + end + if not success then + if create then + template.errtxt = errtxt or "Failed to create template" + else + template.errtxt = errtxt or "Failed to save template" + end + end + return template +end + +delete_template = function(template_id) + 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 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).."'" + runsqlcommand(sql) + -- Delete the template file + os.remove(tmp[1].filename) + result = "Template Deleted" + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ value=result, errtxt=errtxt, label="Delete Template Result" }) +end + +list_classes = function() + local retval = {} + local errtxt + -- Get the classes from the DB + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_classes ORDER BY class_id ASC" + retval = getselectresponse(sql) + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="structure", value=retval, label="List of Classes", errtxt=errtxt }) +end + +get_class = function(class_id) + local retval = {} + retval.class_id = cfe({value=class_id or "", label="Class ID"}) + retval.name = cfe({label="Name"}) + retval.label = cfe({label="Label"}) + retval.groups = cfe({type="multi", value={}, label="Parameter Groups", option={}}) + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + if class_id and class_id ~= "" then + local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class_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 class-to-paramgroup mappings + sql = "SELECT group_id FROM classes_to_param_groups WHERE class_id="..escape(class_id) + tmp = getselectresponse(sql) + for i,g in ipairs(tmp) do + retval.groups.value[#retval.groups.value + 1] = g.group_id + end + end + -- Finally, get the paramgroup options + sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY label ASC" + tmp = getselectresponse(sql) + for i,g in ipairs(tmp) do + retval.groups.option[#retval.groups.option + 1] = {value=g.group_id, label=g.label, name=g.name} + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Provisioning Class", errtxt=errtxt }) +end + +create_class = function(class) + return update_class(class, true) +end + +update_class = function(class, create) + local success = true + local errtxt + -- Validate the settings + success = modelfunctions.validatemulti(class.value.groups) + if class.value.name.value == "" then + success = false + class.value.name.errtxt = "Cannot be blank" + end + if class.value.label.value == "" then + success = false + class.value.label.errtxt = "Cannot be blank" + end + if success then + local res, err = pcall(function() + local connected = databaseconnect() + if not create then + local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class.value.class_id.value).."'" + local tmp = getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + errtxt = "Class does not exist" + end + end + if success then + local sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + if create then + sql = "INSERT INTO provisioning_classes VALUES(DEFAULT, '"..escape(class.value.name.value).."', '"..escape(class.value.label.value).."')" + runsqlcommand(sql, true) + sql = "SELECT class_id FROM provisioning_classes WHERE name='"..escape(class.value.name.value).."' AND label='"..escape(class.value.label.value).."'" + local tmp = getselectresponse(sql, true) + if tmp and #tmp>0 then + class.value.class_id.value = tmp[1].class_id + end + else + sql = "UPDATE provisioning_classes SET (name, label) = ('"..escape(class.value.name.value).."', '"..escape(class.value.label.value).."') WHERE class_id='"..escape(class.value.class_id.value).."'" + runsqlcommand(sql, true) + sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..escape(class.value.class_id.value).."'" + runsqlcommand(sql, true) + end + -- Insert the class to group entries + for i,g in ipairs(class.value.groups.value) do + sql = "INSERT INTO classes_to_param_groups VALUES('"..escape(class.value.class_id.value).."', '"..escape(g).."')" + runsqlcommand(sql, true) + end + + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + pcall(function() con:execute("ROLLBACK") end) + success = false + errtxt = err + end + end + if not success then + if create then + class.errtxt = errtxt or "Failed to create class" + else + class.errtxt = errtxt or "Failed to save class" + end + end + return class +end + +delete_class = function(class_id) + local result = "" + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class_id).."'" + local tmp = getselectresponse(sql) + if #tmp == 0 then + errtxt = "Class does not exist" + else + sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + sql = "DELETE FROM provisioning_classes WHERE class_id='"..escape(class_id).."'" + runsqlcommand(sql, true) + sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..escape(class_id).."'" + runsqlcommand(sql, true) + result = "Class Deleted" + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ value=result, errtxt=errtxt, label="Delete Class Result" }) +end + +list_groups = function() + local retval = {} + local errtxt + -- Get the groups from the DB + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_groups ORDER BY seq ASC, name ASC, label ASC" + retval = getselectresponse(sql) + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="structure", value=retval, label="List of Parameter Groups", errtxt=errtxt }) +end + +get_group = function(group_id) + local retval = {} + retval.group_id = cfe({value=group_id or "", label="Group ID", seq=1}) + retval.name = cfe({label="Name", seq=2}) + retval.label = cfe({label="Label", seq=3}) + retval.seq = cfe({label="Sequence", seq=4}) + retval.params = cfe({type="multi", value={}, label="Parameters", option={}, descr="Each selected parameter will be included in the group", seq=5}) + retval.editable = cfe({type="multi", value={}, label="Editable Parameters", option={}, descr="Each selected parameter will be user editable", seq=6}) + retval.defaults = cfe({type="group", value={}, label="Parameter Defaults", seq=7}) + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + -- First, let's get all the parameters to set up the params.options and defaults + local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC" + local tmp = getselectresponse(sql) + for i,p in ipairs(tmp) do + retval.params.option[#retval.params.option + 1] = {value=p.param_id, label=p.label} + retval.editable.option[#retval.editable.option + 1] = {value=p.param_id, label=p.label} + p.seq = i + if p.type == "boolean" then + p.value = (p.value == "true") + end + retval.defaults.value[p.param_id] = p + end + if group_id and group_id ~= "" then + sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group_id).."'" + 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 paramgroup-to-param mappings + sql = "SELECT * FROM param_groups_to_params WHERE group_id="..escape(group_id) + tmp = getselectresponse(sql) + for i,p in ipairs(tmp) do + retval.params.value[#retval.params.value + 1] = p.param_id + if (p.editable == "t") then + retval.editable.value[#retval.editable.value + 1] = p.param_id + end + if retval.defaults.value[p.param_id].type == "boolean" then + retval.defaults.value[p.param_id].value = (p.value == "true") + else + retval.defaults.value[p.param_id].value = p.value + end + end + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Provisioning Parameter Group", errtxt=errtxt }) +end + +create_group = function(group) + return update_group(group, true) +end + +update_group = function(group, create) + local success = true + local errtxt + -- Validate the settings + success = modelfunctions.validatemulti(group.value.params) + if group.value.name.value == "" then + success = false + group.value.name.errtxt = "Cannot be blank" + end + if group.value.label.value == "" then + success = false + group.value.label.errtxt = "Cannot be blank" + end + if not validator.is_integer(group.value.seq.value) then + success = false + group.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_groups WHERE group_id='"..escape(group.value.group_id.value).."'" + local tmp = getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + errtxt = "Group does not exist" + end + end + if success then + local sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + if create then + sql = "INSERT INTO provisioning_groups VALUES(DEFAULT, '"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."')" + runsqlcommand(sql, true) + sql = "SELECT group_id FROM provisioning_groups WHERE name='"..escape(group.value.name.value).."' AND label='"..escape(group.value.label.value).."'" + local tmp = getselectresponse(sql, true) + if tmp and #tmp>0 then + group.value.group_id.value = tmp[1].group_id + end + else + sql = "UPDATE provisioning_groups SET (name, label, seq) = ('"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."') WHERE group_id='"..escape(group.value.group_id.value).."'" + runsqlcommand(sql, true) + sql = "DELETE FROM param_groups_to_params WHERE group_id='"..escape(group.value.group_id.value).."'" + runsqlcommand(sql, true) + end + -- Reverse the editable table for ease of use below + local reverseeditable = {} + for i,p in ipairs(group.value.editable.value) do + reverseeditable[p] = i + end + -- Insert the group to param entries + for i,p in ipairs(group.value.params.value) do + sql = "INSERT INTO param_groups_to_params VALUES('"..escape(group.value.group_id.value).."', '"..escape(p).."', '"..escape(tostring(group.value.defaults.value[p].value)).."', '"..tostring(reverseeditable[p] ~= nil).."')" + runsqlcommand(sql, true) + end + + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + pcall(function() con:execute("ROLLBACK") end) + success = false + errtxt = err + end + end + if not success then + if create then + group.errtxt = errtxt or "Failed to create parameter group" + else + group.errtxt = errtxt or "Failed to save parameter group" + end + end + return group +end + +delete_group = function(group_id) + local result = "" + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group_id).."'" + local tmp = getselectresponse(sql) + if #tmp == 0 then + errtxt = "Group does not exist" + else + sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + sql = "DELETE FROM provisioning_groups WHERE group_id='"..escape(group_id).."'" + runsqlcommand(sql, true) + sql = "DELETE FROM param_groups_to_params WHERE group_id='"..escape(group_id).."'" + runsqlcommand(sql, true) + result = "Parameter Group Deleted" + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ value=result, errtxt=errtxt, label="Delete Parameter Group Result" }) +end + +list_params = function() + local retval = {} + local errtxt + -- Get the params from the DB + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC, label ASC" + retval = getselectresponse(sql) + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="structure", value=retval, label="List of Parameters", errtxt=errtxt }) +end + +get_param = function(param_id) + local retval = {} + retval.param_id = cfe({value=param_id or "", label="Param ID", seq=1}) + retval.name = cfe({label="Name", seq=2}) + retval.type = cfe({type="select", label="Type", option={"text", "boolean"}, seq=3}) + retval.label = cfe({label="Label", seq=4}) + retval.descr = cfe({label="Description", seq=5}) + retval.value = cfe({label="Default Value", seq=6}) + retval.seq = cfe({label="Sequence", seq=7}) +-- FIXME - we should add validation and option stuff here + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + if param_id and param_id ~= "" then + sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'" + 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 + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Provisioning Parameter", errtxt=errtxt }) +end + +create_param = function(param) + return update_param(param, true) +end + +update_param = function(param, create) + local success = true + local errtxt + -- Validate the settings + success = modelfunctions.validateselect(param.value.type) + if param.value.name.value == "" then + success = false + param.value.name.errtxt = "Cannot be blank" + end + if param.value.label.value == "" then + success = false + param.value.label.errtxt = "Cannot be blank" + end + if not validator.is_integer(param.value.seq.value) then + success = false + param.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_params WHERE param_id='"..escape(param.value.param_id.value).."'" + local tmp = getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + errtxt = "Param does not exist" + end + end + if success then + local sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + if create then + sql = "INSERT INTO provisioning_params VALUES(DEFAULT, '"..escape(param.value.name.value).."', '"..escape(param.value.type.value).."', '"..escape(param.value.label.value).."', '"..escape(param.value.descr.value).."', '"..escape(param.value.value.value).."', '"..escape(param.value.seq.value).."')" + runsqlcommand(sql, true) + sql = "SELECT param_id FROM provisioning_params WHERE name='"..escape(param.value.name.value).."' AND label='"..escape(param.value.label.value).."'" + local tmp = getselectresponse(sql, true) + if tmp and #tmp>0 then + param.value.param_id.value = tmp[1].param_id + end + else + sql = "UPDATE provisioning_params SET (name, type, label, descr, value, seq) = ('"..escape(param.value.name.value).."', '"..escape(param.value.type.value).."', '"..escape(param.value.label.value).."', '"..escape(param.value.descr.value).."', '"..escape(param.value.value.value).."', '"..escape(param.value.seq.value).."') WHERE param_id='"..escape(param.value.param_id.value).."'" + runsqlcommand(sql, true) + end + + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + pcall(function() con:execute("ROLLBACK") end) + success = false + errtxt = err + end + end + if not success then + if create then + param.errtxt = errtxt or "Failed to create parameter" + else + param.errtxt = errtxt or "Failed to save parameter" + end + end + return param +end + +delete_param = function(param_id) + local result = "" + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'" + local tmp = getselectresponse(sql) + if #tmp == 0 then + errtxt = "Parameter does not exist" + else + sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + sql = "DELETE FROM provisioning_params WHERE param_id='"..escape(param_id).."'" + runsqlcommand(sql, true) + result = "Parameter Deleted" + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ value=result, errtxt=errtxt, label="Delete Parameter Result" }) +end + +list_devices = function() + local retval = {} + local errtxt + -- Get the devices from the DB + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_devices ORDER BY name ASC, label ASC" + retval = getselectresponse(sql) + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="structure", value=retval, label="List of Devices", errtxt=errtxt }) +end + +get_device = function(device_id) + local retval = {} + 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="multi", value={}, label="Classes", option={}, seq=5}) + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + if device_id and device_id ~= "" then + local sql = "SELECT * FROM provisioning_devices 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 + retval.classes.value[#retval.classes.value + 1] = g.class_id + end + end + -- Next, get the template options + sql = "SELECT template_id, label FROM provisioning_templates ORDER BY template_id 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 * FROM provisioning_classes ORDER BY class_id ASC" + tmp = getselectresponse(sql) + for i,c in ipairs(tmp) do + retval.classes.option[#retval.classes.option + 1] = {value=c.class_id, label=c.label} + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt }) +end + +create_device = function(device) + return update_device(device, true) +end + +update_device = function(device, create) + local success = true + local errtxt + -- Validate the settings + success = modelfunctions.validateselect(device.value.template_id) + success = modelfunctions.validatemulti(device.value.classes) and success + if device.value.name.value == "" then + success = false + device.value.name.errtxt = "Cannot be blank" + end + if success then + 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 tmp = getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + errtxt = "Device does not exist" + end + end + if success then + 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).."')" + 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) + if tmp and #tmp>0 then + 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).."'" + runsqlcommand(sql, true) + sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."'" + runsqlcommand(sql, true) + end + -- Insert the device to class entries + for i,c in ipairs(device.value.classes.value) do + sql = "INSERT INTO devices_to_classes VALUES('"..escape(device.value.device_id.value).."', '"..escape(c).."')" + runsqlcommand(sql, true) + end + + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + pcall(function() con:execute("ROLLBACK") end) + success = false + errtxt = err + end + end + if not success then + if create then + device.errtxt = errtxt or "Failed to create device" + else + device.errtxt = errtxt or "Failed to save device" + end + end + return device +end + +delete_device = function(device_id) + local result = "" + local errtxt + local res, err = pcall(function() + local connected = databaseconnect() + local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" + local tmp = getselectresponse(sql) + if #tmp == 0 then + errtxt = "Device does not exist" + else + sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + sql = "DELETE FROM provisioning_devices WHERE device_id='"..escape(device_id).."'" + runsqlcommand(sql, true) + sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device_id).."'" + runsqlcommand(sql, true) + result = "Device Deleted" + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + + return cfe({ value=result, errtxt=errtxt, label="Delete Device Result" }) +end + +get_editable_device_params = function(device_id) + return get_device_params(device_id, true) +end + +get_device_params = function(device_id, editable) + local retval = {} + retval.device_id = cfe({value=device_id or "", label="Device ID", seq=0}) + retval.name = cfe({label="Name", 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 tmp = getselectresponse(sql) + if tmp and #tmp > 0 then + errtxt = nil + retval.name.value = tmp[1].name + retval.label.value = tmp[1].label + -- Next, get all of the param groups + sql = "SELECT * FROM provisioning_groups" + local tmp = getselectresponse(sql) + -- Loop through the groups and put them into the result + for i,g in ipairs(tmp) do + retval[g.name] = g + retval[g.name].type="group" + retval[g.name].value={} + end + -- Then, get all of the parameters for this device + sql = "SELECT g.name AS group, p.param_id, p.name, p.type, p.label, p.descr, p.seq, 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, g2p.value AS default ".. + "FROM (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 ) ".. + "WHERE d2t.device_id='"..escape(device_id).."'" + if editable then + sql = sql.." AND g2p.editable='t'" + end + local tmp = getselectresponse(sql) + -- Loop through the params and put them into the groups + for i,p in ipairs(tmp) do + local value = retval[p.group].value + if p.type == "boolean" then + p.value = (p.value == "true") + end + value[#value+1] = p + end + -- Finally, loop through the result and remove empty groups + for name,val in pairs(retval) do + if #val.value == 0 then + retval[name] = nil + end + end + end + if connected then databasedisconnect() end + end) + if not res and err then + errtxt = err + end + end + + return cfe({ type="group", value=retval, label="Provisioning Device Parameters", errtxt=errtxt }) +end + +set_editable_device_params = function(params) + return set_device_params(params, true) +end + +set_device_params = function(params, editable) + local success = true + local errtxt + -- Validate the settings +-- FIXME + if success then + 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 tmp = getselectresponse(sql) + if not tmp or #tmp == 0 then + success = false + errtxt = "Device does not exist" + end + end + if success then + local sql = "BEGIN TRANSACTION" + runsqlcommand(sql) + if not editable then + -- Delete all values fro this device (can't do this if only updating editable) + sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(params.value.device_id.value).."'" + runsqlcommand(sql, true) + end + -- Loop through the groups and params + for group,v in pairs(params.value) do + if v.type == "group" then + for name,param in pairs(v.value) do + if editable then + sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(params.value.device_id.value).."' AND group_name='"..escape(group).."' AND param_id='"..escape(param.param_id).."'" + runsqlcommand(sql, true) + end + if tostring(param.value) ~= param.default then + sql = "INSERT INTO provisioning_values VALUES('"..escape(params.value.device_id.value).."', '"..escape(group).."', '"..escape(param.param_id).."', '"..escape(tostring(param.value)).."')" + runsqlcommand(sql, true) + end + end + end + end + + sql = "COMMIT" + runsqlcommand(sql) + end + if connected then databasedisconnect() end + end) + if not res and err then + pcall(function() con:execute("ROLLBACK") end) + success = false + errtxt = err + end + end + if not success then + params.errtxt = errtxt or "Failed to save device parameters" + end + return params +end + +get_device_values = function(name) + local retval = {} + 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 = 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 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 + + -- Next, get all of the parameters for this device + sql = "SELECT g.name AS group, p.name, p.type, 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 (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 ) ".. + "WHERE d2t.device_id='"..escape(tmp[1].device_id).."'" + local tmp = getselectresponse(sql) + -- Loop through the params and put them into the groups + for i,p in ipairs(tmp) do + if p.type == "boolean" then + p.value = (p.value == "true") + end + if not retval.values.value[p.group] then + retval.values.value[p.group] = {} + end + retval.values.value[p.group][p.name] = p.value + end + else + errtxt = "Invalid device name" + end + if connected then databasedisconnect() end + end + end) + if not res and err then + errtxt = err + end + + return cfe({ type="group", value=retval, label="Provisioning Device Parameter Values", errtxt=errtxt }) +end diff --git a/provisioning-overridedeviceparams-html.lsp b/provisioning-overridedeviceparams-html.lsp new file mode 120000 index 0000000..cd963d2 --- /dev/null +++ b/provisioning-overridedeviceparams-html.lsp @@ -0,0 +1 @@ +provisioning-editdeviceparams-html.lsp \ No newline at end of file diff --git a/provisioning.menu b/provisioning.menu new file mode 100644 index 0000000..350dd63 --- /dev/null +++ b/provisioning.menu @@ -0,0 +1,7 @@ +# Prefix and controller are already known at this point +# Cat Group Tab Action +Applications 89Provisioning Devices listdevices +Applications 89Provisioning Templates listtemplates +Applications 89Provisioning Classes listclasses +Applications 89Provisioning Param_Groups listgroups +Applications 89Provisioning Params listparams diff --git a/provisioning.roles b/provisioning.roles new file mode 100644 index 0000000..911c947 --- /dev/null +++ b/provisioning.roles @@ -0,0 +1,5 @@ +GUEST=provisioning:getdevicevalues +USER= +EDITOR=provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:deletedevice +EXPERT=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:overridedeviceparams +ADMIN=provisioning:listtemplates,provisioning:edittemplate,provisioning:createtemplate,provisioning:deletetemplate,provisioning:listdevices,provisioning:editdevice,provisioning:createdevice,provisioning:deletedevice,provisioning:listclasses,provisioning:editclass,provisioning:createclass,provisioning:deleteclass,provisioning:listgroups,provisioning:editgroup,provisioning:creategroup,provisioning:deletegroup,provisioning:listparams,provisioning:editparam,provisioning:createparam,provisioning:deleteparam,provisioning:editdeviceparams,provisioning:overridedeviceparams -- cgit v1.2.3