From f0b845ab4f625e6786e2bbf3c1a941a08ca784ba Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Thu, 17 Sep 2015 16:15:06 +0000 Subject: Check column types so we can filter with regex for text and not for other types --- lib/dbmodelfunctions.lua | 18 +++++++++++++++--- 1 file changed, 15 insertions(+), 3 deletions(-) diff --git a/lib/dbmodelfunctions.lua b/lib/dbmodelfunctions.lua index 20b101a..d333548 100644 --- a/lib/dbmodelfunctions.lua +++ b/lib/dbmodelfunctions.lua @@ -126,8 +126,14 @@ function mymodule.list_table_entries(dbase, self, clientdata) if t == retval.value.table.value then retval.value.table.errtxt = nil retval.errtxt = nil + -- FIXME - in the future, we should be able to do next these three things in one function call to db retval.value.fields.value = db.listcolumns(t) or {} retval.value.keyfields.value = db.listkeycolumns(t) or {} + -- this will not work for sqlite and the results are database-specific + local sql = "SELECT data_type FROM information_schema.columns WHERE table_name = '"..db.escape(t).."' ORDER BY ordinal_position" + local types = db.getselectresponse(sql) + local fieldtypes = {} + for i,f in ipairs(types) do fieldtypes[retval.value.fields.value[i]] = f.data_type end local orderby = {} local columns = {} @@ -140,14 +146,20 @@ function mymodule.list_table_entries(dbase, self, clientdata) end local filter = {} - -- FIXME - not all columns types will allow regex filtering, such as date columns + -- FIXME - the following has not been exhaustively tested for all column types and databases for c,f in pairs(retval.value.filter.value) do if columns[c] and f ~= "" then - filter[#filter+1] = columns[c].."~'"..db.escape(f).."'" + if string.find(fieldtypes[c]:lower(), "char") or string.find(fieldtypes[c]:lower(), "text") then + filter[#filter+1] = columns[c].."~'"..db.escape(f).."'" + elseif tonumber(f) then + filter[#filter+1] = columns[c].."="..tonumber(f) + else + filter[#filter+1] = columns[c].."='"..db.escape(f).."'" + end end end - local sql = " FROM "..db.escape(t) + sql = " FROM "..db.escape(t) if #filter>0 then sql = sql.." WHERE "..table.concat(filter, " AND ") end -- cgit v1.2.3