From 282d068aced3f88956af4182a353f8ea150caa2b Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Tue, 28 Oct 2014 19:03:59 +0000 Subject: Move the lib files into a subdirectory in preparation for creating a generic controller/model --- Makefile | 39 ++--- README | 1 + db-listdatabases-html.lsp | 68 -------- db-listtables-html.lsp | 66 -------- db-viewtable-html.lsp | 111 ------------- dbcontrollerfunctions.lua | 27 ---- dbmodelfunctions.lua | 366 ------------------------------------------ lib/Makefile | 29 ++++ lib/db-listdatabases-html.lsp | 68 ++++++++ lib/db-listtables-html.lsp | 66 ++++++++ lib/db-viewtable-html.lsp | 111 +++++++++++++ lib/dbcontrollerfunctions.lua | 27 ++++ lib/dbmodelfunctions.lua | 366 ++++++++++++++++++++++++++++++++++++++++++ 13 files changed, 685 insertions(+), 660 deletions(-) create mode 100644 README delete mode 100644 db-listdatabases-html.lsp delete mode 100644 db-listtables-html.lsp delete mode 100644 db-viewtable-html.lsp delete mode 100644 dbcontrollerfunctions.lua delete mode 100644 dbmodelfunctions.lua create mode 100644 lib/Makefile create mode 100644 lib/db-listdatabases-html.lsp create mode 100644 lib/db-listtables-html.lsp create mode 100644 lib/db-viewtable-html.lsp create mode 100644 lib/dbcontrollerfunctions.lua create mode 100644 lib/dbmodelfunctions.lua diff --git a/Makefile b/Makefile index eb3696c..c9eca99 100644 --- a/Makefile +++ b/Makefile @@ -1,14 +1,14 @@ -include config.mk +APP_NAME=db +PACKAGE=acf-$(APP_NAME) +VERSION=0.1.0 -LIB_DIST=dbcontrollerfunctions.lua\ - dbmodelfunctions.lua\ - db-*-html.lsp\ +APP_DIST=db* \ -EXTRA_DIST=README Makefile -DISTFILES=$(LIB_DIST) $(EXTRA_DIST) +SUBDIRS=lib +EXTRA_DIST=README Makefile config.mk +DISTFILES=$(APP_DIST) $(EXTRA_DIST) -install_dir=$(DESTDIR)/$(acflibdir) -dist_dir=$(DISTDIR)/$(notdir $(PWD)) +install_dir=$(DESTDIR)/$(appdir)/$(APP_NAME) phony+=all all: @@ -16,23 +16,18 @@ 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";\ +export DISTDIR DESTDIR +phony+=install-recursive +install-recursive: + for dir in $(SUBDIRS); do\ + ( cd $$dir && $(MAKE) install ) || exit 1;\ 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 +install: install-recursive $(SUBDIRS) + mkdir -p "$(install_dir)" + cp -a $(APP_DIST) "$(install_dir)" +include config.mk .PHONY: $(phony) diff --git a/README b/README new file mode 100644 index 0000000..40bada4 --- /dev/null +++ b/README @@ -0,0 +1 @@ +ACF library for editing databases diff --git a/db-listdatabases-html.lsp b/db-listdatabases-html.lsp deleted file mode 100644 index dcc9dcf..0000000 --- a/db-listdatabases-html.lsp +++ /dev/null @@ -1,68 +0,0 @@ -<% local form, viewlibrary, page_info, session = ... %> -<% htmlviewfunctions = require("htmlviewfunctions") %> -<% html = require("acf.html") %> - -<% if form.value.databases and #form.value.databases.value>0 then %> - - - - - -<% end %> - -<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %> -<% if form.value.connection then - -- hide the database, user, and password, since we don't need them to list databases - form.value.connection.value.database = nil - form.value.connection.value.user = nil - form.value.connection.value.password = nil -end %> -<% if form.value.connection and next(form.value.connection.value) ~= nil then - htmlviewfunctions.displayformstart(form, page_info) - htmlviewfunctions.displayitem(form.value.connection, page_info, htmlviewfunctions.incrementheader(header_level), "connection") - form.option = "Update" - htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level)) -end %> -<% if form.value.databases and #form.value.databases.value>0 then %> - - - - - - -<% -- We need to pass the connection key values to listtables -local formvalues = {} -if form.value.connection then - formvalues.connection = form.value.connection - for n,v in pairs(form.value.connection.value) do v.type="hidden" end -end -form.value.connection.value.database = cfe({ type="hidden" }) -%> -<% for i,dbase in ipairs(form.value.databases.value) do %> - <% formvalues.connection.value.database.value = dbase %> - - - - -<% end %> -
ActionDatabase
- <% if viewlibrary.check_permission("listtables") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="View", action="listtables"}), page_info, -1) %> - <% end %> - <%= html.html_escape(dbase) %>
-<% else %> -

