From 8292f99d5a0da91490b7bb445b4db0405a22045f Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Mon, 7 Sep 2015 22:30:42 +0000 Subject: Add server-side pagination and filtering to viewtable action using jquery-tablesorter Note that filtering fails for non-character columns and have seen some table entries cause failures --- lib/db-viewtable-html.lsp | 203 +++++++++++++++++++++++++++++++--------------- lib/dbmodelfunctions.lua | 57 ++++++++++++- 2 files changed, 192 insertions(+), 68 deletions(-) diff --git a/lib/db-viewtable-html.lsp b/lib/db-viewtable-html.lsp index a8efaa7..3c89c41 100644 --- a/lib/db-viewtable-html.lsp +++ b/lib/db-viewtable-html.lsp @@ -11,17 +11,14 @@ - - <% +-- Calculate the redir to get back to this page (and set up keyvalues used to createtableentry with same connection and table) 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 @@ -32,52 +29,28 @@ if form.value.connection then 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 or form.value.connection.errtxt ) 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 +-- Create formvalues table to pass connection and 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 + formvalues.connection = cfe({type="group", value={}}) + for n,v in pairs(form.value.connection.value) do + formvalues.connection.value[n] = cfe({type="hidden", value=v.value}) + end end local reversekeyfields = {} if form.value.keyfields then formvalues.fields = cfe({ type="group", value={} }) formvalues.nulls = cfe({ type="group", value={} }) for i,f in ipairs(form.value.keyfields.value) do - formvalues.fields.value[f] = cfe({ type="hidden" }) - formvalues.nulls.value[f] = cfe({ type="hidden", value="false" }) + formvalues.fields.value[f] = cfe({ type="hidden", value=f.."_VALUE" }) + formvalues.nulls.value[f] = cfe({ type="hidden", value=f.."_NULL" }) reversekeyfields[f] = i end end -formvalues.table = form.value.table -form.value.table.type = "hidden" +formvalues.table = cfe({type="hidden", value=form.value.table.value}) formvalues.redir = redir + -- We will hide the update link if all fields are key fields local allowupdate = false for i,f in ipairs(form.value.fields.value) do @@ -88,41 +61,137 @@ for i,f in ipairs(form.value.fields.value) do end allowupdate = allowupdate and viewlibrary.check_permission("updatetableentry") %> -<% for i,tableentry in ipairs(form.value.entries.value) do %> - - <% if viewlibrary.check_permission("deletetableentry") or allowupdate then %> + + + +<% 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 or form.value.connection.errtxt ) 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)) +%> + +
Action<%= html.html_escape(f) %>
+ + <% if viewlibrary.check_permission("deletetableentry") or allowupdate then %> + <% end %> <% for i,f in ipairs(form.value.fields.value) do %> - + <% end %> -<% end %> +
Action - <% if not tableentry[f] then %> - NULL - <% else %> - <%= html.html_escape(tableentry[f]) %> - <% end %> - <%= html.html_escape(f) %>
+ +
+
+ Page: + + + + + + +
+
+ <% htmlviewfunctions.displayinfo(form) %> <% if #form.value.entries.value == 0 then %>

No entries found

diff --git a/lib/dbmodelfunctions.lua b/lib/dbmodelfunctions.lua index 0d31b03..20b101a 100644 --- a/lib/dbmodelfunctions.lua +++ b/lib/dbmodelfunctions.lua @@ -94,11 +94,29 @@ function mymodule.list_table_entries(dbase, self, clientdata) local retval = get_connection(db, self, clientdata) retval.label = "Database Table Entries" + -- Need to specify the table retval.value.table = cfe({ label="Table", key=true }) + -- Here is the pagination/filtering info + retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true }) + retval.value.pagesize = cfe({ value=10, label="Page Size", key=true }) + retval.value.rowcount = cfe({ value=0, label="Row Count" }) + -- orderby must be an array of tables with column name and direction + retval.value.orderby = cfe({ type="structure", value={}, label="Order By", key=true }) + -- filter is a table with a string filter for each column + retval.value.filter = cfe({ type="structure", value={}, label="Filter", key=true }) self.handle_clientdata(retval, clientdata) + + -- Here is the result 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" }) + + -- Process the incoming page data + local page = tonumber(retval.value.page.value) or 0 + retval.value.page.value = page + local pagesize = tonumber(retval.value.pagesize.value) or 10 + retval.value.pagesize.value = pagesize + local res, err = pcall(function() local connected = db.databaseconnect() local tables = db.listtables() @@ -110,7 +128,43 @@ function mymodule.list_table_entries(dbase, self, clientdata) 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 {} + + local orderby = {} + local columns = {} + for i,c in ipairs(retval.value.fields.value) do columns[c] = c end + local directions = {asc="ASC", desc="DESC", ASC="ASC", DESC="DESC"} + for i,o in ipairs(retval.value.orderby.value) do + if columns[o.column] and directions[o.direction] then + orderby[#orderby+1] = columns[o.column].." "..directions[o.direction] + end + end + + local filter = {} + -- FIXME - not all columns types will allow regex filtering, such as date columns + for c,f in pairs(retval.value.filter.value) do + if columns[c] and f ~= "" then + filter[#filter+1] = columns[c].."~'"..db.escape(f).."'" + end + end + + local sql = " FROM "..db.escape(t) + if #filter>0 then + sql = sql.." WHERE "..table.concat(filter, " AND ") + end + if page > 0 then + local count = db.getselectresponse("SELECT count(*)"..sql) + retval.value.rowcount.value = count[1].count + end + if #orderby>0 then + sql = sql.." ORDER BY "..table.concat(orderby, ", ") + end + if page > 0 then + sql = sql.." LIMIT "..pagesize.." OFFSET "..(page - 1)*pagesize + end + retval.value.entries.value = db.getselectresponse("SELECT *"..sql) or {} + if page <= 0 then + retval.value.rowcount.value = #retval.value.entries.value + end end end if connected then db.databasedisconnect() end @@ -118,6 +172,7 @@ function mymodule.list_table_entries(dbase, self, clientdata) if not res and err then retval.value.connection.errtxt = err end + return retval end -- cgit v1.2.3