Filter exception handling

[moschlar]

I want to put this up for discussion:

Currently, if a specified filter leads to an exception in SQLAlchemy (e.g. on the default TG2 quickstart model with auth, create a CrudRestController for the User model class and search for group=0), the exception is not caught.
(Or another example: Depending on the backend, filtering an Integer column for a character (e.g. user_id=a, works on sqlite, doesn't work on postgres) will throw a DataError).

In my opinion, for an operation like filtering search results, this should only lead to an empty result set and maybe a warning flash to the user that he tried an illegal operation.

What do others think?

[amol-]

Search filtering has a bunch of issues currently. The first is the one you are reporting, with type matching. I actually think that in case of invalid filters (like characters for numbers), this can probably be easily detected by catching for ValueError when calling self.table_filler.get_value

The other is that, if I'm not wrong, searching for relations has many limits: Searching for related objects requires to write the id (when you would expected to write what you see, which is usually the view_names).

[moschlar]

I'm gonna tackle them next ;)

[amol-]

I would like to tackle this issue for the upcoming release, do you have an already started patch or can I start from scratch?

[moschlar]

Nope, I haven't started yet... :-/

[moschlar]

I gave the first part of the problem a try.

Now there are some semantic inconsistencies between different database backends...
With sqlite, you can query an integer column by a string, it just doesn't return any rows; but on postgres, you will get a DataError from that...
If we catch the exception and discard the query, this will of course lead to different results.
To get it consistent, we could just return values = {} there.
Any thoughts?

[moschlar]

Thinking some seconds about it, I think that in fact values = {} is a much better default...

[amol-]

I think that we can somehow achieve the expected behavior by using sprox validators detection to validate the search inputs. That should cope with issues with type and also cast searches according to the validators the user applied to the model. Would that be a solution?

[moschlar]

Yeah, cool idea!