Re: REST API: Sorting, wish-list

On Sat, Aug 24, 2019 at 04:58:32PM +0200, Ralf Schlatterbeck wrote:
> Sorting
> -------
> 
> I noticed that we don't have sorting in the API. Similar to the
> @sort/@group syntax in the index urls I'd suggest a @sort property that
> gets a list of properties (similar to @fields) but with an optional
> prepended '-' to the property indicating reverse-sorting by that
> property?
> Example:
> @sort=creator,-creation
> would give us a list of issues sorted by creator and creation date,
> earlier dates last (reverse-sorted by creation date).

This is now implemented.

> Security
> --------
> 
> I also noticed that the security checks for attributes seem to (always?)
> check the whole class, not individual properties. I think I can come up
> with a patch to fix this.

Fixed.

> Limit
> -----
> 
> Then I'd like to have a '@limit=number' (similar to SQL limit) clause
> that can limit the number of results returned (don't we already have
> something like this?). The idea is to return the biggest or smallest
> item (think dates) of something. So @sort + @limit=1 would get me this.
> Note that it would be a lot faster if this is handed down to SQL.

Now we have 'offset' and 'limit' parameters in Class.filter.
These are used for the existing pagination of the REST API

They could also be used in the batch implemenation in index template but
currently this is not done.  The index templates currently display the
number of items found and we would need a 'count' SQL implementation for
this and it is doubtful if making the same query twice, once with
'count', once without would speed up things as long as we do not
retrieve the issue properties in one go from the database.

So the existing pagination implementation with '@page_index' and
'@page_size' now uses SQL limit and offset for optimizing the search.

With the @sort above it's now possible to retrieve a maximum/minimum
(depending on sort order) for a given query with @page_size=1.

> Substring search
> ----------------
> 
> Currently strings do a substring-search. For an exact match we would
> have to extend the 'filter' method of the database. This is (at least
> for me) probably quite easy to do.
> 
> How would we change the syntax of the REST queries?
> I'd suggest we change the semantics to do an exact match for
> property=string searches, that leave us with the question how substring
> search should be expressed.

I've now implemented exact string search.
For the filter method in Class, this just takes an additional dictionary
named 'exact_match_spec' similar to the existing filterspec.

In the REST-API I've adopted the following syntax:

propname:=exact
for the exact string search
propname~=nonexact
for the case-insensitive substring search

The syntax with a single '=' still does the substring search as before.
The same syntax can also be used for properties other than String, but
makes no difference.

The ':' *before* the '=' (and likewise the ~) was adopted because the
form parse will put the ':' or '~' at the end of the property name. In
this way we do not exclude certain strings from being searched. A
special character *after* the '=' would have the drawback that this
character could not start a string to be searched.

Let me know what you think about the new syntax.

Ralf
-- 
Dr. Ralf Schlatterbeck                  Tel:   +43/2243/26465-16
Open Source Consulting                  www:   http://www.runtux.com
Reichergasse 131, A-3411 Weidling       email: [email protected]