diff options
Diffstat (limited to 'lib/dbmodelfunctions.lua')
| -rw-r--r-- | lib/dbmodelfunctions.lua | 57 |
1 files changed, 56 insertions, 1 deletions
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 |