No databases found

-<% end %> -<% htmlviewfunctions.displaysectionend(header_level) %> diff --git a/db-listtables-html.lsp b/db-listtables-html.lsp deleted file mode 100644 index f0db6b7..0000000 --- a/db-listtables-html.lsp +++ /dev/null @@ -1,66 +0,0 @@ -<% local form, viewlibrary, page_info, session = ... %> -<% htmlviewfunctions = require("htmlviewfunctions") %> -<% html = require("acf.html") %> - -<% if form.value.tables and #form.value.tables.value>0 then %> - - - - - -<% end %> - -<% htmlviewfunctions.displaycommandresults({"createdatabase"}, session) %> - -<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %> -<% if form.value.connection and next(form.value.connection.value) ~= nil then - htmlviewfunctions.displayformstart(form, page_info) - htmlviewfunctions.displayitem(form.value.connection, page_info, htmlviewfunctions.incrementheader(header_level), "connection") - form.option = "Update" - htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level)) -end %> -<% if form.value.tables and #form.value.tables.value>0 then %> - - - - - - -<% -- We will reuse the form connection structure to pass key values to viewtable -local formvalues = {} -if form.value.connection then - formvalues.connection = form.value.connection - for n,v in pairs(form.value.connection.value) do v.type="hidden" end -end -formvalues.table = cfe({ type="hidden" }) -%> -<% for i,tab in ipairs(form.value.tables.value) do %> - <% formvalues.table.value = tab %> - - - - -<% end %> -
ActionTable
- <% if viewlibrary.check_permission("viewtable") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="View", action="viewtable"}), page_info, -1) %> - <% end %> - <%= html.html_escape(tab) %>
-<% elseif viewlibrary.check_permission("createdatabase") then %> - <% htmlviewfunctions.displayitem(cfe({type="form", value={}, label="Create Database", option="Create", action="createdatabase" }), page_info, 0) %> -<% else %> -

No tables found

-<% end %> -<% htmlviewfunctions.displaysectionend(header_level) %> diff --git a/db-viewtable-html.lsp b/db-viewtable-html.lsp deleted file mode 100644 index 3bde35e..0000000 --- a/db-viewtable-html.lsp +++ /dev/null @@ -1,111 +0,0 @@ -<% local form, viewlibrary, page_info, session = ... %> -<% htmlviewfunctions = require("htmlviewfunctions") %> -<% html = require("acf.html") %> - - - - - - - -<% -local redir = cfe({ type="hidden", value=page_info.orig_action.."?table="..html.url_encode(form.value.table.value) }) -local keyvalues = {table=form.value.table.value} -if form.value.connection then - keyvalues.connection = {} - for n,v in pairs(form.value.connection.value) do - redir.value = redir.value.."&connection."..n.."="..html.url_encode(v.value) - keyvalues.connection[n] = v.value - end -end -keyvalues.redir = redir.value -%> - -<% htmlviewfunctions.displaycommandresults({"deletetableentry", "updatetableentry"}, session) %> -<% htmlviewfunctions.displaycommandresults({"createtableentry"}, session, true) %> - -<% if form.value.table.value ~= "" then form.label = form.label.." - "..form.value.table.value end %> -<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %> -<% -local header_level2 = htmlviewfunctions.incrementheader(header_level) -htmlviewfunctions.displayformstart(form, page_info) -if form.value.connection and next(form.value.connection.value) ~= nil then - htmlviewfunctions.displayitem(form.value.connection, page_info, header_level2, "connection") -end -htmlviewfunctions.displayitem(form.value.table, page_info, header_level2, "table") -form.option = "Update" -htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level)) -%> - - - <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> - - <% end %> - <% for i,f in ipairs(form.value.fields.value) do %> - - <% end %> - - -<% -- We will reuse the form connection structure to pass key values to updatetableentry and deletetableentry -local formvalues = {} -if form.value.connection then - formvalues.connection = form.value.connection - for n,v in pairs(form.value.connection.value) do v.type="hidden" end -end -if form.value.keyfields then - formvalues.fields = cfe({ type="group", value={} }) - for i,f in ipairs(form.value.keyfields.value) do - formvalues.fields.value[f] = cfe({ type="hidden" }) - end -end -formvalues.table = form.value.table -form.value.table.type = "hidden" -formvalues.redir = redir -%> -<% for i,tableentry in ipairs(form.value.entries.value) do %> - - <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> - <% - if formvalues.fields then - for i,f in ipairs(form.value.keyfields.value) do - formvalues.fields.value[f].value = tableentry[f] - end - end - %> - - <% end %> - <% for i,f in ipairs(form.value.fields.value) do %> - - <% end %> - -<% end %> -
Action<%= html.html_escape(f) %>
- <% if viewlibrary.check_permission("updatetableentry") then %> - <% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="Update", action="updatetableentry"}), page_info, -1) %> - <% end %> - <% if viewlibrary.check_permission("deletetableentry") then %> - <% htmlviewfunctions.displayitem(cfe({type="form", value=formvalues, label="", option="Delete", action="deletetableentry", class="deletetableentry"}), page_info, -1) %> - <% end %> - <%= html.html_escape(tableentry[f]) %>
-<% htmlviewfunctions.displayinfo(form) %> -<% if #form.value.entries.value == 0 then %> -

