Check column types so we can filter with regex for text and not for other types

1 files 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