Initial commit of ACF library for viewing/editing database entries

Based on code from acf-kamailio

6 files changed, 380 insertions, 0 deletions

diff --git a/Makefile b/Makefile
new file mode 100644
index 0000000..eb3696c
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,38 @@
+include config.mk
+
+LIB_DIST=dbcontrollerfunctions.lua\
+	dbmodelfunctions.lua\
+	db-*-html.lsp\
+
+EXTRA_DIST=README Makefile
+DISTFILES=$(LIB_DIST) $(EXTRA_DIST)
+
+install_dir=$(DESTDIR)/$(acflibdir)
+dist_dir=$(DISTDIR)/$(notdir $(PWD))
+
+phony+=all
+all:
+
+phony+=clean
+clean:
+
+phony+=distdir
+distdir: $(DISTFILES)
+	mkdir -p "$(dist_dir)"
+	for i in $(DISTFILES); do\
+		dest=`dirname "$(dist_dir)/$$i"`;\
+		mkdir -p "$$dest";\
+		cp "$$i" "$$dest";\
+	done
+
+phony+=install
+install:
+	mkdir -p $(install_dir)
+	for i in $(LIB_DIST); do\
+		dest=`dirname "$(install_dir)/$$i"`;\
+		mkdir -p "$$dest";\
+		cp "$$i" "$$dest";\
+	done
+
+
+.PHONY: $(phony)
diff --git a/config.mk b/config.mk
new file mode 100644
index 0000000..00e2773
--- /dev/null
+++ b/config.mk
@@ -0,0 +1,12 @@
+prefix=/usr
+datadir=${prefix}/share
+sysconfdir=${prefix}/etc
+localstatedir=${prefix}/var
+acfdir=${datadir}/acf
+wwwdir=${acfdir}/www
+cgibindir=${wwwdir}/cgi-bin
+appdir=${acfdir}/app
+acflibdir=${acfdir}/lib
+sessionsdir=${localstatedir}/lib/acf/sessions
+bindir=${prefix}/bin
+luadir=${prefix}/share/lua/5.1/acf
diff --git a/db-listtables-html.lsp b/db-listtables-html.lsp
new file mode 100644
index 0000000..fec3ac4
--- /dev/null
+++ b/db-listtables-html.lsp
@@ -0,0 +1,24 @@
+<% local form, viewlibrary, page_info, session = ... %>
+<% require("htmlviewfunctions") %>
+<% html = require("acf.html") %>
+
+<% htmlviewfunctions.displaycommandresults({"createdatabase"}, session) %>
+
+<H1><%= html.html_escape(form.label) %></H1>
+<DL>
+<% for i,table in ipairs(form.value) do %>
+	<li>
+	<% if viewlibrary.check_permission("viewtable") then %>
+		<%= html.link{value = "viewtable?table=" .. table, label=table} %>
+	<% else %>
+		<%= html.html_escape(table) %>
+	<% end %>
+<% end %>
+<% if #form.value == 0 and viewlibrary.check_permission("createdatabase") then %>
+<DT>Create Database</DT><DD>
+<form action="<%= html.html_escape(page_info.script .. page_info.prefix .. page_info.controller .. "/createdatabase") %>">
+<input class="submit" type="submit" name="submit" value="Create"></DD>
+</form>
+</DD>
+<% end %>
+</DL>
diff --git a/db-viewtable-html.lsp b/db-viewtable-html.lsp
new file mode 100644
index 0000000..e1531d0
--- /dev/null
+++ b/db-viewtable-html.lsp
@@ -0,0 +1,55 @@
+<% local form, viewlibrary, page_info, session = ... %>
+<% require("htmlviewfunctions") %>
+<% html = require("acf.html") %>
+
+<% htmlviewfunctions.displaycommandresults({"deletetableentry", "updatetableentry"}, session) %>
+<% htmlviewfunctions.displaycommandresults({"createtableentry"}, session, true) %>
+
+<H1><%= html.html_escape(form.label) %> - <%= html.html_escape(form.value.table.value) %></H1>
+<DL>
+<TABLE>
+	<TR style="background:#eee;font-weight:bold;">
+	<% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %>
+		<TD style="padding-right:20px;white-space:nowrap;" class="header">Action</TD>
+	<% end %>
+	<% for i,f in ipairs(form.value.fields.value) do %>
+		<TD style="padding-right:20px;white-space:nowrap;" class="header"><%= html.html_escape(f) %></TD>
+	<% end %>
+	</TR>
+
+<% for i,tableentry in ipairs(form.value.entries.value) do %>
+	<TR>
+	<% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %>
+		<TD style="padding-right:20px;white-space:nowrap;">
+	<% if viewlibrary.check_permission("updatetableentry") then %>
+		<form action="updatetableentry" method="POST">
+                <input class="hidden" type="hidden" name="table" value="<%= html.html_escape(form.value.table.value) %>">
+                <input class="hidden" type="hidden" name="id" value="<%= html.html_escape(tableentry.id) %>">
+                <input class="hidden" type="hidden" name="redir" value="<%= html.html_escape(page_info.orig_action.."?table="..form.value.table.value) %>">
+                <input class="submit" type="submit" value="Update"></form>
+	<% end %>
+	<% if viewlibrary.check_permission("deletetableentry") then %>
+		<form action="deletetableentry" method="POST">
+                <input class="hidden" type="hidden" name="table" value="<%= html.html_escape(form.value.table.value) %>">
+                <input class="hidden" type="hidden" name="id" value="<%= html.html_escape(tableentry.id) %>">
+                <input class="submit" type="submit" name="submit" value="Delete"></form>
+	<% end %>
+		</TD>
+	<% end %>
+	<% for i,f in ipairs(form.value.fields.value) do %>
+		<TD><%= html.html_escape(tableentry[f]) %></TD>
+	<% end %>
+	</TR>
+<% end %>
+</TABLE>
+<% if form.errtxt then %>
+<p class="error"><%= html.html_escape(form.errtxt) %></p>
+<% end %>
+<% if #form.value.entries.value == 0 then %>
+<p>No entries found</p>
+<% end %>
+</DL>
+
+<% if page_info.action == "viewtable" and viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createtableentry") then
+	viewlibrary.dispatch_component("createtableentry", {table=form.value.table.value})
+end %>
diff --git a/dbcontrollerfunctions.lua b/dbcontrollerfunctions.lua
new file mode 100644
index 0000000..6d83bd4
--- /dev/null
+++ b/dbcontrollerfunctions.lua
@@ -0,0 +1,21 @@
+module(..., package.seeall)
+
+function listtables(self)
+	return self.model.list_tables()
+end
+
+function viewtable(self)
+	return self.model.list_table_entries(self, self.clientdata)
+end
+
+function deletetableentry(self)
+	return self.handle_form(self, self.model.get_delete_table_entry, self.model.delete_table_entry, self.clientdata, "Delete", "Delete Table Entry", "Table Entry deleted")
+end
+
+function updatetableentry(self)
+	return self.handle_form(self, self.model.get_table_entry, self.model.update_table_entry, self.clientdata, "Update", "Update Table Entry", "Entry updated")
+end
+
+function createtableentry(self)
+	return self.handle_form(self, self.model.get_new_table_entry, self.model.create_table_entry, self.clientdata, "Create", "Create New Table Entry", "Entry created")
+end
diff --git a/dbmodelfunctions.lua b/dbmodelfunctions.lua
new file mode 100644
index 0000000..3bc2382
--- /dev/null
+++ b/dbmodelfunctions.lua
@@ -0,0 +1,230 @@
+module(..., package.seeall)
+-- Helper library for model functions for viewing and editing database entries
+-- Relies heavily on the acf-lib/db.lua library (db object passed into each function)
+
+function list_tables(db)
+	local retval = {}
+        local errtxt
+	local res, err = pcall(function()
+		local connected = db.databaseconnect()
+		retval = db.listtables()
+		if connected then db.databasedisconnect() end
+	end)
+	if not res and err then
+		errtxt = err
+	end
+	
+	return cfe({ type="list", value=retval, label="List of Database Tables", errtxt=errtxt })
+end
+
+function list_table_entries(db, self, clientdata)
+	local table = clientdata.table
+	local retval = {}
+	retval.table = cfe({ value=table or "", label="Table" })
+	retval.fields = cfe({ type="list", value={}, label="List of Table Fields" })
+	retval.entries = cfe({ type="structure", value={}, label="List of Database Entries" })
+        local errtxt
+	local res, err = pcall(function()
+		local connected = db.databaseconnect()
+		local tables = db.listtables()
+		retval.table.errtxt = "Table does not exist"
+		errtxt = "Table does not exist"
+		for i,t in ipairs(tables) do
+			if t == table then
+				retval.table.errtxt = nil
+				errtxt = nil
+				retval.entries.value = db.getselectresponse("SELECT * FROM "..db.escape(table).." ORDER BY id ASC") or {}
+				retval.fields.value = db.listcolumns(table) or {}
+			end
+		end
+		if connected then db.databasedisconnect() end
+	end)
+	if not res and err then
+		errtxt = err
+	end
+	
+	return cfe({ type="group", value=retval, label="Database Table Entries", errtxt=errtxt })
+end
+
+function get_new_table_entry(db, self, clientdata)
+	local retval = {}
+	retval.table = cfe({ value=clientdata.table or "", label="Table", errtxt="Table does not exist", readonly=true, seq=0 })
+	local errtxt = "Table does not exist"
+	if clientdata.table and clientdata.table ~= "" then
+		local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			local tables = db.listtables()
+			for i,t in ipairs(tables) do
+				if t == clientdata.table then
+					retval.table.errtxt = nil
+					errtxt = nil
+					break
+				end
+			end
+			if not errtxt then
+				local fields = db.listcolumns(clientdata.table)
+				for i,f in ipairs(fields) do
+					retval[f] = cfe({ label=f, seq=i })
+				end
+				if retval.id then
+					retval.id.type = "hidden"
+				end
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			errtxt = err
+		end
+	end
+
+	return cfe({ type="group", value=retval, label="Database Table Entry", errtxt=errtxt })
+end
+
+function get_table_entry(db, self, clientdata)
+	local retval = get_new_table_entry(db, self, clientdata)
+	if not retval.value.table.errtxt and retval.value.id then
+		retval.value.id.type = "text"
+		local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			retval.errtxt = "Entry does not exist"
+			if retval.value.id then
+				retval.value.id.value = clientdata.id or ""
+				retval.value.id.errtxt = "Entry does not exist"
+				if clientdata.id and clientdata.id ~= "" then
+					local entry = db.getselectresponse("SELECT * FROM "..db.escape(clientdata.table).." WHERE id='"..db.escape(clientdata.id).."'")
+					if entry and #entry > 0 then
+						for n,v in pairs(entry[1]) do
+							if retval.value[n] then retval.value[n].value = v end
+							if n == "id" then
+								retval.value.id.readonly = true
+								retval.value.id.errtxt = nil
+								retval.errtxt = nil
+							end
+						end
+					end
+				end
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			retval.errtxt = err
+		end
+	end
+
+	return retval
+end
+
+function create_table_entry(db, self, entry, action)
+        return update_table_entry(db, self, entry, action, true)
+end
+
+function update_table_entry(db, self, entry, action, create)
+	local success = true
+	local errtxt
+	-- Validate the settings
+	-- relying on get_table_entry to do the validation of table
+	if entry.value.table.value == "" or entry.value.table.errtxt then
+		success = false
+		entry.value.table.errtxt = "Table does not exist"
+	end
+	if not create then
+		if not entry.value.id then
+			success = false
+		elseif not entry.value.id.value or entry.value.id.value == "" then
+			success = false
+			entry.value.id.errtxt = "Invalid id"
+		end
+	end
+	if success then
+        	local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			local tables = db.listtables()
+			success = false
+			entry.value.table.errtxt = "Table does not exist"
+			for i,t in ipairs(tables) do
+				if t == entry.value.table.value then
+					success = true
+					entry.value.table.errtxt = nil
+					break
+				end
+			end
+			if success and not create then
+				local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.id.value).."'"
+				local tmp = db.getselectresponse(sql)
+				if not tmp or #tmp == 0 then
+					success = false
+					entry.value.id.errtxt = "Entry does not exist"
+					errtxt = "Entry does not exist"
+				end
+			end
+			if success then
+				local names = {}
+				local values = {}
+				for n,v in pairs(entry.value) do
+					if n ~= "table" and n ~= "id" then
+						names[#names+1] = db.escape(n)
+						values[#values+1] = db.escape(v.value)
+					end
+				end
+				if create then
+					sql = "INSERT INTO "..db.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')"
+				else
+					sql = "UPDATE "..db.escape(entry.value.table.value).." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..db.escape(entry.value.id.value).."'"
+				end
+				db.runsqlcommand(sql)
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			success = false
+			errtxt = err
+		end
+	end
+	if not success then
+		if create then
+			entry.errtxt = errtxt or "Failed to create entry"
+		else
+			entry.errtxt = errtxt or "Failed to save entry"
+		end
+	end
+	return entry
+end
+
+function get_delete_table_entry(db, self, clientdata)
+	local retval = {}
+	retval.table = cfe({ value=clientdata.table or "", label="Table", seq=0 })
+	retval.id = cfe({ value=clientdata.id or "", label="ID", seq=1 })
+	return cfe({ type="group", value=retval, label="Delete Database Table Entry" })
+end
+
+function delete_table_entry(db, self, entry)
+	entry.errtxt = "Failed to delete table entry"
+	if entry.value.table.value == "" then
+		entry.value.table.errtxt = "Invalid table"
+	elseif entry.value.id.value == "" then
+		entry.value.id.errtxt = "Invalid entry"
+	else
+	       	local res, err = pcall(function()
+			local connected = db.databaseconnect()
+			entry.value.table.errtxt = "Invalid table"
+			local tables = db.listtables()
+			for i,t in ipairs(tables) do
+				if t == entry.value.table.value then
+					entry.value.table.errtxt = nil
+					break
+				end
+			end
+			if not entry.value.table.errtxt then
+				local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.id.value).."'"
+				db.runsqlcommand(sql)
+				entry.errtxt = nil
+			end
+			if connected then db.databasedisconnect() end
+		end)
+		if not res and err then
+			entry.errtxt = err
+		end
+	end
+
+	return entry
+end