No entries found

-<% end %> -<% htmlviewfunctions.displaysectionend(header_level) %> - -<% if page_info.action == "viewtable" and viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createtableentry") then - viewlibrary.dispatch_component("createtableentry", keyvalues) -end %> diff --git a/dbcontrollerfunctions.lua b/dbcontrollerfunctions.lua deleted file mode 100644 index 913919e..0000000 --- a/dbcontrollerfunctions.lua +++ /dev/null @@ -1,27 +0,0 @@ -local mymodule = {} - -function mymodule.listdatabases(self) - return self.model.list_databases(self, self.clientdata) -end - -function mymodule.listtables(self) - return self.model.list_tables(self, self.clientdata) -end - -function mymodule.viewtable(self) - return self.model.list_table_entries(self, self.clientdata) -end - -function mymodule.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 mymodule.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 mymodule.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 - -return mymodule diff --git a/dbmodelfunctions.lua b/dbmodelfunctions.lua deleted file mode 100644 index d9c8689..0000000 --- a/dbmodelfunctions.lua +++ /dev/null @@ -1,366 +0,0 @@ -local mymodule = {} - -db = require("acf.db") - --- 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) - -local function get_connection(dbase, self, clientdata) - local retval = cfe({ type="group", value={} }) - retval.value.connection = cfe({ type="group", value={}, label="Database Connection", seq=0 }) - retval.value.connection.value.engine = cfe({ type="select", label="Database Engine", option={}, key=true, seq=1 }) - for n,v in pairs(db.engine) do - retval.value.connection.value.engine.option[#retval.value.connection.value.engine.option+1] = {name=n, value=v} - end - retval.value.connection.value.database = cfe({ label="Database", key=true, seq=2 }) - retval.value.connection.value.user = cfe({ label="User", key=true, seq=3 }) - retval.value.connection.value.password = cfe({ type="password", label="Password", key=true, seq=4 }) - retval.value.connection.value.host = cfe({ label="Host", key=true, seq=5 }) - retval.value.connection.value.port = cfe({ label="Port", key=true, seq=6 }) - - self.handle_clientdata(retval, clientdata) - - -- If dbase has a default, remove the cfe. Otherwise, fill in with the value from the cfe. - for n,v in pairs(retval.value.connection.value) do - if dbase[n] then - retval.value.connection.value[n] = nil - else - dbase[n] = v.value - end - end - - return retval -end - -local function fill_connection(dbase, self, retval) - -- Fill in dbase with the values from the cfe retval - for n,v in pairs(retval.value.connection.value) do - dbase[n] = v.value - end - - return retval -end - -function mymodule.list_databases(dbase, self, clientdata) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - - local retval = get_connection(db, self, clientdata) - retval.label = "List of Databases" - retval.value.databases = cfe({ type="list", value={}, label="List of Databases" }) - local res, err = pcall(function() - retval.value.databases.value = db.listdatabases() - end) - if not res and err then - retval.value.connection.errtxt = err - end - - return retval -end - -function mymodule.list_tables(dbase, self, clientdata) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - - local retval = get_connection(db, self, clientdata) - retval.label = "List of Database Tables" - retval.value.tables = cfe({ type="list", value={}, label="List of Database Tables" }) - local res, err = pcall(function() - local connected = db.databaseconnect() - retval.value.tables.value = db.listtables() - if connected then db.databasedisconnect() end - end) - if not res and err then - retval.value.connection.errtxt = err - end - - return retval -end - -function mymodule.list_table_entries(dbase, self, clientdata) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - - local retval = get_connection(db, self, clientdata) - retval.label = "Database Table Entries" - retval.value.table = cfe({ label="Table", key=true }) - self.handle_clientdata(retval, clientdata) - retval.value.fields = cfe({ type="list", value={}, label="List of Table Fields" }) - retval.value.keyfields = cfe({ type="list", value={}, label="List of Key Table Fields" }) - retval.value.entries = cfe({ type="structure", value={}, label="List of Database Entries" }) - local res, err = pcall(function() - local connected = db.databaseconnect() - local tables = db.listtables() - retval.value.table.errtxt = "Table does not exist" - retval.errtxt = "Table does not exist" - for i,t in ipairs(tables) do - if t == retval.value.table.value then - retval.value.table.errtxt = nil - retval.errtxt = nil - retval.value.fields.value = db.listcolumns(t) or {} - retval.value.keyfields.value = db.listkeycolumns(t) or {} - retval.value.entries.value = db.getselectresponse("SELECT * FROM "..db.escape(t)) or {} - end - end - if connected then db.databasedisconnect() end - end) - if not res and err then - retval.value.connection.errtxt = err - end - return retval -end - -function mymodule.get_new_table_entry(dbase, self, clientdata) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - - local retval = get_connection(db, self, clientdata) - retval.label = "Database Table Entry" - - retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 }) - self.handle_clientdata(retval, clientdata) - retval.errtxt = "Table does not exist" - if retval.value.table.value ~= "" then - local res, err = pcall(function() - local connected = db.databaseconnect() - local tables = db.listtables() - for i,t in ipairs(tables) do - if t == retval.value.table.value then - retval.value.table.errtxt = nil - retval.value.table.readonly = true - retval.errtxt = nil - for n,v in pairs(retval.value.connection.value) do - v.readonly = true - end - break - end - end - if not errtxt then - retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 }) - local fields = db.listcolumns(retval.value.table.value) - for i,f in ipairs(fields) do - retval.value.fields.value[f] = cfe({ label=f, seq=i }) - end - end - if connected then db.databasedisconnect() end - end) - if not res and err then - retval.value.connection.errtxt = err - end - end - - return retval -end - -function mymodule.get_table_entry(dbase, self, clientdata) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - - local retval = mymodule.get_new_table_entry(db, self, clientdata) - self.handle_clientdata(retval, clientdata) -- Fill in the fields - if not retval.value.connection.errtxt and not retval.value.table.errtxt and retval.value.fields then - local res, err = pcall(function() - local connected = db.databaseconnect() - local keyfields = db.listkeycolumns(retval.value.table.value) - local reversekeyfields = {} - local fieldchecks = {} - for i,f in ipairs(keyfields) do - reversekeyfields[f] = i - retval.value.fields.value[f].errtxt = "Entry does not exist" - fieldchecks[#fieldchecks+1] = f.."='"..db.escape(retval.value.fields.value[f].value).."'" - end - retval.errtxt = "Entry does not exist" - local sql = "SELECT * FROM "..db.escape(retval.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") - local entry = db.getselectresponse(sql) - if entry and #entry > 0 then - retval.errtxt = nil - for n,v in pairs(entry[1]) do - if retval.value.fields.value[n] then retval.value.fields.value[n].value = v end - if reversekeyfields[n] then - retval.value.fields.value[n].readonly = true - retval.value.fields.value[n].errtxt = nil - 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 mymodule.create_table_entry(dbase, self, entry, action) - return mymodule.update_table_entry(dbase, self, entry, action, true) -end - -function mymodule.update_table_entry(dbase, self, entry, action, create) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - fill_connection(db, self, entry) - - 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 or nil == entry.value.fields then - success = false - entry.value.table.errtxt = "Table does not exist" - end - if success then - local res, err = pcall(function() - local connected = db.databaseconnect() - local keyfields = db.listkeycolumns(entry.value.table.value) - local reversekeyfields = {} - local fieldchecks = {} - for i,f in ipairs(keyfields) do - reversekeyfields[f] = i - entry.value.fields.value[f].errtxt = "Entry does not exist" - fieldchecks[#fieldchecks+1] = f.."='"..db.escape(entry.value.fields.value[f].value).."'" - end - local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") - local tmp = db.getselectresponse(sql) - if not create and (not tmp or #tmp == 0) then - success = false - for i,n in ipairs(keyfields) do - entry.value.fields.value[n].errtxt = "Entry does not exist" - end - elseif create and tmp and #tmp>0 then - success = false - for i,n in ipairs(keyfields) do - entry.value.fields.value[n].errtxt = "Entry already exists" - end - end - if success then - local names = {} - local values = {} - for n,v in pairs(entry.value.fields.value) do - if create or not reversekeyfields[n] 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 "..table.concat(fieldchecks, " AND ") - 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 mymodule.get_delete_table_entry(dbase, self, clientdata) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - - local retval = get_connection(db, self, clientdata) - retval.label = "Delete Database Table Entry" - - retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 }) - self.handle_clientdata(retval, clientdata) - retval.errtxt = "Table does not exist" - if retval.value.table.value ~= "" then - local res, err = pcall(function() - local connected = db.databaseconnect() - local tables = db.listtables() - for i,t in ipairs(tables) do - if t == retval.value.table.value then - retval.value.table.errtxt = nil - retval.value.table.readonly = true - retval.errtxt = nil - for n,v in pairs(retval.value.connection.value) do - v.readonly = true - end - break - end - end - if not errtxt then - local keyfields = db.listkeycolumns(retval.value.table.value) - retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 }) - for i,f in ipairs(keyfields) do - retval.value.fields.value[f] = cfe({ label=f, seq=i }) - end - end - if connected then db.databasedisconnect() end - end) - if not res and err then - retval.value.connection.errtxt = err - end - end - - return retval -end - -function mymodule.delete_table_entry(dbase, self, entry) - local db = dbase - if type(dbase) == "function" then - db = dbase() - end - fill_connection(db, self, entry) - - entry.errtxt = "Failed to delete table entry" - if entry.value.table.value == "" then - entry.value.table.errtxt = "Invalid table" - 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 keyfields = db.listkeycolumns(entry.value.table.value) - local fieldchecks = {} - for i,f in ipairs(keyfields) do - fieldchecks[#fieldchecks+1] = f.."='"..db.escape(entry.value.fields.value[f].value).."'" - end - local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") - 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 - -return mymodule diff --git a/lib/Makefile b/lib/Makefile new file mode 100644 index 0000000..b128d6f --- /dev/null +++ b/lib/Makefile @@ -0,0 +1,29 @@ +include ../config.mk + +LIB_DIST=dbcontrollerfunctions.lua\ + dbmodelfunctions.lua\ + db-*-html.lsp\ + +EXTRA_DIST=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)" + cp -a $(DISTFILES) "$(dist_dir)" + +phony+=install +install: + mkdir -p "$(install_dir)" + cp -a $(LIB_DIST) "$(install_dir)" + +.PHONY: $(phony) diff --git a/lib/db-listdatabases-html.lsp b/lib/db-listdatabases-html.lsp new file mode 100644 index 0000000..dcc9dcf --- /dev/null +++ b/lib/db-listdatabases-html.lsp @@ -0,0 +1,68 @@ +<% local form, viewlibrary, page_info, session = ... %> +<% htmlviewfunctions = require("htmlviewfunctions") %> +<% html = require("acf.html") %> + +<% if form.value.databases and #form.value.databases.value>0 then %> + + + + + +<% end %> + +<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %> +<% if form.value.connection then + -- hide the database, user, and password, since we don't need them to list databases + form.value.connection.value.database = nil + form.value.connection.value.user = nil + form.value.connection.value.password = nil +end %> +<% if form.value.connection and next(form.value.connection.value) ~= nil then + htmlviewfunctions.displayformstart(form, page_info) + htmlviewfunctions.displayitem(form.value.connection, page_info, htmlviewfunctions.incrementheader(header_level), "connection") + form.option = "Update" + htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level)) +end %> +<% if form.value.databases and #form.value.databases.value>0 then %> + + + + + + +<% -- We need to pass the connection key values to listtables +local formvalues = {} +if form.value.connection then + formvalues.connection = form.value.connection + for n,v in pairs(form.value.connection.value) do v.type="hidden" end +end +form.value.connection.value.database = cfe({ type="hidden" }) +%> +<% for i,dbase in ipairs(form.value.databases.value) do %> + <% formvalues.connection.value.database.value = dbase %> + + + + +<% end %> +
ActionDatabase
+ <% if viewlibrary.check_permission("listtables") then %> + <% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="View", action="listtables"}), page_info, -1) %> + <% end %> + <%= html.html_escape(dbase) %>
+<% else %> +

