summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/db-viewtable-html.lsp203
-rw-r--r--lib/dbmodelfunctions.lua57
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-25 03:56:11 +0000