Implement filtering options for Number types.

[kgodey]

Problem

We need to ensure that records that include columns of all Number types support the following filters via API:

• between {x} and {y}
• equals {x}
• does not equal {x}
• greater than {x}
• lesser than {x}
• greater than or equal to {x}
• less than or equal to {x}
• is empty
• is not empty

This involves:

• Implementing the filters in the backend
• Updating the /api/v0/databases/<id>/types/ endpoint to store available filters on this type
  • Filter information should include the number of parameters needing to be passed in (e.g. between needs 2 parameters, is empty needs 0)

Additional context

• We're using our fork of sqlalchemy-filters to provide filtering. See: https://github.com/centerofci/sqlalchemy-filters.
• Update filtering issues to cover storing filter/group information in the backend. #557 provides some context on storing filters.
• This issue is blocked by implementation of Number types:
  • Handle NUMERIC data type in the backend. #377
  • Handle SMALLINT data type in the backend. #378
  • Handle INTEGER data type in the backend. #379
  • Handle BIGINT data type in the backend. #380
  • Handle DECIMAL data type in the backend. #381
  • Handle REAL data type in the backend. #382
  • Handle DOUBLE PRECISION data type in the backend. #383
  • Backend work for custom MATHESAR_TYPES.PERCENTAGE type. #434

[mathemancer]

I suggest making sure we use inclusive BETWEEN (i.e., x BETWEEN y AND Z should be equivalent to x >= y AND x <= z rather than x > y AND x < z). This will make things consistent with the BETWEEN operator in PostgreSQL.

Do we want to use symmetric between, or not? I.e., should 5 BETWEEN 8 AND 2 be true, or false? (for details about symmetric between, see: https://www.postgresql.org/docs/13/functions-comparison.html).

[kgodey]

I agree with using inclusive BETWEEN. I think using symmetric between also makes sense.

[dmos62]

Since "between" is ambiguous about its symmetry and inclusiveness, maybe instead let's have the user create/compose two simpler filters where these properties are explicit: a >= x AND a <= y instead of a BETWEEN x AND y. I doubt there's a performance concern, but if there were we could use pattern matching to optimize the filter behind the scenes. Seems desirable to not have the user wonder what "between" means.

[mathemancer]

I definitely think we should have the former, but I'm not convinced that should be in lieu of the latter. Maybe initially, though. @kgodey I note that sqlalchemy-filters doesn't include a between operator, so we'd have to write one custom for that. I guess I'm ambivalent w.r.t. whether BETWEEN should be prioritized at the moment.

[kgodey]

I'm fine with skipping between.

[dmos62]

I'm starting to draft a design. The idea at the moment is to formalize the filters on the db namespace using dataclasses. In some ways this will be a test bed for #823.

[dmos62]

Actually, since the filtering will target Mathesar types (correct?), which the db namespace is oblivious of, and which are defined in mathesar.database, filters should probably be defined there too.

[kgodey]

Filtering should be for DB types. Mathesar types are only used to group DB types, they shouldn't have other functionality applied to them.

[dmos62]

Filtering should be for DB types. Mathesar types are only used to group DB types, they shouldn't have other functionality applied to them.

Oh, what led me astray was that the Boolean filters are defined on Mathesar Types. It's not a big deal, I was going to rewrite them anyway.

[kgodey]

Yes, please rewrite them to fit, thanks!