REGI-469: Location Catalog Kind Filter - *NOT LIKE* Regex Query

[zack-rma]

Hi all,

This is in relation to REGI-469, a ticket to add support for REGI queries to CDA. The current PR for this work can be found here: #1211

The work for this ticket involves adding support for a location kind exclusion filter. There is already support for location kind LIKE regex matching, but the Oracle implementation of regex in the database (POSIX ERE) does not support using NOT operators in the passed expression.

As a result, there are three possible options for adding this support that have been identified:

1. Support a new ! prepended syntax. This would involve checking regex for a prepended ! and triggering different JOOQ logic to negate the match.
2. Add a "not-like" query parameter for each parameter that may need it. This would quickly increase the number of query parameters on an endpoint that already has quite a few.
3. Support specific limited patterns of standard negative lookahead regex syntax with known equivalents. This would involve matching a query like ^((?!outlet|turbine|project).)*$ against a pattern such as \^\(\(\?\!.*\)\.\)\*\$ to trigger negation via JOOQ.

Option 1 would likely be the cleanest solution, but would add custom syntax to the query parameter. It would certainly need to be documented, as this is non-standard behavior for a regex parameter.

Please provide your thoughts and feedback, as a decision on this implementation will be necessary to move forward.

[rma-rripken]

My personal preference is for #2. Its boring but API users don't seem to appreciate surprises. I agree that the number of parameters on the catalog end-points has grown but apart from splitting the timeseries/location catalog into their own end-points there isn't too much we can do about it. Those end-points are very useful, makes sense that people want lots of options and specializations. Recently added Parameter objects should at least make the Controller<->Dao interactions a tiny bit less tedious.
#1 is attractive because its a minor tweak but it makes the regex non-standard and might have unintended side-effects like making it difficult to query for things that start with exclamation.
#3 is creative. Oracle doesn't support negative look-ahead but there is a standard syntax. We could support limited versions of negative look-ahead. There are probably only a couple types of exclude queries that we need and those could be specifically coded to. #3 is probably fragile and will be difficult to explain but seems very doable.

I have no idea what other databases support. Is it just Oracle that doesn't do negative lookahead? Does postgres support it?

[zack-rma]

#2 would be the most user-friendly solution, especially if we're only adding the negation parameter for location kind at the moment.

It would seem that Postgres does support negative lookahead, just Oracle's implementation is lacking. Oracle only supports using not regexp_like() to get the effect we're after.

[rma-rripken]

Is this just on location-kind? Or would similar filters be needed for other fields? If it was used on IDs I'd prefer having a like argument and a separate not-like argument because you can combine them together. Isn't location kind limited to a fixed set of options? Seems like you could do an exclude by getting the full set of location-kinds and then querying for just the ones you want.

[MikeNeilson]

I almost wonder if moving to the new rsql filter would be better long term - though more of a learning curve for the users.

But I otherwise agree, while I like the ! syntax idea, I'm also just very used to it. The not-like is more obvious to users who aren't as involved in programming.

I'm surprise you can't do a "not" in the Oracle regex syntax... well maybe I'm not. I do know that the ^ symbol is often used for both "start of line" and inversion so it's just difficult to do the not within a regular expression anyways.

[zack-rma]

At the moment, this filter would only be necessary for location kind.

There are only 16 kind options in my development database, but from what I can see, there isn't currently a method to retrieve the full list of available kinds other than via direct database access. Plus, this would require another step to achieve something the endpoint would be better off supporting on its own.

I agree with Mike's point about the ! syntax. It makes sense to us, but perhaps not as much to all users. The less of a learning curve we create for users, the better.

[adamkorynta]

I think our primary audience would know basic operators in one of JavaScript, Python, or Java. To show our python users some love, another option could be "NOT:" that should also work for any non-devs. I'd also argue that anyone who is patient enough to learn regex syntax can also pick up a not operator.

The other use-cases I see for a not operator is for data sanitation - when you want to check for any fields that don't match naming standards (location name case matching, time zone fixing, location alias checking, etc).

[MikeNeilson]

The "NOT:" seems pretty reasonable. I don't dislike the current boolean either and both could be supported with minimal effort.