Add support for pagination

[jamesaoverton]

One of the next features we need is support for paging through result sets. We want to follow PostgREST https://postgrest.org/en/stable/api.html#limits-and-pagination. The basic situation is that we query SQLREST with a LIMIT and an OFFSET, and we want:

1. the result rows we asked for, accounting for the LIMIT and OFFSET
2. the size of the larger result set, ignoring the LIMIT, e.g. 3573458
3. our location in the larger result set, e.g. 0-24

PostgREST returns pagination information as part of an HTTP response, e.g.

HTTP/1.1 206 Partial Content
Range-Unit: items
Content-Range: 0-24/3573458

I think that we want to return a JSON object, with a "rows" key for the result rows, and one or more keys for the pagination information. Maybe something like:

{
  "status": 206,
  "unit": "items",
  "start": 0,
  "end": 24,
  "count": 3573458
}

We could then turn this JSON into an HTTP response if desired.

I'm not exactly sure how all of this should work. Here are my current thoughts.

PostgREST has multiple options for getting overall counts, but we should start with exact counts. We could start with a two-query approach, with counting and results as separate queries. I would like to at least try and make window functions work.

For the two query approach, maybe we add methods like select.to_sqlite_count() that generates a SQL string. For example: If our URL is foo?bar=gt.1&order=bar.asc&offset=10&limit=20, then the current select.to_sqlite() returns something like:

SELECT *
FROM foo
WHERE bar > 1
ORDER BY bar ASC
OFFSET 10
LIMIT 20

and the new select.to_sqlite_count() would return something like:

SELECT COUNT(*)
FROM foo
WHERE bar > 1

Then we would want a new method like select.fetch_as_json(pool, params) which would execute both queries and return a JSON object as above.