From fa0bd17a19e211fb331e5a0d6dd5398d2d3930f1 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Fri, 27 Sep 2013 03:28:54 +0000 Subject: Initial commit of ACF library for viewing/editing database entries Based on code from acf-kamailio --- Makefile | 38 ++++++++ config.mk | 12 +++ db-listtables-html.lsp | 24 +++++ db-viewtable-html.lsp | 55 +++++++++++ dbcontrollerfunctions.lua | 21 +++++ dbmodelfunctions.lua | 230 ++++++++++++++++++++++++++++++++++++++++++++++ 6 files changed, 380 insertions(+) create mode 100644 Makefile create mode 100644 config.mk create mode 100644 db-listtables-html.lsp create mode 100644 db-viewtable-html.lsp create mode 100644 dbcontrollerfunctions.lua create mode 100644 dbmodelfunctions.lua 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) %> + +

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

+
+<% for i,table in ipairs(form.value) do %> +
  • + <% 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 %> +
    Create Database
    +
    "> +
    + + +<% end %> +
    • 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) %> + +

    <%= html.html_escape(form.label) %> - <%= html.html_escape(form.value.table.value) %>

    +
    + + + <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> + + <% end %> + <% for i,f in ipairs(form.value.fields.value) do %> + + <% end %> + + +<% for i,tableentry in ipairs(form.value.entries.value) do %> + + <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> + + <% end %> + <% for i,f in ipairs(form.value.fields.value) do %> + + <% end %> + +<% end %> +
    Action<%= html.html_escape(f) %>
    + <% if viewlibrary.check_permission("updatetableentry") then %> +
    + + + +
    + <% end %> + <% if viewlibrary.check_permission("deletetableentry") then %> +
    + + +
    + <% end %> +     		<%= html.html_escape(tableentry[f]) %>
    +<% if form.errtxt then %> +

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

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

    No entries found

    +<% end %> +
    + +<% 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 -- cgit v1.2.3