No databases found

+<% end %> +<% htmlviewfunctions.displaysectionend(header_level) %> diff --git a/lib/db-listtables-html.lsp b/lib/db-listtables-html.lsp new file mode 100644 index 0000000..f0db6b7 --- /dev/null +++ b/lib/db-listtables-html.lsp @@ -0,0 +1,66 @@ +<% local form, viewlibrary, page_info, session = ... %> +<% htmlviewfunctions = require("htmlviewfunctions") %> +<% html = require("acf.html") %> + +<% if form.value.tables and #form.value.tables.value>0 then %> + + + + + +<% end %> + +<% htmlviewfunctions.displaycommandresults({"createdatabase"}, session) %> + +<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %> +<% if form.value.connection and next(form.value.connection.value) ~= nil then + htmlviewfunctions.displayformstart(form, page_info) + htmlviewfunctions.displayitem(form.value.connection, page_info, htmlviewfunctions.incrementheader(header_level), "connection") + form.option = "Update" + htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level)) +end %> +<% if form.value.tables and #form.value.tables.value>0 then %> + + + + + + +<% -- We will reuse the form connection structure to pass key values to viewtable +local formvalues = {} +if form.value.connection then + formvalues.connection = form.value.connection + for n,v in pairs(form.value.connection.value) do v.type="hidden" end +end +formvalues.table = cfe({ type="hidden" }) +%> +<% for i,tab in ipairs(form.value.tables.value) do %> + <% formvalues.table.value = tab %> + + + + +<% end %> +
ActionTable
+ <% if viewlibrary.check_permission("viewtable") then %> + <% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="View", action="viewtable"}), page_info, -1) %> + <% end %> + <%= html.html_escape(tab) %>
+<% elseif viewlibrary.check_permission("createdatabase") then %> + <% htmlviewfunctions.displayitem(cfe({type="form", value={}, label="Create Database", option="Create", action="createdatabase" }), page_info, 0) %> +<% else %> +

