Allow SQL Functions to be used on Fields

[MRichards99]

Hi Rolf, me again!

While testing DataGateway, someone noticed some cases of our search boxes (which use the like keyword in the where clause of the query) being case sensitive. After some investigation, we noticed this behaviour only happened where ICAT is being used on Oracle databases (i.e. production) as these perform case sensitive string comparisons, unlike MariaDB or similar.

TopCAT mitigates this issue by using the UPPER() function on the field:

UPPER(facilityCycle.description) like concat('%', 'ISIS', '%')

We'd like to replicate this solution for DataGateway, ideally uppercasing both the field and the user's input (I'm not sure why the input isn't uppercased in the example, but demonstrates our idea nonetheless). Doing the user's input is no issue, I've already quickly tested this in DataGateway API, but uppercasing the field is proving to be more difficult.

Query(client, "Investigation",
    conditions={
        "UPPER(title)": "like '%PolICE%'"
    }
)

The above query causes an exception to be raised because the attribute name isn't recognised which is understandable as the input obviously isn't a valid ICAT attribute. The traceback comes back to the self._attrpath call.

In a nutshell, we'd like SQL functions to work for fields.

I've been thinking about this and made a couple of changes on my local Python ICAT to get something I'm happy I can implement into DataGateway API to solve our issue. I'll put them into a branch, create a PR to explain them so you can see what you think.