sql: sqlite: server side paging

Question

I have a client talking with server program(using sqlite3 as the storage) which needs to support paging. I am thinking about how to implement that. One approach:

1) user request page 1
  a. execute query from sqlite3
  b. return the first page range items to client

2) user request page N
  a. execute query from sqlite3
  b. return the N page range itmes to client

So in my approach every time user requests data I will re issue the query and get the specified elements, which seems waste of time...(comparing with caching all the items one time and just give the portion user asks, but this approach is more complex, as I need to timeout the cache - user is using browser I don't know when the user is loggout and the cache is useless)

Any better ideas?

Answer 1

Maybe you could use the LIMIT… OFFET… capability of SQLite.

Taken from the select syntax documentation:

Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma. In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression. This is counter-intuitive, as when using the OFFSET clause the second of the two expressions is the OFFSET and the first the LIMIT. This is intentional - it maximizes compatibility with other SQL database systems.