No tables found

+<% end %> +<% htmlviewfunctions.displaysectionend(header_level) %> diff --git a/lib/db-viewtable-html.lsp b/lib/db-viewtable-html.lsp new file mode 100644 index 0000000..3bde35e --- /dev/null +++ b/lib/db-viewtable-html.lsp @@ -0,0 +1,111 @@ +<% local form, viewlibrary, page_info, session = ... %> +<% htmlviewfunctions = require("htmlviewfunctions") %> +<% html = require("acf.html") %> + + + + + + + +<% +local redir = cfe({ type="hidden", value=page_info.orig_action.."?table="..html.url_encode(form.value.table.value) }) +local keyvalues = {table=form.value.table.value} +if form.value.connection then + keyvalues.connection = {} + for n,v in pairs(form.value.connection.value) do + redir.value = redir.value.."&connection."..n.."="..html.url_encode(v.value) + keyvalues.connection[n] = v.value + end +end +keyvalues.redir = redir.value +%> + +<% htmlviewfunctions.displaycommandresults({"deletetableentry", "updatetableentry"}, session) %> +<% htmlviewfunctions.displaycommandresults({"createtableentry"}, session, true) %> + +<% if form.value.table.value ~= "" then form.label = form.label.." - "..form.value.table.value end %> +<% local header_level = htmlviewfunctions.displaysectionstart(form, page_info) %> +<% +local header_level2 = htmlviewfunctions.incrementheader(header_level) +htmlviewfunctions.displayformstart(form, page_info) +if form.value.connection and next(form.value.connection.value) ~= nil then + htmlviewfunctions.displayitem(form.value.connection, page_info, header_level2, "connection") +end +htmlviewfunctions.displayitem(form.value.table, page_info, header_level2, "table") +form.option = "Update" +htmlviewfunctions.displayformend(form, htmlviewfunctions.incrementheader(header_level)) +%> + + + <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> + + <% end %> + <% for i,f in ipairs(form.value.fields.value) do %> + + <% end %> + + +<% -- We will reuse the form connection structure to pass key values to updatetableentry and deletetableentry +local formvalues = {} +if form.value.connection then + formvalues.connection = form.value.connection + for n,v in pairs(form.value.connection.value) do v.type="hidden" end +end +if form.value.keyfields then + formvalues.fields = cfe({ type="group", value={} }) + for i,f in ipairs(form.value.keyfields.value) do + formvalues.fields.value[f] = cfe({ type="hidden" }) + end +end +formvalues.table = form.value.table +form.value.table.type = "hidden" +formvalues.redir = redir +%> +<% for i,tableentry in ipairs(form.value.entries.value) do %> + + <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> + <% + if formvalues.fields then + for i,f in ipairs(form.value.keyfields.value) do + formvalues.fields.value[f].value = tableentry[f] + end + end + %> + + <% end %> + <% for i,f in ipairs(form.value.fields.value) do %> + + <% end %> + +<% end %> +
Action<%= html.html_escape(f) %>
+ <% if viewlibrary.check_permission("updatetableentry") then %> + <% htmlviewfunctions.displayitem(cfe({type="link", value=formvalues, label="", option="Update", action="updatetableentry"}), page_info, -1) %> + <% end %> + <% if viewlibrary.check_permission("deletetableentry") then %> + <% htmlviewfunctions.displayitem(cfe({type="form", value=formvalues, label="", option="Delete", action="deletetableentry", class="deletetableentry"}), page_info, -1) %> + <% end %> + <%= html.html_escape(tableentry[f]) %>
+<% htmlviewfunctions.displayinfo(form) %> +<% if #form.value.entries.value == 0 then %> +

