Pagination support in dbcore

[PierreRust]

The creation of this issue has been discussed in #736.
To implement pagination properly in the HTTP API, we need to add support for pagination in dbCore. Ideally the pagination mechanism should also be usable with the command-line interface.

Ideas about pagination have been discussed in #718. It seems were are basically 4 options :

• the naive approach : using start/offset parameters in query. While this is trivial to implement it is definitely not very efficient.
• use a seek-able id in the query ( i.e. "gives me the next items starting with this one") : this is the approach recommended by sqlite, we have to make a small prototype to make sure it's doable with complex queries.
• when performing a query, store temporarily the result and return a token to the client, which can be used to walk through the ResultSet. The difficult part here is to maintain, and discard at some point, the ResultSets
• last solution : only return id for complex queries. This looks nice but I'm afraid it might not play well with the current query mechanism, which fetches full rows anyway.

[sampsyo]

Good plan.

Even though offset/limit is clearly the most straightforward API, I do tend to agree that the anchor-item approach is the most future-scalable. Perhaps the methods can be made to accept either a full Model instance (wherein we will look up the necessary fields for constructing the inequality in the WHERE clause) or an ID (which will cause an extra query to first look up the Model).

Of course, we will also need a (slow) alternative for slow (in-Python) queries. It won't be too hard to just skip over all rows before the anchor item is seen.

I am so not an expert in this area, though. Folks with more experience writing web apps, for example, probably have better opinions than I do.

[pjones]

This would be especially useful for the Smart Playlist and Random plug-ins. Right now there's no way to limit the number of tracks generated by the Smart Playlist plug-in.

[jonathanthomas83]

Sorry guys, I'm not sure if I'm barking up the wrong tree here, and please tell me to get lost, but from a UI perspective, pagination or pager navigation isn't the most user friendly interface to have to use - we've done a lot of usability testing and found that they're not as straightforward as we'd like to think. You'd be surprised at the types of feedback, which I can share if you need me to.

A lot of the stuff mentioned here goes right over my head, but after reading #738, it seems like pagers/paged navigation methods will play a part in the proposed UI implementations. Can we reconsider their use and look at alternative options before making a decision - like I said, in terms of UI. Many thanks.

[sampsyo]

Yes! I absolutely agree that it's not a foregone conclusion that the interface should paginate. But it's still useful for the API to admit pagination for efficiency reasons—independent of the interface.

[jonathanthomas83]

That's good news, as long as we're on the same "page" ;-)

FWIW, our guys are still developing paging on the API too, makes sense.