diff options
| -rw-r--r-- | Makefile | 38 | ||||
| -rw-r--r-- | config.mk | 12 | ||||
| -rw-r--r-- | db-listtables-html.lsp | 24 | ||||
| -rw-r--r-- | db-viewtable-html.lsp | 55 | ||||
| -rw-r--r-- | dbcontrollerfunctions.lua | 21 | ||||
| -rw-r--r-- | dbmodelfunctions.lua | 230 |
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 |