No entries found

+<% end %> +<% htmlviewfunctions.displaysectionend(header_level) %> + +<% if page_info.action == "viewtable" and viewlibrary and viewlibrary.dispatch_component and viewlibrary.check_permission("createtableentry") then + viewlibrary.dispatch_component("createtableentry", keyvalues) +end %> diff --git a/lib/dbcontrollerfunctions.lua b/lib/dbcontrollerfunctions.lua new file mode 100644 index 0000000..913919e --- /dev/null +++ b/lib/dbcontrollerfunctions.lua @@ -0,0 +1,27 @@ +local mymodule = {} + +function mymodule.listdatabases(self) + return self.model.list_databases(self, self.clientdata) +end + +function mymodule.listtables(self) + return self.model.list_tables(self, self.clientdata) +end + +function mymodule.viewtable(self) + return self.model.list_table_entries(self, self.clientdata) +end + +function mymodule.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 mymodule.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 mymodule.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 + +return mymodule diff --git a/lib/dbmodelfunctions.lua b/lib/dbmodelfunctions.lua new file mode 100644 index 0000000..d9c8689 --- /dev/null +++ b/lib/dbmodelfunctions.lua @@ -0,0 +1,366 @@ +local mymodule = {} + +db = require("acf.db") + +-- 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) + +local function get_connection(dbase, self, clientdata) + local retval = cfe({ type="group", value={} }) + retval.value.connection = cfe({ type="group", value={}, label="Database Connection", seq=0 }) + retval.value.connection.value.engine = cfe({ type="select", label="Database Engine", option={}, key=true, seq=1 }) + for n,v in pairs(db.engine) do + retval.value.connection.value.engine.option[#retval.value.connection.value.engine.option+1] = {name=n, value=v} + end + retval.value.connection.value.database = cfe({ label="Database", key=true, seq=2 }) + retval.value.connection.value.user = cfe({ label="User", key=true, seq=3 }) + retval.value.connection.value.password = cfe({ type="password", label="Password", key=true, seq=4 }) + retval.value.connection.value.host = cfe({ label="Host", key=true, seq=5 }) + retval.value.connection.value.port = cfe({ label="Port", key=true, seq=6 }) + + self.handle_clientdata(retval, clientdata) + + -- If dbase has a default, remove the cfe. Otherwise, fill in with the value from the cfe. + for n,v in pairs(retval.value.connection.value) do + if dbase[n] then + retval.value.connection.value[n] = nil + else + dbase[n] = v.value + end + end + + return retval +end + +local function fill_connection(dbase, self, retval) + -- Fill in dbase with the values from the cfe retval + for n,v in pairs(retval.value.connection.value) do + dbase[n] = v.value + end + + return retval +end + +function mymodule.list_databases(dbase, self, clientdata) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + + local retval = get_connection(db, self, clientdata) + retval.label = "List of Databases" + retval.value.databases = cfe({ type="list", value={}, label="List of Databases" }) + local res, err = pcall(function() + retval.value.databases.value = db.listdatabases() + end) + if not res and err then + retval.value.connection.errtxt = err + end + + return retval +end + +function mymodule.list_tables(dbase, self, clientdata) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + + local retval = get_connection(db, self, clientdata) + retval.label = "List of Database Tables" + retval.value.tables = cfe({ type="list", value={}, label="List of Database Tables" }) + local res, err = pcall(function() + local connected = db.databaseconnect() + retval.value.tables.value = db.listtables() + if connected then db.databasedisconnect() end + end) + if not res and err then + retval.value.connection.errtxt = err + end + + return retval +end + +function mymodule.list_table_entries(dbase, self, clientdata) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + + local retval = get_connection(db, self, clientdata) + retval.label = "Database Table Entries" + retval.value.table = cfe({ label="Table", key=true }) + self.handle_clientdata(retval, clientdata) + retval.value.fields = cfe({ type="list", value={}, label="List of Table Fields" }) + retval.value.keyfields = cfe({ type="list", value={}, label="List of Key Table Fields" }) + retval.value.entries = cfe({ type="structure", value={}, label="List of Database Entries" }) + local res, err = pcall(function() + local connected = db.databaseconnect() + local tables = db.listtables() + retval.value.table.errtxt = "Table does not exist" + retval.errtxt = "Table does not exist" + for i,t in ipairs(tables) do + if t == retval.value.table.value then + retval.value.table.errtxt = nil + retval.errtxt = nil + retval.value.fields.value = db.listcolumns(t) or {} + retval.value.keyfields.value = db.listkeycolumns(t) or {} + retval.value.entries.value = db.getselectresponse("SELECT * FROM "..db.escape(t)) or {} + end + end + if connected then db.databasedisconnect() end + end) + if not res and err then + retval.value.connection.errtxt = err + end + return retval +end + +function mymodule.get_new_table_entry(dbase, self, clientdata) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + + local retval = get_connection(db, self, clientdata) + retval.label = "Database Table Entry" + + retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 }) + self.handle_clientdata(retval, clientdata) + retval.errtxt = "Table does not exist" + if retval.value.table.value ~= "" then + local res, err = pcall(function() + local connected = db.databaseconnect() + local tables = db.listtables() + for i,t in ipairs(tables) do + if t == retval.value.table.value then + retval.value.table.errtxt = nil + retval.value.table.readonly = true + retval.errtxt = nil + for n,v in pairs(retval.value.connection.value) do + v.readonly = true + end + break + end + end + if not errtxt then + retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 }) + local fields = db.listcolumns(retval.value.table.value) + for i,f in ipairs(fields) do + retval.value.fields.value[f] = cfe({ label=f, seq=i }) + end + end + if connected then db.databasedisconnect() end + end) + if not res and err then + retval.value.connection.errtxt = err + end + end + + return retval +end + +function mymodule.get_table_entry(dbase, self, clientdata) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + + local retval = mymodule.get_new_table_entry(db, self, clientdata) + self.handle_clientdata(retval, clientdata) -- Fill in the fields + if not retval.value.connection.errtxt and not retval.value.table.errtxt and retval.value.fields then + local res, err = pcall(function() + local connected = db.databaseconnect() + local keyfields = db.listkeycolumns(retval.value.table.value) + local reversekeyfields = {} + local fieldchecks = {} + for i,f in ipairs(keyfields) do + reversekeyfields[f] = i + retval.value.fields.value[f].errtxt = "Entry does not exist" + fieldchecks[#fieldchecks+1] = f.."='"..db.escape(retval.value.fields.value[f].value).."'" + end + retval.errtxt = "Entry does not exist" + local sql = "SELECT * FROM "..db.escape(retval.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") + local entry = db.getselectresponse(sql) + if entry and #entry > 0 then + retval.errtxt = nil + for n,v in pairs(entry[1]) do + if retval.value.fields.value[n] then retval.value.fields.value[n].value = v end + if reversekeyfields[n] then + retval.value.fields.value[n].readonly = true + retval.value.fields.value[n].errtxt = nil + 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 mymodule.create_table_entry(dbase, self, entry, action) + return mymodule.update_table_entry(dbase, self, entry, action, true) +end + +function mymodule.update_table_entry(dbase, self, entry, action, create) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + fill_connection(db, self, entry) + + 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 or nil == entry.value.fields then + success = false + entry.value.table.errtxt = "Table does not exist" + end + if success then + local res, err = pcall(function() + local connected = db.databaseconnect() + local keyfields = db.listkeycolumns(entry.value.table.value) + local reversekeyfields = {} + local fieldchecks = {} + for i,f in ipairs(keyfields) do + reversekeyfields[f] = i + entry.value.fields.value[f].errtxt = "Entry does not exist" + fieldchecks[#fieldchecks+1] = f.."='"..db.escape(entry.value.fields.value[f].value).."'" + end + local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") + local tmp = db.getselectresponse(sql) + if not create and (not tmp or #tmp == 0) then + success = false + for i,n in ipairs(keyfields) do + entry.value.fields.value[n].errtxt = "Entry does not exist" + end + elseif create and tmp and #tmp>0 then + success = false + for i,n in ipairs(keyfields) do + entry.value.fields.value[n].errtxt = "Entry already exists" + end + end + if success then + local names = {} + local values = {} + for n,v in pairs(entry.value.fields.value) do + if create or not reversekeyfields[n] 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 "..table.concat(fieldchecks, " AND ") + 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 mymodule.get_delete_table_entry(dbase, self, clientdata) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + + local retval = get_connection(db, self, clientdata) + retval.label = "Delete Database Table Entry" + + retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 }) + self.handle_clientdata(retval, clientdata) + retval.errtxt = "Table does not exist" + if retval.value.table.value ~= "" then + local res, err = pcall(function() + local connected = db.databaseconnect() + local tables = db.listtables() + for i,t in ipairs(tables) do + if t == retval.value.table.value then + retval.value.table.errtxt = nil + retval.value.table.readonly = true + retval.errtxt = nil + for n,v in pairs(retval.value.connection.value) do + v.readonly = true + end + break + end + end + if not errtxt then + local keyfields = db.listkeycolumns(retval.value.table.value) + retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 }) + for i,f in ipairs(keyfields) do + retval.value.fields.value[f] = cfe({ label=f, seq=i }) + end + end + if connected then db.databasedisconnect() end + end) + if not res and err then + retval.value.connection.errtxt = err + end + end + + return retval +end + +function mymodule.delete_table_entry(dbase, self, entry) + local db = dbase + if type(dbase) == "function" then + db = dbase() + end + fill_connection(db, self, entry) + + entry.errtxt = "Failed to delete table entry" + if entry.value.table.value == "" then + entry.value.table.errtxt = "Invalid table" + 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 keyfields = db.listkeycolumns(entry.value.table.value) + local fieldchecks = {} + for i,f in ipairs(keyfields) do + fieldchecks[#fieldchecks+1] = f.."='"..db.escape(entry.value.fields.value[f].value).."'" + end + local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ") + 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 + +return mymodule -- cgit v1.2.3