diff options
-rw-r--r-- | lib/db-viewtable-html.lsp | 203 | ||||
-rw-r--r-- | 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 @@ <script type="text/javascript"> if (typeof $.tablesorter == 'undefined') { document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery.tablesorter.js"><\/script>'); + document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/jquery.tablesorter.widgets.js"><\/script>'); + document.write('<link href="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/jquery.tablesorter.pager.css" rel="stylesheet">'); + document.write('<script type="text/javascript" src="<%= html.html_escape(page_info.wwwprefix) %>/js/widgets/widget-pager.js"><\/script>'); } </script> -<script type="text/javascript"> - $(document).ready(function() { - $("#list").tablesorter({widgets: ['zebra']}); - $(".deletetableentry").click(function(){ return confirm("Are you sure you want to delete this entry?")}); - }); -</script> - <% +-- 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)) -%> -<table id="list" class="tablesorter"><thead> - <tr> - <% if viewlibrary.check_permission("deletetableentry") or viewlibrary.check_permission("updatetableentry") then %> - <th>Action</th> - <% end %> - <% for i,f in ipairs(form.value.fields.value) do %> - <th><%= html.html_escape(f) %></th> - <% end %> - </tr> -</thead><tbody> -<% -- 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 %> - <tr> - <% if viewlibrary.check_permission("deletetableentry") or allowupdate then %> + +<script type="text/javascript"> + $(document).ready(function() { + // The following is a hack to include a multiline string + var MultiString = function(f) { + return f.toString().split('\n').slice(1, -1).join('\n'); + } + var actions = MultiString(function() {/** <% - if formvalues.fields then - for i,f in ipairs(form.value.keyfields.value) do - if not tableentry[f] then - formvalues.nulls.value[f].value = "true" - else - formvalues.fields.value[f].value = tableentry[f] - end + if allowupdate 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 - end %> - <td> - <% if allowupdate 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 %> - </td> + **/}); + + var columns = [<% if viewlibrary.check_permission("deletetableentry") or allowupdate then print('"action", ') end%>"<%= table.concat(form.value.fields.value, '", "') %>"]; + + $("#list").tablesorter({widgets: ['zebra', 'filter', 'pager'], widgetOptions: { + // Filtering is handled by the server + filter_serversideFiltering: true, + + // We can put the page number and size here, filtering and sorting handled by pager_customAjaxUrl + pager_ajaxUrl : '<%= html.html_escape(page_info.script)..redir.value %>&viewtype=json&page={page+1}&pagesize={size}', + + // Modify the url after all processing has been applied to handle filtering and sorting + pager_customAjaxUrl: function(table, url) { + var directions = ["asc", "desc"]; + for (var s=0; s<table.config.sortList.length; s++) { + // 0=column number, 1=direction(0 is asc) + if ((table.config.sortList[s][0] < columns.length) && (table.config.sortList[s][1] < directions.length)) { + url += "&orderby."+(s+1)+".column="+columns[table.config.sortList[s][0]]+"&orderby."+(s+1)+".direction="+directions[table.config.sortList[s][1]] + } + } + for (var f=0; f<table.config.pager.currentFilters.length; f++) { + var filter = table.config.pager.currentFilters[f]; + if (filter.trim()) { + url += "&filter."+columns[f]+"="+encodeURIComponent(filter.trim()); + } + } + return url; + }, + + // process ajax so that the following information is returned: + // [ total_rows (number), rows (array of arrays), headers (array; optional) ] + pager_ajaxProcessing: function(data){ + if (data && data.value && data.value.entries) { + rows = []; + for ( r=0; r<data.value.entries.value.length; r++) { + row=[]; + <% if viewlibrary.check_permission("deletetableentry") or allowupdate then %> + var temp = actions; + for (var i=1; i<columns.length; i++) { + if (null == data.value.entries.value[r][columns[i]]) { + temp = temp.replace(new RegExp(columns[i]+"_VALUE", 'g'), "null").replace(new RegExp(columns[i]+"_NULL", 'g'), "true"); + } else { + temp = temp.replace(new RegExp(columns[i]+"_VALUE", 'g'), data.value.entries.value[r][columns[i]]).replace(new RegExp(columns[i]+"_NULL", 'g'), "false"); + } + } + row[0] = temp; + + for (var i=1; i<columns.length; i++) { + <% else %> + for (var i=0; i<columns.length; i++) { + <% end %> + if (null == data.value.entries.value[r][columns[i]]) { + row[i] = "NULL"; + } else { + row[i] = data.value.entries.value[r][columns[i]]; + } + } + rows.push(row); + } + return [ parseInt(data.value.rowcount.value), rows]; + } + } + }}) + .bind('pagerComplete', function(e, c){ + $(".deletetableentry").click(function(){ return confirm("Are you sure you want to delete this entry?")}); + }); + $("#list").tablesorter({widgets: ['zebra']}); + }); +</script> + +<% 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)) +%> + +<table id="list" class="tablesorter"><thead> + <tr> + <% if viewlibrary.check_permission("deletetableentry") or allowupdate then %> + <th class="filter-false remove sorter-false">Action</th> <% end %> <% for i,f in ipairs(form.value.fields.value) do %> - <td> - <% if not tableentry[f] then %> - <i>NULL</i> - <% else %> - <%= html.html_escape(tableentry[f]) %> - <% end %> - </td> + <th><%= html.html_escape(f) %></th> <% end %> </tr> -<% end %> +</thead><tbody> </tbody></table> + +<div id="pager" class="pager"> + <form> + Page: <select class="gotoPage"></select> + <img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/first.png" class="first"/> + <img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/prev.png" class="prev"/> + <span class="pagedisplay"></span> <!-- this can be any element, including an input --> + <img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/next.png" class="next"/> + <img src="<%= html.html_escape(page_info.wwwprefix..page_info.staticdir) %>/tablesorter/last.png" class="last"/> + <select class="pagesize"> + <option selected="selected" value="10">10</option> + <option value="20">20</option> + <option value="30">30</option> + <option value="40">40</option> + </select> + </form> +</div> + <% htmlviewfunctions.displayinfo(form) %> <% if #form.value.entries.value == 0 then %> <p>No entries found</p> 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 |